索引失效的 12 种情况

目录

一、未使用索引字段进行查询

二、索引列使用了函数或表达式

三、使用了不等于（!= 或 <>）操作符

四、LIKE 操作符的模糊查询

五、对索引列进行了数据类型转换

六、使用 OR 连接多个条件

七、表中数据量较少

八、索引列上存在大量重复值

九、数据分布不均匀

十、索引列上存在过多的 NULL 值

十一、索引字段与排序字段不匹配

十二、索引列进行了隐式类型转换

---

索引失效是指数据库中的索引无法有效地加速查询操作，导致查询性能下降。索引失效的情况主要包括以下几种：

一、未使用索引字段进行查询

• 当查询条件中不包含索引字段时，数据库无法利用索引来加速查询，导致索引失效。
• 例如，如果一个表的索引是在 user_id 字段上建立的，但是查询时使用了 name 字段作为条件，那么索引就会失效。

二、索引列使用了函数或表达式

• 如果查询条件中对索引列使用了函数、类型转换或者表达式，数据库无法直接使用索引，而是需要先计算表达式的值，然后再进行查询，这会导致索引失效。
• 例如，如果对 date_created 字段进行了函数操作，如 DATE_FORMAT(date_created, '%Y-%m-%d') = '2022-01-01'，那么索引可能会失效。

三、使用了不等于（!= 或 <>）操作符

• 对于不等于操作符，数据库通常无法利用索引进行加速查询，因为索引是按照排序顺序组织的，而不等于操作符无法利用索引的排序特性。
• 例如，SELECT * FROM table WHERE status != 'completed'，这种情况下索引可能会失效。

四、LIKE 操作符的模糊查询

• 对于 LIKE 操作符进行的模糊查询，如果通配符放在索引列的开头，数据库无法利用索引，导致索引失效。
• 例如，SELECT * FROM table WHERE name LIKE '%keyword%'，这种情况下索引可能会失效。

五、对索引列进行了数据类型转换

• 如果查询条件中对索引列进行了数据类型转换，数据库可能无法利用索引，导致索引失效。
• 例如，如果索引是在 age 字段上建立的，但是查询时对 age 进行了类型转换，如 CAST(age AS CHAR) = '18'，那么索引可能会失效。

六、使用 OR 连接多个条件

• 当查询条件中使用了 OR 连接多个条件时，如果其中至少一个条件无法利用索引，整个查询可能会导致索引失效。
• 例如，SELECT * FROM table WHERE status = 'completed' OR amount > 1000，这种情况下索引可能会失效。

七、表中数据量较少

• 当表中的数据量较少时，数据库可能会选择全表扫描而不是利用索引，因为全表扫描的开销相对较小，这也可能导致索引失效。

八、索引列上存在大量重复值

• 当索引列上存在大量重复值时，数据库可能会认为使用索引并不能显著提高查询性能，因此选择不使用索引，导致索引失效。
• 例如，如果索引是在布尔类型的 is_active 字段上建立的，而且大部分记录的值都是相同的，那么索引可能会失效。

九、数据分布不均匀

• 如果索引列的数据分布不均匀，即某些值出现的频率远高于其他值，数据库可能会选择不使用索引，导致索引失效。
• 例如，如果在一个性别字段上建立了索引，但是大部分记录的性别都是相同的，那么索引可能会失效。

十、索引列上存在过多的 NULL 值

• 当索引列上存在过多的 NULL 值时，数据库可能会认为使用索引并不能提高查询性能，因为索引无法存储 NULL 值，导致索引失效。
• 例如，如果在一个可选的电话号码字段上建立了索引，而大部分记录的电话号码都是 NULL，那么索引可能会失效。

十一、索引字段与排序字段不匹配

• 如果查询中使用了 ORDER BY 子句，而排序字段与索引字段不匹配，数据库可能会选择不使用索引，导致索引失效。
• 例如，如果在 created_at 字段上建立了索引，但是查询中排序字段是 updated_at，那么索引可能会失效。

十二、索引列进行了隐式类型转换

• 当查询条件中对索引列进行了隐式类型转换时，数据库可能无法利用索引，导致索引失效。
• 例如，如果索引是在整数类型的 id 字段上建立的，但是查询时传入的是字符串类型的值，那么索引可能会失效。