数据库索引作用失效场景

索引是数据库优化的重要手段，能够显著提高查询性能。然而，在某些情况下，索引可能会失效，导致查询性能下降。以下是 MySQL 和 PostgreSQL 数据库中常见的索引失效场景及其解决方法。

---

1. MySQL 索引失效场景

1.1 对索引列使用函数或表达式

• 场景：在索引列上使用函数或表达式会导致索引失效。
• 示例：

  -- 索引失效
  SELECT * FROM users WHERE YEAR(created_at) = 2023;
  
  -- 优化方法：避免在索引列上使用函数
  SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
  

1.2 使用 OR 条件

• 场景：如果 OR 条件中的某一列没有索引，整个查询可能无法使用索引。
• 示例：

  -- 索引失效（假设 name 列没有索引）
  SELECT * FROM users WHERE id = 1 OR name = 'Alice';
  
  -- 优化方法：使用 UNION 替代 OR
  SELECT * FROM users WHERE id = 1
  UNION
  SELECT * FROM users WHERE name = 'Alice';
  

1.3 使用 LIKE 以通配符开头

• 场景：LIKE 查询以通配符（如 %）开头时，索引可能失效。
• 示例：

  -- 索引失效
  SELECT * FROM users WHERE name LIKE '%Alice%';
  
  -- 优化方法：尽量避免以通配符开头的 LIKE 查询
  SELECT * FROM users WHERE name LIKE 'Alice%';
  

1.4 数据类型不匹配

• 场景：查询条件中的数据类型与索引列的数据类型不匹配时，索引可能失效。
• 示例：

  -- 索引失效（假设 id 是整数类型）
  SELECT * FROM users WHERE id = '1';
  
  -- 优化方法：确保数据类型匹配
  SELECT * FROM users WHERE id = 1;
  

1.5 使用 NOT 或 !=

• 场景：使用 NOT 或 != 可能导致索引失效。
• 示例：

  -- 索引失效
  SELECT * FROM users WHERE id != 1;
  
  -- 优化方法：尽量避免使用 NOT 或 !=
  SELECT * FROM users WHERE id > 1 OR id < 1;
  

1.6 复合索引未使用前缀

• 场景：复合索引未使用前缀列时，索引可能失效。
• 示例：

  -- 假设有复合索引 (name, email)
  -- 索引失效
  SELECT * FROM users WHERE email = 'example@example.com';
  
  -- 优化方法：使用复合索引的前缀列
  SELECT * FROM users WHERE name = 'Alice' AND email = 'example@example.com';
  

---

2. PostgreSQL 索引失效场景

2.1 对索引列使用函数或表达式

• 场景：在索引列上使用函数或表达式会导致索引失效。
• 示例：

  -- 索引失效
  SELECT * FROM users WHERE LOWER(name) = 'alice';
  
  -- 优化方法：创建函数索引
  CREATE INDEX idx_users_lower_name ON users(LOWER(name));
  SELECT * FROM users WHERE LOWER(name) = 'alice';
  

2.2 使用 OR 条件

• 场景：如果 OR 条件中的某一列没有索引，整个查询可能无法使用索引。
• 示例：

  -- 索引失效（假设 name 列没有索引）
  SELECT * FROM users WHERE id = 1 OR name = 'Alice';
  
  -- 优化方法：使用 UNION 替代 OR
  SELECT * FROM users WHERE id = 1
  UNION
  SELECT * FROM users WHERE name = 'Alice';
  

2.3 使用 LIKE 以通配符开头

• 场景：LIKE 查询以通配符（如 %）开头时，索引可能失效。
• 示例：

  -- 索引失效
  SELECT * FROM users WHERE name LIKE '%Alice%';
  
  -- 优化方法：使用全文搜索或避免以通配符开头的 LIKE 查询
  SELECT * FROM users WHERE name LIKE 'Alice%';
  

2.4 数据类型不匹配

• 场景：查询条件中的数据类型与索引列的数据类型不匹配时，索引可能失效。
• 示例：

  -- 索引失效（假设 id 是整数类型）
  SELECT * FROM users WHERE id = '1';
  
  -- 优化方法：确保数据类型匹配
  SELECT * FROM users WHERE id = 1;
  

2.5 使用 NOT 或 !=

• 场景：使用 NOT 或 != 可能导致索引失效。
• 示例：

  -- 索引失效
  SELECT * FROM users WHERE id != 1;
  
  -- 优化方法：尽量避免使用 NOT 或 !=
  SELECT * FROM users WHERE id > 1 OR id < 1;
  

2.6 复合索引未使用前缀

• 场景：复合索引未使用前缀列时，索引可能失效。
• 示例：

  -- 假设有复合索引 (name, email)
  -- 索引失效
  SELECT * FROM users WHERE email = 'example@example.com';
  
  -- 优化方法：使用复合索引的前缀列
  SELECT * FROM users WHERE name = 'Alice' AND email = 'example@example.com';
  

---

3. 通用索引失效场景

3.1 数据分布不均匀

• 场景：如果索引列的数据分布不均匀（如大部分值相同），索引可能失效。
• 优化方法：重新设计索引或使用其他查询条件。

3.2 索引统计信息不准确

• 场景：索引统计信息不准确可能导致查询优化器选择不使用索引。
• 优化方法：更新索引统计信息。
  • MySQL：使用 ANALYZE TABLE。
  • PostgreSQL：使用 ANALYZE。

3.3 查询返回大量数据

• 场景：如果查询返回的数据量很大，数据库可能选择全表扫描而不是使用索引。
• 优化方法：优化查询条件，减少返回的数据量。

---

总结

• MySQL 和 PostgreSQL 索引失效场景：包括对索引列使用函数、使用 OR 条件、LIKE 以通配符开头、数据类型不匹配、使用 NOT 或 !=、复合索引未使用前缀等。
• 优化方法：避免在索引列上使用函数、使用 UNION 替代 OR、创建函数索引、确保数据类型匹配、优化查询条件等。

通过了解这些索引失效场景及其优化方法，可以更好地设计和使用索引，提高数据库查询性能。