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

The following is mainly to bring you the detailed usage of Explain in MySQL, I hope that the detailed usage of Explain in MySQL can bring you practical use, which is also the main purpose of editing this article. Okay, no more nonsense, let's go straight to the following.

、

query Execution plan

syntax

explain select * from table

Column in explain

Expain comes out with 10 columns of information,

id,select_type,table, type,partitions,possible_keys,key,key_len,ref,rows,Extra, respectively. Here are some possible explanations for the appearance of these fields:

I. ID

SQL execution order identification,SQL execution from large to small

1. When the ID is the same, the execution order is from top to bottom.

2. If it is a sub-query, the ID number will be incremented. The higher the ID value, the higher the priority, and the first to be executed.

3. If the ID is the same, it can be considered as a group and executed in sequence from top to bottom; in all groups, the larger the ID value, the higher the priority, and the first to execute.

Select_type

Shows the type of each select clause in the query

SIMPLE: Simple SELECT, not UNION or subquery.

2. PRIMARY: Select the outermost layer.

3. UNION: The second layer, UNION is used after SELECT.

DEPENDENT UNION: The second SELECT in the UNION statement, which depends on the outer subquery.

UNION RESULT: The result of UNION.

SUBQUERY: The first SELECT in a subquery.

DEPENDENT SUBQUERY: The first SELECT in a subquery, depending on the outer query.

DERIVED: SELECT of exported tables (subquery of FROM clause)

9. Materialized: Materialized Subquery

UNCACHEABLE SUBQUERY: Subqueries that cannot cache results must be recalculated for each row of the outer query

UNCACHEABLE UNION: UNION is the second or last choice of an uncacheable subquery.

III. Table

The name of the table referenced by the output row. This can also be one of the following values:

: This line refers to the union of id value M and id value N.

: This row refers to the value N used for the derived table result id from this row. For example, derived tables can come from subqueries in the FROM clause

: This row refers to the result N of the materialized subquery of the row with id value

IV. Type

Indicates how MySQL finds the desired row in the table, also known as an "access type."

Common types are: NULL, system, const, eq_ref, ref, range, index, ALL (from left to right, performance from poor to good)

The following list describes connection types from best to worst

NULL

MySQL decomposes statements during optimization and executes them without even accessing tables or indexes. For example, selecting the minimum value from an index column can be done by a separate index lookup.

system

The table has only one row (e.g., a system table). This is a special case of the const connection type

const

The table has at most one matching row, read at the beginning of the query. Because there is only one row, the rest of the optimizer can treat the values of the columns in that row as constants. Const tables are very fast because they are read only once.

SELECT * FROM tbl_name WHERE primary_key=1;SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

For each row combination in the previous table, read one row from that table. This is the best connection type besides the system and const types. Use it when the join uses all parts of the index and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

ref

Indicates the join matching condition of the above table, i.e. which columns or constants are used to find values on index columns

fulltext

Perform joins using FULLTEXT indexes.

ref_or_null

SELECT * FROM ref_table WHERE key_column IS NULL;

index_merge

The index merge access method retrieves with multi-row range scans and merges their results into one. This access method only merges index scans from a single table, rather than scanning multiple tables. Merging can generate union, crossover, or cross union of its underlying scans

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;SELECT * FROM tbl_nameWHERE (key1 = 10 OR key2 = 20) AND non_key = 30;SELECT * FROM t1, t2WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')AND t2.key1 = t1.some_col;SELECT * FROM t1, t2WHERE t1.key1 = 1AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

unique_subquery

This type replaces some IN subqueries of the form eq_ref:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery

This connection type is similar to unique_subquery. It replaces the IN subquery, but it applies to non-unique indexes in subqueries of the following form:

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

Retrieves only rows within a given range, using the index to select rows. The column in the key output row of indicates which index to use. Include key_len as the key part that has been used for the longest time. The ref column NULL applies to this type.

range =,>,>=, can be used when a key column uses any as opposed to constant

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.