文档章节

一个20秒SQL慢查询优化的经历与处理方案

暗夜在火星
 暗夜在火星
发布于 2015/04/11 11:31
字数 1242
阅读 8529
收藏 304
点赞 12
评论 46

背景

前几天在项目上线过程中,发现有一个页面无法正确获取数据,经排查原来是接口调用超时,而最后发现是因为SQL查询长达到20多秒而导致了问题的发生。

这里,没有高深的理论或技术,只是备忘一下经历和解读一些思想误区。


复杂SQL语句的构成

这里不过多对业务功能进行描述,但为了突出问题所在,会用类比的语句来描述当时的场景。复杂的SQL语句可以表达如下:

SELECT * FROM a_table AS a 
LEFT JOIN b_table AS b ON a.id=b.id 
WHERE a.id IN (
    SELECT DISTINCT id FROM a_table 
    WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)

关联查询

从上面简化的SQL语句,可以看出,首先进行的是关联查询。

子查询

其次,是嵌套的子查询。此子查询是为了找出多个用户共同拥有的组ID。所以语句中的“100,102,103”是根据场景来定的,并且需要和后面“count(id) > 3”的个数对应。简单来说,就是找用户交集的组ID。


耗时在哪?

假设现在a_table表的数据量为20W,而b_table的数据量为2000W。大家可以想一下,你觉得主要的耗时是在关联查询部分,还是在子查询部分?


(思考空间。。。。)


(思考空间。。。。 。。。)


(思考空间。。。。 。。。 。。。)


问题定位

对于SQL底层的原理和高深的理论,我暂时掌握不够深入。但我知道可以通过类比和简单的测试来验证是哪一块环节出了问题。


初步断定

首先,对于只有一个用户ID时,我会把上面的语句简化成:

SELECT * FROM a_table AS a 
LEFT JOIN b_table AS b ON a.id=b.id 
WHERE user_id IN (100)


所以,初步断定应该是嵌套的子查询部分占用了大部分的时间。


再进一步验证

既然定位到了是嵌套的子查询语句的问题,那又要分为两块待排查的区域:是子查询本身耗时大,还是嵌套而导致慢查询?


结果很容易发现,当我把子查询单独在DB中执行时,是非常快的。所以排除。

剩下的不言而喻,20秒的慢查询是嵌套引起的


但因为处于上线紧急的过程中,为了确保,我快速地验证了我的结论:

1、将子查询的ID单独执行,并把得到的结果序列手动拼成一段ID,如:1,2,3,4, ... , 999

2、将上面得到的序列ID,手动替换到原来的SQL语句

3、执行,发现,很快!只用了约150 ms


Well Done!  准备修复上线!


解决方案

线上的问题,很多时间都是在定位问题和分析原因,既然问题找到了,原因也找到了,解决方案不言而喻。代码简单处理即可。


另外一个需要注意的点

当前,实际的SQL语句,会比这个更为复杂,但已足以表达问题所在。但在前期,笔者也做了一些SQL的代码。

因为b_table比a_table大,所以一开始 b_table 左关联 a_table 时,很慢,大概是1秒多,而且数据量是很少的;但若反过来,a_table 左关联 b_table 时,则很快,大概是100毫秒

所以,又发现一个有趣的现象:

大表 左关联 小表,很慢;小表 左关联 大表,很快。


当然,这些我们理论上都知道,但实际开发会忘却。又或者一开始两个表都为空时,而又没考虑到后期这两个表增长的速度时,日后就会埋下坑了。


总结

首先,嵌套的子查询是很慢的。

原因,我还没仔细去研究,但在下班的路上和我的同事交流时,他说曾经看过这方面相关的书籍,是说每一次的子查询都会产生一个SQL语句,所以就N次查询了。而另外一位资深的QA同事则跟我说,应该是M*N的问题。


其次,我一开始使用嵌套子查询,是存在这样一个误区:我觉得将这些操作交给MySQL自身来处理会更高效,毕竟DB内部会有良好的机制来执行这些查询由。

