MySQL数据库—索引

一、索引基础1. 什么是索引索引是一种数据结构它通过对表中数据按照特定规则排序帮助数据库快速定位和查询数据本质上类似于字典的目录先查目录找到页码再翻到对应页找具体内容。2. 为什么要使用索引核心目的提升查询效率在海量数据下避免全表扫描。** trade-off**查询效率提升的同时会降低写入INSERT/UPDATE/DELETE效率因为索引需要同步维护。3. 索引的数据结构选型不同数据结构的特性决定了其适用场景MySQL 最终选择了B 树作为索引的底层数据结构表格数据结构时间复杂度优点缺点MySQL 支持HASHO(1)等值查询极快不支持范围查询❌ 不默认使用二叉搜索树O(logN)结构简单树高不可控磁盘 I/O 次数多❌ 不使用N 叉树O(logN)降低树高减少 I/O节点数据冗余⚠️ 过渡方案B 树O(logN)支持范围查询I/O 次数少性能稳定结构相对复杂✅InnoDB 默认使用二、B 树与 MySQL 存储结构1. B 树核心特性非叶子节点只存储索引键和子节点指针不存储真实数据因此单个节点可以存储更多索引大幅降低树高。叶子节点包含完整数据记录且通过双向链表连接天然支持范围查询和排序。与 B 树的区别B 树数据只存在于叶子节点B 树所有节点都存数据。B 树叶子节点链表化范围查询更高效。相同树高下B 树能存储更多数据查询路径更短。2. MySQL 的页Page页是 InnoDB 与磁盘交互的最小单位默认大小为16KB。数据和索引都以页为单位存储利用局部性原理一次 I/O 加载一页数据到内存提升后续查询效率。页结构页头部元信息→ 数据行 → 页尾部校验信息。3. B 树在 MySQL 中的应用每个 B 树节点就是一个页根节点、非叶子节点、叶子节点共同构成完整索引树。聚簇索引主键索引的叶子节点直接存储完整行数据辅助索引的叶子节点存储主键值需要回表查询完整数据。三、索引分类与创建1. 索引分类表格索引类型定义特点主键索引 (PRIMARY KEY)唯一标识表中记录非空且唯一一张表只能有一个InnoDB 中是聚簇索引唯一索引 (UNIQUE)列值必须唯一允许一个 NULL业务字段有唯一性要求时使用普通索引 (INDEX)最基础的索引类型用于提升高频查询列的查询速度复合索引多个列共同组成的索引遵循最左前缀原则高效支持多条件查询覆盖索引索引包含查询所需的所有列避免回表查询效率极高2. 自动创建索引创建PRIMARY KEY、UNIQUE、FOREIGN KEY约束时MySQL 会自动创建对应索引。未指定主键时MySQL 会自动选择一个非空唯一列作为主键若无则生成隐藏ROW_ID作为主键。3. 手动创建索引3.1 主键索引sql-- 建表时指定 CREATE TABLE student ( id bigint PRIMARY KEY AUTO_INCREMENT, name varchar(20) NOT NULL ); -- 建表后添加 ALTER TABLE student ADD PRIMARY KEY (id);3.2 唯一索引sql-- 建表时指定 CREATE TABLE student ( id bigint PRIMARY KEY AUTO_INCREMENT, name varchar(20) UNIQUE ); -- 建表后添加 ALTER TABLE student ADD UNIQUE INDEX idx_name (name);3.3 普通 / 复合索引sql-- 建表时指定 CREATE TABLE student ( id bigint PRIMARY KEY AUTO_INCREMENT, name varchar(20), class_id bigint, INDEX idx_name_class (name, class_id) -- 复合索引 ); -- 建表后添加 CREATE INDEX idx_sno ON student (sno); ALTER TABLE student ADD INDEX idx_name (name);4. 删除索引sql-- 删除主键索引需先移除 AUTO_INCREMENT ALTER TABLE student MODIFY id bigint; ALTER TABLE student DROP PRIMARY KEY; -- 删除其他索引 ALTER TABLE student DROP INDEX idx_name;四、索引使用与优化1. 索引生效规则最左前缀原则复合索引会按照创建顺序排序查询时必须包含索引的最左列才能生效。例idx(a,b,c)支持WHERE a?、WHERE a? AND b?、WHERE a? AND b? AND c?不支持WHERE b?。覆盖索引查询的列都在索引中无需回表查询速度最快。回表查询通过辅助索引找到主键后再通过主键索引查询完整行数据。2. 索引失效场景使用LIKE %xxx以通配符开头。对索引列进行函数 / 运算操作如WHERE YEAR(create_time) 2024。类型隐式转换如WHERE id 123id 是数字类型。复合索引未遵循最左前缀原则。数据量过小优化器选择全表扫描。3. 查看与分析索引sql-- 查看表中所有索引 SHOW INDEX FROM student; -- 查看 SQL 执行计划判断是否使用索引 EXPLAIN SELECT * FROM student WHERE name 张三;type字段ref/range表示使用了索引ALL表示全表扫描。key字段显示实际使用的索引名称。五、最佳实践总结选择合适的列创建索引高频查询列、WHERE条件列、JOIN关联列。优先使用复合索引替代多个单列索引减少索引数量提升查询效率。避免过度索引索引越多写入性能越差。使用覆盖索引将查询所需列都包含在索引中避免回表。定期维护索引大数据量下考虑使用OPTIMIZE TABLE重建索引减少碎片。谨慎使用SELECT *只查询需要的列更容易命中覆盖索引。