第一章 总则

第1条 目的及适用范围

规范生产环境数据库设计，统一数据库设计标准。避免设计不合理造成重复犯错。

适用范围：Web技术部的全体开发。

第二章 命名规范

第1条 基本规范

（1） 原则：清晰明了，简洁准确，见名知意;

（2） 组成：英文字母, 下划线, 阿拉伯数字;

（3） 长度：最长64个字符，建议不超过30, 以免对后序的操作带来不便;

（4） 命名方式

1. 名称中除下划线、数字的部分，要使用英文单词或英文短语或相应缩写，不推荐使用汉语拼音;

2. 禁止使用MySQL关键字和保留字，见附录-关键字和保留字;

3. 禁止出现数字开头;

4. 英文字母使用小写形式，避免因操作系统或mysql服务配置的不同引起的问题;

5. 名词使用单数;

6. 多个单词用下划线分隔;

7. 禁止两个下划线中间只出现数字;

第2条 具体规范

（1） 库命名

1. 使用项目名缩写作为前缀，如游戏项目： game_xxx;

2. 库名长度控制在10个字符内，超出10个字符时，尽量使用单词缩写;

3. 备份库必须以bak_为前缀，以日期为后缀 例：bak_video_20180307;

4. 临时库必须以tmp_为前缀，以日期为后缀 例：tmp_video_20180307;

（2） 表命名

1. 用系统或模块的英文名的缩写作为前缀，优先使用模块名;

2. 表名长度控制在15个字符内，超出15个字符时，尽量使用单词缩写;

3. 备份表必须以bak_为前缀，以日期为后缀 例：bak_video_user_20180307;

4. 临时表必须以tmp_为前缀，以日期为后缀 例：tmp_video_user_20180307;

（3） 字段命名

1. 避免出现数字;

2. 除外键或其它表的主键外，一般不需要添加前缀;

3. 字段名长度控制在20个字符内，超出20个字符时，使用单词缩写;

（4） 索引命名

1. 主键索引名为pk_字段名，pk_即 primary key;

2. 非唯一索引 按照“idx_字段名称字段名称[字段名]”进行命名, idx_ 即 index;

3. 唯一索引 按照“uk_字段名称字段名称[字段名]”进行命名, uk_即unique key;

4. 如索引名过长，使用缩写;

第三章 注释

第1条 表注释

（1） 每个表必须要有注释;

（2） 描述数据表用途;

（3） 注释不超过30个字符;

第2条 字段注释

（1） 每个字段必须要有注释;

（2） 描述该字段的用途及可能存储的内容，字段的取值含义或者范围;

（3） 如果是枚举类型，将该字段中使用的内容都定义出来;

（4） 注释不超过30个字符;

第四章 MySQL字符集

第1条 推荐字符集

（1） 非特殊需求，所有字符存储与表示，均以utf-8编码;

（2） 如果需要存储Emoji表情，那么字段选择utf8mb4来进行存储;

第2条 创建对象的字符集

（1） 字符集和校对规则的4个级别 （从上到下，级别递减）

1. 服务器级别;

2. 数据库级别;

3. 表级别、列级别;

4. 连接级别;

（2） 更低级别的设置会继承高级别的设置;

（3） 通用规则

1. 先为服务器或者数据库选择一个合理的字符集，然后根据不同的实际情况，让某个列选择自己的字符集;

2. 字符集能表达的字符范围 utf8 > gbk > gb2312 > latin1;

3. 字符集中的ci 为 Case Insensitive (大小写不敏感)的缩写， cs 为Case Sensitive (大小写敏感)的缩写;

4. 字符集中 bin 表示用二进制存储数据，用编码值进行比较，区分大小写;

第3条 控制server和client端交互通信的字符集

（1） character_set_server：mysql server默认字符集;

（2） character_set_database：数据库默认字符集;

（3） character_set_client：MySQL server假定客户端发送的查询使用的字符集;

（4） character_set_connection：MySQL Server接收客户端发布的查询请求后，将其转换为character_set_connection变量指定的字符集;

（5） character_set_results：mysql server把结果集和错误信息转换为character_set_results指定的字符集，并发送给客户端;

（6） character_set_system：系统元数据(字段名等)字符集;

第五章 数据库设计

第1条 命名

见 库命名规范;

第2条 排序规则/校验规则 (collation)

（1） 尽量显式地设置字符集，而不是依赖于MySQL的默认设置;

