数据库规范

规范清单

#	规范分类	二级分类	规范说明	执行建议	说明及示例
1	库表设计规范	库表容量规范	单表不得超过50G	【强制】	说明：表太大，ddl很慢， 1G2min，50G的表，ddl就需要将近2小时，如有其他要求，请邀请DBA一起进行评估。 问题：无COE，DBA作业要求
2			单库不得超过200G	【强制】	说明：超过200G，不支持工单迁移的（RDS限制），如有其他要求，请邀请DBA一起进行评估。 问题：无COE，DBA作业要求
3		表设计规范	禁止使用 int 类型进行业务主键设计	【强制】	说明：(1) 非核心业务：可以使用主键自增ID，如告警、日志、监控等信息。(2) 核心业务：建议单独设计业务主键，全局唯一且是单调递增。建议使用发号器生成Long值，避免使用UUID、MD5 和HASH这些作为主键值。 问题：无COE，订单表订单号类型依赖治理
4			建议更新时间设置为：DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	【推荐】	说明：current_timestamp 支持sql语法，now仅仅是mysql，current_timestamp 更广泛
6	索引设计规范	索引数量规范	单张表中索引数量不超过5个	【强制】	说明：现在mysql的索引中列总长度不能超过200，虽然索引可以提高查询性能，但过多的索引也会导致性能下降。因为创建索引会占用磁盘空间，并且在插入、更新和删除操作时需要维护索引，可能会增加额外的开销，ddl工单里约束的，超过就用前缀索引，建议3个左右 问题：无COE，DBA工单约束
7			单个索引中的字段数不超过5个	【强制】	说明：字端过多浪费内存，且列越多区分度肯定不高，建议3个以内 问题：无COE 组合索引一般设计原则： (1) 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）。 (2) 尽量把字段长度小的列放在联合索引的最左侧（字段长度越小，一页能存储的数据量越大，IO性能也就越好）。 (3) 使用最频繁的列放到联合索引的左侧（可以比较少的建立一些索引）。 (4) 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引。 重复索引示例：primary key(id)、index(id)、unique index(id) 冗余索引示例：index(a,b,c)、index(a,b)、index(a)
8		索引设计规范	频繁的SELECT语句条件、ORDER BY、GROUP BY、DISTINCT的字段建议是索引。	【推荐】	说明：通过使用索引，查询操作可以更快地完成，从而减少数据库服务器的负载。这对于高负载的应用程序和大型数据集尤为重要。 案例： 假如有个订单表： CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT NOT NULL, order_date DATE NOT NULL, product_name VARCHAR(255) NOT NULL, quantity INT NOT NULL, price DECIMAL(10, 2) NOT NULL ); 如果你经常需要根据某个字段（例如order_date）对结果进行排序，那么为该字段建立索引可以提高排序操作的效率。
9	SQL开发规范	事务规范	禁止一次修改大量数据的事务	【强制】	说明：建议事务执行时间不能太长，避免超100ms或执行超1000行的大事务，大事务一次修改大量数据，可能会占用过多资源，影响系统性能，甚至导致系统崩溃。 问题：2017  【事务包含RPC请求】拣货添加中转箱功能事务中进行接口调用，接口中批量操作数据慢SQL，导致了大事务。
10		DML语句规范	禁止使用 select * 查询	【强制】	说明：(1) 增加查询分析器解析成本。（2） 增减字段容易与 resultMap 配置不一致。(3) 无用字段增加网络消耗，尤其是 text 类型的字段。 问题：2143 SQL语句使用selcet * 查询， 新增字段时导致有字段映射丢失。
11			核心业务场景禁止使用JOIN语句	【强制】	说明：原则上禁止超过三个表 join，需要 join 的字段，数据类型保持绝对一致，多表关联查询时，保证被关联的字段需要有索引。 问题：1421 3张表以上Join的复杂SQL，查询条件较多，执行计划容易走错索引。
12			单次查询结果集不得超过5000条	【强制】	说明：应避免全表扫描，除少量的配置表数据，可视具体场景而定。 问题：2336 接口请求参数未做校验，SQL查询未限制查询返回结果集，返回了226w条数据。
			MYSQL，DML语句后有LIMIT需要有限制	【推荐】	说明：删除、更新 案例：DELETE FROM students WHERE some_condition LIMIT 100;            UPDATE students SET column_name = 'new_value' WHERE another_condition LIMIT 50;
