1. Explain extendedselect... decompile the execution plan into a SELECT statement and run showwarnings to obtain the query statement optimized by the MYSQL optimizer. 2. explainpartitionsselect... information contained in the explain execution plan of the partitioned table idselect_typetabletypepossible_keyskey
1. explain extended select... decompile the execution plan into a SELECT statement. Run show warnings to obtain the query statement optimized by the MYSQL optimizer. 2. explain partitions select... used for partition table EXPLAIN 3. Information contained in the execution plan id select_type table type possible_keys key
1. explain extended select .....
Decompile the execution plan into a SELECT statement and run show warnings to obtain the query statement optimized by the MYSQL optimizer.
2. explain partitions select ....
Used to EXPLAIN the Partition Table
3. Information contained in the execution plan
Id select_type table type possible_keys key key_len ref rows extra
3.1 id: contains a set of numbers, indicating the order in which the select clause or operation table is executed in the query.
If the IDs are the same, they can be considered as a group and executed sequentially from top to bottom. In all groups, the greater the id value, the higher the priority, the more advanced the execution.
3.2 select_type: indicates the type of each select clause in the query (simple OR complex)
A. simple: the query does not contain subqueries or union
B. If a query contains any complicated child parts, the outermost query is marked as primary.
C. a subquery is included in the SELECT or WHERE list. The SUBQUERY is marked as SUBQUERY.
D. The subquery contained in the from list is marked as DERIVED.
E. If the second SELECT statement appears after UNION, it is marked as UNION. If UNION is included in the subquery of the from clause, the outer SELECT statement is marked as DERIVED.
F. SELECT for obtaining results from the UNION table is marked as: UNION RESULT
3.3 type: indicates how MYSQL finds the required rows in the table, also known as "access type". Common types are as follows:
All index range ref eq_ref const, system null
From left to right, from worst to best
A. ALL: full table scan. MYSQL will traverse the entire TABLE to find matched rows.
B. INDEX: FULL INDEX SCAN. The difference between INDEX and ALL is that only indexes are traversed.
C. RANGE: index RANGE scan. The index scan starts at a certain point and returns rows that match the value RANGE. It is common in between, <,> and other queries.
D. REF: Non-unique index scan, which returns all rows matching a single value. It is common in searches using a non-unique prefix of a non-unique index.
E. EQ_REF: unique index scan. For each index key, there is only one record in the table that matches it. Common in primary key or unique index scanning.
F. CONST, SYSTEM: when MYSQL optimizes a certain part of the query and converts it to a constant, use these types of access, such as placing the primary key in the WHERE list, MYSQL can convert the query into a constant. SYSTEM is a special case of the CONST type. When the queried table has only one row, the SYSTEM
G. NULL: MYSQL breaks down statements during optimization, and does not even need to access tables or indexes during execution.
3.4 possible_keys
It indicates which index MYSQL can use to find rows in the table. If an index exists in the fields involved in the query, the index will be listed, but not necessarily used by the query.
3.5 key
Displays the indexes actually used by MYSQL in the query. If no index is used, it is NULL. If the index is overwritten, the index is displayed in the KEY list.
3.6 key_len
The number of bytes used in the index. You can use this column to calculate the length of the Index Used in the query. The value displayed by key_len is the maximum possible length of the index field, rather than the actual length. That is, KEY_LEN is calculated based on the table definition and is not retrieved from the table.
3.7 ref
The join matching condition of the preceding table, that is, the column or constant used to find the value of the index column.
3.8 rows
It indicates that MYSQL estimates the number of rows to be read Based on the table statistics and index selection.
3.9 extra: contains additional information that is not suitable for displaying but important to other columns.
A. using index: This value indicates that the overwrite INDEX (covering index) is used in the SELECT Operation)
Overwrite index: MYSQL can use the index to return fields in the SELECT list, instead of reading data files again based on the index.
Indexes that contain all the data that meets the query requirements are called overwrite indexes.
Note: To use overwriting indexes, You must retrieve only the required columns from the SELECT list and not SELECT *. If you index all fields together, the index file will be too large, query performance decreases.
Using where: indicates that the MYSQL server performs "filter" after receiving the record by the storage engine. If the query fails to use the index, the function of using where only reminds us that MYSQL will use the WHERE clause to filter the result set.
Using temporary: indicates that MYSQL uses a TEMPORARY table to store the result set, which is common in sorting and grouping queries.
Using filesort: sorting operations that cannot be completed USING indexes in MYSQL are called "File Sorting"
4. Limitations of the MYSQL execution plan:
4.1EXPLAIN won't tell you about triggers, stored procedures, or the impact of user-defined functions on queries
4.2 EXPLAIN does not consider various caches
4.3 EXPLAIN does not display the optimization work performed by MYSQL during query execution.
Part 1 of the statistical information is estimated, not accurate.
4.5 EXPLAIN can only EXPLAIN the SELECT Operation. Other operations must be rewritten to SELECT to view the execution plan.
