mysql临时表优化_优化临时表使⽤，SQL语句性能提升100倍【问题现象】
线上mysql数据库爆出⼀个慢查询，DBA观察发现，查询时服务器IO飙升，IO占⽤率达到100%， 执⾏时间长达7s左右。
SQL语句如下：
SELECT DISTINCT g.*,cp.name AS cp_name, c.name AS category_name, t.name AS type_name FROMgm_game g LEFT
JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER dify_time DESC LIMIT 20 ;
【问题分析】
使⽤explain查看执⾏计划，结果如下：
这条sql语句的问题其实还是⽐较明显的：
查询了⼤量数据(包括数据条数、以及g.* )，然后使⽤临时表order by，但最终⼜只返回了20条数据。
DBA观察到的IO⾼，是因为sql语句⽣成了⼀个巨⼤的临时表，内存放不下，于是全部拷贝到磁盘，导致IO飙升。mysql面试题sql语句多表联查
【优化⽅案】
vue nexttick原理优化的总体思路是拆分sql，将排序操作和查询所有信息的操作分开。
第⼀条语句：查询符合条件的数据，只需要查询g.id即可
SELECT DISTINCT g.idFROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT
JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER dify_time DESC LIMIT 20 ;
第⼆条语句：查询符合条件的详细数据，将第⼀条sql的结果使⽤in操作拼接到第⼆条的sql
interceptsSELECT DISTINCT g.*, cp.name AS cp_name,c.name AS category_name,t.name AS type_name FROMgm_game g LEFT
JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 and g.id in(…………………) ORDER BY
【实测效果】
在SATA机器上测试，优化前⼤约需要50s，优化后第⼀条0.3s，第⼆条0.1s，优化后执⾏速度是原来的100倍以上，IO从100%降到不到1%
在SSD机器上测试，优化前⼤约需要7s，优化后第⼀条0.3s，第⼆条0.1s，优化后执⾏速度是原来的10倍以上，IO从100%降到不到1%
可以看出，优化前磁盘io是性能瓶颈，SSD的速度要⽐SATA明显要快，优化后磁盘不再是瓶颈，SSD和SATA性能没有差别。
【理论分析】
MySQL在执⾏SQL查询时可能会⽤到临时表，⼀般情况下，⽤到临时表就意味着性能较低。
临时表存储
MySQL临时表分为“内存临时表”和“磁盘临时表”，其中内存临时表使⽤MySQL的MEMORY存储引擎，磁盘临时表使⽤MySQL的MyISAM存储引擎；
⼀般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表；
Linux平台上缺省是/tmp⽬录，/tmp⽬录⼩的系统要注意啦。
使⽤临时表的场景
1)ORDER BY⼦句和GROUP BY⼦句不同， 例如：ORDERY BY price GROUP BY name；
2)在JOIN查询中，ORDER BY或者GROUP BY使⽤了不是第⼀个表的列 例如：SELECT * from TableA, TableB ORDER BY
TableA.price GROUP by TableB.name
3)ORDER BY中使⽤了DISTINCT关键字 ORDERY BY DISTINCT(price)
4)SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很⼩，请直接使⽤内存临时表，不需要使⽤索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW⼀起使⽤ ⼀般情况下，我们没有必要使⽤这个选项，让MySQL服务器选择即可。
直接使⽤磁盘临时表的场景
1)表包含TEXT或者BLOB列；
2)GROUP BY 或者 DISTINCT ⼦句中包含长度⼤于512字节的列；
3)使⽤UNION或者UNION ALL时，SELECT⼦句中包含⼤于512字节的列；
临时表相关配置
注意：最终的系统创建的内存临时表⼤⼩是取上述两个配置值的最⼩值。
表的设计原则
使⽤临时表⼀般都意味着性能⽐较低，特别是使⽤磁盘临时表，性能更慢，因此我们在实际应⽤中应该尽量避免临时表的使⽤。 常见的避免临时表的⽅法有：
1)创建索引：在ORDER BY或者GROUP BY的列上创建索引；
oracle索引优缺点2)分拆很长的列：⼀般情况下，TEXT、BLOB，⼤于512字节的字符串，基本上都是为了显⽰信息，⽽不会⽤于查询条件， 因此表设计的时候，应该将这些列独⽴到另外⼀张表。
SQL优化
如果表的设计已经确定，修改⽐较困难，那么也可以通过优化SQL语句来减少临时表的⼤⼩，以提升SQL执⾏效率。
flexa常见的优化SQL语句⽅法如下：
calloc用法1)拆分SQL语句
临时表主要是⽤于排序和分组，很多业务都是要求排序后再取出详细的分页数据，这种情况下可以将排序和取出详细数据拆分成不同的SQL，以降低排序或分组时临时表的⼤⼩，提升排序和分组的效率，我们的案例就是采⽤这种⽅法。
2)优化业务，去掉排序分组等操作
有时候业务其实并不需要排序或分组，仅仅是为了好看或者阅读⽅便⽽进⾏了排序，例如数据导出、数据查询等操作，这种情况下去掉排序和分组对业务也没有多⼤影响。
如何判断使⽤了临时表？
使⽤explain查看执⾏计划，Extra列看到Using temporary就意味着使⽤了临时表。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。