ORDER BY 本身不建临时表，但索引未配对时会触发磁盘临时表，进而卡住元数据锁（MDL），导致后续 DDL 或高并发查询被阻塞；其根本原因是 filesort 超出 sort_buffer_size 后写入磁盘临时文件 ibtmp1，该过程需持有 MDL，I/O 慢+数据量大显著延长锁持有时间。
ORDER BY 本身不建临时表，但索引没配对时会触发磁盘临时表，进而卡住元数据锁（MDL），导致后续 DDL 或高并发查询被阻塞。
为什么 ORDER BY 会悄悄创建临时表？
MySQL 在无法用索引完成排序时，会启用 filesort；若结果集超出 sort_buffer_size，就会把中间数据写入磁盘临时表（ibtmp1）。这个过程需要短暂持有目标表的元数据锁（MDL），而磁盘 I/O 慢 + 数据量大 = MDL 持有时间拉长 → 其他线程在等这张表的结构变更或查询就被挂起。
- 常见诱因：
EXPLAIN中出现Using filesort且rows值远大于实际返回行数 - 特别危险的是带
LIMIT的分页查询：SELECT * FROM t ORDER BY created_at LIMIT 10000,20，MySQL 仍要扫描并排序前 10000 行，锁范围和耗时都放大 - 临时表一旦落到磁盘，
SHOW PROCESSLIST的State常显示为Creating sort index，不是Sorting result
怎么验证是不是 ORDER BY 引发了临时表锁？
别只看 SHOW PROCESSLIST，它默认截断 SQL，Info 字段可能只显示 SELECT * FROM orders O...。必须用完整命令定位：
- 执行
SHOW FULL PROCESSLIST，找State = 'Creating sort index'或'Sending data'且Time> 30 秒的连接 - 对可疑 SQL 执行
EXPLAIN FORMAT=JSON，重点看sort_mode：如果是<sort_key, additional_fields>说明用了 sort buffer；若出现<sort_key, packed_additional_fields>或直接没sort_mode字段，大概率已落盘 - 查
performance_schema.data_locks，如果看到大量RECORD类型锁集中在排序字段值密集区间（比如created_at BETWEEN '2026-06-01' AND '2026-06-30'），基本坐实是排序扫行过多引发的锁扩散
临时表锁问题的实操修复路径
核心思路：让排序走索引免 filesort，同时控制扫描范围不膨胀。不是加个索引就行，顺序和方向必须严丝合缝：
- 索引字段顺序必须匹配查询逻辑：等值条件（
=、IN单值）放最左，范围条件（>、BETWEEN、LIKE 'abc%'）紧接，排序字段必须紧跟其后 —— 中间不能插其他字段 - 方向必须一致：MySQL 8.0+ 才支持
DESC显式声明，建索引时得写成INDEX (shop_id, created_at DESC)，不能只建(shop_id, created_at)然后ORDER BY created_at DESC - 避免函数和表达式：如
ORDER BY ABS(created_at)、ORDER BY DATE(created_at)必然失效，改用生成列 + 索引 - 临时表场景要额外注意：对
CREATE TEMPORARY TABLE ... AS SELECT的结果再ORDER BY，MySQL 不会复用源表索引。此时必须用子查询包装，强制外层排序：SELECT * FROM (SELECT id, name FROM temp_table) AS t ORDER BY name
容易被忽略的磁盘临时表残留风险
即使你杀掉了那个慢查询，ibtmp1 文件大小不会自动缩回 —— 它只会增长，不会收缩。955GB 的 ibtmp1 只能靠重启 MySQL 重建释放。所以线上环境务必提前设上限：
- 在
my.cnf中配置：innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G - 否则一个没审核的三表无关联条件 JOIN + ORDER BY，就能把磁盘打爆，且 DBA 还没法在线清理
