Shulou(Shulou.com)06/01 Report--

The explain command is as follows:

Mysql > explain select * from t_blog +-- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | SIMPLE | t_blog | ALL | NULL | 7 | +- -+ 1 row in set

Where the select_type column indicates the type of read operation for the SQL.

There are six types of select_type: simple, primmy, subQuery, derived, union, union result.

1 、 simple

Indicates that the sql is a simple select and does not contain any subqueries and union, for example:

Mysql > explain select * from t_blog +-- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | SIMPLE | t_blog | ALL | NULL | 7 | +- -+ 1 row in set

2 、 primmy

If any subquery is included in the query, the outermost query is marked as primmy, for example:

Mysql > explain select * from t_blog where id = (select id from t_type where name = "JAVA") +-+ | id | select_type | table | type | possible_keys | key | Key_len | ref | rows | Extra | +-+ | 1 | PRIMARY | | t_blog | const | PRIMARY | PRIMARY | 4 | const | 1 | 2 | SUBQUERY | t_type | ALL | NULL | 4 | Using where | + -+ 2 rows in set

This sql reads a total of two tables, t_type is loaded as a subquery, and t_blog is marked as PRIMMY as the outermost read operation.

3 、 subquery

In the query, if a subquery is included in the select or where self-occupation, the subquery is marked as subquery, such as t_type in the example above

4 、 derived

The list in from contains a subquery, which is marked as derived (derivative), for example:

Mysql > explain select * from t_blog inner join (select id from t_type) an on t_blog.typeId = a.id +-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | PRIMARY | | ALL | NULL | 4 | | 1 | PRIMARY | t_blog | ALL | NULL | 7 | Using where | 2 | DERIVED | t_type | NULL | PRIMARY | 4 | NULL | 4 | Using index | + -+ 3 rows in set

When the t_type table is read, it appears in the from statement, so it is marked derived. Mysql will first put the query results of the subquery of the from statement into a temporary table, and then return the final result, so it is a memory-consuming operation. It is worth noting that the word appears in the table column of the first row, indicating that the table is a derived virtual table, and his source is found based on the following number "2", which is the id of the first column, indicating that it is a derived virtual table of the derived table with id of 2.

5 、 union

If the sql contains union, the second select will be marked as union

Mysql > explain select * from t_blog b left join t_type t on b.typeId = t.id union select * from t_blog b1 right join t_type T1 on b1.typeId = t1.id +-+ | id | select_type | table | | type | possible_keys | key | key_len | ref | rows | Extra | +-- | -+ | 1 | PRIMARY | b | ALL | NULL | 7 | | 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 4 | blog.b.typeId | 1 | | 2 | UNION | T1 | ALL | NULL | 4 | | 2 | UNION | B1 | ALL | NULL | 7 | | NULL | UNION RESULT | | ALL | NULL | | +-- -+ 5 rows in set

Both T1 and T2 tables appear in the select after union, so they are marked as union.

6 、 union result

Indicates that the query is the select that gets the result from the union table and is the result set of union. As in the example above, the previous select is the result set of the obtained union.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.