mysql8.0规范

有梦想的小何

已于 2024-08-02 17:26:57 修改

阅读量663

点赞数 14

文章标签： java mysql

于 2024-08-02 17:20:25 首次发布

本文链接：https://blog.csdn.net/heyongjin22/article/details/140875886

版权

MySQL 数据库开发规范

前言

在开发中，虽然性能大多决定于架构设计，但是合理的使用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) 等类型的扇出系数就低一些，所以索引效率也会低些。

有梦想的小何

关注

14
点赞
踩
11

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql8.0规范

在开发中，虽然性能大多决定于架构设计，但是合理的使用sql语句，是开发人员的必修课，今天基于mysql官方文档，给大家整理一些mysql的规定。约束度：【强制】规范描述同一项目（产品）中存储相同数据的列类型必须一致，列名必须一致。同业务字段在不同项目数据表需要使用同一字段名。使用 DTS 同步的数据表和原表保持一致的字段定义，表名可根据业务不同。同一个业务线采用统一字符集，避免隐式转换。控制单库表个数，单库表个数不超过 4096 个。
复制链接

扫一扫