Id:id is a set of numbers that indicate the order in which a SELECT clause or action table is executed in a query, if the ID is the same, the execution order is from top to bottom, and if it is a subquery, the sequence number of the ID is incremented, and the higher the ID, the higher the priority, and the first execution. The table for which the ID column is null is this is a result set and does not need to be used for querying.Select_type:simple: Represents an easy 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. Primary: A select that requires a union operation or contains a subquery, and the select_type of the outermost unit query is primary. and only one. subquery: In addition to subqueries contained in the FROM clause, subqueries that appear elsewhere may be subquerydependent subquery: Similar to dependent union, indicates that this subquery query is affected by external table queries. subqueries, also called derived tables, that appear in derived:from words, may be called inline views or nested select in other databases. union:union connected Two select queries, the first query is a dervied derived table, except for the first table, the second table Select_type is union. Dependent Union: As with union, it appears in the Union or UNION ALL statement, but the query is affected by external queriesUnion 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. table:The name of the query table that is displayed, and if the query uses an alias, the alias is shown here. If the operation on the data table is not involved, this is displayed as null. If the <derived n>, which is enclosed in angle brackets, indicates that this is a temporary table, the back N is the ID in the execution plan, indicating that the result is from this query. If the <union m,n> 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.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 one index. System: There is only one row of data or empty table in the table and can only be used for MyISAM and memory tables. If it is a InnoDB engine table, the Type column in this case is usually all or indexConst: 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 scans. 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 the primary key or unique index of the second table, and must be not NULL, when the unique index and primary key are multiple columns, only the eq_ref appears when all columns are used as comparisons. Ref: 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, it is common to find the equivalent of the secondary index. 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. 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. Ref_or_null: Similar to the ref method, only increases the comparison of null values. The actual use is not much. Unique_subquery: For in-form subqueries in where, subqueries return distinct value unique values. index_subquery: Used for in-form subqueries with a secondary index or in constant list, the subquery may return duplicate values, and the subquery can be weighed using an index. Range: Index range Scan, common in queries that use >,<,is Null,between, in, and like operators. 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 inferior to range. Index : Indexed full-table scan, sweep the index from start to finish, it is common to use indexed columns to handle queries that do not need to read data files, can use indexes to sort or group queries. All: This is the full table scan data file, and then the server layer to filter back to meet the requirements of the record. Possible_keys: The index to which the query may be used is listed here. Key: The query is really used to the index, when Select_type is Index_merge, there may be more than two index, the other select_type here only one appears. Key_len: The length of the index used to process the query, if it is a single-column index, then the entire index length, if it is a multi-column index, then the query may not be able to use all the columns, the specific number of columns to use the index, which will be counted in, no use of the column, this is not counted in. 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. In addition , Key_len only calculates the index length used by the Where condition, and sorting and grouping are not counted to Key_len, even if the index is used. Ref: If the constant-value query is used, the const is shown here, and if it is a connection query, the execution plan of the driver table will show the associated field of the driver table, if the condition uses an expression or function, or if the conditional column has an implicit internal conversion, it may appear as Func. rows: This is the estimated number of scanned rows in the execution plan, not the exact values. Extra: This column can display a lot of information, there are dozens of kinds, commonly used are:distinct: The DISTINC keyword was used in the Select sectionno tables used: query without FROM clause or from dual query. A connection query using the not 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.using Filesort: This occurs when the index cannot be used when sorting. Common in order BY and group by statements. using index: Query is not required to return a table query, directly through the index to get the data of the query. using_union: Indicates that when a condition using an or connection is used for each index, that information represents a collection from the processing resultusing intersect: Represents a condition that is used to obtain an intersection from a processing result when using an index of andusing Sort_union and using sort_intersection: Similar to the previous two counterparts, except that they appear when querying for large amounts of information with and and OR, the primary key is queried, and then the merge is sorted before the record can be read and returned. using where: Indicates that the records returned by the storage engine do not all satisfy the query criteria and need 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 displays the using index conditionUsing 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. Firstmatch (tb_name): 5.6.x introduces one of the new features of the optimized subquery, which is common in the WHERE clause contains a subquery of type in (). If the data volume of the inner table is larger, this may occurLoosescan (M.. 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 the In () type subqueryfiltered: This column appears when you use explain extended, and the version after 5.7 has this field by default and does not require the use of 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.
Radishes
Source: Radish's blog http://www.cnblogs.com/xiaoboluo768/
Explain implementation plan detailed
