MySQL Sorts requiring temporary tables占比过高原因与优化方法

Sorts requiring temporary tables是MySQLTuner统计的核心性能指标，代表所有排序操作中，无法在单次内存排序缓冲区内完成、必须借助临时表中转才能完成排序的比例。当前你的实例该占比为73%，属于偏高的水平，但从统计数据看临时表落盘率为0%，说明这类临时表目前均驻留内存，尚未造成磁盘IO压力，但依然会带来额外的CPU、内存开销。

“未给ORDER BY字段加索引”只是触发该问题的原因之一，即便为排序字段添加了索引，以下场景依然会导致排序必须使用临时表：

• 索引与排序规则不匹配：联合索引字段顺序与ORDER BY字段顺序不一致、多个排序字段升降序混用且未建对应降序索引、排序字段不在查询使用的索引覆盖范围内时，索引自带的有序性无法被利用，只能走文件排序，待排序数据量超过排序缓冲区阈值时就会创建临时表。
• 查询逻辑导致必须先收集中间结果再排序：包括同时使用GROUP BY和ORDER BY且两个子句字段不匹配、JOIN查询的排序字段来自被驱动表、查询包含DISTINCT与ORDER BY组合等场景，MySQL必须先创建临时表存储聚合/关联/去重后的中间结果，再对中间结果做排序。
• 排序数据格式触发双路排序：查询结果包含TEXT/BLOB类型大字段、查询字段总长度超过max_length_for_sort_data阈值时，MySQL会采用双路排序逻辑，先提取排序键和行ID排序，再回表拉取完整数据，数据量较大时会借助临时表完成归并排序。
• 排序字段经过计算处理：ORDER BY后跟随函数、表达式（比如ORDER BY create_time + 0、ORDER BY IFNULL(score,0)）时，无法直接利用索引的有序性，只能全量取值计算后排序，极易触发临时表。
• 排序相关内存配置过小：当前实例sort_buffer_size、read_rnd_buffer_size均为默认256K，稍大的结果集就无法在单次排序缓冲区内容纳，必须拆分后用临时表做归并排序。
• 无索引关联查询拉高占比：当前统计显示有697次JOIN查询未使用索引，这类查询会生成大量无索引的中间结果集，对这类结果集排序几乎都会用到临时表，是拉高该指标占比的核心原因之一。

• 优先修复无索引JOIN问题：通过慢查询日志或performance_schema定位未走索引的关联SQL，为关联条件字段添加匹配的索引，从根源减少大中间结果集的生成。
• 优化索引适配排序逻辑：
  • 建联合索引时，字段顺序尽量对齐WHERE筛选、ORDER BY排序的字段顺序，多字段排序时尽量统一排序方向，确需混用升降序时建立对应的降序联合索引。
  • 高频关联排序场景，尽量让排序字段来自JOIN驱动表，或建立同时包含关联字段、查询字段、排序字段的覆盖索引，减少回表和中间结果生成。
• 合理调整内存配置（注意当前实例最大计算内存已超过物理内存，调整时严禁一次性设置过大值，避免OOM）：
  • 根据实际连接需求调低max_connections，从当前最高连接数仅7个的实际使用情况看，设置为30-50即可，大幅降低线程级内存的总开销。
  • 将sort_buffer_size逐步调整到2M-4M，read_rnd_buffer_size调整到1M左右，提升单次排序可容纳的数据量，减少临时表拆分。
  • 因实例几乎无MyISAM表使用，将key_buffer_size设置为0，释放不必要的内存占用。
  • 按建议将InnoDB单日志文件大小调整为256M，使总日志大小达到InnoDB缓冲池的25%，提升写入性能。
• 优化SQL写法：
  • 避免在ORDER BY字段上直接使用函数、做表达式计算，可将计算逻辑下推到应用层，或使用生成列加索引的方式支持排序需求。
  • 大表分页查询必须添加WHERE条件限定结果范围，禁止对全量无筛选的大表直接做排序。
  • 避免无意义的SELECT *写法，只查询业务实际需要的字段，减少排序时需要处理的数据总长度。

内容的提问来源于stack exchange，提问作者mgiuffrida