MySQL Learning note--sql Statement optimization tool

The basics of MySQL are explained in the previous section, which explains MySQL's statement optimization.

First, positioning slow query

we want to optimize the SQL statement, the first step is to find a slow execution of the statement, then how to locate in a project these slow-performing SQL statements? A method for locating slow queries is described below.

1.1. Database Preparation

First create a database table:

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 ' superior number ', HI Redate date NOT null COMMENT ' entry time ', Sal decimal (7,2) not  null COMMENT ' salary ', Comm DECIMAL (7,2) NOT null COMMENT ' bonus ', DEP TNO mediumint UNSIGNED not NULL default 0 COMMENT ' Department number ') engine=innodb default Charset=utf8;

　　Then we build a storage function that returns a random string with a length of parameter n:

Delimiter $ $create function rand_string (n INT) returns varchar (255) #该函数会返回一个字符串begin declare chars_str varchar (DEFA) Ult ' abcdefghijklmnopqrstuvwxyzabcdefjhijklmnopqrstuvwxyz ';d eclare return_str varchar (255) Default ';d eclare 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;

　Next we create a storage function that returns a random int value:

Delimiter $ $create Function rand_num () returns INT (5) Begin  DECLARE i int default 0; Set i = Floor (10+rand () *500); retur n i;  End $ $delimiter;

Then we create a stored procedure with the two stored functions that we just created, which contains a parameter that represents the number of data bars that are inserted into the data table EMP:

Delimiter $ $create Procedure insert_emp (in Max_num Int (ten)) begindeclare I int default 0;  Set autocommit = 0;   Repeat set i = i + 1; INSERT into EMP values (I, rand_string (6), ' salesman ', 0001,curdate (), 2000,400,rand_num ());  Until I = Max_num end repeat;   Commit End $ $delimiter;

Finally, we call the stored procedure created by the change, inserting 1000w data into the EMP table:

Call Insert_emp (10000000);

1.2. View Slow Query

We can view the number of slow queries with the following command:

Show status like ' Slow_queries ';

Now typing the command in MySQL, you can see that value is 1, this slow query is just bulk inserted 1000w data generated.

This command can only be used to see the number of slow queries, but we have no way of knowing which queries produced slow queries, and if you want to know which queries are causing slow queries, we must modify the MySQL configuration file. Open the MySQL configuration file (the Windows system is the My.ini,linux system is MY.CNF) and add the following code under [Mysqld]:

Log-slow-queries=mysql_slow.loglong_query_time=1

　　At this point we run the following command in MySQL, we can see that Slow_query_log is on, Log_file is also the file we specified:

Mysql> Show variables like ' slow_query% ';  +---------------------+------------------------------+| Variable_name       | Value                        |+---------------------+------------------------------+| slow_query_log      | On                           | | slow_query_log_file | mysql_slow.log |+---------------------+------------------------------+2 rows in Set ( 0.00 sec)

　　Run the following command we can see that we set the slow query time also effective, at this time, as long as the query time is greater than 1s, query statements will be stored in the log file.

Mysql> Show variables like ' long_query_time ';  +-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+1 row in Set ( 0.00 sec)

Now we run a query that has a query time exceeding 1s:　　

Mysql> SELECT * from emp where empno=413345;+--------+--------+----------+-----+------------+---------+--------+- -------+| empno |  ename |  job      | mgr | hiredate   | sal     | comm   | deptno |+--------+--------+----------+---- -+------------+---------+--------+--------+| 413345 | Vvohub | Salesman |   1 | 2014-10-26 | 2000.00 | 400.00 |     |+--------+--------+----------+-----+------------+---------+--------+--------+1 row in Set (6.55 sec)

　　Then look at the data directory under the MySQL installation directory, which produces a slow query log file: Mysql_slow.log, which reads:

/usr/local/mysql/bin/mysqld, Version:5.1.73-log (MySQL Community Server (GPL)). Started with:tcp port:3306  Unix socket:/tmp/mysql.socktime                 Id Command    time:141026 23:24:08# [ Email protected]: root[root] @ localhost []# query_time:6.547536  rows_sent:1  rows_examined:10000000 Use temp; SET timestamp=1414337048; SELECT * from emp where empno=413345;

In this log file, we can know the time the slow query was generated, resulting in a few rows of results, testing several rows of results, and running the statement. Here we can see that this statement produces a result, but detects a 1000w row record, which is a full table scan.

Ii. Explain implementation plan

The slow query log helps us to record all SQL statements that have long queries, and we should use the explain command to look at the MySQL execution plan to find the optimal points before we can optimize the statements.

The use of the explain command is simple and requires only the "explain + SQL statement", as the following command is the result of using explain with our just slow query statement:

Mysql> explain select * from EMP where empno=413345\g;*************************** 1. Row ***************************           id:1  select_type:simple        table:emp         Type:ALLpossible_keys:NULL          key:null      key_len:null          ref:null         rows:10000351        extra:using where1 row in Set (0.00 sec) Error:no Q Uery specified

