MySQL性能优化（四）-慢查询

2020-05-28 00:00:00 查询 语句 执行 时间 开启

1.慢查询的用途

它能记录下所有执行超过longquerytime时间的SQL语句，帮我们找到执行慢的SQL，方便我们对这些SQL进行优化。

2.查看是否开启慢查询

show variables like 'slow_query%%';

  • slowquerylog = off，表示没有开启慢查询

  • slowquerylog_file 表示慢查询日志存放的目录

3.开启慢查询（需要的时候才开启，因为很耗性能，建议使用即时性的）

方式一：（即时性的，重启mysql之后失效，常用的）

set global slowquerylog=1; 或者 set global slowquerylog=ON;

开启之后 我们会发现 /var/lib/mysql下已经存在 localhost-slow.log了，未开启的时候默认是不存在的。

方式二：（性的）

在/etc/my.cfg文件中的[mysqld]中加入：

    

  1. slow_query_log=ON
    2. 

  2. slow_query_log_file=/var/lib/mysql/localhost-slow.log
    3.

4.设置慢查询记录的时间

查询慢查询记录的时间：show variables like 'longquery%%'，默认是10秒钟，意思是大于10秒才算慢查询。我们现在设置慢查询记录时间为1秒：set longquery_time=1;

5.执行select count(1) from order o where o.user_id in (select u.id where users);

因为我们开启了慢查询，且设置了超过1秒钟的就为慢查询，此sql执行了24秒，所以属于慢查询。

我们在日志中查看：

more /var/lib/mysql/localhost-slow.log，

我们可以看到查询的时间，用户，花费的时间，使用的数据库，执行的sql语句等信息。在生产上我们就可以使用这种方式来查看 执行慢的sql。

6.查询慢查询的次数：show status like 'slow_queries';

在我们重新执行刚刚的查询sql后，查询慢查询的次数会变为8当然，用 more /var/lib/mysql/localhost-slow.log 也是可以看到详细结果的。

在生产中，我们会分析查询频率高的，且是慢查询的sql，并不是每一条查询慢的sql都需要分析。

7.慢查询日志分析工具Mysqldumpslow

由于在生产上会有很多慢查询，所以采用上述的方法查看慢查询sql会很麻烦，还好MySQL提供了慢查询日志分析工具Mysqldumpslow。

其功能是, 统计不同慢sql的出现次数(Count)，执行长时间(Time)，累计总耗费时间(Time)，等待锁的时间(Lock)，发送给客户端的行总数(Rows)，扫描的行总数(Rows)

（1）查询Mysqldumpslow的帮助信息，随便进入一个文件夹下，执行：mysqldumpslow --help

查看mysqldumpslow命令安装在哪个目录：whereis mysqldumpslow说明：

  • -s，是order的顺序，主要有c（按query次数排序）、t（按查询时间排序）、l（按lock的时间排序）、r （按返回的记录数排序）和 at、al、ar，前面加了a的代表平均数

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

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

  • -r：倒序

（2）案例：取出耗时长的两条sql

格式：mysqldumpslow -s t -t 2 慢日志文件

mysqldumpslow -s t -t 2 /var/lib/mysql/localhost-slow.log

参数分析：

  • 出现次数(Count),

  • 执行长时间(Time),

  • 累计总耗费时间(Time),

  • 等待锁的时间(Lock),

  • 发送给客户端的行总数(Rows),

  • 扫描的行总数(Rows),

  • 用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).

（3）案例：取出查询次数多，且使用了in关键字的1条sql

mysqldumpslow -s c -t 1 -g 'in' /var/lib/mysql/localhost-slow.log这种方式更加方便，更加快捷！

8.show profile

用途：用于分析当前会话中语句执行的资源消耗情况

（1）查看是否开启profile，mysql默认是不开启的，因为开启很耗性能

show variables like 'profiling%%';（2）开启profile（会话级别的，关闭当前会话就会恢复原来的关闭状态）

set profiling=1; 或者 set profiling=ON;

（3）关闭profile

set profiling=0; 或者 set profiling=OFF;

（4）显示当前执行的语句和时间

show profiles;

（5）显示当前查询语句执行的时间和系统资源消耗

show profile cpu,block io for query 4;（分析show profiles中query_id等于4的sql所占的CPU资源和IO操作）

或者直接 ：show profile for query 4;




相关文章