mysql开启慢查询日志和profile

野性酷女 2022-07-14 06:51 225阅读 0赞

mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句，以便后期跟踪优化。 mysqld在语句执行完并且所有锁释放后记入慢查询日志。

1.通过配置开启，需要重启mysql服务，但是持久配置

vi my.cnf

\-------------------------------------

slow\_query\_log = ON

slow\_query\_log\_file = /var/log/mysql\_slow\_querys.log

long\_query\_time = 3

log-queries-not-using-indexes

log-slow-admin-statements

2.通过修改mysql的全局变量，不需要重启服务，临时配置，重启配置丢失

set global slow\_query\_log=ON;

set global slow\_query\_log\_file="/var/log/mysql\_slow\_querys.log"

set global long\_query\_time=3;

set global log-queries-not-using-indexes=ON

set global log-slow-admin-statements=ON

3.查看配置情况

mysql>show variables like 'long%';

mysql>show variables like 'slow%';

4.参数说明

slow\_query\_log      ：  指定是否开启慢查询日志 ON|OFF (1|0)  缺省关闭

log\_slow\_queries    ：  指定是否开启慢查询日志(该参数要被slow\_query\_log取代，做兼容性保留)

slow\_query\_log\_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host\_name-slow.log

long\_query\_time    ：  设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，单位为妙，缺省值为10s

min\_examined\_row\_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志

log\_queries\_not\_using\_indexes: 不使用索引的慢查询日志是否记录到索引

log-slow-admin-statements : 开启记录管理型慢SQL。 缺省情况下数据库相关管理型SQL(比如OPTIMIZE TABLE、ANALYZE TABLE和ALTER TABLE)不会被记录到日志。

log\_throttle\_queries\_not\_using\_indexes:  每分钟允许记录到slow log的且未使用索引的SQL语句次数。该值默认为0，表示没有限制， 在生产环境下，若没有使用索引， 此类SQL语句会频繁地被记录到slow log，从而导致slow log文件的大小不断增加，故DBA可通过此参数进行配置。该参数 MySQL 5.6.5版本开始新增

用查询缓存处理的查询不加到慢查询日志中，表有零行或一行而不能从索引中受益的查询也不写入慢查询日志。

MySQL服务器按以下顺序记录SQL是否写入到慢查询日志

a. The query must either not be an administrative statement, or --log-slow-adminstatements must have been specified.

b. The query must have taken at least long\_query\_time seconds, or log\_queries\_not\_using\_indexes must be enabled and the query used no indexes for row lookups.

c. The query must have examined at least min\_examined\_row\_limit rows.

d. The query must not be suppressed according to the log\_throttle\_queries\_not\_using\_indexes setting.

5. 使用mysqldumpslow命令分析慢查询日志

可以使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志。

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

![Center][]

\-s，order的顺序，默认是at。 c,t,l,r和ac,at,al,ar，分别是按照query次数，查询时间，lock时间和返回的记录数来排序，前面加了a的时平均值

\-r  最大的放在最后

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

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

访问次数最多的20个sql语句

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

按照时间返回前10条里面含有左连接的sql语句。

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

按照最大耗用时间排最后，只显示2条

mysqldumpslow -r -t 2 /var/lib/mysql/suse11b-slow.log

除了，通过查询慢查询日志并分析，我们也可以通过 show profile来查看sql的执行情况：

1.查看 profiling是否开启

show variables like "%pro%";

开启： set profiling=1;

2.查询操作，并使用  show profiles查看执行的sql

![Center 1][]

![Center 2][]

![Center 3][]

也可以查看执行计划

EXPLAIN  SELECT ……

变体：

1. EXPLAIN EXTENDED SELECT ……

将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句

2. EXPLAIN PARTITIONS SELECT ……

用于分区表的EXPLAIN

[Center]: /images/20220714/17f841229a0244f2a54a9ed8edcb0017.png
[Center 1]: /images/20220714/49ebf83a66574e66bd81b46fa5445025.png
[Center 2]: /images/20220714/61a71ebe1e0e445b8fac3b1d38f2675e.png
[Center 3]: /images/20220714/d639ea30ba0b4a3c86313f392fc4eb46.png