一、索引失效介绍
索引失效：索引已经创建，建索引相当于给字段进行排序，如按顺序建立了三个索引。而索引失效就是你建立的索引的顺序用不上了，即索引建了但是没用上。
不让索引失效的sql就是好sql. 避免索引失效的方法：
1、全值匹配（where后面的查询条件与索引完全一致）我最爱。最佳左前缀法则。
2、不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描。
3、存储引擎不能使用索引中范围条件右边得列。
4、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
5、like以通配符开头（‘%abc...’）mysql索引失效会变成全表扫描的操作。
6、字符串不加单引号索引失效。（出现自动类型转换时才会失效）
7、mysql在使用is null、is not null 、不等于（!=或者<>）的时候，如果mysql计算认为使用索引的时间成本高于全表扫描，则无法使用索引会导致全表扫描。
8、少用or，用它来连接时会索引失效。（但是并不是所有带or的查询都会失效）
二、数据准备
下面通过案例来演示
创建表
create table staffs ( id int primary key auto_increment, name varchar(24) not null default '' comment '姓名', age int not null default 0 comment '年龄', pos varchar(20) not null default '' comment '职位', add_time timestamp not null default current_timestamp comment '入职时间' )charset utf8 comment '员工记录表';
插入记录：
insert into staffs(name,age,pos,add_time) values ('z3',22,'manager',now()); insert into staffs(name,age,pos,add_time) values ('July',23,'dev',now()); insert into staffs(name,age,pos,add_time) values ('2000',23,'dev',now());
在cmd中进行操作：
mysql -uroot -p
输入密码后查询数据库
show databases;
进入test数据库
use test;
查询表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| staffs |
+----------------+
查询staffs表的数据
mysql> select * from staffs; +----+------+-----+---------+---------------------+ | id | name | age | pos | add_time | +----+------+-----+---------+---------------------+ | 1 | z3 | 22 | manager | 2023-06-28 09:47:32 | | 2 | July | 23 | dev | 2023-06-28 09:47:32 | | 3 | 2000 | 23 | dev | 2023-06-28 09:47:32 | +----+------+-----+---------+---------------------+ 3 rows in set (0.00 sec)
三、索引失效详解
1、 全值匹配我最爱。
全值匹配即个数和顺序一致。
我们先建了一个复合索引（name,age,pos）,
create index idx_staffs_nameAgePos on staffs(name,age,pos);
查询staffs表的索引
mysql> show index from staffs; +--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | staffs | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL | | staffs | 1 | idx_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | | staffs | 1 | idx_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | | staffs | 1 | idx_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | +--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 4 rows in set (0.01 sec)
最好最完美的sql就是你的索引怎么建的，我就怎么用。
1、如果建了3个索引，而where后条件只用了第一个索引。索引不会失效，只是使用部分索引。
mysql> explain select * from staffs where name='z3'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
2、如果建了3个索引，而where后条件只用了前两个索引。索引不会失效，只是使用部分索引。
mysql> explain select * from staffs where name='z3' and age=22; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
注意：key_len由上面的74变成了78。在相同结果下，key_len越小越好。但是如果精度提高，付出的代价要大一些，即key_len更大。精度要求越来越高，花费的代价要多一些。
3、如果建了3个索引，where后条件用了全部三个个索引。索引不会失效，这即全值匹配。
mysql> explain select * from staffs where name='z3' and age=22 and pos='manager'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
注意：key_len变得更大了。
4、如果建了3个索引，而where后条件只用了后两个索引。虽然能查询出来，索引会失效。
先查询数据
mysql> select * from staffs where age=23 and pos='dev'; +----+------+-----+-----+---------------------+ | id | name | age | pos | add_time | +----+------+-----+-----+---------------------+ | 2 | July | 23 | dev | 2023-06-28 09:47:32 | | 3 | 2000 | 23 | dev | 2023-06-28 09:47:32 | +----+------+-----+-----+---------------------+ 2 rows in set (0.00 sec)
再explain
mysql> explain select * from staffs where age=23 and pos='dev'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
此时key为null，表示没有用到索引。
发现全表扫描，实际建了索引，但是没有用到。另外ref为null，原来为const。
5、如果建了3个索引，而where后条件只用了第一个和第三个索引。虽然能查询出来且索引不会失效，但不是全值匹配，而是使用部分索引，索引只用到了第一个即name。
mysql> select * from staffs where name='July' and pos='dev'; +----+------+-----+-----+---------------------+ | id | name | age | pos | add_time | +----+------+-----+-----+---------------------+ | 2 | July | 23 | dev | 2023-06-28 09:47:32 | +----+------+-----+-----+---------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name='July' and pos='dev'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using index condition | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
Using index condition：where condition contains indexed and non-indexed column and the optimizer will first resolve the indexed column and will look for the rows in the table for the other condition (index push down)
大概意思是：where条件中包含索引列和非索引列，优化器先解析索引列，然后根据其他条件在表中查询记录（索引下拉）。
Using where; Using index : 'Using index' meaning not doing the scan of entire table. 'Using where' may still do the table scan on non-indexed column but it will use if there is any indexed column in the where condition first more like using index condition
大概意思是：Using index表示不会进行全表扫描。Using where:如果where条件中有索引列，就会优先接卸索引列，然后根据非索引列在表中查询记录，跟Using index condition很像。
6、如果建了3个索引，而where后条件只用了最后一个索引或者只用了一个中间的字段。虽然能查询出来，索引会失效。
where后条件只用了一个中间的字段
mysql> select * from staffs where age=22; +----+------+-----+---------+---------------------+ | id | name | age | pos | add_time | +----+------+-----+---------+---------------------+ | 1 | z3 | 22 | manager | 2023-06-28 09:47:32 | +----+------+-----+---------+---------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where age=22; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
where后条件只用了最后一个索引的情形：
mysql> select * from staffs where pos='dev'; +----+------+-----+-----+---------------------+ | id | name | age | pos | add_time | +----+------+-----+-----+---------------------+ | 2 | July | 23 | dev | 2023-06-28 09:47:32 | | 3 | 2000 | 23 | dev | 2023-06-28 09:47:32 | +----+------+-----+-----+---------------------+ 2 rows in set (0.00 sec) mysql> explain select * from staffs where pos='dev'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
使用图表归纳：
总结：我们建立了复合索引（name,age,pos）,但是如果查询条件中没有第一个name字段，只有单独的age字段或者单独的pos字段，或者只有后面两个字段，索引会失效。违背了高效sql最重要的原则。最佳左前缀法则。
最佳左前缀法则：指的是查询从索引的最左前列开始并且不跳过索引中的列。如果索引了多列，要遵守最左前缀法则。
（1）、即带头大哥不能死。从最左开始，你建的索引的第一个字段不能丢，
（2）、中间兄弟不能断。
要区分：索引失效、部分使用索引、全值匹配。
2、不在索引列上做任何操作（计算、函数、自动或手动类型转换）
计算、函数、自动或手动类型转换，会导致索引失效而转向全表扫描
Left是mysql自带的函数，有点类似于java的subString函数。Left（name,4）表示从左边开始选四位。此时在索引列name上包了一个函数。·
mysql> select * from staffs where left(name,4) = 'July'; +----+------+-----+-----+---------------------+ | id | name | age | pos | add_time | +----+------+-----+-----+---------------------+ | 2 | July | 23 | dev | 2023-06-28 09:47:32 | +----+------+-----+-----+---------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where left(name,4) = 'July'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
仍然能够查出来，但是索引会失效。
3、存储引擎不能使用索引中范围条件右边的列
全值匹配
没有使用范围条件时：
mysql> explain select * from staffs where name='z3' and age=21 and pos='manager'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
使用范围条件时
mysql> explain select * from staffs where name='z3' and age>21 and pos='manager'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
Type由ref变为了range，范围之后的索引全失效。但是范围本身的字段age有被用到。Age与name还有点区别：name用于了查询，但是age只给了一个范围，age也用到了（注意name和age索引都用到了），否则不会给range，但是age只用于去排序，而不是像name一样着重检索（查询）。
下图的key_len证明用到了age字段
mysql> explain select * from staffs where name='z3'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name='z3' and age=22; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name='z3' and age=22 and pos='manager'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name='z3' and age>11 and pos='manager'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
4、尽量使用覆盖索引，减少select *
只访问索引的查询（索引列和查询列一致）
我们写sql的原则：最好是按需取数据，用多少取多少，尽量跟索引重合。尽量少用*
mysql> explain select * from staffs where name='z3' and age=22 and pos='manager'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
使用了索引（全值匹配），但没有使用索引覆盖（Using index）。
使用*的话，共有四个字段，除了name，age,pos，还有入职时间add_time。
mysql> explain select name,age,pos from staffs where name='z3' and age=22 and pos='manager'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
索引覆盖了
用到了Using index，表示使用了索引覆盖，避免了访问表的数据行。从索引中取数据，性能更加优秀，更好一些。
Using index:表示相应的select操作使用了索引覆盖（covering index）,避免了访问表的数据行，效率不错。如果同事出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
如果你建的索引的字段刚好是你查的字段，而且个数和顺序刚好一致，这是最好的。
mysql> explain select name,age,pos from staffs where name='z3' and age>21 and pos='manager'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using where; Using index | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
部分使用索引，且索引覆盖。
Using where：表示使用了where过滤，去表中检索。
范围之后的索引会失效，Using index表示去索引上拿，key_len为74而不是78，没有用到range，
mysql> explain select name,age,pos from staffs where name='July' and age=22; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
部分使用索引，且索引覆盖。
上面的例子中，你建的索引是name,age,pos,而你查的刚好是name,age,pos,一一吻合，即索引覆盖。
而如果你只查询索引字段的一部分，此时也会用到Using index，即索引覆盖
mysql> explain select name from staffs where name='z3' and age=22; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
部分使用索引，且进行了索引覆盖。
5、like以通配符开头（‘%ABC’），mysql索引失效会变成全表扫描的操作。
使用“%July%”和“%July”虽然可以查询出来，但是会导致索引失效而全表扫描。使用 “July”则不会导致索引失效。所以说一般写like查询，百分号写在右边。即百分like加右边。
mysql> explain select * from staffs where name like '%July%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name like '%July'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name like 'July%'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | NULL | 1 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.08 sec)
百万级别的数据量的单表必须要避免全表扫描。
一般就是在你的where语句中出现了between、<、>、in、like等的查询，type才为range。即like查询是一个范围。
6、字符串不加单引号索引失效
如果varchar类型写错了，一定会被项目经理骂死，即varchar类型绝对不能忘记单引号。
mysql> select * from staffs where name=2000; +----+------+-----+-----+---------------------+ | id | name | age | pos | add_time | +----+------+-----+-----+---------------------+ | 3 | 2000 | 23 | dev | 2023-06-28 09:47:32 | +----+------+-----+-----+---------------------+ 1 row in set, 1 warning (0.08 sec) mysql> explain select * from staffs where name=2000; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set, 3 warnings (0.00 sec) mysql> explain select * from staffs where name='2000'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
Name是varchar类型，name=2000仍然能查出结果，Mysql功能很强大，整数的2000和String的2000一个是int类型，一个是varchar类型，当name是varchar类型，而你写成整数的时候，这是mysql将会在底层自动的做一次类型转换，实现了从数字到String，从而帮你查出来。前面讲过，不要在索引列上做任何操作（自动类型转换），否则会导致索引失效。
7、少用or，用它来连接时可能会索引失效
注意：（1）、不是使用or就会失效。（2）、是少用而不是不用。
mysql> select * from staffs where name='z3' or name = 'July'; +----+------+-----+---------+---------------------+ | id | name | age | pos | add_time | +----+------+-----+---------+---------------------+ | 2 | July | 23 | dev | 2023-06-28 09:47:32 | | 1 | z3 | 22 | manager | 2023-06-28 09:47:32 | +----+------+-----+---------+---------------------+ 2 rows in set (0.00 sec) mysql> explain select * from staffs where name='z3' or name = 'July'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | NULL | 2 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
对同一个字段使用or时，索引没有失效。
对不同的字段使用or时要分情况：（1）、如果表中不是所有字段都是索引列，则会失效。（2）、如果表的所有字段都是索引列，那么不会失效：
（1）、如果表中不是所有字段都是索引列，对不同的字段使用or时r则会失效。
mysql> explain select * from staffs where name='z3' or age = 22; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 55.56 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name='z3' or pos = 'dev'; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 55.56 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where age=22 or pos = 'dev'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 55.56 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name='z3' or age=22 or pos = 'dev'; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 70.37 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
注意：也有可能出现不同的字段使用or时也不会失效的情况
（2）、如果表的所有字段都是索引列，那么不会失效：
下面进行测试：
create table test1 ( id int primary key auto_increment, name varchar(24) not null default '' comment '姓名', age int not null default 0 comment '年龄' )charset utf8 ;
创建索引
create index idx_test_nameAge on test1(name,age);
explain
mysql> explain select * from test1 where name = '张三' or age=2; +----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test1 | NULL | index | idx_test_nameAge | idx_test_nameAge | 78 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
发现索引没有失效。并不是所有带or的查询都会失效，如果有两个字段，两个字段都有索引就不会失效，会走两个索引。
但是如果我给表再添加一个字段，就会出现不一样的情况
create table test1 ( id int primary key auto_increment, name varchar(24) not null default '' comment '姓名', age int not null default 0 comment '年龄', gender varchar(1) not null default '男' )charset utf8 ;
创建索引
create index idx_test_nameAge on test1(name,age);
explain
mysql> explain select * from test1 where name = '张三' or age=2; +----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test1 | NULL | ALL | idx_test_nameAge | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
上面出现索引没有失效，原因可能是所有字段都是索引列。
小总结：
like查询的是一个范围。
四、解决like “%字符串%”时索引不被使用的方法：用覆盖索引来解决。
如果生产环境中非要两边写百分号，否则数据不对。此时就需要用索引覆盖来解决。
创建表
create table t_user( id int not null auto_increment, name varchar(20) default null, age int default null, email varchar(20) default null, primary key(id) ) charset=utf8;
插入数据
insert into t_user(name,age,email) values ('1aa1',21,'b@163.com'); insert into t_user(name,age,email) values ('2aa2',222,'a@163.com'); insert into t_user(name,age,email) values ('3aa3',265,'c@163.com'); insert into t_user(name,age,email) values ('4aa4',21,'d@163.com');
建索引之前，使用like “%aa%”查询id，name,age中的单个字段,会导致索引失效。
mysql> explain select id from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select name from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select age from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
建索引之前，使用like “%aa%”查询id，name,age中的两个或三个字段,会导致索引失效。
mysql> explain select id,name from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select id,age from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select name,age from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
建索引之前，使用like “%aa%”查询id，name,age，email中的全部字段,会导致索引失效。
mysql> explain select id,name,age,email from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
结论：没建索引之前，都是全表扫描。
用覆盖索引来防止索引失效，从而避免全表扫描。
创建索引：假设经常要查询name和age这两个字段。
create index idx_user_nameAge on t_user(name,age);
explain
mysql> explain select name,age from t_user where name like '%aa%'; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
因为id是主键，它也可以从主键上去取。
mysql> explain select id from t_user where name like '%aa%'; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select age from t_user where name like '%aa%'; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select id,name from t_user where name like '%aa%'; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select id,name,age from t_user where name like '%aa%'; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
如果没有索引覆盖就会全表扫描
mysql> explain select * from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select id,name,age,email from t_user where name like '%aa%'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
覆盖索引就是：你建的索引和我查的字段个数和顺序完全一致。你建的索引是复合索引（name,age）
五、为何大众误解认为is null、is not null、!=这些判断条件会导致索引失效而全表扫描呢？
导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描，于是mysql宁可全表扫描也不愿意使用索引。使用索引的时间成本高于全表扫描的临界值可以简单得记忆为70%~80%左右，并没有一个固定的数据量占比来决定优化器是否使用全表扫描。
也就是如果一条查询语句导致的回表范围超过全部记录的80%，则会出现索引失效的问题。而is null、is not null、!=这些判断条件经常会出现在这些回表范围很大的场景，然后被人误解为是这些判断条件导致的索引失效。
mysql官方文档也已经明确说明使用is null时，mysql能使用索引查找null
MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.
复现索引失效
mysql> select * from staffs; +----+------+------+---------+---------------------+ | id | name | age | pos | add_time | +----+------+------+---------+---------------------+ | 1 | z3 | 22 | manager | 2023-06-28 09:47:32 | | 2 | July | 23 | dev | 2023-06-28 09:47:32 | | 3 | NULL | 23 | dev | 2023-06-28 09:47:32 | | 4 | NULL | 25 | dev | 2023-06-29 14:28:27 | | 5 | NULL | NULL | dev | 2023-06-29 14:28:52 | | 6 | NULL | 26 | dev | 2023-06-29 14:36:53 | | 7 | NULL | 21 | dev | 2023-06-29 14:43:55 | | 8 | NULL | 21 | dev | 2023-06-29 14:44:19 | | 9 | NULL | 31 | dev | 2023-06-29 15:13:41 | | 10 | NULL | 32 | dev | 2023-06-29 15:13:41 | | 12 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 13 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 14 | NULL | 33 | dev | 2023-06-29 15:15:50 | +----+------+------+---------+---------------------+ 13 rows in set (0.00 sec) mysql> explain select * from staffs where name is null; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 13 | 84.62 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name is not null; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 75 | NULL | 2 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
此时name为null的比例为84.6%。is null为全表扫描，is not null 为走索引。
mysql> select * from staffs; +----+------+------+---------+---------------------+ | id | name | age | pos | add_time | +----+------+------+---------+---------------------+ | 1 | z3 | 22 | manager | 2023-06-28 09:47:32 | | 2 | July | 23 | dev | 2023-06-28 09:47:32 | | 3 | zs | 23 | dev | 2023-06-28 09:47:32 | | 4 | NULL | 25 | dev | 2023-06-29 14:28:27 | | 5 | NULL | NULL | dev | 2023-06-29 14:28:52 | | 6 | NULL | 26 | dev | 2023-06-29 14:36:53 | | 7 | NULL | 21 | dev | 2023-06-29 14:43:55 | | 8 | NULL | 21 | dev | 2023-06-29 14:44:19 | | 9 | NULL | 31 | dev | 2023-06-29 15:13:41 | | 10 | NULL | 32 | dev | 2023-06-29 15:13:41 | | 12 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 13 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 14 | NULL | 33 | dev | 2023-06-29 15:15:50 | +----+------+------+---------+---------------------+ 13 rows in set (0.00 sec) mysql> explain select * from staffs where name is null; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 75 | const | 10 | 100.00 | Using index condition | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name is not null; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 75 | NULL | 3 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
此时name为null的比例为76.9%。is null为走索引，is not null 为走索引。
mysql> select * from staffs; +----+------+------+---------+---------------------+ | id | name | age | pos | add_time | +----+------+------+---------+---------------------+ | 1 | z3 | 22 | manager | 2023-06-28 09:47:32 | | 2 | July | 23 | dev | 2023-06-28 09:47:32 | | 3 | zs | 23 | dev | 2023-06-28 09:47:32 | | 4 | l4 | 25 | dev | 2023-06-29 14:28:27 | | 5 | ls | NULL | dev | 2023-06-29 14:28:52 | | 6 | w5 | 26 | dev | 2023-06-29 14:36:53 | | 7 | z6 | 21 | dev | 2023-06-29 14:43:55 | | 8 | zl | 21 | dev | 2023-06-29 14:44:19 | | 9 | t7 | 31 | dev | 2023-06-29 15:13:41 | | 10 | tq | 32 | dev | 2023-06-29 15:13:41 | | 12 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 13 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 14 | NULL | 33 | dev | 2023-06-29 15:15:50 | +----+------+------+---------+---------------------+ 13 rows in set (0.00 sec) mysql> explain select * from staffs where name is null; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 75 | const | 3 | 100.00 | Using index condition | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name is not null; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 13 | 76.92 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
此时name为null的比例为23%。is null为走索引，is not null 为全表扫。
现在我们来测试不等于的情况：
先查看索引情况：
mysql> show index from staffs; +--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | staffs | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL | | staffs | 1 | idx_staffs_agePos | 1 | age | A | 9 | NULL | NULL | YES | BTREE | | | YES | NULL | +--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.00 sec)
此时索引普通索引只有一个：age
mysql> select * from staffs; +----+------+------+---------+---------------------+ | id | name | age | pos | add_time | +----+------+------+---------+---------------------+ | 1 | z3 | 22 | manager | 2023-06-28 09:47:32 | | 2 | July | 22 | dev | 2023-06-28 09:47:32 | | 3 | zs | 23 | dev | 2023-06-28 09:47:32 | | 4 | l4 | 23 | dev | 2023-06-29 14:28:27 | | 5 | ls | 23 | dev | 2023-06-29 14:28:52 | | 6 | w5 | 23 | dev | 2023-06-29 14:36:53 | | 7 | z6 | 21 | dev | 2023-06-29 14:43:55 | | 8 | zl | 21 | dev | 2023-06-29 14:44:19 | | 9 | t7 | 31 | dev | 2023-06-29 15:13:41 | | 10 | tq | 32 | dev | 2023-06-29 15:13:41 | | 12 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 13 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 14 | NULL | 33 | dev | 2023-06-29 15:15:50 | +----+------+------+---------+---------------------+ 13 rows in set (0.00 sec) mysql> explain select * from staffs where age !=22; +----+-------------+--------+------------+------+-------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_agePos | NULL | NULL | NULL | 13 | 84.62 | Using where | +----+-------------+--------+------------+------+-------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
此时不等于22的比例为84.6%，索引失效。
mysql> select * from staffs; +----+------+------+---------+---------------------+ | id | name | age | pos | add_time | +----+------+------+---------+---------------------+ | 1 | z3 | 22 | manager | 2023-06-28 09:47:32 | | 2 | July | 22 | dev | 2023-06-28 09:47:32 | | 3 | zs | 22 | dev | 2023-06-28 09:47:32 | | 4 | l4 | 22 | dev | 2023-06-29 14:28:27 | | 5 | ls | 22 | dev | 2023-06-29 14:28:52 | | 6 | w5 | 23 | dev | 2023-06-29 14:36:53 | | 7 | z6 | 21 | dev | 2023-06-29 14:43:55 | | 8 | zl | 21 | dev | 2023-06-29 14:44:19 | | 9 | t7 | 31 | dev | 2023-06-29 15:13:41 | | 10 | tq | 32 | dev | 2023-06-29 15:13:41 | | 12 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 13 | NULL | 33 | dev | 2023-06-29 15:15:50 | | 14 | NULL | 33 | dev | 2023-06-29 15:15:50 | +----+------+------+---------+---------------------+ 13 rows in set (0.00 sec) mysql> explain select * from staffs where age !=22; +----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_agePos | idx_staffs_agePos | 5 | NULL | 8 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
此时不等于22的比例为61.5%，走索引。
