MySQL的NULL值、空值查询；模糊查询的like、%和=比较

MySQL的NULL值、空值查询；模糊查询的like、%和=比较（用初略测试数据）

mysql 用法 Explain

MySQL_执行计划详细说明

MySQL执行计划extra中的using index 和 using where using index 的区别

提要

​ 今天正好项目要设计数据库，再纠结以前没特地纠结的问题，那就是MySQL如果有字段可能不存在，是否要设置成NULL还是用NOT NULL default ''来设置空值，自己就无聊用3W条左右的数据试了下(数据量比较小，但是我自己电脑配置比较差，这样就已经跑了3000s左右。)

数据库表格原型

​ 话不多说，把测试用的表格贴上来。有4个字段，分别是自增主键id，标题title，简介profile，密码password。其中密码可能压根不存在，比如房间不需要输入密码就可以直接登录，而简介是不管有没有都需要至少显示为空值的（常理上我是这么想的）。

下面给除了本来就是主键的id以外的title、profile、password都设置了NORMAL索引，索引方法为BTREE

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for room
-- ----------------------------
DROP TABLE IF EXISTS `room`;
CREATE TABLE `room`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '标题',
  `profile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '可以为空值',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '可以压根没有密码',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `title`(`title`) USING BTREE,
  INDEX `profile`(`profile`) USING BTREE,
  INDEX `password`(`password`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

测试数据插入

​ 我自己测的时候，一开始之前插入了5-6左右的垃圾数据，问题不大。(我的垃圾电脑跑了差不多3000s，要是电脑也不是很好的，建议就没必要特地尝试了)

下面重点就3句：

insert into room values (null,CONCAT("title",i),"",NULL);

insert into room values (null,CONCAT("title_",i),CONCAT("",i),NULL);

insert into room values (null,CONCAT("title__",i),"",CONCAT("",i));

delimiter //                            #定义标识符为双斜杠
drop procedure if exists test;          #如果存在test存储过程则删除
create procedure test()                 #创建无参存储过程,名称为test
begin
    declare i int;                      #申明变量
    set i = 0;                          #变量赋值
    lp : loop                           #lp为循环体名,可随意 loop为关键字
        insert into room values (null,CONCAT("title",i),"",NULL);    #往test表添加数据
				insert into room values (null,CONCAT("title_",i),CONCAT("",i),NULL);    #往test表添加数据
				insert into room values (null,CONCAT("title__",i),"",CONCAT("",i));    #往test表添加数据
        set i = i + 1;                  #循环一次,i加一
        if i > 10000 then                  #结束循环的条件: 当i大于10时跳出loop循环
            leave lp;
        end if; 
    end loop;
    select * from room;                 #查看test表数据
end
//                                      #结束定义语句
call test(); 

几组测试+结果

首先，由上面的存储过程procedure，可以知道，3W条数据里面应该有2W行的profile为空值，2W行的password为NULL。然后3W条title都以字符串"title"开头。下面EXPLAIN指令看不懂的可以看看我上面推荐的文章，前人写得很详细了。

下面的观点比较主观，建议要是确实想知道区别，自己动手用自己设计的数据模拟。毕竟我这里就单表查询，也没有用什么复杂的查询条件啥的

1. password 通过判断NULL，能否用到索引，效率如何？

   从下面可以看出该查询语句is NULL用到了索引。

   按照上面推荐的文章的意思就是，这里索引用来进行键值查找，而没有被用来实际进行查找动作。（毕竟这里is NULL就够筛选了，本身其实也算是很具体的查找条件了）。

EXPLAIN	SELECT * FROM	room WHERE `password` is NULL;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	room	(Null)	ref	password	password	1023	const	15176	100.00	Using index condition

​ 下面查询(将近2W条)时间，按照运行的次数：0.224s->0.122s -> 后面基本就是0.1~0.17之间波动了。试了下删除索引，测试后，稳定下平均0.075s。索引这里设置索引等于浪费了空间，也没得到时间上的便宜。

SELECT * FROM	room WHERE `password` is NULL;

对比

​ 可以看出 is NOT NULL没有用到索引，直接全表查询了（主要还是因为搜索的字段是*，所有列）。

EXPLAIN	SELECT * FROM	room WHERE `password` is NOT NULL;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	room	(Null)	ALL	password	(Null)	(Null)	(Null)	30352	50.00	Using Where

​ 下面查询(将近1W条)时间，按照运行的次数(最初几次已经被漏记了)：0.68s->0.57s -> 后面基本就是0.05·0.06之间波动了。虽然走的全表查询，但是时间却差不多是is NULL的1/2，查询出来的数据量正好也是其1/2。这里初略判断is NULL实际没有走索引，所以查询(数据量2倍)出来的时间为不走索引的is NOT NULL的两倍。后面我删除索引试了下，查询时间还是差不多0.058s左右波动，也就是索引确实没有起到作用，但是也没有明显“副作用”。

SELECT * FROM	room WHERE `password` is NOT NULL;

2. profile使用空值，而不用NULL来标识，看看效率如何。下面我试的情况比较多，就不一一说明了，自己看看就好。

• = ""因为获取的是所有字段，由于索引不是所有字段，获取所有列需要回表查询，所以没标识Using Index。

EXPLAIN SELECT * FROM	room WHERE `profile` = "";

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	room	(Null)	ref	profile	profile	1022	const	15176	100.00	(Null)

​ 这里我试了删除profile索引和保留索引的情况，结果有索引平均0.125s；没索引平均0.081s。没错，就是没索引反而快了，我没有打错字。

SELECT * FROM	room WHERE `profile` = "";

• =''，因为获取所有字段，所以没有用到索引。

EXPLAIN SELECT * FROM	room WHERE `profile` = '';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	room	(Null)	ref	profile	profile	1022	const	15152	100.00	(Null)

… 实在有点太多了。都有点打算放弃打字的形式了。

​ 上面不完整，主要觉得自己讲得不是很清楚，直接贴出我测的所有情况应该会更直观一点。反正看到这里的人应该都大概懂了。我就直接按照我想到的情况，把测试结果都贴出来得了。

---

下面贴出我测试的各种情况的结果（下面查询时间都是取多次查询后的稳定时间）

1. password和is NULL

• password有设置NORMAL索引，索引方式为BTREE时

EXPLAIN SELECT * FROM	room WHERE `password` is NULL; -- Using index condition
EXPLAIN SELECT `id` FROM	room WHERE `password` is NULL; -- Using where; Using index
EXPLAIN SELECT `title