面试:xxx是索引字段,那么mysql 中where xxx is null 会使用到索引吗?
答案:如果索引字段设置的是可以为空的情况下,是可以使用到索引的。
验证:
CREATE TABLE `department` (
`id` int(10) NOT NULL COMMENT 'id' ,
`depname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci ***NOT NULL*** COMMENT '部门名称' ,
`depparentid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci ***NULL*** DEFAULT NULL COMMENT '父ID' ,
`depdesc` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述' ,
PRIMARY KEY (`id`),
UNIQUE INDEX `indx_deptName` (`depname`) USING BTREE ,
UNIQUE INDEX `index_deptDesc` (`depdesc`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;
department 表中 depname 字段设置为不为空;depdesc 设置为可以为空。
SELECT VERSION();-- 5.5.27
-- depdesc 字段设置为可以为 NULL
-- type ref 索引没有失效
EXPLAIN SELECT * FROM department t WHERE t.depdesc is NULL;
-- type ALL 索引失效
EXPLAIN SELECT * FROM department t WHERE t.depdesc is NOT NULL;
-- depname 字段设置为不能为null
-- type 为null 根本没有使用到索引
EXPLAIN SELECT * FROM department t where t.depname is NULL;
-- type ALL 索引失效
EXPLAIN SELECT * FROM department t where t.depname is NOT NULL;
一句话总结:只有字段设置为 可以为空的时候,使用 is null 才可以使用到索引,其他情况使用不到索引。