mysql-explain

EXPLAIN Output Columns

Column	Meaning
id	The SELECT identifier
select_type	The SELECT type
table	The table for the output row
partitions	The matching partitions
type	The join type
possible_keys	The possible indexes to choose
key	The index actually chosen
key_len	The length of the chosen key
ref	The columns compared to the index
rows	Estimate of rows to be examined
filtered	Percentage of rows filtered by table condition
Extra	Additional information

 select_type

The type of SELECT, which can be any of those shown in the following table.

select_type Value	Meaning
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	Result of a UNION.
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DERIVED	Derived table SELECT (subquery in FROM clause)
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

type : join type

system:表只有一行/被查询的表是张衍生表且至多只有一行数据

The table has only one row (= system table). This is a special case of the  const  join type.

explain select * from (select * from ta where a=100) tt      -- a为主键

const:按主键查询或按唯一健查询，且查询条件为等于常量

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_namecan be used as a const table:

   

    

     
    
   
SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

SELECT * FROM tb1_name WHERE unique_key=1;

eq_ref (1.等值连接，被连接的表连接的字段为主键或非空唯一性索引)

One row is read from this table for each combination of rows from theprevious tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an eq_ref join to process ref_table:

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref（1.被连接的表只有几行满足要求2.被连接的字段是部分索引或唯一索引）

 （说白了说是被连接的表中只有几行满足连接要求）

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can use aref join to process ref_table:

explain select * from ta inner join tb on ta.a=tb.b1

CREATE TABLE `tb` (
  `a1` int(11) DEFAULT NULL,
  `b1` int(11) DEFAULT NULL,
  KEY `b1` (`b1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

explain select * from tb where b1=1

fulltext:被连接的的字段是全文索引字段

The join is performed using a FULLTEXT index.

ref_or_null（ref +null）

This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use a ref_or_null join to processref_table:

  

   

    
   
  
SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

range（索引范围查询）

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN()operators:

  

   

    
   
  
SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1= 10 AND key_part2 IN (10,20,30);

 index

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

• If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.

• A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

MySQL can use this join type when the query uses only columns that are part of a single index.

ALL

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

Extra

Impossible WHERE noticed after reading const tables

MySQL has read all const (and system) tables and notice that the WHERE clause is always false.

CREATE TABLE `liketest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `title` varchar(128) NOT NULL,
  `memo` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

explain select * from liketest where id=1; （主键查询，但是表中没有数据，所有where 条件永远不成立）