1. SQL execution process

1.1 Internal execution of SQL statements

MySQL is divided into Server layer and Storage Engine layer. Server layer includes connectors, analyzers, optimizers, executors and so on, while storage engine layer is responsible for data storage and reading.
When SQL is executed, connections are established and privileges are obtained through connectors; connectors maintain and manage connections.
MySQL parses the SQL statement by analyzer, analyses the meaning of each part of the statement, and then judges whether the SQL conforms to the grammar of MySQL according to the grammar rules.
After analyzer analysis, MySQL will optimize the processing of SQL requests. The optimizer judges the situation of statement index, connection order and so on, and decides which execution scheme is most suitable.
Finally, it comes to the stage of executor. According to the engine definition of the table, the executor calls the engine interface to execute the SQL statement.

1.2 Order of execution of SQL statements

1. FROM# calculates Cartesian product for the left and right tables of FROM, and generates virtual table VT1.
2. ON# filters virtual table VT1 by ON, and only those qualified rows are recorded in virtual table VT2.
3. JOIN # If an external join (such as left join, right join) is specified, the unmatched rows in the retained table are added to the virtual table VT2 as external rows, resulting in the virtual table VT3. If more than two tables are included in the from clause, steps 1 to 3 are repeated for the result VT 3 from the previous join join and for the next table until all tables are processed.
4. WHERE# Conditional WHERE filtering for virtual table VT3. Only qualified records will be inserted into virtual table VT4.
5. GROUP BY# Grouped the records in VT4 according to the columns in the group by clause, and generated the virtual table VT5.
6. AVG, SUM ___________________
7. having_applies hav
8. SELECT # performs the select operation, selects the specified column, and inserts it into the virtual table VT8.
9. DISTINCT # de-duplicates the records in VT8. Generate virtual table VT9.
10. ORDER BY # sorts the records in virtual table VT9 according to the specified column, and generates virtual table VT10.
11. LIMIT # takes out the record of the specified row, generates the virtual table VT11, and returns the result.

Interpretation of Implementation Plan

2.1 How to View SQL Statement Execution Plan

MySQL has built-in EXPLAIN commands to view the execution plan of SQL statements. EXPLAIN supports SELECT, DELETE, INSERT, REPLACE and UPDATE statements, as well as partition table parsing. In MySQL 8.0.17, EXPLAIN does not support EXPLAIN PARTITIONS and EXPLAIN EXTENDED grammar, and FORMAT adds TREE format. Through the information displayed by EXPLAIN, we can know the order of table query and the way of table join, and judge the efficiency of statement execution according to these information, and decide whether to add index or rewrite SQL statement to optimize table join to improve execution efficiency.
EXPLAIN grammar is as follows:

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}
DESCRIBE and EXPLAIN Sentences are synonyms. but DESCRIBE Keyword is more often used to obtain information structure about tables, while EXPLAIN Used to retrieve query execution plans.

_This article will explain the output of execution plan in three formats of EXPLAIN:

2.2 Output description in TRADITIONAL format

_RADITIONAL format is also the default format, the output format is as follows:

mysql> explain format='TRADITIONAL' select t1.id,t1.address,t2.from_date,t3.dept_name from t1 left join t2 on t1.id=t2.id left join t3 on t2.dept_id=t3.dept_id where t3.dept_name in (select dept_name from t3 whhere dept_id<1000)
+----+-------------+-------+------------+--------+-----------------------+---------------+---------+-----------------+-------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys         | key           | key_len | ref             | rows  | filtered | Extra                    |
+----+-------------+-------+------------+--------+-----------------------+---------------+---------+-----------------+-------+----------+--------------------------+
|  1 | PRIMARY     | t1    | NULL       | index  | NULL                  | idx_address   | 202     | NULL            | 99975 |   100.00 | Using index              |
|  1 | PRIMARY     | t2    | NULL       | eq_ref | PRIMARY               | PRIMARY       | 4       | test.t1.id      |     1 |   100.00 | NULL                     |
|  1 | PRIMARY     | t3    | NULL       | eq_ref | PRIMARY               | PRIMARY       | 16      | test.t2.dept_id |     1 |   100.00 | Using where              |
|  2 | SUBQUERY    | t3    | NULL       | index  | PRIMARY,idx_dept_name | idx_dept_name | 202     | NULL            | 81406 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+--------+-----------------------+---------------+---------+-----------------+-------+----------+--------------------------+
4 rows in set, 2 warnings (0.01 sec)

