MySQL索引全景长文：B+树原理与联合索引优化实践

1. 为什么 MySQL 索引值得写一篇「全景长文」

我做了十几年数据库相关工作，MySQL 索引是被问得最多的一个话题，没有之一。面试会问，线上排查会碰，优化慢查询要动，连写业务代码的同学也经常来咨询：这个字段要不要加索引？那个查询为什么没走索引？联合索引到底怎么建？

说实话，索引这东西，表面看就是一棵 B+ 树，背几个概念谁都会。但真正到了生产环境，你会发现索引设计得好不好，直接决定了数据库是扛得住还是崩得早。同样是千万级数据量的表，有人一条查询几十毫秒，有人直接扫全表把 CPU 打满，差别往往就在索引上。

这篇文章我会把 MySQL 索引从底层数据结构、单个索引到联合索引、失效场景、设计原则、慢查询分析、DBA 经验等几个维度完整串一遍。内容会比较多，建议先收藏再读，读完你基本可以应付日常开发、面试和线上排查中绝大多数索引相关问题。

为了让你有个整体框架，我先列一下全文的目录结构：

• 索引的本质：从数据结构和存储引擎说起
• InnoDB 的索引模型：聚簇索引与二级索引
• 单列索引与联合索引：怎么选字段、怎么排序
• 覆盖索引与回表：影响查询性能的关键机制
• 索引失效的典型场景与原因分析
• 索引设计的最佳实践与经验总结
• 慢查询分析：如何用 EXPLAIN 定位索引问题
• 高频面试题详解与错题复盘

你可能会问，网上索引文章那么多，为什么还要写一篇长文？我的看法是，大部分文章要么只讲概念不讲实践，要么只给结论不讲原理。这篇我会把原理、实操、案例放在一起讲，尽量让你看完知道「为什么」以及「怎么办」。

先说明一下，本文默认以 MySQL 8.0 的 InnoDB 存储引擎为例进行讲解，因为这是目前最主流的生产环境组合。如果你还在用 MyISAM，除了全文索引和表锁特性外，索引核心逻辑也是通用的，但建议尽早迁移到 InnoDB。

2. 索引的本质与底层数据结构

2.1 索引到底解决什么问题

索引存在的根本原因只有一个：减少磁盘 I/O。

数据库的数据最终存在磁盘上，磁盘随机读的速度比内存慢几个数量级。如果没有索引，你要查一条数据，只能从第一行开始逐行扫描，直到找到目标。这个过程叫全表扫描（full table scan），在数据量大的时候是灾难。

我举个例子你就明白了。假设一张用户表有 1000 万行数据，每行大约 1KB，整张表就是 10GB 左右。没有索引的情况下，执行SELECT * FROM user WHERE id = 1234567，MySQL 需要把整张表的数据页全部读出来逐行比对。这时候磁盘 I/O 就是瓶颈，查询耗时可能在几秒甚至十几秒。

有了索引，情况完全不同。索引相当于一本书的目录，你要找某一章的内容，先翻目录定位页码，再直接翻到那一页。同样一条查询，可能只需要读几个索引页和几个数据页，耗时从秒级降到毫秒级。

这就是索引的核心价值。理解了这个本质，后面所有的技术细节都有了落脚点。

2.2 为什么偏偏是 B+ 树而不是其他结构

聊索引必然绕不开数据结构。MySQL 索引主要用 B+ 树，但也支持哈希索引（Memory 引擎和 InnoDB 的自适应哈希索引）。为什么默认用 B+ 树？我对比几种常见结构来解释。

先看哈希表。哈希索引的查询速度确实是 O(1)，但它有两个致命缺陷：一是不支持范围查询，WHERE age > 18这种条件哈希表直接歇菜；二是不支持排序，ORDER BY也没办法走索引。而业务查询里范围查询和排序太常见了，所以哈希索引只能作为辅助。

再看二叉树和红黑树。它们的问题是树的高度会随着数据量增加而变高。1000 万条数据，二叉树的高度可能在 20 层以上，意味着每次查询要做 20 多次磁盘 I/O。虽然红黑树能保持平衡，但依然逃不过高度问题，每次查找都要从根节点一路走到叶子节点，每层都可能触发磁盘读取。

B 树是 B+ 树的变体前的形态，它每个节点既存索引键值又存数据。问题在于单个节点能存储的键值数量有限，树的高度依然偏高。而 B+ 树做了两个关键优化：非叶子节点只存索引键值，不存数据；叶子节点通过链表串联起来。这样每个节点能容纳更多键值，树的高度被压得非常低。

