“大喜这个分页查询不是走了主键索引吗为什么查最后几页耗时要 8 秒多MySQL 监控上的 CPU 怎么直接干到了 100%”周二上午负责 C 端订单列表的研发小哥拿着一段执行计划跑来找我。他的英短猫公仔还摆在电脑屏幕前脸上的表情比代码里的报错还要纠结。我扫了一眼他的 SQL 和EXPLAIN输出在Extra列赫然写着四个大字Using filesort。“小哥你对filesort有什么误解吗”我指着屏幕“这个词表面上叫‘文件排序’很多人以为它只是在内存里排个序或者顶多写个临时小文件。但实际上它是关系型数据库查询执行计划里最残暴的‘CPU 和 I/O 碎纸机’之一。”撕下面具filesort 究竟在干什么很多开发者望文生义以为filesort就是一定会把数据存进磁盘文件排序。错MySQL 官方定义里的filesort本质是指无法利用 B 树索引原本自带的天然有序性Ordered Leaf Pages必须在计算层开辟额外内存空间进行显式的全量排序算法。1. 两种排序模式双路排序Two-pass与单路排序Single-passMySQL 在执行filesort时会根据系统变量max_length_for_sort_data和查询字段的总长度选择两种截然不同的排序算法[模式 A双路排序 (Two-pass)] 1. 扫描满足 WHERE 条件的行只提取【主键 ID】与【排序字段 (Sort Key)】 2. 塞入内存中的 sort_buffer 进行快速排序 3. 排序完成后拿着排好序的主键 ID再次回表二次 I/O读取 SELECT 需要的其他所有字段 缺点极其严重的随机磁盘 I/O回表寻道 [模式 B单路排序 (Single-pass)] 1. 扫描满足 WHERE 条件的行把【SELECT 需要的全部字段】连同排序键一次性全部读入 sort_buffer 2. 在内存中直接按排序键完成全量数据行排序 3. 排序后直接输出零二次回表 缺点行体积庞大极其容易撑爆 sort_buffer_size2. 致命的连锁反应当 sort_buffer_size 被打穿如果单路排序提取的数据总行数乘以行宽超过了 MySQL 分配的sort_buffer_size比如默认的 256KB 或 1MBMySQL 就会被迫启动多路归并磁盘外排序External Merge Sort将填满的数据块在内存排好序后以临时分片文件形式溢写Spill到磁盘的tmpdir循环往复直到所有数据扫描完毕可能生成了几十甚至上百个临时磁盘小文件使用多路归并算法Merge-sort algorithm在磁盘和内存之间进行成百上千次的反复块读取、比对、合并在SHOW STATUS LIKE Sort_%中你会看到Sort_merge_passes数值像心电图一样狂飙。磁盘 IOPS 被吃满CPU 疯狂在内核态做页置换与上下文切换服务器负载瞬间崩塌。事故重现一条普通分页 SQL 如何引发血案我们来看让研发小哥崩溃的原版 SQL-- 表结构定义 CREATE TABLE t_mall_order ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, order_status TINYINT NOT NULL, order_amount DECIMAL(10, 2) NOT NULL, buyer_remark VARCHAR(255), create_time DATETIME NOT NULL, KEY idx_user_id (user_id) ) ENGINEInnoDB; -- 业务线上查询查询用户近期的已完成订单列表 SELECT id, user_id, order_status, order_amount, buyer_remark, create_time FROM t_mall_order WHERE user_id 8802914 AND order_status 3 ORDER BY create_time DESC LIMIT 1000, 20;为什么这里必然触发 filesort当前的索引是idx_user_id (user_id)。MySQL 执行引擎的操作逻辑是沿着idx_user_id索引树找到所有user_id 8802914的叶子节点每找到一个必须根据主键 ID 回表到聚集索引Clustered Index拉取整行数据在服务层内存比对order_status 3进行谓词过滤过滤出来的行其create_time是杂乱无章的因为索引只对user_id排序无法保证第三个维度的有序性只能将这几万条数据塞进sort_buffer进行惨烈的filesort根治方案最左匹配原则下的复合索引与覆盖索引设计消除filesort的最高境界是让排序操作在读取索引叶子节点的第一时间就已经物理完成根本不给计算引擎任何显式排序的机会。1. 构建符合执行序的联合索引Colocated Composite Index我们要牢牢遵循一个索引排序黄金法则等值查询在前排序字段紧随其后范围查询在最后。在本例中属于等值查询的字段是user_id和order_status属于排序需要的字段是create_time。我们重构索引结构-- 建立精准贴合查询骨架的三元联合索引 ALTER TABLE t_mall_order ADD INDEX idx_user_status_time (user_id, order_status, create_time);此时再看执行计划-------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | t_mall_order | NULL | ref | idx_user_status_time | idx_user_status_time | 9 | const,const | 1020 | 100.00 | NULL | --------------------------------------------------------------------------------------------------------------------------------------------看到了吗Extra里的Using filesort彻底消失了因为在idx_user_status_time这棵 B 树上所有user_id 8802914 AND order_status 3的叶子节点物理上原本就是按照create_time从小到大严格排列的执行引擎只需要反向双向链表扫描ORDER BY ... DESC直接按部就班取出前 1020 行即可CPU 排序计算开销直接降为 02. 进阶大招延迟关联Deferred Join彻底消灭大深分页回表虽然联合索引消除了filesort但如果遇到大深分页比如LIMIT 100000, 20MySQL 依然需要对前面 10 万行数据执行 10 万次无意义的聚集索引回表。我们可以利用**覆盖索引Covering Index**配合子查询延迟关联-- 优化后的大深分页极致写法先在二级纯索引上滚动最后精准回表 20 行 SELECT t.id, t.user_id, t.order_status, t.order_amount, t.buyer_remark, t.create_time FROM t_mall_order AS t INNER JOIN ( -- 覆盖索引驱动在这个子查询中只查主键 id无需回表纯内存零开销完成扫描 SELECT id FROM t_mall_order WHERE user_id 8802914 AND order_status 3 ORDER BY create_time DESC LIMIT 100000, 20 ) AS lim ON t.id lim.id;在内部驱动子查询中由于id是主键天然存在于idx_user_status_time二级索引的叶子节点中。子查询在Extra呈现Using index纯覆盖索引无回表。前 10 万条无用数据的磁盘回表被 100% 消除最终只有确定的 20 个主键去主键树上拉取详细列。查询耗时从原本的 8.4 秒直线暴降至 12 毫秒整整提速 700 倍性能避坑速查表在设计关系型数据库的排序架构时请在脑海里常驻这张“避坑自检清单”陷阱写法触发 filesort 的底层根源针对性根治手段WHERE a 10 ORDER BY b范围查询a 10打断了联合索引中b的物理有序性业务评估是否能改为等值查询或采用单列分段扫描WHERE a 1 ORDER BY b, c DESC排序字段的排序方向不一致一正一倒MySQL 8.0 必须使用降序索引(a, b ASC, c DESC)ORDER BY RAND()每一行都要调用随机数发生器并做临时全量排序业务层生成随机 ID 范围改用主键等值命中SELECT *导致字段过宽单路排序超出sort_buffer_size触发磁盘临时文件外排绝不 SELECT *严格按需指定列开启覆盖索引数据架构的精细度体现在对每一个字节流转和每一片存储页物理排布的敬畏中。不要把数据库看作万能计算黑盒善用索引的天然拓扑才能让你的系统在亿级流量面前举重若轻。