通過show status指令了解各種SQL的執行頻率
MySQL用戶端連接配接成功後，通過使用show [session|global] status 指令可以提供伺服器狀态資訊；
其中的session來表示目前的連接配接的統計結果，global來表示自資料庫啟動至今的統計結果，預設是session級别的。
show status 常用指令：
show status like 'com_%';
其中com_xxx表示xxx語句所執行的次數；
重點注意com_select, com_insert, com_update, com_delete通過這幾個參數，可以容易的了解到目前資料庫應用是以插入更新為主還是以查詢為主(來決定不同的存儲引擎)，以及各類的SQL大緻執行比例是多少。
show status like 'com_insert';
show statuslike 'com_delete';
show statuslike 'com_update';
show statuslike 'com_select';
show status like 'connections'; //連接配接到MySQL伺服器的連接配接數
show statuslike 'uptime'; //伺服器的工作時間(機關：s)
show statuslike 'slow_queries'; //慢查詢的次數(預設是慢查詢時間：10S)：不僅是查詢，insert也包括的
如何定位慢查詢(執行效率低)的SQL語句
預設情況下，MySQL認為10秒才是一個慢查詢。
mysql> show variables like 'long_query_time';+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
set long_query_time=1; //修改mysql慢查詢時間
mysql> set long_query_time=1;
Query OK,0 rows affected (0.00sec)
mysql> show variables like 'long_query_time';+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
注意：set隻對本MySQL Client有效。當打開一個新的用戶端的時候，還是預設值10秒。
也可以在my.ini中進行配置，就會全局有效：
long_query_time=1
建構一個大表(400W)用于測試mysql優化語句：
#建立表DEPTCREATE TABLE dept( deptno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,
dnameVARCHAR(20) NOT NULL DEFAULT"",
locVARCHAR(13) NOT NULL DEFAULT""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
#建立表EMP雇員CREATE TABLEemp
(empno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0, enameVARCHAR(20) NOT NULL DEFAULT "", jobVARCHAR(9) NOT NULL DEFAULT "",mgr MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,hiredate DATENOT NULL,salDECIMAL(7,2) NOT NULL,commDECIMAL(7,2) NOT NULL,deptno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0 )ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
#工資級别表CREATE TABLEsalgrade
(
grade MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,
losalDECIMAL(17,2) NOT NULL,
hisalDECIMAL(17,2) NOT NULL)ENGINE=MyISAM DEFAULT CHARSET=utf8;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 $$
#删除自定的函數drop functionrand_string $$
#這裡建立了一個函數.
#rand_string(nINT) rand_string 是函數名 (n INT) //該函數接收一個整數create function rand_string(n INT)returns varchar(255) #該函數會傳回一個字元串begin#chars_str定義一個變量 chars_str,類型是varchar(100),預設值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar(255) default '';declare i int default 0;while i
delimiter ;select rand_string(6);
# 随機産生部門編号
delimiter $$drop functionrand_num $$
#這裡我們又自定了一個函數create functionrand_num( )returns int(5)begin
declare i int default 0;set i = floor(10+rand()*500);returni;end$$
delimiter ;selectrand_num();
#******************************************#向emp表中插入記錄(海量的資料)
delimiter $$drop procedureinsert_emp $$
#随即添加雇員[光标]400wcreate procedure insert_emp(in start int(10),in max_num int(10))begin
declare i int default 0;
#set autocommit =0把autocommit設定成0set autocommit = 0;
repeatset i = i + 1;insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i=max_numendrepeat;commit;end$$
delimiter ;
#調用剛剛寫好的函數, 1800000條記錄,從100001号開始
call insert_emp(100001,4000000);select count(*) fromemp;#4000000#**************************************************************# 向dept表中插入記錄
delimiter $$drop procedureinsert_dept $$create procedure insert_dept(in start int(10),in max_num int(10))begin
declare i int default 0;set autocommit = 0;
repeatset i = i + 1;insert into dept values ((start+i) ,rand_string(10),rand_string(8));
until i=max_numendrepeat;commit;end$$
delimiter ;
call insert_dept(100,10);
#------------------------------------------------
#向salgrade 表插入資料
delimiter $$drop procedureinsert_salgrade $$create procedure insert_salgrade(in start int(10),in max_num int(10))begin
declare i int default 0;set autocommit = 0;ALTER TABLEemp DISABLE KEYS;
repeatset i = i + 1;insert into salgrade values ((start+i) ,(start+i),(start+i));
until i=max_numendrepeat;commit;end$$
delimiter ;
#測試不需要了
#call insert_salgrade(10000,1000000);
#----------------------------------------------
事先将long_query_time設定為1
mysql> select * from emp where empno=234567;+--------+--------+----------+-----+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 234567 | fgqmkp | SALESMAN | 1 | 2014-09-23 | 2000.00 | 400.00 | 444 |
+--------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (8.09 sec)
總共花費了8.09秒
mysql> show status like 'slow_queries';+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 2 |
+---------------+-------+
1 row in set (0.00 sec)
為什麼是2？？？其實統計的并不隻是select，前面通過存儲過程插入資料也算。
将慢查詢的sql記錄到我們的一個日志中
在預設情況下mysql不記錄慢查詢日志，需要在啟動的時候指定
bin\mysqld.exe --safe-mode --slow-query-log [mysql5.5 可以在my.ini指定]
bin\mysqld.exe --log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]
關閉mysql服務并啟動：bin\mysqld.exe --safe-mode --slow-query-log
如果啟用了慢查詢日志，預設把這個檔案放在my.ini 檔案中記錄的位置:datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
下建立了一個：hostname-slow.log的檔案
由于重新開機了mysql服務，為了測試，則需要重新設定慢查詢時間：set long_query_time=1;
mysql> select * from emp where empno=234567;+--------+--------+----------+-----+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 234567 | fgqmkp | SALESMAN | 1 | 2014-09-23 | 2000.00 | 400.00 | 444 |
+--------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (8.01 sec)
那麼在hostname-show.log檔案中就記錄下了如下慢查詢資訊：
# Time: 140923 22:06:13#[email protected]: root[root] @ localhost [127.0.0.1]# Query_time:8.011458 Lock_time: 0.037002 Rows_sent: 1 Rows_examined: 4000000
usemysqltest;SET timestamp=1411481173;select * from emp where empno=234567;
當第二次執行相同的sql語句時，速度會快很多。