您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

mysql慢查询语句分析总结

时间:2020-09-12 10:14:14  来源:  作者:

我们经常会接触到MySQL,也经常会遇到一些MySQL的性能问题。我们可以借助慢查询日志和explain命令初步分析出SQL语句存在的性能问题

通过SHOW FULL PROCESSLIST查看问题

SHOW FULL PROCESSLIST相当于select * from information_schema.processlist可以列出正在运行的连接线程,

mysql慢查询语句分析总结

processlist

说明:

  • id 连接id,可以使用kill+连接id的方式关闭连接(kill 9339)
  • user显示当前用户
  • host显示连接的客户端IP和端口
  • db显示进程连接的数据库
  • command显示当前连接的当前执行的状态,sleep、query、connect
  • time显示当前状态持续的时间(秒)
  • state显示当前连接的sql语句的执行状态,copying to tmp table、sorting result、sending data等
  • info显示sql语句,如果发现比较耗时的语句可以复制出来使用explain分析。

慢查询日志

慢查询日志是MySQL用于记录响应时间超过设置阈值(long_query_time)的SQL语句,默认情况下未开启慢查询日志,需要手动配置。
下面我们要记住几个常用的属性:

  • slow_query_log:是否开启慢查询(ON为开启,OFF则为关闭)
  • long_query_time:慢查询阀值,表示SQL语句执行时间超过这个值就会记录,默认为10s
  • slow_query_log_file:慢查询日志存储的文件路径
  • log_queries_not_using_indexes: 记录没有使用索引查询语句(ON为开启,OFF为关闭)
  • log_output:日志存储方式(FILE表示将日志写入文件,TABLE表示写入数据库中,默认值为FILE,如果存入数据库中,我们可以通过select * from mysql.slow_log的方式去查询,一般性能要求相对较高的建议存文件)

我们可以通过show variables like ‘%关键字%’的方式查询我们设置的属性值

mysql慢查询语句分析总结

slow


我们有两种方式设置我们的属性,一种是set global 属性=值的方式(重启失效),另一种是配置文件(重启生效)
命令方式:

 

set global slow_query_log=1;
set global long_query_time=1; 
set global slow_query_log_file='mysql-slow.log'

配置文件方式:

 

slow_query_log = 'ON'
slow_query_log_file = D:/Tools/mysql-8.0.16/slow.log
long_query_time = 1
log-queries-not-using-indexes

pt-qurey-digest分析慢查询语句

percona-toolkit包含了很多实用强大的mysql工具包,pt-qurey-digest只是其中一个用于分析慢查询日志是工具。需要去官网下载,使用方法也很简单:

 

./pt-query-digest slow2.log >> slow2.txt

即可得出一个分析结果:

 

# Query 9: 0.00 QPS, 0.00x concurrency, ID 0xF914D8CC2938CE6CAA13F8E57DF04B2F at byte 499246
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.22
# Time range: 2019-07-08T03:56:12 to 2019-07-12T00:46:28
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          8      69
# Exec time      1    147s      1s      3s      2s      3s   685ms      2s
# Lock time      0   140ms     2ms    22ms     2ms     3ms     2ms     2ms
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  23.96M 225.33k 482.77k 355.65k 462.39k  81.66k 345.04k
# Query size     2  17.72k     263     263     263     263       0     263
# String:
# Databases    xxxx
# Hosts        xx.xxx.xxx.xxx
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `xxxx` LIKE 'xxxxx_track_exec_channel'G
#    SHOW CREATE TABLE `xxxx`.`xxxxxxxx_exec_channel`G
#    SHOW TABLE STATUS FROM `xxx` LIKE 'xxxxx_TRACK_ASSIGN'G
#    SHOW CREATE TABLE `xxxx`.`xxxxx_EFFECTIVE_TRACK_ASSIGN`G
#    SHOW TABLE STATUS FROM `xxx` LIKE 'xxxx_task_exec'G
#    SHOW CREATE TABLE `xxxx`.`xxxxx_task_exec`G
UPDATExxxxxx_effective_track_exec_channel a 
SET EXEC_CHANNEL_CODE=(SELECT GROUP_CONCAT(DISTINCT(channel_id)) FROM xxxxxx_EFFECTIVE_TRACK_ASSIGN WHERE status in (1,2,4) AND id IN (SELECT assgin_id FROM xxxxxx_task_exec WHERE task_id=a.task_id))G

explain分析SQL语句

上面几点大概的介绍到了几种获取慢查询SQL语句的方式,现在,我们就需要借助explain来分析查找SQL语句慢的原因。explain使用也很简单,直接在SELECT|UPDATE等语句前加上EXPLAIN即可

