Mysql数据库之慢查询
一、简介
开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
二、参数说明
slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录
三、设置步骤
1.查看慢查询相关参数
mysql> show variables like'slow_query%'; +---------------------------+----------------------------------+| Variable_name | Value |+--------------------------+----------------------------------+| slow_query_log | OFF ||
slow_query_log_file | /mysql/data/localhost-slow.log |+---------------------------+----------------------------------+
mysql> show variables like 'long_query_time';
+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+
2.设置方法
方法一：全局变量设置
将 slow_query_log 全局变量设置为“ON”状态
mysql> set global slow_query_log='ON';
设置慢查询日志存放的位置
mysql> set global slow_query_log_file='/var/lib/mysql/mysql_slow.log';
查询超过1秒就记录
mysql> set global long_query_time=1;
方法二：配置文件设置
修改配置文件my.cnf，在[mysqld]下的下方加入
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
3.重启MySQL服务
service mysqld restart
4.查看设置后的参数
mysql> show variables like'slow_query%'; +---------------------+--------------------------------+|
Variable_name | Value |+---------------------+--------------------------------+|
slow_query_log | ON ||
slow_query_log_file | /usr/local/mysql/data/slow.log |+---------------------+--------------------------------+
mysql> show variables like 'long_query_time';
+-----------------+----------+| Variable_name | Value |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+
四、测试
1.执行一条慢查询SQL语句
mysql> select sleep(2);
2.查看是否生成慢查询日志
ls /usr/local/mysql/data/slow.log
如果日志存在，MySQL开启慢查询设置成功！
mysqldumpslow命令
/path/mysqldumpslow -s c -t 10 /database/MySQL/slow-log
这会输出记录次数最多的10条SQL语句，其中：
-s，是order的顺序
al 平均锁定时间
ar 平均返回记录时间
at 平均查询时间（默认）
c 计数
l 锁定时间
r 返回记录
t 查询时间
-t，是top n的意思，即为返回前面多少条的数据
-g，后边可以写一个正则匹配模式，大小写不敏感的
比如
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回记录集最多的10个查询。
/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照时间排序的前10条里面含有左连接的查询语句。
不过mysqldumpslow有一个挺不爽的缺点，就是查询的结果只有一些抽象的SQL，没有可供实际运行的SQL例子。
主要功能是, 统计不同慢sql的
输出日志参数详解
出现次数(Count),
执行最长时间(Time),
累计总耗费时间(Time),
等待锁的时间(Lock),
发送给客户端的行总数(Rows),
扫描的行总数(Rows),
用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).
常见用法
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log
# 取出使用最多的10条慢查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log
# 取出查询时间最慢的3条慢查询(总)
mysqldumpslow -s at -t 100 mysql_slow.1708082350 >/08_08B.txt
#取出查询时间最慢的3条慢查询(单)
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log
# 按照扫描行数最多的
注意: 使用mysqldumpslow的分析结果不会显示具体完整的sql语句,只会显示sql的组成结构;
例子：
虽然这两条语句条件不一样，
1：一个是server_id=10,一个是server_id=20
2：一个是LIMIT 0, 1000,一个是LIMIT 10000, 1000
但是mysqldumpslow分析会认为这是一种类型的语句，会合并显示。
