查詢mysql的操作信息

show status -- 顯示全部mysql操作信息show status like 'com_insert%'; -- 獲得mysql的插入次數;show status like 'com_delete%'; -- 獲得mysql的刪除次數;show status like 'com_select%'; -- 獲得mysql的查詢次數;show status like 'uptime'; -- 獲得mysql服務器運行時間show status like ’connections’; -- 獲得mysql連接次數

查詢mysql的操作信息show [session|global] status like .... 如果你不寫 [session|global] 默認是session 會話，只取出當前窗口的執行，如果你想看所有(從mysql 啟動到現在，則應該 global)

通過查詢mysql的讀寫比例,可以做相應的配置優化;

慢查詢

當Mysql性能下降時，通過開啟慢查詢來獲得哪條SQL語句造成的響應過慢，進行分析處理。當然開啟慢查詢會帶來CPU損耗與日志記錄的IO開銷，所以我們要間斷性的打開慢查詢日志來查看Mysql運行狀態。

慢查詢能記錄下所有執行超過long_query_time時間的SQL語句, 用于找到執行慢的SQL, 方便我們對這些SQL進行優化.

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

慢查詢開啟設置

mysql> show variables like ’long_query_time’; -- 默認情況下，mysql認為10秒才是一個慢查詢+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+mysql> set long_query_time=1; -- 修改慢查詢時間,只能當前會話有效;mysql> set global slow_query_log=’ON’;-- 啟用慢查詢 ,加上global，不然會報錯的;

也可以在配置文件中更改修改mysql配置文件my.ini[windows]/my.cnf[Linux]加入,注意必須在[mysqld]后面加入

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 -- 最長查詢的秒數;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:

利用存儲過程構建一個大的數據庫來進行測試;

數據準備

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 把autocommit設置成0,關閉自動提交; 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 -- 該查詢發生在2015-5-30 15:30:58# User@Host: root[root] @ localhost [127.0.0.1] -- 是誰,在什么主機上發生的查詢# Query_time: 1.134065 Lock_time: 0.000000 Rows_sent: 8 Rows_examined: 4000000 -- Query_time: 查詢總共用了多少時間,Lock_time: 在查詢時鎖定表的時間,Rows_sent: 返回多少rows數據,Rows_examined: 表掃描了400W行數據才得到的結果;SET timestamp=1432971058; -- 發生慢查詢時的時間戳;SELECT * FROM `emp` where ename like ’%mQspyv%’;

開啟慢查詢后每天都有可能有好幾G的慢查詢日志,這個時候去人工的分析明顯是不實際的;

慢查詢分析工具:

mysqldumpslow

該工具是慢查詢自帶的分析慢查詢工具，一般只要安裝了mysql，就會有該工具；

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] -- 后跟參數以及log文件的絕對地址; -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 # 取出查詢時間最慢的3條慢查詢mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log # 得到按照時間排序的前10條里面含有左連接的查詢語句 mysqldumpslow -s r -t 10 -g ’left join’ /var/run/mysqld/mysqld-slow.log # 按照掃描行數最多的

注意: 使用mysqldumpslow的分析結果不會顯示具體完整的sql語句,只會顯示sql的組成結構;

假如: 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慢查詢的一個工具，它可以分析binlog、General log、slowlog，也可以通過SHOWPROCESSLIST或者通過tcpdump抓取的MySQL協議數據來進行分析。可以把分析結果輸出到文件中，分析過程是先對查詢語句的條件進行參數化，然后對參數化以后的查詢進行分組統計，統計出各查詢的執行時間、次數、占比等，可以借助分析結果找出問題進行優化。pt-query-digest是一個perl腳本，只需下載并賦權即可執行。

安裝

wget http://www.percona.com/get/pt-query-digest chmod +x pt-query-digest# 注意這是一個Linux腳本,要指明絕對或相對路徑來使用--或者下載整套工具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

語法及重要選項

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

第一部分：總體統計結果:

標準分析報告解釋

Overall: 總共有多少條查詢，上例為總共266個查詢。Time range: 查詢執行的時間范圍。unique: 唯一查詢數量，即對查詢條件進行參數化以后，總共有多少個不同的查詢，該例為4。total: 總計 min:最小 max: 最大 avg:平均95%: 把所有值從小到大排列，位置位于95%的那個數，這個數一般最具有參考價值。median: 中位數，把所有值從小到大排列，位置位于中間那個數。

第二部分: 查詢分組統計結果:

這部分對查詢進行參數化并分組，然后對各類查詢的執行情況進行分析，結果按總執行時長，從大到小排序。Response: 總的響應時間。time: 該查詢在本次分析中總的時間占比。calls: 執行次數，即本次分析總共有多少條這種類型的查詢語句。R/Call: 平均每次執行的響應時間。Item : 查詢對象

第三部分：每一種查詢的詳細統計結果:

由上圖可見,1號查詢的詳細統計結果，最上面的表格列出了執行次數、最大、最小、平均、95%等各項目的統計。Databases: 庫名Users: 各個用戶執行的次數（占比）Query_time distribution : 查詢時間分布, 長短體現區間占比，本例中1s-10s之間查詢數量沒有,全部集中在10S里面。Tables: 查詢中涉及到的表Explain: 該條查詢的示例

用法示例

(1)直接分析慢查詢文件:

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

(2)分析最近12小時內的查詢：

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

(3)分析指定時間范圍內的查詢：

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) 針對某個用戶的慢查詢

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數據庫的query_review表,如果沒有的話會自動創建;

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協議數據，然后再分析

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 慢查詢的詳細內容，更多關于MySQL 慢查詢的資料請關注好吧啦網其它相關文章！