mysql慢查询语句分析总结

 

explain

 

id

表的执行顺序,复制的sql语句往往会分为很多步,序号越大越先执行,id相同执行顺序从上往下

select_type

数据读取操作的操作类型:

  • SIMPLE(简单SELECT,不使用UNION或子查询等)
  • PRIMARY(子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
  • UNION(UNION中的第二个或后面的SELECT语句)
  • DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)
  • UNION RESULT(UNION的结果,union语句中第二个select开始后面所有select)
  • SUBQUERY(子查询中的第一个SELECT,结果不依赖于外部查询)
  • DEPENDENT SUBQUERY(子查询中的第一个SELECT,依赖于外部查询)
  • DERIVED(派生表的SELECT, FROM子句的子查询)
  • UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)

table

数据来源于那张表,关联等复杂查询时会用临时虚拟表

type

检索数据的方式

  • system:表只有一行记录
  • const:通过索引查找并且一次性找到
  • eq_ref:唯一性索引扫描
  • ref:非唯一行索引扫描
  • range:按范围查找
  • index:遍历索引树
  • all:全表扫描

possible_keys

显示可能使用的索引

Key

实际使用的索引

key_len

索引的长度,一般来说,长度越短越好

ref

列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

rows

估算查找的结果记录条数

Extra

SQL查询的详细信息

  • Using where:表示使用where条件过滤
  • Using temporary:使用了临时表暂存结果
  • Using filesort:说明mysql对数据使用一个外部索引排序。未按照表内的索引顺序进行读取。
  • Using index:表示select语句中使用了覆盖索引,直接从索引中取值
  • Using join buffer:使用了连接缓存
  • Using index condition:表示查询的列有非索引的列

[参考]
MySQL Explain详解



Tags:慢查询   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
作者介绍钱芳园,专注数据库和数据库自动化领域的工程师,擅长MySQL、Redis运维以及基于go语言的数据库自动化开发。 一、背景 所谓 MySQL 慢查询,是指在 MySQL 中执行时间超过指...【详细内容】
2021-10-11  Tags: 慢查询  点击:(36)  评论:(0)  加入收藏
慢查询日志概念 MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询...【详细内容】
2021-02-07  Tags: 慢查询  点击:(143)  评论:(0)  加入收藏
我们经常会接触到MySQL,也经常会遇到一些MySQL的性能问题。我们可以借助慢查询日志和explain命令初步分析出SQL语句存在的性能问题通过SHOW FULL PROCESSLIST查看问题SHOW FU...【详细内容】
2020-09-12  Tags: 慢查询  点击:(97)  评论:(0)  加入收藏
MySQL慢查询日志是我们在日常工作中经常会遇到的一个功能,MySQL慢查询日志提供了超过指定时间阈值的查询信息,为性能优化提供了主要的参考依据,是一个非常实用的功能,MySQL慢查...【详细内容】
2020-07-10  Tags: 慢查询  点击:(52)  评论:(0)  加入收藏
相信很多小伙伴都见过一些商业产品中的url接口响应时间,实时汇总显示功能。可以理解为web接口的慢查询,与sql的慢查询有异曲同工之妙,但是想做却无从入手不知道怎么实现此功能,...【详细内容】
2020-05-29  Tags: 慢查询  点击:(49)  评论:(0)  加入收藏
概述今天主要介绍如何用slowquery工具来配置邮件告警,仅供参考。1、修改邮件配置进入到slowquery/alarm_mail/目录里,修改sendmail.php配置信息。<?php$get_mail_content = ge...【详细内容】
2019-12-04  Tags: 慢查询  点击:(141)  评论:(0)  加入收藏
慢查询,大家可能已经接触到了MySQL的慢查询。我们配置一个时间,如果查询时间超过了我们设置的时间,我们就认为这是一个慢查询. 如上图所示:Redis客户端一条命令执行分4个步骤: 发...【详细内容】
2019-11-29  Tags: 慢查询  点击:(110)  评论:(0)  加入收藏
一、问题背景现网出现慢查询,在500万数量级的情况下,单表查询速度在30多秒,需要对sql进行优化,sql如下: 我在测试环境构造了500万条数据,模拟了这个慢查询。简单来说,就是查询一定...【详细内容】
2019-09-20  Tags: 慢查询  点击:(268)  评论:(0)  加入收藏
Redis数据库是一个基于内存的 key-value存储系统,现在redis最常用的使用场景就是存储缓存用的数据,在需要高速读/写的场合使用它快速读/写,从而缓解应用数据库的压力,进而提升应...【详细内容】
2019-09-18  Tags: 慢查询  点击:(184)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(20)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条