MySQL 索引优化这件事，很多搞后端和数据库的人最终都会走到这一步。一开始可能只是简单地“加了索引就变快了”，但真正到了线上问题排查、SQL 慢查询分析的时候才发现，索引远不是“建一个 B+Tree”这么简单。这篇内容我会结合自己这些年做数据库优化、处理线上 SQL 性能问题的实际经验，把 MySQL 索引从底层结构、设计原则到最常踩的坑完整过一遍，希望能帮你在做表结构设计和 SQL 优化时，少走一些弯路。
文章的主角是MySQL 索引优化，或者说，是基于 InnoDB 存储引擎下索引的完整实践总结。不管你是刚接触数据库的写 SQL 新手，还是工作中天天面对慢查询的业务后端，这篇文章都会给你一套可以落地的思路。
1. 索引选型与底层结构：先搞清楚 B+Tree 才谈得上优化
很多人知道 InnoDB 用 B+Tree，但为什么偏偏是 B+Tree，而不是其他结构？这个问题的答案，直接决定了你后面如何设计索引字段、如何评估索引效果。
1.1 为什么 MySQL 选择 B+Tree 而不是红黑树或哈希表
先看红黑树。红黑树的本质是二叉树，树的高度和节点数量成对数关系，但在数据量大的场景下，比如一张表 1000 万行数据，红黑树的高度会达到 20 到 30 层。每访问一层，在磁盘上就是一次 I/O，一次随机 I/O 的耗时大约是 10 毫秒级别，30 次就是 300 毫秒，这已经是一个无法接受的查询延迟。
再看哈希表。哈希索引做等值查询确实是 O(1) 级别，但它天然不支持范围查询。实际业务里“SELECT ... WHERE id BETWEEN 100 AND 200”“按时间范围查订单”这类需求太常见了，哈希索引直接歇菜。所以哈希表只能作为自适应哈希索引存在，不是主索引结构。
B+Tree 牛在哪？它是一个多路平衡树，每个节点能存很多个 key，树的高度被压得非常低。InnoDB 一个数据页默认 16KB，假设一行数据 1KB，一个叶子节点就能存约 16 行，一个三层高的 B+Tree 大约能存储 2000 多万条记录。这意味着，就算表里有几千万条数据，走主键索引查找一条记录，也只需要 3 次磁盘 I/O。
还有一点很关键：B+Tree 的叶子节点之间通过双向链表连接，这使得范围查询只需要找到边界，然后在链表上顺序遍历即可，效率远高于其他树结构。
1.2 聚簇索引与非聚簇索引的本质区别
在 InnoDB 中，表数据本身就是按照主键索引（聚簇索引）的顺序存储在叶子节点上的。也就是说，找到了主键索引，就找到了整行数据，不需要再回表。这也是为什么 InnoDB 建表必须有一个主键，如果没有显式定义主键，InnoDB 会找一个非空唯一索引作为主键，如果没有合适的，会隐藏生成一个 rowid 作为主键。
非聚簇索引（也叫二级索引）则不同，它的叶子节点存的是索引列的值加主键值。比如你在 name 字段上建了一个索引，那么索引树里存的是 name 和主键 id。你通过 name 查数据时，先在二级索引树里找到对应的主键 id，再到聚簇索引里回表拿完整行数据。这个“两次查找”的过程就是回表。
这里有个实测中很容易忽略的性能差异，我用一个对比表总结一下：
| 对比项 | 聚簇索引（主键索引） | 二级索引（普通索引） |
|---|---|---|
| 叶子节点内容 | 整行数据 | 索引列 + 主键值 |
| 是否需要回表 | 不需要 | 通常需要 |
| 建表数量限制 | 一张表只有一个 | 一张表可以有多个 |
| 插入性能影响 | 影响最大，非顺序插入易页分裂 | 影响相对小，但索引太多也会拖慢写入 |
| 适合场景 | 主键等值/范围查询 | 高频查询列、排序列、联合查询 |
这个表如果你能看懂，那你在建索引时，第一反应就会从“这列要建索引吗”变成“这列建索引后，查询能不能覆盖索引从而避免回表”。
2. 索引设计的核心原则：从字段选择到联合索引排列
索引设计不是“哪个字段查得多就给哪个加”。我见过太多因为随意建索引导致的性能反噬案例：索引建了一堆，写入变慢，磁盘占用翻倍，查询却并没有明显加快。下面这部分内容，是我在实际项目中总结出的几条核心设计原则。
2.1 区分度是索引的第一生命线
索引的核心价值在于快速缩小数据扫描范围。如果一列的可选值非常少，比如性别只有“男”“女”，或者状态字段只有“0”“1”，那在它上面建索引几乎没有任何正向效果。因为优化器会算一个东西叫索引基数，当前列上不同值的个数和总行数的比值过低时，优化器会认为走索引还不如全表扫描。
这里有个可以直接用的经验值：索引列的区分度建议不低于 20%。也就是说，如果表有 100 万行，索引列不同值最好超过 20 万。当然，这不是一个绝对标准，但当你用SHOW INDEX FROM 表名看到 Cardinality 这个统计值明显偏低时，就要警惕这条索引可能是无效索引。
2.2 联合索引：顺序决定生死
联合索引是业务表设计中最容易出问题的地方。很多人把最常查的字段放前面，这个直觉是对的，但不完整。联合索引遵循最左前缀原则，即查询必须从联合索引的第一个字段开始匹配，逐步往右匹配，才能用到这个索引。跳过了前面的字段直接查后面的字段，索引就失效了。
举个例子，订单表上建了一个联合索引(user_id, status, created_at)，下面这些查询能用上索引：
WHERE user_id = 100 AND status = 1WHERE user_id = 100 ORDER BY created_at DESCWHERE user_id = 100 AND status IN (1,2)
而这三个查询用不上索引或者只能部分用上：
WHERE status = 1：跳过了最左字段 user_id，完全失效WHERE user_id = 100 AND created_at > '2024-01-01'：跳过了 status，只能用到 user_id 这个前缀WHERE created_at > '2024-01-01' ORDER BY user_id：完全不符合最左前缀
所以设计联合索引时，正确的思考顺序是：先看等值查询的字段有哪些，再看排序字段，最后才放范围查询字段。等值查询字段放最前面，排序字段可以紧跟其后，范围查询字段尽量放最后。
2.3 索引下推解决了什么问题
索引下推（Index Condition Pushdown，ICP）是 MySQL 5.6 引入的优化，很多人没用过，但它对联合索引的查询效率提升非常明显。
举个例子，联合索引是(name, age)，查询条件是WHERE name LIKE '张%' AND age = 20。在不支持 ICP 的情况下，InnoDB 会先从二级索引里把所有 name 以“张”开头的记录的主键找出来，回表读到整行，再判断 age 是否等于 20。这样回表的次数很多。
开启 ICP 后，MySQL 会在二级索引的遍历过程中，直接判断 age = 20 这个条件，过滤掉不符合的记录，只对通过筛选的记录回表。回表次数大幅度减少。
实际使用中，ICP 通常是默认开启的，你基本不用手动干预，但你需要意识到：联合索引字段越多，ICP 的过滤价值越大。这也是我建议不要过度精简联合索引字段数的原因之一——适当的冗余字段放进索引，可以让下推过滤更彻底。
3. 索引失效场景排查：那些写对了但没走索引的 SQL
索引设计得再合理，SQL 写得不讲究也白搭。这一节专门盘点我实际排查慢查询时最常遇到的索引失效场景，每个都会给出问题根因和改写方案。
3.1 隐式类型转换：最隐蔽的索引杀手
最常见的一个坑是字段类型和查询条件不匹配。比如表里 phone 字段是 varchar 类型，你写WHERE phone = 13800138000，这里传的是数字，MySQL 会把字段类型隐式转换成数字再比较，导致索引列的类型被函数化，索引索引列上发生了运算，索引就失效了。
为什么隐式转换会让索引失效？原因在于 MySQL 得把每一行的 phone 字段值先转换成数字，再和 13800138000 比较，索引树里存的是原始字符串，无法按数字顺序进行快速定位。解决方式很简单，所有条件都写成phone = '13800138000'，保持类型一致。
3.2 函数操作和表达式计算：索引列不能包在函数里
WHERE DATE(created_at) = '2024-06-01'这种写法，看起来人畜无害，但 created_at 上建的索引就是走不了。因为索引树里存的是完整时间值，你想要的是这一天的所有记录，MySQL 无法在索引树里直接按“这一天的日期”快速定位。
改写方式有两个思路。第一，把函数去掉，改写成范围查询：WHERE created_at >= '2024-06-01 00:00:00' AND created_at < '2024-06-02 00:00:00'，这是效率最高、最能利用索引的写法。第二，如果你确实经常按日期查询，可以考虑新增一个日期字段或者生成列并建索引。
3.3 模糊查询和 OR 条件：两个高频问题场景
模糊查询LIKE '%关键词%'无法走索引，这是老生常谈，因为数据库不知道通配符前的内容是什么，无法在索引树中定位。但LIKE '关键词%'是可以走索引的，这个区别很关键。业务上如果必须做中间模糊匹配，建议考虑全文索引或者引入搜索引擎，不要让这个 SQL 在 MySQL 上硬扛。
OR 条件则要分情况。如果 OR 两边的字段都分别建了索引，MySQL 理论上可以通过索引合并（index merge）来优化，但实际情况并不稳定，还是会有走全表扫描的情况。更稳妥的改写是把 OR 拆成 UNION ALL，或者用 IN 替代。举例：WHERE name = '张三' OR age = 20拆分后变成WHERE name = '张三' UNION ALL WHERE age = 20，两个子查询各自走索引，效率通常会更好。
3.4 范围查询后面的索引失效问题
联合索引中有范围查询字段时，后面的索引字段会失效。比如联合索引(status, created_at, updated_at)，查询条件是WHERE status = 1 AND created_at > '2024-01-01' AND updated_at > '2024-01-01'，这时候 status 和 created_at 能用上索引，但 updated_at 用不上了。
这不是 MySQL 的 bug，而是索引结构与范围查询天生冲突。索引树是按照(status, created_at, updated_at)的字典序排列的，当 created_at 的过滤条件是一个范围时，updated_at 在树中的位置无法被精确定位，就只能做回表后的二次过滤。碰到这种场景，我通常的建议是：把等值条件放联合索引前面，范围条件放最后一位，如果真的还有第二个范围条件，那就只能业务层拆 SQL，或者评估是否需要调整查询逻辑。
4. 实操案例：一次线上订单查询慢问题的完整调优过程
前面的内容偏理论，这一节我拿一个实操过的案例来完整走一遍排查与优化的流程。这个案例很有代表性，几乎覆盖了索引优化的大部分核心知识点。
4.1 现象与初步排查
有一个订单列表页接口，功能是按用户查询他的订单列表，并且按创建时间倒序展示。数据量大概是 500 万条订单记录，用户表 50 万。上线初期响应很快，之后数据量增长到 200 万时开始变慢，到 500 万时接口直接超时。
我第一反应是打开慢查询日志，把那个 SQL 捞出来，简化后结构像这样：
SELECT order_id, order_status, total_amount FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;表的索引设计是：主键索引，联合索引(user_id, created_at)。按道理这个 SQL 应该很快才对，但实际却扫描了大量数据。
4.2 用 EXPLAIN 定位问题
执行EXPLAIN SELECT ...后，关键信息是这样的：
| 字段 | 值 | 说明 |
|---|---|---|
| type | ref | 用了非唯一索引前缀查找 |
| key | idx_user_created | 实际用的索引 |
| rows | 38621 | 预估扫描了 3.8 万行 |
| Extra | Using filesort | 使用了文件排序 |
问题很快就清楚了：虽然走了联合索引，但因为用户下单记录很多，WHERE user_id = 12345命中了 3 万多行，然后 MySQL 需要把这 3 万多行的数据拿出来，再按 created_at 做一次文件排序，最后才取 20 条。这 3.8 万行还涉及回表取整行数据，性能自然上不去。
4.3 问题根源：索引顺序与排序字段的冲突
联合索引(user_id, created_at)其实已经考虑到了排序问题，但为什么还是用了 filesort？根源在于WHERE user_id = 12345得到的是一个等值条件，它定位到一个固定的 user_id 前缀，在这个前缀内部，数据本来就按照 created_at 排序。问题是查询返回的列除了 order_id 和 order_status，还有 total_amount，这需要回表取数据。
排序是要把回表拿到完整数据之后，按照 created_at 排好序，再取前 20 条。但实际上，如果你从(user_id, created_at)这个索引树里按顺序扫描，数据天然就是按 created_at 排好的。优化器没充分利用这一点，是因为它要回表拿数据，然后它选择了先在临时表里排序。
4.4 覆盖索引方案：一劳永逸的解决方式
最终我给出了一个非常经典的优化方案：把联合索引扩展为覆盖索引(user_id, created_at, order_id, order_status, total_amount)，让查询需要的所有列都在二级索引里。
改为覆盖索引之后，SQL 还是原来的 SQL，但执行计划变成了：
| 字段 | 值 | 说明 |
|---|---|---|
| type | ref | 命中索引前缀 |
| key | idx_user_created_cover | 覆盖索引 |
| rows | 46 | 优化器预估扫描行数大幅减少 |
| Extra | Using index | 不需要回表，索引覆盖 |
修改之后，接口响应时间从原来的 2 秒以上降到了 30 毫秒以内。这个案例的核心其实不是索引本身有多复杂，而是你看没看懂索引数据在树中是如何分布的。覆盖索引的威力在于：当你需要的数据全部能从二级索引树上取到，回表操作就直接消失了，查询路径短得惊人。
5. 索引维护与常见问题排查实录
索引建好之后不是一劳永逸的。随着业务和数据量的变化，索引也会出现冗余、失效、膨胀等问题。这一节重点聊索引维护和工作中高频率遇到的几个问题。
5.1 如何利用 EXPLAIN 快速判断索引效果
EXPLAIN 是排查索引问题的第一工具，我把几个关键字段拆开讲：
- type：从好到差依次是 system、const、eq_ref、ref、range、index、ALL。看到 ALL 说明全表扫描，优先级最高的优化对象；看到 index 说明扫描了整棵索引树，也不理想。
- key：实际使用的索引名称，如果为 NULL，说明这条 SQL 根本没有可用的索引。
- rows：优化器估计的扫描行数，是个估算值，实际可能偏差很大，但越小通常越好。
- Extra 字段：这个信息量最大。出现 Using filesort 说明排序没走索引，出现 Using temporary 说明用了临时表，出现 Using index 说明走了覆盖索引，出现 Using where 表示在存储引擎层过滤后再在 Server 层过滤。
可以习惯性地给每个新增或变更的 SQL 都跑一次 EXPLAIN，把结果截图或记录在案，方便之后性能对比。
5.2 索引统计信息过旧导致的错误执行计划
优化器决定走不走索引，依赖的是索引统计信息。当表数据发生大量增删改后，统计信息可能过期，导致优化器做出了错误的选择，看到明明有索引却不走。
处理方法很简单，执行ANALYZE TABLE 表名重新统计索引信息。我遇到过几次线上慢查询，排查了半天不是 SQL 问题，而是一条ANALYZE TABLE就解决的。当然，频繁执行 ANALYZE 是没有必要的，通常大表在批量导入数据后，或者索引建立后，做一次即可。
5.3 冗余索引和重复索引：别让索引成为写入拖累
不少表会同时存在(a, b)联合索引和a单独索引。这种情况下，单独的a索引其实是冗余的，因为联合索引(a, b)已经足够覆盖所有WHERE a = ?的查询。每次插入、更新、删除，MySQL 都要维护每一条索引，索引越多，写入越慢。删掉冗余索引后，写入性能会有可感知的提升。
具体怎么找冗余索引？可以用SHOW INDEX FROM 表名把索引列导出来，然后逐个分析，看是否有索引是另一个索引的左前缀。有的话就考虑删除。这里给一个参考案例佐证：之前优化一张 1000 万行的用户表，清理了 3 个冗余索引后，批量导入耗时降低了 28%，查询性能没有下降。
5.4 主键设计对索引性能的影响
主键索引是聚簇索引，所有二级索引的叶子都存主键值，所以主键的大小直接影响每一条索引的存储大小。理论上主键越短越好，这也是推荐自增主键的原因。自增主键还有一个好处：数据天然按顺序插入，避免了随机的页分裂。用 UUID 做主键，插入时索引树会频繁调整节点，产生碎片，拖慢写入，同时二级索引的存储空间也会大不少。
这一点在 5.7 和 8.0 版本下的表现是很稳定的：UUID 主键表对比自增主键表，磁盘占用大约多 20% 到 30%，批量写入速度可能慢一倍以上。如果你在建新表，对主键的选择要非常谨慎。
6. 大表下索引优化的特殊性：MySQL 8.0 的值得关注的新特性
MySQL 8.0 里有些和索引相关的特性，新项目里如果已经在用 8.0，是可以直接吃到的红利。
6.1 不可见索引和降序索引
不可见索引是一个很实用的运维特性：如果某个索引你怀疑没用但不敢直接删，可以先把它设置为不可见，执行ALTER TABLE 表名 ALTER INDEX idx_name INVISIBLE，观察一段时间没有问题再删。这比直接 DROP 安全得多。
降序索引在 8.0 中被真正支持了。在这之前，ORDER BY col DESC通常在二级索引上要反向扫描，还有可能触发 filesort。8.0 中可以显式创建INDEX idx_created_desc (created_at DESC)，让排序彻底利用索引顺序，效率更好。
6.2 直方图统计信息对优化器的影响
8.0 还引入了直方图，在非索引列上也能生成统计信息。比如某个列没有索引但分布很不均匀，优化器以前只能瞎猜，现在你可以通过ANALYZE TABLE生成直方图，让优化器更准确地判断扫描行数。这个特性在复合查询条件较多、实际数据分布不均的场景下很有用。
当然，直方图不是万能的。它针对的是列值分布分析，不能替代索引，但它可以让优化器在“走索引”和“走全表”之间做出更聪明的选择。
7. 索引优化之余：SQL 书写习惯与开发流程建议
索引优化到一定程度后，你会发现真正的瓶颈往往来自业务侧。这里顺带分享几个干活经验，虽然不是纯粹的索引知识，但对 SQL 性能影响非常大。
7.1 不用 SELECT *，按需取列
SELECT * 意味着查询要拿到全部列，它会强制走聚簇索引回表拿整行数据，覆盖索引的价值直接归零。线上就见过这样的例子：明明只查两个字段，写成 SELECT * 之后，一个覆盖索引方案彻底失效，查询性能回到解放前。规范做法是：SQL 里只写需要的字段。
7.2 深分页问题的索引应对策略
LIMIT 100000, 20这种深分页在业务上很常见，问题是 MySQL 会把前 100000 行全部扫描出来再丢掉，越往后越慢。通过索引优化的思路有两个：一是延迟关联，先用覆盖索引拿到主键，再 join 回表取完整数据；二是记住上一页最后一条记录的主键或排序字段，用WHERE id > 上页最大id LIMIT 20代替 offset。我实际测过，在 1000 万行表上，后一种方案的分页响应时间基本稳定在 20 毫秒以内。
7.3 写完 SQL 先过一遍执行计划
我的习惯是，每条业务 SQL 写完后，本地先跑一次 EXPLAIN，重点看三件事：有没有 ALL 全表扫描，有没有 Using filesort，有没有 Using temporary。这三样东西任何一个出现，都要认真评估。如果 SQL 已经上线了才发现性能问题，排查成本往往十倍不止。
这些内容说到底，都指向同一个核心逻辑：先理解索引的底层原理，再基于原理做设计，最后通过真实数据反馈来持续校准方案。索引优化的实际工作，其实不是“加索引”这个动作，而是不断建立“SQL 写法、索引结构、数据分布、优化器行为”四者之间的匹配关系。这个过程需要一点耐心，但每次调优带来的性能提升，是真的会让人上瘾。
