Welcome to pay attention to github.com/hsfxuebao, I hope to help you, if you think it can trouble to click on the Star ha

MySQL Indexing and Tuning 4- Performance Analysis Tools 1(mysqlDumpslow, Show Profile), this article continues to examine the use of performance analysis tools.

6. Analyze the query statement: EXPLAIN

6.1 an overview of the

After locating slow SQL queries, we can use the EXPLAIN or DESCRIBE tools to do targeted analysis queries. The DESCRIBE statement is used in the same way as the EXPLAIN statement and the analysis results are the same.

MySQL has an optimizer module specifically responsible for the optimization of SELECT statements. Its main function is to provide the optimal execution plan for the Query requested by the client by calculating and analyzing the statistics collected in the system. (It thinks the optimal data retrieval method is the most time-consuming, but not necessarily the DBA’s opinion is optimal.)

The execution plan shows how specific queries will be executed next, such as the order in which multiple table joins will be performed, what access method will be used for each table to execute specific queries, and so on. MySQL provides EXPLA statement to help us see the specific execution plan of a query statement. You can understand the output items of EXPLAIN statement, which can be targeted to improve the performance of our query statement.

6.1.1 What can I Do?

The read order of the table
Operation type of data read operation
Which indexes are available
Which indexes are actually used
References between tables
How many rows per table are queried by the optimizer

6.1.2 Official Website:

[5.7] (dev.mysql.com/doc/refman/… [8.0] (dev.mysql.com/doc/refman/…

Version status:

MySQL 5.6.3 previously could only EXPLAIN SELECT; EXPLAIN SELECT, UPDATE, DELETE with MYSQL 5.6.3
Prior to version 5.7, displaying partitions required the explain partitions command; To show Filtered, use the Explain extended command. After version 5.7, the default explain directly shows information in Filtered partitions and filtered.

6.2 Basic Syntax

The syntactic form of the EXPLAIN or DESCRIBE statement is as follows:

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options
Copy the code

If we want to see the execution plan for a particular query, we can put an EXPLAIN in front of the specific query statement, like this:

mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ 1 row in set, 1 warning (0.01 SEC)Copy to clipboardErrorCopiedCopy the code

The various columns in the output of the EXPLAIN statement do the following:

The column name	describe
`id`	Each in a large query statement`SELECT`Each keyword corresponds to a unique`id`
`select_type`	`SELECT`The type of query that the keyword corresponds to
`table`	The name of the table
`partitions`	Matched partition information
`type`	Access methods for a single table
`possible_keys`	Possible indexes
`key`	The index actually used
`key_len`	The actual length of the index used
`ref`	When indexed column equivalence query is used, information about the object with which the index column is matched
`rows`	Estimated number of records to read
`filtered`	The percentage of records that remain after a table has been filtered by search criteria
`Extra`	Some additional information

6.3 Data Preparation

Build tables:

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;
Copy the code

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;
Copy the code

Set the parameterslog_bin_trust_function_creators

Create a function. If an error occurs, enable the following command:

set global log_bin_trust_function_creators=1; The current window is valid only without global.Copy the code

Create a function:

DELIMITER // CREATE FUNCTION rand_string1(n INT) RETURNS VARCHAR(255) # 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 ;Copy the code

Create a stored procedure:

Create a stored procedure for inserting data into s1:

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 ;
Copy the code

Create a stored procedure for inserting data into s2:

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 ;
Copy the code

Calling a stored procedure:

Add 10000 records to s1 table:

CALL insert_s1 00 (10001100);Copy the code

Adding s2 table data: adding 10,000 records:

CALL insert_s2 00 (10001100);Copy the code

6.4 EXPLAIN the function of each column

1. table

No matter how complex our query is, and how many tables are included in the inner side, we will eventually need to perform a single table access for each table, so the design of MySQL specifies that each record output by the EXPLAIN statement corresponds to a single table access method, and that the table column of the record represents the table name of the table. So let’s look at a simpler query:

mysql> EXPLAIN SELECT * FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL | 100.00 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row In set, 1 warning (0.00 SEC)Copy to clipboardErrorCopiedCopy the code

This query only involves a single-table query on s1, so there is only one record in the EXPLAIN output, where the value of the table column is S1, indicating that this record is used to EXPLAIN the single-table access method on S1.

Let’s look at the execution plan of a join query:

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 | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL 100.00 | | | 1 SIMPLE | | NULL s2 | | NULL ALL | NULL | NULL | NULL | 9954 | | 100.00 Using the join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.01 SEC) Copy to clipboardErrorCopiedCopy the code

You can see that the execution plan for this join query has two records with table columns S1 and S2, which are used to specify the access method for s1 and S2, respectively.

2. id

Query statements usually start with the SELECT keyword. Simple query statements have only one SELECT keyword, such as the following query statement:

SELECT * FROM s1 WHERE key1 = 'a';
Copy the code

Slightly more complex join queries also have a single SELECT keyword, such as:

SELECT * FROM s1 INNER JOIN s2
    ON s1.key1 = s2.key1
    WHERE s1.common_field = 'a';
Copy the code

However, there are two cases where multiple SELECT keywords can appear in a query:

The condition in which a query contains subqueries

For example, the following query contains two SELECT keywords:
```
SELECT * FROM s1 
    WHERE key1 IN (SELECT * FROM s2);
Copy the code
```
The case where the query contains a UNION statement

For example, the following query contains two SELECT keywords:
```
SELECT * FROM s1  UNION SELECT * FROM s2;
Copy the code
```

Every time a SELECT keyword appears in a query, the uncle that designed MySQL assigns it a unique ID value. This id value is the first column in the EXPLAIN statement. For example, there is only one SELECT keyword in the following query, so there is only one record with id 1 in the EXPLAIN result:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)
Copy the code

For join queries, multiple tables can be followed in the FROM clause after the SELECT keyword, so in the execution plan of join queries, each table will have one record, but the id value of the records will be the same, for example:

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 | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL 100.00 | | | 1 SIMPLE | | NULL s2 | | NULL ALL | NULL | NULL | NULL | 9954 | | 100.00 Using the join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.01 SEC)Copy the code

It can be seen that in the above join query, the tables S1 and S2 participating in the join respectively correspond to one record, but the corresponding ID value of these two records is 1. It is important to remember that in the execution plan of join query, each table will correspond to a record, and the value of the ID column of these records is the same. The table appearing in the front indicates the driven table, and the table appearing in the back indicates the driven table. So as we can see from the EXPLAIN output above, the query optimizer intends to have s1 table as the driven table and S2 table as the driven table to execute the query.

For queries containing subqueries, multiple SELECT keywords may be involved, so in the execution plan of a query containing subqueries, each SELECT key will have a unique ID value, such as this:

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 | | NULL | | ALL s1 idx_key3 | NULL | NULL | NULL | 9688 | | 100.00 Using the where | | 2 | SUBQUERY | S2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | | 100.00 Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- ----+ 2 rows in set, 1 Warning (0.02 SEC)Copy the code

Table s1 has an independent SELECT keyword in the outer query, so the id of the first record is 1. Table S2 has an independent SELECT keyword in the sub-query, so the ID of the second record is 2.

