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;

测试结果，如下：

在这里插入图片描述
为了更清楚地说明，以下是将所有结果组合在一张图表中，然后进行响应时间比较：

在这里插入图片描述
在这里插入图片描述
每秒事务数的原始结果可能有用：

Threads12481632
General Query Log disabled383.996814.7591421.2881674.7331414.9851071.189
General Query Log enabled (File)281.642521.391230.7431406.1271095.896923.986
General Query Log enabled (CSV Table)231.659447.173787.578507.846426.324439.992
General Query Log enabled (MyISAM Table)249.47536.379933.304532.912476.454454.015
General Query Log enabled (MyISAM Table + index)238.508430.05875.209465.464465.464395.063
General Query Log enabled (MyISAM Table + Fulltext index)157.436236.156210.968212.273218.617220.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，在event_time列上添加索引并在argument列上添加 FULLTEXT 索引，吞吐量降低了 85%，响应时间增加了 542%

  • 虽然使用表作为日志输出目的地有很多好处（如上所述），但与日志文件相比，它对 MySQL 性能的负面影响更大

  • 增加并发运行的线程数（在 log_output=TABLE 的情况下）将增加general_log 表争用，该争用由MyISAM 或CSV ENGINES 的表锁定级别控制

  • 与任何其他 MySQL 表一样 - 日志表中插入的行数越多，负面性能影响越大

  • 虽然 mysqldump 不包括备份中的日志表内容，但使用 Xtrabackup 或任何其他基于物理备份的工具进行完整物理备份时，情况并非如此

  • 最后，最好只在真正需要时才启用通用查询日志，不建议在生产系统中启用它。它可以（动态）启用一段时间，然后在我们获得要搜索的内容后再次禁用

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/296122.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

权限提升技术：攻防实战与技巧

权限提升技术：攻防实战与技巧

本次活动赠书1本&#xff0c;包邮到家。参与方式&#xff1a;点赞收藏文章即可。获奖者将以私信方式告知。 网络安全已经成为当今社会非常重要的话题&#xff0c;尤其是近几年来&#xff0c;我们目睹了越来越多的网络攻击事件&#xff0c;例如公民个人信息泄露&#xff0c;企业…
阅读更多...
软件测试学习（一）

软件测试学习（一）

1.软件测试的定义 软件是控制计算机硬件工作的工具。 软件基本组成&#xff1a;客服端、服务器、数据库 软件产生过程&#xff1a;需求产生->需求文档->设计效果图->产品开发->产品测试->部署上线 软件测试的定义&#xff1a;使用技术手段来验证软件产品是否…
阅读更多...
第四百三十九回

第四百三十九回

文章目录 1. 概念介绍2. 实现方法3. 示例代码 我们在上一章回中介绍了dart语言中的setter/getter相关的内容&#xff0c;本章回中将介绍局部动态列表.闲话休提&#xff0c;让我们一起Talk Flutter吧。 1. 概念介绍 在正常情况下列表位于整个页面中&#xff0c;而且可以在整个页…
阅读更多...
波士顿房价预测案例（python scikit-learn）---多元线性回归(多角度实验分析)

波士顿房价预测案例（python scikit-learn）---多元线性回归(多角度实验分析)

波士顿房价预测案例&#xff08;python scikit-learn&#xff09;—多元线性回归(多角度实验分析) 这次实验&#xff0c;我们主要从以下几个方面介绍&#xff1a; 一、相关框架介绍 二、数据集介绍 三、实验结果-优化算法对比实验&#xff0c;数据标准化对比实验&#xff0…
阅读更多...
Rredis缓存常见面试题

Rredis缓存常见面试题

文章目录 1.什么是缓存穿透&#xff0c;怎么解决2.什么是缓存击穿&#xff0c;怎么解决3.什么是缓存雪崩&#xff0c;怎么解决4.双写一致性问题5.redisson添加的排他锁是如何保证读写、读读互斥的6.为什么不使用延迟双删7.redis做为缓存&#xff0c;数据的持久化是怎么做的8.re…
阅读更多...
MySQL的基本操作(超详细)

MySQL的基本操作(超详细)

&#x1f468;‍&#x1f4bb;作者简介&#xff1a;&#x1f468;&#x1f3fb;‍&#x1f393;告别&#xff0c;今天 &#x1f4d4;高质量专栏 &#xff1a;☕java趣味之旅 &#x1f4d4;&#xff08;零基础&#xff09;专栏&#xff1a;MSQL数据库 欢迎&#x1f64f;点赞&…
阅读更多...
ngAlain下使用nz-select与文件上传框出现灵异bug

ngAlain下使用nz-select与文件上传框出现灵异bug

bug描述 初始化页面&#xff0c;文件上传框无法出现&#xff1a; 但点击一次选择框以后&#xff0c;就会出现&#xff1a; 真的很神奇。。。 下面逐步排查看看是什么原因。 设想一&#xff1a; 选择框与文件框不可同时存在&#xff0c;删掉选择框看看&#xff1a; 还…
阅读更多...
创业者的三大法宝：自我进化、自我激励与诚信坚守

创业者的三大法宝：自我进化、自我激励与诚信坚守

一、摘要&#xff1a; 在创业的道路上&#xff0c;每一位创业者都如同航海家&#xff0c;驾驶着自己的船只&#xff0c;在波涛汹涌的大海中探寻成功的彼岸。而在这条充满未知与挑战的旅程中&#xff0c;创业者们需要具备哪些关键的品质和能力呢&#xff1f;京东集团创始人刘强…
阅读更多...
搭建电商网站外贸网站用API接口可以实现哪些功能（天猫API接口|京东API接口）

