【2. 表结构设计和数据类型优化】

表结构设计和数据类型优化

数据库表设计

​ 良好的表结构设计是高性能的基石，应该根据系统将要执行的业务查询来设 计，这往往需要权衡各种因素。糟糕的表结构设计，会浪费大量的开发时间，严 重延误项目开发周期，让人痛苦万分，而且直接影响到数据库的性能，并需要花 费大量不必要的优化时间，效果往往还不怎么样。

​ 在数据库表设计上有个很重要的设计准则，称为范式设计。

范式设计

什么是范式？

​ 范式来自英文 Normal Form，简称 NF。要想设计—个好的关系，必须使关系 满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求 得严格。满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入 (insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟，不仅给数据 库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信息。

​ 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三 范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又 称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上 进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来 说，**数据库只需满足第三范式(3NF）**就行了。

数据库设计的第一范式

​ 定义： 属于第一范式关系的所有属性都不可再分，即数据项不可分。

​ 理解： 第一范式强调数据表的原子性，是其他范式的基础。例如下表

​ 列不可再分

数据库设计的第二范式

​ 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二 范式（2NF）必须先满足第一范式（1NF）。

​ 第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。 通常在实现来说，需要为表加上一个列，以存储各个实例的惟一标识。例如员工 信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是惟一的，因 此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。

​ 也就是说要求表中只具有一个业务主键，而且第二范式（2NF）要求实体的 属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的 属性。什么意思呢？

​ 有两张表：订单表，产品表

​ > 问题: 订单时间部分依赖于订单ID而和产品id没有任何关系,但是订单id有不能唯一标识一行记录

​ 一个订单有多个产品，所以订单的主键为【订单 ID】和【产品 ID】组成的 联合主键，这样 2 个组件不符合第二范式，而且产品 ID 和订单 ID 没有强关联， 故，把订单表进行拆分为订单表与订单与商品的中间表

​ 不用使用联合字段作为表的主键,消除部分依赖

数据库设计的第三范式

​ 指每一个非非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第 二范式的基础上消除了非主键对主键的传递依赖。例如，存在一个部门信息表， 其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员 工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息 再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该 构建它，否则就会有大量的数据冗余。

​ 其中

​ 产品 ID 与订单编号存在关联关系

​ 产品名称与订单编号存在关联关系

​ 产品 ID 与产品名称存在关联关系

​ 如果产品 ID 发生改变，产品姓名也会改变；产品名称发生变化，这样不符 合第三范式，应该把产品名称这一列从订单表中删除

​ 消除非主键对主键的传递依赖, 消除非主键的冗余

范式说明

​ 真正的数据库范式定义上，相当难懂，比如第二范式（2NF）的定义“若某 关系 R 属于第一范式，且每一个非主属性完全函数依赖于任何一个候选码，则关 系 R 属于第二范式。”，这里面有着大堆专业术语的堆叠，比如“函数依赖”、 “码”、“非主属性”、与“完全函数依赖”等等，而且有完备的公式定义，需 要仔细研究的同学，请参考这本书：

反范式设计

什么叫反范式化设计

​ 完全符合范式化的设计真的完美无缺吗？很明显在实际的业务查询中 会大量存在着表的关联查询，而大量的表关联很多的时候非常影响查询的性能。 所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设 计范式得要求进行违反。允许存在少量得冗余，换句话来说反范式化就是使用空 间来换取时间。

范式化和反范式总结

范式化设计优缺点

​ 1、范式化的更新操作通常比反范式化要快。

​ 2、当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。

​ 3、范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。

​ 4、很少有多余的数据意味着检索列表数据时更少需要 DISTINCT 或者 GROUP BY 语句。在非范式化的结构中必须使用 DISTINCT 或者 GROUPBY 才能获得一份唯 一的列表，但是如果是一张单独的表，很可能则只需要简单的查询这张表就行了

​ 范式化设计的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的 表上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引 策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中 本可以属于同一个索引。

反范式化设计优缺点

​ 1、反范式设计可以减少表的关联

​ 2、可以更好的进行索引优化。

反范式设计缺点也很明显，1、存在数据冗余及数据维护异常，2、对数据的修改 需要更多的成本。

实际工作中的反范式实现

性能提升-缓存和汇总

​ 范式化和反范式化的各有优劣，怎么选择最佳的设计?

​ 而现实也是，完全的范式化和完全的反范式化设计都是实验室里才有的东西， 在真实世界中很少会这么极端地使用。在实际应用中经常需要混用。

​ 最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特 定列。

​ 比如从父表冗余一些数据到子表的。前面我们看到的分类信息放到商品表里 面进行冗余存放就是典型的例子。

​ 缓存衍生值也是有用的。如果需要显示每个用户发了多少消息，可以每次执 行一个对用户发送消息进行 count 的子查询来计算并显示它，也可以在 user 表用 户中建一个消息发送数目的专门列，每当用户发新消息时更新这个值。

​ 有需要时创建一张完全独立的汇总表或缓存表也是提升性能的好办法。“缓存表”来表示存储那些可以比较简单地从其他表获取（但是每次获取的速度比较 慢）数据的表（例如，逻辑上冗余的数据)。而“汇总表”时,则保存的是使用 GROUP BY 语句聚合数据的表

​ 在使用缓存表和汇总表时，有个关键点是如何维护缓存表和汇总表中的数据， 常用的有两种方式，实时维护数据和定期重建，这个取决于应用程序，不过一般 来说，缓存表用实时维护数据更多点，往往在一个事务中同时更新数据本表和缓 存表，汇总表则用定期重建更多，使用定时任务对汇总表进行更新。

性能提升-计数器表

​ 计数器表在 Web 应用中很常见。比如网站点击数、用户的朋友数、文件下 载次数等。对于高并发下的处理，首先可以创建一张独立的表存储计数器，这样 可使计数器表小且快，并且可以使用一些更高级的技巧。

​ 比如假设有一个计数器表，只有一行数据，记录网站的点击次数，网站的每次点击都会导致对计数器进行更新，问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行，会严重限制系统的并发能力。

​ 怎么改进呢？可以将计数器保存在多行中，每次随机选择一行进行更新。在 具体实现上，可以增加一个槽（slot)字段，然后预先在这张表增加 100 行或者更 多数据，当对计数器更新时，选择一个随机的槽（slot)进行更新即可。

