这是我参与11月更文挑战的第5天，活动详情查看：2021最后一次更文挑战
说明：本规范是自己基于多家公司经验，有自己开发经验也有行业规范要求，列举一些常用的场景而成文，提供给初创团队参考使用，可根据实际开发参考
一. 核心理念
制定规范的直接目的是约束设计行为，最终目的是确保设计的合理统一。规
范虽然是有丰富项目经验的人制定的，但维护的却不是某个人的意志，而是项目*的意志，因为遵守此规范对项目是好的有利的，此规范才有意义。
对于数据库核心理念可以归纳为以下几点：
◆不在数据库做计算，cpu 计算务必移至业务层
◆控制单表数据量，单表记录控制在千万级
◆控制列数量，字段数控制在 30 以内
◆平衡范式与冗余，为提高效率可以牺牲范式设计，冗余数据
◆拒绝 3B(big)，大 sql，大事务，大批量
二. 表结构设计
2.1 禁用保留字作为字段名或表名
- desc、range、match、delayed、comment 等加上引号也不行
2.3 标志类字段优先使用 tinyint
- 启用标记、状态标记、纳税人状态等统一使用 tinyint 存储
- 标记类字段必须为非空且带有默认值
- 对于标记特殊类型建议采用负值记录
- 启用标记一般情况默认都是启用,启用使用 1 表示
2.4 避免使用 NULL 字段
*NULL 的列使索引/索引统计/值比较都更加复杂，对 MySQL 来说更难优化
- NULL 字段的索引需要额外空间
- NULL 字段的复合索引无效
- 可以为空时要额外详细说明原因
- NOT NULL 之后能有默认最好有默认值
- 对 null 的处理时候，只能采用 is null 或 is not null，而不能采用=、in、<、<>、!=、not in 这些操作符号。如：where name!=’zhuyungao’，如果存在 name 为null 值的记录，查询结果就不会包含 name 为 null 值的记录
2.7 禁止在主表中使用 TEXT、BLOB 类型
- 会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能
- 可以在主表中存储索引,利用索引到明细表具体查询
- 对于大的字符串内容,可以在主表中存储 MD5 摘要进行匹配
- 不允许对大字段进行列表查询
- 对于图片、音频等,可以存放在 OSS 上,数据库中存 URI
2.8 原则上禁止使用浮点类型
- 浮点数和机器精度相关,容易导致偏差
- 一定要使用小数必须使用 decimal 类型
2.9 必须使用 varchar(20 或 50)存储手机号
- 涉及到区号或者国家代号，可能出现+-()
- 手机号并不会做数学运算
- varchar 可以支持模糊查询
2.11 必须包含显式主键
- 主键推荐为整数,且必须设置自增, 自增从 10000 开始
- 禁止使用 varchar 作为主键
- 禁止使用复合列作为主键
2.12 禁止使用外键
- 外键逻辑在程序里处理
2.13 建议使用 InnoDB 存储引擎
- 当前业务来看不需要使用其它引擎
- InnoDB 引擎支持事务、行级锁、并发性能更好、CPU 及内存缓存页优化使得资源利用率更高
- 我们的 RDS 默认就是 InnoDB 引擎,不指定即可
2.14 必须使用 UTF-8 字符集
- 我们的 RDS 默认就是 UTF-8 字符集
- 如果需要存储表情字符,则需要显式指定为 UTF-8-mb4
2.15 数据表、数据字段必须加入中文注释
- 表结构是需要给其他人或后来维护者看的
- 特别注意修改字段之后要同时修改注释
2.16 禁止使用存储过程、触发器、Event
- 高并发大数据的互联网业务，架构设计思路是“解放数据库 CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU 计算还是上移吧
- RDS 本身对这些功能的支持有一定的局限性
- 可以一定程度使用视图
2.18 表名和字段名命名规范
- 都统一使用大写,下划线风格,不超过 30 个字,根据业务命名,不得中英混用
- 英文尽量不要使用缩写
- 表名如果是英文命名请使用单数
2.19 原则上表字段必须小于 30 个 l 过多字段应该考虑使用扩展表
- 该规则根本目的是为了防止表过大
- 如果每个字段都很轻,可以允许超出
- 从其他数据库同步、其他方式导入等场景例外
2.20 固定长度字符列尽量使用定长 char
- 对于数据大多在固定长度范围的 varchar 可转换为 char
- 使用 char 的时候必须是 not null
2.21 字段要适当冗余
- 打破范式设计,适当冗余避免大量查询,ᨀ高查询性能
- 冗余的字段应该更新不会过于频繁,或者有手段能让其更新不频繁
2.22 对于单表数据超过 300 万行才允许分库分表
- 设计时应评估三年内的数据量情况,能达到 300 万行数据的才推荐使用分库分表
- 对于本身数据量不大的表严禁分库分表
2.23 选择适当的存储长度
- 深入了解业务,根据业务场景选择合适的长度,能省则省
- 对于 varchar 等可扩展格式,不宜在最初就设定一个超长值
- 在选择长度时应考虑各系统标准规范,如文件路径在 windows 下标准为 255
- 也要考虑横向第三方系统兼容性,老旧系统兼容性,特别是金三系统
2.24 字段命名与取长尽力保持已知习惯
- 业内知名的必须使用,如 qymc、nsrsbh、shxydm 等 l 大家已经习惯的强烈建议保持,比如 xxsx(显式顺序)、node_id 等 l 目前系统记录作者字段采用了 varchar(30),那么后续应尽量使用该长度
三. SQL 语句规范
3.1 SQL 语句要尽可能简单
- 一条 SQL 只能在一个 CPU 中运算,一条大的 SQL 可以长期占有 CPU 最终会堵死整个数据库,且没有任何中断处理措施.
- 把大语句拆成小语句,通过代码逻辑多次执行,减少锁时间
- 如果涉及到事务,更加要注意事务要尽可能的小,执行时间短,比如上传图片、加工表数据就决不能使用事务,带事务的处理必须在 100ms 以内
- 在 DRDS 模式下,禁止使用触发器和函数,在 SQL 里也不允许出现自定义函数,官方函数除聚合函数之外也应该尽量少使用
- 所有 SQL 必须在 1000ms 内返回,原则上在 100ms 内返回
3.2 禁止使用 SELECT *
- SELECT 会额外消耗 CPU、IO、网络资源
- SELECT * 也不利于程序扩展性,一旦字段变动程序就会出现问题
- 不能有效的利用覆盖索引
3.3 limit 高效分页
- limit 越大，效率越低,所以当存在很大的 limit 情况时应考虑通过线性 ID来取巧设计.select id from t limit 1000000, 10; 可以改为 =>select id from t where id > 1000000 limit 10;
- 当 limit 起始为很大数时,应考虑业务上给予限制,比如分页结果应该限制最多翻到 1000 页.
3.4 建议使用 union all 替代 union
- union 有去重开销,首先业务上查询时就不应该存在重复,确有重复可以在代码中去除
- 大多数业务场景都可以在业务端保证不重复
- 要正确认识和在正确场景下分别使用 union all 和 union
3.5 统一使用 count(*)
- count(*)是 SQL 标准写法
- count(*)会统计值为 NULL 的行，而 count(列名)不会统计,但是出现统计非 NULL 行这种业务本身就不合理
- 一定要统计指定列非空场景则至少应该使用 count(distinct 列名)
3.6 JOIN 使用规范
- 尽量不用连接 JOIN,保持单表查询
- JOIN 连接字段的数据类型保持一致,避免类型转换,甚至全表扫᧿
- JOIN 连接字段原则建议都带有索引,主表侧必须带有索引
- 表很小的情况下可以没有索引,保持索引区分度
- 原则上禁止 3 张自建业务表以上的 JOIN
- 禁止大表使用 JOIN,会产生大量临时表,极大消耗数据库内存
3.7 禁止使用 OR
- RDS 对 OR 子句的支持很差,虽然 MySQL 后续版本已经优化,但是当前 RDS还是存在严重 OR 子句问题
- 可以把 OR 改写为 IN 子句或者 UNION 子句
3.8 禁止使用 INSERT INTO 表名 VALUES(xxx)
- 必须显示指定插入的列清单,容易在增加或者删除字段后出现程序 BUG
3.9 禁止使用属性隐式转换
- 禁止查询时类型不匹配,如数据库是字符串类型,查询时给了 int 类型,会导致全表扫比如: SELECT xxx FROM 表名 WHERE 字符串类=123456
3.10 禁止在 WHERE 条件的属性上使用函数或者表达式
- 应该对条件值使用函数而不是对条件列使用函数,禁止对条件列使用函数 如:SELECT xxx FROM 表名 WHERE from_unixtime(day)>=’2017-02-15’正确的写法是:SELECT xxx FROM 表名 WHERE day>= unix_timestamp(‘2017-02-15’)
3.11 禁止负向查询
- 负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE 等，会导致全表扫
3.12 禁止左模糊匹配
- %开头的左模糊查询无法利用索引,会导致全表扫
3.13 使用 ISNULL()来判断是否 NULL 值 l NULL = NULL 的结果返回值是 NULL 而不是 true
- NULL=1 的结果返回值是 NULL 而不是 true
- 对 null 的处理时候，只能采用 is null 或 is not null，而不能采用=、in、<、<>、!=、not in 这些操作符号。如：WHERE name != ’zhuyungao’，如果存在 name 为null 值的记录，查询结果就不会包含 name 为 null 值的记录
3.14 查询尽量利用索引
- 使用索引需注意 where 条件以及排序、分组,比如有联合索引 idx_a_b_c
where a=1 and b=2 and c=3 可以完全利用索引
where a=1 and c=3 and b=2 也可以利用索引,但是需要一层内存转换消耗
where a=1 and b>2 and c=3 仅可以利用 a、b 列索引
where a=1 and b like ‘hello%’ order by 3 仅可以利用 a、b 列索引
where a=1 and b=2 group by c 可以利用 a、b、c 列索引 - 可以查看执行计划确认索引使用情况
*至少需要达到 range 级别以上
3.15 禁止使用 DELETE 语句
*业务表数据都通过软删除来处理
- DELETE 语句一律运维操作执行
3.16 数据库一次查询行数必须小于 1000 行 l 超过 1000 行一定要分页查询
3.17 禁止 IN 配合 SQL 子句
*IN (params),必须保证 params 为常量值,而不能是 SQL 子句
- 尽量应转换为 JOIN 查询
3.18 SQL 中不允许出现密钥或密码
- 可以先使用占位符代替在私聊给数据库执行人
四. 索引规范
4.1 唯一特性组合字段必须有索引
- 业务上唯一的单个字段必须加上唯一索引
- 业务上多个字段组合逻辑唯一必须加上联合唯一索引,不能仅在程序里逻辑保证
4.2 JOIN 列必须有索引
- JOIN 列上务必有索引且类型相同,防止全表扫᧿
4.3 尽量不要在 varchar 上建立索引
- 在 varchar 字段上需要索引说明表设计存在一定的缺陷或业务流程为能理清
- 一定要在 varchar 上建立索引则必须指定索引长度
4.4 联合索引顺序规范
- 联合索引首先按照常使用的业务查询顺序排列,适当调整业务查询顺序
- 再者按照列的区分程度高低顺序排列,区分程度高的一定要在前
- ORDER BY 的字段放在联合索引最后,防止 file_sort
4.5 单表索引不得超过 5 个 l 可以考虑使用联合索引合并多个索引,MySQL 中联合索引支持左匹配
4.6 一个索引的字段不得超过 6 个 l 一个业务特性需要索引 6 个列来区分,本身就存在问题
4.7 建立索引的区分度必须小于千分之一
- 索引能定位到的行占总行的千分之一以下
- 区分度低的索引不仅不能ᨀ高查询效率,反而大幅降低插入效率
- 覆盖记录条数过多的列绝不能建索引，例如“性别”
4.8 索引命名规范
- 唯一索引使用 uk_xxxx,根据业务取名,不得中英混合,不得超过 32 个字
- 其它索引使用 idx_xxxx,根据业务取名,不得中英混合,不得超过 32 个字
五. 业务类规范
5.1 业务表前缀指定
- A业务表统一使用前缀 A_ l
5.2 业务表必须包含字段
- 必须包含 creator 字段,用于记录行创建人,不用于业务记录
- 必须包含 creator_date 字段,用于记录行创建时间,不用于业务记录
- 必须包含 modifier 字段,用于记录行修改人
- 必须包含 modification_date 字段,用于记录行修改时间
- 拼音模式下为 cjr、cjsj、xgr、xgsj
5.3 业务字段命名规范
- 手机号码统一英文使用 mobile、拼音统一使用 sjhm
- 启用标记统一英文使用 enabled、拼音统一使用 qybj
5.4 加密类规范
- 反射 Bean 和 Mapper 文件时必须使用公司的反射工具
- 公司的反射工具通过字段注释来区分采用何种方式加密并自动生成加解密 TypeHandler
- #CryptBase36#代表加密结果集在 26 个字母+10 个数字范围内
- #CryptBase52#代表加密结果集在 52 个字母+10 个数字范围内
- #CryptSimple#加密结果在字符串范围内
六. 程序使用规范
6.1 禁止使用 Mybatis 的部分用法
- 禁止使用 association 关键字
- 禁止使用 collection 作为映射中关键字
- 禁止使用 select 作为结果映射中关键字
七. 其他规范
7.1 数据库审批流程附件必须以.sql 结尾
- SQL 必须以附件形式出现
- 附件必须以.sql 结尾
7.2 表结构修改必须说明目的
- 涉及到结构修改应说明修改目的、设计思维
- 另需对比说明原来为何如此设计、现在这样改带来的好处
7.3 审批流程仅指执行生产库
- 开发库可以由开发自行执行
- 测试库在事业部负责人同意情况下可自行执行
本文转载自: 掘金