【数据库专题】实战演示造成数据库全表扫描的坑

小鱼儿 2024-05-06 09:43 146阅读 0赞

#### 文章目录 ####

*  一 创建表
 *   *  1.1 我们先创建一张学生表：
     *  1.2 创建索引
 *  二 测试编写SQL进行测试
 *   *  2.1整个SQL语句里没用where子句
     *  2.2 添加where子句

本文所有示例都使用的是MySQL和DBeaver。

为了直观地认识到哪些写法会容易导致全表扫描，我们这里进行实际操作。

> ?每日推荐
> 
> ?文章开始之前我想首先介绍一下[牛客][Link 1]，以便没有使用过的小伙伴能够快速入手，[牛客网][Link 1]是国内最大的算法、面试、招聘网站，涵盖了多种大厂面试真题以及题解，里面大佬云集，各种题目的解决方案层出不穷，绝对能让你大开眼界，而且[牛客][Link 1]是你在人生中不同的阶段都能对你有所帮助的编程软件（完全免费），如果感兴趣可以访问注册一下
> 
> 访问链接：[牛客-国内最大的刷题网站][Link 1]

## 一 创建表 ##

### 1.1 我们先创建一张学生表： ###

create table test_student(
        id int primary key auto_increment,
        name varchar(100),
        gender varchar(2),
        age int,
        address varchar(200),
        email varchar(100),
        grade varchar(2)
    )

### 1.2 创建索引 ###

在这张表中，除了主键id有主键索引外，其它字段暂时都没有索引。然后再初始化一些数据：

create
        procedure test_batch_insert()
    begin
        declare cnt int default 0;
    
    while cnt < 100 do insert
        into
            test01.test_student (name,
            gender,
            age,
            address,
            email,
            grade)
        values(concat('unkonwn', cnt),
        '女',
        24 + cnt,
        '大唐长安',
        'libai@tang',
        '2');
    
    set
    cnt = cnt + 1;
    end while;
    end;
    call test_batch_insert();

初始化的数据各位可以自行定义，此处仅供参考。

## 二 测试编写SQL进行测试 ##

### 2.1整个SQL语句里没用where子句 ###

![img][]

没有where导致的全表扫描

类型为ALL就代表全表扫描，在后面的键列都是空的，说明此次查询没有可以使用的键。

\*\*建议：\*\*任何语句包括delete、update等，都一定要加上where条件，除了防止全表扫描，更要防止大规模地误修改了数据。

如果你就是要修改表中的所有数据怎么办？如果你就是不在乎是否走索引怎么做？可以加上`where created_date <= sysdate()`，如此虽然性能上可能没有实质性提升，但好歹显得更加规范了。

### 2.2 添加where子句 ###

**查询条件列虽然有索引，但是where筛选条件中使用了`is null`或者`is not null`。**

我们先给name字段加上索引：

`alter table test_student add index name_index(name);`

然后看看是否索引有效。  
![在这里插入图片描述][1b830a74632847dab026f22fd18f2151.webp_pic_center]

索引有效的情况

此时在执行计划里面可以看到，类型是ref，此次查询使用的键就是我们刚刚创建的索引`name_index`，证明索引有效。然后我们把查询条件的name改为使用`is null`或者`is not null`试试：

![img][img 1]

is null导致的全表扫描

![img][img 2]

is not null导致的全表扫描

我们看到，查询条件列虽然有索引，但是如果使用了`is null`或者`is not null`就全表扫描了。

\*\*建议：\*\*在设计表的时候，就应该考虑到，将那些可能会被用作查询条件的列设置为`not null`,不允许该列出现空值，并指定该列的默认值。

1.  **查询条件列虽然有索引，但是where筛选条件中使用了`!=`或者`<>`操作符。**
    
    我们还是基于上面的查询语句做下变更：

![img][img 3]

使用!=导致的全表扫描

![img][img 4]

使用<>导致的全表扫描

但是如果我们使用`<,>,<=,>=,=,between,in`以及部分like的场景，都是可以走索引的，下面我们仅仅针对in的情况给出示例：

![img][img 5]

使用in的时候可以走索引

1.  **查询条件列虽然有索引，但是不恰当地使用了like。**
    
    当需要模糊查询的时候，需要避免使用前模糊，我们先来看下使用前模糊造成的后果：

