测试杂谈——一条SQL引发的思考

此篇只是个人记录,相信各位大神早已轻车熟路,不喜勿喷;有错之处,欢迎指正。
有一天收到新人的咨询,是关于sql的问题。
问题1:为什么sql查询的数据与界面展示的不准确;
问题2:为什么sql查询时间那么久。
在仔细查看sql后,问题得到解决。这个问题很简单,来看sql,如下所示:

SELECT v.ep_id, COUNT(*) FROM dw_l_v v WHERE v.id = 'XXX' AND v.ep_id = 'XXX' OR v.ep_id = 'XXX' OR v.ep_id = 'XXX' OR v.ep_id = 'XXX' AND v.language = 'zh_cn' GROUP BY v.ep_id;

问题给新人解答后,自己有感而发,所以稍加整理了此篇。
sql查询数据不对 依然来看这2个问题,先看问题1,数据不对的问题。
这个问题很好解答,在SQL必知必会的章节中,也有提到该问题。出现这个问题的原因,就在于,数据过滤条件的优先级了。
常用的逻辑运算符有:and / or / not
所对应的优先级为:() > not > and > or
sql在处理OR操作符前,优先处理AND操作符。
由于上述sql没有将OR操作符用小括号提高优先级,从而导致先处理了AND操作符,再来处理OR操作符,最终结果就是数据不准确。
我们将sql修改成如下即可:
SELECT v.ep_id, COUNT(*) FROM dw_l_v v WHERE v.id = 'XXX' AND (v.ep_id = 'XXX' OR v.ep_id = 'XXX' OR v.ep_id = 'XXX' OR v.ep_id = 'XXX' ) AND v.language = 'zh_cn' GROUP BY v.ep_id;

或者ep_id字段条件用IN操作符来处理,修改如下:
SELECT v.ep_id, COUNT(*) FROM dw_l_v v WHERE v.id = 'XXX' AND v.ep_id IN ('XXX','XXX','XXX','XXX') AND v.language = 'zh_cn' GROUP BY v.ep_id;

sql调整后,再来执行核对数据,数据查询准确。
我们从上述修改的sql看出,IN操作符与OR操作符实现的功能是一样的。的确是这样,最终得到的结果完全一致。
但IN操作符对比OR操作符,还有如下优点:
  • 存在很多合法选项时,IN操作符的语法更清楚、更直观;
  • 在与其他AND和OR操作符组合使用IN时,求值顺序更容易管理;
  • IN操作符一般比一组OR操作符执行的更快;
  • IN最大优点是可以包含其他 SELECT 语句,能够动态的建立 WHERE 子句。
sql查询时间久 在解决了sql查询数据不对的问题,我们再来看sql查询花费时间久的问题。
在mysql中,我们可以通过 EXPLAIN 来查看sql的执行计划。我们分别查看修改前跟修改后sql的执行计划,如下所示:
修改前的sql执行计划:
测试杂谈——一条SQL引发的思考
文章图片

修改后的sql执行计划:
测试杂谈——一条SQL引发的思考
文章图片

直接拿2张图来对比,为什么查询慢,想必就很清楚了吧,一目了然。
从图中可知,修改前的sql,rows 字段,客户端发送160W+的数据量;而修改后的sql,rows 字段,客户端发送2000+的数据量。先不对比其他的字段,单凭这点就可以看出sql的问题所在了。
由于数量级的差异,从而sql查询的花费时间也就不一样了。
在不修改之前,sql查询的时间要花费15s左右;而修改后的sql查询,查询结果的时间1s都不需要。
从上而知,平时在项目中的sql优化也是很有必要的。
在看完rows字段后,我们再来看个type字段。
通过type字段可以看出,修改前的sql,是使用了全表扫描,所以看到这,也能解答为什么客户端发送了160W+的数据量了,原因就是全表扫描的原因。
mysql执行计划的其他字段,大家可以自行百度了解,今天就先不多聊了。
【测试杂谈——一条SQL引发的思考】好了,今天的分享就到这了,文章有误之处,欢迎批评指正。

    推荐阅读