数据库设计最佳实践

数据库设计最佳实践

一：表设计原则

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 等不是=的操作）条件，最左匹配终止，后续的字段就无法用来过滤或排序了。因此，第 14、15句 SQL 只能利用到 IDX 的(a, b)两个字段。可以利用索引的最左匹配原则减少索引数量。例如，我们定义了(a, b, c)索引，就没有必要再定义(a, b)与(a)索引；

4.5 索引与主键

InnoDB 存储引擎为每一个非主键索引在索引树的叶子节点上额外存储了主键，如果定义了索引 IDX(f)，就等同于定义了一个复合索引 IDX(f, PK)。但是查询分析器并不知道这个“潜规则”，有些可以走索引的场合却走了排序。因此，强烈建议所有非主键索引都在末尾显式添加主键字段，这不会带来额外的成本，但对查询分析器更加友好。
注：唯一索引是个例外，绝不能将主键加到它的末尾。为什么？

4.6 唯一索引

如果索引字段数据具有唯一性（除 null 外），强烈建议将其定义为唯一索引，这样不仅可以提升查询性能，还能强制约束业务数据不重复。

二：SQL 编写原则

1. 参数传递

禁止用字符串拼接 SQL 中的参数值，必须通过带参数的 PreparedStatement执行 SQL。
例如读取产品信息的 SQL：
select item_no from Product where id=? 或
select item_no from Product where id=#{id}（MyBatis 写法）
只是传入的 id 参数值不同，但 SQL 文本一模一样。这样做有 2 个好处：
（1） 对于相同的 SQL 文本，查询分析器可以直接重用缓存中的 SQL 文本分析结果与执行计划，提高了 SQL 执行效率；
（2） 避免黑客在参数值中注入危险代码。假如黑客给 password 参数赋值"’ or 1=1 or ‘1’='"，那么下列 SQL 就可以跳过登录检查：

select 1 from User where name = 'name' and password = 'password ';

2. SQL 函数

禁止在 where 或是 order by 的字段上使用 SQL 函数，这样无法利用任何索引。
例如按照创建时间过滤产品的 SQL：

select item_no from Product where date_add(create_time, 1 interval day)>now()

上述 SQL 在 create_time 字段上执行了函数，导致 MySQL 必须对所有记录的create_time 值进行函数运算后才知道它是否满足>now()的条件，无法通过索引结构快速查找。
如果函数不在条件字段上还是可以使用的，例如上述 SQL 可以改成：

select item_no from Product where create_time>date_sub(now(), 1 interval day)

因为不等式的右值在查询表数据之前就可以计算完成。

3. 反向条件

<>，NOT IN, NOT EXISTS, NOT LIKE 等反向条件无法使用索引，应尽可能将
其转成正向条件。

4. 全模糊匹配

禁止在过滤条件中使用全模糊 Like 匹配。
（1） like ‘%abc%’ 这样的全模糊匹配无法用到任何索引；
（2） like ‘abc%’ 这样的前缀匹配还是可以用到索引的；

5. 分页实现

当总记录数巨大、页数较多时，写出一个高性能的分页 SQL 不是一件容易的事情。
以下 SQL：

select id, name, price, description from Product order by create_time limit 200000, 100

的执行效率是非常低的，可以建一个索引(create_time, id)，然后用以下 2 个 SQL取代上述一个 SQL，执行效率有几个数量级的提升：

select id from Product order by create_time limit 200000, 100;
select id, name, price, description from Product where id in (?, ...) order by create_time;

注：如果第二个 SQL 不加 order by create_time，就要在应用代码中按照前一个 SQL 返回的 id 顺序对结果集排序，切记！
第一个 SQL 满足“索引全覆盖”的条件，执行效率很高；第二个 SQL 的查询时间稳定，不会随页码的增加而增加。
如果 id 是一个按产品创建时间自增的序列，那么有一种更加优化的方法：

select id, name, price, description from Product where id>? order by id limit 100;

每次记住上次查询到的最大一个 id 即可，执行引擎只需扫描 100 行记录。

6. 子查询

应尽量避免子查询，下面两个 SQL 返回同样的结果集，但查询性能差几个数
量级：
秒级：

select id from Favourite where product_id in (select id from Product where create_time>?)

