上周分诊了一条慢SQLEXPLAIN一拉Extra列同时出现了Using filesort和Using temporary。看到这两个词基本就能断定order by 和 group by 的双重压力已经把这条查询压垮了。order by 慢慢在排序group by 慢慢在临时表。两条路到最后都会撞上同一个瓶颈磁盘IO。这篇文章不打算再重复加索引就完事这类正确但无用的废话。我会把 order by 和 group by 优化里真正值得抠的细节拆开讲filesort 怎么吃内存、临时表什么时候落盘、联合索引为什么能消除排序、group by 的松散索引扫描是什么以及一条带排序带分组带分页的报表SQL是怎么从3.6秒优化到0.19秒的。适合刚接手线上慢SQL的DBA也适合写业务SQL时总在犹豫到底要不要加索引的后端开发。1. 一条慢SQL里filesort和临时表是怎么吃资源的很多人对排序和分组的理解停留在慢就加索引这个层面但索引不是万能药。要真正优化得先搞清楚底层那两个耗资源的家伙是怎么工作的。1.1 filesort的两条路线内存排序与磁盘归并只要是ORDER BY无法直接利用索引的有序性MySQL 就必须自己排序这个过程就叫 filesort。它分两条路线内存排序排序数据量小于sort_buffer_size时直接在内存缓冲区里完成排序速度很快。磁盘归并排序排序数据量超过sort_buffer_sizeMySQL 会把数据切成多个分块写入磁盘临时文件然后对这些分块做归并排序。每一轮归并都是一次磁盘读写IO 开销非常大。sort_buffer_size默认只有256KB注意这里有个容易踩的坑这个参数是每个线程每次排序操作都要分配的内存不是全局的。并发上来以后你把sort_buffer_size从256KB调大到16MB内存压力立刻翻几十倍所以调参要特别克制不能看到排序慢就无脑加大。还有一个已经过时的参数叫max_length_for_sort_data在 MySQL 5.7.6 里被废弃8.0 已经移除。它的作用是决定排序采用单行排序还是元组排序排序行宽小时把整行数据放进 sort buffer排完直接返回不用回表行宽大时只排主键id和排序列排完再回表取整行。后者会引入大量随机IO所以早期资料会叫你把行宽调大。这套逻辑在旧库上确实有效但 MySQL 8.0 已经自动处理如果你还翻到2015年前后的文章照抄别怪我没提醒。1.2 group by的临时表从内存走向磁盘的过程GROUP BY的本质是分组聚合MySQL 在执行时需要用一张临时表来存放每个分组的状态。和排序一样它也有内存和磁盘两档内部临时表小于min(tmp_table_size, max_heap_table_size)时创建在内存里。一旦超过这个阈值临时表立即转为磁盘临时表internal_tmp_disk_storage_engine指定的引擎常见的是 InnoDB。可以这么理解内存临时表就像办公桌上的一张草稿纸写着写着写不下了就得搬到仓库里的台账本上继续。草稿纸随手翻很快台账本每次翻页都要跑一趟仓库速度天差地别。让我给一个具体的数值参考。tmp_table_size和max_heap_table_size默认都是16MB左右不同版本略有差异如果GROUP BY要聚合的中间结果超过这个量分批落盘这个偶发慢查询的根因就找到了。优化方向很简单要么让中间结果变少先用 WHERE 收紧范围要么让单个分组的行变窄只 SELECT 需要的列要么让分组走索引后面第三章详细说。1.3 用EXPLAIN的Extra列识别排序与分组的病灶很多人在优化第一步就走错了方向——不看执行计划直接凭感觉加索引。实际上EXPLAIN的Extra列已经把答案写在脸上了Extra 标识含义影响Using filesortORDER BY 无法用索引需要额外排序排序量大时拖垮性能Using temporary查询使用了临时表常见于 GROUP BY、DISTINCT、某些 JOINUsing index覆盖索引扫描无需回表好事尽量往这个方向靠Using index for group-by松散索引扫描group by 的理想态后面细讲如果 Extra 同时出现前两项基本可以认定这条 SQL 是排序 临时表双料慢查询。接下来要做的是顺着 WHERE 条件、GROUP BY 列、ORDER BY 列逐一分析能不能让排序字段走索引能不能让分组字段借助索引的有序性能不能用覆盖索引把临时表的行宽压到最窄不要只看 rows 这一列它只是优化器的估算值很多时候跟实际行数差十倍以上。更可靠的验证手段是 MySQL 8.0.18 之后提供的EXPLAIN ANALYZE它会真实执行 SQL并输出每个算子实际处理的行数和耗时这个我在第五章会演示。2. order by优化的核心让排序消失而不是让排序变快有个反直觉的结论ORDER BY的最优优化是让排序根本不发生。2.1 联合索引为什么能省掉filesortInnoDB 的索引本身是有序存储的。如果ORDER BY的列恰好和某个索引的排列顺序一致MySQL 沿着索引从头读到尾结果自然就是排好的根本不需要额外的排序动作。这里有一条极其重要的联合索引设计原则等值条件列放前面排序字段放后面。比如这样一张订单表CREATE TABLE order_info ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, KEY idx_user_createtime (user_id, create_time) ) ENGINEInnoDB;执行这条查询SELECT order_no, amount, create_time FROM order_info WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;优化器会走idx_user_createtime先在索引里定位user_id12345的所有记录因为create_time同时作为索引的第二列这些记录天然按 create_time 排好序直接顺序读取即可Extra 列不会出现 Using filesort。如果把索引设计成(create_time, user_id)同样一条 SQL虽然 WHERE 条件也能通过索引过滤但过滤出来的记录在 create_time 相同的情况下user_id 的先后顺序和索引排列不一致排序依然需要额外做。所以等值条件在前、排序字段在后不是经验之谈而是索引组织结构决定的必然选择。2.2 两个让排序索引失效的常见操作范围条件与函数包裹联合索引中范围条件之后的列无法继续用于排序。这是一个高频翻车点。-- 假设索引 (user_id, create_time) SELECT order_no, amount, create_time FROM order_info WHERE user_id 1000 ORDER BY create_time DESC LIMIT 20;user_id 上用了范围查询虽然 records 还是能从索引里读但create_time的全局有序性已经被打断不同 user_id 段之间的 create_time 是无序的优化器只能放弃索引排序退回 filesort。遇到这种场景要么等值条件替换范围条件要么单独建一个(create_time)索引让排序走另一条索引。注意这里我强调的是一条独立索引不是把原本的联合索引改成范围条件开头。还有一个更隐蔽的坑对排序字段做函数包裹。SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS pay_date FROM order_info ORDER BY pay_date只要排序表达式不是裸索引列比如DATE_FORMAT(create_time, ...)、YEAR(create_time)、amount 1优化器就无法利用索引的有序性filesort 基本跑不掉。处理办法在5.7之后可以用生成列generated column把表达式结果物化成新列再给这个新列建索引查询里直接按新列排序。2.3 覆盖索引排序之外的回表成本排序走索引只是第一步真正执行时还有一个隐藏开销——回表。每次从二级索引定位到主键再根据主键到聚簇索引取整行数据都是一次随机IO。如果结果集有几千上万行回表几千次慢查询照样出现。覆盖索引可以一次性解决排序和回表两个问题把查询要返回的列全部塞进索引索引扫描得到的记录本身就是完整答案连聚簇索引都不用碰。用前面的例子如果这个接口经常要按 user_id create_time 查询并返回 order_no、amount可以把索引改成ALTER TABLE order_info DROP INDEX idx_user_createtime, ADD INDEX idx_user_createtime_cover (user_id, create_time, order_no, amount);这时候执行计划里 Extra 会多一个Using index代表纯索引扫描。代价是索引变大、写入变慢所以覆盖索引要在高频慢查询上精打细算而不是每个列都塞进去索引列的宽度过大会直接把磁盘占用撑爆。2.4 降序索引与ORDER BY RAND的边界一个很容易被忽略的问题是排序方向。索引(user_id, create_time)只支持create_time ASC的顺序如果查询是ORDER BY create_time DESC在 MySQL 5.7 及以前优化器要么反向扫索引要么做 filesort。反向扫描在数据量上还没到无法接受的程度但混合场景就难办了。MySQL 8.0 开始支持降序索引ALTER TABLE order_info ADD INDEX idx_user_createtime_desc (user_id, create_time DESC);这样ORDER BY user_id ASC, create_time DESC就能完全匹配索引方向filesort 彻底消失。注意降序索引不是普通索引的反向而是真实按降序存储增加了索引的维护成本仅建议在排序方向单一且高频的路径上使用。至于ORDER BY RAND()不管怎么优化索引都没用——它是随机函数排序值在每条记录上都不一样索引的有序性对它没有意义。小表用一下无所谓大表要随机抽样建议先用WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t)))之类的方式取一个随机起点再按主键排序取若干行避免全表排列。3. group by优化索引扫描、临时表与聚合函数的取舍3.1 紧凑索引扫描与松散索引扫描GROUP BY在索引层面的利用程度直接决定了要不要动用临时表。MySQL 有两种索引分组扫描方式紧凑索引扫描tight index scan扫描分组所需的全部索引键对同一分组内的记录做聚合。它虽然省不了扫描但能利用索引的有序性至少不需要额外建临时表去重。松散索引扫描loose index scan利用索引的顺序性跳过组内不需要的键直接跳转到下一个分组的起始位置。Extra 列显示Using index for group-by这是 GROUP BY 最理想的状态。举例说明假设有索引(user_id, create_time)执行SELECT user_id, MIN(create_time), MAX(create_time) FROM order_info GROUP BY user_id;MySQL 会走松散索引扫描因为主键user_id已经有序每个分组只需要读取第一条和最后一条索引记录就能拿到 MIN 和 MAX根本不扫描组内其他记录也不建临时表。但如果 SQL 里再多查一个非索引列比如SELECT user_id, MIN(create_time), MAX(amount)松散扫描就失效了——因为 amount 不在索引里必须回表取所有行临时表逃不掉。松散索引扫描的限制非常严格聚合函数一般只能是 MIN、MAX查询列必须满足最左前缀WHERE 条件不能破坏分组顺序。日常碰到的大多数 GROUP BY走的还是紧凑扫描加临时表剩下部分才是设计问题。3.2 5.7以后GROUP BY不再自动帮你排序了很多老 MySQL 使用者有一个固守的记忆GROUP BY出来的结果天然是按分组字段排序的。这个行为在MySQL 5.7.5 之前确实是默认的但在 5.7.5 版本之后官方出于性能考虑移除了GROUP BY的隐式排序。现在如果你不显式写ORDER BY分组结果的顺序是不确定的。这带来的问题是双面的以前依赖隐式排序的旧业务升级到 5.7 后数据顺序变了可能引发 Bug从性能角度如果你根本不需要排序现在的执行计划反而省掉了一次额外 filesort是好事。所以写 SQL 时瞄一眼SELECT 里GROUP BY user_id之后真的需要排序吗不需要就千万别顺手加ORDER BY尤其别对聚合结果排序那会在临时表基础上再叠一层 filesort查询直接被拖垮。3.3 聚合函数的索引利用与count(distinct)的处理聚合函数对索引的利用程度差异很大COUNT(*)、MIN(col)、MAX(col)在走覆盖索引时效率极高MIN/MAX 甚至可以只取分组的第一条/最后一条记录。SUM(col)、AVG(col)必须把组内每条记录的 col 值取出来计算无法跳过。COUNT(DISTINCT col)最麻烦的聚合之一。它要把整个分组内的 col 值做去重统计临时表压力大很难走松散索引扫描。一个我在实践中用的处理手法是如果精确去重计数不是硬需求就用COUNT(DISTINCT)的近似替代。比如对日活用户这种大数据量统计可以在业务层面维护一张 UV 表用 HyperLogLog 之类的近似算法估算如果必须精确那就接受临时表但确保查询列尽量少并在 WHERE 阶段过滤掉尽可能多的行让临时表变瘦。永远不要对COUNT(DISTINCT)加 ORDER BY那是一场灾难。另外DISTINCT和GROUP BY在很多场景下可以互相改写。SELECT DISTINCT user_id FROM order_info和SELECT user_id FROM order_info GROUP BY user_id走索引时的执行路径几乎一样但注意DISTINCT后面跟多个列时索引设计完全按联合索引最左前缀来。3.4 临时表的行宽陷阱与参数兜底临时表慢除了行数多还有一个隐蔽因素是行宽。SELECT *GROUP BY会让临时表把整行所有字段都存下来行宽动辄几百字节内存临时表轻轻松松就触顶落盘。优化办法是只 SELECT 分组列和聚合列别贪心。参数层面兜底的思路是这样tmp_table_size和max_heap_table_size中较小的那个决定内存临时表上限。比如 tmp_table_size 设为64MB但 max_heap_table_size 还是默认16MB实际内存临时表超过16MB就会转型磁盘。要调就两个一起调并且根据业务并发量控制总体内存。MySQL 5.7 之后还有一条internal_tmp_disk_storage_engine磁盘临时表默认是 InnoDB 引擎必要时可以换成 MyISAM 获得更轻量的写入但代价是崩溃恢复能力下降这个操作一般不建议生产环境做。4. order by limit的深分页问题数据翻得越深回表撞得越疼4.1 深分页慢的本质随机IO次数随偏移量线性增长ORDER BY create_time DESC LIMIT 20000, 20这样的深分页语句表面看只需要返回20条实际执行却可能在索引上扫描 20020 条记录然后回表 20020 次。回表是典型的随机IO机械硬盘时代是致命伤SSD 上虽然好一些但 20 万偏移量以后同样扛不住。我用一个真实压测数据说明一张 3000 万行订单表LIMIT 100000, 20的耗时常在 1.5 秒到 3 秒之间LIMIT 1000000, 20直接飙到 8 秒以上。这不是索引没建对而是深分页本身的结构性缺陷。4.2 延迟关联与书签法改写最常用的改写方案是延迟关联子查询先在索引上定位主键再回到原表取数据。SQL 如下-- 优化前慢 SELECT order_no, amount, create_time FROM order_info WHERE status 2 ORDER BY create_time DESC LIMIT 20000, 20; -- 优化后快 SELECT t.order_no, t.amount, t.create_time FROM order_info t INNER JOIN ( SELECT id FROM order_info WHERE status 2 ORDER BY create_time DESC LIMIT 20000, 20 ) tmp ON t.id tmp.id ORDER BY t.create_time DESC;子查询里只取主键 id走(status, create_time, id)这样的覆盖索引完全不回表只扫描 20020 个主键外层再根据这 20 个主键回表取整行。回表次数从 20020 次降到了 20 次查询耗时通常下降 80% 以上。如果业务场景是下一页还有更极致的书签法。记住上一页最后一条记录的create_time下一页直接SELECT order_no, amount, create_time FROM order_info WHERE status 2 AND create_time 2024-06-30 23:59:59 ORDER BY create_time DESC LIMIT 20;跳过偏移量每次都按索引定位到书签位置性能恒定数据量再大也不怕。缺点是只适合持续往下翻的场景跳到第100页这种随机翻页没法用书签法。4.3 排序字段不连续时的分页边界问题深分页优化有个容易被忽略的副作用如果ORDER BY的字段在表里有大量重复值比如 create_time 精确到秒同一秒内可能有几百条订单那么按 create_time 排序 翻页会出现同一秒的数据在上一页和下一页重复或漏排。解决思路是在排序列后面补一个唯一性次级排序比如ORDER BY create_time DESC, id DESC保证排序序列完全确定。同时如果对排序字段做主键索引书签法也要同步带上 id 条件WHERE (create_time ?) OR (create_time ? AND id ?)。否则下一页的起点会因为重复键漂移导致数据错乱。5. 实战复盘一条3.6秒的渠道统计SQL压到0.19秒的完整过程前面的原理讲得再多没有一次完整的实战记录总是不够的。下面这个案例来自我线上处理过的一张报表表结构和业务场景做了脱敏但优化思路完全一致。5.1 拿到SQL后的第一件事EXPLAIN业务方报过来一条季度渠道统计SQL跑了 3.6 秒需要每半小时执行一次。原始SQL长这样SELECT channel, status, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE pay_time 2024-01-01 AND pay_time 2024-04-01 GROUP BY channel, status ORDER BY total_amount DESC LIMIT 10;表里有 3000 万行表结构里 pay_time 有一个单列索引。EXPLAIN 的结果很典型type: ALL根本没走 pay_time 索引rows: 29,880,000Extra: Using where; Using temporary; Using filesort三个标签叠加模型上已经清晰了全表扫描 临时表分组 对聚合结果排序。注意ORDER BY total_amount里的 total_amount 是聚合别名排序对象是临时表计算完的结果集合这部分必然 filesort。为什么 pay_time 有索引却不走根源在 GROUP BY 的列不是索引列优化器得全表扫描全部放进临时表等物化完再聚合再排序。对于这种 GROUP BY 和 WHERE 字段不匹配的场景只建单列索引没有意义。5.2 联合索引调整从先过滤转向先消除临时表第一版调整我建了联合索引ALTER TABLE order_info ADD INDEX idx_channel_status_paytime (channel, status, pay_time, amount);这个索引的高明之处在于GROUP BY channel, status直接利用索引的有序性在索引扫描过程中就完成了分组不需要额外的临时表WHERE pay_time的范围过滤在索引扫描时同步完成同时amount被包进索引里SUM(amount)直接在索引上算彻底避免回表。重新 EXPLAINtype 从 ALL 变成了 indexrows 降到 108 万季度数据量Extra 变成了Using where; Using index不再出现 Using temporary。执行时间从 3.6 秒降到了 0.9 秒左右。这里插一个细节优化器最终选择走这个联合索引是因为覆盖索引让扫描代价远低于回表版本。如果你把 amount 从索引里拿掉优化器评估回表成本后很可能走回全表扫描。5.3 排序改写与应用层兜底到这一步临时表被消灭了但ORDER BY total_amount DESC造成的 filesort 还在只是排序的输入从 3000 万行变成了 108 万分组的聚合结果代价已经降了一个数量级。不过聚合排序本身依然不便宜。我做的第二步改写是把这个排序直接拿掉让应用层来处理。理由很简单报表页面只展示 TOP 10应用层内存里对 108 万个分组结果排序取前10内存操作毫秒级而数据库层 filesort 要写临时文件IO开销不在一个数量级。改写后的SQLSELECT channel, status, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE pay_time 2024-01-01 AND pay_time 2024-04-01 GROUP BY channel, status;当然不是所有场景都能把排序挪到应用层这取决于结果集大小和网络传输成本。108 万行结果返回给应用层大约 10MB 左右内网传输可控这个业务能接受。如果结果集巨大或跨机房还是得在数据库层排序那就保留原始 SQL在已有的联合索引里再把 channel 和 status 的顺序调一下争取让聚合结果按channel, status, 聚合值的某个顺序输出。5.4 EXPLAIN ANALYZE验证最终执行计划最后用EXPLAIN ANALYZE验证MySQL 8.0.18EXPLAIN ANALYZE SELECT channel, status, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE pay_time 2024-01-01 AND pay_time 2024-04-01 GROUP BY channel, status;输出里能看到真实的执行耗时分布Table scan 阶段actual time0.014..120.4 rows1.08e6分组聚合阶段actual time0.016..185.3 rows1.08e6 loops1总耗时约190ms最终这套SQL稳定在 0.19 秒左右相比最初的 3.6 秒提升了约 19 倍。执行计划里没有 Using temporary没有 Using filesort只有 Using index。这个案例给了一个很重要的迁移经验GROUP BY 优化的突破口不在让分组计算变快而在让分组计算不需要临时表。当你把分组列和过滤列塞进同一个联合索引并让聚合函数在索引覆盖范围内完成绝大部分性能问题都自然消解。6. 别被优化骗了统计信息、参数与三个容易翻车的习惯6.1 优化器统计信息对排序计划的影响优化器决定走哪条索引、用不用 filesort依赖的是 InnoDB 的统计信息索引基数、行数估算。统计信息是采样评估出来的不是精确值一旦统计失真优化器就可能做出看似合理实则离谱的选择比如放着好好的索引不用偏要走全表扫描然后 filesort。一个我踩过的真实案例一张表的某个区分度很低的列比如 status 只有 0 和 2 两个值优化器统计后认为走索引要回表一多半的行不如全表扫描 排序结果全表扫了 1000 万行。但实际上真正命中的行数只有几百条因为统计信息把索引基数严重高估了。解决方案很简单定期ANALYZE TABLE order_info刷新统计信息。大表跑一次 ANALYZE 可能要几秒到几十秒注意避开业务高峰。MySQL 8.0 默认的innodb_stats_auto_recalc会自动做但只在变化量达到 10% 左右时触发某些慢增长场景下依然需要手动补一次。6.2 sort_buffer_size与tmp_table_size的边界这两组参数我在前面反复提过这里集中给一个调参思路参数默认值风险提示sort_buffer_size256KB每线程每排序分配别一次调太大tmp_table_size约16MB不能只调它要和 max_heap_table_size 一起max_heap_table_size约16MB内存临时表上限参考两者最小值max_length_for_sort_data已废弃8.0别再看旧资料瞎调实际生产中的做法是先把慢查询本身优化到不需要排序/临时表为止再谈调参。参数是兜底不是救命稻草。如果线上确实有大量无可避免的排序操作比如报表中心一次性汇总大量明细可以单独开一个连接池给这类任务配合SET SESSION sort_buffer_size8M只影响当前会话而不是全局改动。6.3 三个容易翻车的优化习惯第一个乱建冗余索引。很多开发看到ORDER BY慢就加索引看到GROUP BY慢又加一个最后一张表上五六个联合索引互相重叠写入性能直线下降。每次加索引前问自己这个索引能不能同时覆盖 WHERE 过滤、ORDER BY 排序、GROUP BY 分组、SELECT 需要的列一个索引至少同时解决两个问题才值得建。第二个只盯执行计划不看真实耗时。EXPLAIN 的 rows 是估算值可能偏离实际十倍。我用EXPLAIN ANALYZE抓过不少看起来走了索引、实际回表爆炸的案例。判断一个优化的真实效果必须对比优化前后的实际执行时间和资源消耗逻辑读、临时表磁盘写入量。第三个对排序字段做运算。排序字段一旦被函数包裹索引就废了。这个前面说过但实操中总有人踩。我的经验法则是ORDER BY 左边长得和表字段一模一样才可能走索引。同理GROUP BY 的表达式也要尽量避免运算需要日期截断之类的就用生成列预先物化。说到生成列顺便分享一个后续可以扩展的方向报表类查询完全不必追逐每一条实时聚合的极致性能。季度渠道统计这种场景更优雅的长期方案是一张预聚合汇总表每小时把上一小时的 channel、status、amount 累加进汇总表报表只查几十条汇总记录性能随数据量增长基本恒定。MySQL 8.0 也没有内置物化视图但业务侧定时任务实现起来成本并不高这是我处理高频报表慢查询时的最终兜底方案。写在最后order by 和 group by 的优化本质上是在跟 MySQL 的执行器博弈排序想让你走 filesort你就用索引秩序把它按回去分组想让你建临时表你就用联合索引让它在索引扫描中默默完成。掌握这几个手段之后再遇到带排序、带分组、带分页的报表SQL先别急着加索引把 EXPLAIN 拿出来看看 Using filesort 和 Using temporary 分别出现在哪一步对症下药大多数慢查询都能在毫秒级翻身。
order by与group by慢SQL优化：从filesort到索引利用
上周分诊了一条慢SQLEXPLAIN一拉Extra列同时出现了Using filesort和Using temporary。看到这两个词基本就能断定order by 和 group by 的双重压力已经把这条查询压垮了。order by 慢慢在排序group by 慢慢在临时表。两条路到最后都会撞上同一个瓶颈磁盘IO。这篇文章不打算再重复加索引就完事这类正确但无用的废话。我会把 order by 和 group by 优化里真正值得抠的细节拆开讲filesort 怎么吃内存、临时表什么时候落盘、联合索引为什么能消除排序、group by 的松散索引扫描是什么以及一条带排序带分组带分页的报表SQL是怎么从3.6秒优化到0.19秒的。适合刚接手线上慢SQL的DBA也适合写业务SQL时总在犹豫到底要不要加索引的后端开发。1. 一条慢SQL里filesort和临时表是怎么吃资源的很多人对排序和分组的理解停留在慢就加索引这个层面但索引不是万能药。要真正优化得先搞清楚底层那两个耗资源的家伙是怎么工作的。1.1 filesort的两条路线内存排序与磁盘归并只要是ORDER BY无法直接利用索引的有序性MySQL 就必须自己排序这个过程就叫 filesort。它分两条路线内存排序排序数据量小于sort_buffer_size时直接在内存缓冲区里完成排序速度很快。磁盘归并排序排序数据量超过sort_buffer_sizeMySQL 会把数据切成多个分块写入磁盘临时文件然后对这些分块做归并排序。每一轮归并都是一次磁盘读写IO 开销非常大。sort_buffer_size默认只有256KB注意这里有个容易踩的坑这个参数是每个线程每次排序操作都要分配的内存不是全局的。并发上来以后你把sort_buffer_size从256KB调大到16MB内存压力立刻翻几十倍所以调参要特别克制不能看到排序慢就无脑加大。还有一个已经过时的参数叫max_length_for_sort_data在 MySQL 5.7.6 里被废弃8.0 已经移除。它的作用是决定排序采用单行排序还是元组排序排序行宽小时把整行数据放进 sort buffer排完直接返回不用回表行宽大时只排主键id和排序列排完再回表取整行。后者会引入大量随机IO所以早期资料会叫你把行宽调大。这套逻辑在旧库上确实有效但 MySQL 8.0 已经自动处理如果你还翻到2015年前后的文章照抄别怪我没提醒。1.2 group by的临时表从内存走向磁盘的过程GROUP BY的本质是分组聚合MySQL 在执行时需要用一张临时表来存放每个分组的状态。和排序一样它也有内存和磁盘两档内部临时表小于min(tmp_table_size, max_heap_table_size)时创建在内存里。一旦超过这个阈值临时表立即转为磁盘临时表internal_tmp_disk_storage_engine指定的引擎常见的是 InnoDB。可以这么理解内存临时表就像办公桌上的一张草稿纸写着写着写不下了就得搬到仓库里的台账本上继续。草稿纸随手翻很快台账本每次翻页都要跑一趟仓库速度天差地别。让我给一个具体的数值参考。tmp_table_size和max_heap_table_size默认都是16MB左右不同版本略有差异如果GROUP BY要聚合的中间结果超过这个量分批落盘这个偶发慢查询的根因就找到了。优化方向很简单要么让中间结果变少先用 WHERE 收紧范围要么让单个分组的行变窄只 SELECT 需要的列要么让分组走索引后面第三章详细说。1.3 用EXPLAIN的Extra列识别排序与分组的病灶很多人在优化第一步就走错了方向——不看执行计划直接凭感觉加索引。实际上EXPLAIN的Extra列已经把答案写在脸上了Extra 标识含义影响Using filesortORDER BY 无法用索引需要额外排序排序量大时拖垮性能Using temporary查询使用了临时表常见于 GROUP BY、DISTINCT、某些 JOINUsing index覆盖索引扫描无需回表好事尽量往这个方向靠Using index for group-by松散索引扫描group by 的理想态后面细讲如果 Extra 同时出现前两项基本可以认定这条 SQL 是排序 临时表双料慢查询。接下来要做的是顺着 WHERE 条件、GROUP BY 列、ORDER BY 列逐一分析能不能让排序字段走索引能不能让分组字段借助索引的有序性能不能用覆盖索引把临时表的行宽压到最窄不要只看 rows 这一列它只是优化器的估算值很多时候跟实际行数差十倍以上。更可靠的验证手段是 MySQL 8.0.18 之后提供的EXPLAIN ANALYZE它会真实执行 SQL并输出每个算子实际处理的行数和耗时这个我在第五章会演示。2. order by优化的核心让排序消失而不是让排序变快有个反直觉的结论ORDER BY的最优优化是让排序根本不发生。2.1 联合索引为什么能省掉filesortInnoDB 的索引本身是有序存储的。如果ORDER BY的列恰好和某个索引的排列顺序一致MySQL 沿着索引从头读到尾结果自然就是排好的根本不需要额外的排序动作。这里有一条极其重要的联合索引设计原则等值条件列放前面排序字段放后面。比如这样一张订单表CREATE TABLE order_info ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, KEY idx_user_createtime (user_id, create_time) ) ENGINEInnoDB;执行这条查询SELECT order_no, amount, create_time FROM order_info WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;优化器会走idx_user_createtime先在索引里定位user_id12345的所有记录因为create_time同时作为索引的第二列这些记录天然按 create_time 排好序直接顺序读取即可Extra 列不会出现 Using filesort。如果把索引设计成(create_time, user_id)同样一条 SQL虽然 WHERE 条件也能通过索引过滤但过滤出来的记录在 create_time 相同的情况下user_id 的先后顺序和索引排列不一致排序依然需要额外做。所以等值条件在前、排序字段在后不是经验之谈而是索引组织结构决定的必然选择。2.2 两个让排序索引失效的常见操作范围条件与函数包裹联合索引中范围条件之后的列无法继续用于排序。这是一个高频翻车点。-- 假设索引 (user_id, create_time) SELECT order_no, amount, create_time FROM order_info WHERE user_id 1000 ORDER BY create_time DESC LIMIT 20;user_id 上用了范围查询虽然 records 还是能从索引里读但create_time的全局有序性已经被打断不同 user_id 段之间的 create_time 是无序的优化器只能放弃索引排序退回 filesort。遇到这种场景要么等值条件替换范围条件要么单独建一个(create_time)索引让排序走另一条索引。注意这里我强调的是一条独立索引不是把原本的联合索引改成范围条件开头。还有一个更隐蔽的坑对排序字段做函数包裹。SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS pay_date FROM order_info ORDER BY pay_date只要排序表达式不是裸索引列比如DATE_FORMAT(create_time, ...)、YEAR(create_time)、amount 1优化器就无法利用索引的有序性filesort 基本跑不掉。处理办法在5.7之后可以用生成列generated column把表达式结果物化成新列再给这个新列建索引查询里直接按新列排序。2.3 覆盖索引排序之外的回表成本排序走索引只是第一步真正执行时还有一个隐藏开销——回表。每次从二级索引定位到主键再根据主键到聚簇索引取整行数据都是一次随机IO。如果结果集有几千上万行回表几千次慢查询照样出现。覆盖索引可以一次性解决排序和回表两个问题把查询要返回的列全部塞进索引索引扫描得到的记录本身就是完整答案连聚簇索引都不用碰。用前面的例子如果这个接口经常要按 user_id create_time 查询并返回 order_no、amount可以把索引改成ALTER TABLE order_info DROP INDEX idx_user_createtime, ADD INDEX idx_user_createtime_cover (user_id, create_time, order_no, amount);这时候执行计划里 Extra 会多一个Using index代表纯索引扫描。代价是索引变大、写入变慢所以覆盖索引要在高频慢查询上精打细算而不是每个列都塞进去索引列的宽度过大会直接把磁盘占用撑爆。2.4 降序索引与ORDER BY RAND的边界一个很容易被忽略的问题是排序方向。索引(user_id, create_time)只支持create_time ASC的顺序如果查询是ORDER BY create_time DESC在 MySQL 5.7 及以前优化器要么反向扫索引要么做 filesort。反向扫描在数据量上还没到无法接受的程度但混合场景就难办了。MySQL 8.0 开始支持降序索引ALTER TABLE order_info ADD INDEX idx_user_createtime_desc (user_id, create_time DESC);这样ORDER BY user_id ASC, create_time DESC就能完全匹配索引方向filesort 彻底消失。注意降序索引不是普通索引的反向而是真实按降序存储增加了索引的维护成本仅建议在排序方向单一且高频的路径上使用。至于ORDER BY RAND()不管怎么优化索引都没用——它是随机函数排序值在每条记录上都不一样索引的有序性对它没有意义。小表用一下无所谓大表要随机抽样建议先用WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t)))之类的方式取一个随机起点再按主键排序取若干行避免全表排列。3. group by优化索引扫描、临时表与聚合函数的取舍3.1 紧凑索引扫描与松散索引扫描GROUP BY在索引层面的利用程度直接决定了要不要动用临时表。MySQL 有两种索引分组扫描方式紧凑索引扫描tight index scan扫描分组所需的全部索引键对同一分组内的记录做聚合。它虽然省不了扫描但能利用索引的有序性至少不需要额外建临时表去重。松散索引扫描loose index scan利用索引的顺序性跳过组内不需要的键直接跳转到下一个分组的起始位置。Extra 列显示Using index for group-by这是 GROUP BY 最理想的状态。举例说明假设有索引(user_id, create_time)执行SELECT user_id, MIN(create_time), MAX(create_time) FROM order_info GROUP BY user_id;MySQL 会走松散索引扫描因为主键user_id已经有序每个分组只需要读取第一条和最后一条索引记录就能拿到 MIN 和 MAX根本不扫描组内其他记录也不建临时表。但如果 SQL 里再多查一个非索引列比如SELECT user_id, MIN(create_time), MAX(amount)松散扫描就失效了——因为 amount 不在索引里必须回表取所有行临时表逃不掉。松散索引扫描的限制非常严格聚合函数一般只能是 MIN、MAX查询列必须满足最左前缀WHERE 条件不能破坏分组顺序。日常碰到的大多数 GROUP BY走的还是紧凑扫描加临时表剩下部分才是设计问题。3.2 5.7以后GROUP BY不再自动帮你排序了很多老 MySQL 使用者有一个固守的记忆GROUP BY出来的结果天然是按分组字段排序的。这个行为在MySQL 5.7.5 之前确实是默认的但在 5.7.5 版本之后官方出于性能考虑移除了GROUP BY的隐式排序。现在如果你不显式写ORDER BY分组结果的顺序是不确定的。这带来的问题是双面的以前依赖隐式排序的旧业务升级到 5.7 后数据顺序变了可能引发 Bug从性能角度如果你根本不需要排序现在的执行计划反而省掉了一次额外 filesort是好事。所以写 SQL 时瞄一眼SELECT 里GROUP BY user_id之后真的需要排序吗不需要就千万别顺手加ORDER BY尤其别对聚合结果排序那会在临时表基础上再叠一层 filesort查询直接被拖垮。3.3 聚合函数的索引利用与count(distinct)的处理聚合函数对索引的利用程度差异很大COUNT(*)、MIN(col)、MAX(col)在走覆盖索引时效率极高MIN/MAX 甚至可以只取分组的第一条/最后一条记录。SUM(col)、AVG(col)必须把组内每条记录的 col 值取出来计算无法跳过。COUNT(DISTINCT col)最麻烦的聚合之一。它要把整个分组内的 col 值做去重统计临时表压力大很难走松散索引扫描。一个我在实践中用的处理手法是如果精确去重计数不是硬需求就用COUNT(DISTINCT)的近似替代。比如对日活用户这种大数据量统计可以在业务层面维护一张 UV 表用 HyperLogLog 之类的近似算法估算如果必须精确那就接受临时表但确保查询列尽量少并在 WHERE 阶段过滤掉尽可能多的行让临时表变瘦。永远不要对COUNT(DISTINCT)加 ORDER BY那是一场灾难。另外DISTINCT和GROUP BY在很多场景下可以互相改写。SELECT DISTINCT user_id FROM order_info和SELECT user_id FROM order_info GROUP BY user_id走索引时的执行路径几乎一样但注意DISTINCT后面跟多个列时索引设计完全按联合索引最左前缀来。3.4 临时表的行宽陷阱与参数兜底临时表慢除了行数多还有一个隐蔽因素是行宽。SELECT *GROUP BY会让临时表把整行所有字段都存下来行宽动辄几百字节内存临时表轻轻松松就触顶落盘。优化办法是只 SELECT 分组列和聚合列别贪心。参数层面兜底的思路是这样tmp_table_size和max_heap_table_size中较小的那个决定内存临时表上限。比如 tmp_table_size 设为64MB但 max_heap_table_size 还是默认16MB实际内存临时表超过16MB就会转型磁盘。要调就两个一起调并且根据业务并发量控制总体内存。MySQL 5.7 之后还有一条internal_tmp_disk_storage_engine磁盘临时表默认是 InnoDB 引擎必要时可以换成 MyISAM 获得更轻量的写入但代价是崩溃恢复能力下降这个操作一般不建议生产环境做。4. order by limit的深分页问题数据翻得越深回表撞得越疼4.1 深分页慢的本质随机IO次数随偏移量线性增长ORDER BY create_time DESC LIMIT 20000, 20这样的深分页语句表面看只需要返回20条实际执行却可能在索引上扫描 20020 条记录然后回表 20020 次。回表是典型的随机IO机械硬盘时代是致命伤SSD 上虽然好一些但 20 万偏移量以后同样扛不住。我用一个真实压测数据说明一张 3000 万行订单表LIMIT 100000, 20的耗时常在 1.5 秒到 3 秒之间LIMIT 1000000, 20直接飙到 8 秒以上。这不是索引没建对而是深分页本身的结构性缺陷。4.2 延迟关联与书签法改写最常用的改写方案是延迟关联子查询先在索引上定位主键再回到原表取数据。SQL 如下-- 优化前慢 SELECT order_no, amount, create_time FROM order_info WHERE status 2 ORDER BY create_time DESC LIMIT 20000, 20; -- 优化后快 SELECT t.order_no, t.amount, t.create_time FROM order_info t INNER JOIN ( SELECT id FROM order_info WHERE status 2 ORDER BY create_time DESC LIMIT 20000, 20 ) tmp ON t.id tmp.id ORDER BY t.create_time DESC;子查询里只取主键 id走(status, create_time, id)这样的覆盖索引完全不回表只扫描 20020 个主键外层再根据这 20 个主键回表取整行。回表次数从 20020 次降到了 20 次查询耗时通常下降 80% 以上。如果业务场景是下一页还有更极致的书签法。记住上一页最后一条记录的create_time下一页直接SELECT order_no, amount, create_time FROM order_info WHERE status 2 AND create_time 2024-06-30 23:59:59 ORDER BY create_time DESC LIMIT 20;跳过偏移量每次都按索引定位到书签位置性能恒定数据量再大也不怕。缺点是只适合持续往下翻的场景跳到第100页这种随机翻页没法用书签法。4.3 排序字段不连续时的分页边界问题深分页优化有个容易被忽略的副作用如果ORDER BY的字段在表里有大量重复值比如 create_time 精确到秒同一秒内可能有几百条订单那么按 create_time 排序 翻页会出现同一秒的数据在上一页和下一页重复或漏排。解决思路是在排序列后面补一个唯一性次级排序比如ORDER BY create_time DESC, id DESC保证排序序列完全确定。同时如果对排序字段做主键索引书签法也要同步带上 id 条件WHERE (create_time ?) OR (create_time ? AND id ?)。否则下一页的起点会因为重复键漂移导致数据错乱。5. 实战复盘一条3.6秒的渠道统计SQL压到0.19秒的完整过程前面的原理讲得再多没有一次完整的实战记录总是不够的。下面这个案例来自我线上处理过的一张报表表结构和业务场景做了脱敏但优化思路完全一致。5.1 拿到SQL后的第一件事EXPLAIN业务方报过来一条季度渠道统计SQL跑了 3.6 秒需要每半小时执行一次。原始SQL长这样SELECT channel, status, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE pay_time 2024-01-01 AND pay_time 2024-04-01 GROUP BY channel, status ORDER BY total_amount DESC LIMIT 10;表里有 3000 万行表结构里 pay_time 有一个单列索引。EXPLAIN 的结果很典型type: ALL根本没走 pay_time 索引rows: 29,880,000Extra: Using where; Using temporary; Using filesort三个标签叠加模型上已经清晰了全表扫描 临时表分组 对聚合结果排序。注意ORDER BY total_amount里的 total_amount 是聚合别名排序对象是临时表计算完的结果集合这部分必然 filesort。为什么 pay_time 有索引却不走根源在 GROUP BY 的列不是索引列优化器得全表扫描全部放进临时表等物化完再聚合再排序。对于这种 GROUP BY 和 WHERE 字段不匹配的场景只建单列索引没有意义。5.2 联合索引调整从先过滤转向先消除临时表第一版调整我建了联合索引ALTER TABLE order_info ADD INDEX idx_channel_status_paytime (channel, status, pay_time, amount);这个索引的高明之处在于GROUP BY channel, status直接利用索引的有序性在索引扫描过程中就完成了分组不需要额外的临时表WHERE pay_time的范围过滤在索引扫描时同步完成同时amount被包进索引里SUM(amount)直接在索引上算彻底避免回表。重新 EXPLAINtype 从 ALL 变成了 indexrows 降到 108 万季度数据量Extra 变成了Using where; Using index不再出现 Using temporary。执行时间从 3.6 秒降到了 0.9 秒左右。这里插一个细节优化器最终选择走这个联合索引是因为覆盖索引让扫描代价远低于回表版本。如果你把 amount 从索引里拿掉优化器评估回表成本后很可能走回全表扫描。5.3 排序改写与应用层兜底到这一步临时表被消灭了但ORDER BY total_amount DESC造成的 filesort 还在只是排序的输入从 3000 万行变成了 108 万分组的聚合结果代价已经降了一个数量级。不过聚合排序本身依然不便宜。我做的第二步改写是把这个排序直接拿掉让应用层来处理。理由很简单报表页面只展示 TOP 10应用层内存里对 108 万个分组结果排序取前10内存操作毫秒级而数据库层 filesort 要写临时文件IO开销不在一个数量级。改写后的SQLSELECT channel, status, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE pay_time 2024-01-01 AND pay_time 2024-04-01 GROUP BY channel, status;当然不是所有场景都能把排序挪到应用层这取决于结果集大小和网络传输成本。108 万行结果返回给应用层大约 10MB 左右内网传输可控这个业务能接受。如果结果集巨大或跨机房还是得在数据库层排序那就保留原始 SQL在已有的联合索引里再把 channel 和 status 的顺序调一下争取让聚合结果按channel, status, 聚合值的某个顺序输出。5.4 EXPLAIN ANALYZE验证最终执行计划最后用EXPLAIN ANALYZE验证MySQL 8.0.18EXPLAIN ANALYZE SELECT channel, status, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE pay_time 2024-01-01 AND pay_time 2024-04-01 GROUP BY channel, status;输出里能看到真实的执行耗时分布Table scan 阶段actual time0.014..120.4 rows1.08e6分组聚合阶段actual time0.016..185.3 rows1.08e6 loops1总耗时约190ms最终这套SQL稳定在 0.19 秒左右相比最初的 3.6 秒提升了约 19 倍。执行计划里没有 Using temporary没有 Using filesort只有 Using index。这个案例给了一个很重要的迁移经验GROUP BY 优化的突破口不在让分组计算变快而在让分组计算不需要临时表。当你把分组列和过滤列塞进同一个联合索引并让聚合函数在索引覆盖范围内完成绝大部分性能问题都自然消解。6. 别被优化骗了统计信息、参数与三个容易翻车的习惯6.1 优化器统计信息对排序计划的影响优化器决定走哪条索引、用不用 filesort依赖的是 InnoDB 的统计信息索引基数、行数估算。统计信息是采样评估出来的不是精确值一旦统计失真优化器就可能做出看似合理实则离谱的选择比如放着好好的索引不用偏要走全表扫描然后 filesort。一个我踩过的真实案例一张表的某个区分度很低的列比如 status 只有 0 和 2 两个值优化器统计后认为走索引要回表一多半的行不如全表扫描 排序结果全表扫了 1000 万行。但实际上真正命中的行数只有几百条因为统计信息把索引基数严重高估了。解决方案很简单定期ANALYZE TABLE order_info刷新统计信息。大表跑一次 ANALYZE 可能要几秒到几十秒注意避开业务高峰。MySQL 8.0 默认的innodb_stats_auto_recalc会自动做但只在变化量达到 10% 左右时触发某些慢增长场景下依然需要手动补一次。6.2 sort_buffer_size与tmp_table_size的边界这两组参数我在前面反复提过这里集中给一个调参思路参数默认值风险提示sort_buffer_size256KB每线程每排序分配别一次调太大tmp_table_size约16MB不能只调它要和 max_heap_table_size 一起max_heap_table_size约16MB内存临时表上限参考两者最小值max_length_for_sort_data已废弃8.0别再看旧资料瞎调实际生产中的做法是先把慢查询本身优化到不需要排序/临时表为止再谈调参。参数是兜底不是救命稻草。如果线上确实有大量无可避免的排序操作比如报表中心一次性汇总大量明细可以单独开一个连接池给这类任务配合SET SESSION sort_buffer_size8M只影响当前会话而不是全局改动。6.3 三个容易翻车的优化习惯第一个乱建冗余索引。很多开发看到ORDER BY慢就加索引看到GROUP BY慢又加一个最后一张表上五六个联合索引互相重叠写入性能直线下降。每次加索引前问自己这个索引能不能同时覆盖 WHERE 过滤、ORDER BY 排序、GROUP BY 分组、SELECT 需要的列一个索引至少同时解决两个问题才值得建。第二个只盯执行计划不看真实耗时。EXPLAIN 的 rows 是估算值可能偏离实际十倍。我用EXPLAIN ANALYZE抓过不少看起来走了索引、实际回表爆炸的案例。判断一个优化的真实效果必须对比优化前后的实际执行时间和资源消耗逻辑读、临时表磁盘写入量。第三个对排序字段做运算。排序字段一旦被函数包裹索引就废了。这个前面说过但实操中总有人踩。我的经验法则是ORDER BY 左边长得和表字段一模一样才可能走索引。同理GROUP BY 的表达式也要尽量避免运算需要日期截断之类的就用生成列预先物化。说到生成列顺便分享一个后续可以扩展的方向报表类查询完全不必追逐每一条实时聚合的极致性能。季度渠道统计这种场景更优雅的长期方案是一张预聚合汇总表每小时把上一小时的 channel、status、amount 累加进汇总表报表只查几十条汇总记录性能随数据量增长基本恒定。MySQL 8.0 也没有内置物化视图但业务侧定时任务实现起来成本并不高这是我处理高频报表慢查询时的最终兜底方案。写在最后order by 和 group by 的优化本质上是在跟 MySQL 的执行器博弈排序想让你走 filesort你就用索引秩序把它按回去分组想让你建临时表你就用联合索引让它在索引扫描中默默完成。掌握这几个手段之后再遇到带排序、带分组、带分页的报表SQL先别急着加索引把 EXPLAIN 拿出来看看 Using filesort 和 Using temporary 分别出现在哪一步对症下药大多数慢查询都能在毫秒级翻身。