一篇文章聊透索引失效有哪些情况及如何解决-益强数据堂

一篇文章聊透索引失效有哪些情况及如何解决

时间：2025-11-05 13:53:09 来源：益强数据堂作者：人工智能阅读：854次

MySQL 索引失效是篇文一个相当普遍的问题，尤其在处理慢查询时特别需要注意是章聊否存在索引失效的情况。

排查索引失效问题的透索第一步，必须定位要分析的引失 SQL 语句，然后通过EXPLAIN来查看其执行计划。情况主要关注type、及何解决key和extra这几个字段。篇文

具体需要关注的章聊字段可参考文章：分析 SQL 执行计划，需要关注哪些重要信息

我们需根据 key、透索type 和 extra 判断 SQL 查询是引失否利用了索引。若是情况，是及何解决否为覆盖索引、索引下推、篇文整体索引扫描，章聊或是透索索引跳跃扫描等情况。

通常，优化的索引使用情况包括以下几种：

首先，key 字段必须有值，不得为 NULL。其次，type 应该是 ref、亿华云eq_ref、range、const 等几种类型。此外，extra 字段如果为 NULL 或者包含"using index"、"using index condition"都是可以接受的情况。

如果执行计划显示一条 SQL 语句没有有效利用索引，例如 type = ALL，key = NULL，extra = Using where。

那么就需要进一步分析未能有效利用索引的原因。需要了解的是，是否需要使用索引以及应该使用哪个索引，这是由 MySQL 的优化器决定的，它会根据成本估算做出决策。

以下是导致未能有效利用索引的几种可能情况：

索引未正确创建：当查询语句中的 where 条件涉及的字段未创建索引，或者索引未满足最左前缀匹配条件时，就未能正确创建索引。索引区分度不足：如果索引的区分度不高，站群服务器可能导致未使用索引，因为在这种情况下，利用索引并不能有效提升查询效率。表过小：当表中的数据量很少时，优化器可能认为全表扫描的成本不高，因此可能选择不使用索引。查询语句中使用了函数或字段类型不匹配等情况导致索引失效。

这时候我们需要从头开始逐一分析：

如果索引未正确创建，根据 SQL 语句创建适当的索引。如果未遵守最左前缀原则，调整索引或修改 SQL 语句。若索引区分度不高，考虑选择另一个更合适的索引字段。对于表过小的情况，优化影响可能不大，因此是否使用索引可以不做过多优化。排查具体的失效原因，然后有针对性地调整 SQL 语句。可能导致索引失效的情况

假设我们有一张表（以下 SQL 实验基于 MySQL 5.7）：

复制CREATE TABLE `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `age` int(11) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `age` (`age`), KEY `create_time` (`create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into mytable(id,name,age,create_time) values (1,"paidaxing",20,now()); insert into mytable(id,name,age,create_time) values (2,"paidaxing1",21,now()); insert into mytable(id,name,age,create_time) values (3,"paidaxing2",22,now()); insert into mytable(id,name,age,create_time) values (4,"paidaxing3",20,now()); insert into mytable(id,name,age,create_time) values (5,"paidaxing4",14,now()); insert into mytable(id,name,age,create_time) values (6,"paidaxing5",43,now()); insert into mytable(id,name,age,create_time) values (7,"paidaxing6",32,now()); insert into mytable(id,name,age,create_time) values (8,"paidaxing7",12,now()); insert into mytable(id,name,age,create_time) values (9,"paidaxing8",1,now()); insert into mytable(id,name,age,create_time) values (10,"paidaxing9",43,now());1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21. 索引列参与计算复制select * from mytable where age = 12; +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+1.2.3.4.5.6.7.

以上 SQL 语句是可以利用索引的，但如果在字段中增加计算操作，就可能导致索引失效：

但如果计算的形式如下，仍然可以利用索引：

以上 SQL 语句可以利用索引，WordPress模板但如果在字段中添加函数操作，可能会导致索引失效：

但如果使用 OR，并且 OR 条件中的两侧包含<或者>操作符时，可能会导致索引失效，例如：

但如果 OR 条件的两侧都是等号判断，并且两个字段都有索引，仍然可以利用索引，例如：

以上四种 LIKE 模式中，"paidaxing%"和"paida%xing"这两种可以利用索引，但是如果是"%paidaxing%"和"%paidaxing"就无法利用索引。

以上情况中，如果表中的 name 字段是 varchar 类型，但我们使用 int 类型进行查询，这会导致索引失效。

然而，有一个特例是，如果字段类型是 int 类型，而查询条件中添加了单引号或双引号，MySQL 会将参数转换为 int 类型，这种情况下也可以利用索引。

但并非所有情况都是如此，例如在以下情况下，使用 ID 进行!=比较时，可能会利用索引：

在使用!=（不等于）操作符时，索引是否失效与多个因素有关，包括索引的选择性、数据的分布情况等，并不能简单地因为使用了!=操作符就说导致了索引失效。

is not null

以下情况是索引失效的：