MYSQL- 表结构设计mysql基础表设计，对常用的字符集，排序规则，时间类型，自增主键，页压缩，非结构存储等总结

表结构设计

数字类型

整型类型

类型	占用空间	最小值-最大值[signed]	最小值-最大值[unsigned]
TINYINT	1	-128 ~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608 ~8388607	0~16777215
INT	4	-2147483448~2147483647	0~4294967295
BIGINT	8	-9223372036854775808 ~9223372036854775807	0~18446744073709551615

整型类型中，有signed和unsigned属性，表示整型取值范围，默认signed。 sql_mode 设置为 NO_UNSIGNED_SUBTRACTION，可以允许unsigned相减的结果为 signed

浮点和高精度

float和double不是高精度，也不是sql标准类型，不推荐使用。使用Decimal类型

业务设计实践

整型结合属性auto_increment可以实现自增功能，但是一般要注意两点:
- 用BIGINT做主键，不是INT
- 自增值不持久化，可能有回溯现象(8.0版本前)
当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误；
在海量互联网业务设计标准中，资金类型并不推荐使用DECIMAL类型，而是更推荐将DECIMAL转换成整型类型。

字符串类型

mysql字符串类型有CHAR, VARCHAR, BINARY, BLOB, TEXT, ENUM, SET

CHAR(N) 用来保存固定长度的字符，N的范围是0 ~ 255。VARCHAR(N)用来保存边长字符，N的范围是0 ~ 65536。N表示字符。

超出65536个字符的情况下，可以考虑用TEXT或BLOB，两者最大存储长度是4G，区别是BLOB没有字符集属性，纯属二进制存储。

字符集

字符集推荐使用UTF8MB4

SELECT CAST(0xF09F988E as char charset utf8mb4) as emoji

包括8.0在内，字符集被默认设置成UTM8MB4， 8.0之前默认Latin1。可以在配置文件中进行配置:

[mysqld]
character-set-server=utf8mb4

GDK字符集，1个字符串最多存储两个字节， UTF8MB4最多存储4个字节。在多字节字符集下，CHAR和VARCHAR完全相同，都是变长存储。

排序规则

排序规则(Collation) 是比较和排序字符串的一种规则, 可以通过SHOW CHARSET来查看字符集默认的排序规则

SHOW CHARSET LIKE '%utf8%'
SHOW COLLATION  LIKE 'utf8mb4%'

排序规则以_ci 结尾，表示不区分大小写， _cs表示大小写敏感， _bin表示通过存储字符的二进制进行比较

SELECT CAST('a' as char) COLLATE utf8mb4_0900_as_cs = CAST('A' as CHAR) COLLATE utf8mb4_0900_as_cs as result;

修改字符集需要执行

ALTER TABLE emoji_test CHARSET utf8mb4

这是将表的字符集修改成UTF8MB4，下次新增列，不显式指定字符集，新列的字符集会变成UTF8MB4，但对已经存在的列，默认字符集不做修改。

如果要彻底改变，需要执行

ALTER TABLE emoji_test CONVERT TO CHARSET utf8mb4;

业务设计实践

性别字段设计

8.0 之前，可以用ENUM字符串枚举，只允许有限的定义值插入，如果将参数SQL_MODE设计为严格模式，插入非定义数据就会报错。

Create Table: CREATE TABLE `User` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sex` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

SET sql_mode = 'STRICT_TRANS_TABLES';

在MySQL8.0.16后，数据库原生提供CHECK约束功能

Create Table: CREATE TABLE `User` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_chk_1` CHECK (((`sex` = _utf8mb4'M') or (`sex` = _utf8mb4'F')))
) ENGINE=InnoDB

日期类型

Mysql数据库中常见的日期类型有YEAR, DATE, TIME,DATETIME,TIMESTAMP。

DATETIME

DATETIME最终展现形式是: YYYY-MM-DD HH:MM:SS，固定占用8个字节。

从Mysql5.6开始， DATETIME类型支持毫秒，DATETIME(N)中的N表示毫秒的精度。