（2） 如果不考虑占用空间及带宽因素，推荐使用utf-8;

第3条 数据库连接用完后,及时关闭

避免数据库连接数过大;

第六章 数据表设计

第1条 命名

见 表命名规范

第2条 排序规则/校验规则 (collation)

（1） 尽量显式地设置字符集;

（2） 结合业务使用英文字母大小写敏感/不敏感 的字符集;

第3条 表引擎

（1） 默认使用InnoDB;

（2） 从大量的select操作性能上考虑，日志及报表类数据表适合使用 MyISAM引擎，其余推荐使用InnoDB;

（3） 从支持事务，大量的insert, update 操作上来考虑，与交易、审核、金额相关的表用 InnoDB;

（4） 归档数据表(可查询，不更新删除)，使用ARCHIVE引擎;

（5） 临时数据表，如数据量不大，不需要较高的数据安全生性，使用Memory引擎;

第4条 数据表存储内容

（1） 单表数据行数建议控制在1000万以内，或者数据表占用磁盘空间不超过10G;

（2） 不在数据库中存储文件，应使用对应文件的路径;

（3） 禁止存储明文密码;

第5条 字段

（1） 单表字段数建议不超过30;

（2） 字段数过多就进行垂直分表

1. 冷热数据分离;

2. 大字段分离;

3. 常在一起作为条件和返回列的字段不分离;

第6条 表注释

如果要创建同表A一样的表结构B

推荐SQL语句：create table B like A, 或是先show create table A， 然后修改表名后，执行上一SQL结果中的表名，再执行;

避免使用 create table B as select * from B 来创建表（此操作只会创建表字段，而不会创建索引结构）;

第7条 表注释

添加简洁明了的数据表功能说明, 详细见 表注释;

第七章 字段设计

第1条 命名

见 字段命名规范;

第2条 原则

（1） 用尽量少的存储空间来存储一个字段的数据;

（2） 表内的每一行都应当被唯一地标识;

（3） 同一库中，不同表中相同含义的字段，字段名保持一致;

（4） 如无备注，所有字段都设置not null, 并设置默认值;

（5） 字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循:

1. 不是频繁修改的字段;

2. 不是 varchar 超长字段，更不能是 text 字段;

第3条 强制字段

（1） id 主键，类型为unsigned int (bigint)，自增，步长为1;

（2） 如业务需要以下字段，推荐以下命名与字段类型:

1. create_time, 创建时间； update_time, 更新时间;

2. 类型 推荐 datetime, 特点如下:

3. 日期查询速度快;

4. 可读性高;

5. 无时区变换问题;

第4条 字段类型选取

（1） 能用tinyint 就不用int;

（2） 能用int 就不用char或varchar;

（3） 字段如果为非负数，必须是 unsigned;

（4） 如果存储的字符串长度相等，使用 char 定长字符串类型 ;

（5） 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint ( 1表示是，0表示否);

（6） 小数类型为 decimal，禁止使用 float 和 double;

说明:float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储;

（7） varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率 ;

（8） 减少text,blob类型，如必要，需要将text,blob字段拆分后单独存储;

（9） 不建议使用enum类型，考虑使用tinyint类型替代;

（10）使用int unsigned存储IPV4 (使用php的 long2ip, ip2long 函数与 mysql 的 inet_aton, inet_aton) 进行转换;

（11）InnoDB引擎优先考虑使用varbinary存储大小写敏感的变长字符串或二进制内容;

第5条 表字段顺序

（1） 从前到后，按照字段的重要性和使用频率排列，id 作为第一列;

（2） 按照字段的功能归集排列，功能相似的字段相邻排列;

（3） create_time，update_time 放在最后;

第6条 字段注释

添加简洁明了的该列存储数据说明, 详细见 字段注释;

第八章 索引设计

第1条 命名

见 索引命名规范;

第2条 原则

（1） 最左前缀匹配原则;

（2） 为经常作为查询条件的字段建立索引;

（3） 为经常需要排序，分组的字段创建索引;

（4） 选择区分度高的列作为索引（组合索引除外）

1. 区分度计算：count(distinct left(列名, 索引长度))/count(*);

2. 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度即可 ;

说明：索引的长度与区分度是一对矛盾体，长度越长，区分度越高;

（5） 使用前缀索引

1. 索引的值过长，会影响查询速度;

2. 要结合区分度，来选择合适长度的字段来建立索引;