It is important to note, however, that the query optimizer may rewrite queries involving subqueries to convert them to join queries. So if we want to know if the query optimizer has rewritten a statement containing a subquery, we can simply look at the execution plan, for example:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a'); +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- --+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL s2 | | ALL idx_key3 | NULL | NULL | NULL | 9954 | | 10.00 Using  where; Start temporary | | | 1 SIMPLE | | NULL | s1 ref | idx_key1 | idx_key1 | 303 | xiaohaizi. S2. Key3 | 1 | | End 100.00 temporary | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.00 SEC)Copy the code

As you can see, although our query statement is a subquery, the ids of the records corresponding to the S1 and S2 tables in the execution plan are all 1, indicating that the query optimizer transforms the subquery into a join query.

For a query containing a UNION clause, it is also true that each SELECT keyword has an ID, but there is something special about it, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-------- ---------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-------- -- -- -- -- -- -- -- -- -- + | 1 | PRIMARY | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL 100.00 | | 2 | UNION | | NULL s2 | | NULL ALL | NULL | NULL | NULL | 9954 | | NULL 100.00 | | NULL | UNION RESULT | < union1, 2 > | NULL | | NULL ALL | NULL  | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-------- ---------+ 3 rows in set, 1 Warning (0.00 SEC)Copy the code

What is the third record of the execution plan for this statement? Where id is NULL and table length is weird? Remember what the UNION clause is for. It combines the result sets of multiple queries and deduplicates the records in the result sets. MySQL uses internal temporary tables. As shown in the above query plan, the UNION clause is used to merge and deduplicate the result set of the query with ID 1 and the result set of the query with ID 2, so a temporary table named

is created internally (which is the name of the table column for the third record of the execution plan). A NULL id indicates that the temporary table was created to merge the result sets of two queries.
,>

In contrast to UNION, UNION ALL does not need to be de-duplicated for the final result set. It simply merges the records of multiple query result sets into one and returns it to the user. Therefore, there is no need to use temporary tables. So in the execution plan of a query that contains the UNION ALL clause, there is no record with the NULL id, as follows:

mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | 1 PRIMARY | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL 100.00 | | 2 | UNION | | NULL | | NULL | ALL s2 NULL | NULL | NULL | 9954 | | NULL | 100.00 + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 Rows in set, 1 warning (0.01 SEC)Copy the code

Summary:

Ids that are the same can be considered a group and executed from top to bottom
In all groups, the greater the ID value, the higher the priority and the earlier the execution
Concern: ID Number Each number represents an independent query. The fewer the number of queries in an SQL query, the better

3. select_type

A large query can contain several SELECT keys, each representing a small query, and each SELECT key can contain several tables in the FROM clause (which is used to do join query). Each table corresponds to a record in the execution plan output and has the same ID value for the tables in the same SELECT keyword.

Select_type = select_type; select_type = select_type; select_type = select_type; select_type = select_type Let’s take a look at what select_Type can take (with a brief description in the documentation for accuracy, which will be explained later) :

The name of the	describe
`SIMPLE`	Simple SELECT (not using UNION or subqueries)
`PRIMARY`	Outermost SELECT
`UNION`	Second or later SELECT statement in a UNION
`UNION RESULT`	Result of a UNION
`SUBQUERY`	First SELECT in subquery
`DEPENDENT SUBQUERY`	First SELECT in subquery, dependent on outer query
`DEPENDENT UNION`	Second or later SELECT statement in a UNION, dependent on outer query
`DERIVED`	Derived table
`MATERIALIZED`	Materialized subquery
`UNCACHEABLE SUBQUERY`	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
`UNCACHEABLE UNION`	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

English description is too simple, do not know what to say? Take a closer look at what each of the inner values does:

SIMPLE

If a query does not contain a UNION or subquery, it counts as SIMPLE. For example, select_type is SIMPLE:

mysql> EXPLAIN SELECT * FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL | 100.00 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row In set, 1 Warning (0.00 SEC)Copy the code

Of course, join queries are also of SIMPLE type, such as:

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 | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL 100.00 | | | 1 SIMPLE | | NULL s2 | | NULL ALL | NULL | NULL | NULL | 9954 | | 100.00 Using the join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.01 SEC)Copy the code

PRIMARY

For a large query with a UNION, UNION ALL, or subquery, it consists of several smaller queries, of which the leftmost query with select_type is PRIMARY, for example:

mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-------- ---------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-------- -- -- -- -- -- -- -- -- -- + | 1 | PRIMARY | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL 100.00 | | 2 | UNION | | NULL s2 | | NULL ALL | NULL | NULL | NULL | 9954 | | NULL 100.00 | | NULL | UNION RESULT | < union1, 2 > | NULL | | NULL ALL | NULL  | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-------- ---------+ 3 rows in set, 1 Warning (0.00 SEC)Copy the code

As you can see FROM the result, the leftmost small query SELECT * FROM s1 corresponds to the first record in the execution plan, and its select_type value is PRIMARY.

UNION

For a large query with UNION or UNION ALL, it consists of several small queries, with ALL but the left of the small query whose select_type value is UNION.
UNION RESULT

MySQL select select_type (” select_type “, “select_result”, “select_result”, “select_result”, “select_result”, “select_result”, “select_result”, “select_result”, “select_result”)

SUBQUERY

If the query statement containing the subquery cannot be converted to the corresponding semi-join form, the subquery is irrelevant, and the query optimizer decides to materialize the subquery to execute the subquery, The select_type of the query represented by the first SELECT keyword is SUBQUERY, as in the following query:

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 | | NULL | | ALL s1 idx_key3 | NULL | NULL | NULL | 9688 | | 100.00 Using the where | | 2 | SUBQUERY | S2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | | 100.00 Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- ----+ 2 rows in set, 1 Warning (0.00 SEC)Copy the code

Select_type (PRIMARY); select_type (SUBQUERY); Note that as select_type SUBQUERY is materialized, it only needs to be executed once.

DEPENDENT SUBQUERY

If the query statement containing the SUBQUERY cannot convert to the corresponding semi-join form and the SUBQUERY is a DEPENDENT SUBQUERY, then the select_type of the query represented by the first SELECT keyword is the DEPENDENT SUBQUERY, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'; +----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+---- --+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows  | filtered | Extra | +----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+---- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | | NULL | | ALL s1 idx_key3 | NULL | NULL | NULL | 9688 | | 100.00 Using Where | | 2 | DEPENDENT SUBQUERY | | NULL s2 | ref | idx_key2, idx_key1 | idx_key2 | | 5 xiaohaizi. S1. Key2 | 1 | | 10.00 Using where | +----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+---- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 2 warnings (0.00 SEC)Copy the code

Note that select_type DEPENDENT SUBQUERY may be executed multiple times.

DEPENDENT UNION

In a large query with UNION or UNION ALL, if ALL the smaller queries depend on the outer query, the select_type value of ALL the smaller queries except the leftmost query is DEPENDENT UNION. For example, the following query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1  = 'b'); +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+-------- --+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | 100.00 Using the where | | 2 | DEPENDENT SUBQUERY | | NULL s2 | ref | idx_key1 | idx_key1 | 303 | const 12 | | | 100.00 Using the where; Using the index | | 3 | DEPENDENT UNION | | NULL | s1 ref | idx_key1 | idx_key1 | 303 | const 8 | | | 100.00 Using the where; Using index | | NULL | UNION RESULT | < union2, 3 > | NULL | | NULL ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 4 rows in the set, 1 warning (0.03 SEC)Copy the code

