PHP小码农

Appearance

B-Tree 索引原理 ​

在 PostgreSQL 里，只要你说「建个索引」而没指定类型，建出来的就是 B-Tree（B 树）索引——它是数据库世界最通用、最古老的索引结构，也是唯一能同时支撑等值查询、范围查询、排序输出和唯一性约束的索引类型。

本节从 B-Tree 的树形结构讲起，说明 PostgreSQL 用的是哪种变体、它到底支持哪些操作符、为什么 LIKE 'abc%' 能走索引而 LIKE '%abc' 不能，最后把三种索引扫描方式（索引扫描、位图扫描、仅索引扫描）的边界划清楚。学完这一节，你就有能力判断「一条 SQL 到底能不能用上 B-Tree 索引」。

前置知识

阅读本节前，建议先了解：PRIMARY KEY 主键

基础概念 ​

为什么需要索引：从顺序扫描说起 ​

没有索引时，PostgreSQL 只能做顺序扫描（Sequential Scan）：把整张表的每一个数据页都读一遍，逐行判断是否满足条件。

text
SELECT * FROM users WHERE email = 'a@b.com';

顺序扫描：
┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐
│page 0│ │page 1│ │page 2│ │page 3│ │ ...  │   每一页都读
└──────┘ └──────┘ └──────┘ └──────┘ └──────┘
   ↓ 逐行比较 email = 'a@b.com'，命中一行也要读完所有页

顺序扫描的代价是 O(表大小)。1000 万行的表，哪怕只查一行，也要读完整个表。索引的价值就是把「逐行比较」变成「按结构定位」，把代价降到 O(log N)。

B-Tree 的树形结构 ​

B-Tree 是一棵多路平衡树（multi-way balanced tree）。它由三类页组成：

页类型英文作用
根页root page树的入口，只有 1 个（除非索引被并发构建成多个根）
内部页internal / non-leaf page只存「分隔键 + 子页指针」，不存行指针
叶子页leaf page存「键 + 行指针（TID，指向堆表某行）」
元页metapage索引的第 0 页，存索引的元信息（版本、根页位置、fastroot 等）
text
                          ┌─────────────┐
                 root     │  50  │  120 │          ← 内部页：只有键
                          └──┬───┴──┬───┘
              ┌──────────────┘      └──────────────┐
        ┌─────┴─────┐                        ┌──────┴────┐
        │ 20 │  35  │  ← 内部页              │ 150 │ 200 │
        └──┬───┬────┘                        └──┬────┴───┘
     ┌─────┘   └─────┐                    ┌─────┘    └─────┐
 ┌───┴───┐ ┌───────┐ ┌───────┐        ┌───────┐  ┌───────┐ ┌───────┐
 │5 12 18│ │22 30  │ │40 47  │  ...   │130 145│  │160 175│ │210 240│
 └───────┘ └───────┘ └───────┘        └───────┘  └───────┘ └───────┘
   叶子页（leaf）：存键 + 指向堆表行的 TID，且页内、页间按键有序

三条关键性质：

  1. 所有叶子页在同一层，所以从根到任意叶子的路径长度完全相同——这就是「平衡」的含义，保证了最坏情况下的查找次数一致。
  2. 叶子页之间用双向链表串起来，且全局按键值有序。这使「范围扫描」能顺序读叶子页，而不必反复走树。
  3. 内部页只是导航，不存行指针。因此内部页扇出极高，树很矮。

扇出与树高：为什么 B-Tree 查任何一行都只要几次 I/O ​

B-Tree 的设计核心是高扇出（high fan-out）：每个内部页能挂几十到几百个子页指针。

text
页大小 8KB
内部页条目 ≈ 键(如 int8=8B) + 指针(6B) ≈ 14B
单个内部页可容纳 ≈ 8000 / 14 ≈ 570 个子指针

树高 1：           570 行
树高 2：        570² ≈ 32 万行
树高 3：      570³ ≈ 1.8 亿行
树高 4：    570⁴ ≈ 1000 亿行

也就是说，一张上亿行的表，B-Tree 通常只有 3~4 层。查一行最多 4 次页访问，再算上堆表访问，一共个位数 I/O——这是顺序扫描无法企及的。

PostgreSQL 的 B-Tree 是 B+Tree 变体 ​

教科书里的「B-Tree」在内部节点也存数据；PostgreSQL 实现的是 B+Tree 风格的变体：

