转载

记一次 MySQL 的慢查优化

最近遇见一个 MySQL 的慢查问题，于是排查了下，这里把相关的过程做个总结。

定位原因

我首先查看了 MySQL 的慢查询日志，发现有这样一条 query 耗时非常长（大概在 1 秒多），而且扫描的行数很大（10 多万条数据，差不多是全表了）：

SELECT*FROMtgdemand_demand t1 WHERE   (     t1.idIN     (       SELECTt2.demand_id       FROMtgdemand_job t2       WHERE(t2.state ='working'ANDt2.wangwang ='abc')     )     AND     NOT(t1.state ='needConfirm')   ) ORDERBYt1.create_dateDESC

这个查询不是很复杂，首先执行一个子查询，取到任务的状态（state）是 ‘working’ 并且任务的关联人（wangwang）是’abc’的所有需求 id（这个设计师进行中的任务对应的需求 id），然后再到主表 tgdemand_demand 中带入刚才的 id 集合，查询出需求状态（state）不是 ‘needConfirm’ 的所有需求，最后进行一个排序。

按道理子查询筛选出 id 后到主表过滤是直接使用到主键，应该是很快的啊。而且，我检查了子查询的 tgdemand_job 表的索引，where 中用到的查询条件都已经增加了索引。怎么会这样呢？

于是，我对这个 query 执行了一个 explain（输出 sql 语句的执行计划），看看 MySQL 的执行计划是怎样的。输出如下：

记一次 MySQL 的慢查优化

我们看到，第一行是 t1 表，type 是 ALL（全表扫描），rows（影响行数）是 157089，没有用到任何索引；第二行是 t2 表，用到了索引。和我之前理解的执行顺序完全不一样！

为什么 MySQL 不是先执行子查询，而是对 t1 表进行了全表扫描呢？我们仔细看第二行的 select_type，发现它的值是 DEPENDENT_SUBQUERY，意思是这个子查询的查询方式依赖外层的查询。这是什么意思？

实际上，MySQL 对于这种子查询会进行改写，上面的 SQL 会被改写成下面的形式：

SELECT*FROMtgdemand_demand t1WHEREEXISTS (   SELECT*FROMtgdemand_job t2WHEREt1.id = t2.demand_idAND(t2.state ='working'ANDt2.wangwang ='abc') ) ANDNOT(t1.state ='needConfirm') ORDERBYt1.create_dateDESC;

这表示，SQL 会去扫描 tgdemand_demand 表的所有数据，每条数据再传入到子查询中与表 tgdemand_job 进行关联，执行子查询，子查询根本不会先执行，而且子查询会执行 157089 次（外层表的记录数量）。还好我们的子查询加了必要的索引，不然结果会更加惨不忍睹。

这个结果真是太坑爹，而且十分违反直觉。对于慢查询，千万不要想当然，还是多多 explain，看看数据库实际上是怎么去执行的。

问题修复

既然子查询会被改写，那最简单的解决方案就是不用子查询，将内层获取需求 id 的 SQL 单独拿出来执行，取到结果后再执行一条 SQL 去获取实际的数据。大概像这样（下面的语句是不合法的，只是示意）：

ids = SELECTt2.demand_id FROMtgdemand_job t2 WHERE(t2.state ='working'ANDt2.wangwang ='abc');   SELECT*FROMtgdemand_demand t1 WHERE   (     t1.idINids     AND     NOT(t1.state ='needConfirm')   ) ORDERBYt1.create_dateDESC;

说干咱就干，我找到了下面的代码（是 python 语言写的）：

demand_ids = Job.objects.filter(wangwang=user['wangwang'], state='working').values_list("demand_id", flat=True)   demands = Demand.objects.filter(id__in=demand_ids).exclude(state__in=['needConfirm']).order_by('-create_date')

咦！这不是和我想得是一样的嘛？先查出需求 id（代码第一行），然后用 id 集合再去执行实际的查询（代码第二行）。为什么经过 ORM 框架的处理后产出的 SQL 就不一样了呢？

带着这个问题我搜索了一番。原来 Django 自带的 ORM 框架生成的 QuerySet 是懒执行的（lazy evaluated），我们可以将这种 QuerySet 到处传，直到需要时才会实际的执行 SQL。

比如，我们代码里面的 Job.objects.filter(wangwang=user['wangwang'], state='working').values_list("demand_id", flat=True) 这个 QuerySet 实际上并没有执行，就被作为参数传递给了 id__in ，当 Demand.objects.filter(id__in=demand_ids).exclude(state__in=['needConfirm']).order_by('-create_date') 这个 QuerySet 执行时，刚才未执行的 QuerySet 才开始作为 SQL 执行，于是生成了最开始的 SQL 语句。

既然如此，我们的目的要让 QuerySet 提前执行，获得结果集。根据文档，对 QuerySet 进行循环、slice、取 len、list 转换的时候被执行。于是我将代码更改为了下面的样子：

demand_ids = list(Job.objects.filter(wangwang=user['wangwang'], state='working').values_list("demand_id", flat=True))   demands = Demand.objects.filter(id__in=demand_ids).exclude(state__in=['needConfirm']).order_by('-create_date')

终于，页面打开速度恢复正常了。

实际上，我们也可以对 SQL 进行改写来解决问题：

select*fromtgdemand_demand t1, (selectt.demand_idfromtgdemand_job twheret.state ='working'andt.wangwang ='abc') t2 wheret1.id=t2.demand_idandnot(t1.state ='needConfirm') orderbyt1.create_dateDESC

思路是去掉子查询，换用 2 个表进行 join 的方式来取得数据。这里就不展开了。