mysql8.0规范

avatar
作者
猴君
阅读量:0

MySQL 数据库开发规范

目录

  1. 背景与目标
  2. 规范列表
  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 EXISTSIN / NOT IN 性能更优。
    • 使用 LOCATE()、POSITION()、INSTR()、FIND_IN_SET() 的性能稍微优于 LIKE。
    • 使用 PREPARED STATEMENT 可以提高性能并避免 SQL 注入。
    • 严禁开发使用 LOCK TABLE 进行人为锁表,仅允许使用 SELECT ... FOR UPDATE 语句。
    • WHERE 条件尽可能避免非等值条件,INBETWEEN<<=>>= 会导致后面的条件使用不了索引。
    • 使用 UNION ALL 代替 UNION
    • UPDATEDELETE 语句不要使用 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)=1where num+1=100
    • 禁止使用 IS NULLIS 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) 等类型的扇出系数就低一些,所以索引效率也会低些。 

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!