DAY 37 W6 · 两年之后：索引与执行计划

索引类型全家福

不同的慢法用不同的索引：状态列只有几个值、搜索要忽略大小写、商品名要做全文搜。一个 B-tree 打不了天下。

学 45 min
练 60 min
盘 15 min
共 120 分钟

学 · 45 min

  1. 01B-tree / Hash / GIN / GiST / BRIN / SP-GiST 各自适用场景

    场景索引类型六兄弟，先认脸再认专长。

    B-tree（默认）：等值 + 范围 + 排序，90% 场景；Hash：只等值、不支持范围（场景少）；GIN：倒排索引，管「一个字段里含什么」（数组、jsonb、全文）；GiST：几何、范围类型、近邻搜索；BRIN：块区间摘要，超大时序表；SP-GiST：前缀/四叉树类结构（IP、电话前缀）。选型看数据形态，不是看名字酷不酷。

    create index on orders (user_id);                      -- B-tree（默认）
create index on orders using hash (user_id);           -- 只能等值
create index on products using gist (price_range);     -- GiST：范围/几何

    易错PG 的 Hash 索引在 PG 10 前不记 WAL（崩溃会丢），老资料直接说「别用 Hash」；PG 10+ 可用但场景依然窄。

  2. 02GIN 用于 jsonb 和全文检索

    场景商品加了个 jsonb 属性列，老板要按属性筛选--B-tree 对 jsonb 束手无策。

    GIN = 倒排索引：「值 -> 哪些行含有它」的映射表。jsonb 的 @> 包含查询、tsvector 全文检索、数组包含，全靠它。没有 GIN，这些查询全是全表扫。

    -- jsonb：给扩展属性建 GIN
create index on products using gin (attrs jsonb_path_ops);
select * from products where attrs @> '{"color": "红"}';

-- 全文：先转 tsvector 再 GIN
create index on products using gin (to_tsvector('simple', name));
select * from products
where to_tsvector('simple', name) @@ to_tsquery('simple', '手机 & 配件');

    易错GIN 建得慢、更新代价高（倒排表维护复杂）--写多读少的表慎用；jsonb_path_ops 比默认 operator class 更小更快但只支持 @>。

  3. 03BRIN 用于超大且物理有序的时序表

    场景500 万行登录日志按时间查--B-tree 索引几百 MB，有没有更省的？

    BRIN 只记录每个物理块区间的 min/max。数据按时间追加（物理有序）时，「login_at > 昨天」这种条件能直接跳过 99% 的块。索引小到 KB 级，是「时序大表 + 追加写」的完美搭档。

    create index on user_logins using brin (login_at);
create index on user_logins (login_at);   -- 对照组：B-tree

select relname, pg_size_pretty(pg_relation_size(relname::regclass)) as 大小
from pg_class where relname like 'user_logins%';   -- BRIN 小几个数量级

    易错BRIN 的前提是物理有序：数据乱序写入时 min/max 全重叠，BRIN 完全失效。先确认写入模式再选它。

  4. 04部分索引与表达式索引--PG 相对 MySQL 的明显优势

    场景「待支付」订单只占 1%，查询却总在全表里捞；搜索要不区分大小写。

    部分索引：where 条件 只索引满足条件的行--索引小、写入省、命中率还高（专给高频查询用）。表达式索引：索引的不是列而是表达式的结果，如 lower(email)；查询里必须写一模一样的表达式才命中。MySQL 没有部分索引（8.0 才有函数索引），这两个是 PG 的招牌优势。

    -- 部分索引：只索引待支付订单（后台高频轮询它）
create index idx_pending on orders (created_at)
where status = 1;
select * from orders where status = 1 and created_at > now() - interval '1 day';

-- 表达式索引：忽略大小写的登录
create index idx_email_lower on users (lower(email));
select * from users where lower(email) = 'a@b.com';   -- 命中

    易错表达式索引要求查询表达式一字不差：索引 lower(email)，查询写 upper(email) 或 email 不带函数，全都不命中。

练 · 60 min

  1. 给 status = 'pending' 建部分索引，对比它和全量索引的大小
    参考答案

    待支付约占一成，部分索引也就只有全量索引的 ≈1/10；后台「待支付且近一天」的轮询查询照样命中它。省空间还省写入，这就是部分索引的卖点。

    -- 题面的 'pending' 是业务叫法，库里 status 是 int，待支付 = 1
create index idx_orders_created_full    on orders (created_at);
create index idx_orders_created_pending on orders (created_at) where status = 1;

select relname, pg_size_pretty(pg_relation_size(relname::regclass)) as 大小
from pg_class
where relname like 'idx_orders_created%'
order by pg_relation_size(relname::regclass) desc;
  2. 建 lower(email) 表达式索引，验证 where lower(email)=... 能命中
    参考答案

    表达式索引要求查询里写一字不差的表达式：索引 lower(email)，查询写裸 email 或 upper(email) 都不命中。users 才 1 万行，计划差异不一定明显，重点记住命中规则。

    create index idx_users_email_lower on users (lower(email));

explain select * from users where lower(email) = 'user42@example.com';  -- 命中
explain select * from users where email = 'user42@example.com';         -- 不命中
  3. 加一个 jsonb 列，建 GIN 索引并做包含查询
    参考答案

    500 行的小表优化器多半仍选 Seq Scan（它算得过来账），set enable_seqscan = off 再 explain 能看到 GIN 计划。jsonb_path_ops 比默认 operator class 更小更快，但只支持 @> 一种查询。

    -- products 还没有 jsonb 列，先加一个并灌值
alter table products add column attrs jsonb;
update products
set attrs = jsonb_build_object(
      'color', (array['红','蓝','黑'])[1 + floor(random() * 3)::int],
      'weight', round((0.1 + random() * 2)::numeric, 2));

create index idx_products_attrs on products using gin (attrs jsonb_path_ops);

select count(*) from products where attrs @> '{"color": "红"}';
  4. 在时间列上分别建 B-tree 和 BRIN，对比索引大小
    参考答案

    预期 B-tree ≈十几 MB、BRIN 只有几十 KB，差两三个数量级。但注意：本库登录数据是乱序灌入的（物理不按时间有序），BRIN 的过滤效果会打折--它吃的是「追加写时序表」场景，先确认写入模式再选它。

    create index idx_logins_bt   on user_logins (login_at);            -- B-tree
create index idx_logins_brin on user_logins using brin (login_at); -- BRIN

select relname, pg_size_pretty(pg_relation_size(relname::regclass)) as 大小
from pg_class
where relname like 'idx_logins%'
order by pg_relation_size(relname::regclass) desc;

explain (analyze) select count(*) from user_logins
where login_at >= current_date - 7;
  5. 用 tsvector + GIN 做一次商品名全文检索
    参考答案

    查询里的表达式必须和索引里的一字不差。'simple' 配置不做中文分词，'商品42' 整体算一个词，所以只能整词命中；生产环境中文全文检索要上 zhparser / pg_jieba 这类分词扩展。

    create index idx_products_fts on products
  using gin (to_tsvector('simple', name));

select name
from products
where to_tsvector('simple', name) @@ to_tsquery('simple', '商品42');
过关标准 能各举一个部分索引和表达式索引的真实用途。