tulip notes
EffectTang
2024-10-23
目录
范式准则
工程上的表结构设计实战
反范式的优劣
自增主键设计
UUID主键设计
是否是排序UUID
业务自定义生成主键
消除冗余
存储中的压缩
表压缩
MySQL 压缩表设计
COMPRESS 页压缩
TPC 压缩
压缩在业务上的使用
表压缩操作
在建表时启用页压缩
在已有表上启用页压缩
压缩级别和算法
扩展

忘记范式与使用页压缩

# 忘记范式与使用页压缩

而我们在对一张表进行设计时，还要遵守一些基本的原则，比如你经常听见的“范式准则”。但范式准则过于理论，在真实业务中，你不必严格遵守三范式的要求。而且有时为了性能考虑，你还可以进行反范式的设计。

# 范式准则

范式设计是非常重要的理论，是通过数学集合概念来推导范式的过程，在理论上，要求表结构设计必须至少满足三范式的要求。

由于完全是数据推导过程，范式理论非常枯燥，但你只要记住几个要点就能抓住其中的精髓：

  • 一范式要求所有属性都是不可分的基本数据项；
  • 二范式解决部分依赖；
  • 三范式解决传递依赖。

# 工程上的表结构设计实战

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

  • 每张表一定要有一个主键（方法有自增主键设计、UUID 主键设计、业务自定义生成主键）；
  • 消除冗余数据存在的可能。

我想再次强调一下，你不用过于追求所谓的数据库范式准则，甚至有些时候，我们还会进行反范式的设计。反范式的目的是为了，在某些情况下优化性能或简化查询。尽管规范化是数据库设计中的重要原则，但在某些特定情况下，反范式设计是有益的。

# 反范式的优劣

以下是反范式的几个好处：

  1. 提高查询性能

规范化的数据库设计通常会导致多表连接（JOIN），而JOIN操作可能会成为性能瓶颈。反范式设计可以减少JOIN操作的数量，从而提高查询速度。

  1. 简化查询

通过在单个表中存储重复数据，可以简化查询逻辑。用户不需要编写复杂的多表连接查询来获取所需的数据。

  1. 提高读取效率

对于读密集型的应用场景，反范式设计可以显著提高读取效率。因为减少了JOIN操作，数据可以更快地从磁盘或内存中加载。

  1. 减少数据冗余

尽管反范式设计会增加数据冗余，但在某些情况下，这种冗余是可以接受的，甚至是有利的。例如，在大型数据分析或报表生成场景中，存储重复数据可以加速报表生成过程。

  1. 减少锁定竞争

在高并发场景下，JOIN操作可能导致锁定竞争。通过反范式设计，可以减少锁定竞争，从而提高并发性能。

注意：

尽管反范式设计有许多优点，但也有一些潜在的问题需要考虑：

  1. 数据冗余：反范式设计会导致数据冗余，需要更多的存储空间。
  2. 数据一致性：需要额外的逻辑来确保数据的一致性，尤其是在更新数据时。
  3. 维护成本：反范式设计可能会增加维护成本，因为需要管理更多的表和字段。
  4. 复杂性：在某些情况下，反范式设计可能会增加数据模型的复杂性。

# 自增主键设计

主键用于唯一标识一行数据，所以一张表有主键，就已经直接满足一范式的要求了。之前提及可以使用 BIGINT 的自增类型作为主键，同时由于整型的自增性，数据库插入也是顺序的，性能较好。

但请千万注意，使用 BIGINT 的自增类型作为主键的设计仅仅适合非核心业务表，比如告警表、日志表等。

真正的核心业务表，一定不要用自增键做主键，主要有 6 个原因：

  • 自增存在回溯问题；
  • 自增值在服务器端产生，存在并发性能问题；
  • 自增值做主键，只能在当前实例中保证唯一，不能保证全局唯一；
  • 公开数据值，容易引发安全问题，例如知道地址http://www.example.com/User/10/ (opens new window)，很容猜出 User 有 11、12 依次类推的值，容易引发数据泄露；
  • MGR（MySQL Group Replication） 可能引起的性能问题；
  • 分布式架构设计问题。

