MySQL ‘IN‘ ‘IS NULL‘ ‘IS NOT NULL‘ 到底走不走索引_mysql5.7 int is null

看了很多篇文章，关于走不走索引这个问题真是众说纷纭，不如自己实践一波来得准。

先说我的mysql版本为5.7；

再说结论：有时候走，有时候不走。这个基于MySQL自身的查询优化。

直接上例子！

准备工作：

首先创建一个students表，内含自增主键id，在name和school_id两列上建立多列索引，最后还有一个普通列age；

CREATE TABLE `students` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(16),
  `school_id` INT,
  `age` INT,
  PRIMARY KEY (`id`),
  INDEX `idx_name_school_id` (`name`, `school_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

 然后我们创建一个存储过程批量添加10000条数据。

drop procedure if exists insert_items;
 
delimiter $$
 
create procedure insert_items()
begin
    declare n int default 1;
    declare MAX int default 10000;
    while n <= MAX do
        INSERT INTO students (`name`, `school_id`, `age`) VALUES (CONCAT('name - ', n), n, n);
        set n = n + 1;
    end while;
end
$$
 
delimiter ;
 
call insert_items();

 测试一波！

1. IN语句

• 用“in”查询所有字段没有使用索引

SELECT * FROM students WHERE school_id IN (10, 100, 1000);

执行计划：

• 用“in”查询部分字段，由于所有查询字段都在索引中，无需回表，因此使用了索引

SELECT id, name, school_id FROM students WHERE school_id IN (10, 100, 1000);

执行计划：

2. '>' '>=' '<' '<=' 'between and' 语句 

• 例1，使用school_id，返回主键值和索引包含的值，走索引（无需回表）

SELECT id, name, school_id FROM students WHERE school_id < 1000;

 执行计划：

例2，使用school_id，返回age值（age不在索引中），不走索引  （需要回表）

SELECT age FROM students WHERE school_id < 1000;

执行计划：

3. '<>' '!=' 语句 

例1，由于返回值在索引中，无需回表，走索引

SELECT name FROM students WHERE school_id <> 1000;

例2，由于返回值不全在索引中，需要回表，不走索引

SELECT * FROM students WHERE school_id <> 1000;

4. IS NULL 和 IS NOT NULL语句

首先需要加点儿null数据

drop procedure if exists insert_null_items;
 
delimiter $$
 
create procedure insert_null_items()
begin
    declare n int default 10001;
    declare MAX int default 20000;
    while n <= MAX do
        INSERT INTO students (`name`, `school_id`, `age`) VALUES (CONCAT('name - ', n), null, n);
        set n = n + 1;
    end while;
end
$$
 
delimiter ;
 
call insert_null_items();

现在表中有10000条数据有school_id并且10000条数据没有school_id了。

​SELECT name FROM students WHERE school_id IS NOT NULL;
SELECT name FROM students WHERE school_id IS NULL;

但以上两条数据仍然走索引，因为它不用回表。

再修改一下数据：

UPDATE students SET name = null WHERE name = 'name - 1';

目前只有1条数据的name的值为null。再试一下回表的sql：

SELECT * FROM students WHERE name IS NULL;

这个SQL虽然回表了，但是仍然走了type=ref的索引。

综上，只要不回表或者需要回表的次数非常少，还是可以走索引的。