mysql如何优化内存中临时表排序_mysql max_length_for_sort_data

多数情况下应调小max_length_for_sort_data，因其控制单行排序字段长度上限，设过大易误判内存充足导致实际落盘，设过小则强制两次扫描；真正影响排序内存的是sort_buffer_size。

临时表排序慢，max_length_for_sort_data 该调大还是调小？

多数情况下要调小，不是调大。mysql 在 ORDER BY 或 GROUP BY 时，如果内存中能放下所有排序字段+主键（或行指针），就走“优化排序”（single-pass）；否则退化为“两次扫描”（two-pass）：先排字段+主键，再回表取其余列——后者磁盘 IO 暴增，才是卡顿主因。

常见错误现象：SHOW PROFILE 显示大量 Copying to tmp table on disk，Created_tmp_disk_tables 值持续上涨，但 Created_tmp_tables 并不高。

• max_length_for_sort_data 默认值是 4096（字节），它限制的是「单行参与排序的字段总长度」，不是整个结果集大小
• 调得过大，容易让 MySQL 误判“能全放内存”，实际分配不足，最终仍落盘；调得太小，则强制走 two-pass，但至少避免 OOM 或内存碎片挤占其他操作
• 真正该优先调的是 sort_buffer_size（每个线程独享），它才决定排序内存池大小；max_length_for_sort_data 只是辅助判断策略的开关

怎么确认当前查询到底用了哪种排序模式？

靠 EXPLAIN format=json 最准。重点看 sort_mode 字段：

• <sort_key additional_fields></sort_key> → single-pass（理想状态）
• <sort_key rowid></sort_key> → two-pass（会回表，性能差）
• <sort_key packed_additional_fields></sort_key> → 启用了紧凑存储，但仍是 single-pass

别信 EXPLAIN 的 Extra 列里写的 “using filesort” —— 这词有误导性，它只表示用了排序，不区分内存还是磁盘。

实操建议：

• 在慢查询上执行 EXPLAIN FORMAT=JSON select ... ORDER BY ...
• 检查 sort_mode 和 rows（预估扫描行数），若 rows 很大但 sort_mode 是 rowid，大概率是 max_length_for_sort_data 设太高，或 sort_buffer_size 不够
• 用 SELECT @@sort_buffer_size, @@max_length_for_sort_data; 看当前会话值，注意它不继承全局，可能被客户端驱动重设

max_length_for_sort_data 和字符集、TEXT/BLOB 字段强相关

这个参数对可变长度字段极其敏感。比如一个 utf8mb4 的 VARCHAR(500)，最大可能占 2000 字节（4 × 500），哪怕实际只存 10 个字，MySQL 仍按上限算——很容易超默认 4096，直接触发 two-pass。

常见踩坑场景：

• 表里有 TEXT 字段参与 ORDER BY（哪怕只是 ORDER BY id，但 SELECT * 包含 TEXT）→ MySQL 会把整个 TEXT 长度计入排序行宽判断
• 用 utf8mb4 存邮箱、标题等字段，未加 COLLATE utf8mb4_0900_as_cs 等轻量校对规则，导致排序开销翻倍
• GROUP BY + SELECT * 时，隐式包含所有列，哪怕没显式排序，也可能触发排序逻辑

解决方向比调参更有效：

• 明确 SELECT 字段，删掉不用的 TEXT/BLOB
• 给排序字段单独建覆盖索引，避免排序本身发生（如 INDEX (status, created_at) 支持 WHERE status=1 ORDER BY created_at）
• 真要调参，建议从 1024 开始试，观察 sort_mode 是否切回 sort_key, additional_fields，而不是盲目拉到 8192

并发高时，sort_buffer_size 比 max_length_for_sort_data 更危险

sort_buffer_size 是每个连接独占内存，设成 4M，100 个并发连接就吃掉 400MB 物理内存；而 max_length_for_sort_data 只是整型阈值，不占内存。

所以线上调优顺序必须是：

• 先查 SHOW GLOBAL STATUS LIKE 'Threads_connected'; 和峰值并发数
• 再算 sort_buffer_size × Threads_connected 是否接近物理内存 20%～25%
• 若已逼近，宁可接受部分查询走 two-pass，也不要盲目加大 sort_buffer_size
• max_length_for_sort_data 可以按需微调（例如统一设成 512 或 1024），但它只是配合策略，不是性能杠杆

最常被忽略的一点：很多 ORM 自动生成 SELECT * + ORDER BY id，开发者以为只是按主键排，其实只要表结构里带大字段，MySQL 就不得不评估整行宽度——这时候删字段比调参管用十倍。