数据库schema设计与优化

1、 前言

对于数据库而言，在日常开发中我们主要的关注点有两块，一个是schema的结构设计，另一个就是索引的优化，这两块是影响我们最终系统结构和性能的关键部分，自然也是我们花费精力最多的部分；

本文主要介绍数据库设计中的一般原则和优化手段，包括数据库的一半范式、反范式设计、数据切分、数据路由与合并等等

2、 Schema设计的一般性原则2.1 概述

范式理论是关系型数据库设计的黄金法则，它提供了数据结构化的理论基础，有效地保证了数据的一致性，应该说，关系型数据库就是在范式的基础上才成长起来的。

数据库的范式有很多种，但是我们一般常用的只有第一、二、三范式和BC范式，这些范式直接在我们的数据库schema设计中得到体现，虽然有时我们根本就没有意识到。

2.2 第一范式和第二范式

在关系型数据结构的实体-关系模型中，是允许实体集和关系集的属性具有某种程度的子结构的，比如多值属性和组合属性；而第一范式则限制了这种存在，他要求所有的字段都是不可再分的、原子的，否则就违反了第一范式；第一范式主要是为了避免数据表结构过于复杂多样，使得上层操作的可抽象性和数据一致性遭到破坏。

第二范式简单的说，则是要求数据库中的每条记录都要有其对应的主键id存在，这样要求的主要目的是为了能够满足上层业务要求唯一标识每条记录的需求；其实数据库管理系统本身也有这种需求，部分数据库的索引结构就是基于此的，只不过这不是数据范式（data normal form）应该关心的东西；

2.3 第三范式

第三范式要求在在一个实体集中，不能存在一个非主属性可以作为该实体集中某个子集的候选主键，还可以表述为，不同的关系集中不能存在除了主键字段外的其他相同字段；这两者是等价的。

简单的说，第三范式主要是要求将实体集尽量拆分，将不同的业务单元属性字段拆分到不同的表里，然后通过关系表进行关联。

第三范式一定程度上减少了数据的冗余，降低了数据不一致的风险；通常情况下，大部分的schema都应达到第三范式的要求。

2.4 BC范式

BC范式是在第三范式的一个子集，它在第三范式之上做了更强的约束，即实体集中的任何子集都只能依赖于主键（注意，不是主属性，这一点是BC范式和第三方范式的差别所在），不能存在一个非主属性或非主属性集可以作为某个子集的主键。

BC范式在定义上和第三方是差不多，他最大程度的减少了数据冗余，不过在实际应用中，二者基本是一样的，只有在表的主键包含多个字段时，才会产生差异。

3、 反范式化设计3.1 数据冗余

这里介绍一个很经典的例子。我们所常见的大部分网站都会有会员系统，用户需要注册会员账号，然后登录才可以享受进一步的功能或服务。对于这张会员表，我们不妨命名为user_info表，一般会包括会员id，会员昵称，真实姓名，登录密码，性别，教育经历、工作经历、电话、电子邮箱、个人简介、兴趣爱好等信息，其他可能还包括跟该网站会员业务相关的一些字段（比如积分、经验值、充值账户余额等），一般的schema设计是这样的：

表名	user_info	数据量		100w
功能描述	主要存储用户的基本信息，如id、姓名、年龄、联系方式等；
字段名（新增）	数据类型（精度范围）	空/非空	缺省值	字段含义
id	BIGINT(20)	N		用户id
name	VARCHAR(32)	N		姓名
gender	TINYINT(4)	N		性别
age	INT(8)	N		年龄
tel	VARCHAR(16)	Y		联系电话
email	VARCHAR(64)	Y		电子邮箱
school	VARCHAR(32)	Y		就读学校
company	VARCHAR(32)	Y		供职机构
interest	VARCHAR(512)	Y		兴趣爱好
gmt_create	DATETIME	N		记录创建时间
gmt_modified	DATETIME	N		记录修改时间

这张表结构看起来是没什么问题的，而且在初期业务简单，用户数量少、访问量低的情况下也确实都ok的；

但是随着网站访问量不断增大，每天登录的用户越来越多，user_info表的访问量越来越大，瓶颈慢慢出现；

我们经过进一步分析发现，在处理用户登录的过程中，我们需要的操作很简单，就是根据用户输入的会员昵称查询相应记录，进而判断密码是否正确；而会员登录后，也仅会显示用户的昵称或真实姓名。