​ 这种解决思路其实就是写热点的分散，在 JDK 的 JDK1.8 中新的原子类 LongAdder 也是这种处理方式，而我们在实际的缓冲中间件 Redis 等的使用、架 构设计中，可以采用这种写热点的分散的方式，当然架构设计中对于写热点还有削峰填谷的处理方式，这种在 MySQL 的实现中也有体现。

反范式设计-分库分表中的查询

​ 例如,用户购买了商品,需要将交易记录保存下来,那么如果按照买家的维度分表,则每个买家的交易记录都被保存在同一表中, 我们可以很快、 很方便地査 到某个买家的购买情况, 但是某个商品被购买的交易数据很有可能分布在多张表中, 査找起来比较麻烦 。 反之, 按照商品维度分表, 则可以很方便地査找到该商品的购买情况, 但若要査找到买家的交易记录, 则会比较麻烦 。

​ 所以常见的解决方式如下。

​ ( 1 ) 在多个分片表查询后合并数据集, 这种方式的效率很低

​ ( 2 ) 记录两份数据, 一份按照买家纬度分表, 一份按照商品维度分表

​ ( 3 ) 通过搜索引擎解决, 但如果实时性要求很高, 就需要实现实时搜索

​ 在某电商交易平台下, 可能有买家査询自己在某一时间段的订单, 也可能有卖家査询自已在某一时间段的订单, 如果使用了分库分表方案, 则这两个需求是难以满足的, 因此, 通用的解决方案是, 在交易生成时生成一份按照买家分片的 数据副本和一份按照卖家分片的数据副本,查询时分别满足之前的两个需求,因此, 查询的数据和交易的数据可能是分别存储的,并从不同的系统提供接口。

