数据库索引策略深入

数据库慢,90% 是索引问题;索引加对,90% 的慢查询会消失。但"加索引"这件事,远不止 CREATE INDEX idx_x ON t(col) 这一句。会写这一句的人满大街都是,真正能讲清为什么这一棵索引比那一棵快十倍、什么时候该上 GIN、什么时候一个索引都别加——那才是资深工程师的水位线。

一、索引是性能的第一杠杆,但要按查询模式设计

先建立两个基本认知。

第一,索引是拿写换读的交易。每加一个索引,每次 INSERT/UPDATE/DELETE 都要多维护一棵 B-tree。索引不是越多越好,而是越准越好——准,意味着它恰好覆盖了真实的查询模式,不多不少。

第二,索引是按查询设计的,不是按表设计的。不要一上来给每列都建索引。正确的工作流是反过来的:先有慢查询,分析它的 WHERE / ORDER BY / JOIN,再针对性设计一棵能服务它的索引。一棵为查询量身定制的索引,抵得上十个拍脑袋加的单列索引。

判断索引有没有生效,唯一的办法是 EXPLAIN。不看执行计划就调优,等于闭眼开车。下面是一个典型的全表扫描被索引消灭的过程:

-- 1000 万行的 orders 表
EXPLAIN SELECT * FROM orders WHERE user_id = 88123;

-- 没索引时:
-- Seq Scan on orders  (cost=0.00..234511.00 rows=1)
-- 全表扫描,磁盘 IO 爆炸

CREATE INDEX idx_orders_user_id ON orders(user_id);

EXPLAIN SELECT * FROM orders WHERE user_id = 88123;
-- Index Scan using idx_orders_user_id on orders  (cost=0.43..8.45 rows=12)
-- 成本从 23 万降到 8 —— 这就是第一杠杆的力度

二、复合索引与最左前缀

真正复杂的查询很少只过滤一列,这时复合索引(多列索引)登场。最左前缀原则是它的灵魂:

一个 (a, b, c) 的复合索引,可以服务这些查询:

• WHERE a = ? ✅
• WHERE a = ? AND b = ? ✅
• WHERE a = ? AND b = ? AND c = ? ✅
• WHERE b = ? ❌(跳过了 a,用不上)
• WHERE b = ? AND c = ? ❌
• WHERE a = ? AND c = ? ⚠️(只能用到 a 这一段)

为什么?因为 B-tree 是按 a 排序、a 相同再按 b 排序、再按 c 排序组织的。跳过 a 直接查 b,树里 b 是无序的,索引帮不上忙。

列顺序怎么定?记住一条黄金法则——等值 → 范围 → 排序:

-- 典型查询:某用户最近 30 天的订单,按时间倒序
SELECT * FROM orders
WHERE user_id = 88123           -- 等值
  AND created_at > '2026-06-17' -- 范围
ORDER BY created_at DESC;       -- 排序

-- 正确索引:等值列在前,范围列在后,排序列搭便车
CREATE INDEX idx_orders_user_time ON orders(user_id, created_at);

-- Index Scan Backward using idx_orders_user_time on orders
-- 一棵索引同时搞定过滤 + 排序,免去 Sort 算子

如果把 created_at 放前面会怎样?WHERE created_at > X 命中的是一大段时间内所有用户的数据,索引选择性差,而且 user_id 的等值过滤用不上索引顺序。等值列放前面,是因为它能把索引快速收敛到一小段连续区间,后续列才能在这段区间里继续发力。 范围列一旦"开闸",它后面的列就只能在结果集里逐行扫,排序帮助有限——这是为什么范围列永远是最后一道。

一个常见误区:为了覆盖各种查询组合,把列的每种排列都建一遍索引——(a,b,c)、(a,c,b)、(b,a,c)…… 这是灾难。应该用 pg_stat_user_indexes(PG)或 sys.schema_unused_indexes(MySQL)看哪些索引实际被用到了,没用的果断删。

三、覆盖索引:免回表

普通二级索引里存的是"索引列 + 行指针(主键)"。用索引找到行后,如果查询还需要其他列,数据库得拿主键回主键索引(或堆表)再查一次——这叫回表(heap fetch)。回表是随机 IO,在 SSD 上也不便宜,在 HDD 上更是性能杀手,在高并发场景下还会放大锁竞争。

覆盖索引(Covering Index) 的思路:把查询用到的所有列都塞进索引,这样索引扫完就拿到了所有数据,根本不用回表。

PostgreSQL 用 INCLUDE 语法实现,优雅又克制:

-- 查询:用户列表,只要 id、邮箱、昵称
SELECT id, email, nickname FROM users WHERE email = '[email protected]';

-- 普通索引:命中后还要回表取 nickname
CREATE INDEX idx_users_email ON users(email);

-- 覆盖索引:把 nickname 额外"挂"进索引,免回表
CREATE INDEX idx_users_email_cover ON users(email) INCLUDE (nickname);

