MySQL SQL statement explain (i)

Reference: http://www.2cto.com/database/201307/230048.html

http://blog.csdn.net/zhuxineli/article/details/14455029

Explain is used to analyze how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements.

1. ID

The order of execution of the statement is identified. Refers to the order in which SELECT statements are executed

2, Select_type:2.1. Simple

simple Type, as long as there is no subquery or union in the statement.

EXPLAIN SELECT * from ' fm_company '

2.2 Primary

The outermost select, in the statement with the subquery, the outermost select query is primary. Query keyword independent of primary key

EXPLAIN select * from ' Fm_company ' as T1 where OrgId in (SELECT orgId from Fm_company as T2 WHERE t2. ' id ' = 1)

2.3Union

The following select of the Union statement executes the execution statement.

EXPLAIN SELECT * from ' fm_company ' as T1 unionselect * from ' fm_company ' as T2

2.4Dependent subquery 

The first SELECT statement in the inner layer of a subquery.

EXPLAIN select * from ' Fm_company ' as T1 where OrgId in (SELECT orgId from Fm_company as Sub_t1 WHERE sub_t1. ' id ' = 148)

2.5.devived

Query statements for derived tables (intermediate tables)

EXPLAIN  Select  * FROM (SELECT * from ' Fm_company ' as T1 unionselect * from ' fm_company ' as T2)  subquery

2.6.Dependent Union

A subquery is a union and all subsequent select, starting with the second select in the Union, depends on the external result set.

EXPLAIN select * from ' Fm_company ' as T1 where OrgId in (SELECT orgId from Fm_company as Sub_t1 WHERE sub_t1. ' id ' = 148 uni On SELECT orgId from Fm_company as Sub_t2 WHERE sub_t2. ' id ' = 149)

2.7Union result

As the name implies is the result of merging in union

3. Table
Displays the name of the table in the database accessed by this step 4. The type column is important, showing which category the connection is using, and if there are any indexes used. The type represents the connection method used by the table specified in the Query execution plan (QEP). The best to worst connection type is
4.1.systemsystem is a const special case where there is only one record in the table. This is a special case.
4.2.const

The const is a constant in the Where condition as a query condition, with up to one record match in the table. Because it is a constant, you actually need to read it only once.

The const is used to compare primary key or unique index. Because it matches only one row of data, it quickly

Remember that the primary key or unique must be used, and only two data will be retrieved in the case of Const. As in the following SQL. Find directly with primary key ID

EXPLAIN SELECT * from Fm_company WHERE id=148

4.3. Eq_regthere will be at most one matching result, typically accessed through a primary key or a unique index. Typically appears in the statement of the connection query.
mysql Manual reads:" For each row combination from the preceding table, read one row from the table. This may be the best type of join except Const UNIQUE or Span style= "Color:rgb (51,51,51); font-family:arial; font-size:14px; Line-height:26px ">primary KEY ". Eq_ref can be used to compare indexed columns with

EXPLAIN SELECT * from Fm_company T1  , Fm_company T2  WHERE t1.id=t2.id

There is time to study it later.
4. Ref, 5. Range, 6.index, 7. All

MySQL SQL statement explain (i)