然后,实际表白,我错了。因为这不是简单的合并MC批量查询。


当我们决定使用一些底层的技术时,只有当我们理解透彻了,才能使用更为恰当。而因为无知就断定工具、框架、底层无所不能时,往往就会中招。

© 著作权归作者所有

共有 人打赏支持
暗夜在火星

暗夜在火星

粉丝 150
博文 150
码字总数 310930
作品 1
广州
程序员
加载中

评论(46)

柠檬茶001
柠檬茶001

引用来自“柠檬茶001”的评论

SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE a.id IN (
SELECT DISTINCT id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)

不晓得楼主是怎么做的分析。
首先,这段SQL的逻辑都不对。嵌套中的SQL得到的ID值是不对的,根本就不能得出正确的结果。
正确的逻辑,及优化的写法应该如下:
SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE a.user_id exists (
SELECT user_id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)
and a.user_id IN (100,102,103)

引用来自“暗夜在火星”的评论

你好,上面的代码是根据业务场景简化后的示例代码,有可能写法上有问题。

但我重看了一下,原来的写法好像也是正确的。嵌套的子查询查出符合条件的id (你改的是user_id),再IN一下(你的是使用了exists 判断user_id)。
你可以创建个表,插入一些数据,尝试一下。看看得出的结果是什么样的。
柠檬茶001
柠檬茶001

引用来自“柠檬茶001”的评论

SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE a.id IN (
SELECT DISTINCT id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)

不晓得楼主是怎么做的分析。
首先,这段SQL的逻辑都不对。嵌套中的SQL得到的ID值是不对的,根本就不能得出正确的结果。
正确的逻辑,及优化的写法应该如下:
SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE a.user_id exists (
SELECT user_id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)
and a.user_id IN (100,102,103)

引用来自“暗夜在火星”的评论

你好,上面的代码是根据业务场景简化后的示例代码,有可能写法上有问题。

但我重看了一下,原来的写法好像也是正确的。嵌套的子查询查出符合条件的id (你改的是user_id),再IN一下(你的是使用了exists 判断user_id)。
嵌套子查询:
SELECT DISTINCT id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
这样是查不出符号条件的ID的。因为GROUP BY 的是user_id,那么select后面只能跟user_id.这样写虽然也能出结果,但是得出的结果是错误的。如果是在oracle中,会直接提示你语法错误。mysql对语法检查不严格,这是mysql解析器的一个BUG。
你只是看了我的替换的user_id,但是根本就没看懂我替换的逻辑。
暗夜在火星
暗夜在火星

引用来自“柠檬茶001”的评论

SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE a.id IN (
SELECT DISTINCT id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)

不晓得楼主是怎么做的分析。
首先,这段SQL的逻辑都不对。嵌套中的SQL得到的ID值是不对的,根本就不能得出正确的结果。
正确的逻辑,及优化的写法应该如下:
SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE a.user_id exists (
SELECT user_id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)
and a.user_id IN (100,102,103)
你好,上面的代码是根据业务场景简化后的示例代码,有可能写法上有问题。

但我重看了一下,原来的写法好像也是正确的。嵌套的子查询查出符合条件的id (你改的是user_id),再IN一下(你的是使用了exists 判断user_id)。
柠檬茶001
柠檬茶001
SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE a.id IN (
SELECT DISTINCT id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)

不晓得楼主是怎么做的分析。
首先,这段SQL的逻辑都不对。嵌套中的SQL得到的ID值是不对的,根本就不能得出正确的结果。
正确的逻辑,及优化的写法应该如下:
SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE a.user_id exists (
SELECT user_id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)
and a.user_id IN (100,102,103)
暗夜在火星
暗夜在火星

引用来自“纳兰清风”的评论

结果集里需要用到a表中的字段吗?
是的
纳兰清风
纳兰清风
结果集里需要用到a表中的字段吗?
慕容月
慕容月
因为mysql的嵌套查询是先外后内,不是所谓的先内后外,所以每次都是先把外面的查询的结果全部查询出来,再去和子查询一个的一个的匹配,所以这种子查询很慢的!
暗夜在火星
暗夜在火星

