前言

制定规范的直接目的是约束设计行为，最终目的是确保设计的合理统一。
约定优先于配置（Convention Over Configuration）。
数据库命名规范、数据库基本设计规范、数据库索引设计规范、数据库字段设计规范、数据库SQL开发规范、数据库操作行为规范

数据库规范

库名规范

命名有意义，只能使用英文字母、数字、下划线，使用小写字母，长度32个字符以内
避免用MySQL的保留字如：call、group等

编码

采用 UTF8 编码。只能是utf8或者utf8mb4

分库

名称：库通配名+编号（从0开始递增），比如：wenda_001
把一个数据库分成多个，建议做个读写分离，做分库会加大开发成本，不推荐使用。
采用分库策略的，库的数量不能超过1024

数据表

表名

命名有意义，只能使用英文字母、数字、下划线，使用小写字母，表名不使用复数名词，长度32个字符以内
避免用MySQL的保留字如：call、group等
同一模块使用的表名尽量使用统一前缀。 模块前缀+表名，如师资系统采用”sz”作为前缀，渠道系统采用”qd”作为前缀等。
相关模块的表名与表名之间尽量体现join的关系，如user表和user_login表。
临时表(运营、开发或数据库人员临时用作临时进行数据采集用的中间表)命名规则：加上tmp前缀和8位时间后缀（tmp_test_user_20181109） 临时库表必须以tmp为前缀并以日期为后缀
备份表（DBA备份用作保存历史数据的中间表）命名规则：加上bak前缀和8位时间后缀（bak_test_user_20181109） 备份库、备份表必须以bak为前缀并以日期为前缀

主键

id必须是主键，每个表必须有主键，且保持增长趋势的，小型系统可以依赖于 MySQL 的自增主键，大型系统或者需要分库分表时才使用内置的 ID 生成器
id类型没有特殊要求，必须使用bigint unsigned，禁止使用int，即使现在的数据量很小。id如果是数字类型的话，必须是8个字节。主键尽量用自增字段类型，推荐类型为 INT 或者 BIGINT 类型。
如果一定要使用 UUID 作为主键，让应用程序来产生。（UUID()，USER() 这样的 MySQL INSIDE 函数对于复制来说是很危险的，会导致主备数据不一致，所以请不要使用。）
①强制要求主键为id，类型为int或bigint，且为auto_increment。②标识表里每一行主体的字段不要设为主键，建议设为其他字段如user_id，order_id等，并建立unique key索引(可参考cdb.teacher表设计)。因为如果设为主键且主键值为随机插入，则会导致innodb内部page分裂和大量随机I/O，性能下降。

外键

禁止使用外键

必须字段

核心表(如用户表，金钱相关的表)必须有行数据的创建时间字段create_time和最后更新时间字段update_time，便于查问题。

大型字段

建议对表里的blob、text等大字段，垂直拆分到其他表里，仅在需要读这些对象的时候才去select。
同一表中，所有 varchar 字段的长度加起来，不能大于65535. 如果有这样的需求，请使用 TEXT/LONGTEXT 类型。

冗余字段

适当的时候，可以有一定的字段容余
经常需要join查询的字段，在其他表里冗余一份。如user_name属性在user_account，user_login_log等表里冗余一份，减少join查询。
不是频繁修改的字段。
不是 varchar 超长字段，更不能是 text 字段。

字段数量

50
表不应该有重复字段
禁止在表中建立预留字段
单表物理大小限制在16GB，表中数据控制在2000W内
尽量做到冷热数据分离，减小表的宽度 mysql限制最多存储4096列，为了是减少磁盘IO，保证热数据的内存缓存命中率，利用更有效的利用缓存，避免读入无用的冷数据经常使用的列放在一个表中
尽量控制单表数据量的大小，建议控制在500万以内，500万并不是mysql数据库的限制
当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表 (主要是为了性能考虑)。

字段类型

禁止在数据库中存储图片、文件等二进制数据
禁止使用复杂数据类型(数组、自定义等)

编码

显式指定字符集为utf8或utf8mb4。

注释

所有表和字段都需要添加注释，使用comment从句添加表和列的备注
每个字段和表必须提供清晰的注释

引擎

如无特殊需求，一律为InnoDB。

结构

Mysql 的表尽量设置成 KV (Key-Value) 结构，这样便于扩展和维护。

