如何從一個大專案中，迅速的定位執行速度慢的語句. (定位慢查詢)

大部分內容總結自韓順平老師教學

首先我們瞭解mysql資料庫的一些執行狀態如何查詢(比如想知道當前mysql執行的時間/一共執行了多少次select/update/delete.. / 當前連線)

 

show status（用處很多，自己看文件，在此列舉常用的）

 

常用的:

show status like ‘uptime’ ;

show  stauts like ‘com_select’  show stauts like ‘com_insert’ ...類推 update  delete

 

show [session|global] status like .... 如果你不寫  [session|global] 預設是session 會話，指取出當前視窗的執行，如果你想看所有(從mysql 啟動到現在，則應該 global)

show status like ‘connections’;

 

//顯示慢查詢次數

show status like ‘slow_queries’;

如何去定位慢查詢

 

構建一個大表(400 萬)-> 儲存過程構建

 

預設情況下，mysql認為10秒才是一個慢查詢.

 

1. 修改mysql的慢查詢.

 

show variables like ‘long_query_time’ ; //可以顯示當前慢查詢時間

set long_query_time=1 ;//可以修改慢查詢時間

 

構建大表->大表中記錄有要求, 記錄是不同才有用，否則測試效果和真實的相差大.

 

建立:

CREATE TABLE dept( /*部門表*/

deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  /*編號*/

dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名稱*/

loc VARCHAR(13) NOT NULL DEFAULT "" /*地點*/

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

 

 

CREATE TABLE emp

(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*編號*/

ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/

job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級編號*/

hiredate DATE NOT NULL,/*入職時間*/

sal DECIMAL(7,2)  NOT NULL,/*薪水*/

comm DECIMAL(7,2) NOT NULL,/*紅利*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號*/

)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

 

 

CREATE TABLE salgrade

(

grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

losal DECIMAL(17,2)  NOT NULL,

hisal DECIMAL(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 $$

 

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 < 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 $$

 

如果希望在程式中使用，是Ok!

 

建立一個儲存過程

 

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 $$

 

#呼叫剛剛寫好的函式, 1800000條記錄,從100001號開始

call insert_emp(100001,4000000);

 

• 這時我們如果出現一條語句執行時間超過1秒中，就會統計到.
• 如果把慢查詢的sql記錄到我們的一個日誌中

在預設情況下，我們的mysql不會記錄慢查詢，需要在啟動mysql時候，指定記錄慢查詢才可以，通過cmd啟動MySQL，進入MySQL的安裝目錄bin目錄外（你可以shift+右鍵）

bin\mysqld.exe - -safe-mode  - -slow-query-log [mysql5.5 可以在my.ini指定（datadir的地址）]

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

 

先關閉mysql,再啟動, 如果啟用了慢查詢日誌，預設把這個檔案放在

my.ini 檔案中記錄的位置

#Path to the database root

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

 

• 測試,可以看到在日誌中就記錄下我們的mysql慢sql語句.

 

優化問題.

通過 explain 語句可以分析，下篇寫.

新增索引，下篇。