197 lines
9.9 KiB
Markdown
197 lines
9.9 KiB
Markdown
|
# ***\*数据库规范\****
|
|||
|
|
|
|||
|
|
| 日期 | 修订版本 | 修订描述 | 编写人 |
|
|||
|
|
| ---------- | --------- | -------- | ------ |
|
|||
|
|
| 2024.05.23 | v01.00.00 | | |
|
|||
|
|
| | | | |
|
|||
|
|
| | | | |
|
|||
|
|
| | | | |
|
|||
|
|
|
|||
|
|
# **一、** ***\*安全规范\****
|
|||
|
|
|
|||
|
|
1. 【强制】禁止在数据库中存储明文密码，需把密码加密后存储
|
|||
|
|
|
|||
|
|
2. 【推荐】利用审计插件（如`mysqlaudit`）记录重要SQL的访问情况，对重要sql的访问频率或次数要做历史趋势监控，及时发现异常行为
|
|||
|
|
|
|||
|
|
3. 【推荐】线上连接数据库统一使用数据库服务器内网IP。外网链接使用的用户名、密码定期进行更换，并且统一交由@王朋管理
|
|||
|
|
|
|||
|
|
# ***\*二、基础规范\****
|
|||
|
|
|
|||
|
|
1. 【强制】库，表。字符型字段，mysql8：字符集要求：utf8mb4。排序规则：utf8mb4_0900_ai_ci。（为了兼容老业务系统框架 mysql5.7:字符集要求：utf8。排序规则：utf8_general_ci
|
|||
|
|
|
|||
|
|
2. 【推荐】尽量不在数据库做运算，复杂运算需移到业务应用里完成
|
|||
|
|
|
|||
|
|
3. 【推荐】拒绝大sql语句、拒绝大事务、拒绝大批量，可转化到业务端完成
|
|||
|
|
|
|||
|
|
说明：大批量操作可能会造成严重的主从延迟，binlog日志为row格式会产生大量的日志
|
|||
|
|
|
|||
|
|
4. 【推荐】避免使用存储过程、触发器、函数等，容易造成业务逻辑与DB耦合。
|
|||
|
|
|
|||
|
|
说明：数据库擅长存储与索引、要解放数据库CPU，将计算转移到服务层、也具备更好的扩展性
|
|||
|
|
|
|||
|
|
5. 【强制】数据表、数据字段必须加入中文注释。说明：后续维护的人看到后才清楚表是干什么用的。
|
|||
|
|
|
|||
|
|
6. 【强制】不在数据库中存储图片、文件等大数据 说明：大文件和图片需要储在文件系统。
|
|||
|
|
|
|||
|
|
7. 【推荐】对于程序连接数据库账号，遵循权限最小原则
|
|||
|
|
|
|||
|
|
8. 【推荐】数据库设计时，需要问下自己是否对以后的扩展性进行了考虑。
|
|||
|
|
|
|||
|
|
9. 【推荐】数据库开启slow_query_log，并定期分析，进行优化。
|
|||
|
|
|
|||
|
|
10. 【推荐】如果数据量或数据增长在前期规划时就较大，那么在设计评审时就应加入分表策略。
|
|||
|
|
|
|||
|
|
11. 【推荐】要求所有研发SQL关键字全部是小写，每个词只允许有一个空格。
|
|||
|
|
|
|||
|
|
12. 【强制】定期自动执行数据库备份，包括全量备份和增量备份。
|
|||
|
|
|
|||
|
|
# ***\*三、命名规范\****
|
|||
|
|
|
|||
|
|
1. 【强制】mysql8:库名、表名、字段名要小写，下划线风格，不超过32个字符，（为了兼容老业务系统框架 mysql5.7:库名表名为小写+下划线，字段名为大写+下划线）必须见名知意，建议使用名词而不是动词，词义与业务、产品线等相关联，禁止拼音英文混用。
|
|||
|
|
2. 【强制】库名、表名、字段名禁止使用MySQL保留字
|
|||
|
|
3. 【强制】临时库表名必须以tmp为前缀，并以日期为后缀
|
|||
|
|
4. 【强制】备份库表必须以back_日期为后缀
|
|||
|
|
5. 【推荐】按日期时间分表需符合按年分表：表名_ YYYY 按月分表：表名_ YYYYMM格式
|
|||
|
|
8. 【强制】表达是与否概念的字段，禁止使用 is_xxx 的方式命名，一律使用xxx_flag的方式命名，数据类型是 tinyint（1 表示是，2 表示否,尽量不使用0）
|
|||
|
|
|
|||
|
|
# ***\*四、库设计规范\****
|
|||
|
|
|
|||
|
|
1. 【推荐】数据库使用InnoDB存储引擎
|
|||
|
|
|
|||
|
|
2. 【强制】禁止使用外键，如果有外键完整性约束，需要应用程序控制
|
|||
|
|
|
|||
|
|
3. 【强制】每个Innodb 表必须有且只有一个主键。
|
|||
|
|
|
|||
|
|
说明：多主键可能导致聚簇索引无法正确被使用。
|
|||
|
|
|
|||
|
|
4. 【推荐】单表列数目最好小于50
|
|||
|
|
|
|||
|
|
6. 【推荐】拆分大字段和访问频率低的字段，分离冷热数据
|
|||
|
|
|
|||
|
|
7. 【推荐】采用合适的分库分表策略，例如千库十表、十库百表等（建议表大小控制在2G）
|
|||
|
|
|
|||
|
|
8. 【推荐】单表不超过50个int字段；不超过20个char字段，不超过2个text字段
|
|||
|
|
|
|||
|
|
9. 【推荐】日志类型的表可以考虑按创建时间水平切割，定期归档历史数据
|
|||
|
|
|
|||
|
|
10. 【强制】禁止使用order by rand()
|
|||
|
|
|
|||
|
|
说明：order by rand()会为表增加一个伪列，然后用rand()函数为每一行数据计算出rand()值，基于该行排序，这通常都会生成磁盘上的临时表，因此效率非常低。
|
|||
|
|
|
|||
|
|
11. 【参考】可以结合使用hash、range、lookup table进行散表
|
|||
|
|
|
|||
|
|
12. 【强制】业务主表中必须有creator,create_time,operator,operate_time,is_delete五个字段（垂直拆分的附表可以没有）
|
|||
|
|
|
|||
|
|
13. 【强制】禁止在表中建立预留字段
|
|||
|
|
|
|||
|
|
说明：预留字段的命名很难做到见名识义，预留字段无法确认存储的数据类型，所以无法选择合适的类型；对预留字段类型的修改，会对表进行锁定
|
|||
|
|
|
|||
|
|
# ***\*五\*******\*、\*******\*字段设计规范\****
|
|||
|
|
|
|||
|
|
1. 【强制】禁止使用小数存储国币、使用“分”作为单位，这样数据库里就是整数了
|
|||
|
|
|
|||
|
|
2. 【强制】用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
|
|||
|
|
|
|||
|
|
3. 【强制】字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量
|
|||
|
|
|
|||
|
|
4. 【推荐】适当增加冗余字段，减少JOIN
|
|||
|
|
|
|||
|
|
5. 【强制】表示状态的字段，如is_delete、state等使用INT，禁止使用 VARCHAR
|
|||
|
|
|
|||
|
|
6. 【参考】VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N
|
|||
|
|
|
|||
|
|
7. 【参考】VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存
|
|||
|
|
|
|||
|
|
8. 【推荐】VARCHAR(N)，N>5000时，使用BLOB类型
|
|||
|
|
|
|||
|
|
9. 【推荐】使用短数据类型，比如取值范围为0~80时，使用TINYINT UNSIGNED
|
|||
|
|
|
|||
|
|
10. 【强制】存储状态，性别等，用TINYINT
|
|||
|
|
|
|||
|
|
11. 【强制】所有存储相同数据的列名和列类型必须一致（在多个表中的字段如user_id，它们类型必须一致）
|
|||
|
|
|
|||
|
|
12. 【推荐】优先选择符合存储需要的最小数据类型
|
|||
|
|
|
|||
|
|
13. 【推荐】如果存储的字符串长度几乎相等，使用 char 定长字符串类型
|
|||
|
|
|
|||
|
|
# **六、索引设计规范**
|
|||
|
|
|
|||
|
|
1. 【强制】索引命名必须遵循规范：普通索引使用`idx_字段名`，唯一索引使用`uk_字段名`
|
|||
|
|
|
|||
|
|
2. 【推荐】核心SQL优先考虑覆盖索引
|
|||
|
|
|
|||
|
|
3. 【推荐】区分度高（重复值少）、频繁作为WHERE条件的字段应建立索引
|
|||
|
|
|
|||
|
|
说明：区分度计算公式：count(distinct col)/count(col)，区分度越高索引效果越好，一般认为超过0.1的字段适合建索引
|
|||
|
|
|
|||
|
|
4. 【参考】避免冗余和重复索引
|
|||
|
|
|
|||
|
|
5. 【推荐】研发要经常使用explain，如果发现索引选择性差，必须要学会使用hint
|
|||
|
|
|
|||
|
|
6. 【推荐】能使用唯一索引就要使用唯一索引，提高查询效率
|
|||
|
|
|
|||
|
|
7. 【强制】了解索引失效场景，避免以下用法：
|
|||
|
|
- 使用 `LIKE '%xxx'` 或 `LIKE '%xxx%'`（前缀模糊 `LIKE 'xxx%'` 可使用索引）
|
|||
|
|
- 在索引列上进行计算、函数操作
|
|||
|
|
- 隐式类型转换（如字符串字段用数字查询）
|
|||
|
|
- 使用 `OR` 连接条件（部分情况）
|
|||
|
|
- 使用 `!=`、`<>`、`NOT IN`、`IS NULL`、`IS NOT NULL`
|
|||
|
|
- 联合索引未遵循最左前缀原则
|
|||
|
|
|
|||
|
|
8. 【推荐】多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量
|
|||
|
|
|
|||
|
|
9. 【推荐】WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引
|
|||
|
|
|
|||
|
|
10. 【参考】合理创建联合索引（避免冗余），如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)
|
|||
|
|
|
|||
|
|
11. 【推荐】复合索引中的字段数建议不超过5个
|
|||
|
|
|
|||
|
|
12. 【推荐】联合索引遵循最左前缀原则，将区分度高的字段放在前面
|
|||
|
|
|
|||
|
|
说明：查询条件必须包含联合索引的第一列（最左列），索引才会生效
|
|||
|
|
|
|||
|
|
13. 【强制】禁止给表中的每一列都建立单独的索引
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
# ***\*七、SQL使用规范\****
|
|||
|
|
|
|||
|
|
1. 【强制】WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化
|
|||
|
|
|
|||
|
|
说明：因为MySQL进行隐式类型转化之后，可能会将索引字段类型转化成=号右边值的类型，导致使用不到索引，原因和避免在索引字段中使用函数是类似的，例子 select uid from t_user where phone=15855550101（phone为 varchar 类型，此时查询中使用数字查询，会导致索引失效）
|
|||
|
|
|
|||
|
|
2. 【推荐】禁止在WHERE条件的属性上使用函数或者表达式
|
|||
|
|
|
|||
|
|
3. 【强制】应用程序必须捕获SQL异常，并有相应处理
|
|||
|
|
|
|||
|
|
4. 【推荐】sql语句尽可能简单、大的sql想办法拆成小的sql语句，减少锁表时间
|
|||
|
|
|
|||
|
|
5. 【推荐】事务要简单，整个事务的时间长度不要太长
|
|||
|
|
|
|||
|
|
6. 【强制】避免在数据库中进行数学运算或者函数运算(MySQL不擅长数学运算和逻辑判断，也容易将业务逻辑和DB耦合在一起)
|
|||
|
|
|
|||
|
|
7. 【推荐】sql中使用到OR的改写为用IN() (or的效率没有in的效率高)
|
|||
|
|
|
|||
|
|
8. 【推荐】尽量使用union all替代union
|
|||
|
|
|
|||
|
|
9. 【参考】避免使用大表JOIN
|
|||
|
|
|
|||
|
|
10. 【推荐】使用合理的SQL语句减少与数据库的交互次数
|
|||
|
|
|
|||
|
|
11. 【强制】禁止单条SQL语句同时更新多个表
|
|||
|
|
|
|||
|
|
12. 【推荐】获取大量数据时，建议分批次获取数据，每次获取数据少于2000条，结果集应小于1M
|
|||
|
|
|
|||
|
|
13. 【强制】禁止跨库查询（为数据迁移和分库分表留出余地，降低耦合度，降低风险）
|
|||
|
|
|
|||
|
|
14. 【推荐】尽量避免使用子查询，可以把子查询优化为join操作（子查询的结果集无法使用索引，子查询会产生临时表操作，如果子查询数据量大会影响效率，消耗过多的CPU及IO资源）
|
|||
|
|
|
|||
|
|
15. 【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好
|
|||
|
|
|
|||
|
|
16. 【强制】不要使用物理删除。0表示未删除，1表示已删除
|
|||
|
|
|
|||
|
|
17. 【强制】在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句
|
|||
|
|
|
|||
|
|
18. 【推荐】使用 ISNULL()来判断是否为 NULL 值
|
|||
|
|
|
|||
|
|
|