MySQL中提供了一个慢查询的日志记录功能(有点类似 oracle的v$session_longops )，可以把查询SQL语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在；

 可以通过mysqldumpslow、mysqlsla、pt-query-digest ..等工具进行分析慢日志, mysqldumpslow、mysqlsla文本查看，而pt-query-digest工具可导入数据库，方便查看；

 可参考之前博客 MySQL slow query [慢查询] 资料整理  ；

本文主要通过pt-query-digest工具将所需内容导入到数据库(sql按照发起sql者区分),最终将各个用户下所有信息导入到一张总表.再进行分析..

#!/bin/bash

# Descirption:mysql slow_log Statistics
# DATE: 2015年7月1日
# Author: 毛海晴 

#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用户名

#日期设置， 生成慢查询表命令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}'`

# 表数据要保证为空，避免“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};"

#（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命名 数据库表(存在先删除..)

# 由于后续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，用来查询使用

#（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...) 慢查询统计 -------------'

---

 

# 查询SQL 

---

#未使用表自带字段“*_*_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;

显示结果:


注意:

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 工具结果如下：

---

---