【MySQL】高性能高可用表设计实战-表设计篇（MySQL专栏启动）

📫作者简介：小明java问道之路，专注于研究 Java/ Liunx内核/ C++及汇编/计算机底层原理/源码，就职于大型金融公司后端高级工程师，擅长交易领域的高安全/可用/并发/性能的架构设计与演进、系统优化与稳定性建设。

📫 热衷分享，喜欢原创~ 关注我会给你带来一些不一样的认知和成长。

🏆 InfoQ签约作者、CSDN专家博主/后端领域优质创作者/内容合伙人、阿里云专家/签约博主、51CTO专家 🏆

🔥如果此文还不错的话，还请👍关注、点赞、收藏三连支持👍一下博主~

本文导读

本篇文章结合前面上一篇文章 MySQL建表与常见类型设计陷阱，结合上文所学的属性类型，本文详细介绍表结构的字段类型选择和表的物理存储设计，主要有库、表、字段、索引的命名规范，反范式的设计原则、表压缩。

一、库、表、字段、索引命名设计规范

命名规范如下，命名时的字符取值范围为：az、AZ和下划线。 ，所有表名小写并且不允许驼峰式命名，不允许使用其他特殊字符作为名称，减少潜在风险。

数据库库名和表名的命名都应该遵循见名知意的原则。

表名的命名规则仅使用 a~z、下划线，分表名称为表名_编号，业务表必须包含业务含义

这样包含了更多的业务信息，比如：订单系统库ORDER、商品系统库GOODS、活动营销系统库PROMO等等。订单表order_info、分摊明细表fork_business_detail、支付流水表order_pay_log等等。

字段名精确，遵循见名知意的原则，格式：名称_后缀。例如用户名的字段为 UserName 而不是 Name ，布尔型的字段，以 is 开头，例如是否通过检查 ischecked
常见后缀如下：流水号或者无意义主键，后缀为 id，比如 order_id，时间后缀为 time，insert_time。

索引命名格式，主要为了区分哪些对象是索引，前缀_表名或缩写）_字段名，主键必须使用前缀“pk_”，UNIQUE 约束必须使用前缀“uk_”，普通索引必须使用前缀“idx_”。

创建表时显示指定字符集、存储引擎、注释信息等。不同系统之间，应该由DBA统一规范；

不同表之间的相同字段或者关联字段，字段类型/命名要保持一致；库表字符集和前端程序、中间件必须保持一致的 UTF8mb4。

数据库规范库表字段的命名，能够提高数据库的易读性。

二、反范式表结构设计实战

1、什么是数据库设计范式？

1.1 数据库设计的第一范式

数据库表中的所有字段都只具有单一属性，单一属性的列是由基本数据类型所构成的，设计出来的表都是简单的二维表：
例如name-age列具有两个属性，一个name,一个age不符合第一范式，应该把它拆分成两列

1.2 数据库设计的第二范式

要求表中只具有一个业务主键，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系。

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

1.3 数据库设计的第三范式

指每一个非非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上相处了非主键对主键的传递依赖

其中。客户编号 和订单编号管理 关联，客户姓名 和订单编号管理 关联，客户编号 和 客户姓名 关联，如果客户编号发生改变，用户姓名也会改变，这样不符合第三大范式，应该把客户姓名这一列删除。

2、忘记范式准则，反范式设计实战

真实的业务场景是工程实现，表结构设计做好以下三点就已经足够：

1、每张表一定要有一个主键、有创建时间、创建人、更新时间更新人；

2、适当冗余数据、对复杂对象使用JSON大字段存储，一份数据在一张表只保留一份，可以适当冗余需要关联查询的不变的数据；

3、对分库分表（分布式数据库）不是用自增ID做主键，业务表应该使用 UUID 或业务（具有业务唯一性）自定义主键；

三、企业表压缩实战

当业务发展到一定规模，表设计的再优秀也会发生性能问题，一旦发生就是严重性的。对于没有分库分表的表，在物理存储层面主要是考虑是否要使用压缩（页）表功能，默认情况下，所有表都是非压缩的。

1、什么是压缩表

数据库表空间中的页存储在磁盘上，MySQL数据库必须首先将磁盘中的页读取到内存缓冲池，然后读取和管理页中的记录。因此，页中存储的记录越多，数据库的性能就越高。

在MySQL中，页的默认大小为16K，页构成每个表的表空间。页中存储的记录越多，内存中可以存储的记录就越多，访问效率就越高。

如果要增加页中的记录数，可以启用压缩功能。此外，启用压缩后，存储空间更小，同一存储单元可以存储更多数据。

启用压缩技术，可以根据每个数据、数据库表和页空间压缩数据库。

然而在实际项目中，我们通常使用页压缩技术。为什么？

压缩每条记录，每次读取或写入记录时，都会对其进行压缩和解压缩。如果过分依赖CPU的计算能力，性能将显著下降，而且单个记录的大小不是特别大，一般小于1K，所以压缩效率不是特别好；压缩表空间要求表空间文件不会静态增长。

通过页压缩技术，MySQL可以将16K页压缩到8K，甚至4K，这样当从磁盘写入或读取时，I/O请求大小可以减半，甚至更小，以提高数据库的整体性能。

2、TPC压缩实战

我们一般使用，TPC压缩，TPC（Transparent Page Compression 透明页压缩）是5.7版中引入的一种新的页压缩功能。压缩16K页后，将为8K。

然后数据库将为16K页的剩余8K填充0x00。当16K页写入磁盘时，基于TPC压缩的测试结果将在未压缩的基础上额外提高40%的性能。

可以使用以下命令创建TPC压缩表，如果是创建表的时候没有压缩，通过 ALTER TABLE “表名” 启用 TPC 压缩后，还需要执行命令 OPTIMIZE TABLE 才能立即完成空间的压缩。

CREATE TABLE order_info （
  // 
)
COMPRESSION=ZLIB | LZ4 | NONE;

总结

本篇文章结合前面上一篇文章 MySQL建表与常见类型设计陷阱，结合上文所学的属性类型，本文详细介绍表结构的字段类型选择和表的物理存储设计，主要有库、表、字段、索引的命名规范，反范式的设计原则、表压缩。

后续博主会对建表的索引设计进行详细介绍。