关于mysql:MySQL-团队开发规范

数据库对象
数据库对象全局命名标准
数据库命名标准
表命名标准
字段命名标准
索引命名标准
视图命名标准
存储过程命名标准
函数命名标准
触发器命名标准
束缚命名标准
用户命名标准

存储引擎的抉择
字符集的抉择
表设计规范
字段设计规范
索引设计规范
束缚设计规范

select 检索的规范性
操作的规范性

数据库对象是数据库的组成部分，常见的有以下几种：表（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 提供事务反对以及内部键等高级数据库性能。

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

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

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

关于mysql:MySQL-团队开发规范

MySQL 团队开发标准

1、数据库对象命名标准

2、数据库对象设计规范

3、SQL 应用标准

4、程序上的束缚

一、数据库对象命名标准

1、数据库对象

2、数据库对象全局命名标准

3、数据库命名标准

4、表命名标准

5、字段命名标准

6、索引命名标准

7、视图命名标准

8、存储过程命名标准

9、函数命名标准

10、触发器命名标准

11、束缚命名标准

12、用户命名标准

二、数据库对象设计规范

1、存储引擎的抉择

2、字符集的抉择

3、表设计规范

4、字段设计规范

5、索引设计规范

6、束缚设计规范

三、SQL 应用标准

1、select 检索的规范性

2、操作的规范性

四、程序上的束缚