当前位置：首页 > news >正文

MySQL索引设计与优化

news 2026/6/10 22:18:48

摘要：索引是 MySQL 性能优化中最直接、最有效的手段。本文从 InnoDB 索引的底层数据结构出发，深入讲解聚簇索引、二级索引、联合索引的最左前缀原则、覆盖索引等核心概念，并结合 8 大索引失效场景与 EXPLAIN 实战分析，帮你建立系统化的索引设计思维。全文包含 6 张原创原理图，建议收藏。

一、为什么需要索引？

没有索引的查询，MySQL 只能逐行扫描整张表，时间复杂度为O(n)。当表数据达到千万级时，一次查询可能需要数秒甚至数分钟。

索引的本质是排好序的数据结构，它通过牺牲一定的写入性能和存储空间，换取查询效率的指数级提升。InnoDB 使用的是B+ 树索引，单次查询的时间复杂度为O(log n)，千万级数据通常只需3~4 次磁盘 IO。

二、InnoDB 索引的底层结构：B+ 树

2.1 B+ 树的核心特点

B+ 树相比 B 树和红黑树，有以下几个专为磁盘存储设计的优势：

特性	B+ 树	B 树	红黑树
数据存储位置	仅叶子节点	所有节点	所有节点
树高	更低（多路平衡）	较高	最高（二叉树）
范围查询	叶子节点链表，顺序 IO	中序遍历，随机 IO	中序遍历
查询稳定性	所有查询到叶子节点	可能在内部节点命中	不稳定
适用场景	磁盘数据库	内存数据结构	内存数据结构

关键设计：

**内部节点（非叶子节点）**只存储键值和指针，不存数据，使得一个节点能容纳更多键值，进一步降低树高
叶子节点存储完整的行数据（聚簇索引）或主键值（二级索引），且叶子节点之间通过双向链表连接，支持高效的范围查询和排序
树高通常 2~4 层，即使千万级数据，也只需 3~4 次 IO 即可定位到数据页

三、聚簇索引 vs 二级索引

3.1 聚簇索引（Clustered Index）

InnoDB 的表数据本身就是按照主键顺序存储的 B+ 树，这种索引称为聚簇索引。

叶子节点存储的是完整的行数据
一张表只能有一个聚簇索引（因为数据只能有一种物理排序方式）
如果未显式定义主键，InnoDB 会自动选择第一个非空唯一索引，或隐式生成一个 6 字节的 row_id

3.2 二级索引（Secondary Index）

除主键索引外的其他索引都是二级索引（也叫辅助索引）。

叶子节点存储的是「索引列 + 主键值」
通过二级索引查询时，需要先找到主键值，再回表到聚簇索引查完整数据

回表（Bookmark Lookup）：通过二级索引查到主键值后，再用主键值到聚簇索引中查找完整行数据的过程。回表会产生额外的随机 IO，是查询性能下降的主要原因之一。

四、索引设计的核心原则

4.1 索引选择性（Cardinality）

索引的选择性 =COUNT(DISTINCT 列值) / COUNT(*)，比值越接近 1，索引效果越好。

-- 计算某列的选择性SELECTCOUNT(DISTINCTuser_id)/COUNT(*)ASselectivityFROMorders;

高选择性列（如用户 ID、手机号、邮箱）：非常适合建索引
低选择性列（如性别、状态标志）：单独建索引意义不大，优化器可能直接选择全表扫描。可作为联合索引的后缀列

4.2 联合索引的最左前缀原则

联合索引(a, b, c)在 B+ 树中是按照a 先排序，a 相同再按 b 排序，b 相同再按 c 排序的方式存储的。

匹配规则：

SQL 条件	索引使用情况	说明
`WHERE a = 1`	✅ 使用索引	匹配最左列
`WHERE a = 1 AND b = 2`	✅ 使用索引	匹配前两列
`WHERE a = 1 AND b = 2 AND c = 3`	✅ 完全使用	完美匹配
`WHERE b = 2`	❌ 索引失效	缺少最左列 a
`WHERE a = 1 AND c = 3`	⚠️ 只用 a	跳过了 b，c 无法使用
`WHERE a > 1 AND b = 2`	⚠️ 只用 a	范围查询后的列失效
`WHERE a = 1 ORDER BY b`	✅ 使用索引排序	避免 filesort
`WHERE a = 1 ORDER BY c`	❌ 索引失效	跳过了 b

