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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 2719 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

---------

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

select * from profiles where nickname is null;

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

null

索引

MySQL

nickname

6 条回复 • 2017-07-18 10:12:33 +08:00

justtery

2017-07-18 08:10:18 +08:00 via Android

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

sagaxu

2017-07-18 08:20:44 +08:00 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

2017-07-18 09:09:44 +08:00 via Android

2 楼正解

lamCJ

2017-07-18 09:51:48 +08:00

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

2017-07-18 10:08:48 +08:00

@lamCJ

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

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

rrfeng

2017-07-18 10:12:33 +08:00

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

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

null 和 0 没有本质区别。