萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

善用 MySQL General Log 找出高頻 SQL,效能調優第一步

開啟 General Log

SHOW VARIABLES LIKE '%general%';
SET GLOBAL general_log = 'ON';
-- 可選:SET GLOBAL general_log_file = '/var/log/mysql/general.log';

Python 解析腳本:analysis-general-log.py

#!/usr/bin/env python3
import re, sys, os

if len(sys.argv) != 2 or not os.path.exists(sys.argv[1]):
    print(f"Usage: {sys.argv[0]} "); sys.exit(1)

log_path = sys.argv[1]
query_pattern = re.compile(r'^\s*\d+\s+.*\bQuery\s+(.*)$', re.I)
cleanup_patterns = [
    (r'values\s*\(.*?\)', 'values (x)', re.I),
    (r"([=<>]=?)\s*('|\").*?\2", r"\1 'x'"),
    (r'([=<>]=?)\s*\d+', r'\1 x'),
    (r"like\s+('|\").*?\1", "like 'x'", re.I),
    (r'in\s+\(.*?\)', 'in (x)', re.I),
    (r'limit.*', 'limit', re.I),
    (r'\s+', ' '),
]

with open(log_path) as f:
    prev_line = ''
    for line in f:
        line = line.rstrip('\n')
        m = query_pattern.match(line)
        if m:
            if prev_line:
                sql = prev_line
                for pat, repl, *flags in cleanup_patterns:
                    sql = re.sub(pat, repl, sql, flags=flags[0] if flags else 0)
                print(sql.strip())
            prev_line = m.group(1)
        else:
            prev_line += ' ' + line.strip()
    # 最後一行
    if prev_line:
        sql = prev_line
        for pat, repl, *flags in cleanup_patterns:
            sql = re.sub(pat, repl, sql, flags=flags[0] if flags else 0)
        print(sql.strip())

使用方式

python3 analysis-general-log.py /var/log/mysql/general.log \
  | sort | uniq -c | sort -nr | head -20

輸出範例(已正規化)

1032 SELECT * FROM wp_comments WHERE ( comment_approved = 'x' OR comment_approved = 'x' ) AND comment_post_ID = x ORDER BY comment_date_gmt DESC
653 SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id in (x) ORDER BY meta_id ASC
527 SELECT FOUND_ROWS()
...

前幾名即為最需優化的查詢(加索引、改寫、快取)。

General Log 輪替技巧(不可直接刪除檔案)

USE mysql;
CREATE TABLE gn2 LIKE general_log;
RENAME TABLE general_log TO oldLogs, gn2 TO general_log;
-- 稍後可 DROP TABLE oldLogs;
copyright © 萬盛學電腦網 all rights reserved