关于数据库:公司用的-MySQL-团队开发规范太详细了建议收藏

数据库对象

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

数据库对象全局命名标准

1、命名应用具备意义的英文词汇，词汇两头以下划线分隔
2、命名只能应用英文字母、数字、下划线，以英文字母结尾
3、防止用 MySQL 的保留字如：backup、call、group 等
4、所有数据库对象应用小写字母，实际上 MySQL 中是能够设置大小写是否敏感的，为了保障统一性，咱们这边标准全副小写示意。

数据库命名标准

1、数据库命名尽量不超过 30 个字符。
2、数据库命名个别为项目名称 + 代表库含意的简写，比方 IM 我的项目的工作流数据库，能够是 im_flow。
3、数据库创立时必须增加默认字符集和校对规定子句。默认字符集为 UTF8（已迁徙 dumbo 的应用 utf8mb4）
4、命名应应用小写。

表命名标准

1、惯例表表名以 t_结尾，t 代表 table 的意思，命名规定即 t + 模块（蕴含模块含意的简写）+ 表（蕴含表含意的简写），比方用户模块的教育信息表：t_user_eduinfo。
2、长期表（RD、QA 或 DBA 同学用于数据长期解决的表），命名规定：temp 前缀 + 模块 + 表 + 日期后缀：temp_user_eduinfo_20210719
3、备份表（用于保留和归档历史数据或者作为灾备复原的数据）命名规定，bak 前缀 + 模块 + 表 + 日期后缀：bak_user_eduinfo_20210719
4、同一个模块的表尽可能应用雷同的前缀，表名称尽可能表白含意
5、多个单词以下划线 _ 分隔
6、惯例表表名尽量不超过 30 个字符，temp 表和 bak 表视状况而定，也尽量简短为宜，命名应应用小写

字段命名标准

1、字段命名须要示意其理论含意的英文单词或简写，单词之间用下划线 _ 进行连贯，如 service_ip、service_port。
2、各表之间雷同意义的字段必须同名，比方 a 表和 b 表都有创立工夫，应该对立为 create_time，不统一会很凌乱。
3、多个单词以下划线 _ 分隔
4、字段名尽量不超过 30 个字符，命名应该应用小写

索引命名标准

1、惟一索引应用 uni + 字段名来命名：create unique index uni_uid on t_user_basic(uid)。
2、非惟一索引应用 idx + 字段名来命名：create index idx_uname_mobile on t_user_basic(uname,mobile)。
3、多个单词以下划线 _ 分隔。
4、索引名尽量不超过 50 个字符，命名应该应用小写，组合索引的字段不宜太多，不然也不利于查问效率的晋升。
5、多单词组成的列名，取尽可能代表意义的缩写，如 test_contact 表 member_id 和 friend_id 上的组合索引：idx_mid_fid。
6、了解组合索引最左前缀准则，防止反复建设索引，如果建设了(a,b,c)，相当于建设了(a), (a,b), (a,b,c)。

视图命名标准

1、视图名以 v 结尾，示意 view，残缺构造是 v + 视图内容含意缩写。
2、如果视图只起源单个表，则为 v + 表名。如果视图由几个表关联产生就用 v + 下划线（_）连贯几个表名，视图名尽量不超过 30 个字符。如超过 30 个字符则取简写。
3、如无非凡须要，严禁开发人员创立视图。
4、命名应应用小写。

存储过程命名标准

1、存储过程名以 sp 结尾，示意存储过程（storage procedure）。之后多个单词以下划线（_）进行连贯。存储过程命名中应体现其性能。存储过程名尽量不能超过 30 个字符。
2、存储过程中的输出参数以 i_结尾，输入参数以 o_结尾。

3、命名应应用小写。

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

函数命名标准

1、函数名以 func 开始，示意 function。之后多个单词以下划线（_）进行连贯，函数命名中应体现其性能。函数名尽量不超过 30 个字符。
2、命名应应用小写。
```
create function func_format_date(ctime datetime)
```
触发器命名标准
1、触发器以 trig 结尾，示意 trigger 触发器。
2、根本局部，形容触发器所加的表，触发器名尽量不超过 30 个字符。
3、后缀（_i,_u,_d）, 示意触发条件的触发形式（insert,update 或 delete）。

4、命名应应用小写。

DROP TRIGGER IF EXISTS trig_attach_log_d;
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)。
```
`id` int(11) NOT NULL,
`name` varchar(30) DEFAULT '',
`deptId` int(11) DEFAULT 0,
`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 系列面试题和答案全副整顿好了，微信搜寻民工哥技术之路，在后盾发送：MySQL 能够在线浏览。

字符集的抉择

1、如无特殊要求，必须应用 utf8 或 utf8mb4。

在国内，抉择对中文和各语言反对都十分欠缺的 utf8 格局是最好的形式，MySQL 在 5.5 之后减少 utf8mb4 编码，mb4 就是 most bytes 4 的意思，专门用来兼容四字节的 unicode。

所以 utf8mb4 是 utf8 的超集，除了将编码改为 utf8mb4 外不须要做其余转换。当然，为了节俭空间，个别状况下应用 utf8 也就够了。举荐看下：MySQL 数据库开发的 36 条军规。

能够应用如下脚本来查看数据库的编码格局

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
-- 或
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、INT：如无非凡须要，寄存整型数字应用 UNSIGNED INT 型，整型字段后的数字代表显示长度。比方 id int(11) NOT NULL
2、DATETIME：所有须要准确到工夫 (时分秒) 的字段均应用 DATETIME, 不要应用 TIMESTAMP 类型。

