DBA夜读·第六季第3期｜索引艺术——从B-tree到向量检索

第六季·《PostgreSQL从入门到精通》 本季将开启一段全新的数据库学习旅程。我们将从零开始，系统性地探索“世界上最先进的开源数据库”——PostgreSQL。本季旨在为DBA和开发者提供一条从入门到专家的清晰学习路径。

【上期回顾】

上一期我们深入学习了PostgreSQL强大的数据类型系统：

• 标准类型：数值、字符串、日期时间、布尔

• 高级类型：JSON/JSONB（文档数据库）、数组、范围类型、复合类型

• DDL/DML：数据库/表管理、UPSERT（ON CONFLICT）

• 查询基础：SELECT、聚合、CTE、窗口函数入门

掌握了这些，你已经能够使用PostgreSQL进行日常开发了。但有一个关键问题亟待解决：性能。

无论表结构设计得多合理，随着数据量增长，查询速度都会成为瓶颈。而解决方案就是——索引。

【本集概览】

【第一部分】索引基础

1.1 为什么需要索引？

索引是数据库中用于加速数据检索的数据结构。没有索引时，数据库只能进行顺序扫描（Sequential Scan）——逐行检查，时间复杂度O(n)。有索引后，可以快速定位目标数据，时间复杂度降至O(log n)甚至O(1)。

sql

-- 没有索引：全表扫描

EXPLAIN SELECT * FROM users WHERE email ='zhang@example.com';

-- Seq Scan on users (cost=0.00..1000.00 rows=1 width=100)

-- 创建索引后：索引扫描

CREATE INDEX idx_users_email ON users(email);

EXPLAIN SELECT * FROM users WHERE email ='zhang@example.com';

-- Index Scan using idx_users_email (cost=0.28..8.29 rows=1)

1.2 B-tree索引——通用之王

B-tree是PostgreSQL的默认索引类型，也是最通用的索引。适用于：

• 等值查询（=
  ）

• 范围查询（>
  、<
  、BETWEEN
  ）

• 排序（ORDER BY
  ）

• 前缀匹配（LIKE 'abc%'
  ，注意%
  不能在开头）

sql

-- 创建B-tree索引（默认类型）

CREATE INDEX idx_users_age ON users(age);

CREATE INDEX idx_users_created ON users(created_at);

-- 查看索引

\d users

-- 删除索引

DROPINDEX idx_users_age;

-- 重命名索引

ALTER INDEX idx_users_created RENAMETO idx_users_created_at;

B-tree索引内部结构：

text

                    50

                      \

                30      80

               /  \    /  \

             20  40  70  90

              \

          10  25

- 平衡树结构，所有叶子节点在同一层

- 节点内数据有序排列

- 查询复杂度 O(log n)

1.3 多列索引（复合索引）

复合索引包含多个列，对同时过滤多列的查询非常有效。

sql

-- 创建复合索引

CREATE INDEX idx_users_age_status ON users(age,status);

-- 以下查询会使用该索引

SELECT * FROM users WHERE age =25 AND status='active';

SELECT * FROM users WHERE age =25;

-- 只使用第一列

SELECT * FROM users ORDER BY age,status;

-- 支持排序

-- 以下查询**不会**使用该索引

SELECT * FROM users WHEREstatus='active';

-- 缺少前导列

复合索引的关键原则——最左前缀原则：

索引 (a, b, c)
 可以加速以下条件的查询：

• WHERE a = ?
  

• WHERE a = ? AND b = ?
  

• WHERE a = ? AND b = ? AND c = ?
  

• WHERE a = ? ORDER BY b
  

不能加速：

• WHERE b = ?
  （缺少a）

• WHERE a = ? ORDER BY c
  （跳过了b）

sql

-- 实际案例：电商订单查询

-- 常见查询：按用户+时间范围

CREATE INDEX idx_orders_user_time ON orders(user_id, created_at);

-- 能用的查询

SELECT * FROM orders WHERE user_id =123;

-- 使用索引

