Database Tuning tutorial (2) Prepare slow query data and optimize Databases

Source: Internet
Author: User

Database Tuning tutorial (2) Prepare slow query data and optimize Databases

1. Slow query found


In the previous section, we talked about the definition of slow queries. This section describes how to create a large table and prepare data for slow queries.

2. Prepare slow query data


To find slow queries, you must first make slow queries happen. Slow queries are not allowed in a common table of magnitude, unless you define a slow query in milliseconds. Therefore, we must manually create a table of an order of magnitude. Here we will create a table of magnitude 0.4 million (students can also create millions of tables, if your computer is amazing. But under normal circumstances, 100,000 of the data can be seen as slow queries ).

1) Create a database

Create database bigTable default character set GBK;

2) create a table

# Department table #


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 ;


# Employee table #

Create table emp (id int unsigned primary key auto_increment, empno mediumint unsigned not null default 0,/* No. */ename VARCHAR (20) not null default "", /* name */job VARCHAR (9) not null default "",/* Work */mgr mediumint unsigned not null default 0,/* Superior Number */hiredate date not null, /* Start time */sal DECIMAL (7,2) not null,/* salary */comm DECIMAL (7,2) not null, /* bonus */deptno mediumint unsigned not null default 0/* Department No. */) ENGINE = innodb default charset = GBK;


3) create a function

Functions are used to randomly generate data to ensure that each data entry is different.

# Function 1 Creation #

# Create a function to randomly generate a string. This function receives an integer


Delimiter $ # define a new command to end with the create function rand_string (n INT) returns varchar (255) # This function returns a string begin # chars_str defines a variable chars_str, the type is varchar (100). The default value is 'hangzhou'; declare chars_str varchar (100) default 'hangzhou'; 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 $


# Function 2 Creation #

# Used to randomly generate Department numbers

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

 

# Stored Procedure 1 #

# This stored procedure is used to insert a large amount of data into the emp table

Create procedure insert_emp (in start int (10), in max_num int (10) 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, curdate (), 2000,400, rand_num (); until I = max_num end repeat; commit; end $


Execute the stored procedure and add 0.4 million pieces of data to the emp table.

call insert_emp(100001,400000);

Query. We found that the Emp table has inserted 0.4 million records.



# Stored Procedure 2 #

# Add random data to the dept table

create procedure insert_dept(in start int(10),in max_num int(10))begindeclare 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 $$


Execution of Stored Procedure 2

delimiter ;call insert_dept(100,10);


Now, data preparation is complete. We have created a large table emp.


In the next section, we will use the created large tables to find slow queries and try to record slow queries to logs.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.