一、慢查询日志(slow_log)

慢查询日志（slow log）可帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。例如，可以在MySQL启动时设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。DBA每天或每过一段时间对其进行检查，确认是否有SQL语句需要进行优化。

二、慢查询日志设置

1. 慢查询日志开启

//开启慢查询日志
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/ubuntu-slow.log |
+---------------------+--------------------------------+

2. 慢查询日志相关设置

(1) 设置sql语句超时时间

两点需要注意。首先，设置long_query_time这个阈值后，MySQL数据库会记录运行时间超过该值的所有SQL语句，但运行时间正好等于long_query_time的情况并不会被记录下。其次，从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行的时间，之前仅用秒为单位记录。

//设置sql语句超时时间
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

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

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

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

mysql> select @@global.long_query_time;
+--------------------------+
| @@global.long_query_time |
+--------------------------+
|                 1.000000 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select @@session.long_query_time;
+---------------------------+
| @@session.long_query_time |
+---------------------------+
|                  1.000000 |
+--------------------------
(2) 开启未使用索引查询的sql语句
//开启未使用索引查询的sql语句
mysql> set global log_queries_not_using_indexes=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

//每分钟允许记录到slow log的且未使用索引的SQL语句次数:0 无限制
mysql> show variables like 'log_throttle_queries_not_using_indexes';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+

三、使用慢查询日志

1. 创建一个测试表

//选择测试库
mysql> show databases;
mysql> use learn;

//创建表
 create table t ( 
 		id INT AUTO_INCREMENT , 
 		a INT,
 	 	PRIMARY KEY (id)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

//插入测试数据
INSERT INTO t (id,a) VALUES (1,1);
INSERT INTO t (id,a) VALUES (2,2);

//查看数据
mysql> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+

2. 相关操作

mysql> select * from t ;
mysql> select * from t where b=2;

//不按索引查询的sql语句
root@ubuntu:/var/lib/mysql# mysqldumpslow ubuntu-slow.log
Reading mysql slow query log from ubuntu-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=2.0 (2), root[root]@localhost
  select * from t

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select * from t where a=N

//执行时间最长的10条SQL语句
root@ubuntu:/var/lib/mysql# mysqldumpslow -s al -n 10 ubuntu-slow.log
......

2. 慢查询日志表:slow_log

慢查询的日志记录可以设置写入 mysql.slow_log 表中

(1) 查看mysql.slow_log 表信息
//mysql版本
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.30-0ubuntu0.18.04.1 |
+-------------------------+
1 row in set (0.00 sec)


//mysql.slow_log 表信息
mysql> SHOW CREATE TABLE mysql.slow_log;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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=utf8 COMMENT='Slow log' |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(2) 将慢查询输出格式由文件转化为表
mysql> SHOW VARIABLES LIKE'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

//测试
mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

mysql> SELECT * FROM mysql.slow_log;
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+------------------------------+-----------+
| start_time                 | user_host                 | query_time      | lock_time       | rows_sent | rows_examined | db    | last_insert_id | insert_id | server_id | sql_text                     | thread_id |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+------------------------------+-----------+
| 2020-07-17 16:01:31.610090 | root[root] @ localhost [] | 00:00:10.001169 | 00:00:00.000000 |         1 |             0 | learn |              0 |         0 |         0 | select sleep(10)             |         2 |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+------------------------------+-----------+
(3) 修改mysql.slow_log 表存储引擎,提高在大数据下查询效率
//slow_log表使用的是CSV引擎，对大数据量下的查询效率可能不高。用户可以把slow_log表的引擎转换到MyISAM，并在start_time列上添加索引以进一步提高查询的效率
//不能忽视的是，将slow_log表的存储引擎更改为MyISAM后，还是会对数据库造成额外的开销。不过好在很多关于慢查询的参数都是动态的，用户可以方便地在线进行设置或修改。
mysql> SET GLOBAL slow_query_log=off;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE mysql.slow_log ENGINE=MyISAM;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE mysql.slow_log;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log' |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Logo
一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

cover

byzer plugin install log

avatar Byzer 白泽

函数实现越通用越好？来看看 Byzer-LLM 的 Function Implementation 带来的编程思想大变化...

前言Function Calling 是 OpenAI 首先提出来的一个非常有用的功能，实现了大模型对函数的调用能力。Byzer-LLM 给开源模型也带来了 Function Calling 实现。在这个基础上，我们还拓展了 Respond With Class 功能，允许大模型输出标准的Python对象，进步控制了大模型的输出能力。这两个能力参看：给开源大模型带来Function Callin.

avatar Byzer 白泽
cover

OpenMLDB＋Byzer，SQL 也能玩转机器学习全流程

avatar Byzer 白泽