SELECT * FROM orders WHERE user_id =123 AND created_at >'2026-01-01';

-- 使用索引

SELECT * FROM orders WHERE user_id =123 ORDER BY created_at;

-- 使用索引，避免排序

-- 不能高效使用的查询

SELECT * FROM orders WHERE created_at >'2026-01-01';

-- 全表扫描

1.4 唯一索引

唯一索引保证列（或列组合）的值在表中唯一。

sql

-- 单列唯一索引

CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 多列唯一索引（联合唯一）

CREATE UNIQUE INDEX idx_user_product ON user_favorites(user_id, product_id);

-- 条件唯一索引（部分唯一）：只对active用户要求唯一

CREATE UNIQUE INDEX idx_active_user_email ON users(email)

WHERE status='active';

与UNIQUE约束的关系：

sql

-- 这两种写法效果完全相同

ALTERTABLE users ADD CONSTRAINT users_email_unique UNIQUE(email);

CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 区别：约束是标准SQL，索引是PostgreSQL实现

-- 推荐使用UNIQUE约束，语义更清晰

【第二部分】高级索引类型

2.1 Hash索引——极速等值查询

Hash索引专门用于等值查询（=
），在等值查询场景下，Hash索引通常比B-tree更快。

sql

-- 创建Hash索引

CREATE INDEX idx_users_phone_hash ON users USIN Ghash(phone);

-- 适用查询

SELECT*FROM users WHERE phone ='13800000000';

-- 使用Hash索引

-- 不适用查询

SELECT * FROM users WHERE phone >'13800000000';

-- 无法使用Hash

SELECT * FROM users ORDER BY phone;

-- 无法使用Hash

Hash索引特点：

PostgreSQL 10+重要改进：Hash索引已支持WAL日志（崩溃安全），可以放心在生产环境使用。

2.2 GIN索引——倒排索引之王

GIN（Generalized Inverted Index，通用倒排索引） 是PostgreSQL最强大的索引类型之一，专门用于包含多值组件的列。

适用数据类型：

• tsvector
  （全文搜索）

• jsonb
  （JSON文档）

• array
  （数组）

• text
  （支持全文搜索）

sql

-- 1. JSONB索引

CREATE INDEX idx_products_gin ON products USING gin(product_info);

-- 加速JSON查询

SELECT * FROM products WHERE product_info @>'{"category": "electronics"}';

SELECT * FROM products WHERE product_info ? 'in_stock';

-- 2. 数组索引

CREATE INDEX idx_articles_tags ON articles USING gin(tags);

-- 加速数组查询

SELECT * FROM articles WHERE'postgres'=ANY(tags);

SELECT * FROM articles WHERE tags @> ARRAY['postgres','database'];

-- 3. 全文搜索索引

CREATE INDEX idx_documents_fts ON documents USING gin(to_tsvector('english', content));

-- 加速全文搜索

SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & tutorial');

GIN索引结构特点：

text

文档1: [apple, banana, orange]

文档2: [apple, grape]

文档3: [banana, orange, pear]

倒排索引：

apple  → 文档1, 文档2

banana → 文档1, 文档3

orange → 文档1, 文档3

grape  → 文档2

pear   → 文档3

查询 apple AND banana → 文档1

2.3 GiST索引——平衡树与搜索树

GiST（Generalized Search Tree，通用搜索树） 是一个平衡树的索引框架，支持多种非传统数据类型。

适用场景：

• 地理空间数据（PostGIS）

• 全文搜索（替代GIN）

• 范围类型（如tsrange
  ）

• 模糊匹配（pg_trgm
  ）

sql

-- 1. 范围类型索引（排他约束需要btree_gist）

CREATE EXTENSION btree_gist;

CREATE TABLE reservations (

    room_id integer,

    period tsrange,

    EXCLUDE USING gist (room_id WITH=, period WITH &&));

-- 2. 地理空间索引（PostGIS）

CREATE EXTENSION postgis;

