SQL Execution Plan interpretation

Statement

• 5.6 DESC does not see show warnings, also cannot see filtered column
• 5.7 of DESC equals 5.6 of the DESC extended, so you can see show warnings,5.6 filtered column is very not allowed, 5.7 better

Look at an execution plan first.

([email protected]) [test]> desc select * from l;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

Ⅰ, expand parse each Field ID column--Indicates the order of SQL execution

• ID equal, usually simple association, from the top down to see
• ID is not equal, generally for the occurrence of sub-query, first look at the big look at the small
• At the same time exist equal and unequal, generally equal to a group, first look at the big and then look at the small

There is a unspoken rule called:ID equal from the top down, id ranged from bottom to top look

Select_type--select type, for area molecule query, correlation query, etc.

• Simple: Easy query with no subquery and union
• PRIMARY: A subquery is included in the query, and the outermost query is marked as PRIMARY
• Union: When you use Union to connect Select, the second select starts with a union
• Subquery:select, or the subquery behind the where (not from the from), can be subquery
• DERIVED: The subquery contained in the from IS marked as DERIVED (derived table)
• Dependentsubquery: subquery that relies on external queries
• Result of UNION result:union, corresponding ID is null

table--table for Output records

• Alias is used in the query, the alias is shown here
• Does not involve table operations, it is displayed as null
• < Derivedn >/< Subqueryn > results from a query with ID n
• < Unionm,n > Result set generated by query union with ID m,n

type--Access types

• A special case of system:const with only one row of records
• Const: Use a unique index or primary key to fetch only one row of data
• EQ_REF: When multiple table joins, the driver table returns only one row of data, and this row of data is the primary key or unique index of the drive table, and must not be null
• Ref: Scan for non-unique indexes, typically a non-unique index of an equivalent query
• Range: Retrieves the rows for a given range, using an index to select rows, usually in the where condition between, <, >, in, like, and so on
• Index:full index Scan, read full table according to index
• All:full table Scan, sweep the entire data file
• Fulltext: Full-Text indexing
• Ref_or_null: Querying with a normal index, but querying for null values
• Index_merge or: The type to which the query will be used, maybe one SQL uses two indexes, and then merge
  Unique_subquery and Index_subquery: Very rarely the previous is a subquery column is a unique index, the second is a subquery column is a normal index

The main optimization object is index and all, and there are two scenarios to consider preserving the index
Query index columns only, do not return tables or use indexes to sort or aggregate

Possible_keys

Indexes that the optimizer may use

Key

Index of the optimizer's actual selection

Key_len

Length of bytes using the index

Ref

• The equivalent query displays the const
• Connect Query The driver table shows the join column of the driver table here

Rows

Number of records estimated by the optimizer

Filtered

Percentage of records that are filtered by criteria

Extra

• Using index: The optimizer simply uses the index to get the result index overlay
• Using Index Condition: Optimizer trial index condition pushdown optimization, Level two index
• Using index for GROUP by: The optimizer can handle a group by or distinct statement simply by using the index
  The above 3 basically ignore it, there is no parameter good tune
• Using temporary: Use temporary tables, common in order By,group by
• Using Filesort: Use additional sort adjustment sort_buffer_size
• Using join buffer: The optimizer needs to adjust join_buffer_size using the join buffer
• Using MRR: Optimizer uses MRR tuning read_cache_size
• Using temporary: The optimizer needs to use temporal table adjustment tmp_table_size
• Using Where: The optimizer uses the where filter

Ⅱ, analyze two execution plans see Case 1

