Mysql Stress test Script Instance _mysql

This example describes the MySQL pressure test script, shared for everyone to reference. Specifically as follows:

CREATE TABLE Dept

Copy Code code as follows:

CREATE Table Dept (/* Department Table * *
Deptno mediumint UNSIGNED not NULL DEFAULT 0,
Dname VARCHAR not NULL DEFAULT "",
Loc VARCHAR () not NULL DEFAULT ""
) Engine=myisam DEFAULT Charset=utf8;

Create a Table EMP employee

Copy Code code as follows:

CREATE TABLE EMP
(Empno mediumint UNSIGNED not NULL DEFAULT 0,
ename VARCHAR not NULL DEFAULT "",
Job VARCHAR (9) Not NULL DEFAULT "",
Mgr Mediumint UNSIGNED not NULL DEFAULT 0,
HireDate DATE not NULL,
Sal DECIMAL (7,2) is not NULL,
Comm DECIMAL (7,2) not NULL,
Deptno mediumint UNSIGNED not NULL DEFAULT 0
) Engine=myisam DEFAULT Charset=utf8;

Salary scale

Copy Code code as follows:

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);

Randomly generating strings
Define a new command to end the match

Copy Code code as follows:

Delimiter $$

Delete a custom function

Copy Code code as follows:

Drop function Rand_string $$

Here I created a function.

Copy Code code as follows:

Create function rand_string (n INT)
Returns varchar (255)
Begin
DECLARE chars_str varchar (MB) 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 $$

delimiter;
Select Rand_string (6);

Randomly generated department number

Copy Code code as follows:

Delimiter $$
Drop function Rand_num $$

Here we have a custom function

Copy Code code as follows:

Create function Rand_num ()
returns INT (5)
Begin
declare i int default 0;
Set i = Floor (10+rand () *500);
return i;
End $$

delimiter;
Select Rand_num ();

Insert a record into the EMP table (massive data)

Copy Code code as follows:

Delimiter $$
drop procedure Insert_emp $$

CREATE PROCEDURE insert_emp (in Start int (a), in Max_num Int (10))
Begin
declare i int default 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 $$
delimiter;

Call the function you just wrote, 1.8 million records, starting with number 100,001th.

Copy Code code as follows:

Call Insert_emp (100001,1800000);

Inserting records into the Dept table

Copy Code code as follows:

Delimiter $$
drop procedure Insert_dept $$

CREATE PROCEDURE insert_dept (in Start int (a), 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 (Ten), rand_string (8));
Until I = Max_num
End repeat;
Commit
End $$

delimiter;
Call Insert_dept (100,10);

Inserting data into the Salgrade table

Copy Code code as follows:

Delimiter $$
drop procedure Insert_salgrade $$
CREATE PROCEDURE Insert_salgrade (in Start int (a), in Max_num Int (10))
Begin
declare i int default 0;
Set autocommit = 0;
ALTER TABLE emp DISABLE KEYS;
Repeat
Set i = i + 1;
INSERT into Salgrade values (start+i), (Start+i), (start+i));
Until I = Max_num
End repeat;
Commit
End $$
delimiter;
#测试不需要了
#call Insert_salgrade (10000,1000000);

I hope this article is helpful to the design of MySQL database.