The syntax and usage of MySQL Sexplain

MySQL tutorial sexplain syntax and usage

Explain [extended] select ... from ... where ...

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

Optimization information.

For example, we execute the Select UID from user where uname= ' Scofield ' The Order by UID execution knot

Fruit will have

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len |

Ref | Rows | Extra |

These things.

Where table represents the data for which tables.

Type is more important. Represents the type of link. Link types are good to bad, followed by System > Const >

Eq_ref > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery >

Index_subquery > Range > Index > All

Generally, you must reach at least the range level, preferably the ref level. Otherwise, there may be a performance problem.

Possible_keys refers to an index that can be applied to the table, if null.

Key refers to the index used.

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

Ref refers to the column in which the index is used. is usually a constant.

Rows refers to how many rows there are.

Extra refers to additional information. is also more important. If the value is distinct, it means that MySQL found the domain inline

Matches the row, it is no longer looked up.

If the value is not exits:mysql optimizes the left join, once a left join is found, the row is

No more searches are done.

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

If you are using Filesort, you need to improve SQL. This means that MySQL execution requires file sorting. This is more than

More effective than the effect.

If the using temporary is used, this is a temporary table. This situation also affects efficiency, SQL needs to change

Into. or improvements from the application layer.

If you use a where statement for the where used description. If type is all or index, it usually appears

Kind of results. Such a problem, generally, is that the query needs to be improved.

In a generally slightly larger system, the basic as much as possible to reduce the join, subqueries and so on. MySQL uses the simplest query

, which is the most efficient. As for join, you can put it in the application layer to solve

Two. Explain output interpretation

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

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

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len |

Ref | Rows | Extra |

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

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

1.id

My understanding is that SQL performs smoothly with the identification of SQL from large to small executions.

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 |

NULL |    NULL |       1 | |

| 2 | DERIVED | <derived3> | System | NULL | NULL |

NULL |    NULL |       1 | |

| 3 | DERIVED | T3 | Const | primary,idx_t3_id | PRIMARY | 4

|    |       1 | |

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

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

It is clear that this SQL is executed from the inside out, or from the id=3 up.

2. Select_type

Is the Select type, you can have the following

(1) Simple

Simple select (Do not use union or subqueries, etc.) for 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

My understanding is the outermost select. For example:

Mysql> Explain SELECT * FROM (SELECT * from T3 where id=3952602) A;

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

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

| ID | Select_type | Table | Type | Possible_keys | Key |

Key_len | Ref | Rows | Extra |

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

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

| 1 | PRIMARY | <derived2> | System | NULL | NULL |

NULL |    NULL |       1 | |

| 2 | DERIVED | T3 | Const | primary,idx_t3_id | PRIMARY | 4

|    |       1 | |

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

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

(3). UNION

The second or subsequent SELECT statement in the 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 |

NULL | NULL |       1000 | |

| NULL | UNION Result | <union1,2> | All | NULL | NULL |

NULL | NULL |       NULL | |

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

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

(4). DEPENDENT UNION

The second or subsequent SELECT statement in the Union, depending on the query outside

Mysql> explain select * from T3 where IDs in (select IDs from T3 where

id=3952602 UNION ALL select IDs from T3);

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

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

| ID | Select_type | Table | Type | Possible_keys | Key

| Key_len | Ref | Rows | Extra |

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

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

| 1 | PRIMARY | T3 | All | NULL | Null

| 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 | The Using where; Using Index |

| NULL | UNION Result | <union2,3> | All | NULL | Null

| NULL | NULL |                          NULL | |

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

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