表设计时索引和约束应该如何规划？

简化版

索引要围绕高频查询路径设计，约束要围绕数据正确性设计。主键、唯一约束、非空、检查约束、外键或逻辑外键用于保证数据质量；普通索引用于提升查询效率。不要把索引只当性能工具，也不要把所有字段都建索引。

详细版

常见规划：

主键：稳定唯一，尽量短；
唯一约束：手机号、订单号、业务编码等不能重复的数据；
非空约束：必填字段在数据库层兜底；
检查约束：状态值、金额范围、数量范围；
外键或逻辑外键：表达表间引用关系；
普通索引：服务 where、join、order by、group by。

设计顺序建议：

先明确数据正确性规则，建必要约束；
再根据高频 SQL 设计访问路径；
最后评估索引成本，避免冗余和过宽索引。

约束保证“不能错”，索引保证“查得快”，两者目标不同。

完整版教学

一、约束是数据库层的数据防线

很多数据错误不是因为开发不懂规则，而是入口太多：接口、后台、脚本、导入任务、修复任务都可能写库。如果规则只写在某一段业务代码里，很容易漏。

数据库约束能把底线下沉。例如订单金额不能为负，手机号不能重复，创建时间不能为空。这些规则应该尽量由数据库兜住。

二、唯一约束不只是索引优化

唯一约束常常被当作“唯一索引”，但它首先是业务正确性约束。

比如防止重复下单，不能只靠应用层先查再插。高并发下两个请求可能同时查不到，然后都插入。唯一约束能在数据库层阻止重复记录。

常见唯一字段：

用户账号；
手机号或邮箱；
订单号；
第三方支付流水号；
幂等请求号。

三、非空和默认值要谨慎

字段是否允许为空，要表达真实语义。NULL 表示未知或不存在，不应该用空字符串、0、特殊日期乱代替。

但也不能所有字段都允许 NULL。必填字段如订单号、用户 ID、创建时间、状态，应该加非空约束。

默认值也要谨慎。比如状态默认 INIT 很合理，但金额默认 0 可能掩盖上游漏传问题。

四、索引要服务查询路径

索引规划要基于真实 SQL：

where tenant_id = ?
  and status = ?
order by created_at desc
limit 20

这样的查询可能适合 (tenant_id, status, created_at)。如果只是给 tenant_id、status、created_at 分别建三个单列索引，未必能同时解决过滤和排序。

索引设计不是字段清单，而是访问路径设计。

五、索引也有成本

每个索引都要占空间，写入、更新、删除时也要维护。索引太多会：

拖慢写入；
增大存储；
降低缓存效率；
增加优化器选择复杂度；
造成冗余索引。

所以表设计时要定期复盘索引使用情况，删除明显无用或重复的索引。

六、检查约束和枚举值

很多状态字段只允许固定值，比如订单状态只能是 INIT、PAID、CANCELLED。可以用检查约束、枚举表或应用层枚举配合数据库约束。

如果状态流转复杂，仅靠字段约束不够，还要用状态机控制“从哪个状态能流到哪个状态”。数据库约束负责字段合法，业务状态机负责流转合法。

规划索引时还要把读写比例放进去。同样是订单表，如果每天写入 1000 万行、查询主要走订单号，给十几个低频字段都建索引会明显拖慢写入；如果是后台配置表，一天只改几十次但查询条件很多，适当多建几个索引的代价就小得多。

七、常见误区与追问

设计对象	主要目的	典型例子
主键	标识一行数据	`id bigint primary key`
唯一约束	防止业务重复	`unique(order_no)`
普通索引	加速访问路径	`(tenant_id, status, created_at)`
检查约束	限制字段合法范围	`amount >= 0`

易错点：约束优先回答“这条数据能不能存在”，索引优先回答“这条查询怎样更快找到数据”。两个问题都重要，但不能互相替代。

用一个查询推演索引顺序：如果订单列表每次按租户和状态筛选，再按创建时间倒序取 20 条，组合索引通常围绕下面的访问路径设计：

select *
from orders
where tenant_id = 8 and status = 'PAID'
order by created_at desc
limit 20;

如果表有 1000 万行，tenant_id=8 命中 50 万行，status='PAID' 命中其中 10 万行，再按 created_at 排序取 20 条，合适的联合索引能减少扫描和排序；三个单列索引不一定能同时满足过滤与排序。

误区：索引越多查询越快。 索引会增加写入维护成本和存储成本，冗余索引还会降低缓存效率。
误区：唯一索引只是性能优化。 它首先是业务正确性约束，例如订单号、支付流水号、幂等号不能重复。
误区：所有字段都应该设置默认值。 不合理默认值会掩盖上游漏传，例如金额默认 0 可能制造假数据。
追问：联合索引字段顺序怎么定？ 结合等值过滤、范围过滤、排序和区分度；面试里要拿具体 SQL 说明，而不是背“最左前缀”四个字。
追问：检查约束能替代状态机吗？ 不能。检查约束保证字段值合法，状态机保证从一个状态流转到另一个状态合法。
追问：如何发现冗余索引？ 对比索引前缀、查询计划和实际使用统计；例如已有 (a,b,c) 时，单独 (a) 可能冗余，但是否删除要看查询和排序需求。

八、加强记忆

约束管正确性，索引管访问效率。唯一约束、非空、检查约束是数据库底线；普通索引围绕高频 SQL 的过滤、关联、排序来设计。不要把约束都丢给应用，也不要给每个字段都建索引。

简化版

详细版