【MySQL 】使用原则和设计规范，详细梳理

MySQL 虽然具有很多特性并提供了很多功能，但是有些特性会严重影响它的性能，下面就总结一下。

基本使用原则：

1. MySQL 数据库只用于数据的存储，不进行数据的复杂计算，不承载业务逻辑，确保存储和计算分离；
2. 查询数据时，尽量单表查询，减少跨库查询和多表关联；
3. 还有就是要杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手
   • 大事务，运行步骤较多，涉及的表和字段较多，容易造成资源的争抢，甚至形成死锁。一旦事务回滚，会导致资源占用时间过长。
   • 大 SQL，复杂的 SQL 意味着过多的表的关联，MySQL 数据库处理关联超过 3 张表以上的 SQL 时，占用资源多，性能低下。
   • 大批量，意味着多条 SQL 一次性执行完成，必须确保进行充分的测试，并且在业务低峰时段或者非业务时段执行。
   • 大字段，blob、text 等大字段，尽量少用。必须要用时，尽量与主业务表分离，减少对这类字段的检索和更新。

数据库的基本设置规则：

• 必须指定默认存储引擎为 InnoDB，并且禁用 MyISAM 存储引擎，随着 MySQL 8.0 版本的发布，所有的数据字典表都已经转换成了 InnoDB，MyISAM 存储引擎已成为了历史。

• 默认字符集 UTF8mb4，以前版本的 UTF8 是 UTF8mb3，未包含个别特殊字符，新版本的 UTF8mb4 包含所有字符，官方强烈建议使用此字符集。

• 关闭区分大小写功能。设置 lower_case_tables_name=1，即可关闭区分大小写功能，即大写字母 T 和小写字母 t 一样。

  • 操作步骤：
    • MySQL dump 导出数据库。
    • 修改参数 lower_case_tables_name=1。
    • 导入备份数据时，必须停止数据库，停止业务，影响非常大。
    • 开启 per-table 表空间，开启后，每张业务表会单独创建一个独立于系统表空间的表空间，便于空间的回收，数据的迁移。

规范命名

命名规范如下，命名时的字符取值范围为：a_z，09 和 _（下画线）：

• 所有表名小写，不允许驼峰式命名；
• 允许使用 -（横线）和 （空格）；如下图所示，当使用 -（横线），后台默认会转化成 @002d；
• 不允许使用其他特殊字符作为名称，减少潜在风险。

库名规则为“数据库类型代码 + 项目简称 + 识别代码 + 序号”。

表名的命名规则分为：

• 单表仅使用 a~z、_；
• 分表名称为“表名_编号”；
• 业务表名代表用途、内容：子系统简称_业务含义_后缀。

常见业务表类型有：

• 临时表，tmp；
• 备份表，bak；
• 字典表，dic；
• 日志表，log。

字段名精确，遵循“见名知意”的原则，格式：名称_后缀。

• 避免普遍简单、有歧义的名称。
• 用户表中，用户名的字段为 UserName 比 Name 更好。
• 布尔型的字段，以助动词（has/is）开头。
• 用户是否有留言 hasmessage，用户是否通过检查 ischecked 等。
• 常见后缀如下：
  • 流水号/无意义主键，后缀为 id，比如 task_id；
  • 时间，后缀为 time，insert_time。
  • 程序账号与数据库名称保持一致。如果所有的程序账号都是 root@‘%’，密码也一样，很容易错连到其他的数据库，造成误操作。

表设计规则

• 显式指定需要的属性；
• 创建表时显示指定字符集、存储引擎、注释信息等。
• 不同系统之间，统一规范；
• 不同表之间的相同字段或者关联字段，字段类型/命名要保持一致；
• 库表字符集和前端程序、中间件必须保持一致的 UTF8mb4。

字段设计要求

• 根据业务场景需求，选择合适的类型，最短的长度；确保字段的宽度足够用，但也不要过宽。所有字段必须为 NOT NULL，空值则指定 default 值，空值难以优化，查询效率低。
• 表字段数少而精，尽量不加冗余列。
• 单实例表个数必须控制在 2000 个以内。
• 单表分表个数必须控制在 1024 个以内。
• 单表字段数上限控制在 20~50 个。
• 主键列，无负数，建议使用 INT UNSIGNED 或者 BIGINT UNSIGNED bigint unsigned；预估字段数字取值会超过 42 亿，使用 BIGINT 类型。
• 短数据使用 TINYINT 或 SMALLINT，比如：人类年龄，城市代码。
• 使用 UNSIGNED 存储非负数值，扩大正数的范围。

注意点：
禁用 ENUM、SET 类型。

• 兼容性不好，性能差。
• 解决方案：使用 TINYINT，在 COMMENT 信息中标明被枚举的含义。is_disable TINYINT UNSIGNED DEFAULT ‘0’ COMMENT '0:启用 1:禁用 2:异常’。

禁用列为 NULL：

• MySQL 难以优化 NULL 列；
• NULL 列加索引，需要额外空间；
• 含 NULL 复合索引无效。
• 解决方案：在列上添加 NOT NULL DEFAULT 缺省值。

禁止 VARBINARY、BLOB 存储图片、文件等。

• 禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统中，数据库中存储路径。

不建议使用 TEXT/BLOB：

• 处理性能差；
• 行长度变长；
• 全表扫描代价大。
• 解决方案：拆分成单独的表。

InnoDB 表的注意事项

• 主键列，UNSIGNED 整数，使用 auto_increment；禁止手动更新 auto_increment，可以删除。
• 必须添加 comment 注释。必须添加 comment 注释。必须添加 comment 注释。
• 必须显示指定的 engine。
• 表必备三字段：id、 xxx_create、 xxx_modified。
• id 为主键，类型为 unsigned bigint 等数字类型；
• xxx_create、xxx_modified 的类型均为 datetime 类型，分别记录该条数据的创建时间、修改时间。