MySQL表结构设计

数字类型

整型类型

• 在整型类型中，有singed和unsingned属性，其表示的整型的取值范围，默认是singed，在设计时不建议刻意去用unsingned属性（正数），因为在做一些数据分析时，SQL可能返回的结果不是想要的结果。
• MySQL要求unsingned数值相减之后依然为unsingned，否则会报错。
• 为了避免这个错误，需要对数据库参数 sql_mode设置为 NO_UNSIGNED_SUBTRACTION，允许想减的结果为 signed，这样才能得到理想的结果

整型类型与自增设计

• 用bigint做主键，而不是int
  • INT类型范围最大在42亿级别
• 自增值并不持久化，可能会有回溯现象（MySQL 8.0 版本前）

资金字段设计

• 在海量互联网业务的设计标准中，并不推荐使用 DECIMAL 类型，而是更推荐将 DECIMAL 类型转换为整型类型。
  • DECIMAL 类型是个变长字段，长度不好统一。BIG INT是定长字段，存储高效。
  • 类型 DECIMAL 类型是通过二进制实现的一种编码方式，计算效率不如整型来的高效。

字符串类型

CHAR与VARCHAR的定义

• CHAR(N) 用来保存固定长度的字符，N 的范围是0~255，请牢记，N表示的是字符，而不是字节。
• VARCHAR(N) 用来保存变长字符，N的范围是0~65535，N表示字符
• 在超出 65536 个字符的情况下，可以考虑使用更大的字符类型TEXT或BLOB，两者最大的存储长度是4G，其区别是BLOB没有字符集属性，纯属二进制存储。
• 在MySQL数据库下，绝大部分场景使用类型 VARCHAR 就足够了。
• 对于varchar来说，最多能存放的字符个数为65532，varchar(N)，utf8 编码，计算方式如下：
  • 由于 utf8 的每个字符最多占用 3 个字节。而 MySQL定义行的长度不能超过65535，因此 N 的最大值计算方法为：(65535-1-2)/3。
  • 减去 1 的原因是实际存储从第二个字节开始； 减去 2 的原因是因为要在列表长度存储实际的字符长度； 除以 3 是因为 utf8 限制； 每个字符最多占用 3 个字节

CHAR与VARCHAR特点比较

• char的特点

  • char表示定长字符串，长度是固定的；
  • 如果插入数据的长度小于char的固定长度时，则用空格填充；
  • 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

• 对于char来说，最多能存放的字符个数为255，和编码无关。

• varchar的特点

  • varchar表示可变长字符串，长度是可变的；
  • 插入的数据是多长，就按照多长来存储；

◾varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；

字符集

• 推荐把MySQL默认字符集设置为 UTF8MB4
• 在多字节字符集下，CHAR与VARCHAR底层的实现完全相同，都是变长存储。
• 鉴于目前推荐字符集为 UTF8MB4，所以在表结构设计时，可以把 CHAR 全部用 VARCHAR替换，底层存储的本质实现一模一样

排序规则

• 排序规则（Collation) 是比较和排序字符串的一种规则
• 绝大部分业务的表结构无须设置排序规则为大小写敏感

正确修改字符集

• 下列语句只是把表的字符集修改为 UTF8MB4，下次新增列时，如不显示指定字符集，新列的字符集就变更为 UTF8MB4，但对于已经存在的列，其默认的字符集并不做修改

alter table emoji_test charset utf8mb4;

• 修改表中已有列的字符集，使用命令如下：

alter table xxx convert to ...;

业务表结构设计

• 用户性别，运行状态等有限列的值，MySQL 8.0.16 版本直接使用 CHECK 约束机制，之前的版本可使用 ENUM 枚举类型串类型，外加 SQL_MODE 的严格模式
• 业务隐私加密，如密码、手机、信用卡等信息，需要加密。切记简单的MD5S算法是可以暴力破解的，并不安全，推荐使用“动态盐+动态加密算法“进行隐私数据的存储
• 状态列的最优设计是用字符串+CHECK约束，这样能避免脏数据的插入，如：

CREATE TABLE...(
   ...
   status CHAR(1)
   ...
   CONSTRAINT `check_status`
   CHECK (status in ('S','D','T','U'))
   ...
)

