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:
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 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:
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.
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:
Ref_or_nullSELECT * FROM ref_table WHERE key_column=expr OR key_column IS 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
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.
The index that is actually used. If the index is not used, NULL is displayed.
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.
Which fields and keys are used together. No use.
The number of rows affected. are not particularly precise.
Explain the additional information. Contains some additional information about MySQL for query optimization. very useful . The results may appear as follows:
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.