我算一笔账你就有感觉了。InnoDB 默认页大小是 16KB，假设一个索引键是 8 字节，加上指针等开销，一个节点大概能存 1000 个键值。1000 万条数据的 B+ 树，三层就搞定了。三层的含义是什么？从根节点到叶子节点最多只需要三次磁盘 I/O，这性能当然好。

B+ 树的叶子节点用双向链表串联，这是为了范围查询和排序。WHERE age BETWEEN 18 AND 30，先在索引里定位到 18 的位置，然后顺着链表往下扫就行了，不需要反复从根节点遍历。

2.3 索引的物理存储：页与区

理解了 B+ 树还不够，你还得知道索引在磁盘上是怎么组织的。InnoDB 存储数据的最小单位是页（page），默认 16KB。页里面存储一行行的数据或索引键值。多个连续的页组成区（extent），区的大小一般是 1MB，也就是 64 个连续的页。

B+ 树的一个节点对应一个或多个页。读取数据时，InnoDB 以页为单位从磁盘载入内存缓冲池（Buffer Pool），然后在内存中进行查找。这就是为什么你经常看到建议把 Buffer Pool 设置得大一些——索引和数据能更多地缓存在内存里，磁盘 I/O 自然减少。

索引页的结构包含几个部分：页头记录页的元信息、页尾记录校验和、页中间是实际的索引键值。每个叶子节点页内部，键值是按顺序排列的，这样在页内可以做二分查找，进一步提高效率。

这些物理细节你可能平时用不到，但排查性能问题时会派上用场。比如你发现某个索引的碎片化严重，实际占用的磁盘空间远大于数据本身，那就是页分裂和删除导致的。解决办法是定期执行ALTER TABLE xxx ENGINE=InnoDB来重建表，或者用OPTIMIZE TABLE整理碎片。

3. InnoDB 的索引模型与主键设计

3.1 聚簇索引：数据跟索引长在一起

InnoDB 的索引模型和 MyISAM 有个根本性的区别：数据本身存储在聚簇索引的叶子节点上，也就是说数据行的物理存放顺序就是主键索引的顺序。

每张 InnoDB 表都有一个聚簇索引。如果你定义了主键，主键索引就是聚簇索引。如果你没定义主键，InnoDB 会用第一个非空的唯一索引作为聚簇索引。如果连唯一索引都没有，InnoDB 会生成一个隐藏的 6 字节 RowID 作为聚簇索引。

聚簇索引的特点决定了两个重要事实：第一，按主键查询的速度极快，因为从 B+ 树根节点查到叶子节点，叶子节点里就是完整的行数据，一次性搞定；第二，数据插入时物理上会按主键值顺序排列，如果主键是自增的，插入永远是在末尾追加，效率很高。如果主键是 UUID 之类的随机值，插入时经常要挪动已有数据，页分裂频繁，性能会明显下降。

我在实际项目中遇到过主键设计不当引发的性能故障。有个日志表主键用了 UUID 字符串，数据量加到几百万后插入越来越慢，磁盘 I/O 居高不下。后来换成了自增 BIGINT 主键，插入性能立刻恢复了。这个教训说明，主键设计不光是规范问题，直接关系到数据库的写入性能。

3.2 二级索引与回表查询

除了聚簇索引，其他索引都叫二级索引（也叫辅助索引）。二级索引的叶子节点存的内容是索引键值 + 主键值，而不是完整的数据行。

这就引出了回表（bookmark lookup）的概念。比如你有一张表，主键是 id，还有一个普通索引idx_age建立在 age 字段上。执行SELECT * FROM user WHERE age = 25，MySQL 会先去idx_age这棵 B+ 树里定位 age=25 的记录，拿到主键 id，然后再去聚簇索引里按 id 查一遍，才能取到完整的数据行。

也就是说，走二级索引查询，至少需要查两棵 B+ 树。这个额外的第二次查找就是回表。表数据量越大、二级索引的键值区分度越低，回表带来的性能损耗就越明显。

这里有一个优化思路：尽量避免回表。怎么做？让查询所需的字段都包含在二级索引里，这样查询时只需要扫描二级索引就够了，不需要回表。这种索引叫作覆盖索引。我后面单独开一节详细讲，这里先记住这个概念。

