查詢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)行時(shí)間
show status like 'connections'; -- 獲得mysql連接次數(shù)
查詢mysql的操作信息show [session|global] status like .... 如果你不寫 [session|global] 默認(rèn)是session 會(huì)話，只取出當(dāng)前窗口的執(zhí)行，如果你想看所有(從mysql 啟動(dòng)到現(xiàn)在，則應(yīng)該 global)
通過查詢mysql的讀寫比例,可以做相應(yīng)的配置優(yōu)化;
慢查詢
當(dāng)Mysql性能下降時(shí)，通過開啟慢查詢來獲得哪條SQL語句造成的響應(yīng)過慢，進(jìn)行分析處理。當(dāng)然開啟慢查詢會(huì)帶來CPU損耗與日志記錄的IO開銷，所以我們要間斷性的打開慢查詢?nèi)罩緛聿榭碝ysql運(yùn)行狀態(tài)。
慢查詢能記錄下所有執(zhí)行超過long_query_time時(shí)間的SQL語句, 用于找到執(zhí)行慢的SQL, 方便我們對(duì)這些SQL進(jìn)行優(yōu)化.
show variables like "%slow%";-- 是否開啟慢查詢;
show status like "%slow%"; -- 查詢慢查詢SQL狀況;
show variables like "long_query_time"; -- 慢查詢時(shí)間
慢查詢開啟設(shè)置
mysql> show variables like 'long_query_time'; -- 默認(rèn)情況下，mysql認(rèn)為10秒才是一個(gè)慢查詢
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
mysql> set long_query_time=1; -- 修改慢查詢時(shí)間,只能當(dāng)前會(huì)話有效;
mysql> set global slow_query_log='ON';-- 啟用慢查詢 ,加上global，不然會(huì)報(bào)錯(cuò)的;
也可以在配置文件中更改
修改mysql配置文件my.ini[windows]/my.cnf[Linux]加入,注意必須在[mysqld]后面加入
slow_query_log = on -- 開啟日志;
slow_query_log_file = /data/f/mysql_slow_cw.log -- 記錄日志的log文件; 注意:window上必須寫絕對(duì)路徑,比如 D:/wamp/bin/mysql/mysql5.5.16/data/show-slow.log
long_query_time = 2 -- 最長(zhǎng)查詢的秒數(shù);
log-queries-not-using-indexes -- 表示記錄沒有使用索引的查詢
使用慢查詢
Example1:
mysql> select sleep(3);
mysql> show status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 1 |
+---------------------+-------+
-- Slow_queries 一共有一條慢查詢
Example2:
利用存儲(chǔ)過程構(gòu)建一個(gè)大的數(shù)據(jù)庫來進(jìn)行測(cè)試;
數(shù)據(jù)準(zhǔn)備
CREATE TABLE dept(
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '編號(hào)',
dname VARCHAR(20) NOT NULL DEFAULT "" comment '名稱',
loc VARCHAR(13) NOT NULL DEFAULT "" comment '地點(diǎn)'
) 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 '上級(jí)編號(hào)',
hiredate DATE NOT NULL comment '入職時(shí)間',
sal DECIMAL(7,2) NOT NULL comment '薪水',
comm DECIMAL(7,2) NOT NULL comment '紅利',
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '部門編號(hào)'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '雇員表';
CREATE TABLE salgrade(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '等級(jí)',
losal DECIMAL(17,2) NOT NULL comment '最低工資',
hisal DECIMAL(17,2) NOT NULL comment '最高工資'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '工資級(jí)別表';
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)閉自動(dòng)提交;
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: 查詢總共用了多少時(shí)間,Lock_time: 在查詢時(shí)鎖定表的時(shí)間,Rows_sent: 返回多少rows數(shù)據(jù),Rows_examined: 表掃描了400W行數(shù)據(jù)才得到的結(jié)果;
SET timestamp=1432971058; -- 發(fā)生慢查詢時(shí)的時(shí)間戳;
SELECT * FROM `emp` where ename like '%mQspyv%';
開啟慢查詢后每天都有可能有好幾G的慢查詢?nèi)罩?這個(gè)時(shí)候去人工的分析明顯是不實(shí)際的;
慢查詢分析工具:
mysqldumpslow
該工具是慢查詢自帶的分析慢查詢工具，一般只要安裝了mysql，就會(huì)有該工具；
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] -- 后跟參數(shù)以及l(fā)og文件的絕對(duì)地址;
-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 # 取出查詢時(shí)間最慢的3條慢查詢
mysqldumpslow -s t -t 10 -g “l(fā)eft join” /database/mysql/slow-log # 得到按照時(shí)間排序的前10條里面含有左連接的查詢語句
mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log # 按照掃描行數(shù)最多的
注意: 使用mysqldumpslow的分析結(jié)果不會(huì)顯示具體完整的sql語句,只會(huì)顯示sql的組成結(jié)構(gòu);
假如: SELECT * FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000;
mysqldumpslow來顯示
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;
pt-query-digest
說明
pt-query-digest是用于分析mysql慢查詢的一個(gè)工具，它可以分析binlog、General log、slowlog，也可以通過SHOWPROCESSLIST或者通過tcpdump抓取的MySQL協(xié)議數(shù)據(jù)來進(jìn)行分析?？梢园逊治鼋Y(jié)果輸出到文件中，分析過程是先對(duì)查詢語句的條件進(jìn)行參數(shù)化，然后對(duì)參數(shù)化以后的查詢進(jìn)行分組統(tǒng)計(jì)，統(tǒng)計(jì)出各查詢的執(zhí)行時(shí)間、次數(shù)、占比等，可以借助分析結(jié)果找出問題進(jìn)行優(yōu)化。
pt-query-digest是一個(gè)perl腳本，只需下載并賦權(quán)即可執(zhí)行。
安裝
wget http://www.percona.com/get/pt-query-digest
chmod +x pt-query-digest
# 注意這是一個(gè)Linux腳本,要指明絕對(duì)或相對(duì)路徑來使用
--或者下載整套工具
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
語法及重要選項(xiàng)
pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table 當(dāng)使用--review參數(shù)把分析結(jié)果輸出到表中時(shí)，如果沒有表就自動(dòng)創(chuàng)建。
--create-history-table 當(dāng)使用--history參數(shù)把分析結(jié)果輸出到表中時(shí)，如果沒有表就自動(dòng)創(chuàng)建。
--filter 對(duì)輸入的慢查詢按指定的字符串進(jìn)行匹配過濾后再進(jìn)行分析
--limit限制輸出結(jié)果百分比或數(shù)量，默認(rèn)值是20,即將最慢的20條語句輸出，如果是50%則按總響應(yīng)時(shí)間占比從大到小排序，輸出到總和達(dá)到50%位置截止。
--host mysql服務(wù)器地址
--user mysql用戶名
--password mysql用戶密碼
--history 將分析結(jié)果保存到表中，分析結(jié)果比較詳細(xì)，下次再使用--history時(shí)，如果存在相同的語句，且查詢所在的時(shí)間區(qū)間和歷史表中的不同，則會(huì)記錄到數(shù)據(jù)表中，可以通過查詢同一CHECKSUM來比較某類型查詢的歷史變化。
--review 將分析結(jié)果保存到表中，這個(gè)分析只是對(duì)查詢條件進(jìn)行參數(shù)化，一個(gè)類型的查詢一條記錄，比較簡(jiǎn)單。當(dāng)下次使用--review時(shí)，如果存在相同的語句分析，就不會(huì)記錄到數(shù)據(jù)表中。
--output 分析結(jié)果輸出類型，值可以是report(標(biāo)準(zhǔn)分析報(bào)告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于閱讀。
--since 從什么時(shí)間開始分析，值為字符串，可以是指定的某個(gè)”yyyy-mm-dd [hh:mm:ss]”格式的時(shí)間點(diǎn)，也可以是簡(jiǎn)單的一個(gè)時(shí)間值：s(秒)、h(小時(shí))、m(分鐘)、d(天)，如12h就表示從12小時(shí)前開始統(tǒng)計(jì)。
--until 截止時(shí)間，配合—since可以分析一段時(shí)間內(nèi)的慢查詢。
第一部分：總體統(tǒng)計(jì)結(jié)果:
標(biāo)準(zhǔn)分析報(bào)告解釋
Overall: 總共有多少條查詢，上例為總共266個(gè)查詢。
Time range: 查詢執(zhí)行的時(shí)間范圍。
unique: 唯一查詢數(shù)量，即對(duì)查詢條件進(jìn)行參數(shù)化以后，總共有多少個(gè)不同的查詢，該例為4。
total: 總計(jì) min:最小 max: 最大 avg:平均
95%: 把所有值從小到大排列，位置位于95%的那個(gè)數(shù)，這個(gè)數(shù)一般最具有參考價(jià)值。
median: 中位數(shù)，把所有值從小到大排列，位置位于中間那個(gè)數(shù)。
第二部分: 查詢分組統(tǒng)計(jì)結(jié)果:
這部分對(duì)查詢進(jìn)行參數(shù)化并分組，然后對(duì)各類查詢的執(zhí)行情況進(jìn)行分析，結(jié)果按總執(zhí)行時(shí)長(zhǎng)，從大到小排序。
Response: 總的響應(yīng)時(shí)間。
time: 該查詢?cè)诒敬畏治鲋锌偟臅r(shí)間占比。
calls: 執(zhí)行次數(shù)，即本次分析總共有多少條這種類型的查詢語句。
R/Call: 平均每次執(zhí)行的響應(yīng)時(shí)間。
Item : 查詢對(duì)象
第三部分：每一種查詢的詳細(xì)統(tǒng)計(jì)結(jié)果:
由上圖可見,1號(hào)查詢的詳細(xì)統(tǒng)計(jì)結(jié)果，最上面的表格列出了執(zhí)行次數(shù)、最大、最小、平均、95%等各項(xiàng)目的統(tǒng)計(jì)。
Databases: 庫名
Users: 各個(gè)用戶執(zhí)行的次數(shù)（占比）
Query_time distribution : 查詢時(shí)間分布, 長(zhǎng)短體現(xiàn)區(qū)間占比，本例中1s-10s之間查詢數(shù)量沒有,全部集中在10S里面。
Tables: 查詢中涉及到的表
Explain: 該條查詢的示例
用法示例
(1)直接分析慢查詢文件:
pt-query-digest slow.log > slow_report.log
(2)分析最近12小時(shí)內(nèi)的查詢：
pt-query-digest --since=12h slow.log > slow_report2.log
(3)分析指定時(shí)間范圍內(nèi)的查詢：
pt-query-digest slow.log --since '2014-05-17 09:30:00' --until '2014-06-17 10:00:00'> > slow_report3.log
(4)分析只含有select語句的慢查詢
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log
(5) 針對(duì)某個(gè)用戶的慢查詢
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log
(6) 查詢所有所有的全表掃描或full join的慢查詢
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log
(7)把查詢保存到test數(shù)據(jù)庫的query_review表,如果沒有的話會(huì)自動(dòng)創(chuàng)建;
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review --create-review-table slow.log
(8)把查詢保存到query_history表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ history --create-review-table slow.log_20140401
(9)通過tcpdump抓取mysql的tcp協(xié)議數(shù)據(jù)，然后再分析
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
(10)分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log
(11)分析general log
pt-query-digest --type=genlog localhost.log > slow_report11.log
以上就是詳解MySQL 慢查詢的詳細(xì)內(nèi)容，更多關(guān)于MySQL 慢查詢的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- Mysql sql慢查詢監(jiān)控腳本代碼實(shí)例
- MySQL慢查詢?nèi)绾味ㄎ辉斀?/li>
- MySQL慢查詢相關(guān)參數(shù)原理解析
- MySQL開啟慢查詢方法及實(shí)例
- MySQL5.7慢查詢?nèi)罩緯r(shí)間與系統(tǒng)時(shí)間差8小時(shí)原因詳解
- Mysql慢查詢優(yōu)化方法及優(yōu)化原則
- 通過MySQL慢查詢優(yōu)化MySQL性能的方法講解
- MySQL開啟慢查詢?nèi)罩竟δ艿姆椒?/li>
- MySQL慢查詢?nèi)罩镜呐渲门c使用教程
- MySQL 開啟慢查詢?nèi)罩镜姆椒?/li>
- 實(shí)例講解MySQL 慢查詢
