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

Source: Internet
Author: User
Tags dname

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

[Plain]View plaincopy
  1. Create database bigTable default character set GBK;

2) create a table

# Department table #


[Plain]View plaincopy
  1. Create table dept (
  2. Id int unsigned primary key auto_increment,
  3. Deptno mediumint unsigned not null default 0,
  4. Dname VARCHAR (20) not null default "",
  5. Loc VARCHAR (13) not null default ""
  6. ) ENGINE = innodb default charset = GBK;


# Employee table #

[Plain]View plaincopy
  1. Create table emp
  2. (
  3. Id int unsigned primary key auto_increment,
  4. Empno mediumint unsigned not null default 0,/* Number */
  5. Ename VARCHAR (20) not null default "",/* name */
  6. Job VARCHAR (9) not null default "",/* Work */
  7. Mgr mediumint unsigned not null default 0,/* Superior Number */
  8. Hiredate date not null,/* Start time */
  9. Sal DECIMAL (7,2) not null,/* salary */
  10. Comm DECIMAL (7,2) not null,/* dividend */
  11. Deptno mediumint unsigned not null default 0/* Department ID */
  12. ) 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


[Plain]View plaincopy
  1. Delimiter $ # define a new command to end compliance
  2. Create function rand_string (n INT)
  3. Returns varchar (255) # This function returns a string
  4. Begin
  5. # Chars_str defines a variable chars_str of the varchar (100) type. The default value is 'abcdefghijklmnopqrstuvwxyzabcdefjhijklmnopqrstuvwxyxy ';
  6. Declare chars_str varchar (100) default
  7. 'Abcdefghijklmnopqrstuvwxyzabcdefjhijklmnopqrstuvwxy ';
  8. Declare return_str varchar (255) default '';
  9. Declare I int default 0;
  10. While I <n do
  11. Set return_str = concat (return_str, substring (chars_str, floor (1 + rand () * 52), 1 ));
  12. Set I = I + 1;
  13. End while;
  14. Return return_str;
  15. End $


# Function 2 Creation #

# Used to randomly generate Department numbers

[Plain]View plaincopy
  1. Create function rand_num ()
  2. Returns int (5)
  3. Begin
  4. Declare I int default 0;
  5. Set I = floor (10 + rand () * 500 );
  6. Return I;
  7. 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

[Plain]View plaincopy
  1. Create procedure insert_emp (in start int (10), in max_num int (10 ))
  2. Begin
  3. Declare I int default 0;
  4. # Set autocommit = 0 set autocommit to 0
  5. Set autocommit = 0;
  6. Repeat
  7. Set I = I + 1;
  8. Insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (start + I), rand_string (6), 'salesman', 0001, curdate (), 2000,400, rand_num ());
  9. Until I = max_num
  10. End repeat;
  11. Commit;
  12. End $


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

[Plain]View plaincopy
  1. Call insert_emp (20171,400000 );

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



# Stored Procedure 2 #

# Add random data to the dept table

[Plain]View plaincopy
  1. Create procedure insert_dept (in start int (10), in max_num int (10 ))
  2. Begin
  3. Declare I int default 0;
  4. Set autocommit = 0;
  5. Repeat
  6. Set I = I + 1;
  7. Insert into dept (deptno, dname, loc) values (start + I), rand_string (10), rand_string (8 ));
  8. Until I = max_num
  9. End repeat;
  10. Commit;
  11. End $


Execution of Stored Procedure 2

[Plain]View plaincopy
  1. Delimiter;
  2. Call insert_dept (100,10 );


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


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.