This query is quite complex. The large query contains a sub-query, and the sub-query contains two smaller queries connected by the UNION. SELECT select_type FROM s2 WHERE key1 = ‘a’; SELECT select_type FROM s2 WHERE key1 = ‘a’; SELECT select_type FROM s1 WHERE key1 = ‘b’;

DERIVED

If a query is materialized and contains a DERIVED table, the select_type of the DERIVED table is DERIVED, such as the following query:

mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1; +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+---- ---------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+---- -- -- -- -- -- -- -- -- -- + | 1 | PRIMARY | < derived2 > | NULL | | NULL ALL | NULL | NULL | NULL | 9688 | | 33.33 Using the where | | | 2 DERIVED | | NULL | s1 index | idx_key1 | idx_key1 | 303 | NULL | 9688 | | 100.00 Using index | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+---- ---------+ 2 rows in set, 1 Warning (0.00 SEC)Copy the code

As you can see from the execution plan, the record with ID 2 represents the execution of the subquery. Its select_type is DERIVED, indicating that the subquery is executed materialized. Note that the derived2 column is

, indicating that the query is performed on the derived table after the derived table is materialized.

Note: if the derived table can be executed by merging with the outer query, the execution plan is a different storyCopy the code

MATERIALIZED

When the query optimizer, when executing the statement containing the subquery, chooses to materialize the subquery and connect it with the outer query, the corresponding select_Type attribute of the subquery is MATERIALIZED, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); +----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+---- --+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows  | filtered | Extra | +----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+---- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | ALL s1 idx_key1 | NULL | NULL | NULL | 9688 | | 100.00 Using Where | | | 1 SIMPLE | < subquery2 > | NULL | eq_ref | < auto_key > | < auto_key > | 303 | xiaohaizi. S1. Key1 | 1 | | 100.00 NULL | | 2 | MATERIALIZED | | NULL s2 | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | | 100.00 Using index | +----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+---- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set, 1 warning (0.01 SEC)Copy the code

The ID value of the third record of the execution plan is 2, indicating that this record corresponds to a single-table query. From its select_type value of MATERIALIZED, it can be seen that the query optimizer first converts the sub-query into MATERIALIZED table. And then see the record of the first two execution plan id value is 1, to illustrate the two records corresponding to connect the query, it is important to note the second record table column value is < subquery2 >, that the table is actually id is 2 corresponding sub query execution after the materialized form, and then connect s1 and the physico-chemical tables for the query.

UNCACHEABLE SUBQUERY

If I don’t use it often, I won’t talk too much.
UNCACHEABLE UNION

If I don’t use it often, I won’t talk too much.

4. Partitions (omitted)

If you want to know more, you can test it as follows. Create a partition table:

Select * from p0 where id<100; CREATE TABLE user_partitions (ID INT auto_increment, NAME VARCHAR(12),PRIMARY KEY(id)) PARTITION BY RANGE(id)( PARTITION p0 VALUES less than(100), PARTITION p1 VALUES less than MAXVALUE );Copy the code

DESC SELECT * FROM user_partitions WHERE id>200;
Copy the code

Query for records with ids greater than 200 (200>100, p1 partition) and query for execution plans. Partitions are P1 in accordance with our partition rules

5. Type fostered fostered

As we mentioned earlier, a record of the execution plan represents the access method used by MySQL to perform a query on a table. The type column indicates what the access method is, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.04 sec)
Copy the code

You can see that the value of the type column is ref, indicating that MySQL is about to use the ref access method to perform a query on table S1. But we’ve only talked about the single table access methods for tables using InnoDB’s storage engine. The complete access methods are as follows: System, const, eq_ref, ref, fulltext, ref_or_NULL, index_merge, unique_subquery, index_subquery, range, index, ALL. Of course, we need to talk about it in detail:

system

When there is only one record in the table and the statistics of the storage engine used by the table are accurate, such as MyISAM, Memory, then the method of accessing the table is system. Let’s say we create a new MyISAM table and insert a record into it:

mysql> CREATE TABLE t(i int) Engine=MyISAM; Query OK, 0 rows affected (0.05 SEC) mysql> INSERT INTO t VALUES(1); Query OK, 1 row affected (0.01sec)Copy the code

Then let’s look at the execution plan for querying this table:

mysql> EXPLAIN SELECT * FROM t; +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | 1 | SIMPLE | t | NULL | system | NULL | NULL | NULL | NULL | 1 | | NULL | 100.00 + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 Row in set, 1 Warning (0.00 SEC)Copy the code

You can see that the value of the type column is system.

Tip: you can change the table to use the InnoDB storage engine. Try out the type column for the execution plan.Copy the code

const

We talked about this earlier, that is, the access method to a single table is const if we are matching constant values based on a primary key or a unique secondary index column (a unique index column).

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | | 1 SIMPLE | | NULL | s1 const | PRIMARY | PRIMARY 4 | | const | 1 | | NULL | 100.00 +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01sec)Copy the code

eq_ref

When join queries, if a table is driven by a primary key or the only secondary indexes (the only index) list of contour matching approach to access (if the primary key, or the only secondary indexes were joint index, all indexed column must be equivalent comparison), was the driver table access method is eq_ref, for instance:

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+--------- -+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+--------- + -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL s1 | | ALL PRIMARY | NULL | NULL | NULL | 9688 | | NULL 100.00 | | 1 | SIMPLE | | s2 NULL | eq_ref | PRIMARY | PRIMARY 4 | | xiaohaizi. S1. Id | 1 | | NULL | 100.00 +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+--------- -+-------+ 2 rows in set, 1 warning (0.01sec)Copy the code

From the results of the execution plan, it can be seen that MySQL intends to take S1 as the driven table and S2 as the driven table. The access method of S2 is eq_ref, which indicates that the access to S2 can be carried out through the equivalent matching of primary keys.

ref

When a table is queried using an ordinary secondary index column matching a constant, the access method to the table may be ref.
fulltext

Full-text indexes, which we haven’t covered in detail, skip ~

ref_or_null

When an equivalent matching query is performed on a normal secondary index and the index column value can also be NULL, the access method to the table may be ref_OR_NULL, for example:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL; +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-- ---------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 ref_or_null | idx_key1 | idx_key1 | 303 | const | | | 100.00 9 Using index condition | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set, 1 warning (0.01 SEC)Copy the code

index_merge

Intersection, Union, and sort-union can be used to query a table with only one index. Intersection, Union, and sort-Union can be used to query a table. MySQL uses index merge to perform a query on a certain table:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+- ---------+---------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+- ---------+---------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | Idx_key1, idx_key3 14 | | 303303 | NULL | | 100.00 Using the union (idx_key1 idx_key3); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.01 SEC)Copy the code

From the value of index_merge in the type column of the execution plan, you can see that MySQL intends to use index merge to perform the query on s1.

unique_subquery

Similar to the eq_ref access method for the driven table IN a two-table join, unique_subquery is used for query statements that contain IN subqueries if the query optimizer decides to convert the IN subquery to an EXISTS subquery that can use primary keys for equivalence matching. The value of the type column of the subquery execution plan is unique_subquery, as in the following query statement:

mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+- ---------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | | NULL | | ALL s1 idx_key3 | NULL | NULL | NULL | 9688 | | 100.00 Using the where | | 2 | DEPENDENT SUBQUERY | | NULL s2 | unique_subquery | PRIMARY, idx_key1 | PRIMARY 4 | | func | 1 | | 10.00 Using where | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 2 warnings (0.00 SEC)Copy the code

