mysql 索引失效、联合索引失效场景和举例

索引失效

假设有一张user 表，表中包含索引
(id);
(name);
(birthday);
(name,age);

1. 对索引字段进行函数操作

select name from user where year(birthday) = '2000';

2. 使用模糊查询，查询中使用通配符

select name from user where name like '%益达%';

3. 使用is null 或者 is not null

select name from user where name is null;

4. 使用查询条件时，字段类型和索引类型不一致，假设原表中的索引为 integer.

select name from user where CAST(id AS CHAR) = '1';

联合索引失效

1. 使用的查询条件没有命中联合索引最左边的索引（name,age）

select name from user where age > 10;

2. 查询中使用了函数

select name from user where UPPER(name)='yida' and age>10;

4. 查询中使用的数据类型和联合索引的字段类型不一致

select name from user where name = 'yida' and age > '10';

5. 使用联合索引时，查询条件的顺序和索引的顺序不一致

select name from user where age >12 and name = 'yida';

6. 复合索引列的离散值过大，mysql 会优先走全表扫描；

解决措施

1. 确保查询条件中至少包含联合索引的第一列。

2. 确保查询条件中的索引列类型和联合索引列类型一致。

3. 将索引列类型进行显式转换，以避免隐式转换导致的索引失效问题。

4. 确保查询条件中的索引列顺序和联合索引列顺序一致。

5. 避免在查询条件中使用函数，或者尽可能减少函数的使用。

6. 重新设计索引，或者将查询拆分为多个查询，以避免数据分布不均匀导致的索引失效问题。