SQL别再用count查找是否存在
   一些代码   0 评论   1071 浏览

SQL别再用count查找是否存在

   一些代码   0 评论   1071 浏览

根据某一条件从数据库表中查询 『有』与『没有』,只有两种状态,那为什么在写SQL的时候,还要SELECT count(*) 呢?

目前多数人的写法

多次REVIEW代码时,发现如下现象:
业务代码中,需要根据一个或多个条件,查询是否存在记录,不关心有多少条记录。普遍的SQL及代码写法如下。

SQL写法:

SELECT count(*) FROM table WHERE a = 1 AND b = 2

Java写法:

int nums = xxDao.countXxxxByXxx(params);
if ( nums > 0 ) {
  //当存在时,执行这里的代码
} else {
  //当不存在时,执行这里的代码
}

是不是感觉很OK,没有什么问题

优化方案

SQL写法:

SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

Java写法:

Integer exist = xxDao.existXxxxByXxx(params);
if ( exist != NULL ) {
  //当存在时,执行这里的代码
} else {
  //当不存在时,执行这里的代码
}

SQL不再使用count,而是改用LIMIT 1,让数据库查询时遇到一条就返回,不要再继续查找还有多少条了。

业务代码中直接判断是否非空即可。

总结

根据查询条件查出来的条数越多,性能提升的越明显,在某些情况下,还可以减少联合索引的创建。

为什么都说SELECT * 效率低

面试官:“小陈,说一下你常用的SQL优化方式吧 icon_surprised.png 。”
陈小哈:“那很多啊,比如不要用SELECT *,查询效率低。巴拉巴拉... guzhang.png

面试官:“为什么不要用SELECT * ?它在哪些情况下效率低呢? icon_question.png
陈小哈:“SELECT * 它好像比写指定列名多一次全表查询吧,还多查了一些无用的字段。 guzhang.png

面试官:“嗯... icon_mrgreen.png
陈小哈:“emmm~ 没了 icon_exclaim.png

陈小哈:“....??(几个意思)”

面试官:“嗯...好,那你还有什么要问我的么? icon_lol.png
陈小哈:“我问你个锤子,把老子简历还我! icon_mad.png


无论在工作还是面试中,关于SQL中不要用“SELECT *”,都是大家听烂了的问题,虽说听烂了,但普遍理解还是在很浅的层面,并没有多少人去追根究底,探究其原理。

废话不多说,本文带你深入了解一下"SELECT * "效率低的原因及场景。

不需要的列会增加数据传输时间和网络开销

对于无用的大字段,如 varchar、blob、text,会增加 io 操作

失去MySQL优化器“覆盖索引”策略优化的可能性

例如,有一个表为t(a,b,c,d,e,f),其中,a为主键,b列有索引。

那么,在磁盘上有两棵 B+ 树,即聚集索引和辅助索引(包括单列索引、联合索引),分别保存(a,b,c,d,e,f)和(a,b),如果查询条件中where条件可以通过b列的索引过滤掉一部分记录,查询就会先走辅助索引,如果用户只需要a列和b列的数据,直接通过辅助索引就可以知道用户查询的数据。
如果用户使用select *,获取了不需要的数据,则首先通过辅助索引过滤数据,然后再通过聚集索引获取所有的列,这就多了一次b+树查询,速度必然会慢很多。

由于辅助索引的数据比聚集索引少很多,很多情况下,通过辅助索引进行覆盖索引(通过索引就能获取用户需要的所有列),都不需要读磁盘,直接从内存取,而聚集索引很可能数据在磁盘(外存)中(取决于buffer pool的大小和命中率),这种情况下,一个是内存读,一个是磁盘读,速度差异就很显著了,几乎是数量级的差异。

心得体会

有朋友问我,你对SQL规范那么上心,平时你写代码不会用SELECT * 吧?
咋可能啊,天天用。。代码里也在用 icon_wink.png ,其实我们的项目普遍很小,数据量也上不去,性能上还没有遇到瓶颈,所以比较放纵。

本文由 RawChen 发表, 最后编辑时间为:2020-07-19 14:12
如果你觉得我的文章不错,不妨鼓励我继续写作。

发表评论
选择表情
Top