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

This article takes MySQL 5.7Reference Manual as the main axis (translation & take its essence) and collates it with the strength of hundreds of online articles, because the author's level is limited, please forgive me if there are any inaccuracies in the article, if reprinted, please indicate the source of the original guocun09-Oraman 's diary.

Basic concepts:

EXPLAIN provides information on how SQL statements are executed and works for select,delete,insert,replace,update statements.

EXPLAIN returns a row of information for each table used in the query statement.

All join methods in MySQL use nested-loop join.

I. detailed description

EXPLAIN Output Columns

Column name

explain

Description

Id

Select identifier

Query Optimizer selects the sequence number of the query in the execution plan. Indicates the order in which the select clause or action table is executed in the query. The higher the id value, the higher the priority, and the earlier it is executed. The id is the same and the execution order is from top to bottom

Select_type

Select Typ

It is simple when there is no subquery or union, otherwise there will be primary and union, etc. Note here that the type with uncacheable means it cannot be cached, and the outer row switch will cause the select to be recalculated.

Table

The table to which the output line belongs

Table name or

Partitions

Matching partition

When it comes to table partitioning, if no partition is used, it is NULL.

Type

Join Typ

The following is a detailed description

Possible_keys

Indexes that may be selected

What index can MySQL use in this table to help with the query? if empty, there is no index available.

Key

The actual selected index

The index that is actually selected. If index is not selected, the value is NULL.

Key_len

The length of the selected key

The length of the part used by MySQL in a multipart index, which may have multiple values

Ref

Columns that need to be compared (joined) to the index

Column name or const (constant, where id = 1 is const)

Rows

Estimated number of rows to be tested

InnoDB is not necessarily accurate, it's just an estimate.

Filtered

The percentage of rows filtered by the criteria of the table

Estimated value

Extra

Additional information

Additional information

1.select_type

Select_type Typ

Description

SIMPLE

Simple select query without using union and subquery

PRIMARY

Outermost select query

UNION

The second or subsequent select query in UNION does not depend on the result set of the external query

DEPENDENT UNION

The second or subsequent select query in UNION, depending on the result set of the external query

SUBQUERY

The first select query in the subquery does not depend on the result set of the external query

DEPENDENT SUBQUERY

The first select query in the subquery, depending on the result set of the external query

DERIVED

Used in situations where there are subqueries in the from clause. MySQL recursively executes these subqueries and puts the results in a temporary table

UNCACHEABLE SUBQUERY

Subqueries whose result sets cannot be cached must be re-evaluated for each row of the outer query

UNCACHEABLE UNION

The second or subsequent select query in UNION, which is a subquery that is not cacheable

2.Join type (type field)

Join Typ

(sort by best to worst)

Description

System

The table has only one row (= system table)

Const

Tables have at most one row to match, which is usually used: competition or Unique index

Eq_ref

Each time in parallel with the previous table, only one row is read in that table, which is the best except for system,const.

Characterized by the use of =, and all parts of the index participate in join and the index is a primary key or an index with a non-null unique key

Ref

Using = or, it can be a leftmost prefix index or a non-primary or unique key, and it would be better to match only a few rows at a time

Fulltext

Full-text index search

Ref_or_null

Similar to ref, but including NULL

Example: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL

Index_merge

Index merging, such as having multiple index column in a table in a where condition

Example: SELECT * FROM ref_table WHERE key_column1=expr1and key_column2=expr2

Unique_subquery

Just index lookup, replacing subqueries to achieve better efficiency

Example: value IN (SELECT primary_key FROM single_table WHERE some_expr)

Index_subquery

Ditto, but replace "select non_unique_key_column" in the subquery

Range

Index range retrieval. The key field shows which index is used.

Usually use: =, >, > =, explain select * from emp where emp_no='MW00001'

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 62 | const | 1 | 100.00 | NULL |

+-- +

Explanation: Simple simple single-table query, type:const uses PK,possible_keys: may use index as PRIMARY,key: actual use index as PRIMARY

Example 3.

Mysql > explain select * from emp adept b where an emptiness nameplate Oraman 'and a.dept=b.dept_no

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |

| | 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 137 | gc.a.dept | 1 | 100.00 | NULL |

+-- +

Explanation: the same id of both lines is 1, and the first row is executed first as the driver table.

Simple simple single table query, the first row type:all full table scan, the second row type:eq_ref a table and b table join to = and only one row, ref:gc.a.dept connects table b through table a dept field

Example 4.

Mysql > explain select * from dept b where exists (select * from emp a where age > 30 and a.dept=b.dept_no)

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | PRIMARY | b | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |

| | 2 | DEPENDENT SUBQUERY | a | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |

+-- +

Explanation: the id of 2 is executed first as the second row of the driven table, and the select_type:DEPENDENT SUBQUERY subquery depends on the external query result set. The outermost select of line 1 select_type:PRIMARY

Do you understand the above basic EXPLAIN examples? Is not very simple, the difference between Oracle and let me understand.

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.