数据库规范

此规范是结合DBA 李海春MySQL基础规范、美团、阿里等大厂公开规范以及产品中心实践过程中经验累积综合整理出来的文档，是产品中心全体成员的成果结晶。

修订记录

版本号	制定团队	更新日期	发布对象	备注
0.0.1	产品中心架构组	2019.12.13	评审小组	初始提交
0.0.1-beta.1	产品中心架构组	2019.12.18	产品中心	添加线上环境SQL审核规范
0.0.1-beta.2	产品中心	2020/2/8	产品中心	评审后调整

命名规范

1. 【强制】库名、表名、字段名必须使用小写字母，并采用下划线分割
2. 【强制】库名、表名、字段名禁止超过32个字符，须见名知意，使用名词不是动词
3. 【强制】库名、表名、字段名禁止使用MySQL保留字
4. 【强制】有关联的表名、字段名需统一名称前缀
5. 正例：导出日志表（t_export_log）、导出日志详情（流水）表（t_export_log_detail）
6. 【强制】临时库、表名必须以tmp为前缀，并以日期为后缀

基础规范

1. 【强制】使用INNODB存储引擎
2. 【强制】表字符集使用UTF8
3. 【强制】所有表名、字段名都需要添加注释，枚举值类型的字段需要阐明枚举值含义
4. 【强制】禁止在数据库中使用VARBINARY、BLOB存储图片、文件等
5. 【强制】禁止使用DECIMAL、FLOAT、DOUBLE等类型存储浮点数，业务上有小数的采用整型方式存储
6. 【强制】整型定义中不添加长度，比如使用INT，而不是INT(4)
7. 【强制】时间、日期使用 BIGINT 类型存储毫秒时间戳
8. 【强制】禁止使用使用ENUM、SET类型，枚举值字段使用TINYINT类型存储，建议设置默认值
9. 【建议】VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N
10. 【建议】VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存
11. 【建议】尽可能不使用TEXT、BLOB类型，如需使用向开发组长提出申请

索引规范

1. 【强制】表必须有主键

2. 【强制】索引必须按照“idx_表名_字段名称”进行命名

3. 【强制】数据库设计不允许使用外键

4. 【强制】更新频繁的列不使用索引

5. 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生

1. 【强制】索引列不能参与计算（数学运算和函数运算）

如：from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。此语句应该写成create_time = unix_timestamp(’2014-05-29’)。

1. 【强制】索引中的字段数建议不超过5个

2. 【强制】WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化，导致索引失效

f_business_id定义为字符型 案例说明中where条件未加 ‘xxxx’ 引号，会导致索引失效

• mysql # `f_business_id` varchar(40) DEFAULT NULL COMMENT '业务id ' select f_business_id FROM human.t_task WHERE f_business_id = 433423882127424;

• 【建议】单张表的索引数量控制在5个以内

• 【建议】对字符串使用前缀索引，前缀索引长度不超过8个字符

前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

# 建立前缀索引的语法：
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

1. 【建议】合理创建联合索引（避免冗余），(a,b,c)相当于(a)、(a,b)、(a,b,c)

2. 【建议】合理利用覆盖索引来进行查询操作，避免回表

   说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。

   正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用explain的结果，extra列会出现：using index。

3. 【建议】索引建在区分度高的字段上面，不在低基数列上建立索引，例如性别

   区分度计算：区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，参考文章

4. 【建议】区分度最大的字段放在前面

   说明：存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>? and d=?那么即使c的区分度更高，也必须把d放在索引的最前列，即索引idx_d_c。

   正例：如果where a=? and b=?，如果a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。

5. 【建议】重要的SQL必须被索引、核心SQL优先考虑覆盖索引

6. 【建议】不使用%前导的查询，如like “%ab”（强制）

   说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引

7. 【建议】不使用负向查询，如not in/like

   说明：使用负向查询是指使用负向运算符，如：NOT、!=、<>、NOT EXISTS、NOT IN以及NOT LIKE等等。因为通过索引有顺序的结构，可以有效的利用二分查找法，快速找到对等的数据，但若使用负向查询，则无法利用索引结构做二分查找，只好全表扫描。

8. 【参考】最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9. 【参考】创建索引时避免有如下极端误解：

10. 宁滥勿缺。认为一个查询就需要建一个索引

11. 宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度

