MySQL 数据库设计规范
提供一个MySQL优化干货, 需要的可以查看。
目录
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 之类,与查询字段和表名之类的小写区分开,这样语句清晰方便维护。