CREATE TABLE User (
    id BIGINT NOT NULL AUTO_INCREMENT,
    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),
    CHECK (sex = 'M' OR sex = 'F'),
    PRIMARY KEY(id)
);

TIMESTAMP

实际存储内容为'1970-01-01 00:00:00'到现在的毫秒数，占用4个字节，存储上限只能到‘2038-01-19 03:14:07’.

从Mysql5.6开始， TIMESTAMP类型也能支持毫秒，不同的是有毫秒时，类型TIMESTAMP占用7个字节， DATESTAMP无论是否存储毫秒信息，都占用8个字节。

TIMESTAMP带有时区信息。参数time_zone指定了当前使用的时区，默认SYSTEM操作系统时区，用户可以根据该参数指定需要的时区。

SET time_zone = '-08:00'
SET time_zone = 'America/Los_Angeles'
SET time_zone = 'Asia/Shanghai'

中国时区'+08:00', 美国时区'-08:00'，也可以直接设置名字

业务设计实践

选型

TIMESTAMP 上限值很快到来，而且有潜在的性能问题。从毫秒数转换TIMESTAMP本身指令不多，不会带来直接的性能问题，但是使用默认的操作系统时区，每次通过时区计算时间，需要调用操作系统底层函数_tz_convert()函数，需要额外的加锁操作，确保操作系统时区没有改变。所以性能不如DATETIME,海量并发时，存在性能抖动问题。优化策略是使用显式的时区, 在配置文件中设置时区, 不要使用系统时区。

[mysqld]
time_zone = "+08:00"

可以通过mysqlslap测试性能

mysqlslap --create-schema=test -uroot --number-of-queries=1000000 --concurrency=100 --query='SELECT NOW()'

非结构存储

JSON 类型是从 MySQL 5.7 版本开始支持的功能，而 8.0 版本解决了更新 JSON 的日志性能瓶颈。如果要在生产环境中使用 JSON 数据类型，强烈推荐使用 MySQL 8.0 版本。

JSON 类型比较适合存储一些修改较少、相对静态的数据

JSON分为JSON对象和JSON数组

业务设计实践

用户登录

CREATE TABLE UserLogin (
    userId BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(userId)
);

SET @a = '
{
	"cellphone" : "13918888888",
	"wxchat" : "破产码农",
    "QQ" : "82946772"
}
';

INSERT INTO UserLogin VALUES (1,@a);

常见的函数 JSON_EXTRACT，它用来从 JSON 数据中提取所需要的字段内容。JSON_UNQUOTE函数是去掉引号的功能将原json串的引号去掉转成string类型

SELECT
    userId,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;

等价于

SELECT
    userId,
    loginInfo->>"$.cellphone" cellphone,
    loginInfo->>"$.wxchat" wxchat
FROM UserLogin;

给JSON创建索引方法:

ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");
ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);

等价于

CREATE TABLE UserLogin (
    userId BIGINT,
    loginInfo JSON,
    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
    PRIMARY KEY(userId),
    UNIQUE KEY uk_idx_cellphone(cellphone)
);

用户画像

CREATE TABLE UserTag (
    userId bigint NOT NULL,
    userTags JSON,
    PRIMARY KEY (userId)
);

INSERT INTO UserTag VALUES (1,'[2,6,8,10]');
INSERT INTO UserTag VALUES (2,'[3,10,12]');

mysql8.0.17开始支持Multi-Valued Indexes, 用于JSON数组上创建索引，并通过member of, json_contains,json_overlans快速检索数据

 ALTER TABLE UserTag ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));
EXPLAIN SELECT * FROM UserTag 
WHERE 10 MEMBER OF(userTags->"$")\G
EXPLAIN SELECT * FROM UserTag 
WHERE JSON_CONTAINS(userTags->"$", '[2,10]')\G
EXPLAIN SELECT * FROM UserTag 
WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]')\G

忘记范式准则

三范式

第一范式(1NF): 数据达到原子性，数据不可再分
第二范式(2NF): 每行数据具有唯一性，消除数据之间的部分依赖，一个表中的非主键字段，完全依赖主键字段
第三范式(3NF): 每个字段都独立的依赖于主键关系，消除部分非主键字段的内部依赖

业务设计实践