([email protected])    [dbt3]> DESC------------P_partkey in (select             , L_partkey, LineItem, WHERE, L_shipdate between ' 1997-01-01 ' and ' 1997-02-01 '), ORDER by P_retailprice DESC, LIMIT 10;+----+--------- -----+-------------+------------+--------+----------------------------------------------+--------------+------- --+---------------------+--------+----------+----------------------------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+--------------+-------------+------------+--------+------------------------------- ---------------+--------------+---------+---------------------+--------+----------+----------------------------  ------+| 1 | Simple | Part | NULL | All | PRIMARY | NULL | NULL | NULL |   197706 | 100.00 | Using where;  Using Filesort | | 1 | Simple | <subquery2> | NULL | Eq_ref | <auto_key> | <auto_key> | 5 |      Dbt3.part.p_partkey |   1 | 100.00 |  NULL | | 2 | materialized | LineItem | NULL | Range | I_l_shipdate,i_l_suppkey_partkey,i_l_partkey | I_l_shipdate | 4 | NULL |   138672 | 100.00 | Using index condition; Using MRR |+----+--------------+-------------+------------+--------+-------------------------------------------- --+--------------+---------+---------------------+--------+----------+----------------------------------+3 rows In set, 1 warning (0.01 sec)

ID Order
The 1②part table (appearance) is associated with the Subquery2 (the table of the 14W records produced by the id=2), which is associated with all records in the part table, is 19w rows, is associated with L_partkey, and the final sort is used Filesort
The 1③ table is indexed, so the MySQL optimizer automatically adds a unique index to the data from the first step, in which it is a deduplication (which is actually a materialized), so it is a unique index, EQ_REF is associated with a unique index, and P_partkey in the outside
2① first check LineItem table, is a range range query, using the I_l_shipdate index, l_shipdate is the date type, occupies four bytes, estimated 140,000 rows of records, filtered out 100%, Materiallized indicates that an actual table has been generated, and that the index has been added, L_partkey, and the unique index (in inside is a go-heavy)

Pay attention to a detail

([email protected])    [dbt3]> DESC------------P_partkey in (select             , L_partkey, LineItem, WHERE, L_shipdate between ' 1997-01-01 ' and ' 1997-01-07 '), ORDER by P_retailprice DESC, LIMIT 10;+----+--------- -----+-------------+------------+--------+----------------------------------------------+--------------+------- --+-----------------------+-------+----------+----------------------------------------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+--------------+-------------+------------+--------+------------------- ---------------------------+--------------+---------+-----------------------+-------+----------+----------------------------------------------+| 1 | Simple | <subquery2> | NULL | All | NULL | NULL | NULL |  NULL |   NULL | 100.00 | Using where; Using temporary;  Using Filesort | | 1 | Simple | Part | NULL | Eq_ref | PRIMARY | PRIMARY | 4 |     <subquery2>.l_partkey |   1 | 100.00 |  NULL | | 2 | materialized | LineItem | NULL | Range | I_l_shipdate,i_l_suppkey_partkey,i_l_partkey | I_l_shipdate | 4 | NULL |   29148 | 100.00 | Using index condition; Using MRR |+----+--------------+-------------+------------+--------+------------------------------------------ ----+--------------+---------+-----------------------+-------+----------+-------------------------------------- --------+3 rows in set, 1 Warning (0.00 sec)

The driver table becomes the Subquerry2, when the optimizer also looks at the query as a look, which means the optimizer is smart

In subqueries, the optimizer will help you rewrite the join and help you choose whether the subquery is an inner table or a semblance

([email protected])         [dbt3]> DESC Select, A.*, Part A, (select DISTINCT,  L_partkey, from, LineItem, where l_shipdate between ' 1997-01-01 ' and ' 1997-02-01 ') A.p_partkey=b.l_partkey, ORDER by a.p_retailprice Desc-C, where, B, 10;+----+---- ---------+------------+------------+--------+----------------------------------------------+--------------+---- -----+-------------+--------+----------+---------------------------------------------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+------------+------------+--------+---------------- ------------------------------+--------------+---------+-------------+--------+----------+--------------------- ------------------------------+| 1 | PRIMARY | <derived2> | NULL | All | NULL | NULL | NULL | NULL |   138672 | 100.00 | Using where; Using temporary;  Using Filesort | | 1 | PRIMARY | A | NULL | Eq_ref | PRIMARY | PRIMARY | 4 |      B.l_partkey |   1 | 100.00 |  NULL | | 2 | DERIVED | LineItem | NULL | Range | I_l_shipdate,i_l_suppkey_partkey,i_l_partkey | I_l_shipdate | 4 | NULL |   138672 | 100.00 | Using index condition; Using MRR; Using temporary |+----+-------------+------------+------------+--------+---------------------------------------- ------+--------------+---------+-------------+--------+----------+--------------------------------------------- ------+3 rows in Set, 1 Warning (0.00 sec)

