记一次 MySQL 的慢查优化

发表于   |  

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

定位原因

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM tgdemand_demand t1
WHERE
  (
    t1.id IN
    (
      SELECT t2.demand_id
      FROM tgdemand_job t2
      WHERE (t2.state = 'working' AND t2.wangwang = 'abc')
    )
    AND
    NOT (t1.state = 'needConfirm')
  )
ORDER BY t1.create_date DESC

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

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

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

explain

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

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

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

1
2
3
4
SELECT * FROM tgdemand_demand t1 WHERE EXISTS (
  SELECT * FROM tgdemand_job t2 WHERE t1.id = t2.demand_id AND (t2.state = 'working' AND t2.wangwang = 'abc')
) AND NOT (t1.state = 'needConfirm')
ORDER BY t1.create_date DESC;

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

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

问题修复

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

1
2
3
4
5
6
7
8
9
10
11
12
ids = SELECT t2.demand_id
FROM tgdemand_job t2
WHERE (t2.state = 'working' AND t2.wangwang = 'abc');

SELECT * FROM tgdemand_demand t1
WHERE
  (
    t1.id IN ids
    AND
    NOT (t1.state = 'needConfirm')
  )
ORDER BY t1.create_date DESC;

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

1
2
3
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 转换的时候被执行。于是我将代码更改为了下面的样子：

1
2
3
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 进行改写来解决问题：

1
2
3
select * from tgdemand_demand t1, (select t.demand_id from tgdemand_job t where t.state = 'working' and t.wangwang = 'abc') t2
where t1.id=t2.demand_id and not (t1.state = 'needConfirm')
order by t1.create_date DESC

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

感想

框架可以提高生产率的前提是对背后的原理足够了解，不然应用很可能就会在某个时间暴露出一些隐蔽的要命问题（这些问题在小规模阶段可能根本都发现不了……）。保证应用的健壮真是个大学问，还有很多东西值得我们去探索。

参考资料

本文采用 知识共享署名 3.0 中国大陆许可协议，可自由转载、引用，但需署名作者且注明文章出处 。