一个20秒SQL慢查询优化的经历与处理方案
暗夜在火星 发表于2年前
一个20秒SQL慢查询优化的经历与处理方案
  • 发表于 2年前
  • 阅读 7936
  • 收藏 302
  • 点赞 12
  • 评论 46

【粉丝福利】:web前端基础到高级实战免费在线直播教学>>>   

摘要: 1、大表 左关联 小表,很慢;小表 左关联 大表,很快。2、走出自身的思想误区,应对底层有深入理解才能正确使用。

背景

前几天在项目上线过程中,发现有一个页面无法正确获取数据,经排查原来是接口调用超时,而最后发现是因为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批量查询。


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

共有 人打赏支持
暗夜在火星
粉丝 128
博文 129
码字总数 281001
作品 1
评论 (46)
xiang-g
这个类比的sql有问题,子查询只meng能选user_id,其次在表连接
xiang-g
前可以先查询。尽量不要用in,换用exists
xiang-g

引用来自“xiang-g”的评论

这个类比的sql有问题,子查询只meng能选user_id,其次在表连接

软件回复有bug,不说了
廖君
应该是: 大表 左关联 小表很慢 ,小表 左关联 大表,很快。
暗夜在火星

引用来自“廖君”的评论

应该是: 大表 左关联 小表很慢 ,小表 左关联 大表,很快。
sorry, 已fixed
崇-子
EXPLAIN SELECT t_a.`a_id` FROM (

SELECT a_id, COUNT(*) AS num FROM t_a
WHERE user_id IN (1, 2, 3)
GROUP BY a_id
HAVING num > 2
) t_a
LEFT JOIN t_b ON t_a.a_id = t_b.`a_id`

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  PRIMARY  <derived2>  ALL          9  
1  PRIMARY  t_b  ref  ix_a_id  ix_a_id  4  t_a.a_id  1  Using index
2  DERIVED  t_a  ALL  ix_user_id        9  Using where; Using temporary; Using filesort
暗夜在火星

引用来自“崇-子”的评论

EXPLAIN SELECT t_a.`a_id` FROM (

SELECT a_id, COUNT(*) AS num FROM t_a
WHERE user_id IN (1, 2, 3)
GROUP BY a_id
HAVING num > 2
) t_a
LEFT JOIN t_b ON t_a.a_id = t_b.`a_id`

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  PRIMARY  <derived2>  ALL          9  
1  PRIMARY  t_b  ref  ix_a_id  ix_a_id  4  t_a.a_id  1  Using index
2  DERIVED  t_a  ALL  ix_user_id        9  Using where; Using temporary; Using filesort
谢谢,学习了
mark35
mysql上面的许多所谓优化技巧(比如lz发现的“大表 左关联 小表,很慢;小表 左关联 大表,很快。”)根本原因是因为mysql比较烂,除非是在大企业,在它上面的深入研究和优化基本上是在浪费生命。
咚往咚来
这种不如分两次查询
咚往咚来
如果有些场景关键很慢,不如在mysql之上整一个mongo来多次查mongo,我们公司就这样
狂奔的蜗牛.
为什么要join,一定有不用join的快速方法。不可能说某个业务不用join解决不了的,方法问题。
古城痴人
explain 一下看看执行计划
在下李景仰
很好的解决经验。我也遇到过
_Yud
试试这个

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

引用来自“狂奔的蜗牛.”的评论

为什么要join,一定有不用join的快速方法。不可能说某个业务不用join解决不了的,方法问题。
是可以不用join的写法,但复杂业务对应的SQL查询都用PHP外部来处理,代码复杂度会异常地高,因为有不定组其他的where条件
暗夜在火星

引用来自“古城痴人”的评论

explain 一下看看执行计划
explain 这个前位的同学有提及过,不过还是谢谢,学习了
暗夜在火星

引用来自“_Yud”的评论

试试这个

SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id , (
SELECT DISTINCT id FROM a_table
WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
) c
WHERE a.id =c.id
这SQL语句,看得不是很懂
暗夜在火星

引用来自“狂奔的蜗牛.”的评论

为什么要join,一定有不用join的快速方法。不可能说某个业务不用join解决不了的,方法问题。
而且不用join而用语言来处理,要考虑到中间过程的数据大小,如是否超出PHP内存限制。有时,是需要在性能和实现各方面权衡的
wrean2013
很好,学习了
__JM_Joy__
子查询确实很慢啊,不知道分多几次查怎样
×
暗夜在火星
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: