看懂mysql执行计划--官方文档

MysqL was able to do a  optimization on the query and does not examine more rows in this table for the prevIoUs row combination after it finds one row that matches the  criteria. Here is an example of the type of query that can be optimized this way:

Assume that  is defined as . In this case,MysqL scans  and looks up the rows in  using the values of . If MysqL finds a matching row in ,it kNows that  can never be ,and does not scan through the rest of the rows in  that have the same  value. In other words,for each row in ,MysqL needs to do only a single lookup in ,regardless of how many rows actually match in .
 (JsON property: none)
This value occurs with  when the optimizer has not finished creating the execution plan for the statement executing in the named connection. If execution plan output comprises multiple lines,any or all of them Could have this  value,depending on the progress of the optimizer in determining the full execution plan.
N) (JsON property: )
MysqL found no good index to use,but found that some of indexes might be used after column values from preceding tables are kNown. For each row combination in the preceding tables,MysqL checks whether it is possible to use a or  access method to retrIEve rows. This is not very fast,but is faster than performing a join with no index at all. The applicability criteria are as described in ,and ,with the exception that all column values for the preceding table are kNown and consIDered to be constants.
Indexes are numbered beginning with 1,in the same order as shown by  for the table. The index map value N is a bitmask value that indicates which indexes are candIDates. For example,a value of  (binary 11001) means that indexes 1,4,and 5 will be consIDered.
N databases (JsON property: )
This indicates how many directory scans the server performs when processing a query for  tables,as described in . The value of N can be 0,1,or .
 (JSON property: )
The optimizer determined 1) that at most one row should be returned,and 2) that to produce this row,a deterministic set of rows must be read. When the rows to be read can be read during the optimization phase (for example,by reading index rows),there is no need to read any tables during query execution.
The first condition is fulfilled when the query is implicitly grouped (contains an aggregate function but no  clause). The second condition is fulfilled when one row lookup is performed per index used. The number of indexes read determines the number of rows to read.
Consider the following implicitly grouped query:
Suppose that  can be retrieved by reading one index row and  can be retrieved by reading one row from a different index. That is,for each column  and ,there exists an index where the column is the first column of the index. In this case,one row is returned,produced by reading two deterministic rows.
This  value does not occur if the rows to read are not deterministic. Consider this query:
Suppose that  is a covering index. Using this index,all rows with  must be scanned to find the minimum  value. By contrast,consider this query:
In this case,the first index row with  contains the minimum  value. Only one row must be read to produce the returned row.
For storage engines that maintain an exact row count per table (such as ,but not ),this  value can occur for  queries for which the  clause is missing or always true and there is no  clause. (This is an instance of an implicitly grouped query where the storage engine influences whether a deterministic number of rows can be read.)
, ,  (JSON property: )
These values indicate file-opening optimizations that apply to queries for  tables,“Optimizing INFORMATION_SCHEMA Queries”.
: table files do not need to be opened. The information has already become available within the query by scanning the database directory.
: Only the table's  file need be opened.
: The unoptimized information lookup. The , ,and  files must be opened.
,  (JsON property: )
This indicates temporary table use for the semi-join Duplicate Weedout strategy.
 (JsON property: )
For a query such as tbl_name,no rows satisfy the condition for a  index or  on the table.
 (JsON property: )
MysqL must do an extra pass to find out how to retrIEve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the  clause. The keys then are sorted and the rows are retrIEved in sorted order. See .
 (JSON property: )
The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
For  tables that have a user-defined clustered index,that index can be used even when  is absent from the  column. This is the case if  is  and  is .
 (JsON property: )
tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way,index information is used to defer (
 (JSON property: )
Similar to the  table access method,  indicates that MySQL found an index that can be used to retrieve all columns of a  or  query without any extra disk access to the actual table. Additionally,the index is used in the most efficient way so that for each group,only a few index entries are read. For details,see .
,  (JsON property: )
tables from earlIEr joins are read in portions into the join buffer,and then their rows are used from the buffer to perform the join with the current table.  indicates use of the Block nested-Loop algorithm and indicates use of the Batched Key Access algorithm. That is,the keys from the table on the preceding line of the  output will be buffered,and the matching rows will be fetched in batches from the table represented by the line in which  appears.
In JsON-formatted output,the value of  is always either one of  or .
 (JsON property: )
tables are read using the Multi-Range Read optimization strategy. See title="9.2.1.13 Multi-Range Read Optimization" href="https://dev.MysqL.com/doc/refman/5.7/en/mrr-optimization.HTML">Section 9.2.1.13,“Multi-Range Read Optimization”.
, ,  (JsON property: )
These indicate how index scans are merged for the  join type. See title="9.2.1.4 Index Merge Optimization" href="https://dev.MysqL.com/doc/refman/5.7/en/index-merge-optimization.HTML">Section 9.2.1.4,“Index Merge Optimization”.
 (JsON property: )
To resolve the query,MysqL needs to create a temporary table to hold the result. This typically happens if the query contains  and  clauses that List columns differently.
 (JsON property: )
A  clause is used to restrict which rows to match against the next table or send to the clIEnt. Unless you specifically intend to fetch or examine all rows from the table,you may have something wrong in your query if the  value is not  and the table join type is  or .
 has no direct counterpart in JsON-formatted output; the  property contains any  condition used.
 (JsON property: )
This item applIEs to  tables only. It means that MysqL Cluster is using the Condition Pushdown optimization to improve the efficIEncy of a direct comparison between a nonindexed column and a constant. In such cases,the condition is
 (JSON property: )
The query had a  clause and cannot select any rows.
EXPLAIN Output Interpretation
You can get a good indication of how good a join is by taking the product of the values in the  column of the  output. This should tell you roughly how many rows MysqL must examine to execute the query. If you restrict querIEs with the  system variable,this row product also is used to determine which multiple-table  statements to execute and which to abort. See title="6.1.1 Configuring the Server" href="https://dev.MysqL.com/doc/refman/5.7/en/server-configuration.HTML">Section 6.1.1,“Configuring the Server”.
The following example shows how a multiple-table join can be optimized progressively based on the information provIDed by .
Suppose that you have the  statement shown here and that you plan to examine it using :
For this example,make the following assumptions:
The columns being compared have been declared as follows.
 (primary key) (primary key) values are not evenly distributed.
Initially,before any optimizations have been performed,the  statement produces the following information:
Because  is  for each table,this output indicates that MysqL is generating a Cartesian product of all the tables; that is,every combination of rows. This takes quite a long time,because the product of the number of rows in each table must be examined. For the case at hand,this product is 74 × 2135 × 74 × 3872 = 45,268,558,720 rows. If the tables were bigger,you can only imagine how long it would take.
One problem here is that MysqL can use indexes on columns more efficIEntly if they are declared as the same type and size. In this context,  and  are consIDered the same if they are declared as the same size.  is declared as  and  is ,so there is a length mismatch.
To fix this disparity between column lengths,use  to lengthen  from 10 characters to 15 characters:
 ALTER table tt MODIFY ActualPC VARCHAR(15);
Now  and  are both . Executing the  statement again produces this result:
This is not perfect,but is much better: The product of the  values is less by a factor of 74. This version executes in a couple of seconds.
A second alteration can be made to eliminate the column length mismatches for the  and  comparisons:
 ALTER table tt MODIFY AssignedPC VARCHAR(15),    ->                MODIFY ClIEntID   VARCHAR(15);
After that modification,  produces the output shown here:
At this point,the query is optimized almost as well as possible. The remaining problem is that,by default,MysqL assumes that values in the  column are evenly distributed,and that is not the case for the  table. Fortunately,it is easy to tell MysqL to analyze the key distribution:
 ANALYZE table tt;
With the additional index information,the join is perfect and  produces this result:
name="IDm140007032046432">The  column in the output from  is an educated guess from the MysqL join optimizer. Check whether the numbers are even close to the truth by comparing the  product with the actual number of rows that the query returns. If the numbers are quite different,you might get better performance by using  in your  statement and trying to List the tables in a different order in the  clause. (However,  may prevent indexes from being used because it disables semi-join transformations. See .)
It is possible in some cases to execute statements that modify data when  is used with a subquery; for more information,see title="14.2.10.8 SubquerIEs in the FROM Clause" href="https://dev.MysqL.com/doc/refman/5.7/en/from-clause-subquerIEs.HTML">Section 14.2.10.8,“SubquerIEs in the FROM Clause”.
          总结       
以上是内存溢出为你收集整理的看懂mysql执行计划--官方文档全部内容，希望文章能够帮你解决看懂mysql执行计划--官方文档所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/sjk/1169184.html