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 - GPL

Copyright (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.cc

const 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 receive
  while it listens for a new command in ::dispatch_command

  @par Warning
  Add new commands to the end of this list, otherwise old
  servers 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 GTID
    code 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\G

CREATE 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\G

CREATE 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\G

CREATE 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\G

CREATE 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 或任何其他基于物理备份的工具进行完整物理备份时，情况并非如此

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

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Java23种设计模式

Java23种设计模式

本文主要是对Java中一些常用的设计模式进行讲解 后期会进行不断的更新&#xff0c;欢迎浏览 23种设计模式 创建型模式&#xff0c;共五种&#xff1a;工厂方法模式、抽象工厂模式、建造者模式、原型模式、单例模式。结构型模式&#xff0c;共七种&#xff1a;适配器模式、桥接…
阅读更多...
显示器and拓展坞PD底层协商

显示器and拓展坞PD底层协商

简介&#xff1a; PD显示器或者PD拓展坞方案中&#xff0c;连接显示设备的Type-C端口主要运行在DRP模式&#xff0c;在此模式下可以兼容Source&#xff08;显卡&#xff09;、Sink&#xff08;信号器&#xff09;、DRP&#xff08;手机、电脑&#xff09;模式的显示设备。 Sou…
阅读更多...
OpenCASCADE源码分析：总论

OpenCASCADE源码分析：总论

OpenCASCADE是20世纪90年代由法国Matra Datavision公司开发的三维曲面/实体造型引擎&#xff0c;目前&#xff0c;国内许多CAE软件将其用作几何模块的开发。 本文拟从系统设计的角度&#xff0c;对OpenCASCADE架构(核心组件、关键流程等)进行概要性总结。 注1&#xff1a;限于…
阅读更多...
C语言杂谈

C语言杂谈

努力扩大自己&#xff0c;以靠近&#xff0c;以触及自身以外的世界 文章目录 什么是定义&#xff1f;什么是声明&#xff1f;什么是赋值&#xff1f;什么是初始化&#xff1f;什么是生命周期&#xff1f;什么是作用域&#xff1f;全局变量&#xff1f;局部变量&#xff1f;size…
阅读更多...
TCP/IP通信demo

TCP/IP通信demo

TCP/IP&#xff08;Transmission Control Protocol/Internet Protocol&#xff09;是一组通信协议&#xff0c;用于在网络上实现数据传输。它是互联网的基础&#xff0c;也被广泛用于局域网和广域网中。TCP/IP协议族由多个协议组成&#xff0c;其中最重要的是TCP和IP。 IP&…
阅读更多...
JAVAEE之Spring, Spring Boot 和Spring MVC的关系以及区别

JAVAEE之Spring, Spring Boot 和Spring MVC的关系以及区别

1.Spring, Spring Boot 和Spring MVC的关系以及区别 Spring: 简单来说, Spring 是⼀个开发应⽤框架&#xff0c;什么样的框架呢&#xff0c;有这么⼏个标签&#xff1a;轻量级、⼀ 站式、模块化&#xff0c;其⽬的是⽤于简化企业级应⽤程序开发 Spring的主要功能: 管理对象&am…
阅读更多...
【2024红明谷】三道Web题目的记录

【2024红明谷】三道Web题目的记录

红明谷 文章目录 红明谷Web1 | SOLVED LaterWeb2 | UNSOLVEDWeb3 | SOLVED 容器已经关咯&#xff0c;所以有些场景只能靠回忆描述啦&#xff0c;学习为主&#xff0c;题目只是一个载体~ 本次比赛学习为主&#xff0c;确实再一次感受到久违的web题目的魅力了&#xff0c;可能也是…
阅读更多...
【SpringCloud】Ribbon 负载均衡

【SpringCloud】Ribbon 负载均衡

目 录 一.负载均衡原理二.源码跟踪1. LoadBalancerIntercepor2. LoadBalancerClient3. 负载均衡策略 IRule4. 总结 三.负载均衡策略1.负载均衡策略2.自定义负载均衡策略 四.饥饿加载 在 order-service 中 添加了 LoadBalanced 注解&#xff0c;即可实现负载均衡功能&#xff0c…
阅读更多...
网络安全基础之网络协议与安全威胁

网络安全基础之网络协议与安全威胁

OSI(OpenSystem Interconnect)&#xff0c;即开放式系统互联。 一般都叫OSI参考模型&#xff0c;是ISO(国际标准化组织)组织在1985年研究的网络互联模型。 网络协议的简介&#xff1a; 定义&#xff1a;协议是网络中计算机或设备之间进行通信的一系列规则集合。 什么是规则?…
阅读更多...
Leetcode442. 数组中重复的数据

Leetcode442. 数组中重复的数据

Every day a Leetcode 题目来源&#xff1a;442. 数组中重复的数据 解法1&#xff1a;将元素交换到对应的位置 由于给定的 n 个数都在 [1,n] 的范围内&#xff0c;如果有数字出现了两次&#xff0c;就意味着 [1,n] 中有数字没有出现过。 因此&#xff0c;我们可以尝试将每一…
阅读更多...
【Pt】马灯贴图绘制过程 04-玻璃脏迹

【Pt】马灯贴图绘制过程 04-玻璃脏迹

目录 效果 步骤 一、透明玻璃 二、烟熏痕迹 三、粗糙 四、浮尘 效果 步骤 一、透明玻璃 1. 打开纹理集设置&#xff0c;着色器链接选择“新的着色器链接” 在着色器设置中可以看到此时名称为“Main shader &#xff08;Copy&#xff09;” 这里修改名称为“玻璃” 在…
阅读更多...
redis群集有三种模式

redis群集有三种模式

目录 redis群集有三种模式 redis群集有三种模式 分别是主从同步/复制、哨兵模式、Cluster ●主从复制&#xff1a;主从复制是高可用Redis的基础&#xff0c;哨兵和集群都是在主从复制基础上实现高可用的。主从复制主要实现了数据的多机备份&#xff0c;以及对于读操作的负载均…
阅读更多...
C++ 静态库与动态库的生成和使用：基于 VS Studio 生成 newmat 矩阵库的静态库与动态库

C++ 静态库与动态库的生成和使用：基于 VS Studio 生成 newmat 矩阵库的静态库与动态库

文章目录 Part.I IntroductionChap.I 预备知识Chap.II 静态库与动态库区分 Part.II 静态库的生成与使用 (newmat)Chap.I 生成静态库Chap.II 使用静态库 Part.III 动态库的生成与使用 (newmat)Chap.I 生成动态库Chap.II 使用动态库 Part.IV 文件内容Chap.I test.cpp (静态库)Cha…
阅读更多...
5G智慧地铁数字孪生可视化平台，推进铁路行业数字化转型

5G智慧地铁数字孪生可视化平台，推进铁路行业数字化转型

随着科技的快速发展&#xff0c;5G智慧地铁数字孪生可视化平台正逐渐成为铁路行业数字化转型的重要推动力。巨蟹数科数字孪生平台集成了5G通信技术、大数据分析、云计算和人工智能等先进技术&#xff0c;通过构建数字孪生模型&#xff0c;实现对地铁运营全过程的实时监控、预测…
阅读更多...
rocketmq的运维

rocketmq的运维

1. admintool创建topic的时候 -o 的用法含义 https://rocketmq.apache.org/zh/docs/4.x/producer/03message2/ 有关orderMessageEnable和returnOrderTopicConfigToBroker的设置可以参考 https://blog.csdn.net/sdaujsj1/article/details/115741572 -c configFile通过-c命令指…
阅读更多...
大模型prompt技巧——思维链（Chain-of-Thought）

大模型prompt技巧——思维链（Chain-of-Thought）

1、Zero-shot、One-shot、Few-shot 与fintune prompt的时候给出例子答案&#xff0c;然后再让模型回答。 2、zero-shot-CoT “Let’s think step by step”有奇迹效果 3、多数投票提高CoT性能——自洽性&#xff08;Self-consistency&#xff09; 多个思维链&#xff0c;然后取…
阅读更多...
【深度学习】sdwebui的token_counter，update_token_counter，如何超出77个token的限制？对提示词加权的底层实现

【深度学习】sdwebui的token_counter，update_token_counter，如何超出77个token的限制？对提示词加权的底层实现

文章目录 前言关于token_counter关于class StableDiffusionProcessingTxt2Img(StableDiffusionProcessing)如何超出77个token的限制&#xff1f;对提示词加权的底层实现Overcoming the 77 token limit in diffusers方法1 手动拼方法2 compel 问询、帮助请看&#xff1a; 前言 …
阅读更多...
跟着Kimi Chat学习提示工程Prompt Engineering！让AI更高效地给你打工！

跟着Kimi Chat学习提示工程Prompt Engineering！让AI更高效地给你打工！

大家好&#xff0c;我是木易&#xff0c;一个持续关注AI领域的互联网技术产品经理&#xff0c;国内Top2本科&#xff0c;美国Top10 CS研究生&#xff0c;MBA。我坚信AI是普通人变强的“外挂”&#xff0c;所以创建了“AI信息Gap”这个公众号&#xff0c;专注于分享AI全维度知识…
阅读更多...
【JavaWeb】百度地图API SDK导入

【JavaWeb】百度地图API SDK导入

百度地图开放平台 | 百度地图API SDK | 地图开发 (baidu.com) 登录注册&#xff0c;创建应用&#xff0c;获取AK 地理编码 | 百度地图API SDK (baidu.com) 需要的接口一&#xff1a;获取店铺/用户 所在地址的经纬度坐标 轻量级路线规划 | 百度地图API SDK (baidu.com) 需要的…
阅读更多...
Fastjson 1.2.47 远程命令执行漏洞复现分析环境

Fastjson 1.2.47 远程命令执行漏洞复现分析环境

Fastjson 1.2.47 远程命令执行漏洞 1、靶机环境安装 1.1、虚机机linux环境参数 1、操作系统&#xff1a;CentOS Linux release 7.4.1708 (Core) 2、IP&#xff1a;192.168.127.1321.1、docker与docker compose安装 1.2、下载https://github.com/vulhub/vulhub/tree/master/…
阅读更多...
最新文章

Copyright @ 2022~2023