自增存在回溯问题，是因为mysql8.0之前是不会持久化的，如果你想让核心业务表用自增作为主键，那么MySQL 数据库版本应该尽可能升级到 8.0 版本。

又因为自增值是在 MySQL 服务端产生的值，需要有一把自增的 AI 锁保护，若这时有大量的插入请求，就可能存在自增引起的性能瓶颈。比如在 MySQL 数据库中，参数 innodb_autoinc_lock_mode 用于控制自增锁持有的时间。假设有一 SQL 语句，同时插入 3 条带有自增值的记录：

INSERT INTO ... VALUES (NULL,...),(NULL,...),(NULL,...);
1

则参数 innodb_autoinc_lock_mode 的影响如下所示：

图片1.png

从表格中你可以看到，一条 SQL 语句插入 3 条记录，参数 innodb_autoinc_lock_mode 设置为 1，自增锁在这一条 SQL 执行完成后才释放。

如果参数 innodb_autoinc_lock_mode 设置为2，自增锁需要持有 3 次，每插入一条记录获取一次自增锁。

  • 这样设计好处是： 当前插入不影响其他自增主键的插入，可以获得最大的自增并发插入性能。
  • 缺点是： 一条 SQL 插入的多条记录并不是连续的，如结果可能是 1、3、5 这样单调递增但非连续的情况。

所以，如果你想获得自增值的最大并发性能，把参数 innodb_autoinc_lock_mode 设置为2。

虽然，我们可以调整参数 innodb_autoinc_lock_mode获得自增的最大性能，但是由于其还存在上述 5 个问题。因此，在互联网海量并发架构实战中，我更推荐 UUID 做主键或业务自定义生成主键。

# UUID主键设计

UUID（Universally Unique Identifier）代表全局唯一标识 ID。显然，由于全局唯一性，你可以把它用来作为数据库的主键。

MySQL 数据库遵循 DRFC 4122 (opens new window) 命名空间版本定义的 Version 1规范，可以通过函数 UUID自动生成36字节字符。如：

mysql> SELECT UUID();

+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| e0ea12d4-6473-11eb-943c-00155dbaa39d |
+--------------------------------------+
1
2
3
4
5
6
7

根据 Version 1的规范，MySQL中的 UUID 由以下几个部分组成：

UUID = 时间低（4字节）- 时间中高+版本（4字节）- 时钟序列 - MAC地址
1

前 8 个字节中，60 位用于存储时间，4 位用于 UUID 的版本号，其中时间是从 1582-10-15 00：00：00.00 到现在的100ns 的计数。

60 位的时间存储中，其存储分为：

  • 时间低位（time-low），占用 12 位；
  • 时间中位（time-mid），占用 2 字节，16 位；
  • 时间高位（time-high），占用 4 字节，32 位；

需要特别注意的是，在存储时间时，UUID 是根据时间位逆序存储， 也就是低时间低位存放在最前面，高时间位在最后，即 UUID 的前 4 个字节会随着时间的变化而不断“随机”变化，并非单调递增。而非随机值在插入时会产生离散 IO，从而产生性能瓶颈。这也是 UUID 对比自增值最大的弊端。

为了解决这个问题，MySQL 8.0 推出了函数UUID_TO_BIN，它可以把 UUID 字符串：

  • 通过参数将时间高位放在最前，解决了 UUID 插入时乱序问题；
  • 去掉了无用的字符串”-“，精简存储空间；
  • 将字符串其转换为二进制值存储，空间最终从之前的 36 个字节缩短为了 16 字节。

下面我们将之前的 UUID 字符串 e0ea12d4-6473-11eb-943c-00155dbaa39d 通过函数 UUID_TO_BIN 进行转换，得到二进制值如下所示：

SELECT UUID_TO_BIN('e0ea12d4-6473-11eb-943c-00155dbaa39d',TRUE) as UUID_BIN;
+------------------------------------+
| UUID_BIN                           |
+------------------------------------+
| 0x11EB6473E0EA12D4943C00155DBAA39D |
+------------------------------------+

1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

