MySQL 团队开发规范,太详细了

Post Time:2021-10-26 14:44:47 Views:106

数据库对象命名规范

数据库对象

数据库对象是数据库的组成部分,常见的有以下几种:表(Table )、索引(Index)、视图(View)、图表(Diagram)、缺省值(Default)、规则(Rule)、触发器(Trigger)、存储过程(Stored Procedure)、 用户(User)等。命名规范是指数据库对象如数据库(SCHEMA)、表(TABLE)、索引(INDEX)、约束(CONSTRAINTS)等的命名约定。

视图命名规范

1、视图名以v开头,表示view,完整结构是v+视图内容含义缩写。

2、如果视图只来源单个表,则为v+表名。如果视图由几个表关联产生就用v+下划线(_)连接几个表名,视图名尽量不超过30个字符。如超过30个字符则取简写。

3、如无特殊需要,严禁开发人员创建视图。

4、命名应使用小写。

存储过程命名规范

1、存储过程名以sp开头,表示存储过程(storage procedure)。之后多个单词以下划线(_)进行连接。存储过程命名中应体现其功能。存储过程名尽量不能超过30个字符。

2、存储过程中的输入参数以i_开头,输出参数以o_开头。

3、命名应使用小写。

1 create procedure sp_multi_param(in i_id bigint,in i_name varchar(32),out o_memo varchar(100))  

函数命名规范

1、函数名以func开始,表示function。之后多个单词以下划线(_)进行连接,函数命名中应体现其功能。函数名尽量不超过30个字符。

2、命名应使用小写。

1 create function func_format_date(ctime datetime)

触发器命名规范

1、触发器以trig开头,表示trigger 触发器。

2、基本部分,描述触发器所加的表,触发器名尽量不超过30个字符。

3、后缀(_i,_u,_d),表示触发条件的触发方式(insert,update或delete)。

4、命名应使用小写。

1 DROP TRIGGER IF EXISTS trig_attach_log_d;
2 CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW; 

约束命名规范

1、唯一约束:uk_表名称_字段名。uk是UNIQUE KEY的缩写。比如给一个部门的部门名称加上唯一约束,来保证不重名,如下:ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE(name);

2、外键约束:fk_表名,后面紧跟该外键所在的表名和对应的主表名(不含t_)。子表名和父表名用下划线(_)分隔。如下:ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id);

3、非空约束:如无特殊需要,建议所有字段默认非空(not null),不同数据类型必须给出默认值(default)。

1 `id` int(11) NOT NULL,
2 `name` varchar(30) DEFAULT '',
3 `deptId` int(11) DEFAULT 0,
4 `salary` float DEFAULT NULL, 

4、出于性能考虑,如无特殊需要,建议不使用外键。参照完整性由代码控制。这个也是我们普遍的做法,从程序角度进行完整性控制,但是如果不注意,也会产生脏数据。

5、命名应使用小写。

用户命名规范

1、 生产使用的用户命名格式为 code_应用

2、 只读用户命名规则为 read_应用

数据库对象设计规范

存储引擎的选择

1、如无特殊需求,必须使用innodb存储引擎。

可以通过 show variables like 'default_storage_engine' 来查看当前默认引擎。主要有MyISAM 和 InnoDB,从5.5版本开始默认使用 InnoDB 引擎。

基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行速度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。

另外,MySQL 系列面试题和答案全部整理好了,微信搜索Java技术栈,在后台发送:面试,可以在线阅读。

字符集的选择

1、如无特殊要求,必须使用utf8或utf8mb4。

在国内,选择对中文和各语言支持都非常完善的utf8格式是最好的方式,MySQL在5.5之后增加utf8mb4编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。

所以utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。

可以使用如下脚本来查看数据库的编码格式

1 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
2 -- 或
3 SHOW VARIABLES Like '%char%';  

表设计规范

1、不同应用间所对应的数据库表之间的关联应尽可能减少,不允许使用外键对表之间进行关联,确保组件对应的表之间的独立性,为系统或表结构的重构提供可能性。目前业内的做法一般 由程序控制参照完整性。

2、表设计的角度不应该针对整个系统进行数据库设计,而应该根据系统架构中组件划分,针对每个组件所处理的业务进行数据库设计。

3、表必须要有PK,主键的优势是唯一标识、有效引用、高效检索,所以一般情况下尽量有主键字段。

4、一个字段只表示一个含义。

5、表不应该有重复列。

6、禁止使用复杂数据类型(数组,自定义等),Json类型的使用视情况而定。

7、需要join的字段(连接键),数据类型必须保持绝对一致,避免隐式转换。比如关联的字段都是int类型。

