记一次 MySQL 的慢查优化

news/2024/5/22 17:23:39

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

定位原因

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

SELECT * FROM tgdemand_demand t1
WHERE(t1.id IN(SELECT t2.demand_idFROM tgdemand_job t2WHERE (t2.state = 'working' AND t2.wangwang = 'abc'))ANDNOT (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 会被改写成下面的形式:

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 idsANDNOT (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。

比如,我们代码里面的 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 * 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 的方式来取得数据。这里就不展开了。

感想

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

参考资料

  • http://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html

  • http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

  • https://docs.djangoproject.com/en/1.9/ref/models/querysets/


http://lihuaxi.xjx100.cn/news/245554.html

相关文章

java基础回顾

1.集合 1.1集合的类型与各自的特性 1 --|Collection:单列集合2 --|List:有序,可重复3 --|ArrayList:数组实现,查找快,增删慢4 由于是数组实现,在增和删…

php获取网页标题接口,PHP获取网页标题的3种实现方法代码实例

一、推荐方法 CURL获取$c curl_init();$url ‘www.jb51.net‘;curl_setopt($c, CURLOPT_URL, $url);curl_setopt($c, CURLOPT_RETURNTRANSFER, 1);$data curl_exec($c);curl_close($c);$pos strpos($data,‘utf-8‘);if($posfalse){$data iconv("gbk","utf…

微信公众平台对所有公众号开放自定义菜单

据统计,微信公众号已达1000多万了,但大多数没有微信认证,且没有开发能力,为此微信公众平台开放了自定义菜单功能给所有公众号,这是微信团队年前给广大自媒体送的大礼,期待微信越来越开放 公众帐号运营者点击…

软考之CPU的寻址方式

在复习软考的时候,发现CPU的内部工作原理这一部分的内容挺重要的,现对CPU的寻址方式进行了一下总结。以下就来一一介绍一下。我们都知道一个指令分为操作码和地址码两部分。操作码确定指令的类型。地址码确定指令所要处理的数据。依据地址码代表的地址类…

Node.js安装

通过nvm安装 下载nvm并执行wget -qO- https://raw.github.com/creationix/nvm/v0.33.11/install.sh | sh将命令输出到终端命令中~/.bashrcexport NVM_DIR"$HOME/.nvm"更新文件source .bashrc通过nvm安装node.jsnvm install 10.13安装的版本是10.13的版本 通过命令查看…

Hyper-V虚拟化测试05防火墙及证书配置

3.防火墙和证书3.1、防火墙配置打开Windows防火墙,并进入到高级配置入站规则,启用“Hyper-V副本HTTP侦听器(TCP入站)”和“Hyper-V副本HTTPS侦听器(TCP入站)”可以看到已经启用了如上两条规则允许入站流量3…

php中文件操作函数,php中常用文件操作函数介绍

小编今天来给php初学者介绍php文件操作的常用函数使用方法总结,包括:文件读写,创建,查看文件属性,文件删除等等关于文件的操作。在对一个文件进行访问之前,一般我们都需要判断文件是否存在,以免…

Mozilla开源了VR框架A-Frame

Mozilla创建并开源了A-Frame,这是一个用于在桌面浏览器、智能手机和Oculus Rift上创建VR场景的框架。\\A-Frame是一个在浏览器中创建VR体验的开源框架。该框架由Mozilla的MozVR团队创建和开发。A-Frame使用了一个在游戏开发中经常使用的“实体-组件(Enti…