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

背景

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

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

复杂SQL语句的构成

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

关联查询

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

子查询

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

耗时在哪?

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

(思考空间。。。。)

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

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

问题定位

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

初步断定

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

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

再进一步验证

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

结果很容易发现,当我把子查询单独在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批量查询。

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

1 5 收藏 6 评论

相关文章

可能感兴趣的话题



直接登录
最新评论
  • 我好像也用到了嵌套  不过数据少  没感觉到速度问题。

  • oliver_lv Python工程师 2016/11/02

    这种情况可能是因为你外部返回的行远大于内部子查询返回的行,对于in语句,mysql优化器可能会重写成exists语句,譬如下面这样 select * from table1 where  table1.id in (select id from table2 ). mysql优化器会重写为 select  * from table1 where exists (select id from table2 where  table2.id = table1.id); 如果外部查询返回M行,内部返回N行。那么查询时间复杂度就是0(M*N)。这也许是查询慢的原因。而当你把子查询的结果作为in(1,2,3,4)。mysql优化器不会那么重写为exists了。所以时间复杂度最多为0(M+N) 。这就是查询快了的原因。对于in查询一般的优化方式是用join代替。譬如写成这样试试速度怎么样?(SELECT * FROM a_table AS a
    LEFT JOIN b_table AS b ON a.id=b.id ) as m
    join  (
    SELECT DISTINCT id FROM a_table
    WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
    )  as n on m.id=n.id 不知道我语句写的对不对,反正就是那个意思,此外优化看看有没有在关键字段上有没有加索引,譬如你语句中的user_id。但对于in语句有时候不一定会用到索引。具体分析可以explain 你的sql 语句看看。 希望我的回答能给你带去帮助。

  • 有同感,我曾经把一个9s+的查询改成了3s以内,时间越长改短好改,时间短了再更短,就不是一个级别的问题了,哈哈

  • 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)

跳到底部
返回顶部