本文主要是介绍MySQL 学习心得和知识总结(五)|MySQL的一般查询日志(general log)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
目录结构
注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
1、参考书籍：《PostgreSQL数据库内核分析》
2、参考书籍：《数据库事务处理的艺术：事务管理与并发控制》
3、PostgreSQL数据库仓库链接，点击前往
4、日本著名PostgreSQL数据库专家 铃木启修 网站主页，点击前往
5、参考书籍：《PostgreSQL中文手册》
6、参考书籍：《PostgreSQL指南：内幕探索》，点击前往
7、参考书籍：《事务处理 概念与技术》
8、Linux安装odbc连接mysql，点击前往
1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享 抛砖引玉 一起学习
3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关
4、大家可以根据需要自行 复制粘贴以及作为其他个人用途，但是不允许转载 不允许商用 （写作不易，还请见谅 💖）
MySQL的一般查询日志 general log
- 文章快速说明索引
- 一般查询日志简单介绍
- 日志简介参数配置
- 文件生成内容分析
- odbc连接内容生成
- 一般查询日志性能影响
- 系统信息
- 禁用一般查询日志
- 启用一般查询日志
- 输出是文件
- 输出为表(CSV 表)
- 输出为表(MYISAM 表)
- 输出为表(MYISAM 表，结构发生一些变化)
- 结论
文章快速说明索引
学习目标：
做数据库内核开发久了就会有一种 少年得志，年少轻狂 的错觉，然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间 将着重于学习分享Postgres的基础知识和实践内幕。
学习内容：（详见目录）
1、MySQL的一般查询日志(general log)
学习时间：
2024-04-03 18:06:50 星期三
学习产出：
1、MySQL数据库基础知识回顾 1个
2、CSDN 技术博客 1篇
3、PostgreSQL数据库内核深入学习
注：下面我们所有的学习环境是Centos8+PostgreSQL16.1+Oracle19C+MySQL8.0
postgres=# select version();version
------------------------------------------------------------------------------------------------------------PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)postgres=##-----------------------------------------------------------------------------#SQL> select * from v$version; BANNER Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
BANNER_FULL Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
CON_ID 0#-----------------------------------------------------------------------------#mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.06 sec)mysql>
一般查询日志简单介绍
日志简介参数配置
查询日志分为一般查询日志和慢查询日志。
通过查询是否超出如下变量指定时间的值来判定的，例如：
# 指定慢查询超时时长，超出此时长的属于慢查询，会记录到慢查询日志中[root@127.0.0.1] mysql>show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)[root@127.0.0.1] mysql>
# 定义一般查询日志和慢查询日志的输出格式，不指定时默认为file# log_output={TABLE|FILE|NONE}
# TABLE：表示记录日志到表中
# FILE：表示记录日志到文件中
# NONE：表示不记录日志[root@127.0.0.1] mysql>show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.01 sec)[root@127.0.0.1] mysql>
在超时时间内完成的查询是一般查询，可以将其记录到一般查询日志中；而超出时间的查询是慢查询，可以将其记录到慢查询日志中。我们今天学习的重点在于一般查询日志，慢查询日志后面有机会详聊！
一般查询日志general log，记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来。
开启general log，MySQL服务器就会不断地记录日志，会产生一定的系统开销。因此所有都默认关闭一般查询日志。
开启general log，如下：
# 它是全局变量[root@127.0.0.1] mysql>show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.00 sec)[root@127.0.0.1] mysql>set global general_log=on;
Query OK, 0 rows affected (0.00 sec)[root@127.0.0.1] mysql>show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.01 sec)[root@127.0.0.1] mysql>
# 设置日志文件路径，默认是库文件路径下 主机名加上.log[root@127.0.0.1] mysql>show global variables like 'general_log_file';
+------------------+--------------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------------+
| general_log_file | /home/mysql/mysql-8.0.27/data/dbserver.log |
+------------------+--------------------------------------------+
1 row in set (0.00 sec)[root@127.0.0.1] mysql>set global general_log_file='/home/mysql/general.log';
Query OK, 0 rows affected (0.00 sec)[root@127.0.0.1] mysql>show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)[root@127.0.0.1] mysql>
文件生成内容分析
general log即General Query Log，记录了MySQL服务器的操作。当客户端连接、断开连接、接收到客户端的SQL语句等，都会向general log中写入日志。开启general_log会损失一定的性能(性能上的损失 下面详细解释)，但是在开发、测试环境下开启日志，可以帮忙我们加快排查出现的问题。如下：
# 我这里开启两个连接 间隔执行SQL 如下：[mysql@dbserver ~]$ cat general.log
/home/mysql/mysql-8.0.27/bin/mysqld, Version: 8.0.27 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2024-04-02T06:58:16.355675Z 10 Query show variables like 'log_output'
2024-04-02T07:00:20.967875Z 10 Quit
2024-04-02T07:00:41.942253Z 11 Connect root@localhost on using SSL/TLS
2024-04-02T07:00:41.944182Z 11 Query select @@version_comment limit 1
2024-04-02T07:00:41.945409Z 11 Query select USER()
2024-04-02T07:01:11.162705Z 11 Query SELECT DATABASE()
2024-04-02T07:01:11.163151Z 11 Init DB mysql
2024-04-02T07:01:25.838190Z 12 Connect root@localhost on using SSL/TLS
2024-04-02T07:01:25.838263Z 12 Connect Access denied for user 'root'@'localhost' (using password: YES)
2024-04-02T07:01:51.785487Z 13 Connect root@localhost on using SSL/TLS
2024-04-02T07:01:51.785881Z 13 Query select @@version_comment limit 1
2024-04-02T07:01:51.786956Z 13 Query select USER()
2024-04-02T07:01:57.526667Z 13 Query SELECT DATABASE()
2024-04-02T07:01:57.526913Z 13 Init DB mysql
2024-04-02T07:02:29.853995Z 11 Query show tables
2024-04-02T07:02:52.531882Z 11 Query drop table t0401
2024-04-02T07:04:43.538680Z 11 Query create table t0402(id int, curtime TIMESTAMP(6))
2024-04-02T07:06:27.948203Z 11 Query insert into t0402 values(1, CURRENT_TIMESTAMP(6))
2024-04-02T07:06:37.420331Z 13 Query select * from t0402
2024-04-02T07:07:04.077164Z 13 Query drop table t0402
2024-04-02T07:08:04.054919Z 13 Query create table t0402(id int, curtime_int bigint(15))
2024-04-02T07:09:14.694347Z 13 Query insert into t0402 values(1, REPLACE(unix_timestamp(current_timestamp(6)),'.',''))
2024-04-02T07:09:24.407249Z 11 Query select * from t0402
2024-04-02T07:10:18.758216Z 11 Quit
2024-04-02T07:10:23.518195Z 13 Quit
[mysql@dbserver ~]$
[mysql@dbserver ~]$ mysql -h 127.0.0.1 -u root -p -A
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.27 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.[root@127.0.0.1] (none)>use mysql;
Database changed
[root@127.0.0.1] mysql>select * from t0402;
+------+------------------+
| id | curtime_int |
+------+------------------+
| 1 | 1712041754694258 |
+------+------------------+
1 row in set (0.00 sec)[root@127.0.0.1] mysql>prepare p1 as select * from t0402 where id < $1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as select * from t0402 where id < $1' at line 1
[root@127.0.0.1] mysql>
[root@127.0.0.1] mysql>prepare p1 from select * from t0402 where id < $1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from t0402 where id < $1' at line 1
[root@127.0.0.1] mysql>
[root@127.0.0.1] mysql>prepare p1 from 'select * from t0402 where id < $1';
ERROR 1054 (42S22): Unknown column '$1' in 'where clause'
[root@127.0.0.1] mysql>
[root@127.0.0.1] mysql>prepare p1 from 'select * from t0402 where id < ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared[root@127.0.0.1] mysql>SET @a = 3;
Query OK, 0 rows affected (0.00 sec)[root@127.0.0.1] mysql>EXECUTE p1 USING @a;
+------+------------------+
| id | curtime_int |
+------+------------------+
| 1 | 1712041754694258 |
+------+------------------+
1 row in set (0.00 sec)[root@127.0.0.1] mysql>prepare p2 from 'select * from t0402 where id = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared[root@127.0.0.1] mysql>SET @a = 1;
Query OK, 0 rows affected (0.00 sec)[root@127.0.0.1] mysql>EXECUTE p2 USING @a;
+------+------------------+
| id | curtime_int |
+------+------------------+
| 1 | 1712041754694258 |
+------+------------------+
1 row in set (0.00 sec)[root@127.0.0.1] mysql>
...
2024-04-02T07:40:34.865834Z 15 Connect root@localhost on using SSL/TLS
2024-04-02T07:40:34.866145Z 15 Query select @@version_comment limit 1
2024-04-02T07:40:34.866744Z 15 Query select USER()
2024-04-02T07:40:47.075169Z 15 Query SELECT DATABASE()
2024-04-02T07:40:47.075481Z 15 Init DB mysql
2024-04-02T07:40:58.135514Z 15 Query select * from t0402
2024-04-02T07:42:26.177204Z 15 Query PREPARE p1 FROM ...
2024-04-02T07:42:40.265662Z 15 Query PREPARE p1 FROM ...
2024-04-02T07:42:40.265822Z 15 Prepare select * from t0402 where id < ?
2024-04-02T07:43:18.121465Z 15 Query SET @a = 3
2024-04-02T07:43:41.852319Z 15 Query EXECUTE p1 USING @a
2024-04-02T07:43:41.852422Z 15 Execute select * from t0402 where id < 3
2024-04-02T07:45:07.481268Z 15 Query PREPARE p2 FROM ...
2024-04-02T07:45:07.481445Z 15 Prepare select * from t0402 where id = ?
2024-04-02T07:45:19.637677Z 15 Query SET @a = 1
2024-04-02T07:45:26.939353Z 15 Query EXECUTE p2 USING @a
2024-04-02T07:45:26.939432Z 15 Execute select * from t0402 where id = 1
[mysql@dbserver ~]$
如上图所示：
- 有语法错误的没有记入
- 有错 但非 语法错误，则仍然记入
- simple query记入一行 Query
- 一个prepare/execute 记入两行：Query Prepare/Execute
如上各列说明，如下：
Time日志记录的时间Id进程ID，可以通过show processlist命令查看Command执行的命令Argument命令参数
这里的命令类型，如下：
// sql\sql_parse.ccconst std::string Command_names::m_names[] = {"Sleep","Quit","Init DB", // 使用命令use选择库时的记录"Query","Field List","Create DB","Drop DB","Refresh","Shutdown","Statistics","Processlist","Connect","Kill","Debug","Ping","Time","Delayed insert","Change user","Binlog Dump","Table Dump","Connect Out","Register Replica","Prepare","Execute","Long Data","Close stmt","Reset stmt","Set option","Fetch","Daemon","Binlog Dump GTID","Reset Connection","clone","Group Replication Data Stream subscription","Error" // Last command number
};
// include\my_command.h/**@enum enum_server_command@brief A list of all MySQL protocol commands.These are the top level commands the server can receivewhile it listens for a new command in ::dispatch_command@par WarningAdd new commands to the end of this list, otherwise oldservers won't be able to handle them as 'unsupported'.
*/
enum enum_server_command {/**Currently refused by the server. See ::dispatch_command.Also used internally to mark the start of a session.*/COM_SLEEP,COM_QUIT, /**< See @ref page_protocol_com_quit */COM_INIT_DB, /**< See @ref page_protocol_com_init_db */COM_QUERY, /**< See @ref page_protocol_com_query */COM_FIELD_LIST, /**< Deprecated. See @ref page_protocol_com_field_list */COM_CREATE_DB, /**< Currently refused by the server. See ::dispatch_command */COM_DROP_DB, /**< Currently refused by the server. See ::dispatch_command */COM_REFRESH, /**< Deprecated. See @ref page_protocol_com_refresh */COM_DEPRECATED_1, /**< Deprecated, used to be COM_SHUTDOWN */COM_STATISTICS, /**< See @ref page_protocol_com_statistics */COM_PROCESS_INFO, /**< Deprecated. See @ref page_protocol_com_process_info */COM_CONNECT, /**< Currently refused by the server. */COM_PROCESS_KILL, /**< Deprecated. See @ref page_protocol_com_process_kill */COM_DEBUG, /**< See @ref page_protocol_com_debug */COM_PING, /**< See @ref page_protocol_com_ping */COM_TIME, /**< Currently refused by the server. */COM_DELAYED_INSERT, /**< Functionality removed. */COM_CHANGE_USER, /**< See @ref page_protocol_com_change_user */COM_BINLOG_DUMP, /**< See @ref page_protocol_com_binlog_dump */COM_TABLE_DUMP,COM_CONNECT_OUT,COM_REGISTER_SLAVE,COM_STMT_PREPARE, /**< See @ref page_protocol_com_stmt_prepare */COM_STMT_EXECUTE, /**< See @ref page_protocol_com_stmt_execute *//** See @ref page_protocol_com_stmt_send_long_data */COM_STMT_SEND_LONG_DATA,COM_STMT_CLOSE, /**< See @ref page_protocol_com_stmt_close */COM_STMT_RESET, /**< See @ref page_protocol_com_stmt_reset */COM_SET_OPTION, /**< See @ref page_protocol_com_set_option */COM_STMT_FETCH, /**< See @ref page_protocol_com_stmt_fetch *//**Currently refused by the server. See ::dispatch_command.Also used internally to mark the session as a "daemon",i.e. non-client THD. Currently the scheduler and the GTIDcode does use this state.These threads won't be killed by `KILL`@sa Event_scheduler::start, ::init_thd, ::kill_one_thread,::Find_thd_with_id*/COM_DAEMON,COM_BINLOG_DUMP_GTID,COM_RESET_CONNECTION, /**< See @ref page_protocol_com_reset_connection */COM_CLONE,COM_SUBSCRIBE_GROUP_REPLICATION_STREAM,/* don't forget to update const char *command_name[] in sql_parse.cc *//* Must be last */COM_END /**< Not a real command. Refused. */
};
如上文件中的时间值，转换为微秒的时间戳 如下：
[root@127.0.0.1] (none)>select REPLACE(unix_timestamp('2024-04-02T07:10:18.758216Z'),'.','');
+---------------------------------------------------------------+
| REPLACE(unix_timestamp('2024-04-02T07:10:18.758216Z'),'.','') |
+---------------------------------------------------------------+
| 1712013018758216 |
+---------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)[root@127.0.0.1] (none)>show variables like "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)[root@127.0.0.1] (none)>select now();
+---------------------+
| now() |
+---------------------+
| 2024-04-02 15:29:36 |
+---------------------+
1 row in set (0.00 sec)[root@127.0.0.1] (none)>
[root@127.0.0.1] (none)>select REPLACE(unix_timestamp('2024-04-02 07:10:18.758216'),'.','');
+--------------------------------------------------------------+
| REPLACE(unix_timestamp('2024-04-02 07:10:18.758216'),'.','') |
+--------------------------------------------------------------+
| 1712013018758216 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)[root@127.0.0.1] (none)>
这里的prepare操作，转换如下：
2024-04-02T07:45:07.481268Z 15 Query PREPARE p2 FROM ...
2024-04-02T07:45:07.481445Z 15 Prepare select * from t0402 where id = ?
2024-04-02T07:45:19.637677Z 15 Query SET @a = 1
2024-04-02T07:45:26.939353Z 15 Query EXECUTE p2 USING @a
2024-04-02T07:45:26.939432Z 15 Execute select * from t0402 where id = 1// sql\sql_rewrite.cc/**Rewrite the query for the PREPARE statement.@param[in,out] rlb Buffer to return the rewritten query in.@retval true the query was rewritten@retval false otherwise
*/
bool Rewriter_prepare::rewrite(String &rlb) const {LEX *lex = m_thd->lex;if (lex->prepared_stmt_code_is_varref) return false;rlb.append(STRING_WITH_LEN("PREPARE "));rlb.append(lex->prepared_stmt_name.str, lex->prepared_stmt_name.length);rlb.append(STRING_WITH_LEN(" FROM ..."));return true;
}
odbc连接内容生成
SQL> select version();
+----------+
| version()|
+----------+
| 8.0.27 |
+----------+
SQLRowCount returns 1
1 rows fetched
SQL>
[mysql@dbserver ~]$ sudo yum install unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel
已加载插件：ulninfo
软件包 unixODBC-2.3.1-14.0.1.el7.x86_64 已安装并且是最新版本
软件包 unixODBC-devel-2.3.1-14.0.1.el7.x86_64 已安装并且是最新版本
软件包 libtool-ltdl-2.4.2-22.el7_3.x86_64 已安装并且是最新版本
软件包 libtool-ltdl-devel-2.4.2-22.el7_3.x86_64 已安装并且是最新版本
无须任何处理
[mysql@dbserver ~]$ ## https://downloads.mysql.com/archives/c-odbc/
[mysql@dbserver odbc_mysql8]$ wget https://cdn.mysql.com/archives/mysql-connector-odbc-8.0/mysql-connector-odbc-8.0.20-1.el7.x86_64.rpm
--2024-04-03 16:09:12-- https://cdn.mysql.com/archives/mysql-connector-odbc-8.0/mysql-connector-odbc-8.0.20-1.el7.x86_64.rpm
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 2402:4f00:4002:19d::1d68, 2402:4f00:4002:190::1d68, 23.7.220.59
正在连接 cdn.mysql.com (cdn.mysql.com)|2402:4f00:4002:19d::1d68|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：4302344 (4.1M) [application/x-redhat-package-manager]
正在保存至: “mysql-connector-odbc-8.0.20-1.el7.x86_64.rpm”100%[=========================================================================================================================================================================>] 4,302,344 3.47MB/s 用时 1.2s 2024-04-03 16:09:16 (3.47 MB/s) - 已保存 “mysql-connector-odbc-8.0.20-1.el7.x86_64.rpm” [4302344/4302344])[mysql@dbserver odbc_mysql8]$ ls
mysql-connector-odbc-8.0.20-1.el7.x86_64.rpm
[mysql@dbserver odbc_mysql8]$
[mysql@dbserver odbc_mysql8]$ sudo rpm -ivh mysql-connector-odbc-8.0.20-1.el7.x86_64.rpm
警告：mysql-connector-odbc-8.0.20-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
正在升级/安装...1:mysql-connector-odbc-8.0.20-1.el7################################# [100%]
Success: Usage count is 1
Success: Usage count is 1
[mysql@dbserver odbc_mysql8]$
测试unixODBC安装是否成功，如下：
[mysql@dbserver ~]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/mysql/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[mysql@dbserver ~]$ odbcinst -q -d
[PostgreSQL]
[MySQL]
[MySQL ODBC 8.0 Unicode Driver]
[MySQL ODBC 8.0 ANSI Driver]
[mysql@dbserver ~]$
配置 odbc.ini，如下：
[mysql@dbserver ~]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/mysql/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[mysql@dbserver ~]$ odbcinst -q -d
[PostgreSQL]
[MySQL]
[MySQL ODBC 8.0 Unicode Driver]
[MySQL ODBC 8.0 ANSI Driver]
[mysql@dbserver ~]$
[mysql@dbserver ~]$ sudo vim /etc/odbc.ini
[mysql@dbserver ~]$
[mysql@dbserver ~]$ cat /etc/odbc.ini
[mysql]
Description = Data source sampleMySQL for accessing MySQL database sampleMySQL
Driver = MySQL ODBC 8.0 Unicode Driver
Server = 127.0.0.1
Host = 127.0.0.1
Database = mysql
Port = 3306
User = root
Password =123456
[mysql@dbserver ~]$
[mysql@dbserver ~]$ isql -v mysql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
我这里使用odbc进行操作，日志内容 如下：
[mysql@dbserver ~]$ cat general.log | grep '12 '
2024-04-03T08:26:37.163240Z 12 Connect root@localhost on mysql using SSL/TLS
2024-04-03T08:26:37.163849Z 12 Query SET NAMES utf8
2024-04-03T08:26:37.164332Z 12 Query SET character_set_results = NULL
2024-04-03T08:26:37.164538Z 12 Query SET SQL_AUTO_IS_NULL = 0
2024-04-03T08:27:18.292965Z 12 Query set @@sql_select_limit=DEFAULT
2024-04-03T08:27:18.293298Z 12 Query desc t0402
2024-04-03T08:27:35.010027Z 12 Query select * from t0402
2024-04-03T08:28:04.549888Z 12 Query PREPARE odbcp1 FROM ...
2024-04-03T08:28:04.550654Z 12 Prepare select * from t0402 where id < ?
2024-04-03T08:28:32.707333Z 12 Query SET @a = 3
2024-04-03T08:28:58.102807Z 12 Query EXECUTE odbcp1 USING @a
2024-04-03T08:28:58.102929Z 12 Execute select * from t0402 where id < 3
2024-04-03T08:29:12.346332Z 12 Quit
[mysql@dbserver ~]$
可以看出使用odbc的日志记录(格式/内容)上，和上面直接使用client一致！
一般查询日志性能影响
- IMPACT OF GENERAL QUERY LOG ON MYSQL PERFORMANCE，点击前往
有时，需要启用通用查询日志（默认情况下禁用）。如果启用了通用查询日志，则当客户端连接或断开连接时，以及从客户端接收到的每个 SQL 语句时，服务器都会写入此日志信息。问题是：
- 启用通用查询日志会影响MySQL性能吗？
- 另外，可以将该日志的输出记录到mysql数据库（mysql.general_log）中的文件或表中，每种记录对性能有何影响？
让我们针对这些场景做一些简单的基准测试来衡量对 mysql 性能的实际影响。
系统信息
硬件配置：
- CPU: Intel® Core™ i7-3520M Processor (4M Cache, up to 3.60 GHz).
-
- 2 cores, 4 threads, HT enabled.
- Memory: 8GB RAM (1600).
- Storage: HDD 1TB/ 5400RPM.
软件配置：
- OS: Ubuntu 12.04
- MySQL Server: 5.6.17
- Sysbench: 0.4.12
测试信息：
- Sysbench命令：
sysbench --num-threads=1 --max-requests=1000 --db-driver=mysql --test=oltp --mysql-table-engine=InnoDB --mysql-user=root run
- sysbench 测试使用的表结构
mysql> show create table sbtest.sbtest\GCREATE TABLE `sbtest` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`k` int(10) unsigned NOT NULL DEFAULT '0',`c` char(120) NOT NULL DEFAULT '',`pad` char(60) NOT NULL DEFAULT '',PRIMARY KEY (`id`),KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=8574 DEFAULT CHARSET=latin1
注意：
该测试针对 1、2、4、8、16 和 32 个线程进行，每个测试用例的每个线程数的每个吞吐量/响应时间值是由十 (10) 次执行的平均值生成的。
禁用一般查询日志
要确保禁用常规查询日志：
mysql> show global variables like'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
测试结果，如下：
启用一般查询日志
通用查询日志是一个动态变量，这意味着它可以在线启用或禁用，而无需重新启动 MySQL（自 MySQL 5.1 起）：
mysql> SET GLOBAL general_log=ON;
我们可以选择将此日志输出到日志文件（默认情况下）或 MySQL 表 (mysql.general_log) 中。如果我们将日志输出存储在表而不是文件中，我们可能会得到什么好处？
- 我们可以使用普通的 SQL 语句访问日志内容来获取有关特定条件的信息（例如使用 WHERE 条件），这在处理文件时有点困难
- 如果有人可以连接到 MySQL 服务器，则可以远程访问日志内容
- 日志条目的标准格式
- 如果日志表使用 CSV 引擎，则可以轻松将 CSV 文件导入到电子表格中
- 只需 TRUNCATE 日志表即可轻松使日志过期
- 通过使用 RENAME TABLE 语句可以进行日志轮转
- 日志条目不会复制到从属服务器，因为它们不会写入二进制日志
- 即使使用了 --all-databases 备份选项，mysqldump 也不在备份中包含日志表内容（general_log 或 Slow_log）
那么，让我们检查一下每个日志输出对性能的影响。
输出是文件
要检查通用查询日志的输出目的地，应使用以下命令：
mysql> show global variables like'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
测试结果，如下：
输出为表(CSV 表)
要将通用查询日志的输出目的地从文件更改为表（默认为 CSV），应使用以下命令：
mysql> SET GLOBAL log_output='TABLE';
mysql> show global variables like'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
测试结果，如下：
输出为表(MYISAM 表)
也许由于 CSV 存储引擎的性质，我们在之前的案例中遇到了性能问题。是否可以更改general_log表的表引擎？
答案是肯定的，但不幸的是，我们被限制只能使用 MyISAM 存储引擎，不允许使用 CSV 或 MyISAM 以外的引擎。检查此链接以获取更多信息。
要更改日志表，您必须首先禁用日志记录：
mysql> alter table mysql.general_log engine=MYISAM;
ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled
mysql> SET GLOBAL general_log=OFF;mysql> alter table mysql.general_log engine=MYISAM;mysql> SET GLOBAL general_log=ON;
测试结果，如下：
输出为表(MYISAM 表，结构发生一些变化)
一般来说，为了使任何 SQL 查询运行得更快，我们可能需要优化表结构、添加索引、重写查询等。
通用查询日志表结构如下：
mysql> show create table mysql.general_log\GCREATE TABLE `general_log` (`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`user_host` mediumtext NOT NULL,`thread_id` bigint(21) unsigned NOT NULL,`server_id` int(10) unsigned NOT NULL,`command_type` varchar(64) NOT NULL,`argument` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
我们来看看如何优化general_log表结构（欢迎其他建议）：
- 我们可以在该表上创建分区来提高我们的搜索吗？虽然这是一个普通的 MyISAM 表，但日志表不允许分区（顺便说一句，CSV 表也不允许分区）
- 我们可以将
user_host列的数据类型从mediumtext更改为例如varchar(100)？（我的机器上该列数据的最大长度不超过50个字符）虽然它在语法方面已被接受，但此后表中不会存储任何日志，并且错误日志文件中将打印以下错误：
2014-03-06 18:44:21 6987 [ERROR] Failed to write to mysql.general_log:
2014-03-06 18:44:23 6987 [ERROR] Incorrect definition of table mysql.general_log: expected column 'user_host' at position 1 to have type mediumtext, found type varchar(100).
-
在我们将用于大多数搜索的列（
event_time和argument）上创建索引怎么样？ -
- 在
event_time列上添加索引
- 在
mysql> SET GLOBAL general_log=OFF;mysql> alter table mysql.general_log add index ev_tm_idx(`event_time`);mysql> show create table mysql.general_log\GCREATE TABLE `general_log` (`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`user_host` mediumtext NOT NULL,`thread_id` bigint(21) unsigned NOT NULL,`server_id` int(10) unsigned NOT NULL,`command_type` varchar(64) NOT NULL,`argument` mediumtext NOT NULL,KEY `ev_tm_idx` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'mysql> SET GLOBAL general_log=ON;
测试结果，如下：
-
- 在
argument列上添加全文索引
- 在
mysql> SET GLOBAL general_log=OFF;mysql> alter table mysql.general_log add fulltext index (`argument`);mysql> show create table mysql.general_log\GCREATE TABLE `general_log` (`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`user_host` mediumtext NOT NULL,`thread_id` bigint(21) unsigned NOT NULL,`server_id` int(10) unsigned NOT NULL,`command_type` varchar(64) NOT NULL,`argument` mediumtext NOT NULL,KEY `ev_tm_idx` (`event_time`),FULLTEXT KEY `argument` (`argument`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'mysql> SET GLOBAL general_log=ON;
测试结果，如下：
为了更清楚地说明，以下是将所有结果组合在一张图表中，然后进行响应时间比较：
每秒事务数的原始结果可能有用：
| Threads | 1 | 2 | 4 | 8 | 16 | 32 |
|---|---|---|---|---|---|---|
| General Query Log disabled | 383.996 | 814.759 | 1421.288 | 1674.733 | 1414.985 | 1071.189 |
| General Query Log enabled (File) | 281.642 | 521.39 | 1230.743 | 1406.127 | 1095.896 | 923.986 |
| General Query Log enabled (CSV Table) | 231.659 | 447.173 | 787.578 | 507.846 | 426.324 | 439.992 |
| General Query Log enabled (MyISAM Table) | 249.47 | 536.379 | 933.304 | 532.912 | 476.454 | 454.015 |
| General Query Log enabled (MyISAM Table + index) | 238.508 | 430.05 | 875.209 | 465.464 | 465.464 | 395.063 |
| General Query Log enabled (MyISAM Table + Fulltext index) | 157.436 | 236.156 | 210.968 | 212.273 | 218.617 | 220.701 |
结论
-
在上述所有测试用例中，最好的MySQL性能可以通过禁用常规查询日志来实现，例如，如果我们比较上述4并发线程情况的结果(大多数情况下的最高值)，我们会发现：
-
- 使用启用的通用查询日志 (general_log = ON) 且日志目标为文件 (log_output = FILE) 可使吞吐量降低 13.4%，并使响应时间增加 17.5%
-
- 使用启用的通用查询日志并且日志目标是 CSV 表，吞吐量降低了 44.6%，响应时间增加了 90%
-
- 使用启用的通用查询日志并且日志目标是 MyISAM 表，吞吐量降低了 34.3%，响应时间增加了 59%
-
- 使用启用的通用查询日志并且日志目标是 MyISAM，并在
event_time列上添加索引，吞吐量降低了 38.4%，响应时间增加了 73%
- 使用启用的通用查询日志并且日志目标是 MyISAM，并在
-
- 使用启用的通用查询日志并且日志目标是 MyISAM，在
event_time列上添加索引并在argument列上添加 FULLTEXT 索引，吞吐量降低了 85%，响应时间增加了 542%
- 使用启用的通用查询日志并且日志目标是 MyISAM，在
-
虽然使用表作为日志输出目的地有很多好处（如上所述），但与日志文件相比，它对 MySQL 性能的负面影响更大
-
增加并发运行的线程数（在 log_output=TABLE 的情况下）将增加general_log 表争用，该争用由MyISAM 或CSV ENGINES 的表锁定级别控制
-
与任何其他 MySQL 表一样 - 日志表中插入的行数越多，负面性能影响越大
-
虽然 mysqldump 不包括备份中的日志表内容，但使用 Xtrabackup 或任何其他基于物理备份的工具进行完整物理备份时，情况并非如此
-
最后，最好只在真正需要时才启用通用查询日志，不建议在生产系统中启用它。它可以（动态）启用一段时间，然后在我们获得要搜索的内容后再次禁用
这篇关于MySQL 学习心得和知识总结(五)|MySQL的一般查询日志(general log)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
