MySQL慢查詢的方法

show status -- 顯示全部mysql操作信息

show status like "com_insert%"; -- 獲得mysql的插入次數(shù);

show status like "com_delete%"; -- 獲得mysql的刪除次數(shù);

show status like "com_select%"; -- 獲得mysql的查詢次數(shù);

show status like "uptime"; -- 獲得mysql服務(wù)器運(yùn)行時間

show status like 'connections'; -- 獲得mysql連接次數(shù)

show variables like "%slow%";-- 是否開啟慢查詢;
show status like "%slow%"; -- 查詢慢查詢SQL狀況;
show variables like "long_query_time"; -- 慢查詢時間

mysql> show variables like 'long_query_time'; -- 默認(rèn)情況下，mysql認(rèn)為10秒才是一個慢查詢
+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

mysql> set long_query_time=1; -- 修改慢查詢時間,只能當(dāng)前會話有效;
mysql> set global slow_query_log='ON';-- 啟用慢查詢 ,加上global，不然會報錯的;

slow_query_log = on -- 開啟日志;
slow_query_log_file = /data/f/mysql_slow_cw.log -- 記錄日志的log文件; 注意:window上必須寫絕對路徑,比如 D:/wamp/bin/mysql/mysql5.5.16/data/show-slow.log 
long_query_time = 2 -- 最長查詢的秒數(shù);
log-queries-not-using-indexes -- 表示記錄沒有使用索引的查詢

mysql> select sleep(3);

mysql> show status like '%slow%';
+---------------------+-------+
| Variable_name    | Value |
+---------------------+-------+
| Slow_launch_threads | 0   |
| Slow_queries    | 1   |
+---------------------+-------+
-- Slow_queries 一共有一條慢查詢

CREATE TABLE dept(
deptno MEDIUMINT  UNSIGNED NOT NULL DEFAULT 0 comment '編號', 
dname VARCHAR(20) NOT NULL DEFAULT "" comment '名稱', 
loc VARCHAR(13) NOT NULL DEFAULT "" comment '地點'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '部門表' ;

CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, 
ename VARCHAR(20) NOT NULL DEFAULT "" comment '名字', 
job VARCHAR(9) NOT NULL DEFAULT "" comment '工作',
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '上級編號',
hiredate DATE NOT NULL comment '入職時間',
sal DECIMAL(7,2) NOT NULL comment '薪水',
comm DECIMAL(7,2) NOT NULL comment '紅利',
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '部門編號' 
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '雇員表';

CREATE TABLE salgrade(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '等級',
losal DECIMAL(17,2) NOT NULL comment '最低工資',
hisal DECIMAL(17,2) NOT NULL comment '最高工資'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '工資級別表';

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $
create function rand_num() 
returns tinyint(6) READS SQL DATA 
begin 
 declare return_num tinyint(6) default 0;
 set return_num = floor(1+rand()*30);
 return return_num;
end $

delimiter $
create function rand_string(n INT) 
returns varchar(255) READS SQL DATA 
begin 
 declare chars_str varchar(100) default
 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do 
  set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
  set i = i + 1;
 end while;
 return return_str;
end $

delimiter $
create procedure insert_emp(in start int(10),in max_num int(10))
begin
 declare i int default 0; 
 #set autocommit =0 把a(bǔ)utocommit設(shè)置成0,關(guān)閉自動提交;
 set autocommit = 0; 
 repeat
  set i = i + 1;
  insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 commit;
end $

call insert_emp(1,4000000);
SELECT * FROM `emp` where ename like '%mQspyv%'; -- 1.163s

# Time: 150530 15:30:58 -- 該查詢發(fā)生在2015-5-30 15:30:58
# User@Host: root[root] @ localhost [127.0.0.1] -- 是誰,在什么主機(jī)上發(fā)生的查詢
# Query_time: 1.134065 Lock_time: 0.000000 Rows_sent: 8 Rows_examined: 4000000 
-- Query_time: 查詢總共用了多少時間,Lock_time: 在查詢時鎖定表的時間,Rows_sent: 返回多少rows數(shù)據(jù),Rows_examined: 表掃描了400W行數(shù)據(jù)才得到的結(jié)果;
SET timestamp=1432971058; -- 發(fā)生慢查詢時的時間戳;
SELECT * FROM `emp` where ename like '%mQspyv%';

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] -- 后跟參數(shù)以及l(fā)og文件的絕對地址;

 -s      what to sort by (al, at, ar, c, l, r, t), 'at' is default 
        al: average lock time 
        ar: average rows sent
        at: average query time
        c: count 
        l: lock time
        r: rows sent
        t: query time

 -r      reverse the sort order (largest last instead of first)
 -t NUM    just show the top n queries
 -a      don't abstract all numbers to N and strings to 'S'
 -n NUM    abstract numbers with at least n digits within names
 -g PATTERN  grep: only consider stmts that include this string
 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
        default is '*', i.e. match all
 -i NAME   name of server instance (if using mysql.server startup script)
 -l      don't subtract lock time from total time

mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log # 取出使用最多的10條慢查詢 

mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log # 取出查詢時間最慢的3條慢查詢

mysqldumpslow -s t -t 10 -g “l(fā)eft join” /database/mysql/slow-log # 得到按照時間排序的前10條里面含有左連接的查詢語句

 mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log # 按照掃描行數(shù)最多的

Count: 1 Time=1.91s (1s) Lock=0.00s (0s) Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196]
SELECT * FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N;

