前言：
mysql中提供了一個慢查詢的日志記錄功能(有點類似 oracle的v$session_longops )，可以把查詢sql語句時間大于多少秒的語句寫入慢查詢日志，日常維護中可以通過慢查詢日志的記錄資訊快速準确地判斷問題所在；
可以通過mysqldumpslow、mysqlsla、pt-query-digest ..等工具進行分析慢日志, mysqldumpslow、mysqlsla文本檢視，而pt-query-digest工具可導入資料庫，友善檢視；
本文主要通過pt-query-digest工具将所需内容導入到資料庫(sql按照發起sql者區分),最終将各個使用者下所有資訊導入到一張總表.再進行分析..
<b>腳本内容：</b>
#!/bin/bash
# descirption:mysql slow_log statistics
# date: 2015年7月1日
# author: 毛海晴
#腳本目的：
#每天将慢查詢sql及資訊導入到mysql資料庫表作分析（需要資訊：執行sql使用者{database}、sql文本、執行次數、總執行時間、平均時間、查詢行數、傳回行數）
#腳本編寫思路
#pt-query-digest未發現直接按照database過濾自帶參數（問題 當提供慢sql時需要知道sql在哪個庫執行的 的情況該怎麼辦）
#不過可以考慮使用pt-query-digest自帶使用者限制參數--filter '($event->{user} || "") =~ m/^使用者/i' 可以簡單用使用者充當database再結合mysql.user再分析')
#通過pt-query-digest user 限制後導入資料庫，但導入後并沒有某字段記錄database_name資訊
#是以考慮循環限制并導入database+date命名資料表後手動加database_name字段，預設值為 相應sql執行的使用者名
#最終會生成多表（例如：slow_log_his_2015_07_01_retail_uc、slow_log_his_2015_07_01_retail_pms...）第一個字段是database_name和相應的執行sql使用者名
#表整理：将多表追加到一張slow_log_his_2015_07_01表，再通過mysql sql 取出我們需要字段。
# *********************************************步驟********************************************
## 變量設定 （注釋：變量定義=等号前後不能出現空格，否則會被系統了解成指令）
#登陸名
user='root'
#登陸密碼
slow_root='root'
slow_password=123456
slow_host=寫入遠端庫ip
slow_port='3306'
slow_dbname='test'
#日期設定， 生成慢查詢表指令yyyy_mm_dd 'train_public'字尾名避免多庫導入同一遠端資料庫表名重複，資料覆寫問題。
pt_table=slow_log_his_`date +%y_%m_%d`_train_public
#ip擷取
# ip=`ifconfig |grep "bcast"|awk -f ':' '{print $2}'|awk -f ' ' '{print $1}'`
echo '------------- '`date +%y-%m-%d' '%h:%m:%s`' 開始 "'`hostname`'"(ip...) 慢查詢統計 -------------'
slow_name=`mysql -u$slow_root -p$slow_password -e "show variables like 'slow_query_log_file'"|grep log|awk '{print $2}'`
#--> 一、整合表'slow_log_his_'$date配置配置
# 表資料要保證為空，避免“error 1062 (23000) at line 1: duplicate entry 'xx' for key 'primary'”
#--》1 本次僞例限制--filter '($event->{user} || "") =~ m/^mhq/i'導入空表 mhq使用者并不存在
#--》或2 方案，在insert之前先 delete from 表；
#（1）删除已有并建立
mysql -h${slow_host} -u${slow_root} -p${slow_password} -d${slow_dbname} -b -n -e " drop table if exists ${pt_table};"
pt-query-digest --user=${slow_root} --password=${slow_password} --no-report --history h=${slow_host},d=${slow_dbname},t=${pt_table} --create-history-table --filter '($event->{user} || "") =~ m/^mhq/i' $slow_name.`date +%y%m%d`
#（2）加database_name 字段，保證各個表結構相同
mysql -h${slow_host} -u${slow_root} -p${slow_password} -d${slow_dbname} -b -n -e "alter table ${pt_table} add column database_name varchar(255) not null first ; "
#--> 二、查找database_name，開始循環導入database_name+date命名 資料庫表
#（1）查找所有database_name
for username in `mysql -uroot -hlocalhost -p123456 -n -b -e "select distinct user from mysql.user;"`
do
#（2）使用pt工具将通過--filter 過濾database_name循環導入database_name+date命名 資料庫表(存在先删除..)
mysql -h${slow_host} -u${slow_root} -p${slow_password} -d${slow_dbname} -b -n -e "drop table if exists ${pt_table}_${f};"
pt-query-digest --user=${slow_root} --password=${slow_password} --filter '($event->{user} || "") =~ m/^'$username'/i' --no-report --history h=${slow_host},d=${slow_dbname},t=${pt_table}_${f} --create-history-table $slow_name.`date +%y%m%d`
# 由于後續sql過濾隻用到history表，是以未加“--review h=$host,d=$dbname,t='slow_log_'$date'_'$username --create-review-table”參數
#--> 三、處理循環導入的database_name+date命名表
mysql -h${slow_host} -u${slow_root} -p${slow_password} -d${slow_dbname} -b -n -e "
#（1）向每張表加字段，預設值為database_name，用來查詢使用
alter table ${pt_table}_${f} add column database_name varchar(255) not null default '$username' first ;
#（2）循環将database_name+date命名表資料讀到'slow_log_his_'$date表.
insert into ${pt_table} select * from ${pt_table}_${f};
#（3）清理database_name+date命名表
drop table ${pt_table}_${f};
"
## -e " 冒号需要寫在同一行，否則會提示“mysql: option '-e' requires an argument”
#結束循環
done
echo '------------- '`date +%y-%m-%d' '%h:%m:%s`' 結束 "'`hostname`'"(ip...) 慢查詢統計 -------------'
# pt-query-digest用到的參數注釋：
# --user mysql使用者名
#--password mysql使用者密碼
#--history 将分析結果儲存到表中，分析結果比較詳細，下次再使用--history時，如果存在相同的語句，且查詢所在的時間區間和曆史表中的不同,則會記錄到資料表中，可以通過查詢同一checksum來比較某類型查詢的曆史變化
# --no-report 不列印分析log
# --host mysql伺服器位址
# h 要導入mysql伺服器位址
# d 要導入到的database
# t 要導入到的table
#--create-history-table 當使用--history參數把分析結果輸出到表中時，如果沒有表就自動建立。
# --filter 對輸入的慢查詢按指定的字元串進行比對過濾後再進行分析
#--limit限制輸出結果百分比或數量，預設值是20,即将最慢的20條語句輸出，如果是50%則按總響應時間占比從大到小排序，輸出到總和達到50%位置截止。
# 其他參數
#--since 從什麼時間開始分析，值為字元串，可以是指定的某個”yyyy-mm-dd [hh:mm:ss]”格式的時間點，也可以是簡單的一個時間值：s(秒)、h(小時)、m(分鐘)、d(天)，如12h就表示從12小時前開始統計。
#--until 截止時間，配合—since可以分析一段時間内的慢查詢。
<b># 查詢sql </b>
#未使用表自帶字段“*_*_pct_95”平均值，是因為 sum / ts_cnt 并不相等于*_pct_95字段
select
his.*
from
( select
database_name as "sql發起者",
sample sql_text,
ts_cnt as "執行次數",
round(query_time_sum, 2) as "執行時間(總)",
round(query_time_sum / ts_cnt, 2) as "執行時間(每)",
round(lock_time_sum, 3) as "鎖時間(總)",
round(lock_time_sum / ts_cnt, 3) as "鎖時間 (每)",
round(rows_examined_min / ts_cnt) as "參加運算的記錄平均行數",
round(rows_sent_sum / ts_cnt) as "平均傳回記錄數"
from
slow_log_his_2015_07_03_train_public t
#where database_name like 'retail_%' #database_name not like 'retail_%'
order by
4 desc
) his
limit 10;
<b>顯示結果:</b>
<b>注意:</b>
1、需要在慢啟動日志切割後執行。
2、/*!40001 sql_no_cache */ 表示: 不把查詢結果存儲在查詢緩存中 (類似oracle 的direct path read) ...本環境是由mysqldump引起/*!40001 sql_no_cache */
3、pt-query- digest小瑕疵：
選中部分sql select /*!40001 sql_no_cache */ * from `item_sku` 執行次數 =9 ；
而實際上是select * from 三個分别三次不同的表 ... (review和history 表都記錄到一條資料，執行次數為 9)
而mysqldumpslow 工具結果如下：
count: 3 time=9.89s (29s) lock=0.00s (0s) rows=2453000.0 (7359000), root[root]@localhost
select /*!n sql_no_cache */ * from `item_sku`
count: 3 time=6.68s (20s) lock=0.00s (0s) rows=1545142.0 (4635426), root[root]@localhost
select /*!n sql_no_cache */ * from `item_extension`
count: 3 time=4.01s (12s) lock=0.00s (0s) rows=328886.0 (986658), root[root]@localhost
select /*!n sql_no_cache */ * from `item`
<b>mysql慢日志分析參數的涵義 </b>
` ts_min ` datetime not null default '0000-00-00 00:00:00' comment '最早執行時間' ,
` ts_max` datetime not null default '0000-00-00 00:00:00' comment '最晚執行時間' ,
` ts_cnt` float default null comment '總共執行次數' ,
` query_time_sum` float default null comment '總查詢時間' ,
` query_time_min` float default null comment '最小查詢時間' ,
` query_time_max` float default null comment '最大查詢時間' ,
` query_time_pct_95` float default null comment '平均查詢時間' ,
` query_time_stddev` float default null comment '查詢時間标準差' ,
` query_time_median` float default null comment '查詢時間中位數' ,
` lock_time_sum` float default null comment '總鎖定時間' ,,
` lock_time_min` float default null comment '最小鎖定時間' ,
` lock_time_max` float default null comment '最大鎖定時間' ,
` lock_time_pct_95` float default null comment '平均鎖定時間' ,
` lock_time_stddev` float default null comment '鎖定時間标準差' ,
` lock_time_median` float default null comment '鎖定時間中位數' ,
` rows_sent_sum` float default null comment '總傳回記錄行數' ,
` rows_sent_min` float default null comment '最小傳回記錄數' ,
` rows_sent_max` float default null comment '最大傳回記錄數' ,
` rows_sent_pct_95` float default null comment '平均傳回記錄數' ,
` rows_sent_stddev` float default null comment '發送傳回數标準差' ,
` rows_sent_median` float default null comment '傳回記錄數中位數' ,
` rows_examined_sum` float default null comment '參加運算的記錄總行數' ,
` rows_examined_min` float default null comment '最少參加運算的記錄行數' ,
` rows_examined_max` float default null comment '最多參加運算的記錄行數' ,
` rows_examined_pct_95` float default null comment '平均參加運算的記錄行數' ,
` rows_examined_stddev` float default null comment '參加運算的記錄行數标準差' ,
` rows_examined_median` float default null comment '參加運算的記錄行數中位數' ,
` rows_affected_sum` float default null comment '受影響記錄總行數' ,
` rows_affected_min` float default null comment '最少受影響記錄行數' ,
` rows_affected_max` float default null comment '最大受影響記錄行數' ,
` rows_affected_pct_95` float default null comment '平均受影響記錄行數' ,
` rows_affected_stddev` float default null comment '受影響記錄行數标準差' ,
` rows_affected_median` float default null comment '受影響記錄行數中位數' ,
` rows_read_sum` float default null comment '讀入記錄行總數' ,
` rows_read_min` float default null comment '讀入記錄行最小數' ,
` rows_read_max` float default null comment '讀入記錄行最大數' ,
` rows_read_pct_95` float default null comment '讀入記錄行平均數' ,
` rows_read_stddev` float default null comment '讀入記錄行數标準差' ,
` rows_read_median` float default null comment '讀入記錄行數中位數' ,
` merge_passes_sum` float default null comment '資料表合并總數' ,
` merge_passes_min` float default null comment '資料表合并最小數' ,
` merge_passes_max` float default null comment '資料表合并最大數' ,
` merge_passes_pct_95` float default null comment '資料表合并平均數' ,
` merge_passes_stddev` float default null comment '資料表合并數标準差' ,
` merge_passes_median` float default null comment '資料表合并數中位數' ,
` innodb_io_r_ops_min` float default null comment '統計計劃需要讀取的最小頁數' ,
` innodb_io_r_ops_max` float default null comment '統計計劃需要讀取的最大頁數' ,
` innodb_io_r_ops_pct_95` float default null comment '統計計劃需要讀取的平均頁數' ,
` innodb_io_r_ops_stddev` float default null comment '統計計劃需要讀取的頁數标準差' ,
` innodb_io_r_ops_median` float default null comment '統計計劃需要讀取的頁數中位數' ,
` innodb_io_r_bytes_min` float default null comment '統計計劃需要讀取的最小位元組數' ,
` innodb_io_r_bytes_max` float default null comment '統計計劃需要讀取的最大位元組數' ,
` innodb_io_r_bytes_pct_95` float default null comment '統計計劃需要讀取的平均位元組數' ,
` innodb_io_r_bytes_stddev` float default null comment '統計計劃需要讀取的位元組數标準差' ,
` innodb_io_r_bytes_median` float default null comment '統計計劃需要讀取的位元組數中位數' ,
` innodb_io_r_wait_min` float default null comment '讀取記錄時産生鎖等待的最小時間' ,
` innodb_io_r_wait_max` float default null comment '讀取記錄時産生鎖等待的最大時間' ,
` innodb_io_r_wait_pct_95` float default null comment '讀取記錄時産生鎖等待的平均時間' ,
` innodb_io_r_wait_stddev` float default null comment '讀取記錄時産生鎖等待時間标準差' ,
` innodb_io_r_wait_median` float default null comment '讀取記錄時産生鎖等待時間中位數' ,
` innodb_rec_lock_wait_min` float default null comment '讀取記錄時産生行鎖等待的最小時間' ,
` innodb_rec_lock_wait_max` float default null comment '讀取記錄時産生行鎖等待的最大時間' ,
` innodb_rec_lock_wait_pct_95` float default null comment '讀取記錄時産生行鎖等待的平均時間' ,
` innodb_rec_lock_wait_stddev` float default null comment '讀取記錄時産生行鎖等待時間标準差' ,
` innodb_rec_lock_wait_median` float default null comment '讀取記錄時産生行鎖等待時間中位數' ,
` innodb_queue_wait_min` float default null comment '等待進入 innodb 隊列或在隊列中等待執行所消耗的最小時間' ,
` innodb_queue_wait_max` float default null comment '等待進入 innodb 隊列或在隊列中等待執行所消耗的最大時間' ,
` innodb_queue_wait_pct_95` float default null comment '等待進入 innodb 隊列或在隊列中等待執行所消耗的平均時間' ,
` innodb_queue_wait_stddev` float default null comment '等待進入 innodb 隊列或在隊列中等待執行所消耗時間标準差' ,
` innodb_queue_wait_median` float default null comment '等待進入 innodb 隊列或在隊列中等待執行所消耗時間中位數' ,
` innodb_pages_distinct_min` float default null comment '讀取的獨立頁最小個數' ,
` innodb_pages_distinct_max` float default null comment '讀取的獨立頁最大個數' ,
` innodb_pages_distinct_pct_95` float default null comment '讀取的獨立頁平均個數' ,
` innodb_pages_distinct_stddev` float default null comment '讀取的獨立頁個數标準差' ,
` innodb_pages_distinct_median` float default null comment '讀取的獨立頁個數中位數' ,
` qc_hit_cnt` float default null comment 'query緩沖區的命中次數' ,
` qc_hit_sum` float default null comment 'query緩沖區的命中總數' ,
` full_scan_cnt` float default null comment '全表掃描次數' ,
` full_scan_sum` float default null comment '全表掃描總數' ,
` full_join_cnt` float default null comment '資料表全連接配接次數' ,
` full_join_sum` float default null comment '資料表全連接配接總數' ,
` tmp_table_cnt` float default null comment '記憶體中使用臨時表次數' ,
` tmp_table_sum` float default null comment '記憶體中使用臨時表總數' ,
` disk_tmp_table_cnt` float default null comment '磁盤上使用臨時表總數' ,
` disk_tmp_table_sum` float default null comment '磁盤上使用臨時表總數' ,
` filesort_cnt` float default null comment '記憶體排序次數' ,
` filesort_sum` float default null comment '記憶體排序總數' ,
` disk_filesort_cnt` float default null comment '磁盤排序次數' ,
` disk_filesort_sum` float default null comment '磁盤排序總數'
<b> mysql version = 5.6.19 字段差異</b>
-->增加：
checksum => 校驗值
sample => sql 樣本
-->差異：
原字段 改後字段
disk_tmp_table_cnt => tmp_table_on_disk_cnt
disk_tmp_table_sum => tmp_table_on_disk_sum
disk_filesort_cnt => filesort_on_disk_cnt
disk_filesort_sum => filesort_on_disk_sum
【源于本人筆記】 若有書寫錯誤，表達錯誤，請指正...