- 一 创建表
- 1.1 我们先创建一张学生表：
- 1.2 创建索引
- 二 测试编写SQL进行测试
- 2.1整个SQL语句里没用where子句
- 2.2 添加where子句
本文所有示例都使用的是MySQL和DBeaver。
为了直观地认识到哪些写法会容易导致全表扫描，我们这里进行实际操作。
一 创建表 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子句没有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);
然后看看是否索引有效。
索引有效的情况
此时在执行计划里面可以看到，类型是ref，此次查询使用的键就是我们刚刚创建的索引name_index，证明索引有效。然后我们把查询条件的name改为使用is null或者is not null试试：
is null导致的全表扫描
is not null导致的全表扫描
我们看到，查询条件列虽然有索引，但是如果使用了is null或者is not null就全表扫描了。
**建议：**在设计表的时候，就应该考虑到，将那些可能会被用作查询条件的列设置为not null,不允许该列出现空值，并指定该列的默认值。
-
查询条件列虽然有索引，但是where筛选条件中使用了!=或者<>操作符。
我们还是基于上面的查询语句做下变更：
使用!=导致的全表扫描
使用<>导致的全表扫描
但是如果我们使用<,>,<=,>=,=,between,in以及部分like的场景，都是可以走索引的，下面我们仅仅针对in的情况给出示例：
使用in的时候可以走索引
-
查询条件列虽然有索引，但是不恰当地使用了like。
当需要模糊查询的时候，需要避免使用前模糊，我们先来看下使用前模糊造成的后果：
使用前模糊导致的全表扫描
但是如果我们使用后模糊查询，就会走索引：
使用后模糊的时候可以走索引
-
查询条件列虽然有索引，但是在查询条件列上使用了函数或者计算操作。
我们先给年龄字段也加上索引。
alter table test_student add index age_index(age);
然后普通的索引列查询是走索引的，没有问题。
年龄字段正常走索引
我们在年龄字段虽然加上了索引，但是如果在查询的时候加上了计算：
索引列加上计算导致的全表扫描
如果使用函数：
索引列加上函数导致的全表扫描
建议：
可以把ts.age/2 = 11改成ts.age = 22，计算操作挪到等号右边。
-
查询条件列虽然有索引，但是在查询条件列上发生了隐式转换。
比如我们现在有个记录的name字段值为2，然后我们以字符串形式查询时没有问题：
索引查询
但是如果我们使用数值进行查询，就发生了隐式转换，系统自动将数值转换为字符串再查询：
索引列发生隐式转换导致的全表扫描
但是如果我们自己做了显式转换，就没有问题，索引仍旧可以使用：
索引列显式转换
-
查询条件列虽然有索引，但是在查询条件列上数据的区分度不明显。
我们先来给年级字段也加上索引。
alter table test_student add index grade_index(grade);
然后使用该索引列发起查询：
索引列数据区分度不大导致的全表扫描
因为真实数据中的该列值除了1就是2，所以区分度不大时也无法使用索引。
暑期编程PK赛 得CSDN机械键盘等精美礼品！ 每日推荐
文章开始之前我想首先介绍一下牛客，以便没有使用过的小伙伴能够快速入手，牛客网是国内最大的算法、面试、招聘网站，涵盖了多种大厂面试真题以及题解，里面大佬云集，各种题目的解决方案层出不穷，绝对能让你大开眼界，而且牛客是你在人生中不同的阶段都能对你有所帮助的编程软件（完全免费），如果感兴趣可以访问注册一下
访问链接：牛客-国内最大的刷题网站
