MySQL's execution plan

1.explain Parsing SQL statements

For example:

 EXPLAIN select  *  from  blog_info bi inner  join  UAM_ View_unit_account uua 
  on  bi.account_instance_id =  uua.account_instance_id 
 =  0  or  ( Bi.is_comment =  1  Span style= "color: #808080;" >and  bi.is_forward =  1 ) 
  order by  bi. ' Publish_time '   

return Result:

And today is not the SQL, far more complicated than this, but also can reflect the situation.

(1) Using the view causes SQL to use the intermediate table during execution, which is the using temporary. Because the view cannot use the index, this also results in a less efficient performance.

(2) When an order by appears, and the sort field is not indexed, it causes the using Filesort, which causes the file ordering of the disk.

(3) and because Uam_view_unit_account this view is a union4 table (UNION RESULT), neither rows nor type can improve performance.

2. Attribute parsing

Only a few important attributes are remembered:

 select_type: The type of query used, There are several types of queries that are mainly listed below.  DEPENDENT subquery: The first select of the inner layer of a subquery that relies on the result set of an external query. DEPENDENT Union: The Union in the subquery, and all subsequent select from the second select in the Union, also depend on the result set of the outer query. PRIMARY: The outermost query in the subquery, note that it is not a primary key query. Simple: A query other than a subquery or union. Subquery: The first select of a subquery inner query, the result is not dependent on the external query result set. Uncacheable subquery: The result set cannot be cached by a subquery. All select after the second select starts in the Union:union statement, and the first select is primary. The merge result in the UNION result:union. 

 type: tells us how to access the table, mainly containing the following set types.  all : Full table scan.  const  : read constant, at most one record match, because it is a constant, In fact, it only needs to be read once. Eq_ref: There will be a maximum of one matching result, typically accessed through a primary key or unique key index. Fulltext: Full-text index retrieval. index : Full index scan. Index_merge: Two (or more) indexes are used in the query, then the index results are merged (merge), and then the table data is read. Index_subquery: The returned result field combination in a subquery is an index (or combination of indexes), but not a primary key or unique index. Rang: Index range scan. The query that is referenced by the drive table index in the Ref:join statement. Ref_or_null: The only difference from ref is that you add a query with a null value in addition to the query referenced by the index. System: A table with only one row of data; Unique_subquery: The returned result field combination in a subquery is a primary KEY or a unique constraint. 
  

(1) type shows the type of access, which is an important indicator, The result values from good to bad are:
system > const  > eq_ref > ref > fulltext > ref_or_null > index_ Merge > unique_subquery > index_subquery > range > index > all
range level, preferably to ref

 Extra: The additional details that are implemented in each step of the query are mainly the following. Distinct: Find the Distinct value, when MySQL finds the first matching result, it stops the query for that value, and then goes back to the other value query. Full scan on null key: An optimization method in a subquery that is primarily encountered when the use of Null values cannot be accessed through an index. Range checked forEach record (index MAP:N): As described in the official MySQL manual, when MySQL Query Optimizer does not find a good index to use, a partial index can be used if it finds that the column values of the preceding table are known. For each row combination of the preceding table, MySQL checks whether the range or Index_merge access method can be used to request rows. SELECT tables Optimized away: When we use some aggregate functions to access a field that exists in the index, MySQL query Optimizer navigates directly to the desired data row through the index to complete the query. Of course, the premise is that you cannot have GROUP by operations in Query. When using min () or Max (). Using filesort: When query contains an order by operation, and the index cannot be used to complete the sort operation, MySQL Query Optimizer has to choose the appropriate sorting algorithm to implement. Using Index: The required data is all available at index and does not need to be taken to the table. Using Index forGroup-by: With data access and using index, the required data only needs to read the index, and when the group BY or DISTINCT clause is used in query, the information in the extra is the using index if the grouping field is also in the index forgroup-by . using temporary: When MySQL must use temporary tables in certain operations, the using temporary appears in the Extra information. It is mostly common in operations such as GROUP by and ORDER by. Using Where: If you do not read all of the table's data, or you can obtain all the required data simply by indexing, the using where information appears. Using where with pushed condition: This is a message that appears only in the Ndbcluster storage engine, and it needs to be used by turning on the Condition pushdown optimization feature. Control parameters are
Engine_condition_pushdown. Impossible WHERE noticed after readingConstTables:mysql Query Optimizer The statistical information collected to determine the impossibility of the existence of results. The no Tables:query statement uses the from dual or does not contain any from clauses. NOT exists: In some left connections, MySQL query optimizer can partially reduce the number of data accesses by using an optimization method that changes the composition of the original query. 

(1) To make the query as fast as possible, it should be noted that the value of the extra field is Usingfilesort and the using temporary case.

3. Small summary

(1) If a SQL execution is found to be very slow, then direct SQL needs to be explain analyzed in the database.

(2) Look at the explain table, focusing on the type, rows, extra three properties inside.

1.type is all or index;

2.extra with using filesort or using temporary;

The value of 3.rows is similar to the number of data bars in its table;

This time represents the need for this SQL to be optimized.

(3) Build relevant indexes based on analysis hints, or even modify SQL notation to improve efficiency.