3.3 主键索引与唯一索引的区别

这个问题被问的次数非常多。主键索引和唯一索引都是唯一约束，但有几个关键区别：

• 一张表只能有一个主键索引，但可以有多个唯一索引。
• 主键索引的列值不允许为 NULL，唯一索引的列值允许有一个或多个 NULL（MySQL 的 InnoDB 引擎下，唯一索引允许存在多个 NULL 值）。
• 主键索引是聚簇索引，决定了数据的物理存储顺序；唯一索引是二级索引，不影响数据存储顺序。

实际业务中，唯一索引常用于业务上的唯一性约束，比如用户的手机号、订单编号等。但要注意，给这个字段加唯一索引既能保证数据不乱，又能加速基于该字段的查询，是一举两得的做法。

不过有个坑要提醒你：唯一索引的写入性能比普通索引差一些。因为每次写入都要做唯一性检查，多了一次索引查找。对于高频写入的表，如果有不需要的唯一约束，可以考虑去掉，改为业务层校验，换写入性能。

4. 单列索引与联合索引：从 SQL 出发的建索引思路

4.1 单列索引怎么建最合理

单列索引的建立相对简单，但要考虑几个因素：字段的区分度、查询频率、更新频率。

区分度是指字段值的多样性程度。计算公式是COUNT(DISTINCT column) / COUNT(*)。区分度越高，索引的过滤效果越好。性别字段区分度太低，建索引基本没意义，因为过滤完还是剩半张表的数据，优化器大概率会选择全表扫描。而手机号、邮箱这类字段区分度接近 1，特别适合建索引。

查询频率很好理解，你经常在 WHERE 条件里用的字段优先建索引。反过来，那些只在 SELECT 里出现、不在 WHERE 里出现的字段，建索引意义不大，因为索引主要是加速行定位，不是加速列读取（那是覆盖索引的事，后面讲）。

更新频率要重点考虑。索引本身是额外的存储结构，每次 INSERT、UPDATE、DELETE 都要同步维护索引。索引越多，写放大越严重。如果一个字段经常被更新，它的索引也会跟着频繁变动，导致页分裂和碎片。所以高频更新字段建索引要慎重，最好评估一下查询收益是否大于写损耗。

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

联合索引（复合索引）是指基于多个字段建立的索引。比如idx_user_phone_name (phone, name)就是一个联合索引，涉及 phone 和 name 两个字段。

联合索引的核心机制是最左前缀原则。这个原则的含义是：联合索引(a, b, c)能加速包含 a、包含 a 和 b、包含 a 和 b 和 c 的查询条件，但不能加速只包含 b 或只包含 c 的查询条件。也就是说，索引只能从最左边的字段开始匹配，一旦跳过某个字段，后面的字段就用不上索引了。

举个具体例子。假设表里有联合索引(a, b, c)，下面这些查询都能用到索引：

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 AND c = 3（能用到 a，但 c 用不上索引）

下面这些查询用不上索引（或只能部分使用）：

• WHERE b = 2
• WHERE c = 3
• WHERE b = 2 AND c = 3

用生活类比解释就是，联合索引就像一本先按姓氏、再按名字排列的通讯录。你能直接找到「姓张的」，也能找到「姓张且叫张三的」。但你没法直接按「名字叫三」去找人，因为通讯录根本不按名字排。

有人会问，把 WHERE 条件顺序写成c = 3 AND a = 1能走索引吗？答案是能。MySQL 的优化器会做条件重排，把能利用索引的条件放在前面。所以 SQL 写的顺序不影响索引使用，最左前缀原则看的是条件本身，不是 SQL 书写顺序。这条我在实际辅导新人时反复强调过，很多人在这里有误解。

4.3 高频问题：WHERE a AND b 应该怎么建索引

标题里提到了一个典型问题：WHERE a AND b应该怎么建索引。这里给你一套完整的方法。

首先明确一个事实：MySQL 8.0 之前的索引合并（Index Merge）能力有限，对于WHERE a = ? AND b = ?，通常只会选择一个索引，另一个条件靠回表后过滤。所以给 a 和 b 分别建单列索引，在大多数情况下不如建一个联合索引。

其次要判断哪个字段放在联合索引前面。原则是：区分度较高的字段放前面，或者根据实际查询频率来安排。如果 a 字段的值范围非常小（比如状态字段，只有 0 和 1），b 字段的区分度高（比如手机号），那么建议建(b, a)而不是(a, b)，因为把高区分度字段放前面，能更快地缩小扫描范围。

