Mysql建模guideline及Review标准规范

一.数据的性质分析

对于任何新建的数据表，都必须进行数据的性质分析，并把分析结果放在自定义wiki上

二.数据的边界

数据必须有归属系统，不得建立和已有数据归属系统重叠的数据管理模块，必须直接调用VIS接口

仅以下三种情况允许数据拷贝：

   1.数据源无法提供需要的接口

   2.数据源存在性能风险或可用性级别低于本系统

   3.需要保留源数据的快照

 如果需要数据拷贝，则须保证：

   1.拷贝的数据必须有一致性策略

   2.拷贝的数据不能修改

   3.拷贝的数据不能未经集成作为数据源继续向下游发布

三. 数据库命名

   1.数据库命名规范，统一：yunbee_xxxx；表名不超过40个字符（即最大只能40个字符） 

   2.一律采用小写字母

四.表的命名

   1.表名一律依据业务数据的英文名来确定，以下划线"_"分隔各组成单词，不得嵌入中文拼音

   2.一律采用小写字母，必须有注释（必须加上COMMENT '<字段扼要解说>'）

    3.各种表命名规范：

五.字段的命名

   1.一律依据业务数据的英文名来确定，以下划线"_"分隔各组成单词，不得嵌入中文拼音

   2.一律采用小写字母，必须有注释（必须加上COMMENT '<字段扼要解说>'）

   3.各字段命名规范：

六.主键和索引的命名

   1.主键约束：默认PRIMARY；

   2.unique约束：UK_

   3.check约束：CK_

   4.外键约束：业务禁用

七.注释

   1.表的要有一个相对准确的中文名称，以"表"结尾。例如"订单表"

   2.字段的注释必须完整说明字段的业务含

   3.注释统一用中文

八.规范化设计

   1.数据必须要分解   不能使用半结构化的方式把业务数据存储在业务表的string/text中，除非以下两种情形： 
        - 本系统无需维护该数据内部各字段的关系。
        - 该数据结构变化迅速，但又因为某种约束必须用关系数据库存储  

   2.数据类型/精度（长度）必须符合业务实际，禁止滥用例如varchar等类型解决所有问题

   3.统一使用INNODB存储引擎,UTF8编码(整个数据库的编码统一为utf8_general_ci，为此不需要建立表的DDL加上特别CHARACTER SET utf8 COLLATE utf8_general_ci)

4.字段的数据类型和长度，必须符合数据的实际, 不能滥用varchar来解决一切问题：

   5.如果字段只有true or false，请使用tinyint（数值范围-128~127），如模块分类：1订单 2商品；删除标志 0正常，1删除；状态 1为可选，2为不可选等等

   6.存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性

   7.禁止default NULL，数字类型not null default 0，字符类型not null default ''，时间not null default '1970-01-01 00:00:00'或者 ‘0000-00-00 00:00:00’

   8.所有表必须有create_time和last_update_time，方便后期数据分析与记录变化排查，哪怕只是配置表，只有10行记录; 为进一步明确操作来源，统一加上created_by, last_updated_by两个字段，记录数据的创建者和修改者。

九.业务主键 

   1.业务主键在物理上以唯一索引实现，父表的代理主键（自增ID)可以作为子表业务主键的一部分

   2.对外接口统一使用业务主键作为实体数据的唯一标识

   3.主键的内容不能被修改

   4.外键约束一般不在数据库上创建，只表达一个逻辑的概念，由程序控制，禁用数据库外键

十.自增主键

   1.自增代理主键，主要用于优化存储和进行内部连接，本身不承载业务职能。及不能赋予自增主键id任何本系统之外的业务含义

   2.引用本系统其他表的数据时，只能通过自增主键id进行引用，不能通过业务主键，除非该业务主键确定永不变

   3.表必须有主键，建议统一由Auto-Increment字段生成整型，不建议使用组合主键，自增id只作为虚拟主键，不建议与业务数据处理有关联关系，如果把控不好，会有问题(案例：AUTO_INCREMENT主键字段不要与业务有关联关系)

十一.数据更新与删除

   1.除了接口表和临时表，所有业务实体表/关系表，禁止硬删除。如果业务上需要有数据删除的动作，必须软删除，加上is_deleted字段，标注这条记录的状态

   2.日志数据一律不允许修改

十二.索引

   1.在模型设计的时候就充分考虑使用场景，把能想到的索引在设计阶段就建立

   2.不能把所有的索引都放到写SQL的时候去解决，表的设计和访问模式，是数据库设计者的工作，不能把这项工作完全交给开发人员。

   3.一律采用小写字母

   4.非唯一索引建议使用“idx_表缩写名称_字段缩写名称”进行命名。

   5.唯一索引建议使用“uniq_表缩写名称_字段缩写名称”进行命名。

   6.唯一键不和主键重复。每个业务实体表和关系表都应该至少有一个业务主键对应的唯一索引。

   7.索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面，就是数据分布。

   8.使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。

   9.UPDATE、DELETE语句需要根据WHERE条件添加索引。

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

   11.合理利用覆盖索引。比如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid,email)，以获得性能提升。

十三.分库

   1.独立的业务之间不得混用数据库

   2.是否要独立instance/server，参见数据库instance的独立和复用和拆分

   3.需在设计阶段考虑如果访问量非常大，且不做scale out表拆分的话，需做sharding/读写分离，但读写分离注意主从复制有延迟的可能性； 参考 是否采用读写分离方案的讨论

十四.归档

   1.每个预估数据量会达到10G以上的表，都必须有明确的归档策略。包括归档的条件，周期，归档数据的访问方式

   2.每张表数据量建议控制在千万级别行以下，为此设计阶段需考虑数据的归档