整个过程前后所涉及到的字段，只有会员id、昵称、密码和真实姓名等3、4个，而user_info表中其他绝大部分字段，诸如教育经历、兴趣爱好等，在这个过程中是根本不需要的，但他们的体积却比较大，每次用户登录都要读取，白白浪费时间和带宽。

那么我们为什么不把这部分字段单独拿出来放到一张新的表里呢？我们不妨建一个login表，里边只有会员id、会员昵称、登录密码以及会员真实姓名，每次用户登录，都只读取这张login表，这样不但数据库读取记录的时间会缩短，也可以将应用和数据库之间网络传输量降到最低，完全符合我们前一篇文章里所提到的将结果集缩减到最小的原则。

表名	login	数据量		100w
功能描述	主要存储用户的登录信息；
字段名（新增）	数据类型（精度范围）	空/非空	缺省值	字段含义
id	BIGINT(20)	N		用户id
name	VARCHAR(32)	N		姓名
password	VARCHAR(128)	N		登录密码的md5值

但是有人不禁要问，这样不是产生数据冗余了吗？是的，同一份数据在user_info表和login表中各存了一份，确实有可能存在不一致的情况，应用程序中每次新增或修改记录，都可能需要访问两张表，这无形中增加写操作的成本；所以这种优化方式也不是绝对的，要根据具体的场景区别对待，比如在上述这种读写比例非常高的场景中，我们这样处理就是合适的；而在一个同样读写比例比较高，但是对数据一致性要求也非常高的系统中，对数据进行冗余存储就需要花费额外的精力去处理可能存在的数据不一致情况。

3.2 去关联化

Join是我们在数据库操作时经常会使用的一个关键字，其作用就是将两张表拼接起来，然后过滤出符合条件的记录；但是在拼接的过程当中，是采用的是笛卡尔积的方式，其原理图如下：

在MySQL中，Join的实现方式为Nested Loop Join ，主要以驱动表结果集作为基础数据进行循环，有点类似编程语言中的双层for循环嵌套；这种方式实现最最简单，性能也基本可以接受；其他数据库还提供Hash Join、Sort Merge Join等Join方式，都是针对不同场景有性能提升，很难简单的说谁好谁坏。

从笛卡尔积的基本原理上来看，不管采用何种实现算法，表间join都是非常耗时耗力的操作，尤其是当其中一张表或两张表的数据量都非常大时更是如此！

所以我们在做schema设计的时候，应该尽量避免join的出现，通过一定的字段合并和数据冗余将这种需求降到最低。

3.3 去一致性约束

在传统应用中，数据一致性是很重要的一点，但是在很多互联网应用对数据的一致性要求并不是很高；比如说数据库的外键约束、唯一性约束等等，当记录增删时，数据库都会去检查相关的条件是否满足，这些都是需要额外开销的，有时候其开销甚至会超过当前操作本身。

在数据库层去掉这些约束并不意味着系统中就不需要，我们其实可以将这部分约束校验提前到应用程序中；前面我们提到过，应用程序的扩展相对来说是比较容易的，所以我们何尝不充分利用一下来为数据库减负呢？

3.4 去SQL化3.4.1 数据库底层数据存储

我们使用的关系型数据库虽然提供了表、字段、索引、sql等种种特性，但是归根到底，其最底层的数据存储仍然是<k,v>格式，比如MySQL，其数据的存储都是有由下层存储引擎来负责的，虽然在逻辑结构上我们看到的是一张张表和其中一个个分散的字段，但是实际上每条记录在物理存储上都是一个整体；所谓的表结构、字段这些只是上层来维护的元数据，对底层来说，其实没有字段的概念，就是一条条的物理记录；

比如说我们前边的user_info表，我们如果执行类似下边的一条sql：

select user_id,user_name from user_info where age =8;

虽然我只想返回user_id和user_name两个字段，但是存储引擎还是要将所有满足的age=8的记录取出来，然后在分别扫描每条记录，取出我们需要的两个字段数据返回；

所以我们可以看到，所谓的关系数据库只不过是在<k,v>系统的上层做了进一步的封装，比如说权限验证、sql解析、二级索引等等；

3.4.2 MySQL数据库的层次结构

