資料庫調優教程(二)慢查詢資料準備,資料庫調優
一、 發現慢查詢
上一講我們談論了慢查詢的定義,這一講我們來建立一張大表,為慢查詢做資料準備。
2. 慢查詢資料準備
要想發現慢查詢,首先要使慢查詢發生。在一張普通數量級的表格中是不能發生慢查詢的,除非你對於慢查詢的定義時一個毫秒。因此我們必須手動建立一張大數量級的表,這裡選擇建立一張40萬數量級的表(同學們也可以建立百萬級的,如果你們的電腦很厲害。但是一般情況下,十萬級的資料就可以看出慢查詢了)。
1) 建立資料庫
[plain] view plaincopy
- Create database bigTable default character set GBK;
2) 建立表
#部門表#
[plain] view plaincopy
- CREATE TABLE dept(
- id int unsigned primary key auto_increment,
- deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
- dname VARCHAR(20) NOT NULL DEFAULT "",
- loc VARCHAR(13) NOT NULL DEFAULT ""
- ) ENGINE=INNODB DEFAULT CHARSET=GBK ;
#僱員表#
[plain] view plaincopy
- CREATE TABLE emp
- (
- id int unsigned primary key auto_increment,
- 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=INNODB DEFAULT CHARSET=GBK ;
3) 建立函數
函數用於隨機產生資料,保證每條資料都不同
#函數1 建立#
#建立函數. 用於隨機產生字串。該函數接收一個整數
[plain] view plaincopy
- 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 $$
#函數2建立#
#用於隨機產生部門編號
[plain] view plaincopy
- create function rand_num( )
- returns int(5)
- begin
- declare i int default 0;
- set i = floor(10+rand()*500);
- return i;
- end $$
4) 建立預存程序
#預存程序一#
#該預存程序用於往emp表中插入大量資料
[plain] view plaincopy
- 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 (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
- until i = max_num
- end repeat;
- commit;
- end $$
執行預存程序,往emp表添加40萬條資料
[plain] view plaincopy
- call insert_emp(100001,400000);
查詢,發現Emp表插入了40萬條記錄
#預存程序二#
#往dept表添加隨機資料
[plain] view plaincopy
- create procedure insert_dept(in start int(10),in max_num int(10))
- begin
- declare i int default 0;
- set autocommit = 0;
- repeat
- set i = i + 1;
- insert into dept (deptno ,dname,loc ) values ((start+i) ,rand_string(10),rand_string(8));
- until i = max_num
- end repeat;
- commit;
- end $$
執行預存程序二
[plain] view plaincopy
- delimiter ;
- call insert_dept(100,10);
至此,資料準備完成。我們建立了大表emp。