mysql索引优化这个话题我在之前的文章里聊过一些基础原理这次标题是“实战2”意味着不讲什么是B树也不再重复聚簇索引的概念而是直接进入真实业务里那些让人头疼的细节。这篇的内容全部来自我自己的线上排查记录明明字段有索引explain 一看 type 还是 ALL联合索引建了一堆慢查询却一个没少深分页翻到后面接口直接超时。文章会依次拆解索引失效的高频场景、覆盖索引与回表优化、排序和深分页的处理手法以及如何用 explain 和 key_len 判断一条慢 SQL 的优化方向。适合正在被慢查询折磨的开发者、准备 mysql 面试想系统过一遍索引优化考点的人也适合那种“建索引全凭感觉”的同学参考。1. 排查索引失效的五个高频场景1.1 函数操作与隐式类型转换索引失效的头号元凶先说一个真实例子。我这边订单流水表每天新增几十万条数据运营要拉某一天的订单SQL 写成了这样SELECT order_id, amount, status FROM orders WHERE DATE(create_time) 2025-01-20;create_time 字段本身是有索引的但这条 SQL 稳定出现在慢查询日志里。explain 结果 type 是 ALL扫描行数接近全表。原因就是 WHERE 条件对 create_time 使用了 DATE() 函数只要索引列参与了函数运算MySQL 就无法按索引有序查找只能把整张表的数据取出来逐行计算。这个机制可以类比成一本字典不按拼音排却要求你先把每个字的笔画数算出来再筛痛苦程度翻倍。正确写法是把这个等值条件改写成范围条件SELECT order_id, amount, status FROM orders WHERE create_time 2025-01-20 00:00:00 AND create_time 2025-01-21 00:00:00;范围条件是可以走索引的改写后同一批数据查询时间从几百毫秒降到了几十毫秒。比函数操作更隐蔽的是隐式类型转换。用户表的 mobile 字段是 VARCHAR(11)查询条件却写成 WHERE mobile 13800138000数字类型的参数传入后MySQL 会先把字段值转成数字再比较这个转换本质上也是对索引列做了函数操作索引自然就失效了。有一次排查我把应用代码里的入参类型改成了字符串同一个 SQL 从全表扫描变成走索引性能直接提升了一个量级。所以建索引时字段类型要统一查询时参数类型更要和字段严格一致这是索引能用上的大前提。注意对索引列做任何函数、运算、隐式转换都可能让优化器放弃索引。排查慢查询第一步不是调参数而是看 WHERE 条件里有没有这几种“危险动作”。1.2 联合索引的“最左前缀”到底怎么理解最左前缀原则是索引优化里被问得最多、也最容易理解错的概念。我经常用一个场景说明假设表里有 user_id、order_status、create_time 三个字段业务上最频繁的查询是“查某个用户某状态下的订单”于是建了联合索引 (user_id, order_status, create_time)。这个索引对下面的查询非常友好SELECT order_id FROM orders WHERE user_id 123 AND order_status 1 AND create_time 2025-01-01;三条条件分别能命中索引的三列explain 里的 key_len 可以直接算到第三列。但换一个查询就不一样了SELECT order_id FROM orders WHERE order_status 1 AND create_time 2025-01-01;它跳过了第一列 user_id直接查第二列此时联合索引基本是废的。原因是联合索引的数据结构先按第一列排序第一列相同再按第二列排序第一列值不确定时第二列谈不上有序查找。这就像一本按“班级-学号-姓名”建立的目录只知道姓名翻目录是翻不到的。还有一个容易踩的坑是范围条件的影响。同样的索引如果查询写成WHERE user_id 123 AND order_status 1 AND create_time 2025-01-01order_status 是范围条件它后面的 create_time 就无法通过索引精确定位了因为 order_status 大于 1 的时候create_time 已经不是全局有序。MySQL 优化器只能在 order_status 的范围内把 create_time 当作普通过滤条件逐行判断。所以设计联合索引时要把最常用的等值条件放在前面范围查询字段放在靠后的位置才能让索引的每一列都发挥价值。2. 覆盖索引与回表优化实战2.1 什么是回表为什么会成为性能瓶颈先把 InnoDB 的存储机制讲透。InnoDB 表是聚簇索引结构主键索引的叶子节点直接存整行数据二级索引的叶子节点只存“索引列本身 主键值”。所以通过二级索引查到的记录如果查询还需要其他列就得拿着主键值再回主键索引里找完整行这个过程叫回表。我习惯用一个类比解释二级索引是书后面的关键词索引它告诉你某个词在哪几页你得翻到对应页去看正文。如果整段话都印在这份关键词索引里就不用翻正文了这就是覆盖索引。回表本身不是问题真正的问题是回表的次数。一个查询命中 100 条记录回表就要做 100 次随机 I/O每次都要沿着 B 树走一遍。如果查询结果集很大回表次数多慢查询就出现了。我曾经处理过一个报表接口服务端代码里用循环查了上万条数据再聚合每条都触发回表接口直接卡死几分钟。后来改成一条 SQL 配合覆盖索引一次搞定问题彻底消失。2.2 用覆盖索引改写慢查询以我之前优化过的一个订单列表页为例。业务 SQL 原本是这样SELECT order_no, order_status, pay_amount, create_time FROM orders WHERE user_id 123 AND order_status 1 ORDER BY create_time DESC LIMIT 10;这个查询会先通过 user_id 相关的索引找到一批记录然后回表取出 order_no、order_status、pay_amount、create_time再排序返回。问题出在回表上如果 user_id 下有几万条记录即使最终只返回 10 条中间过程也可能回表几万次。优化思路是做一个覆盖索引把查询涉及的列都放进去ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, order_status, create_time, order_no, pay_amount);加完之后explain 的 Extra 里出现了 Using index意思是查询需要的所有列都直接从索引拿到不用回表。同样的接口平均响应时间从 200ms 降到了 30ms 左右。但这里必须泼一盆冷水覆盖索引不是列加得越多越好。索引本身也是数据每次插入、更新、删除都要同步维护。如果为了覆盖索引把十几列都塞进去写入性能会明显下降binlog 也变大。我的经验是覆盖索引只用于高频列表查询而且只覆盖固定的业务列如果业务查询是 SELECT * 这种覆盖索引基本没用还是老老实实从回表和结果集大小入手优化。提示判断一个查询是不是覆盖索引就看 explain 的 Extra 里有没有 Using index。有这句说明索引扛起了所有需要的数据回表次数为零。3. 排序与深分页优化让 filesort 靠边站3.1 order by 走索引的条件排序是索引优化里很容易被忽略的一环。一个查询即使 WHERE 条件走了索引如果 ORDER BY 的字段不在索引里MySQL 还得专门做一次排序这就是 filesort。filesort 在数据量小的时候不一定慢但数据量大、sort_buffer 放不下时会产生临时文件和磁盘 I/O这时查询就会明显变慢。让排序走索引的核心思路是让 ORDER BY 字段和 WHERE 条件的字段共用同一个索引并且顺序一致。举例订单表经常按状态和时间排序SELECT order_id, amount FROM orders WHERE order_status 1 ORDER BY create_time DESC LIMIT 20;最合适的索引是 (order_status, create_time)先按状态定位状态相同的数据在索引里天然按 create_time 排好了序MySQL 直接顺序读取即可不需要额外 filesortexplain 的 Extra 里不会出现 Using filesort。如果索引是 (create_time, order_status)查询条件却是 ORDER BY create_time由于 create_time 是索引第一列而 WHERE 条件直接查 order_status跳过了第一列这个查询既难以高效过滤也排不了序。所以索引设计要结合 WHERE 和 ORDER BY 一起看本质上是“等值字段优先排序字段随后”。方向不一致也是个坑。ORDER BY create_time ASC 和 DESC 如果方向混用比如索引按 ASC 建查询要 DESC在 MySQL 8.0 之前优化器可能还是选择 filesort。MySQL 8.0 支持降序索引可以在建索引时直接指定 DESC 方向但实际工程里遇到这种情况我会先评估排序数据量数据量不大就安心用 filesort数据量大再考虑改造索引不用一见到 filesort 就慌。3.2 深分页优化LIMIT 100000, 10 为什么这么慢深分页的慢不在于返回 10 条数据而在于 MySQL 要把前 100010 条都翻一遍。比如 LIMIT 100000, 10即使有索引MySQL 也会从索引头部开始一路数到第 100000 条再往下取 10 条中间扫描的 10 万行是纯浪费的。我处理过一个实际案例后台订单列表用户翻到了很深的页码接口响应直接超时。优化方案有两个。第一个是延迟关联先只查主键再回表取完整行SELECT t1.id, t1.order_no, t1.amount FROM orders t1 JOIN ( SELECT id FROM orders WHERE order_status 1 ORDER BY create_time DESC LIMIT 100000, 10 ) t2 ON t1.id t2.id ORDER BY t1.create_time DESC;子查询里只取 id 列能走覆盖索引扫描 10 万行 id 的成本比取整行数据低得多拿到 10 个目标 id 后再去主表取完整数据回表次数只有 10 次。第二种是游标分页。业务上记住上一页最后一个 id 或时间下一页用 WHERE id ? ORDER BY id LIMIT 10 来取。这个方案本质上把“深分页”改成了“浅分页”不管翻多少页性能都稳定。我个人的倾向是列表页真的翻到几千上万页产品层面就该限制了。技术优化能兜底但体验和效率上限制最大页码、提供筛选条件才是更合理的产品解法。4. Explain 实战拿一条慢 SQL 怎么一步步看4.1 关键字段与执行计划解读拿到一条慢 SQL 之后我第一步永远是 EXPLAIN先把执行计划打出来。字段很多我认为最需要读懂的是 type、key、rows、Extra 这四个。type 表示访问类型从好到差大致是const eq_ref ref range index ALL。看到 const 或 eq_ref说明走的是主键或唯一索引性能基本没问题。ref 是普通索引等值匹配也不错。range 是范围扫描可以接受。index 是全索引扫描虽然不是全表扫描但也不理想。ALL 是全表扫描这是慢查询最常见的原因。我习惯把这几个等级整理成表格方便对比type含义建议const / eq_ref主键或唯一索引等值查询理想状态ref普通索引等值匹配性能不错range索引范围扫描可用但注意范围大小index全索引扫描需优化ALL全表扫描必须优化key 表示实际用到的索引。这里有个常见误区你以为建了索引就一定用得上实际上 explain 的 key 可能为 NULL说明优化器压根没选中这个索引。rows 是优化器估算的扫描行数注意是估算值但不同执行计划对比时参考价值很大。Extra 里的信息更丰富。出现 Using index 说明是覆盖索引这是加分项。Using where 表示取回记录后还要额外过滤通常是因为部分条件无法在索引层完成。Using filesort 是排序在索引之外进行的信号Using temporary 则意味着查询用了临时表通常和 GROUP BY、DISTINCT、子查询有关这类查询要格外警惕。我一般给出的建议是先确认 type 不是 ALL再确认 key 不为空最后看 Extra 有没有 Using filesort 或 Using temporary这三点都过关优化方向基本就明确了。4.2 key_len 计算判断联合索引到底用了几列很多人在 explain 里只看 type 和 rows忽略了 key_len其实 key_len 是判断联合索引命中哪些列的关键。key_len 表示 MySQL 在索引里实际使用到的字节数它由字段类型、字符集、是否允许 NULL、是否变长共同决定。计算方法拿最常见的 utf8mb4 举例。utf8mb4 下一个字符最多占 4 字节VARCHAR(20) 不是定长额外加 2 字节记录长度如果字段允许 NULL再加 1 字节。所以一个允许为 NULL 的 VARCHAR(20) 字段key_len 20 × 4 2 1 83。INT 类型的字段是 4 字节允许 NULL 再加 1 就是 5。DATETIME 是 8 字节允许 NULL 是 9。假设联合索引是 (user_id INT, order_status INT, create_time DATETIME)如果 explain 出来的 key_len 是 10说明只用了 user_id 和 order_status 两列各占 5 字节create_time 没有被索引命中。这样就能反推 SQL 的写法是不是触发了范围条件截断从而判断是改 SQL 还是改索引。这个技巧在 mysql 面试里也经常被当作进阶考点很多人知道联合索引最左前缀但不知道如何验证到底命中了几列key_len 就是回答这个问题的关键数据。4.3 字符集不一致等隐藏的坑索引失效有一个非常隐蔽的诱因字符集不一致。我之前排查过一个订单表关联用户表的查询订单表 user_id 是 utf8mb4用户表 user_id 是 utf8关联条件明明写得很正常explain 一看订单表这边直接 type 为 ALL。原因是 MySQL 在做关联时会把字符集不一致的字段隐式转换相当于对订单表的 user_id 做了转换操作索引就失效了。解决办法是统一库表字段的字符集一般推荐全库统一 utf8mb4。MySQL 8.0 里 utf8mb4 也是默认字符集兼容 emoji 和更多字符。另外OR 条件也是索引失效的高发地。WHERE user_id 1 OR user_id 2 这种简单情况优化器会尝试索引合并但 OR 关联的是不同字段比如 user_id 1 OR status 1两边各自有索引MySQL 8.0 会尝试 index merge但结果不一定理想。更稳的做法是拆成两个查询然后 UNION ALL让每个查询都稳定走自己的索引。LIKE 查询以 % 开头同样会让索引失效这个大家比较熟悉但要注意 LIKE abc% 是可以用索引的关键是通配符不能出现在最左侧。5. 索引运维与长期维护5.1 看起来合理实则低效的索引设计做了这么多年优化最常见的不是不会建索引而是索引建得太多太杂。我见过一张业务表有十六个索引其中大部分是不同同事在不同时期“看到慢查询就加一个”的结果。写入延迟明显上升磁盘占用也大而真正高频使用的只有三四个。这个问题可以用 pt-duplicate-key-checker 或者 sys.schema_redundant_indexes 视图来检查冗余索引。比如已经有了联合索引 (a, b)再单独建 (a) 就是冗余的因为 (a, b) 索引已经能覆盖以 a 为前缀的查询。另一种低效设计是给低区分度字段建索引。比如 status 字段一共只有两三个值数据分布严重不均。如果表里 90% 数据都是 status1查询 status1 时优化器大概率会放弃索引直接全表扫描因为这时全表扫反而更快。遇到这种字段单独建索引没有意义不如在查询条件里增加其他高区分度字段或者结合 (status, create_time) 这种联合索引让时间的范围条件来协助定位数据。5.2 线上加索引的正确姿势与统计信息维护线上业务高峰期直接 ALTER TABLE ADD INDEX 风险很大。MySQL 8.0 虽然对 DDL 做了不少优化但大表的 ALTER 操作依然可能长时间锁表尤其在有长事务存在的时候。工程上的标准做法是用 pt-online-schema-change 或者 gh-ost 这类在线变更工具通过创建临时表、同步增量数据、最后切换的流程把对业务的影响降到最低。我在千万级大表上加索引从来都是走这个路子不会直接执行原生 ALTER。另外优化器选择索引依赖统计信息。如果一张表的数据量发生过剧烈变化比如大批量删除或导入后统计信息没有及时更新优化器可能还用旧的统计信息选索引导致执行计划不理想。这时候执行 ANALYZE TABLE 更新统计信息往往能解决莫名奇妙的慢查询。这类问题排查起来很隐蔽因为表结构、索引、SQL 都没变只是数据量变化执行计划就飘了。我的习惯是大批量数据操作之后主动跑一次 ANALYZE TABLE并且用 sys.schema_unused_indexes 视图定期检查那些长期没有用到的索引该删就删。索引维护不是一次性的事而是和业务迭代同步持续的动态过程。这些年做索引优化我最深的一个体会是不要迷信“加索引能解决一切”。索引优化本质上是在读性能和写性能之间做权衡一个精心设计的联合索引能救活一条慢 SQL但设计不当的索引同样能把写入拖垮。真功夫都在 explain 和 key_len 这些细节里多跑几次真实案例比背一百条理论管用得多。
MySQL索引优化实战：索引失效、覆盖索引与深分页调优
mysql索引优化这个话题我在之前的文章里聊过一些基础原理这次标题是“实战2”意味着不讲什么是B树也不再重复聚簇索引的概念而是直接进入真实业务里那些让人头疼的细节。这篇的内容全部来自我自己的线上排查记录明明字段有索引explain 一看 type 还是 ALL联合索引建了一堆慢查询却一个没少深分页翻到后面接口直接超时。文章会依次拆解索引失效的高频场景、覆盖索引与回表优化、排序和深分页的处理手法以及如何用 explain 和 key_len 判断一条慢 SQL 的优化方向。适合正在被慢查询折磨的开发者、准备 mysql 面试想系统过一遍索引优化考点的人也适合那种“建索引全凭感觉”的同学参考。1. 排查索引失效的五个高频场景1.1 函数操作与隐式类型转换索引失效的头号元凶先说一个真实例子。我这边订单流水表每天新增几十万条数据运营要拉某一天的订单SQL 写成了这样SELECT order_id, amount, status FROM orders WHERE DATE(create_time) 2025-01-20;create_time 字段本身是有索引的但这条 SQL 稳定出现在慢查询日志里。explain 结果 type 是 ALL扫描行数接近全表。原因就是 WHERE 条件对 create_time 使用了 DATE() 函数只要索引列参与了函数运算MySQL 就无法按索引有序查找只能把整张表的数据取出来逐行计算。这个机制可以类比成一本字典不按拼音排却要求你先把每个字的笔画数算出来再筛痛苦程度翻倍。正确写法是把这个等值条件改写成范围条件SELECT order_id, amount, status FROM orders WHERE create_time 2025-01-20 00:00:00 AND create_time 2025-01-21 00:00:00;范围条件是可以走索引的改写后同一批数据查询时间从几百毫秒降到了几十毫秒。比函数操作更隐蔽的是隐式类型转换。用户表的 mobile 字段是 VARCHAR(11)查询条件却写成 WHERE mobile 13800138000数字类型的参数传入后MySQL 会先把字段值转成数字再比较这个转换本质上也是对索引列做了函数操作索引自然就失效了。有一次排查我把应用代码里的入参类型改成了字符串同一个 SQL 从全表扫描变成走索引性能直接提升了一个量级。所以建索引时字段类型要统一查询时参数类型更要和字段严格一致这是索引能用上的大前提。注意对索引列做任何函数、运算、隐式转换都可能让优化器放弃索引。排查慢查询第一步不是调参数而是看 WHERE 条件里有没有这几种“危险动作”。1.2 联合索引的“最左前缀”到底怎么理解最左前缀原则是索引优化里被问得最多、也最容易理解错的概念。我经常用一个场景说明假设表里有 user_id、order_status、create_time 三个字段业务上最频繁的查询是“查某个用户某状态下的订单”于是建了联合索引 (user_id, order_status, create_time)。这个索引对下面的查询非常友好SELECT order_id FROM orders WHERE user_id 123 AND order_status 1 AND create_time 2025-01-01;三条条件分别能命中索引的三列explain 里的 key_len 可以直接算到第三列。但换一个查询就不一样了SELECT order_id FROM orders WHERE order_status 1 AND create_time 2025-01-01;它跳过了第一列 user_id直接查第二列此时联合索引基本是废的。原因是联合索引的数据结构先按第一列排序第一列相同再按第二列排序第一列值不确定时第二列谈不上有序查找。这就像一本按“班级-学号-姓名”建立的目录只知道姓名翻目录是翻不到的。还有一个容易踩的坑是范围条件的影响。同样的索引如果查询写成WHERE user_id 123 AND order_status 1 AND create_time 2025-01-01order_status 是范围条件它后面的 create_time 就无法通过索引精确定位了因为 order_status 大于 1 的时候create_time 已经不是全局有序。MySQL 优化器只能在 order_status 的范围内把 create_time 当作普通过滤条件逐行判断。所以设计联合索引时要把最常用的等值条件放在前面范围查询字段放在靠后的位置才能让索引的每一列都发挥价值。2. 覆盖索引与回表优化实战2.1 什么是回表为什么会成为性能瓶颈先把 InnoDB 的存储机制讲透。InnoDB 表是聚簇索引结构主键索引的叶子节点直接存整行数据二级索引的叶子节点只存“索引列本身 主键值”。所以通过二级索引查到的记录如果查询还需要其他列就得拿着主键值再回主键索引里找完整行这个过程叫回表。我习惯用一个类比解释二级索引是书后面的关键词索引它告诉你某个词在哪几页你得翻到对应页去看正文。如果整段话都印在这份关键词索引里就不用翻正文了这就是覆盖索引。回表本身不是问题真正的问题是回表的次数。一个查询命中 100 条记录回表就要做 100 次随机 I/O每次都要沿着 B 树走一遍。如果查询结果集很大回表次数多慢查询就出现了。我曾经处理过一个报表接口服务端代码里用循环查了上万条数据再聚合每条都触发回表接口直接卡死几分钟。后来改成一条 SQL 配合覆盖索引一次搞定问题彻底消失。2.2 用覆盖索引改写慢查询以我之前优化过的一个订单列表页为例。业务 SQL 原本是这样SELECT order_no, order_status, pay_amount, create_time FROM orders WHERE user_id 123 AND order_status 1 ORDER BY create_time DESC LIMIT 10;这个查询会先通过 user_id 相关的索引找到一批记录然后回表取出 order_no、order_status、pay_amount、create_time再排序返回。问题出在回表上如果 user_id 下有几万条记录即使最终只返回 10 条中间过程也可能回表几万次。优化思路是做一个覆盖索引把查询涉及的列都放进去ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, order_status, create_time, order_no, pay_amount);加完之后explain 的 Extra 里出现了 Using index意思是查询需要的所有列都直接从索引拿到不用回表。同样的接口平均响应时间从 200ms 降到了 30ms 左右。但这里必须泼一盆冷水覆盖索引不是列加得越多越好。索引本身也是数据每次插入、更新、删除都要同步维护。如果为了覆盖索引把十几列都塞进去写入性能会明显下降binlog 也变大。我的经验是覆盖索引只用于高频列表查询而且只覆盖固定的业务列如果业务查询是 SELECT * 这种覆盖索引基本没用还是老老实实从回表和结果集大小入手优化。提示判断一个查询是不是覆盖索引就看 explain 的 Extra 里有没有 Using index。有这句说明索引扛起了所有需要的数据回表次数为零。3. 排序与深分页优化让 filesort 靠边站3.1 order by 走索引的条件排序是索引优化里很容易被忽略的一环。一个查询即使 WHERE 条件走了索引如果 ORDER BY 的字段不在索引里MySQL 还得专门做一次排序这就是 filesort。filesort 在数据量小的时候不一定慢但数据量大、sort_buffer 放不下时会产生临时文件和磁盘 I/O这时查询就会明显变慢。让排序走索引的核心思路是让 ORDER BY 字段和 WHERE 条件的字段共用同一个索引并且顺序一致。举例订单表经常按状态和时间排序SELECT order_id, amount FROM orders WHERE order_status 1 ORDER BY create_time DESC LIMIT 20;最合适的索引是 (order_status, create_time)先按状态定位状态相同的数据在索引里天然按 create_time 排好了序MySQL 直接顺序读取即可不需要额外 filesortexplain 的 Extra 里不会出现 Using filesort。如果索引是 (create_time, order_status)查询条件却是 ORDER BY create_time由于 create_time 是索引第一列而 WHERE 条件直接查 order_status跳过了第一列这个查询既难以高效过滤也排不了序。所以索引设计要结合 WHERE 和 ORDER BY 一起看本质上是“等值字段优先排序字段随后”。方向不一致也是个坑。ORDER BY create_time ASC 和 DESC 如果方向混用比如索引按 ASC 建查询要 DESC在 MySQL 8.0 之前优化器可能还是选择 filesort。MySQL 8.0 支持降序索引可以在建索引时直接指定 DESC 方向但实际工程里遇到这种情况我会先评估排序数据量数据量不大就安心用 filesort数据量大再考虑改造索引不用一见到 filesort 就慌。3.2 深分页优化LIMIT 100000, 10 为什么这么慢深分页的慢不在于返回 10 条数据而在于 MySQL 要把前 100010 条都翻一遍。比如 LIMIT 100000, 10即使有索引MySQL 也会从索引头部开始一路数到第 100000 条再往下取 10 条中间扫描的 10 万行是纯浪费的。我处理过一个实际案例后台订单列表用户翻到了很深的页码接口响应直接超时。优化方案有两个。第一个是延迟关联先只查主键再回表取完整行SELECT t1.id, t1.order_no, t1.amount FROM orders t1 JOIN ( SELECT id FROM orders WHERE order_status 1 ORDER BY create_time DESC LIMIT 100000, 10 ) t2 ON t1.id t2.id ORDER BY t1.create_time DESC;子查询里只取 id 列能走覆盖索引扫描 10 万行 id 的成本比取整行数据低得多拿到 10 个目标 id 后再去主表取完整数据回表次数只有 10 次。第二种是游标分页。业务上记住上一页最后一个 id 或时间下一页用 WHERE id ? ORDER BY id LIMIT 10 来取。这个方案本质上把“深分页”改成了“浅分页”不管翻多少页性能都稳定。我个人的倾向是列表页真的翻到几千上万页产品层面就该限制了。技术优化能兜底但体验和效率上限制最大页码、提供筛选条件才是更合理的产品解法。4. Explain 实战拿一条慢 SQL 怎么一步步看4.1 关键字段与执行计划解读拿到一条慢 SQL 之后我第一步永远是 EXPLAIN先把执行计划打出来。字段很多我认为最需要读懂的是 type、key、rows、Extra 这四个。type 表示访问类型从好到差大致是const eq_ref ref range index ALL。看到 const 或 eq_ref说明走的是主键或唯一索引性能基本没问题。ref 是普通索引等值匹配也不错。range 是范围扫描可以接受。index 是全索引扫描虽然不是全表扫描但也不理想。ALL 是全表扫描这是慢查询最常见的原因。我习惯把这几个等级整理成表格方便对比type含义建议const / eq_ref主键或唯一索引等值查询理想状态ref普通索引等值匹配性能不错range索引范围扫描可用但注意范围大小index全索引扫描需优化ALL全表扫描必须优化key 表示实际用到的索引。这里有个常见误区你以为建了索引就一定用得上实际上 explain 的 key 可能为 NULL说明优化器压根没选中这个索引。rows 是优化器估算的扫描行数注意是估算值但不同执行计划对比时参考价值很大。Extra 里的信息更丰富。出现 Using index 说明是覆盖索引这是加分项。Using where 表示取回记录后还要额外过滤通常是因为部分条件无法在索引层完成。Using filesort 是排序在索引之外进行的信号Using temporary 则意味着查询用了临时表通常和 GROUP BY、DISTINCT、子查询有关这类查询要格外警惕。我一般给出的建议是先确认 type 不是 ALL再确认 key 不为空最后看 Extra 有没有 Using filesort 或 Using temporary这三点都过关优化方向基本就明确了。4.2 key_len 计算判断联合索引到底用了几列很多人在 explain 里只看 type 和 rows忽略了 key_len其实 key_len 是判断联合索引命中哪些列的关键。key_len 表示 MySQL 在索引里实际使用到的字节数它由字段类型、字符集、是否允许 NULL、是否变长共同决定。计算方法拿最常见的 utf8mb4 举例。utf8mb4 下一个字符最多占 4 字节VARCHAR(20) 不是定长额外加 2 字节记录长度如果字段允许 NULL再加 1 字节。所以一个允许为 NULL 的 VARCHAR(20) 字段key_len 20 × 4 2 1 83。INT 类型的字段是 4 字节允许 NULL 再加 1 就是 5。DATETIME 是 8 字节允许 NULL 是 9。假设联合索引是 (user_id INT, order_status INT, create_time DATETIME)如果 explain 出来的 key_len 是 10说明只用了 user_id 和 order_status 两列各占 5 字节create_time 没有被索引命中。这样就能反推 SQL 的写法是不是触发了范围条件截断从而判断是改 SQL 还是改索引。这个技巧在 mysql 面试里也经常被当作进阶考点很多人知道联合索引最左前缀但不知道如何验证到底命中了几列key_len 就是回答这个问题的关键数据。4.3 字符集不一致等隐藏的坑索引失效有一个非常隐蔽的诱因字符集不一致。我之前排查过一个订单表关联用户表的查询订单表 user_id 是 utf8mb4用户表 user_id 是 utf8关联条件明明写得很正常explain 一看订单表这边直接 type 为 ALL。原因是 MySQL 在做关联时会把字符集不一致的字段隐式转换相当于对订单表的 user_id 做了转换操作索引就失效了。解决办法是统一库表字段的字符集一般推荐全库统一 utf8mb4。MySQL 8.0 里 utf8mb4 也是默认字符集兼容 emoji 和更多字符。另外OR 条件也是索引失效的高发地。WHERE user_id 1 OR user_id 2 这种简单情况优化器会尝试索引合并但 OR 关联的是不同字段比如 user_id 1 OR status 1两边各自有索引MySQL 8.0 会尝试 index merge但结果不一定理想。更稳的做法是拆成两个查询然后 UNION ALL让每个查询都稳定走自己的索引。LIKE 查询以 % 开头同样会让索引失效这个大家比较熟悉但要注意 LIKE abc% 是可以用索引的关键是通配符不能出现在最左侧。5. 索引运维与长期维护5.1 看起来合理实则低效的索引设计做了这么多年优化最常见的不是不会建索引而是索引建得太多太杂。我见过一张业务表有十六个索引其中大部分是不同同事在不同时期“看到慢查询就加一个”的结果。写入延迟明显上升磁盘占用也大而真正高频使用的只有三四个。这个问题可以用 pt-duplicate-key-checker 或者 sys.schema_redundant_indexes 视图来检查冗余索引。比如已经有了联合索引 (a, b)再单独建 (a) 就是冗余的因为 (a, b) 索引已经能覆盖以 a 为前缀的查询。另一种低效设计是给低区分度字段建索引。比如 status 字段一共只有两三个值数据分布严重不均。如果表里 90% 数据都是 status1查询 status1 时优化器大概率会放弃索引直接全表扫描因为这时全表扫反而更快。遇到这种字段单独建索引没有意义不如在查询条件里增加其他高区分度字段或者结合 (status, create_time) 这种联合索引让时间的范围条件来协助定位数据。5.2 线上加索引的正确姿势与统计信息维护线上业务高峰期直接 ALTER TABLE ADD INDEX 风险很大。MySQL 8.0 虽然对 DDL 做了不少优化但大表的 ALTER 操作依然可能长时间锁表尤其在有长事务存在的时候。工程上的标准做法是用 pt-online-schema-change 或者 gh-ost 这类在线变更工具通过创建临时表、同步增量数据、最后切换的流程把对业务的影响降到最低。我在千万级大表上加索引从来都是走这个路子不会直接执行原生 ALTER。另外优化器选择索引依赖统计信息。如果一张表的数据量发生过剧烈变化比如大批量删除或导入后统计信息没有及时更新优化器可能还用旧的统计信息选索引导致执行计划不理想。这时候执行 ANALYZE TABLE 更新统计信息往往能解决莫名奇妙的慢查询。这类问题排查起来很隐蔽因为表结构、索引、SQL 都没变只是数据量变化执行计划就飘了。我的习惯是大批量数据操作之后主动跑一次 ANALYZE TABLE并且用 sys.schema_unused_indexes 视图定期检查那些长期没有用到的索引该删就删。索引维护不是一次性的事而是和业务迭代同步持续的动态过程。这些年做索引优化我最深的一个体会是不要迷信“加索引能解决一切”。索引优化本质上是在读性能和写性能之间做权衡一个精心设计的联合索引能救活一条慢 SQL但设计不当的索引同样能把写入拖垮。真功夫都在 explain 和 key_len 这些细节里多跑几次真实案例比背一百条理论管用得多。
