MySQL EXPLAIN SQL Output Information description

    EXPLAIN语句能够被用于获取一些关于SQL执行时的相关信息，比如表的连接顺序，对表的方式方式等等。通过对该相关信息进行进一步的分析，我们    可以通过对表添加适当的索引，以及优化连接顺序，使用提示等等手段来达到使SQL高效运行的目的。本文描述了EXPLAIN的用法并给出了相关示例。

I. Overview of explain

EXPLAIN 语句主要是用于解析SQL执行计划，通过分析执行计划采取适当的优化方式提高SQL运行的效率。EXPLAIN 语句输出通常包括id列，select_type，table，type，possible_keys，key等等列信息MySQL 5.6.3后支持SELECT, DELETE, INSERT,REPLACE, and UPDATE. EXPLAIN EXTENDED支持一些额外的执行计划相关的信息EXPLAIN PARTITIONS支持基于分区表查询执行计划的相关信息

Second, explain output column description

--The following example shows the explain output column ([email protected]) [sakila]> explain select sum (amount) from customer a,- Payment B where 1=1 and a.customer_id=b.customer_id and- email= ' [email protected] ' \g************************** * 1. Row * **************************id:1Select_type:simpletable:aType:allPossible_keys:primaryKey:nullKey_len:nullRef:nullrows:590extra:using where************************** * 2. Row * **************************id:1Select_type:simpletable:bType:refPossible_keys:idx_Fk_customer_Idkey:idx_fk_customer_idKey_len:2ref:sakila.a.customer_idrows:14Extra:

1, the meaning of the column expression

Column meaning------        ------------------------------------ID theSELECTIdentifier Select_type theSELECTTypeTableTheTable  forTheOutput RowPartitions the matching Partitionstype theJoinTypepossible_keys the possible indexes toChooseKeyIndex actually chosenkey_len the length ofThe ChosenKeyRef the columns compared toThe indexrowsEstimate of rows  toBe examinedfiltered Percentage of rowsFiltered by TableConditionextra Additional Information

2, the specific description on each column