13			单次查询结果集大小不得超过1M	【强制】	说明：应避免全表扫描，除少量的配置表数据，可视具体场景而定。 问题：2336 接口请求参数未做校验，SQL查询未限制查询返回结果集，返回了226w条数据。
14			索引查询时，对索引，禁止使用前缀是%的like	【强制】	将导致引擎放弃使用索引而进行全表扫描。
15			禁止使用linq 或 mybatis-plus组件	【强制】	说明：linq&mybatisplus组件目前无法通过sonar工具直接扫描出全表扫描风险。
16			避免在where子句中对字段进行null值判断	【推荐】	说明： 将导致引擎放弃使用索引而进行全表扫描，建议对字段设置默认值，是默认值来判断。 问题：这里目前没有什么具体的线上COE，但是潜在风险比较高 案例：假设我们有一个employees表，其中有一个manager_id字段，表示员工的直接经理的ID。如果某个员工没有经理（例如，高层管理人员或某些特殊情况），则manager_id字段的值可能为NULL。 原始查询（包含NULL值判断） sql SELECT * FROM employees WHERE (manager_id IS NULL OR manager_id = ?); 优化策略 使用默认值：如果可能的话，考虑为manager_id字段设置一个默认值，比如一个特殊的ID（如0或-1），表示没有经理。这样，你就可以避免在查询中检查NULL值。 左连接（LEFT JOIN）：如果你需要基于经理ID来连接其他表，并希望包括那些没有经理的员工，可以使用左连接。 索引：确保manager_id字段已经建立了索引，这样即使进行了NULL值判断，查询性能也不会受到太大影响。 应用层逻辑：在某些情况下，你可能可以在应用层处理这种逻辑，而不是在数据库层。例如，你可以首先检索所有具有特定经理ID的员工，然后在应用代码中处理那些没有经理的员工。 如果我们将没有经理的员工的manager_id设置为-1，那么查询就可以简化为： SELECT * FROM employees WHERE manager_id = ? UNION SELECT * FROM employees WHERE manager_id = -1;
17			避免使用负向查询，如not in、not like， where 子句中使用 != 或 <> 操作符	【推荐】	说明：not in、not like 将导致引擎放弃使用索引而进行全表扫描,!= 或 <> 操作符将引擎放弃使用索引而进行全表扫描，MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。 案例：--not in 可以优化成 SELECT * FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM cancelled_orders co WHERE o.order_id = co.order_id ); 案例：SELECT * FROM orders WHERE status <> 'cancelled'; 优化：SELECT * FROM orders WHERE status IN ('pending', 'shipped');
18			避免在where中使用 or	【推荐】	说明：将导致引擎放弃使用索引而进行全表扫描。 案例： SELECT * FROM users WHERE email = 'example@email.com' OR phone_number = '1234567890'; 优化： SELECT * FROM users WHERE email = 'example@email.com' UNION SELECT * FROM users WHERE phone_number = '1234567890'; ⚠️：UNION会自动去除重复的行。如果你确定两个查询不会产生重复的结果，或者你想要保留重复的行，可以使用UNION ALL
19			避免数据类型的隐式转换，关联字段必须使用相同数据类型	【强制】	隐式转换会导致索引失效。 案例：假如订单表 orders 及 包裹表 packages 里面都有订单号，订单表long，packages因为某些原因设计为 varchar类型 如果我们在关联查询时数据类型不一致，我们要使用函数进行显示转换（ CAST() 或 CONVERT()）
20			避免在数据库中进⾏数学运算	【推荐】	说明：where 子句中的“=”左边进行函数、算术运算或其他表达式运算，将可能无法正确使用索引。 案例： SELECT * FROM employees WHERE UPPER(first_name) = 'JOHN'; 优化： SELECT * FROM employees WHERE first_name = LOWER('JOHN'); ⚠️ 尽可能重写查询，将函数或运算移到等号的右侧，这样数据库就可以使用索引了
21		主从开发规范	建议核心业务查询语句建议走主库查询。	【推荐】	说明：(1)【供应链】核心业务系统要求强制执行，(2)  其他业务场景可视具体场景而定。 问题：1459 数据库主从延迟导致回访记录里显示收益与实际收益不一致。
22	上线流程规范	脚本执行规范	禁止在业务高峰时段执行批量DML、DDL脚本，要求22点以后执行。	【强制】	建议影响作业场景脚本在凌晨空闲期间执行，应尽可能避免影响业务。
23		数仓同步流程规范	新增表字段及表迁移务必同步数仓，需及时评估数据影响。	【强制】