B-tree 索引失效 避坑指南

警惕！这些场景会让你的 B-tree 索引失效（PostgreSQL/MySQL 避坑指南）

在 PostgreSQL 和 MySQL 中，‌B-tree 索引‌是优化查询性能的利器，但若使用不当，索引可能无法被数据库优化器命中，导致查询效率骤降。本文将揭秘 10 个常见的 B-tree 索引失效场景，助你避坑！

---

一、索引失效的十大场景

1️⃣ 违反最左前缀原则

‌场景‌：联合索引 (a, b, c) 生效需从最左列 a 开始，否则索引失效。
‌示例‌：

-- 索引失效（未包含 a）
SELECT * FROM table WHERE b = 1 AND c = 2; 
-- 部分失效（跳过 b）
SELECT * FROM table WHERE a = 1 AND c = 2; ```
‌解决‌：确保查询条件包含最左列，并按顺序组合条件。

2️⃣ 对索引列进行运算或函数操作

‌场景‌：对索引列使用函数、表达式或类型转换。
‌示例‌：

-- 索引失效（YEAR 函数）
SELECT * FROM table WHERE YEAR(date_column) = 2023; ```
-- 索引失效（运算）
SELECT * FROM table WHERE price * 0.8 > 100; 
‌解决‌：改写查询，避免对索引列直接运算：
SELECT * FROM table WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';

3️⃣ 隐式类型转换
‌场景‌：查询条件与索引列类型不匹配（如字符串转数值）。
‌示例‌：

-- 索引失效（string_column 是 VARCHAR）
SELECT * FROM table WHERE string_column = 123; 
‌解决‌：保持类型一致：
SELECT * FROM table WHERE string_column = '123';

4️⃣ 混合使用 OR 条件
‌场景‌：OR 连接索引列和非索引列，导致全表扫描。
‌示例‌：

-- 索引失效（b 无索引）
SELECT * FROM table WHERE a = 1 OR b = 2; 
‌解决‌：改用 UNION 或为所有列创建索引：
(SELECT * FROM table WHERE a = 1) UNION (SELECT * FROM table WHERE b = 2);

5️⃣ 模糊查询前导通配符
‌场景‌：LIKE 以 % 或 _ 开头，无法利用索引。
‌示例‌：

-- 索引失效
SELECT * FROM table WHERE name LIKE '%John'; 
-- 有效（后缀匹配）
SELECT * FROM table WHERE name LIKE 'John%'; 
‌解决‌：避免前导通配符，或使用全文索引。

6️⃣ 数据区分度过低
‌场景‌：索引列重复值过多（如性别），优化器放弃索引。
‌示例‌：

-- 可能全表扫描（gender 仅有 'M'/'F'）
SELECT * FROM table WHERE gender = 'M'; 
‌解决‌：避免对低区分度列建索引，或强制使用索引：
SELECT * FROM table FORCE INDEX(index_name) WHERE gender = 'M';

7️⃣ 使用否定条件
‌场景‌：!=、NOT IN、NOT EXISTS 等否定操作。
‌示例‌：

-- 可能全表扫描
SELECT * FROM table WHERE a != 1; 
SELECT * FROM table WHERE a NOT IN (1, 2); 
‌解决‌：改写为正向查询或范围查询：
SELECT * FROM table WHERE a > 1;

8️⃣ 覆盖索引未命中
‌场景‌：查询需回表时，优化器可能放弃索引。
‌示例‌：

Copy Code
-- 索引 (a, b)，需回表
SELECT * FROM table WHERE a = 1; 
-- 覆盖索引，无需回表
SELECT a, b FROM table WHERE a = 1; 
‌解决‌：尽量使用覆盖索引（查询列包含在索引中）。

9️⃣ 统计信息过期
‌场景‌：表频繁更新后，统计信息未刷新，优化器误判代价。
‌解决‌：手动更新统计信息：

Copy Code
-- PostgreSQL
ANALYZE table_name;
-- MySQL
ANALYZE TABLE table_name;

🔟 全表扫描更优
‌场景‌：查询需要访问大部分数据时，全表扫描更快。
‌示例‌：

-- 无过滤条件，全表扫描
SELECT * FROM table; 
‌解决‌：优化查询条件或分页查询。

二、如何验证索引是否失效？
使用 ‌EXPLAIN‌ 分析执行计划：

-- PostgreSQL/MySQL 通用
EXPLAIN SELECT * FROM table WHERE condition;
‌MySQL‌：检查 key 字段是否为索引名，type 是否为 ref/range。
‌PostgreSQL‌：查看执行计划中是否出现 Index Scan。

三、最佳实践总结

‌设计索引时‌：
优先满足最左前缀原则。
避免对低区分度列建索引。
‌编写 SQL 时‌：
禁止对索引列进行运算或函数操作。
注意隐式类型转换。
‌维护阶段‌：
定期更新统计信息。
监控慢查询，及时优化索引。
‌提醒‌：索引不是越多越好！过度索引会导致写性能下降。合理设计，方能兼顾读写效率。