利用 MySQL 通用查詢日誌找出高頻 SQL 語句

利用 MySQL 通用查詢日誌找出高頻 SQL 語句

通用查詢日誌記錄所有 SQL，透過簡單的腳本即可統計出執行頻率最高的語句，幫助定位熱點查詢、優化索引或調整快取策略。

一、開啟通用日誌並輸出到 TABLE

SET GLOBAL general_log = ON;
SET GLOBAL log_output = 'TABLE';  -- 方便直接用 SQL 分析

二、查看日誌表內容

SELECT * FROM mysql.general_log \G
*************************** 1. row ***************************
event_time: 2014-08-14 10:53:18
user_host: root[root] @ localhost []
thread_id: 3
server_id: 0
command_type: Query
argument: SELECT * FROM mysql.general_log

三、切換到 FILE 模式並指定路徑

SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file = '/tmp/general.log';

四、使用 Python 腳本分析高頻 SQL

假設已將日誌導出為 general.log，使用以下管道命令：

python analysis-general-log.py general.log | sort | uniq -c | sort -nr

analysis-general-log.py 邏輯：解析每行 Query 後的 SQL，正規化常數值（數字→N、字串→S），輸出標準化 SQL 模板。

五、實際輸出範例

1032 SELECT * FROM wp_comments WHERE (comment_approved = 'N' OR comment_approved = 'N') AND comment_post_ID = N ORDER BY comment_date_gmt DESC
653  SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (N) ORDER BY meta_id ASC
527  SELECT FOUND_ROWS()
438  SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = 'S' AND t.term_id = N LIMIT N
341  SELECT option_value FROM wp_options WHERE option_name = 'S' LIMIT N

前幾名多為 WordPress 核心查詢，可針對性加索引或啟用 Query Cache。

六、通用日誌清理技巧

無法直接刪除正在寫入的日誌檔，採用重命名表法：

USE mysql;
CREATE TABLE gn2 LIKE general_log;
RENAME TABLE general_log TO oldLogs, gn2 TO general_log;

檔案模式下同理：mv general.log general-old.log; mysqladmin flush-logs;

七、注意事項

• 通用日誌會記錄所有 SQL（含 SELECT），高併發下極快佔滿磁碟，務必限時開啟。
• 分析腳本需正規化常數，否則相同模板的 SQL 會被視為不同語句。
• 生產環境建議搭配 pt-query-digest 或 mysqlsla 等專業工具。