6.7.7 临时表与排序优化

临时表与排序优化#

导语#

本节聚焦 MySQL 临时表与排序机制的触发条件、监控定位、参数调优与排错实践。通过可执行示例、命令与练习帮助建立“可验证、可回退”的优化流程。

原理草图：临时表与排序路径#

临时表的类型与触发场景#

内存临时表：优先使用 MEMORY，受 tmp_table_size 与 max_heap_table_size 限制。
磁盘临时表：内存不足或含 BLOB/TEXT 时落盘；5.7+ 可能使用 InnoDB 作为临时表引擎。
常见触发：GROUP BY、DISTINCT、ORDER BY、UNION、子查询物化、无合适索引的排序/聚合。

监控与定位（含命令）#

1）查看全局临时表统计

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

预期输出示例：

Created_tmp_tables        12890
Created_tmp_disk_tables   349
Created_tmp_files         22

2）定位具体 SQL（慢查询 + EXPLAIN）

-- 慢查询日志开启（临时验证后记得回退）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;

-- 对可疑 SQL 进行执行计划分析
EXPLAIN SELECT col1, COUNT(*) 
FROM orders 
WHERE status='paid'
GROUP BY col1
ORDER BY COUNT(*) DESC;

关注 Extra 中的 Using temporary、Using filesort。

3）性能视图查看临时表情况（5.7+）

SELECT * 
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 5;

临时表优化策略（示例与效果对比）#

场景：GROUP BY + ORDER BY 触发临时表与排序

原始 SQL

SELECT user_id, COUNT(*) AS c
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id
ORDER BY c DESC
LIMIT 20;

优化思路
1. 建立组合索引减少临时表：

ALTER TABLE orders 
ADD INDEX idx_created_user (created_at, user_id);

覆盖索引减少回表：

ALTER TABLE orders 
ADD INDEX idx_created_user_cover (created_at, user_id);

使用 EXPLAIN 验证：

EXPLAIN SELECT user_id, COUNT(*) AS c
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id
ORDER BY c DESC
LIMIT 20;

预期效果
- Using temporary 消失或显著减少
- Created_tmp_disk_tables 降低

排序机制与 filesort#

单路排序：仅排序键，回表取数据（可能磁盘/内存）
双路排序：排序时携带行数据，避免回表但更耗内存
filesort 不一定落盘，受 sort_buffer_size 与结果集大小影响

排序优化策略（参数 + SQL 示例）#

1）索引顺序取数

-- 索引：status, created_at
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- SQL：先过滤再排序
SELECT id, status, created_at 
FROM orders 
WHERE status='paid'
ORDER BY created_at DESC 
LIMIT 50;

预期：使用 idx_status_created，避免 filesort

2）合理调整排序参数（会话级）

-- 会话级测试，避免全局风险
SET SESSION sort_buffer_size = 4*1024*1024;
SET SESSION read_rnd_buffer_size = 2*1024*1024;

-- 执行排序 SQL 并对比耗时
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000;

3）避免排序大字段

-- 避免 BLOB/TEXT 参与排序
SELECT id, created_at 
FROM orders 
ORDER BY created_at DESC 
LIMIT 1000;

排错清单（含命令与处理）#

问题 1：大量 Using temporary; Using filesort

EXPLAIN SELECT name, COUNT(*) 
FROM users 
GROUP BY name
ORDER BY COUNT(*) DESC;

处理：
- 为 name 建立索引或联合索引
- 避免函数包装列（GROUP BY LOWER(name)）

问题 2：Created_tmp_disk_tables 激增

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

处理：
- 优先优化 SQL/索引
- 小幅提升临时表内存上限（示例：从 16M 到 64M）

SET GLOBAL tmp_table_size = 64*1024*1024;
SET GLOBAL max_heap_table_size = 64*1024*1024;

问题 3：排序导致 CPU 飙高

SHOW PROCESSLIST;
-- 找到排序 SQL 后 EXPLAIN 分析

处理：增加过滤条件、减少返回列、控制 LIMIT

练习（可执行）#

练习 1：构造触发临时表的 SQL

CREATE TABLE t_demo (
  id INT PRIMARY KEY AUTO_INCREMENT,
  k INT,
  v VARCHAR(100),
  created_at DATETIME,
  INDEX idx_k (k)
);

INSERT INTO t_demo(k, v, created_at)
SELECT RAND()*1000, REPEAT('a', 50), NOW()
FROM information_schema.COLUMNS
LIMIT 2000;

EXPLAIN SELECT k, COUNT(*) 
FROM t_demo 
GROUP BY k 
ORDER BY COUNT(*) DESC;

观察 Using temporary 与 Using filesort。

练习 2：加索引减少临时表

ALTER TABLE t_demo ADD INDEX idx_k_created (k, created_at);

EXPLAIN SELECT k, COUNT(*) 
FROM t_demo 
WHERE created_at >= NOW() - INTERVAL 1 DAY
GROUP BY k 
ORDER BY COUNT(*) DESC;

对比 Extra 与执行耗时。

练习 3：调参验证

SET SESSION sort_buffer_size = 1*1024*1024;
SELECT * FROM t_demo ORDER BY created_at DESC LIMIT 1000;

SET SESSION sort_buffer_size = 8*1024*1024;
SELECT * FROM t_demo ORDER BY created_at DESC LIMIT 1000;

对比执行时间与 Created_tmp_disk_tables 的变化。

小结与实践建议#

优先通过索引与 SQL 改写降低临时表与排序开销，再进行参数调整。
避免盲目增大 sort_buffer_size，关注高并发内存风险。
将“临时表落盘率”“filesort 频次”纳入基线监控与容量规划。