MySQL explain explained

1), id column

The larger the number the first execution, if the number is the same size, then proceed from the top down, the ID column is NULL for the table is this is a result set, do not need to use it to query.

&NBSP;2), Select_type columns are common: a:simple: Represents a simple select query that does not require a union operation or does not contain subqueries. When there is a connection query, the outer query is simple, and there is only one b:primary: a select that requires a union operation or a subquery, and the select_type of the outermost unit query is primary. And there is only one c:union:union connected two select query, the first query is a dervied derived table, except for the first table, the second table Select_type is Union d:dependent Union: As with Union,　　Appears in the union or UNION ALL statement, but the query is affected by external queries e:union result: The result set containing the union, in the Union and UNION all statements, because it does not need to participate in the query, so the ID field is NULL F:subquery: In addition to subqueries contained in the FROM clause, subqueries that appear elsewhere may be subquery g:dependent subquery: Similar to dependent Union, Indicates that this subquery query is affected by external table queries H:derived:from the subquery that appears in the sentence, also called the derived table, may be called an inline view or nested select 3 in the other database, and the query table name displayed by the table. If the query uses aliases, then the alias is shown here, and if the operation on the data table is not involved, then this is shown as null if the <derived n> shown in angle brackets indicates that this is a temporary table, and the following N is the ID in the execution plan. Indicates that the result is derived from this query. If the <union m,n&gt is enclosed in angle brackets, like <derived n>, it is also a temporary table that indicates that the result is derived from the result set of the union query with the ID m,n. &NBSP;4), type from good to poor: system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_ Merge,index,all, except for all, other type can be used to the index, except Index_merge, the other type can only use an index A:system: The table is only one row of data or empty table, and can only be used for MyISAM andMemory table. If it is a InnoDB engine table, the Type column in this case is usually all or index b:const: When a unique index or primary key is used, the return record must be an equivalent where condition of 1 rows of records, usually the type is const. Other databases are also called unique index scan c:eq_ref: In a query plan that is connected to a table, the driver table returns only one row of data, and the row data is a primary key or a unique index for the second table, and must be not NULL, when the unique index and primary key are multiple columns. Eq_ref D:ref only occurs when all columns are used as comparisons: Unlike Eq_ref, which requires a connection order, there is no requirement for a primary key and a unique index, as long as an equality condition is used to retrieve it, a common lookup for equivalence with the secondary index is found.　　Or a multi-column primary key, a unique index, the use of columns other than the first column as an equivalent lookup also appears, in short, the return data is not unique to the equivalent of a lookup can occur. E:fulltext: Full-text index retrieval, note that full-text indexing is very high priority, if the full-text index and normal index exist simultaneously, MySQL regardless of the cost, priority to use full-text index F:ref_or_null: Similar to the ref method, just increase the comparison of null values.　　The actual use is not much. G:unique_subquery: For in-form subqueries in where, subqueries return distinct value unique values H:index_subquery: used for in-form subqueries with secondary indexes or in constant lists, subqueries may return duplicate values,　　You can use the index to go back to the sub-query.　　I:range: Index range scanning, common in queries that use operators such as >,<,is Null,between, in, and like. J:index_merge: Indicates that the query uses more than two indexes, and finally takes the intersection or the set, common and, or the condition uses a different index, the official sort this after ref_or_null, but actually because to read the index,　　Performance may not be as good as Range K:index: Index full table scan, sweep the index from start to finish, common in the use of indexed columns to handle queries that do not need to read data files, can use indexes to sort or group queries. L:all: This is the full table scan data file, and then the server layer to filter back to meet the requirements of the record.  5), Possible_keys query may be used to the index will be listed here  6), key query really used index, select_type to Index_merge, here may appear more than two indexes, The other select_type here onlyThere will be one. &NBSP;7), Key_len is used to process the index length of the query, if it is a single-column index, then the entire index length, if it is a multi-column index, then the query will not necessarily be able to use all the columns, the specific number of columns to use the index, here will be counted, no use of the column, It's not going to count in here. Pay attention to the value of this column, and calculate the total length of your multi-column index to see if all the columns are used. Note that the index used by the ICP attribute of MySQL is not counted into it. Other than thatkey_len only calculates the index length used for where conditions, and the sorting and grouping are not counted to key_len, even if the index is used. &NBSP;8), ref if the constant equivalent query is used, this will show the const, if it is a connection query, the driver table's execution plan here will show the driver table of the associated field, if the condition uses the expression or function, or the condition column has an internal implicit conversion, this may appear as Func &NBSP;9), rows here is the number of scanned rows estimated in the execution plan, not the exact value  10), extra this column can display a lot of information, there are dozens of kinds, commonly used a:distinct: In the Select section using the DISTINC keyword B:no Tables used: Query without a FROM clause or from dual query C: A connection query using the non in () Form subquery or the NOT EXISTS operator, which is called an anti-join.　　That is, the general connection query is to query the inner table, and then query the appearance, anti-connection is to query the appearance first, then query the inner table. D:using Filesort: This occurs when the index cannot be used when sorting.　　Common in order BY and GROUP BY statements E:using Index: Queries do not need to return a table query, directly through the index to get the data of the query. F:using Join buffer (block nested loop), using join buffer (batched key Accss): Version after 5.6.x optimizes the Bnl,bka attribute of the associated query.　　It is mainly to reduce the number of loops in the inner table and scan the query sequentially. G:using sort_union,using_union,using intersect,using sort_intersection:using intersect: Represents the criteria for each index using and This information represents the acquisition of the intersection using union from the processing result, which represents the acquisition and use of the using sort_union and using sort_intersection from the processing result when using or to concatenate the conditions of each working index: similar to the previous two counterparts,　　It's just that they appear when querying the information with and and OR, the primary key is queried, and then the merge is sorted before the record can be read and returned. H:using Temporary: Indicates that interim tables are used to store intermediate results.　　Temporary tables can be memory temporary tables and disk temporary tables, the execution plan is not visible, you need to see the status variables, used_tmp_table,used_tmp_disk_table to see. I:using Where: Indicates that the records returned by the storage engine are not allsatisfies the query criteria and needs to be filtered at the server level. The query criteria are divided into constraints and check conditions, before 5.6, the storage engine can only scan the data based on the constraints and return, and then the server layer is filtered based on the inspection criteria and then returns the data that really conforms to the query. After 5.6.x, the ICP feature can be pushed down to the storage engine layer, data that does not meet the check conditions and restrictions, and does not read directly, thus greatly reducing the number of records scanned by the storage engine. The extra column shows one of the new features of the optimized subquery introduced by using index condition J:firstmatch (tb_name): 5.6.x, which is common in the WHERE clause contains a subquery of type in (). If the amount of data in the inner table is larger, this k:loosescan (m.) may occur. N): One of the new features of the optimized subquery introduced after 5.6.x, which may occur when a subquery returns a duplicate record in a subquery of type in () this   in addition to these, there are many query data dictionary libraries, in the execution of the planning process found that there is no possible results of some hints &NBSP;11), Filtered this column appears when using explain extended, and the version after 5.7 has this field by default and does not need to use explain extended. This field indicates how much of the data returned by the storage engine is filtered by the server layer, the percentage of records that are satisfied with the query, and the percent, not the specific number of records.   

Original turnip

Source: The Radish blog http://www.cnblogs.com/xiaoboluo768/

MySQL explain explained