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

时间快进：百万订单与人生第一个索引

「时间快进」日：把库冲到 100 万订单 / 250 万明细 / 80 万支付 / 50 万登录。灌完你会发现，上周还秒出的查询现在慢得离谱--先建第一个索引救急，顺便搞懂它为什么快。

学 40 min
练 55 min
盘 25 min
共 120 分钟

学 · 40 min

  1. 01generate_series 大灌数的套路（现在你能看懂 seed.sql 了）

    场景seed.sql 的 W6 段把库从 5 万冲到百万--当年是天书，今天是套路。

    公式：insert into t select ... from generate_series(...)，配上 random() 造分布、setseed() 固定随机、日期锚定 current_date 平移出「过去两年」。百万行一条语句几十秒，学习成本最低的压测手段。

    insert into orders(id, user_id, status, total_amount, created_at)
select gen_random_uuid(),
       (select id from users offset floor(random()*10000) limit 1),
       (array[1,2,2,2,3])[1 + floor(random()*5)],
       round((random()*990 + 10)::numeric, 2),
       current_date - (random()*730)::int
from generate_series(1, 1000000);
analyze orders;   -- 灌完必做，下一条讲为什么

    易错灌完不 ANALYZE，统计信息还停留在 5 万行时代，优化器按旧地图走路（D41 详述）。

  2. 02B+ 树结构：为什么不是二叉树、不是哈希表；树高与磁盘 IO 的关系

    场景第一个索引建完，查询从 400ms 变 1ms--凭什么快 400 倍？

    磁盘按页（PG 8KB）读取，一次 IO 拿一页。B+ 树把每个节点做成一整页、一个节点上百个分叉，所以矮胖：100 万行只要 3~4 层 = 3~4 次 IO。二叉树高瘦（20 层 = 20 次 IO）；哈希表虽是 O(1)，但不支持范围和排序。树高 = IO 次数，这就是快的全部本质。

    -- 对比：按主键点查
select * from orders where user_id = '某个uuid';      -- 无索引：全表扫
create index on orders(user_id);
select * from orders where user_id = '某个uuid';      -- Index Scan：3~4 次 IO

    易错B+ 树 vs B 树的考点：数据全在叶子层、叶子间有链表--范围扫描时顺藤摸瓜，不用回到上层。

  3. 03PG 是堆表 + 二级索引，没有聚簇索引--与 MySQL InnoDB 的关键差异

    场景面试官问：「PG 和 MySQL 的索引有什么区别？」--今天这个答案是加分位。

    MySQL InnoDB：表就是主键索引（聚簇），数据按主键序物理存放，二级索引叶子存主键值，查完还要再走一遍主键树。PG：表是无序堆（heap），所有索引（含主键）一律是二级索引，叶子存 (键, ctid 物理地址)。后果：PG 的主键查询也要「回堆」，但插入没有聚簇键的排序负担。

    select ctid, id from orders limit 3;   -- ctid = (页号, 行偏移)，行的物理地址
-- 索引叶子里存的就是它：查到键 -> 拿 ctid -> 回堆取整行

    易错PG 可以用 CLUSTER 命令按某索引重排物理顺序，但那是一次性操作、不是 MySQL 那种「聚簇索引」。

  4. 04「回表」在 PG 里是什么（索引 -> heap 取行）

    场景「回表」这个词总在性能文章里出现，在 PG 里有确切对应物。

    Index Scan 两步走：索引里查到键、拿到 ctid；再拿 ctid 去堆里取整行。第二步就是回表。查询需要的列全在索引里时，理论上可以不回表--Index Only Scan（D38 展开）。回表是随机 IO，取的行一多，索引反而不如全表扫。

    explain select * from orders where user_id = '...';
-- Index Scan using ... -> Heap Fetches 发生在扫描节点内部
explain select user_id from orders where user_id = '...';
-- 只取索引里有的列：可能变成 Index Only Scan

    易错「建了索引反而更慢」的大多数场景，就是回表的随机 IO 输给了全表扫的顺序 IO（D40 展开）。

  5. 05索引的三项代价：空间、写入放大、维护

    场景想把每列都建上索引--先看账单。

    ① 空间：索引本身占磁盘（能到表的一半大小）；② 写入放大：每次 insert / update 都要同步维护所有相关索引；③ 维护：膨胀、失效索引清理、优化器选择空间变大。索引是「读加速、读付费」的交易，不是白捡的。

    select relname, pg_size_pretty(pg_relation_size(relname::regclass)) as 大小
