• 数据库对象命名标准
  • 数据库对象
  • 数据库对象全局命名标准
  • 数据库命名标准
  • 表命名标准
  • 字段命名标准
  • 索引命名标准
  • 视图命名标准
  • 存储过程命名标准
  • 函数命名标准
  • 触发器命名标准
  • 束缚命名标准
  • 用户命名标准
  • 数据库对象设计规范
  • 存储引擎的抉择
  • 字符集的抉择
  • 表设计规范
  • 字段设计规范
  • 索引设计规范
  • 束缚设计规范
  • SQL应用标准
  • 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)。

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也就够了。

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

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

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

程序上的束缚

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