📚 课程目录第1章索引基础

1. 索引基础：B-Tree索引原理、索引类型概述、创建与删除索引语法

1.1 B-Tree 索引核心原理

B-Tree（平衡多路搜索树）是 PostgreSQL 默认且最通用的索引类型。其核心设计目标是：在大量数据中，以极少的磁盘 I/O 次数完成等值查询与范围查询。

  关键指标：一棵高度为 3~4 的 B-Tree 即可支撑数亿行记录，每次索引查找仅需 3~4 次磁盘读取。

B-Tree 索引的内部结构分为三层：

根节点（Root）：索引的入口，存储指向中间节点的指针与键值范围。
内部节点（Internal）：负责路由，每个节点包含多个键值对和子节点指针，键值按升序排列。
叶子节点（Leaf）：包含实际索引键值以及指向对应表行（TID，即页号+偏移量）的指针。叶子节点之间通过双向链表连接，支持高效的范围扫描。

面试高频点：B-Tree 索引为什么快？—— 每个节点存储多个键值（扇出大），树高度低；叶子节点有序且链表相连，范围查询无需回溯。

1.2 索引类型概述

PostgreSQL 提供了丰富的索引类型，以适应不同的查询模式与数据类型。下表对比了最常用的五种索引：

索引类型	适用场景	支持操作符	典型用途
B-Tree	等值查询、范围查询、排序	=, >, <, >=, <=, BETWEEN, IN, LIKE（非前缀）	主键、唯一约束、外键、常规过滤
Hash	等值查询（不支持范围）	=	长字符串精确匹配，如 UUID、MD5
GiST	几何、全文检索、范围重叠	&&, @>, <@, ~=, @@	地理坐标、全文搜索、排除约束
GIN	复合值（数组、JSONB、全文检索）	@>, ? , ?\| , ?&, @@	JSONB 查询、数组包含、全文检索
BRIN	超大表、数据天然有序（如时间序列）	=, >, <, BETWEEN	日志表、时序数据、归档表

避坑提示：Hash 索引在 PostgreSQL 10 之前不支持 WAL 日志，崩溃恢复后需重建；10+ 版本已修复，但仍不支持范围查询与排序。绝大多数场景优先选择 B-Tree。

1.3 创建与删除索引语法

PostgreSQL 的索引 DDL 语法简洁且功能强大。以下为最常用的操作：

1.3.1 创建索引

-- 单列 B-Tree 索引（默认类型）
CREATE INDEX idx_users_email ON users(email);

-- 多列复合索引（B-Tree）
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- 唯一索引（自动创建于主键和唯一约束）
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 部分索引（只索引满足条件的行，节省空间）
CREATE INDEX idx_active_orders ON orders(order_status)
WHERE order_status = 'ACTIVE';

-- 并发创建索引（不阻塞 DML）
CREATE INDEX CONCURRENTLY idx_orders_amount ON orders(amount);

1.3.2 删除索引

-- 删除索引（会阻塞对该表的并发访问）
DROP INDEX idx_users_email;

-- 并发删除索引（允许其他会话继续读写）
DROP INDEX CONCURRENTLY idx_users_email;

  生产建议：在大型生产表上创建或删除索引，务必使用 CONCURRENTLY 选项，避免长时间锁表导致业务中断。注意 CONCURRENTLY 会消耗更多资源且执行时间更长。

1.4 索引创建策略对比

策略	语法示例	优势	适用场景
单列索引	`CREATE INDEX ... ON t(c1);`	简单、维护成本低	单字段过滤
复合索引	`CREATE INDEX ... ON t(c1, c2);`	覆盖多条件查询，避免回表	多字段组合查询
部分索引	`CREATE INDEX ... ON t(c1) WHERE c2 > 0;`	索引体积小，写入性能好	热点数据过滤
唯一索引	`CREATE UNIQUE INDEX ... ON t(c1);`	保证数据唯一性，优化器优先使用	业务唯一约束

1.5 本章小结

B-Tree 索引通过多路平衡树 + 叶子节点双向链表实现高效等值与范围查询。
PostgreSQL 提供 B-Tree、Hash、GiST、GIN、BRIN 等索引类型，B-Tree 是默认且最通用的选择。
创建索引使用 CREATE INDEX，删除使用 DROP INDEX；生产环境推荐 CONCURRENTLY 选项避免锁表。
部分索引和复合索引是优化查询性能与节省存储空间的重要工具。