可为NULL字段在何时索引失效?
文章目录
前言
在一篇博客上看到一哥们说 : “官方文档显示,字段为null时,如果该字段有索引,查询该字段的时候是走索引的,两个字段组成的复合索引,都为null时,才不走索引”。不是很懂,因此我自己动手实践了下,来查看是否正确,并在博客上记录下。但优化器真正怎么做的可能自己也理解的不到位,若有前辈指正,会在博客上更新哒。
一、数据表创建
创建一个有两个字段可为NULL的表。
CREATE TABLE test_null (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT '' ,
`name_null` varchar(32) DEFAULT NULL,
`age_null` int(11) DEFAULT NULL,
`name_empty` varchar(32) DEFAULT '',
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
表数据:
二、单列索引和联合索引
创建idx_numa_null的单列索引和idx_name_age_null的联合索引。
CREATE INDEX idx_name_null ON test_null(name_null);
CREATE INDEX idx_name_age_null ON test_null(name_null,age_null);
1.全值匹配使用索引
对于单个NULL值在查询过程中会使用到索引。
EXPLAIN SELECT * FROM test_null WHERE name_null = 'c';
使用了索引
2.对于匹配两个可为NULL的列值
2.1 单列索引和联合索引都存在
对于双值在查询过程中会使用到联合索引。
EXPLAIN SELECT * FROM test_null WHERE name_null = 'c' AND age_null = 5;
使用了联合索引
2.2.当联合索引被删除后
当联合索引被删除后,使用单列索引
DROP INDEX idx_name_age_null ON test_null;
EXPLAIN SELECT * FROM test_null WHERE name_null = 'c' AND age_null = 5;
使用了联合索引
2.3 对于OR
当然对于or来说,OR前后存在非索引的列,索引失效。
总结
从实践上来看,就算是两个都可为NULL的列组成的联合索引也是会被使用的。