wget http://www.percona.com/get/pt-query-digest 
chmod +x pt-query-digest
# 注意這是一個Linux腳本,要指明絕對或相對路徑來使用

--或者下載整套工具

wget percona.com/get/percona-toolkit.rpm
rpm -ivh percona-toolkit-2.2.13-1.noarch.rpm

wget percona.com/get/percona-toolkit.tar.gz
tar -zxvf percona-toolkit-2.2.13.tar.gz 
cd percona-toolkit-2.2.13
perl Makefile.PL
make && make install

pt-query-digest [OPTIONS] [FILES] [DSN]

--create-review-table 當(dāng)使用--review參數(shù)把分析結(jié)果輸出到表中時，如果沒有表就自動創(chuàng)建。
--create-history-table 當(dāng)使用--history參數(shù)把分析結(jié)果輸出到表中時，如果沒有表就自動創(chuàng)建。
--filter 對輸入的慢查詢按指定的字符串進(jìn)行匹配過濾后再進(jìn)行分析
--limit限制輸出結(jié)果百分比或數(shù)量，默認(rèn)值是20,即將最慢的20條語句輸出，如果是50%則按總響應(yīng)時間占比從大到小排序，輸出到總和達(dá)到50%位置截止。
--host mysql服務(wù)器地址
--user mysql用戶名
--password mysql用戶密碼
--history 將分析結(jié)果保存到表中，分析結(jié)果比較詳細(xì)，下次再使用--history時，如果存在相同的語句，且查詢所在的時間區(qū)間和歷史表中的不同，則會記錄到數(shù)據(jù)表中，可以通過查詢同一CHECKSUM來比較某類型查詢的歷史變化。
--review 將分析結(jié)果保存到表中，這個分析只是對查詢條件進(jìn)行參數(shù)化，一個類型的查詢一條記錄，比較簡單。當(dāng)下次使用--review時，如果存在相同的語句分析，就不會記錄到數(shù)據(jù)表中。
--output 分析結(jié)果輸出類型，值可以是report(標(biāo)準(zhǔn)分析報告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于閱讀。
--since 從什么時間開始分析，值為字符串，可以是指定的某個”yyyy-mm-dd [hh:mm:ss]”格式的時間點，也可以是簡單的一個時間值：s(秒)、h(小時)、m(分鐘)、d(天)，如12h就表示從12小時前開始統(tǒng)計。
--until 截止時間，配合—since可以分析一段時間內(nèi)的慢查詢。

pt-query-digest slow.log > slow_report.log

pt-query-digest --since=12h slow.log > slow_report2.log

pt-query-digest slow.log --since '2014-05-17 09:30:00' --until '2014-06-17 10:00:00'> > slow_report3.log

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review --create-review-table slow.log

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ history --create-review-table slow.log_20140401

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log

pt-query-digest --type=genlog localhost.log > slow_report11.log