MySQL虽然是个开源数据库，体积也远小于DB2、Oracle这些商业数据库，但是一个RDBMS必备的结构MySQL却一样也不少，正所谓“麻雀虽小，五脏俱全”。

下边我们就来看下MySQL到底有怎样的层次结构；

MySQL从上到下，可以分为三层，第一部分为客户端，中间部分为数据库管理系统（DBMS），最底层为存储引擎层；MySQL使用独立的存储引擎，可以方便切换，这一点和其他数据库有所不同；

3.4.3 哪些是可以不要的

前面给出了MySQL数据库的层次结构，其他数据库结构也是类似的；那么不妨从上到下好好想一想，整个体系中哪些东西其实不是我们必需的？

首先，Client端肯定是需要的，我们总要连接数据库的，那么来看第二层DBMS层，其中的连接管理是需要的，不管什么样的数据库总要处理客户端连接；如果一个数据库在安全网络环境中，并且只有我们自己在用的话，那么就不需要用户权限验证了，这一层可以去掉；为了减轻数据库负担，我们也不使用sql，需要的逻辑可以直接使用API在应用层实现，那么这一层也可以省掉；而对于访问控制模块，因为数据库为我们自己所独享，或者都是可信任的使用者，所以这一层也不那么重要了，或者说是可以进一步简化的；

我们再来往下看存储引擎层。索引的话可以很好的提高数据的查询效率，这一点不管在什么类型的存储系统中都适用，那么这个功能我们需要保留，但是如果我们只想要个<k,v>存储的话，那就可以只保留主键索引好了；事务管理呢，最多我们不用好了，不麻烦数据库了；锁的话即使我们自己使用也会有很多的并发访问控制，那么需要保留；

经过这样的精简之后，我们的数据库还剩以下这些东西；

3.4.4 NoSQL存储系统

前面我们提到了去关联化、去一致性约束以及数据冗余等等；3.1中我们讨论了关系型数据库的本质，其实就是在<k,v>存储之上又封装和增添了诸多的功能，而3.3中我们又对关系型数据库进行了裁剪，去掉了一些我们不必需的；经过所有的这么步骤，剩下的部分（图1-1所示），基本上就是现在NoSQL存储了；

所以，NoSQL不是什么高级的东东，而是关系型数据库做了退化，回归到了其基础本源；而分布式的特性，也并不是NoSQL独有的，关系型数据之所以难有分布式的架构，本质就是因为其选择了“向上生长”，上层的复杂特性制约了其“横向”生长的能力；而NoSQL只不过是在<k,v>之上，选择了另外一个生长方向而已。

由于去掉了上层的“高级”特性，NoSQL系统的性能有了比较大的提升，同时由于横向生长，其存储能力也有了很大的增强；

所以当我们的系统受制于关系型数据库的性能时，不妨放弃schema模式，来尝试一下“自由”的NoSQL数据库。

4、 数据扩展4.1 Scale Up和Scale Out

Scale up主要是指增强数据库的单机处理能力，比如说提高CPU、内存、硬盘、网卡等硬件配置；其实scaleup这个概念包括我们后便将要提到的scale out，不光对于数据库，对于大部分的软件系统都适用的，只不过具体的实施方案会有所差异；

因为scale up主要是增加机器硬件配置，相对来说比较简单，也不需要迁移数据，对于技术人员来说没有新的东西，所以对于中小型系统来说非常合适；

scale out是指横向的扩展，就是增加数据库实例或节点来增加整体的处理能力，这里边还包括两种方式，一种常见的数据复制，比如MySQL的Replication，Oracle rac等；另一种横向扩展的方式是进行数据切分，也就是说把本应该放在一台机器上的数据切成几部分，分别放在不同的节点上（并不是相同数据的备份），这样访问的压力就会分散到多个节点。

scale out的优点是成本低，如果整个系统都使用PC Server的话，可以用很低的成本来支撑海量的数据和高并发；而且一般来说，这种扩展是线性的，即有多少机器，就能支撑多少的数据和多大的访问量，但通常这需要有个比较好的数据系统架构或中间件系统来支持；

