一、开启查询日志
SHOW VARIABLES LIKE '%slow_query_log%';
#查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.10 sec)
SET GLOBAL slow_query_log=1;
#开启慢查询日志
SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
#查看慢查询时间阈值
mysql> SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
SET GLOBAL long_query_time=3;
#设置慢查询时间阈值
二、查看慢查询
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
#查看超过阈值的SQL语句数量
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
1 row in set (0.00 sec)
三、使用日志分析工具
#进入mysql安装路径bin目录下
./mysqldumpslow --help
#查看帮助信息
实例：
mysqldumpslow -s r -t 10 slow.log
#返回记录集最多的10个SQL
mysqldumpslow -s c -t 10 slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s t -t 10 -g "left join" slow.log
#得到按时间排序的前10条里面含有左连接的SQL