测试杂谈——一条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 子句。
在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执行计划的其他字段,大家可以自行百度了解,今天就先不多聊了。
【测试杂谈——一条SQL引发的思考】好了,今天的分享就到这了,文章有误之处,欢迎批评指正。
推荐阅读
- 戏说领域驱动设计(十六)——实体概念
- 低代码开发|驰骋BPM低代码快速开发平台之—.NET版准备工作篇
- 蓝桥杯|[蓝桥杯]刷题日记——冲刺进国赛
- 数据结构|数据结构课程设计——学生成绩查询与分析系统(简单详细版,含讲解)
- python扫雷|python扫雷 广度优先_【原创教程】数据结构与算法(5)——广度与深度优先搜索...
- #|Python——数据结构——树——二叉树——二叉排序树
- 深度学习|YOLOv1——v5学习笔记
- 大数据|R语言——数据格式和数据读取
- java|Java并发编程—生产者消费者Java实现
- java|Java并发编程—ThreadLocalRandom类