首页 > 数据库 > MySQL

记一次 MySQL 的慢查优化

来源：SegmentFault

时间：2023-01-13 09:29:20 315浏览收藏

本篇文章向大家介绍《记一次 MySQL 的慢查优化》，主要包括MySQL、python、Django，具有一定的参考价值，需要的朋友可以参考一下。

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

定位原因

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

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），然后再到主表

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 去获取实际的数据。大概像这样（下面的语句是不合法的，只是示意）：

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 语言写的）：

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。

比如，我们代码里面的

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 * 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 的方式来取得数据。这里就不展开了。

感想

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

参考资料

以上就是《记一次 MySQL 的慢查优化》的详细内容，更多关于mysql的资料请关注golang学习网公众号！

mysql MySQL python Django

声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除