mysql压力测试脚本实例

本文实例讲述了mysql压力测试的脚本，分享给大家供大家参考。具体如下： 创建表DEPT CREATE TABLE dept( /*部门表*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT “”, loc VARCHAR(13) NOT NULL DEFAULT “” ) ENGINE=

本文实例讲述了mysql压力测试的脚本，分享给大家供大家参考。具体如下：

创建表DEPT

CREATE TABLE dept( /*部门表*/<br />deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,<br />dname VARCHAR(20)  NOT NULL  DEFAULT "",<br />loc VARCHAR(13) NOT NULL DEFAULT ""<br />) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

创建表EMP雇员

CREATE TABLE emp<br />(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0,<br />ename VARCHAR(20) NOT NULL DEFAULT "",<br />job VARCHAR(9) NOT NULL DEFAULT "",<br />mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,<br />hiredate DATE NOT NULL,<br />sal DECIMAL(7,2)  NOT NULL,<br />comm DECIMAL(7,2) NOT NULL,<br />deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0<br />)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

工资级别表

CREATE TABLE salgrade<br />(<br />grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,<br />losal DECIMAL(17,2)  NOT NULL,<br />hisal DECIMAL(17,2)  NOT NULL<br />)ENGINE=MyISAM DEFAULT CHARSET=utf8;<br />INSERT INT<strong>本文来源gao@daima#com搞(%代@#码@网2</strong>O salgrade VALUES (1,700,1200);<br />INSERT INTO salgrade VALUES (2,1201,1400);<br />INSERT INTO salgrade VALUES (3,1401,2000);<br />INSERT INTO salgrade VALUES (4,2001,3000);<br />INSERT INTO salgrade VALUES (5,3001,9999);

随机产生字符串
定义一个新的命令结束符合

delimiter $$

删除自定的函数

drop  function rand_string $$

这里我创建了一个函数.

create function rand_string(n INT)<br />returns varchar(255)<br />begin <br /> declare chars_str varchar(100) default<br />   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';<br /> declare return_str varchar(255) default '';<br /> declare i int default 0;<br /> while i < n do <br />   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));<br />   set i = i + 1;<br />   end while;<br />  return return_str;<br />  end $$

delimiter ;
select rand_string(6);

随机产生部门编号

delimiter $$<br />drop  function rand_num $$

这里我们又自定了一个函数

create function rand_num( )<br />returns int(5)<br />begin <br /> declare i int default 0;<br /> set i = floor(10+rand()*500);<br />return i;<br />  end $$

delimiter ;
select rand_num();

向emp表中插入记录(海量的数据)

delimiter $$<br />drop procedure insert_emp $$<br /> <br />create procedure insert_emp(in start int(10),in max_num int(10))<br />begin<br />declare i int default 0; <br /> set autocommit = 0;  <br /> repeat<br /> set i = i + 1;<br /> insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());<br />  until i = max_num<br /> end repeat;<br />   commit;<br /> end $$<br />delimiter ;

调用刚刚写好的函数, 1800000条记录,从100001号开始

call insert_emp(100001,1800000);

向dept表中插入记录

delimiter $$<br />drop procedure insert_dept $$

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 values ((start+i) ,rand_string(10),rand_string(8));
until i = max_num
end repeat;
commit;
end $$

delimiter ;
call insert_dept(100,10);

向salgrade 表插入数据

delimiter $$<br />drop procedure insert_salgrade $$<br />create procedure insert_salgrade(in start int(10),in max_num int(10))<br />begin<br />declare i int default 0; <br /> set autocommit = 0;<br /> ALTER TABLE emp DISABLE KEYS;  <br /> repeat<br /> set i = i + 1;<br /> insert into salgrade values ((start+i) ,(start+i),(start+i));<br />  until i = max_num<br /> end repeat;<br />   commit;<br /> end $$<br />delimiter ;<br />#测试不需要了<br />#call insert_salgrade(10000,1000000);

希望本文所述对大家的MySQL数据库程序设计有所帮助。

喜欢 (0)赏

[搞代码]

分享 (0)