MySQL-如何定位慢查询SQL以及优化

慢查询日志记录慢SQL

定位慢SQL可以通过慢查询日志来查看慢SQL，默认的情况下，MySQL数据库不开启慢查询日志(slow query log)，需要手动把它打开
SET GLOBAL slow_query_log = ‘ON’;

查看下慢查询日志配置
SHOW VARIABLES LIKE ‘slow_query_log%’

slow_query_log：表示慢查询开启的状态
slow_query_log_file：表示慢查询日志存放的位置

查看超过多少时间，才记录到慢查询日志
SHOW VARIABLES LIKE ‘long_query_time’

注意： 这样配置是临时的如果需要永久修改需要去配置文件(/etc/my.cnf)

explain查看分析SQL执行计划

通过慢查询日志定位出查询效率较低的SQL，可以使用explain查看SQL的执行计划

id

1. id 值相同时，被视为一组从上向下执行。
2. 如果是子查询，id 值会递增，id 值越高，优先级越高
3. id为NULL最后执行

select_type

1. simple: 简单的select, 查询中不包含子查询或者 union。例如: select name from student where id= 100
2. primary: 子查询中最外层查询, 查询中若包含任何复杂的子部分, 最外层的select被标记为primary
3. derived：在 from 的列表中包含的子查询被标记成 derived（派生表）。例如: explain select id from (select id,name from student) student1 where name= ‘name100’
4. subquery：在 select 或 where 列表中包含了子查询，则子查询被标记成 subquery。例如: explain select id from student where score = (select score from student where
name=‘name100’);
5. union: union中的第二个或后面的select语句. 例如: EXPLAIN select id from student where id<12691055 UNION all select id from student where id<12691060;

table

显示这一步所访问数据库中表名称. 有时候不是真实的表名, 可能是简称

partitions

该字段看table所在的分区, 值为NULL表示表未被分区

possible_keys

可能会使用到的索引(ps.其实不太重要)

重点关注的字段
type

表示连接类型，查看索引执行情况的一个重要指标 以下性能从好到坏依次：system > const > eq_ref > ref >
ref_or_null > index_merge > unique_subquery > index_subquery > range >
index > ALL
system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的
const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，速度非常快
eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询 ref : 常用于非主键和唯一索引扫描
ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
index_merge：使用了索引合并优化方法，查询使用了两个以上的索引
unique_subquery：类似于eq_ref，条件用了in子查询
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值
range：常用于范围查询，比如：between … and 或 In 等操作 index：全索引扫描 ALL：全表扫描

key

实际使用到的索引

key_len

实际使用到的索引的长度

rows

该列表示MySQL估算找到我们所需的记录，需要读取的行数

filtered

该列是一个百分比，是满足条件的记录数量与我们查询了多少记录数量的比值

extra

该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：
● Usingfilesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现，一般见于order by语句
● Using index：表示是否用了覆盖索引
● Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化，一般多见于groupby语句，或者union语句
● Using where : 表示使用了where条件过滤
● Using index condition：MySQL5.6之后新增的索引下推，在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据

profile分析执行耗时

explain只是看到SQL预估的执行计划，如果要了解SQL真正的执行线程状态及消耗的时间，需要使用profiling，开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化

Optimizer Trace分析详情

profile只能查看到SQL的执行耗时，但无法看到SQL真正执行的过程信息，不知道MySQL优化器是如何选择执行计划，这时候，可以使用Optimizer
Trace，它可以跟踪执行语句的解析优化执行的全过程

三个阶段分为对应：准备阶段、分析阶段、执行阶段

确定问题采用响应措施

● 多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，可以优化索引
● 还可以优化SQL语句，比如一些in元素过多问题(分批)，深分页问题(基于上一次数据过滤等)，进行时间分段查询
● SQL没办法很好优化，可以改用ES的方式，或者数仓
● 如果单表数据量过大导致慢查询，可以考虑分库分表
● 如果数据库在刷脏页导致慢查询，考虑是否可以优化一些参数
● 如果存量数据量太大，考虑是否可以让部分数据归档