十五.函数使用规范

   1.禁用Stored procedure (包括存储过程，函数，触发器)

   2.禁止使用 UUID()，USER()这样的MYSQL INSIDE函数对于复制来说是很危险的，会导致主备数据不一致，重要的是会严重影响mysql性能

十六.连接DB规范

   1.如果应用使用的是长连接，应用必须具有自动重连的机制。但请避免每执行一个SQL去检查一次DB可用性；

   2.如果应用使用的是长连接，应用应该具有连接的TIMEOUT检查机制，及时回收长时间没有使用的连接，TIMEOUT时间一般建议为20min。

   3.SQL语句必须采用preparedStatement技术，如果编程语言不支持preparedStatement技术，需要做好特殊字符过滤，如不要前后有空串，可以提供性能并且避免SQL注入

十七.事物处理标准

   1.一个事务，处理的行数不能超过1000 rows/s （曾经发生过的案例，超出了会导致主从复制延迟的问题：2014-12-23 SHOP域所有从库因为有批量的delete导致复制延迟）

   2.禁止一些框架或定制化的底层类等使用set autocommit=0；set autocommit=1；这样控制事务，应该由程序把控，需要时begin；操作完后及时commit；（曾经发生过的案例：一个session持有锁，事务长时间不commit的场景）

   3.要合理使用事务

十八.SQL语句标准

  1.禁止多于2表的join

   2.SELECT语句只获取需要的字段，禁止使用SELECT * FROM语句，这是有效防止新增字段对应用逻辑的影响，还能减少对性能的影响；

   3.INSERT语句必须显式的指明字段名称，不使用INSERT INTO table value()。

  4.禁止在where子句中对字段施加函数，如to_date(add_time)>xxxxx,应改为:add_time >= unix_timestamp(date_add(str_to_date('20130227','%Y%m%d'),interval - 29 day))

   5.写到应用程序里的SQL语句，禁止一切DDL操作，如对这些权限有要求，必须与DBA协商同意方可使用

   6.WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化，如ISENDED=1，字段类型是tinyint，那么不能是ISENDED=‘1’。

   7.避免在SQL语句进行数学运算或者函数运算，容易将业务逻辑和DB耦合在一起。

   8.INSERT语句使用batch提交。

   9.避免使用存储过程、触发器、函数等，容易将业务逻辑和DB耦合在一起，并且MySQL的存储过程、触发器、函数中存在一定的bug。

   10.使用合理的SQL语句减少与数据库的交互次数。

   11.不使用ORDER BY RAND()，使用其他方法替换。

   12.建议使用合理的分页方式以提高分页的效率。

   13.InnoDB表避免使用COUNT(*)操作，计数统计实时要求较强可以使用memcache或者redis，非实时统计可以使用单独统计表，定时更新。

   14.不建议使用%前缀模糊查询，例如LIKE “%weibo”。

   15.避免多余的排序。使用GROUP BY 时，默认会进行排序，当你不需要排序时，可以使用order by null,例如Select a.OwnerUserID,count(*) cnt from DP_MessageList a group by a.OwnerUserID order by null;

   16.新增排序要求：不鼓励在DB里排序，特别是只有1000行以下的，请在app server上排序，app server有上百台，而db仅仅个位数的服务器数量，排序都在db，会把db压垮的，特别是禁止上千行的排序在db这边。

十九.DDL/DML Review标准

    1.所有表的DDL，都不回退

   2.表一旦设计好，字段只允许增加，不允许减少（drop column），不允许改名称（change column）

   3.多表join的时候，写SQL的时候一定要给每个字段指定表名做前缀；如: select a.id,a.name from test1 a, test2 b where a.id=b.id

   4.尽量用单表查询，避免多表JOIN，禁止多于3表join，join的字段数据类型必须绝对一致。

   5.表结构变更须由库表OWNER所在团队发起

   6.不要使用TEXT、BLOB、char，请使用VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N，请注意同一表中，所有varchar字段的长度加起来，不能大于65535。

   7.禁止使用子查询，select col、col from table where id in (select col from table)这是禁止的

   8.加字段禁止使用after，因为你不确定全局代码里面(如其他团队使用你的表)是否都insert into table(col，col，col。。。) value，如果你在中间插一个字段，就导致数据偏移的问题了，影响可大可小，同样select * 的也可能会影响数值的偏移，所以才要求，禁止after，必须带default(第18点要求)

   9.线上MySQL表名是忽略大小写的(lower_case_table_names=1)

   10.查询字段里的值是忽略大小写的(mysql存储是区分大小写，但查询是忽略大小写，如果业务查询要区分大小写，如short url 对应的long url，则select xxx from short_map where binary url = ‘’)

   11.表大小控制在3千万行以内，控制行数：控制DDL变更时 间、缩短整库备份时间，降低恢复难度。从性能角度看，走索引，每次查询数据精准到就几百行，22亿和1亿性能是没有区别的，如果是比较特殊场景，如where后面不带时间范围条件，而你明知道n个月前的数据不需要，全表查某个类型（type）的查询就有性能问题了。Online DDL的速度：总行数/3000行每秒/3600秒= n 小时，注意：一个库要变更10张表，只能是串行，不能并行，假设每一张表都要1小时的话，那么10张表变更完，就是10个小时

往期回顾

 云贝学院11-12月热销课程安排

 恭喜 云贝学院 成为中国PostgreSQL培训认证合作伙伴

 [公开课]TDSQL技术分享课

 Update的内部原理

 DBA工作指南