CREATE TABLE locations (

    id integer,

    geom geometry(Point,4326));

CREATE INDEX idx_locations_geom ON locations USING gist(geom);

-- 查询附近点

SELECT * FROM locations WHERE ST_DWithin(geom, ST_MakePoint(121.48,31.22)::geography,1000);

-- 3. 模糊匹配索引（pg_trgm）

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);

-- 支持 LIKE '%xxx%'（前后都有%）

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

GiST vs GIN：

2.4 BRIN索引——大数据集的轻量之选

BRIN（Block Range Index，块范围索引） 是PostgreSQL 9.5引入的特殊索引，专为超大数据表设计，磁盘占用极小。

原理：BRIN不索引每一行，而是索引每个数据块的范围（最小值、最大值）。

sql

-- 创建BRIN索引

CREATE INDEX idx_orders_created_brin ON orders USING brin(created_at);

-- 适用场景：天然有序的数据

-- ✅ 时间戳（插入时间递增）

-- ✅ 自增ID

-- ✅ 日志表、时序数据

BRIN索引效率对比：

sql

-- 实际案例：日志表

CREATE TABLE access_logs (

    id bigserial,

    log_time timestamptz defaultnow(),

    url text,

    ip inet);

-- 插入1000万条数据后

-- B-tree索引：约200MB

CREATE INDEX idx_logs_time_btree ON access_logs(log_time);

-- BRIN索引：仅约2MB

CREATE INDEX idx_logs_time_brin ON access_logs USING brin(log_time);

-- 查询性能对比

EXPLAIN (ANALYZE, BUFFERS)

SELECT * FROM access_logs WHERE log_time BETWEEN '2026-04-01' AND '2026-04-02';

-- BRIN索引扫描的块数远少于B-tree

BRIN索引限制：

• 仅适用于自然有序的数据

• 不适合随机插入的数据

• 范围查询效率高，但点查询（=
  ）可能不如B-tree

2.5 SP-GiST索引——空间分区树

SP-GiST（Space-Partitioned GiST） 适用于非平衡、空间可分区的数据结构。

适用场景：

• 点、线、面（GIS）

• IP地址（inet类型）

• 前缀搜索（电话号码、邮政编码）

sql

-- IP地址索引

CREATE TABLE ip_ranges (

    ip_start inet,

    ip_end inet,

    location text);

CREATE INDEX idx_ip_range ON ip_ranges USING spgist(ip_start);

-- 前缀搜索

CREATE TABLE phone_numbers (

    phone text);

CREATE INDEX idx_phone_prefix ON phone_numbers USING spgist(phone);

SELECT * FROM phone_numbers WHERE phone ^@ '138';

-- 以138开头的号码

【第三部分】索引优化技巧

3.1 部分索引——只索引你需要的数据

部分索引只对表中满足条件的一部分行建立索引，大幅减少索引大小。

sql

-- 场景1：只索引活跃用户

CREATE INDEX idx_active_users_email ON users(email)

WHERE status='active';

-- 查询会自动使用部分索引

SELECT * FROM users WHERE status='active' AND email ='zhang@example.com';

-- 场景2：只索引未完成的订单

CREATE INDEX idx_pending_orders ON orders(created_at)

WHERE status='pending';

-- 场景3：排除NULL值

CREATE INDEX idx_users_phone ON users(phone)

WHERE phone IS NOT NULL;

部分索引的优势：

• 索引更小，节省磁盘空间

• 更新开销更小

• 查询更快（扫描更少条目）

3.2 表达式索引——对函数结果建索引

表达式索引对函数或表达式的结果建立索引，解决函数包裹列导致无法使用索引的问题。

sql

-- 问题：以下查询无法使用普通索引

SELECT * FROM users WHERE lower(email)='zhang@example.com';

-- 即使有 idx_users_email，也不会使用

-- 解决方案：表达式索引

CREATE INDEX idx_users_email_lower ON users(lower(email));

-- 现在可以使用索引了

SELECT * FROM users WHERE lower(email)='zhang@example.com';