以淘宝的交易库来说，原来使用的都是IOE（I指的是IBM的小型机，O指Oracle数据库，E即EMC的高端存储），采用主备双机方式，用Orcle和高端存储来支撑每天的巨大访问量，但是整个系统的成本也非常高（据说一套下来要2000多万），而经过去IOE以后，通过使用MySQL和廉价的PC Server（线上16主16备，双11的时候扩展为32主32备），通过数据切分和Replication机制，不仅整个数据库的在线处理能力提高了4倍，成本也降为原来的1 / 8不到，同时数据的安全性和容灾能力也得到了保证；

但是数据库技术不是本文关注的重点，下边将主要介绍和我们日常开发联系更为紧密的数据切分知识。

4.2 数据切分4.2.1 为什么要切分数据

对于这个问题，可能有人会觉得的是废话：你前边不是已经说过了吗，干嘛还问？没错，扩展系统，支持更大访问和并发和替换商业数据库，降低成本这两个确实我们进行数据切分的主要原因；但这样来说太笼统了

对于数据库来讲，不管是商业的还是开源的，其单库和单表的承载能力都是有限的；在通常的业务场景下（写操作和读操作比较均衡），普通的pc server上，MySQL数据库单表数据记录的承载能力在千万级（数据量在TB级别）左右，TPS大概在千这个级别（具体测试环境和数据可参考另一篇文档）；当然，我们在这里没有必要苛求具体的数据，因为这和具体业务场景、实际读写比、服务器硬件配置、具体的数据引擎、MySQL的配置参数等相关，比如说，如果只是将MySQL作为日志数据库（基本只有写操作，不需要建索引），单表的支撑能力可达到上亿甚至是十亿的级别；但这毕竟只是种极限场景，不能拿来用作一般场景的参考。

另外需要说明的是，前面说的单库单表的承载能力有限，并不是说当数据量超过这个上限时，数据库就会马上崩溃或者拒绝服务，而是在这种情况下，数据库的整体的读写性能就会急剧下降，甚至于一条很简单的sql查询也可能会超时，如果本来就是负载很重的一张表，那与崩溃无异了！

4.2.2 数据切分基本原则

数据切分所要遵循的原则主要有两点：

第一就是将数据均衡分散在多个处理节点上，其实这里主要强调的是均衡，但这个均衡并不可简单的看成是每个节点上库（表）数量相等或是记录条数一样；而更多是要从数据访问和处理能力的均衡上去考虑，主要原则有以下几条。

a、 不同节点业务关联度要低

b、 同一节点业务类型尽量一致

c、 数据（访问量）要均衡

d、 数据的一致性和安全性

4.2.3 垂直切分

垂直切分主要是根据表中数据的业务类型，将不同业务的数据放在不同的表或者数据库中；

在系统结构设计中我们会经常提到一个原则，叫做高内聚、低耦合，其实这个原则在数据库的垂直切分中同样适用；所以在做水平切分的时候要尽量做到不同功能的表关联尽可能少，这不但可以减少SQL语句中的join出现的几率，同时后续表结构变更时也更容易；

对于中小型系统来讲，很多人喜欢各种复杂功能一个sql搞定，甚至有些还使用存储过程，这样对前端程序来讲确实方便了许多；但是这种方便也往往会给我们带来伤害，尤其是当数据量和访问量都增长比较快的情况下，你会发现几条慢查询可能让你的整个系统直接失去响应！

4.2.4 水平切分4.2.4.1 什么是水平切分

垂直切分主要是按照系统功能来切分的，所以同样是有瓶颈存在的，比如说，某一项功能比其他的要复杂许多，或者数据量要大很多，很难再进一步拆分，这样就不适合垂直切分了；这在实际的业务场景中是存在的；

比如说淘宝的订单系统，虽然功能看起来简单，但是由于交易类型复杂，中间状态繁多，耦合性非常强，加之订单数量巨大，其系统是很难再进行功能上的剥离的；但是这个系统属于底层基础系统，为了支撑巨大的访问量，只能采取水平数据切分方式来解决高并发问题。

水平切分就是我们通常所说的分库分表，主要是将一张表中的数据按照某个字段（比如说用户id、商品id、订单id等）分散存储在多张结构相同的表中，这样访问的压力就会分散到多张表上；

