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 SQL1 SELECT 2 * 3 from 4 School5WHERE 6 = ' County First primary School '
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 small, if interested can see another blog: MySQL execution plan id is empty-union keyword, here no longer repeat.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.|
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:
The 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 a
const special 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.
system is the
const best 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 ref5EXPLAINSELECT6 *7 from8 Test,9 test2Ten WHERE OneTest. NAME=test2. NAME A andTest2.job= ' -'test ref and Eq_ref
Execution results such as:
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:
Ref_or_null1 SELECT * from ref_table 2 WHERE key_column=ORisNULL;
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.
Unique_subqueryinch (SELECTfromWHERE 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)
Therange 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.
Index scans all indexes, and the index is scanned as a whole.
All full table scan, the slowest query. Should avoidPossible_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:
FirstMatch(the semi-connected Firstmatch connection shortcut policy is used
HAVINGclause is always false and cannot select any rows.
WHEREclause is always false and no rows can be selected.
system) tables and notes that the
WHEREThe clause is always false.
UPDATE, the optimizer finds no deleted or updated content after the partition is trimmed.
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 .
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.
WHERErows of all rows that match the clause . The keys are then sorted and the rows are retrieved in sorted order
Using index 仅使用索引树中的信息从表中检索列信息，而不必另外寻找读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。
DISTINCTAll 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.
ORDER BYclauses that are listed in different ways. .
LIMIT 0clause and cannot select any rows.