Syntax and usage of mysql sexplain

Syntax and usage of mysql tutorial sexplain

Explain [extended] select... From... Where...

If extended is used, after the explain statement is executed, you can use the show warnings statement to query the corresponding

.

For example, we execute select uid from user where uname = 'scofield' order by uid to execute the statement.

If yes

| Id | select_type | table | type | possible_keys | key | key_len |

Ref | rows | Extra |

These things.

Table indicates the data of the table.

Type is important. Indicates the link type. From good to bad, the link type is system> const>.

Eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery>

Index_subquery> range> index> ALL

Generally, at least the range level should be reached, preferably the ref level. Otherwise, there may be performance problems.

Possible_keys indicates the index that can be applied to the table. If it is NULL, no.

Key refers to the index used.

Key_len is the index length. The smaller the value, the better, without affecting the query accuracy.

Ref indicates that the column of the index is used. It is usually a constant.

Rows indicates the number of rows.

Extra refers to additional information. It is also important. If the value is distinct, mysql finds the domain connection.

The matched rows are no longer searched.

If the value is not exits: mysql optimizes left join. Once the matching row of left join is found

No more searches.

If the value is rang checked for each: the ideal index is not found.

If it is using filesort, you need to improve the SQL. This indicates that File Sorting is required for mysql execution. This is more

Which has a greater impact on efficiency.

If it is using temporary, a temporary table is used. This situation also affects efficiency, and SQL needs to be modified.

. You can also make improvements at the application layer.

If where used is used, the where statement is used. If the type is all or index

Sample results. This is generally because the query needs to be improved.

In a system that is generally slightly larger, the join and subquery operations are minimized. Mysql uses the simplest Query

, Which is the most efficient. Join and so on can be solved at the application layer.

Ii. explain the output

+ ---- + ------------- + ------- + ------------------- + ---------

+ ------- + ------ + ------- +

| Id | select_type | table | type | possible_keys | key | key_len |

Ref | rows | Extra |

+ ---- + ------------- + ------- + ------------------- + ---------

+ ------- + ------ + ------- +

1. id

My understanding is the mark of the smooth execution of SQL statements, from large to small.

For example:

Mysql> explain select * from (select * from t3 where

Id = 3952602) a) B;

+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ---

------ + ------- +

| Id | select_type | table | type | possible_keys | key |

Key_len | ref | rows | Extra |

+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ---

------ + ------- +

| 1 | PRIMARY | <derived2> | system | NULL |

NULL | 1 |

| 2 | DERIVED | <derived3> | system | NULL |

NULL | 1 |

| 3 | DERIVED | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4

| 1 |

+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ---

------ + ------- +

Obviously, this SQL statement is executed from the inside out, that is, from id = 3 up.

2. select_type

Is the select type, there can be the following types

(1) SIMPLE

Simple SELECT (without UNION or subquery) Example:

Mysql> explain select * from t3 where id = 3952602;

+ ---- + ------------- + ------- + ------------------- + ---------

+ ------- + ------ + ------- +

| Id | select_type | table | type | possible_keys | key | key_len |

Ref | rows | Extra |

+ ---- + ------------- + ------- + ------------------- + ---------

+ ------- + ------ + ------- +

| 1 | SIMPLE | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4 |

Const | 1 |

+ ---- + ------------- + ------- + ------------------- + ---------

+ ------- + ------ + ------- +

(2). PRIMARY

In my understanding, select is the outermost layer. For example:

Mysql> explain select * from (select * from t3 where id = 3952602);

+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ---

------ + ------- +

| Id | select_type | table | type | possible_keys | key |

Key_len | ref | rows | Extra |

+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ---

------ + ------- +

| 1 | PRIMARY | <derived2> | system | NULL |

NULL | 1 |

| 2 | DERIVED | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4

| 1 |

+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ---

------ + ------- +

(3). UNION

The second or subsequent SELECT statement in UNION. For example:

Mysql> explain select * from t3 where id = 3952602 union all select * from t3

;

+ ---- + -------------- + ------------ + ------- + ------------------- + --------- + ---

------ + ------- +

| Id | select_type | table | type | possible_keys | key |

Key_len | ref | rows | Extra |

+ ---- + -------------- + ------------ + ------- + ------------------- + --------- + ---

------ + ------- +

| 1 | PRIMARY | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4

| Const | 1 |

| 2 | UNION | t3 | ALL | NULL |

NULL | 1000 |

| NULL | union result | <union1, 2> | ALL | NULL |

NULL |

+ ---- + -------------- + ------------ + ------- + ------------------- + --------- + ---

------ + ------- +

(4). DEPENDENT UNION

The second or subsequent SELECT statement in UNION depends on the external query.

Mysql> explain select * from t3 where id in (select id from t3 where

Id = 3952602 union all select id from t3 );

+ ---- + -------------------- + ------------ + -------- + ------------------- + ------

--- + --------- + ------- + ------ + -------------------------- +

| Id | select_type | table | type | possible_keys | key

| Key_len | ref | rows | Extra |

+ ---- + -------------------- + ------------ + -------- + ------------------- + ------

--- + --------- + ------- + ------ + -------------------------- +

| 1 | PRIMARY | t3 | ALL | NULL

| NULL | 1000 | Using where |

| 2 | dependent subquery | t3 | const | PRIMARY, idx_t3_id |

PRIMARY | 4 | const | 1 | Using index |

| 3 | dependent union | t3 | eq_ref | PRIMARY, idx_t3_id |

PRIMARY | 4 | func | 1 | Using where; Using index |

| NULL | union result | <union2, 3> | ALL | NULL

| NULL |

+ ---- + -------------------- + ------------ + -------- + ------------------- + ------

--- + --------- + ------- + ------ + -------------------------- +