字段数据类型优化

​ MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

基本原则

更小的通常更好

​ 一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要 的 CPU 周期也更少。

​ 但是要确保没有低估需要存储的值的范围，因为在的多个地方增加数据类型 的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。

简单就好

​ 简单数据类型的操作通常需要更少的 CPU 周期。例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。比如应该使用 MySQL 内建的类型而不是字符串来存储日期和时间。

尽量避免 NULL

​ 很多表都包含可为 NULL(空值)的列，即使应用程序并不需要保存 NULL 也是 如此，这是因为可为 NULL 是列的默认属性。通常情况下最好指定列为 NOT NULL， 除非真的需要存储 NULL 值。

​ 如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储 空间，在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时，每个索引记录 需要一个额外的字节。

​ 通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小，所以（调优 时）没有必要首先在现有 schema 中查找并修改掉这种情况，除非确定这会导致 问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。

Int/整数类型

​ 存储整数，可以使用这几种整数类型:TINYINT，SMALLINT，MEDIUMINT，INT， BIGINT。分别使用 8，16，24，32，64 位存储空间，也就是 1、2、3、4、8 个字 节。它们可以存储的值的范围请自行计算。(1->4(int) -> double(8))

​ 同时整数类型有可选的 UNSIGNED 属性，表示不允许负值，这大致可以使 正数的上限提高一倍。例如 TINYINT UNSIGNED 可以存储的范围是 0~255，而 TINYINT 的存储范围是-128~127。

​ 有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根 据实际情况选择合适的类型。

MySQL 可以为整数类型指定宽度，例如 INT(11)，对大多数应用这是没有意 义的，它不会限制值的合法范围,只是规定了 MySQL 的一些交互工具（例如 MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说，INT(1)和 INT(20)是相同的。

​ 在选择上，遵循着更小的通常更好的原则，在业务许可的情况下，尽量选择 位数小的。

实数类型

​ 实数是带有小数部分的数字。MySQL 既支持精确类型的存储 DECIMAL 类型， 也支持不精确类型存储 FLOAT 和 DOUBLE 类型。

​ DECIMAL 类型用于存储精确的小数，本质上 MySQL 是以字符串形式存放的。 所以 CPU 不支持对 DECIMAL 的直接计算，所以在 MySQL 中自身实现了 DECIMAL 的高精度计算。相对而言，CPU 直接支持原生浮点计算，所以浮点运算明显更快。

​ 浮点和 DECIMAL 类型都可以指定精度。对于 DECIMAL 列，可以指定小数点 前后所允许的最大位数。这会影响列的空间消耗。MySQL 5.0 和更高版本将数字打包保存到一个二进制字符串中（每 4 个字节存 9 个数字)。例如，DECIMAL(18,9) 小数点两边将各存储 9 个数字，一共使用 9 个字节:小数点前的数字用 4 个字节， 小数点后的数字用 4 个字节，小数点本身占 1 个字节。

​ MySQL 5.0 和更高版本中的 DECIMAL 类型允许最多 65 个数字。

​ 浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。FLOAT 使用 4 个字节存储，DOUBLE 占用 8 个字节，所以 DOUBLE 比 FLOAT 有更高的精 度和更大的范围。

​ 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL，例如存储财务或金融数据，在精度不敏感和需要快速运算的时候，选择 FLOAT 和 DOUBLE。

​ 但在数据量比较大的而且要求精度时，可以考虑使用 BIGINT 代替 DECIMAL， 将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万,然后将结果存储在 BIGINT 里，这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题。

字符串类型

