上一篇文章中說(shuō)到了定位慢sql，拿到了慢sql后，我們要怎么重現(xiàn)問(wèn)題呢？那么就需要造數(shù)據(jù)。函數(shù)和存儲(chǔ)過(guò)程就可以幫助我們?cè)齑罅康臄?shù)據(jù)，用來(lái)重現(xiàn)生產(chǎn)環(huán)境的問(wèn)題。

一、是什么

函數(shù)和存儲(chǔ)過(guò)程都是sql的集合，就是用sql寫(xiě)的一段代碼。函數(shù)與存儲(chǔ)過(guò)程的區(qū)別就是函數(shù)有返回值，存儲(chǔ)過(guò)程沒(méi)有返回值。

二、能干嘛

其實(shí)就是相當(dāng)于我們java封裝的方法啦，可以實(shí)現(xiàn)某個(gè)功能的代碼集，可以復(fù)用，很方便。比如我現(xiàn)在要往一個(gè)表里插入1000萬(wàn)的數(shù)據(jù)，如果要用函數(shù)或者存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)，該怎么做呢？

1. 建庫(kù)建表：

create database bigData;
use bigData;

# 部門(mén)表
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 ""
);

# 員工表
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
);

2. 設(shè)置參數(shù)：

創(chuàng)建函數(shù)的時(shí)候，可能會(huì)報(bào)錯(cuò)：

this function has none of deterministic……

我們得開(kāi)啟一個(gè)參數(shù)，首先執(zhí)行如下語(yǔ)句可以查看該參數(shù)：

show variables like 'log_bin_trust_function_creators';

可以看到現(xiàn)在是off狀態(tài)的，執(zhí)行以下sql將其開(kāi)啟：

set global log_bin_trust_function_creators=1;

不過(guò)之前也說(shuō)過(guò)，通過(guò)這種方式設(shè)置的參數(shù)，一重啟就失效了，所以可以在配置文件的[mysqld]標(biāo)簽下加上這么一行：

log_bin_trust_function_creators=1

3. 創(chuàng)建函數(shù)：

• 創(chuàng)建一個(gè)函數(shù)，用來(lái)產(chǎn)生隨機(jī)字符串，當(dāng)做員工編號(hào)。

delimiter $$
create function rand_string(n int) returns varchar(255)
begin
       declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
       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 $$

解釋一下這個(gè)function：

• 首先用delimiter $$聲明了兩個(gè)美元符$$表示程序的結(jié)束。因?yàn)閒unction里面會(huì)有很多行sql，如果還是分號(hào)表示結(jié)束的話(huà)，那可能function遇到第一個(gè)分號(hào)的時(shí)候就認(rèn)為結(jié)束了，所以這個(gè)相當(dāng)重新定義結(jié)束符號(hào)。

• 然后創(chuàng)建一個(gè)名為rand_string，輸入?yún)?shù)為int類(lèi)型的n，返回值為varchar類(lèi)型；

• 接著定義了一個(gè)字符串chars_str以及返回值return_str；

• 最后循環(huán)從chars_str中截取字符設(shè)置到return_str中。

那么如何驗(yàn)證這個(gè)函數(shù)有沒(méi)有創(chuàng)建成功呢？

我們知道，執(zhí)行：

select now() from dual;

就會(huì)顯示當(dāng)前時(shí)間，是因?yàn)镸ySQL自帶了now()函數(shù)，那么如果我執(zhí)行：

select rand_string(2) from dual;

會(huì)返回字符串，那說(shuō)明函數(shù)創(chuàng)建成功了。

• 創(chuàng)建一個(gè)函數(shù)，用來(lái)生成隨機(jī)數(shù)，當(dāng)做部門(mén)編號(hào)：

delimiter $$
create function rand_num() returns int(5)
begin
   declare i int default 0;
   set i = floor(100 + rand() * 10);
   return i;
end $$

假如要?jiǎng)h除rand_num函數(shù)，那么就是執(zhí)行：

drop function rand_num;

4. 創(chuàng)建存儲(chǔ)過(guò)程：

delimiter $$
create procedure insert_emp(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 emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
       values((start + i), rand_string(6), 'salesman', 0001, curdate(), 2000, 4000, rand_num());
       until i = max_num
       end repeat;
       commit;
end $$

這個(gè)存儲(chǔ)過(guò)程就是往員工表插入數(shù)據(jù)，這里關(guān)閉了自動(dòng)提交，因?yàn)榇鎯?chǔ)過(guò)程里面也很多語(yǔ)句，沒(méi)執(zhí)行一次就提交一次很麻煩，所以等存儲(chǔ)過(guò)程執(zhí)行完手動(dòng)提交。然后再創(chuàng)建往部門(mén)表插數(shù)據(jù)的存儲(chǔ)過(guò)程，如下：

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

5. 調(diào)用存儲(chǔ)過(guò)程：

調(diào)用的sql如下：

delimiter ;
call insert_dept(100, 10);

首先將結(jié)束符改回分號(hào)，然后調(diào)用兩個(gè)存儲(chǔ)過(guò)程，100表示編號(hào)從100開(kāi)始，10表示插入10條數(shù)據(jù)。

執(zhí)行結(jié)果如下：

然后再往emp表插入50萬(wàn)數(shù)據(jù)：

delimiter ;
call insert_emp(100001, 500000);

執(zhí)行結(jié)果：

插50萬(wàn)數(shù)據(jù)22秒就搞定了，還是很快的，接下來(lái)查詢(xún)emp表的數(shù)據(jù)：

select * from emp;

查50萬(wàn)數(shù)據(jù)，耗時(shí)1.39秒，如果把慢查日志的閥值設(shè)置為1s，那么該sql就會(huì)被記錄到日志中了。