MySQL索引失效实践-ZHD的小窝

创建测试表：

create table t_test_index_not_work
(
    id            bigint unsigned auto_increment comment '主键'
        primary key,
    varchar_field varchar(255) not null default '' comment '字符串',
    int_field     int          not null default 0 comment 'int',
    enum_field    varchar(64)  not null default '' comment '枚举值',
    time_field    datetime     not null default CURRENT_TIMESTAMP comment '时间字段'
) ENGINE INNODB
  DEFAULT charset utf8mb4 comment '测试索引失效问题表';

1. 查询条件使用了函数或计算

在时间字段上添加索引，然后使用函数查询

ALTER TABLE t_test_index_not_work add index idx_time_field (time_field);

explain
select * from t_test_index_not_work where YEAR(time_field) = 2023;

解决方法：重写查询条件，避免在索引列上使用函数或计算。例如，SELECT * FROM t_test_index_not_work WHERE time_field >= '2023-01-01' AND time_field < '2024-01-01';

2. 隐式类型转换

在字符类型字段加上索引，然后where条件传入int参数

ALTER TABLE t_test_index_not_work add index idx_varchar_field (varchar_field);

explain
select * from t_test_index_not_work where varchar_field = 123;

*but，如果在int类型字段上加索引，where条件传入char类型参数可以走索引

ALTER TABLE t_test_index_not_work add index idx_int_field (int_field);

explain
select * from t_test_index_not_work where int_field = '123';

解决方法：确保查询条件中的数据类型与索引列的数据类型一致。

3. LIKE语句以通配符开头

使用LIKE语句时，如果模式以通配符%开头，MySQL将无法使用索引。

explain
select * from t_test_index_not_work where varchar_field like  '%bsd'; --索引失效

explain
select * from t_test_index_not_work where varchar_field like  '%bsd%'; --索引失效

explain
select * from t_test_index_not_work where varchar_field like  'bsd%'; --索引有效

解决方法：尽可能将通配符放在模式的末尾，或者避免使用LIKE语句，改用其他查询方式。

4. OR条件中的非索引列

当使用OR连接查询条件时，如果其中一个条件涉及的列没有索引，MySQL可能放弃使用索引。

explain
select * from t_test_index_not_work where int_field = 123 or enum_field = 1; -- enum_field 此时没有加索引

解决方法：确保OR条件中的所有列都被索引，或者重写查询逻辑，避免使用OR。

5. 索引列被包含在NULL值判断

当索引列包含大量的NULL值时，对于IS NULL或IS NOT NULL的查询，MySQL可能无法高效地使用索引。

explain
select * from t_test_index_not_work where varchar_field is null;

explain
select * from t_test_index_not_work where varchar_field is not null;

6. IN使用不当

在MySQL中，IN 操作符通常不会导致索引失效，前提是索引字段和IN列表中的值类型匹配，并且IN列表中的值数量不是特别大。MySQL优化器通常能够很好地处理IN操作符，并利用索引来加速查询。

然而，有几种情况下IN操作符可能会导致索引失效或查询性能下降：

IN列表中的值数量非常大：如果IN列表中的值数量非常大（例如，几千个或更多），MySQL可能会选择全表扫描而不是使用索引，因为扫描整个表可能比逐个检查每个索引条目更快。
类型不匹配：如果IN列表中的值类型与索引字段类型不匹配，MySQL可能无法使用索引。例如，如果索引字段是整数类型，而IN列表中包含字符串类型的值，MySQL可能无法使用该索引。
查询优化器的选择：MySQL查询优化器会根据统计信息和查询成本估算来决定是否使用索引。在某些情况下，优化器可能会认为全表扫描比使用索引更快，因此会选择不使用索引。

explain
select * from t_test_index_not_work where int_field  in (123);

explain
select * from t_test_index_not_work where int_field  in (1,2,3,4,5,6,7,8,123,2312,31,3213,2,434,3,23,123,54,54,65,765,4324,2342,435,45,565,876,9870,78968,76,78657,567,657,5675,756,7,567,657,6556,765,654,654,54,3,4532,423,21,31,22,543,6,457,6,9,8709,87,8,65,7,45,63,45,2,37357);

7. 联合索引最左前缀

删除表的其他索引，我们来创建一个联合索引


drop index idx_time_field on t_test_index_not_work;
drop index idx_varchar_field on t_test_index_not_work;
drop index idx_int_field on t_test_index_not_work;

ALTER TABLE t_test_index_not_work
    add index idx_join_int_varchar_enum (int_field, varchar_field, enum_field);

explain
select * from t_test_index_not_work where int_field  = 123 and varchar_field = 'abc' and enum_field = 1; -- 走索引

explain
select * from t_test_index_not_work where int_field  = 123 and varchar_field = 'abc'; -- 走索引

explain
select * from t_test_index_not_work where int_field  = 123 and enum_field = 1; -- 走索引

explain
select * from t_test_index_not_work where  varchar_field = 'abc' and enum_field = 1; -- 不走索引

explain
select * from t_test_index_not_work where  int_field  = 123 -- 走索引

explain
select * from t_test_index_not_work where  varchar_field = 'abc'; -- 不走索引

explain
select * from t_test_index_not_work where  enum_field = 1; -- 不走索引

试验证明：如果创建了联合索引（A，B，C），那么使用where条件时A字段条件一定要存在才能走索引，也就是所谓的“最左”。

目录CONTENT

MySQL索引失效实践