MySQL索引入门

1. 什么是索引？

索引是 MySQL 数据库为提升数据检索效率而设计的有序数据结构，其核心作用是将无序的原始数据转化为可快速定位的有序结构，类比日常使用的字典 —— 通过拼音 / 部首索引能直接定位到目标汉字，而非逐页翻阅。

从底层逻辑来看，索引本身是一张独立的 “映射表”，存储了索引字段的值、对应的主键，以及指向实体表记录的物理存储地址。由于实际业务中数据表的体量通常极大，索引文件本身也会占用大量存储空间，无法全部加载到内存中，因此索引默认以文件形式存储在磁盘上，且其排序逻辑与TreeSet（红黑树）一致，保证了查找、插入、删除操作的高效性。

索引的核心优势：

• 提升查询效率：合理设计的索引能将全表扫描转化为索引树的精准查找，使 MySQL 的查询性能实现质的飞跃
• 降低数据库 IO 开销：索引通过有序结构减少了磁盘 IO 的次数，避免了对全表数据的逐一读取，大幅降低硬件资源消耗

索引的潜在代价：

• 更新性能损耗：对表执行INSERT、UPDATE、DELETE操作时，MySQL 不仅要修改实体数据，还需同步更新索引文件，数据量越大、索引越多，更新耗时越明显。
• 磁盘空间占用：每个索引都会生成独立的索引文件，无节制创建索引会快速消耗磁盘存储空间。
• 维护成本增加：过多的索引会增加数据库的维护负担，如索引碎片清理、优化器选择最优索引的计算成本等。

2. 索引的底层实现与存储特性

在 MySQL 中，索引是独立于数据表的对象，不同存储引擎对索引的存储方式和实现逻辑不一样：

• MyISAM 引擎：索引与数据分离存储，索引文件（.MYI）单独存放索引信息，数据文件（.MYD）存储实际数据，索引中记录的是数据行的物理存储地址。
• InnoDB 引擎：采用 “聚簇索引” 设计，索引与数据融合存储在表空间（tablespace）中 —— 主键索引的叶子节点直接存储整行数据，辅助索引的叶子节点存储主键值，通过主键再回表查询完整数据。
• MEMORY 引擎：索引直接存储在内存中，基于哈希表或 B 树实现，适用于临时表、高频读写的小表，但数据重启后会丢失。

3. 什么时候适合添加索引？

索引并非越多越好，以下场景适合添加索引：

• 数据量阈值达标：当表中数据量达到一定规模（通常建议万级以上），索引的收益会远大于维护成本；小表使用索引反而可能因索引查找 + 回表的额外开销降低效率
• 查询条件高频字段：字段频繁出现在WHERE子句、JOIN关联条件、ORDER BY/GROUP BY子句中
• DML 操作低频字段：字段极少被修改；若字段需高频更新，则需谨慎创建索引

PS：索引的添加必须适可而止，建议单表索引数量不超过 5 个，核心业务字段优先，非核心查询字段不建索引

4. 索引的创建、删除

1、索引的创建

-- 为temp表的ENAME字段创建单列索引mysql>createindextemp_ename_indexontemp(ENAME);Query OK,0rowsaffected(0.15sec)Records:0Duplicates:0Warnings:0-- 查看表结构，确认索引创建成功（Key列显示MUL表示存在非唯一索引）mysql>desctemp;+----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+---------+-------+|EMPNO|int(4)|NO||NULL|||ENAME|varchar(10)|YES|MUL|NULL|||JOB|varchar(9)|YES||NULL|||MGR|int(4)|YES||NULL|||HIREDATE|date|YES||NULL|||SAL|double(7,2)|YES||NULL|||COMM|double(7,2)|YES||NULL|||DEPTNO|int(2)|YES||NULL||+----------+-------------+------+-----+---------+-------+8rowsinset(0.00sec)

-- 为temp表的ENAME和JOB字段创建复合索引mysql>createindextemp_ename_job_indexontemp(ename,job);Query OK,0rowsaffected(0.14sec)Records:0Duplicates:0Warnings:0

2、索引的删除

-- 删除temp表的temp_ename_index索引mysql>dropindextemp_ename_indexontemp;Query OK,0rowsaffected(0.07sec)Records:0Duplicates:0Warnings:0

3、索引有效性验证：EXPLAIN关键字

EXPLAIN可判断索引是否被命中：

• type列：显示查询类型，ALL表示全表扫描（索引未生效），ref/range/eq_ref等表示索引生效；
• key列：显示实际使用的索引名称，NULL表示未使用索引；
• rows列：显示 MySQL 预估需要扫描的行数，数值越小说明查询越高效。

