【问题现象】 线上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 from gm_game g left
【问题现象】线上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 from 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 by g.modify_time desc limit 20 ;
【问题分析】使用explain查看执行计划，结果如下：
这条sql语句的问题其实还是比较明显的：
查询了大量数据(包括数据条数、以及g.* )，然后使用临时表order by，但最终又只返回了20条数据。
dba观察到的io高，是因为sql语句生成了一个巨大的临时表，内存放不下，于是全部拷贝到磁盘，导致io飙升。
【优化方案】优化的总体思路是拆分sql，将排序操作和查询所有信息的操作分开。
第一条语句：查询符合条件的数据，只需要查询g.id即可
select distinct g.id from 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 by g.modify_time desc limit 20 ;
第二条语句：查询符合条件的详细数据，将第一条sql的结果使用in操作拼接到第二条的sql
select distinct g.*, cp.name as cp_name,c.name as category_name,t.name as type_name from 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 and g.id in(…………………) order by g.modify_time desc ;
【实测效果】在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字节的列；
临时表相关配置tmp_table_size：指定系统创建的内存临时表最大大小； http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size
max_heap_table_size: 指定用户创建的内存表的最大大小； http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size
注意：最终的系统创建的内存临时表大小是取上述两个配置值的最小值。
表的设计原则使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有：
1）创建索引：在order by或者group by的列上创建索引；
2）分拆很长的列：一般情况下，text、blob，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。
sql优化如果表的设计已经确定，修改比较困难，那么也可以通过优化sql语句来减少临时表的大小，以提升sql执行效率。
常见的优化sql语句方法如下：
1）拆分sql语句
临时表主要是用于排序和分组，很多业务都是要求排序后再取出详细的分页数据，这种情况下可以将排序和取出详细数据拆分成不同的sql，以降低排序或分组时临时表的大小，提升排序和分组的效率，我们的案例就是采用这种方法。
2）优化业务，去掉排序分组等操作
有时候业务其实并不需要排序或分组，仅仅是为了好看或者阅读方便而进行了排序，例如数据导出、数据查询等操作，这种情况下去掉排序和分组对业务也没有多大影响。
如何判断使用了临时表？使用explain查看执行计划，extra列看到using temporary就意味着使用了临时表。
详细信息请参考mysql官方手册： http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
原文地址：优化临时表使用，sql语句性能提升100倍, 感谢原作者分享。