首先查看目前Mysql慢查詢的參數(shù)情況
mysql> show variables like '%slow%';
+---------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------+------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/i-apsl772j-slow.log |
+---------------------------+------------------------------------+
5 行于數(shù)據(jù)集 (0.22 秒)
slow_query_log -- 是否開啟的慢查詢?nèi)罩居涗?slow_query_log_file -- 慢查詢?nèi)罩镜奈募恢?如果 slow_query_log 是 OFF ，可以通過如下命令來開啟慢查詢?nèi)罩?
mysql> set global slow_query_log = ON;
Query OK, 0 rows affected (0.02 秒)
下面來看一下慢查詢的時(shí)間，執(zhí)行如下命令;同時(shí)可以通過如下命令來設(shè)置慢查詢的時(shí)間;
mysql> show variables like '%long_query%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 行于數(shù)據(jù)集 (0.03 秒)
mysql> set long_query_time=2;
Query OK, 0 rows affected (0.02 秒)
下面來執(zhí)行一個(gè)比較復(fù)雜的查詢，來看一下是否能記錄到慢查詢?nèi)罩局?下面只是一個(gè)例子，可以根據(jù)實(shí)際情況來寫,命令執(zhí)
行完成后打開上面慢查詢的日志文件?？梢钥吹铰樵円呀?jīng)記錄了新的記錄。
select * from xhy_device_log xl,xhy_device xd where xl.mcode = xd.mcode;
[root@i-apsl772j ~]# cat /var/lib/mysql/i-apsl772j-slow.log
/usr/sbin/mysqld, Version: 5.7.10 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2016-10-12T04:31:48.245466Z
# User@Host: root[root] @ [125.33.118.101] Id: 886963
# Query_time: 30.872068 Lock_time: 0.000198 Rows_sent: 65032 Rows_examined: 121726
use xunheyun_device;
SET timestamp=1476246708;
select * from xhy_device_log xl,xhy_device xd where xl.mcode = xd.mcode;
上面介紹了如何記錄慢查詢的日志，稍后會(huì)介紹如何對(duì) SQL 進(jìn)行優(yōu)化。本文接下來再寫一些慢查詢相關(guān)的內(nèi)容；
查看文件輸入方式;
mysql> show variables like '%output%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| log_output | FILE |
+----------------------------+-------+
3 行于數(shù)據(jù)集 (0.05 秒)