（6） 如果字段事实上是与其它表的关键字相关联而未设计为外键引用，需建索引;

（7） 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引;

（8） 多表关联查询时， 保证被关联的字段需要有索引;

（9） 有null 值存在的列，不要建索引;

（10）单张表中索引数量不超过5个;

（11）单个索引中的字段数不超过5个;

（12）禁止使用外键作为索引;

（13）索引长度不能超出限制

MyISAM: 所有索引字段定义长度的总和不能超过1000字节;

InnoDB:单一索引字段定义长度的总和不能超过767字节;

（14）使用explain 测试索引;

第3条 索引分类

（1） 唯一索引

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

2. 唯一索引的长度要和字段致;

3. 禁止使用某字段的前N个字符做唯一索引;

（2） 组合索引

1. 作为组合的查询条件、排序条件、分组条件中涉及的字段，可以建立组合索引;

2. 建组合索引的时候，区分度最高的在最左边

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

b) 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如:where a>? and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列;

3. order by 最后的字段是组合索引的一部分，并且要放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能;

（3） 全文索引

1. 在MySQL5.6以下，只有存储引擎为MyISAM的数据表支持全文检索，MySQL5.6及以上，InnoDB引擎表才开始支持全文检索;

2. MySQL全文索引只能对英文进行全文检索，本身不支持中文分词;

3. 只支持在char, varchar, text字段上创建fulltext indexes;

4. MySQL全文检索时，所有FULLTEXT索引列必须使用相同的字符集;

5. MySQL全文检索时，默认检索长度为4，即关键词的长度必须大于5才能被捕获;

第九章 分库分表

第1条 分库分表的环境条件

（1） 单机负载过高;

（2） 数据表的数据量太大，正常的运维工作影响业务访问

1. 对数据库备份时间过长;

2. DDL操作锁表时间长;

3. 读写压力大;

（3） 数据表增长快，或无穷增长;

（4） 当前数据库、数据表设计不合理;

第2条 分库分表的原则

（1） 能不分就不分

1. 分库分表增加了业务逻辑的复杂度;

2. 优先从业务逻辑、实现方法上来解决问题;

（2） 出现在联表查询中的数据表，要分在一个库中;

（3） 避免造成跨库的事务;

第3条 分库分表方法

（1） 垂直拆分

1. 根据业务按照模块或冷热数据(表)进行分库 (如微服务所使用的方式);

2. 把数据表字段按照冷热数据(字段)分离、大数据(字段)分离、关联数据(字段)不分离的原则进行拆分;

（2） 水平拆分

1. 如事先可估算出表能达到的量，可以通过一定的算法，计算每一条数据要存放的表名;

2. 如表能达到的量级不能估算，则使用号段分表， 如 id 小于 1000w对应partition_1, id介于1000w - 2000w对就partition_2….;

第十章 附录

第1条 MySQL常用数据类型

（1） 整数类型

1. tinyint (1字节)

2. smallint (2字节)

3. mediumint (3字节)

4. int (4字节)

5. bigint (8字节)

（2） 定点类型

decimal （对DECIMAL(M,D) ，如果M>D，为M+2字节否则为D+2字节）

（3） 浮点类型

1. float (4字节)

2. double (8字节)

（4） 字符类型

1. char(Length) (Length字节)

2. varchar (0-255字节)

3. tinnytext (0-255字节)

4. text (0-65535字节)

5. mediumtext (0-16 777 215字节)

6. longtext (0-4 294 967 295字节)

7. tinyblob (0-255字节)

8. blob (0-65535字节)

9. mediumblob (0-16 777 215字节)

10. longblob (0-4 294 967 295字节)

（5） 日期类型

1. date (3字节) 格式：YYYY-MM-DD 范围:1000-01-01/9999-12-31

2. time (3字节) 格式：HH:MM:SS 范围: -838:59:59/838:59:59

3. year (1字节) 格式：YYYY 范围: 1901/2155

4. timestamp （4字节）范围：1970-01-01 00:00:00/2038-01-19 03:14:07 (GMT)

5. datetime （8字节）范围：1000-01-01 00:00:00/9999-12-31 23:59:59

（6） 枚举类型

1. enum 最多65535个成员

2. set 最多 64个成员

第2条 MySQL关键字与保留字

MySQL关键字与保留字 数量较多(MySQL5.7版本 622)，不再一一列举，请见官方文档：https://dev.mysql.com/doc/refman/5.N/en/keywords.html#keywords-in-current-series