【MYSQL】EXPLAIN执行计划之TYPE列

发表于 分类于 阅读次数： 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE TABLE user
(
id    INT PRIMARY KEY,
name  NVARCHAR(20),
hobby NVARCHAR(20),
INDEX (hobby)
) ENGINE = innodb;

INSERT INTO user
VALUES (1, 'tom', 'football');
INSERT INTO user
VALUES (2, 'dick', 'basketball');
INSERT INTO user
VALUES (3, 'lovin', 'volleyball');
INSERT INTO user
VALUES (4, 'wanda', 'badminton');
INSERT INTO user
VALUES (5, 'stuart', 'tennis');
CREATE TABLE user_money
(
uid   INT PRIMARY KEY,
money INT
) ENGINE = innodb;

INSERT INTO user_money
VALUES (1, 268);
INSERT INTO user_money
VALUES (2, 12846);
INSERT INTO user_money
VALUES (3, 4856);
INSERT INTO user_money
VALUES (4, 3489);
INSERT INTO user_money
VALUES (5, 489123);

所有英文来源于MYSQL官网

1
SET SESSION optimizer_switch ='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off,prefer_ordering_index=off';

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 * FROM tbl_name WHERE primary_key=1;

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

1
EXPLAIN SELECT * FROM user u WHERE u.id = 1;

eq_ref

One row is read from this table for each combination of rows from the previous 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;

1
2
EXPLAIN SELECT * FROM user u
LEFT JOIN user_money um ON u.id = um.uid WHERE u.id = um.uid;

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 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 a ref join to process ref_table:

SELECT * FROM ref_table WHERE key_column=expr;

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;

1
2
3
EXPLAIN SELECT * FROM user u
LEFT JOIN user_money um ON u.id = um.uid
WHERE u.hobby = '1';

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, LIKE, 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);

1
EXPLAIN SELECT * FROM user u WHERE u.id > 1;

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.

1
EXPLAIN SELECT u.hobby FROM user u;

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.

1
EXPLAIN SELECT u.* FROM user u;