MySQL 开启慢查询日志

MySQL 直接通过命令即可开启慢查询日志。

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

通过上面两句命令便可以开启慢查询了。

通过 slow_log 表可以获取慢查询的具体情况:

select * from mysql.slow_log order by start_time desc;

slow_log 的表结构

CREATE TABLE `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COMMENT='Slow log'

查看 slow_log 相关配置

show variables like 'slow_query_log%';

结果是

+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/hostname-slow.log     |
+---------------------+--------------------------------------+

然后我们也可以从 slow_query_log_file 读取和分析慢查询的具体情况