索引失效的几种情况

在使用数据库索引时，索引可以极大提升查询效率，但在某些情况下，索引可能会失效，从而导致查询变慢。以下是几种常见的索引失效情况：

1. WHERE 子句中使用了函数或表达式

如果在 WHERE 子句中对索引列使用了函数或者表达式，索引可能会失效。因为数据库需要对每一行的列进行计算，然后再比较，而不是直接从索引中查找。

示例：

SELECT * FROM users WHERE YEAR(birthdate) = 2020;

在这种情况下，如果 birthdate 列上有索引，由于使用了 YEAR() 函数，索引会失效。

解决方法：避免对索引列使用函数或表达式，可以通过调整条件来使用索引。

SELECT * FROM users WHERE birthdate BETWEEN '2020-01-01' AND '2020-12-31';

2. 模糊查询 % 号在前

在使用 LIKE 进行模糊查询时，如果 % 号出现在查询条件的开头，索引将会失效。因为 % 号在前会导致数据库无法通过索引快速定位到符合条件的记录。

示例：

SELECT * FROM users WHERE name LIKE '%John';

这种查询会导致索引失效。

解决方法：如果可能，尽量避免在查询中使用前置 %，改为后置 %。

SELECT * FROM users WHERE name LIKE 'John%';

3. OR 条件不当使用

当 OR 条件中只有一部分字段上有索引，而其他字段没有索引时，索引会失效。

示例：

SELECT * FROM users WHERE name = 'John' OR age = 30;

如果 name 有索引而 age 没有索引，这种情况下索引可能失效。

解决方法：可以将 OR 改为 UNION 查询，或确保两个条件的列上都有索引。

SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE age = 30;

4. 隐式类型转换

当查询条件中的数据类型与字段的数据类型不一致时，数据库可能会对索引列进行隐式转换，从而导致索引失效。

示例：

SELECT * FROM users WHERE phone = 1234567890;  -- phone 列为字符串类型

此处 phone 是字符串类型，但条件中的值是数字类型，可能会导致索引失效。

解决方法：保持查询条件和列的数据类型一致。

SELECT * FROM users WHERE phone = '1234567890';

5. 不满足最左前缀原则（复合索引）

对于复合索引（多列组合索引），如果查询条件没有使用到最左边的列，索引将不会被使用。

示例： 假设有一个复合索引 (name, age, city)，以下查询不会使用索引：

SELECT * FROM users WHERE age = 25 AND city = 'New York';

因为没有使用到 name，复合索引失效。

解决方法：查询条件应尽量遵守最左前缀原则，首先使用最左边的列。

SELECT * FROM users WHERE name = 'John' AND age = 25;

6. 不等号（<> 或 !=）和 IS NULL/IS NOT NULL

对于一些特定的不等号操作符或 IS NULL/IS NOT NULL，索引可能失效，因为这些操作会导致数据库扫描大量数据。

示例：

SELECT * FROM users WHERE age != 25;

这种查询条件会导致索引失效。

解决方法：如果可能，尽量避免使用不等号，可以通过其他方式优化查询。

7. 过高的表数据选择性

如果一个索引列的数据选择性（即不同值的数量）过低，例如列中的大部分值相同，索引可能不会生效。因为数据库认为全表扫描的成本可能更低。

示例： 对于一个性别字段 gender，只有 M 和 F 两个值，查询时可能不会使用索引。

解决方法：对于选择性较低的列，不建议单独创建索引，可以将其与其他高选择性列组合创建复合索引。

8. 小表不使用索引

对于非常小的表，数据库通常会选择全表扫描而不是使用索引，因为全表扫描的成本更低。

9. 使用 !=、<> 或者 NOT IN

这些操作符常常会导致索引失效，因为它们会导致数据库遍历大部分或全部记录。

解决方法：如果可以，使用等值操作或者范围查询代替。

总结

索引是提高数据库查询效率的关键工具，但在某些情况下可能会失效。开发者需要了解这些情况并合理设计查询和索引，以充分发挥索引的性能。