Mysql execution plan Explain, no longer need to be afraid of SQL optimization!

As you all know, mysql performs query optimization when executing a query. In simple terms, the execution time is to generate the execution plan based on the cost and rules optimization, and then execute the query according to the execution plan. This article mainly introduces EXPLAIN the meaning of each output item, so as to help you better SQL performance optimization!

The main content of this article is based on the nuggets pamphlet “from the root to understand MySQL” collation. If you want to understand in detail, it is recommended to buy the nuggets brochure to read.

We can see the execution plan of the query by prefacing the query with the EXPLAIN keyword. For example,

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.01sec)Copy the code

As you can see, the execution plan contains a number of output columns. Let’s briefly review the role of each column before going into more detail.

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

Pre-related knowledge points

To understand the meaning of each column of the execution plan in detail, we need to understand the following points.

Irrelevant subqueries

If a subquery can run on its own without relying on the outer query, it is called an irrelevant subquery.

Correlation subquery

If the execution of a subquery depends on the value of the outer query, the subquery is called a correlation subquery.

Materialize subqueries

Instead of taking the result set of an unrelated subquery directly as an argument to the outer query, write the result set to a temporary table (materialized table). Such as:

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
Copy the code

Suppose the name of the materialized subquery table is materialized_TABLE, and the materialized table stores the column M_VAL of the subquery result set. After the materialized subquery, table S1 can be joined with the materialized subquery table materialized_table, and the corresponding query result can be obtained.

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;
Copy the code

Convert the subquery to semi-join

Materialize the subquery and then execute the query will have the cost of creating a temporary table. Can we not materialize the subquery directly into a join? Let’s revisit the query statement above:

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
Copy the code

We can think of this query as: For a record in s1, if we can find one or more records in S2 that match s2.common_field=s1.key1, then the s1 record will be added to the final result set. This process is similar to joining s1 and S2:

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

The only problem with this is that for a single record in s1, if there are multiple records in S2 that satisfy s1.key1 = s2.common_field, the record will be added to the final result set multiple times. Therefore, the two cannot be considered completely equivalent, hence the semi-join. Semi-join s1 and S2 means: for a certain record in S1, we only care about whether there is a matching record in S2, not the specific number of matching records, and only the records in S1 are retained in the final result set. Of course, semi-join is an internal mechanism of mysql and cannot be directly used in SQL statements.

Semi-join implementation mechanism

Table pullout (Table pullout in subquery)

If there are only primary keys or unique index columns in the query list of the sub-query, you can directly pull up the table in the sub-query to the FROM clause of the outer query, and merge the search criteria in the sub-query into the search criteria of the outer query, for example:

SELECT * FROM s1  WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');
Copy the code

Since key2 is the only secondary index column of S2, we can directly pull S2 into the FROM clause of the outer query, and merge the search criteria of the sub-query into the search criteria of the outer query. In fact, we can directly optimize the sub-query into join query.

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

DuplicateWeedout Execution strategy

Such as the following query:

SELECT * FROM s1  WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
Copy the code

After conversion to a half-join query, a record in s1 may have multiple matching records in S2, so it may be added to the final result set multiple times. To eliminate duplication, create a temporary table. For example, create a temporary table that looks like this:

CREATE TABLE tmp (
    id PRIMARY KEY
);
Copy the code

When a join query is executed, each time a record from s1 is added to the result set, the value of that record’s ID is first added to the temporary table. This method of using temporary tables to eliminate duplicate values in the semi-join result set is called DuplicateWeedout.

LooseScan Execution Strategy

Such as the following query:

SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');
Copy the code

In the subquery, fors2Table access is availablekey1The index of the column that happens to be in the query list of the subquerykey1Column so that after converting the query to a half-join query, if thes2When executing a query as a driver table, it looks like this:As shown in the picture, ins2The tableidx_key1In index, the value is'aa'There are three secondary index records in s1, so you only need to search for the first value in s1s1.key3 = 'aa'The records, if you can ins1The corresponding record is found in the table, and the corresponding record is added to the result set.This method is called a loose scan, in which only the first value of the same record is matched.

FirstMatch execution strategy

FirstMatch is a primitive semi-join execution method. In simple terms, it takes one of the records in the outer query, searches the sub-query table for matching records, and if it finds a matching record, puts the outer query record into the final result set and stops searching for more matching records. If not found, the outer query record is discarded; Then start fetching the next record in the outer query and repeat the process.

Detailed execution plan

To explain the meaning of the execution plan columns in detail, we will create two sample tables S1 and S2 with identical table structures.

CREATE TABLE s1 (
    id INT NOT NULL 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),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
Copy the code

table

No matter how complex our query is and how many tables are included in the inner side, it will ultimately require a single table access for each table, so each record output by the EXPLAIN statement will correspond to a single table access method. The table column represents the name of the table. Such as:

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

The above query only involves a single table query, so EXPLAIN outputs only one record. The value of the table column is s1, which means that the record describes the access method to the s1 table.

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

As you can see, EXPLAIN outputs only two records. The values of the table columns are S1 and s2, which represent the access methods to s1 and S2, respectively.

id

As you know, a query usually contains one or more SELECT keywords. You can simply assume that for every SELECT keyword in a query, there will be a corresponding ID value in the execution plan. For example, the following query has only one SELECT keyword:

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, a select is often queried against multiple tables, so there will be multiple records in the execution plan, but they will all have the same ID. The table appearing in the front is the driven table, and the table appearing in the back is the driven table.

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