-- 其他示例

-- 日期截断

CREATE INDEX idx_orders_date ON orders(date(created_at));

SELECT * FROM orders WHERE date(created_at)='2026-04-13';

-- 计算字段

CREATE TABLE products (price numeric, discount numeric);

CREATE INDEX idx_products_final_price ON products((price *(1- discount)));

SELECT * FROM products WHERE price *(1- discount)<100;

3.3 覆盖索引——索引即数据

覆盖索引（Covering Index）通过INCLUDE
子句将非索引列附加到索引中，查询时无需回表。

sql

-- 普通索引：索引只存email，查询name需要回表

CREATE INDEX idx_users_email ON users(email);

SELECT email, name FROM users WHERE email ='zhang@example.com';

-- 执行：Index Scan → 回表获取name

-- 覆盖索引：索引同时存储email和name

CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (name);

SELECT email, name FROM users WHERE email ='zhang@example.com';

-- 执行：Index Only Scan，无需回表！

-- 多列覆盖

CREATE INDEX idx_orders_user_time_covering ON orders(user_id, created_at) INCLUDE (amount,status);

覆盖索引的限制：

• INCLUDE
  列不参与索引排序和条件过滤

• 会增加索引大小

• 适用于高频查询的特定列

3.4 索引合并——多个索引协作

PostgreSQL可以在一次查询中使用多个索引，通过位图扫描（Bitmap Scan）合并结果。

sql

-- 两个单列索引

CREATE INDEX idx_users_age ON users(age);

CREATE INDEX idx_users_status ON users(status);

-- 查询会使用两个索引的位图合并

SELECT * FROM users WHERE age =25 AND status = 'active';

-- 执行计划示例：

-- Bitmap Heap Scan

--   BitmapAnd

--     Bitmap Index Scan on idx_users_age

--     Bitmap Index Scan on idx_users_status

何时索引合并有效：

• 多个条件的选择性都较高

• 复合索引无法满足所有查询组合

• 写多读少的场景（维护多个小索引成本低）

【第四部分】查询计划分析

4.1 EXPLAIN基础

EXPLAIN
显示PostgreSQL如何执行查询，是索引优化的核心工具。

sql

-- 基础用法：显示执行计划

EXPLAIN SELECT * FROM users WHERE age =25;

-- 执行计划 + 实际执行时间

EXPLAIN ANALYZE SELECT * FROM users WHERE age =25;

-- 更详细：显示缓冲区使用情况

EXPLAIN(ANALYZE, BUFFERS) SELECT * FROM users WHERE age =25;

-- 最详细：显示所有信息

EXPLAIN(ANALYZE, BUFFERS, VERBOSE, TIMING) SELECT * FROM users WHERE age =25;

4.2 执行计划关键指标

sql

-- 示例输出

Seq Scan on users  (cost=0.00..1000.00rows=10000 width=100)(actual time=0.012..5.234rows=10000 loops=1)  Buffers: shared hit=10read=90Planning Time: 0.123 msExecution Time: 6.789 ms

4.3 常见的扫描类型

4.4 判断索引是否生效

sql

-- 1. 查看是否使用了索引

EXPLAIN SELECT * FROM users WHERE email ='test@example.com';

-- ✅ Index Scan using idx_users_email

-- ❌ Seq Scan（索引未生效）

-- 2. 检查索引条件

-- ❌ 函数包裹列（需要表达式索引）

EXPLAIN SELECT * FROM users WHERE lower(email)='test@example.com';

-- ❌ 隐式类型转换

EXPLAIN SELECT * FROM users WHERE phone =13800000000;

-- phone是text类型

-- ✅ 正确写法

EXPLAIN SELECT * FROM users WHERE phone ='13800000000';

-- 3. 复合索引前导列检查

CREATE INDEX idx_users_age_name ON users(age, name);

-- ✅ 使用索引

EXPLAIN SELECT * FROM users WHERE age =25;

-- ❌ 不使用索引

