The induction of type in MySQL explain

to better understand the connection type (type), the connection type is simply summed up according to the different query criteria.

The table is defined as follows:

1.id PRIMARY Key

mysql> show create table key_id;+--------+--------------------------------------------- -------------------------------------------------------------------------------------------------------------+|  Table | Create Table                                                                                                                                                   |+------ --+------------------------------------------------------------------------------------------------------------ ----------------------------------------------+| key_id | create table  ' key_id '   (  ' id '  char (+)  NOT NULL DEFAULT  ',  ' data '  char (+)  default  NULL, PRIMARY KEY  (' id '))  engine=innodb default charset=utf8 |+------ --+------------------------------------------------------------------------------------------------------------ ----------------------------------------------+

2.id Unique

mysql> show create table key_id;+--------+--------------------------------------------- ------------------------------------------------------------------------------------------------------------+|  table | create table                                                                                                                                                  |+--------+---- --------------------------------------------------------------------------------------------------------------- --------------------------------------+| key_id | create table  ' key_id '   (  ' Id '  int (one)  NOT NULL DEFAULT  ' 0 ',  ' data '  char ( default null),  PRIMARY KEY  (' id '))  engine=innodb default charset=utf8 |+--------+-------- --------------------------------------------------------------------------------------------------------------- ----------------------------------+

3.id as normal index

mysql> show create table index_id;+----------+----------------------------------------- -------------------------------------------------------------------------------------------------------+|  table   | create table                                                                                                                                         |+----------+------------------------------------------------- -----------------------------------------------------------------------------------------------+| index_ id | create table  ' index_id '   (  ' id '  int (one)  DEFAULT NULL,  ' Data '  char (+)  default null,  key ' id '   (' id '))  engine=innodb default  charset=utf8 |+----------+------------------------------------------------------------------------- -----------------------------------------------------------------------+

First, the equivalent query:

1.const

Mysql> explain extended select * fromkey_id where id= ' 8 '; +----+--------- ----+--------+-------+---------------+---------+---------+-------+------+----------+-------+| id |  select_type | table  | type | possible_keys | key      |key_len | ref   | rows | filtered |  extra|+----+-------------+--------+-------+---------------+---------+---------+-------+------+---------- +-------+|  1| simple      | key_id | const | primary       | primary | 384     |  const |    1 |  100.00 | null  |+----+------ -------+--------+-------+---------------+---------+---------+-------+------+----------+-------+

Mysql> explain select id from key_id where id=4;+----+-------------+--------+-------+---------------+---------+--- ------+-------+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key |key_len | Ref | Rows |  Extra |+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+| 1| Simple | key_id | Const | PRIMARY | PRIMARY | 4 |    Const | 1 | Using index |+----+-------------+--------+-------+---------------+---------+---------+-------+------+----------- --+

can see when u the Nique and primary key indexes are used as trigger when the condition is equal. The const is very fast, and the execution of the optimized parse value has been calculated.

2. Ref

mysql> explain select * fromindex_id   where id=2;  +----+-------------+----------+------+---------------+------+---------+-------+--- ---+-------+| id | select_type | table    | type |  possible_keys | key  | key_len | ref   | rows  | extra |+----+-------------+----------+------+---------------+------+---------+-------+------ +-------+|  1 | simple      | index_id | ref  | id             | id   | 5       | const |    1 | null  |+----+------ -------+----------+------+---------------+------+---------+-------+------+-------+

The normal index is triggered when the equivalent condition is used.

3.all

mysql>  explain select * from index_id   where data= ' a ', +----+-------------+----------+------+---------------+------+---------+------+ ------+-------------+| id| select_type | table    | type | possible_keys | key  | key_len | ref  | rows |  extra       |+----+-------------+----------+------+---------------+------+- --------+------+------+-------------+|  1 | simple      |  index_id | ALL  | NULL           | null | null    | null |  10 | using  where |+----+-------------+----------+------+---------------+------+---------+------+------+------- ------+

Triggered when the condition column does not have any indexes. Search for full table at this time

Second, the scope of inquiry

1.range

 mysql>  explain extended select * from key_id whereid>8;   +----+-------------+--------+-------+---------------+---------+---------+------+------+----------+ -------------+| id | select_type | table  | type | possible_ keys | key     |key_len | ref  | rows |  filtered | extra       |+----+-------------+--------+-------+---- -----------+---------+---------+------+------+----------+-------------+|  1| simple       | key_id | range | primary       | primary | 4        | null |    2 |  100.00 | using where  |+----+-------------+--------+-------+---------------+---------+---------+------+------+----------+-------------+ 

you can see that the unique and primary key indexes are triggered when the index is used as a range query condition. The average index (based on experience) cannot be triggered by reading more than 20% of the data in the table, or it will be a full table search .

mysql> explain select *  from index_id where id >9;+----+-- -----------+----------+-------+---------------+------+---------+------+------+-----------------------+|  Id | select_type | table    | type | possible_keys  | key  | key_len |ref  | rows | Extra                  |+----+------------- +----------+-------+---------------+------+---------+------+------+-----------------------+|  1|  SIMPLE      | index_id | range |id             | id   | 5       | null |    1 | using indexcondition |+--- -+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+ 

Because InnoDB is looking for data by index, the secondary index is traversed first, then the primary key index is obtained based on the pointer of the secondary Index leaf node, and then the complete row data is obtained based on the primary key index. And the secondary index is not the same data segment as the primary key index, so access to the normal index is a random read process, when the required amount of data is large, the search data through the normal index is not a full table search this order to read faster.

2.all

mysql> explain select * from index_id  Where data> ' F ', +----+-------------+----------+------+---------------+------+---------+------+------+-- -----------+| id | select_type | table    | type |  possible_keys | key  | key_len | ref  | rows |  extra       |+----+-------------+----------+------+---------------+-- ----+---------+------+------+-------------+|  1| simple      |  index_id | ALL  | NULL           | null | null    | null |  10 | using  where |+----+-------------+----------+------+---------------+------+---------+------+------+------- ------+

mysql> explain select * fromindex_id   where id>2;+----+-------------+----------+------+---------------+------+---------+------+------+------ -------+| id | select_type | table    | type |  possible_keys | key  | key_len | ref  | rows |  extra       |+----+-------------+----------+------+---------------+------+- --------+------+------+-------------+|  1| simple      |  index_id | all  | id             | null | null    | null |    6 | using where |+----+-------------+----------+------+ ---------------+------+---------+------+------+-------------+

You can see that a full table search is triggered when the condition column does not have any indexes and the normal indexed column gets more rows than the data in the table 20%.

Three, without conditions

1.index

mysql> explain extended select id fromkey_id;+----+-------------+--------+-------+ ---------------+---------+---------+------+------+----------+-------------+| id | select_type  | table  | type | possible_keys | key      |key_len | ref  | rows | filtered | extra       |+----+-------------+--------+-------+---------------+--------- +---------+------+------+----------+-------------+|  1| simple       | key_id | index | null          | primary | 384      | NULL |   4 |   100.00 | Using  index |+----+-------------+--------+-------+---------------+---------+---------+------+------+----------+ -------------+ 

You can see that when the column of the query is indexed, index is triggered. Only the index pages need to be traversed at this time.

2.all

mysql> explain extended select * fromkey_id; +----+-------------+--------+------+---------------+------+---------+------+------+----------+-------+|  Id | select_type | table  | type | possible_keys | key   | key_len | ref  | rows | filtered | Extra  |+----+-------------+--------+------+---------------+------+---------+------+------+----------+-------+|   1| simple      | key_id | all  | null           | NULL | NULL     | null |   5 |   100.00 | null  |+----+ -------------+--------+------+---------------+------+---------+------+------+----------+-------+

When the query field is non-indexed, the entire table needs to be traversed.

This article from "diligence and desolate in the hippie" blog, reprint please contact the author!

The induction of type in MySQL explain