还有一种常见场景：WHERE a AND b ORDER BY c。这种情况下，联合索引最好设计为(a, b, c)，让排序也走索引，避免 filesort。filesort 需要额外的排序操作，数据量大时性能损失很明显。

我见过一个真实案例。有个订单表，查询条件是WHERE user_id = ? AND status = ? ORDER BY create_time DESC。最初索引只建了(user_id, status)，每次查询都有 filesort，耗时 200 毫秒以上。后来改成(user_id, status, create_time)，查询时间直接降到 20 毫秒以内。这就是联合索引设计对排序的优化，性价比极高。

4.4 等值查询与范围查询混搭时怎么排

先看一个经验法则：联合索引(a, b)，如果 a 是等值查询a = 1，b 是范围查询b > 100，那么索引排序是(a, b)没问题，a 精确命中后，b 在索引内按顺序扫。但如果 a 是范围查询，b 是等值查询，建议排序为(b, a)，让等值条件在前。

为什么？因为联合索引的本质是先按第一个字段排序，再按第二个字段排序。如果第一个字段是范围，那么在范围确定的这批数据内部，第二个字段并不是全局有序的，可能无法继续利用第二个字段索引。

举个例子。假设索引是(a, b)，查询是WHERE a > 100 AND b = 5。MySQL 会先用索引定位 a > 100 的所有记录，然后在记录的二级索引叶子节点上过滤 b = 5。b 的等值条件无法充分使用索引的连续扫描特性，查询效率不如(b, a)这种建法。

不过这里有一个细微点：等值 + 范围的组合（如a = 1 AND b > 100），索引(a, b)是完整生效的，因为 a 的等值匹配已经把范围缩小到很小一块，b 的顺序扫描只在 a=1 的子集内进行，效率非常高。所以混合排续的优先级是：等值条件放前面，范围条件放后面。

5. 覆盖索引与回表优化

5.1 什么是覆盖索引

覆盖索引（Covering Index）是指查询的字段全部包含在某个索引中，以至于查询可以不回表就能拿到所有数据。严格来说不是一种独立的索引类型，而是一种使用索引的方式。

举例子最直观。还是那张 user 表，有主键索引id和二级索引idx_age (age)。假设你要查：

SELECT id, age FROM user WHERE age = 25

age 字段在idx_age里，id 是主键也在二级索引叶子节点上，这两个字段都能直接从idx_age这棵 B+ 树里拿到，不需要回表查聚簇索引。这就是覆盖索引。

但如果是：

SELECT id, age, name FROM user WHERE age = 25

name字段不在idx_age里，MySQL 只能回表取 name，无法完全覆盖。

那么覆盖索引的价值在哪里？最直接的价值是减少磁盘 I/O。二级索引的叶子节点只存索引键值和主键值，比聚簇索引的完整行小得多。同样大小的数据页，二级索引能装更多条目，扫描时读取的页更少。另一方面，回表操作意味着随机 I/O 和额外树查找，避免回表就是性能提升。

5.2 怎么设计覆盖索引

设计覆盖索引的核心是「开小灶」思路：把查询里高频出现的字段，也加入索引中。

举个例子。假设你有一个订单查询接口，经常执行：

SELECT order_id, user_id, status FROM orders WHERE user_id = ? AND status = ?

原始索引只建了(user_id, status)，查询时需要回表取 order_id。你可以在已有索引基础上追加 order_id，变成(user_id, status, order_id)。这样上面的查询全部字段都能从索引里取到，回表没了，查询速度自然上来。

但有代价：索引变宽意味着更多磁盘空间、更慢写入。索引不是越宽越好，字段多了叶子节点能装的条目变少，扫描效率也可能下降，而且插入更新时索引维护开销变大。所以覆盖索引要服务于具体的高频查询场景，不能盲目把所有字段都塞进索引。

我的经验是：优先优化查询次数最多、单次耗时最长的 Top 3 SQL。给这些 SQL 设计合适的覆盖索引，收益最大。冷门 SQL 就不值得为它们扩宽索引了。

5.3 回表与覆盖索引的取舍实战

我在一个交易系统里做过一次优化，效果非常典型。一张流水表有近亿行，业务侧有个统计页面，需要按用户和时间范围查流水编号和金额：

SELECT biz_no, amount FROM trade_flow WHERE user_id = ? AND create_time BETWEEN ? AND ?

