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

傷城~ 2023-10-08 17:19 7阅读 0赞

**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 表空间，开启后，每张业务表会单独创建一个独立于系统表空间的表空间，便于空间的回收，数据的迁移。

## 规范命名 ##

**命名规范如下，命名时的字符取值范围为：az，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 类型，分别记录该条数据的创建时间、修改时间。