Each field has the following meanings:

2.2.1 id

The smaller the id value is, the more external the query is, and the larger the internal the query is. The id values are executed in the order of large to small, and if the id values are the same, they are executed from top to bottom.

2.2.2 select_type

2.2.3 table

_Currently, which table is used to obtain data, if an alias is specified for the table, the alias is displayed, and if there is no data reading for the table, the NULL is displayed. There are also several cases as follows:

<unionM, N>: The result after quoting id as M and N UNION.
<derivedN>: Tables derived from results that refer to id as N. Derived tables can be a result set, such as results derived from FROM neutron queries.
Subquery N >: Table materialized from sub-query results with id N. Generate a temporary table to save the results of the sub-query.

2.2.4 partitions

_This column shows the partition hit by the partition table. The field in the non-partitioned table is null.

2.2.5 type

_According to the best to worst connection types, the order is: system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL.
_Except ALL, other types can be used in index, except index_merge, other types can only be used in one index.

• system: There is only one row of data or an empty table in the table, which is a special case of const type. It can only be used for myisam and memory tables. In the case of Innodb engine tables, type columns are usually all or index in this case.
• Const: At most one row of records matches. When all fields of a union primary key or unique index are compared with constant values, the join type is const. Other databases are also called unique index scans.
• eq_ref: When multiple tables join, only one row can be found in the current table for each row from the previous table. This is the best type except system and const. This type is used when all fields of a primary key or unique non-NULL index are used as join joins. eq_ref can be used to compare index columns using the'='operator. The value of the comparison can be a constant or an expression that uses the columns of the table read before the table.
• Ref: For each row from the previous table, multiple rows can be matched in the index of this table. If the join only uses the leftmost prefix of the index or if the index is not a primary key or a unique index, use the ref type (that is, the join can match multiple rows of records). Ref can be used to compare index columns using the'='or'<=>' operators.

The difference between eq_ref and ref is that it uses the only index, that is, the primary key or the unique index, while ref uses a non-unique index or a common index. Eq_ref can only find one line, while ref can find multiple lines.

• fulltext: This type is used when using full-text indexing. It should be noted that full-text index has a high priority. If full-text index and ordinary index exist at the same time, mysql prefers to use full-text index regardless of cost.
• ref_or_null: Similar to ref type, it only adds a comparison of null values. Not much practical use.
• index_merge: Indicates that the query uses more than two indexes, and finally takes intersection or union. Common and or conditions use different indexes. The official ranking is after ref_or_null, but in fact the performance may be worse than range for most of the time due to multiple indexes being read.
• unique_subquery: For in-form sub-queries in where, sub-queries return unique values of non-repetitive values, which can completely replace sub-queries, which is more efficient.
• index_subquery: This join type is similar to unique_subquery. For non-unique indexes, duplicate values can be returned.
• Range: Index range queries are common in queries using operators such as =,<>,>,>=,<,<=, IS NULL,<=>, BETWEEN, IN() or like.
• Index: Scan the entire table of the index and scan the index from beginning to end. There are two cases:
  One is that the query uses overlay index, so it only needs to scan the index to get data. This efficiency is faster than full table scanning, because the index is usually smaller than the data table, and can avoid second query. Use index is displayed in extra. Conversely, if a full table scan is performed on the index, there is no use index prompt.
• ALL: Full table scan, the worst performance.

2.2.6 possible_keys

