跳到主要内容

MySQL 索引失效情况

在MySQL中，索引是提高查询性能的重要工具。然而，在某些情况下，索引可能会失效，导致查询性能下降。本文将详细介绍MySQL索引失效的常见情况，并通过实际案例帮助你理解如何避免这些问题。

什么是索引失效？​

索引失效是指MySQL在执行查询时，无法有效利用索引来加速查询，而是进行了全表扫描或其他低效的操作。这通常是由于查询条件或表结构设计不当导致的。

索引失效的常见情况​

1. 使用!=或<>操作符​

当查询条件中使用!=或<>操作符时，MySQL通常无法使用索引。这是因为这些操作符需要检查所有不等于给定值的记录，而索引无法直接支持这种操作。

sql
-- 示例：索引失效
SELECT * FROM users WHERE age != 30;

2. 使用OR连接条件​

如果查询条件中使用OR连接多个条件，且这些条件中至少有一个无法使用索引，那么整个查询将无法使用索引。

sql
-- 示例：索引失效
SELECT * FROM users WHERE age = 30 OR name = 'John';

3. 对索引列进行函数操作​

如果对索引列进行函数操作（如DATE()、UPPER()等），MySQL将无法使用索引。

sql
-- 示例：索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;

4. 使用LIKE以通配符开头​

当使用LIKE进行模糊查询时，如果通配符%出现在开头，MySQL将无法使用索引。

sql
-- 示例：索引失效
SELECT * FROM users WHERE name LIKE '%John%';

5. 数据类型不匹配​

如果查询条件中的数据类型与索引列的数据类型不匹配，MySQL将无法使用索引。

sql
-- 示例：索引失效
SELECT * FROM users WHERE age = '30';  -- age是整数类型

6. 复合索引未使用最左前缀​

对于复合索引（即多列索引），MySQL只能使用索引的最左前缀。如果查询条件中没有使用最左前缀，索引将失效。

sql
-- 示例：索引失效
CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE age = 30;  -- 未使用name列

实际案例​

假设我们有一个users表，包含以下列：id、name、age、created_at。我们为name和age列创建了一个复合索引idx_name_age。

sql
CREATE INDEX idx_name_age ON users(name, age);

案例1：使用LIKE以通配符开头​

sql
-- 索引失效
SELECT * FROM users WHERE name LIKE '%John%';

案例2：对索引列进行函数操作​

sql
-- 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;

案例3：复合索引未使用最左前缀​

sql
-- 索引失效
SELECT * FROM users WHERE age = 30;

如何避免索引失效？​

  1. 避免使用!=或<>操作符：尽量使用=操作符或其他可以充分利用索引的操作符。
  2. 避免使用OR连接条件：可以将OR条件拆分为多个查询，或者使用UNION。
  3. 避免对索引列进行函数操作：尽量将函数操作放在查询条件的右侧。
  4. 避免使用LIKE以通配符开头：如果必须使用通配符，尽量将其放在末尾。
  5. 确保数据类型匹配：确保查询条件中的数据类型与索引列的数据类型一致。
  6. 合理设计复合索引：确保查询条件中使用复合索引的最左前缀。

总结​

MySQL索引是提高查询性能的重要工具，但在某些情况下，索引可能会失效。了解索引失效的常见情况，并采取相应的措施避免这些问题，可以显著提高查询性能。

附加资源​

练习​

  1. 创建一个包含name和age列的users表，并为这两列创建复合索引。
  2. 编写一个查询，确保使用了复合索引的最左前缀。
  3. 修改查询条件，使其导致索引失效，并观察查询性能的变化。