Mysql execution plan Introduction

Bad SQL not only directly affects the SQL response time, but also affects the database performance, resulting in a longer response time for other normal SQL statements. It is vital to learn how to write SQL statements and view execution plans. The following is a brief description of the mysql Execution Plan, which only lists some common situations and hopes to help you.

Test Table Structure:

Copy codeThe Code is as follows:
Create table 't1 '(
'C1 'int (11) not null default '0 ',
'C2 'varchar (128) default null,
'C3' varchar (64) default null,
'C4 'int (11) default null,
Primary key ('c1 '),
KEY 'ind _ c2 '('c2 '),
KEY 'ind _ cid' ('cid ')
) ENGINE = InnoDB default charset = utf8
Create table 't2 '(

'C1 'int (11) not null default '0 ',
'C2 'varchar (128) default null,
'C3' varchar (64) default null,
'C4 'int (11) default null,
Primary key ('c1 '),
KEY 'ind _ c2 '('c2 ')
) ENGINE = InnoDB default charset = utf8

Create table 't3 '(
'C1 'int (11) not null default '0 ',
'C2 'varchar (128) default null,
'C3' varchar (64) default null,
'C4 'int (11) default null,
Primary key ('c1 '),
KEY 'ind _ c2 '('c2 ')
) ENGINE = InnoDB default charset = utf8

1. view the mysql execution plan

Explain select ......

2. Information contained in the execution plan

(1). id

Meaning, indicating the order of select statements or operation tables.

Eg1: The id is the same. The execution sequence is from top to bottom. The following execution plan indicates that table t1 is operated first, table t2 is operated, and table t3 is finally operated.

Eg2: If a subquery exists, the id of the subquery (inner query) is greater than that of the parent query (outer query). The subquery is executed first. The higher the id, the higher the priority.

(2). select_type

Meaning: select statement type

Type:

A. SIMPLE: the query does not contain subqueries or UNION

B. If the query contains any complicated sub-parts, the outermost query is marked as PRIMARY.

C. a subquery is included in the SELECT or WHERE list. The SUBQUERY is marked as SUBQUERY.

D. The subquery contained in the from list is marked as DERIVED (derivative)

E. If the second SELECT statement appears after UNION, it is marked as UNION. If UNION is included in the subquery of the from clause,

The outer SELECT will be marked as DERIVED

F. SELECT for obtaining results from the UNION table is marked as: UNION RESULT

Eg:

<Derived2> is displayed for a table with id 1, indicating that the result is derived from derivative table 2.

If the id is 2, The subquery is used to read Table t3.

Id: 3: union, which is the second select of union and is first executed;

If id is NULL, union result is the type. <union> indicates that the operation with id 1 and the operation with id 3 are combined.

Execution sequence 3-> 2-> 1-> NULL

(3). type

Meaning: the method used to obtain record rows, that is, the mysql access method.

A. ALL: Full Table Scan. MySQL will traverse the entire Table to find matched rows.

B. index: Full Index Scan. The difference between index and ALL is that the index type only traverses the index, and the Index is generally smaller than the record.

Because the index contains c1, c1 and c2 can be queried through index scanning.

C. range: index range scan. The index scan starts at a certain point and returns rows that match the value range. It is common in between, <,>, and other queries.

Note: The range type must use index scanning; otherwise, the type is ALL.

D. ref: Non-unique index scan. All rows matching a single value are returned. It is common in searches with a non-unique prefix of a non-unique index.

T2.c4 is a non-unique index

E. eq_ref: unique index scan. For each index key, there is only one record in the table that matches it. Common in primary key or unique index Scanning

T2.c1 is the primary key index, and the primary key index is also the unique index.

F. const, system: these types of access are used when MySQL optimizes a part of the query and converts it to a constant. For example, place a primary key in the where list,

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

(4). possible_keys

Meaning: indicates which index MySQL can use to find rows in the table. If an index exists in the fields involved in the query, the index will be listed, but not necessarily used by the query.

(5). key

Description: displays the indexes actually used by MySQL in the query. If no index is used, it is NULL.

(6) key_len

Description: The number of bytes used in the index. You can use this column to calculate the length of the Index Used in the query.

(7) ref

Description: used for connection query. It indicates that a column in a specific table is referenced.

(8) rows

Meaning: MySQL estimates the number of rows to be read from the required records based on the table statistics and index selection. This value is inaccurate and only serves as a reference.

(9) Extra

Meaning: displays additional auxiliary information.

A. Using index indicates that the index is used.

B. Using where, which indicates filtering by the where Condition

C. Using temporary, indicating that a temporary table is used, which is common in grouping and sorting.

D. Using filesort, indicating that index sorting cannot be used and files need to be sorted.

Eg1: The t1.c3 column has no index.

Eg2: Use the index column t1.c2