_shows the index MySQL may use when looking up the data in the current table, which is of little practical significance.

2.2.7 key

_shows the index MySQL decides to use when actually looking up data, and if the field value is NULL, it indicates that no index is used.

2.2.8 key_len

_shows the size of the index MySQL actually uses, in unit bytes. The size of key_len can be used to evaluate which parts of the composite index are used.
The index length of several common field types is as follows, assuming that the character code is utf8mb4: if the field is allowed to be NULL, an additional byte is needed;
Character:
char(n): 4n bytes
varchar(n): 4n+2 bytes
NUMERICAL TYPE:
tinyint: 1 byte
int: 4 bytes
bigint: 8 bytes
Time type:
date: 3 bytes
datetime: 5 bytes + seconds precision bytes
timestamp: 4 bytes + seconds precision bytes
Second precision bytes (up to 6 bits):
1-2 bits: 1 byte
3-4 bits: 2 bytes
5-6 bits: 3 bytes

2.2.9 ref

_If a constant equivalent query is used, const will be displayed here, if a join query is used, the execution plan of the driven table will show the associated fields of the driven table, if the condition uses an expression or function, or if the condition column undergoes an internal implicit conversion, it may be shown as func.

2.2.10 rows

_This is the number of lines that mysql estimates need to scan (not the exact value). This value is very intuitive to show the efficiency of SQL, in principle, the less rows the better.

2.2.11 filtered

_This field indicates the percentage of records remaining to satisfy the query after the data returned by the storage engine is filtered in the server layer. Note that the percentage is not the specific number of records.

2.2.12 Extra

A lot of extra information in EXPLAIN will be displayed in Extra field. There are several common contents:

• Using index
  _Only querying the index tree can get the required data rows without reading the actual data rows in the table. Usually applied to select fields is part of the index used by queries, even with overlay indexes.

mysql> explain select dept_id from t2;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | index | NULL          | idx_dept_id | 16      | NULL | 100035 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• Using index condition
  _Display uses Index Condition Pushdown(ICP) feature to get data from tables by index. For ICP features, you can refer to the official document: Index Condition Pushdown Optimization. Simply stated as follows:

If the ICP feature is turned on, part of where condition can be pushed down to the storage engine to filter by index. ICP can reduce the number of times that the storage engine accesses the base table.
If the ICP feature is not turned on, the storage engine directly accesses the base table to obtain data according to the index needs and returns it to the server layer for where condition filtering.

#set persist optimizer_switch='index_condition_pushdown=off';
mysql> explain select * from t2  where to_date='1980-01-01' and from_date<'1970-01-01';                                                                                                                          
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                  |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | idx_from_to   | idx_from_to | 5       | NULL |    1 |    10.00 | Using where; Using MRR |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+------------------------+
1 row in set, 1 warning (0.00 sec)
#set persist optimizer_switch='index_condition_pushdown=on';
mysql> explain select * from t2  where to_date='1980-01-01' and from_date<'1970-01-01';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | idx_from_to   | idx_from_to | 5       | NULL |    1 |    10.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

• Using where
  Show that MySQL needs to return to the table to get the required data after it has been positioned by the index condition.

mysql> explain select * from t2  where to_date='1980-01-01';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100035 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.40 sec)

• Impossible WHERE
  The conditions of where clause can never be true.

mysql> explain select * from t2  where 1=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 | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)
  In the table join process, part of the data of the previous table is read into the join buffer, and then read from the buffer to connect with the current table. There are two main algorithms: Block Nested Loop and Batched Key Access.