You can see that the type value of the second record in the execution plan is unique_subquery, indicating that the index of the ID column will be used in the execution of the subquery.

index_subquery

Index_subquery is similar to unique_subquery, except that the tables in the subquery are accessed using normal indexes, such as this:

mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+ ----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | | NULL | | ALL s1 idx_key3 | NULL | NULL | NULL | 9688 | | 100.00 Using the where | | 2 | DEPENDENT SUBQUERY | | NULL s2 | index_subquery | idx_key1, idx_key3 | idx_key3 | 303 | func | 1 | | 10.00 Using where | +----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 2 warnings (0.01 SEC)Copy the code

range

If you use an index to retrieve a range of records, you might use the range access method, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c'); +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- --------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |  Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 range | idx_key1 | idx_key1 | 303 | NULL 27 | | | 100.00 Using the index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.01 SEC)Copy the code

Or:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- --------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |  Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 range | idx_key1 | idx_key1 | 303 | NULL | 294 | | 100.00 Using the index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

index

When we can use index overwrites, but need to scan all index records, the table access method is index, for example:

mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----- ---------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 index | NULL | idx_key_part | 909 | NULL | 9688 | | 10.00 Using the where;  Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set, 1 warning (0.00 SEC)Copy the code

The above query contains only key_part2 in the search list, and only key_part3 in the search condition. These two columns are contained in the idx_KEY_part index, but the search condition key_part3 cannot be directly accessed by the index ref or range. Only the entire idx_KEY_PART index can be scanned, so the value of the type column in the query plan is index.

Tip: once again, for table use the InnoDB storage engines, secondary index record contains only index column and the value of the primary key columns and the clustering index contains user defined all the columns, and some hidden columns, so scan the cost of secondary indexes than a full table scan directly, namely scanning clustering index price lower.Copy the code

ALL

The most familiar full table scan, not much nagging, directly look at the example:

mysql> EXPLAIN SELECT * FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL | 100.00 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row In set, 1 Warning (0.00 SEC)Copy the code

In general, these access methods deteriorate in the order in which we introduce them. Except for the All access method, All the other access methods can use the index. Except for the index_merge access method, All the other access methods can use only one index at most.

Summary:

The resulting values, from best to worst, are: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index Word-wrap: break-word! Important; “> < span style =” max-width: 100%;

Target for SQL performance optimization: at least range level, ref level, preferably ConSTS level. (Requirements of Alibaba Development Manual)

6. Possible_keys and key

In the execution plan output from the EXPLAIN statement, the possible_keys column indicates possible indexes in a single table query on a table, and the key column indicates actual indexes, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'; +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+----- --------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+----- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 ref | idx_key1, idx_key3 | idx_key3 | 303 | const | | | 2.75 6 Using the where | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+----- --------+ 1 row in set, 1 warning (0.01 SEC)Copy the code

The possible_keys column values of the above execution plan are IDx_KEY1 and IDx_KEY3, indicating that the query may use two indexes idX_KEY1 and IDX_KEY3. The key column values are IDx_KEY3, indicating that after the query optimizer calculates the cost of using different indexes, It was decided that it would be cost-effective to use IDx_KEY3 to perform the query.

The possible_keys column is empty when querying a table using the index access method, while the key column shows the actual index used, as in this example:

mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----- ---------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 index | NULL | idx_key_part | 909 | NULL | 9688 | | 10.00 Using the where;  Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set, 1 warning (0.00 SEC)Copy the code

Another point to note is that possible_keys isn’t always better. The more possible indexes you can use, the longer it takes the query optimizer to calculate the cost of the query, so if you can, try to remove indexes that aren’t needed.

7. Key_len fostered fostered

The key_len column represents the maximum length of an index record when the optimizer decides to use it to perform a query, and is made up of three parts:

For an index column that uses a fixed-length type, the maximum length of storage space it actually occupies is the fixed value. For an index column of variable length type in a specified character set, such as an index column of typeVARCHAR(100), the character set used isutf8, then the maximum storage space actually occupied by the column is100 times 3 is 300Bytes.
If the index column can be storedNULLValue,key_lenThe ratio cannot be storedNULLValue is 1 byte more.
For variable-length fields, there will be2 bytesSpace for storageThe actual length of the variable length column.

For example:

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | | 1 SIMPLE | | NULL | s1 const | PRIMARY | PRIMARY 4 | | const | 1 | | NULL | 100.00 +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01sec)Copy the code

Since the id column is of type INT and NULL values cannot be stored, key_len is 4 when the index of the column is used. When an index column can store NULL values, such as:

mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 5; +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | | 1 SIMPLE | | NULL | s1 const | idx_key2 | idx_key2 | | 5 const | 1 | | NULL | 100.00 +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

You can see that the key_len column becomes 5, which is 1 more than the index for the ID column.

For variable length indexed columns, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Copy the code

Since the type of the key1 column is VARCHAR(100), the actual maximum storage space for this column is 300 bytes, and since this column allows NULL values, key_len needs to be incremented by 1, and since this column is a variable length column, key_len needs to be incremented by 2. So ken_len is 303.

Some of you may be wondering: When you talked about the InnoDB row format earlier, didn’t you say that the actual length of the variable-length fields can take up 1 or 2 bytes? Why is it now that no matter three seven twenty-one it takes two bytes? It is important to note that execution plan generation is a function of the MySQL Server layer, not a storage engine specific function. The main reason why MySQL uncle outputs key_len column in the execution plan is to distinguish the number of index columns used in a query that uses a federated index. It is not intended to specify exactly whether it takes 1 byte or 2 bytes to store the actual length of varied-length fields for a particular storage engine. For example, the following query uses the union index IDx_KEY_part:

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a'; +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+----- --+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+----- -- -- + | | 1 SIMPLE | | NULL | s1 ref | idx_key_part | idx_key_part | 303 | const 12 | | | NULL | 100.00 +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+----- --+ 1 row in set, 1 warning (0.00 SEC)Copy the code

We can see from the key_len column in the plan that the value is 303, which means MySQL can only use one of the idx_KEY_part indexes in the above query.

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+---------- +-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+---------- + -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 ref | idx_key_part | idx_key_part | 606 | const, const | 1 | | NULL | 100.00 +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+---------- +-------+ 1 row in set, 1 warning (0.01sec)Copy the code

The ken_len column of the query execution plan has a value of 606, indicating that the two index columns of the combined index IDX_KEY_PART can be used to execute the query.

Exercise: The length of key_len is calculated as follows:

* (character set: UTF8 =3, GBK =2,latin1=1)+1(NULL)+2(latin1=1)

* (character set: utf8=3, GBK =2,latin1=1)+2(character set: utf8=3, GBK =2,latin1=1))

Char (10) fixed field and allowed NULL = 10 * (character set: utf8=3, GBK =2,latin1=1)+1(NULL)

Character set: utf8=3, GBK =2,latin1=1

8. ref

When performing a query using index column equivalence matching conditions, i.e., const, eq_ref, ref, ref_OR_NULL, unique_subquery, or index_subquery, the ref column displays what is equivalent to the index column. Let’s say it’s just a constant or a column. Take a look at the following query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | | 1 SIMPLE | | NULL | s1 ref | idx_key1 | idx_key1 | 303 | const | | | NULL | 100.00 8 +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01sec)Copy the code

