查詢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 -- 最長查詢的秒數(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 sentat: average query timec: count l: lock timer: rows sentt: 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)行分析。可以把分析結(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.rpmrpm -ivh percona-toolkit-2.2.13-1.noarch.rpmwget percona.com/get/percona-toolkit.tar.gztar -zxvf percona-toolkit-2.2.13.tar.gz cd percona-toolkit-2.2.13perl Makefile.PLmake && 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í)長，從大到小排序。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í)間分布, 長短體現(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.txtpt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

(10)分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sqlpt-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)注好吧啦網(wǎng)其它相關(guān)文章！