数据库问答题 01：数据建模、SQL 与索引设计

结论： 内部高频关联优先考虑紧凑稳定的 bigint；需要分布式生成或不可枚举外部标识时评估 UUIDv7。

原因： bigint 占用较小且递增写入局部性好；UUIDv4 随机，UUIDv7 时间有序但仍为 16 字节。

边界： UUIDv7 暴露大致生成时间，也不能充当严格业务顺序；代理键不能替代业务唯一约束。

落地建议： 记录关联、生成位置、安全和索引体积需求，必要时采用内部 bigint 加外部 UUID 双键。

结论： 类型选择应由长度契约、大小写语义和排序规则决定，而不是假设 varchar 更快。

原因： text 与 varchar 的主要差异是长度约束；大小写不敏感唯一性还涉及 citext、表达式索引或不区分大小写排序规则。

边界： 邮箱本地部分理论上可能区分大小写，国际化和规范化规则也需由产品定义。

落地建议： 先定义规范化策略，再用 lower(email) 唯一索引、citext 或合适排序规则，并保存原始展示值。

结论： 代理主键只保证行身份，不保证订单号、租户内名称等业务事实不重复。

原因： 并发请求可能同时通过应用层查询，只有数据库唯一约束能在最终写入点原子仲裁。

边界： 软删除、多租户和可空字段会改变唯一范围，需要设计部分索引或组合键。

落地建议： 列出业务候选键，为其建立唯一约束，并把冲突转换成幂等或领域错误。

结论： 高频过滤、关联、排序、约束和统计分析的稳定字段应优先拆成类型化列。

原因： 普通列有明确类型、约束和统计信息，通常更容易建立针对性索引与稳定计划。

边界： 低频扩展属性仍可留在 JSONB；拆列会增加迁移与模型维护。

落地建议： 从查询日志识别热点路径，双写并回填新列，验证后切换读取并保留兼容窗口。

结论： 检查是否存在同一事实重复存储、局部依赖或更新异常，而不是机械追求更多表。

原因： 规范化的价值是让事实拥有单一维护位置，降低插入、更新和删除异常。

边界： 历史快照、搜索文档和读模型可能允许受控冗余，但必须有来源与重建方式。

落地建议： 用业务不变量审查字段依赖；先规范核心写模型，再以测量结果决定读侧冗余。

结论： 两者都可行，选择取决于币种、精度、计算规则和跨系统协议。

原因： numeric 表达十进制精确；bigint 紧凑高效，但需要明确每个值对应的币种与小数位。

边界： 不能用浮点数替代精确财务语义，多币种也不能假设统一两位小数。

落地建议： 在领域模型中固定货币与舍入规则，数据库增加非负和币种一致性约束。

结论： 典型索引为 user_id 等值列在前，随后是与排序一致的 created_at DESC 和唯一 id DESC。

原因： 该顺序既缩小用户范围，又可直接输出稳定顺序，避免深分页中的全量排序。

边界： 返回列可用 INCLUDE 覆盖，但会扩大索引且 Index Only Scan 仍依赖可见性映射。

落地建议： 用真实查询建立索引，改为 Keyset Pagination，并比较块读取、Heap Fetches 与写入成本。

结论： 适合谓词稳定且只关心少量热点行的场景，例如待处理任务或未删除记录。

原因： 只索引子集可以减小空间和维护成本，提高热点条件的缓存命中。

边界： 规划器必须证明查询条件蕴含谓词；参数化条件和不断变化的热点定义可能无法使用。

落地建议： 查看应用实际预备语句，在测试数据分布下确认计划，并监控子集比例变化。

结论： 索引包含全部返回列只是前提，PostgreSQL 还要确认堆页对当前快照全可见。

原因： 可见性映射未标记全可见时，执行器必须访问堆页验证元组可见性。

边界： 更新频繁的表会反复清除全可见标记，过多 INCLUDE 列还会增加索引写放大。

落地建议： 检查执行计划的 Heap Fetches、Vacuum 状态和更新模式，再决定是否保留覆盖列。

结论： 高选择性点查和范围排序通常用 B-tree；超大、追加且物理相关的数据可评估 BRIN。

原因： BRIN 保存块范围摘要，体积小但会读取候选范围并二次检查。

边界： 列值与物理位置相关性差时 BRIN 可能读取大量无关块，不能按表大小单独决定。

