背景
关于mysql索引失效的几种情况，网上有很多文章，但是大多我感觉都是抄来抄去的，也有少数附上了自己的实验结果，但是并没有剖析背后的原理，所以今天自己亲自做套实验，分析一下。
准备数据
-
CREATE
TABLE
`test` (
-
`id`
int(
11)
unsigned
NOT
NULL AUTO_INCREMENT,
-
`name`
varchar(
50)
NOT
NULL
DEFAULT
'',
-
`age`
int(
11)
NOT
NULL
DEFAULT
0,
-
`nick`
varchar(
200)
DEFAULT
NULL,
-
`status`
int(
11)
NOT
NULL
DEFAULT
0
COMMENT
'状态',
-
PRIMARY
KEY (
`id`),
-
KEY
`name` (
`name`)
USING BTREE,
-
KEY
`age` (
`age`)
USING BTREE,
-
KEY
`nick` (
`nick`)
USING BTREE
-
)
ENGINE=
InnoDB
DEFAULT
CHARSET=utf8
其中nick字段有索引，但是nick字段允许为NULL（用于验证判断null是否走索引的情况）
-
for ($i =
1; $i <=
30000; $i++){
-
$tmp = [
-
'name' =>
'abc' . rand(
0,
10000),
-
'age' => rand(
0,
10000),
-
'status' => $i %
2,
-
];
-
if ($i %
5 !=
0){
-
$tmp[
'nick'] =
'xyz' . rand(
0,
10000);
-
}
-
$table->insert($tmp);
-
}
首先必须明确知道一个前提，mysql优化器在使用索引返回的结果大于80%时，不管什么时候，都不会使用索引，除非用force强制使用！！！！所以下面默认测试的情况都是保证返回结果大于80%的情况。
当字段允许为NULL时，is null和is not null
is null的情况
explain select * from test where nick is null;
is not null，查询所有字段时，不走索引
explain select * from test where nick is not null;
使用覆盖索引时，会走索引
-
select
count(*)
from
test
where nick
is
not
null；
2003条
-
-
select
count(*)
from
test
where nick
is
null
27999条
explain select age from test where nick is not null
所以结论：在满足条件行数小于80%的前提下，is null可以走索引，而is not null只有满足覆盖索引的条件才可以走索引。
当使用!=条件时（这里需要注意了啊，网上很多文章一口咬定!=不会走索引）
-
update
test
set
name =
'abc'
where
id >
58000;
-
-
update
test
set
name =
''
where
id <=
58000;
-
-
select
count(*)
from
test
where
name !=
''; 2002条记录
-
-
select
count(*)
from
test
where
name =
''; 28000条记录
explain select * from test where name != '';
我们把 !='' 的行数改为大于表总记录的百分之80，再测试一次
-
update
test
set
name =
''
where
id >
58000;
-
-
update
test
set
name =
'abc'
where
id <=
58000;
-
-
select
count(*)
from
test
where
name !=
''; 28000条
-
-
select
count(*)
from
test
where
name =
''; 2002条
explain select * from test where name != '';
结论：!= 走不走索引不是绝对的，要看对应条件的数量是否大于整表的80%，如果大于80%，那么mysql优化器认为走索引开销更大，因为选择性不高，最终还要回表查询大部分数据，所以还不如不走索引效率高，最终不走索引，以后面试再问到千万不要在说!=会导致索引失效了！！！
使用or的情况
当我们status字段没有索引的时候，发现是全表扫描
explain select * from test where age = 2644 or status = 1;
我们为status字段加上索引之后，发现用到了age和status两个索引
explain select * from test where age = 2644 or status = 1;
结论：or是否走索引取决于or前后的两个字段是否都建立了索引！
not in情况
explain select * from test where id not in (1)
explain select age from test where age not in (2644)
使用主键索引和覆盖索引时也可以走索引，是因为not in无需二次回表
结论：当not in无需二次回表时，可以走索引，否则不走索引
having情况
explain select age from test where age HAVING (2644)
explain select name from test where age HAVING (2644)
当使用普通索引时，只有返回索引字段时（覆盖索引）才会走索引，否则不会走索引。
explain select status from test where id HAVING (12345)
explain select age from test where id HAVING (12345)
当使用主键索引时，只有返回建立索引的字段时才会走索引，否则不会走索引
结论：使用having时，当使用普通索引时，只有返回索引字段时（覆盖索引）才会走索引，否则不会走索引；当使用主键索引时，只有返回建立索引的字段时才会走索引，否则不会走索引
其他通用情况
- 隐式转换：传入的条件和字段类型不一致，索引必失效，无法命中索引
- 联合索引使用时没有遵循最左匹配原则索引会断开（使用一部分）或失效
- 当走索引返回的行数大于全表的80%时，优化器是选择不走索引
- 当用like左通配符时，索引失效，原因是违反最左原则
结论
纸上得来终觉浅，绝知此事要躬行，如果看完了对您有帮助，动动小手点个赞，关注一下，会持续更新技术文章！
转载：https://blog.csdn.net/why444216978/article/details/104986241