DAY 41 W6 · 两年之后：索引与执行计划 高频

加了索引却没被用：失效场景与统计信息

索引建了，查询还是慢？你逐个复现六种「索引装死」的写法，还发现统计信息过期会让优化器选错路。

学 40 min
练 65 min
盘 15 min
共 120 分钟

学 · 40 min

  1. 01六类失效：函数包裹列、隐式类型转换、前导 %、OR 连接、低选择性、排序方向不匹配

    场景索引明明在，查询就是不走--先过一遍六种「装死」写法。

    ① 函数包列：where date(created_at) = ...（D2 埋的伏笔今天收）；② 隐式类型转换：varchar 列 = 整数值，列被套 cast；③ 前导 %：like '%手机'，B-tree 没法定位（后缀通配可以反转+表达式索引）；④ OR 连接：OR 的两半不是每列都有索引时退化；⑤ 低选择性：命中行太多，优化器主动放弃（D40 讲过，是正确决策）；⑥ 排序方向 / collation 不匹配：索引的排序和 ORDER BY 要的不一致。

    -- ①② 的修法对照
explain select * from orders where date(created_at) = current_date - 1;   -- 失效
explain select * from orders
where created_at >= current_date - 1 and created_at < current_date;       -- 命中

    易错六类里只有①②④是「写法病」要治；⑤ 是优化器正确判断，治了反而慢。

  2. 02统计信息从哪来、ANALYZE 做了什么

    场景优化器的一切估算都来自一本「户口本」--谁在维护它？

    PG 对每列维护统计（pg_stats：直方图、高频值、n_distinct、null 占比），analyze 命令就是采样刷新这本户口本（默认采样 3 万行）。优化器的 rows 估算、索引选择、join 算法全按它算。大批量写入后统计就是旧的，优化器拿着旧地图走新路。

    -- 灌数后统计还是旧的（reltuples 停留在 5 万）
select relname, reltuples::bigint from pg_class where relname = 'orders';

analyze orders;   -- 刷新，reltuples 立即接近百万
select relname, reltuples::bigint from pg_class where relname = 'orders';

    易错「昨天好好的今天突然慢了」的排查第一步：想想昨晚是不是跑过大批量导入 / 更新。

  3. 03autovacuum 与统计信息过期导致选错计划

    场景没人手动 analyze，统计怎么平时还算新鲜？--autovacuum 在后台干活。

    autovacuum 三件事：清死元组（vacuum）、刷新 VM、更新统计（analyze）。触发的默认规则是「变更行数超过表大小的 10%」（analyze 部分），大表上 10% 是个很大的数字--刚导入完的窗口期统计就是旧的。可以按表调小：alter table t set (autovacuum_analyze_scale_factor = 0.02)。

    -- 看各表 autovacuum 的最近活动
select relname, last_autoanalyze, last_autovacuum, n_live_tup, n_dead_tup
from pg_stat_user_tables
order by last_autoanalyze nulls first;

    易错「大批量导入后手动 analyze」不是玄学仪式，是把 autovacuum 的下一次触发提前到「现在」。

  4. 04pg_stat_statements 的安装与使用

    场景「到底哪条 SQL 慢」不能靠猜--让数据库自己记账。

    pg_stat_statements 扩展记录每条（归一化后）语句的调用次数、总耗时、平均耗时、返回行数。慢查询治理的第一入口：先看 TOP 10 总耗时（total_exec_time），再看平均耗时（mean_exec_time）和调用次数的组合。

    -- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'，然后重启
create extension pg_stat_statements;

select calls,
       round(total_exec_time::numeric, 0) as 总ms,
       round(mean_exec_time::numeric, 1)  as 平均ms,
       rows,
       left(query, 70) as query
from pg_stat_statements
order by total_exec_time desc
limit 10;

    易错需要 preload + 重启才生效；归一化把字面量换成 $1，同一个模板的慢查询才会被聚合统计。

