MySQL数据库设计规范

MayoiKaze

已于 2023-10-24 15:06:42 修改

阅读量140

点赞数 1

文章标签：数据库 mysql 设计规范 1024程序员节

于 2023-10-21 10:24:11 首次发布

本文链接：https://blog.csdn.net/NaZoKaZe/article/details/133957233

版权

一、数据库规范

所有规范会按照【高危】、【强制】、【推荐】、【建议】四个级别进行标注，遵守优先级从高到底。对于不满足【高危】和【强制】两个级别的设计，DBA 会强制打回要求修改。

1 设计规范

1.1 建库规范

【强制】：库的名称必须控制在32个字符以内
【建议】：库的名称格式：业务系统名称_子系统(模块)名
【建议】：一般分库名称命名格式是库通配名_编号或库通配名_时间
【建议】：创建数据库时可显示指定字符集，只能使用 utf8 或 utf8mb4 格式，数据库默认字符集位utf8

1.2 建表规范

1.2.1 整体结构限制

【强制】：表名称必须控制在32个字符以内，表名只能使用字母、数字和下划线
【建议】：表名建议全部使用小写字符
【建议】：单独同一模块的表名建议使用相同字符作为前缀区分
【强制】：不允许创建分区表
【强制】：无特殊需求，所有表的存储引擎默认都为InnoDB。如果需要使用其他存储引擎，必须通过开发负责人和DBA审核之后才可以使用
【建议】：创建表时可显式指定字符集，只能使用utf8或utf8mb4格式，表默认字符集为 utf8
【强制】：建表必须有comment注释信息
【建议】：使用临时表或中间表时，建议以tmp_、bak_、日期等作为表名前缀或后缀区分
【强制】：表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint
（ 1 表示是，0 表示否）。
说明：任何字段如果为非负数，必须是 unsigned。
正例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。
【强制】：表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只
出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库
名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name

1.2.2 列限制

【强制】：建表SQL列名不能设置为MySQL关键字
【建议】：建表SQL列名使用小写字符
【强制】：建表SQL中自增列必须设置为int或bigint类型，并且只能有一列自增列
【建议】：自增列初始值建议设置为1
【建议】：对核心表及配置记录表等建议设置数据创建时间、更新时间字段
【建议】：表中所有字段都需要设置默认值，并不能使用NULL值，避免出现聚合计算偏差
【建议】：不推荐使用enum、set类型，不利于后续枚举值变更，推荐使用tinyint或int类型
【强制】：所有字段都必须设置comment注释信息
【强制】：表名不使用复数名词。
说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数
形式，符合表达习惯。
【强制】小数类型为 decimal，禁止使用 float 和 double。
说明：float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不
正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
【强制】varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长
度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索
引效率。
【推荐】表的命名最好是加上“业务名称_表的作用”。
正例：alipay_task / force_project / trade_config

1.2.3 索引限制

【强制】：建表SQL必须有且只有一个主键，类型为int或bigint，主键最多只能指定一个字段
【建议】：表主键建议设置为自增，避免随机主键写入引起的性能下降
【强制】：不允许创建外键，同步更新需通过业务逻辑程序实现
【建议】：单个索引中每个索引记录的长度不能超过64KB
【强制】：唯一索引以uk_或uq_开头，普通索引以idx_开头，以字段名称或缩写作为后缀
【建议】：单个表上的索引个数不能超过5个
【建议】：在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面
【建议】：在多表join的SQL里，保证被驱动表的连接列上有索引，这样join执行效率最高
【建议】：建表时，保证表里互相不存在冗余索引
【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明
显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必
然有脏数据产生。
【强制】超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询时，
保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据
实际文本区分度决定索引长度即可。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索
引。
【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合
索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引
a_b 无法排序。
【推荐】利用覆盖索引来进行查询操作，避免回表。
说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览
一下就好，这个目录就是起到覆盖索引的作用。
正例：能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是一种查询的一种
效果，用 explain 的结果，extra 列会出现：using index。
【推荐】利用延迟关联或者子查询优化超多分页场景。
说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回
N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过
特定阈值的页数进行 SQL 改写。
正例：先快速定位需要获取的 id 段，然后再关联：
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts
最好。
说明：
1）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2）ref 指的是使用普通的索引（normal index）。
3）range 对索引进行范围检索。
反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级
别比较 range 还低，与全表扫描是小巫见大巫。
【推荐】建组合索引的时候，区分度最高的在最左边。
正例：如果 where a=? and b=? ，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即
可。
说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>?
and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。
【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
【建议】创建索引时避免有如下极端误解：
1）宁滥勿缺。认为一个查询就需要建一个索引。
2）宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
3）抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

