mysql 慢sql

怼烎@ 2023-10-11 19:01 71阅读 0赞

### 如何查找MySQL中查询慢的SQL语句 ###

一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句1，slow\_query\_log这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。2，long\_query\_time当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。3，slow\_query\_log\_file记录日志的文件名。4，log\_queries\_not\_using\_indexes这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。二、检测mysql中sql语句的效率的方法1、通过查询日志(1)、Windows下开启MySQL慢查询MySQL在Windows系统中的配置文件一般是是my.ini找到\[mysqld\]下面加上代码如下log-slow-queries=F:/MySQL/log/mysqlslowquery。loglong\_query\_time=2(2)、Linux下启用MySQL慢查询MySQL在Windows系统中的配置文件一般是是my.cnf找到\[mysqld\]下面加上代码如下log-slow-queries=/data/mysqldata/slowquery。loglong\_query\_time=2说明log-slow-queries=F:/MySQL/log/mysqlslowquery。为慢查询日志存放的位置，一般这个目录要有MySQL的运行帐号的可写权限，一般都将这个目录设置为MySQL的数据存放目录；long\_query\_time=2中的2表示查询超过两秒才记录；

本回答由提问者推荐

### 如何实时监控mysql中的SQL语句的执行情况 ###

首先介绍下pt-stalk，它是Percona-Toolkit工具包中的一个工具，说起PT工具包大家都不陌生，平时常用的pt-query-digest、pt-online-schema-change等工具都是出自于这个工具包，这里就不多介绍了。

pt-stalk的主要功能是在出现问题时收集OS及MySQL的诊断信息，这其中包括：

1.OS层面的CPU、IO、内存、磁盘、网络等信息；

2.MySQL层面的行锁等待、会话连接、主从复制，状态参数等信息。

而且pt-stalk是一个Shell脚本，对于我这种看不懂perl的人来说比较友好，脚本里面的监控逻辑与监控命令也可以拿来参考，用于构建自己的监控体系。

接着我们来看下如何使用这个工具。

pt-stalk通常以后台服务形式监控MySQL并等待触发条件，当触发条件时收集相关诊断数据。

触发条件相关的参数有以下几个：

function：

∘默认为status，代表监控SHOWGLOBALSTATUS的输出；

∘也可以设置为processlist，代表监控showprocesslist的输出；

variable：

∘默认为Threads\_running，代表监控参数，根据上述监控输出指定具体的监控项；

threshold：

∘默认为25，代表监控阈值，监控参数超过阈值，则满足触发条件；

∘监控参数的值非数字时，需要配合match参数一起使用，如processlist的state列；

cycles：

∘默认为5，表示连续观察到五次满足触发条件时，才触发收集；

连接参数：host、password、port、socket。

其他一些重要参数：

iterations：该参数指定pt-stalk在触发收集几次后退出，默认会一直运行。

run-time：触发收集后，该参数指定收集多长时间的数据，默认30秒。

sleep：该参数指定在触发收集后，sleep多久后继续监控，默认300秒。

interval：指定状态参数的检查频率，判断是否需要触发收集，默认1秒。

dest：监控数据存放路径，默认为/var/lib/pt-stalk。

retention-time：监控数据保留时长，默认30天。

daemonize：以后台服务运行，默认不开启。

log：后台运行日志，默认为/var/log/pt-stalk.log。

collect：触发发生时收集诊断数据，默认开启。

∘collect-gdb：收集GDB堆栈跟踪，需要gdb工具。

∘collect-strace：收集跟踪数据，需要strace工具。

∘collect-tcpdump：收集tcpdump数据，需要tcpdump工具。

### sql和mysql ###

MYSQL比SQL SERVER好，简单来说，MYSQL是开放的且有伸缩性，而SQL SERVER是保守的，限制较多，普遍应用性不比前者。YAHOO站点也是用的MYSQL，你可以选择它。MYSQL同样适用于WINDOWS平台。

### sql和mysql ###

### 如何开启mysql的慢查询 ###

1，配置开启

Linux:

在mysql配置文件my.cnf中增加

log-slow-queries=/var/lib/mysql/slowquery.log (指定日志文件存放位置，可以为空，系统会给一个缺省的文件host\_name-slow.log)

long\_query\_time=2 (记录超过的时间，默认为10s)

log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)

log-long-format (如果设置了，所有没有使用索引的查询也将被记录)

Windows:

在my.ini的\[mysqld\]添加如下语句：

log-slow-queries = E:\\web\\mysql\\log\\mysqlslowquery.log

long\_query\_time = 2(其他参数如上)

2,查看方式

Linux:

使用mysql自带命令mysqldumpslow查看

\-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at’ is default

\-t NUM just show the top n queries

\-g PATTERN grep: only consider stmts that include this strings，是order的顺序，说明写的不够详细，包括看了代码，主要有 c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒序 -t，是top n的意思，即为返回前面多少条的数据 -g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log

mysqldumpslow -s r -t 20 host-slow.log上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

mysqldumpslow -t 10 -s t -g “left join” host-slow.log这个是按照时间返回前10条里面含有左连接的sql语句。开启mysql慢查询日志查看配置：

//查看慢查询时间

show variables like "long\_query\_time";默认10s

//查看慢查询配置情况

show status like "%slow\_queries%";

//查看慢查询日志路径

show variables like "%slow%";

修改配置文件

在my.ini中加上下面两句话

log-slow-queries = d:\\wamp\\mysql\_slow\_query.log

long\_query\_time=5

第一句使用来定义慢查询日志的路径(因为是windows，所以不牵涉权限问题)