毫秒级：
select f.id from Favourite f, Product p where p.create_time>? and p.id=f.product_id在编写子查询前，你必须了解 EXISTS 与 IN 子句的优劣后作出选择。EXISTS的执行过程是循环遍历外表的每一条记录执行子查询，若子查询没有返回记录则丢弃外表记录；IN 则先执行子查询得到一个结果集，然后对外表与该结果集做
Hash 连接。
因此可以得出如下结论：
（1） 如果内外表大小相当，EXISTS 与 IN 子句差距不大；
（2） 如果是一张小表 A 与一张大表 B，则需区分四种场景：
[a.1] select id from A where exists (select 1 from B where id=A.b_id)
– 性能好，EXISTS 遍历的 A 记录少，且可利用 B.id 索引
[a.2] select id from A where b_id in (select id from B)
– 性能差，虽然可以利用 A.b_id 索引，但子查询返回的结果集太大
[b.1] select id from B where exists (select 1 from A where b_id=B.id)
– 性能差，EXISTS 遍历的 B 记录太多，虽然可以利用 A.b_id 索引
[b.2] select id from B where id in (select b_id from A)
– 性能好，子查询返回的结果集小，且可利用 B.id 索引

7. 数据库特性语法

MySQL 提供了很多标准 SQL 的扩展，例如：

select ... for update
insert into ... on duplicate key update ...
insert ignore into ...
replace into ...

考虑“增加积分”这样一个功能，如果不采用特性 sql，则流程如下：
（1） select qty from Point where user_id=#{userId}
（2） 根据该用户的积分记录是否存在，应用程序决定后续采用 update 还是 insert。
这样做有很严重的数据正确性问题。在多线程并发的场景下，两个线程都执行了 select 后：
（1） 若发现积分记录不存在，则两个线程都插入了新的积分记录；（积分表应该在 user_id 字段建立唯一索引避免此类严重的数据问题）
（2） 若发现积分记录存在，则后面执行的那个线程覆盖了前一个线程的更新记录；

