Query optimization for MySQL

As a result of the work, the recent customer side of the party has repeatedly responded to their side of the system query slow, after the exclusion, found that their side of the database is not used at all index, simply pit a pen, through the slow query log analysis, for the data table set up the appropriate index, the query speed significantly improved up, So this time also summarizes if the MySQL optimization query.

1. Slow Query

MySQL itself has a slow query time and slow query record, but by default, our MySQL does not log slow query, need to start MySQL, specify the record slow query can

(1) Use show variables like ' long_query_time ' command to view slow query times

The slow query time is now 10s, but we can temporarily modify it with set Long_query_time (the slow query time will be reset back to 10 after the session is closed)

Alternatively, we can view the configuration of the slow query by the show variables like '%slow% ' command

Slow query log is not enabled by default, so we have to open it, there are two methods, one is to set the variable with set, the other is to modify the configuration file directly, it is recommended to modify the variable method (because it is temporary)

To test whether to turn on slow query, you can use Select Sleep (3), after running to the corresponding directory to find the slow query log whether there is a record, there is no more to say.

2. Build large data volumes for testing

Because the previously optimized query stores the customer's data, it is not convenient for display, so we can build a table of big data by ourselves, here we use the stored procedure of MySQL (using stored procedure is nothing more than want to insert data to run the time is reduced, In fact, we can write code through PHP BULK INSERT)

I don't say much about how the stored procedure is written, and I can find a lot of test data code on the Internet, so I'm going to go straight to the code.

(1) Create a table

CREATE TABLE Dept (

Deptno mediumint UNSIGNED not NULL DEFAULT 0 COMMENT ' number ',

Dname VARCHAR () not NULL DEFAULT "" COMMENT ' name ',

Loc VARCHAR (+) not NULL DEFAULT "" COMMENT ' location '

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE EMP (

Empno mediumint UNSIGNED not NULL DEFAULT 0 COMMENT ' number ',

Ename VARCHAR () not NULL DEFAULT "" COMMENT ' name ',

Job VARCHAR (9) Not NULL DEFAULT "" COMMENT ' work ',

Mgr Mediumint UNSIGNED not NULL DEFAULT 0 COMMENT ' ancestor number ',

HireDate date not NULL COMMENT ' entry time ',

Sal DECIMAL (7,2) not NULL COMMENT ' salary ',

Comm DECIMAL (7,2) not NULL COMMENT ' dividend ',

Deptno mediumint UNSIGNED not NULL DEFAULT 0 COMMENT ' Department number '

) Engine=myisam DEFAULT Charset=utf8;

is the Department table (dept) and the Employee table (EMP), where I deliberately did not create the primary key, and so on for the demo

(2) Creating a custom function

Delimiter $$

Create a custom function to return a random number of 1-10

Create function Rand_num ()
returns int
Begin
Return floor (1+rand () *10);
end$$

Create a custom function to return a random string

Create function rand_string (n INT) returns varchar (255) BEGIN DECLARE CHARS_STR varchar (+) Default ' ABCDEFGHIJKLMNOPQRS Tuvwxyzabcdefjhijklmnopqrstuvwxyz ';

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$$

In order to enable the normal execution of the stored procedure, we should first change the statement ending symbol of MySQL to $$, and then change it back with the delimiter command after creation.

(3) Create a stored procedure that inserts data

CREATE PROCEDURE insert_emp (in Start int (ten), in Max_num Int (10))
Begin
declare i int default 0;
#set autocommit = 0 Set autocommit to 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 $$

(4) Call Insert_emp (100001,4000000) to create 400w data

Insert 400w data with 8.5, then we can go to see the slow query log is recorded

Not to be continued ....

Query optimization for MySQL