For subqueries, it is possible to have multiple SELECT keys, each with a unique ID value.

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

But there is one more thing to note: the query optimizer may rewrite query statements involving subqueries to convert them into join queries. The id value of the execution plan is the same.

For queries that contain the union keyword, in addition to an ID value for each SELECT keyword, there will be a record with an ID value of NULL. This record is mainly used to indicate that the result set of the two queries is de-duplicated (union all does not need to be de-duplicated, so this record is not available).

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

select_type

We already know that each select keyword represents a small query, and the select_Type attribute is used to describe the meaning of the current small query. The description of the select_Type attribute is as follows:

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)

SIMPLE

Queries that do not contain UNION or subqueries are of the SIMPLE type, such as common single-table queries and join queries.

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

UNION

For a large query that contains a UNION or UNION ALL, it is made up of several small queries, of which ALL but the leftmost small query have a select_type value of UNION.

UNION RESULT

MySQL select select_type from temporary table to delete UNION 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

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

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.

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

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

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

type

As mentioned above, one record of the execution plan represents the access method to a table, and the type column is used to describe the access method. The full access methods are as follows: system, const, eq_ref, ref, fulltext, ref_or_NULL, index_merge, unique_subquery, index_subquery, range, index, ALL.

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.

const

The access method to a single table is const when an equivalent match is made to a constant based on a primary key or unique secondary 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

In join queries, if the driven table is accessed by a primary key or unique secondary index column equivalence match (all index columns must be compared if the primary key or unique secondary index is a union index), then the method of access to the driven table is eq_ref.

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

ref

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

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

ref_or_null

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

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

In general, only one index can be used for a query on a table. However, in some scenarios, index merge may be used. In this case, the type is index_Merge.

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

The index combined

Typically, at most one index is used to execute a query. However, it is possible to use multiple secondary indexes in special cases, and queries executed this way are called index_Merge. There are three specific index merging algorithms.

Intersection Intersection. A query can use multiple secondary indexes, and the intersection of results from multiple secondary indexes is used.
```
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
Copy the code
```
When writing a query, we often want to retrieve records that meet both one search criterion and another search criterion. We say that there is an OR relationship between these different search criteria. Such as:
```
SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'
Copy the code
```
Intersection is used when search conditions that use different indexes are joined with AND. Union stands for Union and is used when search terms using different indexes are joined by OR.
A Union index merge can be used only when all secondary index columns are matched with each other. For example, a Union index merge cannot be used in the following query:
```
SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
Copy the code
```
We call sort-Union index merge as sorting by primary key of secondary index records, and then Union index merge as sorting by primary key of secondary index records. Obviously, sort-Union index merge takes one more step to Sort primary key of secondary index records than pure Union index merge.

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

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

index

When all index records need to be scanned, the access method of this table is index.

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

ALL

A full table scan

Possible_keys and key

The possible_keys column describes possible indexes in a single table query on a table, and the key column describes 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

key_len

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 amount of storage space it actually occupies is the fixed value. For index columns whose character set is of variable length type, such as an index column whose type isVARCHAR(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 columns, there will always be 2 bytes of space to store the actual length of the variable-length column.

ref

When the query is executed using the condition of index column equivalence matching, that is, when the access method is const, eq_ref, ref, ref_OR_NULL, unique_subquery, and index_subquery, the ref column displays the specific information of index column equivalence matching. 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

rows

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

filtered

We pay more attention to the filtered value of the execution plan record that corresponds to the driver table in the join query, because this directly affects the fan out value of the driver table. In the case of rows, the bigger the filtered, the smaller the fan out value, and the more efficient it may be. Such as:

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.

Extra

Extra is used to illustrate some of the information to help us understand the query more accurately. Let’s pick a few more common ones to introduce.

No tables used

No tables Used appears when there is No FROM in the query statement.

Impossible WHERE

Impossible WHERE occurs when the where clause in a query statement is always false.

No matching min/max row

No matching min/ Max row appears when the query list has a min() or Max () aggregation function, but No records are matched.

Using index

When Using index overrides, the Using Index appears.

Using index condition

If Index Condition Pushdown is used during query execution, a Using Index Condition is present. Such as:

SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
Copy the code

1. Select idx_key1 from idx_key1 based on key1 > ‘z’. Select * from key1 where key1 LIKE ‘%a’; select * from key1 where key1 LIKE ‘%a’;

Using where

When a query is executed Using a full table scan, Using WHERE appears if the query statement contains a WHERE condition. When performing queries Using index access, Using WHERE also appears if the WHERE clause contains non-indexed column fields.

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.

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

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

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

If an index merge is used to execute the query, a Using INTERSECT (…) will appear. Or Using the union (…). Or Using sort_union (…). . Such as:

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

Zero limit

Zero limit occurs when the limit clause argument is 0.

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

However, more often than not, sorting operations do not use indexes, but only filesort. If the sort uses filesort, then Using filesort appears in the Extra column.

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

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

Start temporary, End temporary

The query optimizer will first try to convert the IN subquery to semi-join, and semi-join has several execution strategies. When DuplicateWeedout is the execution strategy, that is, to create a temporary table to duplicate the records IN the outer query, 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

Original is not easy, feel that the article is written well small partners, a praise 👍 to encourage it ~

Welcome to my open source project: a lightweight HTTP invocation framework for SpringBoot