ID: Contains a set of numbers that represent the execution in a querySelectThe order ID of the clause or action table is the same, the execution order is from top to bottom, otherwise the higher the ID value (usually the subquery is generated) the higher the priority, the first is the execution ID if the same, can be considered a group, from the top down order execution; In all groups, the higher the ID value, the higher the priority, and the Represents a query in eachSelectType of clause (simpleORComplex) Select_typeValueMeaning------------------------------------------------------------Simple and simpleSELECT( not using UNION orsubqueries)PRIMARYOutermostSELECTThe outermost layerSelect    UNION                    Second orLaterSELECTStatementinchAUNIONDEPENDENTUNION          Second orLaterSELECTStatementinchAUNION, dependent on outerQueryUNIONResult result ofAUNION. Subquery First SELECT inchSubquery DEPENDENT subquery First SELECT inchsubquery, dependent on outerQuery (typically a correlated subquery) DERIVED DERIVEDTable SELECT(subqueryinch  fromclause) materialized materialized subquery uncacheable subquery A subquery forWhich the result cannot be cached andMust be reevaluated for  each Row  ofTheouterQuery uncacheableUNIONTheSecond orLaterSelect inchAUNIONthat belongs toAn uncacheable subquery (see uncacheable subquery)Table: from which table (table name) The row records are output, or the following values:? <unionm,n>: theRowRefers toTheUnion  ofTherows  withIdValues  ofM andN.? <derivedn>: theRowRefers toThe derivedTableResult forTheRow  withAn IDvalue  ofN. A DerivedTableMay result, forExample fromA subqueryinchThe fromClause. ? <subqueryn>: theRowRefers toThe result ofA materialized subquery forTheRow  withAn IDvalue  ofN. Partitions: The query matches the record from which partition, when using explain, the partition partitions keyword is specified when type: Connection type system table has only one row const Table has a maximum of one row to match, the common use for primary KEY or unique index comparison eq_ref each time with the previous table merge rows only read one row in the table, this is the best except system,const, characterized by the use of =, and the index of the Some of them are involved.JoinAnd index is the index of the primary key or non-null unique key if you match only a few rows at a time, it is a good one, using = or <=>, can be left overlay index or non-primary key or non-unique key fulltext full-text Search Ref_or_ Null is similar to ref, but includesNULLIndex_merge indicates that an index merge optimization (including intersection, Union, and union between intersections) has occurred, but does not include cross-table and full-text indexes. This is more complicated, and the current understanding is to merge the range index scan of a single table (if the cost estimate is better than the normal range) unique_subqueryinchSub-query, isvalue inch(Select...) The shape is like "SelectUnique_key_column "is replaced by a subquery. PS: So not necessarilyinchThe use of subqueries in clauses is inefficient! Index_subquery Ibid, but the shape of the "SelectNon_unique_key_column "to replace the range of the range constant value index A. When the query is indexed, that is, when all data is available from the index tree (extraUsingB. Full table scan of data rows from the index in index order (noneUsingC. If the extraUsingIndex andUsing WhereAt the same time, it is the use of the index to find the meaning of the key; d. If it appears separately, the read index is used instead of the read row, but not for the lookup AllFull table Scan Possible_keys: Indicates which index MySQL can use to find rows in the table.    If there is an index on the field involved in the query, the index will be listed but not necessarily used by the query. If the change is listed asNULLThat the query does not use the relevant indexes on the current table, considering whether it is necessary to add an indexKeyDisplays the index that MySQL actually uses in the query, and if the index is not used, it is displayed asNULLmay also existKeyA situation that is not equal to Possible_keys, that is, Possible_keys is not suitable for extracting the required rows and querying the selected columns for more efficient TIPS when using other indexes: If an overwrite index is used in the query, the index appears only in theKeyThe Key_len in the list represents the number of bytes used in the index, and the length of the index used in the query can be computed by using the column ref represents the connection matching criteria for the above table, that is, which columns or constants are used to find the value on the index columnrowsIndicates that MySQL estimates the number of rows needed to find the required records, based on table statistics and index selection for InnoDB, which is not necessarily accurate extra contains additional information that is not suitable for display in other columns but is important

Iii. examples of using explain EXTENDED

([email protected]) [sakila]> Explain extended select * from city where country_id in(select country_id from country where country= ' China ') and 1=1 \g************************** * 1. Row * **************************id:1Select_type:simpleTable:countryType:allPossible_keys:primaryKey:nullKey_len:nullRef:nullrows:109filtered:100.00extra:using where************************** * 2. Row * **************************id:1Select_type:simpletable:cityType:refPossible_keys:idx_Fk_country_Idkey:idx_fk_country_idKey_len:2ref:sakila.country.country_idrows:1filtered:100.00Extra:null2 rows in Set, 1 Warning (0.00 sec) ([email protected]) [sakila]> show Warnings\g************************** * 1. Row * **************************Level:note Code:1003message:/* select#1 */select' City '.' city_id 'As' city_id ',' City '.' City 'As' City ',' City '.' country_id 'As' country_id ',' City '.' Last_update 'As' Last_update 'From' Sakila '.' Country 'Join' Sakila '.' City 'Where (' City '.' country_id '=' Country '.' country_id ') and (' Country '.' Country '= ' China ') 1 rows in Set (0.00 sec)--from the above extended use can be seen, the query in the filtered column--second, the original SQL statement is actually executed at the time of the rewrite, that the original 1=1 condition is removed-- For complex SQL statements we can get a clearer, really executed SQL statement in this way

Iv. Examples of EXPLAIN partitions

([email protected]) [sakila]> CREATE TABLE' Actor_part '(' actor_id ' smallint (5) unsigned not NULL auto_increment,' first_name ' varchar () not NULL,' last_name ' varchar () not NULL,' last_update ' timestamp not NULL DEFAULT current_timestamp on update current_timestamp,PRIMARY KEY (' actor_id '),KEY ' idx_actor_last_name ' (' last_name ')) partition by hash (actor_id) partitions 4;Query OK, 0 rows affected (0.11 sec) ([email protected]) [sakila]> insert INTO actor_part select * from actor; Query OK, $ rows Affected (0.02 sec) records:200 duplicates:0 warnings:0([email protected]) [sakila]> Explain select * from actor_part where Actor_id=10; --When partitions is not used+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+-------+---------------+---------+---------+-------+------ +-------+| 1 | Simple | Actor_part | Const | PRIMARY | PRIMARY | 2 |    Const | 1 | NULL |+----+-------------+------------+-------+---------------+---------+---------+-------+------+------- +1 row in Set (0.00 sec) ([e-mail protected]) [sakila]> explain partitions select * from actor_part where Actor_id=10; --When using partitions+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+ -------+| 1 | Simple | Actor_part | P2 | Const | PRIMARY | PRIMARY | 2 |    Const | 1 | NULL |+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+- ------+1 row in Set (0.00 sec)-- Out of the Partitions column.

V. References:

MySQL reference manual 5.6其他个人博客           

Copyright NOTICE: This article for Bo Master original article, welcome to spread, spread please be sure to indicate the source.

MySQL EXPLAIN SQL Output Information description