So rewrite, b table is always appearance, sub-query just produces a derived table, but there is no way to index it, if the sub-query out of the result set is very large, when the performance is not as in, the word optimizer will use it as the inner table

Case 2

([email protected]) [dbt3]> DESC Select Max (L_extendedprice), from Orders,lineitem, where o_orderdate between ' 1995-01- 1995-01-31 ' and ' l_orderkey=o_orderkey;+----+-------------+----------+------------+-------+---------- ----------------------------------+---------------+---------+------------------------+-------+----------+------ --------------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+----------+------------+-------+--------------------------------------------  +---------------+---------+------------------------+-------+----------+--------------------------+| 1 | Simple | Orders | NULL | Range | Primary,i_o_orderdate | I_o_orderdate | 4 | NULL |   40696 | 100.00 | Using where;  Using Index | | 1 | Simple |LineItem | NULL | Ref | primary,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 |     Dbt3.orders.o_orderkey |   3 | 100.00 | NULL |+----+-------------+----------+------------+-------+-------------------------------------------- +---------------+---------+------------------------+-------+----------+--------------------------+2 rows in set, 1 Warning (0.00 sec)

Orderkey on the index, but useless, use is pk,orders to show the appearance, according to filter conditions to filter out the data to do the appearance, and then with the LineItem table associated with PK, the associated column is Orders.o_orderkey

If you forcibly walk the Orderkey index, the cost is very high, need to return to the table, through the primary key not back to the table

Case 3

([email protected])    [dbt3]> DESC SELECT *, LineItem, where, l_shipdate <= ' 1995-12-32 ' , A, a, select-------LineItem, L_shipdate > = ' 1997-01-01 ', +----+--------------+------------+------------+------+---------------+------+---------+------+--- ------+----------+-----------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+--------------+------------+------------+------+---------------+------+---------+------+---------+-  ---------+-----------------+| 1 | PRIMARY | LineItem | NULL | All | I_l_shipdate | NULL | NULL | NULL |    5409799 | 33.33 |  Using where | | 2 | UNION | LineItem | NULL | All | I_l_shipdate | NULL | NULL | NULL |    5409799 | 50.00 | Using where | | null| UNION RESULT | <union1,2> |      Null | All | NULL | NULL | NULL |    NULL |     NULL | NULL | Using temporary |+----+--------------+------------+------------+------+---------------+------+---------+------+- --------+----------+-----------------+3 rows in set, 3 warnings (0.10 sec)

Union result merges two tables using the temporary, with the temporary table, the union will go heavy, so to build a temporary table, with a unique index on it, there are two indexes, so that a SQL can only use one index is not right

Case 4

([email protected])    [employees]> DESC Select, Emp_no, Dept_no, (SELECT, COUNT (1) -I-dept_emp T2, WHERE, T1.emp_no <= t2.emp_ NO) as row_num from dept_emp t1;+----+--------------------+-------+------------+-------+------------ ----+--------+---------+------+--------+----------+------------------------------------------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+--------------------+-------+------------+-------+----------------+-  -------+---------+------+--------+----------+------------------------------------------------+| 1 | PRIMARY | T1 | NULL | Index | NULL | Emp_no | 4 | NULL |   331570 | 100.00 |  Using Index | | 2| DEPENDENT subquery | T2 | NULL | All | Primary,emp_no | NULL | NULL | NULL |    331570 | 33.33 | Range checked for each record (index map:0x3) |+----+--------------------+-------+------------+-------+-------------- --+--------+---------+------+--------+----------+------------------------------------------------+2 rows in set, 2 Warnings (0.00 sec)

For this SQL, first executed 1 and then executed 2,2 is dependent subquery, to rely on sub-query, so first executed 1, so T1 is the appearance, T2 is the inner table, each has to be associated with 33w * 33% times, a total association 33w times, altogether 33w * 10w times

Line number problem, performance is very poor

SQL Execution Plan interpretation