MySQL实战指南-表结构设计

学习自姜承尧的MySQL实战宝典

表结构设计篇

数字类型

• 不推荐使用整型类型的属性 Unsigned，若非要使用，参数 sql_mode 务必额外添加上选项 NO_UNSIGNED_SUBTRACTION；

• 自增整型类型做主键，务必使用类型 BIGINT，而非 INT，后期表结构调整代价巨大；

• MySQL 8.0 版本前，自增整型会有回溯问题，做业务开发的你一定要了解这个问题；

• 当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误；

• 不要再使用浮点类型 Float、Double，MySQL 后续版本将不再支持上述两种类型；

• 账户余额字段，设计是用整型类型，而不是 DECIMAL 类型，这样性能更好，存储更紧凑，建议使用分的这个单位存储，类型使用bigint。

字符串类型

• 推荐 MySQL 字符集默认设置为 UTF8MB4，可以用于存储 emoji 等扩展字符；
• CHAR 和 VARCHAR 虽然分别用于存储定长和变长字符，但对于变长字符集（如 GBK、UTF8MB4），其本质是一样的，都是变长，设计时完全可以用 VARCHAR 替代 CHAR；
• 绝大部分业务的表结构设计无须设置排序规则为大小写敏感
• 修改表中已有列的字符集，使用命令 ALTER TABLE … CONVERT TO …；
• 用户性别，运行状态等有限值的列，MySQL 8.0.16 版本直接使用 CHECK 约束机制，之前的版本可使用 ENUM 枚举字符串类型，外加 SQL_MODE 的严格模式；
• 业务隐私信息，如密码、手机、信用卡等信息，需要加密。切记简单的MD5算法是可以进行暴力破解，并不安全，推荐使用动态盐+动态加密算法进行隐私数据的存储。
• 订单状态使用整型，因为可能值会变，如果使用枚举或check约束，需要修改表结构，用户头像直接使用varchar存URL。

时间类型

日期类型通常就是使用 DATETIME 和 TIMESTAMP 两种类型，然而由于类型 TIMESTAMP 存在性能问题，建议你还是尽可能使用类型 DATETIME。我总结一下今天的重点内容：

• MySQL 5.6 版本开始 DATETIME 和 TIMESTAMP 精度支持到毫秒；

• DATETIME 占用 8 个字节，TIMESTAMP 占用 4 个字节，DATETIME(6) 依然占用 8 个字节，TIMESTAMP(6) 占用 7 个字节；

• TIMESTAMP 日期存储的上限为 2038-01-19 03:14:07，业务用 TIMESTAMP 存在风险；

• 使用 TIMESTAMP 必须显式地设置时区，不要使用默认系统时区，否则存在性能问题，推荐在配置文件中设置参数 time_zone = ‘+08:00’；

• 推荐日期类型使用 DATETIME，而不是 TIMESTAMP 和 INT 类型；

• 表结构设计时，每个核心业务表，推荐设计一个 last_modify_date 的字段，用以记录每条记录的最后修改时间。

非结构存储JSON

• JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型可以有效解决很多业务中实际问题。

• 使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes；

• JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；

• 不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；

• JSON 数据类型推荐使用在不经常更新的静态数据存储。

忘记范式准则

总的来说，范式是偏数据库理论范畴的表结构设计准则，在实际的工程实践上没有必要严格遵循三范式要求，在 MySQL 海量并发的工程实践上，表结构设计应遵循这样几个规范：

• 每张表一定要有一个主键；

• 自增主键只推荐用在非核心业务表，甚至应避免使用；

• 核心业务表推荐使用 UUID 或业务自定义主键；

• 一份数据应尽可能保留一份，通过主键关联进行查询，避免冗余数据；

• 在一些场景下，可以通过 JSON 数据类型进行反范式设计，提升存储效率；

表压缩

在进行表结构设计时，除了进行列的选择外，还需要考虑存储的设计，特别是对于表的压缩功能的设计，总结来说：

• MySQL 中的压缩都是基于页的压缩；

• COMPRESS 页压缩适合用于性能要求不高的业务表，如日志、监控、告警表等；

• COMPRESS 页压缩内存缓冲池存在压缩和解压的两个页，会严重影响性能；

• 对存储有压缩需求，又希望性能不要有明显退化，推荐使用 TPC 压缩；

• 通过 ALTER TABLE 启用 TPC 压缩后，还需要执行命令 OPTIMIZE TABLE 才能立即完成空间的压缩。