示例：无索引时查询ename = "KING"的执行计划

mysql>explainselect*fromtempwhereename="KING";+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|1|SIMPLE|temp|NULL|ALL|NULL|NULL|NULL|NULL|14|10.00|Usingwhere|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1rowinset,1warning(0.00sec)

解读：type=ALL表示全表扫描，key=NULL表示未使用索引，需扫描 14 行数据，效率较低。

5. 索引失效的六大场景

1、场景 1：模糊查询以 % 开头

当LIKE查询的匹配符%出现在字符串开头时，索引无法被匹配，触发全表扫描：

-- 索引失效：%在开头select*fromempwhereenamelike'%T';-- 索引生效：%在结尾select*fromempwhereenamelike'T%';

优化方案：避免前缀模糊查询；若业务必须使用，可考虑全文索引（FULLTEXT）或数据预处理（如存储字段的反向字符串）

2、场景 2：OR连接的条件字段未全部建索引

OR查询的逻辑是 “只要满足任一条件即可”，若OR两侧的字段有一个未建索引，MySQL 会放弃索引，选择全表扫描

-- 假设仅ename有索引，job无索引，此查询索引失效select*fromempwhereename='KING'orjob='MANAGER';

优化方案：尽量用UNION替代OR；若必须用OR，则确保所有条件字段都建索引。

3、场景 3：复合索引未遵循 “最左前缀原则”

复合索引的生效顺序是从左到右，查询时若跳过左侧的索引字段，直接使用右侧字段，索引会完全失效：

-- 复合索引：temp_ename_job_index (ename, job)-- 索引生效：使用左侧字段enameselect*fromtempwhereename="KING";-- 索引失效：跳过ename，直接使用jobselect*fromtempwherejob='MANAGER';-- 索引生效：遵循最左前缀，同时使用ename+jobselect*fromtempwhereename="KING"andjob='MANAGER';

优化方案：创建复合索引时，将查询频率最高的字段放在左侧

4、场景 4：索引列参与数学 / 算术运算

若在WHERE子句中对索引字段进行加减乘除等运算，MySQL 无法直接使用索引，需先计算再匹配，导致索引失效：

-- 索引失效：sal字段参与运算select*fromempwheresal+1000>5000;-- 索引生效：将运算移到右侧（等价逻辑）select*fromempwheresal>5000-1000;

优化方案：避免在索引列上执行运算，将运算逻辑转移到查询条件的右侧

5、场景 5：索引列使用函数处理

对索引字段使用内置函数（如SUBSTR、DATE_FORMAT、CONCAT等），会破坏索引的有序性，导致索引失效：

-- 索引失效：ename使用SUBSTR函数select*fromempwhereSUBSTR(ename,1,1)='K';-- 索引失效：hiredate使用DATE_FORMAT函数select*fromempwhereDATE_FORMAT(hiredate,'%Y')='1981';

优化方案：避免在索引列上使用函数；若业务需要，可通过新增冗余字段（如存储ename的首字母、hiredate的年份）建立索引

6、场景 6：隐式类型转换导致索引失效

当索引字段的类型与查询条件的值类型不一致时，MySQL 会进行隐式类型转换，进而触发索引失效：

-- 假设empno是int类型，以下查询索引失效（字符串转数字）select*fromempwhereempno='7369';-- 索引生效：类型匹配select*fromempwhereempno=7369;

优化方案：确保查询条件的值类型与索引字段类型完全一致，避免隐式转换

6. 索引的完整分类

1、按索引字段数量划分

• 单列索引：仅对单个字段创建的索引
• 复合索引：对多个字段组合创建的索引

2、按索引特性划分

• 主键索引：主键字段自动生成的索引，具有唯一性且非空，一张表只能有一个主键索引
• 唯一性索引：基于UNIQUE约束的字段创建的索引，保证索引字段的值唯一
• 普通索引：无唯一性约束的索引，仅用于提升查询效率
• 全文索引：针对文本内容的索引（如文章、评论），支持模糊匹配和关键词检索，仅适用于CHAR、VARCHAR、TEXT类型

3、按存储结构划分

• B + 树索引：MySQL 默认的索引结构，所有数据存储在叶子节点，非叶子节点仅存索引值，支持范围查询、排序，适用于绝大多数场景
• 哈希索引：基于哈希表实现，查找速度极快（O (1)），但不支持范围查询、排序，MEMORY 引擎默认使用哈希索引。
• 空间索引：针对地理空间数据（如GEOMETRY、POINT类型）的索引，适用于位置相关的查询，使用场景较窄。