The value of the ref column is const, indicating that the object matching the value of the key1 column is a constant when executing a query using the idx_KEY1 index.

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+--------- -+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+--------- + -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL s1 | | ALL PRIMARY | NULL | NULL | NULL | 9688 | | NULL 100.00 | | 1 | SIMPLE | | s2 NULL | eq_ref | PRIMARY | PRIMARY 4 | | xiaohaizi. S1. Id | 1 | | NULL | 100.00 +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+--------- -+-------+ 2 rows in set, 1 Warning (0.00 SEC)Copy the code

It can be seen that the access method of the driven table S2 is eq_ref, and the value of the corresponding ref column is xiaohaizi.s1.id, which indicates that the PRIMARY index will be used in the access of the driven table, that is, the condition for the clustering index to perform equivalent matching with a column. The object matching the id of s2 is xiaohaizi.s1.id (note that the database name is also written here).

Sometimes the object that matches the index column is a function, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1); +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+---------- -------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+---------- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL 100.00 | | 1 | SIMPLE | | s2 NULL | ref | idx_key1 | idx_key1 | 303 | func | 1 | | 100.00 Using index condition | +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+---------- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.00 SEC)Copy the code

Func = func; func = func; func = func; func = func; func = func;

9. Rows oblivious

If the query optimizer decides to perform a query on a table using a full table scan, the rows column of the execution plan represents the number of rows expected to be scanned. If the query is executed using an index, the rows column of the execution plan represents the number of index record rows expected to be scanned. For example:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- --------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |  Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 range | idx_key1 | idx_key1 | 303 | NULL | 266 | | 100.00 Using the index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

We see that the value of the rows column for executing the plan is 266, which means that the query optimizer, after analyzing the cost of running a query using IDx_KEY1, only sees 266 records that satisfy the condition key1 > ‘z’.

10. filtered

The concept of Condition Filtering was proposed in the analysis of the cost of join query, which is a strategy used by MySQL to calculate the driver table fan out:

Student: If you use thetaSingle-table queries performed in full table scan mode, then it is necessary to estimate how many records meet the search conditions when calculating the fan out of the driver table.
Student: If you use thetaA single table scan performed by an index, so when calculating the fan out of the driving table, it is necessary to estimate the number of records that meet the search conditions other than those that use the corresponding index.

For example, the following query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- ---------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows  | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 range | idx_key1 | idx_key1 | 303 | NULL | 266 | | 10.00 Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

As you can see from the key column of the execution plan, this query uses the IDx_KEY1 index to execute the query, and you can see from the rows column that there are 266 records that satisfy key1 > ‘z’. The filtered columns of the execution plan represent the percentage of the 266 records that the query optimizer predicts will meet the remaining search criteria, that is, common_field = ‘a’. The value of the filtered column is 10.00, which means that the query optimizer predicts that 10.00% of the 266 records meet the condition that common_field = ‘a’.

For a single table query, the value of the filtered column is meaningless. We are more concerned with the filtered value of the execution plan record that drives the table in a join query, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- --+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered  | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | ALL s1 idx_key1 | NULL | NULL | NULL | 9688 | | 10.00 Using the where | | | 1 SIMPLE | | NULL s2 | ref | idx_key1 | idx_key1 | 303 | xiaohaizi. S1. Key1 | 1 | | NULL | 100.00 +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.00 SEC)Copy the code

As you can see from the execution plan, the query optimizer intends to treat S1 as the driven table and S2 as the driven table. We can see that the rows column for the drive table S1 execution plan is 9688, filtered at 10.00, which means that the fan out value for drive table S1 is 9688 × 10.00% = 968.8, which means that there are about 968 more queries to be performed on the drive table.

11. Extra oblivious

As the name suggests, the Extra column is used to provide additional information that will help us understand more precisely how MySQL will execute a given query. MySQL provides dozens of additional information, we will not introduce one by one, so we will only pick some common or important additional information to introduce to you.

No tables used

This additional information is prompted when the query does not have a FROM clause, such as:

mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ 1 row in set, 1 warning (0.00 SEC)Copy the code

Impossible WHERE

This additional information is prompted when the WHERE clause of the query statement is always FALSE, for example:

mysql> EXPLAIN SELECT * FROM s1 WHERE 1 ! = 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- ----+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- ----+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- ----+ 1 row in set, 1 warning (0.01 SEC)Copy the code

No matching min/max row

When there is a MIN or MAX aggregation function in the query list, but there is no record that matches the search criteria in the WHERE clause, this additional information is prompted, for example:

mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -----------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -----------+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

Using index

When our query list and search criteria contain only columns belonging to an index, that is, when index overwriting is available, the Extra column will be prompted for this additional information. For example, the following query only uses idx_KEY1 and does not need to return to the table:

mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------- ----+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------- -- -- + | | 1 SIMPLE | | NULL | s1 ref | idx_key1 | idx_key1 | 303 | const 8 | | | 100.00 Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------- ----+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

Using index condition

There are some search criteria that have index columns but do not use indexes, such as the following query:
```
SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
Copy the code
```
Select * from MySQL where key1 > ‘z’ and key1 LIKE ‘%a’ do not use index;
- According to the firstkey1 > 'z'This condition is indexed from secondaryidx_key1To obtain the corresponding secondary index record.
- According to the primary key value of the secondary index record obtained in the previous step, the table is returned to find the complete user record and then check whether the record is consistentkey1 LIKE '%a'This condition adds the records that meet the condition to the final result set.
Key1 LIKE ‘%a’ does not allow range access, but this condition only involves the key1 column.
- According to the firstkey1 > 'z'This condition, locate to secondary indexidx_key1The corresponding secondary index record in.
- For a specified secondary index record, the table is not rushed back, but first checked to see if the record meetskey1 LIKE '%a'If this condition is not met, then there is no need for the secondary index record to return to the table at all.
- To meet thekey1 LIKE '%a'The secondary index record for this condition performs a back-table operation.
We say that table back operation is actually a random IO, which is time-consuming. Therefore, although the above modification only improves a little, it can save the cost of many table back operation. The folks who designed MySQL call their improvement Index Condition Pushdown.

If this feature is pushed Using index conditions during query execution, the Using Index condition will be displayed in the Extra column, as in:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- --------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |  Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | s1 range | idx_key1 | idx_key1 | 303 | NULL | 266 | | 100.00 Using the index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.01 SEC)Copy the code

Using where

The above additional information is prompted in the Extra column when we perform a query on a table using a full table scan and there are search criteria for that table in the WHERE clause of the statement. For example:

mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | 10.00 Using the where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01sec)Copy the code

This additional information is also prompted in the Extra column when index access is used to perform a query on a table and the statement has search criteria other than the columns contained in the index in the WHERE clause. Select * from ‘idx_key1’ where ‘a’ = ‘common_field’; select * from ‘idx_key1’ where ‘a’ = ‘a’;

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------- ----+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------- -- -- + | | 1 SIMPLE | | NULL | s1 ref | idx_key1 | idx_key1 | 303 | const 8 | | | 10.00 Using the where | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------- ----+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

Using join buffer (Block Nested Loop)

MySQL allocates a block of memory called a join buffer to the table that is being driven when the index cannot be used to speed up the query. This is a block based nested loop algorithm (described in the next article).

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len |  ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | 100.00 NULL | | | 1 SIMPLE | | NULL s2 | | NULL ALL | NULL | NULL | NULL | 9954 | | 10.00 Using the where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.03 SEC)Copy the code

Two hints can be displayed in the Extra column of the execution plan for the S2 table:

