一.首先看一个我在某公众号看到的一个关于数据库优化的举措

二.如果where子句中查询的列执行了 “is null” 或者 “is not null” 或者 “<=> null” 会不会使用索引呢？

先列出结论：where子句中使用上述对null的判断，如果判断的列设置了索引，那就可以使用到索引

官方依据在：https://dev.mysql.com/doc/refman/5.6/en/is-null-optimization.html

三.测试：

1.建表

1 CREATE TABLE `test_null_index` (2 `id` int(11) DEFAULT NULL,3 `mark` varchar(20) DEFAULT NULL,4 `name` varchar(11) DEFAULT NULL5 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插数据

create procedure test_null_index(in num int)BEGINDECLARE i int; set i=1; while (i<num) DO if mod(i,10)!=0 then insert into test_null_index values (i,concat(‘aaa‘,i),null); else insert into test_null_index values (null,concat(‘aaa‘,i),‘bbb‘); end if;set i=i+1; END while; END;call test_null_index(10000);

3.没加索引时，type为All，全表扫描

mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+| 1 | SIMPLE | test_null_index | ALL | NULL | NULL | NULL | NULL | 10589 | Using where |+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+1 row in set (0.00 sec)

4.加上索引，可以看到，索引起作用了

mysql> create index idx_test_null on test_null_index(id);Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+| 1 | SIMPLE | test_null_index | ref | idx_test_null | idx_test_null | 5 | const | 998 | Using where |+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+1 row in set (0.00 sec)

四.注意，只能使用针对一个字段的关于null的判断，如果同时在两个字段对null进行判断，还是会走全表扫描

1.测试，可以看到，在name字段加上索引，并查询name为空的语句，同样会走索引

mysql> create index idx_test_null2 on test_null_index(name);Query OK, 0 rows affected (0.13 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE name is null;+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+| 1 | SIMPLE | test_null_index | ref | idx_test_null2 | idx_test_null2 | 36 | const | 8994 | Using where |+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+1 row in set (0.00 sec)

2.同时针对id和name查询为空的语句，走的是全表扫描

mysql> explain SELECT * from test_null_index WHERE id is null or name is null;+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+| 1 | SIMPLE | test_null_index | ALL | idx_test_null,idx_test_null2 | NULL | NULL | NULL | 10589 | Using where |+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+1 row in set (0.01 sec)