MYSQL Execution Plan

MySQL execution plan is the SQL statement after the query optimizer, the query optimizer based on the user's SQL statement contains the number of fields and the amount of content such as statistics, select an execution efficiency optimal (MySQL system considered optimal) execution plan, and then according to the execution plan, call the storage engine provided by the interface, Get the data.

However, at the time of MySQL execution, exactly what kind of execution plan was used and whether the index was used. When the data size is large, when SQL executes, the execution plan is different, which directly affects the speed of SQL execution. At this point, you need to debug the SQL statement execution.

MySQL when we debug SQL statements, we do not debug code in the same way we write Java or other language code, by breaking points. At this point, we need to debug our SQL by looking at the execution plan. MySQL uses explain to see the execution plan, and when we write SQL statements, we add a explain to the statement. Explain can be used in statements such as SELECT, DELETE, INSERT, Peplace, and update, such as:

EXPLAIN SELECT
    *
FROM
    school
WHERE
    school_nick = 'County First Primary School'

EXPLAIN SQL 

After execution, the results are as follows:

Here is a description of each field and what each field means:

ID  

The ID is an identifier for the execution plan and is the ordinal of the select query. If the result assembly is combined with the result of another table with the Union keyword, the ID may be empty.

If the ID is empty, the impact on the execution plan is smal.

select_type   

Select_type represents the type of SQL statement query. Specifically, the following table is represented:

The value of the Select_type	Meaning
Simple	A simple select query that does not use associations and subqueries.
PRIMARY	The outermost select, which contains the subquery, is the outermost query
UNION	In a union query, the second or subsequent subquery operation
DEPENDENT UNION	In a union query, a second subquery or a subsequent select query needs to rely on an external query
UNION RESULT	Return result set of union
Subquery	The first SELECT statement of a subquery statement
DEPENDENT subquery	The first subquery that relies on an external query
DERIVED	Derived table--the temporary table is a subquery derived from a subquery that is located in a form
Materialized	Materialized sub-query (not sure what the meaning, after the study to come back to add, or the great God advice)
Uncacheable subquery	Subqueries that cannot cache results must be recalculated for each row of the external query
Uncacheable UNION	A second or later non-cacheable subquery in the union.

table

The name of the table referenced by the output row. It is generally a table name or alias, or it may be the following value:

1.UNION set of the set of results.

2.derivedN the current row points to the derived result set. May be a derived table, such as a result set from the FROM clause.

3.subqueryN the current row points to the result set of a subquery.

type

The connection type. The column output indicates how the table is joined. The following types represent the best to worst types

1. System This table has only one row (= system table). This is aconstspecial case of the connection type.

2.Const can only match at most one line, at the beginning of the query, calculate the corresponding address of the constant, direct access, for example: SELECT * FROM test where name = ' Zhang ' When name is a unique index, it is possible to appear const.constvery fast, because it is read only once.

3.Eq_ref systemis theconstbest connection type except for and type. Use all parts of the index when two tables are combined (for a composite index), and when the index is a primary key or a unique index. Use the "=" operator for comparison of indexed columns.

4.ref is a non-unique index scan that returns all rows of a matching value. Common terms are not unique indexes. For those unfamiliar with eq_ref and ref, you can look at the following code: 

1 --A unique index is added to the name field of the test table, and the Test2 job row adds a non-unique index. 
2 --After this code executes, the TEST2 query is executed first to find out all the collections of job = ' teacher '. 
3 --so Test2 's Typ is a result set that is ref that matches job = ' teacher '. 
4 --The collection of name is then removed from the result set to match the results of the test1.name. 
Because Test1.name is a unique index, a name matches up to one record, so the type of test is ref
5 EXPLAINSELECT
6 *
7  from
8 Test,
9 test2
10 WHERE OneTest. NAME=test2. NAME A  andTest2.job= ' -'

5.Fulltext is queried using the fulltext index.

6.Ref_or_null This type of link is similar to ref, but in addition to ref, a value that contains null is searched. Often used to parse subqueries. The code examples are as follows:

 
 
SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

Ref_or_null

7.index_merge This link type represents the use of index merge optimizations. The output content is included in the index list.

8.unique_subquery Index Lookup, replace subquery, to improve efficiency.

 
value IN (SELECT primary_key FROM single_table WHERE some_expr)

 9. Index_subquery similar to Unique_subquery but replaces in subquery, applies to non-unique indexes, code: value in (SELECT Key_ Column from single_table WHERE some_expr)

