导航
导航

再记一次sql优化

2018.11.16 sherryriver 流水账技术  pv:

线上环境爆出一个慢查询，监控发现，查询时服务器IO飙升，IO占用率达到100%， 执行时间长达8-9s左右。

大概sql如下

SELECT DISTINCT g.*, A.name AS cp_name, B.name AS category_name, C.name AS type_name FROM G g 
LEFT JOIN A  ON A.id = g.cp_id AND A.deleted = 0 
LEFT JOIN B ON c.id = g.category_id AND B.deleted = 0
LEFT JOIN C t ON t.id = g.type_id AND C.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 10 ;

然后使用explain查看执行计划，Extra列看到Using temporary。

问题还是比较明显的，查询了大量数据，然后使用临时表order by，但最终又只返回了10条数据。

然后dba观察到的IO高，是因为sql语句生成了一个巨大的临时表，内存放不下，于是全部拷贝到磁盘，导致IO飙升。

这样问题清晰了起来。

解决方法：

拆分sql，将排序操作和查询所有信息的操作分开

//查询符合条件的数据，只需要查询g.id即可
SELECT DISTINCT g.id .....(省略关联表)  ORDER BY g.modify_time DESC LIMIT 10 ;
//查询符合条件的详细数据，将第一条sql的结果使用in操作拼接到第二条的sql
SELECT DISTINCT g.* ...(省略) WHERE g.deleted = 0 and g.id in(…………………) ORDER BY g.modify_time DESC ;

经过测试优化前大约需要7s，优化后第一条0.3s，第二条0.1s，IO从100%降到不到1%

后续：（查略资料）

mysql在执行SQL查询时可能会用到临时表，一般情况下，用到临时表就意味着性能较低。

临时表存储

MySQL临时表分为“内存临时表”和“磁盘临时表”，其中内存临时表使用MySQL的MEMORY存储引擎，磁盘临时表使用MySQL的MyISAM存储引擎；

一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表；

使用临时表的场景

  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：指定系统创建的内存临时表最大大小；

max_heap_table_size: 指定用户创建的内存表的最大大小；

常见的避免临时表的方法有：

1、在ORDER BY或者GROUP BY的列上创建索引；

2、TEXT、大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。