目录
1 目的
MySQL 数据库与 Oracle、 SQL Server 等数据库相比,有其内核上的优势与劣势。我们在使用 MySQL 数据库的时候需要遵循一定规范,扬长避短。本规范旨在帮助或指导技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL 编写等方面予以规范,从而为公司业务系统稳定、健康地运行提供保障。
2 数据库设计原则
2.1 核心原则
不在数据库做运算
;
控制列数量(字段少而精
,
字段数建议在
100
以内
)
;
平衡范式与冗余(效率优先;往往牺牲范式)
拒绝
3B
(拒绝大
sql
语句:
big sql
、拒绝大事物:
big transaction
、拒绝大批量:
big batch
)
;
- 控制单表数据量:单表记录控制在1000w
,大数据量建议存储nosql;
多数据渲染字段尽量保证在主表内,单条数据(不用于大数据接口渲染)修改可在副表
2.2 字段类原则
用好数值类型(用合适的字段类型节约空间)
;
字符转化为数字(能转化的最好转化
,
同样节约空间、提高查询性能)
;
避免使用
NULL
字段(
NULL
字段很难查询优化、
NULL
字段的索引需要额外空间)
;
少用
text
类型(尽量使用
varchar
代替
text
字段)
;
2.3 索引类原则
合理使用索引(改善查询
,
减慢更新
,
索引一定不是越多越好)
;
不在索引做列运算
;
innodb
主键推荐使用自增列(主键建立聚簇索引
,
主键不应该被修改
,
字符串不应该做主键)(理解
Innodb
的索引保存结构就知道了)
;
不建议使用外键(由程序保证约束)
;
2.4 sql语句原则
sql
语句尽可能简单(一条
sql
只能在一个
cpu
运算
,
大语句拆小语句
,
减少锁时间
,
一条大
sql
可以堵死整个库)
;
简单的事务
;
不用
select *
(消耗
cpu,io,
内存
,
带宽
,
这种程序不具有扩展性)
;
OR
改写为
IN
(
or
的效率是
n
级别)
;
limit
高效分页(
limit
越大,效率越低)
;
少用连接
join
(一条语句中不建议超过
3
个)
;
2.5 性能分析命令
show profile;
explain
;
show processlist;
3 MySQL规范
3.1 设计规范
数据库三范式
- 第一范式:确保每列的原子性(强调的是列的原子性,即列不能够再分成其他几列)。 如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式。
例如:顾客表(姓名、编号、地址、……),其中”“地址”“列还可以细分为国家、省、市、区等。
- 第二范式:在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关。一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的部分) 如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式。
例如:订单表(订单编号、产品编号、定购日期、价格、……),”订单编号”为主键,”产品编号”和主键列没有直接的关系,即”产品编号”列不依赖于主键列,应删除该列。
- 第三范式:在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键,不能存在传递依赖).
- 如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.
例如:订单表(订单编号,定购日期,顾客编号,顾客姓名,……),初看该表没有问题,满足第二范式,每列都和主键列”订单编号”相关,再细看你会发现”顾客姓名”和”顾客编号”相关,”顾客编号”和”订单编号”又相关,最后经过传递依赖,”顾客姓名”也和”订单编号”相关。为了满足第三范式,应去掉”顾客姓名”列,放入客户表中。
3.2 库名设计( *为必须遵守的要求)
- * 库的名称必须控制在32个字符以内,相关模块的表名与表名之间尽量提现join的关系,如user表和user_login表;
- * 库的名称格式:业务系统名称_子系统名,同一模块使用的表名尽量使用统一前缀。例如:sample_info
- * 创建数据库时必须显式指定字符集,并且字符集只能是utf8或者utf8mb4
# 以下是正确示范
- Create database db_name charset=utf8;
3.3 表结构设计
- * 表和列的名称必须控制在32个字符以内,表名只能使用字母、数字和下划线,一律小写。
# 正确示范 :sample_info、user_detail等;
- * 表名要求模块名强相关
例如:
Model | 命名规范 | eg |
样本管理 | sample_xxx | sample、sample_info |
用户管理 | user_xxx | user、user_detail、user_address |
- * 建表必须有comment。
- * 表中所有字段必须都是NOT NULL属性,业务可以根据需要定义DEFAULT值。
- 【建议】反范式设计:把经常需要join查询的字段,在其他表里冗余一份。
- * 所有的表必须使用 Innodb 存储引擎
- * 不建议使用外键
3.4 字段设计
- * 避免使用NULL字段
- # 以下是正确示范
- `age` int not null default 0
- # 以下是错误示范
- `name` char(32) default null `age` int not null
- *
少用text或longtext类型(尽量使用varchar代替text字段)
- * 用好数值类型
# 错误示范 : int(1)/int(11)
数值设计详见下表:
字段类型 | 存储空间(b) | UNSIGNED | 取值范围 |
tinyint | 1 | 否 | -128~127 |
是 | 0~255 | ||
smallint | 2 | 否 | -32768~32767 |
是 | 0~65535 | ||
mediumint | 3 | 否 | -8388608~8388607 |
是 | 0~16777215 | ||
Int | 4 | 否 | -2147483648~2147483647 |
是 | 0~4294967295 | ||
bigint | 8 | 否 | -9223372036854775808 ~9223372036854775807 |
是 | 0~18446744073709551615 |
3.5 索引设计
- * InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新。
- 【建议】主键索引名为pk字段名;唯一索引名为uk字段名;普通索引名则为idx字段名,一律使用小写格式,以表名/字段的名称或缩写作为后缀。
说明:pk 即primary key;uk_ 即unique key;idx_ 即index的简称。
- 【建议】单个表上的索引个数不能超过5个;
- * 在多表join的SQL里,保证被驱动表的连接列上有索引,这样join执行效率最高。
- * 禁止在更新十分频繁、区分度不高的属性上建立索引。
- * 禁止在索引做列运算
# 以下是错误示范
select id where age +1 = 10;
3.6 SQL语句编写规范
- * SELECT语句必须指定具体字段名称,禁止写成“*”,如果要写成SELECT *,语句一定要limit条数;
- 【建议】in值列表限制在500以内。
# 以下是正确示范
select… where userid in(….500个以内…),这么做是为了减少底层扫描,减轻数据库压力从而加速查询。
- * where条件里等号左右字段类型必须一致,否则无法利用索引。
- 【建议】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句,且WHERE子句的条件必需使用索引查找
- * 生产数据库中强烈不推荐大表上发生全表扫描,但对于100行以下的静态表可以全表扫描。查询数据量不要超过表行数的25%,否则不会利用索引。
# 以下是错误示范
SELECT … FROM DB_TABLE ORDER BY DATETIME ASC LIMIT 1
这会导致遍历整个表数据进行排序
- * WHERE 子句中禁止只使用全模糊的LIKE条件进行查找,必须有其他等值或范围查询条件,否则无法利用索引。
- 【建议】分页查询,当limit起点较高时,可先用过滤条件进行过滤。
- #以下是正确示范
- select a,b,c from t1 where id>10000 limit 20;
- # 以下是错误示范
- select a,b,c from t1 limit 10000,20
3.7 多表连接
- 【不建议】禁止跨库的join语句。
- * 禁止在业务的更新类SQL语句中使用join,比如update t1 join t2…
- 【建议】不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用join来代替子查询。
- 【建议】线上环境,多表join不要超过3个表。
- 【建议】在多表join中,尽量选取结果集较小的表作为驱动表,来join其他表。
3.8 事务
- 【建议】事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在2000以内,以及WHERE子句中IN列表的传参个数控制在500以内。
- 【建议】批量操作数据时,需要控制事务处理间隔时间,进行必要的sleep,一般建议值5-10秒。
- 【建议】对于有auto_increment属性字段的表的插入操作,并发需要控制在200以内。
- 【建议】事务里包含SQL不超过5个(支付业务除外)
说明: 因为过长的事务会导致锁数据较久,MySQL内部缓存、连接消耗过多等雪崩问题。
3.9 排序和分组
- 【建议】减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的。
- 【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by可以利用key(a,b)。
- 【建议】包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢。
3.10 禁止使用的SQL语句
- * 禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。
- * 禁止使用关联子查询,如update t1 set … where name in(select name from user where…);效率极其低下。
- * 禁止联表更新语句,如update t1,t2 where t1.id=t2.id…
3.11 视图表的构建
- 在构建较大数据库或者数据库中表格较多时,可采用构建视图表的方法加快数据库的查询等操作的速度,减少用户查询结果的等待时间,以便增加友好度。