from pg_class
where relkind in ('r', 'i') and relname like 'orders%'
order by pg_relation_size(relname::regclass) desc;

    易错写多读少的表，索引是纯负担；没人查询的索引是纯负债（pg_stat_user_indexes 的 idx_scan = 0 可以揪出来）。

练 · 55 min

  1. 跑 seed.sql「W6」段大灌数，各表 count(*) 核对行数
    参考答案

    行数只对量级：明细每单随机 1~4 件、支付流水只给已支付订单。D41 会看到「灌完不 ANALYZE」的后果，这里先把习惯养成。

    -- seed.sql 的 §F 段共五条语句，psql 里逐条执行（整段要跑几分钟）
-- 灌完必做：统计信息还停留在 5 万行时代
analyze orders; analyze order_items; analyze payments; analyze user_logins;

select (select count(*) from orders)      as 订单,     -- ≈100 万
       (select count(*) from order_items) as 明细,     -- ≈250 万
       (select count(*) from payments)    as 支付流水, -- ≈80 万
       (select count(*) from user_logins) as 登录日志; -- ≈50 万
  2. 记录 3 条常用查询的基线耗时（\timing，比如按用户查订单）
    参考答案

    把三条耗时原样写进 notes.md--这是本周的「体检基线」，D42 算提升倍数全靠它。此刻 user_id 还没索引，①预计几百 ms（100 万行全表扫），别慌，下一题就救它。

    \timing on

-- ① 点查：某用户的订单
select * from orders where user_id = '00000000-0000-0000-0000-000000000042';
-- ② 日报：近 30 天每日单量与 GMV
select created_at::date, count(*), sum(total_amount)
from orders
where created_at >= current_date - 30
group by 1;
-- ③ 连接聚合：各城市已支付订单数
select u.city, count(*)
from orders o join users u on u.id = o.user_id
where o.status = 2
group by 1;
  3. 对 orders.user_id 无索引查询计时，建索引后再计时，记录倍数
    参考答案

    预期差一到两个数量级（机器不同有浮动），把两个数都记下来--今天的过关标准就是说出这个倍数。UUID 用 seed 的确定性映射（编号 42 拼出来的这个）保证一定查得到。

    \timing on

-- 无索引（学栏示例若建过，先删掉）
drop index if exists orders_user_id_idx;
select * from orders where user_id = '00000000-0000-0000-0000-000000000042';
-- ≈几百 ms：Seq Scan 扫全部 100 万行

create index on orders (user_id);
select * from orders where user_id = '00000000-0000-0000-0000-000000000042';
-- ≈个位数 ms：Index Scan，B+ 树 3~4 层 = 3~4 次 IO
  4. 用 pg_relation_size 对比表和索引各自占多大
    参考答案

    orders 表本体 ≈100+ MB，user_id 索引 ≈20~30 MB--索引能到表的几分之一，「读加速、空间付费」。想看含全部索引的总占用用 pg_total_relation_size。

    select relname,
       pg_size_pretty(pg_relation_size(relname::regclass)) as 大小
from pg_class
where relkind in ('r', 'i') and relname like 'orders%'
order by pg_relation_size(relname::regclass) desc;
  5. 在有索引和无索引的表上各批量插 10 万行，对比写入耗时
    参考答案

    预期带索引慢 ≈1.5~2 倍：每行写入都要同步维护 B-tree（写入放大）。包在事务里 rollback 是标准做法，耗时照样被 \timing 记到。

    \timing on

-- 第一遍：带着第 3 题建好的索引
begin;
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() * 730)::int
from generate_series(1, 100000);
rollback;                          -- 记下耗时再回滚，数据不弄脏

-- 第二遍：删掉索引重复同样的 begin; insert ...; rollback;
drop index orders_user_id_idx;
create index on orders (user_id);  -- 测完把索引建回来
过关标准 说出加索引前后的倍数；能主动讲出 PG 与 MySQL 在表组织方式上的差异--这是很好的加分点。