实操演示——那些造成数据库全表扫描的坑

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

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

我们先创建一张学生表：

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)
)

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

create
    procedure test_batch_insert()
begin
    declare cnt int default 0;

while cnt 

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

1. 整个SQL语句中没有where子句。

没有where导致的全表扫描

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

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

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

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

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

   alter table test_student add index name_index(name);

   然后看看是否索引有效。

索引有效的情况

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

is null导致的全表扫描

is not null导致的全表扫描

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

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

3. 查询条件列虽然有索引，但是where筛选条件中使用了!=或者操作符。

   我们还是基于上面的查询语句做下变更：

使用!=导致的全表扫描

使用导致的全表扫描

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

使用in的时候可以走索引

4. 查询条件列虽然有索引，但是不恰当地使用了like。

   当需要模糊查询的时候，需要避免使用前模糊，我们先来看下使用前模糊造成的后果：

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

但是如果我们使用后模糊查询，就会走索引：

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

5. 查询条件列虽然有索引，但是在查询条件列上使用了函数或者计算操作。

   我们先给年龄字段也加上索引。

   alter table test_student add index age_index(age);

   然后普通的索引列查询是走索引的，没有问题。

年龄字段正常走索引

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

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

如果使用函数：

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

建议：

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

6. 查询条件列虽然有索引，但是在查询条件列上发生了隐式转换。

   比如我们现在有个记录的name字段值为2，然后我们以字符串形式查询时没有问题：

索引查询

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

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

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

索引列显式转换

7. 查询条件列虽然有索引，但是在查询条件列上数据的区分度不明显。

   我们先来给年级字段也加上索引。

   alter table test_student add index grade_index(grade);

   然后使用该索引列发起查询：

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

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

本文持续更新中，欢迎增加。