1.3 改表规范

1.3.1 整体结构限制

【强制】：禁止修改表的存储引擎类型
【强制】：同个表的改表语句需合并在一条SQL中，避免多次改表影响性能
【高危】：禁止提交DROP、TRUNCATE、RENAME等高危工单
【建议】：对于超过100W的表结构修改，尽量发起定时执行工单，在低峰期处理

1.3.2 列限制

【强制】：禁止添加或修改字段名为 MySQL关键字
【建议】：建议添加或修改字段名称全部使用小写字符
【强制】：禁止删除字段
【强制】：禁止缩短字段长度
【强制】：禁止修改字段类型，比如int转为varchar类型等
【强制】：新增自增字段时，类型必须为int或bigint
【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
1）不是频繁修改的字段。
2）不是 varchar 超长字段，更不能是 text 字段。
正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存
储类目名称，避免关联查询。

2 使用规范

2.1 DML语句

2.1.1 insert语句

【强制】：insert语句指定具体字段名称
【强制】：insert语句中禁止使用select *语法
【强制】：insert语句中使用select语法时必须指定where条件
【建议】：insert into…values(XX),(XX),(XX)…这里XX的值不要超过5000个，避免引起数据库主从延迟
【建议】：对于有auto_increment属性字段的表的插入操作，并发需要控制在200以内

2.1.2 delete语句

【强制】：delete删除数据时需指定where条件
【建议】：删除数据时尽量使用主键或其他索引列作为限制条件，避免全表扫描
【高危】：禁用delete t1 … where a=XX limit XX; 这种带limit的删除语句，因为会导致主从不一致，导致数据错乱
【建议】：批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep，一般建议值5-10秒
【建议】：大批量数据删除时建议提交数据归档工单申请

2.1.3 update语句

【强制】：update更新数据时需指定where条件
【建议】：更新数据时尽量使用主键或其他索引列作为限制条件，避免全表扫描
【高危】：禁用update t1 … where a=XX limit XX;这种带 limit 的更新语句，因为会导致主从不一致，导致数据错乱
【高危】：update禁止使用关联子查询，如update t1 set … where name in(select name from user where…);效率极其低下
【强制】：禁止在业务的更新类SQL语句中使用join

2.1.4 select语句

【强制】：select语句必须指定具体字段名称，禁止写为select *
【建议】：select查询时建议使用索引字段作为限制条件，并且查询数据量不要超过全表的25%，确保查询使用到合适索引
【强制】：where条件里等号左右字段类型必须一致，否则无法利用索引
【强制】：WHERE子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引
【建议】：索引列不要使用函数或表达式，否则无法利用索引
【建议】：in值列表限制在500以内，可以减少底层扫描，减轻数据库压力从而加速查询
【建议】：select语句尽量使用union all代替union，并且关联子句个数限制在5个以内。因为union all不需要去重，节省数据库资源，提高性能
【建议】：减少使用or语句，可将or语句优化为union，然后在各个where条件上建立索引
【建议】：分页查询，当 limit 起点较高时，可先用过滤条件进行过滤
【强制】：禁止跨db的join语句
【建议】：不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询
【建议】：线上环境，多表join不要超过3个表
【建议】：多表连接查询推荐使用别名，且SELECT列表中要用别名引用字段，数据库.表格式
【建议】：在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表
【建议】：减少使用order by，尽量在业务程序上排序，减少对机器 CPU 的性能损耗
【建议】：order by、group by、distinct这些 SQL 尽量利用索引直接检索出排序好的数据。如where a=1 order by b可以利用key(a,b)。
【建议】：包含了order by、group by、distinct这些查询的语句，where 条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