​ MysQL 支持多种字符串类型，包括 VARCHAR 和 CHAR 类型、BLOB 和 TEXT 类型、ENUM（枚举）和 SET 类型。

VARCHAR 和 CHAR 类型

​ VARCHAR 和 CHAR 是两种最主要的字符串类型。

VARCHAR

​ VARCHAR 类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间)。

​ 在内部实现上，既然是变长，VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度，如果列的最大长度小于或等于 255 字节,则只使用 1 个字节表示,否则使用 2 个字节。 // <= 255

​ VARCHAR 节省了存储空间，所以对性能也有帮助。但是，由于行是变长的， 在 UPDATE 时新值比旧值长时，使行变得比原来更长，这就肯能导致需要做额外 的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，MyISAM 会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。

CHAR

​ CHAR 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。 当存储 CHAR 值时，MySQL 会删除所有的末尾空格，CHAR 值会根据需要采用空格进行填充以方便比较。 // 清空和补充

小结

​ 在 CHAR 和 VARCHAR 的选择上，这些情况下使用 VARCHAR 是合适的：

​ 字符串列的最大长度比平均长度大很多﹔列的更新很少；使用了像 UTF-8 这 样复杂的字符集，每个字符都使用不同的字节数进行存储。

​ CHAR 适合存储很短的字符串，或者所有值定长或都接近同一个长度。例如， CHAR 非常适合存储密码的 MD5 值，因为这是一个定长的值。对于经常变更的数 据，CHAR 也比 VARCHAR 更好，因为定长的 CHAR 类型不容易产生碎片。

​ **对于非常短的列，CHAR 比 VARCHAR 在存储空间上也更有效率。**例如用 CHAR(1)来存储只有 Y 和 N 的值，如果采用单字节字符集只需要一个字节，但是 VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

​ 另外，使用 VARCHAR(5)和 VARCHAR(200)存储’hello’在磁盘空间上开销是一 样的。我们随便选择一个就好？应该使用更短的列，为什么?

​ 事实证明有很大的优势。更长的列会消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

​ 所以最好的策略是只分配真正需要的空间。

BLOB 和 TEXT 类型

​ BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

BLOB: binary large object, 二进制大对象

​ 与其他类型不同，MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。 存储引擎在存储时通常会做特殊处理。当 BLOB 和 TEXT 值太大时，InnoDB 会使 用专门的**“外部”存储区域来进行存储**，此时每个值在行内需要 1~4 个字节存储 一个指针，然后在外部存储区域存储实际的值。

​ BLOB 和 TEXT 家族之间仅有的不同是 BLOB 类型存储的是二进制数据，没有排序规则或字符集，而 TEXT 类型有字符集和排序规则。

使用 BLOB 和 TEXT 要慎重：

