因为现有业务都使用的MySQL，也可叫MySQL设计规范。

一、数据库设计规范

1.1 必须遵守

• 库名、表名、字段名、索引名必须使用小写字母，并且不能以MySQL关键字&保留字命名;
• 所有的数据库、表使用UTF8MB4字符集

CREATE TABLE `tbname`
(
    `id`    int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    `name`  varchar(16)      NOT NULL DEFAULT '' COMMENT '名称',
    `reply` varchar(64)      NOT NULL DEFAULT '' COMMENT '评论',
    `ctime` timestamp        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `mtime` timestamp        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间'
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COMMENT ='xxx表'

• 所有表必须有 INT/BIGINT unsigned NOT NULL AUTO_INCREMENT 类型的主键, 提高顺序 insert 效率，强烈建议该列与业务没有联系，并且不建议使用组合主键，仅仅作为自增主键 id 使用

• 为什么选择自增id作为主键?

  • 主键自增, 数据行写入可以提高插入性能, 可以避免page分裂, 减少表碎片提升空间和内存的使用
  • 自增型主键设计(int, bigint)可以降低二级索引的空间, 提升二级索引的内存命中率;
  • 主键要选择较短的数据类型, Innodb引擎普通索引都会保存主键的值, 较短的数据类型可以有效的减少索引的磁盘空间, 提高索引的缓存效率;
  • 无主键的表删除, 在row模式的主从架构, 会导致备库夯住。

• 所有字段都是必须用NOT NULL DEFAULT 属性, 避免字段存在NULL值, 不便于计算与比较;

  • 数值类型使用：NOT NULL DEFAULT 0
  • 字符类型使用：NOT NULL DEFAULT ""

• 所有表必须携带ctime(创建时间),mtime(最后修改时间)这两个字段，便于数据分析以及故障排查;

#两个字段的类型如下，只需要在建表时建立即可，不需要开发人员再往其中插入时间值，前提是INSERT INTO语句显示的字段名称：
ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’;
mtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’

• 所有表以及字段必须添加 COMMENT, 方便自己和他人阅读, 一段时间之后可能连自己都不知道这些没有加 COMMENT 的字段是干嘛的(这是真实存在的事件);
• 非唯一索引按照 "ix字段名称[字段名称]" 进行命名, 如ix_uid_name;
• 唯一索引按照 "uk字段名称[字段名称]" 进行命名, 如uk_uid_name;
• JOIN查询时, 用于 JOIN 的字段定义必须完全相同(避免隐式转换), 并且建立索引。
• 存储单个 IP 时，必须使用整型 INT UNSIGNED 类型, 不允许使用字符型 VARCHAR() 存储单个 IP。
• 时间类型，首选使用整型 INT、INT UNSIGNED 类型, 其次使用timestamp类型。

• 日期类型, 请使用date类型。
• 所有表必须将 mtime 增加一个普通索引 ix_mtime(mtime), 便于数据平台、AI、搜索部门增量获取数据。
• 单实例单业务, 不要混合业务使用数据库

1.2 强烈建议

• 涉及精确金额相关用途的字段类型，强烈建议扩大 N 倍后转换成整型存储（例如金额中的分扩大百倍后存储成整型）, 避免浮点数加减出现不准确的问题, 也强烈建议比实际需求多保留一位, 便于后续财务方面对账更加准确;
• 对于 CHAR(N)/VARCHAR(N) 类型，在满足够用的前提下, 尽可能小的选择N的大小, 并且建议 N<255, 用于节省磁盘空间和内存空间;

# 自动插入默认时间类型，多用于创建时间类型
ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
# 自动插入默认时间且随着记录的更新而更新，多用于更新时间类型
mtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
# 程序不指定时间的前提下，插入'0000-00-00 00:00:00'，且不随着记录的更新而更新，多用于单纯的记录时间
dt TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '记录时间'

• 强烈建议使用 TINYINT 代替 ENUM 类型, 新增 ENUM 类型需要在 DDL 操作, 对于 TINYINT 类型在数据库字段 COMMENT 和程序代码中做好备注信息, 避免混淆, 如：

# 错误示例，使用enum类型
mysql> create table t(id int not null auto_increment primary key comment '自增ID',num enum('0','1','2','3') comment 'enum枚举类型' );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t(num) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+----+------+
| id | num  |
+----+------+
|  1 | 0    |
+----+------+
1 row in set (0.00 sec)
mysql> insert into t(num) values('1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+----+------+
| id | num  |
+----+------+
|  1 | 0    |
|  2 | 1    |
+----+------+
2 rows in set (0.00 sec)
# 正确示例，使用TINY类型
`num` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'TINY枚举类型：0-不通过，1-通过'

• 强烈建议不要在数据库中进行排序, 特别是大数据量的排序，可考虑在程序中设计排序；
• 强烈建议不要对数据做真正意义的物理删除(DELETE…), 可考虑逻辑删除，即在表中设计一个 is_deleted 字段标记该字段是否删除, 防止毁灭性事件的发生;
• 强烈建议每张表数据量控制在千万级别以下, 如果预估超过千万级别, 请在设计时考虑归档, 日志系统, 数据分析平台等方案;
• 强烈建议索引选择时，WHERE条件中并不是所有的列都适合作为索引列, 组合索引尽量将区分度高以及使用频率高的字段优先放在前面, 如 "性别" 由于区分度太小则不适合做索引。

1.3 尽量避免

• 尽量避免使用BLOB, TEXT类型的字段, 超大文件建议使用对象存储, 在 mysql 中只保存路径, 隐患如下:

  • 会浪费更多的磁盘和内存空间, 非必要的大量的大字段查询会淘汰掉热数据, 导致内存命中率急剧降低, 影响数据库性能
  • 大量的查询会非常消耗磁盘 IO 和网络 IO 资源, 当 IO 被打满之后, 会影响到当前服务器上的所有数据库
  • 如果必须使用, 请与主表拆开, 使用主键进行关联
  • 如果必须使用, 请控制 QPS 在 100 以内
• 尽量避免使用浮点型类型, 计算机处理整型比浮点型快 N 倍, 如果必须使用, 请将浮点型扩大N倍后转为整型;
• 尽量避免在数据库中做计算, 减轻数据库压力;
• 尽量避免JOIN查询, 请尽可能的使用单表查询, 减少查询复杂度, 减轻数据库压力。

1.4 绝对禁止

• 生产环境中，表一旦设计好, 字段只允许增加(ADD COLUMN), 禁止减少(DROP COLUMN), 禁止改名称(CHANGE/MODIFY COLUMN);
• 禁止使用UPDATE ... LIMIT ...和DELETE ... LIMIT ...操作, 因为你无法得知自己究竟更新或者删除了哪些数据, 请务必添加ORDER BY进行排序, 如：

# 这是错误的语法示例
UPDATE tb SET col1=value1 LIMIT n;
# 这是错误的语法示例
DELETE FROM tb LIMIT n;
# 这是正确的语法示例
UPDATE tb SET col1=value1 ORDER BY id LIMIT n;
# 这是正确的语法示例
DELETE FROM tb ORDER BY id LIMIT n;

• 禁止超过2张表的JOIN查询；
• 禁止使用子查询，如；

# 这是错误的语法示范
SELECT col1,col2 FROM tb1 WHERE id IN (SELECT id FROM tb2);

• 禁止回退表的DDL操作；
• 禁止在数据库中使用视图、存储过程、函数、触发器、事件；
• 禁止出现冗余索引，如索引(a),索引(a,b)，此时索引(a)为冗余索引;
• 禁止使用外键，外键的逻辑应当由程序去控制；

• 禁止使用ORDER BY RAND()排序，性能极其低下。

二、语句书写规范

2.1 CREATE TABLE 语句

# 这是正确的语法示范
CREATE TABLE `sunmi`
(
    `c1`    int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增主键ID',
    `c2`    int(11)          NOT NULL DEFAULT 0 COMMENT '无符号数值型字段',
    `c3`    int(11)          NOT NULL DEFAULT 0 COMMENT '有符号数值型字段',
    `c4`    varchar(16)      NOT NULL DEFAULT '' COMMENT '变长字符型字段',
    `c5`    tinyint(4)       NOT NULL DEFAULT 0 COMMENT '枚举类型字段：0-xxx,1-xxx,2-xxx',
    `ctime` timestamp        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间类型字段',
    `mtime` timestamp        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间类型字段',
    UNIQUE `uk_c2` (`c2`),
    INDEX `ix_c3` (`c3`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COMMENT ='xxx表';

2.2 ALTER TABLE 语句

• 添加字段, 添加字段时禁止使用 after/before 属性，避免数据偏移。

# 这是正确的语法示范
ALTER TABLE sunmi ADD COLUMN c8 int(11) NOT NULL DEFAULT 0 COMMENT '添加字段测试';

• 变更字段

# 这是正确的语法示范
# MODIFY只修改字段定义（优先使用）
ALTER TABLE sunmi MODIFY COLUMN c8 varchar(16) NOT NULL DEFAULT 0 COMMENT 'MODIFY修改字段定义';
# CHANGE修改字段名称
ALTER TABLE sunmi CHANGE COLUMN c7 c8 varchar(16) NOT NULL DEFAULT 0 COMMENT 'CHANGE修改字段名称';

• 添加主键

# 这是正确的语法示范
ALTER TABLE sunmi ADD PRIMARY KEY(c1);

• 删除字段，(不要想不开)

# 这是正确的语法示范
ALTER TABLE sunmi DROP COLUMN c8;

• 删除主键，(不要想不开)

# 这是正确的语法示范
ALTER TABLE sunmi DROP PRIMARY KEY;

2.3 CREATE/DROP INDEX 语句

• 添加普通索引

  • tips：如果创建的是联合索引, 筛选度高的列靠左

# 这是正确的语法示范
alter table tb1 add INDEX ix_c3(c3);

• 添加唯一索引

# 这是正确的语法示范
alter table tb1 add UNIQUE INDEX uk_c2(c2);

• 删除普通索引

# 这是正确的语法示范
alter table tb1 DROP INDEX ix_c3;

• 删除唯一索引

# 这是正确的语法示范
alter table tb1 DROP INDEX uk_c2;

2.4 SELECT语句

• 禁止使用 SELECT * FROM 语句，SELECT 只获取需要的字段，既防止了新增字段对程序应用逻辑的影响，又减少了对程序和数据库的性能影响;

# 这是错误的语法示范
SELECT * FROM tb WHERE col1=value1;

# 这是正确的语法示范
SELECT col1,col2 FROM tb WHERE col1=value1;

• 合理的使用数据类型, 避免出现隐式转换, 隐式转换无法使用索引且效率低, 如：SELECT name FROM tb WHERE id='1';,此时 id 为 int 类型，此时出现隐式转换［这是错误的语法示范］;
• 不建议使用 ％ 前缀模糊查询，导致查询无法使用索引, 如：SELECT id FROM tb WHERE name LIKE '%lyafei';［这是错误的语法示范］;
• 对于 LIMIT 操作，强烈建议使先 ORDER BY 再 LIMIT，即 ORDER BY c1 LIMIT n;

2.5 INSERT 语句

• INSERT INTO语句需要显示指明字段名称;

# INSERT INTO语句的正确语法示例
INSERT INTO tb(col1,col2) VALUES(value1,values2);

• 对于多次单条 INSERT INTO 语句，务必使用批量 INSERT INTO 语句，提高 INSERT INTO 语句效率，如：

# 多次单条INSERT INTO，这是错误的语法示例
INSERT INTO tb(col1,col2) VALUES(value1,values2);
INSERT INTO tb(col1,col2) VALUES(value3,values4);
INSERT INTO tb(col1,col2) VALUES(value5,values6);

# 批量INSERT INTO语句，这是正确的语法示例
INSERT INTO tb(col1,col2) VALUES(value1,values2),(value3,values4),(value5,values6);

2.6 UPDATE语句

• 注意：SET 后接的并列字段分隔符为"逗号(,)", 而不是常见的 "AND", 使用 "AND" 也能将 UPDATE 语句执行成功, 但意义完全不一样

# UPDATE语句的正确语法示例
UPDATE tb SET col1=value1,col2=value2,col3=value3 WHERE col0=value0 AND col5=value5;

• 强烈建议 UPDATE 语句后携带 WHERE 条件，防止灾难性事件的发生;
• 如果需要使用 UPDATE 修改大量数据时，请联系 DBA 协助处理，该语句极易引起主从复制延迟;
• 禁止使用 UPDATE ... LIMIT ... 语法，详情请看第1.4条规范。

2.7 DELETE 语句

• 强烈建议 DELETE 语句后携带 WHERE 条件, 防止灾难性事件的发生;

# DELETE语句的正确语法示例
DELETE FROM tb WHERE col0=value0 AND col1=value1;

• 如果需要使用 DELETE 语句删除大量数据时, 请联系 DBA 协助处理, 该语句极易引起主从复制延迟;
• 禁止使用 DELETE ... LIMIT ... 语法, 详情请见第1.4条规范。

2.8 其他书写规范

• 禁止在字段上使用函数

# 这是错误的语法示范
SELECT col1,col2 FROM tb WHERE unix_timestamp(col1)=value1;

# 这是正确的语法示范
SELECT col1,col2 FROM tb WHERE col1=unix_timestamp(value1);

• 强烈建议字段放在操作符左边

# 这是错误的语法示范
SELECT col1,col2 FROM tb WHERE value1=col1;

# 这是正确的语法示范
SELECT col1,col2 FROM tb WHERE col1=value1;

• 禁止将字符类型传入到整型类型字段中, 也禁止整形类型传入到字段类型中, 存在隐式转换的问题

# 这是错误的语法示范
# var_col字段为VARCHAR类型
SELECT col1,col2 FROM tb WHERE var_col=123;
# int_col字段为INT类型
SELECT col1,col2 FROM tb WHERE int_col='123';

# 这是正确的语法示范
# var_col字段为VARCHAR类型
SELECT col1,col2 FROM tb WHERE var_col='123';
# int_col字段为INT类型
SELECT col1,col2 FROM tb WHERE int_col=123;

三、程序操作数据库设置规范

3.1 必须遵守

• 如果应用使用的是长连接, 应用必须具有自动重连的机制, 但请避免每执行一个 SQL 去检查一次 DB 可用性;
• 如果应用使用的是长连接, 应用应该具有连接的 TIMEOUT 检查机制, 及时回收长时间没有使用的连接, TIMEOUT 时间一般建议为2小时;
• 程序访问数据库连接的字符集请设置为 utf8mb4;

3.2 绝对禁止

• 程序中禁止一切DDL操作。

四、行为规范

• 禁止使用应用程序配置文件内的帐号手工访问线上数据库, 大部分配置文件内的数据库配置的是主库, 你无法预知你的一条SQL会不会导致MySQL崩溃；
• 大型活动(如拜年祭) 或 突发性大量操作数据库(如发送私信)等操作时, 应提前与DBA当面沟通, 进行流量评估, 避免数据库出现瓶颈；
• 批量清洗数据, 需要开发和DBA共同进行审查, 应避开业务高峰期时段执行, 并在执行过程中观察服务状态;
• 禁止在主库上执行后台管理和统计类的功能查询, 这种复杂类的SQL会造成CPU的升高, 进而会影响业务。

五、分库分表命名规则

自增数字分表(库)，表(库)名使用自动补齐规则

• lyafei表分10 张表，命名如下：lyafei_0 ~ lyafei_9
• lyafei表分100张表，命名如下：lyafei_00 ~ lyafei_99
• lyafei表分1000张表，命名如下:lyafei_000 ~ lyafei_999

按年分表(库)，表(库)名后缀为对应的年份

• lyafei_2017 ~ lyafei_2020

按月分表(库)，表(库)名后缀为对应的年月

• lyafei_201701 ~ lyafei_202012

按天分表(库)，表(库)名后缀为对应的年月日

• lyafei_20170101 ~ lyafei_20201201

六、常用字段数据类型范围

数值类型

字符类型

VARCHAR(N)：在 MySQL 数据库中, VARCHAR(N) 中的 N 代表 N 个字符, 不管你是中文字符还是英文字符, VARCHAR(N) 能存储最大为 N 个中文字符/英文字符。

时间类型

TIMESTAMP: 1970-01-01 00:00:01 UTC ~2038-01-19 03:14:07 UTC
DATETIME: 1000-01-0100:00:00 ~ 9999-12-31 23:59:59