MySQL之查询截取分析（慢查询日志等）

最新推荐文章于 2024-05-01 16:21:51 发布

Jeremy_Lee123

最新推荐文章于 2024-05-01 16:21:51 发布

阅读量761

点赞数 1

分类专栏： MySQL高级文章标签：查询截取分析

本文链接：https://blog.csdn.net/lixinkuan328/article/details/103002781

版权

MySQL高级专栏收录该内容

27 篇文章 7 订阅

订阅专栏

一、慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

默认关闭：SHOW VARIABLES LIKE '%slow_query_log%';

1、临时生效：set global slow_query_log=1;（只对当前数据库生效，如果MySQL重启后则会失效）

2、全局变量设置，但对当前连接不影响

对当前连接立刻生效

3、永久生效，修改配置文件my.cnf（其它系统变量也是如此）

修改my.cnf文件，[mysqld]下增加或修改参数
slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件

    slow_query_log =1
    slow_query_log_file=/var/lib/mysql/atguigu-slow.log

关于慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

常用Case：

查看当前多少秒算慢：SHOW VARIABLES LIKE 'long_query_time%';
设置慢的阙值时间：set global long_query_time=1；set session long_query_time=1
记录慢SQL并后续分析：实验一条慢sql ；跟踪日志信息
查询当前系统中有多少条慢查询记录：show global status like '%Slow_queries%';

配置版：

【mysqld】下配置： //my.cnf 或者my.ini
slow_query_log=1; 
slow_query_log_file=/var/lib/mysql/atguigu-slow.log 
long_query_time=3; 
log_output=FILE

二、日志分析工具mysqldumpslow（重点）

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

查看帮助信息：mysqldumpslow --help

-s: 是表示按照何种方式排序；
    c: 访问次数
    l: 锁定时间
    r: 返回记录
    t: 查询时间
    al:平均锁定时间
    ar:平均返回记录数
    at:平均查询时间
-t:即为返回前面多少条的数据；
-g:后边搭配一个正则匹配模式，大小写不敏感的；

执行命令：mysqldumpslow /var/lib/mysql/mysql-slow.lo

得到的信息解释:

主要功能是, 统计不同慢sql的
出现次数(Count),
执行最长时间(Time),
累计总耗费时间(Time),
等待锁的时间(Lock),
发送给客户端的行总数(Rows),
扫描的行总数(Rows),
用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).

虽然是自带的工具，但是，这些信息没有提示是哪个数据库报的，一般一台服务器有很多数据库，这样根本看不出来。

建议使用mysqlsla 分析慢查询日志：https://www.jianshu.com/p/3c829890d9ed

三、Show Profile

1、是否支持，看看当前的mysql版本是否支持：Show variables like 'profiling';

2、开启功能，默认是关闭，使用前需要开启： set profiling=on;

3、运行SQL

select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5；

4、查看结果： show profile;

5、诊断SQL，show profile cpu,block io for query [query_id]; query_id为上一步前面的问题SQL数字号码

6. 日常开发需要注意的结论

Creating tmp table 创建临时表
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
locked 被锁住

四、Show Processlist

Show Processlist显示哪些线程正在运行，即查询所有连接该数据库的用户在干什么

不在mysql提示符下使用时用 mysql -uroot -e 'Show processlist' 或者 mysqladmin processlist
删除可疑用户： kill [id]

如果您有root权限，您可以看到所有线程。否则，您只能看到登录的用户自己的线程，通常只会显示100条如果想看跟多的可以使用full修饰（show full processlist）

首先是几条常用的SQL。

1、按客户端 IP 分组，看哪个客户端的链接数最多

select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist) as connect_info group by client_ip order by client_num desc;

2、查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

3、找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀（此处 5分钟可根据自己的需要调整SQL标红处）

可复制查询结果到控制台，直接执行，杀死堵塞进程

select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

4、查询线程及相关信息

show full processlist

ID 为此线程ID，Time为线程运行时间，Info为此线程SQL

详细说明如下：

Id: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。
User: 就是指启动这个线程的用户。
Host: 记录了发送请求的客户端的 IP 和端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
Command: 是指此刻该线程正在执行的命令。这个很复杂，下面单独解释
Time: 表示该线程处于当前状态的时间。
State: 线程的状态，和 Command 对应，下面单独解释。
Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

下面我们单独看一下 Command 的值：

Binlog Dump: 主节点正在将二进制日志，同步到从节点
Change User: 正在执行一个 change-user 的操作
Close Stmt: 正在关闭一个Prepared Statement 对象
Connect: 一个从节点连上了主节点
Connect Out: 一个从节点正在连主节点
Create DB: 正在执行一个create-database 的操作
Daemon: 服务器内部线程，而不是来自客户端的链接
Debug: 线程正在生成调试信息
Delayed Insert: 该线程是一个延迟插入的处理程序
Drop DB: 正在执行一个 drop-database 的操作
Execute: 正在执行一个 Prepared Statement
Fetch: 正在从Prepared Statement 中获取执行结果
Field List: 正在获取表的列信息
Init DB: 该线程正在选取一个默认的数据库
Kill : 正在执行 kill 语句，杀死指定线程
Long Data: 正在从Prepared Statement 中检索 long data
Ping: 正在处理 server-ping 的请求
Prepare: 该线程正在准备一个 Prepared Statement
ProcessList: 该线程正在生成服务器线程相关信息
Query: 该线程正在执行一个语句
Quit: 该线程正在退出
Refresh：该线程正在刷表，日志或缓存；或者在重置状态变量，或者在复制服务器信息
Register Slave：正在注册从节点
Reset Stmt: 正在重置 prepared statement
Set Option: 正在设置或重置客户端的 statement-execution 选项
Shutdown: 正在关闭服务器
Sleep: 正在等待客户端向它发送执行语句
Statistics: 该线程正在生成 server-status 信息
Table Dump: 正在发送表的内容到从服务器
Time: Unused

如果有歧义可以查看官方文档： 8.14.1 Thread Command Values