最初的索引是(user_id, create_time)，虽然能定位到目标记录块，但取 amount 时必须回表。由于用户单次查询命中的记录可能有几百到几千条，每条都回表，耗时稳定在 800 毫秒以上，页面经常超时。

我做了两个改动：第一，把索引改成(user_id, create_time, biz_no, amount)，四个字段全部覆盖查询所需；第二，SQL 里把所有字段明确列出，不写*。改完后这条查询稳定在 50 毫秒以内，页面秒开。

你可能注意到，我把 amount 也加进了索引。可能有人觉得多余，因为 amount 是数值，存储开销不大，但收益很明显——除查询列外，没必要不放。实践下来，多花一点写索引的时间换查询性能的大幅提升，非常划算。

6. 索引失效的典型场景与原因分析

6.1 失效场景清单：别再背锅了

索引失效是 MySQL 使用中最高频的坑。下面按场景整理一份我实际遇到过的典型清单，每条都配上原因，方便你排查。

• 对索引列使用函数：WHERE YEAR(create_time) = 2024。函数处理会让索引列变成计算后的结果，B+ 树里存的是原始值，无法直接匹配。
• 对索引列进行隐式类型转换：WHERE phone = 13800138000，phone 是 VARCHAR 类型，但查询条件给了整数。MySQL 会先把列转成数字做比较，导致索引失效。解决办法是查询条件写成字符串'13800138000'。
• 使用前导模糊查询：WHERE name LIKE '%张%'。B+ 树按最左前缀匹配，前面不确定就没办法用索引。但'张%'这种后置模糊是可以走索引的。
• OR 条件中存在非索引列：WHERE id = 1 OR name = '张三'，如果 name 没有索引，OR 条件无法同时走两个索引，可能退化为全表扫描。
• 联合索引不满足最左前缀：前面已经详细说了，这里再强调一次。
• 索引列参与运算：WHERE num + 1 > 100。运算改变了字段值，索引无法匹配。应该改写为WHERE num > 99。
• NOT IN、NOT LIKE 等否定条件：MySQL 优化器通常选择全表扫描，因为否定条件很难利用索引有序性。
• 优化器认为全表扫描更快：即使满足索引条件，如果索引区分度太低或返回行数过大，优化器也会放弃索引。这是最常见的隐形坑，是优化器的正常选择，不算业务 bug。

我强烈建议你把上面这份清单保存下来，每次线上 SQL 慢查询先对照一遍。多数情况下，索引失效问题都能迅速定位。

6.2 深入解析：为什么函数会让索引失效

很多人不理解为什么WHERE YEAR(create_time) = 2024用不了索引，这里从 B+ 树的底层逻辑解释。

B+ 树索引存储的是列的原始值，并且按照列值的大小排好序。查询时走索引的前提是，你能在 B+ 树中按照原值进行二分定位。一旦对列做了函数计算，比如YEAR(create_time)，MySQL 必须对每一行的 create_time 都计算一次 YEAR 函数，才能判断是否为 2024。这个过程相当于在扫描过程中实时计算，索引原有的有序性完全失效，优化器只能选择全表扫描。

有人问，MySQL 为什么不在建立索引时就把 YEAR(create_time) 的结果存进去？这正是函数索引（MySQL 8.0 支持的功能索引）做的事。如果确实经常按年份过滤，可以创建一个函数索引：ALTER TABLE t ADD INDEX idx_year ((YEAR(create_time)))。这样 YEAR(create_time) 的计算结果会真正出现在索引中，查询就能走索引了。

我个人对这个特性的态度是：优先改写 SQL，让索引列保持原始形式。比如把YEAR(create_time) = 2024改成create_time >= '2024-01-01' AND create_time < '2025-01-01'，这样不仅能用索引，而且范围更清晰，语义也更准确。

6.3 隐式类型转换的实战教训

隐式类型转换是工程上最容易犯的错。我之前接手过一个用户表，phone 字段是 VARCHAR(20)。排查慢查询时发现，WHERE phone = 13800138000这条查询竟然走了全表扫描。

原因就是 MySQL 会把字符串列和数字比较时，优先将字符串转换成数字。转换完成后，索引列参与了一个隐式函数运算（隐式 CAST），索引失效。