实际上这个功能用一个 SQL 就可以解决：
（1） 在 user_id 字段上建立唯一索引；
（2） insert into Point(user_id, qty) values(#{userId}, #{qty}) on duplicate key update qty=qty+(#{qty})
采用 select … for update 语法也可以解决，不过这个方法需要在事务中完成：

set auto_commit=0;
 select qty from Point where userId=#{userId} for update;
 -- 根据上述查询结果集是否存在执行 insert 或 update
 insert into Point (user_id, qty) values (#{userId}, #{qty});
 update Point set qty=qty+(#{delta}) where user_id=#{userId};
 commit;

select ... for update 相比 insert into ... on duplicate update key ...各有优缺点：
（1） 当积分记录不存在时，select … for update 还是会导致第二个并发事务失败（违反了 user_id 唯一约束），而 insert into ... on duplicate
update key ...都能更新成功；
（2） 当需要控制积分不能为负时，select ... for update 后应用程序可以直接判断，为负时就不必做后续更新操作。insert into ... on duplicate
update key …则需要在事务中做如下处理：

set auto_commit=0;
insert into Point (user_id, qty) values (#{userId}, #{qty}) on duplicate key update
qty=qty+(#{qty});
select qty from Point where user_id=#{userId};

– 根据修改后的 qty 确定提交还是回滚
commit 或 rollback
后者用了代价极大的事务回滚方式才达到和前者一样的结果，因此，如果积分为负是经常发生的事情，推荐用前者。

业务逻辑与事务原则

1. 只查询必要的数据

（1） 对于数据量较大的表，不分页而一次性查询出所有数据将带来灾难，你会不定期看到 OutOfMemoryError；
（2） 在 select 子句中明确指定每一个返回列是一种好的做法；

2. 避免在循环中查询数据库

（1） 合并查询条件，一次查询数据库得到最终数据是更好的做法；
（2） 嵌套循环中查询数据库更是要杜绝；
（3） 批量的增删改比循环的单条操作快上几个数量级；

3. 应用层缓存

（1） 变动不频繁、总量不大且经常批量访问的数据（例如：权限树、国家列表），可以考虑缓存在 JVM 内存，并且在数据发生变动时通过 MQ 告知各服务器刷新缓存。
（2） 总量大的数据（例如：产品、用户）考虑存储在 Redis 或 Memcached 等
集中式缓存；

4. 事务

（1） 事务是代价极大的操作，应尽量避免使用，除非数据完整性是第一优先级需求，例如余额操作；
（2） 事务中的处理过程尽量精简，处理过程需要的数据应该在事务启动之前完成准备；
（3） 事务中禁止远程调用，当外部系统不可用时会让这个事务挂起直至超时失败；
（4） 一个事务仅完成最核心的业务逻辑，其它相关处理可以考虑异步化。例如：下单时让客户成功创建订单是最重要的，而返积分、发送邮件可以后续完成，失败也不要紧只要能够重试成功；
（5） 当两个启用了事务的业务流程访问共同的多个资源时，尤其要注意避免死锁，访问资源的顺序要严格一致。假设 P1 与 P2 都需要在事务更新 Ta,Tb 表，P1 更新了 Ta 接着更新 Tb，而 P2 更新了 Tb 接着更新 Ta，此时就出现了死锁。死锁与一般的性能问题不一样，它是代码错误，必须予以解决。

5. 统计算法

（1） 是否可以通过一些状态标志实现增量统计？例如客户最近 30 天的订单金额。参见《订单统计功能设计》中 stat_date 字段的设计。
（2） 在从库上统计，将统计结果更新到主库；
（3） 统计的条件个数与组合变化大，是否可以通过 lucene 实现？

一个索引优化的例子

以下分析结果基于 MySQL 5.0，在其它版本上可能得到不同的分析结果。
表结构：

create table Notice (
 id int unsigned auto_increment,
 email varchar(64) not null,
 status smallint not null,
 contact varchar(64) not null,
 create_time datetime not null,
 primary key(id)
);

需求：
按照 email、status、contact 过滤 Notice，其中 email 与 status 条件必选，contact 条件可选，查询结果按照 create_time 排序。
满足需求的两个 SQL 如下：

（1） select email, status, contact, create_time from Notice where email='a' and status=1 and
contact in ('1', '2', '3') order by create_time;
（2） select email, status, contact, create_time from Notice where email='a' and status=1 order
by create_time;

现在我们要设计索引，让上述两个 SQL 的执行性能达到最优。

1. 分析

先看 SQL(1)，直觉上，我们感觉应该这么设计索引：
create index ix_notice_email on Notice(email, status, contact, create_time, id); – 所有非主键索引末尾都加上 PK
这个索引的字段顺序与 SQL(1)中的 where 及 order by 顺序是完全左匹配的，应该没问题吧？
很不幸，当我们执行 explain 时看到 MySQL 在最终排序时还是走了 filesort，而 filesort 是查询性能的大敌：

explain select email, status, contact, create_time from Notice where email='a' and status=1
and contact in ('1', '2', '3') order by create_time;

这是为什么呢？因为索引的顺序是这样的：字段 1 按照顺序排列、字段 1 相同的前提下字段 2 按照顺序排列……
如果 SQL 如下就没有问题：

explain select email, status, contact, create_time from Notice where email='a' and status=1
and contact='1' order by create_time;

注：MySQL 会将 contact in (‘1’)这种单值的 IN 优化为 contact=‘1’
前面 3 个条件确定了 create_time 的范围，在这个范围内的 create_time 本来就是排好序的，所以不需要额外的 filesort 排序。
但是由于 SQL(1)中 contact in (‘1’, ‘2’, '3’)条件的存在，分段取出的数据的create_time 已经不是排好序的了（当然，局部还是排好序的），因此需要放到排序缓冲区进行最终的排序。同样的道理：只要在索引的某个字段有范围查询（<,>,<=,>=,IN,OR,LIKE 等不是=的操作）条件，后续的索引字段就无法用于排序了。
再看 SQL(2)：

explain select email, status, contact, create_time from Notice where email='a' and status=1
order by create_time;

的分析结果也走了 filesort：
不过 SQL(2)走 filesort 的原因与 SQL(1)不同，它是因为 ix_notice_email 索引的第三个字段是 contact 而不是 create_time，不满足最左匹配原则，所以不能直接利用索引排序。

2. 优化

我们可以简单地调整一下索引设置优化查询性能，把索引中的 contact 与 create_time 的顺序换一下：

create index ix_notice_email on Notice(email, status, create_time, contact, id); -- 所有非主键

索引末尾都加上 PK
现在执行一下：

explain select email, status, contact, create_time from Notice where email='a' and status=1
and contact in ('1', '2', '3') order by create_time;
explain select email, status, contact, create_time from Notice where email='a' and status=1
order by create_time;

讨厌的 filesort 消失了：

3. 进一步优化

需要说明的是，如果满足 email='a' and status=1 and contact in ('1', '2', '3')条件的记录数很少，出现 filesort 未必是不可接受的（只是放到缓冲区进行了一次排序而已）。
在选择索引的时候，有时 MySQL 和我们想象中的过程并不相同。例如：

create index ix_notice_email1 on Notice(email, status, contact, create_time, id); -- 所有非主键索引末尾都加上 PK

create index ix_notice_email2 on Notice(email, status, create_time, contact, id); -- 所有非主键

索引末尾都加上 PK
结果对于下列 SQL：

explain select email, status, contact, create_time from Notice where email='a' and status=1
and contact in ('1', '2', '3'); -- 无 order by create_time

MySQL 选择了 ix_notice_email2 而不是 ix_notice_email1。而对于：

explain select email, status, contact, create_time from Notice where email='a' and status=1
order by create_time;

恰恰选择了 ix_notice_email1 而不是 ix_notice_email2（因而出现了 filesort）。这个问题只能通过 use index 显式指定索引解决。至于显式指定哪个索引，要根据实际情况做出判断。例如上述例子，如果满足 email=‘a’ and status=1 and contact in (‘1’, ‘2’, ‘3’)条件的记录很少，对于：

select email, status, contact, create_time from Notice where email='a' and status=1 and
contact in ('1', '2', '3') order by create_time;
select email, status, contact, create_time from Notice where email='a' and status=1 and
contact in ('1', '2', '3'); -- 无 order by create_time

来说，ix_notice_email1 可能是更好的索引。调整如下：

select email, status, contact, create_time from Notice use index(ix_notice_email1) where
email='a' and status=1 and contact in ('1', '2', '3') order by create_time;
select email, status, contact, create_time from Notice use index(ix_notice_email1) where
email='a' and status=1 and contact in ('1', '2', '3'); -- 无 order by create_time

订单统计功能设计

1. 需求

统计每个用户最近 30 天的订单总金额，存入 User 表的 amount 字段。

2. 简单实现

简单做法，一个 SQL 语句即可完成：

update User u, (select user_id, sum(amount) amount
 from OrderForm
 where paid_time>=date_sub(now(), interval 30 day)
 group by user_id) t
set u.amount=t.amount
where u.id=t.user_id

但是，如果用户基数很大、每日订单数很多，执行 SQL 期间 User 与OrderForm 表会被锁定，直接影响到线上下单。除了锁表问题之外，上述做法在逻辑上是有缺陷的，因为它只会更新最近 30天有订单的 User 记录，对于 30 天内没有任何活动的用户，我们必须将其 amount字段归零。偷懒一些的做法，可以在上述统计之前做个全表归零，然后再把统计
数据写进去：

update User set amount=0

同样的，当用户基数很大时，上述 SQL 长时间地锁定 User 表，影响线上业务。

3. 优化实现

仔细分析一下这个业务的特征：
（1） 如果 30 天内有订单，那么相应的 User 记录肯定会被更新；
（2） 对于 30 天内都没有订单的用户记录，仅需要归零一次即可，不用每天归零；
其中第二点非常重要，通常用户表存在大量的“僵尸”记录，即长时间没有任何行为的账号，我们没有必要每天更新它们。
针对已知的业务特征，我们调整设计，为 User 表增加一个字段 stat_date：

alter table User add column stat_date varchar(10);
create index ix_user_stat_date on User(stat_date, id); -- 所有非主键索引末尾都加上 PK

该字段有值表示上次统计时该客户有 30 天内的订单，值为统计日期，例如：2018-9-29；
统计过程：
（1） today=2018-9-30；
（2） 将 stat_date 有值的 User 记录归零：

update User set amount=0, stat_date=null where stat_date<#{today}

（3） 统计 30 天内有订单的用户及其金额：

select user_id, sum(amount) amount
from OrderForm
where paid_time>=date_sub(now(), interval 30 day)
group by user_id

（4） 批量更新用户记录：

update User set amount=#{amount}, stat_date=#{today} where id=#{userId}

其中，步骤 2 的 stat_date=null 是整个设计的关键所在。如果一个用户昨天统计时还有金额，今天统计没有，那么他的 amount 会被归零，且 stat_date 被设置为 null。假如这个用户后续再也不下单，成为了“僵尸”用户，那么他的stat_date 会一直保持 null，也就永远不会满足 stat<date<#{today}的过滤条件，对应的 User 记录就不会再被更新。
注：上述做法在归零与更新两个时间点之间金额不准确，如果业务上不能容忍，可以考虑在内存做缓存归并。