識別 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://mysqldev.dev.org.tw/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';