Database Tuning Tutorial (ii) Slow query data preparation

First, find slow query

We talked about the definition of a slow query, which we create a large table for data preparation for slow queries.

2. Slow query data preparation

To find a slow query, you first have to make the slow query happen. A slow query cannot occur in a table of ordinary orders of magnitude, unless you have a millisecond for the definition of a slow query. So we have to create a large order of tables manually, and choose to create a table of 400,000 orders of magnitude (students can also create millions if your computer is very powerful.) However, in general, the level 100,000 data can be seen slow query.

1) Create a database

Create database bigTable default character set GBK;

2) Create a table

#部门表 #

CREATE TABLE Dept (id int unsigned primary key auto_increment,deptno mediumint   unsigned not  NULL  DEFAULT 0, DN Ame varchar (+) not  null  default "", loc varchar (+) NOT null default "") Engine=innodb default CHARSET=GBK;

#雇员表 #

CREATE TABLE emp (id int unsigned primary key auto_increment,empno  mediumint unsigned not  NULL  DEFAULT 0,/* number */ename varchar () NOT NULL default "",/* name */job VARCHAR (9) NOT NULL default "",/* work */mgr mediumint UNSIGNED NOT NULL DE FAULT 0,/* Advanced number */hiredate date not null,/* entry time */sal decimal (7,2) not  null,/* salary */comm Decimal (7,2) Not null,/* dividend */ Deptno mediumint UNSIGNED not NULL default 0/* Department number */) Engine=innodb default CHARSET=GBK;

3) Create a function

Functions are used to generate data randomly, ensuring that each piece of data is different

#函数1 Create #

#创建函数. Used to randomly generate a string. The function receives an integer

Delimiter $$ #定义一个新的命令结束符合create function rand_string (n INT) returns varchar (255) #该函数会返回一个字符串begin #chars_str定义一个变量 CHARS_STR, the type is varchar (100), the default value ' ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFJHIJKLMNOPQRSTUVWXYZ '; DECLARE chars_str varchar (+) 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创建 #

#用于随机产生部门编号

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

4) Create a stored procedure

#存储过程一 #

#该存储过程用于往emp表中插入大量数据

CREATE PROCEDURE insert_emp (in Start int (ten), in Max_num Int (ten)) begindeclare I int default 0; #set autocommit = 0 Set autocommit to 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,c Urdate (), 2000,400,rand_num ());  Until I = Max_num end repeat;   Commit End $$

Execute stored procedure, add 400,000 data to EMP table

Call Insert_emp (100001,400000);

Query, found that the EMP table inserted 400,000 records

#存储过程二 #

#往dept表添加随机数据

CREATE PROCEDURE insert_dept (in Start int (ten), in Max_num Int (ten)) begindeclare I int default 0;  Set autocommit = 0;   Repeat set i = i + 1; INSERT INTO Dept (DEPTNO, Dname,loc  ) VALUES ((Start+i), rand_string (Ten), rand_string (8));  Until I = Max_num end repeat;   Commit End $$

executing a stored procedure two

delimiter; call Insert_dept (100,10);

The data is ready to complete. We created a large table emp.

Next, we'll use the large table we've created to find the slow query and try to log the slow query.

Database Tuning Tutorial (ii) Slow query data preparation