40. 表达式索引与索引使用注意

先准备好全书统一的示例表 users 和 orders：

postgres=# CREATE TABLE users (
postgres=#   id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
postgres=#   username   TEXT NOT NULL,
postgres=#   email      TEXT,
postgres=#   age        INT,
postgres=#   created_at TIMESTAMP DEFAULT now()
postgres=# );

postgres=# CREATE TABLE orders (
postgres=#   id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
postgres=#   user_id    INT,
postgres=#   amount     NUMERIC(10,2),
postgres=#   status     TEXT,
postgres=#   created_at TIMESTAMP DEFAULT now()
postgres=# );

普通索引的盲区

普通索引建在列上，只对”列本身”的查询有效。如果你查的是列的加工结果，比如不区分大小写地按用户名查：

postgres=# SELECT * FROM users WHERE lower(username) = 'alice';

这条查询用不到 username 上的普通索引，因为索引存的是原始值，没存 lower() 之后的值。数据库只能全表扫描，一行行算 lower()。

表达式索引（函数索引）

解决办法是建”表达式索引”，把函数结果也存进索引：

postgres=# CREATE INDEX idx_users_lower_name ON users (lower(username));

注意：查询里的写法必须和索引里的表达式完全一致，才能命中：

postgres=# EXPLAIN SELECT * FROM users WHERE lower(username) = 'alice';
-- 走 Index Scan，用到 idx_users_lower_name

常见用法还有对日期取”天”：

postgres=# CREATE INDEX idx_orders_created_date ON orders (date_trunc('day', created_at));
postgres=# SELECT * FROM orders
postgres=# WHERE date_trunc('day', created_at) = date '2026-07-31';

也可以对算术表达式建索引，比如按”金额加运费”查：

postgres=# CREATE INDEX idx_orders_total ON orders ((amount + 10));
postgres=# SELECT * FROM orders WHERE amount + 10 > 100;

用表达式索引支持 LIKE

普通 B-tree 索引对前缀 LIKE 'abc%' 其实也能用，但前提是列用的是默认排序规则（collation）。如果你想稳妥地按前缀查，可以显式指定 text_pattern_ops：

postgres=# CREATE INDEX idx_users_name_pattern ON users (username text_pattern_ops);
postgres=# SELECT * FROM users WHERE username LIKE 'ali%';  -- 能走索引

覆盖索引与 INCLUDE（PG 11+）

如果你常查”某几列”，可以建一个覆盖索引，把需要的列一起带进去，做到”只扫索引、不回表”（Index Only Scan）：

postgres=# CREATE INDEX idx_orders_user_status_amt
postgres=# ON orders (user_id, status) INCLUDE (amount);

这样查 user_id、status、amount 时，数据都在索引里，不必再回表取 amount，速度更快。注意 INCLUDE 里的列不参与排序和筛选，只用于返回。

索引的选择性

选择性（Selectivity）指一列里不重复值的比例。手机号、邮箱选择性高，性别、状态这类重复值多的列选择性低。

比如 status 只有 ‘paid’ / ‘unpaid’ / ‘refunded’ 几个值。查 status = 'paid' 可能命中一大半数据，这时走索引未必比全表扫描快，优化器可能直接放弃索引。

索引何时会失效

记住几种常见”索引白建了”的情况：

1. 对列套了函数却没建表达式索引，如 WHERE age + 1 = 30。
2. 用前导模糊匹配 LIKE '%结尾'，开头不确定，B-tree 派不上用场。
3. 类型不匹配，比如字符串列和数字用 = 比较，触发隐式转换，索引失效。
4. 优化器估算走全表更快（小表、命中行数太多）。

postgres=# -- 用不到索引：左边被函数包住
postgres=# SELECT * FROM users WHERE age + 1 = 30;
postgres=# -- 用得到：把运算挪到右边
postgres=# SELECT * FROM users WHERE age = 30 - 1;

避免滥用索引

索引是双刃剑。建之前想清楚三件事：

• 这列真的常在 WHERE、JOIN、ORDER BY 里出现吗？
• 表是读多还是写多？写多的表索引要克制。
• 表本身很小吗？小表全表扫描更快，别加。

用 EXPLAIN 看索引到底命中没有

光看查询写法不够，最好实际验证。加 BUFFERS 能看到读写了多少数据块：

postgres=# EXPLAIN (ANALYZE, BUFFERS)
postgres=# SELECT * FROM users WHERE lower(username) = 'alice';

如果输出里是 Index Scan using idx_users_lower_name，说明表达式索引生效了；是 Seq Scan 说明没生效，得回头检查表达式是否一致。

覆盖索引与 Index Only Scan 的前提

第 39 章讲过用 INCLUDE 做覆盖索引。但要真正”只扫索引不回表”（Index Only Scan），还得靠”可见性映射”（visibility map）。刚写入大量数据后，可见性映射还没更新，PostgreSQL 可能还得回表确认行是否可见，于是退化成普通索引扫描。跑一次 VACUUM 能改善：

postgres=# VACUUM orders;

BRIN 索引：超大时序表的省空间方案

如果数据是按某列”自然有序”写入的（比如按时间递增的日志），普通 B-tree 太占空间。BRIN 索引只记录”每段范围里的最小值和最大值”，体积极小。先准备一张按时间递增写入的日志表：

postgres=# CREATE TABLE logs (
postgres=#   id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
postgres=#   created_at TIMESTAMP
postgres=# );

postgres=# CREATE INDEX idx_logs_time ON logs USING brin (created_at);

一条实用的调优流程

遇到慢查询，我一般按这个顺序走：

1. 用 EXPLAIN ANALYZE 看是不是 Seq Scan。
2. 看清过滤条件落在哪一列、是不是被函数包住。
3. 选择性高的列建普通索引，被函数包住的建表达式索引。
4. 再 EXPLAIN 一次验证，确认走了 Index Scan。
5. 写完别忘评估写入开销，必要时 REINDEX、清理无用索引。

一句话建立索引直觉

把索引想成”书的目录”还差点意思，更准确的比喻是”排过序的抽屉标签”。你想找”年龄为 28 的人”，目录告诉你去第几页；但如果你问”年龄加 1 等于 29 的人”，目录里没这条，只能翻遍全书。所以：查的”形状”要和索引的”形状”一致，索引才用得上。

索引使用的整体建议

总结一句：索引是加速查询的工具，但不是万能药。先想清楚查询长什么样，再决定建普通索引、表达式索引还是部分索引；建完用 EXPLAIN 验证；定期 REINDEX、清理没人用的索引。把这套流程变成习惯，慢查询会少一大半。

常见误区

1. 以为 LIKE '%xx' 能走索引。不行，只有前缀 LIKE 'xx%' 才行。
2. 表达式索引里写了 lower(name)，查询里却写 upper(name)，当然匹配不上。
3. 低选择性列上建大索引，结果优化器根本不用。
4. 索引越多越好，忘了它对写入的拖累。