在平时的开发中，数据的水平切分比垂直切分应用的更多，因为一般来讲，需要进行垂直数据切分时，通常系统的规模和负载都已经很大了（尤其是使用oracle的时候），这时候我们最先实施的，往往是通过RPC或者服务化将应用分成多个系统，底层数据表之间的依赖，很自然的会转化成系统间的接口依赖，所以这个时候，数据库当然也会跟着分开了，不需要太刻意其考虑垂直切分这个概念了。

4.2.4.2 水平切分优点

成本固定；只要在系统设计之初就指定好分表数量和分表字段，不管是要分成8个库1024张表，还是16个库4096张表，其成本都是一样的；

解决了单表瓶颈问题；水平切分方式很好的解决了垂直切分时可能存在的单表瓶颈问题，只要在开始时做好容量预估，设定适当的切分数量，基本可以满足业务很长一段时期内的存储需求；

对事务透明；分表对于数据库来说是透明的，所以原来的事务该怎么做还怎么做。

4.2.4.3 水平切分缺点

水平切分的虽然很有效，但是其缺点也不少，主要如下：

sql路由变得复杂；每次在做了切分的库或表上执行sql时，都必须要明确指出目标分库或分表；这无形中增加业务方的成本。

分表字段单一；水平切分只能使用单一的分表字段；如果业务中有需求按照非分表字段进行查询，则会变得很困难，只能扫描所有的表；一个解决方案就是，按照不同查询字段做多份分表；但是又要花费精力去解决数据冗余问题。

join操作变得困难；显而易见，以前单表间的join放到多表上是无法执行的，这时候我们最好还是选择放弃；

二次扩展比较麻烦；如果分表之后，我们数据增长太快，又达到存储瓶颈了，就面临着二次拆分的命运；但因为路由规则发生了改变，迁移数据的麻烦是避免不了的；所以要有必要的手段去保证迁移数据时系统依然能够对外提供服务。

4.2.4.4 水平切分注意事项

在做水平切分后，我们的部分业务实现方式或是开发方式可能需要随着改变；以下是我们再做水平切分时需要注意的点，主要是针对水平切分的弱点而言的：

根据业务场景确定切分字段；业务中根据什么字段去查询，就用什么字段去分表；

避免热点数据问题；通常切分时采用的hash算法理论上可以保证数据的分散性，但在实际应用中，仍可能遇到数据热点问题；理论是理论，实际归实际，没有绝对的，不要以为分了表就万事大吉了。

分表宜多不宜少；这样做主要是为了尽量避免后期可能遇到的二次拆分，因为前面我们说过，拆成1024张表和拆成4096张表的操作成本是一样的。

避免分表上的join操作；在分表的缺点中我们就提到过，join在水平切分场景下会很困难，所以在业务实现中，对这种情况能避免就避免，哪怕牺牲一些简洁性，多绕几步。

避免非分表字段查询；道理也是一样的，切分后只能按照切分字段进行查询；如果非要按其他字段查询，那就冗余数据吧。

4.2.5 其他切分方式

上边我们提到的是我们最常见的切分方式，其他还有一些切分方式不太“规矩”，它们具有部分水平切分或垂直切分的特点，但又很难直接归入到某一分类中；正如那句老话所言：山无定势，水无定形。

4.2.5.1 逻辑切分

逻辑切分类似于垂直切分，也是将数据按照不同业务逻辑拆分到不同的表中；但这种方式追求的不是单纯的负载均衡，而是不同业务的数据隔离；比如说某部分数据读多些少，某部分数据则可能读少写多；这样进行隔离后，可以充分的利用不同的业务特点进行优化，比如说创建不同的索引结构，使用不同的查询方案等等；前面我们提到的数据冗余其实就属于这类切分方式。

另一个典型的案例就是数据倾斜；比如说对于淘宝上的卖家来说，有些卖家比较小，可能他的店铺中的宝贝只有几十数百个，但是有些品牌卖家或热门店铺，他们的宝贝可能有上万甚至是数十万，再加上未上架的或是已下架的历史宝贝，数量会更多！这两类用户的处理，如果使用相同的逻辑，很可能会产生问题；但是如果我们将这部分大卖家提出来放在另外的节点上来处理，效果可能会好很多。

4.2.5.2 时间切分

