记得上一篇 MySQL 实战文章发出来后有不少同学私信问我索引到底怎么建才合理为什么 SQL 明明加了索引还是慢面试被问到 BTree 和回表的时候总是说得不够深入怎么办其实这几个问题可以合并成一个核心模块来学——MySQL 索引调优 索引相关面试题。这也是后端开发、DBA、甚至数据岗位面试中几乎必考的方向。我结合自己刷课、做项目、梳理面经的实战经历把 MySQL 从索引基础原理到 EXPLAIN 调优、索引失效场景、慢 SQL 排查再到高频面试题完整整理成一套学习笔记。无论你是刚准备校招还是在职想系统补一补数据库短板这篇文章都值得收藏慢慢看。1. 为什么 MySQL 索引是面试和调优的核心1.1 索引的本质是什么索引Index是帮助 MySQL 高效获取数据的数据结构你也可以把它理解为书的目录。没有目录你要找某一章就得从头翻到尾有了目录你直接翻到对应页码就行。在没有索引的情况下MySQL 执行查询会走全表扫描Full Table Scan也就是一行一行地比较时间复杂度 O(N)。数据量小的时候无所谓但一旦表里有百万、千万级数据全表扫描就会慢到无法接受。而索引的底层实现在 InnoDB 存储引擎中默认是BTree。BTree 是多路平衡查找树它的核心特点包括非叶子节点只存储索引键值不存储数据所以每个节点能容纳更多键值树的高度更低。所有数据都存储在叶子节点并且叶子节点之间通过双向指针连接非常适合范围查询。查询时间复杂度稳定在 O(log N)千万级数据量下也只需要三四次磁盘 IO。这也是面试中“为什么 MySQL 选择 BTree”这个问题的核心答案后面面试题部分会再展开。1.2 InnoDB 中的索引分类InnoDB 是 MySQL 默认的存储引擎它里面的索引可以分成三类聚簇索引Clustered Index表的主键会自动生成聚簇索引。聚簇索引的叶子节点直接存放整行数据也就是说数据行物理上是按主键顺序排列的。二级索引Secondary Index非主键索引统称二级索引也叫辅助索引。二级索引的叶子节点存放的是索引列的值 主键值。联合索引多个字段组成的索引比如(a, b, c)查询时遵循最左前缀原则。这里有一个关键概念叫回表Bookmark Lookup当你通过二级索引查询数据时先通过二级索引找到主键再根据主键去聚簇索引中查找完整行记录这个过程就是回表。回表会增加额外 IO因此我们要尽量避免不必要的回表操作。后面讲覆盖索引时会给出具体优化方法。1.3 索引调优的学习路径索引调优不是背几个口诀就能掌握的我总结的学习路径是这样先理解 BTree 结构和索引存储方式。掌握 EXPLAIN 命令学会分析 SQL 的执行计划。熟悉索引失效的常见场景写 SQL 时心里有数。学会用慢查询日志和 profiling 定位真实慢 SQL。最后在设计表结构时从源头避免索引滥用或缺失。下面我从一条生产环境的慢 SQL 出发完整走一遍排查和优化过程。2. 环境准备与版本说明在开始实操之前先说明本文使用的环境。版本不需要完全一致配置思路是通用的。2.1 本地环境项目版本/说明操作系统CentOS 7.9 / macOS 均可MySQL8.0.x建议使用 8.0 以上版本客户端工具MySQL Workbench 或 Navicat数据库字符集utf8mb4隔离级别默认 Repeatable Read如果本地还没有安装 MySQL可以参考 MySQL 官方文档或网上保姆级安装教程安装完成后用mysql -uroot -p登录。2.2 准备测试表为了复现索引调优场景我们需要建一张数据量较大的测试表。下面的 SQL 会创建一个用户订单表包含用户 ID、订单金额、订单状态、创建时间等常用字段。-- 建表语句 CREATE TABLE t_order ( id bigint NOT NULL AUTO_INCREMENT COMMENT 主键, user_id bigint NOT NULL COMMENT 用户ID, order_no varchar(64) NOT NULL COMMENT 订单编号, amount decimal(10,2) NOT NULL COMMENT 订单金额, status tinyint NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已取消, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单测试表;为了模拟真实场景我建议插入 50 万到 100 万条测试数据。你可以用存储过程批量插入注意不要在主键上插入重复值否则会报主键冲突错误。-- 批量插入测试数据示例插入 50 万条 DELIMITER $$ CREATE PROCEDURE insert_test_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i 500000 DO INSERT INTO t_order (user_id, order_no, amount, status, create_time) VALUES ( FLOOR(RAND() * 100000), CONCAT(NO, LPAD(i, 10, 0)), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 3), DATE_ADD(2023-01-01, INTERVAL FLOOR(RAND() * 365) DAY) ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL insert_test_data();执行完数据插入后可以查看一下数据量和表占用空间SELECT COUNT(*) FROM t_order; SHOW TABLE STATUS LIKE t_order\G;有了数据后才能真实感受到索引带来的性能差异。3. EXPLAIN 执行计划详解EXPLAIN 是 MySQL 提供的一条分析 SQL 执行计划的命令也是索引调优的第一工具。它不会真正执行查询而是输出 MySQL 优化器选择的执行方案。3.1 EXPLAIN 的基本用法使用方法非常简单直接在 SQL 前面加上 EXPLAIN 关键字EXPLAIN SELECT * FROM t_order WHERE user_id 10086;执行结果会返回一列信息包含 id、select_type、table、type、possible_keys、key、key_len、ref、rows、filtered、Extra 等字段。其中最重要的几个字段含义如下字段含义type访问类型从好到差依次是 system const eq_ref ref range index ALLkey实际使用的索引名称key_len使用的索引长度长度越长说明索引覆盖越精确rows预估扫描的行数越小越好Extra额外信息比如 Using index、Using where、Using filesort 等type 字段是分析慢 SQL 时最优先查看的字段。如果 type 是 ALL说明走了全表扫描必须重点关注。3.2 常见访问类型下面直接看一个具体查询EXPLAIN SELECT * FROM t_order WHERE user_id 10086;在user_id上有索引的情况下type 一般是 ref表示使用了非唯一索引等值匹配。如果我们查询主键EXPLAIN SELECT * FROM t_order WHERE id 1;type 为 const表示通过主键或唯一索引等值查询只需要查询一次。再看一个会产生全表扫描的例子EXPLAIN SELECT * FROM t_order WHERE amount 500;由于amount列没有索引type 显示为 ALLrows 接近全表数据量。这里需要说明不是所有查询都适合加索引。比如表中只有几百条数据全表扫描可能比走索引更快因为索引本身也有维护成本。3.3 Extra 字段里的隐晦信息Extra 字段经常被忽略但它能暴露很多性能问题。Using filesort表示查询需要额外的排序操作当排序字段无法利用索引时出现。Using temporary表示查询使用了临时表一般出现在 group by、distinct 或 union 中。Using index表示覆盖索引查询所需的字段都包含在索引中无需回表这是最优情况。Using where表示 MySQL 在存储引擎层返回记录后再通过 where 条件进行过滤。举个例子EXPLAIN SELECT user_id, order_no FROM t_order WHERE user_id 10086;如果有一个联合索引(user_id, order_no)那么 Extra 会显示Using index表示覆盖索引生效不需要回表。但如果查询的字段包含amount而amount不在联合索引中EXPLAIN SELECT user_id, order_no, amount FROM t_order WHERE user_id 10086;Extra 字段变成Using where同时需要回表获取 amount 字段。在实际项目中优先设计覆盖索引是减少回表、提升查询性能的重要手段。3.4 真实调优场景深分页优化分页查询是 MySQL 调优里非常经典的问题。比如SELECT * FROM t_order ORDER BY create_time LIMIT 200000, 20;这条 SQL 的写法看起来没问题但当偏移量很大时MySQL 会扫描前面 200020 行然后丢弃前 200000 行性能极差。通过 EXPLAIN 可以看到它使用了 filesortrows 会非常大。这种情况下可以改为基于主键的分页或延迟关联-- 优化前深分页性能差 SELECT * FROM t_order ORDER BY create_time LIMIT 200000, 20; -- 优化后延迟关联先通过索引拿到主键范围再关联回原表 SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order ORDER BY create_time LIMIT 200000, 20 ) tmp ON t.id tmp.id;子查询利用索引覆盖只读取主键和排序字段可以大大减少扫描量再通过主键关联回表链路。这种写法在面试中被问到时是一个不错的加分项。4. 索引失效场景与解决方案索引调优不仅仅要会“建索引”还要会避免“有索引却不走”。下面列出实际开发中最常见的索引失效场景每一项都附了示例。4.1 未遵循最左前缀原则联合索引(a, b, c)在使用时必须从最左边开始匹配跳过前面的字段直接使用后面的字段会导致索引失效。-- 建联合索引 ALTER TABLE t_order ADD INDEX idx_user_status_create_time (user_id, status, create_time); -- 有效使用 user_id SELECT * FROM t_order WHERE user_id 10086; -- 有效使用 user_id status SELECT * FROM t_order WHERE user_id 10086 AND status 1; -- 失败跳过第一个字段直接使用 status SELECT * FROM t_order WHERE status 1;但是有一点需要说明如果跳过了中间的字段比如user_id create_timeMySQL 只能用到 user_id 和部分索引create_time 无法用于索引过滤。你可以通过key_len来观察实际使用索引的长度。4.2 对索引列使用函数或表达式在 where 条件中对索引列使用函数、计算等操作会导致优化器放弃索引。-- 失效对 create_time 使用 DATE 函数 SELECT * FROM t_order WHERE DATE(create_time) 2023-05-01; -- 有效使用范围查询 SELECT * FROM t_order WHERE create_time 2023-05-01 00:00:00 AND create_time 2023-05-02 00:00:00;除了函数对索引列做算术运算也一样比如WHERE amount 1 100也会失效。4.3 隐式类型转换当查询条件的类型和字段类型不一致时MySQL 会隐式地把字段转换成其他类型导致索引失效。假设order_no是 varchar 类型-- 失效数值传给 varchar 字段内部会做类型转换 SELECT * FROM t_order WHERE order_no 1234567890; -- 有效字符串匹配 SELECT * FROM t_order WHERE order_no 1234567890;这个细节在实际开发中非常容易踩坑尤其是接口参数没有做类型校验的时候。4.4 LIKE 模糊查询以通配符开头-- 失效以 % 开头无法利用索引 SELECT * FROM t_order WHERE order_no LIKE %NO123%; -- 有效以固定前缀开头可以走索引 SELECT * FROM t_order WHERE order_no LIKE NO123%;这一条是面试高频考点大家要记住%在前索引失效%在后索引生效。4.5 使用 OR 连接非索引列当 OR 的左右两侧只要有一个列没有索引整个查询就很难使用索引。-- 假设 amount 没有索引 SELECT * FROM t_order WHERE user_id 10086 OR amount 500;优化方式是拆成两个查询后 UNION 合并或者给 amount 也建立合适的索引。4.6 索引失效场景汇总表我把上面这些场景整理成了一张表方便大家复习失效场景错误写法示例正确做法最左前缀WHERE status 1跳过 user_id联合索引从第一个字段开始使用使用函数WHERE DATE(create_time) 2023-05-01改成范围查询隐式转换WHERE order_no 123456WHERE order_no 123456LIKE 前置通配符WHERE order_no LIKE %NO123%使用LIKE NO123%OR 连接非索引列WHERE user_id 1 OR amount 500拆分为 UNION 或索引覆盖所有 OR 字段负向查询NOT IN等WHERE status NOT IN (1, 2)根据业务改写为正向匹配需要补充的是IS NULL、IS NOT NULL在某些场景下也会影响索引使用但 MySQL 8.0 对空值处理有优化大家根据 EXPLAIN 结果判断即可不必死记硬背。5. 强制走索引与索引监控5.1 强制索引的用法有些特殊情况下MySQL 优化器对数据分布统计不够准确选择了全表扫描而实际上走索引更快。这时可以用FORCE INDEX强制指定索引。SELECT * FROM t_order FORCE INDEX (idx_user_id) WHERE user_id 10086;但我要提醒一点FORCE INDEX是最后手段不建议在生产环境长期依赖。更合理的做法是更新统计信息或者检查 SQL 写法是否能改写。5.2 查看索引基数索引的区分度直接影响优化器的选择。你可以通过下面的命令查看索引基数SHOW INDEX FROM t_order;重点关注Cardinality字段它表示索引中不重复值的估算数量。如果 Cardinality 远小于表的行数说明这个索引的区分度不高优化器可能不会选择它。举个例子如果status字段只有 3 个取值但表里有 50 万行那单独给 status 建索引意义不大优化器大概率也会放弃索引。5.3 慢查询日志排查生产环境慢 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;设置完成后执行时间超过 1 秒的 SQL 会被记录到慢查询日志中。再配合mysqldumpslow工具可以快速分析哪些 SQL 最需要优化。这条链路是从“被动等用户体验差”变成“主动发现慢 SQL”的关键一步。我在实际项目中的习惯是上线前先开慢查询日志运行几天后集中分析再用 EXPLAIN 一一定位问题。6. 高频 MySQL 索引面试题深度解析这一节把面试中关于 MySQL 索引的高频问题做一次集中拆解。每个问题不只是给答案也补充了面试官背后的考察点方便你理解记忆。6.1 为什么 InnoDB 使用 BTree 而不是 B-Tree 或 Hash 索引这是最经典的一道索引原理题。回答思路B-Tree 的每个节点都存储数据同样的磁盘页能存放的键值更少树更高查找时磁盘 IO 次数更多。BTree 的非叶子节点只存键值叶子节点存数据树更矮更胖IO 次数更少。BTree 叶子节点通过双向链表相连非常适合范围查询和排序而 B-Tree 做范围查询时需要中序遍历。Hash 索引虽然等值查询 O(1) 很快但不支持范围查询、排序和部分索引匹配所以 InnoDB 默认不使用 Hash 作为索引结构。最后可以补充InnoDB 在自适应 Hash 索引中会将某些热点数据转为 Hash 加速但这是存储引擎内部的优化和 BTree 并不冲突。6.2 什么是回表回表一定慢吗回表是指通过二级索引找到主键后再到聚簇索引中获取完整行的过程。回表本身多了一次 IO所以通常比覆盖索引慢但并不是绝对。如果数据量小或者数据已经被缓存在 buffer pool 中回表性能也未必差。不过在设计索引时我们应该优先考虑用覆盖索引避免回表。6.3 覆盖索引为什么快覆盖索引是指查询所需的字段都能在索引中找到不需要回表。比如CREATE INDEX idx_user_status_on_t_order ON t_order(user_id, status); SELECT user_id, status FROM t_order WHERE user_id 10086;上面的查询只用到 user_id 和 status都在索引中Extra 会显示Using index。由于减少了回表 IO性能会明显提升。实际项目中的典型做法是把高频查询涉及的字段组合成联合索引既能满足查询条件又能覆盖 select 的字段。6.4 最左前缀原则是怎么实现的联合索引(a, b, c)在 BTree 中排序时先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。所以查询条件中如果没有第一个字段BTree 无法确定从哪里开始扫描。这也是为什么面试官总爱问“联合索引字段顺序有什么讲究”。原则是区分度高的字段放在前面经常等值查询的字段放在前面范围查询的字段放后面。6.5 为什么不要过度建索引索引不是越多越好。原因包括每个索引都需要占用磁盘空间。每次 insert、update、delete 都需要同步维护索引写入性能会下降。优化器在多个索引之间做选择时也会增加额外开销。我在实际项目中见过一张表建了十几个索引的情况最后不仅写入变慢而且有些索引完全没人用。可以用 MySQL 8.0 的sys.schema_unused_indexes视图查看哪些索引从未被使用SELECT * FROM sys.schema_unused_indexes;这张视图会帮你找出没有被使用的冗余索引是索引治理的好帮手。6.6 联合索引和单列索引有什么区别联合索引是多个字段组成的一个索引单列索引是每个字段独立建索引。联合索引的查询遵循最左前缀原则而多个单列索引叠加后数据库通常会选择其中一个区分度最高的索引使用不会把多个索引自动合并成联合索引的效果。只有当查询条件同时命中多个单列索引时部分版本才会做 Index Merge 优化但效果不稳定不建议依赖。所以设计索引时要根据实际查询模式优先考虑联合索引而不是给每个字段单独建索引。6.7 索引为什么能加速查询却影响写入这个问题的本质是数据结构的代价。BTree 在查询时能快速定位数据但在插入、删除时需要维护树的平衡可能需要节点分裂、合并操作。表上索引越多每次数据变更需要同步更新的索引树也就越多所以写入性能会下降。回答时可以补充在批量导入数据时先删掉非必要索引导入完成后再重建是常见的性能优化手段。但要注意导入期间索引缺失会影响线上查询所以生产环境要选择低峰期操作并且做好评估和回滚方案。6.8 什么情况下索引会失效这个问题结合第 3 章的失效场景回答即可。最好自上而下说明最左前缀、函数运算、隐式转换、LIKE 前置通配符、OR 连接非索引列、负向查询等。面试时如果能结合 EXPLAIN 实例说明会比背概念更有说服力。7. 索引调优最佳实践与工程建议这一部分是根据我自己的项目经验总结的注意事项偏向工程落地建议收藏。7.1 索引设计原则为高频查询建立索引优先考虑 where、join、order by、group by 后面的字段。选择区分度高的字段做索引候选比如手机号、订单号而不是性别这种低区分度字段。使用联合索引时遵守最左前缀原则将等值查询字段放前面范围查询字段放后面。控制单表索引数量一般不超过 5 个左右的活跃索引。如果字段过多重新审视表设计是否合理。避免对过长的 varchar 字段整体建索引可以使用前缀索引。前缀索引示例ALTER TABLE t_order ADD INDEX idx_order_no_prefix (order_no(8));注意前缀索引会降低区分度选择多长的前缀需要根据数据分布测试决定。7.2 SQL 编写规范where 条件避免对索引列使用函数和隐式类型转换。尽量避免SELECT *优先查询需要的字段配合覆盖索引避免回表。深分页使用延迟关联优化。排序字段尽量与索引顺序一致避免 filesort。大批量数据更新或删除要分批执行避免长事务和锁竞争。下面是一个分批更新的例子-- 分批删除示例每次处理 1000 条避免一次性锁大量行 DELETE FROM t_order WHERE status 2 AND id IN ( SELECT id FROM ( SELECT id FROM t_order WHERE status 2 LIMIT 1000 ) tmp );7.3 监控与测试上线前用 EXPLAIN 检查核心 SQL 的执行计划。测试环境开启慢查询日志设置 long_query_time 为 1 秒。用sys.schema_unused_indexes定期清理无用索引。大表 DDL 使用在线 DDL 工具或分阶段执行避免长时间锁表。需要特别注意权限问题开启慢查询日志、变更索引、清理索引这些操作都涉及线上数据库变更一定要经过合理授权先在测试环境验证再在业务低峰期操作并且提前做好备份和回滚方案。7.4 生产环境索引变更流程这个流程是我在团队内部推行过的分享出来供你参考在测试环境还原线上慢 SQL。用 EXPLAIN 确认优化方案。在测试环境执行 DDL验证查询性能提升。评估新增索引对写入性能的影响。提交变更申请在业务低峰期执行。变更后观察慢查询日志和监控指标。如果不再需要旧索引确定无引用后清理。8. 总结与后续学习建议MySQL 索引调优这一块真正掌握的标准不只是“知道 BTree”和“会给 where 字段加索引”而是拿到一条慢 SQL 之后能解释清楚它为什么慢能不能通过 EXPLAIN 定位到关键问题以及如何用覆盖索引、联合索引、深分页优化等手段去解决。如果能做到这一点不管是日常开发还是面试都会有明显优势。学完索引之后下一步可以继续补这三个方向事务与锁机制MVCC、当前读和快照读、间隙锁、死锁分析。执行计划进阶优化器成本模型、统计信息、直方图理解 MySQL 为什么会选错索引。SQL 调优实战通过慢查询日志收集真实 SQL反复做 EXPLAIN 分析和改写训练。如果这篇文章对你有帮助可以收藏备用也可以按照文中方法自己建一张测试表亲手跑一遍。调优能力是练出来的光看效果有限。换个数据量、换个索引组合你会遇到很多预料之外的细节那才是成长最快的时候。