EXPLAIN SELECT * FROM users WHERE name ='张三';

4.5 强制索引使用（谨慎使用）

通常情况下，PostgreSQL的优化器会选择正确的执行计划。但必要时可以干预：

sql

-- 临时禁用顺序扫描（测试用）

SET enable_seqscan =off;

SELECT * FROM users WHERE age =25;SET enable_seqscan =on;

-- 恢复-- 使用索引提示（需要扩展）

CREATE EXTENSION pg_hint_plan;

/*+ IndexScan(users idx_users_age) */

SELECT * FROM users WHERE age =25;

【第五部分】索引维护与最佳实践

5.1 索引膨胀与重建

索引随着DML操作会产生膨胀（死元组占用空间），需要定期维护。

sql

-- 查看索引大小

SELECT

    indexname,

    pg_size_pretty(pg_indexes_size(indexname::regclass)) as size

FROM pg_indexes WHERE tablename ='users';

-- 查看索引使用率

SELECT

    schemaname,

    tablename,

    indexname,

    idx_scan,

-- 索引扫描次数

    idx_tup_read,

-- 索引返回行数

    idx_tup_fetch

      -- 索引获取行数（回表）

FROM pg_stat_user_indexes ORDER BY idx_scan ASC;

-- 从未使用的索引排在最前

-- 重建索引（在线，不锁表）

REINDEX INDEX CONCURRENTLY idx_users_email;

-- 重建表的所有索引

REINDEX TABLE CONCURRENTLY users;

-- 重建整个数据库的索引

REINDEX DATABASE CONCURRENTLY mydb;

5.2 统计信息与自动清理

PostgreSQL依赖统计信息选择执行计划。

sql

-- 手动更新统计信息

ANALYZE users;

-- 查看表统计信息

SELECT

    relname,

    reltuples,

-- 预估行数

    relpages,

-- 预估页数

    n_live_tup,

-- 活跃行数（来自autovacuum）

    n_dead_tup

           -- 死行数

FROM pg_class JOIN pg_stat_user_tables ON pg_class.relname = pg_stat_user_tables.relname WHERE relname ='users';

-- 调整统计信息精度

ALTER TABLE users ALTER COLUMN age SET STATISTICS 1000;

-- 默认100

ANALYZE users;

5.3 索引选型决策矩阵

5.4 索引创建原则

DO（推荐）：

• ✅ 为WHERE
  、JOIN
  、ORDER BY
  中的列创建索引

• ✅ 为高选择性列创建索引（区分度高的列）

• ✅ 为频繁查询的组合列创建复合索引

• ✅ 为JSONB、数组等使用GIN索引

• ✅ 为时间序列表考虑BRIN索引

• ✅ 定期分析未使用的索引并删除

DON'T（避免）：

• ❌ 在小表（<1000行）上创建索引（全表扫描更快）

• ❌ 为低选择性列单独建索引（如性别）

• ❌ 为频繁更新的列建过多索引（写放大）

• ❌ 创建从未使用的索引（浪费空间和写开销）

• ❌ 索引顺序与查询条件顺序不匹配（复合索引）

5.5 生产环境索引管理

sql

-- 1. 查找重复索引

SELECT

    schemaname,

    tablename,

    indexname,

    indexdef

FROM pg_indexes

WHERE indexdef IN(SELECT indexdef FROM pg_indexes GROUP BY indexdef HAVING COUNT(*)>1);

-- 2. 查找未使用的索引（超过30天未扫描）

SELECT

    schemaname,

    tablename,

    indexname,

    idx_scan,

    pg_size_pretty(pg_indexes_size(indexname::regclass)) as size

FROM pg_stat_user_indexes

WHERE idx_scan =0 AND indexname NOT LIKE'%pkey'

-- 保留主键

ORDER BY pg_indexes_size(indexname::regclass)DESC;

-- 3. 查找低效索引（扫描多但返回少）

