MySQL 数据库开发规范

目录

1. 背景与目标
2. 规范列表
   • 1. 库表设计
     • 1.1 必须字段
     • 1.2 命名规范
   • 2. 定义规范
     • 2.1 约束规范
     • 2.2 类型规范
       • 2.2.1 字段类型与长度
       • 2.2.2 状态字段数据类型
       • 2.2.3 布尔型
       • 2.2.4 varchar和text, json
       • 2.2.5 decimal(m,d)
   • 3. 索引规范
   • 4. 其他规范
   • 5. SQL 使用
     • 5.1 索引
     • 5.2 查询
     • 5.3 操作
   • 6. SQL 版本控制
     • 6.1 审核
3. 补充说明

前言

在开发中，虽然性能大多决定于架构设计，但是合理的使用sql语句，是开发人员的必修课，今天基于mysql官方文档，给大家整理一些mysql的规定。

规范列表

规范依据约束力强弱及故障敏感性依次分为【强制】、【推荐】、【参考】三大类。

1. 库表设计

1.1 必须字段

• 约束度：【强制】

• 规范描述：

  • 无特殊需求，默认使用 InnoDB 存储引擎。
  • 基本约束：表设计必须有主键 id、创建时间 create_time、修改时间 update_time。
  • 主键无特殊需求，使用 bigint 和 auto_increment。
  • 数据库默认选取 utf8mb4 作为字符集，只有 utf8mb4 才能存放 emoji 表情符。
  • 每个表的字段数不要超过 50 个（无特殊需求情况）。
  • 根据更新的频繁程度决定字段的顺序。为提高数据库效率，将更新频繁程度高的字段排在表中靠前的位置，越靠后的字段效率越低。

  CREATE TABLE `table_exp` (   `id` bigint unsigned AUTO_INCREMENT NOT NULL COMMENT '主键ID',   `package_id` int unsigned NOT NULL DEFAULT 0 COMMENT '套系id',   `module_id` int unsigned NOT NULL DEFAULT 0 COMMENT '模块id',   `module_name` varchar(64) NOT NULL DEFAULT '' COMMENT '模块名称',   `is_delete` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '是否删除，0-未删除，1-删除，默认为0',   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',   PRIMARY KEY (`id`),   KEY idx_package_id(package_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='模块示例表'; 

1.2 命名规范

• 约束度：【强制】
• 规范描述：
  • 库名、表名、字段名，索引名，别名必须使用小写字母开头，使用"_"分割，不超过 30 个字符，禁止使用 MySQL 保留字，禁止使用字母、下划线和数字以外的其他字符。
  • 临时库、临时表必须以 _tmp_8位日期 结尾，如：order_tmp_20160712
  • 备份库、备份表必须以 _bak_8位日期 结尾，如：order_bak_20160712
  • 【create|alter】table 语句不指定字符集，统一由库定义。

2. 库表设计

2.1 定义规范

• 约束度：【强制】
• 规范描述：
  • 同一项目（产品）中存储相同数据的列类型必须一致，列名必须一致。
  • 同业务字段在不同项目数据表需要使用同一字段名。
  • 使用 DTS 同步的数据表和原表保持一致的字段定义，表名可根据业务不同。
  • 同一个业务线采用统一字符集，避免隐式转换。
  • 控制单库表个数，单库表个数不超过 4096 个。
  • 创建数据库的语句必须包含字符集字句和默认的校验规则。

  CREATE DATABASE IF NOT EXISTS my_database DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_bin; 

2.2 约束规范

• 约束度：【强制】
• 规范描述：
  • 所有表和字段都需要添加注释。
  • 字段设置 not null 非空约束。默认值 0 或 ‘’。

2.3 类型规范

2.2.1 字段类型与长度

• 约束度：【强制**

• 规范描述：

  • 合理分配字段类型和长度，字段值与类型一致避免用字符串存数字等。

  `price` DECIMAL(10,2) NOT NULL; 

2.2.2 状态字段数据类型

• 约束度：【强制】【推荐】

• 规范描述：

  • 表示状态字段使用 TINYINT UNSIGNED，禁止使用枚举类型定义，
  • 注释必须清晰地说明每个状态的含义，以及是否多选等。

  `status` TINYINT UNSIGNED NOT NULL COMMENT '1: 启用，0: 禁用'; 

2.2.3 布尔型

• 约束度：【强制】【推荐】

• 规范描述：

  • 注释必须清晰地说明每个值的含义。
  • 表达是否概念或有限 list 的，应该用 unsigned tinyint。

  `is_active` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '1: 启用，0: 禁用'; 

2.2.4 varchar和text, json

• 约束度：【强制**
• 规范描述：
  • 可变长度 varchar 类型，长度不建议超过 1000。如果超过 4000，必须分离到单表，以主键和主表关联，避免影响其他字段的数据效率。
  • text 字段类型，必须分离到单表，以主键和主表关联，避免影响其他字段的数据效率。

2.2.5 decimal(m,d)

• 约束度：【强制**
• 规范描述：
  • 价格或需要精确数值的字段使用 decimal，避免使用 float 或 double。

3. 索引规范

• 约束度：【强制**
• 规范描述：
  • 一两个的查询字段和关联字段、where 字句字段可以考虑建立覆盖索引。
  • 唯一索引使用 uk_[字段名] 来命名；
  • 非唯一索引使用 idx_[字段名] 来命名。
  • 长字符串采用前缀索引，长度通过 count(distinct left(col_name,n))/count(1) 来计算，达到 90% 即可。
  • 使用组合索引，字段顺序按区分度高低排列（满足最左匹配原则为优先）。
  • 索引必须创建在索引选择性较高的列上。
  • 联合索引的第一个字段，必须在 where 子句中。联合索引中将索引选择性高的字段靠前放。
  • 禁止使用外键。容易产生死锁，且影响性能。
  • TEXT 类型字段必须使用前缀索引。
  • 单表的索引数量控制在 7 个以内，把索引建在 SELECT 操作比较频繁且数据量大的表，经常有大批量插入、更新操作的表尽量少建索引。组合索引的字段数不超过 5 个。
  • 禁止对过长的 VARCHAR 类型字段建立索引。MySQL 的 VARCHAR 索引只支持不超过 768 个字节，utf8 一个字符三字节，即：768/3=256，所以最长支持 255 个字符的字段创建索引。除了前缀索引外超过 32 字符的 VARCHAR 列加索引需要 DBA 评估。

4. 其他规范

• 约束度：【强制**
• 规范描述：
  • 禁止使用存储过程、触发器、视图、Event、自定义函数、外键约束。
  • 无特殊需求，严禁使用分区表。
  • 进行大批量操作时必须分批提交，每次数据量操作不能超过 10 万条。
  • LAST_INSERT_ID() 函数只能返回当前 SESSION 最近一次 INSERT 操作之后所使用到的 AUTO_INCREMENT 类型字段的值。
  • 用 IN() / UNION 替代 OR，并注意 IN 的个数不要超过 300，IN 的性能高于 OR，而 EXISTS / NOT EXISTS 比 IN / NOT IN 性能更优。
  • 使用 LOCATE()、POSITION()、INSTR()、FIND_IN_SET() 的性能稍微优于 LIKE。
  • 使用 PREPARED STATEMENT 可以提高性能并避免 SQL 注入。
  • 严禁开发使用 LOCK TABLE 进行人为锁表，仅允许使用 SELECT ... FOR UPDATE 语句。
  • WHERE 条件尽可能避免非等值条件，IN、BETWEEN，<，<=，>，>= 会导致后面的条件使用不了索引。
  • 使用 UNION ALL 代替 UNION。
  • UPDATE、DELETE 语句不要使用 LIMIT。
  • INSERT 语句必须指明字段名称，避免后期因为字段扩展而影响原有应用程序。
  • INSERT 使用 BULK 提交，VALUES 的个数不宜过多。BULK 提交可以提高写的效率。
  • 拆分复杂的 SQL 为多个小 SQL，避免大事务。
  • 尽量采用批量 SQL 语句：
    • INSERT ... ON DUPLICATE KEY UPDATE
    • INSERT IGNORE
    • INSERT INTO VALUES()
    • REPLACE INTO
  • 对同一个表的多次 ALTER 操作必须合并为一次操作，开发使用 ALTER 需要 DBA 进行严格审核。

5. SQL 使用

5.1 索引

• 约束度：【强制**
• 规范描述：
  • WHERE 字段列禁止使用表达式或函数，它们不会使用该列上的索引，如：WHERE month(create_time)=1 或 where num+1=100。
  • 禁止使用 IS NULL 或 IS NOT NULL。
  • OR 两边字段都应该有索引。
  • 两百万以上大表禁止使用全模糊查询，如 LIKE '%keywork%'。此类业务推荐使用搜索引擎或者全文索引。
  • 对于电话等末位匹配查询，推荐反向存储数值，查询时采用“前缀”。
  • WHERE 字段列和字段值类型应该一致，避免隐式转换。
  • 区间查询应该使用封闭区间，避免 [-∞,n] 和 [n,+∞] 不可控范围。
  • WHERE 字段应该根据统一补充条件，避免索引过度。

5.2 查询

• 约束度：【强制**
• 规范描述：
  • 只允许 2 表关联，禁止使用 3 个表及以上的关联查询。
  • 关联查询时，被关联的字段需要有索引，多字段关联应该使用联合索引，关联字段数据类型和字符集必须一致避免索引失效。
  • ORDER BY 语句必须跟 LIMIT n 限制条件。
  • 统计查询禁止使用 ORDER BY。

5.3 操作

• 约束度：【强制**
• 规范描述：
  • 代码中禁用 SELECT *，必须指定列名。

  SELECT column1, column2 FROM table_name; 

  • 所有内连接的 SQL 语句必须使用 INNER JOIN（JOIN） ... ON ..，外连接必须使用 LEFT JOIN（LEFT OUTER JOIN） ... ON。不使用 RIGHT JOIN。
  • 代码中 INSERT INTO table 必须指定列名与值的对应关系。

6. SQL 版本控制

6.1 审核

• 约束度：【强制**
• 规范描述：
  • 版本上线更新，必须提供 SQL 原型文件进行审核。
  • SQL 脚本变更和初始化必须存储在代码仓库版本的同一级目录，建立单独的目录。
  • lshm_admin_v1.0.0.0_liaozr_20201121_001.sql 为初始全量 SQL 脚本，下一个 SQL 脚本为差异 SQL 脚本，按版本号顺序依次迭代。

补充说明

• rowid 生成器：举例 雪花算法。
• 数据库设计示例：建议覆盖所有规约项。
• 集中业务字段：将常用的基础业务字段集中管控起来（字段名、字段标题、字段类型、字段长度），方便业务统一。

附录 1: 字段定义长度与数据页及效率的计算

因为 MySQL 是索引组织表，所以常规情况下，操作 MySQL 的表都是根据索引进行的，即使全表扫描，也是如此。索引通常用 B+树 来实现。

数据只保存在绿色的叶子结点，非叶子结点都是用来索引叶子结点的。 假设索引高度为 h，那么每次索引查询都要查询 h 个索引页面才能找到叶子结点的索引数据。 假设每行记录大小为 1KB，则每个叶子页面可以容纳 16 行，则总共可索引的行数为100W*16=1600W！ 在高度 h=4 时，总行数=1000^3*16=160亿条！ 

对于 bigint 的主键表来说，通常索引树的高度在 2~4 个。

索引字段的数据类型越简单，效率越好。例如：int 或 tinyint，索引效率会更好，而 varchar(40) 等类型的扇出系数就低一些，所以索引效率也会低些。