MySQL 索引列包涵 null，查询也不会出错啊？

This topic created in 3238 days ago, the information mentioned may be changed or developed.

高性能 MySQL 介绍说不要在 null 列上加索引 ...

---------

我尝试在默认值为 null 的 nickname 的列上加索引，然后

select * from profiles where nickname is null;

查询的结果也都正常啊，并没有出错。当然查询速度是不是会慢不确定（数据量小感觉不出来）。MySQL5.5 / 5.6 版本都没报错，另外，这个查询是不是走了索引怎么看？

null

索引

MySQL

nickname

6 replies • 2017-07-18 10:12:33 +08:00

justtery

Jul 18, 2017 via Android

是不会出错的吧，不过似乎索引就没用了

sagaxu

Jul 18, 2017 via Android

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

syncher

Jul 18, 2017 via Android

2 楼正解

lamCJ

Jul 18, 2017

InnoDB MyISAM MEMORY NDB 都支持在非主键的 nullable 字段建索引（ mysql5.6/5.7 ）

see: [Create Index@Table 13.2 Storage Engine Index Characteristics]( https://dev.mysql.com/doc/refman/5.7/en/create-index.html)

分析查询计划：EXPLAIN|DESC|DESCRIBE SQL_STATEMENT => possible_keys/key

书上说不要不代表 MySQL 会报错，只是作者给的性能上的最佳实践建议，对于 NULL，不光是最好不要在 nullable 上建索引，而且字段能 NOT NULL 就 NOT NULL，除非你要存 NULL （ unknown value ）

rogwan

Jul 18, 2017

@lamCJ

情况是这样的：这个“昵称” nickname 字段里，只有不到 10%比率的用户会填昵称（因为是可选项），所以当我要找出哪些是填写了 nickname 的用户，就要用到 select user_id from profiles where nickname is not null;

如果用 default = 0 代替 null，要实现上面的查询，就要改成 nickname != 0，这样的查询效率会比 is not null 更高吗？

rrfeng

Jul 18, 2017

如果大量的 null 在里面会导致索引区分度下降。

例如你有 100w 用户，90% 是 null，那么用 nickname IS NULL 去查出 90w 来，有什么意义？

null 和 0 没有本质区别。