解决办法有两种：一是在应用层保证传入字符串'13800138000'；二是把列类型改成 BIGINT，彻底统一。我推荐第二种，因为手机号本质是数值，用 VARCHAR 存储虽然方便，但查询时必须严格保证带引号。数据库的数据类型就应该跟现实语义匹配，减少后续开发犯错的可能。

类似的问题还有：日期字段用字符串存储，比较时转义出错；德文等特殊字符集导致的排序规则问题等。总之，数据类型是数据库设计的根基，别埋雷。

7. MySQL 索引设计的最佳实践与经验总结

7.1 建索引前的需求分析和字段评估

建索引不是想到就加。我在设计索引前会先做一轮需求分析，核心是三件事：梳理高频 SQL、分析字段区分度、评估写入压力。

先说高频 SQL。从业务日志或慢查询日志里找出 Top 10 的 SELECT 语句，把它们的 WHERE 条件、ORDER BY、GROUP BY 字段列出来。联合索引的字段选择和顺序，应该优先满足这些高频 SQL。低频率查询的索引需求排在后面。

然后是字段区分度。区分度不高的字段不要单独建索引，即使建了大概率也会被优化器放弃。判断方式很简单：跑一条SELECT COUNT(DISTINCT col) FROM table看结果，跟总行数对比。如果这个比例低于 20%，索引性价比就很低了。

最后是写入评估。如果表是高频写入型（比如日志、流水），索引数量要严格控制。我的经验是：千万级数据量的写入型表，索引数控制在 4~6 个以内；查询型表（如配置表、基础资料表）可以多建一些，但也不超过 8~10 个。核心原则就是「够用即可」。

7.2 不要在每个字段上都建索引

我见过不少人，为了让查询「快一点」，给表里一半字段都建了索引。实际上这是一个严重的反模式。

原因很简单：每个索引都是独立的 B+ 树结构，占磁盘空间；每次写操作，插入、更新、删除，都要维护所有索引；查询时优化器还要在各种可能的索引之间做代价估算，索引太多反而增加优化器的负担。

我再举一个具体的例子。一张 1000 万行的表，加一个二级索引大概需要额外几百 MB 甚至上 GB 的磁盘空间，取决于索引字段长度。如果加十个索引，空间开销直接翻好几倍。而这些索引里，真正被高频使用的可能就那么两三个。

对已经建多的索引，我的建议是直接删掉使用率低的。怎么判断使用率？MySQL 的performance_schema.table_io_waits_summary_by_index_usage表记录了所有索引的使用情况，可以查出每个索引被访问的次数。如果某索引很长时间没被使用过，就放心删除。

7.3 前缀索引与字符串索引优化

对于 TEXT、VARCHAR 等长字符串字段，全文建索引会导致索引体积膨胀、查询效率下降。此时可以考虑前缀索引，只对字段的前 N 个字符建索引。

比如商品表有一个product_code字段，值是类似ABC20250101001的字符串，长度 15 个字符。你可以建立前缀索引：

ALTER TABLE product ADD INDEX idx_product_code (product_code(8));

只取前 8 个字符作为索引键值。这样索引体积大幅减小，查询效率提升。但要注意前缀长度的选择：长度太短区分度不够，过滤效果差；太长又失去前缀索引的意义。做法是先统计不同长度的区分度，选一个区分度接近全列且字符数最小的值。比如：

SELECT COUNT(DISTINCT product_code(6)) / COUNT(*) FROM product; SELECT COUNT(DISTINCT product_code(8)) / COUNT(*) FROM product; SELECT COUNT(DISTINCT product_code(10)) / COUNT(*) FROM product;

哪个长度能让比例接近 1，就用哪个。

7.4 索引统计信息与更新策略

MySQL 优化器决定是否走索引，依赖的是索引的统计信息（基数和选择性）。统计信息不是实时更新的，它会随数据的增删改产生滞后。

在 MySQL 8.0 中，InnoDB 提供了持久化统计信息，统计信息存在 mysql.innodb_index_stats 表里。它会在表数据变化到一定量时自动重新计算，但也会在ANALYZE TABLE时手动刷新。如果发现索引明明建对了，但查询计划却显示全表扫描，很可能就是统计信息过期误导了优化器。

我在一个系统里遇到过：一张表的数据从 100 万涨到了 800 万，统计信息没刷新，优化器基于旧数据判断「用这个索引会扫太多行，不如全表扫描」，结果一直没走索引。手动执行ANALYZE TABLE之后，统计信息更新，查询立刻走了索引，单条 SQL 耗时从 3 秒降到了 50 毫秒。

