17370845950

减少临时表使用是MySQL查询优化的关键，需通过覆盖索引避免排序分组临时表、限制DISTINCT/UNION滥用、优化JOIN驱动表与索引、合理设置tmp_table_size等参数。

减少临时表使用是 MySQL 查询优化的关键环节。当查询无法直接通过索引完成排序、分组或去重时，MySQL 会创建内部临时表（可能在内存中，也可能落盘），这会显著拖慢查询速度，尤其在数据量大或并发高时。

用覆盖索引避免排序和分组临时表

ORDER BY 或 GROUP BY 涉及的字段如果不在同一个索引中，或索引不能“覆盖”查询所需列，MySQL 就可能建临时表。确保索引包含所有 ORDER BY / GROUP BY 列，并尽量延伸为覆盖索引（即包含 SELECT 中的所有列）。

• 错误示例：SELECT name, age FROM user WHERE city='Beijing' ORDER BY age;，若只有 (city) 索引，ORDER BY age 无法利用索引，易触发临时表
• 优化写法：建立联合索引 (city, age, name)，既支持 WHERE 过滤，又支持排序和回表取值
• 用 EXPLAIN 查看 Extra 列是否含 Using filesort 或 Using temporary，二者常相伴出现

限制 DISTINCT 和 UNION 的滥用

DISTINCT 和 UNION 默认会隐式触发临时表去重。若业务允许近似去重，可考虑用应用层 dedup；若必须去重，优先让去重字段走索引，或改用 EXISTS / IN 替代部分 DISTINCT 场景。

• SELECT DISTINCT user_id FROM order WHERE status=1; 若 (status, user_id) 有联合索引，可避免临时表
• UNION ALL 不去重，不建临时表；如确认结果无重复，优先用 UNION ALL 替代 UNION
• 避免 SELECT DISTINCT *，只选真正需要的字段，减少临时表的数据体积

控制连接方式与驱动表顺序

多表 JOIN 时，若被驱动表（非驱动表）的关联字段无索引，或连接条件无法走索引，MySQL 可能为中间结果建临时表缓存。确保所有 ON 条件字段都有合适索引，并让小结果集做驱动表。

• 用 EXPLAIN FORMAT=TREE（8.0+）或 EXPLAIN 的 rows 和 type 列判断实际扫描行数
• 对 LEFT JOIN，左表是驱动表，应保证其过滤后结果集尽量小；必要时用子查询提前缩小左表数据
• 避免在 JOIN 条件中对字段做函数操作，如 ON YEAR(t1.create_time) = YEAR(t2.create_time)，会导致索引失效并诱发临时表

调整 tmp_table_size 和 max_heap_table_size

这两个参数共同控制内存临时表的最大容量。若查询本可用内存临时表，但因设置过小而被迫落盘（变成 MyISAM 临时表），性能会断崖下降。

• 建议设为相等，例如 tmp_table_size = max_heap_table_size = 64M（根据可用内存合理设定，不宜超过物理内存的 1/4）
• 监控状态变量：SHOW GLOBAL STATUS LIKE 'Created_tmp%';，重点关注 Created_tmp_disk_tables，该值持续增长说明频繁落盘，需优化 SQL 或调参
• 注意：该调整仅缓解症状，不能替代索引和语句优化