MySQL数据库设计规范
MySQL 数据库与 Oracle、 SQL Server 等数据库相比，有其内核上的优势与劣势。我们在使用 MySQL 数据库的时候需要遵循一定规范，扬长避短。本规范旨在帮助或指导开发人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL 编写等方面予以规范，从而为公司业务系统稳定、健康地运行提供保障。
一 数据库设计
-
配置规范
1. MySQL 数据库默认使用 InnoDB 存储引擎。
2. 保证字符集设置统一，MySQL 数据库相关系统、数据库、表和字段的字符集都用 UTF8，应用程序连接、展示、客户端等可以设置字符集的地方也都统一设置为 UTF8 字符集。
3. Mysql数据库的事务隔离级别默认为 RR（Repeatable-Read），建议初始化时统一设置为 RC（Read-Committed），对于 OLTP 业务更合适。
4. 数据库中的表要合理规划，控制单表数据量，对于 MySQL 数据库来说，建议单表记录数控制在2000万以内。
5. MySQL 实例下，数据库、表数量尽可能少：数据库一般不超过50个，每个数据库下，数据表数量一般不超过500个（包括分区表）。
-
建表规范
1. 表和列的名称建议控制在12个字符以内，表名只能使用字母、数字和下划线，一律小写。
2. 表名要求以 t_ 为前缀,并与模块名强相关，如师资系统采用”t_sz”作为前缀，渠道系统采用”t_qd”作为前缀等。
3. 建表时关于主键：主键字段建议为 int 或 bigint， 如果配置表可以为auto_increment。如果是业务表，数据量大的表要求主键id为定长的前八后九的方式，前8位为日期，后9位为自增的ID（具体实现方式可问开发leader）
主键值构成： 8位日期 + 8/9/10位序列号，如：201912010000000001 （8位日期20191201、10位序列号：0000000001）sequence-util
说明：
1、当主序列号不超过11位时，数字串长度总共为19位，这个长度是在bigint的存储范围内，因此使用bigint类型作主键；biginit 最长20位 即 biginit(20)
当设计出biginit(30) 其实是假的 最多只能20位，biginit(20)
2、UUID强烈不建议使用。
解释：
InnoDB存储引擎使用的是IOT（索引组织）表，因此整张表就是一棵树（B+Tree）。因此，一切高效的访问和使用 IOT 表的基础就是这颗树的高度低，而降低索引的高度，就是主键长度越小越好。
bigint的存储范围：有符号：(-9,223,372,036,854,775,808，9 223 372 036 854 775 807) 前后一共19位，无符号：(0，18 446 744 073 709 551 615) 一共20位，完全能满足19位数字串的存储需要。
bigint 占用8字节，如果改成varchar(20)，则长度为 20字符 * 字节编码长度(utf8：一字符占3字节，utf8mb4：一字符占4字节）=> 60+1字节（utf8）/ 80+1字节 （utf8mb4）
而如果改用decimal(M,D)类型， 其中M将每9位数字包装成4个字节；因此，decimal(20,0)长度为： 两个9位(8字节) + 一个2位(1字节) = 9字节
|
类型
|
长度
|
|---|---|
|
类型
|
长度
|
| bigint | 8字节 |
| decimal(20,0) | 9字节 |
| varchar(20) | 60+1字节（utf8）/ 80+1字节 （utf8mb4） |
4. InnoDB 禁止使用外键约束，可以通过程序层面保证。
6. 核心表（如用户表，流水表）必须有创建时间字段 create_time 和最后更新时间字段 update_time，便于查问题和数据清洗。
7. 存储精确浮点数必须使用 DECIMAL 代替 FLOAT 和 DOUBLE。
8. 整形定义中无需定义显示宽度，比如：使用 INT，而不是 INT(4), 如确认定长则需要宽度。
解释：INT(4) 不是指存储范围是0-9999，如果存储范围只要0-9999，可以改用 SMALLINT 型，具体各数据类型长度请查看附录表格。括号里面的数字仅在字段加上 zerofill 属性时显示宽度，否则 INT(2)、INT(4) 和 INT(10) 无任何差别
9. 不建议使用 ENUM 类型，可使用 TINYINT 来代替。
解释：ENUM 类型属于字符型数据，在表定义的时候就把枚举值写死了，并且浪费存储空间。如果增加枚举值需要执行相应的 DDL 操作。TINYINT 则是数值型，存储只占用1个字节，对于类型扩展很方便操作，不需要执行 DDL。
10. 尽可能不使用 TEXT、BLOB 类型，如果必须使用，建议将过大字段或是不常用的描述型大字段拆分到其他表中；另外，禁止用关系型数据库存储图片或文件。
11. 时间使用 timestamp 数据类型，存储年时使用YEAR(4)，不使用YEAR(2)。更高级的做法，用 INT 型来存储时间戳，没有时区差异处理，然后通过 SQL 函数 unix_timestamp() 和 from_unixtime() 来转换。
12. 建议字段定位为 NOT NULL，这对于该列索引的效率会很有帮助。
13. 反范式设计：把经常需要 join 查询的字段，在其他表里冗余一份。如：user_name 属性在 user_account，user_login_log 等表里冗余一份，减少 join 查询。
14. 业务中IP地址字段推荐使用 int 类型，不推荐用 char(15)。因为 int 只占4字节，可以用如下函数相互转换，而 char(15) 占用至少15字节。一旦表数据行数到了1亿，那么要多用 1.1G 存储空间。
示例：select inet_aton('192.168.1.1'); select inet_ntoa(3232235777);
15. 订单表varchar单个字段类型最大建议长度为1024 即varchar（1024）,建议是varchar（255）以下 特殊情况与DBA说明
16. 单表字段数不超过30个，超过需要拆分父子表。
17. 不能用json 字段类型
-
-
命名规范
-
1. 库、表、字段全部采用小写。
2. 库名、表名、字段名、索引名称均使用小写字母，并以“_”分隔。
3. 库名、表名、字段名建议不超过12个字符。（对多支持64个字符）
4. 库名、表名、字段名见名知意，并且添加注释。
5. 库名、表名、字段不允许出现数字，需要全英文命名。
6. 命名时避开 MySQL 关键字。参考：https://dev.mysql.com/doc/refman/5.7/en/keywords.html
-
-
索引规范
-
1. 索引建议命名规则：idx_col1_col2[_colN]、uk_col1_col2[_colN]（如果字段过长建议采用缩写）。
2. 索引中的字段数建议不超过5个。
3. 单张表的索引个数控制在5个以内。
4. InnoDB 表强制要求有主键列。
5. 建立复合索引时，优先将选择性高的字段放在前面。
6. UPDATE、DELETE 语句需要根据 WHERE 条件添加索引。
7. 不建议使用%前缀模式查询。例如 LIKE ‘%WEIBO’，无法使用索引，会导致全表扫描。
8. 合理利用覆盖索引。例如：SELECT email,uid FROM user_email WHERE uid=xxx，如果 uid 不是主键，可以创建覆盖索引 idx_uid_email(uid,email) 来提高查询效率。
9. 避免在索引字段上使用函数，否则会导致查询时索引失效。
10. 避免不必要的重复或冗余索引，根据索引左前缀使用原则，如表中的存在索引 key(a,b)，则索引 key(a) 即为冗余索引，需要删除。
11. 确认索引是否需要变更时需要联系 DBA。
一个较为规范的建表语句：
CREATE TABLE db.t_user (
id bigint(20) NOT NULL AUTO_INCREMENT,
user_id bigint(20) NOT NULL COMMENT '用户id',
username varchar(45) NOT NULL COMMENT '真实姓名',
email varchar(30) NOT NULL COMMENT '用户邮箱',
nickname varchar(45) NOT NULL COMMENT '昵称',
avatar int(11) NOT NULL COMMENT '头像',
birthday date NOT NULL COMMENT '生日',
sex tinyint(4) DEFAULT '0' COMMENT '性别',
short_introduce varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
user_resume varchar(255) NOT NULL COMMENT '用户提交的简历存放地址',
user_register_ip int NOT NULL COMMENT '用户注册时的源ip',
create_time timestamp NOT NULL COMMENT '用户记录创建的时间',
update_time timestamp NOT NULL COMMENT '用户资料修改的时间',
user_review_status tinyint(4) NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核',
PRIMARY KEY (id),
UNIQUE KEY idx_user_id (user_id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户基本信息';
--新建表后需要赋予相应的权限
grant select ,update ,INSERT,DELETE on db.t_user to dbuser@'%';
-
-
程序层DAO设计建议
-
1. 推荐使用手动拼 SQL+ 绑定变量传入参数的方式。
2. 前端程序连接 MySQL 或者 Redis，必须要有连接超时和失败重连机制，且失败重试必须有间隔时间。
3. 前端程序报错里尽量能够提示 MySQL 或 Redis原生态的报错信息，便于排查错误。
4. 对于有连接池的前端程序，必须根据业务需要配置初始、最小、最大连接数，超时时间以及连接回收机制，否则会耗尽数据库连接资源，造成线上事故。
5. 对于 log 或 history 类型的表，随时间增长容易越来越大，因此上线前开发和 DBA 必须建立表数据清理或归档方案。
6. 在应用程序设计阶段，开发必须考虑并规避数据库中主从延迟对于业务的影响。尽量避免从库短时延迟（20秒以内）对业务造成影响，建议强制一致性的读开启事务走主库，或更新后过一段时间再去读从库。
7. 多个并发业务逻辑访问同一块数据（innodb表）时，会在数据库端产生行锁甚至表锁导致并发下降，因此建议更新类 SQL 尽量基于主键去更新。
8. 业务逻辑之间加锁顺序尽量保持一致，否则会导致死锁。
9. 对于单表读写比大于10:1的数据行或单个列，可以将热点数据放在缓存里（如mecache或redis），加快访问速度，降低 MySQL 压力。
二 SQL 编写
-
-
DML语句
-
1. SELECT 语句必须指定具体字段名称，禁止写成 *。因为 select * 会将不该读的数据也从 MySQL 里读出来，造成网卡压力。且表字段一旦更新，但 model 层没有来得及更新的话，系统会报错。
2. INSERT 语句指定具体字段名称，不要写成 INSERT INTO t1 values(…)，道理同上。
3. INSERT INTO…VALUES(XX),(XX),(XX)…。这里 XX 的值不要超过5000个。值过多虽然上线很很快，但会引起主从同步延迟。
4. SELECT 语句慎重使用 UNION，推荐使用 UNION ALL，并且 UNION 子句个数限制在5个以内。因为 UNION ALL 不需要去重，节省数据库资源，提高性能。
5. IN 值列表限制在500以内。例如 SELECT… WHERE userid IN(….500个以内…)，这么做是为了减少底层扫描，减轻数据库压力从而加速查询。
6. 事务里批量更新数据需要控制数量，进行必要的 sleep，做到少量多次。
7. 事务涉及的表必须全部是 InnoDB 表。否则一旦失败不会全部回滚，且易造成主从库同步终端。
8. 写入和事务发往主库，只读 SQL 发往从库。
9. 除静态表或小表（1000行以内），DML 语句必须有 WHERE 条件，且使用索引查找。
10. 生产环境禁止使用 hint，如 sql_no_cache，force index，ignore key，straight join 等。
11. WHERE 条件里等号左右字段类型必须一致，否则无法利用索引。
12. SELECT|UPDATE|DELETE|REPLACE 要有 WHERE 子句，且 WHERE 子句的条件必需使用索引查找。
13. 生产数据库中强烈不推荐大表上发生全表扫描，但对于1000行以下的静态表可以全表扫描。查询数据量不要超过表行数的25%，否则不会利用索引。
14. WHERE 子句中禁止只使用全模糊的 LIKE 条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引。
15. 索引列不要使用函数或表达式，否则无法利用索引。如 WHERE length(name)='Admin' 或 WHERE user_id+2=10023。
16. 减少使用 OR 语句，可将 OR 语句优化为 UNION，然后在各个 WHERE 条件上建立索引。如 WHERE a=1 or b=2 优化为 WHERE a=1… UNION …WHERE b=2, key(a),key(b)。
17. 分页查询，当 limit 起点较高时，可先用过滤条件进行过滤。如 select a,b,c from t1 limit 10000,20; 优化为: select a,b,c from t1 where id>10000 limit 20;。
-
-
多表连接
-
1. 禁止跨 db 的 join 语句。因为这样可以减少模块间耦合，为数据库拆分奠定坚实基础。
2. 禁止在业务的更新类 SQL 语句中使用 join，比如 update t1 join t2…。
3. 不建议使用子查询，建议将子查询 SQL 拆开结合程序多次查询，或使用 join 来代替子查询。
4. 线上环境，多表 join 不要超过3个表。
5. 多表连接查询推荐使用别名，且 SELECT 列表中要用别名引用字段，数据库.表格式，如 select a from db1.table1 alias1 where …。
6. 在多表 join 中，尽量选取结果集较小的表作为驱动表，来 join 其他表。
-
-
事务
-
1. 事务中 INSERT|UPDATE|DELETE|REPLACE 语句操作的行数控制在2000以内，以及 WHERE 子句中 IN 列表的传参个数控制在500以内。
2. 批量操作数据时，需要控制事务处理间隔时间，进行必要的 sleep，一般建议值5-10秒。
3. 对于有 auto_increment 属性字段的表的插入操作，并发需要控制在200以内。
4. 程序设计必须考虑“数据库事务隔离级别”带来的影响，包括脏读、不可重复读和幻读。线上建议事务隔离级别为 Read-Commit。
5. 事务里包含 SQL 不超过5个（支付业务除外）。因为过长的事务会导致锁数据较久，MySQL 内部缓存、连接消耗过多等雪崩问题。
6. 事务里更新语句尽量基于主键或 unique key，如 update … where id=XX; 否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。
7. 尽量把一些典型外部调用移出事务，如调用 webservice，访问文件存储等，从而避免事务过长。
8. 对于 MySQL 主从延迟严格敏感的 select 语句，请开启事务强制访问主库。
-
-
排序和分组
-
1. 减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct 这些语句较为耗费 CPU，数据库的 CPU 资源是极其宝贵的。
2. order by、group by、distinct 这些 SQL 尽量利用索引直接检索出排序好的数据。如 where a=1 order by可以利用 key(a,b)。
3. 包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集请保持在1000行以内，否则 SQL 会很慢。
-
-
线上禁止使用的SQL语句
-
1. 禁用 update|delete t1 … where a=XX limit XX; 这种带 limit 的更新语句。因为会导致主从不一致，导致数据错乱。建议加上 order by PK。
2. 禁止使用关联子查询，如 update t1 set … where name in(select name from user where…); 效率极其低下。
3. 禁用 procedure、function、trigger、views、event、外键约束。因为他们消耗数据库资源，降低数据库实例可扩展性。推荐都在程序端实现。
4. 禁用 insert into …on duplicate key update… 在高并发环境下，会造成主从不一致。
5. 禁止联表更新语句，如 update t1,t2 where t1.id=t2.id…。
附：MySQL 数据类型
-
-
整型
-
|
类型
|
大小
|
范围（有符号）
|
范围（无符号）
|
用途
|
|---|---|---|---|---|
|
类型
|
大小
|
范围（有符号）
|
范围（无符号）
|
用途
|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
-
-
日期和时间类型
-
|
类型
|
大小
(字节) |
范围
|
格式
|
用途
|
|---|---|---|---|---|
|
类型
|
大小
(字节) |
范围
|
格式
|
用途
|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 |
1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 |
YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
-
-
字符串类型
-
|
类型
|
大小
|
用途
|
|---|---|---|
|
类型
|
大小
|
用途
|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
浙公网安备 33010602011771号