经验是：数据量发生过大规模变化（比如月结、批量导入后），及时执行ANALYZE TABLE；如果条件允许，把innodb_stats_auto_recalc打开，让系统自动维护统计信息。

8. 慢查询分析与 EXPLAIN 实操

8.1 如何定位一个慢 SQL

生产环境里定位慢查询，有几个固定入口。

第一个是慢查询日志。MySQL 提供了slow_query_log参数，可以记录执行时间超过阈值的 SQL。查看当前是否开启：

SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time';

如果没开启，可以临时开启并设置阈值：

SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;

注意long_query_time的单位是秒，设置为 1 表示超过 1 秒的记录。线上环境建议设为 1 或 2，太低的阈值会产生大量日志。

第二个是 performance_schema。它记录更细粒度的 SQL 执行统计，比如语句耗时、锁等待时间、扫描行数等。查询 Top 慢 SQL 可以用这张视图：

SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

第三个是业务侧监控。干脆把每个接口的数据库耗时打点上报，超过阈值直接告警。生产环境里真正跑到秒级的 SQL 往往会被监控系统先发现，再配合慢查询日志回放定位。

8.2 EXPLAIN 输出怎么读

拿到一条慢 SQL 后，第一件事就是用 EXPLAIN 看执行计划。这是 MySQL 优化器给你的一张「问卷答案」，能直接告诉你它是怎么执行这条 SQL 的。

一个标准的 EXPLAIN 输出长这样：

EXPLAIN SELECT id, name FROM user WHERE age = 25 AND status = 1;

输出字段里，我最关注这几个：

• type：访问类型，const、eq_ref、ref、range、index、ALL 是从优到劣的排序。const/eq_ref 说明是精准命中；ref 和 range 说明走了索引但可能有多个匹配；ALL 是全表扫描，必须警惕。
• key：实际用到的索引。如果为 NULL，说明没走索引。
• rows：MySQL 预估需要扫描的行数。这个数字越小越好。
• Extra：附加信息，重点注意 Using filesort（需要额外排序）和 Using temporary（需要临时表），这两个都是性能杀手。

下面给一个典型例子。假设你有一张 user 表，建了索引idx_age(age)。执行：

EXPLAIN SELECT * FROM user WHERE age = 25;

type 会是 ref，key 是 idx_age，rows 预估为某个数量级，Extra 为空——这是健康的执行计划。

再假设你把条件改成：

EXPLAIN SELECT * FROM user WHERE age > 25;

如果 age=25 的数据占比过高，优化器可能改为全表扫描，type 变成 ALL。这不是索引失效，而是优化器的理性选择。判断是否合理，要看返回的数据比例。

8.3 实际案例：一条慢 SQL 的完整排查过程

我尽可能还原一个完整的排查过程，让你对方法论有手感。

背景：一张 5000 万行的订单表，业务反馈某个列表接口很慢。抓到的 SQL 是：

SELECT order_id, amount, status FROM orders WHERE buyer_id = 12345 AND status = 1 ORDER BY create_time DESC LIMIT 20;

EXPLAIN 第一次执行的结果：

• type: ref
• key: idx_buyer (单列索引，只有 buyer_id)
• rows: 50000
• Extra: Using filesort

问题很清晰：走了 buyer_id 索引，但 status 过滤和排序都需要额外处理。50 万行数据里按创建时间排序，Filesort 很慢。

优化方案：

1. 建立联合索引(buyer_id, status, create_time)，把等值条件 status 和排序字段 create_time 都吃进索引。
2. 覆盖查询所需的 amount、order_id，追加字段变成(buyer_id, status, create_time, amount, order_id)。

执行ALTER TABLE后重新 EXPLAIN，结果变为：

• type: ref
• key: idx_buyer_status_time
• rows: 800
• Extra: Using index

filesort 消失，rows 从 50000 降到 800，Extra 显示 Using index（覆盖索引）。实际接口耗时从 1.8 秒降到 100 毫秒以内。

这个案例最有价值的一点是：执行计划不是玄学，每个字段都有自己的含义。你能读懂 EXPLAIN，就等于看到了优化器的完整决策过程。

9. 高频面试题详解与错题复盘

9.1 为什么 InnoDB 必须有主键？

这个问题考察的是对聚簇索引模型的理解。InnoDB 要求表必须有聚簇索引，而聚簇索引只能有一个。如果没有显式主键，InnoDB 会选第一个非空唯一索引；如果连这都没有，就生成隐藏的 6 字节 RowID 作为主键。