SELECT

    indexname,

    idx_scan,

    idx_tup_fetch,CASE WHEN idx_scan >0 THEN round(100.0* idx_tup_fetch / idx_scan,2) ELSE 0 END as efficiency

FROM pg_stat_user_indexes

WHERE idx_scan >100 ORDER BY efficiency ASC LIMIT 10;

【第六部分】本章总结

6.1 核心知识点速查

6.2 性能调优口诀

等值范围B-tree，JSON数组用GIN
地理范围GiST好，时序大表BRIN省
活跃子集部分建，函数包裹表达式
高频回表覆盖解，复合索引左前缀
定期清理未用者，生产切忌索引多

6.3 实战练习

练习1：分析现有索引

sql

-- 创建测试表并插入100万条数据

CREATE TABLE test_users (

    id serial primary key,

    username text,

    email text,

    age integer,statustext,

    created_at timestamptz defaultnow());

-- 插入100万条数据（使用generate_series）

INSERT INTO test_users (username, email, age,status)

SELECT 'user'|| i,'user'|| i ||'@example.com',(random()*100)::int,CASE WHEN random()>0.5 THEN 'active' ELSE 'inactive' END

FROM generate_series(1,1000000) i;

-- 任务：

-- 1. 分析以下查询的执行计划，判断是否需要索引

-- 2. 创建合适的索引

-- 3. 对比优化前后的性能

-- 查询1：按邮箱精确查找

SELECT * FROM test_users WHERE email ='user50000@example.com';

-- 查询2：按年龄范围查找

SELECT * FROM test_users WHERE age BETWEEN 20 AND 30;

-- 查询3：查找活跃用户中年龄小于25的

SELECT * FROM test_users WHERE status= 'active' AND age <25;

-- 查询4：按创建日期查找最近7天的用户

SELECT * FROM test_users WHERE created_at >now()-interval '7 days';

-- 查询5：模糊搜索用户名包含'5000'的用户

SELECT * FROM test_users WHERE username LIKE'%5000%';

练习2：索引效果对比

sql

-- 创建BRIN和B-tree索引对比

CREATE TABLE logs (

    id bigserial,

    log_time timestamptz defaultnow(),

    message text);

-- 插入500万条有序数据

INSERT INTO logs (log_time, message)

SELECT now()-(i ||' seconds')::interval,'log message '|| i

FROM generate_series(1,5000000) i;

-- 任务：

-- 1. 分别创建B-tree和BRIN索引

-- 2. 对比索引大小

-- 3. 对比时间范围查询的性能

-- 4. 理解BRIN的适用场景

练习3：复合索引顺序

sql

-- 电商订单表

CREATE TABLE orders (

    id serial primary key,

    user_id integer,

    order_date date,

    amount numeric(10,2),statustext);

-- 插入100万条数据后

-- 任务：为以下查询设计最优的复合索引

-- 查询A：查找某用户某时间段的订单

-- SELECT * FROM orders WHERE user_id = 123 AND order_date BETWEEN '2026-01-01' AND '2026-01-31';

-- 查询B：查找某用户的所有订单，按时间排序

-- SELECT * FROM orders WHERE user_id = 123 ORDER BY order_date DESC;

-- 查询C：查找某时间段内某状态的订单

-- SELECT * FROM orders WHERE order_date = '2026-04-13' AND status = 'pending';

-- 思考：如何用最少的索引覆盖最多的查询？

6.4 下期预告

第4期：高级查询技巧——CTE、窗口函数与递归

我们将深入PostgreSQL的查询利器：

• CTE（公共表表达式）：使复杂查询模块化、可读

• 递归CTE：处理树形结构、图遍历

• 窗口函数深入：排名、移动平均、累积和、分组统计

• 高级聚合：FILTER子句、有序集聚合

• LATERAL连接：子查询引用前面的表

• DISTINCT ON：每个分组取第一条

这些技巧将让你的SQL能力从“会用”提升到“精通”。

下期见！

本文为学习笔记，内容基于PostgreSQL 17官方文档及社区最佳实践提炼总结。