mysql进阶篇之索引失效因素

作者：YXN-sql 阅读量：88 发布日期：2022-10-02

1、复合索引最左特性

复合索引也称为联合索引，当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

举例：创建联合索引ABC，如果是以最左边开始匹配的字段都可以使用上联合索引，比如：

A+B+C A+B A+C

而 B+C 却不能使用到联合索引，这就是最左匹配原则。

联合索引不满足最左原则，索引一般会失效。

2、模糊查询右模糊

例如下面，只有1使用索引，2可以利用`reverse + function index`的形式变为1

1. 模糊匹配后面任意字符：like '张%'

2. 模糊匹配前面任意字符：like '%张'

3. 模糊匹配前后任意字符：like '%张%'

3、在索引列进行 列运算、使用内置函数、类型转换

列运算：select * from study where id+1=2;

使用内置函数：select * from study where ifnull(id,0)=1;

类型转换：比如 address 为字符串类型，而查询的时候设置了 int 类型的值（隐式或显式类型转换）

select * from study where address =1;

4、使用 !=，<>，is null，is not null

1. 并不是说使用了 is null 或者 is not null 就会不走索引了，跟 mysql 版本以及查询成本都有关；

2. 如果 mysql 优化器发现，走索引比不走索引成本还要高，就会放弃索引

3. 当字段可以为空使用 is null 会使用索引，不为空不使用，is not null不管可不可为空都不使用，可以把 null 值，换成默认值，比如：select * from study where age>0

5、OR 前后只要存在非索引的列，都会导致索引失效

6、数据库和表的字符集统一使用utf8mb4

不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

7、查询条件数据量过大

如果WHERE子句过滤出的数据行数超过表总数据的15%，MySQL可能会选择全表扫描而不是使用索引。

 

 

YXN-sql

2022-10-02