MySQL 是否索引 NULL 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/289001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does MySQL index NULL values?
提问by too much php
I have a MySQL table where an indexed INTcolumn is going to be 0 for 90% of the rows. If I change those rows to use NULLinstead of 0, will they be left out of the index, making the index about 90% smaller?
我有一个 MySQL 表，其中INT90% 的行的索引列将为 0。如果我将这些行更改为 useNULL而不是 0，它们是否会被排除在索引之外，从而使索引小 90% 左右？
回答by Chu Khanh Van
http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html
http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html
MySQL can perform the same optimization on col_name IS NULLthat it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULLwith IS NULL.
MySQL 可以执行相同的优化col_name IS NULL，它可以用于col_name = constant_value. 例如，MySQL能使用索引和范围来搜索NULL与IS NULL。
回答by Bill the Lizard
It looks like it does index the NULLs too.
看起来它也索引了NULLs 。
Be careful when you run this because MySQL will LOCK the table for WRITES during the index creation. Building the index can take a while on large tables even if the column is empty (all nulls).
运行它时要小心，因为 MySQL 会在索引创建期间为 WRITES 锁定表。即使列为空（全为空），在大表上构建索引也可能需要一段时间。
参考。
回答by J.D. Fitz.Gerald
Allowing a column to be null will add a byte to the storage requirements of the column. This will lead to an increased index size which is probably not good. That said if a lot of your queries are changed to use "IS NULL" or "NOT NULL" they might be overall faster than doing value comparisons.
允许一列为空将增加一个字节到该列的存储要求。这将导致索引大小增加，这可能并不好。也就是说，如果您的许多查询更改为使用“IS NULL”或“NOT NULL”，它们总体上可能比进行值比较要快。
My gut would tell me not null, but there's one answer: test!
我的直觉会告诉我不为空，但有一个答案：测试！
回答by dkretz
No, it will continue to include them, but don't make too many assumptions about what the consequences are in either case. A lot depends on the range of other values (google for "cardinality").
不，它将继续包括它们，但不要对两种情况下的后果做出太多假设。很大程度上取决于其他值的范围（谷歌搜索“基数”）。
MSSQL has a new index type called a "filtered index" for this type of situation (i.e. includes records in the index based on a filter). dBASE-type systems used to have a similar capability, and it was pretty handy.
MSSQL 有一种新的索引类型，称为“过滤索引”，适用于这种情况（即基于过滤器在索引中包含记录）。dBASE 类型的系统曾经具有类似的功能，而且非常方便。
回答by Alix
Each index has a cardinality means how many distinct values are indexed. AFAIK it's not a reasonable idea to say indexes repeat the same value for many rows but the index will only addresses a repeated value to the clustered index of many rows (rows having null value for this field) and keeping the reference ID of the clustered index means : each row with a NULL value indexed field wastes a size as large as the PK (for this reason experts recommend to have a reasonable PK size if you have composite PK).
每个索引都有一个基数，表示索引了多少个不同的值。AFAIK 说索引对许多行重复相同的值不是一个合理的想法，但索引只会将重复值寻址到许多行的聚集索引（该字段具有空值的行）并保留聚集索引的引用 ID意思是：每行具有 NULL 值索引字段都会浪费与 PK 一样大的大小（因此，如果您有复合 PK，专家建议使用合理的 PK 大小）。