设计口诀：

等值查询的列放前面，范围查询的列放后面，排序/分组列考虑进索引顺序。

4.3 覆盖索引（Covering Index）

如果查询的所有字段都在索引中，MySQL 无需回表，直接从索引返回结果，这种索引称为覆盖索引。

覆盖索引的优势：

避免回表：减少一次聚簇索引查找，消除随机 IO
减少 IO：索引页通常比数据页小，同样的内存能缓存更多索引页
避免排序：如果ORDER BY/GROUP BY的列在索引中，可直接利用索引有序性

设计方法：将查询中SELECT、WHERE、ORDER BY、GROUP BY涉及的列都纳入联合索引。但需注意索引不是越多越好，要权衡写入性能和存储成本。

-- 示例：为高频查询设计覆盖索引-- 原查询: SELECT name, age FROM user WHERE city = '上海' AND age > 30;-- 最优索引: (city, age, name)-- city 和 age 用于 WHERE 过滤，name 用于 SELECT 返回，无需回表CREATEINDEXidx_city_age_nameONuser(city,age,name);

五、索引失效的 8 大场景与优化方案

场景 1：对索引列使用函数或运算

-- ❌ 索引失效：函数破坏了索引的有序性SELECT*FROMuserWHEREYEAR(create_time)=2023;-- ✅ 优化为范围查询SELECT*FROMuserWHEREcreate_time>='2023-01-01'ANDcreate_time<'2024-01-01';

原理：索引存储的是列的原始值，一旦使用函数，优化器无法将函数结果与索引键值直接比较。

场景 2：隐式类型转换

-- ❌ 索引失效：user_id 是 VARCHAR，传入数字导致隐式转换SELECT*FROMuserWHEREuser_id=123;-- ✅ 保持类型一致SELECT*FROMuserWHEREuser_id='123';

原理：MySQL 会将索引列转换为传入值的类型再比较，相当于对索引列做了隐式函数处理。

场景 3：LIKE 前导模糊查询

-- ❌ 索引失效：前导 % 无法利用 B+ 树前缀有序性SELECT*FROMuserWHEREnameLIKE'%张三%';-- ✅ 右模糊查询可以使用索引SELECT*FROMuserWHEREnameLIKE'张三%';

优化方案：如果业务必须前后模糊，考虑使用全文索引（FULLTEXT）或引入Elasticsearch。

场景 4：违反最左前缀原则

-- 索引: idx_abc (a, b, c)-- ❌ 索引失效：缺少最左列 aSELECT*FROMtWHEREb=2ANDc=3;-- ✅ 正确使用SELECT*FROMtWHEREa=1ANDb=2;

场景 5：范围查询后的列失效

-- 索引: idx_abc (a, b, c)-- ⚠️ b 使用范围查询后，c 无法使用索引SELECT*FROMtWHEREa=1ANDb>10ANDc=3;-- 优化思路：如果 c 的过滤性很强，考虑调整索引顺序为 (a, c, b)-- 或拆分为两个查询 UNION ALL

场景 6：OR 条件使用不当

-- ❌ OR 一边无索引，可能导致全表扫描SELECT*FROMtWHEREindexed_col=1ORnon_indexed_col=2;-- ✅ 拆分为 UNION ALLSELECT*FROMtWHEREindexed_col=1UNIONALLSELECT*FROMtWHEREnon_indexed_col=2ANDindexed_col<>1;

场景 7：使用 NOT、!=、<> 操作符

-- ❌ 负向查询通常不走索引（取决于数据分布）SELECT*FROMordersWHEREstatus!='completed';-- ✅ 改写为正向查询SELECT*FROMordersWHEREstatusIN('pending','shipped','paid');

场景 8：优化器主动放弃索引

当查询需要返回的数据量超过表总行数的20%~30%时，优化器认为回表成本高于全表扫描，会主动放弃索引。