引用来自“liang3p”的评论

lz的业务理解有问题,找用户交集的组ID,count(id)有什么用?
select id
from tb_a
where uid in ('100','102','103')
group by 1 having count(distinct uid) >=3
同时属于这三个组的交集,而不是并集
l
liang3p
lz的业务理解有问题,找用户交集的组ID,count(id)有什么用?
select id
from tb_a
where uid in ('100','102','103')
group by 1 having count(distinct uid) >=3
-赵本山-
-赵本山-

引用来自“mark35”的评论

mysql上面的许多所谓优化技巧(比如lz发现的“大表 左关联 小表,很慢;小表 左关联 大表,很快。”)根本原因是因为mysql比较烂,除非是在大企业,在它上面的深入研究和优化基本上是在浪费生命。
你这么屌。公司领导知道么?
OSChina 技术周刊第29期 —— HTTP 有时候比 HTTPS 好?

每周技术抢先看,总有你想要的! 移动开发 【软件】iOS 图表控件 ios-charts 【软件】跨平台应用开发框架 Bridge.NET 【博客】为什么不能往 Android 的 Application 对象里存储数据 【博客】...

OSC编辑部 ⋅ 2015/04/12 ⋅ 0

OSChina 技术周刊第二十九期 —— HTTP 有时候比 HTTPS 好?

每周技术抢先看,总有你想要的! 移动开发 【软件】iOS 图表控件 ios-charts 【软件】跨平台应用开发框架 Bridge.NET 【博客】为什么不能往 Android 的 Application 对象里存储数据 【博客】...

OSC编辑部 ⋅ 2015/04/12 ⋅ 0

java面试常用问题

MYSQL一个语句很慢,怎么调优法? 因为问题是调优,也就是基本不能改变整体的现有实现。下面分析慢的原因并给出对应可能正确的解决方案 1、SQL语句本身的优化,比如去掉不必要的排序等。 2、...

ovirtKg ⋅ 2016/12/02 ⋅ 0

谈谈SQL慢查询的解决思路

最近,在运维部及DBA同事的帮助和大家的共同努力下,对项目中的慢SQL进行了优化和修正,效果还是很明显的,在此给大家点一个大大的赞。为了让我们在SQL的处理上更为合理,形成可实践、可借鉴...

谢东升Forest ⋅ 2017/08/03 ⋅ 0

mysql 优化步骤

优化SQL语句的一般步骤: 1. 通过 show status 命令了解各种SQL的执行效率。 show status like 'com%' Comxxx表示每个xxx语句执行的次数,我们通常比较关心的是一下几个参数: Comselect: 执...

nao ⋅ 2015/12/15 ⋅ 0

一文教会你数据库性能调优(附某大型医院真实案例)

原文:一文教会你数据库性能调优(附某大型医院真实案例) 前言 微软工程师的一个工程师曾经对性能调优有一个非常形象的比喻:剥洋葱 。我也非常认可,让我们来一层一层拨开外面它神秘的面纱。...

杰克.陈 ⋅ 01/15 ⋅ 0

MySQL优化思路,以及解决方案

mysql优化索引和配置,以及慢查询分析 s首先基本的思路 1)性能瓶颈定位 使用show命令、 慢查询日志、 explain分析查询、 profiling分析查询、 2)索引及查询优化 3)配置优化 MySQL数据库常...

tty之星 ⋅ 2017/06/18 ⋅ 0

一个SQL查询优化问题

有一张数据表(company_table)存放企业基本信息,约有记录 150W 主要字段 id(主键,自增) company_name companyotherinfo 1 XXX科技有限公司 …… 2 XXX公司 …… …… XXX …… 1500000 XX...

归海一刀 ⋅ 2013/09/01 ⋅ 19

SQL语句的优化

SQL语句的优化 如何索取有性能问题SQL的渠道 通过用户反馈获取存在性能问题的SQL 通过慢查日志获取存在性能问题的SQL 实时获取存在性能问题的SQL 慢查询日志介绍 slowqueylog=on 启动记录慢查...

