数字类型：避免自增踩坑

• 不推荐使用整型类型的属性 Unsigned，若非要使用，参数 sql_mode 务必额外添加上选项 NO_UNSIGNED_SUBTRACTION； MySQL 要求 unsigned 数值相减之后依然为 unsigned，否则就会报错
• 自增整型类型做主键，务必使用类型 BIGINT，而非 INT，后期表结构调整代价巨大； INT 容易到达上限
• MySQL 8.0 版本前，自增整型会有回溯问题，自增计数在内存中，重启后自增为每张表的 max(id) 开始 升级 MySQL 版本到 8.0 版本，每张表的自增值会持久化；
• 当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误；
• 不要再使用浮点类型 Float、Double，MySQL 后续版本将不再支持上述两种类型；
• 账户余额字段，设计是用整型类型，而不是 DECIMAL 类型，这样性能更好，存储更紧凑。 定长数据存储效率更高，变长数据容易发生页分，原来的空间太小，需要申请一个更大的空间，原来的空间被标记为删除，并且后续不会被使用，就像一个“空洞”一样。除非人为的进行表空间的碎片整理

字符串类型：不能忽略的 COLLATION

• CHAR 和 VARCHAR 虽然分别用于存储定长和变长字符，但对于变长字符集（如 GBK、UTF8MB4），其本质是一样的，都是变长，设计时完全可以用 VARCHAR 替代 CHAR； 设计表结构时，指定数据类型 char(n) 和varchar(n) 后更的数字 n 是指定能存储的字符数量，是字符而不是字节
• 推荐 MySQL 字符集默认设置为 UTF8MB4，可以用于存储 emoji 等扩展字符；
• 排序规则很重要，用于字符的比较和排序，但大部分场景不需要用区分大小写的排序规则； 排序规则以 _ci 结尾，表示不区分大小写（Case Insentive），_cs 表示大小写敏感，_bin 表示通过存储字符的二进制进行比较
• 修改表中已有列的字符集，使用命令 ALTER TABLE ... CONVERT TO ....；
• 用户性别，运行状态等有限值的列，MySQL 8.0.16 版本直接使用 CHECK 约束机制，之前的版本可使用 ENUM 枚举字符串类型，外加 SQL_MODE 的严格模式；
• 业务隐私信息，如密码、手机、信用卡等信息，需要加密。切记简单的MD5算法是可以进行暴力破解，并不安全，推荐使用动态盐+动态加密算法进行隐私数据的存储。 $salt$cryption_algorithm$value

日期类型：TIMESTAMP 可能是巨坑

类型 DATETIME 最终展现的形式为：YYYY-MM-DD HH：MM：SS，固定占用 8 个字节。

• MySQL 5.6 版本开始 DATETIME 和 TIMESTAMP 精度支持到毫秒；
• DATETIME 占用 8 个字节，TIMESTAMP 占用 4 个字节，DATETIME(6) 依然占用 8 个字节，TIMESTAMP(6) 占用 7 个字节； TIMESTAMP 存储带毫秒的话，占用 7 个字节，DATETIME 无论是否存储毫秒信息，都占用 8 个字节
• TIMESTAMP 日期存储的上限为 2038-01-19 03:14:07，业务用 TIMESTAMP 存在风险；
• 使用 TIMESTAMP 必须显式地设置时区，不要使用默认系统时区，否则存在性能问题（系统锁），推荐在配置文件中设置参数 time_zone = '+08:00'直接指定时区
• 推荐日期类型使用 DATETIME，而不是 TIMESTAMP 和 INT 类型；
• 表结构设计时，每个核心业务表，推荐设计一个 last_modify_date 的字段，用以记录每条记录的最后修改时

非结构存储：用好 JSON 这张牌

• 使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes；
• JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；
• 不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；
• JSON 数据类型推荐使用在不经常更新的静态数据存储。

表结构设计：忘记范式准则

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

• 自增主键只推荐用在非核心业务表，甚至应避免使用； 推荐使用有序的 uuid 或者业务自增主键，8.0 的 uuid_to_bin，取消了 "-" 并且根据时间有序 自增值在服务器端产生，存在并发性能问题 INSERTINTO ... VALUES (NULL,...),(NULL,...),(NULL,...); 参数 innodb_autoinc_lock_mode 用于控制自增锁持有的时间 | 参数值 | 持有自增锁时间 | 持有/释放自增锁次数 | | --- | --- | --- | | 0 | 5.1 版本之前的表锁，不推荐 | | | 1 | 每条 sql 结束后释放自增锁 | 1 | | 2 | 每次自增释放自增锁 | 3 |

  参数设置成 2 性能最高，当时可能 id 不是连续的

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

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

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

表压缩：不仅仅是空间压缩

• MySQL 中的压缩都是基于页的压缩；
• COMPRESS 页压缩适合用于性能要求不高的业务表，如日志、监控、告警表等；
• COMPRESS 页压缩内存缓冲池存在压缩和解压的两个页，会严重影响性能；
• 对存储有压缩需求，又希望性能不要有明显退化，推荐使用 TPC 压缩； 所有页的读写操作都和非压缩页一样，没有开销，只有当这个页需要刷新到磁盘时，才会触发页压缩功能一次 一个 16K 的页压缩后是 8K，接着数据库会对这 16K 的页剩余的 8K 填充0x00，这样当这个 16K 的页写入到磁盘时，利用文件系统空洞特性，则实际将仅占用 8K 的物理存储空间（填充 0x00 的磁盘为空洞，下次需要 8k 大小的空间直接使用这个，而不用再次申请）
• 通过 ALTER TABLE 启用 TPC 压缩后，还需要执行命令 OPTIMIZE TABLE 才能立即完成空间的压缩。

索引

B+ 树索引由根节点（root node）、中间节点（non leaf node）、叶子节点（leaf node）组成 当 B+ 树的高度大于等于 2 时，根节点和中间节点存放的是索引键对，由（索引键、指针）组成。 索引键就是排序的列，而指针是指向下一层的地址，在 MySQL 的 InnoDB 存储引擎中占用 6 个字节

高度为 2 的 B+ 树，理论上能存放多少行记录？ 主键为 bigint 类型，8B；指针占用 6B；页大小为 16K，即 16384B

根节点能最多存放以下多个键值对：16384/(8+6)= 1,170

假设每条记录占用 500B

叶子节点能存放的最多记录：16384 / 500 ≈ 32

综上所述

总记录数 = 1,170 * 32 = 37,440

树高度为 3 的 B+ 树索引，最多能存放的记录数为：

总记录数 = 1,170 * 1,170 * 32 = 43,804,800

理论情况下，高度为 3 的 B+ 树索引竟然能存放 4 千万条记录 真实环境中，每个页其实利用率并没有这么高，还会存在一些碎片的情况，但 3 层还是至少能存储几百万的数据的

• 索引是加快查询的一种数据结构，其原理是插入时对数据排序，缺点是会影响插入的性能；
• MySQL 当前支持 B+树索引、全文索引、R 树索引；
• B+ 树索引的高度通常为 3~4 层，高度为 4 的 B+ 树能存放 50 亿左右的数据；
• 由于 B+ 树的高度不高，查询效率极高，50 亿的数据也只需要插叙 4 次 I/O；
• MySQL 单表的索引没有个数限制，业务查询有具体需要，创建即可，不要迷信个数限制；
• 可以通过表 sys.schema_unused_indexes 和索引不可见特性，删除无用的索引。