Using join buffer (Block Nested Loop): This is because of the watchs2Is not an efficient use of the index, so use the next best thingjoin bufferTo reduce thes2The number of times the table is accessed, thereby improving performance.
Using where: You can see there is one in the query statements1.common_field = s2.common_fieldCondition, becauses1Is the drive table,s2Is driven table, so in accesss2Table,s1.common_fieldThe value of is already determined, so actually the querys2The conditions for the table areS2.com mon_field = a constant“, so hintUsing whereAdditional information.

Not exists

When we use left (outer) joins, if the WHERE clause contains a search condition that requires a column of the driven table to be equal to NULL, and that column is Not allowed to store NULL, then the Extra column in the execution plan of the table will indicate Not exists.

mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL; +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- --+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | NULL 100.00 | | | 1 SIMPLE | | NULL s2 | ref | idx_key1 | idx_key1 | 303 | xiaohaizi. S1. Key1 | 1 | | 10.00 Using the where; Not exists | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.00 SEC)Copy the code

S2. Id column IS not allowed to store NULL values. S2. Id IS NULL. This means that it must be the driver table records in the table driven to find matched ON clause condition records are the records of the driver table added to the final result set, so for one of the driving records in the table, if you can find in the driver table 1 the ON clause for the qualified record, then the driver table records will not be added to the final result set, This means that we do not need to find all the records in the driven table that meet the condition of the ON clause, which can save a bit of performance.

Tip: right (outer) connections can be converted to left (outer) connections, so there is no mention of right (outer) connections.Copy the code

Using intersect(…) , Using the union (…). And Using sort_union (…).

Using intersect(…) if Extra column of the execution plan appears Hint that you are ready to execute the query using the Intersect index merge, in parentheses… Indicates the name of the index to be merged. Using union(…) Prompts that you are ready to execute the query using a Union index merge; Using sort_union(…) Prompt, indicating that you are ready to execute the query using sort-union index merge. For example, the execution plan for this query:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND key3 = 'a'; +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+- ---------+-------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+- ---------+-------------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key3, idx_key1 | 303303 | NULL | 1 | | 100.00 Using intersects (idx_key3 idx_key1); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.01 SEC)Copy the code

The Extra column shows Using INTERSECT (idx_KEY3,idx_key1), indicating that MySQL is going to use idx_KEY3 and IDx_KEY1 to merge the query.

Select * from Extra columns where Extra columns are mergedCopy the code

Zero limit

When the argument to our LIMIT clause is 0, which means that we are not going to read any records from the table at all, we will be prompted for additional information like this:

mysql> EXPLAIN SELECT * FROM s1 LIMIT 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Zero limit |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
1 row in set, 1 warning (0.00 sec)
Copy the code

Using filesort

There are cases where indexes can be used to sort records in a result set, such as the following query:

mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | index | NULL          | idx_key1 | 303     | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)
Copy the code

This query can use the IDX_KEY1 index to fetch the 10 records in the KEY1 column, and then perform the table back operation. In most cases, you can only sort files in memory (when there are fewer records) or on disk (when there are more records). A Using filesort prompt is displayed in the Extra column of the execution plan if a query needs to be executed Using filesort, for example:

mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | 100.00 Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ 1 row in set, 1 warning (0.00 SEC)Copy the code

If you have a large number of records that need to be sorted using filesort, this process can be very costly. It is best to find a way to change the execution mode from file sorting to index sorting.

Using temporary

MySQL may use temporary tables to perform some functions, such as deduplication, sorting, etc. For example, in the process of executing many queries with DISTINCT, GROUP BY, UNION clauses, if the index cannot be effectively used to complete the query, MySQL may use temporary tables to perform some functions, such as deduplication, sorting, etc. MySQL will most likely seek to create internal temporary tables to execute queries. If an internal temporary table is used in the query, a Using temporary prompt is displayed in the Extra column of the execution plan, for example:

mysql> EXPLAIN SELECT DISTINCT common_field FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- ---+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | 100.00 Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ 1 row in set, 1 warning (0.00 SEC)Copy the code

Such as:

mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | 100.00 Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

The Extra column contains not only a Using temporary prompt but also a Using filesort prompt, but also an ORDER BY clause. Select * from GROUP BY; select * from GROUP BY; select * from GROUP BY; select * from GROUP BY; select * from GROUP BY;

EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY common_field;
Copy the code

If we do not want to sort a query that contains the GROUP BY clause, we need to explicitly write ORDER BY NULL, like this:

mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY NULL; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- ---+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- -- -- -- + | | 1 SIMPLE | | NULL | | NULL ALL s1 | NULL | NULL | NULL | 9688 | | 100.00 Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------- --+ 1 row in set, 1 warning (0.00 SEC)Copy the code

This time, there is no hint of Using filesort in the execution plan, which means that the query can be executed without the cost of sorting records by files.

Using temporary is not a good sign because it is expensive to create and maintain temporary tables, so it is best to use indexes instead of temporary tables.

mysql> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- ----+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- -- -- + | | 1 SIMPLE | | NULL | s1 index | idx_key1 | idx_key1 | 303 | NULL | 9688 | | 100.00 Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------- ----+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

Select * from idx_KEY1; select * from idx_key1;

Start temporary, End temporary

The query optimizer will first try to convert the IN subquery to semi-join. Semi-join has several execution strategies. DuplicateWeedout DuplicateWeedout DuplicateWeedout DuplicateWeedout DuplicateWeedout DuplicateWeedout Select Start TEMPORARY from the Extra column of the drive table and End TEMPORARY from the Extra column of the drive table

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a'); +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- --+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL s2 | | ALL idx_key3 | NULL | NULL | NULL | 9954 | | 10.00 Using  where; Start temporary | | | 1 SIMPLE | | NULL | s1 ref | idx_key1 | idx_key1 | 303 | xiaohaizi. S2. Key3 | 1 | | End 100.00 temporary | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.00 SEC)Copy the code

LooseScan

If the LooseScan execution policy is used when converting the In subquery to semi-join, the LooseScan prompt is displayed In the Extra column of the execution plan In the driver table, for example:

mysql> EXPLAIN SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'z'); +----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+------- ---+-------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+------- ---+-------------------------------------+ | 1 | SIMPLE | s2 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 270 | 100.00 | Using the where; Using index; LooseScan | | | 1 SIMPLE | | NULL | s1 ref | idx_key3 | idx_key3 | 303 | xiaohaizi. S2. Key1 | 1 | | NULL | 100.00 +----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.01 SEC)Copy the code

FirstMatch(tbl_name)

If the In subquery is converted to semi-join, the Extra column of the execution plan will display the FirstMatch(tbl_name) prompt, such as:

mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key1 FROM s2 where s1.key3 = s2.key3); +----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+---- ------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+---- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | | NULL | | ALL s1 idx_key3 | NULL | NULL | NULL | 9688 | | 100.00 Using the where | | | 1 SIMPLE | | NULL s2 | ref | idx_key1, idx_key3 | idx_key3 | 303 | xiaohaizi. S1. Key3 | 1 | | 4.87 Using  where; FirstMatch(s1) | +----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+---- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 2 warnings (0.00 SEC)Copy the code

Summary:

EXPLAIN does not take into account various caches EXPLAIN does not show the optimizations MySQL makes when executing a query EXPLAIN does not tell you about triggers, stored procedures, or the impact of user-defined functions on the query. Some statistics are estimated and not exact

7. Further use of EXPLAIN

