"Go" MySQL Execution plan introduction

Original address: http://www.jb51.net/article/43306.htm

1. View MySQL Execution plan

SELECT *    from where STATUS<>4;

2. Information contained in the execution plan

(1). ID

Meaning, indicating the order of the select sentence or action table.

Eg1:id the same, the execution sequence is from top to bottom, the following execution plan represents the first operation of the T1 table, then the T2 table, and finally the T3 table operation.

EG2: If there is a subquery, the subquery (inner query) ID is larger than the parent query (outer query), and the subquery is executed first. The larger the ID, the higher the priority.

(2). Select_type

Meaning: The type of the SELECT statement

Type:

A.simple: The query does not contain subqueries or union

B. If any complex sub-parts are included in the query, the outermost query is marked as: PRIMARY

C. A subquery is included in the Select or where list, and the subquery is marked as: subquery

D. Subqueries included in the From list are marked as: DERIVED (derived)

E. If the second select appears after the union, it is marked as union, and if the Union is contained in a subquery of the FROM clause,

The outer select will be marked as: DERIVED

F. Select that gets the result from the Union table is marked: Union result

eg

Table with ID 1 shows <derived2>, indicating that the result is derived from table 2.

ID 2 indicates subquery, read T3 table

The ID 3 type is union, which is the second select of the Union, which is executed first;

A type with an ID of NULL is union result, <union 1,3> represents an operation with ID 1 and a result set merge with an operation with ID 3.

Execution Order 3->2->1->null

(3). Type

Meaning: Gets the way the record line is used, that is, how MySQL accesses it.

A.all:full table Scan, MySQL will traverse the full table to find a matching row

B.index:full index Scan,index is different from all for index types that only traverse the index, which is generally smaller than the record.

Because the index contains C1, the query C1,C2 can be implemented through an index scan.

C.range: Index range Scan, scan of index starts at a point, return rows matching the value of the domain, common in between, <, > and other queries

Note: The range type must be an index scan, otherwise type is all

D.ref: A non-unique index scan that returns all rows that match a single value. Lookups that are common to non-unique prefixes that use non-unique indexes that are unique indexes

T2.C4 is not a unique index

E.eq_ref: Unique index Scan, for each index key, only one record in the table matches it. Common to primary key or unique index scans

T2.C1 index, primary key index is also a unique index

F.const, System: Use these types of access when MySQL optimizes a part of the query and converts it to a constant. If you place the primary key in the Where list,

MySQL can convert the query to a constant, system is a special case of the const type, and when the queried table has only one row, use the system

(4). Possible_keys

Meaning: Indicates which index MySQL can use to find rows in the table, and if there are indexes on the fields involved in the query, the index will be listed but not necessarily used by the query

(5). Key

Meaning: Displays the index that MySQL actually uses in the query, and displays NULL if no index is used

(6) Key_len

Meaning: Represents the number of bytes used in the index, which evaluates the length of the index used in the query

(7) ref

Meaning: Used to connect a query that indicates that a column of a particular table is referenced

(8) Rows

Meaning: MySQL estimates the number of rows required to find the required records, based on table statistics and index selection, which is inaccurate and only refers to meaning.

(9) Extra

Meaning: Show some additional auxiliary information

A.using index, which indicates that indexes are used

B.using where, which means filtering through the Where condition

C.using temporary, representing the use of temporal tables, common in grouping and sorting

D.using Filesort, indicating that the index sort cannot be used, and that file sorting is required

EG1:T1.C3 column does not have an index

EG2: Using indexed columns T1.C2

"Go" MySQL Execution plan introduction