yicode

为什么加了索引，查询还是走全表扫描

·约 1700 字 · PostgreSQL查询优化

上周处理了一个很典型的工单：订单列表页在数据量涨到 900 万行之后开始超时，负责的同事已经在 created_at 上建了 B-tree 索引，但响应时间没有任何变化。他问我「索引明明建好了，为什么不走？」

「索引不走」几乎不是一个原因造成的，而是一类现象。这篇把我这些年遇到过的五种情况整理出来，顺便说说排查顺序——顺序其实比结论更有用。

第零步：先看执行计划，不要猜

任何关于索引的讨论，脱离执行计划都是空谈。PostgreSQL 里用 EXPLAIN (ANALYZE, BUFFERS)，它会真正执行一次并给出实际行数和缓冲区读取量：

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, user_id, amount, created_at
FROM orders
WHERE created_at >= '2024-09-01'
ORDER BY created_at DESC
LIMIT 50;

重点看三个地方：

  • 节点类型：Seq Scan 是顺序扫描，Index Scan / Index Only Scan / Bitmap Heap Scan 才用到了索引。
  • rows 的估算值与实际值差距：rows=1200 ... actual rows=830000 这种数量级偏差，基本可以断定统计信息有问题。
  • Buffers 里的 shared read：真正从磁盘读了多少块，比时间更稳定，不受缓存预热影响。

如果计划里出现了 Filter 而不是 Index Cond，说明条件是在读出行之后才过滤的，索引没起到定位作用。

场景一：列被函数或表达式包住了

这是出现频率最高的一种。索引记录的是列的原始值，一旦在列上套了函数，索引里的有序结构就用不上了：

-- 走不了 idx_orders_created_at
SELECT * FROM orders WHERE date(created_at) = '2024-09-14';

-- 改写成范围条件，索引可用
SELECT * FROM orders
WHERE created_at >= '2024-09-14 00:00:00+08'
  AND created_at <  '2024-09-15 00:00:00+08';

同类问题还有 WHERE upper(email) = 'A@B.COM'、WHERE amount + 0 > 100、WHERE substr(phone, 1, 3) = '138'。

如果查询模式确实固定，可以直接建表达式索引：

CREATE INDEX CONCURRENTLY idx_users_lower_email
    ON users (lower(email));

注意表达式必须和查询里写的完全一致，规划器做的是语法层面的匹配，不会替你做代数变换。

线上建索引一律加 CONCURRENTLY。它不持有阻塞写入的锁，代价是要扫两遍表、失败后会留下 INVALID 状态的索引，需要手动 DROP 掉重来。

场景二：隐式类型转换

这个更隐蔽，因为 SQL 本身看不出任何问题。假设 order_no 是 varchar，而应用传进来一个整数：

SELECT * FROM orders WHERE order_no = 20240914001;

PostgreSQL 会把列转成数值类型再比较，等价于在列上加了一次 ::bigint，索引随之失效（在严格模式下它甚至会直接报类型错误，反而是好事）。MySQL 更「贴心」，会静默完成转换，于是问题一直藏着。

排查手段是看执行计划里的 Index Cond 是否带了 :: 转换标记。修复方式很简单：让参数类型和列类型一致，ORM 层面通常是显式声明字段类型。

场景三：选择性太差，规划器主动放弃

很多人不接受的一点是：不走索引有时是正确决策。索引扫描是随机 I/O，每命中一行都要回表取数据；顺序扫描是连续 I/O，还能利用预读。当满足条件的行占到全表的相当比例时（经验值大约 5%～20%，取决于表的物理排布），顺序扫描反而更快。

典型的反面教材是在 status 这种只有三四个取值的列上建索引，然后查询 WHERE status = 'paid'，而 80% 的订单都是 paid。

验证方法是临时关掉顺序扫描，对比两种计划的实际耗时：

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;

如果强制走索引之后反而更慢，那规划器是对的，问题在别处。如果强制之后快很多，再去看是不是 random_page_cost 设得不合理——这个参数默认 4.0，是按机械硬盘的寻道代价定的。SSD 上通常调到 1.1：

ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

另一个方向是让索引本身覆盖查询，避免回表。加上 INCLUDE 列之后可以走 Index Only Scan：

CREATE INDEX CONCURRENTLY idx_orders_status_created
    ON orders (status, created_at DESC)
    INCLUDE (amount);

还有一招是部分索引。如果业务只关心未完成的订单，索引体积可以缩小两个数量级：

CREATE INDEX CONCURRENTLY idx_orders_pending
    ON orders (created_at DESC)
    WHERE status IN ('pending', 'processing');

场景四：前缀通配与排序规则

B-tree 索引按值排序，LIKE 'abc%' 可以定位到区间起点，LIKE '%abc' 不行。这一点大家都知道，但容易被忽略的是排序规则（collation）：只有在 C 或 POSIX 排序规则下，LIKE 前缀匹配才能直接用普通索引。如果数据库是用 zh_CN.UTF-8 初始化的，需要给索引显式指定操作符类：

CREATE INDEX CONCURRENTLY idx_products_name_prefix
    ON products (name text_pattern_ops);

而中缀、后缀匹配就别指望 B-tree 了。要么上 pg_trgm 的 GIN 索引，要么老老实实接全文检索：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
    ON products USING gin (name gin_trgm_ops);

场景五：统计信息过期

规划器完全依赖 pg_statistic 里的采样数据估算行数。批量导入几百万行之后，autovacuum 还没来得及跑，估算值可能停留在导入之前的水平，计划自然是错的。

-- 看上次分析时间和死元组比例
SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

ANALYZE orders;

对于数据分布倾斜严重的列，可以提高采样精度（默认 100，最大 10000）：

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders (status);

还有一种情况是多列之间存在函数依赖，比如「城市」和「省份」，规划器默认按独立事件相乘估算，结果严重偏小。PostgreSQL 10 之后可以建扩展统计：

CREATE STATISTICS stat_orders_region (dependencies, ndistinct)
    ON province, city FROM orders;
ANALYZE orders;

一份可以照着走的排查清单

  1. 跑 EXPLAIN (ANALYZE, BUFFERS)，确认到底是不是索引问题，很多「慢查询」其实慢在排序或哈希连接上。
  2. 比对估算行数与实际行数，差一个数量级以上就先 ANALYZE。
  3. 检查 WHERE 条件里的列有没有被函数包住、有没有隐式转换。
  4. 用 SET enable_seqscan = off 做一次 A/B，判断规划器的选择是否合理。
  5. 确认索引列顺序和查询的过滤、排序方式匹配——复合索引 (a, b) 支持按 a 过滤，不支持只按 b 过滤。
  6. 最后才考虑加新索引。每个索引都会拖慢写入并占用缓存，先用 pg_stat_user_indexes 把 idx_scan = 0 的僵尸索引清掉。

回到开头那个工单：真实原因是场景一和场景三的叠加——查询里写了 date(created_at) = ...，同时还有一个 status = 'paid' 的低选择性条件。改成时间范围条件、并建了一个带 status 前导列的复合索引之后，P95 从 4.8 秒降到 40 毫秒。

← 返回首页