mysql explain官方解释
来源:互联网 发布:棘轮设计软件 编辑:程序博客网 时间:2024/06/06 06:52
EXPLAIN Join Types
The type
column of EXPLAIN
output describes how tables are joined. In JSON-formatted output, these are found as values of the access_type
property. The following list describes the join types, ordered from the best type to the worst:
system
The table has only one row (= system table). This is a special case of the
const
join type.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 aPRIMARY KEY
orUNIQUE
index to constant values. In the following queries,tbl_name
can be used as aconst
table:SELECT * FROM
tbl_name
WHEREprimary_key
=1;SELECT * FROMtbl_name
WHEREprimary_key_part1
=1 ANDprimary_key_part2
=2;eq_ref
One row is read from this table for each combination of rows from the previous tables. Other than the
system
andconst
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 aPRIMARY KEY
orUNIQUE 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 aneq_ref
join to processref_table
:SELECT * FROM
ref_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
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 aPRIMARY KEY
orUNIQUE
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 processref_table
:SELECT * FROM
ref_table
WHEREkey_column
=expr
;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;fulltext
The join is performed using a
FULLTEXT
index.ref_or_null
This join type is like
ref
, but with the addition that MySQL does an extra search for rows that containNULL
values. This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use aref_or_null
join to processref_table
:SELECT * FROM
ref_table
WHEREkey_column
=expr
ORkey_column
IS NULL;See Section 8.2.1.8, “IS NULL Optimization”.
index_merge
This join type indicates that the Index Merge optimization is used. In this case, the
key
column in the output row contains a list of indexes used, andkey_len
contains a list of the longest key parts for the indexes used. For more information, see Section 8.2.1.4, “Index Merge Optimization”.unique_subquery
This type replaces
ref
for someIN
subqueries of the following form:value
IN (SELECTprimary_key
FROMsingle_table
WHEREsome_expr
)unique_subquery
is just an index lookup function that replaces the subquery completely for better efficiency.index_subquery
This join type is similar to
unique_subquery
. It replacesIN
subqueries, but it works for nonunique indexes in subqueries of the following form:value
IN (SELECTkey_column
FROMsingle_table
WHEREsome_expr
)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. Thekey_len
contains the longest key part that was used. Theref
column isNULL
for this type.range
can be used when a key column is compared to a constant using any of the=
,<>
,>
,>=
,<
,<=
,IS NULL
,<=>
,BETWEEN
, orIN()
operators:SELECT * FROM
tbl_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 asALL
, 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 avoidALL
by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.
- mysql explain官方解释
- mysql explain语法解释
- MySQL Explain 详细解释
- mysql explain的解释
- mysql explain详细解释
- mysql explain 解释
- mysql explain 结果解释
- MySQL中EXPLAIN解释命令
- 详解MySQL中EXPLAIN解释
- mysql explain 的type解释
- MySQL中EXPLAIN的解释
- mysql explain 的type解释
- MySQL中EXPLAIN解释命
- mysql explain 的type解释
- mysql的explain解释使用方法
- mysql explain 的type解释
- mysql查询 EXPLAIN 简单解释
- mysql中explain的详细解释
- 类类型自动转换
- Linux Top 命令详解及使用技巧
- RocketMQ的安装与使用
- KMP算法(一)
- Python系列之 - Collections系列
- mysql explain官方解释
- session超时时间设设置方法三种
- laravel中遇到的问题
- 判断浏览器类型,不判断版本
- centos 大家samba
- 在VMware中为Linux系统安装VM-Tools的详解教程
- XPath
- linux 线程--内核线程、用户线程实现方法
- 服务器性能测试:JMeter对Oracle数据库性能测试方法