Detailed Analysis of Explain Execution Plan in MySQL

Preface

How to write efficient SQL statements is inseparable from the analysis of Explain execution plan. As for what is the execution plan and how to write efficient SQL, this article will introduce them one by one.

Execution plan

The execution plan is a query solution made by the database based on the SQL statement and the statistical information of related tables. This solution is automatically analyzed and generated by the query optimizer.

Using the explain keyword can simulate the optimizer to execute SQL query statements, so that you can understand how MySQL processes your SQL statements, analyze the performance bottlenecks of your select statements or table structures, let us know the reasons for the low efficiency of select, and thus improve our queries.

The result of explain is as follows:

The following is a detailed introduction to each column. The important ones are id, type, key, rows, and extra.

id

• The number in the id column is the sequence number of the select, which can also be understood as the identifier of the SQL execution order. There are as many ids as there are selects.
  • Different id values: If it is a query only, the id number will increase, the larger the id value, the higher the priority, and the earlier it will be executed;
  • The id values ​​are the same: execute from top to bottom;
  • The id column is null: This means that this is a result set and there is no need to use it for querying.

select_type

The query type is mainly used to distinguish common queries, joint queries, subqueries and other complex queries;

simple: indicates that the query does not include union operations or subqueries. The select_type of the outermost query is simple, and there is only one;

 explain select * from t3 where id=3952602;

primary: requires a union operation or a select with subqueries. The select_type of the outermost query is primary, and there is only one;

explain select * from (select * from t3 where id=3952602) a ;

Derived: Subqueries that appear in the from list are also called derived tables; MySQL recursively executes these subqueries and places the results in a temporary table.

 explain select * from (select * from t3 where id=3952602) a ;

Subquery: Except for the subquery contained in the from clause, any subquery that appears elsewhere may be a subquery.

explain select * from t3 where id = (select id from t3 whereid=3952602 ) ;

union: If the second select appears after the union, it is marked as union; if the union is contained in a subquery in the from clause, the outer select will be marked as derived.

explain select * from t3 where id=3952602 union all select * from t3;

union result: The select that gets the result from the union table. Since it does not need to participate in the query, the id field is null.

 explain select * from t3 where id=3952602 union all select * from t3;

Dependent union: Like union, it appears in a union or union all statement, but this query is affected by the outer query;

Dependent subquery: Similar to dependent union, the first SELECT in the subquery is affected by the outer table query.

table

Indicates which table a row of explain is accessing.

• If the query uses an alias, the alias is displayed here;
• If no operation on the data table is involved, this is displayed as null;
• If it is enclosed in angle brackets, it means that this is a temporary table, and the N behind it is the id in the execution plan, indicating that the result comes from this query;
• If it is <union M,N> enclosed in angle brackets, it is similar to , and is also a temporary table, indicating that this result comes from the result set of the union query with id M,N.

type

The access type is how MySQL decides to find rows in the table.

From best to worst: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL. Except all, other types can use indexes. Except index_merge, other types can only use one index. Generally speaking, you need to ensure that the query reaches at least the range level, preferably the ref level.

1. system: There is only one row of data in the table (equal to the system table). This is a special case of the const type and does not appear normally, so it can be ignored.
2. const: Use a unique index or primary key, which means that it can be found by using the index once. const is used to compare primary keys or unique indexes. Since only one row of data needs to be matched, it is very fast. If you put the primary key in the where list, mysql will be able to convert the query into a const.
3. eq_ref: Unique index scan, for each index key, there is only one row of data in the table that matches it. Common in primary key or unique index scans.
4. ref: A nonunique index scan that returns all rows that match a single value. Essence is also an index.
5. fulltext: full-text index retrieval. The full-text index has a high priority. If the full-text index and the ordinary index exist at the same time, MySQL will give priority to the full-text index regardless of the cost.
6. ref_or_null: Similar to the ref method, but with the addition of a comparison for null values.
7. index_merge: indicates that the query uses more than two indexes. It is an optimization method for index merging, and finally takes the intersection or union. Common and or conditions use different indexes.
8. unique_subquery: used for in-form subqueries in where, the subquery returns unique values ​​without duplication;
9. index_subquery: used for in-form subqueries that use auxiliary indexes or in constant lists. Subqueries may return duplicate values, and indexes can be used to deduplicate subqueries.
10. range: Index range scan, commonly used in queries using operators such as >, <, between, in, and like.
11. index: full table scan of the index, scanning the index tree from beginning to end;
12. all: traverse the entire table to find matching rows (although both Index and ALL read the entire table, index reads from the index, while ALL reads from the hard disk)
13. NULL: MySQL breaks down the statement during optimization and executes it without even accessing the table or index.

possible_keys

Shows the indexes that may be used by the query.

key

Shows which index the query actually used to optimize access to the table;

When select_type is index_merge, more than two indexes may appear here, and other select_types only one index may appear here.

key_len

• The length of the index used to process the query, which indicates the number of bytes used in the index. This value can be used to determine which part of a multi-column index is actually used.
• Note: The value displayed by key_len is the maximum possible length of the index field, not the actual length used. That is, key_len is calculated based on the table definition, not retrieved from the table. In addition, key_len only calculates the length of the index used in the where condition, and even if the index is used for sorting and grouping, it will not be calculated in key_len.

ref

Shows which field or constant is used with key.

• If you are using a constant equal value query, const will be displayed here.
• If it is a join query, the execution plan of the driven table will display the associated fields of the driving table.
• If the condition uses an expression or function, or an internal implicit conversion occurs in the condition column, it may be displayed as func.

rows

Indicates that MySQL roughly estimates the number of rows that need to be read to find the required target record based on table statistics and index selection. It is not an exact value.

extra

Additional important information that doesn't fit in another column.

This column can display a lot of information, there are dozens of types, the commonly used ones are:

Filter by

• This column appears when using explain extended. Versions after 5.7 have this field by default, so there is no need to use explain extended.
• This field indicates the ratio of the number of records that satisfy the query after the data returned by the storage engine is filtered at the server level. Note that it is a percentage, not a specific number of records.

Limitations of MySQL execution plans

1. EXPLAIN will not tell you about triggers, stored procedures, or the effects of user-defined functions on the query;
2. EXPLAIN does not consider various caches;
3. EXPLAIN does not show the optimization work done by MySQL when executing the query;
4. Some statistics are estimates and not exact values;
5. EXPALIN can only explain SELECT operations. Other operations must be rewritten as SELECT and then viewed.

Query Planning Case Study

Execution Order

1. (id = 4): [select id, name from t2]: select_type is union, which means the select of id=4 is the second select in the union.
2. (id = 3): [select id, name from t1 where address = '11']: Because it is a subquery included in the from statement, it is marked as DERIVED. Where address = '11' can be retrieved through the composite index idx_name_email_address, so type is index.
3. (id = 2): [select id from t3]: Because it is a subquery included in the select, it is marked as SUBQUERY.
4. (id = 1): [select d1.name, … d2 from … d1]: select_type is PRIMARY, indicating that the query is the outermost query, and the table column is marked as "derived3", indicating that the query results come from a derived table (the select result of id = 3).
5. (id = NULL): [ … union … ]: represents the stage of reading rows from the temporary table of union. The “union 1, 4” in the table column indicates the union operation using the select results of id=1 and id=4.

Summarize

This is the end of this article about the in-depth exploration of MySQL Explain execution plan. For more relevant MySQL Explain execution plan content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!