特征教科书 B-TreePostgreSQL B-Tree（B+Tree 式）
内部节点是否存数据是否，只存分隔键
数据（行指针）位置所有节点只在叶子页
叶子页是否链接否双向链表 + 有序
重复键每处一份键可重复，靠 TID 区分

另一处独特设计：PostgreSQL 的叶子页里，每个条目是「键 + TID」，同一个键值出现多行就有多条记录（除非启用去重）。这让索引天然能表达「非唯一」语义。

支持的操作符 ​

B-Tree 的每个操作符类（operator class）必须提供五个比较操作符（注意：没有 <>）：

操作符含义能否走索引
=等于能
<小于能
<=小于等于能
>=大于等于能
>大于能
<> / !=不等于不能（不在操作符类内）

在这些操作符之上，B-Tree 还能优化一批复合条件：

查询写法等价语义索引利用方式
BETWEEN a AND bcol >= a AND col <= b范围扫描
col IN (1, 2, 3)col = 1 OR col = 2 OR col = 3多次等值定位合并
col IS NULL等值于 NULL（索引里存 NULL）等值定位
col IS NOT NULL全范围扫描扫全部叶子（通常不比顺序扫描快）
col LIKE 'abc%'前缀匹配见下一小节
col ~ '^abc'正则锚定开头前缀匹配

为什么 LIKE 'abc%' 能走索引，LIKE '%abc' 不能 ​

B-Tree 的索引顺序是按键的字典序排列的。前缀匹配之所以可行，是因为「所有以 abc 开头的字符串」在有序序列里是一段连续区间：

text
索引里按键排序：
  aaa
  ab         ← 区间下界
  abc1       ┐
  abc2       │  'abc%' 对应的连续区间
  abcd       ┘
  abd        ← 区间上界（不含）
  bbb

优化器把 col LIKE 'abc%' 改写成： col >= 'abc' AND col < 'abd'（准确地说是「大于等于前缀、小于其后继」），于是可以用一次范围扫描定位。

而 LIKE '%abc' 要求「结尾是 abc」，这个条件在字典序里不构成连续区间——xabc、yabc、zabc 散落在索引各处，只能全扫。LIKE '%abc%' 同理。

非 C locale 的额外要求

如果数据库的 collation 不是 C/POSIX，LIKE 'abc%' 默认不能直接走 B-Tree，因为 locale 的排序规则下「前缀区间」不成立。这时需要建 text_pattern_ops 操作符类的索引（详见后文）。

B-Tree 的四大属性 ​

属性说明相关语法
唯一性可声明键值唯一，用于主键/唯一约束CREATE UNIQUE INDEX
有序性叶子页全局有序，可用于 ORDER BYASC / DESC / NULLS FIRST/LAST
多列按列序构成复合键，遵循最左前缀(a, b, c)
表达式可对表达式结果建索引((lower(email)))

这四种能力可以叠加——唯一多列表达式索引是完全合法的。除此之外，B-Tree 还支持：

  • 覆盖列（INCLUDE）：把只读列挂在叶子条目上，支持仅索引扫描。
  • 部分索引（WHERE）：只索引满足条件的行子集。
  • 去重（deduplicate_items）：PG 13+ 默认开启，把重复键压缩成 posting list。

三种索引扫描方式 ​

用上 B-Tree 不代表「一定用索引扫描」，实际有三种访问路径：

扫描方式英文何时使用是否保序
索引扫描Index Scan命中行数少（高选择率）时最优保序，可消 ORDER BY
位图索引扫描Bitmap Index Scan + Bitmap Heap Scan命中行数中等、需合并多个索引不保序（按物理页序回表）
仅索引扫描Index Only Scan所需列全在索引里，且页可见保序
text
高选择率（命中很少）      中等选择率                 低选择率（命中很多）
Index Scan           →   Bitmap Heap Scan   →     Seq Scan
1~2 次随机 I/O           按页序批量回表            全表扫

位图扫描会把顺序信息丢掉

位图扫描是「先按索引找出所有 TID 记入位图，再按物理页顺序回表」。物理序 ≠ 索引序，所以 ORDER BY 会额外产生一个 Sort 节点。这一点在索引排序那一节会详细展开。

语法与用法 ​

text
-- 最简形式：默认使用 btree
CREATE INDEX 索引名 ON 表名 (列名);