时间的切分主要是将记录按照创建时间的先后顺序，放在不同的表中；mysql的分区表便提供了这样一种切分的机制；分区表对外逻辑上表现为一张表，但是实际物理存储上是多张表，不同的表对应不同的时间区段；用户可以设定创建新分区表的周期，比如说一天或者是一周，在某个时间点插入的记录便会写入对应的分区表；读取时，会从最近一个分区开始扫描，直到找到目标记录。

4.2.5.3 冷热切分

冷热切分主要是按照数据的访问频率对数据进行隔离，有点类似于前边我们提到的逻辑切分。

淘宝的交易历史库就是典型的代表。淘宝的订单总量巨大，每天产生的订单数量也非常多；如果我们为用户提供订单查询服务，那么巨大的订单量会使我们的服务性能下降很多；但是实际中我们会发现，用户很少会去查询自己三个月以前的订单，那我们不妨将用户三个月以前的订单拿出来单独提供存储和查询服务，这样就可以使用户访问频繁的订单表数据量变得很小，从而可以提供更高的处理性能。

4.2.5.1 体积切分

按体积切分主要是按照数据表的尺寸或是记录条数进行切分，这种切分一般适用于业务类型单一，对表的体积可以很好预估的情况，主要是为了避免表的尺寸过大而是性能下降。

一般来说，只有日志类型的表适用于这种切分方式，通常是以自增id作为判断标识，因为基本不存在删除和修改操作，所以可以很好的控制体积；不过这种情况下大部分系统更倾向于使用按时间切分的方式，所以按体积切分的实际应用很少。

4.3 数据路由与合并

当我们进行了分库分表之后，一个我们不得不面对的问题就是sql的路由。当我们将数据分散存储在诸如名为test_0000、test_0001的分表中时，我们会发现，必须要对原来的程序代码或数据库进行相应的改造，否则程序将找不到正确的库或表；这种情况下，通常的解决方案有三种：

4.3.1 修改程序

这种方案，只需将程序里涉及到数据库读写的代码按照分表逻辑进行改造即可，技术上比较简单，不需要额外的软件或者是技术的支持；缺点是对业务代码侵入性强，可能涉及到多个地方的修改，工作量较大，而且后期的修改和维护成本也比较高。

4.3.2 修改数据库

这种方法对程序透明，是的上层业务逻辑不需要考虑分库分表的读写规则，应用代码可以保持不变；

缺点是需要修改数据库系统，或者以模块、插件等形式对数据库进行增强，开发成本和后期维护成本都很高，部分商业数据库根本无法自行修改。

4.3.3 使用中间层代理

这个应该是目前采用最多的方式；其优点是对上层业务逻辑和底层数据库都透明，只需要对应用的访问层进行简单改造，即可快速切换到拆分之后的数据库，不管是开发人员还是数据库管理人员都不需要增加多少工作成本；

其缺点是技术门槛较高，需要专门的人员来开发和维护；功能受限，部分在单库单表下的常见操作在这种中间层代理的方式下会变得麻烦，比如说跨库、跨表join，全局数据分组与排序等。

对于以上几种数据路由方案，不同的场景可能会有不同的选择，具体要看自身的业务需求，技术储备以及实施成本等。

4.4 Scale up之闪存存储

以上通篇几乎都在介绍如何对数据库进行水平扩展和数据切分，其实有时候我们不必搞得如此复杂，如果仅通过简单的硬件升级就能满足业务增长对数据库的要求，我们何乐而不为呢？

我们都知道，普通的服务器，甚至是很多高端存储，都是基于机械硬盘存放数据的；而机械硬盘最大的缺点就是速度上存在物理上限；

一般的机械硬盘读写数据都要经过寻道操作，主要由两个步骤组成：一是移动磁头，二是转动盘片；这两者都属于机械操作，前者一般在2ms~4ms，而后者，对于现在常用的SATA盘或SAS盘来说，一般会在1ms以内，所以，一次寻道操作要耗费大约5ms左右的时间，而对于一些老式硬盘来说，这个时间可能要达到10ms甚至更多！也就是说，单块磁盘随机读写的iops只有100左右，这简直低的不可忍受，再牛B的系统也被拖死了！

对于一个初级团队，再没有很多技术积累的情况下，盲目的进行水平扩容往往会给自己带来更高的维护成本和更差的系统稳定性，所以这个时候我们不妨尝试一下数据库性能提升的又一利器——闪存存储；毕竟我们不断对系统进行优化的目标就是为了获得更高的性能！