MySQL has an explain command that can be used to analyze the running effect of the SELECT statement. For example, explain can obtain the SELECT statement.
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, explain 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 * was optimized by MySQL to
select 'dbunit '. 'account '. 'id' as 'id', 'dbu Nit '. 'account'. 'name' as 'name '.
explain extended not only tells us what MySQL query optimization can do, but also tells us what MySQL query optimization can do.
. In this article, the author uses explain
extended + show warnings to find the areas that cannot be optimized in the MySQL query optimizer.
from the explain extended select * From sbtest where ID> 5 and ID> 6 and c = "a" and pad = C
statement output, we know MySQL query optimization. you cannot combine the two query conditions ID> 5 and ID> 6 into an ID> 6. In MySQL performance's explain extendedArticleIn the third example and the extended option Article of the explain in the static room,
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. /******************************CodeAnd the extended option **************/
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 static 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 in the explain extended statement 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 | 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 obtain that table T1 is used for query. "const" type, that is to say, during MySQL query,
t1.id = 5 first finds t1.k and then uses the value of t1.k to query data in table T2, obviously, the query optimization result is not found in the
show warings output.
conclusion
or reference the statements in this article about the extended option of explain." From the output of explain extend, we can see
the SQL Execution method, which is helpful for analyzing SQL ".
related resources
explain extended MySQL performance
explain extended option of the Quiet Room
MySQL Reference Manual syntax (obtain select-related information) |