第二句使用来定义查过多少秒的查询算是慢查询，我这里定义的是5秒

第二步：查看关于慢查询的状态

执行如下sql语句来查看mysql慢查询的状态

show variables like '%slow%';

执行结果会把是否开启慢查询、慢查询的秒数、慢查询日志等信息打印在屏幕上。

第三步：执行一次慢查询操作

其实想要执行一次有实际意义的慢查询比较困难，因为在自己测试的时候，就算查询有20万条数据的海量表，也只需要0.几秒。我们可以通过如下语句代替：

select sleep(10);

第四步：查看慢查询的数量

通过如下sql语句，来查看一共执行过几次慢查询：

show global status like '%slow%';

mysql日志的配置：

注意：这些日文件在mysql重启的时候才会生成

\#记录所有sql语句

log=e:/mysqllog/mysql.log

\#记录数据库启动关闭信息，以及运行过程中产生的错误信息

log-error=e:/mysqllog/myerror.log

\# 记录除select语句之外的所有sql语句到日志中，可以用来恢复数据文件

log-bin=e:/mysqllog/bin

\#记录查询慢的sql语句

log-slow-queries=e:/mysqllog/slow.log

\#慢查询时间

long\_query\_time=0.5

### 开启mysql慢查询 的话 对系统性能影响大么 ###

mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句,那如何打开mysql的慢查询日志记录呢?

其实打开mysql的慢查询日志很简单,只需要在mysql的配置文件里(windows系统是myf)的\[mysqld\]下面加上如下代码：

复制代码 代码如下:

log-slow-queries=/var/lib/mysql/slowqueryf或my.ini中加入下面这一行可以记录这些查询

这是一个有用的日志。它对于性能的影响不大(假设所有查询都很快)，并且强调了那些最需要注意的查询(丢失了索引或索引没有得到最佳应用)

\# Time: 070927 8:08:52

\# User@Host: root\[root\] @ \[192.168.0.20\]

\# Query\_time: 372 Lock\_time: 136 Rows\_sent: 152 Rows\_examined: 263630

select id, name from manager where id in (66,10135);

这是慢查询日志中的一条，用了372秒，锁了136秒，返回152行，一共查了263630行

如果日志内容很多，用眼睛一条一条去看会累死，mysql自带了分析的工具，使用方法如下：

命令行下，进入mysql/bin目录，输入mysqldumpslow –help或--help可以看到这个工具的参数，主要有

Usage: mysqldumpslow \[ OPTS... \] \[ LOGS... \]

Parse and summarize the MySQL slow query log. Options are

\--verbose verbose

\--debug debug

\--help write this text to standard output

\-v verbose

\-d debug

\-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default

\-r reverse the sort order (largest last instead of first)

\-t NUM just show the top n queries

\-a don't abstract all numbers to N and strings to 'S'

\-n NUM abstract numbers with at least n digits within names

\-g PATTERN grep: only consider stmts that include this string

\-h HOSTNAME hostname of db server for \*-slow.log filename (can be wildcard),

default is '\*', i.e. match all

\-i NAME name of server instance (if using mysql.server startup scrīpt)

\-l don't subtract lock time from total time

\-s，是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有

c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒叙

\-t，是top n的意思，即为返回前面多少条的数据

\-g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log

mysqldumpslow -s r -t 20 host-slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

mysqldumpslow -t 10 -s t -g “left join” host-slow.log

这个是按照时间返回前10条里面含有左连接的sql语句。1，配置开启

linux:

在mysql配置文件my.cnf中增加

log-slow-queries=/var/lib/mysql/slowquery.log (指定日志文件存放位置，可以为空，系统会给一个缺省的文件host\_name-slow.log)

long\_query\_time=2 (记录超过的时间，默认为10s)

log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)

log-long-format (如果设置了，所有没有使用索引的查询也将被记录)

windows:

在my.ini的\[mysqld\]添加如下语句：

log-slow-queries = e:\\web\\mysql\\log\\mysqlslowquery.log

long\_query\_time = 2(其他参数如上)

2,查看方式

linux:

使用mysql自带命令mysqldumpslow查看

\-s order what to sort by (t, at, l, al, r, ar etc), 'at’ is default

\-t num just show the top n queries

\-g pattern grep: only consider stmts that include this strings，是order的顺序，说明写的不够详细，包括看了代码，主要有 c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒序 -t，是top n的意思，即为返回前面多少条的数据 -g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log

mysqldumpslow -s r -t 20 host-slow.log上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

mysqldumpslow -t 10 -s t -g “left join” host-slow.log这个是按照时间返回前10条里面含有左连接的sql语句。开启慢查询对性能有一点点影响吧，但不是太大；

而且开启mysql 慢查询对优化sql性能很有帮助，比如可以从慢查询中找出哪类sql执行比较频繁且执行时间比较久、扫描的行数也比较大，可以针对这类sql进行优化(如优化sql、建立相关索引等)这是一个慢查询日志的展示工具，能够帮助 DBA 或者开发人员分析数据库的性能问题，给出全面的数据摆脱直接查看 slow-log。QAN(Query Analytics)

PMM 目前有 2 个版本，但是对于 QAN 来说其大致由三部分组成：

QAN-Agent(client)：负责采集 slow-log 的数据并上报到服务端

QAN-API(server)：负责存储采集的数据，并对外提供查询接口

QAN-APP：专门用来展示慢查询数据的 grafana 第三方插件

1. 数据流转

slow-log --> QAN-Agent --> QAN-API QAN-APP(grafana)

2. pmm1 架构图

3. pmm2 架构图