#Switch BKA parameters
SET PERSIST optimizer_switch='mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=on/off';
mysql> explain select * from t1 join t2 on t1.birth_date=t2.to_date;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100035 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  99975 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select * from t2 join t3 on t3.dept_id=t2.dept_id;
+----+-------------+-------+------------+-------+---------------+---------------+---------+-----------------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref             | rows  | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------------+---------+-----------------+-------+----------+----------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | index | PRIMARY       | idx_dept_name | 202     | NULL            | 81406 |   100.00 | Using index                            |
|  1 | SIMPLE      | t2    | NULL       | ref   | idx_dept_id   | idx_dept_id   | 16      | test.t3.dept_id |     1 |   100.00 | Using join buffer (Batched Key Access) |
+----+-------------+-------+------------+-------+---------------+---------------+---------+-----------------+-------+----------+----------------------------------------+
2 rows in set, 1 warning (0.10 sec)

• Using MRR
  Multi-Range Read optimization strategy is adopted to read data.

mysql> explain select * from t2  where to_date='1980-01-01' and from_date<'1970-01-01';                                                                                                                          
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                  |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | idx_from_to   | idx_from_to | 5       | NULL |    1 |    10.00 | Using where; Using MRR |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+------------------------+
1 row in set, 1 warning (0.00 sec)

• Using temporary
  MySQL needs to create temporary tables to store query result sets. Usually occurs in statements with GROUP BY or ORDER BY clauses.

mysql> explain select * from t1 join t2 on t1.birth_date=t2.from_date  order by t2.id;
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref                | rows  | filtered | Extra                                  |
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------------+-------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL               | 99975 |   100.00 | Using temporary; Using filesort        |
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_from_to   | idx_from_to | 5       | test.t1.birth_date |     1 |   100.00 | Using join buffer (Batched Key Access) |
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------------+-------+----------+----------------------------------------+
2 rows in set, 1 warning (0.00 sec)

• Using filesort
  MySQL requires an additional sorting operation on the acquired data, which cannot be done by sorting the index. Usually occurs in statements with ORDER BY clauses.

mysql> explain select * from t1 join t2 on t1.birth_date=t2.from_date  order by t2.id;
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref                | rows  | filtered | Extra                                  |
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------------+-------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL               | 99975 |   100.00 | Using temporary; Using filesort        |
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_from_to   | idx_from_to | 5       | test.t1.birth_date |     1 |   100.00 | Using join buffer (Batched Key Access) |
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------------+-------+----------+----------------------------------------+
2 rows in set, 1 warning (0.00 sec)

2.3 JSON format output description

