避免临时表,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 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查看执行计划,结果如下:

   slow

   这条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字节的字符串,基本上都是为了显示信息,而不会用于查询条件, 因此表设计的时候,应该将这些列独立到另外一张表。

  • 如何判断使用了临时表?

  •    使用explain查看执行计划,Extra列看到Using temporary就意味着使用了临时表。

       详细信息请参考MySQL官方手册: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

3 thoughts on “避免临时表,SQL语句性能提升100倍

  1. 有时候不知道为什么,明明扫描了,几行,使用了索引,但是却花费几十秒、

    有时候使用了 临时表,文件排序,速度还很快。有时候 扫描了几千万行,速度依然非常快。
    凌乱了。不知道为什么。

  2. 完全误导,mysql优化,就是要多用临时表,你的效率慢,就是因为没用好临时表,看好啦,你自己去试试,核心要点在于,关联2个表查询就要做一个临时表,四个表,就要,主动做两个临时表,否则就是n*m*t*f 笛卡尔集(假设4个表,每个表的行数n,m,t,f)
    select …left join table1 on ..left join table2 on.. left join table3 on…left join table4 on…
    此处创建 select …left join table1 on ..left join table2 as tmp1 left join
    select …left join table1 on ..left join table3 as tmp4

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>