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 1234567MySQL 需要把整张表的数据页全部读出来逐行比对。这时候磁盘 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 ENGINEInnoDB来重建表或者用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 25MySQL 会先去idx_age这棵 B 树里定位 age25 的记录拿到主键 id然后再去聚簇索引里按 id 查一遍才能取到完整的数据行。也就是说走二级索引查询至少需要查两棵 B 树。这个额外的第二次查找就是回表。表数据量越大、二级索引的键值区分度越低回表带来的性能损耗就越明显。这里有一个优化思路尽量避免回表。怎么做让查询所需的字段都包含在二级索引里这样查询时只需要扫描二级索引就够了不需要回表。这种索引叫作覆盖索引。我后面单独开一节详细讲这里先记住这个概念。3.3 主键索引与唯一索引的区别这个问题被问的次数非常多。主键索引和唯一索引都是唯一约束但有几个关键区别一张表只能有一个主键索引但可以有多个唯一索引。主键索引的列值不允许为 NULL唯一索引的列值允许有一个或多个 NULLMySQL 的 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 1WHERE a 1 AND b 2WHERE a 1 AND b 2 AND c 3WHERE a 1 AND c 3能用到 a但 c 用不上索引下面这些查询用不上索引或只能部分使用WHERE b 2WHERE c 3WHERE 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 和 1b 字段的区分度高比如手机号那么建议建(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 1b 是范围查询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 的顺序扫描只在 a1 的子集内进行效率非常高。所以混合排续的优先级是等值条件放前面范围条件放后面。5. 覆盖索引与回表优化5.1 什么是覆盖索引覆盖索引Covering Index是指查询的字段全部包含在某个索引中以至于查询可以不回表就能拿到所有数据。严格来说不是一种独立的索引类型而是一种使用索引的方式。举例子最直观。还是那张 user 表有主键索引id和二级索引idx_age (age)。假设你要查SELECT id, age FROM user WHERE age 25age 字段在idx_age里id 是主键也在二级索引叶子节点上这两个字段都能直接从idx_age这棵 B 树里拿到不需要回表查聚簇索引。这就是覆盖索引。但如果是SELECT id, age, name FROM user WHERE age 25name字段不在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 13800138000phone 是 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说明没走索引。rowsMySQL 预估需要扫描的行数。这个数字越小越好。Extra附加信息重点注意 Using filesort需要额外排序和 Using temporary需要临时表这两个都是性能杀手。下面给一个典型例子。假设你有一张 user 表建了索引idx_age(age)。执行EXPLAIN SELECT * FROM user WHERE age 25;type 会是 refkey 是 idx_agerows 预估为某个数量级Extra 为空——这是健康的执行计划。再假设你把条件改成EXPLAIN SELECT * FROM user WHERE age 25;如果 age25 的数据占比过高优化器可能改为全表扫描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: refkey: idx_buyer (单列索引只有 buyer_id)rows: 50000Extra: Using filesort问题很清晰走了 buyer_id 索引但 status 过滤和排序都需要额外处理。50 万行数据里按创建时间排序Filesort 很慢。优化方案建立联合索引(buyer_id, status, create_time)把等值条件 status 和排序字段 create_time 都吃进索引。覆盖查询所需的 amount、order_id追加字段变成(buyer_id, status, create_time, amount, order_id)。执行ALTER TABLE后重新 EXPLAIN结果变为type: refkey: idx_buyer_status_timerows: 800Extra: Using indexfilesort 消失rows 从 50000 降到 800Extra 显示 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 时存储引擎把 age25 的所有记录全部回表再由 Server 层过滤 name。有了 ICP存储引擎在扫描索引时就把 name LIKE 张% 的条件先过滤掉回表的记录大幅减少。实际生产环境中ICP 对联合索引范围查询的优化效果非常明显。如果一个查询的 WHERE 条件里既有可走索引的前缀字段又有需要过滤的后缀字段ICP 大概率已经帮你省了一大波回表开销。9.5 索引条件下推、回表、覆盖索引这三者的关系这三者经常被放到一起考。我用一句话把它们串起来查询走二级索引时索引里没有的字段需要回表取。覆盖索引让查询所需字段全部在索引里完全避免回表。索引下推是在索引扫描阶段提前过滤记录减少回表次数但不完全消除回表。真实面试时我会建议把每个环节对应的 Extra 输出也说出来。覆盖索引对应Using indexfilesort 对应Using filesortICP 对应Using index condition。能把这些执行计划字段和原理对应起来的人通常对 MySQL 索引的理解已经很扎实了。10. 写在最后的个人经验文章写到这里我想分享一个坚持了很多年的工作习惯每次建索引前先写清楚这条索引要服务哪条 SQL、能过滤多少数据、要不要覆盖额外字段、对写入性能有没有影响。想不清楚就不建或者先用临时索引验证再转正。这套流程帮我避开了很多同学掉过的坑。另外一个特别想提醒的点是索引不是越多越好也不是越复杂越好。它更像一门平衡的艺术在查询速度、写入速度、存储成本之间找平衡点。真正的高手不是能把索引建得多花哨而是能在业务需求和数据库性能之间做出最合理的取舍。MySQL 索引这个主题一篇文章不可能穷尽但如果你能把我上面讲的这些内容理解透已经能覆盖日常开发、面试和线上排查的绝大部分场景。后续我会再写一篇关于索引碎片整理与统计信息维护的专题文章把运维视角的内容补齐。