mysql中innodb引擎索引生效与索引失效案例

表结构和数据

表结构

索引结构

表数据

复合索引情况下

全值匹配

全值匹配：查询的所有字段都有对应的索引，id是主键，name和pid是联合索引，查看执行计划 索引使用的type是index，extra是using index 即覆盖索引。

在查询的字段顺序与索引顺序不一致时，innodb会自动优化字段顺序，也可以匹配到索引

最左匹配

当索引使用在where条件上时
如果索引是联合索引，需要对索引按顺序进行过滤
当前索引的顺序是 name, pid, type

索引生效的情况：
where条件中 包含索引的最左(第一个)字段

where条件中所有字段能够组合成索引从左到右的字段，
比如查询条件中 pid,type,name 经过innodb自动优化后，
可以得到name pid type的顺序 能够匹配到索引

索引失效情况：
where条件中 没有索引中第一个字段：

key_len 是空 没有使用到索引

索引局部生效：

当where条件中字段顺序 不能构成连续的索引字段顺序，只会使索引中第一个字段到不连续的字段之前的字段生效

查询条件中 只有 name 和 name+type的情况下，索引使用的长度是一样的 说明只有name字段使用到了索引

索引的顺序是 name, pid, type，因为查询条件中没有pid所以pid之后的所有字段并没有使用到索引。

自动或手动的函数包装和类型转换

name 字段定义为 varchar类型 查询条件是数字类型，进行了自动转换导致索引不生效

通过手动包装被查询的值为字段类型后，索引生效

模糊查询

当使用 like右模糊查询时，索引是全部生效的，key_len的使用长度是最长的

当使用 < > != 时 模糊查询列的所有右边列索引不生效

or 条件链接

使用or条件时，索引失效

可以尝试用 union 将两个查询条件的sql单独链接起来，或使用in代替or

is null 和 is not null

is null 可以使用到索引

is not null 可以使用到索引，但使用is not null的字段右边所有索引都无法使用

其他索引失效情况

在通过 <> != is null is not null 这些范围模糊查询时，如果通过索引扫描的数据量 接近 全表扫描的数据量时，mysql 会自动转为全表扫描，自动分析出检索效率更高的查询方式。