MySql Learning Notes (6): scanning range

The type column of explain indicates the scanning range of this query. There are seven types of scans, and the effects are arranged from top to bottom:

System > const > eq_ref > ref > range > index > all.

Data preparation:

CREATE TABLE `tblog` (`id` int (11) NOT NULL auto_increment, `title` varchar (50) default NULL, `typeId` int (11) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `title_ index` (`title`), KEY `type_ index` (`typeId`) ENGINE=InnoDB DEFAULT CHARSET=utf8CREATE TABLE `ttype` (`id`int (11) NOT NULL auto_increment, `name` varchar (20) default NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 、 system

There is only one row in this table, which is rare in daily development and does not go into much detail.

2 、 const

Indicates that the result is found once through the index, which is used to scan the primary key and unique index, for example:

Mysql > explain select * from t_blog where id = 1 +-+ | id | select_type | table | type | possible_keys | key | key_len | ref | | rows | Extra | +-- + | 1 | SIMPLE | t_blog | const | PRIMARY | PRIMARY | 4 | | | const | 1 | | +-+ 1 row in set |

In the where clause, id is the primary key and the value is a constant, and there is only one piece of data corresponding to it in the id index.

3 、 eq_ref

With the primary key and unique index, only one piece of data matches, for example:

Mysql > explain select b.* from t_blog b left join t_type t on b.typeId = t.id +-+ | id | select_type | table | type | | possible_keys | key | key_len | ref | rows | Extra | +-+-- | -+-+ | 1 | SIMPLE | b | ALL | NULL | 7 | | 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | blog.b.typeId | 1 | Using index | +-+-- -+-+ 2 rows in set

Both eq_ref and const indicate that only a row of matching data is found under the action of a unique index or primary key. Const means read by primary key and unique index, while eq_ref is usually reflected in joined tables, which are read by primary key and unique index of joined tables.

4 、 ref

Non-unique index scan with multiple rows matching

Mysql > explain select * from t_blog where typeId = 4 +-+ | id | select_type | table | type | possible_keys | key | | key_len | ref | rows | Extra | +-- + | | 1 | SIMPLE | t_blog | ref | type_index | type_index | 5 | const | 1 | Using where | +-+ -- +-+ 1 row in set

TypeId is a common index of a table, that is, a non-unique index. The biggest difference from eq_ref is that ref represents a non-unique index scan.

5 、 range

Represents a range, selects rows using an index, and uses >

EXPLAIN select * from t_blog where id > 2 +-+ | id | select_type | table | type | possible_keys | key | key _ len | ref | rows | Extra | +-+ | 1 | SIMPLE | t _ Blog | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where | +-+ 1 row in set

6 、 index

Traverse the index tree and read the whole table

Mysql > EXPLAIN select id from t_blog +-+ | id | select_type | table | type | possible_keys | key | key _ len | ref | rows | Extra | +-+ | 1 | SIMPLE | t _ Blog | index | NULL | PRIMARY | 4 | NULL | 7 | Using index | +-+ 1 row in set

Only query id, so only traverse the index file, do not need to read from the hard disk, faster than all.

7 、 all

Read the whole table, do not use any index, read data from the hard disk, the slowest

Mysql > explain select * from t_blog +-- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | SIMPLE | t_blog | ALL | NULL | 7 | +- -+ 1 row in set

* in the general development process, you can reach ref.