这是我参与「第三届青训营 -后端场」笔记创作活动的第4篇笔记。
0. 查询SQL语句执行频率
可使用show [session | global] status查看当前数据库状态信息。可判断当前数据库是以插入为主还是查询为主。
eg: show global status like ‘Com_______’ 查看全局状态信息
eg: show status like ‘Innodb_rows_%’ 查看Innodb表中状态信息
1. 定位低效率SQL语句
- 慢查询日志：通过慢查询日志定位效率较低的SQL语句。
2. explain分析
2.1 id
id表征着select子句或是操作表的顺序，一般来说有以下几种情况：
- id相同表示加载表的顺序从上到下（连表查询）
-
id不同，id值大的优先被执行（子查询）
-
id有相同的也有不同的，相同的是同一组，从上往下顺序查询，id值大的优先执行（一般也还是子查询）
2.2 select_type
常见的select_type结果
| select_type | Meaning |
|---|---|
| SIMPLE | 简单的select单表查询 |
| PRIMARY | 查询中包含子查询 |
| SUBQUERY | 子查询 |
| DERIVED | 在FROM列表中包含的子查询（查询结果作为临时表） |
| UNION | 若第二个SELECT出现在UNION后，则标记为UNION |
| UNIONRESULT | 从UNION表中获取结果的select |
2.3 type
type指的是该查询的类型，是一个比较重要的指标，常见的结果为
| type | Meaning |
|---|---|
| NULL | 不访问任何表和索引，直接返回结果 |
| system | 表只有一行记录，一般不会出现 |
| const | 通过索引一次找到，一般出现在主键或唯一索引，只匹配一行数据 |
| eq_ref | 类似于ref，使用唯一索引，通过主键的关联查询出一行数据 |
| ref | 非唯一索引扫描，返回匹配某个值的所有行 |
| range | 检索给定返回的行，一般出现在between，< > in等 |
| index | 遍历索引树，比all快 |
| all | 遍历全表才找到匹配的行 |
2.4 key
与key相关的主要有三个字段：possible_keys 可能用到的索引，key 实际用到的索引，key_len索引使用的字节数（一般来说越短越好
2.5 extra
额外的信息，一般有以下结果：
| extra | meaning |
|---|---|
| using filesort | 使用外部的索引排序，而不是表内的索引排序，效率低 |
| using temporary | 使用临时表保存中间结果，常见于order by，group by |
| using index | 使用了覆盖索引，避免访问表的数据行，效率高 |
| using index condition | 使用了索引，但不是所有的列数据都在索引树上，访问了实际的数据行 |
| using where | 使用where条件过滤数据 |
| using join buffer | 进行了嵌套循环计算 |
3. show profile分析SQL
首先判断是否开启profile：select @@have_profiling，如果未开启，手动开启：set profiling = 1。
通过profile，我们可以进一步的分析SQL语句中每一步的耗时：
还可以进一步跟踪到每一个query：
4. 具体案例
4.1 插入大批量数据
对比InnoDB类型的表，有以下建议
- 按主键顺序进行插入数据，减少索引树新增结点时间
- 对于数据确定的表，可以关闭唯一性校验，提高导入效率。（SET UNIQUE_CHECKS = 0）
- 手动提交事务
4.2 优化insert语句
- 同时对一张表插入多行数据时应写在一个insert语句里，减少连接时间
- 在事务中进行数据插入（数据量比较大分段提交）
- 按主键顺序有序插入数据
4.3 优化order by语句
尽量使用有序索引扫描直接返回有序数据，将using filesort优化到using index级别。
Hint：排序的顺序需要和索引的顺序保持一致，否则也会导致using filesort
4.4 优化group by语句
group by实际上也会进行排序操作，相当于先排序再分组。如果想要避免排序结果的时间开销，我们可以使用order by null来禁止排序，优化掉了filesort。也可以对order by列创建索引，优化到Using index层面。
4.5 优化子查询
推荐使用多表联查来代替子查询。多表联查可以做到ref级别，优于子查询的index级别。
