测试杂谈 —— 一条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执行计划：

直接拿2张图来对比，为什么查询慢，想必就很清楚了吧，一目了然。

从图中可知，修改前的sql，rows 字段，客户端发送160W+的数据量；而修改后的sql，rows 字段，客户端发送2000+的数据量。先不对比其他的字段，单凭这点就可以看出sql的问题所在了。

由于数量级的差异，从而sql查询的花费时间也就不一样了。

在不修改之前，sql查询的时间要花费15s左右；而修改后的sql查询，查询结果的时间1s都不需要。

从上而知，平时在项目中的sql优化也是很有必要的。

在看完rows字段后，我们再来看个type字段。

通过type字段可以看出，修改前的sql，是使用了全表扫描，所以看到这，也能解答为什么客户端发送了160W+的数据量了，原因就是全表扫描的原因。

mysql执行计划的其他字段，大家可以自行百度了解，今天就先不多聊了。

好了，今天的分享就到这了，文章有误之处，欢迎批评指正。