搭建电商网站外贸网站用API接口可以实现哪些功能（天猫API接口|京东API接口）

在电商领域&#xff0c;API接口可以实现多种功能&#xff0c;起到连接内外部系统及优化电商业务流程等多种作用&#xff0c;从而来提高电商企业的运营效率。 具体来看&#xff0c;API接口接入可以用来&#xff1a; 商品管理&#xff1a; API接口能够用来获取商品详情等&#…
阅读更多...
华为OD面试手撕算法-合并排序数组

华为OD面试手撕算法-合并排序数组

题目描述 本题是leetcode一道简单题&#xff1a;合并两个有序数组&#xff0c;但是对于时间和空间复杂度面试官明确给出了限制。 // 给定两个排序后的数组 A 和 B&#xff0c;其中 A 的末端有足够的缓冲空间容纳 B。 编写一个方法&#xff0c;将 B 合并入 A 并排序。 // 初始化…
阅读更多...
马化腾的电商梦，只能靠它来实现了~

马化腾的电商梦，只能靠它来实现了~

我是王路飞。 腾讯要开始加大对电商的投入力度了&#xff0c; 而这些资源所依托的载体&#xff0c;正是【视频号】。 在2023微信公开课PRO上&#xff0c;视频号团队介绍&#xff0c;2022年总用户使用时长已经超过了朋友圈总用户使用时长的80%。视频号直播的看播时长增长156%…
阅读更多...
Windows12安装Docker

Windows12安装Docker

环境及工具&#xff08;文末提供&#xff09; Docker Desktop Installer.exe &#xff08;官网&#xff09; 一、查看windows相关配置 查看是否开启相应的功能&#xff0c;如果没有需要开启&#xff0c;然后重启电脑 打开任务管理器&#xff08;CTRLSHIFTESC&#xff09;-&g…
阅读更多...
高级IO/多路转接-select/poll(1)

高级IO/多路转接-select/poll(1)

概念背景 IO的本质就是输入输出 刚开始学网络的时候&#xff0c;我们简单的写过一些网络服务&#xff0c;其中用到了read&#xff0c;write这样的接口&#xff0c;当时我们用的就是基础IO&#xff0c;高级IO主要就是效率问题。 我们在应用层调用read&&write的时候&…
阅读更多...
Webpack部署本地服务器

Webpack部署本地服务器

Webpack部署本地服务器 目录 Webpack部署本地服务器目的认识模块热替换&#xff08;HMR&#xff09;什么是 HMRHMR 通过如下几种方式, 来提高开发的速度如何使用 HMRhost 配置 目的 完成自动编译 常用方式: webpack-dev-server webpack-dev-server 是一个用于开发环境的 Web 服…
阅读更多...
PCIE学习总结

PCIE学习总结

一、PCIE与SATA区别 1 SATA是半双工&#xff0c;类似于打电话&#xff0c;同一时间只能一端发送或者接收数据&#xff1b;PCIE是全双工&#xff0c;双端可以同时发送或者接收数据&#xff1b; 2 PCIE是串行总线&#xff0c;速率计算&#xff0c;如果双边速率&#xff08;单边…
阅读更多...
vue3+echarts：echarts地图打点显示的样式

vue3+echarts：echarts地图打点显示的样式

colorStops是打点的颜色和呼吸灯、label为show是打点是否显示数据、rich里cnNum是自定义的过滤模板用来改写显示数据的样式 series: [{type: "effectScatter",coordinateSystem: "geo",rippleEffect: {brushType: "stroke",},showEffectOn: &quo…
阅读更多...
Qt扫盲-QAssisant 集成其他qch帮助文档

Qt扫盲-QAssisant 集成其他qch帮助文档

QAssisant 集成其他qch帮助文档 一、概述二、Cmake qch例子1. 下载 Cmake.qch2. 添加qch1. 直接放置于Qt 帮助的目录下2. 在 QAssisant中添加 一、概述 QAssisant是一个很好的帮助文档&#xff0c;他提供了供我们在外部添加新的 qch帮助文档的功能接口&#xff0c;一般有两中添…
阅读更多...
Vue3从入门到实战：路由的query和params参数

Vue3从入门到实战：路由的query和params参数

在Vue 3中&#xff0c;我们可以通过路由的查询参数来传递数据。这意味着我们可以在不同的页面之间传递一些信息&#xff0c;以便页面可以根据这些信息来显示不同的内容或执行不同的操作。 查询参数的使用方式类似于在URL中添加附加信息&#xff0c;以便页面之间可以根据这些信息…
阅读更多...
计算机网络-TCP/IP 网络模型

计算机网络-TCP/IP 网络模型

TCP/IP网络模型各层的详细描述&#xff1a; 应用层&#xff1a;应用层为应用程序提供数据传输的服务&#xff0c;负责各种不同应用之间的协议。主要协议包括&#xff1a; HTTP&#xff1a;超文本传输协议&#xff0c;用于从web服务器传输超文本到本地浏览器的传送协议。FTP&…
阅读更多...
【Redis基础篇】详细讲解Redis

【Redis基础篇】详细讲解Redis

这篇文章让你详细了解Redis的相关知识&#xff0c;有代码讲解以及图片剖析&#xff0c;让你更轻松掌握 制作不易&#xff0c;感觉不错&#xff0c;请点赞收藏哟 &#xff01;&#xff01;&#xff01; 目录 1 redis基础 1.1 定义 1.2 SQL和NOSQL不同点 1.3 特征 1.4 Redis…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023