但隐藏主键有两个问题：一是业务查询无法使用 RowID，所有查询都必须全表扫描；二是主键值完全随机，数据插入时很容易触发页分裂。所以不管业务上有没有自然主键，我都建议人为加一个自增 BIGINT 主键，这是最稳妥的做法。

9.2 联合索引的最左前缀原则具体是什么意思？

这个问题需要你能画出一个二维排序的 B+ 树结构。联合索引(a, b)的排序逻辑是：先按 a 排序，a 相同再按 b 排序。因此查询条件中 a 的等值或范围匹配，能利用索引的排序结构；一旦跳过 a 只用 b，索引的有序性就失效了。

面试中最容易掉进去的坑是：WHERE a = 1 AND b > 10能不能用联合索引(a, b)？答案是可以，a 用于等值定位，b 在 a 已确定的子集内做范围扫描，是典型的「等值 + 范围」，都能用上索引。但WHERE a > 1 AND b = 10就不能完全使用了，a 的范围让 b 的值不再全局有序。

9.3 MySQL 在哪些场景下会选择不用索引？

被问到这个问题，别只背「函数、隐式转换、LIKE %xx」这些标准答案。要体现出你对优化器的理解。

核心原因是：索引扫描也有成本，优化器会估算全表扫描和索引扫描的代价，选择代价更低的方案。以下几个场景都属于这一类：

• 返回行数占表总行数比例过高（通常超过 20%~30%），优化器选择全表扫描。
• 索引的区分度太低，比如性别字段只有两种值，扫了索引还要大量回表，不如全扫。
• 索引统计信息过期，优化器误判了走索引的代价。
• WHERE 条件为 NULL、NOT NULL、<>时，优化器可能认为全扫更高效。

面试中能把「基于代价」这个底层逻辑讲清楚，表现会比背清单好很多。

9.4 索引下推（Index Condition Pushdown）是什么？

索引下推（ICP）是 MySQL 5.6 引入的重要优化。它允许存储引擎在索引遍历过程中，直接过滤掉不满足 WHERE 条件的记录，减少回表次数。

举个例子。表有联合索引(age, name)，查询：

SELECT * FROM user WHERE age = 25 AND name LIKE '张%'

没有 ICP 时，存储引擎把 age=25 的所有记录全部回表，再由 Server 层过滤 name。有了 ICP，存储引擎在扫描索引时就把 name LIKE '张%' 的条件先过滤掉，回表的记录大幅减少。

实际生产环境中，ICP 对联合索引范围查询的优化效果非常明显。如果一个查询的 WHERE 条件里既有可走索引的前缀字段，又有需要过滤的后缀字段，ICP 大概率已经帮你省了一大波回表开销。

9.5 索引条件下推、回表、覆盖索引这三者的关系

这三者经常被放到一起考。我用一句话把它们串起来：

• 查询走二级索引时，索引里没有的字段需要回表取。
• 覆盖索引让查询所需字段全部在索引里，完全避免回表。
• 索引下推是在索引扫描阶段提前过滤记录，减少回表次数，但不完全消除回表。

真实面试时，我会建议把每个环节对应的 Extra 输出也说出来。覆盖索引对应Using index，filesort 对应Using filesort，ICP 对应Using index condition。能把这些执行计划字段和原理对应起来的人，通常对 MySQL 索引的理解已经很扎实了。

10. 写在最后的个人经验

文章写到这里，我想分享一个坚持了很多年的工作习惯：每次建索引前，先写清楚这条索引要服务哪条 SQL、能过滤多少数据、要不要覆盖额外字段、对写入性能有没有影响。想不清楚就不建，或者先用临时索引验证再转正。这套流程帮我避开了很多同学掉过的坑。

另外一个特别想提醒的点是：索引不是越多越好，也不是越复杂越好。它更像一门平衡的艺术，在查询速度、写入速度、存储成本之间找平衡点。真正的高手不是能把索引建得多花哨，而是能在业务需求和数据库性能之间做出最合理的取舍。

MySQL 索引这个主题，一篇文章不可能穷尽，但如果你能把我上面讲的这些内容理解透，已经能覆盖日常开发、面试和线上排查的绝大部分场景。后续我会再写一篇关于索引碎片整理与统计信息维护的专题文章，把运维视角的内容补齐。