数据库设计最佳实践
一:表设计原则
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 时 Extra 列会出现 Using index。
4.3 索引字段排序
按照先 where 再 order by 最后 select 字段的顺序排列索引字段,如果有多个 order by 字段,必须严格按照它们的先后顺序排列;
4.4 最左匹配原则
假设我们为表 T 的 a、b、c 三个字段创建了一个复合索引 IDX(a, b, c),那么下列 SQL 与索引 IDX 的关系如下:
SQL | 可利用 IDX |
---|---|
select d from T where a=x and b=y and c=z |
是 (a, b, c) |
select d from T where a=x and b=y |
是 (a, b) |
select d from T where a=x |
是 (a) |
select d from T where b=y and c=z |
否 |
select d from T where b=y |
否 |
select d from T where a=x and c=z |
是 (a) |
select d from T where c=z |
否 |
select d from T where a=x order by b, c |
是 (a, b, c) |
select d from T where a=x order by b |
是 (a, b) |
select d from T where a=x order by c, b |
是 (a) |
select d from T where a=x order by c |
是 (a) |
select a, b, c from T where a=x and b=y |
全覆盖 (a, b, c) |
select a, b, c from T where a=x order by b |
全覆盖 (a, b, c) |
select d from T where a=x and b>y and c=z |
是 (a, b) |
select d from T where a=x and b>y order by c |
是 (a, b) |
从索引的第一个字段开始检查 SQL 中的 where 与 order by 条件,如果相应条件不存在则终止检查。我们看到第 1、2、3、6、8、9、10、11、12、13 句 SQL都可以匹配到 IDX 的全部或部分字段,因此可利用索引,其中第 12 与 13 句实现了索引全覆盖性能达到最佳;而第 4、5、7 句 SQL 匹配 IDX 的第一个字段 a就失败了,无法利用索引。只要在索引的某个字段上有范围查询(<,>,<=,>=,IN,OR,LIKE 等不是=的操作)条件,最左匹配终止,后续的字段就无