MySQL数据库设计规范

MySQL 数据库设计规范

提供一个MySQL优化干货， 需要的可以查看。

目录

1. 基础规范
2. 命名设计与规范
3. 索引规范
4. SQL设计

1. 基础规范 ^

1.1. 使用Innodb存储引擎

1.2. 表字符集统一使用UTF8

• 如果遇到 EMOJ 等表情符号的存储需求，可使用 utf8mb4 字符集。

1.3. 控制单表字段数量

• 单表字段数上限30左右，再多的话考虑垂直分表，一是冷热数据分离，二是大字段分离，三是常在一起做条件和返回列的不分离。

1.4. 所有表都要添加注释！所有表都要添加注释！所有表都要添加注释！

重要的事情说三遍，例如status型需指明主要值的含义，注释“0-删除,1-正常”。

1.5. 不强制使用外键参考

• 即使2个表的字段有明确的外键参考关系，也不使用 FOREIGN KEY ，因为新纪录会去主键表做校验，影响性能。

1.6. 数据库中不允许存储明文密码

2. 命名设计与规范 ^

2.1. 库表设计与规范

• 库名、表名、字段名必须使用小写字母，并采用“_”下划线分割。

• 库名以 d_ 开头，表名以 t_+相关业务 开头。

• 临时库、表名须以 tmp 加日期为后缀，按日期时间分表须符合 _YYYYMMDD 格式。

2.2. 字段设计与规范

• 表必须定义主键，默认为表名+_id，整型自增，修改和新增时不允许修改主键。

• 命名简洁明确，需见名知意（长度不能超过32个字符）。

• 多表中的相同列，尽可能命名一致，类型、长度必须一致

• 用好数值类型，例如：能使用 int 就不要使用 varchar、char ，能用 varchar(16) 就不要使用 varchar(256) 。

• 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能)。

• 避免使用 NULL 字段，并设置 default 值（ NULL 字段很难查询优化、 NULL 字段的索引需要额外空间、 NULL 字段的复合索引无效）。

• 在允许的情况下，表必须包含记录创建时间和修改时间的字段。

• IP地址最好使用 int 类型。

• 固定长度的类型最好使用 char ，例如：邮编。

• 能使用 tinyint 就不要使用 smallint 、 int 。

• 禁止使用 float 、 double 类型，建议使用 decimal 或者 int 替代。

• 日期类型字段（时间戳可直接使用int(10)）根据需要定义成对应格式，尽量不要使用 varchar 格式。

类型	大小	格式	用途
DATE	3	YYYY-MM-DD	日期值
TIME	3	HH:MM:SS	时间值或持续时间
YEAR	1	YYYY	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

3. 索引规范 ^

3.1. 任何复杂的SELECT, UPDATE, DELETE，都要先EXPLAIN，看索引使用情况

• 尽量避免 extra 列出现： Using File Sort ， Using Temporary ， rows 超过1000的要谨慎上线。

• 可根据需要调整索引。

3.2. 索引个数限制

• 单张表的索引数量控制在5个以内，或不超过表字段个数的20%。

3.2. 索引尽量建在选择性高的列上

3.3. 尽量不要在频繁更新的列上创建索引

3.4. 避免冗余索引

例如： index1 的定义为 test(filed1,filed2) ，
index2 的定义为 test(filed1,filed2,filed3) ，
则认为index1是多余的。

3.5. 避免在WHERE条件中，在索引列上进行计算或使用函数，因为这将导致索引不被使用

SELECT empno, ename, sal, sal*12 FROM emp1 WHERE sal*12 > 20000;
应该修改为：
SELECT empno, ename, sal, sal*12 FROM emp1 WHERE sal > 20000/12;

3.6. 索引命名

• 非唯一索引必须按照 index_字段名称_字段名称[_字段名] 进行命名。

• 唯一索引必须按照 uniq_字段名称_字段名称[_字段名] 进行命名。

4. SQL设计 ^

4.1. 尽量不要直接 SELECT * 读取全部字段，即使需要所有列

4.2. 禁止使用非同类型的列进行等值查询

• 用不同类型列进行等值查询，容易导致错误以及隐式类型转换，可以先在代码中转换后再变成SQL查询。

4.3. 数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新

4.4. 使用join时，where条件尽量使用充分利用同一表上的索引

例如：
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a=t2.a AND t1.b=123 AND t2.c=4;
如果 t1.c 与 t2.c 字段相同，且 t1上 存在索引 index_b_c(b,c) ，
那么此时的 SQL 就只用到索引 b 了。
此时如果把 WHERE 条件中的 t2.c=4 改成 t1.c=4 ，那么可以用到完整的索引。

4.5. OR改写为IN

• or 的效率是 n 级别， in 的效率是 log(n) 级别，当 n 很大时， or 会慢很多。

4.6. 避免使用is null, is not null这样的比较

4.7. 杜绝危险SQL

• 去掉 WHERE 1=1 这样无意义或恒真的条件，如果遇到 UPDATE/DELETE 或遭到 sql 注入就恐怖了。

4.8. 减少与数据库交互的次数，尽量采用批量SQL语句

例如：
新增数据，可以处理好全部后批量插入（使用事务边处理边插入数据，会导致长时间锁表）。

4.9. MySQL中关键字使用大写

例如： 
SELECT、FROM 之类，与查询字段和表名之类的小写区分开，这样语句清晰方便维护。