Explain (1) SQL statement in mysql)

Explain (1) SQL statement in mysql)

 

Explain is used to analyze how mysql uses indexes to process select statements and connect tables. It can help you select better indexes and write more optimized query statements.

1. id

Statement execution sequence identifier. The execution sequence of select statements.

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 statement. In a subquery statement, the outermost select query is primary. The query keyword is irrelevant to the primary key.

 

EXPLAIN SELECT * FROM `fm_company` AS t1 WHERE orgId IN(SELECT orgId FROM fm_company AS t2 WHERE t2.`id` = 1)

2.3 union

 

The select statement following the union statement is executed.

 

EXPLAIN SELECT * FROM `fm_company` AS t1 UNIONSELECT * FROM `fm_company` AS t2

2.4 dependent subquery

The first select statement in the inner layer of the 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 statement for a derived table (intermediate table)

EXPLAIN  SELECT  * FROM (SELECT * FROM `fm_company` AS t1 UNIONSELECT * FROM `fm_company` AS t2)  subQuery

2.6 dependent union

All the select statements that start with the second select statement in the union clause depend 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 union SELECT orgId FROM fm_company AS sub_t2 WHERE sub_t2.`id` = 149)

2.7 union result

As the name implies, it is the result of union.

 

 

3. table
It is very important to display the table name 4. type in the Database accessed in this step. It shows the category used by the connection and whether the index is used. Type indicates the connection mode used by the table specified in the query execution plan (QEP. The connection type from the best to the worst is
4.1.systemsystem is a special case of const, that is, the table has only one record. This is a special case.
4.2.const

The where condition uses a constant as the query condition, and a maximum of one record matches in the table. Because it is a constant, you only need to read it once.

Const is used to compare primary key or unique indexes. Because only one row of data is matched

Remember that the primary key or unique must be used, and only the const is used to retrieve two pieces of data. See the following SQL statement. Search directly with the primary key id

 

 EXPLAIN SELECT * FROM fm_company WHERE id=148

 

4.3. eq_reg can have at most one matching result, which is generally accessed through a primary key or a unique index. It usually appears in the connection query statement.
The mysql manual says this: "for each row combination from the previous table, read a row from the table. This may be the best join type except the const type. It is used to join all parts of an index and the index is UNIQUE or primary key ". Eq_ref can be used to use = to compare columns with indexes.

 EXPLAIN SELECT * FROM fm_company t1  ,fm_company t2  WHERE t1.id=t2.id

I will try again later.
4. ref, 5. range, 6. index, 7. all