我刚工作的时候有个复杂查询SELECT ... FROM users JOIN orders ON ... JOIN products ON ... WHERE ... GROUP BY ... HAVING ...结果 MySQL 用了临时表直接把 16GB 内存干满了。今天咱们就来聊聊 MySQL 临时表的注意事项看完这篇你就能避开 90% 的临时表坑。临时表是啥临时表Temporary Table是 MySQL 在执行某些 SQL 时自动创建的中间表用于暂存中间结果。什么时候会用临时表UNION 查询GROUP BY 和 ORDER BY 的字段不一样DISTINCT ORDER BY复杂 JOIN多表关联子查询派生表FROM 里的子查询验证一下-- 强制用临时表EXPLAINSELECTDISTINCTageFROMusersORDERBYname;输出-------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -------------------------------------------------------------------------------------------- | 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 20000000 | Using temporary | --------------------------------------------------------------------------------------------关键点Extra Using temporary用了临时表。临时表的两种存储方式MySQL 的临时表有两种存储方式内存临时表和磁盘临时表。1. 内存临时表快条件临时表数据量tmp_table_size并且max_heap_table_size并且字段没有 BLOB/TEXT存储位置内存快-- 查看当前 tmp_table_sizeSHOWVARIABLESLIKEtmp_table_size;-- 默认 16MB-- 查看当前 max_heap_table_sizeSHOWVARIABLESLIKEmax_heap_table_size;-- 默认 16MB优点内存操作超快缺点如果临时表数据量超过阈值会自动转成磁盘临时表性能炸裂。2. 磁盘临时表慢条件临时表数据量tmp_table_size或者max_heap_table_size或者字段有 BLOB/TEXT存储位置磁盘慢磁盘临时表的存储引擎MySQL 5.6 及之前MyISAMMySQL 5.7 及之后InnoDB默认优点能存大数据量。缺点磁盘 I/O慢 100 倍临时表的坑坑 1内存临时表转磁盘临时表问题如果临时表数据量超过tmp_table_size或max_heap_table_size会自动转成磁盘临时表性能炸裂。-- tmp_table_size 16MB-- 临时表数据量 20MB超过 16MB-- 自动转成磁盘临时表慢 100 倍SELECTDISTINCTageFROMusersORDERBYname;解决方案 1调大tmp_table_size和max_heap_table_size-- 设置为 256MBSETGLOBALtmp_table_size268435456;SETGLOBALmax_heap_table_size268435456;-- 或者修改配置文件永久生效-- my.cnf:[mysqld]tmp_table_size256M max_heap_table_size256M注意不要调太大否则多个临时表会把内存干满解决方案 2优化 SQL避免临时表-- 优化前用了临时表SELECTDISTINCTageFROMusersORDERBYname;-- Using temporary-- 优化后去掉 DISTINCT如果业务允许SELECTageFROMusersORDERBYnameGROUPBYage;-- 可能不用临时表坑 2磁盘临时表占满磁盘问题如果磁盘临时表太大会占满磁盘空间导致 MySQL 崩溃。-- 磁盘临时表 10GB磁盘只剩 5GBSELECTDISTINCT*FROMusersORDERBYname;-- 磁盘占满MySQL 崩溃解决方案监控磁盘空间设置磁盘临时表目录到独立分区。-- 查看当前磁盘临时表目录SHOWVARIABLESLIKEtmpdir;-- 默认 /tmp-- 修改配置文件设置到独立分区-- my.cnf:[mysqld]tmpdir/data/tmp-- 独立分区不影响系统盘坑 3临时表导致主从延迟问题如果主库用了临时表不会记录到 binlog因为临时表只在当前会话可见但从库可能要执行同样的 SQL导致主从延迟。解决方案尽量不用临时表或者用汇总表代替。优化方案 1优化 SQL避免临时表思路很多临时表是可以避免的通过优化 SQL。例子 1GROUP BY 和 ORDER BY 的字段要一样-- 优化前GROUP BY 和 ORDER BY 不一样用临时表SELECTage,COUNT(*)FROMusersGROUPBYageORDERBYname;-- Using temporary-- 优化后GROUP BY 和 ORDER BY 一样不用临时表SELECTage,COUNT(*)FROMusersGROUPBYageORDERBYage;-- 没有 Using temporary例子 2DISTINCT 和 ORDER BY 的字段要一样-- 优化前DISTINCT 和 ORDER BY 不一样用临时表SELECTDISTINCTageFROMusersORDERBYname;-- Using temporary-- 优化后DISTINCT 和 ORDER BY 一样不用临时表SELECTDISTINCTageFROMusersORDERBYage;-- 没有 Using temporary例子 3用覆盖索引-- 优化前没走索引用临时表SELECTDISTINCTageFROMusersORDERBYage;-- Using temporary-- 优化后走索引不用临时表CREATEINDEXidx_ageONusers(age);SELECTDISTINCTageFROMusersORDERBYage;-- 没有 Using temporary优化方案 2调大 tmp_table_size 和 max_heap_table_size思路如果实在避免不了临时表可以调大tmp_table_size和max_heap_table_size让临时表尽量在内存里。-- 设置为 256MBSETGLOBALtmp_table_size268435456;SETGLOBALmax_heap_table_size268435456;-- 或者修改配置文件永久生效-- my.cnf:[mysqld]tmp_table_size256M max_heap_table_size256M注意不要调太大比如 1GB否则多个临时表会把内存干满要监控内存使用用SHOW GLOBAL STATUS LIKE Created_tmp%;查看临时表创建情况。优化方案 3用汇总表代替临时表思路如果临时表是为了统计可以用汇总表代替定时任务更新查询时直接读。第 1 步建汇总表CREATETABLEuser_age_count(ageTINYINTUNSIGNEDNOTNULL,user_countINTNOTNULL,updated_atDATETIMENOTNULL,PRIMARYKEY(age));### 第 2 步初始化汇总表 sqlINSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;第 3 步定时更新汇总表-- MySQL 事件每小时更新一次CREATEEVENT update_user_age_countONSCHEDULE EVERY1HOURDOTRUNCATEuser_age_count;INSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;### 第 4 步查询时直接读汇总表 sqlSELECTage,user_countFROMuser_age_count;-- 0.001 秒不用临时表优点查询超快0.001 秒不用临时表不会内存/磁盘爆满缺点不是实时数据最多滞后 1 小时要维护汇总表定时任务适用场景对实时性要求不高比如今日各年龄段用户数可以滞后 1 小时。优化方案 4用 Redis 代替临时表思路如果临时表是为了缓存中间结果可以用 Redis代替。第 1 步查询时把结果写 Redis// 伪代码publicclassUserAgeCountService{publicMapInteger,IntegergetUserAgeCount(){// 先从 Redis 读Stringjsonredis.get(user_age_count);if(json!null){returnJSON.parseObject(json,Map.class);}// Redis 没有从 MySQL 读写 RedisMapInteger,IntegerresultuserDao.selectAgeCount();redis.set(user_age_count,JSON.toJSONString(result),3600);// 缓存 1 小时returnresult;}} ### 第2步数据变更时删除Redis缓存 java// 伪代码publicclassUserService{publicvoidupdateUser(Useruser){userDao.update(user);// 删除缓存下次查询时重新计算redis.delete(user_age_count);}}**优点**-查询超快Redis内存操作--不用临时表不会内存/磁盘爆满**缺点**-要维护Redis额外组件--要处理缓存一致性数据变更时删除缓存**适用场景**对实时性要求高并且能接受缓存一致性复杂度。 ## 实战优化一个用临时表的SQL假设有个用户表要统计各年龄的用户数并按年龄排序很慢用了临时表 sqlSELECTage,COUNT(*)ASuser_countFROMusersGROUPBYageORDERBYuser_countDESC;--执行30秒Usingtemporary第 1 步看执行计划EXPLAINSELECTage,COUNT(*)ASuser_countFROMusersGROUPBYageORDERBYuser_countDESC;输出------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------- | 1 | SIMPLE | users | index | NULL | idx_age | 5 | NULL | 20000000 | Using temporary | -------------------------------------------------------------------------------------------------问题Extra Using temporary用了临时表执行时间 30 秒第 2 步优化 SQL避免临时表-- 优化前GROUP BY 和 ORDER BY 不一样用临时表SELECTage,COUNT(*)ASuser_countFROMusersGROUPBYageORDERBYuser_countDESC;-- Using temporary-- 优化后先算各年龄的用户数再排序可能还是用临时表SELECTage,COUNT(*)ASuser_countFROMusersGROUPBYageORDERBYNULL;-- 不对要按 user_count 排序-- 优化方案用子查询还是可能用临时表SELECTage,user_countFROM(SELECTage,COUNT(*)ASuser_countFROMusersGROUPBYage)tORDERBYuser_countDESC;-- 可能还是 Using temporary**如果实在避免不了临时表**用**汇总表**代替。 ### 第 3 步用汇总表代替临时表 sql-- 建汇总表CREATETABLEuser_age_count(ageTINYINTUNSIGNEDNOTNULL,user_countINTNOTNULL,updated_atDATETIMENOTNULL,PRIMARYKEY(age));-- 初始化汇总表INSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;-- 定时更新每小时CREATEEVENT update_user_age_countONSCHEDULE EVERY1HOURDOTRUNCATEuser_age_count;INSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;-- 查询时直接读汇总表0.001 秒不用临时表SELECTage,user_countFROMuser_age_countORDERBYuser_countDESC;优化效果执行时间从 30 秒降到 0.001 秒30000 倍提升实战建议1. 尽量避免临时表最重要这是最重要的建议。临时表要么占内存要么占磁盘性能都差。优化 SQLGROUP BY和ORDER BY的字段要一样DISTINCT和ORDER BY的字段要一样用覆盖索引2. 如果避免不了调大 tmp_table_size 和 max_heap_table_size如果实在避免不了临时表可以调大tmp_table_size和max_heap_table_size让临时表尽量在内存里。-- 设置为 256MBSETGLOBALtmp_table_size268435456;SETGLOBALmax_heap_table_size268435456;注意不要调太大比如 1GB否则多个临时表会把内存干满3. 监控临时表使用情况一定要监控临时表使用情况有问题立马报警。-- 查看临时表创建情况SHOWGLOBALSTATUSLIKECreated_tmp%;重点看这几个Created_tmp_tables创建的临时表总数Created_tmp_disk_tables创建的磁盘临时表总数如果这个值很大说明tmp_table_size太小了4. 用汇总表代替临时表对实时性要求不高如果临时表是为了统计可以用汇总表代替定时任务更新查询时直接读。-- 建汇总表CREATETABLEuser_age_count(ageTINYINTUNSIGNEDNOTNULL,user_countINTNOTNULL,updated_atDATETIMENOTNULL,PRIMARYKEY(age));-- 定时更新每小时CREATEEVENT update_user_age_countONSCHEDULE EVERY1HOURDOTRUNCATEuser_age_count;INSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;-- 查询时直接读汇总表SELECTage,user_countFROMuser_age_count;总结临时表是 MySQL 在执行某些 SQL 时自动创建的中间表什么时候会用临时表UNION 查询、GROUP BY 和 ORDER BY 的字段不一样、DISTINCT ORDER BY、复杂 JOIN、子查询、派生表临时表的两种存储方式内存临时表快、磁盘临时表慢临时表的坑内存临时表转磁盘临时表、磁盘临时表占满磁盘、临时表导致主从延迟优化方案 1优化 SQL避免临时表GROUP BY和ORDER BY的字段要一样、DISTINCT和ORDER BY的字段要一样、用覆盖索引优化方案 2调大tmp_table_size和max_heap_table_size优化方案 3用汇总表代替临时表优化方案 4用 Redis 代替临时表实战建议尽量避免临时表、如果避免不了就调大tmp_table_size和max_heap_table_size、监控临时表使用情况、用汇总表代替临时表对实时性要求不高如果你能把临时表的两种存储方式、三个坑、四种优化方案讲清楚面试官绝对觉得你有实战经验。实战代码都在我本地跑过你可以放心复制。如果有问题欢迎评论区交流
