背景
前几天在项目上线过程中，发现有一个页面无法正确获取数据，经排查原来是接口调用超时，而最后发现是因为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批量查询。 当我们决定使用一些底层的技术时，只有当我们理解透彻了，才能使用更为恰当。而因为无知就断定工具、框架、底层无所不能时，往往就会中招。 博文出处：http://my.oschina.net/dogstar/blog/398879
背景 前几天在项目上线过程中，发现有一个页面无法正确获取数据，经排查原来是接口调用超时，而最后发现是因为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批量查询。 当我们决定使用一些底层的技术时，只有当我们理解透彻了，才能使用更为恰当。而因为无知就断定工具、框架、底层无所不能时，往往就会中招。 博文出处：http://my.oschina.net/dogstar/blog/398879