EXPLAIN SELECT id, email, nickname FROM users WHERE email = '[email protected]';
-- Index Only Scan using idx_users_email_cover on users
-- 注意是 Index **Only** Scan —— 不回表

INCLUDE 列只存在叶子节点、不参与排序、不占内部节点空间,所以加它几乎不增加索引深度,却能把回表彻底干掉。这是 PG 11+ 最被低估的特性之一。

MySQL InnoDB 的二级索引天然附带主键,所以覆盖索引更划算;MySQL 没有 INCLUDE,只能把列直接塞进索引键列表(INDEX(email, nickname)),代价是这些列参与排序、可能影响选择性。两边取舍不同,但目标一致:让索引自己就能回答查询。

四、部分索引:小而快

很多时候你只关心表里的一小部分数据。比如 users 表里,只有 status = 'active' 的用户会被频繁查询,僵尸用户占了 90% 但几乎从不查。如果给整张表的 email 建索引,90% 的索引项是浪费的,还要为它们的写入持续买单。

部分索引(Partial Index) 用一个 WHERE 把索引范围缩小到有意义的子集:

-- 只索引活跃用户
CREATE INDEX idx_users_active_email ON users(email)
WHERE status = 'active';

-- 大表只索引近 30 天数据
CREATE INDEX idx_orders_recent ON orders(created_at)
WHERE created_at > '2026-06-17';

-- 只索引未发货订单(业务热点)
CREATE INDEX idx_orders_pending ON orders(user_id)
WHERE status = 'pending';

好处是双重的:索引更小(省存储、缓存命中率高),写入更便宜(不满足 WHERE 的行根本不进索引)。对于"少数热点 + 大量冷数据"的典型业务模式——而绝大多数业务都是这种模式——部分索引是性价比最高的选择。

注意:查询的 WHERE 必须能"蕴含"索引的 WHERE,优化器才会用它。WHERE status = 'active' AND email = ? 能用上面的索引;WHERE email = ?(不带 status 条件)就用不上。设计时要和真实查询严格对齐。

五、表达式 / 函数索引

一个经典踩坑:你给 name 建了索引,但查询写的是 WHERE LOWER(name) = 'alice'——索引完全用不上。原因是 B-tree 索引存的是 name 的原值,而查询在拿 LOWER(name) 比较,这是另一个表达式,树里没存它。

解法:给表达式本身建索引。

-- 大小写不敏感查询
CREATE INDEX idx_users_name_lower ON users(LOWER(name));
SELECT * FROM users WHERE LOWER(name) = 'alice';  -- ✅ 命中

-- 不区分大小写的邮箱
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- JSON 提取
CREATE INDEX idx_events_type ON events((metadata->>'type'));

核心原则:索引存什么,WHERE 就得写什么。 只要表达式在索引定义和查询里完全一致,优化器就能匹配上。

MySQL 在这方面历史上较弱,8.0 起支持函数索引(CREATE INDEX idx ON t ((LOWER(name)))),也支持用生成列(generated column) + 索引的迂回方案。PG 一直灵活,任意不可变表达式都能索引——但注意,函数必须是 IMMUTABLE 的:LOWER 是,NOW() 不是(结果随时间变,索引无法稳定)。

六、特殊索引:GIN / GiST / BRIN

B-tree 不是唯一选择。面对非标量数据,专门的索引类型能差出几个数量级。

GIN(Generalized Inverted Index)——倒排索引,擅长"一个值包含多个元素"的查询:

-- jsonb 包含查询
CREATE INDEX idx_events_meta ON events USING GIN (metadata);
SELECT * FROM events WHERE metadata @> '{"type":"signup"}';

-- 数组交集
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags && ARRAY['rust','wasm'];

-- 全文检索
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('english', body));

GIN 牺牲写入速度换极快查询,适合"读多写少 + 元素查找"。建索引慢、更新贵,但查询能从全表扫降到毫秒级。

GiST(Generalized Search Tree)——擅长范围、几何、最近邻这类"非纯有序"的运算:

-- 时间范围重叠(会议室预订冲突检测)
CREATE INDEX idx_bookings_range ON bookings
  USING GIST (tsrange(start_at, end_at));
SELECT * FROM bookings
WHERE tsrange(start_at, end_at) && tsrange('2026-07-17 14:00','2026-07-17 15:00');

-- 地理附近(配 PostGIS)
CREATE INDEX idx_shops_geo ON shops USING GIST (location);
SELECT * FROM shops ORDER BY location <-> 'POINT(121.47 31.23)' LIMIT 10;

BRIN(Block Range Index)——超大表且物理有序时的神器。它不为每行存索引,而是为"一段物理块"存 min/max。索引体积只有 B-tree 的千分之一,代价是精度差、适合范围扫描:

