Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about the role of the explain field in MySQL. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

The Explain command is the main way to see how the query optimizer decides to execute the query. This feature is limited and does not always tell the truth, but its output is the best information available and is worth taking the time to understand, because you can learn how the query is executed. Learning to explain explain will help you understand how the MySQL optimizer works.

Misunderstandings and deficiencies of Explain

It is a misunderstanding that MySQL does not execute the query when Explain is executed. In fact, the query includes a subquery, so MySQL actually executes the subquery, puts its results in a temporary table, and then optimizes the outer query.

Although we can parse the sql statement by calling explain, we should be aware that the result is also an approximate result and nothing else.

Explain won't tell you how triggers and stored procedures affect queries.

He won't tell you about the specific optimizations made by mysql in query execution.

He will not display all the information about the execution plan of the query

He doesn't distinguish between things with the same name. For example, he uses filesort for both memory sorting and temporary files, and displays using temporary for both temporary tables on disk and in memory

It can be misleading. For example, he would display a full index scan for a query with a very small limit

Interpretation of explain column

Id column

This column always contains a number that identifies the row to which the select belongs. If there is no subquery live federated query in the statement, then there will be only a unique select, so each row will display a 1 in this column. Otherwise, the inner select statements are generally numbered sequentially, corresponding to their position in the original statement

Select_ type column

This column shows whether the sql statement is a simple or complex query.

The simple displayed by a simple query, or primary if there is a subquery or federated query

SUBQUERY

The SELECT contained in the subquery in the SELECT list (in other words, not in the FROM clause) is marked SUBQUERY.

DERIVED

The DERIVED value is used to represent the SELECT contained in the subquery of the FROM clause, and MySQL executes recursively and places the results in a temporary table. The server internally calls it a derived table because the temporary table is derived from a subquery.

UNION

The second and subsequent SELECT in UNION are marked as UNION. The first SELECT is marked as if it were executed as a partial external query. This is why the first SELECT in UNION in the previous example appears as PRIMARY. If UNION is included by a subquery in the FROM clause, its first SELECT is marked DERIVED.

UNION RESULT

The SELECT used to retrieve the results from UNION's anonymous temporary table is marked UNION RESULT.

In addition to these values, SUBQUERY and UNION can also be marked as DEPENDENT and UNCACHEABLE. DEPENDENT means that SELECT depends on the data found in the outer query; UNCACHEABLE means that some features in SELECT prevent the results from being cached in an Item_cache. Item_cache is not documented; it is not the same thing as query caching, although it can be denied by some of the same types of artifacts, such as the RAND () function. )

Table column

Displays the names of the tables in the database accessed in this step.

Type column

This is one of the most important fields that show what type the query uses. The connection types from the best to the worst are:

System,const,eq_ref,ref,fulltext,ref_or_null,index_merge,unique_subquery,index_subquery,range,index,ALL

System

System table with only one row of data in the table

Const

If you read a constant, at most one record will match. Because it is a constant, you only need to read it once.

Eq_ref

There will be at most one match, which is usually accessed through a primary key or a unique key index

Ref

For each row from the previous table, multiple rows can be matched in the index of this table. Use the ref type if the join uses only the leftmost prefix of the index or when the index is not a primary key or unique index (that is, the join can match multiple rows of records).

Ref can be used for index columns that use the'='or''operators for comparison.

Fulltext

This is the type when using full-text indexing. It should be noted that the priority of full-text index is very high. If full-text index and general index exist at the same time, mysql will choose to use full-text index first regardless of cost.

Ref_or_null

Similar to the ref type, except that the comparison of null values is added. It doesn't use much in practice.

Eg.

SELECT * FROM ref_table

WHERE key_column=expr OR key_column IS NULL

Index_merge

Indicates that the query uses more than two indexes, and finally takes intersection or union. It is common that the conditions of and and or use different indexes, which is officially sorted after ref_or_null, but in fact, due to reading multiple indexes, performance may not be as good as range most of the time.

Unique_subquery

Used in the in form subquery in where, the subquery returns unique values that do not repeat values, and can completely replace the subquery, which is more efficient.

This type replaces the ref of the in subquery in the following form:

Value IN (SELECT primary_key FROM single_table WHERE some_expr)

Index_subquery

The returned result field combination in a subquery is an index (or index combination), but not a primary key or unique index

Range

Index range query, commonly used in =, >, > =

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.