前言在MySQL执行SQL的过程中内部临时表是一个非常重要的优化机制——它是MySQL自动创建的“幕后英雄”用于存储查询的中间结果辅助复杂SQL的执行。但很多开发者对它的认知只停留在“EXPLAIN里的Using temporary”却不理解它什么时候会被触发、有什么类型、如何查看和优化。本文将从应用层面出发结合流程图、示例截图和实战SQL全面拆解MySQL内部临时表的核心知识帮你真正搞懂这个“看不见的表”。文章目录前言一、前置认知什么是内部临时表1.1 核心定义1.2 内部临时表 vs 用户临时表二、什么时候会触发内部临时表7大常见场景场景1UNION去重触发SQL示例对比UNION ALL场景2GROUP BY无合适索引或需要排序触发SQL示例无索引对比有合适索引场景3ORDER BY 不同列/多表JOIN触发SQL示例多表JOIN排序场景4DISTINCT ORDER BY触发SQL示例场景5FROM子句中的子查询派生表触发SQL示例场景6INSERT ... SELECT ...触发SQL示例场景7多表JOIN无合适索引触发SQL示例无JOIN索引三、内部临时表的两种类型MEMORY vs 磁盘临时表3.1 选择逻辑流程图3.2 两种类型的对比3.3 相关配置参数四、如何查看SQL是否使用了内部临时表2种方法方法1EXPLAIN查看执行计划最常用示例截图1EXPLAIN查看Using temporary方法2SHOW STATUS查看临时表统计查看命令结果示例解读五、优化建议如何减少内部临时表的使用优化1给GROUP BY、ORDER BY的列加合适的索引优化示例优化前无索引触发临时表优化后加索引不触发临时表优化2避免SELECT *只查需要的列优化示例优化前SELECT *触发磁盘临时表优化后只查需要的列用MEMORY临时表优化3避免在GROUP BY/ORDER BY中使用BLOB/TEXT列优化建议优化4调整tmp_table_size和max_heap_table_size参数配置示例my.cnf/my.ini注意事项优化5能用UNION ALL就不用UNION优化示例优化前UNION触发临时表优化后UNION ALL不触发临时表优化6优化子查询用JOIN替代FROM子句的子查询优化示例优化前子查询触发临时表优化后JOIN替代不触发临时表六、总结核心结论一、前置认知什么是内部临时表1.1 核心定义内部临时表是MySQL在执行复杂SQL时自动在内存或磁盘上创建的临时表用于存储查询的中间结果辅助SQL执行。它的特点是自动创建、自动删除用户感知不到SQL执行完后自动清理用户无法直接操作不能用SELECT * FROM 临时表查看也不能手动修改用于辅助查询是MySQL的一种优化手段让复杂SQL能分步执行。1.2 内部临时表 vs 用户临时表很多人会混淆“内部临时表”和“用户手动创建的临时表”我们用表格明确区分对比维度内部临时表用户临时表创建方式MySQL自动创建用户手动执行CREATE TEMPORARY TABLE可见性用户不可见当前会话可见删除时机SQL执行完自动删除会话结束自动删除或手动DROP TEMPORARY TABLE用途辅助MySQL执行复杂查询用户自己存储临时数据查看方式EXPLAIN的Using temporary、SHOW STATUS直接SHOW TABLES查看当前会话核心结论本文讲的是MySQL自动创建的内部临时表不是用户手动创建的临时表。二、什么时候会触发内部临时表7大常见场景内部临时表不是随便用的只有当SQL复杂到“无法一步执行”时MySQL才会创建它。我们整理了7大最常见的触发场景每个场景配简单的SQL示例场景1UNION去重UNION用于合并两个结果集并去重MySQL需要用临时表存储合并后的结果然后进行去重操作。触发SQL示例-- UNION需要去重会触发内部临时表SELECTid,usernameFROMuser_infoWHEREcity武汉UNIONSELECTid,usernameFROMuser_infoWHEREcity北京;对比UNION ALLUNION ALL只是简单合并结果集不需要去重通常不会触发内部临时表除非有其他复杂条件-- UNION ALL不需要去重通常不触发内部临时表SELECTid,usernameFROMuser_infoWHEREcity武汉UNIONALLSELECTid,usernameFROMuser_infoWHEREcity北京;场景2GROUP BY无合适索引或需要排序GROUP BY用于分组统计如果没有合适的索引或者需要对分组结果进行排序MySQL会用临时表存储分组后的中间结果。触发SQL示例无索引-- city列没有索引GROUP BY需要用临时表SELECTcity,COUNT(*)ASuser_countFROMuser_infoGROUPBYcity;对比有合适索引如果city列有联合索引idx_city_usernameMySQL可以直接用索引分组不需要临时表-- city有索引GROUP BY直接用索引不触发临时表SELECTcity,COUNT(*)ASuser_countFROMuser_infoGROUPBYcity;场景3ORDER BY 不同列/多表JOIN如果ORDER BY的列和SELECT的列不一致或者是多表JOIN后的排序MySQL需要用临时表存储中间结果然后排序。触发SQL示例多表JOIN排序-- 多表JOIN后按订单时间排序会触发内部临时表SELECTo.order_no,u.usernameFROMorder_info oJOINuser_info uONo.user_idu.idORDERBYo.create_timeDESC;场景4DISTINCT ORDER BY单独的DISTINCT可能不会触发临时表但如果DISTINCT和ORDER BY结合且排序的列和去重的列不一致MySQL会用临时表。触发SQL示例-- DISTINCT去重username然后按create_time排序会触发临时表SELECTDISTINCTusernameFROMuser_infoORDERBYcreate_timeDESC;场景5FROM子句中的子查询派生表如果在FROM子句中写了子查询也就是派生表MySQL会把这个子查询的结果存入内部临时表然后外层查询再从这个临时表中取数据。触发SQL示例-- FROM子句中的子查询派生表会触发内部临时表SELECT*FROM(SELECTid,username,cityFROMuser_infoWHEREage25)ASyoung_user;场景6INSERT … SELECT …在执行INSERT ... SELECT ...时如果SELECT的结果集比较复杂或者需要对结果进行处理MySQL会用临时表存储SELECT的结果然后再插入。触发SQL示例-- INSERT ... SELECT ...的SELECT部分复杂会触发内部临时表INSERTINTOuser_backup(id,username,phone)SELECTid,username,phoneFROMuser_infoWHEREcreate_time2026-01-01;场景7多表JOIN无合适索引如果多表JOIN时没有合适的JOIN索引MySQL需要用临时表存储中间结果辅助JOIN操作。触发SQL示例无JOIN索引-- order_info.user_id没有索引JOIN需要用临时表SELECTo.*,u.usernameFROMorder_info oJOINuser_info uONo.user_idu.id;三、内部临时表的两种类型MEMORY vs 磁盘临时表内部临时表有两种存储方式MEMORY内存临时表和磁盘临时表MySQL 5.7默认MyISAMMySQL 8.0默认InnoDB。MySQL会根据表结构和配置自动选择。3.1 选择逻辑流程图我们用一张流程图展示MySQL选择存储引擎的逻辑【流程图1内部临时表存储引擎选择逻辑】开始 ↓ SQL需要内部临时表 ↓ 是 检查临时表结构 ↓ 是否包含BLOB/TEXT列 ↓ 是 → 使用磁盘临时表 ↓ 否 检查行长度是否超过限制 限制 min(max_heap_table_size, tmp_table_size) ↓ 是 → 使用磁盘临时表 ↓ 否 使用MEMORY内存临时表 ↓ MEMORY临时表满了 ↓ 是 → 转换为磁盘临时表 ↓ 否 继续使用MEMORY临时表3.2 两种类型的对比对比维度MEMORY内存临时表磁盘临时表MyISAM/InnoDB存储位置内存磁盘tmpdir参数指定的目录性能极高内存读写较低磁盘IO触发条件无BLOB/TEXT、行长度不超限有BLOB/TEXT、行长度超限、MEMORY满了索引支持只支持哈希索引支持B树索引MySQL 8.0默认引擎MEMORYInnoDB可通过internal_tmp_disk_storage_engine配置3.3 相关配置参数我们可以通过调整以下参数让更多临时表使用MEMORY存储提升性能参数名含义默认值建议值tmp_table_size单个MEMORY临时表的最大大小1677721616MB根据服务器内存调整64M-256Mmax_heap_table_sizeMEMORY引擎表的最大大小1677721616MB和tmp_table_size设为一样的值internal_tmp_disk_storage_engine磁盘临时表的存储引擎MySQL 8.0InnoDB保持默认InnoDB即可tmpdir磁盘临时表的存储目录/tmpLinux或C:\Windows\TempWindows建议改为高速SSD目录四、如何查看SQL是否使用了内部临时表2种方法我们可以通过两种方法查看SQL是否触发了内部临时表以及触发的是MEMORY还是磁盘临时表。方法1EXPLAIN查看执行计划最常用用EXPLAIN查看SQL的执行计划重点看Extra列如果Extra列有**Using temporary**说明触发了内部临时表如果同时有Using filesort说明还触发了文件排序性能更差。示例截图1EXPLAIN查看Using temporary【示例截图1EXPLAIN查看Using temporary】*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000000 filtered: 100.00 Extra: Using temporary; Using filesort解读Extra列有Using temporary说明触发了内部临时表同时有Using filesort说明还触发了文件排序。方法2SHOW STATUS查看临时表统计用SHOW STATUS查看MySQL创建的临时表数量对比执行SQL前后的变化查看命令-- 查看临时表统计SHOWSTATUSLIKECreated_tmp%;结果示例【示例截图2SHOW STATUS查看临时表统计】-------------------------------- | Variable_name | Value | -------------------------------- | Created_tmp_disk_tables | 10 | -- 磁盘临时表的创建数量 | Created_tmp_tables | 100 | -- 内部临时表的总创建数量MEMORY磁盘 --------------------------------解读Created_tmp_tables内部临时表的总创建次数Created_tmp_disk_tables磁盘临时表的创建次数优化目标尽量降低Created_tmp_disk_tables的占比让更多临时表用MEMORY存储。五、优化建议如何减少内部临时表的使用内部临时表是MySQL的优化手段但过度使用磁盘临时表会严重影响性能因为磁盘IO比内存IO慢1000倍以上。我们可以通过以下6个方法减少内部临时表的使用尤其是磁盘临时表的使用优化1给GROUP BY、ORDER BY的列加合适的索引这是最有效的优化方法——如果GROUP BY、ORDER BY的列有合适的索引MySQL可以直接用索引完成分组或排序不需要临时表。优化示例优化前无索引触发临时表-- city列无索引GROUP BY触发临时表EXPLAINSELECTcity,COUNT(*)ASuser_countFROMuser_infoGROUPBYcity;-- Extra: Using temporary; Using filesort优化后加索引不触发临时表-- 给city列加联合索引ALTERTABLEuser_infoADDINDEXidx_city(city);-- 再次EXPLAINEXPLAINSELECTcity,COUNT(*)ASuser_countFROMuser_infoGROUPBYcity;-- Extra: NULL没有Using temporary了优化2避免SELECT *只查需要的列SELECT *会查询所有列包括大字段比如TEXT、BLOB这会导致行长度超过tmp_table_size限制被迫用磁盘临时表临时表包含不必要的列浪费内存和磁盘空间。优化示例优化前SELECT *触发磁盘临时表-- SELECT *包含contentTEXT列触发磁盘临时表SELECT*FROMuser_infoGROUPBYcity;优化后只查需要的列用MEMORY临时表-- 只查需要的列不包含TEXT列用MEMORY临时表SELECTid,username,cityFROMuser_infoGROUPBYcity;优化3避免在GROUP BY/ORDER BY中使用BLOB/TEXT列BLOB/TEXT列的长度不固定只要临时表包含BLOB/TEXT列MySQL直接用磁盘临时表不会用MEMORY。优化建议尽量不要在GROUP BY、ORDER BY、DISTINCT中使用BLOB/TEXT列如果必须用可以先把BLOB/TEXT列的查询放到外层内层查询只查需要的小字段。优化4调整tmp_table_size和max_heap_table_size参数如果临时表经常因为“行长度超限”被迫用磁盘临时表可以适当调大这两个参数让更多临时表能用MEMORY存储。配置示例my.cnf/my.ini[mysqld] # 临时表最大大小64MB tmp_table_size 67108864 max_heap_table_size 67108864注意事项两个参数必须设为一样的值不要设得太大比如超过1GB否则会占用过多内存影响其他业务调整后重启MySQL服务生效。优化5能用UNION ALL就不用UNIONUNION需要去重会触发临时表UNION ALL不需要去重通常不会触发临时表。如果业务上不需要去重优先用UNION ALL。优化示例优化前UNION触发临时表-- UNION需要去重触发临时表SELECTid,usernameFROMuser_infoWHEREcity武汉UNIONSELECTid,usernameFROMuser_infoWHEREcity北京;优化后UNION ALL不触发临时表-- 业务不需要去重用UNION ALL不触发临时表SELECTid,usernameFROMuser_infoWHEREcity武汉UNIONALLSELECTid,usernameFROMuser_infoWHEREcity北京;优化6优化子查询用JOIN替代FROM子句的子查询FROM子句的子查询派生表一定会触发内部临时表尽量用JOIN替代减少临时表的使用。优化示例优化前子查询触发临时表-- FROM子句的子查询触发临时表SELECT*FROM(SELECTid,username,cityFROMuser_infoWHEREage25)ASyoung_user;优化后JOIN替代不触发临时表-- 用JOIN替代子查询不触发临时表SELECTid,username,cityFROMuser_infoWHEREage25;六、总结MySQL内部临时表是一把双刃剑好的一面它是MySQL的优化手段帮助复杂SQL分步执行坏的一面过度使用磁盘临时表会严重影响性能磁盘IO瓶颈。核心结论触发场景UNION、无索引的GROUP BY、ORDER BYJOIN、DISTINCTORDER BY、FROM子查询、INSERT…SELECT…、无索引的JOIN两种类型MEMORY内存性能高和磁盘临时表MyISAM/InnoDB性能低查看方法EXPLAIN看Using temporarySHOW STATUS看Created_tmp%优化方法加合适的索引、避免SELECT *、避免BLOB/TEXT列、调整tmp_table_size、用UNION ALL替代UNION、用JOIN替代子查询。希望这篇图文详解能帮你彻底搞懂MySQL内部临时表通过优化SQL和索引减少磁盘临时表的使用提升系统性能
MySQL内部临时表图文详解：从触发场景到优化实践
发布时间：2026/6/17 3:58:07
前言在MySQL执行SQL的过程中内部临时表是一个非常重要的优化机制——它是MySQL自动创建的“幕后英雄”用于存储查询的中间结果辅助复杂SQL的执行。但很多开发者对它的认知只停留在“EXPLAIN里的Using temporary”却不理解它什么时候会被触发、有什么类型、如何查看和优化。本文将从应用层面出发结合流程图、示例截图和实战SQL全面拆解MySQL内部临时表的核心知识帮你真正搞懂这个“看不见的表”。文章目录前言一、前置认知什么是内部临时表1.1 核心定义1.2 内部临时表 vs 用户临时表二、什么时候会触发内部临时表7大常见场景场景1UNION去重触发SQL示例对比UNION ALL场景2GROUP BY无合适索引或需要排序触发SQL示例无索引对比有合适索引场景3ORDER BY 不同列/多表JOIN触发SQL示例多表JOIN排序场景4DISTINCT ORDER BY触发SQL示例场景5FROM子句中的子查询派生表触发SQL示例场景6INSERT ... SELECT ...触发SQL示例场景7多表JOIN无合适索引触发SQL示例无JOIN索引三、内部临时表的两种类型MEMORY vs 磁盘临时表3.1 选择逻辑流程图3.2 两种类型的对比3.3 相关配置参数四、如何查看SQL是否使用了内部临时表2种方法方法1EXPLAIN查看执行计划最常用示例截图1EXPLAIN查看Using temporary方法2SHOW STATUS查看临时表统计查看命令结果示例解读五、优化建议如何减少内部临时表的使用优化1给GROUP BY、ORDER BY的列加合适的索引优化示例优化前无索引触发临时表优化后加索引不触发临时表优化2避免SELECT *只查需要的列优化示例优化前SELECT *触发磁盘临时表优化后只查需要的列用MEMORY临时表优化3避免在GROUP BY/ORDER BY中使用BLOB/TEXT列优化建议优化4调整tmp_table_size和max_heap_table_size参数配置示例my.cnf/my.ini注意事项优化5能用UNION ALL就不用UNION优化示例优化前UNION触发临时表优化后UNION ALL不触发临时表优化6优化子查询用JOIN替代FROM子句的子查询优化示例优化前子查询触发临时表优化后JOIN替代不触发临时表六、总结核心结论一、前置认知什么是内部临时表1.1 核心定义内部临时表是MySQL在执行复杂SQL时自动在内存或磁盘上创建的临时表用于存储查询的中间结果辅助SQL执行。它的特点是自动创建、自动删除用户感知不到SQL执行完后自动清理用户无法直接操作不能用SELECT * FROM 临时表查看也不能手动修改用于辅助查询是MySQL的一种优化手段让复杂SQL能分步执行。1.2 内部临时表 vs 用户临时表很多人会混淆“内部临时表”和“用户手动创建的临时表”我们用表格明确区分对比维度内部临时表用户临时表创建方式MySQL自动创建用户手动执行CREATE TEMPORARY TABLE可见性用户不可见当前会话可见删除时机SQL执行完自动删除会话结束自动删除或手动DROP TEMPORARY TABLE用途辅助MySQL执行复杂查询用户自己存储临时数据查看方式EXPLAIN的Using temporary、SHOW STATUS直接SHOW TABLES查看当前会话核心结论本文讲的是MySQL自动创建的内部临时表不是用户手动创建的临时表。二、什么时候会触发内部临时表7大常见场景内部临时表不是随便用的只有当SQL复杂到“无法一步执行”时MySQL才会创建它。我们整理了7大最常见的触发场景每个场景配简单的SQL示例场景1UNION去重UNION用于合并两个结果集并去重MySQL需要用临时表存储合并后的结果然后进行去重操作。触发SQL示例-- UNION需要去重会触发内部临时表SELECTid,usernameFROMuser_infoWHEREcity武汉UNIONSELECTid,usernameFROMuser_infoWHEREcity北京;对比UNION ALLUNION ALL只是简单合并结果集不需要去重通常不会触发内部临时表除非有其他复杂条件-- UNION ALL不需要去重通常不触发内部临时表SELECTid,usernameFROMuser_infoWHEREcity武汉UNIONALLSELECTid,usernameFROMuser_infoWHEREcity北京;场景2GROUP BY无合适索引或需要排序GROUP BY用于分组统计如果没有合适的索引或者需要对分组结果进行排序MySQL会用临时表存储分组后的中间结果。触发SQL示例无索引-- city列没有索引GROUP BY需要用临时表SELECTcity,COUNT(*)ASuser_countFROMuser_infoGROUPBYcity;对比有合适索引如果city列有联合索引idx_city_usernameMySQL可以直接用索引分组不需要临时表-- city有索引GROUP BY直接用索引不触发临时表SELECTcity,COUNT(*)ASuser_countFROMuser_infoGROUPBYcity;场景3ORDER BY 不同列/多表JOIN如果ORDER BY的列和SELECT的列不一致或者是多表JOIN后的排序MySQL需要用临时表存储中间结果然后排序。触发SQL示例多表JOIN排序-- 多表JOIN后按订单时间排序会触发内部临时表SELECTo.order_no,u.usernameFROMorder_info oJOINuser_info uONo.user_idu.idORDERBYo.create_timeDESC;场景4DISTINCT ORDER BY单独的DISTINCT可能不会触发临时表但如果DISTINCT和ORDER BY结合且排序的列和去重的列不一致MySQL会用临时表。触发SQL示例-- DISTINCT去重username然后按create_time排序会触发临时表SELECTDISTINCTusernameFROMuser_infoORDERBYcreate_timeDESC;场景5FROM子句中的子查询派生表如果在FROM子句中写了子查询也就是派生表MySQL会把这个子查询的结果存入内部临时表然后外层查询再从这个临时表中取数据。触发SQL示例-- FROM子句中的子查询派生表会触发内部临时表SELECT*FROM(SELECTid,username,cityFROMuser_infoWHEREage25)ASyoung_user;场景6INSERT … SELECT …在执行INSERT ... SELECT ...时如果SELECT的结果集比较复杂或者需要对结果进行处理MySQL会用临时表存储SELECT的结果然后再插入。触发SQL示例-- INSERT ... SELECT ...的SELECT部分复杂会触发内部临时表INSERTINTOuser_backup(id,username,phone)SELECTid,username,phoneFROMuser_infoWHEREcreate_time2026-01-01;场景7多表JOIN无合适索引如果多表JOIN时没有合适的JOIN索引MySQL需要用临时表存储中间结果辅助JOIN操作。触发SQL示例无JOIN索引-- order_info.user_id没有索引JOIN需要用临时表SELECTo.*,u.usernameFROMorder_info oJOINuser_info uONo.user_idu.id;三、内部临时表的两种类型MEMORY vs 磁盘临时表内部临时表有两种存储方式MEMORY内存临时表和磁盘临时表MySQL 5.7默认MyISAMMySQL 8.0默认InnoDB。MySQL会根据表结构和配置自动选择。3.1 选择逻辑流程图我们用一张流程图展示MySQL选择存储引擎的逻辑【流程图1内部临时表存储引擎选择逻辑】开始 ↓ SQL需要内部临时表 ↓ 是 检查临时表结构 ↓ 是否包含BLOB/TEXT列 ↓ 是 → 使用磁盘临时表 ↓ 否 检查行长度是否超过限制 限制 min(max_heap_table_size, tmp_table_size) ↓ 是 → 使用磁盘临时表 ↓ 否 使用MEMORY内存临时表 ↓ MEMORY临时表满了 ↓ 是 → 转换为磁盘临时表 ↓ 否 继续使用MEMORY临时表3.2 两种类型的对比对比维度MEMORY内存临时表磁盘临时表MyISAM/InnoDB存储位置内存磁盘tmpdir参数指定的目录性能极高内存读写较低磁盘IO触发条件无BLOB/TEXT、行长度不超限有BLOB/TEXT、行长度超限、MEMORY满了索引支持只支持哈希索引支持B树索引MySQL 8.0默认引擎MEMORYInnoDB可通过internal_tmp_disk_storage_engine配置3.3 相关配置参数我们可以通过调整以下参数让更多临时表使用MEMORY存储提升性能参数名含义默认值建议值tmp_table_size单个MEMORY临时表的最大大小1677721616MB根据服务器内存调整64M-256Mmax_heap_table_sizeMEMORY引擎表的最大大小1677721616MB和tmp_table_size设为一样的值internal_tmp_disk_storage_engine磁盘临时表的存储引擎MySQL 8.0InnoDB保持默认InnoDB即可tmpdir磁盘临时表的存储目录/tmpLinux或C:\Windows\TempWindows建议改为高速SSD目录四、如何查看SQL是否使用了内部临时表2种方法我们可以通过两种方法查看SQL是否触发了内部临时表以及触发的是MEMORY还是磁盘临时表。方法1EXPLAIN查看执行计划最常用用EXPLAIN查看SQL的执行计划重点看Extra列如果Extra列有**Using temporary**说明触发了内部临时表如果同时有Using filesort说明还触发了文件排序性能更差。示例截图1EXPLAIN查看Using temporary【示例截图1EXPLAIN查看Using temporary】*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000000 filtered: 100.00 Extra: Using temporary; Using filesort解读Extra列有Using temporary说明触发了内部临时表同时有Using filesort说明还触发了文件排序。方法2SHOW STATUS查看临时表统计用SHOW STATUS查看MySQL创建的临时表数量对比执行SQL前后的变化查看命令-- 查看临时表统计SHOWSTATUSLIKECreated_tmp%;结果示例【示例截图2SHOW STATUS查看临时表统计】-------------------------------- | Variable_name | Value | -------------------------------- | Created_tmp_disk_tables | 10 | -- 磁盘临时表的创建数量 | Created_tmp_tables | 100 | -- 内部临时表的总创建数量MEMORY磁盘 --------------------------------解读Created_tmp_tables内部临时表的总创建次数Created_tmp_disk_tables磁盘临时表的创建次数优化目标尽量降低Created_tmp_disk_tables的占比让更多临时表用MEMORY存储。五、优化建议如何减少内部临时表的使用内部临时表是MySQL的优化手段但过度使用磁盘临时表会严重影响性能因为磁盘IO比内存IO慢1000倍以上。我们可以通过以下6个方法减少内部临时表的使用尤其是磁盘临时表的使用优化1给GROUP BY、ORDER BY的列加合适的索引这是最有效的优化方法——如果GROUP BY、ORDER BY的列有合适的索引MySQL可以直接用索引完成分组或排序不需要临时表。优化示例优化前无索引触发临时表-- city列无索引GROUP BY触发临时表EXPLAINSELECTcity,COUNT(*)ASuser_countFROMuser_infoGROUPBYcity;-- Extra: Using temporary; Using filesort优化后加索引不触发临时表-- 给city列加联合索引ALTERTABLEuser_infoADDINDEXidx_city(city);-- 再次EXPLAINEXPLAINSELECTcity,COUNT(*)ASuser_countFROMuser_infoGROUPBYcity;-- Extra: NULL没有Using temporary了优化2避免SELECT *只查需要的列SELECT *会查询所有列包括大字段比如TEXT、BLOB这会导致行长度超过tmp_table_size限制被迫用磁盘临时表临时表包含不必要的列浪费内存和磁盘空间。优化示例优化前SELECT *触发磁盘临时表-- SELECT *包含contentTEXT列触发磁盘临时表SELECT*FROMuser_infoGROUPBYcity;优化后只查需要的列用MEMORY临时表-- 只查需要的列不包含TEXT列用MEMORY临时表SELECTid,username,cityFROMuser_infoGROUPBYcity;优化3避免在GROUP BY/ORDER BY中使用BLOB/TEXT列BLOB/TEXT列的长度不固定只要临时表包含BLOB/TEXT列MySQL直接用磁盘临时表不会用MEMORY。优化建议尽量不要在GROUP BY、ORDER BY、DISTINCT中使用BLOB/TEXT列如果必须用可以先把BLOB/TEXT列的查询放到外层内层查询只查需要的小字段。优化4调整tmp_table_size和max_heap_table_size参数如果临时表经常因为“行长度超限”被迫用磁盘临时表可以适当调大这两个参数让更多临时表能用MEMORY存储。配置示例my.cnf/my.ini[mysqld] # 临时表最大大小64MB tmp_table_size 67108864 max_heap_table_size 67108864注意事项两个参数必须设为一样的值不要设得太大比如超过1GB否则会占用过多内存影响其他业务调整后重启MySQL服务生效。优化5能用UNION ALL就不用UNIONUNION需要去重会触发临时表UNION ALL不需要去重通常不会触发临时表。如果业务上不需要去重优先用UNION ALL。优化示例优化前UNION触发临时表-- UNION需要去重触发临时表SELECTid,usernameFROMuser_infoWHEREcity武汉UNIONSELECTid,usernameFROMuser_infoWHEREcity北京;优化后UNION ALL不触发临时表-- 业务不需要去重用UNION ALL不触发临时表SELECTid,usernameFROMuser_infoWHEREcity武汉UNIONALLSELECTid,usernameFROMuser_infoWHEREcity北京;优化6优化子查询用JOIN替代FROM子句的子查询FROM子句的子查询派生表一定会触发内部临时表尽量用JOIN替代减少临时表的使用。优化示例优化前子查询触发临时表-- FROM子句的子查询触发临时表SELECT*FROM(SELECTid,username,cityFROMuser_infoWHEREage25)ASyoung_user;优化后JOIN替代不触发临时表-- 用JOIN替代子查询不触发临时表SELECTid,username,cityFROMuser_infoWHEREage25;六、总结MySQL内部临时表是一把双刃剑好的一面它是MySQL的优化手段帮助复杂SQL分步执行坏的一面过度使用磁盘临时表会严重影响性能磁盘IO瓶颈。核心结论触发场景UNION、无索引的GROUP BY、ORDER BYJOIN、DISTINCTORDER BY、FROM子查询、INSERT…SELECT…、无索引的JOIN两种类型MEMORY内存性能高和磁盘临时表MyISAM/InnoDB性能低查看方法EXPLAIN看Using temporarySHOW STATUS看Created_tmp%优化方法加合适的索引、避免SELECT *、避免BLOB/TEXT列、调整tmp_table_size、用UNION ALL替代UNION、用JOIN替代子查询。希望这篇图文详解能帮你彻底搞懂MySQL内部临时表通过优化SQL和索引减少磁盘临时表的使用提升系统性能