![img][img 6]

使用前模糊导致的全表扫描

但是如果我们使用后模糊查询，就会走索引：  
![在这里插入图片描述][9267ceb20eda4dabbe22ef6683082a64.webp_pic_center]

使用后模糊的时候可以走索引

1.  **查询条件列虽然有索引，但是在查询条件列上使用了函数或者计算操作。**
    
    我们先给年龄字段也加上索引。
    
    `alter table test_student add index age_index(age);`
    
    然后普通的索引列查询是走索引的，没有问题。

![在这里插入图片描述][a87999071a404e25bc6af1ecd80d9f3c.webp_pic_center]

年龄字段正常走索引

我们在年龄字段虽然加上了索引，但是如果在查询的时候加上了计算：

![img][img 7]

索引列加上计算导致的全表扫描

如果使用函数：

![img][img 8]

索引列加上函数导致的全表扫描

**建议：**

可以把`ts.age/2 = 11`改成`ts.age = 22`，计算操作挪到等号右边。

1.  **查询条件列虽然有索引，但是在查询条件列上发生了隐式转换。**
    
    比如我们现在有个记录的name字段值为2，然后我们以字符串形式查询时没有问题：

![img][img 9]

索引查询

但是如果我们使用数值进行查询，就发生了隐式转换，系统自动将数值转换为字符串再查询：

![img][img 10]

索引列发生隐式转换导致的全表扫描

但是如果我们自己做了显式转换，就没有问题，索引仍旧可以使用：

![img][img 11]

索引列显式转换

1.  查询条件列虽然有索引，但是在查询条件列上数据的区分度不明显。
    
    我们先来给年级字段也加上索引。
    
    `alter table test_student add index grade_index(grade);`
    
    然后使用该索引列发起查询：

![img][img 12]

索引列数据区分度不大导致的全表扫描

因为真实数据中的该列值除了1就是2，所以区分度不大时也无法使用索引。

> ? 每日推荐
> 
> ?文章开始之前我想首先介绍一下[牛客][Link 1]，以便没有使用过的小伙伴能够快速入手，[牛客网][Link 1]是国内最大的算法、面试、招聘网站，涵盖了多种大厂面试真题以及题解，里面大佬云集，各种题目的解决方案层出不穷，绝对能让你大开眼界，而且[牛客][Link 1]是你在人生中不同的阶段都能对你有所帮助的编程软件（完全免费），如果感兴趣可以访问注册一下
> 
> 访问链接：[牛客-国内最大的刷题网站][Link 1]

[Link 1]: https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=199&fromPut=pc_csdncpt_ddssyx_sql
[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/b772bdc8bdf24a2887bd14a6d454233c.png
[1b830a74632847dab026f22fd18f2151.webp_pic_center]: https://img-blog.csdnimg.cn/1b830a74632847dab026f22fd18f2151.webp#pic_center
[img 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/0b604ec410d44f389bbe2a9e57c79fd5.png
[img 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/30a05b2e2cb44b0c858765d6c82ca004.png
[img 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/d7eadaccc11945b093833968dcebe525.png
[img 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/89803a1df603450c8c1026d91f95d97d.png
[img 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/a20ee10b8e06470d93848fc16b3813ab.png
[img 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/a3b6b16bd1de4a0db4b6f34467c3a66f.png
[9267ceb20eda4dabbe22ef6683082a64.webp_pic_center]: https://img-blog.csdnimg.cn/9267ceb20eda4dabbe22ef6683082a64.webp#pic_center
[a87999071a404e25bc6af1ecd80d9f3c.webp_pic_center]: https://img-blog.csdnimg.cn/a87999071a404e25bc6af1ecd80d9f3c.webp#pic_center
[img 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/6cdc7d8a87e34fe386f2c49b500f1b6f.png
[img 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/28dc7b72ddca401fba868767ddf2214d.png
[img 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/90ae4b6c53854412abbd62fdfe2167c0.png
[img 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/6002191454314fd28e35a6f4575270bf.png
[img 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/4cbe1d698b5749b28a365861cb900e50.png
[img 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/a3c627ced3ff4b69a0c234b4b563a570.png