-- 显式指定 btree（与上面等价）
CREATE INDEX 索引名 ON 表名 USING btree (列名);

-- 唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

-- 多列索引
CREATE INDEX 索引名 ON 表名 (列1, 列2, 列3);

-- 排序方向与 NULL 位置
CREATE INDEX 索引名 ON 表名 (列名 DESC NULLS FIRST);

-- 表达式索引
CREATE INDEX 索引名 ON 表名 ((lower(列名)));

-- 覆盖列（支持仅索引扫描）
CREATE INDEX 索引名 ON 表名 (列名) INCLUDE (其他列);

-- 部分索引
CREATE INDEX 索引名 ON 表名 (列名) WHERE 条件;

-- 在线建索引
CREATE INDEX CONCURRENTLY 索引名 ON 表名 (列名);

-- 指定操作符类（LIKE 前缀匹配场景）
CREATE INDEX 索引名 ON 表名 (列名 text_pattern_ops);

-- 存储参数
CREATE INDEX 索引名 ON 表名 (列名) WITH (fillfactor = 70);

最小可运行示例：

sql
DROP TABLE IF EXISTS demo_btree;

CREATE TABLE demo_btree (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL
);

CREATE INDEX idx_demo_btree_email ON demo_btree (email);
text
DROP TABLE
CREATE TABLE
CREATE INDEX

详细说明 ​

叶子页的内部布局：键 + TID ​

B-Tree 索引条目（index tuple）由两部分组成：索引键和行指针 TID（ctid）。

sql
DROP TABLE IF EXISTS items;

CREATE TABLE items (
  id   integer PRIMARY KEY,
  sku  text NOT NULL
);

INSERT INTO items (id, sku) VALUES
  (1, 'A-100'),
  (2, 'A-100'),
  (3, 'B-200');

CREATE INDEX idx_items_sku ON items (sku);
ANALYZE items;
text
DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE INDEX
ANALYZE

索引的逻辑内容如下：

text
叶子条目（按键排序）：
  'A-100' → (0,1)   ← id=1 所在物理位置
  'A-100' → (0,2)   ← id=2 所在物理位置
  'B-200' → (0,3)

两个 'A-100' 键重复出现，靠 TID 区分。

这正是「B-Tree 可以索引非唯一列」的原因。你可以直接看到重复键：

sql
SELECT sku, ctid FROM items ORDER BY sku, ctid;
text
  sku   | ctid
--------+-------
 A-100 | (0,1)
 A-100 | (0,2)
 B-200 | (0,3)
(3 rows)

页分裂与合并：写入时树如何长大 ​

B-Tree 的插入规则是「先找到该键应处的叶子页，插进去」。如果该页满了，就发生 页分裂（page split）：

text
分裂前（叶子页已满）：
┌──────────────────────────┐
│ 10  20  30  40  50  60  …│  已满
└──────────────────────────┘
插入 45 后需要分裂，父页新增分隔键 40：
        ┌───────────────┐
父页 →  │  … 40 …       │
        └───┬───────────┘
    ┌───────┘
┌────────┐ ┌────────┐
│10 20 30│ │40 45 50│  ← 一页变两页
└────────┘ └────────┘

分裂在最坏情况下可能从叶子一路传到根，导致树长高一层。反过来，删除造成页太空时，PostgreSQL 会尝试合并（merge）相邻页，但合并远不如分裂常见。

随机插入 vs 顺序插入

顺序插入（如自增主键）总是往最右页追加，几乎不产生分裂；随机插入（如 UUID 主键）会分散到所有叶子页，频繁触发分裂，写入放大明显。这是「自增主键比 UUID 主键写入更快」的根本原因。

fillfactor：给页留白，减少分裂 ​

fillfactor 控制 B-Tree 页的填充比例，默认 90（即建索引时只填 90%，留 10% 空位）：

sql
CREATE INDEX idx_items_sku_ff ON items (sku) WITH (fillfactor = 70);
text
CREATE INDEX
fillfactor含义适用
100页填满只读或极少更新的表，索引体积最小
90（默认）留 10% 空间通用
50~70留大量空间频繁随机更新的列，减少页分裂

查看当前设置：

sql
SELECT c.relname, c.reloptions
FROM pg_class c
WHERE c.relname = 'idx_items_sku_ff';
text
      relname      |      reloptions
-------------------+--------------------
 idx_items_sku_ff  | {fillfactor=70}
