v6ole
962 lines
48 KiB
Markdown
962 lines
48 KiB
Markdown
# 中国电信软件研发规范数据库设计分册（修订版）
|
||
|
||
> 脱敏整理版：已移除编制人员、联系人和联系方式，并合并无意义硬换行。技术条款、章节和示例以原始 DOCX 为争议核验依据。
|
||
|
||
中国电信软件研发规范数据库设计分册 （修订版）
|
||
|
||
中国电信集团有限公司
|
||
|
||
## 2023 年 12 月
|
||
|
||
i
|
||
|
||
> 编制人员信息已移除。
|
||
|
||
版本变更历史
|
||
|
||
## 1 文档说明
|
||
|
||
### 1.1 编制说明
|
||
|
||
为进一步提升软件开发中的数据库设计和数据库操作流程规范化水平，提高数据库系统稳定性，编制本数据库设计分册，用于指导和规范全集团的数据库设计工作， 提升软件质量。
|
||
|
||
### 1.2 适用范围
|
||
|
||
本规范适用于指导中国电信软件研发工作。
|
||
|
||
### 1.3 起草单位
|
||
|
||
本规范的起草单位是中国电信集团公司。
|
||
|
||
### 1.4 解释权
|
||
|
||
本规范解释权属于中国电信集团公司。
|
||
|
||
### 1.5 版权
|
||
|
||
本规范的版权属于中国电信集团公司。
|
||
|
||
### 1.6 名词解释
|
||
|
||
1
|
||
|
||
2
|
||
|
||
3
|
||
|
||
## 2 数据库设计规范
|
||
|
||
### 2.1 命名规范
|
||
|
||
#### 2.1.1 库名命名
|
||
|
||
1) 【强制】库名只能含有字母 、数字和下划线“_ ”三类字符。
|
||
|
||
2) 【强制】库名必须使用小写字母， 用下划线“_ ”分割。
|
||
|
||
3) 【强制】库名禁止超过 32 个字符， 须见名知意。
|
||
|
||
4) 【强制】库名禁止使用数据库特殊关键字命名。
|
||
|
||
5) 【强制】 临时库库名必须以 tmp 开头， 以创建人的简拼字母和日期为后缀。
|
||
|
||
6) 【强制】备份库库名必须以 bak 开头， 以创建人的简拼字母和日期为后缀。
|
||
|
||
#### 2.1.2 表名命名
|
||
|
||
1) 【强制】表名只能含有字母 、数字和下划线“_ ”三类字符。
|
||
|
||
2） 【推荐】表名必须使用小写字母， 用下划线“_ ”分割， 普通业务表以 t_开头（或有意义的简写）_+table_name。
|
||
|
||
3） 【强制】表名禁止超过 32 个字符， 须见名知意。
|
||
|
||
4） 【强制】表名禁止使用数据库特殊关键字命名。
|
||
|
||
5） 【强制】 临时表表名必须以 tmp 开头， 以创建人的简拼字母和日期为后缀。正例： 示例创建人在 2022 年 4 月 21 日建了一张临时表， tmp_xxxxxxx_zs20220421
|
||
|
||
6） 【强制】备份表表名必须以 bak 开头， 以创建人的简拼字母和日期为后缀。正例： 示例创建人在 2022 年 4 月 21 日建了一张备份表， bak_xxxxxxx_zs20220421
|
||
|
||
#### 2.1.3 字段名命名
|
||
|
||
1）【强制】字段名必须使用小写字母， 用下划线“_ ”分割。 4
|
||
|
||
2）【强制】字段名禁止超过 32 个字符， 须见名知意。
|
||
|
||
3）【强制】字段名禁止使用数据库特殊关键字命名。
|
||
|
||
#### 2.1.4 索引命名
|
||
|
||
1) 【推荐】非唯一索引必须以 idx_字段 1_字段 2 命名。
|
||
|
||
2) 【推荐】 唯一索引必须以uidx_字段 1_字段 2 命名。
|
||
|
||
3) 【强制】索引名称必须全部小写。
|
||
|
||
#### 2.1.5 用户名命名
|
||
|
||
1） 【推荐】生产业务用户名推荐： 库名_app。
|
||
|
||
2） 【推荐】生产只读用户名推荐： 库名_read。
|
||
|
||
3） 【推荐】线下研发查询账号用户名推荐： 库名_devread。
|
||
|
||
4） 【推荐】监控用户名推荐： 库名_monitor。
|
||
|
||
5） 【推荐】大数据抽数用户名推荐： 库名_dataextract。
|
||
|
||
6） 【推荐】大数据推数用户名推荐： 库名_dataimport。
|
||
|
||
7） 【推荐】DBA 管理员用户名推荐： 库名_dbaadmin。
|
||
|
||
8） 【推荐】备份用户名推荐： 库名_bakadmin。
|
||
|
||
#### 2.1.6 设计范式
|
||
|
||
在数据库设计中，满足第一范式是最基本要求，一般说来，数据库的逻辑模型满足第三范式即可，在物理模型和持久化时就不一定要遵循第三范式，而会增加一定的数据冗余以提高查询性能。
|
||
|
||
5
|
||
|
||
### 2.2 SQL 设计规范
|
||
|
||
#### 2.2.1 库 、表 、字段 、索引 SQL 设计规范
|
||
|
||
1) 【强制】创建表时， 所有表和字段都必须添加注释。
|
||
|
||
2) 【强制】禁止使用 BLOB 、TEXT 类型的字段， 如果要使用提前找 DBA 进行评估审核。
|
||
|
||
3) 【强制】作为表间连接关系的字段，数据类型必须保持严格一致，避免索引无法正常使用。
|
||
|
||
4) 【强制】快速增长的表， 比如充值记录 、订单记录， 必须考虑清理机制。
|
||
|
||
5) 【推荐】对表结构多列变更用逗号分隔， 而不是写多条变更语句。
|
||
|
||
正例： ALTER TABLE xxx ADD COLUMN name VARCHAR(100),MODIFY COLUMN address VARCHAR(1000),CHANGE COLUMN comment VARCHAR(200);#这样数据库只做一次数据复制；#如果写成多条， 每变更一次复制一次数据。反例： ALTER TABLE stu ADD COLUMN sex CHAR(1) NULL comment '性别' AFTER age;
|
||
|
||
6) 【强制】禁止使用外键， 如果要使用提前找 DBA 进行评估审核。
|
||
|
||
说明：外键用来保护参照完整性，可在业务端实现。对父表和子表的操作会相互影响， 降低可用性。
|
||
|
||
7) 【强制】SELECT、UPDATE、DELETE 语句的 WHERE 条件列必须添加索引，
|
||
|
||
除了一些低基数列可以不加。说明： 低基数列指一些选择性低的列， 例如“性别 ”、”状态 ”等。
|
||
|
||
8) 【强制】不在 WHERE 条件的索引列进行数学运算和函数运算，这会导致无法使用列索引。
|
||
|
||
正例： WHERE id +1 = 5 可以改成 WHERE id = 5 - 1。反例： SELECT offer_inst_id,offer_id, owner_cust_id,status_cd
|
||
|
||
6
|
||
|
||
WHERE commodity_type IN ('40','50') AND status_cd = '1000'AND offer_inst_id > 95050740400 AND To_days(Now()) - To_days(exp_date) >= 1#To_days( EXP_DATE )， 字段使用函数， 不能走索引。
|
||
|
||
9) 【强制】禁止使用前缀是%的 LIKE， 如果确实必要需增加审批程序。
|
||
|
||
说明：在 SQL 中尽量不使用 LIKE。即使使用也要禁止使用前缀是%的 LIKE 匹配， 因为索引文件具有 B Tree 的最左前缀匹配特性， 如果左边的值未确定，那么无法使用此索引。反例：SELECT cust_id,cust_code WHERE cust_name LIKE ‘%ja% ’;正例： SELECT cust_id,cust_code WHERE cust_name LIKE ‘ja% ’;
|
||
|
||
10) 【强制】禁止在开发代码中使用TRUNCATE TABLE 语句。
|
||
|
||
说明： TRUNCATE TABLE 可能会造成生产的性能事故和安全事故 。整表数据删除时，TRUNCATE 比 DELETE FROM 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 执行不当有可能造成事故，且需要 create 和 drop 两个权限，故不建议在应用开发代码中使用此语句 。如果是 PXC 复制架构的环境， 严禁使用 truncate， 以免产生集群锁。
|
||
|
||
11) 【强制】DELETE FROM 、UPDATE 语句， 必须带 WHERE 条件。
|
||
|
||
说明：MySQL 任何复制架构严禁在业务高峰期使用没有 where 条件的 delete from语句一次性删除所有数据， 需分批删除 。delete from删掉的大表数据， 应选业务空闲期， 整理表碎片， 回收存储空间。
|
||
|
||
12) 【强制】SQL 语句不可以出现隐式转换， 查询条件需要保证数据类型一致。说明： 隐式转换， 比如： int 同 char 进行比较。
|
||
|
||
13) 【推荐】SQL 语句尽可能简单， 复杂 SQL 可拆分多个简单 SQL。
|
||
|
||
14) 【推荐】事务要简单，整个事务的更新记录数不要太大，时间长度不要太长，要及时提交（多事务， 小事务原则） 。
|
||
|
||
7
|
||
|
||
15) 【推荐】避免使用反向查找， 如 NOT IN。
|
||
|
||
16) 【推荐】避免超过 3 张表做关联， 如果要超过需提前找 DBA 进行评估审核。
|
||
|
||
17) 【推荐】IN 操作能避免则避免， 若实在避免不了， 需要仔细评估 IN 后边的集合元素数量， 控制在 500 个之内。
|
||
|
||
18) 【推荐】核心业务超过 100 毫秒的查询就是慢查询， 需要优化 SQL 脚本。
|
||
|
||
### 2.3 流程规范
|
||
|
||
#### 2.3.1 概述
|
||
|
||
1) 【强制】所有的建表操作需要提前告知建该表的目的和当前使用的 sql 以及该表使用的相关业务场景。
|
||
|
||
2) 【强制】所有的建表需要确定建立哪些索引后才可以建表上线。
|
||
|
||
3) 【强制】所有的改表结构 、加索引操作都需要将涉及到所改表的查询 sql 需提前告知 DBA。
|
||
|
||
4) 【强制】DDL 操作，必须至少提前一天向 DBA 发起申请， 由 DBA 对操作内容和时间进行评估。
|
||
|
||
5) 【强制】批量读取 、刷新 、导入 、导出数据， 超过 5 万条记录， 必须提前通知 DBA 协助观察。
|
||
|
||
6) 【强制】禁止有 super 权限的应用程序账号存在。
|
||
|
||
7) 【强制】严禁在业务高峰期大批量更新（超过 10 万条数据） 、插入或查询数据库。
|
||
|
||
说明： 如有违反极易造成生产事故。
|
||
|
||
8) 【强制】严禁在业务高峰期 ALTER 表结构。
|
||
|
||
说明： 如有违反极易造成生产事故。
|
||
|
||
9) 【强制】严禁在业务高峰期创建索引。
|
||
|
||
说明： 如有违反极易造成生产事故。
|
||
|
||
10) 【强制】严禁在业务高峰期更新索引统计信息（ANALYZE TABLE 表名） 。说明： 如有违反极易造成生产事故。
|
||
|
||
8
|
||
|
||
11) 【强制】严禁在业务高峰期整理表碎片。
|
||
|
||
说明： 如有违反极易造成生产事故。
|
||
|
||
12) 【强制】业务高峰期 DDL 变更，必须提前向 DBA 发起申请， 由 DBA 对操作内容和时间进行评估。
|
||
|
||
13) 【强制】数据库慢查询语句必须一周内完成整改。
|
||
|
||
14) 【强制】禁止随意在生产环境进行数据库压力测试， 压力测试必须提前向DBA 发起申请， 由 DBA 对操作内容和时间进行评估。
|
||
|
||
15) 【强制】禁止从开发环境 、测试环境数据库直接连接生产环境数据库。
|
||
|
||
16) 【推荐】发布到生产的表结构应设有主键。
|
||
|
||
17) 【强制】严禁在任何复制架构的从库上执行 select 以外的其他语句， 严禁任何写操作的发生。
|
||
|
||
18) 【强制】严禁在任何复制架构的主库上执行导出操作（比如 MySQLdump 等），只能在从库执行。
|
||
|
||
19) 【强制】禁止在生产环境发布大事务 SQL 。代码中发现有大事务的， 应及时拆分或者采用其他方式避免。
|
||
|
||
20) 【强制】数据库账户用途要明确， 不能存在非法的账户。
|
||
|
||
21) 【强制】创建用户的时候限制用户的登录主机，主机使用 IP 地址或 IP 网段，禁止使用主机名或%。
|
||
|
||
22) 【强制】初始化数据库后删除无密码的用户， 删除测试库。
|
||
|
||
23) 【强制】为每个用户设置满足密码复杂度不低于 16 位的包含大小写特殊字符的密码。
|
||
|
||
24) 【强制】定期清理不需要的用户， 非平台业务类的账号要定期修改密码。
|
||
|
||
25) 【强制】脚本涉及账户 、 口令等重要信息的脚本不得随意泄漏和传播 。脚本需项目专人统一保存， 并进行版本管理 。上线前需做到同行评审。
|
||
|
||
26) 【强制】生产数据库导出到非生产环境的， 需要经过流程报备， 并对敏感数据进行脱敏处理。
|
||
|
||
27) 【推荐】定期监控数据库数据目录空间使用率。
|
||
|
||
28) 【推荐】对于程序连接数据库的账号只授予能满足需要的最小权限。
|
||
|
||
说明：程序使用数据库账号只能在一个 DB 下使用，不准跨库程序使用的账9
|
||
|
||
号， 原则上不准有 drop 权限。
|
||
|
||
29) 【推荐】对于超过 100 万行的大表或者核心表进行表结构更改，须经过 DBA审核， 并在业务低峰期执行。
|
||
|
||
30) 【推荐】数据变更时， 如删除和修改记录， 删除整张表， 要先 select， 校验操作的数据， 确认数据无误， 并备份原始数据， 准备好回滚方案后， 才能执行生产变更。
|
||
|
||
31) 【推荐】数据库账户新增或已存在账户的权限调整均需要经过流程审批、并录入系统。
|
||
|
||
10
|
||
|
||
## 3 主流数据库开发规范
|
||
|
||
### 3.1 PostgreSQL 开发规范
|
||
|
||
本规范适用于 PostgreSQL 12.3 及以前所有内核版本，同样也适用于 TeleDB4PG
|
||
|
||
#### 3.1.1 对象名称规范
|
||
|
||
1) 【推荐】DB Name（数据库名）与 service name（业务名）保持一致， 是所有基础设施的名称
|
||
|
||
2) 【强制】DB name 与 table name 需要注意长度限制， 不能超过 64 位
|
||
|
||
3) 【强制】DB 内的对象名只能使用小写字母 、数字 、下划线， 不能使用其他字符
|
||
|
||
4) 【强制】query 中的别名只能使用小写字母 、数字 、下划线， 不能使用其他字符
|
||
|
||
5) 【推荐】主键索引应以 pk_ 开头，唯一索引必须以uidx_字段 1_字段 2 命名，非唯一索引必须以 idx_字段 1_字段 2 命名，不足以区分时可以增加表名等辅助信息
|
||
|
||
6) 【推荐】不同业务的数据以 database 进行区分， 默认都使用 public schema，以方便开发人员与其他数据库使用习惯兼容
|
||
|
||
7) 【推荐】建议所以可以添加comment 的地方均添加comment， 且以英文描述
|
||
|
||
#### 3.1.2 对象设计规范
|
||
|
||
1) 【推荐】PG 中最常用的是数字 、字符 、时间类型 。设计时应尽可能选择合适的数据类型， 能用数字， 就不用字符串 。能用限定长度的varchar 类型，就不用大对象类型 。使用正确的数据类型， 可以匹配数据库的索引， 操作符和函数， 可以提高数据的查询效率。
|
||
|
||
2) 【强制】在创建表结构时， 需要考虑创建对应的索引， 避免全表扫描。
|
||
|
||
11
|
||
|
||
3) 【强制】多个 table 中相同的列， 或者进行 join 的列， 需要保证列名一致，数据类型一致。
|
||
|
||
4) 【推荐】Btree索引的字段不建议超过 2000 字符， 如果超过， 建议使用函数索引或分词索引。
|
||
|
||
5) 【强制】表结构中定义的数据类型，必须与应用程序中的定义一致， 表之间的校对规则一致， 避免报错或无法使用索引的情况发生。
|
||
|
||
6) 【推荐】考虑全球化需求，所有字符存储和表示，均以 UTF-8 编码。所有数据内与时间相关的数据， 时区均为 UTC 时间， 最好使用 int 或 bigint 存储秒或毫秒 。业务程序可以根 据需求， 进行前端显示的时区转换。
|
||
|
||
7) 【强制】PG 中应尽量避免触发器的使用， 这会使数据处理和数据库迁移逻辑复杂， 不便于调试。
|
||
|
||
8) 【强制】有定时海量数据需要归档和删除的表， 应考虑表按时间列分区， 归档后清理时， 不 要使用 delete， 而是用 drop 或 truncate 清理对应表。
|
||
|
||
9) 【强制】未使用的大对象，一定要定时删除部分数据，否则大对象就会一直在数据库中， 占用内存导致内存泄露。
|
||
|
||
10) 【推荐】对于大型文本类数据进行查询时， 要尽量避免 like %xxx% 的模糊匹配 。如果实在有类似需求， 可以考虑在 PG 中建立 gin 索引， 或者使用专门的 ES 等搜索系统。
|
||
|
||
11) 【强制】对于频繁使用的大表（大小超过 10GB， 或者记录数超过 1000 万）应考虑进行分区， 保证单表比较小， 可以提升查询效率 、更新的效率 、创建索引的效率 、备份恢复的效率等
|
||
|
||
#### 3.1.3 大对象设计规范
|
||
|
||
1) 【强制】PG 在设计表结构时， 建议尽量规划好， 避免后续经常添加新列，
|
||
|
||
或者修改数据类型。某些操作可能会触发表的重写， 例如添加新列并有默认值， 修改字段的类型。
|
||
|
||
2) 【强制】如果用户不好规划结构， 可以考虑使用 jsonb 类型存储用户数据。
|
||
|
||
3) 【强制】Jsonb 类型的存储内容需要合理规划， 对于经常需要查询的 value，也可以创建单个字段或 多个字段的 btree索引， 提高查询性能。
|
||
|
||
12
|
||
|
||
4) 【推荐】使用 jsonb 修改数据时， 建议使用 jsonb_set 函数， 同时区分好如果修改对象上一级是否存在， 修改对象不存在时， 是否需要新增。
|
||
|
||
5) 【强制】 对于 jsonb 中的对象，不要存放过多数据。如果数据过多， 会影响查询和更改性能 。可以考 虑规划存放不同数据在多个列， 或多个表中。
|
||
|
||
6) 【强制】如果 jsonb 中查询对象不是一个 value， 而是一个集合， 则需要将集合根据范式设计， 拆分到新表中， 避免查询时扫描所有的 jsonb 数据。
|
||
|
||
7) 【推荐】对于 jsonb 中一些需要进行检索的内容， 可以考虑创建 gin 索引。
|
||
|
||
#### 3.1.4 查询规范
|
||
|
||
1) 【强制】PG 在查询数据时，要在 select 后写明需要查询的所有列名，不要返回不使用的任何字段， 不要使用 select * ， 这样会查询过多内容， 也可能出现程序匹配错误。
|
||
|
||
2) 【强制】PG 在查询时， 一定要考虑查询返回数据量， 避免一次 SQL 返回过多数据， 影响查询性能和网络。
|
||
|
||
3) 【强制】在统计数量时， 应使用 count(*)， 而不用 count(col_name)， 或者count(1)。
|
||
|
||
4) 【强制】在 count 多列列名时， 必须使用括号 count(col1,col2,col3)。
|
||
|
||
5) 【强制】在查询中应清楚 NULL 值的含义和使用， NULL 值不是任意一个确定的值，NULL 与任意值逻辑判断都返回 NULL。例如在 count(distinct col)中，只计算非 NULL 列的不重复结果， NULL 列不会被计算。
|
||
|
||
6) 【强制】应避免向客户端返回大量的数据, ETL 程序除外 。若返回数据量过大， 应考虑需求是否合理。
|
||
|
||
#### 3.1.5 数据操作规范
|
||
|
||
1) 【强制】PG 数据订正时， 删除和修改数据时， 要先 select， 避免误删除， 要确认无误后才能提交执行。
|
||
|
||
13
|
||
|
||
2) 【强制】大批量删除和更新数据时， 不要在一个事物中完成， 建议分批次操作， 避免一次产生较多 垃圾和日志， 对系统资源和相关系统产生不好的影响。
|
||
|
||
3) 【强制】大批量数据入库，可以使用 copy 语法，或者 insert into table value(),(),();的方式， 提高写入速度。
|
||
|
||
4) 【强制】DDL 操作与其他可能获取大锁的操作（如 vacuum full，create index）可以设置锁等待， 防止堵塞与 DDL 锁相关的所有 query。
|
||
|
||
5) 【强制】 可以使用 explain 查询 SQL 的执行计划， 使用 explain analyze 就会实际执行 SQL 并显示对 应的执行计划。
|
||
|
||
6) 【强制】 创建 index 时，为了并行创建，不阻塞其他 DML，可以添加 create index concurrently 关 键字。
|
||
|
||
7) 【强制】 如果 PG 实例配置了 standby，并且使用了 slot，则必须监控 stadnby实例的延时和slot 的状 态， 否则可能会造成主库 XLOG 不断堆积， 占满空间而产生问题
|
||
|
||
#### 3.1.6 稳定性规范
|
||
|
||
1) 【强制】PG 中应避免长事务， 长事务会造成垃圾膨胀。
|
||
|
||
2) 【强制】PG 在代码中写分页逻辑时，如果count 为 0 应直接返回，避免执行后续的分页语句。
|
||
|
||
3) 【强制】两阶段提交的事务，要及时提交或回滚，否则可能导致数据库膨胀。
|
||
|
||
4) 【强制】在高并发场景下， 务必使用程序的连接池，否则性能会很低下 。如果程序没有连接池， 可以考虑使用 pgpool-II 或 pgbouncer 中间件。
|
||
|
||
5) 【强制】程序务必要有重连机制， 如果没有重连机制，一个长期空闲的链接可能会被强制断开， 数据库高可用切换后， 程序也可能有问题。
|
||
|
||
6) 【强制】必须使用合理的隔离级别， 不要越级使用隔离级别， 以满足业务需求为准。
|
||
|
||
7) 【强制】高峰期对大表添加新列时， 建议先不加默认值， 避免rewrite， 后面再用业务逻辑添加默认值。
|
||
|
||
14
|
||
|
||
8) 【强制】 自增字段建议使用序列， 根据情况选择 2 字节 、4 字节或 8 字节。禁止使用触发器产生序列。
|
||
|
||
9) 【强制】线上表结构的变更， 包括添加字段 、索引等， 应尽量在业务低峰期执行。
|
||
|
||
10) 【强制】OLTP 系统在业务高峰期或高并发期间， 应拒绝长 SQL 、大事务、大批量。
|
||
|
||
11) 【强制】冷热数据要进行分离，尽量保证线上实例只存在有限的经常查询的数据
|
||
|
||
#### 3.1.7 索引优化规范
|
||
|
||
1) 【强制】访问 PG 的查询 SQL 应进行查询优化， 尽量避免全表扫描， 首先要考虑在 where, order by,group by 的列上， 建立索引 。 查询特别多的 SQL 要考虑满足覆盖索引。
|
||
|
||
2) 【强制】应尽量避免在 where 子句中使用 != 或 <> 操作符，这种不等于会让 PG 放弃索引， 使用全表扫描。
|
||
|
||
3) 【强制】索引应该建在选择性高的字段或小字段上，选择性低的字段 、大的文本字段一级超长字段不应建索引。
|
||
|
||
4) 【强制】复合索引要符合最左原则，将选择性最好的字段作为第一个列， 其他非第一个字段的列， 如果经常会有查询用到， 也需要创建单独的索引。
|
||
|
||
5) 【强制】频繁进行数据操作的表， 不要建立太多的索引， 因为索引会降低数据操作的性能， 增大数据操作的成本。
|
||
|
||
6) 【强制】对于 btree 索引、hash 索引、gin 索引、gist 索引、BRIN 索引要根据不同的索引特点和适用场景进行合理选择和使用。
|
||
|
||
7) 【强制】对于无用的索引要及时删除，无用的索引不仅会导致更新数据的代价变大， 还可能产生错误的执行计划。
|
||
|
||
8) 【强制】所有的新程序的表结构和 SQL 在上线前最好与 DBA 确认是否都有索引再上线到生产环境。
|
||
|
||
### 3.2 MySQL 开发规范
|
||
|
||
15
|
||
|
||
本规范适用于 MySQL5.7 和 8.0 版本， 同样也适用于 TeleDB4MySQL。
|
||
|
||
#### 3.2.1 对象名称规范
|
||
|
||
l 库名命名
|
||
|
||
1) 【强制】库名只能含有字母 、数字和下划线“_ ”三类字符。
|
||
|
||
2) 【强制】库名必须使用小写字母， 用下划线“_ ”分割。
|
||
|
||
3) 【强制】库名禁止超过 32 个字符， 须见名知意。
|
||
|
||
4) 【强制】库名禁止使用数据库特殊关键字重名。
|
||
|
||
5) 【强制】 临时库库名必须以 tmp 开头， 以创建人的简拼字母和日期为后缀。
|
||
|
||
6) 【强制】备份库库名必须以 bak 开头， 以创建人的简拼字母和日期为后缀。
|
||
|
||
l 表名命名
|
||
|
||
1) 【强制】表名只能含有字母 、数字和下划线“_ ”三类字符。
|
||
|
||
2) 【强制】表名必须使用小写字母， 用下划线“_ ”分割， 普通业务表以 t_开头（或有意义的简写）_+table_name。
|
||
|
||
3) 【强制】表名禁止超过 32 个字符， 须见名知意。
|
||
|
||
4) 【强制】表名禁止使用数据库特殊关键字命名。
|
||
|
||
5) 【强制】 临时表表名必须以 tmp 开头， 以创建人的简拼字母和日期为后缀。正例： 示例创建人在 2022 年 4 月 21 日建了一张临时表， tmp_xxxxxxx_zs20220421
|
||
|
||
6) 【强制】备份表表名必须以 bak 开头， 以创建人的简拼字母和日期为后缀。正例： 示例创建人在 2022 年 4 月 21 日建了一张临时表， bak_xxxxxxx_zs20220421
|
||
|
||
l 字段名命名
|
||
|
||
1) 【强制】字段名必须使用小写字母， 用下划线“_ ”分割。
|
||
|
||
2) 【强制】字段名禁止超过 32 个字符， 须见名知意。
|
||
|
||
3) 【强制】字段名禁止使用数据库特殊关键字命名。
|
||
|
||
l 索引命名
|
||
|
||
1) 【推荐】非唯一索引必须以 idx_字段 1_字段 2 命名。
|
||
|
||
2) 【推荐】 唯一索引必须以uidx_字段 1_字段 2 命名。
|
||
|
||
3) 【强制】索引名称必须全部小写。
|
||
|
||
l 用户名命名16
|
||
|
||
1) 【推荐】生产业务用户名推荐： 库名_app。
|
||
|
||
2) 【推荐】生产只读用户名推荐： 库名_read。
|
||
|
||
3) 【推荐】线下研发查询账号用户名推荐： 库名_devread。
|
||
|
||
4) 【推荐】监控用户名推荐： 库名_monitor。
|
||
|
||
5) 【推荐】大数据抽数用户名推荐： 库名_dataextract。
|
||
|
||
6) 【推荐】大数据推数用户名推荐： 库名_dataimport。
|
||
|
||
7) 【推荐】DBA 管理员用户名推荐： 库名_dbaadmin。
|
||
|
||
8) 【推荐】备份用户名推荐： 库名_bakadmin。
|
||
|
||
#### 3.2.2 对象设计规范
|
||
|
||
1) 【强制】INT 类型不使用 unsigned 无符号属性。
|
||
|
||
2) 【强制】 自增用 8 字节 BIG INT， 不要使用 4 字节 INT。
|
||
|
||
3) 【强制】字符集使用 UTF8MB4 字符编码， 不推荐 GBK 、UTF-8 等其他字符集。
|
||
|
||
4) 【强制】 日期类型用 DATETIME 类型， 需要精确到毫秒用 DATETIME(6)，不要使用 INT 、TIMESTAMP。
|
||
|
||
5) 【强制】类型 JSON 可用于存储非结构化数据，典型场景为用户标签，不要将 JSON 用于频繁更新的字段场景。
|
||
|
||
6) 【推荐】对于日志类的流水表 、报警表 、 日志表， 可以使用压缩设计， 提升存储效率。
|
||
|
||
7) 【强制】类别设计， 用 ENUM+CHECK 约束， 不要使用 INT 类型的设计。
|
||
|
||
8) 【强制】敏感字段需加密，如账户密码、信用卡号等存储使用：动态盐 + 非固定加密算法（MD5/AES256 等） + 多轮加密， 不要简单使用 MD5 算法加密。
|
||
|
||
9) 【推荐】若业务只是简单的 SET、GET 请求，可考虑将其转化为Memcached的 KV 访问方式， 减少 SQL 解析的开销。
|
||
|
||
10) 【强制】InnoDB 和 MyISAM 存储引擎表，索引类型必须为 BTREE；MEMORY表可以根据需要选择 HASH 或者 BTREE 类型索引。
|
||
|
||
17
|
||
|
||
11) 【推荐】在建立索引时， 多考虑建立联合索引， 并把区分度最高的字段放在最前面 。 区分度可由 select count(distinct col_name)计算得出。
|
||
|
||
12) 【推荐】建表或加索引时，保证表里互相不存在冗余索引。若表里已经存在key(a, b)， 则 key(a)为冗余索引， 需要删除。
|
||
|
||
#### 3.2.3 大对象设计规范
|
||
|
||
1) 【推荐】建议对表里的blob 、text 等大字段， 垂直拆分到其他表里， 仅在需要读这些对象的时候才去 select。
|
||
|
||
2) 【强制】对于超过 100W 行的大表进行 alter table， 必须经过 DBA 审核， 并在业务低峰期执行 。alter table 会产生表锁， 期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。
|
||
|
||
#### 3.2.4 查询规范
|
||
|
||
1) 【强制】SELECT 语句必须指定具体字段名称， 禁止写成*。
|
||
|
||
2) 【推荐】SELECT 语句不建议 UNION， 推荐 UNION ALL， 并且 UNION 子句个数限制在 5 个以内。
|
||
|
||
3) 【推荐】in值列表限制在 500 以内， 减少底层扫描。
|
||
|
||
4) 【推荐】查询时，一定要考虑查询返回数据量，避免一次 SQL 返回过多数据。
|
||
|
||
5) 【强制】在统计数量时， 应使用 count(*)或 count(1)， 而非 count(col_name)。
|
||
|
||
6) 【强制】 除静态表或小表（100 行以内）， DML 语句必须有 where 条件， 且使用索引查找。
|
||
|
||
7) 【推荐】减少使用较为耗费 CPU 的 order by、group by、distinct，建议将排序放到程序端去做。
|
||
|
||
8) 【推荐】order by、group by、distinct 这些 SQL 尽量利用索引直接检索出排序好的数据 。如 where a=1 order by 可以利用 key(a, b)。
|
||
|
||
9) 【推荐】包含了 order by 、group by 、distinct 这些查询的语句， where 条件过滤出来的结果集建议保持在 1000 行以内。
|
||
|
||
10) 【推荐】在多表 join 的 SQL 里， 保证被驱动表的连接列上有索引。
|
||
|
||
18
|
||
|
||
11) 【推荐】SELECT 字段 FROM 表 a where limit c1,c2 这里 c1 很大例如几
|
||
|
||
十万 、几百万时， 会产生潜在的性能问题， 可以结合子查询为查询提速。
|
||
|
||
#### 3.2.5 数据操作规范
|
||
|
||
1) 【强制】禁用 update|delete t1 … where a=XX limit XX; 这种带 limit 的更新语句， 可能会导致主从不一致， 导致数据错乱。
|
||
|
||
2) 【强制】禁止使用关联子查询， 如 update t1 set … where name in(select name from user where…); 效率较低。
|
||
|
||
3) 【推荐】建议不使用 procedure、function、trigger、views、event、外键约束等消耗数据库资源和降低数据库实例可扩展性的语句， 在程序端实现。
|
||
|
||
4) 【强制】禁用 insert into …on duplicate key update …，高并发环境下会造成主从不一致。
|
||
|
||
5) 【强制】禁止联表更新语句， 如 update t1,t2 where t1.id=t2.id…。
|
||
|
||
#### 3.2.6 稳定性规范
|
||
|
||
1) 【推荐】insert into …values(XX),(XX),(XX) … 。XX 的值不要超过 5000 个， 值过多容易引起主从同步延迟。
|
||
|
||
2) 【强制】事务里批量更新数据需要控制数量，进行必要的sleep，做到少量多次。
|
||
|
||
3) 【强制】生产环境禁止使用 hint， 如 sql_no_cache， force index， ignore key， straight join 等。
|
||
|
||
4) 【推荐】写入和事务发往主库， 只读 SQL 发往从库。
|
||
|
||
5) 【强制】SELECT|UPDATE|DELETE|REPLACE 要有 WHERE 子句，且 WHERE子句的条件必需使用索引查找。
|
||
|
||
#### 3.2.7 索引优化规范
|
||
|
||
1) 【强制】无需设置单表行数 、列数限制。
|
||
|
||
2) 【推荐】在核心业务中， 使用索引覆盖技术， 提升索引查询性能； 19
|
||
|
||
3) 【强制】对类似 WHERE a = ? ORDER BY b 这样的查询， 一定要创建（a、 b） 组合索引， 这样可以避免一次额外排序， 提升查询性能。
|
||
|
||
4) 【推荐】MySQL 的查询基于成本而不是规则，若发现 SQL 执行计划发生变化， 先分析数据特点 、索引创建是否合理。
|
||
|
||
5) 【推荐】对于 OLTP 业务，一定要做好索引的设计和索引覆盖的考虑（不考虑分布式数据库场景）； 对于 OLAP 业务中的大数据量的关联， 建议使用大数据产品， 如 Hive 、Spark 等产品。
|
||
|
||
6) 【强制】上线前必须确认编写的子查询不能是关联子查询，若发现关联子查询， 改写子查询为 JOIN 或其他方式。
|
||
|
||
7) 【推荐】不推荐使用分区表， 考虑分区表唯一的应用场景是： 需要定期清理历史流水类数据。
|
||
|
||
8) 【强制】业务上线或新版本发布前， DBA 一定要进行所有 SQL Review， 确保 SQL 走索引，否则不予上线，或由业务以邮件等正式方式，通知 DBA 该SQL 不会引起线上事故， 业务方承担后续责任。
|
||
|
||
9) 【强制】DBA 每天要对数据库进行巡检，及早发现慢查询或潜在数据库风险，将任何潜在问题尽早抛出， 否则后续自己承担相关责任。
|
||
|
||
### 3.3 HTAP 开发规范
|
||
|
||
本规范适用于 TiDB 5.4 及以前所有内核版本， 同样也适用于 TeleDB4HTAP。
|
||
|
||
#### 3.3.1 对象名称规范
|
||
|
||
1) 【推荐】命名建议使用具有意义的英文词汇， 词汇中间以下划线分隔。
|
||
|
||
2) 【强制】命名只能使用英文字母 、数字 、下划线。
|
||
|
||
3) 【强制】避免用关键字或保留字如 group， error， rank 等作为对象名。
|
||
|
||
4) 【推荐】建议所有的数据库对象使用小写字母。
|
||
|
||
5) 【强制】所有的数据库对象的命名请注意标识符长度硬限制。
|
||
|
||
20
|
||
|
||
#### 3.3.2 数据库命名规范
|
||
|
||
1) 【强制】按照业务模块 、产品线和访问权限隔离需求来创建数据库， 如： 基础信息库（basicinfo_db） 、认证中心库（certifycenter_db） 。
|
||
|
||
2) 【推荐】建议数据库名称不要超过 16 个字符。
|
||
|
||
#### 3.3.3 表命名规范
|
||
|
||
1) 【强制】同一业务或者模块的表尽可能使用相同的前缀，表名称尽可能表达含义。
|
||
|
||
2) 【强制】多个单词以下划线分隔， 不推荐超过 32 个字符。
|
||
|
||
3) 【推荐】建议对表的用途进行注释说明， 以便于统一认识， 如： 临时表
|
||
|
||
（tmp_t_crm_relation_0425） 、备份表（bak_t_crm_relation_20170425） 、业
|
||
|
||
21
|
||
|
||
务运营临时统计表（tmpst[业务代码][创建人缩写][日期]） 、账期归档表（t_crm_ec_record_YYYY[MM][DD]） 。
|
||
|
||
4) 【强制】不同业务模块的表单独建立 DATABASE， 并增加相应注释。
|
||
|
||
5) 【强制】只支持将 lower-case-table-names 值设为 2，即数字字典中记录的名区分大小写， 匹配查找表名时不区分大小写。
|
||
|
||
#### 3.3.4 字段命名规范
|
||
|
||
1) 【强制】字段命名需要表示其实际含义的英文单词或简写。
|
||
|
||
2) 【推荐】建议各表之间相同意义的字段应同名， 并且一定使用相同的字段类型。
|
||
|
||
3) 【强制】字段也尽量添加注释，枚举型需指明主要值的含义，如“0 - 离线， 1 - 在线 ”。
|
||
|
||
4) 【强制】布尔值列命名为 [is_描述]。如 member 表上表示为 enabled 的会员的列命名为 is_enabled。
|
||
|
||
5) 【推荐】字段名不建议超过 32 个字符。
|
||
|
||
#### 3.3.5 索引命名规范
|
||
|
||
1) 【推荐】主键索引： pk_表名_字段 1_字段2。存在多个字段时， 多个字段名简写用下划线分隔。
|
||
|
||
2) 【推荐】 唯一索引： uidx_表名_字段 1_字段2 。存在多个字段时， 多个字段名简写用下划线分隔， 不足以区分时可以增加表名等辅助信息。
|
||
|
||
3) 【推荐】普通索引：idx_表名_字段 1_字段2。存在多个字段时，多个字段名简写用下划线分隔。
|
||
|
||
4) 【强制】多单词组成的字段名， 使用能代表意义的缩写。
|
||
|
||
22
|
||
|
||
#### 3.3.6 对象设计规范
|
||
|
||
##### 3.3.6.1 表的设计
|
||
|
||
1) 【强制】表需要有主键或者非空唯一索引， 能与各项复制工具更好地兼容。
|
||
|
||
2) 【强制】业务表使用自增主键时， 字段类型推荐使用 bigint unsigned， 最大值可达 18446744073709551615。
|
||
|
||
3) 【强制】出于性能考虑， 应避免存储超宽表，数据长度过大的字段应拆分存储到独立的数据表，单行数据大小不能超过 6 MB。建议单表字段数不超过 60个， 单行数据大小不超过 64K。
|
||
|
||
4) 【推荐】不推荐使用复杂的数据类型， 如 blob 或者 json。
|
||
|
||
5) 【强制】进行 join 的关联字段， 数据类型保证一致， 避免隐式转换。
|
||
|
||
6) 【强制】不能以范式作为唯一标准或者指导，在设计过程中， 需要从实际需求出发，以性能提升为根本目标来展开设计工作。为了提升性能减少表关联，可以适当保存冗余数据做反范式设计。
|
||
|
||
7) 【强制】表对象的设计请注意单个 Table 的限制 。
|
||
|
||
. Columns 的最大限制可通过 table-column-count-limit 修改。. Indexs 的最大限制可通过 index-limit 修改。23
|
||
|
||
##### 3.3.6.2 字段的设计
|
||
|
||
1) 【推荐】所有整数类型的字段推荐只使用 INT 或者 BIGINT。
|
||
|
||
2) 【推荐】BIGINT 定义中不推荐添加长度。
|
||
|
||
3) 【推荐】推荐使用 INT(10) UNSIGNED 存储 IPv4 格式 IP 地址。
|
||
|
||
4) 【推荐】浮点类型推荐使用 DECIMAL 。
|
||
|
||
5) 【强制】 时间字段使用时间日期类型， 不要使用字符串类型存储。
|
||
|
||
6) 【强制】所有只需要精确到天的字段全部使用 DATE 类型， 而不应该使用TIMESTAMP 或者 DATETIME 类型。
|
||
|
||
7) 【强制】所有需要精确到时间(时分秒)的字段均使用 DATETIME， 不要使用TIMESTAMP 类型。
|
||
|
||
8) 【推荐】仅当字符数量可能超过 20000 个的时候，才建议使用TEXT 类型来存放字符类数据。所有使 TEXT 类型的字段建议和原表进行分拆，与原表主键单独组成另外一个表进行存放。
|
||
|
||
9) 【推荐】当使用宽字段类型（如 Text、MediumBlob、MediumText） 时， 需注意读取并发度， 以控制内存使用预防 OOM 。
|
||
|
||
10) 【推荐】不建议使用 ENUM 、SET 类型， 尽量使用 TINYINT 来代替。
|
||
|
||
##### 3.3.6.3 字符集
|
||
|
||
1) 【强制】建表时只使用默认的 utf8mb4 编码。
|
||
|
||
2) 【推荐】utf8mb4 的默认排序规则为 utf8mb4_bin（区分大小写）， 支持
|
||
|
||
utf8mb4_general_ci（不区分大小写）， 但是需要集群部署时配置新的排序规则框架（new_collations_enabled_on_first_bootstrap 设置为 true） 。
|
||
|
||
##### 3.3.6.4 列的自增属性
|
||
|
||
1) 【强制】列的自增属性仅保证唯一， 仅能保证在单个计算节点中自增， 不保证多个计算节点中自增，不保证自动分配的值的连续性。业务不应该依赖自
|
||
|
||
24
|
||
|
||
增属性的连续性和有序性， 如记录的插入顺序排序应按照记录的创建时间。带有自增属性的列出现空洞和跳跃插入的现象是正常现象。
|
||
|
||
2) 【强制】不要在语句中显式指定具有自增属性的列的值，由数据库自动分配，否则可能会出现值重复冲突。
|
||
|
||
3) 【强制】允许移除列的 AUTO_INCREMENT 属性， 但是请谨慎评估， 移除该属性后不可恢复。
|
||
|
||
##### 3.3.6.5 创建 、删除表规范
|
||
|
||
1) 【强制】表的建立在遵循表命名规范前提下，如果业务应用内部封装建表删表语句，需要增加判断逻辑，防止业务流程异常中断。例如：create table if not exists table_name 或者 drop table if exists table_name 语句建议增加 if 判断，避免应用侧由于表的改动造成的异常中断。
|
||
|
||
2) 【强制】不支持 create table as select 语法， 需要改写为表结构复制 create table like … 和将数据写入的 insert into select … 的组合语句。
|
||
|
||
##### 3.3.6.6 变更表规范
|
||
|
||
1) 【强制】不支持单条 ALTER TABLE 语句中完成多个操作，不能在单个语句中添加多个列或索引， 需要更换成多个单个列或索引的操作。
|
||
|
||
2) 【强制】不支持对字段类型的有损修改或修改为超集。
|
||
|
||
##### 3.3.6.7 视图使用规范
|
||
|
||
1) 【推荐】支持为应用程序建立专门的视图而不必非要应用程序直接访问数据表。
|
||
|
||
2) 【强制】视图不可更新，不支持 UPDATE、INSERT、DELETE 等写入操作。
|
||
|
||
25
|
||
|
||
##### 3.3.6.8 分区表规范
|
||
|
||
1) 【推荐】当数据需要按时间进行归档清理时，可按某个业务时间段对表进行分区， 对分区进行 truncate 操作满足数据清理要求。
|
||
|
||
2) 【强制】按时间进行分区表的粒度应该将分区记录数控制在十亿级别，不应该配置太小， 如日分区， 也不应太大， 如年分区。
|
||
|
||
3) 【强制】在分区表上进行查找时， 查找条件必须包含分区字段的查找条件。分区表的二级索引属于分区内索引，需要先通过分区裁剪的方式，定位到具体分区后再经过二级索引回表查找。
|
||
|
||
#### 3.3.7 查询规范
|
||
|
||
##### 3.3.7.1 大事务处理
|
||
|
||
1) 【强制】单个事务的总大小默认不超过 100 MB， 最大支持 10 GB， 实际的单个事务大小限制还取决于服务器剩余可用内存的大小， 执行事务时 TiDB进程的内存消耗大约是事务大小的 6 倍以上。
|
||
|
||
2) 【强制】应做好事务执行的内存容量评估。注意执行事务时 TiDB 进程的内存消耗大约是事务大小的 6 倍以上， 事务设置过大， 或者 Batch 过高， 会导致 tidb-server OOM。
|
||
|
||
3) 【强制】为了使性能达到最优， 需要对大事务按某个业务维度进行拆分，每100～500 行提交一个事务。
|
||
|
||
##### 3.3.7.2 SELECT * 使用规范
|
||
|
||
1) 【强制】禁止使用 SELECT * 进行查询 。建议按需求选择合适的字段列， 杜绝直接 SELECT * 读取全部字段， 减少网络带宽消耗， 有效利用覆盖索引。
|
||
|
||
26
|
||
|
||
##### 3.3.7.3 分页查询 order by 语法使用规范
|
||
|
||
1）【强制】分页查询语句需要带有排序条件， 除业务排序条件外还应包含主键或者其他唯一键以保证分页稳定， 规避没有业务排序字段或者一个业务排序字段值匹配多条记录导致结果集不稳定； 常规分页语句写法(start:起始记录数,page_offset:每页记录数)： select * from table_a t order by gmt_modified
|
||
|
||
desc,pk limit start， page_offset。
|
||
|
||
##### 3.3.6.1 group by 语法使用规范
|
||
|
||
1) 【强制】select 字段中不得引用未在 group by 子句中声明的非聚集字段，即不得使用 MySQL non-full group by 语法; 以下的语句是不被允许的， select class,stuname,max(score) as max_score from score group by class。
|
||
|
||
##### 3.3.7.4 多表关联查询规范
|
||
|
||
1) 【强制】多表关联应该显式使用 join子句，避免漏掉关联条件， 造成笛卡尔积。
|
||
|
||
2) 【强制】嵌套 SQL 语句应该为不同表指定不同别名。
|
||
|
||
3) 【强制】高并发交易场景， 单条语句关联表不超过两张， 使用执行计划为 IndexJoin 的多表关联语句，外表建立了正确的条件过滤索引，内表建立了正确的关联和条件过滤索引。
|
||
|
||
4) 【强制】低并发的分析场景， 单条语句关联表不超过 10 张， 其中亿级表不超过 2 张 。需要注意 tidb_mem_quota_query 参数指定的单条语句内存使用限制， 默认是 1GB， 生产建议不超过 16 GB。
|
||
|
||
#### 3.3.8 数据操作规范
|
||
|
||
27
|
||
|
||
##### 3.3.8.1 防范写入热点创建规范
|
||
|
||
1) 【强制】对于写入量非常大的表，应当通过应用性能压测等方法在测试环境模拟表的热点情况。
|
||
|
||
2) 【强制】通过以下三种手段进行配置， 规避表的主键写入热点 。 避免连续自增主键的设计， 建议采用雪花算法生成 UUID 主键； 主键是非整数的表或者启用 alt-primarykey=true 配置后创建的所有表， 使用
|
||
|
||
SHARD_ROW_ID_BITS 语法创建基于 rowid 的分片方案， 例如： CREATE TABLE t (c int) SHARD_ROW_ID_BITS = 4 或 ALTER TABLE t SHARD_ROW_ID_BITS = 4; 创建按照 Hash 或 Range 分区表避免热点。
|
||
|
||
##### 3.3.8.2 数据删除规范
|
||
|
||
1) 【强制】删除表中全部的数据时， 使用 TRUNCATE 或者 DROP 后重建方式，不要使用 DELETE 。以上几种数据删除方法执行后，都不会立即释放空间，需要等待 数据库后台的 GC (garbage collection) 和 Compaction 机制对空间回收后重新利用。
|
||
|
||
2) 【强制】对于按范围进行部分数据的删除， 如果超过大事务的限制， 可以参考以下窗口函数的方法， 分成批量小任务进行数据的删除；
|
||
|
||
a. 将数据按照主键排序， 然后调用窗口函数 row_number() 为每一行数据生成行号，接着调用聚合函数按照设置好的页大小对行号进行分组，最终计算出每个分组的行号的最小值和最大值。MySQL [demo]> select min(t.serialno) as start_key, max(t.serialno) as end_key, count() as page_size from ( select , row_number () over (order by serialno) as row_num from tmp_loan ) t group by floor((t.row_num - 1) / 50000) order by start_key;
|
||
|
||
| start_key | end_key | page_size |
|
||
|
||
| 200000000 | 200050001 | 50000 | | 200050002 | 200100007 | 50000 | 28
|
||
|
||
| | | || 201900019 | 201950018 | 50000 | | 201950019 | 201999003 | 48985 |
|
||
|
||
## 40 rows in set (1.51 sec)
|
||
|
||
b. 借助计算好的分组信息，使用 serialno between start_key and end_key 操作每个分组的数据， 实现高效数据删除或者更新。
|
||
|
||
#### 3.3.9 稳定性规范
|
||
|
||
1) 【强制】所有的建表操作需要提前告知 DBA 该表涉及的查询 SQL。
|
||
|
||
2) 【强制】所有的建表需要确定建立哪些索引后才可以建表上线。
|
||
|
||
3) 【强制】所有的改表结构、加索引操作都需要将涉及到所改表的查询 SQL 发出来告知 DBA 等相关人员。
|
||
|
||
4) 【强制】在建新表加字段之前， 建议开发人员提前发出给 DBA 评估 、优化和审核。
|
||
|
||
5) 【强制】批量导入 、导出数据必须提前通知 DBA 协助观察。
|
||
|
||
6) 【强制】大批量统计更新， 如临时统计， 应避开高峰期并通知 DBA。
|
||
|
||
7) 【强制】推广活动或上线新功能必须提前通知 DBA 进行流量评估。
|
||
|
||
8) 【强制】及时处理已下线业务的 SQL。
|
||
|
||
#### 3.3.10 索引优化规范
|
||
|
||
1) 【强制】选择区分度大的列建立索引，不在低基数列上建立索引，例如：“性别 ”， “是否是 XXX ”。
|
||
|
||
2) 【强制】单张表的索引数量控制在 5 个以内， 避免冗余索引。
|
||
|
||
3) 【推荐】索引中的字段数建议不超过 5 个。
|
||
|
||
4) 【强制】 唯一索引建议由 3 个或更少的字段组成。
|
||
|
||
29
|
||
|
||
5) 【强制】不应在频繁更新的列上创建索引。
|
||
|
||
6) 【强制】应该将使用频率高的，经常被点查使用的列排在复合索引靠前的位置， 将经常进行范围查询的列排在后面。
|
||
|
||
7) 【推荐】很长的 VARCHAR 字段建立索引时， 指定索引长度， 没必要对全字段建立索引， 根据实际文本区分度决定索引长度即可， 例如
|
||
|
||
idx_table_name (name(10))。
|
||
|
||
8) 【强制】定期删除一些长时间未使用过的索引。
|
||
|
||
9) 【强制】ORDER BY，GROUP BY，DISTINCT 的字段需要添加在索引的后面，形成覆盖索引。
|
||
|
||
10) 【强制】新的 select,update,delete 上线， 都要先执行 explain 命令， 观察执行计划是否有异常情况发现， 以确保索引的正确性。
|
||
|
||
11) 【推荐】不建议在 where 条件索引列上使用函数， 会导致索引失效， 如lower(email)。
|
||
|
||
12) 【推荐】使用 like 模糊匹配， % 不要放首位， 会导致索引失效 。业务语句中使用 like 查找字符串不使用 % 放首位，或者使用时结合其他有效的约束条件。
|
||
|
||
### 3.4 UDAL 开发规范
|
||
|
||
本开发规范适用于 UDAL 所有内核版本。
|
||
|
||
#### 3.4.1 对象名称规范
|
||
|
||
本部分适用对象包括库 、表 、表字段 、全局序列 、用户 、角色。
|
||
|
||
1) 【强制】对象名只能使用小写字母 、数字 、下划线， 不能使用其他字符。
|
||
|
||
2) 【强制】对象名长度不超过 32 位。
|
||
|
||
3) 【强制】库名 、表名 、字段名禁止和数据库特殊关键字重名， 须见名知意。
|
||
|
||
#### 3.4.2 对象设计规范
|
||
|
||
1) 【强制】 自增列必须为 int 或 bigint 类型。
|
||
|
||
30
|
||
|
||
2) 【强制】表必须设置主键。
|
||
|
||
3) 【推荐】列设置非空且有默认值。
|
||
|
||
4) 【强制】禁止使用外键。
|
||
|
||
5) 【强制】禁止使用分区表。
|
||
|
||
6) 【强制】建表时禁止使用除 utf8,utf8mb4 之外的字符集。
|
||
|
||
7) 【强制】建表指定的存储引擎必须为 Innodb。
|
||
|
||
8) 【强制】禁用存储过程 、函数 、触发器 、视图。
|
||
|
||
#### 3.4.3 大对象设计规范
|
||
|
||
1) 【强制】禁止在 BLOB 、CLOB 等字段类型存储超过 16M 的内容。
|
||
|
||
2) 【强制】禁止单条表数据超过 16M。
|
||
|
||
#### 3.4.4 查询规范
|
||
|
||
1) 【强制】查询语句必须带 where 条件， 避免广播查询， 查询条件尽量使用到分片键， 如无法使用分片键， 应考虑非分片键和分片键之间建立切片索引。
|
||
|
||
2) 【推荐】尽量避免跨分片 Join查询语句。
|
||
|
||
3) 【推荐】尽量避免跨分片 Union查询语句。
|
||
|
||
4) 【强制】关联表使用的分片算法必须一致，关联表数据拆分的节点必须相同，关联查询 SQL 上必须带有分片键字段的关联。
|
||
|
||
5) 【强制】和全局表做关联， 要保证非全局表所在的节点有对应全局表。
|
||
|
||
6) 【推荐】在查询数据时， 要在 select 后写明需要查询的所有列名， 不要返回不使用的任何字段， 不要使用 select * ， 这样会查询过多内容， 也可能出现程序匹配错误。
|
||
|
||
7) 【推荐】在查询时，一定要考虑查询返回数据量，避免一次 SQL 返回过多数据， 影响查询性能和网络。
|
||
|
||
#### 3.4.5 数据操作规范
|
||
|
||
1) 【强制】Update/Delete 语句不带 where 条件。 31
|
||
|
||
2) 【强制】禁止使用 Truncate table 语句。
|
||
|
||
3) 【强制】禁止 Update/Delete 语句带 limit 条件 。 因为可能会导致主从不一致。
|
||
|
||
4) 【强制】禁止 Update/Delete 语句带 order by 条件。
|
||
|
||
5) 【强制】禁止更新分片键值。
|
||
|
||
6) 【强制】禁止在 BLOB 或 BINARY 字段中存储大文件内容。
|
||
|
||
7) 【推荐】尽量避免广播语句。
|
||
|
||
8) 【推荐】Insert/Update/Delete 语句尽量避免跨分片执行。
|
||
|
||
9) 【推荐】使用全局序列替换数据库的自增序列。
|
||
|
||
10) 【推荐】如果要执行跨节点 update/delete 语句， 建议在执行前开启分布式事务， 保障跨节点操作的数据一致性。
|
||
|
||
11) 【推荐】根据业务和安全实际需求， 设置相应的 DDL 及 DML 审计规则， 设置 IP 黑白名单。
|
||
|
||
#### 3.4.6 稳定性操作规范
|
||
|
||
1) 【推荐】尽量避免使用分布式事务 。如果要执行跨节点 update/delete 语句，建议在执行前开启分布式事务， 保障跨节点操作的数据一致性。
|
||
|
||
2) 【推荐】应用程序尽量使用数据库连接池或者有重连机制。
|
||
|
||
3) 【推荐】表结构的变更，包括添加字段、索引等，应尽量在业务低峰期执行。
|
||
|
||
4) 【推荐】OLTP 系统在业务高峰期或高并发期间， 应拒绝长 SQL 、大事务、大批量。
|
||
|
||
#### 3.4.7 索引优化规范
|
||
|
||
本部分索引特指 UDAL 提供的切片索引， 即非分片键和分片键之间的索引。
|
||
|
||
1) 【强制】切片索引必须准确设置 one2one 或类型 one2many。
|
||
|
||
2) 【强制】在 one2one类型的索引中， 索引对应的数据记录必须是一对一的关系， 如果有一条索引键对应多条被索引键的情况， 会引起数据丢失。
|
||
|
||
3) 【强制】在 one2many 类型的索引中， 如果一个索引键对应了多条被索引键，那这些被索引键必须是彼此不同的 。否则会引起数据丢失。
|
||
|
||
32
|
||
|
||
4) 【强制】应该避免使用 float/double/year/date/time/datetime/timestamp 类型的字段作为索引键/被索引键，否则容易出现索引失效的问题 。（因为缓存中存储的是不带格式的原始数据，前端查询条件中可能使用的是有格式的数据 。例如字段类型是double， 那么缓存中存储的数据可能是 1.0， 此时前端查询条件只有使用 1.0 时才能命中索引， 如果是 1 或者 1.00 都会导致索引失效） 。
|
||
|
||
5) 【强制】禁止一次执行过大的数据库更新操作（比如： 对千万级别的表直接执行全表更新或者全表删除操作） 。
|
||
|
||
6) 【强制】one2many 类型索引中，一条索引键对应 2-3 条被索引键为宜，禁止出现一条索引键对应百万或者千万级别被索引键的情况。
|
||
|
||
7) 【强制】索引键/被索引键不能过长， 禁止出现索引键/被索引键为长度上千的中文字符串的情况。
|
||
|
||
33
|