type
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.
explain select * from departments;
-- type ALL
const
主键或者unique key
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:
explain SELECT * FROM `departments` where department_id = 10;
system
The table has only one row (= system table). This is a special case of the const
join type.
const的一种特殊案例。表中只有一行。