MySQL 索引调优不是靠背几条规范就能掌握的技能它要求你同时理解索引的底层存储结构、优化器的选择逻辑以及具体 SQL 的真实执行路径。这篇文章直接把“调优”和“面试”两条线合并起来讲先建立索引体系的完整认知再用可复现的建表、EXPLAIN、慢查询流程走一遍优化闭环最后落到高频面试题的回答框架上。如果你正在准备后端面试或者手上有一条 SQL 越跑越慢、EXPLAIN 又看不懂这篇内容可以对照着用。文章默认你已经掌握基础 SQL所有操作示例基于 MySQL 8.0 编写其中绝大部分语句在 MySQL 5.7 同样适用个别差异点我会单独标注出来。1. 核心能力速览这篇索引调优文章覆盖什么能力项说明适用数据库MySQL 5.7 / 8.0InnoDB 存储引擎核心内容索引类型、B 树原理、EXPLAIN 执行计划、索引失效场景、慢查询分析实操工具mysql 命令行、EXPLAIN、慢查询日志、performance_schema、Docker面试覆盖回表、覆盖索引、最左前缀原则、索引下推、深分页、大表建索引前置要求熟悉基础 SQL能连接本地 MySQL适合读者后端开发、DBA、准备数据库面试的工程师从使用场景上看这套内容既能支撑你完成一次线上慢 SQL 的排查也能帮你把“索引相关面试题”串成体系。与其零散地记“索引失效条件”不如先理解优化器怎么选索引这样不管 SQL 怎么写你都能判断它会不会走索引。2. MySQL 索引类型与适用场景2.1 为什么 InnoDB 索引选择 B 树InnoDB 的索引底层是 B 树这个结论几乎所有文章都会写但面试真正考察的是“为什么”。B 树和普通 B 树最大的区别在于B 树的非叶子节点只存索引键值不存数据行一个 16KB 的页面能存放更多索引条目整棵树的高度通常只有 2 到 4 层。对一次查询而言树高基本决定了磁盘 IO 次数树越矮随机 IO 越少查询越快。同时B 树的叶子节点通过双向链表串联天然支持高效的范围扫描。数据库里WHERE id BETWEEN 100 AND 200、按索引排序这类操作非常频繁B 树的这个特性正好匹配。红黑树虽然也是平衡树但树高明显更高节点存储密度低磁盘 IO 次数更多不适合作为磁盘存储结构的底层实现。2.2 InnoDB 索引分类MySQL 索引可以按多个维度分类先用一张表理清基本概念分类维度类型说明数据结构B 树索引InnoDB 默认索引结构数据结构Hash 索引仅 Memory 引擎默认支持InnoDB 的 Adaptive Hash Index 是自动行为聚簇属性聚簇索引主键索引叶子节点存放整行数据聚簇属性二级索引非主键索引叶子节点存放主键值字段数量单列索引 / 联合索引联合索引涉及最左前缀原则唯一性普通索引 / 唯一索引唯一索引约束字段值不重复特殊场景全文索引适用于全文检索InnoDB 支持但使用限制较多特殊场景前缀索引只对字符串前 N 个字符建索引大多数业务场景下我们使用最多的是 B 树下的聚簇索引和二级索引。聚簇索引以主键为键值二级索引的叶子节点存的是主键值这就引出了下图这条完整链路通过二级索引查找数据时先拿到主键再到聚簇索引回表取整行。2.3 回表与覆盖索引假设订单表orders有字段id、user_id、order_no、amount主键是id。我们给order_no建了一个普通索引执行SELECT id, user_id, amount FROM orders WHERE order_no NO20260001;这条 SQL 的查询条件走二级索引但二级索引的叶子节点只保存order_no和主键id不包含user_id、amount所以 MySQL 需要先用二级索引找到主键id再通过id去聚簇索引回表读取完整数据行这个过程就叫回表。回表不是错误它是二级索引的正常工作机制。优化思路是尽可能避免回表如果查询列本身就在索引中那么索引扫描完成后直接返回结果不需要再查聚簇索引这种场景叫做覆盖索引。上面这条 SQL 如果改成只查id和order_no二级索引就能直接覆盖Extra 字段会显示Using index。3. 本地测试环境准备调优不能只在脑内推演建议准备一个本地 MySQL 实例用小数据量跑通 EXPLAIN 和慢查询流程。下面给出一套通用准备流程所有命令都需要根据你本机实际情况调整端口和密码。3.1 通过 Docker 快速启动 MySQL 8.0如果你本机没有 MySQL用 Docker 启动是最快的隔离方案docker run -d \ --name mysql8 \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDyour_password \ mysql:8.0启动后等待几十秒容器进入 healthy 状态就能连接。如果你不习惯用容器也可以直接用 MySQL 官方安装包或系统包管理器安装核心思路是一样的拿到一个可执行 SQL 的数据库环境。检查容器状态docker ps | grep mysql8连接数据库mysql -h127.0.0.1 -P3306 -uroot -p3.2 准备一张订单表和测试数据为了演示索引效果这里准备一张结构简单、但能覆盖大多数索引场景的订单表CREATE DATABASE IF NOT EXISTS demo_db DEFAULT CHARSET utf8mb4; USE demo_db; CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(12, 2) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_created (user_id, created_at), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB;如果想观察真实的数据量和执行时间可以用存储过程插入几十万行测试数据。注意演示环境插入的数据分布是否均匀会直接影响优化器的索引选择判断。DELIMITER $$ CREATE PROCEDURE insert_orders() BEGIN DECLARE i INT DEFAULT 1; WHILE i 200000 DO INSERT INTO orders (user_id, order_no, status, amount, created_at) VALUES (i % 5000, CONCAT(NO, LPAD(i, 10, 0)), i % 4, i % 10000, NOW() - INTERVAL i MINUTE); SET i i 1; END WHILE; END$$ DELIMITER ; CALL insert_orders();这个表同时包含普通联合索引和唯一索引足够演示最左前缀、回表、覆盖索引等核心概念。4. 索引创建与基本操作4.1 查看已有索引SHOW INDEX FROM orders;执行结果里会列出索引名称、索引字段、唯一性、基数等关键信息。Cardinality表示索引的区分度估算值数值越高说明重复值越少索引选择性越好。4.2 创建相关索引创建普通索引CREATE INDEX idx_status ON orders(status);创建唯一索引CREATE UNIQUE INDEX uk_user_order ON orders(user_id, order_no);用 ALTER TABLE 方式也可以ALTER TABLE orders ADD INDEX idx_amount (amount);索引不是越多越好。写操作会同时维护所有索引索引过多会明显拖慢 INSERT、UPDATE、DELETE 的性能同时占用更多磁盘空间。给一个字段建索引之前先确认它是否真的处于高频查询条件或高频排序字段中。4.3 删除索引DROP INDEX idx_status ON orders;删除长期不用的索引属于常规清理操作。判断索引是否冗余一是看有没有完全被别的联合索引覆盖的前缀字段二是看真实业务查询里是否长期没有被优化器选中的日志记录。4.4 联合索引的字段顺序联合索引的字段顺序非常关键它决定这个索引能覆盖哪些查询。(user_id, created_at)这个索引可以支撑WHERE user_id 123 AND created_at 2026-01-01但它不能直接支撑只查询created_at的 SQL因为created_at不是联合索引的最左前缀字段。后文面试部分会详细展开最左前缀原则这里先记住联合索引的字段顺序应该按照查询条件的频率和区分度来设计而不是随意拼接。5. 用 EXPLAIN 看索引是否真的生效创建索引之后验证生效的方法是看执行计划。EXPLAIN 能告诉我们优化器最终选择的访问路径这是索引调优的第一步也是最重要的一步。5.1 最基础的调优闭环先不加任何条件地查看一条查询的执行计划EXPLAIN SELECT * FROM orders WHERE order_no NO2026000001;因为order_no上有唯一索引uk_order_no执行计划里type应该是const或refkey字段显示uk_order_no这说明走了唯一索引效率很高。为了看到对比我们可以模拟一个没有索引的查询字段。给status建索引之前先执行EXPLAIN SELECT * FROM orders WHERE status 1;此时大概率看到type ALL说明优化器选择了全表扫描。接着创建索引CREATE INDEX idx_status ON orders(status); EXPLAIN SELECT * FROM orders WHERE status 1;创建索引后type会从ALL变成refkey变成idx_status。这就是一个完整的最小调优闭环建索引前后用 EXPLAIN 对比用执行计划验证索引是否被使用。5.2 EXPLAIN 核心字段解读EXPLAIN 输出字段很多调优时重点看这几列字段含义重要关注点type访问类型从好到差system const eq_ref ref range index ALLpossible_keys优化器候选索引表示可能有用的索引key最终选择的索引显示 NULL 说明没走索引rows预估扫描行数数值越小通常越好Extra额外信息是否出现 Using filesort、Using temporary、Using indextype是最直观的判断依据。ALL是全表扫描通常需要优化index表示扫描了整棵索引树虽然比 ALL 好但仍然可能遍历大量数据range表示范围扫描比如、、BETWEEN、IN这类查询属于正常范围ref和const表示高价值的等值匹配是大多数点查能达到的理想状态。需要注意EXPLAIN 的rows是基于统计信息的预估值不是实际扫描行数。如果rows与实际差异巨大往往说明表统计信息过期可以执行ANALYZE TABLE orders;更新统计信息后再观察。5.3 关注 Extra 字段Extra字段包含大量调优线索Using index覆盖索引扫描不回表。Using index conditionIndex Condition Pushdown索引下推生效先过滤索引中已有字段减少回表次数。Using where存储引擎返回记录后Server 层又做了条件过滤。Using filesort无法利用索引完成排序需要额外排序操作常见于 ORDER BY 字段不在索引中。Using temporary使用了临时表常见于 GROUP BY、DISTINCT 等操作。Using join buffer批量连接缓冲常见于多表关联时没有走索引。面试中如果被问到“这条 SQL 为什么慢”第一条思路就是看type和Extra。ALL Using filesort的组合基本就是典型的全表扫描加额外排序优化方向很明确。6. 索引失效场景与 SQL 写法避坑索引建了但不走比没建索引更让人难受。下面这些场景是导致索引失效的高频原因每一条都可以用 EXPLAIN 实测验证。6.1 对索引列使用函数或计算-- 索引失效 EXPLAIN SELECT * FROM orders WHERE DATE(created_at) 2026-06-01;date()函数作用在索引列上优化器无法直接使用 B 树定位只能全量扫描。写法改成范围查询-- 可走索引 EXPLAIN SELECT * FROM orders WHERE created_at 2026-06-01 00:00:00 AND created_at 2026-06-02 00:00:00;原则是不要让索引列参与任何函数运算和算术运算。这包括DATE()、YEAR()、MONTH()、SUBSTRING()、LENGTH()等。6.2 隐式类型转换如果user_id是 BIGINT却用字符串去匹配日期字段MySQL 会对字段做隐式转换导致索引失效-- 假设 order_no 是 VARCHAR EXPLAIN SELECT * FROM orders WHERE order_no 2026000001;字符串字段用数字匹配时优化器会把字符串转换成数字比较通常在索引列上发生转换导致索引无法正常匹配。反过来数字字段用字符串匹配也要警惕。最稳妥的办法是保持数据类型一致代码和 SQL 都按表结构传参。6.3 LIKE 以通配符开头-- 索引失效 EXPLAIN SELECT * FROM orders WHERE order_no LIKE %NO2026%; -- 可走索引 EXPLAIN SELECT * FROM orders WHERE order_no LIKE NO2026%;前缀通配会导致优化器无法从 B 树按顺序定位只能扫描全部索引或全表。需要模糊搜索时要么改成前缀匹配要么考虑 ES 等专业检索引擎。6.4 OR 条件包含非索引列-- 如果 status 有索引user_id 没有这个查询无法充分利用索引 EXPLAIN SELECT * FROM orders WHERE status 1 OR user_id 123;OR 两侧只要有一个字段没有索引优化器就只能放弃索引选择权改为全表扫描后逐行过滤。改用 UNION 拆开SELECT * FROM orders WHERE status 1 UNION ALL SELECT * FROM orders WHERE user_id 123;当两侧都有索引时MySQL 也可能使用 Index Merge 优化但这依赖优化器成本判断不能把所有希望都寄托在特殊优化上。6.5 范围查询会导致右侧联合索引列失效联合索引(user_id, created_at)中如果user_id使用了或这类范围条件索引列右侧的created_at往往无法继续用于精确定位。最经典的场景EXPLAIN SELECT * FROM orders WHERE user_id 100 AND created_at 2026-01-01;user_id 100之后created_at列只能作为 Filter 过滤条件无法继续作为索引检索条件。所以联合索引的字段设计要把等值查询列放在前面范围查询列放在后面。6.6 NOT IN、NOT BETWEEN 等否定条件NOT IN、NOT EXISTS、这类否定条件通常不容易走索引原因是优化器认为需要扫描的数据范围过大选择全表扫描成本更低。实际是否失效取决于数据分布和版本最稳的方法是直接用 EXPLAIN 验证不要凭经验下结论。6.7 排序与分组字段不满足索引顺序ORDER BY created_at能否走索引取决于 created_at 是否出现在某个索引的最左前缀序列中。如果联合索引是(user_id, created_at)执行EXPLAIN SELECT * FROM orders WHERE user_id 1 ORDER BY created_at;此时排序可以用到索引的有序性Extra 不会出现Using filesort。但如果你直接ORDER BY created_at由于最左前缀缺失索引无法直接提供有序结果就会产生文件排序。7. 慢查询日志与性能观察EXPLAIN 解决的是“是否走索引”的问题慢查询日志解决的是“哪些 SQL 需要调优”的问题。7.1 开启慢查询日志MySQL 默认可能没有开启慢查询日志可以动态开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;long_query_time表示超过多少秒的记录到慢日志单位是秒。开发环境建议设置为 1 秒生产环境则要根据业务压测结果调整避免日志量过大。查看是否生效SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time;7.2 分析慢查询日志最简单的分析工具是mysqldumpslowmysqldumpslow -t 10 /var/log/mysql/mysql-slow.log它会输出执行次数最多或耗时最长的 Top N 条 SQL。更专业的工具是pt-query-digest能按维度聚合 SQL输出响应时间占比、调用次数、锁等待时间等适合批量分析。慢查询日志中的每一条记录包含 Query_time、Lock_time、Rows_sent、Rows_examined 等字段。判断一条慢 SQL 是否有价值不仅要看 Query_time还要看 Rows_examined 与 Rows_sent 的比率。扫描 10 万行返回 10 行说明查询选择性差需要重点优化扫描 20 行返回 20 行说明问题可能不在 SQL而在整体负载。7.3 在线查看性能视图MySQL 提供了 performance_schema 和 sys 库可以直接查询 SQL 统计SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1000000000 AS avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;这条语句能快速找出当前实例中平均响应时间最长的 SQL 语句。观察索引调优效果时可以用同一组 SQL 在优化前后对比COUNT_STAR、AVG_TIMER_WAIT和ROWS_EXAMINED_AVG的变化。7.4 资源占用观察索引调优不仅是查询速度的问题也要关注空间与内存占用。B 树索引需要磁盘空间也会占用 InnoDB Buffer Pool。索引过多时即使查询变快也可能因为缓冲池命中率下降导致整体性能受损。观察索引大小可以使用SELECT TABLE_NAME, INDEX_LENGTH, DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA demo_db AND TABLE_NAME orders;INDEX_LENGTH是所有索引占用的空间DATA_LENGTH是数据行占用的空间。如果索引占用已经接近甚至超过数据空间就需要评估是否有大量冗余索引。8. 高频 MySQL 面试题与回答思路这一章节把 MySQL 面试里出现频率最高的索引相关问题集中梳理一遍每道题都给出回答思路和关键得分点。8.1 为什么 InnoDB 用 B 树而不是 B 树或红黑树回答结构拆成三点磁盘 IO、范围查询、树高。第一B 树非叶子节点不存数据只存索引键每个磁盘页能存储的索引条目远多于 B 树树高更低。一次索引查找对应一次磁盘 IO树高直接影响查询速度。第二B 树叶子节点用链表串联范围查询和排序可以直接沿着链表顺序扫描B 树则可能涉及回溯到父节点或兄弟节点效率更低。第三红黑树虽然平衡性好但每个节点只存一个键值高度太高磁盘 IO 次数不可接受它主要适用于内存数据结构。8.2 什么是回表如何避免回表是二级索引查出主键后再去聚簇索引取完整数据行的过程。避免回表的直接手段是覆盖索引让查询所需字段全部包含在索引中。比如查询只涉及主键和order_nouk_order_no索引就能直接覆盖。面试时如果能补充一句“回表不是设计缺陷而是二级索引的正常机制目标不是消除回表而是减少无效回表”会很加分。8.3 最左前缀原则是什么联合索引(a, b, c)相当于建立了a、(a, b)、(a, b, c)三个前缀索引。查询条件必须从最左列开始才能利用联合索引排序和检索。WHERE b 1 AND c 1无法命中这个索引因为缺失a。回答时可以举例说明设计影响当需要频繁用b字段单独查询时不能只依赖(a, b, c)需要额外为b建索引这就是“联合索引不能替代所有单列索引”的原因。8.4 索引下推 Index Condition Pushdown 是什么索引下推是 MySQL 5.6 引入的优化。联合索引(user_id, created_at)查询条件包含user_id 1 AND created_at 2026-01-01时在没有 ICP 的情况下优化器会先根据user_id 1查出所有主键再回表逐行过滤created_at。ICP 允许在存储引擎层直接对索引中的created_at字段进行初步过滤只对满足条件的记录回表减少 IO 次数。EXPLAIN Extra 显示Using index condition即代表生效。8.5 深分页查询怎么优化深分页慢的根本原因是 MySQL 需要扫描并丢弃前面的大量有效行。经典写法SELECT * FROM orders ORDER BY id LIMIT 100000, 20;这个查询需要扫描 100020 行然后丢弃前 100000 行。优化方式包括使用延迟关联或基于覆盖索引定位起始点。基于 id 范围的分页示例SELECT * FROM orders WHERE id (SELECT id FROM orders ORDER BY id LIMIT 100000, 1) ORDER BY id LIMIT 20;子查询先通过覆盖索引拿到偏移位置的 id再用主键范围查询取数据避免大偏移扫描。另一种思路是业务上改为游标分页记住上一页的最后一条主键下一次直接WHERE id last_id。8.6 大表加索引有什么风险大表加索引会面临两类风险锁表时间和资源消耗。MySQL 8.0 支持原子 DDL同时在很多场景下可以使用 Online DDL不会长时间阻塞 DML但具体是否在线取决于操作类型和字段。低峰期执行、先备份、评估磁盘空间、观察主从延迟是上线前必做的四件事。如果表非常大可以考虑 gh-ost、gh-ost 类工具配合业务空窗期执行。8.7 联合索引字段顺序如何设计没有绝对公式但有三个通用原则等值查询列优先、区分度高的列靠前、考虑排序与分组字段。等值条件能稳定命中最左前缀区分度高的字段能更快缩小扫描范围。当查询既有过滤又有排序时可以让索引同时覆盖过滤列和排序列避免Using filesort。真正草率的设计是把所有查询条件随机拼进联合索引看起来很全实际哪条查询都无法高效命中。9. 常见问题排查对照表问题现象可能原因排查方式解决方案建了索引但 EXPLAIN 显示全表扫描索引列发生隐式转换或函数运算查看 EXPLAIN key 字段是否为 NULL改写 SQL保持字段类型一致SQL 查询时间波动大统计信息过期、缓冲池冷启动、索引选择错误执行 ANALYZE TABLE对比多次执行时间更新统计信息必要时用 FORCE INDEX 验证是否索引选择问题慢查询日志没有记录long_query_time 阈值过高或慢日志未开启SHOW VARIABLES LIKE slow_query_log动态开启并设置合理阈值分页很深时响应变慢大偏移导致扫描大量无用行查看 Rows_examined改为基于 id 范围或游标分页联合索引未被使用查询条件不满足最左前缀原则检查查询条件的字段顺序调整联合索引顺序或拆分索引覆盖索引没有生效查询列超出索引字段查看 Extra 是否出现 Using index确认查询列是否全部在索引中排序字段导致文件排序ORDER BY 字段不在索引序列内查看 Extra 是否出现 Using filesort让排序列加入联合索引并满足前缀顺序写入性能下降明显冗余索引过多查看 SHOW INDEX 重复索引删除重复或长期未使用的索引10. 索引调优最佳实践第一把 EXPLAIN 作为索引变更的验收标准。任何一次加索引、改 SQL、调整联合索引字段顺序都要用优化前后的 EXPLAIN 和执行时间对比来验证效果。第二统一索引命名规范。主键索引一般不额外命名普通索引使用idx_字段名联合索引用idx_字段1_字段2唯一索引使用uk_字段名。规范命名能让你半年后回看表结构时不用猜。第三验证索引区分度。创建索引前先算一下字段的区分度SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM orders;选择性接近 1 说明字段值几乎全不同适合建索引选择性接近 0 说明大量重复建索引的意义很小全表扫描反而更快。例如订单状态字段通常只有几个枚举值单独建索引的效果有限更适合放在联合索引中。第四联合索引字段顺序按“等值查询列优先、区分度高优先、范围列最后”设计同时把 ORDER BY、GROUP BY 字段纳入考虑避免额外排序。第五控制单表索引数量。常规业务表建议保持 5 个以内索引写入频繁的表更要克制。索引不是越多越好每一次写入都要同步维护所有索引节点。第六测试环境验证后再上生产。先在测试库压一份接近生产数据量的数据用 EXPLAIN 和慢查询日志对比前后差异尽量选择业务低峰期执行 DDL。涉及线上表结构调整时要有备份和回滚方案。11. 总结与下一步这篇文章从索引存储结构讲到 EXPLAIN 分析再到慢查询定位和面试答案核心是帮你建立一条完整的索引调优链路先看type、key、rows、Extra判断是否走索引再根据Using filesort、Using temporary定位额外开销最后用慢查询日志找出真正需要优先优化的 SQL。最先应该做的事是把文中订单表建出来亲手执行几遍带索引和不带索引的 EXPLAIN重点观察type从ALL到ref的变化以及Extra中Using index condition和Using index的区别。最容易踩的坑是隐式类型转换和最左前缀失效这两类场景在面试和线上排查中都特别高频建议用不同字段类型反复验证。后续可以继续拓展的方向阅读 MySQL 官方文档中 Optimizer 章节学习OPTIMIZER_TRACE的详细输出实践 online DDL 在真实业务表上的操作流程再进阶一点可以研究 InnoDB Buffer Pool 命中率、redo log 刷盘策略对整体性能的影响。索引调优是一个需要持续用数据验证的过程把 EXPLAIN 和慢查询日志用熟后面的路会顺很多。