　　As you can see, the explain command results in a total of the following columns: ID, select_type, table, type, Possible_keys, key, Key_len, ref, rows, Extra, which represent the following meanings, respectively:

1,id:select identifier. This is the query serial number of select;

2. Select_type: Query type, mainly primary (outermost query in subquery), subquery (the first select in subquery inner layer), union (all select after the second select in the Union statement), Simple (except for subqueries or other queries other than union);

3, table: The database that is visited indicates;

4, type: Access to the table, including the following types of all (full table scan), index (full index Scan), rang (Index range scan), ref (in the join statement by the Driver Table Index reference query), EQ_REF (access by primary key or unique index, there will be only one result), Const (read constant, read only once), system table. There is only one piece of data in the table), null (fastest).

5, Possible_keys: query may use the index;

6, Key: The last index selected;

7, Key_len: Index length of the selected index;

8. Ref: Lists a field filter for a table;

9, rows: The estimated number of results;

10, Extra: Query details, may be the following values: Distinct, using filesort (order by operation), using index (the data you are looking for can only be obtained in index), using temporary (use temporary table), The using where (this information is included if you include where, and you do not just get the content through the index).

Thus, with the output of the "explain select * from emp where empno=413345\g" command, we can clearly see that this query is a full table scan statement, the query does not use any index, so its query time will certainly be very slow.

Third, the use of Profiling

In addition to providing the explain command for viewing command execution plans, MySQL also provides the profiling tool for viewing resource consumption during statement queries. First we use the following command to turn on the profiling feature:

Set profiling = 1;

　　Next we execute a query command:

Mysql> SELECT * from emp where empno=413345;+--------+--------+----------+-----+------------+---------+--------+- -------+| empno |  ename |  job      | mgr | hiredate   | sal     | comm   | deptno |+--------+--------+----------+----- +------------+---------+--------+--------+| 413345 | Vvohub | Salesman |   1 | 2014-10-26 | 2000.00 | 400.00 |     |+--------+--------+----------+-----+------------+---------+--------+--------+1 row in Set (6.44 sec)

After the query Profiler feature is turned on, MySQL automatically logs all the profile information for the query that was executed. We then get profile profiles for all of the Query that are saved in the system by using the following command:

Mysql> Show profiles;+----------+------------+--------------------------------------+| query_id | Duration   | Query                                |+----------+------------+--------------------------------------+|        1 | 0.00053000 | Show Tables                          | |        2 | 0.07412700 | SELECT * FROM Dept                   | |        3 | 0.06743300 | SELECT * from Salgrade               | |        4 | 6.44056000 | SELECT * from emp where empno=413345 |+----------+------------+--------------------------------------+4 rows in Set ( 0.00 sec)

We can then use the following command to view the profile information for a particular query:

Mysql> Show profile CPU, block IO for query 4;+--------------------+----------+----------+------------+------------ --+---------------+| Status | Duration | Cpu_user | Cpu_system | block_ops_in | Block_ops_out |+--------------------+----------+----------+------------+--------------+---------------+| Starting | 0.000107 |   0.000072 |            0.000025 |             0 | 0 | | Opening Tables | 0.000021 |   0.000018 |            0.000003 |             0 | 0 | | System Lock | 0.000006 |   0.000004 |            0.000001 |             0 | 0 | | Table Lock | 0.000009 |   0.000008 |            0.000001 |             0 | 0 | | init | 0.000034 |   0.000033 |            0.000002 |             0 | 0 | | Optimizing | 0.000012 |   0.000011 |            0.000001 |             0 | 0 | | Statistics | 0.000014 |   0.000012 |            0.000001 |             0 | 0 | | Preparing | 0.000013 |   0.000012 |            0.000002 | 0| 0 | | Executing | 0.000005 |   0.000005 |            0.000016 |             0 | 0 | | Sending Data | 6.440260 |   7.818553 |            0.178155 |             0 | 0 | | End | 0.000008 |   0.000006 |            0.000011 |             0 | 0 | | Query End | 0.000002 |   0.000002 |            0.000003 |             0 | 0 | | Freeing items | 0.000030 |   0.000013 |            0.000017 |             0 | 0 | | Logging Slow Query | 0.000001 |   0.000000 |            0.000001 |             0 | 0 | | Logging Slow Query | 0.000035 |   0.000020 |            0.000015 |             0 | 0 | | Cleaning Up | 0.000003 |   0.000003 |            0.000000 |             0 | 0 |+--------------------+----------+----------+------------+--------------+---------------+16 rows in Set (0.00 sec)

This profile shows how much time is spent on each step and the CPU and block Io, so that we can optimize the query statement more specifically. As you can see, because this is a full-table scan, the most time-consuming here is on sending data. In addition to this scenario, the following situations can also be time consuming:converting HEAP to MyISAM (when the query results are too large, put the results on disk),create TMP table (creating a temporary table, such as group storage Intermediate results),Copying to TMP table on disk (copy the memory temp table to disk),locked (locked by other queries),logging slow Query (slow query logging).

MySQL Learning note--sql Statement optimization tool