设计规则

不同应用间所对应的数据库表之间的关联应尽可能减少，不允许使用外键对表之间进行关联，确保组件对应的表之间的独立性，为系统或表结构的重构提供可能性
表设计的角度不应该针对整个系统进行数据库设计，而应该根据系统架构中组件划分，针对每个组件所处理的业务进行数据库设计
Stored procedure (包括存储过程，函数，触发器) 对于 MYSQL 来说还不是很成熟，没有完善的出错记录处理，不建议使用。

定期操作

需要定期删除(或者转移)过期数据的表，通过分表解决

分表

无特殊需求，严禁使用分区表
如果数据量或数据增长在前期规划时就较大，那么在设计评审时就应加入分表策略
分区表在物理上表现为多个文件没在逻辑上表现为一个表谨慎选择分区键，跨分区查询效率可能更低建议采用物理分表的方式管理大数据
尽量控制单表数据量的大小，建议控制在500万以内，500万并不是mysql数据库的限制
单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。说明:如果预计2年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表 (主要是为了性能考虑)。
分表就是把一张大表，按照如上过程都优化了，还是查询卡死，那就把这个表分成多张表，把一次查询分成多次查询，然后把结果组合返回给用户。
分区表的分区字段(partition-key)必须有索引，或者是组合索引的首列。
单个分区表中的分区(包括子分区)个数不能超过1024。
单个分区文件不超过2G，总大小不超过50G。建议总分区数不超过20个。
对于分区表执行alter table操作，必须在业务低峰期执行。
采用分表策略的，表的数量不能超过4096
单个分表不超过500W行，ibd文件大小不超过2G，这样才能让数据分布式变得性能更佳。
水平分表尽量用取模方式，日志、报表类数据建议采用日期进行分表。

数据表字段

名称

命名有意义，只能使用英文字母、数字、下划线，使用小写字母，长度32个字符以内
避免用MySQL的保留字如：call、group等
各表之间相同意义的字段必须同名，类型也必须相同
布尔值类型的字段命名为 is + 描述。如 member 表上表示是否为 enabled 的会员的字段命名为 IsEnabled。

默认值

表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。
字段尽量设置为 NOT NULL，为字段提供默认值。如字符型的默认值为一个空字符值串’’;数值型默认值为数值 0;逻辑型的默认值为数值 0;
表字段避免 null 值出现，null 值很难查询优化且占用额外的索引空间，推荐默认数字 0 代替 null。

类型要求

需要多表 join 的字段，数据类型保持绝对一致。
字段的类型为枚举型或布尔型时，建议使用 char(1) 类型。
表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint ( 1表示是，0表示否)。表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。
业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间。
不推荐使用enum，set。因为它们浪费空间，且枚举值写死了，变更不方便。推荐使用tinyint或smallint。
任何字段如果为非负数，必须是 unsigned
TEXT字段必须放在独立的表中，用PK与主表关联。如无特殊需要，禁止使用TEXT、BLOB字段
表示boolean类型的都使用TINYINT(1)，因为mysql本身是没有boolean类型的，在自动生成代码的时候，DO对象的字段就是boolean类型，例如 is_delete;其余所有时候都使用TINYINT(4)
优先选择符合存储需要的最小的数据类型将字符串转化为数字类型存储

VARCHAR字段

同一表中，所有 varchar 字段的长度加起来，不能大于65535. 如果有这样的需求，请使用 TEXT/LONGTEXT 类型。
字符串VARCHAR(N)，其中 N表示字符个数，请尽量减少 N 的大小，参考：code VARCHAR(32)；name VARCHAR(32)；memo VARCHAR(512)；
文本数据尽量用varchar存储。因为varchar是变长存储，比char更省空间。MySQL server层规定一行所有文本最多存65535字节，因此在utf8字符集下最多存21844个字符，超过会自动转换为mediumtext字段。而text utf8字符集下最多存21844个字符，mediumtext最多存2^24/3个字符，longtext最多存2^32个字符。一般建议用varchar类型，字符数不要超过2700。
所有动态长度字符串全部使用VARCHAR类型，类似于状态等有限类别的字段，也使用可以比较明显表示出实际意义的字符串，而不应该使用INT之类的数字来代替；VARCHAR(N)，N表示的是字符数而不是字节数。比如VARCHAR(255)，可以最大可存储255个字符（字符包括英文字母，汉字，特殊字符等）。但N应尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，且存储字符个数由所选字符集决定。如UTF8存储一个字符最大要3个节，那么varchar在存放占用3个字节长度的字符时不应超过21845个字符。同时，在进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。(如无特殊需要，原则上单个varchar型字段不允许超过255个字符)
如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

