MySQL索引对NULL值的处理

 

# 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

在很多库表设计规范、某某军规的文章中，是不是经常会看到类似这样的内容。小编也经常看到这样的内容，并且在编写规范的时候，准备也把这一条加进去。但在按部就班之余，小编抽空验证了一下，发现事实却并非如此！

小编使用的MySQL版本是社区版 5.7.21

 

新建测试表 t1，插入不含NULL值得100行数据，然后插入1行带NULL的数据 insert into t1(id) values(101); 表中有主键id，索引a

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

 

测试1，包含NULL单列索引的查询，可以看到即使是查找 IS NULL的行，也是可以用上索引的

测试1：

desc select * from t1 where a > 82;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 5       | NULL |   18 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+