对于mysql 字段设为NULL的疑惑

[natici]

看过一些博文，提及如果字段设为NULL，会影响该字段索引的使用；甚至有人说为NULL的字段，是用不上索引的。
不知道这类说法是否正确？

[ACMAIN_CHM]

.

引用

8.3.1.6 IS NULL Optimization 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. Examples: SELECT * FROM tbl_name WHERE key_col IS NULL; SELECT * FROM tbl_name WHERE key_col <=> NULL; SELECT * FROM tbl_name WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL; If a WHERE clause includes a col_name IS NULL condition for a column that is declared as NOT NULL, that expression is optimized away. This optimization does not occur in cases when the column might produce NULL anyway; for example, if it comes from a table on the right side of a LEFT JOIN. MySQL can also optimize the combination col_name = expr OR col_name IS NULL, a form that is common in resolved subqueries. EXPLAIN shows ref_or_null when this optimization is used. This optimization can handle one IS NULL for any key part. Some examples of queries that are optimized, assuming that there is an index on columns a and b of table t2: SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL; SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL; SELECT * FROM t1, t2 WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b; SELECT * FROM t1, t2 WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL); SELECT * FROM t1, t2 WHERE (t1.a=t2.a AND t2.a IS NULL AND ...) OR (t1.a=t2.a AND t2.a IS NULL AND ...); ref_or_null works by first doing a read on the reference key, and then a separate search for rows with a NULL key value. Note that the optimization can handle only one IS NULL level. In the following query, MySQL uses key lookups only on the expression (t1.a=t2.a AND t2.a IS NULL) and is not able to use the key part on b: SELECT * FROM t1, t2 WHERE (t1.a=t2.a AND t2.a IS NULL) OR (t1.b=t2.b AND t2.b IS NULL);

[ACMAIN_CHM]

引用 楼主 natici 的回复:

看过一些博文，提及如果字段设为NULL，会影响该字段索引的使用；甚至有人说为NULL的字段，是用不上索引的。 不知道这类说法是否正确？

字段设为NULL，会影响该字段索引的使用 这个无直接影响，只是看字段中会有多少值为NULL。 甚至有人说为NULL的字段，是用不上索引的。 是否使用索引，要看具体的数据分布。即使不是NULL，比如字段内容仅为 男|女， 同样无法使用索引。

[3文丰]

顶楼上

[php_aga]

按照我实际使用，是否有NULL值对结果查询是有影响的。

[natici]

引用 4 楼 php_aga 的回复:

按照我实际使用，是否有NULL值对结果查询是有影响的。

能否举个具体例子呢？

[php_aga]

你弄两个表，关联字段设置索引，都有NULL值，left join on后，再对比如果没有设置为索引时的结果看看。

[zhu19774279]

引用 楼主 natici 的回复:

看过一些博文，提及如果字段设为NULL，会影响该字段索引的使用；甚至有人说为NULL的字段，是用不上索引的。 不知道这类说法是否正确？

根据《高性能MySQL》一书中的介绍，NULL确实会对索引有影响，在MyISAM中甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。 不过，这本书也还说了，把NULL变为NOT NULL带来的性能提升不大，所以调优时没有必要把这里视为“主要矛盾”。当然能避免尽量避免。