同事用这2个小技巧,让SQL语句效率提升了1000倍

数据库
本次来讲解与 SQL 查询有关的两个小知识点,掌握这些知识点,能够让你避免踩坑以及提高查询效率。

[[274763]]

 

本次来讲解与 SQL 查询有关的两个小知识点,掌握这些知识点,能够让你避免踩坑以及提高查询效率。

1. 允许字段的值为 null,往往会引发灾难

首先,先准备点数据,后面好演示

  1. create table animal( 
  2. id int
  3. name char(20), 
  4. index(id) 
  5. )engine=innodb; 

index(id) 表示给 id 这个字段创建索引,并且 id 和 name 都允许为 null。

接着插入4条数据,其中最后一条数据的 id 为。

  1. insert into animal(id, namevalues(1, '猫'); 
  2. insert into animal(id, namevalues(2, '狗'); 
  3. insert into animal(id, namevalues(3, '猪'); 
  4. insert into animal(id, namevalues(null'无名动物'); 

此时表中的数据为

同事用这2个小技巧,让SQL语句效率提升了1000倍

 

这时我们查询表中 id != 1 的动物有哪些

  1. select * from animal where id != 1; 

结果如下:

同事用这2个小技巧,让SQL语句效率提升了1000倍

 

此时我们只找到了两行数据,按道理应该是三行的,但是 id = null 的这一行居然没有被匹配到,,可能大家听说过,null 与任何其他值都不相等,按道理 null != 1 是成立的话,然而现实很残酷,它就是不会被匹配到。

所以,坚决不允许字段的值为 null,否则可能会出现与预期不符合的结果。

反正我之前有踩过这个坑,不知道大家踩过木有?

但是万一有人设置了允许为 null 值怎么办?如果真的这样的话,对于 != 的查找,后面可以多加一个 or id is null 的子句(注意,是 is null,不是 = null,因为 id = null 也不会匹配到值为 null 的行)。即:

  1. select * from animal where id != 1 or id is null

结果如下:

同事用这2个小技巧,让SQL语句效率提升了1000倍

 

2. 尽可能用 union 来代替 or

(1)、刚才我们给 id 这个字段建立了索引,如果我们来进行等值操作的话,一般会走索引操作,不信你看:

  1. explain select * from animal where id = 1; 

结果如下:

同事用这2个小技巧,让SQL语句效率提升了1000倍

 

通过执行计划可以看见,id 上的等值查找能够走索引查询(估计在你的意料之中),其中

  • type = ref :表示走非唯一索引
  • rows = 1 :预测扫描一行

(2)、那 id is null 会走索引吗?答是会的,如图

  1. explain select * from animal where id is null

同事用这2个小技巧,让SQL语句效率提升了1000倍

 

其中

  • type = ref :表示走非唯一索引
  • rows = 1 :预测扫描一行

(3)、那么问题来了,那如果我们要找出 id = 1 或者 id = null 的动物,我们可能会用 or 语句来连接,即

  1. select * from animal where id = 1 or id is null

那么这条语句会走索引吗?

有没有走索引,看执行计划就知道了,如图

  1. explain select * from animal where id = 1 or id is null

同事用这2个小技巧,让SQL语句效率提升了1000倍

 

其中:

  • ref = ALL:表示全表扫描
  • rows = 4 :预测扫描4行(而我们整个表就只有4行记录)

通过执行计划可以看出,使用 or 是很有可能不走索引的,这将会大大降低查询的速率,所以一般不建议使用 or 子句来连接条件。

那么该如何解决?

其实可以用 union 来取代 or,即如下:

  1. select * from animal where id = 1 union select * from animal where id is null

同事用这2个小技巧,让SQL语句效率提升了1000倍

 

此时就会分别走两次索引,找出所有 id = 1 和 所有 id = null 的行,然后再用一个临时表来存放最终的结果,最后再扫描临时表。 

责任编辑:庞桂玉 来源: 今日头条
相关推荐

2019-07-25 16:28:22

SQL数据库索引

2019-08-13 19:38:24

SQL数据索引

2020-07-08 17:06:00

Python开发工具

2020-09-23 16:51:15

办公软件电脑技术

2016-10-09 20:07:43

2023-09-25 13:15:50

SQL数据库

2022-04-21 07:51:51

场景JavaSQL

2023-12-28 08:01:59

2020-12-31 10:33:05

Python开发编程

2020-08-19 09:22:14

Python语言工具

2021-02-02 15:38:19

Disruptor缓存Java

2023-05-31 07:24:48

2023-11-03 15:33:22

Docker镜像云计算

2023-02-22 19:15:35

AI工具机器人

2021-06-25 10:20:07

Linux技巧命令

2022-09-09 09:33:14

支付宝代码性能

2019-09-10 11:31:16

Python数据分析表达式

2020-07-22 08:30:02

代码开发工具

2018-02-24 12:08:52

Python开发技巧
点赞
收藏

51CTO技术栈公众号