识别 MySQL 中的慢查询

显示完整进程列表

要显示 MySQL 的处理线程正在执行的所有当前操作，请输入

SHOW FULL PROCESSLIST;

 +----+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
 | Id | User            | Host      | db   | Command | Time    | State                  | Info                  |
 +----+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
 |  5 | event_scheduler | localhost | NULL | Daemon  | 1834441 | Waiting on empty queue | NULL                  |
 | 50 | root            | localhost | NULL | Query   |       0 | init                   | show full processlist |
 +----+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
 2 rows in set (0.00 sec)

上面的输出显示了一个空闲的服务器，只有我们自己的查询以及一个长时间运行的事件监听器。一个活动的服务器将显示更多进程，其中一些进程可能长时间运行。如果没有 FULL 修饰符，此命令将仅显示前 100 个进程，这可能会或可能不会截断您的结果，具体取决于您的服务器活动。

需要查看的一些重要部分是 Time 和 State 列。Time 列计算线程处于提及的 State 状态的秒数。如果您发现进程的 Time 值与您对给定操作的期望不符，则可能是时候进一步调查了。

检查存储引擎状态

另一个要检查的地方是实际存储引擎的状态。

您可以通过键入以下内容找到与给定表关联的存储引擎

SHOW CREATE TABLE <database>.<table>\G

例如，要显示 mysql.time_zone 表使用的存储引擎，请输入