TEXT 字段

仅仅当字符数量可能超过20000个的时候，才可以使用TEXT类型来存放字符类数据，因为所有MySQL数据库都会使用UTF8字符集。所有使用TEXT类型的字段必须和原表进行分拆，与原表主键单独组成另外一个表进行存放。如无特殊需要，严禁开发人员使用MEDIUMTEXT、TEXT、LONGTEXT类型

时间字段

时间统一格式:YYYY-MM-DD HH:MM:SS
尽量使用 TIMESTAMP 而非 DATETIME
时间字段使用时间日期类型，不要使用字符串类型存储，日期使用DATE类型，年使用YEAR类型，日期时间使用DATETIME
所有需要精确到时间(时分秒)的字段均使用DATETIME，不要使用TIMESTAMP类型
时间类型尽量选取timestamp。因为datetime占用8字节，timestamp仅占用4字节，但是范围为1970-01-01 00:00:01到2038-01-01 00:00:00。更为高阶的方法，选用int来存储时间，使用SQL函数unix_timestamp()和from_unixtime()来进行转换。

IP字段

业务中IP地址字段推荐使用int类型，不推荐用char(15)。因为int只占4字节，可以用如下函数相互转换，而char(15)占用至少15字节。一旦表数据行数到了1亿，那么要多用1.1G存储空间。 SQL：select inet_aton(‘192.168.2.12’); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);

INT字段

尽量使用 INT 而非 BIGINT，如果非负则加上 UNSIGNED（这样数值容量会扩大一倍），当然能使用 TINYINT、SMALLINT、MEDIUM_INT 更好。
如无特殊需要，存放整型数字使用UNSIGNED INT型。整型字段后的数字代表显示长度
合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

类型	字节	表示范围
tinyint	1	无符号值: `0～255`; 有符号值： `-128~127`
smallint	2	无符号值: `0～65536`; 有符号值： `-32768~32767`
mediumint	3	无符号值: `0～16777215`; 有符号值： `-8388608~8388607`
int	4	无符号值: `0~4294967295`; 有符号值： `-2147483648~2147483647`
bigint	8	无符号值: `0~((2³²×²)-1)`; 有符号值：`-(2³²×²)/2 ~ (2³²×²)/2-1`

自增字段

表中的自增列(auto_increment属性)，推荐使用bigint类型。因为无符号int存储范围为-2147483648~2147483647(大约21亿左右)，溢出后会导致报错。

金钱字段

存储金钱的字段，建议用int，程序端乘以100和除以100进行存取。因为int占用4字节，而double占用8字节，空间浪费。

带小数字段

对于精确浮点型数据存储，需要使用DECIMAL，严禁使用FLOAT和DOUBLE
小数类型为 decimal，禁止使用 float 和 double。说明:float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。

索引

命名

命名有意义，只能使用英文字母、数字、下划线，使用小写字母，长度32个字符以内
_ind，各部分以下划线 () 分割。
如: sample 表 member_id 上的索引:sample_mid_ind。

基本原则

索引占磁盘空间，不要重复的索引，尽量短
只给常用的查询条件加索引
过滤性高的列建索引，取值范围固定的列不建索引
唯一的记录添加唯一索引
频繁更新的列不要建索引
不要对索引列运算
同样过滤效果下，保持索引长度最小
合理利用组合索引，注意索引字段先后顺序
多列组合索引，过滤性高的字段最前
order by 字段建立索引，避免 filesort
组合索引，不同的排序顺序不能使用索引
<>!=无法使用索引

索引其他规范

