MySQL使用规范

目录

1 目的

2 数据库设计原则

2.1 核心原则

2.2 字段类原则

2.3 索引类原则

2.4 sql语句原则

2.5 性能分析命令

3 MySQL规范

3.1 设计规范

3.2 库名设计（ *为必须遵守的要求）

3.3 表结构设计

3.4 字段设计

3.5 索引设计

3.6 SQL语句编写规范

3.7 多表连接

3.8 事务

3.9 排序和分组

3.10 禁止使用的SQL语句

3.11 视图表的构建

---

1 目的

MySQL 数据库与 Oracle、 SQL Server 等数据库相比，有其内核上的优势与劣势。我们在使用 MySQL 数据库的时候需要遵循一定规范，扬长避短。本规范旨在帮助或指导技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL 编写等方面予以规范，从而为公司业务系统稳定、健康地运行提供保障。

2 数据库设计原则

2.1 核心原则

1. 不在数据库做运算;
2. 控制列数量（字段少而精,字段数建议在100以内）;
3. 平衡范式与冗余（效率优先；往往牺牲范式）
4. 拒绝3B（拒绝大sql语句：big sql、拒绝大事物：big transaction、拒绝大批量：big batch）;
5. 控制单表数据量：单表记录控制在1000w，大数据量建议存储nosql;
6. 多数据渲染字段尽量保证在主表内，单条数据（不用于大数据接口渲染）修改可在副表

2.2 字段类原则

1. 用好数值类型（用合适的字段类型节约空间）;
2. 字符转化为数字（能转化的最好转化,同样节约空间、提高查询性能）;
3. 避免使用NULL字段（NULL字段很难查询优化、NULL字段的索引需要额外空间）;
4. 少用text类型（尽量使用varchar代替text字段）;

2.3 索引类原则

1. 合理使用索引（改善查询,减慢更新,索引一定不是越多越好）;
2. 不在索引做列运算;
3. innodb主键推荐使用自增列（主键建立聚簇索引,主键不应该被修改,字符串不应该做主键）（理解Innodb的索引保存结构就知道了）;
4. 不建议使用外键（由程序保证约束）;

2.4 sql语句原则

1. sql语句尽可能简单（一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库）;
2. 简单的事务;
3. 不用select *（消耗cpu,io,内存,带宽,这种程序不具有扩展性）;
4. OR改写为IN（or的效率是n级别）;
5. limit高效分页（limit越大，效率越低）;
6. 少用连接join（一条语句中不建议超过3个）;

2.5 性能分析命令

1. show profile;
2. explain；
3. show processlist;

3 MySQL规范

3.1 设计规范

数据库三范式

1. 第一范式：确保每列的原子性（强调的是列的原子性，即列不能够再分成其他几列）。 如果每列（或者每个属性）都是不可再分的最小数据单元（也称为最小的原子单元）,则满足第一范式。

例如：顾客表（姓名、编号、地址、……），其中”“地址”“列还可以细分为国家、省、市、区等。

1. 第二范式:在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关。一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的部分） 如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式。

例如:订单表（订单编号、产品编号、定购日期、价格、……），”订单编号”为主键，”产品编号”和主键列没有直接的关系，即”产品编号”列不依赖于主键列，应删除该列。

1. 第三范式:在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关（另外非主键列必须直接依赖于主键，不能存在传递依赖）.
2. 如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.

例如:订单表（订单编号，定购日期，顾客编号，顾客姓名，……），初看该表没有问题，满足第二范式，每列都和主键列”订单编号”相关，再细看你会发现”顾客姓名”和”顾客编号”相关，”顾客编号”和”订单编号”又相关，最后经过传递依赖，”顾客姓名”也和”订单编号”相关。为了满足第三范式，应去掉”顾客姓名”列，放入客户表中。

3.2 库名设计（ *为必须遵守的要求）

1. * 库的名称必须控制在32个字符以内，相关模块的表名与表名之间尽量提现join的关系，如user表和user_login表;
2. * 库的名称格式：业务系统名称_子系统名，同一模块使用的表名尽量使用统一前缀。例如：sample_info
3. * 创建数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4

    # 以下是正确示范

