pt-query-digest
pt-query-digest 屬于 Percona Toolkit 的一個工具，也是使用較多的一個枣购；用于分析slow log,也可以分析MySQL的 binary log 让禀、 general log 日志钢拧。
本文通過pt-query-digest實現(xiàn)慢sql的分析拯爽，優(yōu)化sql
一培己，安裝percona-toolkit
下載:https://www.percona.com/doc/percona-toolkit/2.2/installation.html
安裝在/usr/local/percona-toolkit目錄下
wget percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit.tar.gz
cd percona-toolkit-3.3.1/
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
make && make install
- MySQL開啟慢查詢?nèi)罩静⒃O(shè)置慢查詢時間
mysql> show variables like '%slow_query_log%';
+---------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------------+-------------------------------+
-- 超過一秒的記錄到日志中
mysql> set global long_query_time=1;
開啟慢查詢之后碳蛋，在程序運行一段時間之后，可以拿到記錄的日志
-
使用命令直接分析慢查詢?nèi)罩?/p>
直接拿到的mysql-slow.log看著沒有條理省咨，不容易分析
使用命令分析并生成slow_report.log ：
pt-query-digest slow.log > slow_report.log然后我們可以導(dǎo)出這個文件肃弟，查看其中內(nèi)容
二，分析慢查詢?nèi)罩?/h4>
文件中總共包含三部分：總體統(tǒng)計，sql統(tǒng)計笤受，單個sql的統(tǒng)計
1. 總體統(tǒng)計
# 85.8s user time, 990ms system time, 30.71M rss, 193.21M vsz
# Current date: Mon May 10 11:47:39 2021
# Hostname: xxxx.xxx-xxx.com
# Files: /var/lib/mysql/mysql-slow.log
# Overall: 87.23k total, 73 unique, 0.05 QPS, 0.37x concurrency __________
# Time range: 2021-04-16T03:15:31 to 2021-05-07T08:38:15
# 屬性 總計 最小 最大 平均 95% 標(biāo)準(zhǔn) 中等
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 676605s 2s 905s 8s 23s 12s 4s
# Lock time 242s 0 1s 3ms 4ms 17ms 839us
# Rows sent 8.90M 0 15.53k 107.02 400.73 616.06 8.91
# Rows examine 7.13G 0 8.94M 85.77k 46.68k 412.27k 2.38k
# Query size 161.99M 6 24.91k 1.90k 1.96k 682.67 1.96k
2. sql統(tǒng)計匯總
# Profile
# Rank Query ID Response time Calls R/Call
# ==== =================================== ================ ===== =======
# 1 0x8EBD7078F62A82A7C578540C76F46BC4 602766.9262 8... 75091 8.0272 13.94 SELECT xxxx
# 2 0x40A63F5C50A2324033DB9FCAA2719C4E 18044.3571 2.7% 4131 4.3680 3.07 SELECT xxxx
# 3 0xFB8F32AE0EFAA83C665B91B6E5862D2F 16215.4058 2.4% 2335 6.9445 6.22 SELECT xxxx
# 4 0x2CF3802FA98AFCE8DA5C85F6E8424DCE 12951.3375 1.9% 2390 5.4190 6.56 SELECT xxxx
# 5 0x56A24EC2EC1FFDB2F49A123C34D5E0BD 8612.3662 1.3% 479 17.9799 31... SELECT xxxx
# 6 0x6D73ABA4D5097101273AA5ADB2259759 8328.1423 1.2% 858 9.7065 12.72 SELECT xxxx
# 7 0x75A04B6CA2CBDE5EB7A27A7FC15FFCC1 3864.3549 0.6% 615 6.2835 5.72 SELECT xxxx
# 8 0x886F3B1A59BD9900A6688314B0A3E4E0 3050.7563 0.5% 614 4.9687 2.93 SELECT xxxx
# 9 0xE6AA1C4FE828263924B7C26F5160BD60 680.7256 0.1% 171 3.9809 1.06 SELECT xxxx
# 10 .............
Rank: 排名
Query ID: 語句ID（去掉多余空格和文本字符穷缤，計算hash值）
Response time: 總的響應(yīng)時間和 該查詢在本次分析中總的時間占比
Calls: 執(zhí)行次數(shù)
R/Call: 平均每次執(zhí)行的響應(yīng)時間
3. 單個sql的統(tǒng)計
# Query 1: 0.04 QPS, 0.33x concurrency, ID 0x8EBD7078F62A82A7C578540C76F46BC4 at byte 66396962
# This item is included in the report because it matches --limit.
# Scores: V/M = 13.94
# Time range: 2021-04-16T03:15:31 to 2021-05-07T08:38:15
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 86 75091
# Exec time 89 602767s 2s 281s 8s 23s 11s 4s
# Lock time 64 156s 352us 730ms 2ms 4ms 10ms 839us
# Rows sent 6 554.55k 0 31 7.56 16.81 5.75 5.75
# Rows examine 4 294.12M 110 77.33k 4.01k 10.29k 5.80k 2.38k
# Query size 89 145.64M 1.98k 1.99k 1.99k 1.96k 0.00 1.96k
# Tables
# 設(shè)計到的表
# EXPLAIN /*!50100 PARTITIONS*/
# 具體執(zhí)行的sql語句
SELECT
hg.group_id,
MAX( ham.app_message_id ) latest_message,
COALESCE ( hgrf.last_read_message_id, 0 ) last_read_message_id,
SUM(
CASE
WHEN app_message_id > COALESCE ( last_read_message_id, 0 )
AND ham.receiver_type = 'USER' THEN
1 ELSE 0
END
) unread_message_count
FROM
h_group hg
INNER JOIN h_message hm ON hm.group_id = hg.group_id
INNER JOIN h_app_message ham ON ham.message_id = hm.message_id
AND ham.user_id = 2084
LEFT JOIN h_group_read_flag hgrf ON hg.group_id = hgrf.group_id
AND hgrf.user_id = ham.user_id
AND hgrf.user_type = 0
WHERE
ham.deleted = 0
AND hm.send_flag = 1
GROUP BY
hg.group_id,
hgrf.last_read_message_id
通過以上日志我們可以看出哪些sql執(zhí)行較慢，哪些sql執(zhí)行次數(shù)較多箩兽，然后根據(jù)不同的業(yè)務(wù)需求去分析有問題的sql進(jìn)行優(yōu)化津肛，例如：
對于執(zhí)行慢的sql:
使用explain查詢執(zhí)行計劃，確認(rèn)索引是否正常使用
關(guān)聯(lián)表太多比肄，是否使用冗余字段減少沒必要的表關(guān)聯(lián)
查詢條件復(fù)雜快耿，查詢數(shù)據(jù)量較多，是否可以在業(yè)務(wù)層分多次查詢(有時候多次并不比一次慢)
對于執(zhí)行頻率高的sql:
- 數(shù)據(jù)嘗試使用緩存芳绩，較少數(shù)據(jù)庫的查詢
三掀亥，實際案例分析
SQL分析：執(zhí)行次數(shù)75091，總時間耗費602767s,平均單次8s妥色，最大一次281s搪花，最少也是2s
a. 首先根據(jù)SQL 去優(yōu)化，研究很久SQL沒有優(yōu)化空間
調(diào)試很久嘹害，索引都是正常使用撮竿，時間始終在2s左右，如果某個用戶未讀數(shù)量大笔呀，那花費時間更長
b. 根據(jù)業(yè)務(wù)邏輯拆解SQL, 減少數(shù)據(jù)量幢踏，減少表關(guān)聯(lián)
場景描述和分析：
公司每天會有不定量的推文推送到每個用戶,app_message 會存儲用戶和消息的關(guān)聯(lián)( count=消息數(shù)x用戶數(shù))
總共4張表：
app_message(用戶消息關(guān)聯(lián)表， 主要字段：app_message_id,message_id,user_id),數(shù)據(jù)量1千萬
message(消息表许师，主要字段：message_id房蝉，group_id) 數(shù)據(jù)量近2百萬
h_group(頻道表，主要字段：group_id) 每條推文都有所屬的頻道微渠，數(shù)據(jù)量較少
h_group_read_flag(用戶頻道最新已讀表搭幻，主要字段：last_read_message_id，group_id,user_id) 存儲用戶每個頻道最新已讀消息記錄 (last_read_message_id=app_message_id)逞盆，數(shù)據(jù)量較少
每次用戶打開APP都會通過這4個表關(guān)聯(lián)查詢用戶的未讀數(shù)量以及最新的消息
解決分析：
b. 1: 首先h_group 只是用來關(guān)聯(lián)group_id ,可以在h_app_message 中冗余group_id字段檀蹋，去掉h_group表的關(guān)聯(lián)；
b. 2: h_message的send_flag是撤回推文是0云芦，否則是1（沒有這個關(guān)聯(lián)即可去除h_message表）在撤回推文之后就把h_app_message中相關(guān)數(shù)據(jù)刪除俯逾，這樣h_message也可以不用使用
b. 3: 最后主要是h_app_message表，數(shù)量級較大焕数，然后減少數(shù)量（將跨度較遠(yuǎn)的數(shù)據(jù)按年歸檔處理）
公眾號：Java質(zhì)變之路