​ (1）BLOB 和 TEXT 值会引起一些性能问题，所以尽量避免使用 BLOB 和 TEXT 类型；

​ (2）一定要用，建议把 BLOB 或 TEXT 列分离到单独的表中；

​ (3）在不必要的时候避免检索大型的 BLOB 或 TEXT 值。例如，SELECT *查询 就不是很好的想法，除非能够确定作为约束条件的 WHERE 子句只会找到所需要 的数据行。否则，很可能毫无目的地在网络上传输大量的值。建议可以搜索索引 列，决定需要的哪些数据行，然后从符合条件的数据行中检索 BLOB 或 TEXT 值；

​ (4）还可以使用合成的(Synthetic)索引来提高大文本字段(BLOB 或 TEXT)的查 询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把 这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但 是，要注意这种技术只能用于精确匹配的查询（散列值对于类似“<”或“>=” 等范围搜索操作符是没有用处的)。可以使用 MD5 函数生成散列值，也可以使用 SHA1(或 CRC32)，或者使用自己的应用程序逻辑来计算散列值。

必要时，使用枚举代替字符串

​ 如果表中的字段的取值是固定几个字符串，可以使用枚举列代替常用的字符串类型。

​ 枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中，MySQL 在内部会将每个值在列表中的位置保存为整数，这样的话可以让表的大小大为缩小。

CREATE TABLE enum_test(e ENUM(' fish', 'apple', 'dog') NOT NULL); 
INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

​ 但是要注意，

​ 1）因为枚举列实际存储为整数，而不是字符串，所以不要使用数字作为 ENUM 枚举常量，这种双重性很容易导致混乱，例如 ENUN( ’ 1’,‘2’，‘3’)。

​ 2）枚举字段是按照内部存储的整数而不是定义的字符串进行排序的，所以尽量按照需要的顺序来定义枚举列。

日期和时间类型

日期类型	占用空间(字节)	表示范围
DATETIME	8	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE	3	1000-01-01 ~ 9999-12-31
TIMESTAMP	4	1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR	1	YEAR(2):1970-2070, YEAR(4):1901-2155
TIME	3	-838:59:59 ~ 838:59:59

​ MySQL 可以使用许多类型来保存日期和时间值，例如 YEAR 和 DATE。MySQL 能存储的最小时间粒度为秒。

​ 大部分时间类型都没有替代品，因此没有什么是最佳选择的问题。唯一的问题 MySQL 提供两种相似的日期类型:DATETIME 和 TIMESTAMP。对于很多应用程 序，它们都能工作，但是在某些场景，需要做些适当选择。

DATETIME

​ 这个类型能保存大范围的值，从 1001 年到 9999 年，精度为秒。它把日期和时间封装到格式为 YYYYMMDDHHMMSS(14位) 的整数中，与时区无关。使用 8 个字节(2^64 = 18,446,744,073,709,551,616, 2^32 = 4,294,967,296, 所以4字节不够, 所以选用了8字节) 的存储空间。

TIMESTAMP

​ TIMETAMP 类型保存了从 1970 年 1 月 1 日午夜（格林尼治标准时间)以来的 秒数，它和 UNIX 时间戳相同。TIMESTAMP 只使用 4 个字节的存储空间，因此它 的范围比 DATETIME 小得多﹔只能表示从 1970 年到 2038 年。TIMESTAMP 显示的 值也依赖于时区。从空间效率来说，当然 TIMETAMP 比 DATETIME 更高。

2^32 = 4,294,967,296(10位), 当前时间戳: 2021-12-09 21:14:59 = 1639055699(10位), 2038-01-19 03:14:07 = 2147426047,显然和2^32还有大概一倍只差, 所以有以为作为了符号位处理类, 2^31 - 1 = 2,147,483,648 -1(最后一个是负值) 2,147,483,647 - 2147426047 = 57,600 (这个空间使用了存放什么的7,200 * 8 ?)

​ 如果需要存储比秒更小粒度的日期和时间值怎么办？MySQL 目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用 BIGINT 类型存储微秒级别的时间截，或者使用 DOUBLE 存储秒之后的小数部分。

工程实践-命名规范

​ 1、数据库、表、字段的命名要遵守可读性原则，尽可能少使用或者不使用缩写

​ 2、表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布， 所以字段名称需要慎重考虑。 说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写(这和windows不区分大小写和linux区分大小写有关)。因此，数据库名、表名、字段名，都不允许出现任 何大写字母，避免节外生枝。

​ 3、表名不使用复数名词

​ 4、数据库、表、字段的命名禁用保留字，如 desc、range、match 之类

​ 5、对象的名字应该能够描述它所表示的对象 例如： 表的名称应该能够体现表中存储的数据内容，最好是遵循“业务名称_表的 作用”；对于存储过程存储过程应该能够体现存储过程的功能。库名与应用名称 尽量一致。_

​ 6、主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_ 字段名。

​ pk_、uk_、idx_

​ 7、表达是与否概念的字段，应该使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）。