浅谈mysql中的null - 能不能用索引

浅谈mysql中的null - 能不能用索引

照旧，在开始前，先附上本次试验的ddl, 然后插入数据，随机抽取几条幸运数据的name设为null

CREATE TABLE `user` (
  `id` int NOT NULL COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `age` int NOT NULL COMMENT '年龄',
  `sex` tinyint(1) NOT NULL COMMENT '性别',
  `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '电话',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_sex` (`name`,`age`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

MySQL中IS NULL、IS NOT NULL、!= 等能不能用索引？

先说结论，能！能！能！（重要的事情说三遍）

1. is null

EXPLAIN SELECT * FROM user WHERE name IS NULL

---

1. 表中满足筛选条件的数据量较多时, 不走索引

   当表中只有1条数据name不为null，其余数据均为null时

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	user	$(Null)$	$ALL$	idx_name_age_sex	$(Null)$	$(Null)$	$(Null)$	$99$	98.99	Using where

2. 表中满足筛选条件的数据量较少时, 走索引

   当表中只有17条数据name为null时

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	user	$(Null)$	ref	$idx\_name\_age\_sex$	$idx\_name\_age\_sex$	1023	const	$17$	100.00	Using index condition

2. is not null

EXPLAIN SELECT * FROM user WHERE name IS NOT NULL

---

1. 表中满足筛选条件的数据量较多时, 不走索引

   当表中只有17条数据name为null，其余数据均不为null时

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	user	$(Null)$	$ALL$	idx_name_age_sex	$(Null)$	$(Null)$	$(Null)$	$99$	82.83	Using where

2. 表中满足筛选条件的数据量较少时, 走索引

   当表中只有1条数据name不为null时

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	user	$(Null)$	ref	$idx\_name\_age\_sex$	$idx\_name\_age\_sex$	1023	const	$1$	100.00	Using index condition

3. !=

EXPLAIN SELECT * FROM user WHERE name  != 'user1'

1. 表中为null数据较多时, 走索引

   当表中只有1条数据name不为null，其余数据均为null时

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	user	$(Null)$	range	$idx\_name\_age\_sex$	$idx\_name\_age\_sex$	1023	$(Null)$	$2$	100.00	Using index condition

2. 表中为null数据较少时, 不走索引

   当表中只有17条数据name为null，其余数据均不为null时

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	user	$(Null)$	$ALL$	idx_name_age_sex	$(Null)$	$(Null)$	$(Null)$	$99$	82.83	Using where

4. =

EXPLAIN SELECT * FROM user WHERE name  != 'user1'

不管为null数据量多少，都走索引

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user	$(Null)$	ref	$idx\_name\_age\_sex$	$idx\_name\_age\_sex$	1023	const	$1$	100.00	$(Null)$

综述

当索引列运行为null时，还是能走索引的，但具体走不走索引，还得取决于 走索引和不走索引哪个都成本较小。
注意，这里说的走不走索引，都是指的非聚簇索引。

非聚簇索引的扫描成本由两部分组成：

1. 读取索引记录成本
2. 回表成本 (通过非聚簇索引得到的主键到聚簇索引中查询)

如果查询读取的非聚簇索引越多，需要回表查询的次数就越多，当达到一定比例后，走索引的成本比不走索引的成本还高时，最终的查询就不走索引了，这也就是索引失效的原因