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

This article is about how to use explain in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Analyze the implementation plan of sql-explain

Explain can simulate sql optimized execution of sql statements.

1. Introduction to explan

(1) user list

(2) Department table

(3) Index is not triggered

(4) trigger index

(5) result analysis.

The table that appears in the first row in explain is the driver table.

When a join condition is specified, the table with a small number of rows of records satisfying the query condition is the [driving table].

When no join condition is specified, the table with a small number of rows is [driving table]

Sorting the driven table directly triggers the index, while sorting the undriven table does not trigger the index.

2. Brief introduction of explain query results

(1) id:SELECT identifier. This is the query serial number of SELECT.

(2) select_type:SELECT type:

SIMPLE: simple SELECT (no UNION or subquery)

PRIMARY: outermost SELECT

The second or subsequent SELECT statement in UNION:UNION

The second or subsequent SELECT statement in DEPENDENT UNION:UNION, depending on the external query

Results of UNION RESULT:UNION

SUBQUERY: the first SELECT in the subquery

DEPENDENT SUBQUERY: the first SELECT in the subquery, depending on the external query

DERIVED: export the SELECT of the table (a subquery of the FROM clause)

(3) table: table name

(4) type: join type

System: the table has only one row (= system table). This is a special case of the const join type.

Const: the table has at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constant by the rest of the optimizer. Const is used when comparing all parts of an PRIMARY KEY or UNIQUE index with constant values.

Eq_ref: for each combination of rows from the previous table, read a row from that table. This is probably the best join type, except for the const type. It is used by joins in all parts of an index and the index is UNIQUE or PRIMARY KEY. Eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses the columns of the table read before the table.

Ref: for each row combination from the previous table, all rows with matching index values will be read from this table. Use ref if the join uses only the leftmost prefix of the key, or if the key is not UNIQUE or PRIMARY KEY (in other words, if the join cannot select a single row based on the keyword). This join type is good if the keys used match only a small number of rows. Ref can be used for indexed columns that use the = or operator.

Ref_or_null: this join type is like ref, but MySQL is added to specifically search for rows that contain null values. The optimization of this join type is often used in solving subqueries.

Index_merge: this join type indicates that the index merge optimization method is used. In this case, the key column contains a list of the indexes used, and the key_len contains the longest key elements of the index used.

Unique_subquery: this type replaces the ref:value IN (SELECT primary_key FROMsingle_table WHERE some_expr) of the in subquery in the following form; unique_subquery is an index lookup function that completely replaces the subquery and is more efficient.

Index_subquery: this join type is similar to unique_subquery. In subqueries can be replaced, but only for non-unique indexes in the following form of subqueries: value IN (SELECT key_column FROM single_table WHERE some_expr)

Range: only retrieve a given range of rows, using an index to select rows. The key column shows which index is used. Key_len contains the longest key element of the index used. In this type, ref is listed as NULL. When using =, >, >,

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.