落地建议： 检查 pg_stats 相关性和实际 BUFFERS，按查询范围调整 pages_per_range。

结论： 先确认两侧连接键的基数，判断是一对多还是多对多，而不是立即加 DISTINCT。

原因： 多个明细集合直接连接会形成行数乘积，可能同时造成重复展示和聚合金额错误。

边界： DISTINCT 只删除完全相同的结果行，无法修复错误粒度和重复计算。

落地建议： 按目标实体先聚合或选择单行，再连接；为应唯一的键增加数据库约束。

结论： 使用确定性排序的窗口函数、DISTINCT ON 或 LATERAL Top-N，并包含唯一平局键。

原因： 只按 created_at 排序时相同时间的行没有稳定先后，可能在执行间变化。

边界： 不同写法性能取决于索引、用户数量和每组行数，不存在通用最快语法。

落地建议： 定义“最新”的业务规则，建立 user_id、created_at、id 联合索引并比较计划。

结论： 当需要只计算一次、隔离易变或昂贵表达式，或刻意阻止谓词下推时可显式物化。

原因： PostgreSQL 可能内联单次引用的无副作用 CTE，使外层条件下推。

边界： 物化会写中间结果并阻断部分优化，多次引用也要比较重复计算与物化成本。

落地建议： 分别测试默认、MATERIALIZED 和 NOT MATERIALIZED，依据执行计划而非旧版本经验选择。

结论： 如果只要求活跃记录唯一，可使用带 deleted_at IS NULL 谓词的部分唯一索引。

原因： 普通组合唯一约束会把已删除行继续纳入唯一判断，可能阻止重新创建。

边界： 查询必须统一包含活跃谓词，参数化和 NULL 语义也会影响索引可用性。

落地建议： 集中封装活跃记录条件，建立部分唯一索引，并测试恢复已删除记录时的冲突规则。

结论： 普通索引保存原列排序，函数或列侧转换可能无法直接映射到该访问路径。

原因： 执行器可能对大量行逐个计算后过滤，增加扫描与 CPU 成本。

边界： 并非所有函数都导致失效，规划器可化简部分表达式，也可建立表达式索引。

落地建议： 让参数类型匹配列，日期使用半开范围；固定表达式需求再建立表达式索引。

结论： 大 OFFSET 仍需找到并跳过前面行；顺序浏览可改用稳定排序键的 Keyset Pagination。

原因： 游标条件能从上一页边界继续索引扫描，工作量不随页码线性增长。

边界： Keyset 不擅长随机跳页，且必须包含唯一平局键并处理数据新增删除。

落地建议： 按 created_at、id 等稳定组合编码游标，明确前后翻页与一致性语义。

结论： 根据父表删除更新、子表连接和过滤频率决定；PostgreSQL 不会自动创建。

原因： 父键变更时数据库需要在子表查找引用行，缺索引可能扫描整表并扩大锁时间。

边界： 低频且很小的子表可能无需额外索引，索引也会增加每次写入成本。

落地建议： 从查询与删除路径验证，优先给高频关系和大子表建立引用列索引。

结论： 只为固定查询加速时表达式索引更直接；需要复用派生字段语义时可考虑生成列。

原因： 两者都把派生规则集中到数据库，但虚拟生成列读取时计算，存储生成列占空间。

边界： 生成表达式受不可变性限制，写入成本、权限与逻辑复制行为也需确认。

落地建议： 先定义派生规则的使用者，再比较查询可读性、索引需求和迁移成本。

结论： 批量加载、连接、LATERAL 或数据加载器可把按行往返合并为集合访问。

原因： 数据库往返、重复解析与单行索引访问会随父行数量放大。

边界： 一次巨大连接也可能造成行数乘积和过量数据，不能只追求查询次数最少。

落地建议： 按页面大小批量查询，验证结果粒度、总行数、网络体积和执行计划。

结论： 每个索引都占空间，并在 INSERT、DELETE 和相关 UPDATE 时维护和写 WAL。

原因： 更多索引会降低 HOT 更新机会、增加 Vacuum 和缓存压力。

边界： 唯一约束、低频关键任务和统计重置会让 idx_scan=0 不能直接证明无用。

落地建议： 结合使用周期、约束依赖、写入成本和大小审计，使用并发方式安全移除。