Panda_Jerry ⋅ 2017/11/14 ⋅ 0

一次非常有意思的sql优化经历

场景 我用的数据库是mysql5.6,下面简单的介绍下场景 课程表 create table Course( c_id int PRIMARY KEY, name varchar(10) ) 数据100条 学生表: create table Student( id int PRIMARY KE...

rewiner22 ⋅ 2016/11/30 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Centos7重置Mysql 8.0.1 root 密码

问题产生背景: 安装完 最新版的 mysql8.0.1后忘记了密码,向重置root密码;找了网上好多资料都不尽相同,根据自己的问题总结如下: 第一步:修改配置文件免密码登录mysql vim /etc/my.cnf 1...

豆花饭烧土豆 ⋅ 52分钟前 ⋅ 0

熊掌号收录比例对于网站原创数据排名的影响[图]

从去年下半年开始,我在写博客了,因为我觉得业余写写博客也还是很不错的,但是从2017年下半年开始,百度已经推出了原创保护功能和熊掌号平台,为此,我也提交了不少以前的老数据,而这些历史...

原创小博客 ⋅ 今天 ⋅ 0

LVM讲解、磁盘故障小案例

LVM LVM就是动态卷管理,可以将多个硬盘和硬盘分区做成一个逻辑卷,并把这个逻辑卷作为一个整体来统一管理,动态对分区进行扩缩空间大小,安全快捷方便管理。 1.新建分区,更改类型为8e 即L...

蛋黄Yolks ⋅ 今天 ⋅ 0

Hadoop Yarn调度器的选择和使用

一、引言 Yarn在Hadoop的生态系统中担任了资源管理和任务调度的角色。在讨论其构造器之前先简单了解一下Yarn的架构。 上图是Yarn的基本架构,其中ResourceManager是整个架构的核心组件,它负...

p柯西 ⋅ 今天 ⋅ 0

uWSGI + Django @ Ubuntu

创建 Django App Project 创建后, 可以看到路径下有一个wsgi.py的问题 uWSGI运行 直接命令行运行 利用如下命令, 可直接访问 uwsgi --http :8080 --wsgi-file dj/wsgi.py 配置文件 & 运行 [u...

袁祾 ⋅ 今天 ⋅ 0

JVM堆的理解

在JVM中,我们经常提到的就是堆了,堆确实很重要,其实,除了堆之外,还有几个重要的模块,看下图: 大 多数情况下,我们并不需要关心JVM的底层,但是如果了解它的话,对于我们系统调优是非常...

不羁之后 ⋅ 昨天 ⋅ 0

推荐:并发情况下:Java HashMap 形成死循环的原因

在淘宝内网里看到同事发了贴说了一个CPU被100%的线上故障,并且这个事发生了很多次,原因是在Java语言在并发情况下使用HashMap造成Race Condition,从而导致死循环。这个事情我4、5年前也经历...

码代码的小司机 ⋅ 昨天 ⋅ 2

聊聊spring cloud gateway的RetryGatewayFilter

序 本文主要研究一下spring cloud gateway的RetryGatewayFilter GatewayAutoConfiguration spring-cloud-gateway-core-2.0.0.RC2-sources.jar!/org/springframework/cloud/gateway/config/G......

go4it ⋅ 昨天 ⋅ 0

创建新用户和授予MySQL中的权限教程

导读 MySQL是一个开源数据库管理软件,可帮助用户存储,组织和以后检索数据。 它有多种选项来授予特定用户在表和数据库中的细微的权限 - 本教程将简要介绍一些选项。 如何创建新用户 在MySQL...

问题终结者 ⋅ 昨天 ⋅ 0

android -------- 颜色的半透明效果配置

最近有朋友问我 Android 背景颜色的半透明效果配置,我网上看资料,总结了一下, 开发中也是常常遇到的,所以来写篇博客 常用的颜色值格式有: RGB ARGB RRGGBB AARRGGBB 这4种 透明度 透明度...

切切歆语 ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部