mysql 慢查询日志

查看慢查询路径

mysql> show variables like 'slow_query%';\
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/4dc8e477a0de-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)

查看慢查询时间分界：

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

开启慢查询日志：

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show  variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/4dc8e477a0de-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

修改慢查询分解值：

mysql> set long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

查看日志内容：

root@4dc8e477a0de:/var/lib/mysql# tail  4dc8e477a0de-slow.log -n20
# Time: 2018-05-09T06:03:12.250836Z
# User@Host: root[root] @  [112.25.235.194]  Id:  3247
# Query_time: 18.434085  Lock_time: 0.000780 Rows_sent: 1  Rows_examined: 9008904
SET timestamp=1525845792;
select
        (SELECT
        IFNULL(sum(t_record.value),0)
        FROM
        t_record
        where t_record.is_delete=FALSE

        )
        +
        (SELECT
        IFNULL(sum(t_receive_record.value),0)
        FROM
        t_receive_record
        where t_receive_record.is_delete=FALSE

        );