自增主键设计

使用BIGINT的自增类型作为主键的设计只适合非核心业务表，比如告警表，日志表等。真正的核心业务表，一定不用自增键做主键。

自增存在回溯问题
自增值在服务端产生，存在并发性能问题

自增值在服务端产生，需要有一把自增的AI锁保护，有大量插入请求，可能引起性能瓶颈。innodb_autoinc_lock_mode用于控制自增锁持有的时间。
```
INSERT INTO ... VALUES (NULL,...),(NULL,...),(NULL,...);
```
innodb_autoinc_lock_mode可能值持有自增锁时间持有/释放自增锁次数
0 5.1版本以前的表锁实现，不推荐
1 每次SQL结束释放自增锁 1次
2 每次自增释放自增锁 3次
自增值做主键，只能在当前实例保证唯一，不能保证全局唯一
公开数据值，容易引发安全问题
MGR(MySQL Group Replication) 可能引起性能问题
分布式架构设计问题

innodb_autoinc_lock_mode可能值	持有自增锁时间	持有/释放自增锁次数
0	5.1版本以前的表锁实现，不推荐
1	每次SQL结束释放自增锁	1次
2	每次自增释放自增锁	3次

表结构设计应遵循这样规范

每张表一定要有一个主键；
自增主键只推荐用在非核心业务表，甚至应避免使用；
核心业务表推荐使用 UUID 或业务自定义主键；
一份数据应尽可能保留一份，通过主键关联进行查询，避免冗余数据；
在一些场景下，可以通过 JSON 数据类型进行反范式设计，提升存储效率；

表压缩

如果一个页中存放的记录数越多，数据库的性能越高。启用压缩后，存储空间占用也变小了，同样单位的存储能存放的数据也变多了。

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

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

压缩并不意味着性能下降，或许能额外提升性能，因为大部分的数据库业务系统，CPU 的处理能力是剩余的，而 I/O 负载才是数据库主要瓶颈。

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

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

COMPRESS页压缩

COMPRESS 页压缩是 MySQL 5.7 版本之前提供的页压缩功能。只要在创建表时指定ROW_FORMAT=COMPRESS，并设置通过选项 KEY_BLOCK_SIZE 设置压缩的比例。虽然是通过选项 ROW_FORMAT 启用压缩功能，但这并不是记录级压缩，依然是根据页的维度进行压缩。

CREATE TABLE Log (
  logId BINARY(16) PRIMARY KEY,
  ......
)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8

COMPRESS 页压缩，适合用于一些对性能不敏感的业务表，例如日志表、监控表、告警表等，压缩比例通常能达到 50% 左右。虽然 COMPRESS 压缩可以有效减小存储空间，但 COMPRESS 页压缩的实现对性能的开销是巨大的，性能会有明显退化。主要原因是一个压缩页在内存缓冲池中，存在压缩和解压两个页。

Page1 和 Page2 都是压缩页 8K，但是在内存中还有其解压后的 16K 页。这样设计的原因是 8K 的页用于后续页的更新，16K 的页用于读取，这样读取就不用每次做解压操作了。这样的实现会增加对内存的开销，会导致缓存池能存放的有效数据变少，MySQL 数据库的性能自然出现明显退化。

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

TPC压缩

Transparent Page Compress是 5.7 版本推出的一种新的页压缩功能，其利用文件系统的空洞（Punch Hole）特性进行压缩。

CREATE TABLE Transaction （
  transactionId BINARY(16) PRIMARY KEY,
  .....
)
COMPRESSION=ZLIB | LZ4 | NONE;

由于空洞是文件系统的一个特性，利用空洞压缩只能压缩到文件系统的最小单位 4K，且其页压缩是 4K 对齐的。它对数据库性能的侵入几乎是无影响的（小于 20%），甚至可能还能有性能的提升。不同于 COMPRESS 页压缩，TPC 压缩在内存中只有一个 16K 的解压缩后的页，对于缓冲池没有额外的存储开销。

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

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

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

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

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

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

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

ALTER TABLE Transaction202102 COMPRESSION=ZLIB；
OPTIMIZE TABLE Transaction202102;