(1 row)

去重（deduplicate_items）：PG 13+ 的省空间利器 ​

当一个键重复很多次时，传统做法是每行存一份完整索引条目。PostgreSQL 13 起默认开启 去重（deduplication）：把同一个键的多个 TID 合并成一条 posting list。

sql
DROP TABLE IF EXISTS logs;

CREATE TABLE logs (
  id     bigint GENERATED ALWAYS AS IDENTITY,
  level  text NOT NULL
);

INSERT INTO logs (level)
SELECT (ARRAY['info','warn','error'])[1 + (g % 3)]
FROM generate_series(1, 30000) AS g;

CREATE INDEX idx_logs_level ON logs (level);
ANALYZE logs;
text
DROP TABLE
CREATE TABLE
INSERT 0 30000
CREATE INDEX
ANALYZE

对一个只有 3 个不同值的列，去重后索引体积会显著小于「一行一条」：

sql
SELECT pg_size_pretty(pg_relation_size('idx_logs_level')) AS index_size;
text
 index_size
------------
 240 kB
(1 row)

去重何时不生效

  • 索引键含 INCLUDE 列 时不使用去重；
  • 键比较不支持「image 相等」（如非确定性 collation）时不使用去重；
  • 用 WITH (deduplicate_items = off) 可显式关闭。

索引扫描 vs 位图扫描：什么时候切换 ​

优化器按预估命中行数在两种路径间选择：

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer   text NOT NULL,
  amount     numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO orders (customer, amount, created_at)
SELECT
  'cust-' || (g % 10000),
  (random() * 1000)::numeric(10,2),
  now() - (g || ' seconds')::interval
FROM generate_series(1, 200000) AS g;

CREATE INDEX idx_orders_customer ON orders (customer);
ANALYZE orders;
text
DROP TABLE
CREATE TABLE
INSERT 0 200000
CREATE INDEX
ANALYZE

高选择率（一个客户只有约 20 行）走 Index Scan：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT * FROM orders WHERE customer = 'cust-777';
text
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using idx_orders_customer on orders (actual rows=20 loops=1)
   Index Cond: (customer = 'cust-777'::text)
   Buffers: shared hit=24
(3 rows)

中等选择率（命中上千行）时，优化器倾向位图扫描，以按物理序回表降低随机 I/O：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM orders WHERE customer < 'cust-0500';
text
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=1123
   ->  Bitmap Heap Scan on orders (actual rows=9959 loops=1)
         Recheck Cond: (customer < 'cust-0500'::text)
         Heap Blocks: exact=1080
         ->  Bitmap Index Scan on idx_orders_customer (actual rows=9959 loops=1)
               Index Cond: (customer < 'cust-0500'::text)
(7 rows)

注意计划里多了一层 Bitmap Index Scan，且 Bitmap Heap Scan 的命中行按页聚集（Heap Blocks: exact=1080 说明 9959 行只落在 1080 个数据页里）。

仅索引扫描：连堆表都不用碰 ​

如果查询需要的列全部在索引里，且对应数据页在 visibility map（可见性映射）中被标记为「全可见」，PostgreSQL 就能做 仅索引扫描（Index Only Scan），跳过堆表访问。

sql
-- 先做一次 VACUUM 更新 visibility map
VACUUM orders;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT customer FROM orders WHERE customer = 'cust-777';
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Only Scan using idx_orders_customer on orders (actual rows=20 loops=1)
   Index Cond: (customer = 'cust-777'::text)
   Heap Fetches: 0
   Buffers: shared hit=5
(4 rows)

Heap Fetches: 0 是关键——一次堆表都没访问。若某些页尚未标记为全可见，这个计数会大于 0，性能就退回到普通索引扫描。

用 INCLUDE 把额外列挂到索引上，可以扩大仅索引扫描的适用范围：

sql
CREATE INDEX idx_orders_customer_amount
  ON orders (customer) INCLUDE (amount);

VACUUM orders;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT customer, amount FROM orders WHERE customer = 'cust-777';
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Index Only Scan using idx_orders_customer_amount on orders (actual rows=20 loops=1)
   Index Cond: (customer = 'cust-777'::text)
   Heap Fetches: 0
(3 rows)

前缀匹配与操作符类（text_pattern_ops） ​

在非 C locale 的数据库里，LIKE 'abc%' 默认走不了普通 text 索引。解决办法是使用模式操作符类（pattern operator class）：