• Create database db_name charset=utf8;

3.3 表结构设计

1. * 表和列的名称必须控制在32个字符以内，表名只能使用字母、数字和下划线，一律小写。

# 正确示范 ：sample_info、user_detail等；

1. * 表名要求模块名强相关

例如：

Model	命名规范	eg
样本管理	sample_xxx	sample、sample_info
用户管理	user_xxx	user、user_detail、user_address

1. * 建表必须有comment。
2. * 表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。
3. 【建议】反范式设计：把经常需要join查询的字段，在其他表里冗余一份。
4. * 所有的表必须使用 Innodb 存储引擎 
5. * 不建议使用外键

3.4 字段设计

1. * 避免使用NULL字段

• # 以下是正确示范  
• `age` int not null default 0 
• # 以下是错误示范  
•  `name` char(32) default null `age` int not null

1. * 少用text或longtext类型（尽量使用varchar代替text字段）
2. * 用好数值类型

# 错误示范 : 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 索引设计

1. *  InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新。
2. 【建议】主键索引名为pk字段名；唯一索引名为uk字段名；普通索引名则为idx字段名，一律使用小写格式，以表名/字段的名称或缩写作为后缀。

说明：pk 即primary key；uk_ 即unique key；idx_ 即index的简称。

1. 【建议】单个表上的索引个数不能超过5个；
2. * 在多表join的SQL里，保证被驱动表的连接列上有索引，这样join执行效率最高。
3. * 禁止在更新十分频繁、区分度不高的属性上建立索引。
4. * 禁止在索引做列运算

# 以下是错误示范

select id where age +1 = 10;

3.6 SQL语句编写规范

1. * SELECT语句必须指定具体字段名称，禁止写成“*”，如果要写成SELECT *，语句一定要limit条数；
2. 【建议】in值列表限制在500以内。

# 以下是正确示范

 select… where userid in(….500个以内…)，这么做是为了减少底层扫描，减轻数据库压力从而加速查询。

1. * where条件里等号左右字段类型必须一致，否则无法利用索引。
2. 【建议】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的条件必需使用索引查找
3. * 生产数据库中强烈不推荐大表上发生全表扫描，但对于100行以下的静态表可以全表扫描。查询数据量不要超过表行数的25%，否则不会利用索引。

# 以下是错误示范

SELECT … FROM DB_TABLE ORDER BY DATETIME ASC LIMIT 1

这会导致遍历整个表数据进行排序

1. * WHERE 子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引。
2. 【建议】分页查询，当limit起点较高时，可先用过滤条件进行过滤。

• #以下是正确示范  

• select a,b,c from t1 where id>10000 limit 20;

• # 以下是错误示范  

• select a,b,c from t1 limit 10000,20

3.7 多表连接

1. 【不建议】禁止跨库的join语句。
2. * 禁止在业务的更新类SQL语句中使用join，比如update t1 join t2…
3. 【建议】不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询。
4. 【建议】线上环境，多表join不要超过3个表。
5. 【建议】在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表。

3.8 事务

1. 【建议】事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在2000以内，以及WHERE子句中IN列表的传参个数控制在500以内。
2. 【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep，一般建议值5-10秒。
3. 【建议】对于有auto_increment属性字段的表的插入操作，并发需要控制在200以内。
4. 【建议】事务里包含SQL不超过5个（支付业务除外）

说明: 因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等雪崩问题。

3.9 排序和分组

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会很慢。

3.10 禁止使用的SQL语句

1. * 禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。
2. * 禁止使用关联子查询，如update t1 set … where name in(select name from user where…);效率极其低下。
3. * 禁止联表更新语句，如update t1,t2 where t1.id=t2.id…

3.11 视图表的构建

1. 在构建较大数据库或者数据库中表格较多时，可采用构建视图表的方法加快数据库的查询等操作的速度，减少用户查询结果的等待时间，以便增加友好度。