10.The range scans a partial index, and a scan of the index starts at a point and returns the value of an indexed area. Common queries that have index-based <,>, and so on.

11.Index scans all indexes, and the index is scanned as a whole.

12.All full table scan, the slowest query. Should avoid

Possible_keys

The key that may be used indicates that the rows involved in the current query contain those indexes. If there is an index, it is listed, but it is not necessarily used.

Key

The index that is actually used. If the index is not used, NULL is displayed.

Key_len

The maximum possible length of the corresponding index in the table. You can change this value by setting the index length. For example: a varchar (255) index length of 255, but we do not use so long, we can take the first five bits of the string as the index. Then Key_len is 5. Here is a selection rule for the length of the index value, and later there is a chance to write a blog detail.

ref

Which fields and keys are used together. No use.

rows

The number of rows affected. are not particularly precise.

Extra

Explain the additional information. Contains some additional information about MySQL for query optimization. very useful . The results may appear as follows:

• Const row not found the table is empty
• Deleting All Rows table data is flagged for deletion and is being deleted (some storage engines support a way to delete all rows in a simple and quick way, and the query will appear)
• Distinct MySQL is looking for a different value, so it stops searching for more rows for the current combination of rows after it finds the first matching row.
• FirstMatch(tbl_name) the semi-connected Firstmatch connection shortcut policy is used tbl_name .
• Full scan on NULL key when the optimizer cannot use the index to find the access method, the subquery optimization occurs as a fallback policy.
• Impossible having the HAVINGclause is always false and cannot select any rows.
• Impossible WHERE the WHEREclause is always false and no rows can be selected.
• Impossible WHERE noticed after reading const tables MySQL has read all const(and system) tables and notes that the WHEREThe clause is always false.
• No matching Min/max row does not have rows that satisfy the criteria of the query
• No matching row in const table for queries with joins, there is either an empty table or a row that does not meet the unique index criteria.
• No matching rows after partition pruning for DELETEor UPDATE, the optimizer finds no deleted or updated content after the partition is trimmed.
• No tables used query no FROMclauses
• Not exists the contents of the query do not exist
• Plan isn't ready yetwhen the optimizer has not completed creating an execution plan for statements executed in a named connection, This value will appear .
• Range checked for each record MySQL finds that no good index can be used, but finds that the column values in the preceding table may use some indexes.
• Recursive recursion
• Span>skip_open_table ,   open_ Frm_only ,   OPEN_FULL_TABLE&NBSP;&NBSP;

• • skip_open_table : The table file does not need to be opened. This information has been obtained from the data dictionary.

  • open_frm_only: Only data dictionary to read table information.

  • open_full_table : Non-optimized information lookup. The must read the table information from the data dictionary and read the table file.

• Unique row not found for queries , there are no rows that satisfy the criteria for the index or table.
• Using Filesort uses file sorting. MySQL must perform additional passes to find out how rows are retrieved in sorted order. sorting is done by traversing all rows based on the connection type and storing the sort keys and pointers to theWHERE rows of all rows that match the clause . The keys are then sorted and the rows are retrieved in sorted order
• Using index Use only the information in the index tree to retrieve column information from the table without having to look for another actual row. This strategy can be used when the query uses only columns that belong to a single index.
• Using index condition determines whether the complete table row can be read by first accessing the index.
• The using index for group-by uses an index grouping. indicates that MySQL has found an index that can be used to retrieve GROUP BYor DISTINCT All Columns of the query without any additional disk access to the actual table. In addition, indexes are used in the most efficient way, so only a few index entries are read for each group.
• Using index for skip scan to skip scanning ranges
• The Using join buffer reads the table data into the cache and then reads the data from the cache to perform the operation.
• The using MRR uses a multi-range read optimization strategy to read the table.
• Using temporary with temporary tables,MySQL needs to create a temporary table to hold the results. This typically occurs if the query contains columns and GROUP BY ORDER BY clauses that are listed in different ways. .
• The using where uses a where restriction, which means that the MySQL server "Post-filter" after the storage engine has been recorded, if the query fails to use the index, the using The role of where is just to remind us that MySQL will filter the result set with a WHERE clause.
• The Zero limit query has a LIMIT 0clause and cannot select any rows.
• Only index This means that information is retrieved only from the information in the index tree, which is faster than scanning the entire table.