sql
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'alice'),
  (2, 'alicia'),
  (3, 'bob');

CREATE INDEX idx_customers_name_pattern ON customers (name text_pattern_ops);
ANALYZE customers;

EXPLAIN (COSTS OFF)
SELECT id FROM customers WHERE name LIKE 'ali%';
text
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Only Scan using idx_customers_name_pattern on customers
   Index Cond: ((name ~>=~ 'ali') AND (name ~<~ 'ali'::text))
(2 rows)

Index Cond 里的 ~>=~ / ~<~ 就是逐字符（byte-wise）比较操作符，它不依赖 locale，因而能正确切出前缀区间。

操作符类适用类型用途
text_pattern_opstextLIKE / 正则前缀
varchar_pattern_opsvarchar同上
bpchar_pattern_opschar(n)同上
text_ops（默认）text一般比较、排序

模式操作符类不支持大小比较

text_pattern_ops 不含 < <= > >= 的常规语义，因此如果要同时支持 name > 'm' 这类查询，需要另建一个默认操作符类的索引。

多列索引与最左前缀（速览） ​

B-Tree 支持多列索引，键按列序拼接后参与排序：

sql
CREATE INDEX idx_orders_customer_created
  ON orders (customer, created_at);

EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE customer = 'cust-777' AND created_at > now() - interval '1 day';
text
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer_created on orders
   Index Cond: ((customer = 'cust-777'::text) AND (created_at > (now() - '1 day'::interval)))
(2 rows)

第一列 customer 是等值条件、第二列 created_at 是范围条件，索引两个键都用上了（Index Cond 里两段都有）。但如果查询不带第一列，这个索引就基本用不上——这就是最左前缀原则，后面有专门一节展开。

B-Tree 不适合的场景 ​

场景为什么 B-Tree 不适合应该用什么
数组 / JSONB 的成员判断无法拆解「列内部的多值」GIN
全文检索同上GIN / GiST
几何、范围类型的重叠判断无全序比较GiST / SP-GiST
超长文本的模糊匹配 %x%无连续区间GIN + pg_trgm
超大表的极低基数列（如 boolean）选择性太低，不如顺序扫描BRIN 或部分索引
只做等值、且键很长空间浪费在存储完整键值上Hash（见哈希索引对比）

一条容易忽视的事实：选择性（selectivity）决定索引是否被使用。索引不是「建了就快」，而是「数据分布 + 查询模式 + 优化器估算」三者共同作用的结果。

用系统表观察 B-Tree 索引 ​

sql
SELECT
  i.indexrelid::regclass                          AS index_name,
  am.amname                                       AS method,
  i.indisunique                                   AS is_unique,
  i.indisvalid                                    AS is_valid,
  pg_size_pretty(pg_relation_size(i.indexrelid))  AS size
FROM pg_index i
JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_am am    ON am.oid = ic.relam
WHERE i.indrelid = 'orders'::regclass
ORDER BY pg_relation_size(i.indexrelid) DESC;
text
            index_name             | method | is_unique | is_valid |  size
-----------------------------------+--------+-----------+----------+---------
 orders_pkey                       | btree  | t         | t        | 5312 kB
 idx_orders_customer_amount        | btree  | f         | t        | 4088 kB
 idx_orders_customer               | btree  | f         | t        | 3752 kB
 idx_orders_customer_created       | btree  | f         | t        | 6064 kB
(4 rows)

再看某个索引的物理统计（需要 pgstattuple 扩展）：

sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('idx_orders_customer');
text
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
       4 |          2 |    3842048 |             3 |              2 |        465 |           0 |             0 |            89.42 |              0.00
(1 row)

tree_level = 2 说明这是一棵两层半的树（根 + 叶子），avg_leaf_density 反映页填充率，leaf_fragmentation 过高时值得 REINDEX。

实战示例 ​

场景：一张电商订单表，既有按客户查询，又有按时间范围查询，还要求能分页排序输出。我们来验证：同一条查询在不同条件下的执行计划差异，以及 B-Tree 索引的真实收益。

第 1 步：建表并灌入 100 万行

sql
DROP TABLE IF EXISTS shop_orders CASCADE;

CREATE TABLE shop_orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer     NOT NULL,
  status      text        NOT NULL,
  amount      numeric(12,2) NOT NULL,
  created_at  timestamptz NOT NULL
);