对于 TIMESTAMP，它把写入的工夫从以后时区转化为 UTC（世界规范工夫）进行存储。查问时，将其又转化为客户端以后时区进行返回。而对于 DATETIME，不做任何扭转，基本上是原样输出和输入。

另外 DATETIME 存储的范畴也比拟大：

timestamp 所能存储的工夫范畴为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。datetime 所能存储的工夫范畴为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

然而非凡状况，对于跨时区的业务，TIMESTAMP 更为适合。

3、VARCHAR：所有动静长度字符串全副应用 VARCHAR 类型, 相似于状态等无限类别的字段, 也应用能够比拟显著示意出实际意义的字符串, 而不应该应用 INT 之类的数字来代替；VARCHAR(N)，

N 示意的是字符数而不是字节数。比方 VARCHAR(255)，能够最大可存储 255 个字符（字符包含英文字母，汉字，特殊字符等）。但 N 应尽可能小，因为 MySQL 一个表中所有的 VARCHAR 字段最大长度是 65535 个字节，且存储字符个数由所选字符集决定。

如 UTF8 存储一个字符最大要 3 个字节，那么 varchar 在寄存占用 3 个字节长度的字符时不应超过 21845 个字符。同时，在进行排序和创立长期表一类的内存操作时，会应用 N 的长度申请内存。(如无非凡须要，原则上单个 varchar 型字段不容许超过 255 个字符)

4、TEXT：仅仅当字符数量可能超过 20000 个的时候, 才能够应用 TEXT 类型来寄存字符类数据, 因为所有 MySQL 数据库都会应用 UTF8 字符集。

所有应用 TEXT 类型的字段必须和原表进行分拆，与原表主键独自组成另外一个表进行寄存，与大文本字段的隔离，目标是。如无非凡须要，不应用 MEDIUMTEXT、TEXT、LONGTEXT 类型

5、对于准确浮点型数据存储，须要应用 DECIMAL，严禁应用 FLOAT 和 DOUBLE。
6、如无非凡须要，尽量不应用 BLOB 类型
7、如无非凡须要，字段倡议应用 NOT NULL 属性，可用默认值代替 NULL
8、自增字段类型必须是整型且必须为 UNSIGNED，举荐类型为 INT 或 BIGINT，并且自增字段必须是主键或者主键的一部分。

索引设计规范

1、索引区分度

索引必须创立在索引选择性（区分度）较高的列上，选择性的计算形式为: selecttivity = count(distinct c_name)/count(*) ; 如果区分度后果小于 0.2，则不倡议在此列上创立索引，否则大概率会拖慢 SQL 执行

2、遵循最左前缀

对于确定须要组成组合索引的多个字段，设计时倡议将选择性高的字段靠前放。应用时，组合索引的首字段，必须在 where 条件中，且须要依照最左前缀规定去匹配。

3、禁止应用外键，能够在程序级别来束缚完整性
4、Text 类型字段如果须要创立索引，必须应用前缀索引
5、单张表的索引数量实践上应管制在 5 个以内。常常有大批量插入、更新操作表，应尽量少建索引，索引建设的准则实践上是多读少写的场景。
6、ORDER BY，GROUP BY，DISTINCT 的字段须要增加在索引的前面，造成笼罩索引
7、正确理解和计算索引字段的区分度，文中有计算规定，区分度高的索引，能够疾速得定位数据，区分度太低，无奈无效的利用索引，可能须要扫描大量数据页，和不应用索引没什么差异。
8、正确理解和计算前缀索引的字段长度，文中有判断规定，适合的长度要保障高的区分度和最失当的索引存储容量，只有达到最佳状态，才是保障高效率的索引。
9、联结索引留神最左匹配准则：必须依照从左到右的程序匹配，MySQL 会始终向右匹配索引直到遇到范畴查问 (>、<、between、like) 而后进行匹配。
```
如：depno=1 and empname>'' and job=1 如果建设 (depno,empname,job) 程序的索引，job 是用不到索引的。
```
10、应需而取策略，查问记录的时候，不要一上来就应用 *，只取须要的数据，可能的话尽量只利用索引笼罩，能够缩小回表操作，晋升效率。
11、正确判断是否应用联结索引（下面联结索引的应用那一大节有阐明判断规定），也能够进一步剖析到索引下推（IPC），缩小回表操作，晋升效率。
12、防止索引生效的准则：禁止对索引字段应用函数、运算符操作，会使索引生效。这是实际上就是须要保障索引所对应字段的”洁净度“。
13、防止非必要的类型转换，字符串字段应用数值进行比拟的时候会导致索引有效。
14、含糊查问 ’%value%’ 会使索引有效，变为全表扫描，因为无奈判断扫描的区间，然而 ’value%’ 是能够无效利用索引。
15、索引笼罩排序字段，这样能够缩小排序步骤，晋升查问效率
16、尽量的扩大索引，非必要不新建索引。比方表中曾经有 a 的索引，当初要加 (a,b) 的索引，那么只须要批改原来的索引即可。

举例子：比方一个品牌表，建设的的索引如下，一个主键索引，一个惟一索引

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

当你共事业务代码中的检索语句如下的时候，应该立刻正告了，即没有笼罩索引，也没依照最左前缀准则：

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

倡议改成如下：

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、如无非凡须要，所有字段必须有默认值。

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 格局时会产生大量的日志

后续咱们团队的指标是研发评审工具对开发同学提交的建库、建表、刷数据、查问的语句进行剖析，看看是否合乎应有的标准。如果不合乎，驳回批改。

作者：翁智华
出处：cnblogs.com/wzh2010/