mysqlsla(MySQL Statement Log Analyzer)是 Perl 編寫的慢查詢日誌分析工具,支持 slow log、general log、binary log 多種格式,能按總耗時、執行次數、掃描行數等多維度聚合統計,快速定位性能瓶頸。
# CentOS/RHEL
yum install -y perl-CPAN perl-DBI perl-DBD-Mysql perl-Time-HiRes
# Ubuntu/Debian
apt-get install -y libdbi-perl libdbd-mysql-perl libtime-hires-perl cpanminus
wget https://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
tar zxvf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make && make install
若perl Makefile.PL報缺少模塊,用cpan Module::Name安裝對應 Perl 模塊。
# 分析慢查詢日誌
mysqlsla -lt slow slow.log
# 分析通用日誌
mysqlsla -lt general general.log
# 分析二進制日誌(需先轉文本)
mysqlbinlog binlog.000001 | mysqlsla -lt binary -
| 參數 | 說明 | 常用值 |
|---|---|---|
-lt / --log-type | 日誌類型 | slow, general, binary, msl, udl |
-sort | 排序依據 | t_sum(總耗時), c_sum(總次數), c_sum_p(次數佔比) |
-top | 顯示前 N 條 | 默認 10,如 -top 20 |
-sf / --statement-filter | 語句類型過濾 | +SELECT,+UPDATE 只含 SELECT/UPDATE |
-db | 指定庫 | -db backup |
mysqlsla -lt slow -sort c_sum_p -sf "+SELECT" -db backup -top 2 /tmp/slow.log
Auto-detected logs as slow logs
Report for slow logs: ../slowlog/10.4.1.107.log.1206
799 queries total, 4 unique
Sorted by 't_sum'
Grand Totals: Time 1.25k s, Lock 0 s, Rows sent 917, Rows Examined 426.83M
______________________________________________________________________ 001 ___
Count : 372 (46.56%)
Time : 581.404365 s total, 1.562915 s avg, 1.001595 s to 4.129653 s max (46.56%)
95% of Time : 516.842801 s total, 1.464144 s avg, 1.001595 s to 2.815608 s max
Lock Time (s) : 40.935 ms total, 110 µs avg, 71 µs to 211 µs max (44.74%)
95% of Lock : 38.016 ms total, 108 µs avg, 71 µs to 135 µs max
Rows sent : 1 avg, 1 to 1 max (40.57%)
Rows examined : 387.71k avg, 225.34k to 725.90k max (33.79%)
Database : yy
Users : yyr@10.4.1.83 (33.87%), yyr@10.4.1.81 (33.33%), yyr@10.4.1.82 (32.80%)
Query abstract:
SET timestamp=N; SELECT COUNT(N) AS cnt FROM qz_feed WHERE (qz_id='S') AND (feed_key IN (S1)) AND (created>N) AND feed_id IN(SELECT from_id FROM qz_notice WHERE type_detail='S' AND member_id=N AND isnew=N);
Query sample:
SET timestamp=1354637384;
SELECT COUNT(1) AS cnt FROM qz_feed WHERE (qz_id='377') AND (feed_key in ('speech_add')) AND (created>0) AND feed_id IN(SELECT from_id FROM qz_notice WHERE type_detail='speech_add' AND member_id=38011 AND isnew=1);
pt-query-digest slow.log --limit 20 --filter '$event->{Query_time} > 1'
# crontab 每日 02:00 生成前日報告
0 2 * * * /usr/local/bin/mysqlsla -lt slow /var/log/mysql/slow.log -top 20 > /reports/slow_$(date +\%F).txt
Rows examined / Rows sent > 100 通常意味著全表掃描或索引失效,優先優化此類 SQL。
mysqlsla 通過參數化抽象 + 多維度聚合排序,將海量慢查詢日誌壓縮為可讀性極強的 Top-N 報告。掌握 -sort、-sf、-db 組合使用,配合 Rows examined 指標,可在幾分鐘內定位系統核心性能瓶頸,是 MySQL 性能調優工具箱中的必備利器。