• 对应图片的存储，一般不建议使用二进制类型BLOB存储，图片存储在对象存储上，数据库只是存储图片对应的链接。

日期类型

• MySQL 数据库常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMP，最常使用的日期类型为 DATETIME 和 TIMESTAMP。
• DATETIME 最终展现形式为：YYYY-MM-DD HH:MM:SS ，固定占用8个字节。
• TIMESTAMP 实际存储内容为'1970-01-01 00:00:00'到现在的毫秒数。在MySQL中，由于类型 TIMESTAMP 占用4个字节，因此其存储的时间上限只能到'2038-01-19 03:14:07'，存在风险
• 若带有毫秒时，类型TIMESTAMP占用7个字节，而 DATETIME无论是否存储毫秒信息，都占用8个字节。
• TIMESTAMP 的最大优点是可以带时区属性。使用TIMESTAMP必须显示设置时区，不要使用默认系统时区，否则存在性能问题，推荐在配置文件中设置参数 time_zone = '+8:00'
• 推荐日期类型使用 DATETIME，而不是 TIMESTAMP 和 INT 类型
  • 性能不如DATETIME：DATETIME 不存在时区转化问题
  • 性能抖动：海量并发时，存在性能抖动问题
• 表设计时，每个核心业务表，推荐设计一个 last_modify_date 的字段，用以记录每条记录的最后修改时间

last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) 
-- 在创建时间字段的时候
DATETIME(6)
6表示可以存储6位的毫秒值
DEFAULT CURRENT_TIMESTAMP
表示当插入数据的时候，该字段默认值为当前时间
ON UPDATE CURRENT_TIMESTAMP
表示每次更新这条数据的时候（只要表中的其他字段发生更改），该字段都会更新成当前时间

非结构存储：JSON数据类型

• 使用 JSON 数据类型，推荐使用 MySQL 8.0.17 以上的版本，性能更好，同时支持 Multi-Valued Indexes;
• JSON 数据类型的好处是无须预先定义列，数据本身就有很好的描述性；
• 不要将有明显关系型的数据用JSON存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据。
• JSON 数据类型推荐使用在不经常更新的静态数据存储。
• 缺点在于过于灵活，对业务没有任何约束，可能导致最终管理非常麻烦。

忘记范式准则

三范式理论

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

工程上的表结构设计实战

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

自增主键设计

使用bigint的自增类型作为主键的设计仅仅适合非核心业务表，比如告警表、日志表等。真正的核心业务表，一定不要用自增键做主键，主要有6个原因。

• 自增存在回溯问题（数据库升级到MySQL 8.0可以解决）。
• 自增值在服务器端产生，存在并发性能问题。
  • 自增值在MySQL服务器端产生的值，就需要一把自增的AL锁保护，若这时有大量的插入请求，就可能存在自增引起的性能瓶颈。如果你想获得自增值的最大并发性能，把参数 innodb_autoinc_lock_mode 设置为2。
• 自增值做主键，只能在当前实例中保持唯一，不能保证全局唯一。
• 公开数据值，容易引发安全问题。
• MGR 可能引发的性能问题。
• 分布式架构设计问题。

UUID主键设计

• MySQL 中的UUID 由以下几个部分组成：

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

• 在存储时间时，UUID是根据时间位逆序存储。
• MySQL 8.0 提供的排序 UUID 性能最好，甚至比自增 ID 还要好。

业务自定义生成主键

• 分布式数据库架构，仅用 UUID 做主键依然是不够的。
• 对于分布式架构的核心业务表，我推荐类似如下的设计：

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

消除冗余

• 一份数据应尽可能保留一份，通过主键关联进行查询，避免冗余数据。
• 在一些场景下，可以通过JSON数据类型进行反范式设计，提升存储效率。

表压缩

• MySQL 中的压缩都是基于页的压缩；
• COMPRESS 页压缩适合用于性能要求不高的业务表，如日志、监控、告警表等；
• COMPRESS 页压缩内存缓冲池存在压缩和解压的两个页，会严重影响性能；
• 对压缩有压缩要求，又不希望有明显退化，直接使用TPC压缩；
• 通过 ALTER TABLE启用 TPC压缩后，还需要执行 OPTIMIZE TABLE 才能立即完成空间的压缩。