MySQL-数据库设计开发规范
概述
第一部分，数据库对象命名规范。
第二部分，数据库对象设计规范。
第三部分，数据库开发相关规范。
第四部分，常见数据库问题处理。
第五部分，附录。
- MySQL保留字
- 常用数据类型参考
- 目标对象命名参考
一、数据库对象命名规范
1、基本通用命名原则
1.1 【强制】命名使用带有含义的英文单词，避免以数字开头，多个单词组成时，禁止使用连字符。
1.2 【强制】只能使用小写英文字母、数字和下划线。
1.3 【强制】长度避免超过30个字符，尽量控制在20个字符以内。
1.4 【强制】库名应与应用名称或模块名称保持一致，prod，pre和test环境由ay+工程名称组成，trial环境由ay+工程名称+_trial后缀组成，除了试用环境，尽量避免使用下划线。e.g.,CREATE database ayranger DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
1.5 【强制】避免使用MySQL的关键字、以及系统关键字，关键字信息参见附录。
2、表命名规范
2.1 【强制】表名不使用复数名词，表名应该仅仅表示表中的实体内容，不应该表示实体数量。
2.2 【强制】表名只能使用小写字母、数字和下划线，禁止以数字开头。
2.3 【强制】特殊情况下，需要用到临时表进行数据中转时，临时表的创建使用，需加temp_前缀，名称后面加上日期后缀。备份表的创建使用，需加bak_前缀，名称后面加上日期后缀。（临时表不能存储业务数据，确保两种表可以定期进行清理）
2.4 【建议】表名长度不要超过30个字符。
2.5 【建议】对于同一个应用模块或系统的表，使用统一前缀，整体表名尽可能表达含义传递准确信息，例如：inquiry_order_supplier_quote、feedback_question_result、customer_manual_label等等。前缀应多于2个字符，尽量不多于6个字符。
3、字段命名规范
3.1 【强制】字段名称由表达实际含义的英文单词或缩写组成。
3.2 【强制】表示是与否概念的字段，使用 is_xxx 的方式命名，数据类型使用tinyint（ 1 表示是，0 表示否），例如：is_deleted，1 表示删除，0 表示未删除。
3.3 【强制】自增主键id字段使用id命名，不用增加前缀或后缀等信息，避免误以为将主键设置在业务字段上了使人产生误解，（主键id字段设计规范下文会讲到），由uuid_short()函数构造的主键统一以pk_id命名。
3.4 【强制】每张表中必须包含的四个公共字段，统一定义为如下字段名称：（和现状保持一致）
creationtime（创建时间）
creator（创建人）
modifiedtime（修改时间）
modifier（更新人）
is_deleted (删除标识)
3.5 所有使用bigint类型的字段都必须设置为unsigned（除非有存储负数的场景，特殊情形另外沟通）,防止数据值超出有符号整型的最大值。
3.6【建议】表与表之间相同含义的字段应同名。
4、索引命名规范
4.1 【强制】idx_<column_name>[..._<column_name>]，各部分以下划线（_）分隔。
4.2 【强制】多个单词组成的column_name或多字段复合索引，取字段首个单词或者首字母缩写。
4.3 【强制】索引中列名可简写。
5、约束命名规范
5.1 【强制】主键约束： pk_开头，pk_<column_name>。
5.2 【强制】unique唯一约束：uniq_开头，uniq_<column_name>[..._<column_name>]。
5.3 【强制】禁止添加外键约束，不允许上线，如果业务逻辑需要，尽量从程序层面进行逻辑控制。
6、视图、函数、触发器、存储过程不允许上线
6.1 【强制】不建议在MySQL数据库内创建视图、函数、触发器或存储过程等对象，不允许上线，如果业务逻辑需要，尽量从外部程序层面进行逻辑控制。
6.2 【强制】如果临时需要新建存储过程处理生产问题，提交jira单到运维基建组，并且在处理完问题及时删除。
二、数据库对象设计规范
1、表设计规范
1.1 【强制】上线的各系统业务表统一使用innodb存储引擎，默认存储引擎，无需指定。
1.2 【强制】如无特殊情况，统一指定字符集为utf8或utf8mb4，推荐使用utf8mb4。
1.3 【强制】每张表必须包含五个公共字段，公共字段属性定义如下：
creationtime datetime not null default CURRENT_TIMESTAMP comment '创建时间',
creator varchar(20) not null default '' comment '创建人',
modifiedtime datetime not null default CURRENT_TIMESTAMP comment '修改时间',或者modifiedtime datetime not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '修改时间'(取决于代码本身更新此时间还是在变更时触发数据库自身记录更新时间戳),
modifier varchar(20) not null DEFAULT '' comment '更新人' ,
is_deleted tinyint(2) not null default 0 comment '逻辑删除标识，0 未删除，1 已删除'，
【相关说明】:
- 所有进入生产系统的业务表必须包含这五个公共字段，这五个字段不涉及任何业务逻辑，禁止使用公共字段存储业务数据，纯属底层标识仅供DBA和报表团队使用。
- 公共字段能够标记所有被人为干涉过的每条生产数据在什么时间被谁创建或修改过，以便DB底层的记录和历史的追溯，报表团队也依赖这些字段进行数据仓库日常的数据抽取和统计，所以各位同学务必保证进入DB的所有表包含5个公共字段且未被业务挪用存储业务信息。
- creator，modifier根据实际存储的数据类型可以定义为int(11)、bigint(20) unsinged或者varchar(20)。
1.4 【强制】线上线下环境创建的所有业务表必须包含主键id，主键与业务逻辑无关，禁止使用主键字段存储业务数据。如果主键上包含业务逻辑关系，日常的数据录入与维护很容易产生误操作以及主键冲突，而且没有一成不变的业务，一旦业务发生变迁更新，带来系统级的数据变动代价极其巨大。并且，当前有些系统的MySQL架构对主键键值有严格要求，不允许业务层面对它有变更权力。
【相关说明】:
- 【强制】禁止更新主键值。
- 【强制】禁止使用复合主键。
- 【强制】禁止使用主键字段存储业务数据。
- 【强制】禁止主键使用varchar、char的字符类型。请使用bigint unsigned类型，且设置为自增。
【参考】：[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键
1.5 【建议】根据业务逻辑情况，事先评估表的操作是否频繁，是否需要适当冗余字段以提高查询效率（避免关联查询），但冗余字段必须保证数据一致性、是不被频繁修改的字段、以及不是varchar超长字段。
1.6 【建议】每张表建议不超过60个字段，字段过多时建议进行大表拆分以保障系统性能，例如拆分为一张查询表和一张详细内容表。
2、字段设计规范
2.1 【强制】每个业务字段需要有详细的字段注释，如果字段为枚举类型，或普通数据类型当做枚举使用，需要在字段注释中列举枚举范围并说明每个枚举值的含义如：“status状态，0：待确认，1：可结算，2：结算中，3：已结算”。
2.2 【强制】自增ID建议使用bigint unsigned类型，频繁增删的表自增范围容易耗尽， 后期修改自增字段代价比较大。
2.3 【强制】使用尽可能小的varchar字段，字段长度尽量简化，个别单词过长取缩写表示。
2.4 【强制】原则上所有字段必须定义为not null，且加上默认值default。
【相关说明】:
- 建表时容易将字段默认定义为null，有些MySQL客户端自动生成脚本会默认设置为null，有些开发人员为了省事也不做正确严谨的定义，或者有意设置为null简化逻辑判断，使自己的代码逻辑更方便快捷，各种不规范的设计方式请大家务必重视，并自我调整改善，DBA会继续加强项目上线脚本的审核。
- not null一定程度上减少了存储开销，存储空间消耗比null更低。
- not null能有效避免索引失效，使SQL脚本的效率更高，利于整体全局环境的系统优化。
- null值影响判断，容易导致很多查询场景的查询结果出错。
- null值影响统计计算，非常不利于大数据团队在数据抽取后的统计分析。
2.5 【强制】所有字段在创建和修改时必须带有注释且含义表达清晰。如果修改字段含义或者对字段状态追加时，需要同步更新字段注释。
2.6 【强制】在满足业务使用的前提下，字段的数值范围尽可能小，以节省磁盘和内存空间，不要超出业务逻辑常理的范围。
2.7 【强制】禁止使用Blob、Text、Json类型字段（含MediumText、LongText、MediumBlob、LongBlob等），不允许上线。如有必要，考虑对大字段表做关联分离。
【相关说明】:
当InnoDB的文件格式（innodb_file_format）设置为Antelope，并且行格式为COMPACT 或 REDUNDANT 时，BLOB、TEXT或者长VARCHAR列只会将其前768字节存储在聚集索引页中。（最大768字节的作用是便于创建前缀索引/prefix index），其余更多的内容存储在额外的page里，哪怕只是多了一个字节。因此，所有列长度越短越好。在off-page中存储的BLOB、TEXT或者长VARCHAR列的page是独享的，不能共享。因此强烈不建议在一个表中使用多个长列。
2.8 【强制】小数类型建议使用decimal，禁止使用float和double（数据存储时时可能导致精度损失）。
2.9 【建议】枚举型的字段类型建议使用tinyint类型，尽量避免tinyint(1),以防踩坑。
2.10 【建议】用整型保存ip字段，而不是varchar。使用函数inet_aton和inet_ntoa进行转换。
3、索引设计规范
3.1 【强制】一般情况下创建的均为btree索引，不建议创建bitmap索引。
3.2 【强制】防止因字段类型不同造成的隐式转换，导致索引失效。
3.3 【强制】禁止重复索引，禁止冗余索引，禁止使用外键，禁止使用全文索引，不对过长的varchar字段建立索引，可考虑前缀索引，前缀索引长度建议不超过8个字符。
3.4【强制】页面搜索严禁左模糊或者全模糊。B-tree具有最左前缀匹配特征，如果左边的值无法确定那么无法利用索引检索数据。
3.5 【强制】会进行联表查询的join列必须数据类型相同且需要建立索引，同等条件下建议优先使用数字类型字段进行连接。
3.6 【强制】索引字段选择在where子句中出现，且选择性和过滤性高的字段，且和其他表关联的字段。
3.7 【建议】不在低基数列上建立索引，例如“性别”。
3.8 【建议】建组合索引的时候，区分度最高的在最左边。
3.9 【建议】业务上具有唯一特性的字段，即使是多个字段的组合，建议建成唯一索引。
4、约束设计及其他规范
4.1 【强制】创建主键时，禁止使用组合主键。主键创建后不建议更新。
4.2 【强制】不允许外键与级联，如果业务逻辑需要，应该通过程序层面来控制。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险。
4.3 【强制】如果应用程序使用长连接来连接数据库，尽量具备重连机制，避免每次执行SQL时都检查DB。
5、视图、函数、触发器、存储过程不允许上线
5.1 【强制】不建议在数据库内创建视图、函数、触发器、存储过程等对象，不允许上线，如果业务逻辑需要，应该通过外部程序层面进行逻辑处理。
三、数据库开发相关规范
1、关于count的使用
1.1 【强制】不要使用count(列名)或count(常量)来替代count(*)，count(*)是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。count(*)会统计值为NULL的行，而count(列名)则不会统计。
1.2 【建议】count(distinct col)计算该列除NULL之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为 0。
2、关于Null值字段的处理
2.1 【建议】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE问题。正例：可以使用如下方式来避免sum的NPE问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table。
2.2 【建议】使用ISNULL()来判断是否为NULL值，NULL与任何值的直接比较都为NULL
【相关说明】:
1） NULL<>NULL 的返回结果是 NULL，而不是 false。
2） NULL=NULL 的返回结果是 NULL，而不是 true。
3） NULL<>1 的返回结果是 NULL，而不是 true。
3、关于DML/DDL操作
3.1 【强制】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在200个之内。
3.2 【强制】TRUNCATE TABLE比DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。说明：TRUNCATE TABLE 在功能上与不带WHERE子句的 DELETE语句相同。
3.3 【强制】杜绝直接 SELECT * 读取全部字段，既影响查询的性能，又会对后期扩展造成不必要的麻烦。
3.4 【强制】在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。
3.4 【强制】SQL语句不可以出现隐式类型转换（比如：select col from t where id=’1’）
3.5 【强制】禁止在where条件列上使用函数（会导致该列索引无效，从而全表扫描）
3.6 【强制】事务要简单，拒绝大事务，整个事务的时间长度不要太长。
3.7 【强制】删除操作尽量使用逻辑删除而非物理删除。
3.8 【强制】数据查询或数据操作，关联表的上限尽量控制3张表内，禁止超过3张表的join，且join关联字段的数据类型必须保持一致。
3.7 【建议】SQL语句尽可能简单，大的SQL尽量拆成小的sql语句(充分利用QUERY CACHE和多核CPU)。
4、关于数据存储
【强制】数据库中不允许存储大文件，或者照片等多媒体文件，可以将大对象放到磁盘或者云存储上，数据库中仅存储路径
5、关于DB环境使用
【强制】禁止用线下环境代码连接生产DB环境。
6、关于全球化的需求
【强制】所有的字符存储与表示，均以 utf-8 编码，注意字符统计函数的区别。
说明：
- SELECT LENGTH("轻松工作")；返回为12
- SELECT CHARACTER_LENGTH("轻松工作")；返回为4
- 表情字符选择utfmb4，（占用4个字节），注意它与utf-8编码的区别。
四、数据库常见问题处理
【常见问题一】：数据库无法连接
错误1：
ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx'(61 "Connection refused")
问题分析：找不到服务器地址及对应的端口，需要开发人员查看数据库地址及端口是否填写错误。
错误2：
ERROR 1045 (28000): Access denied for user'username'@'xxx.xxx.xxx.xxx'' (using password: YES)
问题分析：程序已经找到数据库服务器，但是认证的时候报用户名或者密码错误，需检用户名和密码是否有问题。
【常见问题二】：同样的SQL查询，之前还好好的，突然就变慢了，我怎么办？
问题分析：这类问题对刚入门的开发者来说，很常见，当系统运行一段时间后出现卡顿等现象，发现问题后不知道该怎么办，不知道从哪着手分析、解决问题，当发现此类问题后，可以考虑如下方面：
- 查询where条件的列是否有索引。
- 单表数据量是否近期暴涨，如：超过千万级。
- join关联查询时，关联的字段是否都有索引，并且字符集是否一致。
- SQL逻辑是否还有优化的空间，如：优先过滤大表、转换SQL写法等。
- SQL查询时，系统是否出现死锁等问题
- 是否在索引列上，用了函数。
- where条件里，是否有数学计算逻辑。
- 如以上仍无法解决，可以寻求团体成员，或者DBA的帮助。
【常见问题三】：前端页面出现卡顿现象，系统查询延迟增高，服务器CPU及连接数明显增大，查看数据库SQL进程明显增多，可能是什么原因？
问题分析：出现该现象问题分析会复杂一点，可以概括如下方面：
- SQL是否存在慢查询问题，如表缺少索引等。
- 查看数据库是否有锁表现象，在同一事务下，是否有其他SQL的锁等待，造成系统该SQL查询积压。
- 需要运维同学检查前端缓存是否正常工作。
- 需要排查系统是否存在被恶意刷单，刷接口常见。
- 其他，如搞活动，促销等造成系统负载增大。
附录
MySQL保留字
常用字段类型参考
目标对象命名参考
正文到此结束
热门推荐
相关文章
近期评论
-
来自： 留言板
-
来自： 留言板
-
来自： js-处理网页图片宽度自适应屏幕宽度
-
来自： 留言板
-
来自： SpringBoot-线上服务卡死卡顿分析-http请求进不去服务-调整tomcat-undertow线程数
