通过分析下面的一个 mysql 数据库的案例，说明一下数据库允许空值(null)会带来哪些悲剧。
描述
存在一个表 user 如下：
1
2
3
4
5
6
7
| CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT "",
`age` int(11) , # 可以为NULL
PRIMARY KEY (`id`),
KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
插入数据如下：
1
| INSERT INTO `user` VALUES ('1', 'zhang', '12'), ('2', 'li', null), ('3', 'wang', '2'), ('4', 'liu', '4');
|
数据示意图：
1
2
3
4
5
6
7
8
| +----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | zhang | 12 |
| 2 | li | NULL |
| 3 | wang | 2 |
| 4 | liu | 4 |
+----+-------+------+
|
需求
在上面的表中，查找年龄不为12的用户信息
1
| SELECT * FROM `user` WHERE age!=12;
|
那么查询出的结果是什么呢？
结果如下：
1
2
3
4
5
6
| +----+------+------+
| id | name | age |
+----+------+------+
| 3 | wang | 2 |
| 4 | liu | 4 |
+----+------+------+
|
查询出出来的数据，并没有id=2的这一行，即没age=null这一行。如果我们需要包含null的信息，这就会造成漏查。
1
2
3
4
5
| +----+------+------+
| id | name | age |
+----+------+------+
| 2 | li | NULL |
+----+------+------+
|
分析
如果想要查找年龄不为12(包含null)的用户信息，就需要这样查询了
1
| SELECT * FROM `user` WHERE age!=12 or age is null;
|
可以查询出想要的数据，如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| +----+------+------+
| id | name | age |
+----+------+------+
| 2 | li | NULL |
| 3 | wang | 2 |
| 4 | liu | 4 |
+----+------+------+
```
但这会又带来新的问题，就是滥用 `or`可能会带来全表扫描的问题，影响查询速率。上面我们分析了null值的问题，下面我们分析`or`的问题。
**1** 如果我们查询`age=12`的用户，进行查询分析。
```TEXT
explain SELECT * FROM `user` WHERE age=12;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_age | idx_age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|
可见,等值查询，命中索引：
- type = ref 使用了非唯一索引。
- key=idx_age 使用索引idx_age
- rows=1 预计扫描1行
2 如果我们查询age is null的用户，进行查询分析。
1
2
3
4
5
6
7
| explain SELECT * FROM `user` WHERE age is null;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | ref | idx_age | idx_age | 5 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|
可见,也命中了索引：
- type = ref 使用了非唯一索引。
- key=idx_age 使用索引idx_age
- rows=1 预计扫描1行
3 如果我们查询age is null或者age =12的用户，进行查询分析。
1
2
3
4
5
6
7
| explain SELECT * FROM `user` WHERE age is null or age =12;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | idx_age | NULL | NULL | NULL | 4 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|
可见，使用了 or进行全表扫描：
- type = ALL 进行了全表扫描。
- rows=4 预计扫描4行
通过上面的三个查询案例，可见使用了or之后，查询性能暴跌！那怎么优化呢？使用UNION。
1
2
3
4
5
6
7
8
| explain SELECT * FROM `user` WHERE age =12 union SELECT * FROM `user` WHERE age=12;
+----+--------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------------+
| 1 | PRIMARY | user | NULL | ref | idx_age | idx_age | 5 | const | 1 | 100.00 | NULL |
| 2 | UNION | user | NULL | ref | idx_age | idx_age | 5 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------------+
|
可见：有命中了索引
注：第三行(id=NULL)临时表只是对两次查询结果进行合并，所以是ALL。跟查询无关。
综述
由null 的存在，很有可能会用到 or，有了or又会带来全表扫描的问题。
一般情况下，在设计表结构的时候，要避免null的存在。
避免使用null 可以在源头上避免一系列的坑。
经验之谈，数据库允许null，往往是悲剧的开始。所以，要慎之又慎。