练 · 65 min

  1. 逐个复现 6 种失效场景，每种都保存失效前后的执行计划
    参考答案

    psql 里 \o 文件名 可以把每份计划存档，失效 / 命中各存一份。六类里只有①②④是「写法病」要治，⑤是优化器的正确判断，治了反而慢。

    -- ① 函数包列：失效 vs 命中
explain select * from orders where date(created_at) = current_date - 1;
explain select * from orders
where created_at >= current_date - 1 and created_at < current_date;
-- ② 列被套 cast（PG 里「隐式转换失效」的实际形态）
explain select * from orders
where user_id::text = '00000000-0000-0000-0000-000000000042';
-- ③ 前导 %
explain select * from products where name like '%42';
-- ④ OR 的两半不都有索引（total_amount 没索引）
explain select * from orders
where user_id = '00000000-0000-0000-0000-000000000042' or total_amount > 1900;
-- ⑤ 低选择性：命中 ≈80% 行，Seq 是正确决策
explain select * from orders where status = 2;
-- ⑥ 排序键不在索引里：多出 Sort 节点
explain select * from orders
where user_id = '00000000-0000-0000-0000-000000000042'
order by total_amount;
  2. 把 date(created_at) = '2026-01-01' 改写成范围查询，验证索引恢复命中
    参考答案

    左闭右开，和 D2 学的完全一致。列上一套函数，索引就不认识这列了--D2 埋的伏笔今天正式收尾。

    explain select * from orders where date(created_at) = current_date - 1;   -- Seq Scan
explain select * from orders
where created_at >= current_date - 1
  and created_at <  current_date;                                           -- Index Scan
  3. 用 varchar 列与整数比较，观察隐式转换导致的全表扫
    参考答案

    PG 直接报错、绝不静默转换--它从类型系统上堵死了 MySQL 那种隐式转换失效。PG 里等价的翻车形态是给列套 cast（第 1 题的②）：user_id::text = ... 照样全表扫。

    select * from users where email = 42;
-- ERROR:  operator does not exist: text = integer
  4. 大批量更新后先查计划，再手动 ANALYZE，对比计划变化
    参考答案

    预期现象：analyze 前 reltuples 明显偏小、created_at 范围的估算 rows 和 actual rows 拉开差距；analyze 后立刻对齐。「昨天好好的今天慢了」的第一反应：昨晚是不是跑过大批量导入。

    begin;

-- 模拟大批量导入：+20 万行
insert into orders (user_id, status, total_amount, created_at)
select '00000000-0000-0000-0000-000000000042', 2,
       round((20 + random() * 2000)::numeric, 2),
       current_date - (random() * 90)::int
from generate_series(1, 200000);

-- 统计还是旧的：reltuples 停留在灌入前的值
select relname, reltuples::bigint from pg_class where relname = 'orders';
explain (analyze) select count(*) from orders where created_at >= current_date - 30;

analyze orders;   -- 刷新户口本
select relname, reltuples::bigint from pg_class where relname = 'orders';
explain (analyze) select count(*) from orders where created_at >= current_date - 30;

rollback;   -- 数据回滚，别真留下 20 万行
  5. 装 pg_stat_statements，找出耗时 TOP10 的语句
    参考答案

    必须 preload + 重启才生效。归一化把字面量换成 $1，同一模板的慢查询才会聚合成一行；看榜单先看总耗时（谁在吃数据库），再用平均耗时 × 调用次数定位单条慢的。

    -- ① 装扩展（学栏的步骤）：改配置 + 重启容器
alter system set shared_preload_libraries = 'pg_stat_statements';
-- docker restart pg16，重连后：
create extension pg_stat_statements;

-- ② 随便跑几条查询让它记账，再看 TOP 10
select calls,
       round(total_exec_time::numeric, 0) as 总ms,
       round(mean_exec_time::numeric, 1)  as 平均ms,
       rows,
       left(query, 70) as query
from pg_stat_statements
order by total_exec_time desc
limit 10;
过关标准 产出一张「失效写法 -> 正确写法」对照表，至少 6 行，每行附执行计划证据。