8、设计应至少满足第三范式,尽量减少数据冗余。一些特殊场景允许反范式化设计,但在项目评审时需要对冗余字段的设计给出解释。

9、TEXT字段作为大体量文本存储,必须放在独立的表中 , 用PK与主表关联。如无特殊需要,禁止使用TEXT、BLOB字段。

10、需要定期删除(或者转移)过期数据的表,通过分表解决,我们的做法是按照2/8法则将操作频率较低的历史数据迁移到历史表中,按照时间或者则曾Id做切割点。

11、单表字段数不要太多,建议最多不要大于50个。过度的宽表对性能也是很大的影响。

12、MySQL在处理大表时,性能就开始明显降低,所以建议单表物理大小限制在16GB,表中数据行数控制在2000W内。

业内的规则是超过2000W性能开始明显降低。但是这个值是灵活的,你可以根据实际情况进行测试来判断,比如阿里的标准就是500W,百度的确是2000W。实际上是否宽表,单行数据所占用的空间都有起到作用的。

13、如果数据量或数据增长在前期规划时就较大,那么在设计评审时就应加入分表策略,后续会有专门的文章来分析数据拆分的做法:垂直拆分(垂直分库和垂直分表)、水平拆分(分库分表和库内分表);

14、无特殊需求,严禁使用分区表

举例子:比如一个品牌表,建立的的索引如下,一个主键索引,一个唯一索引

1 PRIMARY KEY (`id`),
2 UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)

当你同事业务代码中的检索语句如下的时候,应该立即警告了,即没有覆盖索引,也没按照最左前缀原则:

1 select brand_id,brand_name from  ds_brand_system where status=?  and define_id=?  and app_id=?

建议改成如下:

1 select brand_id,brand_name from  ds_brand_system where app_id=? and define_id=?  and  status=? 

约束设计规范

1、PK应该是有序并且无意义的,由开发人员自定义,尽可能简短,并且是自增序列。

2、表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uk_”作为前缀的唯一约束索引。

3、PK字段不允许更新。

4、禁止创建外键约束,外键约束由程序控制。

5、如无特殊需要,所有字段必须添加非空约束,即not null。

6、如无特殊需要,所有字段必须有默认值。

SQL使用规范

select 检索的规范性

1、尽量避免使用select *,join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数。

一种是可能取出很多不需要的数据,对于宽表来说,这是灾难;一种是尽可能避免回表,因为取一些根本不需要的数据而回表导致性能低下,是很不合算。

2、严禁使用 select * from t_name ,而不加任何where条件,道理一样,这样会变成全表全字段扫描。

3、MySQL中的text类型字段存储:

3.1、不与其他普通字段存放在一起,因为读取效率低,也会影响其他轻量字段存取效率。

3.2、如果不需要text类型字段,又使用了select *,会让该执行消耗大量io,效率也很低下

4、在取出字段上可以使用相关函数,但应尽可能避免出现 now() , rand() , sysdate() 等不确定结果的函数,在Where条件中的过滤条件字段上严禁使用任何函数,包括数据类型转换函数。大量的计算和转换会造成效率低下,这个在索引那边也描述过了。

5、分页查询语句全部都需要带有排序条件 , 否则很容易引起乱序

6、用in()/union替换or,效率会好一些,并注意in的个数小于300

7、严禁使用%前缀进行模糊前缀查询:如:select a,b,c from t_name where a like ‘%name’; 可以使用%模糊后缀查询如:select a,b from t_name where a like ‘name%’;

8、避免使用子查询,可以把子查询优化为join操作

通常子查询在in子句中,且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因:

· 子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能 会受到一定的影响;

· 特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大;

· 由于子查询会产生大量的临时表也没有索引,所以会消耗过多的CPU和IO资源,产生大量的慢查询。

操作的规范性

1、禁止使用不含字段列表的INSERT语句

如:insert into values ('a','b','c'); 应使用 insert into t_name(c1,c2,c3) values ('a','b','c'); 。

2、大批量写操作(UPDATE、DELETE、INSERT),需要分批多次进行操作

· 大批量操作可能会造成严重的主从延迟,特别是主从模式下,大批量操作可能会造成严重的主从延迟,因为需要slave从master的binlog中读取日志来进行数据同步。

· binlog日志为row格式时会产生大量的日志

程序上的约束

后续我们团队的目标是研发评审工具对开发同学提交的建库、建表、刷数据、查询的语句进行分析,看看是否符合应有的规范。如果不符合,驳回修改。

Comments: 3
暂无评论

「人生在世,留句话给我吧」

撰写评论