除此之外，MySQL 8.0 也提供了函数 BIN_TO_UUID，支持将二进制值反转为 UUID 字符串。

当然了，MySQL 8.0版本之前没有函数 UUID_TO_BIN/BIN_TO_UUID，但是你还是可以通过用户义函数（UDF）的方式解决，如创建下面的函数：

CREATE FUNCTION MY_UUID_TO_BIN(_uuid BINARY(36))

    RETURNS BINARY(16)
    LANGUAGE SQL  DETERMINISTIC  CONTAINS SQL  SQL SECURITY INVOKER

    RETURN

        UNHEX(CONCAT(
            SUBSTR(_uuid, 15, 4),
            SUBSTR(_uuid, 10, 4),
            SUBSTR(_uuid,  1, 8),
            SUBSTR(_uuid, 20, 4),
            SUBSTR(_uuid, 25) ));

CREATE FUNCTION MY_BIN_TO_UUID(_bin BINARY(16))
    RETURNS  CHAR(36)
    LANGUAGE SQL  DETERMINISTIC  CONTAINS SQL  SQL SECURITY INVOKER
    RETURN

        LCASE(CONCAT_WS('-',

            HEX(SUBSTR(_bin,  5, 4)),
            HEX(SUBSTR(_bin,  3, 2)),
            HEX(SUBSTR(_bin,  1, 2)),
            HEX(SUBSTR(_bin,  9, 2)),
            HEX(SUBSTR(_bin, 11)) ));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

因此，你可以将其主键修改为 BINARY(16)，用于存储排序后的 16 字节的 UUID 值。其表结构修如下：

