PgSql索引生效和失效

约 1749 字大约 6 分钟

2024-12-25

1. 索引类型

B-tree：默认索引类型，适用于等值、范围查询
Hash：仅支持等值查询（不推荐）
GIN：用于数组、JSONB、全文检索等复杂类型
GiST：支持多维数据（如几何、全文搜索）
SP-GiST：适合非平衡树结构（如 IP 地址）
BRIN：大表范围索引，适合有序字段

2. 创建索引

CREATE INDEX name ON table USING btree (column);

3. 生效场景

3.1 等值查询

生效：B-tree 索引对等值查询非常高效。

CREATE INDEX idx_user_id ON users(user_id);

SELECT * FROM users WHERE user_id = 123;

3.2 范围查询

生效：B-tree 索引支持范围查询（如 <, <=, >, >=, BETWEEN）。

SELECT * FROM orders WHERE create_time > '2024-01-01';

3.3 组合索引（前缀匹配）

生效：使用了组合索引的前导列（name），后续列也参与优化。

CREATE INDEX idx_name_age ON users(name, age);

SELECT * FROM users WHERE name = 'Alice' AND age > 30;

3.4 IN 查询

生效：B-tree 索引可处理多个值的查找。

SELECT * FROM users WHERE user_id IN (100, 200, 300);

3.5 LIKE 查询（前缀匹配）

生效：B-tree 索引可支持前缀匹配（不能以 % 开头）。

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

3.6 IS NULL / IS NOT NULL

生效：如果字段上有索引，默认情况下 PostgreSQL 是不会使用该索引的，除非是表达式索引或使用了 SET LOCAL enable_indexscan = off; 等特殊手段。注意：默认索引不会包含 NULL 值，除非创建了部分索引或表达式索引来专门覆盖这种情况。

SELECT * FROM users WHERE email IS NULL;

3.7 数组操作符查询（需 GIN 索引）

生效：如果 tags 字段有 GIN 索引，则可以加速这类查询。

SELECT * FROM customer WHERE tags @> ARRAY['aa', 'bb'];

3.8 JSONB 查询（GIN 索引）

生效：为 JSONB 字段创建合适的 GIN 索引后，可大幅提高查询效率。

SELECT * FROM logs WHERE data @> '{"type": "error"}';

3.9 函数索引（表达式索引）

生效：通过函数索引可以优化特定表达式的查询。

CREATE INDEX idx_lower_name ON users (lower(name));

SELECT * FROM users WHERE lower(name) = 'alice';

4. 失效场景

4.1 使用了函数/表达式但未创建函数索引

失效：如果没有创建 lower(name) 的函数索引，无法使用普通索引。

SELECT * FROM users WHERE lower(name) = 'alice';

4.2 模糊查询以 % 开头

失效：无法使用 B-tree 索引，建议使用 pg_trgm 扩展 + GIN/GiST 索引支持任意位置匹配。

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

4.3 OR 条件中部分条件无索引

失效：如果 email 没有索引，整个查询可能退化为全表扫描。

SELECT * FROM users WHERE name = 'Alice' OR email = 'alice@example.com';

4.4 隐式类型转换导致索引失效

失效：由于类型不一致，可能导致索引无法使用（视优化器判断而定）。

-- 假设 id 是 INT 类型
SELECT * FROM users WHERE id = '123'; -- 字符串传入

4.5 使用 NOT 条件（如 NOT IN、!=）

失效：通常会导致全表扫描，因为索引更适合“找到”而不是“排除”。

SELECT * FROM users WHERE user_id != 123;

4.6 组合索引未使用前导列

失效：没有使用组合索引的第一个字段 name，则无法命中该索引

CREATE INDEX idx_name_age ON users(name, age);

SELECT * FROM users WHERE age > 30;

4.7 大数据量下优化器选择顺序扫描

失效：当优化器认为“大部分数据都满足条件”，可能会放弃索引扫描，选择更高效的顺序扫描。

SELECT * FROM large_table WHERE indexed_column = 'some_value';

4.8 查询返回大量数据（超过阈值）

失效：如果查询结果占总数据比例过高（例如 20%+），优化器会选择顺序扫描。

SELECT * FROM users WHERE status = 'active'; -- 大部分都是 active

4.9 未分析表统计信息

失效：如果没有更新统计信息，优化器可能做出错误决策，导致索引未被使用。

ANALYZE users;

5. 如何查看索引是否命中

观察输出中是否有：

Index Scan
Bitmap Index Scan 如果没有看到相关索引操作，说明索引未命中。

EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'Alice';

6.查询占比过高导致索引失效

在 PostgreSQL 中，索引是否被使用并不是由一个固定的“占比阈值”决定的，而是由查询优化器（Query Planner）根据统计信息和成本估算模型综合判断。但有一个经验性参考值：当查询结果占表总数据量的 5% ~ 20% 左右，PostgreSQL 的优化器可能会认为使用索引扫描的成本高于顺序扫描（SeqScan），从而选择放弃索引。

6.1 占比

< 1% ✅ 高数据集中，索引效率高
1% ~ 5% ✅ 较高多数情况下仍会使用索引
5% ~ 20% ⚠️ 不确定优化器权衡后可能不使用
>20% ❌ 很可能失效顺序扫描更高效

⚠️ 注意：这个范围是经验性的，并非硬性规定。实际行为取决于多个因素。

6.2 为什么“结果占比高”会导致索引失效？

随机 I/O 成本高使用索引查找时，数据库需要先读取索引页，再通过指针跳转到数据页。如果要查很多行，这种“跳跃式访问”会比一次性顺序扫描整个表更慢。
顺序 I/O 更快磁盘或 SSD 的顺序读取速度远高于随机读取。所以当查询结果较多时，PostgreSQL 会选择全表扫描来减少 I/O 次数。
维护开销索引本身也有维护成本，如果大部分数据都匹配条件，使用索引反而会增加 CPU 和内存负担。

7. 回表

7.1 什么是回表

CREATE INDEX idx_users_name ON users(name);

SELECT * FROM users WHERE name = 'Alice';

查询执行过程：

使用 idx_users_name 索引快速定位到所有 name = 'Alice' 的记录；
该索引只包含 name 和对应的主键 id；
为了获取完整的记录（如 email 字段），数据库需要拿着这些 id 去主表（聚簇索引）中查询完整数据。

➡️ 这个从二级索引跳转到主表的过程，就叫 “回表”。

7.2 为什么“回表”会影响性能？

每次回表都需要一次额外的 I/O 操作；
如果查询结果很多（比如几千条甚至几万条），就会导致大量的磁盘访问或内存读取；
在使用模糊查询（如 LIKE '%xxx%'）、范围查询等时，容易触发大量回表操作，从而影响性能。

7.3 如何避免“回表”？

使用覆盖索引（Covering Index）覆盖索引是指：索引本身已经包含了查询所需的所有字段，无需再回到主表查询。

CREATE INDEX idx_users_name_email ON users(name, email);

--此时以下查询就不会回表：
SELECT email FROM users WHERE name = 'Alice';

使用 INCLUDE 子句（PostgreSQL 11+ 支持）

--如果只想索引某些字段用于过滤，但又想包含其他字段避免回表，可以使用 INCLUDE：
-- 这样也能避免回表查询 email。
CREATE INDEX idx_users_name_include_email ON users(name) INCLUDE (email);