MySQL开发规范及慢查询优化

1、数据库及建表规约

1. 存储引擎统一使用innodb，字符集统一使用utf8mb4

2. 库的名称格式：业务系统名称_子系统名，同一模块使用的表名尽量使用统一前缀

3. 避免在MySQL中创建触发器、存储过程、自定义函数、视图、事件

4. 库名及表名统一使用小写英文、数字以及下划线，建议不超过32个字符，禁止使用保留字 具体可参考Mysql官方保留字

5. 所有表和字段都要添加注释COMMENT

6. 表必须要有主键，建议使用自增的UNSIGNED INT(BIGINT)类型，禁止使用uuid

7. 禁止使用外键对表之间进行关联，一切外键概念必须在应用层解决

8. 需要join的字段(连接键),数据类型必须保持绝对一致，避免隐式转换. 被关联的字段需要有索引

9. 单表数据尽量控制在800W内，如果超出可做数据归档或历史数据转移

10. 尽量为每个表加上以下三个字段：

    create_time: COLUMN create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP update_time: COLUMN update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON
    UPDATE valid 标识，是否有效(1/0)，默认值为1，逻辑删除时为0

2、字段设计规约

1. 字段名统一使用小写英文及下划线构成，建议不超过32个字符，禁止使用保留字
2. 各表之间相同意义的字段必须同名
3. 字段定义为Not Null，且提供默认值 null值的列，很难对索引优化；null的列对占用更多的空间，因为需要额外的空间来标识。 null的查询操作，只能采用is null或is not null，而不能采用=、in、<、<>、not in 、!=操作符， 如：where name != 'xxx'，是不会查询出name为null的值的 使用count函数统计存在为null的列时，无法统计入内
4. 使用varchar替代text类型，如果字段超出长度，需要将text类型的字段单独建表存储
5. 金额类数据存储使用DECIMAL，禁止使用FLOAT和DOUBLE
6. 表达是与否概念的字段命名避免使用is开头，jfinal框架会产生映射错误
7. 优先选择符合业务的最小存储类型 表达是与否概念的字段类型设计为tinyint(1)，默认值0/1 枚举概念的字段类型设计为tinyint(4)，默认值0

3、索引设计规约

1. 索引命名尽量采用大写，命名以IDX开头: IDX_字段1_字段2

2. 区分度不高、更新频繁的列 不建议加索引 索引必须创建在索引选择性较高的列上,选择性的计算方式为: select count(distinct(col_name))/count(*) from tb_name; 如果结果小于0.2，则不建议在此列上创建索引 枚举概念的字段不建议建索引， 例如性别

3. 建立联合索引时，把区分度高的放到最左侧 mysql的索引结构原理：最左索引原则

4. JOIN，ORDER BY，GROUP BY，DISTINCT的字段需要添加索引

5. order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合 索引的一部分，并且放在索引组合顺序的最后，避免file_sort 正例：where a=? and b=? order by c; 索引：a_b_c 反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序

6. 避免建立冗余索引和重复索引 重复索引示例：primary key(id)、index(id)、unique index(id); 冗余索引示例：index(a,b,c)、index(a,b)、index(a)

4、SQL语句规约

1. 避免使用select * 这种方式，需要哪些字段必须明确写明 原因：前者消耗更多的CPU、IO开销 前者无法使用覆盖索引 后者可减少表结构的改动，带来的代码影响

2. where条件中的过滤条件字段上禁止使用任何函数及正则表达式，包括属性隐式转换 where type=4 不要写为 where type='4'

3. 建议使用预编译语句进行数据库操作, 禁止在代码使用sql拼接 预编译语句可以重复使用优化计划，减少SQL编译时间，避免SQL注入

4. 不要使用count(列名)代替count() Mysql5.6 count()使用普通索引 Mysql5.7 对count(*)做了优化，使用了聚集索引

5. 不存在重复数据时使用union all代替union union会删除重复的⾏，可能会在磁盘进⾏排序

6. 模糊查询使用like 'xx%', 避免使用 like '%xx'，like '%xx%'，右匹配/全匹配推荐使用搜索引擎解决

7. 使用in查询时，如果查询字段为整型并且取值范围固定，可以使用between 代替in ,效率更高

8. 使用not exist代替not in

5、慢查询分析及优化

$$

$$

EXPLAIN SELECT idpoi_name FROM ykb_baidupoi_category WHERE poi_name ='交通设施

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ykb_baidu_poi_category	ref	IDX_POI_NAME	IDX_POI_NAME	82	const	1	Using where;Using index

执行计划重要字段说明:

1. Type : mysql数据的访问类型(const>eq_ref>ref>range>index>all)
2. possible_keys: 可能应用的索引，不一定真正用到
3. key: 实际使用的索引
4. rows: 扫描记录行数
5. Extra: 额外信息说明

慢查询分析及优化-访问类型说明

Type	是否使用索引	详细说明
all	全表扫描	原生查找方式，耗时低效
index	全表扫描，扫描索引树	与all的区别在于按照索引的顺序扫描全表
range	有范围的索引扫描	查找索引有范围限制，优于index
ref	索引精确查找，有重复数据(非主键，非唯一索引)	会存在多条记录，需要进行目标值附近的小范围扫描，由于索引有序，不需要扫全表
eq_ref	索引精确查找，无重复数据(主键或唯一索引)	通常用在多表查询里，表A关联表B, 关联字段在表A是唯一索引
const	主键索引/唯一索引精确查找	通常用在单表查询里，只有一条记录

慢查询分析及优化-额外信息说明

Extra	详细说明	优化方案
using filesort	文件排序，即mysql中无法利用索引完成排序，需要在内存里重新排序，耗时低效	查询字段及排序字段建立顺序一致的联合索引增加sort_buffer_size的大小将排序移到业务层解决
usingtemporary	mysql使用了临时表，常见于排序order by和分组查询group by使用不同列 临时表两种: heap类型的内存临时表 Myisam/InnoDB类型的磁盘临时表	group by和order by建立顺序一致的联合索引 优化减少中间结果大小注意: left join on 后面的条件对主表无效， where 后面的过滤条件对主表及连接表都有效建议多表join时条件写在on 后面减少中间结果大小
using where	条件过滤，过滤字段无索引	建立索引
using index condition	先在二级索引上使用索引查找到主键，然后在主键索引上通过主键ID进行查找原因: 二级索引未覆盖所有select字段	不需要优化
using whereusing index	二级索引上获取全部数据原因: 二级索引已覆盖所有select字段	不需要优化
using index	使用主键索引获取全部数据，不需要回表	不需要优化