索引必须创建在索引选择性选择性较高的列上，选择性的计算方式为: select count(distinct(col_name))/count(*) from tb_name; 如果结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行
组合索引的首字段，必须在where条件中，对于确定需要组成组合索引的多个字段，建议将选择性高的字段靠前放
Text类型字段如果需要创建索引，必须使用前缀索引
单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引
ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引
尽量使用Btree索引，不要使用其它类型索引
业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致;多表关联查询时，保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。
在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。说明:索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名，索引长度))/count(*)的区分度来确定。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。正例:where a=? and b=? order by c; 索引:a_b_c 反例:索引中有范围查找，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
利用覆盖索引来进行查询操作，避免回表。说明:如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗?目录浏览一下就好，这个目录就是起到覆盖索引的作用。正例:能够建立索引的种类:主键索引、唯一索引、普通索引，而覆盖索引是一种查询的效果，用explain的结果，extra列会出现:using index。
利用延迟关联或者子查询优化超多分页场景。说明: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
SQL 性能优化的目标:至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。说明:
- consts 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。
- ref 指的是使用普通的索引(normal index)。
- range 对索引进行范围检索。反例:explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。
建组合索引的时候，区分度最高的在最左边。正例:如果 where a=? and b=? ，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。说明:存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如:where a>? and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。防止因字段类型不同造成的隐式转换，导致索引失效。
创建索引时避免有如下极端误解
- 认为一个查询就需要建一个索引。
- 认为索引会消耗空间、严重拖慢更新和新增速度。
- 认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。
单个索引中每个索引记录的长度不能超过64KB。
单个表上的索引个数不能超过7个。
在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。如列userid的区分度可由select count(distinct userid)计算出来。
在多表join的SQL里，保证被驱动表的连接列上有索引，这样join执行效率最高。
建表或加索引时，保证表里互相不存在冗余索引。对于MySQL来说，如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。
限制每张表上的索引数量，建议单张表索引不超过5个索引并不是越多越好的，索引可以提高效率同样可以降低效率索引可以增加查询效率，但同样也会降低插入和更新的效率
禁止给表中的每一列都建立单独的索引
Innodb是按照主键索引的顺序来组织表的。每个Innodb表必须有一个主键不使用更新频繁的列作为主键，不使用多列主键不使用uuid、MD5、hash，字符串列作为主键主键建议使用主键自增
常见的索引列建议： select、update、delete语句的where从句中的列包含在order by、group by、destinct中的字段多表join的关联列
如何选择索引列的顺序：联合索引是从做到右的顺序来实现的
- 区分度最高的列放在联合索引的最左侧
- 尽量把字段长度小的列放在联合索引的最左侧
- 使用最频繁的列放到联合索引的左侧
- 避免建立冗余索引和重复索引重复索引：如 primary key(id)、index(id)、unique index(id) 冗余索引：如 index(a,b,c)、index(a,b)、index(a)
- 对于频繁的查询优先使用覆盖索引覆盖索引：就是包括了所有查询字段的索引优点：避免Innodb表进行索引的二次查找，可以把随机IO变为顺序IO加快查询效率
- 尽量避免使用外键不建议使用外键约束，但一定在表与表之间的关联键上建立索引外键可用于保证数据的参照完整性，但建议在业务端实现外键会影响父表和子表的写操作从而降低性能

约束

不要使用外键约束，如果数据存在外键关系，请在程序层面实现。
主键最好是无意义的，由 Sequence 产生的 ID 字段，类型为 number，不建议使用组合主键。
若要达到唯一性限制的效果，不要创建 uniqueindex，必须显式创建普通索引和约束 (pk 或 uk)，即先创建一个以约束名命名的普通索引，然后创建一个约束，用 using index … 指定索引。
当删除约束的时候，为了确保不影响到 index，最好加上 keep index 参数。
主键的内容不能被修改。
外键约束一般不在数据库上创建，只表达一个逻辑的概念，由程序控制。
当万不得已必须使用外健的话，必须在外健列创建 INDEX。
主键的内容不能被修改。
PK应该是有序并且无意义的，尽量由开发人员自定义，且尽可能短，使用自增序列。
表中除PK以外，还存在唯一性约束的，可以在数据库中创建以“uidx_”作为前缀的唯一约束索引。
PK字段不允许更新。
禁止创建外键约束，外键约束由应用控制。
如无特殊需要，所有字段必须添加非空约束，即not null。
如无特殊需要，所有字段必须有默认值。
主键约束: pk 结尾，pk;
unique 约束:uk 结尾，uk;
check 约束: ck 结尾，ck;
外键约束: _fk 结尾，以 pri 连接本表与主表，pri_fk;