Mysql_ Execution Plan detailed description

1 Brief description

Id	The sequential number of the table query.	In descending view, the same ID is viewed from top to bottom. ID can be null, when table is (union, m,n) type, ID is null, this time, the order of ID is m followed by N.
Select_type	How to Query	Described in detail below.
Table	Table name	Table name, alias, (Union M,n).
Partitions	Partition name	The query uses the partition name to the table partition.
Type	Types of table joins	Described in detail below.
Possible_keys	The index that may be used	The index here is only likely to be available, not necessarily in practice.
Key	The index to use	The index that is actually used.
Key_len	Length of use to index	For example, multi-column index, only use the leftmost column, then the length of the index is the length of the column, so the value is not necessarily equal to the length of the key column index.
Ref	Association information for Predicates	When the join type is const, EQ_REF, or ref, the predicate's Association information. Possible: null (when non-const \ EQ_REF \ Ref join type), const (constant), associated predicate column name.
Rows	Number of rows scanned	The number of rows scanned by the table. Note here that inside MySQL is a nested link, so you need to multiply all rows to get the number of times the query data row is associated
Filtered	The actual number of rows represented by the scan rows ratio	Number of rows actually displayed = rows * filtered/100
Extra	Feature usage

2 Select_type

2. Simple, easy Query method, do not use Union and sub-query;
4. PRIMARY, the table is located at the outermost beginning of the query, usually in combination with other query methods;
6. Union, union the first select is primary, and all the second and subsequent select are union select TYPE;
8. Unionresult, after each result set is taken out, will do the merge operation, this operation is UNION result;
10. DEPENDENTUnion, the union operation in a subquery, the Select type of all the SELECT statements from the second and subsequent union of unions is DEPENDENT UNION, which is generally DEPENDENT Subquery together with the application, the first of the sub-query union is dependent subquery;
12. DEPENDENT subquery, the first select in the inner layer of a subquery, depends on the result set of the external query;
14. subquery, the first select of the subquery inner query, the result is not dependent on the external query result set (which will not be overwritten by the database engine);
16. DERIVED, query using inline view;
18. materialized, sub-query materialized, the table appears in the non-correlated subquery and needs to be materialized will appear materialized keywords;
20. uncacheable subquery, the result set cannot cache the subquery, need successive query;
22. uncacheable UNION, which indicates that subqueries are not materialized and need to be run successively.

2 TYPE Performance Sort: null->system->const->eq-ref->ref->fulltext->ref_or_null->index_merge->unique _subquery->index_subquery->range->index->all

2. null, without accessing any one of the tables
4. System
   • Official website Explanation: The table has only one row (= system table). This was a special case of the const join type.
   • The join type is const, and the table contains only 1 rows of records.
6. Const
   • A constant query of a primary key or a unique index, a table with a maximum of 1 rows of records conforming to the query, usually const using a primary key or a unique index for a fixed value query.
   • Constant queries are very fast.
8. Eq_ref
   • In the join query process, the association condition is a primary key or a unique index, and the number of rows out is more than one row
   • Eq_ref is a well-performing join operation.
   • Example: First query all the data from the SU table 7 lines, and then each row with Xin's primary key ID of 1 lines to match.
10. Ref
    • Constant query for nonclustered indexes
    • The performance is also very good.
12. Fulltext
    • During the query, the fulltext index was used (fulltext index in the InnoDB engine, supported only after version 5.6)
    • Example is a table query with Fulltext Index under the InnoDB engine.
14. Ref_or_null
    • Like a ref query, a conditional query that adds more than one null value to a query based on ref
16. Index Merg
    • When a conditional predicate is used to the leftmost column of multiple indexes and the connection between the predicates is or, the index union query is used
18. Unique subquery
    • A branch of eq_ref that queries the primary Key's subquery:
    • Value in (SELECT primary_key from single_table WHERE some_expr)
    • Temporarily unable to simulate, currently in 5.7.17 version how to test, out of the type are Eq_ref
20. Index subquery
    • A branch of ref that queries a subquery for a nonclustered index:
    • Value in (SELECT key_column from single_table WHERE some_expr)
    • Temporarily unable to simulate, currently in 5.7.17 version how to test, out of the type are ref
22. Ranage
    • When a predicate is used to query the index range: =, <>, >, >=, <, <=, is NULL, between, in, <=> (this is an expression: the left side can be rolled to the right, the right side can also be rolled to the left)
24. Index
    • Using the index, but not the index lookup, but rather a scan of the index tree, even the index scan, most of the time is better than full table scan performance, because the index tree key values only the index column key value + primary key, while the full table scan is in the clustered index tree (primary key + all columns) scan, The index tree is much thin compared to the smaller.
26. All
    • Full table scan, poor performance.
    • For index and all, here's another example.
      • , the table SU has 3 indexes: primary key, Ix_age, Ix_name, the contents of the three index trees are: primary key id+ all columns, age+ primary key ID, name+ primary key ID, sequentially, when scanning the primary key ID query, these three indexes can provide the primary key ID column, So which performance is better? The index tree is the smallest, the least scanning is the best, depending on the number of index content can be size: Ix_age < Ix_name < PK, so the execution plan will choose Ix_age.

3 ref when the join type is EQ_REF or ref, the predicate's Association information. Possible: null (when not eq_ref\ref join type), const (constant), associated predicate column name. 4 Extra

• Commonly used to
1. Using index, use to index
2. Using index conditio, use to index filtering
3. Using MRR, use internal sorting to index
4. Using where, use to where condition
5. Using temporary, use to temp table

1. Using Index
   • Index overrides, that is, more than just using the index, and there is no back-table query
   • Let me give you an example of how
   • In these two queries, the conditions are the same, but the first one is to return all the columns, and the index ix_age contains only the primary key columns and index key values, it is necessary to follow the value of the primary key to the PK tree to find the corresponding column, this operation is called a back table, so the first query extra no using INDEX, And the second query has.
2. Using index Conditio, referred to as ICP
3. Using MRR, abbreviation MRR
4. Using where
   • Based on the Where condition, first take out the data and then associate the query with other tables
5. Using Filesort, cannot use index to complete sorting
6. Using temporary, use to temp table
7. When using a temporary table with a small number of tables, the temporary tables use the cache, but when they are large, the disk is stored, in which case the performance will drop sharply

Mysql_ Execution Plan detailed description