Mysql Implementation Plan Introduction _mysql

Bad SQL not only directly affects the SQL response time, but also affects the performance of the DB, resulting in other normal SQL response time to become longer. How to write good sql, learn to see the implementation plan is essential. Let me briefly talk about the implementation of MySQL plan, only listed a number of common situations, I hope to help.

Test table structure:

Copy Code code as follows:

CREATE TABLE ' t1 ' (
  ' C1 ' int (one) not NULL DEFAULT ' 0 ',
  ' C2 ' varchar (128) default NULL,
  ' c3 ' varchar ($) default NULL,
  ' c4 ' int (one) default NULL,
&NB Sp PRIMARY key (' C1 '),
  key ' ind_c2 ' (' C2 '),
  key ' ind_c4 ' (' C4 ')
) Engine=innodb DEFAULT charset= UTF8
 create TABLE ' T2 ' (

  ' C1 ' int (one) not NULL DEFAULT ' 0 ',
  ' c2 ' varchar (128) DE FAULT null,
  ' c3 ' varchar ($) default NULL,
  ' c4 ' int (one) default null,
  PRIMARY KEY (' C1 '),
  KEY ' ind_c2 ' (' C2 ')
) Engine=innodb DEFAULT charset=utf8

CREATE TABLE ' T3 ' (
  ' C1 ' int (one) NO T null default ' 0 ',
  ' c2 ' varchar (128) default NULL,
  ' c3 ' varchar ($) default NULL,
  ' C4 ' int ( One) default NULL,
  PRIMARY key (' C1 '),
  KEY ' ind_c2 ' (' C2 ')
) engine=innodb default Charset=utf8

1. View MySQL Execution plan

Explain select ...

2. Information contained in the execution plan

(1). ID

Meaning that indicates the order of the select Phrase or action table.

Eg1:id the same, the execution order is from top to bottom, the following execution plan indicates that the first Operation T1 table, then Operation T2 table, finally operation T3 table.

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

(2). Select_type

Meaning: type of SELECT statement

Type:

A.simple: Query does not contain subqueries or union

B. If the query contains any complex sub sections, the outermost query is marked as: PRIMARY

C. A subquery is included in the Select or where list, which 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 in the FROM clause,

The outer select will be marked as: DERIVED

F. A select that obtains results from the Union table is marked as: Union result

eg

A table with ID 1 displays <DERIVED2>, indicating that the result is derived from the Derivative table 2.

ID 2 for subqueries, read T3 table

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

The type of the ID null is union result, and <union 1,3> represents an action with ID 1 and an operation with ID of 3 to combine the results set.

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

(3). Type

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

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

B.index:full index Scan,index and all differ to index types only traverse indexes, which are generally smaller than records.

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

C.range: Index range scanning, scanning of indexes starting at a certain point, returning rows that match the domain, common in between, <, >, etc.

Note: The range type is definitely using an index scan, otherwise type is all

D.ref: A non-uniqueness index scan that returns all rows that match a single value. Lookup that is common in a non unique prefix that uses a unique index, that is, an exclusive index

T2.C4 is not a unique index

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

T2.C1 Primary key index is also a unique index

F.const, System: Use these types of access when MySQL optimizes a part of a 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, and system is a special case of the const type, which, when the query table has only one row, uses system

(4). Possible_keys

Meaning: Indicates which index MySQL can use to find rows in the table, and if the query involves a field where an index is present, 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, if the index is not used, display as null

(6) Key_len

Meaning: Represents the number of bytes used in the index, through which the length of the index used in the query can be calculated

(7) ref

Meaning: Used to connect a query to indicate that a column in a specific table is referenced

(8) Rows

Meaning: MySQL According to the table statistics and index selection, estimated to find the required records to read the number of rows, this value is not accurate, only reference meaning.

(9) Extra

Meaning: Show some auxiliary extra information

A.using index, indicating the use of indexes

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

C.using temporary, which means that temporary tables are used, which are common in grouping and sorting

D.using Filesort, which means that you cannot use index sorting, which requires file sorting

EG1:T1.C3 column has no index

EG2: Using indexed columns T1.C2