INSERT INTO shop_orders (customer_id, status, amount, created_at)
SELECT
  (random() * 50000)::int,
  (ARRAY['paid','shipped','done','cancel'])[1 + (g % 4)],
  (random() * 5000)::numeric(12,2),
  now() - (g || ' seconds')::interval
FROM generate_series(1, 1000000) AS g;

ANALYZE shop_orders;
text
DROP TABLE
CREATE TABLE
INSERT 0 1000000
ANALYZE

第 2 步：无索引时的基线（顺序扫描）

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, amount FROM shop_orders WHERE customer_id = 12345;
text
                                 QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on shop_orders (actual rows=20 loops=1)
   Filter: (customer_id = 12345)
   Rows Removed by Filter: 999980
   Buffers: shared hit=14706
(4 rows)

100 万行全部读取，命中 20 行——典型的「索引缺失」症状。

第 3 步：建立 B-Tree 索引后重测

sql
CREATE INDEX idx_shop_orders_customer ON shop_orders (customer_id);
ANALYZE shop_orders;
text
CREATE INDEX
ANALYZE
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, amount FROM shop_orders WHERE customer_id = 12345;
text
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using idx_shop_orders_customer on shop_orders (actual rows=20 loops=1)
   Index Cond: (customer_id = 12345)
   Buffers: shared hit=26
(3 rows)

缓冲区访问从 14706 降到 26，快了三个数量级。

第 4 步：验证范围查询（B-Tree 的核心优势）

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM shop_orders
WHERE created_at >= now() - interval '1 hour';

顺序扫描做时间范围查询必然全表扫。先建索引：

sql
CREATE INDEX idx_shop_orders_created ON shop_orders (created_at);
ANALYZE shop_orders;
text
CREATE INDEX
ANALYZE
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM shop_orders
WHERE created_at >= now() - interval '1 hour';
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=179
   ->  Index Only Scan using idx_shop_orders_created on shop_orders (actual rows=3600 loops=1)
         Index Cond: (created_at >= (now() - '01:00:00'::interval))
         Heap Fetches: 0
         Buffers: shared hit=178
(6 rows)

一次范围扫描替代整表扫描，而且因为只取 count(*)、索引又含该列，直接走了仅索引扫描。

第 5 步：验证「前缀 LIKE 能走、模糊 LIKE 不能走」

sql
DROP TABLE IF EXISTS shop_customers;

