PostgreSQL / 系统课时

为 tenant_id 访问设计索引

为 tenant_id 访问设计索引

多租户表开启 RLS 后,绝大多数业务查询都会隐含一个 tenant_id = 当前租户 条件。索引设计要把这个隐含条件与显式筛选、排序一起考虑,而不是只给 tenant_id 单独建索引。本文用常见的“租户 + 状态 + 时间倒序”查询验证复合索引顺序。

学习目标

  • 能按租户等值条件、业务等值条件和排序列确定复合索引顺序
  • 能用 EXPLAIN (ANALYZE, BUFFERS) 验证 RLS 查询是否真正使用目标索引
  • 能识别只建 tenant_id 单列索引或盲目堆叠复合索引的局限

从真实访问模式决定列顺序

假设工单列表的高频查询是“当前租户下,只看 open 状态,按 created_at 倒序取最近 20 条”。虽然 SQL 没写租户条件,RLS 会补上等值谓词,因此一个自然的候选索引是 (tenant_id, status, created_at DESC)。

这个顺序有明确目的:tenant_id 先把访问范围收缩到单个租户,status 继续做等值过滤,created_at DESC 让索引本身提供所需顺序,从而避免读出大量行后再排序。

如果大量查询并不带 status,那么 (tenant_id, created_at DESC) 可能更通用;如果核心场景是按租户内业务唯一键点查,则应单独考虑 UNIQUE (tenant_id, external_id)。索引顺序必须跟查询组合对应,不能机械套公式。

建立数据并创建目标索引

下面在可丢弃测试库中用具备建表、建角色权限的管理角色创建 10 个租户、共 20000 行数据,并启用与应用会话变量绑定的 RLS。较大的数据集能让优化器更容易暴露索引设计差异。

sql
CREATE ROLE app_user NOLOGIN;
GRANT app_user TO CURRENT_USER;

CREATE TABLE tickets (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL,
    subject text NOT NULL
);

INSERT INTO tickets (tenant_id, status, created_at, subject)
SELECT
    (g % 10) + 1,
    CASE WHEN g % 4 = 0 THEN 'closed' ELSE 'open' END,
    timestamptz '2026-09-01 00:00+00' - g * interval '1 minute',
    'ticket-' || g
FROM generate_series(1, 20000) AS g;

ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON tickets
USING (tenant_id = current_setting('app.tenant_id')::bigint);

GRANT SELECT ON tickets TO app_user;

CREATE INDEX idx_tickets_tenant_status_created_at
ON tickets (tenant_id, status, created_at DESC);

ANALYZE tickets;

预期结果

text
CREATE ROLE
GRANT ROLE
CREATE TABLE
INSERT 0 20000
ALTER TABLE
CREATE POLICY
GRANT
CREATE INDEX
ANALYZE

ANALYZE 很重要:没有足够新的统计信息,优化器可能基于过时估算选择并非你预期的路径。

验证 RLS 与复合索引是否对齐

以租户 3 的应用会话执行列表查询。业务 SQL 只写状态和排序,租户过滤由策略注入。

sql
SET ROLE app_user;
SET app.tenant_id = '3';

EXPLAIN (
    ANALYZE,
    BUFFERS,
    COSTS OFF,
    TIMING OFF,
    SUMMARY OFF
)
SELECT id, created_at, subject
FROM tickets
WHERE status = 'open'
ORDER BY created_at DESC
LIMIT 20;

RESET ROLE;

预期输出的关键特征是使用目标复合索引,并把租户与状态同时放进 Index Cond;缓冲区数量和实际耗时会因环境不同。

text
Limit (actual rows=20 loops=1)
  ->  Index Scan using idx_tickets_tenant_status_created_at on tickets
        Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::bigint) AND (status = 'open'::text))
        Buffers: shared hit=<VALUE> read=<VALUE>

如果计划已经按该索引顺序读取,通常不会再出现单独的 Sort 节点,因为 created_at DESC 与索引尾部顺序一致。若仍看到顺序扫描、大量 Rows Removed by Filter 或显式排序,就要检查统计信息、数据分布和查询条件是否真的匹配这个索引。

不要把 tenant_id 单列索引当终点

单列 tenant_id 索引只能解决“先找到当前租户的大致范围”,却不能同时满足状态过滤与时间排序。对每个租户都拥有大量数据的表,这意味着数据库可能仍要读取许多租户内行,再做过滤或排序。

反过来,也不要为每个查询排列组合都创建复合索引。每个索引都会增加写入、VACUUM、缓存和存储成本。优先覆盖高频、延迟敏感的访问模式,再通过 pg_stat_user_indexes、pg_stat_statements 和实际计划判断低价值索引是否应删除。

最终验收标准不是“已经有 tenant_id 索引”,而是目标查询在真实应用角色下能把租户条件放进高选择性的访问路径,同时尽量少扫描、少过滤、少排序。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

为 tenant_id 访问设计索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通