SHOW CREATE TABLE mysql.time_zone`\G

*************************** 1. row ***************************
       Table: time_zone
Create Table: CREATE TABLE `time_zone` (
  `Time_zone_id` int unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Time zones'
1 row in set (0.01 sec)

ENGINE=InnoDB 表示该表正在使用 InnoDB 存储引擎。这是大多数配置中的默认存储引擎，因此您可能需要检查其状态。

您可以通过键入以下内容显示 InnoDB 引擎的状态

SHOW ENGINE INNODB STATUS\G

 *************************** 1. row ***************************
   Type: InnoDB
   Name:
 Status:
 =====================================
 2022-02-26 11:55:52 139992513984256 INNODB MONITOR OUTPUT
 =====================================
 Per second averages calculated from the last 16 seconds
 -----------------
 BACKGROUND THREAD
 -----------------
 srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 1835091 srv_idle
 srv_master_thread log flush and writes: 0
 ----------
 SEMAPHORES
 ----------
 OS WAIT ARRAY INFO: reservation count 555
 OS WAIT ARRAY INFO: signal count 457
 RW-shared spins 0, rounds 0, OS waits 0
 RW-excl spins 0, rounds 0, OS waits 0
 RW-sx spins 0, rounds 0, OS waits 0
 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 8778
 Purge done for trx's n:o < 8713 undo n:o < 0 state: running but idle
 History list length 0
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 421467955133656, not started
 0 lock struct(s), heap size 1128, 0 row lock(s)
 ---TRANSACTION 421467955132848, not started
 0 lock struct(s), heap size 1128, 0 row lock(s)
 ---TRANSACTION 421467955132040, not started
 0 lock struct(s), heap size 1128, 0 row lock(s)
 --------
 FILE I/O
 --------
 I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
 I/O thread 1 state: waiting for completed aio requests (log thread)
 I/O thread 2 state: waiting for completed aio requests (read thread)
 I/O thread 3 state: waiting for completed aio requests (read thread)
 I/O thread 4 state: waiting for completed aio requests (read thread)
 I/O thread 5 state: waiting for completed aio requests (read thread)
 I/O thread 6 state: waiting for completed aio requests (write thread)
 I/O thread 7 state: waiting for completed aio requests (write thread)
 I/O thread 8 state: waiting for completed aio requests (write thread)
 I/O thread 9 state: waiting for completed aio requests (write thread)
 Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
  ibuf aio reads:, log i/o's:, sync i/o's:
 Pending flushes (fsync) log: 0; buffer pool: 0
 1779 OS file reads, 3384 OS file writes, 1870 OS fsyncs
 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
 -------------------------------------
 INSERT BUFFER AND ADAPTIVE HASH INDEX
 -------------------------------------
 Ibuf: size 1, free list len 0, seg size 2, 0 merges
 merged operations:
  insert 0, delete mark 0, delete 0
 discarded operations:
  insert 0, delete mark 0, delete 0
 Hash table size 34679, node heap has 2 buffer(s)
 Hash table size 34679, node heap has 9 buffer(s)
 Hash table size 34679, node heap has 9 buffer(s)
 Hash table size 34679, node heap has 15 buffer(s)
 Hash table size 34679, node heap has 7 buffer(s)
 Hash table size 34679, node heap has 3 buffer(s)
 Hash table size 34679, node heap has 4 buffer(s)
 Hash table size 34679, node heap has 6 buffer(s)
 0.00 hash searches/s, 0.00 non-hash searches/s
 ---
 LOG
 ---
 Log sequence number          38540718
 Log buffer assigned up to    38540718
 Log buffer completed up to   38540718
 Log written up to            38540718
 Log flushed up to            38540718
 Added dirty pages up to      38540718
 Pages flushed up to          38540718
 Last checkpoint at           38540718
 2197 log i/o's done, 0.00 log i/o's/second
 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total large memory allocated 0
 Dictionary memory allocated 473011
 Buffer pool size   8192
 Free buffers       6241
 Database pages     1896
 Old database pages 719
 Modified db pages  0
 Pending reads      0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 8, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 1754, created 142, written 926
 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 No buffer pool page gets since the last printout
 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
 LRU len: 1896, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 0 read views open inside InnoDB
 Process ID=59529, Main thread ID=139992560166656 , state=sleeping
 Number of rows inserted 0, updated 0, deleted 0, read 0
 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
 Number of system rows inserted 0, updated 317, deleted 0, read 6128
 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================

 1 row in set (0.00 sec)

输出将包含有关引擎正在使用的资源、正在执行的进程等的​​大量信息。您可以使用它来了解执行中是否存在瓶颈，或者争用中的进程数量是否导致性能问题。

检查 MySQL 是否正在记录慢查询

您应该做的第一件事是验证慢查询日志记录的当前状态。如果已启用慢查询日志记录，则无需执行任何操作。

您可以通过键入以下内容检查是否启用了慢查询日志记录

SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';

 +---------------------+------------------------------------+
 | Variable_name       | Value                              |
 +---------------------+------------------------------------+
 | slow_query_log      | OFF                                |
 | slow_query_log_file | /var/lib/mysql/mysqlutils-slow.log |
 +---------------------+------------------------------------+
 2 rows in set (0.01 sec)

上面的输出表明当前未记录慢查询，因为该功能已关闭。

如果慢查询日志记录已打开，您的输出将如下所示

 +---------------------+-------------------------------+
 | Variable_name       | Value                         |
 +---------------------+-------------------------------+
 | slow_query_log      | ON                            |
 | slow_query_log_file | /var/log/mysql/mysql-slow.log |
 +---------------------+-------------------------------+
 2 rows in set (0.02 sec)

现在您知道了当前状态，您可以根据需要更改它。

配置 MySQL 以记录慢查询

在我们继续之前，重要的是要注意，虽然慢查询日志记录非常有用，但它可能会对性能产生额外的性能影响。 MySQL 必须执行额外的操作来计时每个查询并将结果记录到日志中。这可能会影响性能并意外地填满硬盘空间。

一直记录慢查询可能不是一个好主意。相反，在您积极调查问题时启用该功能，并在完成后禁用它。

考虑到这一点，您可以通过修改 MySQL 服务器的配置文件来配置慢查询日志记录。您也可以交互式地修改这些值，但在配置中设置良好的默认值将使以后更易于交互式调整。

打开 MySQL 的配置文件。在大多数基于 Debian Linux 的系统中，配置文件位于 /etc/mysql/mysql.conf.d/mysqld.conf

sudo vim /etc/mysql/mysql.conf.d/mysqld.conf

我们将要修改或可能添加以下设置

因此，例如，如果我们想打开所有可选日志记录并记录任何检查至少 100 行并花费 2 秒或更长时间执行的语句，我们可以使用以下设置

; enable MySQL to log slow queries
slow_query_log                  = ON
; query log location
slow_query_log_file             = /var/log/mysql/mysql-slow.log
; amount of seconds that a query must surpass to be logged
long_query_time                 = 2
; minimum number of rows affected for a query to be logged
min_examined_row_limit          = 100

; include administrative commands in the logs
log_slow_admin_statements       = ON
; also log queries that are not using indexes
log_queries_not_using_indexes   = ON
; for MySQL servers version 8.0.14 or later, we can
; get extra information by setting this
log_slow_extra                  = ON
; Also log slow statements that have executed on
; the replica
; This only will log statements if `binlog_format` is
; set to `STATEMENT` or `MIXED` and the statement is
; logged in statement format
; RE: https://dev.mysqlserver.cn/doc/refman/8.0/en/replication-options-replica.html#sysvar_log_slow_replica_statements
; This option name depends on your MySQL version:
;   versions >= 8.0.26: log_slow_replica_statements
;   versions < 8.0.26: log_slow_slave_statements
; log_slow_slave_statements   = ON
log_slow_replica_statements   = ON

保存并关闭文件后，您可以通过键入以下内容来验证您的配置更改

sudo mysqld --validate-config

如果未返回错误，则您的 MySQL 服务器配置文件在语法上是有效的。您可以通过键入以下内容重新启动 MySQL 服务器进程

sudo systemctl restart mysql

您可以通过重新运行原始发现查询来验证慢查询现在是否已启用

SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';

一旦您将慢查询配置为您想要的方式，您就可以根据需要在 MySQL 本身中启用和禁用它。调整值的语法如下所示

SET GLOBAL slow_query_log = 'OFF';