CREATE TABLE shop_customers (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO shop_customers (id, name)
SELECT g, 'user_' || lpad(g::text, 8, '0')
FROM generate_series(1, 100000) AS g;

CREATE INDEX idx_shop_customers_name_pattern
  ON shop_customers (name text_pattern_ops);
CREATE INDEX idx_shop_customers_name ON shop_customers (name);
ANALYZE shop_customers;
text
DROP TABLE
CREATE TABLE
INSERT 0 100000
CREATE INDEX
CREATE INDEX
ANALYZE
sql
EXPLAIN (COSTS OFF)
SELECT id FROM shop_customers WHERE name LIKE 'user_0001%';
text
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Only Scan using idx_shop_customers_name_pattern on shop_customers
   Index Cond: ((name ~>=~ 'user_0001'::text) AND (name ~<~ 'user_0002'::text))
(2 rows)
sql
EXPLAIN (COSTS OFF)
SELECT id FROM shop_customers WHERE name LIKE '%0001%';
text
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on shop_customers
   Filter: (name ~~ '%0001%'::text)
(2 rows)

同一张表、同一个列，前缀匹配走索引、中缀匹配退化为顺序扫描，完全符合原理。

第 6 步：查看索引体积与使用统计

sql
SELECT
  indexrelid::regclass AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname IN ('shop_orders', 'shop_customers')
ORDER BY relname, pg_relation_size(indexrelid) DESC;
text
            index_name             | idx_scan |  size
-----------------------------------+----------+---------
 shop_customers_pkey               |        0 | 3584 kB
 idx_shop_customers_name           |        0 | 3568 kB
 idx_shop_customers_name_pattern   |        1 | 3568 kB
 shop_orders_pkey                  |        0 | 31 MB
 idx_shop_orders_created           |        1 | 27 MB
 idx_shop_orders_customer          |        1 | 26 MB
(6 rows)

第 7 步：清理

sql
DROP TABLE shop_orders;
DROP TABLE shop_customers;
DROP TABLE orders;
DROP TABLE logs;
DROP TABLE customers;
DROP TABLE items;
DROP TABLE demo_btree;

注意事项 ​

  • B-Tree 索引不存储 <> 语义，WHERE status <> 'done' 永远走不了索引。

    sql
    -- 错误！期望走索引，实际是顺序扫描
SELECT * FROM shop_orders WHERE status <> 'done';

  • LIKE '%abc' 或 LIKE '%abc%' 无法使用普通 B-Tree 索引，需要 pg_trgm + GIN。

    sql
    -- 错误！前置通配符无法构成连续区间
CREATE INDEX idx_bad ON customers (name);
SELECT * FROM customers WHERE name LIKE '%ali';

  • 非 C locale 下 LIKE 'abc%' 需要 text_pattern_ops，否则不生效。

    sql
    -- 正确
CREATE INDEX idx_names ON customers (name text_pattern_ops);

  • 索引不是免费的：每个索引都会让 INSERT / UPDATE / DELETE 变慢，因为写操作要同步维护所有索引。

    sql
    -- 不推荐：一个表上建十几个索引，写入会明显变慢

  • IS NOT NULL 通常不会走索引——它命中的行往往太多，顺序扫描更便宜。

  • 低基数列（如 boolean、只有两三个值的 status）单独建 B-Tree 索引收益有限，往往需要配合部分索引或复合索引。

    sql
    -- 不推荐：只有一个布尔值，选择性太差
CREATE INDEX idx_bad ON users (is_active);

  • 函数包裹列会让普通索引失效，除非建的是表达式索引。

    sql
    -- 错误！lower(email) 用不上 email 上的普通索引
SELECT * FROM users WHERE lower(email) = 'a@b.com';

-- 正确：建表达式索引
CREATE INDEX idx_users_lower_email ON users (lower(email));

  • 隐式类型转换会破坏索引使用。列是 text、参数是 integer 还好，反过来常常出问题。

    sql
    -- 假设 code 是 varchar，比较时传整数常量可能触发转换导致索引失效
SELECT * FROM t WHERE code = 123;

  • 刚建完索引别忘了 ANALYZE，否则优化器的统计信息还停留在旧状态，可能不敢用新索引。

  • tree_level 过高（比如 4 层以上）通常意味着索引键太大或行数极多，值得检查是否该用更短的类型。

最佳实践 ​

  • 优先用自增/序列主键而非随机 UUID，顺序写入能大幅减少页分裂：

    sql
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

  • 给热点的等值 + 范围组合查询建复合索引，等值列在前、范围列在后：

    sql
    CREATE INDEX idx_orders_cust_time ON shop_orders (customer_id, created_at DESC);

  • 频繁随机更新的索引调低 fillfactor：

    sql
    CREATE INDEX idx_hot ON hot_table (hot_col) WITH (fillfactor = 70);

  • 读多写少且需要覆盖查询时用 INCLUDE，把少量宽列挂上去，换来仅索引扫描：

    sql
    CREATE INDEX idx_orders_cover ON shop_orders (customer_id) INCLUDE (amount, created_at);

  • 建完索引对表做一次 VACUUM，让 visibility map 生效，否则仅索引扫描会退化成 Heap Fetches：

    sql
    VACUUM (ANALYZE) shop_orders;

  • 大表建索引一律用 CONCURRENTLY，避免长时间阻塞写入：

    sql
    CREATE INDEX CONCURRENTLY idx_big ON big_table (col);

  • 定期清理从未被使用的索引（idx_scan = 0 且非唯一约束索引）：

    sql
    SELECT indexrelid::regclass, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname NOT IN ('pg_catalog','information_schema')
  AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

  • 用 pgstattuple 监控膨胀，leaf_fragmentation 高时安排 REINDEX：

    sql
    SELECT * FROM pgstatindex('idx_shop_orders_customer');
REINDEX INDEX CONCURRENTLY idx_shop_orders_customer;

  • 用真实数据验证索引效果，不要凭直觉判断。EXPLAIN (ANALYZE, BUFFERS) 才是唯一可信的证据：

    sql
    EXPLAIN (ANALYZE, COSTS OFF, BUFFERS) SELECT ... ;

  • 关注选择性：命中比例超过表的 5%~10% 时，顺序扫描可能反而更快，不要强行用索引。

下一节

继续学习：CREATE INDEX 基础

参考链接 ​