mysql> explain format='JSON' select t1.id,t1.address,t2.from_date,t3.dept_name from t1 left join t2 on t1.id=t2.id left join t3 on t2.dept_id=t3.dept_id where t3.dept_name in (select dept_name from t3 where deppt_id<1000) or  t2.id<10000;
+------------------------------------------------------------------------------------------------+
| EXPLAIN                                               |
| {
  "query_block": { #The whole query block
    "select_id": 1, #id equivalent to default format
    "cost_info": { #Specific cost information
      "query_cost": "80068.25" #The cost at select_id=1 is 80068.25
    },
    "nested_loop": [ #Recording Type Information for SQL Execution
      {
        "table": {#Contents of specific tables
          "table_name": "t1", #table equivalent to default format
          "access_type": "index",#type equivalent to default format
          "key": "idx_address",#key equivalent to default format
          "used_key_parts": [ #Use specific parts of the index
            "address"
          ],
          "key_length": "202",#key_len Equivalent to the Default Format
          "rows_examined_per_scan": 99975, #Number of rows per scan
          "rows_produced_per_join": 99975, #Number of rows per connection
          "filtered": "100.00",#filtered equivalent to the default format
          "using_index": true, #Whether to use index or not
          "cost_info": {#Specific cost of this part
            "read_cost": "88.25",#Cost of reading
            "eval_cost": "9997.50",#Cost of assessment
            "prefix_cost": "10085.75",#Cost of joining the next table in JOIN
            "data_read_per_join": "32M"#The amount of data to be read by JOIN operations
          },
          "used_columns": [#Used fields
            "id",
            "address"
          ]
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "ref": [ #ref equivalent to default format
            "test.t1.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 99975,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "24993.75",
            "eval_cost": "9997.50",
            "prefix_cost": "45077.00",
            "data_read_per_join": "3M"
          },
          "used_columns": [
            "id",
            "dept_id",
            "from_date"
          ]
        }
      },
      {
        "table": {
          "table_name": "t3",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "dept_id"
          ],
          "key_length": "16",
          "ref": [
            "test.t2.dept_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 99975,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "24993.75",
            "eval_cost": "9997.50",
            "prefix_cost": "80068.25",
            "data_read_per_join": "21M"
          },
          "used_columns": [
            "dept_id",
            "dept_name"
          ],
          "attached_condition": "<if>(found_match(t3), (<in_optimizer>(`test`.`t3`.`dept_name`,`test`.`t3`.`dept_name` in ( <materialize> (/* select#2 */ select `test`.`t3`.`dept_name` from `test`.`t3` where (`test`.`t3`.`dept_id` < 1000) ), <primary_index_lookup>(`test`.`t3`.`dept_name` in <temporary table> on <auto_key> where ((`test`.`t3`.`dept_name` = `materialized-subquery`.`dept_name`))))) or (`test`.`t2`.`id` < 10000)), true)",#Show some additional conditions
          "attached_subqueries": [#Additional subqueries
            {
              "table": {
                "table_name": "<materialized_subquery>",#Physical Chemicals Query
                "access_type": "eq_ref",
                "key": "<auto_key>",
                "key_length": "202",
                "rows_examined_per_scan": 1,
                "materialized_from_subquery": {
                  "using_temporary_table": true,#Temporary tables were used
                  "dependent": true,
                  "cacheable": false,#The result cannot be cached
                  "query_block": {
                    "select_id": 2,
                    "cost_info": {
                      "query_cost": "8228.85"
                    },
                    "table": {
                      "table_name": "t3",
                      "access_type": "index",
                      "possible_keys": [
                        "PRIMARY",
                        "idx_dept_name"
                      ],
                      "key": "idx_dept_name",
                      "used_key_parts": [
                        "dept_name"
                      ],
                      "key_length": "202",
                      "rows_examined_per_scan": 81406,
                      "rows_produced_per_join": 27132,
                      "filtered": "33.33",
                      "using_index": true,
                      "cost_info": {
                        "read_cost": "5515.59",
                        "eval_cost": "2713.26",
                        "prefix_cost": "8228.85",
                        "data_read_per_join": "5M"
                      },
                      "used_columns": [
                        "dept_id",
                        "dept_name"
                      ],
                      "attached_condition": "(`test`.`t3`.`dept_id` < 1000)"
                    }
                  }
                }
              }
            }
          ]
        }
      }
    ]
  }
} |
+------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.01 sec)

2.4 TREE format output description

mysql> explain format='TREE' select t1.id,t1.address,t2.from_date,t3.dept_name from t1 left join t2 on t1.id=t2.id left join t3 on t2.dept_id=t3.dept_id where t3.dept_name in (select dept_name from t3 where deppt_id<1000) or  t2.id<10000;
+------------------------------------------------------------------------------------------------+
| EXPLAIN                |
+------------------------------------------------------------------------------------------------+
| -> Filter: (<in_optimizer>(t3.dept_name,t3.dept_name in (select #2)) or (t2.id < 10000))
    -> Nested loop left join
        -> Nested loop left join #Connection mode
            -> Index scan on t1 using idx_address #Index used
            -> Single-row index lookup on t2 using PRIMARY (id=t1.id) #Specific use of index-related information
        -> Single-row index lookup on t3 using PRIMARY (dept_id=t2.dept_id)
    -> Select #2 (subquery in condition; run only once)#Subquery; run only once
        -> Filter: (t3.dept_id < 1000)#Conditional information
            -> Index scan on t3 using idx_dept_name
 |
+------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.11 sec)

Summary

_This paper describes the internal execution process, execution sequence and execution plan of MySQL 8.0.17. Other new features of MySQL 8.0.17 will be displayed in future blogs.