数据库设计最佳实践

数据库设计最佳实践

一：表设计原则

1. 表定义

（1） 尽可能做到单表查询，通过必要的字段冗余减少表关联的可能；
（2） 常用字段与大字段应拆分到不同表，例如：帖子内容应存到单独的表；

2. 主键设计

（1） 每个表都必须定义主键；
（2） 选择无业务含义的字段作为主键，以免业务数据订正时影响到引用该记录的其他表数据；
（3） 自增类型的主键可以满足大部分场景的需求，并且表数据与索引数据占用空间较小；
（4） 需要分库分表的表主键可采用 UUID，避免表数据合并时发生主键冲突；
注：GUID 类通过压缩编码将 40 位 UUID 减少到 26 位，推荐使用。
（5） 如果将自增主键暴露在外存在安全风险，例如竞争对手可通过每日订单ID 的增量估算我们的务规模，则订单主键宜采用 UUID；
自增字段必须是表的物理主键，一般情况下，我们也将它作为表的逻辑主键。但在特殊场景下，我们可以将自增字段当作一个自维护的排序字段，然后新增一个UUID 字段作为表的逻辑主键。例如：IndexCommand 表利用自增的sequence字段自动记录索引任务的先后顺序，而逻辑上的主键字段id是一个Java程序创建的UUID值。
每增加一个 where 或者 order by 条件时，确认一下是否有相应的索引定义，尽可能利用最左匹配原则重用索引，减少索引的个数。

3. 字段定义

（1） 数字类型比字符串类型占用空间小，查询速度更快；
（2） 字段长度够用就好；
（3） 用于过滤条件的字段通常要定义为 not null。is null 过滤条件用不到任何索引，应杜绝这种查询，可将相关字段指定为 not null，并用特殊值代表不存在。例如：评论表的 user_id 外键字段可以用0 表示用户不存在（只是逻辑上的外键，实际上考虑到性能因素并不在数据库中创建物理外键）；
（4） 数字类型字段一般定义为 not null，避免统计时编写 sum(ifnull(f1,0))这样的复杂语句；

4. 索引设计

4.1 索引个数控制

建立合适的索引可以极大提升查询的性能，但一个表上的索引不是越多越好，索引多了严重影响增删改性能。在某些情况下，例如向临时表大批量导入数据，我们往往会先删除索引，等到数据导入完毕再创建必要的索引。个别情况下，我们反其道而行之，允许某些字段为null。
例如：为确保一个订单任意时刻只能有一个进行中的操作，我们设计了 OrderAction 表，每一个改撤单操作都会产生一条 OrderAction 记录，其中 status 字段为 1表示该操作正在进行中，null 表示完成。我们为OrderAction 表创建了(order_id, status) 唯一索引，可以确保一个 order_id 只会有一个 status 为 1 的 OrderAction记录，但可以同时存在多条 status 为 null 的 OrderAction记录（之前已经完成的操作）。这里利用了唯一索引允许多个 null 值的特性。还可以利用 null 值不存于索引树的特性提升非 null值的查询性能。如果表中绝大多数记录的某字段值为null，而业务上只会查询它的非 null 值时，为该字段建立索引可以极大地提升查询性能。参见《订单统计功能设计》中 stat_date 字段的设计。

4.2 索引字段选择

（1）一般选择 where 与 order by 条件中出现的字段作为索引字段的候选；
（2）字段必须是选择性（或称离散度、区分度）较高的字段，即该字段的取值范围较大，一个反例：性别就不适合作为索引字段。在选择性很小的字段上建立索引，查询时用不上反过来却影响了更新性能；
（3）如果一个查询的 where、order by 与 select 相关字段都在索引中，即索引全覆盖，该查询性能将会很高，因为所需数据都在索引中，无需访问表数据；
注：索引全覆盖的查询，explain 时 Ex