2.2 SQL语句

【强制】不要使用 count(列名)或 count(常量)来替代 count(*)，count(*)是 SQL92 定义的
标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
【强制】当某一列的值全是NULL时，count(col)的返回结果为 0，但 sum(col)的返回结果为
NULL，因此使用sum()时需注意 NPE 问题。
正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
【强制】使用 ISNULL()来判断是否为 NULL 值。
说明：NULL 与任何值的直接比较都为 NULL。
1） NULL<>NULL 的返回结果是 NULL，而不是 false。
2） NULL=NULL 的返回结果是 NULL，而不是 true。
3） NULL<>1 的返回结果是 NULL，而不是 true。
【强制】在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：以学生和成绩的关系为例，学生表中的 student_id是主键，那么成绩表中的 student_id
则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为
级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻
塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
【强制】数据订正时，删除和修改记录时，要先 select，避免出现误删除，确认无误才能执
行更新语句。
【推荐】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控
制在 1000 个之内。
【参考】如果有全球化需要，所有的字符存储与表示，均以 utf-8 编码，注意字符统计函数
的区别。
说明：
SELECT LENGTH("轻松工作"); 返回为 12
SELECT CHARACTER_LENGTH("轻松工作")； 返回为 4
如果需要存储表情，那么选择 utfmb4 来进行存储，注意它与 utf-8 编码的区别。
【建议】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE
无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

2.3 ORM映射

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
说明：
1）增加查询分析器解析成本。
2）增减字段容易与resultMap配置不一致。
【强制】POJO 类的布尔属性不能加is，而数据库字段必须加is_，要求在resultMap中进行
字段与属性之间的映射。
说明：参见定义 POJO 类以及数据库字段定义规定，在中增加映射，是必须的。
在 MyBatis Generator 生成的代码中，需要进行对应的修改。
【强制】不要用resultClass当返回参数，即使所有类属性名与数据库字段一一对应，也需
要定义；反过来，每一个表也必然有一个与之对应。
说明：配置映射关系，使字段与 DO 类解耦，方便维护。
【强制】sql.xml 配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL 注入。
【强制】iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。
说明：其实现方式是在数据库取到statementName对应的SQL语句的所有记录，再通过subList取 start,size 的子集合。
正例：Map<String, Object> map = new HashMap<String, Object>();
map.put("start", start);
map.put("size", size);
【强制】不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
说明：resultClass=”Hashtable”，会置入字段名和属性值，但是值的类型不可控。
【强制】更新数据表记录时，必须同时更新记录对应的 gmt_modified 字段值为当前时间。
【推荐】不要写一个大而全的数据更新接口。传入为 POJO 类，不管是不是自己的目标更新字
段，都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL时，不要更新无改动的字段，一是易出错；二是效率低；三是增加 binlog 存储。
【建议】@Transactional 事务不要滥用。事务会影响数据库的 QPS，另外使用事务的地方需
要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
【建议】<isEqual>中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时带
上此条件；<isNotEmpty>表示不为空且不为 null 时执行；<isNotNull>表示不为 null 值时
执行。

附录

附录一：MySQL索引失效的常见情况

最左前缀原则。例如：存在联合索引idx_a_b(a, b)，查询条件使用where b = 1则无法使用索引
LIKE前置模糊查询。例如：col_name like '%test' 或 col_name like '%test%'
索引列使用函数或存在计算。例如：存在索引idx_col(col)，查询条件使用where left(col, 2) = 'te'
查询条件使用is not null。设计表结构时尽量设置not null约束
字段类型出现隐式转换。例如：存在字段test_col为varchar类型，查询时使用了where test_col = 1，隐式转为了int类型导致索引失效
条件中有 or 存在可能不会使用索引。例如：查询条件为where a = 'testa' or b = 'testb'，存在索引idx_a(a)，此时也不会使用索引，除非为b字段也添加索引
查询结果超过整体结果的25%或三分之一，或者表数据量比较少时，MySQL认为全表扫描代价更小，会导致索引失效