7.1 EXPLAIN four output formats

Here’s the output format for EXPLAIN. EXPLAIN can be output in four formats: traditional, JSON, TREE, and visual output. Users can choose their own format as needed.

1. Traditional format

The traditional format is straightforward and the output is a table outlining the query plan.

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
Copy the code

2. The JSON format

The EXPLA workn statement output described in the first format is missing an important attribute that measures how well the execution is planned – cost. JSON format is the most detailed format of output information among the four formats, including the cost of execution information.

JSON format: Add F0RMAT=JS0N between the EXPLAIN words and the actual query.

   EXPLAIN FORMAT=JSON SELECT ....
Copy the code

EXPLAIN Column vs. JSON (from MySQL 5.7 documentation)

This gives us a JSON-formatted execution plan that contains the cost of the plan, such as this:

mysql> EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": "Cost_info ": {"query_cost": "3197.16"} "nested_loop": {"table": {"table_name": "s1", # s1 is the driver table" access_type": Possible_keys: [idx_key1], rows_examined_per_scan: 9688, # scan 9688 records "rows_produced_per_JOIN ": 968, # drive table S1 fan out is 968 "filtered": "Cost_info ": {"read_cost": "1840.84", # explain later "eval_cost": "Data_read_per_join ": "1M", "used_columns": "193.76", "used_columns": [# to execute queries involved in the columns of the "id", "key1", "key2", "key3", "key_part1", "key_part2", "key_part3", "common_field]", Alter table s1 alter table s1 alter table s1 alter table s1 alter table s1 "((`xiaohaizi`.`s1`.`common_field` = 'a') and (`xiaohaizi`.`s1`.`key1` is not null))" } }, { "table": { "table_name": Possible_keys: [# possible index "idx_key2"], "key": [key_parts], "key_length": "5", # key_len "ref": Rows_examined_per_scan: 1, rows_produced_per_join: 2 968, # fan out of the filtered table S2 is 968 (since there are no additional tables to connect to, this value is not used) "filtered": "Index_condition ", % from # condition filtering "(` xiaohaizi `. ` s1 `. ` key1 ` = ` xiaohaizi `. ` s2 `. ` key2 `)", "cost_info" : {" read_cost ":" 968.80 ", # later explain "eval_cost" : Data_read_per_join: "1M" # data_read_per_join: "1M" # used_columns": [# to execute queries involved in the columns of the "id", "key1", "key2", "key3", "key_part1", "key_part2", "key_part3", "Common_field"]}} 1 row in set, 2 warnings (0.00 SEC)Copy the code

We EXPLAIN the output of the EXPLAIN FORMAT=JSON statement with # followed by a comment, but you may wonder if the costs in “cost_info” look strange. How are they calculated? Let’s look at the “cost_info” section of s1:

"Cost_info" : {" read_cost ":" 1840.84 ", "eval_cost" : "193.76", "prefix_cost" : "2034.60", "data_read_per_join" : "1 m}"Copy the code

Read_cost consists of the following two parts:

IOThe cost of
detectionRows × (1-filter)Records ofCPUThe cost of

"Rows and filter are the output columns of the previous execution plan. In an execution plan in JSON format," rows "is equivalent to rows_examined_per_scan, whose names remain unchanged.Copy the code

Eval_cost is calculated as follows:

The cost of detecting rows × Filter records.
Prefix_cost is the cost of querying table S1 separately, i.e.

read_cost + eval_cost
Data_read_per_join represents the amount of data that needs to be read in this query, so we won’t talk about that much.

Note: there is no need to pay attention to the weird way MySQL calculates read_cost and eval_cost. Prefix_cost is the cost of query s1.Copy the code

The “cost_info” part of the S2 table looks like this:

"Cost_info" : {" read_cost ":" 968.80 ", "eval_cost" : "193.76", "prefix_cost" : "3197.16", "data_read_per_join" : "1 m}"Copy the code

Since s2 is a driven table, it may be read multiple times. Here read_cost and eval_cost are the cumulative values after accessing S2 for multiple times. We mainly pay attention to the inner prefix_cost value, which represents the estimated cost of the whole join query. The sum of the cost of a single query on s1 and multiple queries on S2, i.e.

968.80 + 193.76 + 2034.60 = 3197.16
Copy the code

3. The TREE format

TREE format is a new format introduced after version 8.0.16. It mainly describes how to query according to the relationship between various parts of the query and the execution order of each part.

mysql> EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G *************************** 1. row *************************** EXPLAIN: Nested loop inner join (cost=1360.08 rows=990) -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1013.75 rows=990) -> Table scan on s1 (cost=1013.75 rows=9895)  -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1) 1 row in set, 1 warning (0.00 SEC)Copy the code

4. Visual output

Visual output, you can visually view the MySQL execution plan through MySQL Workbench. A visual query plan can be generated by clicking on the magnifying glass icon in the Workbench.

7.2 Use of SHOW WARNINGS

After viewing the execution plan of a query using the EXPLAN statement, you can then use the SHOW WARNINGS statement to view some extended information about the execution plan of the query, such as this:

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
Copy the code

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
 Level: Note
 Code: 1003
Message: /* select#1 */ select `atguigu`.`s1`.`key1` AS `key1`,`atguigu`.`s2`.`key1`
AS `key1` from `atguigu`.`s1` join `atguigu`.`s2` where ((`atguigu`.`s1`.`key1` =
`atguigu`.`s2`.`key1`) and (`atguigu`.`s2`.`common_field` is not null))
1 row in set (0.00 sec)
Copy the code

As you can see, SHOW WARN has three fields, Level, Code, and Message. Our most common type is Code 1003, and when Code is 1003, the Message field displays information similar to what the query optimizer would have rewritten our query statement. S2.common_field IS NOT NULL. This will cause the query optimizer to optimize the left join query to an inner join query. This can be seen from the Message field of SHOW WARN workers. The original LEFT JOIN has become JOIN.

8. Analysis optimizer execution plan: Trace

SET optimizer_trace="enabled=on",end_markers_in_json=on;

set optimizer_trace_max_mem_size=1000000;
Copy the code

After this function is enabled, the following statements can be analyzed:

SELECT
INSERT
REPLACE
UPDATE
DELETE
EXPLAIN
SET
DECLARE
CASE
IF
RETURN
CALL

Test: Execute the following SQL statement

select * from student where id < 10;
Copy the code

Finally, query information_schema.optimizer_trace to see how MySQL executes SQL:

select * from information_schema.optimizer_trace\G
Copy the code

* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * * / / part 1: QUERY QUERY: Select * from student where id < 10 TRACE: {"steps": [{"join_preparation": {/ / preparation "select#" : 1, "steps" : [{" expanded_query ": "/* select#1 */ select `student`.`id` AS `id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS  `age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)" } ] /* steps */ } /* Join_process */}, {"join_optimization": {" object #": 1, "steps": [{"condition_processing": {"original_condition": "WHERE", "original_condition": "(' student '. 'id' < 10)", "steps": [{"transformation": "equality_propagation", "resulting_condition": "(`student`.`id` < 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`student`.`id` < 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`student`.`id` < 10)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": /* substitute_generated_columns */, {"table_dependencies": "`student`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": / / / using key ref_optimizer_key_uses / * * /}, {" rows_estimation ": [/ line/judge {" table" : "` student `", "range_analysis" : Table_scan: {"rows": 3973767, "cost": 408558} [// Potential scope index {"index": "PRIMARY", "usable": true, "key_parts": usable [ "id" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": /* setup_range_conditions */, "group_index_range": {"chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "Analyzing_range_alternatives ": {// Analyze range options "range_scan_alternatives": [{"index": "PRIMARY", "ranges": [ "id < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": False, "rows": 9, "cost": 1.91986, "chosen": true}] /* range_scan_alternatives */, "analyzing_roworder_INTERSECT ": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "Chosen_range_access_summary ": {"range_access_plan": {"type": "range_scan", "index": "PRIMARY", "rows": 9, "ranges": [ "id < 10" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 9, "cost_for_plan": 1.91986, the "feature" : true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, {"considered_execution_plans": [] /* considered_prefix */, "table": "' student '", "best_access_path": {"rows_to_scan": 1, "access_type": "range", "range_details": { "used_index": "PRIMARY" } /* range_details */, "resulting_rows": 9, "cost": * considered_adsense */, "condition_filtering_pct": 100, // row filter percentage "rows_FOR_plan ": 9, "cost_for_plan": 2.81986, "chosen": True}] /* considered_execution_plans */, {"attaching_conditions_to_tables": { "(`student`.`id` < 10)", "attached_conditions_computation": [] /* attached_conditions_computation */, "attached_conditions_summary": [// "`student`", "attached": "(`student`.`id` < 10)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`student`", "original_table_condition": "(`student`.`id` < 10)", "final_table_condition ": "(` student `. ` id ` < 10)"}] finalizing_table_conditions / * * /}, {" refine_plan ": [/ / lean plan {" table" : "` student `" refine_plan / * * /}}]] / * steps join_optimization / * * / * /}}, {" join_execution ": {/ / execution" select# ": 1, "steps": [] /* steps */} /* join_execution */}] /* steps */} // Part 3: The number of bytes truncated when the trace message is too long. MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 // Missing bytes beyond the maximum capacity // Part 4: Whether the user executing the trace statement has permission to view the object. The column information is 1 and the TRACE field is empty when you do not have permissions, which typically occurs when a view or stored procedure is called with SQL SECURITY DEFINER. INSUFFICIENT_PRIVILEGES: 0 // Missing permission 1 row in set (0.00 SEC)Copy the code

9. MySQL Monitoring analysis view -sys schema

9.1 Sys Schema View Summary

The host related: starts with host_summary, which summarizes information about 10 delays.
Innodb related: Starts with InnoDB and aggregates innoDB buffers and transactions waiting for InnoDB locks.
The I/O: as I. At the beginning, the waiting I/O and I/O usage are summarized.
Memory usage: Displays memory usage from the perspectives of hosts, threads, and events, starting with memory
Connection and session information: ProcessList and session-related views, summarizing session-related information.
Table related: A view starting with schemA_table that displays table statistics.
Index information: Collects statistics on index usage, including redundant indexes and unused indexes.
Statements related to: Starts with statement and contains information about statements that perform full table scan, use temporary tables, and sort.
The user related: View that starts with user, collects statistics on file I/ OS and executed statements used by users.
Waiting for information about the event: starts with wait to show the delay of waiting events.

9.2 Sys Schema View Usage Scenarios

9.2.1 Indexes

Select * from sys. schemA_REDUNdant_INDEXES; Select * from sys.schema_unused_indexes; Select INDEX_NAME, ROws_selected, ROws_INSERTED,rows_updated,rows_deleted from sys. schemA_index_statistics where table_schema='dbname' ;Copy the code

9.2.2 table related

Select table_schema,table_name,sum(io_read_requests+io_write_requests) as IO from sys.schema_table_statistics group by table_schema,table_name order by io desc; Allocated tables select object_schema,object_name, data from sys.innodb_buffer_stats_by_table order by allocated limit 10; Select * from sys. statements_with_full_table_SCANS where db='dbname';Copy the code

9.2.3 Statement related

Select db,exec_count,query from sys.statement_analysis order by exec_count desc; SQL select EXEC_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1; #3. Monitor SQL select DB,exec_count,tmp_tables,tmp_disk_tables,query from sys.statement_analysis where temporary tables are used tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;Copy the code

9.2.4 IO related

Select file,avg_read,avg_write,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_read limit 10;Copy the code

9.2.5 Innodb related

Select * from sys.innodb_lock_waits;Copy the code

Warning: The Mysql database consumes a large amount of resources to collect related information during query in the SYS reservoir. In serious cases, service requests may be blocked and a fault may occur. Do not query the SYS, Performance_scheme, and Information_schema frequently to perform monitoring and inspection.

Nodule 10.

Query is the most frequent operation in the database. Improving the query speed can effectively improve the performance of MySQL database. By analyzing the query statement, we can understand the execution of the query statement, find out the bottleneck of the query statement execution, so as to optimize the query statement.

Refer to the article

Chapter 15 and 16 “MySQL Technology Insider: InnoDB Storage Engine (version 2)” “Database index Design and Optimization”

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MySQL8 Indexing and Tuning Article 5- Performance Analysis Tools 2(Explain, Trace, monitor analysis views)

6. Analyze the query statement: EXPLAIN

6.1 an overview of the

6.1.1 What can I Do?

6.1.2 Official Website:

6.2 Basic Syntax

6.3 Data Preparation

6.4 EXPLAIN the function of each column

1. table

2. id

3. select_type

4. Partitions (omitted)

5. Type fostered fostered

6. Possible_keys and key

7. Key_len fostered fostered

8. ref

9. Rows oblivious

10. filtered

11. Extra oblivious

7. Further use of EXPLAIN

7.1 EXPLAIN four output formats

1. Traditional format

2. The JSON format

3. The TREE format

4. Visual output

7.2 Use of SHOW WARNINGS

8. Analysis optimizer execution plan: Trace

9. MySQL Monitoring analysis view -sys schema

9.1 Sys Schema View Summary

9.2 Sys Schema View Usage Scenarios

9.2.1 Indexes

9.2.2 table related

9.2.3 Statement related

9.2.4 IO related

9.2.5 Innodb related

Nodule 10.

Refer to the article

MySQL8 Indexing and Tuning Article 5- Performance Analysis Tools 2(Explain, Trace, monitor analysis views)

6. Analyze the query statement: EXPLAIN

6.1 an overview of the

6.1.1 What can I Do?

6.1.2 Official Website:

6.2 Basic Syntax

6.3 Data Preparation

6.4 EXPLAIN the function of each column

1. table

2. id

3. select_type

4. Partitions (omitted)

5. Type fostered fostered

6. Possible_keys and key

7. Key_len fostered fostered

8. ref

9. Rows oblivious

10. filtered

11. Extra oblivious

7. Further use of EXPLAIN

7.1 EXPLAIN four output formats

1. Traditional format

2. The JSON format

3. The TREE format

4. Visual output

7.2 Use of SHOW WARNINGS

8. Analysis optimizer execution plan: Trace

9. MySQL Monitoring analysis view -sys schema

9.1 Sys Schema View Summary

9.2 Sys Schema View Usage Scenarios

9.2.1 Indexes

9.2.2 table related

9.2.3 Statement related

9.2.4 IO related

9.2.5 Innodb related

Nodule 10.

Refer to the article

Related Posts

Do you really understand Java dynamic proxies?

May 12, 2021 Redis database redis interview questions

MySQL > alter table set id to autoincrement Isn’t it good to use the serial number as the primary key?