CREATE TABLE User (

    id  BINARY(16) NOT NULL,
  
    name VARCHAR(255) NOT NULL,
    sex CHAR(1) NOT NULL,
    password VARCHAR(1024) NOT NULL,
    money INT NOT NULL DEFAULT 0,
    register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    uuid CHAR(36) AS (BIN_TO_UUID(id)),
    CONSTRAINT chk_sex CHECK (sex = 'M' OR sex = 'F'),
    PRIMARY KEY(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

现在，你可以在客户端通过以下 SQL 命令插入数据，如：

INSERT INTO User VALUES (UUID_TO_BIN(UUID(),TRUE),......);
1

UUID_TO_BIN(UUID(),TRUE)，就是一个生成排序uuid的方法。

当然，很多同学也担心 UUID 的性能和存储占用的空间问题，这里我也做了相关的插入性能测试，结果如下表所示：

图片3.png

可以看到，MySQL 8.0 提供的排序 UUID 性能最好，甚至比自增ID还要好。此外，由于UUID_TO_BIN转换为的结果是16 字节，仅比自增 ID 增加 8 个字节，最后存储占用的空间也仅比自增大了 3G。

而且由于 UUID 能保证全局唯一，因此使用 UUID 的收益远远大于自增ID。可能你已经习惯了用自增做主键，但在海量并发的互联网业务场景下，更推荐 UUID 这样的全局唯一值做主键。

比如，我特别推荐游戏行业的用户表结构设计，使用 UUID 作为主键，而不是用自增 ID。因为当发生合服操作时，由于 UUID 全局唯一，用户相关数据可直接进行数据的合并，而自增 ID 却需要额外程序整合两个服务器 ID 相同的数据，这个工作是相当巨大且容易出错的。

在MySQL中定义一个BINARY(16)类型的字段来存储UUID，并不自动意味着这就是所谓的“排序UUID”。

这个字段可以用来存储16字节（128位）的UUID。由于BINARY(16)是二进制类型，它可以非常高效地存储和比较UUID。

# 是否是排序UUID

要判断是否为排序UUID，关键在于生成UUID的方式。如果你使用UUID_TO_BIN()函数生成UUID，并且使用时间戳前置格式，那么生成的UUID将具有一定的排序特性，但并不完全等同于“排序UUID”。

以下是几种常见的生成方法：

  1. 时间戳前置格式：
    • 使用时间戳前置格式的UUID，并且确保时间戳部分位于前面，以便在时间上具有顺序性。
    • 例如，使用UUID_TO_BIN(UUID(), TRUE)生成UUID。
  2. 时间戳加序列号：
    • 生成UUID时，将时间戳放在前面，并加上一个递增的序列号，以确保UUID在时间上具有严格的顺序性。
    • 例如，可以使用自定义函数生成UUID。

# 业务自定义生成主键

当然了，UUID 虽好，但是在分布式数据库场景下，主键还需要加入一些额外的信息，这样才能保证后续二级索引的查询效率（具体这部分内容将在后面的分布式章节中进行介绍）。现在你只需要牢记：分布式数据库架构，仅用 UUID 做主键依然是不够的。 所以，对于分布式架构的核心业务表，我推荐类似如下的设计，比如：

PK = 时间字段 + 随机码（可选） + 业务信息1 + 业务信息2 ......
1

# 消除冗余

消除冗余也是范式的要求，解决部分依赖和传递依赖，本质就是尽可能减少冗余数据。

所以，在进行表结构设计时，数据只需存放在一个地方，其他表要使用，通过主键关联存储即可。比如订单表中需要存放订单对应的用户信息，则保存用户 ID 即可：

CREATE TABLE Orders (

  order_id VARCHRA(20),
  user_id  BINARY(16),
  order_date datetime,
  last_modify_date datetime
  ...

  PRIMARY KEY(order_id),
  KEY(user_id,order_date)
  KEY(order_date),
  KEY(last_modify_date)

)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

当然了，无论是自增主键设计、UUID主键设计、业务自定义生成主键、还是消除冗余，本质上都是遵循了范式准则。但是在一些其他业务场景下，也存在反范式设计的情况。

# 存储中的压缩

很多同学不会在表结构设计之初就考虑存储的设计，只有当业务发展到一定规模才会意识到问题的严重性。而物理存储主要是考虑是否要启用表的压缩功能，默认情况下，所有表都是非压缩的。

但一些同学一听到压缩，总会下意识地认为压缩会导致 MySQL 数据库的性能下降。这个观点说对也不对，需要根据不同场景进行区分。 这一讲，我们就来看一看表的物理存储设计：不同场景下，表压缩功能的使用。

# 表压缩

数据库中的表是由一行行记录（rows）所组成，每行记录被存储在一个页中，在 MySQL 中，一个页的大小默认为 16K，一个个页又组成了每张表的表空间。

通常我们认为，如果一个页中存放的记录数越多，数据库的性能越高。这是因为数据库表空间中的页是存放在磁盘上，MySQL 数据库先要将磁盘中的页读取到内存缓冲池，然后以页为单位来读取和管理记录。

一个页中存放的记录越多，内存中能存放的记录数也就越多，那么存取效率也就越高。若想将一个页中存放的记录数变多，可以启用压缩功能。此外，启用压缩后，存储空间占用也变小了，同样单位的存储能存放的数据也变多了。

若要启用压缩技术，数据库可以根据记录、页、表空间进行压缩，不过在实际工程中，我们普遍使用页压缩技术，这是为什么呢？

  • 压缩每条记录： 因为每次读写都要压缩和解压，过于依赖 CPU 的计算能力，性能会明显下降；另外，因为单条记录大小不会特别大，一般小于 1K，压缩效率也并不会特别好。
  • 压缩表空间： 压缩效率非常不错，但要求表空间文件静态不增长，这对基于磁盘的关系型数据库来说，很难实现。

而基于页的压缩，既能提升压缩效率，又能在性能之间取得一种平衡。

可能很多同学认为，启用表的页压缩功能后，性能有明显损失，因为压缩需要有额外的开销。的确，压缩需要消耗额外的 CPU 指令，但是压缩并不意味着性能下降，或许能额外提升性能，因为大部分的数据库业务系统，CPU 的处理能力是剩余的，而 I/O 负载才是数据库主要瓶颈。

借助页压缩技术，MySQL 可以把一个 16K 的页压缩为 8K，甚至 4K，这样在从磁盘写入或读取时，就能将 I/O 请求大小减半，甚至更小，从而提升数据库的整体性能。

当然，压缩是一种平衡，并非一定能提升数据库的性能。这种性能“平衡”取决于解压缩开销带来的收益和解压缩带来的开销之间的一种权衡。但无论如何，压缩都可以有效整理数据原本的容量，对存储空间来说，压缩的收益是巨大的。

# MySQL 压缩表设计

# COMPRESS 页压缩

COMPRESS 页压缩是 MySQL 5.7 版本之前提供的页压缩功能。只要在创建表时指定ROW_FORMAT=COMPRESS，并设置通过选项 KEY_BLOCK_SIZE 设置压缩的比例。

需要牢记的是， 虽然是通过选项 ROW_FORMAT 启用压缩功能，但这并不是记录级压缩，依然是根据页的维度进行压缩。

下面这是一张日志表，ROW_FROMAT 设置为 COMPRESS，表示启用 COMPRESS 页压缩功能，KEY_BLOCK_SIZE 设置为 8，表示将一个 16K 的页压缩为 8K。

CREATE TABLE Log (
  logId BINARY(16) PRIMARY KEY,
  ......
)

ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8
1
2
3
4
5
6
7

COMPRESS 页压缩就是将一个页压缩到指定大小。如 16K 的页压缩到 8K，若一个 16K 的页无法压缩到 8K，则会产生 2 个压缩后的 8K 页，具体如下图所示：

图片3.png

COMPRESS 页压缩

总的来说，COMPRESS 页压缩，适合用于一些对性能不敏感的业务表，例如日志表、监控表、告警表等，压缩比例通常能达到 50% 左右。

虽然 COMPRESS 压缩可以有效减小存储空间，但 COMPRESS 页压缩的实现对性能的开销是巨大的，性能会有明显退化。主要原因是一个压缩页在内存缓冲池中，存在压缩和解压两个页。

图片4.png

1 个 COMPRESS 压缩页在内存中存在 2 个页版本

如图所示，Page1 和 Page2 都是压缩页 8K，但是在内存中还有其解压后的 16K 页。这样设计的原因是 8K 的页用于后续页的更新，16K 的页用于读取，这样读取就不用每次做解压操作了。

很明显，这样的实现会增加对内存的开销，会导致缓存池能存放的有效数据变少，MySQL 数据库的性能自然出现明显退化。

为了 解决压缩性能下降的问题，从MySQL 5.7 版本开始推出了 TPC 压缩功能。

# TPC 压缩

TPC（Transparent Page Compression）是 5.7 版本推出的一种新的页压缩功能，其利用文件系统的空洞（Punch Hole）特性进行压缩。可以使用下面的命令创建 TPC 压缩表：

CREATE TABLE Transaction （
  transactionId BINARY(16) PRIMARY KEY,
  .....
)

COMPRESSION=ZLIB | LZ4 | NONE;
1
2
3
4
5
6

要使用 TPC 压缩，首先要确认当前的操作系统是否支持空洞特性。通常来说，当前常见的 Linux 操作系统都已支持空洞特性。

由于空洞是文件系统的一个特性，利用空洞压缩只能压缩到文件系统的最小单位 4K，且其页压缩是 4K 对齐的。比如一个 16K 的页，压缩后为 7K，则实际占用空间 8K；压缩后为 3K，则实际占用空间是 4K；若压缩后是 13K，则占用空间依然为 16K。

TPC 压缩的具体实现如下所示：

图片5.png

TPC 页压缩

上图可以看到，一个 16K 的页压缩后是 8K，接着数据库会对这 16K 的页剩余的 8K 填充0x00，这样当这个 16K 的页写入到磁盘时，利用文件系统空洞特性，则实际将仅占用 8K 的物理存储空间。

空洞压缩的另一个好处是，它对数据库性能的侵入几乎是无影响的（小于 20%），甚至可能还能有性能的提升。

这是因为不同于 COMPRESS 页压缩，TPC 压缩在内存中只有一个 16K 的解压缩后的页，对于缓冲池没有额外的存储开销。

另一方面，所有页的读写操作都和非压缩页一样，没有开销，只有当这个页需要刷新到磁盘时，才会触发页压缩功能一次。但由于一个 16K 的页被压缩为了 8K 或 4K，其实写入性能会得到一定的提升。

图片1.png

官方 TPC 测试对比

上图是 MySQL 官方的 LinkBench 测试结果，可以看到，无压缩的测试结果为 13,432 QPS，传统的 COMPRESS 页压缩性能下降为 10,480 QPS，差不多30%的性能下降。基于TPC压缩的测试结果为 18,882，在未压缩的基础上还能有额外 40% 的性能提升。

# 压缩在业务上的使用

总的来说，对一些对性能不敏感的业务表，例如日志表、监控表、告警表等，它们只对存储空间有要求，因此可以使用 COMPRESS 页压缩功能。

在一些较为核心的流水业务表上，我更推荐使用 TPC压缩。因为流水信息是一种非常核心的数据存储业务，通常伴随核心业务。如一笔电商交易，用户扣钱、下单、记流水，这就是一个核心业务的微模型。

所以，用户对流水表有性能需求。此外，流水又非常大，启用压缩功能可更为有效地存储数据。

若对压缩产生的性能抖动有所担心，我的建议：由于流水表通常是按月或天进行存储，对当前正在使用的流水表不要启用 TPC 功能，对已经成为历史的流水表启用 TPC 压缩功能。

流水表的设计

需要特别注意的是： 通过命令 ALTER TABLE xxx COMPRESSION = ZLIB 可以启用 TPC 页压缩功能，但是这只对后续新增的数据会进行压缩，对于原有的数据则不进行压缩。所以上述ALTER TABLE 操作只是修改元数据，瞬间就能完成。

若想要对整个表进行压缩，需要执行 OPTIMIZE TABLE 命令：

ALTER TABLE Transaction202102 COMPRESSION=ZLIB；

OPTIMIZE TABLE Transaction202102;
1
2
3

# 表压缩操作

MySQL中使用InnoDB存储引擎时，可以使用页压缩功能来减少存储空间的使用并提高I/O效率。页压缩功能可以在建表时设置，也可以在表已经存在之后通过ALTER TABLE命令启用。

# 在建表时启用页压缩

当你在创建表时想要启用页压缩，可以通过指定ROW_FORMAT和KEY_BLOCK_SIZE来设置压缩参数。以下是一个示例：

CREATE TABLE your_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    data TEXT
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
1
2
3
4
5

在这个例子中，ROW_FORMAT=COMPRESSED指定了行格式为压缩格式，KEY_BLOCK_SIZE=8设置了压缩块的大小。

# 在已有表上启用页压缩

如果你已经有了一张表，并且想要启用页压缩，可以使用ALTER TABLE命令来修改表的存储格式：

ALTER TABLE your_table ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
1

# 压缩级别和算法

除了行格式和压缩块大小之外，还可以设置压缩级别和压缩算法。InnoDB默认使用Zlib算法进行压缩，但可以通过以下系统变量来调整：

  • innodb_compression_level：设置压缩级别（默认为6，取值范围为0到9）。
  • innodb_compression_algorithm：设置压缩算法（默认为zlib，还可以设置为lz4或zstd）。

例如，如果你想调整压缩级别和算法，可以使用以下命令：

SET GLOBAL innodb_compression_level = 9;
SET GLOBAL innodb_compression_algorithm = 'lz4';
1
2

# 扩展

  • MyISAM：仅支持整个表文件压缩，压缩后的表只能用于读取，不能写入。

  • InnoDB：除了表文件压缩，还支持页级压缩，同时支持读写，提供更多压缩算法和级别选择。

  • 压缩可以减少存储空间，但会增加CPU负载，特别是在写入时进行压缩和读取时进行解压缩。

  • 可以通过调整压缩级别和算法来平衡存储空间和性能。

上次更新: 2025/03/07, 06:05:43
非结构存储：用好 JSON 这张牌
认识索引的魅力

← 认识索引的魅力→

最近更新
01
使用踩坑记录
04-02
02
三种常见权限控制模式
04-02
03
MybatisPlus的使用跟扩展
03-31
更多文章>
Theme by Vdoing | Copyright © 2023-2025 EffectTang