Type column of MySQL explain

Explain can analyze the execution of the select statement, that is, the "Execution Plan" of MySQL.

Type Column

MySQL finds the required rows in the table. Including (from left to right, from worst to best): | All | index | range | ref | eq_ref | const, system | null |

ALL full table scan. MySQL scans the entire table from start to end to find rows. Mysql> explain select * from a \ G...
Type: ALL

If limit is added, for example, select * from a limit 100 MySQL scans 100 rows, but the scan method remains unchanged.

The index scans the table in the order of indexes, that is, the index is read first and then the actual row is read. In fact, the whole table is scanned. The main advantage is that sorting is avoided because the index is sorted properly. (Read the corresponding data rows by sorting the indexes .) Create table a (a_id int not null, key (a_id); insert into a value (1), (2); mysql> explain select * from a \ G
...
Type: index

Range scans the index to create a table in a range: create table a (a_id int not null, key (a_id); insert into a values (1), (2 ), (3), (4), (5), (6), (7), (8), (9), (10 ); mysql> explain select * from a where a_id> 1 \ G
...
Type: range...

The IN comparison operator is also represented by range: mysql> explain select * from a where a_id in (, 4) \ G
...
Type: range
...

'Ref non-unique index Access table creation: create table a (a_id int not null, key (a_id); insert into a values (1), (2), (3 ), (4), (5), (6), (7), (8), (9), (10 ); mysql> explain select * from a where a_id = 1 \ G
...
Type: ref...

Eq_ref create a table and insert data using a unique index (primary key or unique index): create table a (id int primary key); create table a_info (id int primary key, title char (1); insert into a value (1), (2); insert into a_info value (1, 'A'), (2, 'B '); mysql> explain select * from a join a_info using (id );
... + -------- +...
... | Table | type |...
... + -------- +...
... | A | index |...
... | A_info | eq_ref |...

... + -------- +... At this time, every a_info record corresponds to a one-to-one, and is associated with a primary key id. Therefore, the type of a_info is eq_ref. Delete the primary key of a_info: alter table 'A _ info' drop primary key; now a_info has no index: mysql> explain select * from a join a_info using (id );
+ ---- +... + -------- +... | Id |... | table | type |... + ---- +... + -------- +... | 1 |... | a_info | ALL |... | 1 |... | a | eq_ref |... + ---- +... + -------- +... this time, MySQL adjusted the execution order. First, the entire table scans the_info table and then queries Table a for eq_ref, because table a's id is still the primary key. Delete the primary key of a: alter table a drop primary key; now a has no index: mysql> explain select * from a join a_info using (id );
... + -------- + ------ +...
... | Table | type |...
... + -------- + ------ +...
... | A | ALL |...
... | A_info | ALL |...

... + -------- + ------ +... Now both tables use full table scan.

Create table a (id int primary key); create table a_info (id int, title char (1), key (id )); insert into a value (1), (2); insert into a_info value (1, 'A'), (2, 'B '); now the id column of the_info table is changed to a normal index (non-unique index): mysql> explain select * from a join a_info using (id) where. id = 1;
... + -------- + ------- +...
... | Table | type |...
... + -------- + ------- +...
... | A | const |...
... | A_info | ref |...

The type of the... + -------- + ------- +... a_info table is changed to the ref type. Therefore, only eq_ref appears for a unique index (a ref is displayed for a non-unique index). Because it is unique, only one record is returned at most. You do not need to continue searching after finding the record, so it is faster than ref.

Const is called a "constant", which is hard to understand, but const indicates the following two situations: during the entire Query Process, the table can have at most one matching row, for example, if the primary key id is 1, there must be only one row. You only need to read the table data once to obtain the required results, and the table data is read during the execution plan decomposition. The return value is directly placed in the select statement, similar to select 1 AS f. You can select extended to view the internal process:
Create table a (id int primary key, c1 char (20) not null, c2 text not null, c3 text not null ); insert into a values (1, 'asdfasdf '), (2, 'asdfasdf '); mysql> explain extended select * from a where id = 1 \ G
...
Type: const
Possible_keys: PRIMARY
Key: PRIMARY

... Use show warnings to view how MySQL is optimized: mysql> show warnings \ G
...

Message: select '1' AS 'id', 'asdfasdf 'AS 'c1', 'asdfasdf' AS 'c2 ', 'asdfasdf'
'C3' from 'test'. 'A' where 1: mysql> select * from a where id = 1;
+ ---- + ---------- +
| Id | c1 | c2 | c3 |
+ ---- + ---------- +
| 1 | asdfasdf |

+ ---- + ---------- + You can see that the Field Values in the returned results are displayed in the optimized select statement in the form of "value AS field name. Modify the query: mysql> explain select * from a where id in (1, 2) \ G
...

Type: range... when more than one result is returned, the type is no longer const. Create table a (id int not null); insert into a value (1), (2), (3 ); mysql> explain select * from a where id = 1 \ G
...

Type: currently, the ALL table has only one record with id = 1, but the type is ALL. Only the unique index can ensure that the table has only one record at most. Only in this way can the type be const. After adding a common index for the id, the type changes to ref. After adding a unique or primary key index, the type changes to const.