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'; -- 大部分都是 active4.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);