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.

  1. explain select * from departments;
  2. -- 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的一种特殊案例。表中只有一行。