MySQL 記錄慢查詢的方式 Slow Query Log

在 /etc/my.cnf 中加上三行設定
這樣 MySQLd 啟動後，即自動記錄執行超過 0.2秒的 SQL 資料：

[mysqld]
::
slow_query_log_file= "/var/tmp/mysql-log-slow-queries.log"
long_query_time    = 0.2
slow_query_log     = 1

這個方式得重新啟動 MySQLd 才行

若只是臨時要記錄 Slow Query
可使用命令列方式：

mysql> SET GLOBAL slow_query_log_file = '/var/tmp/mysql-log-slow-queries.log';
mysql> SET global long_query_time = 0.2;
mysql> SET GLOBAL slow_query_log = 1 ;

要停止記錄，下這個命令
mysql> SET GLOBAL slow_query_log = 0 ;

另外，若想要記錄 所有執行過的 SQL 命令，可以再輸入：

mysql> set global log_queries_not_using_indexes = 1;

特別注意，儲存所有執行過的 SQL 命令會很耗硬碟空間

mysql-log-slow-queries.log 的檔案內容，像這樣：

# Time: 240126 16:35:13
# User@Host: root[root] @ localhost []
# Thread_id: 2058  Schema: wordpress  QC_hit: No
# Query_time: 0.231768  Lock_time: 0.000024  Rows_sent: 49  Rows_examined: 98
# Rows_affected: 0  Bytes_sent: 8543
use wordpress;
SET timestamp=1706258113;
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (122,80,117) ORDER BY meta_id ASC;
# Time: 240128  9:02:20
# User@Host: root[root] @ localhost []
# Thread_id: 2565  Schema: wordpress  QC_hit: No
# Query_time: 0.317511  Lock_time: 0.000054  Rows_sent: 4  Rows_examined: 18
# Rows_affected: 0  Bytes_sent: 115
use wordpress;
SET timestamp=1706403740;
SELECT   wp_posts.ID
      FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
      WHERE 1=1  AND (
  ( wp_postmeta.meta_key = '_elementor_edit_mode' AND wp_postmeta.meta_value = 'builder' )
) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'e-landing-page') AND ((wp_posts.post_status = 'publish'))
       GROUP BY wp_posts.ID
       ORDER BY wp_posts.post_date DESC
       LIMIT 0, 10;
# Time: 240128  9:07:51
# User@Host: root[root] @ localhost []
# Thread_id: 2585  Schema: wordpress  QC_hit: No
# Query_time: 0.269269  Lock_time: 0.000070  Rows_sent: 213  Rows_examined: 426
# Rows_affected: 0  Bytes_sent: 1186896
SET timestamp=1706404071;
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1350,1349,1348,1347,1346,1345) ORDER BY meta_id ASC;