MySQL 使用not in条件就一定不走索引吗？答案是不一定！

数据准备

• 建表语句

create table if not exists test_temp
(
    test_id     int auto_increment primary key,
    field_1     varchar(20),
    field_2     varchar(20),
    field_3     bigint,
    status      bigint,
    create_date date
);

create index index_field_123 on test_temp(field_1,field_2,field_3);
create index index_field_1 on test_temp(field_1);
create index index_field_2 on test_temp(field_2);
create index index_field_3 on test_temp(field_3);
create index index_status on test_temp(status);

• 存储过程

drop procedure if exists test_insert;
create procedure test_insert(n int)
begin
    declare v int default 0;
    SET AUTOCOMMIT = 0;
    while v < n
        do
            insert into test_temp(field_1, field_2, field_3,status, create_date)
            values (concat('testing',v),
                    substring(md5(rand()), 1, 10),
                    floor(rand() * 1000000),
                    'good',
                    adddate('1970-01-01', rand(v) * 10000));
            set v = v + 1;
        end while;
    SET AUTOCOMMIT = 1;
end;

• 插入数据

call test_insert(1000000);

• 数据展示
  

总结：利用存储过程向test_temp表中插入100万条数据，并创建了一些索引用于测试。

结果检验

• test1 字段field_1

explain select * from test_temp where field_1 not in ('testing1','testing2','testing100','testing101');

从执行计划上看，type是ALL，key为null，似乎确实没有走索引。

• test2 字段test_id（主键）

explain select * from test_temp where test_id not in (1,2,3,4,5,6,7,8);

咦，发现where条件使用主键后not in走了索引，难道not in走索引与否和主键相关？

• test3 字段status

explain select * from test_temp where status not in ('good');

使用status字段发现走了索引，而且只扫描rows是2，初步证明扫描效率很高。这么看来not in索引与否和主键并没有必然联系，那么为什么status走了索引而field_1却没有走索引呢？初步猜测字段数据特性有关系，因为status字段都是’good’，但是field_1字段几乎是rand随机生成几乎没有重复。

explain select * from test_temp where status not in ('bad');

继续使用status字段，但更换了where条件，发现type是ALL走了全表扫描，因为查询条件是not in (‘bad’)，而目前test_temp表中的status字段均为’good’，在查询优化器看来似乎只能进行全表扫描。

初步总结：
实践方出真知，使用not in并非绝对不走索引，MySQL查询优化器会根据当前表的情况选择最优解。