Mysql 默认为Null的列会导致索引失效吗？

最近听到一个关于mysql 默认列为null，并且在行记录里存在null值引发的一个问题.

大致描述一下:

tableName:    test

id     col1

1         a

2        null

3        b

查询 co1列不为a的记录。写的sql语句是:

select  *  from test where col1  <> 'a' 

这句sql本身是没有问题的，要看具体的使用场景是否需要包含记录为null的行.

恰巧这个场景是需要包含null列的，这句sql就有问题了，因为它查询出来的结果集里不包含 id=2的行.

看到这里，机智的你一定知道应该这样写:

select * from test where col1 <> 'a' or col1 is null

那为什么col1<>'a' 查不出来null所在的行呢.

查询mysql官方文档关于NULL的描述

NULL意味着“没有值”或者“未知值”,且它被看作与众不同的值，为了测试NULL,你不能使用算术比较操作符例如 = <,!=.

直接运行

在col1<>'a' 条件查询符合 不等于'a' 的col1列时，Null与 <> 'a'的结果是NULL,而NULL和0在mysql官方文档里描述意味着假,其他值意味着真.

所以null列不符合条件，故不出现在结果集中.

在查询资料的过程中，发现有说存在Null的列，该列会使索引失效,但是官方文档上没有这么说，所以试验了一下.语句如下

CREATE TABLE `test_index_null` (
  `id` int(11) NOT NULL,
  `test1` varchar(20) DEFAULT NULL,
  `test2` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test1` (`test1`),
  KEY `test2` (`test2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


//创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `gly`.`insertData`()
begin
  declare i int;
  set i=1;
  while(i<=5000)do
    insert into test_index_null values(i, 'test1', null);
     insert into test_index_null values(5000+i, 'test1', 'test2');
    set i=i+1;
  end while;
end


//执行insertData
call insertData();

结论:default null的列，存在null的列并不会使索引失效

接下来执行如下sql

update test_index_null set test1=null where id<1000

将test1列id<1000的记录都置为null 查询条件改为test2 is null or test1 is null

发现还是走索引的.

当执行如下sql后:

update test_index_null set test1=null where id<5000

扩大test1为null的行数为5000，查询条件为test2 is null or test1 is null

发现索引失效.

但是将 or 改为and 或者查询条件改为只有test2 is null 或者只有test1 is null.

还是会走索引的。

默认为Null的列，存在Null值会导致mysql优化器处理起来比较复杂，但是到底走不走索引，或者走那个索引，是要靠mysql优化器预先预估走那个索引成本比较低来决定的.