MYSQL Execution Plan

Source: Internet
Author: User

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:

1 SELECT 2     * 3  from 4     School5WHERE 6     = ' 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 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.
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:

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.

3.Eq_ref 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:

1 SELECT *  from ref_table 2   WHERE key_column=ORisNULL;
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.

inch (SELECTfromWHERE some_expr)
Unique_subquery

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 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 HAVING clause is always false and cannot select any rows.
  • Impossible WHERE the WHERE clause 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 WHERE The 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 DELETE or UPDATE , the optimizer finds no deleted or updated content after the partition is trimmed.
  • No tables used query no FROM clauses
  • 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 the WHERE rows of all rows that match the clause . The keys are then sorted and the rows are retrieved in sorted order
  • Using 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 BY or 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 0 clause 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: