Introduction to extended options in MySQL EXPLAIN statements

The following articles mainly describe the practical application and specific operation steps of the extended option in the MySQL EXPLAIN statement. We all know that there is an explain command in the MySQL database, its main function is to analyze the Running Effect of select statements. For example, explain can obtain select statements.

Indexes used, sorting, and so on. In addition, the extended extension of explain can be used on the basis of the original explain

Some additional query optimization information can be obtained through the show warnings command of MySQL. The following is a simple example.

First, execute MySQL explain for the statement to be analyzed and add the extended option.

MySQL> explain extended select * from account \ G;

1. row

 
 

  
  
id: 1  
  
  
select_type: SIMPLE  
  
  
table: account  
  
  
type: ALL  
  
  
possible_keys: NULL  
  
  
key: NULL  
  
  
key_len: NULL  
  
  
ref: NULL  
  
  
rows: 1  
  
  
filtered: 100.00  
  
  
Extra:  
  
  
1 row in set, 1 warning (0.00 sec) 
 
 

Run Show Warnings.

MySQL> show warnings \ G;

1. row Level: Note

 
 

  
  
Code: 1003  
  
  
Message: select `dbunit`.`account`.`id` AS `id`,`dbunit`.`account`.`name` AS `name` from `dbunit`.`account`  
  
  
1 row in set (0.00 sec) 
 
 

From the output results of show warnings, we can see that the original select * is optimized by MySQL

Select 'dbunit '. 'account'. 'id' AS 'id', 'dbunit'. 'account'. 'name' AS 'name '.

Explain extended not only tells us what MySQL query optimization can do, but also tells us what MySQL

Query Optimization cannot do anything. MySQL performance Extended EXPLAIN in this article, the author uses explain

Extended + show warnings found the areas where the MySQL query optimizer cannot query and optimize.

From explain extended SELECT * FROM sbtest WHERE id> 5 AND id> 6 AND c = "a" AND pad = c

The statement output shows that the MySQL query optimizer cannot combine the two query conditions id> 5 and id> 6 into an id> 6.

In the MySQL performance explain extended article, the third example and the quiet room MySQL explain extended option article,

The two authors have also made further experiments on explain extended. From these two articles, we can draw a conclusion from

In the explain extend output, we can see the SQL Execution method, which is helpful for analyzing SQL.

The content in this article is excerpted from the explain extended option of the quiet room.

The following code is used to analyze the extended options of explain copied to the static room.

 
 

  
  
MySQL>explain extended select * from t where a in (select b from i);  
  
  
+—-+——————–+——-+——+  
  
  
| id | select_type | table | type |  
  
  
+—-+——————–+——-+——+  
  
  
| 1 | PRIMARY | t | ALL |  
  
  
| 2 | DEPENDENT SUBQUERY | i | ALL |  
  
  
+—-+——————–+——-+——+  
  
  
2 rows in set, 1 warning (0.01 sec) 
 
 

The SUBQUERY seems to have nothing to do with external queries. Why does MySQL display dependent subquery,

What about external queries? We can see the cause from the explain extended result.

MySQL> show warnings \ G

1. row

 
 

  
  
Level: Note  
  
  
Code: 1003  
  
  
Message: select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`  
  
  
from `test`.`t` where  
  
  
<in_optimizer>(`test`.`t`.`a`,  
  
  
<exists>(select 1 AS `Not_used` from `test`.`i`  
  
  
where (<cache>(`test`.`t`.`a`) = `test`.`i`.`b`)))  
  
  
1 row in set (0.00 sec) 
 
 

Here, MySQL has rewritten the SQL and optimized in.

The above code and analysis are excerpted to the extended option of explain in the quiet room

However, you must note that the query statement after "optimization" is obtained from EXPLAIN extended + show warnings.

It may not be the final optimized SQL statement, or the information displayed by MySQL explain extended is not enough to indicate that MySQL is the most

The final result of query statement optimization. This is also the second article in MySQL formance's explain Extended article.

The example illustrates this situation.

 
 

  
  
MySQL> EXPLAIN extended SELECT t1.id,t2.pad FROM sbtest t1, sbtest t2 WHERE t1.id=5 
  
  
AND t2.k=t1.k;  
  
  
+—-+————-+——-+——-+—————+———+———+——-+——-+——-+  
  
  
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |  
  
  
+—-+————-+——-+——-+—————+———+———+——-+——-+——-+  
  
  
| 1 | SIMPLE | t1 | const | PRIMARY,k | PRIMARY | 4 | const | 1 | |  
  
  
| 1 | SIMPLE | t2 | ref | k | k | 4 | const | 55561 | |  
  
  
+—-+————-+——-+——-+—————+———+———+——-+——-+——-+  
  
  
2 rows IN SET, 1 warning (0.00 sec)  
  
  
MySQL> SHOW warnings \G  
 
 

1. row Level: Note

 
 

  
  
Code: 1003  
  
  
Message: SELECT `test`.`t1`.`id` AS `id`,`test`.`t2`.`pad` AS `pad` FROM `test`.`sbtest` `t1`  
  
  
JOIN `test`.`sbtest` `t2` WHERE ((`test`.`t2`.`k` = `test`.`t1`.`k`) AND (`test`.`t1`.`id` = 5))  
  
  
1 row IN SET (0.00 sec) 
 
 

From the Explain results, we can see that table t1 queries use the "const" type, that is, when MySQL queries

T1.k is first found by t1.id = 5 and then the t1.k value is used to query data in table t2. Obviously, the query optimization result is not in

In the next Show Warings output, find.

Summary

We can also reference the statements in this article in the extended option of explain. "From the output of MySQL explain extend, we can

It is helpful to analyze the SQL Execution method ".