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:表只有一行/被查询的表是张衍生表且至多只有一行数据
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_name
can be used as a const
table:
SELECT * FROMtbl_name
WHEREprimary_key
=1; SELECT * FROMtbl_name
WHEREprimary_key_part1
=1 ANDprimary_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 * FROMref_table
,other_table
WHEREref_table
.key_column
=other_table
.column
; SELECT * FROMref_table
,other_table
WHEREref_table
.key_column_part1
=other_table
.column
ANDref_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 * FROMref_table
WHEREkey_column
=expr
ORkey_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 * FROMtbl_name
WHEREkey_column
= 10; SELECT * FROMtbl_name
WHEREkey_column
BETWEEN 10 and 20; SELECT * FROMtbl_name
WHEREkey_column
IN (10,20,30); SELECT * FROMtbl_name
WHEREkey_part1
= 10 ANDkey_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 saysUsing index
. An index-only scan usually is faster thanALL
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 theExtra
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 条件永远不成立)