12. 抵制唯一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决

SQL规范

1. 【强制】SQL语句中IN包含的值不超过500

2. 【强制】SELECT语句只获取需要的字段，不允许使用SELECT *

3. 【强制】INSERT语句必须显式的指明字段名称，不允许使用INSERT INTO table()

4. 【强制】禁止单条SQL语句同时更新多个表

5. 【强制】拒绝使用复杂的SQL，将大的SQL拆分成多条简单SQL分步执行

   • 简单的SQL容易使用到MySQL的query cache；

   • 减少锁表时间特别是MyISAM；

   • 可以使用多核cpu。

6. 【推荐】利用延迟关联或者子查询优化超多分页场景。

   说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的id段，然后再关联： SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

行为规范

1. 【强制】开发人员禁止拥有任何环境DLL权限账号，预发布与线上环境数据库通过VPN才允许访问，详见环境管理规范
2. 【强制】版本发布需在指定目录提交线上SQL变更需求，必须详细注明提交人、日期、需求说明
3. 【强制】存在更新、删除线上数据的SQL变更需要单独审核
4. 由开发组长统一提交审核，发送邮件给唐杰、魏传博，抄送给欧哥、志哥，审核通过之后由审核人转发给朱俊执行，审核模板：SQL变更审核模板
5. 所有更新、删除线上数据必须有相应的查询验证步骤
6. 所有更新、删除线上数据必须有数据备份和相应的回滚步骤
7. 大数据量的数据操作存在锁表等风险的需要提出风险预警，避开业务高峰期执行
8. 对业务存在影响的数据操作需要提出业务风险预警，提交审核前需与对应产品经理确认，审核邮件抄送给对应产品经理，SQL执行之后产品经理进行验证（参与角色：开发人员、开发组长、产品经理）
9. 【强制】禁止直接在线上库执行后台管理和统计类查询
10. 【强制】禁止(代码配置文件)有super权限的应用程序账号存在
11. 【强制】不在MySQL数据库中存放业务逻辑
12. 【建议】不在业务高峰期查询数据库

Mysql 数据类型

数字类型

TINYINT                           1 字节 
SMALLINT                          2 个字节 
MEDIUMINT                         3 个字节 
INT                               4 个字节 
INTEGER                           4 个字节 
BIGINT                            8 个字节 
FLOAT(X)                          4 如果 X < = 24 或 8 如果 25 < = X < = 53 
FLOAT                             4 个字节 
DOUBLE                            8 个字节 
DOUBLE PRECISION                  8 个字节 
REAL                              8 个字节 
DECIMAL(M,D)                      M字节(D+2 , 如果M < D) 
NUMERIC(M,D)                      M字节(D+2 , 如果M < D)

日期和时间类型

DATE                                    3 个字节 
DATETIME                                8 个字节 
TIMESTAMP                               4 个字节 
TIME                                    3 个字节 
YEAR                                    1 字节

字符串类型

CHAR(M)                                  M字节，1 <= M <= 255 
VARCHAR(M)                               L+1 字节, 在此L <= M和1 <= M <= 255 
TINYBLOB, TINYTEXT                       L+1 字节, 在此L< 2 ^ 8 
BLOB, TEXT                               L+2 字节, 在此L< 2 ^ 16 
MEDIUMBLOB, MEDIUMTEXT                   L+3 字节, 在此L< 2 ^ 24 
LONGBLOB, LONGTEXT                       L+4 字节, 在此L< 2 ^ 32 
ENUM('value1','value2',...)              1 或 2 个字节, 取决于枚举值的数目(最大值65535） 
SET('value1','value2',...)               1，2，3，4或8个字节, 取决于集合成员的数量(最多64个成员）

FAQ

1. INT[M]，M值代表什么含义？
2. 注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示，python、java客户端等不具备这个功能
3. 什么是覆盖索引？
4. InnoDB存储引擎中，secondary index（非主键索引）中没有直接存储行地址，存储主键值。如果用户需要查询secondary index中所不包含的数据列时，需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。
5. 覆盖索引的概念就是查询可以通过在一个索引中完成，覆盖索引效率会比较高，主键查询是天然的覆盖索引。
6. 合理的创建索引以及合理的使用查询语句，当使用到覆盖索引时可以获得性能提升。
7. 比如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid,email)，以获得性能提升