
慢查询日志记录慢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'
命令,查看超过多少时间,才记录到慢查询日志,如下:
explain查看分析SQL的执行计划
- 当定位出查询效率低的SQL后,可以使用explain查看SQL的执行计划
- 当explain与SQL一起使用时,MySQL将显示来自优化器的有关语句执行计划的信息,即MySQL解释了它将如何处理该语句,包括有关如何连接表以及以何种顺序连接表等信息
- 一条简单SQL,使用了explain的效果如下:
- 需要重点关注type、rows、filtered、extra、key
-
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:全表扫描
-
rows
该列表示MySQL估算要找到我们所需的记录,需要读取的行数,对于InnoDB表,此数字是估计值,并非一定是个准确值 -
filtered
该列是一个百分比的值,表里符合条件的记录数的百分比,简单点说,这个字段表示存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例 -
extra
该字段包含有关MySQL如何解析查询的其他信息,它一般会出现这几个值:- Using filesort:表示按文件排序,一般是在指定的排序和索引排序不一致的情况才会出现,一般见于order by语句
- Using index :表示是否用了覆盖索引
- Using temporary: 表示是否使用了临时表,性能特别差,需要重点优化,一般多见于group by语句,或者union语句
- Using where : 表示使用了where条件过滤
- Using index condition:MySQL5.6之后新增的索引下推,在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据减少回表的数据
-
key
该列表示实际用到的索引,一般配合possible_keys列一起看
-
profile 分析执行耗时
- explain只是看到SQL的预估执行计划,如果要了解SQL真正的执行线程状态及消耗的时间,需要使用profiling,开启profiling参数后,后续执行的SQL语句都会记录其资源开销,包括IO,上下文切换,CPU,内存等等,可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化
- profiling默认是关闭,使用
show variables like '%profil%'
查看是否开启,如下:
- 使用
set profiling=ON
开启,开启后,可以运行几条SQL,然后使用show profiles查看一下
- show profiles会显示最近发给服务器的多条语句,条数由变量profiling_history_size定义,默认是15;如果需要看单独某条SQL的分析,可以show profile查看最近一条SQL的分析,也可以使用show profile for query id(其中id就是show profiles中的QUERY_ID)查看具体一条的SQL语句分析
- profile 查看CPU、IO:https://blog.csdn.net/weixin_35049095/article/details/113452138
Optimizer Trace分析详情
- profile只能查看到SQL的执行耗时,但无法看到SQL真正执行的过程信息,不知道MySQL优化器是如何选择执行计划,这时候,可以使用Optimizer Trace,它可以跟踪执行语句的解析优化执行的全过程
- 使用
set optimizer_trace="enabled=on"
打开开关,接着执行要跟踪的SQL,最后执行select * from information_schema.optimizer_trace
跟踪
- 分析其执行树,会包括三个阶段:
- join_preparation:准备阶段
- join_optimization:分析阶段
- join_execution:执行阶段
确定问题并采用相应的措施
- 多数慢SQL都跟索引有关,比如不加索引,索引不生效、不合理等,这时候,可以优化索引
- 还可以优化SQL语句,比如一些in元素过多问题(分批),深分页问题(基于上一次数据过滤等),进行时间分段查询
- SQL没办法很好优化,可以改用ES的方式,或者数仓
- 如果单表数据量过大导致慢查询,可以考虑分库分表
- 如果数据库在刷脏页导致慢查询,考虑是否可以优化一些参数
- 如果存量数据量太大,考虑是否可以让部分数据归档
转载:https://blog.csdn.net/qq_41956014/article/details/127722364
查看评论