-- 按时间追加写入的日志表(天然物理有序)
CREATE INDEX idx_logs_ts_brin ON logs USING BRIN (ts);
-- 10 亿行的表,B-tree 索引可能 20GB,BRIN 只要几十 MB

判断该用哪个,看数据形态和查询形态:标量等值/范围 → B-tree;jsonb/数组/全文 → GIN;范围/几何/近邻 → GiST;超大有序追加表 → BRIN。

七、索引不是免费的:写代价与维护

讲完好处,必须把代价讲清楚——这是新手和老手最大的分水岭。

写放大:每个索引都是一棵独立的 B-tree。表有 5 个索引,一次 INSERT 实际要写 6 个地方(堆 + 5 棵树)。UPDATE 改了被索引的列,所有相关索引都要更新。高写入表上乱加索引,TPS 会断崖式下跌。

存储成本:索引不是免费的磁盘。一个 100GB 的表,加了 5 个索引,总存储可能膨胀到 200GB+。内存也一样:索引要常驻 buffer cache 才有效,索引越多,每个能分到的缓存越少,反而拖累整体命中率。

维护与碎片:B-tree 反复增删会留下空洞(bloat),查询变慢、空间浪费。需要定期 VACUUM / REINDEX(PG)或 OPTIMIZE TABLE(MySQL)。PG 12+ 的 REINDEX CONCURRENTLY 能在线重建不锁表,生产必备。

实战建议:

• 单表索引数控制在 5-6 个以内,超过就要审视。
• 冗余索引一定要删:(a,b,c) 已存在时,(a,b) 和 (a) 都是多余的(最左前缀已覆盖)。
• 用 pg_stat_user_indexes 看 idx_scan,长期为 0 的索引直接砍掉——它只写不读,纯负担。
• 写多读少的表,索引越少越好;读多写少的报表/搜索表,可以激进些。

八、设计流程:从慢查询到索引

把前面所有内容串成一套可复用的工作流。这是你在生产环境真正该做的事:

1. 找到慢查询。 开 pg_stat_statements(PG)或慢查询日志(MySQL),按平均执行时间排序,挑出 top N。别凭感觉猜,用数据说话。

2. 读 SQL,拆解访问模式。 看三件事:WHERE 用了哪些列?哪些等值、哪些范围?ORDER BY / GROUP BY 用了哪些列?查询最终 SELECT 了哪些列(为覆盖索引准备)?

3. 设计一棵覆盖该查询的索引。 按"等值 → 范围 → 排序"排好列顺序,用 INCLUDE 把 SELECT 用到的列挂上去做成覆盖索引。如果数据有明显的热点子集,加 WHERE 做成部分索引。

4. EXPLAIN 验证。 建索引前后各跑一次 EXPLAIN (ANALYZE, BUFFERS)(PG)或 EXPLAIN ANALYZE(MySQL)。确认:执行计划是 Index Scan / Index Only Scan 而非 Seq Scan;成本和实际时间显著下降;没有意外的 Sort 或大量回表的 Bitmap Heap Scan。

5. 持续观察。 上线一周后,再看 pg_stat_user_indexes 确认新索引被用到了。用不上的就回滚——索引不是"加上去就有保险",而是"验证了才算数"。

举个完整例子。慢查询:

SELECT id, amount_cents, status FROM orders
WHERE user_id = 88123 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

拆解:等值 user_id、等值 status、排序 created_at、SELECT 需要 amount_cents。设计:

CREATE INDEX idx_orders_user_pending_time
ON orders(user_id, status, created_at DESC)
INCLUDE (amount_cents)
WHERE status = 'pending';  -- pending 是少数热点,做部分索引

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- Limit  (cost=0.42..2.30 rows=20 ...)
--   -> Index Scan Backward using idx_orders_user_pending_time on orders
--        Index Cond: (user_id = 88123)
--        Buffers: shared hit=4   -- 4 个页,无回表,无排序

一棵索引同时解决过滤、排序、回表、热点收敛——这才是"懂索引"该有的样子。

小结

1. 索引按查询设计,不按表设计。 先有慢查询,再设计索引;一查一索引,精准胜过堆数量。
2. 复合索引的灵魂是最左前缀,列顺序遵循"等值 → 范围 → 排序"。 这一条决定了你 80% 的索引能不能被用上。
3. 覆盖索引(配 INCLUDE)+ 部分索引(配 WHERE)是两把利器。 一个免回表,一个砍掉冷数据,性价比极高。
4. WHERE 写什么,索引就得建什么。 LOWER(name) 查询要给 LOWER(name) 建索引;表达式必须严格一致,优化器才认。
5. 索引有写放大、存储、维护三重代价。 没用到的索引是纯负债,定期用 pg_stat_user_indexes 清理。

索引不是越多越好,而是越准越好。一棵为查询量身定制的索引,胜过十个拍脑袋的单列索引。