1、 Location execution is slow sql

mysql Slow query log , To record in mysql in Response time exceeds threshold The sentence of , Specifically, the running time exceeds long_query_time It's worth it sql, Will be recorded in the slow query .

By default ,mysql Slow query log is not enabled in the database , You need to set this parameter manually . If it's not for tuning , It is generally not recommended to turn on this parameter , Because turning on the slow query log will more or less bring a certain performance impact .

1.1 Enable the slow query log parameter

１、 Turn on slow_query_log
You can use the following command to check whether the slow query log is enabled ：

mysql> show variables like '%slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.63 sec)

This parameter can be turned on ：

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.59 sec)

After this parameter is turned on , Then check the variables related to this parameter ：
You can see , here slow_query_log It's already open , At the same time, the location of the file on the corresponding disk also appears .

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | ON                                |
| slow_query_log_file | /var/lib/mysql/koping-HP-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

２、 modify long_query_time threshold
Next, check the time threshold setting of slow query ：

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

The default is 10 Seconds or more is slow query , For the convenience of demonstration , Shorten the time to 1 second ：
Global modification ：set global long_query_time=1;
Session modification ：set long_query_time=1;

--　 Global modification 
mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

--　 Session modification 
mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

1.2 View the number of slow queries

Query how many slow query records exist in the current system ：

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

1.3 Case presentation

Here, with the help of the last blog post （Mysql *** 10： Suitable for index creation 11 In this case 、 Not suitable for indexing 7 Cases of ） stay 1.1 Created in the student_info Table to demonstrate , The table contains 100 Ten thousand data .

1.4 Testing and analysis

Run a time-consuming sql sentence ：

mysql> SELECT student_id, COUNT(*) AS num FROM student_info   GROUP BY student_id ORDER BY create_time DESC LIMIT 100;
100 rows in set (1.92 sec)

You can see that sql The statement of exceeds 1 second , Now let's check the number of slow queries , Now it becomes １ 了 .

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

1.5 Slow query log analysis tool ：mysqldumpslow

After sending the slow query , It can be analyzed through the slow query log analysis tool . First, let's look at the parameters of the change ：

[email protected]:~$ mysqldumpslow -h
Option h requires an argument
ERROR: bad option

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time

Next, let's check the current slow query log records ５ What are the slow query statements . You can see , We did see the slow query statement just used in the test .

mysqldumpslow -a -s t -t 5 /var/lib/mysql/koping-HP-slow.log

[email protected]:~# mysqldumpslow -a -s t -t 5 /var/lib/mysql/koping-HP-slow.log

Reading mysql slow query log from /var/lib/mysql/koping-HP-slow.log
Count: 1  Time=1.93s (1s)  Lock=0.00s (0s)  Rows=100.0 (100), root[root]@localhost
  SELECT student_id, COUNT(*) AS num FROM student_info   GROUP BY student_id ORDER BY create_time DESC LIMIT 100

Died at /usr/bin/mysqldumpslow line 167, <> chunk 1.

1.6 Turn off slow query log

１） Stop slow query log function ：

set global slow_query_log=off;

2、 Analyze query statements ：EXPLAIN

2.1 summary

Locate the slow query sql after , You can use EXPALIN perhaps DESCRIBE Tools to do targeted analysis and query statements .

2.2 Basic grammar

EXPALIN or DESCRIBE The syntax of the statement is as follows ：

EXPALIN SELECT select_options
 perhaps 
DESCRIBE SELECT select_options

Be careful ：EXPALIN The output is Implementation plan , This statement is not actually executed .

EXPALIN The functions of each column output by the statement are as follows ：

2.3 Test data preparation

１） establish ２ A watch

CREATE TABLE s1 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;


CREATE TABLE s2 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

２） Create a storage function

DELIMITER //
CREATE FUNCTION rand_string1(n INT) 
	RETURNS VARCHAR(255) # This function returns a string 
BEGIN 
	DECLARE chars_str VARCHAR(100) 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 //
DELIMITER ;

３） establish ２ Stored procedures

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO s1 VALUES(
    (min_num + i),
    rand_string1(6),
    (min_num + 30 * i + 5),
    rand_string1(6),
    rand_string1(10),
    rand_string1(5),
    rand_string1(10),
    rand_string1(10));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;


DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
        SET i = i + 1;
	INSERT INTO s2 VALUES(
        (min_num + i),
		rand_string1(6),
		(min_num + 30 * i + 5),
		rand_string1(6),
		rand_string1(10),
		rand_string1(5),
		rand_string1(10),
		rand_string1(10));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;

４）# Calling stored procedure , Insert into the two tables respectively １ Ten thousand data

mysql> CALL insert_s1(10001,10000);
Query OK, 0 rows affected (9.64 sec)

mysql> CALL insert_s2(10001,10000);
Query OK, 0 rows affected (11.64 sec)

５） Check whether the test data is viewed successfully

mysql> SELECT COUNT(*) FROM s1;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM s2;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

2.4 EXPLAIN Action of each column

2.4.1 table

Each row of records queried corresponds to a single table

mysql> EXPLAIN SELECT * FROM s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL                                  |
|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

2.4.2 id

In a large query statement , Every SELECT Keywords all correspond to a unique id

For example, there are ２ individual select Query statement , Then there is ２ individual id.

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3='a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | s1    | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 9895 |   100.00 | Using where |
|  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9895 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

however , We need to pay attention to , The query optimizer may rewrite the query statements involving subqueries .

For example, the following statement , Although there are ２ individual select, But the query optimizer optimizes , So the result is still only １ individual id：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field='a');
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref             | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL            | 9895 |   100.00 | Using where                        |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key2      | idx_key2 | 5       | dbtest1.s1.key1 |    1 |    10.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

Summary id：

• id If the same , It can be thought of as a group , From top to bottom
• In all groups ,id The bigger the value is. , The higher the priority , Execute first
• concerns ：id Number, each number , Represents an independent query , One sql The fewer query times, the better

2.4.3 select_type

mysql For every one select The small queries represented by keywords have a definition called select_type Properties of , It means that as long as we know the of a small query select_type attribute , I know this What role does small query play in the whole large query . Let's see select_type What values can be taken ：

2.4.4 partitions

Represents the hits in the partition table , Non partition table , This item is NULL. In general, the execution of query statements is planned partitions The values of the columns are NULL

2.4.5 type

A record of the implementation plan represents mysql Access method when executing query on a table , among type Column indicates what the access method is .

The result value from the best to the worst is ：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > All

sql Objectives of performance optimization ： At the very least range Level , The requirement is ref Level , It is best to consts Level .（ Alibaba development manual requirements ）

2.4.6 possible_keys and key

possible_keys The list is shown in a query statement , What are the indexes that may be used to perform a single table query on a table .

key The list shows which indexes are actually used , If NULL, No index is used .

2.4.7 key_len

The actual index length used （ That is, the number of bytes ）

2.4.8 ref

When using index column equivalent queries , Information about the object matching the index column .

This field is associated with type Fields are matched and viewed together .

2.4.9 rows

Estimated record tree to be read , The smaller the value, the better .

2.4.10 filtered

The percentage of records remaining after a table is filtered by search criteria .

For single table queries , This column is meaningless . In the join query, the execution plan records corresponding to the drive table are displayed filtered value , It determines the number of times the driven dynamic table should be executed ：rows * filtered

2.4.11 Extra

Extra It is used to explain some additional information , Can provide mysql How to execute a given query statement .