-- 假设 status 只有 'active'/'inactive'，且各占 50%-- 优化器可能选择全表扫描SELECT*FROMuserWHEREstatus='active';-- ✅ 优化方案 1：使用覆盖索引SELECTid,nameFROMuserWHEREstatus='active';-- 如果索引包含这些列-- ✅ 优化方案 2：增加过滤条件缩小范围SELECT*FROMuserWHEREstatus='active'ANDcreate_time>'2024-01-01';

六、EXPLAIN 实战：如何分析执行计划

6.1 关键字段解读

字段	含义	优化目标
type	访问类型	至少达到`range`，最好是`ref`/`eq_ref`/`const`
key	实际使用的索引	不能为 NULL
rows	预估扫描行数	越小越好，对比表总行数评估
Extra	额外信息	避免出现`Using filesort`、`Using temporary`

6.2 type 性能等级（从优到劣）

system > const > eq_ref > ref > range > index > ALL

const：主键或唯一索引等值查询，最多返回 1 行
eq_ref：JOIN 中被驱动表的主键或唯一索引查询
ref：非唯一索引等值查询
range：索引范围扫描（>、<、BETWEEN、IN）
index：全索引扫描（遍历整棵索引树）
ALL：全表扫描，性能最差

6.3 Extra 关键标识

标识	含义	是否需要优化
Using index	覆盖索引，无需回表	✅ 性能最优
Using where	回表后使用 WHERE 过滤	⚠️ 检查是否可优化为覆盖索引
Using filesort	需要额外排序操作	❌ 需优化，尝试利用索引排序
Using temporary	需要创建临时表	❌ 需优化，常见于复杂 GROUP BY
Using index condition	ICP 索引下推（5.6+）	✅ 减少回表次数

6.4 实战案例

-- 假设表结构CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50),ageINT,cityVARCHAR(50),create_timeDATETIME,INDEXidx_name(name),INDEXidx_city_age(city,age));-- 案例 1：检查是否使用覆盖索引EXPLAINSELECTname,ageFROMuserWHEREcity='上海'ANDage>25;-- 期望: type=range, key=idx_city_age, Extra=Using index condition-- 案例 2：检查是否出现 filesortEXPLAINSELECT*FROMuserWHEREcity='上海'ORDERBYage;-- 如果 Extra 出现 Using filesort，说明未利用索引排序-- 优化: 确保 ORDER BY 列与索引顺序一致-- 案例 3：检查索引是否失效EXPLAINSELECT*FROMuserWHEREYEAR(create_time)=2024;-- 如果 type=ALL, key=NULL，说明索引失效，需要改写 SQL

七、索引设计规范总结

7.1 应该建索引的列

WHERE、JOIN ON、ORDER BY、GROUP BY、DISTINCT涉及的列
外键列（保证引用完整性，自动创建索引）
高选择性列（区分度 > 0.1）

7.2 不应该建索引的列

低选择性列（如性别、布尔值）单独建索引
很少作为查询条件的列
频繁更新的列（索引维护成本高）
超长的 VARCHAR 列（考虑前缀索引）

7.3 联合索引设计 checklist

□ 把等值查询条件列放在最左侧 □ 把范围查询列放在等值列之后 □ 把 ORDER BY / GROUP BY 列纳入索引（保持顺序一致） □ 考虑覆盖索引，减少回表 □ 评估索引选择性，确保最左列区分度高 □ 避免冗余索引（如已有 (a,b,c)，则 (a,b) 是冗余的）

7.4 索引维护建议

-- 1. 定期查看未使用的索引并清理SELECT*FROMsys.schema_unused_indexes;-- 2. 查看索引选择性SELECTINDEX_NAME,COLUMN_NAME,CARDINALITYFROMINFORMATION_SCHEMA.STATISTICSWHERETABLE_NAME='your_table';-- 3. 分析表更新统计信息（大数据量变更后执行）ANALYZETABLEyour_table;-- 4. 重建索引（碎片率高时）OPTIMIZETABLEyour_table;-- 或 ALTER TABLE your_table ENGINE=InnoDB;