mysql explain 中type的歸納

標籤：mysql   explain   

  為了更好的理解連線類型（type），將根據查詢條件的不同對連線類型進行簡單歸納。

  表定義如下：

1.id為主鍵

mysql> show create table key_id;+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                            |+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------+| key_id | CREATE TABLE `key_id` ( `id` char(128) NOT NULL DEFAULT ‘‘, `data` char(128) 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(11) NOT NULL DEFAULT ‘0‘, `data` char(128) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+

3.id為普通索引

mysql> show create table index_id;+----------+------------------------------------------------------------------------------------------------------------------------------------------------+| Table   | Create Table                                                                                                                                  |+----------+------------------------------------------------------------------------------------------------------------------------------------------------+| index_id | CREATE TABLE `index_id` ( `id` int(11) DEFAULT NULL, `data` char(128) DEFAULT NULL,  KEY`id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------+

一、等值查詢：

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 |+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+

可以看到當unique和primary key索引被作為等值條件時觸發。const非常快，在執行最佳化解析時值已經被算出來了。

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  |+----+-------------+----------+------+---------------+------+---------+-------+------+-------+

普通索引被作為等值條件時觸發。

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 |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

當條件列沒有任何索引的情況下觸發。此時為全表搜尋

二、範圍查詢

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 |+----+-------------+--------+-------+---------------+---------+---------+------+------+----------+-------------+

可以看到當unique和primary key索引被作為範圍查詢條件時觸發。而普通索引（根據經驗）讀取的資料不能超過表中資料的20%才觸發，不然將會是全表搜尋。

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 |+----+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+

因為innodb通過索引來尋找資料時，首先會遍曆輔助索引，然後根據輔助索引分葉節點的指標獲得主鍵索引的主鍵，最後再根據主鍵索引得到完整的行資料。並且輔助索引與主鍵索引不在同一個資料區段，所以普通索引的訪問是隨機讀的過程，當需要的資料量大時，通過普通索引搜尋資料沒有全表搜尋這種順序讀來得快。

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 |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

 

可以看出當條件列沒有任何索引以及普通索引列擷取資料的行數大於表中資料20%的情況下觸發全表搜尋。

三、不帶條件

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 |+----+-------------+--------+-------+---------------+---------+---------+------+------+----------+-------------+

可以看到當查詢的列為索引列將觸發index。此時只需要對索引頁進行遍曆。

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  |+----+-------------+--------+------+---------------+------+---------+------+------+----------+-------+

當查詢欄位有未加非索引的情況則需要遍曆整張表。

本文出自 “業精於勤而荒於嬉” 部落格，轉載請與作者聯絡！

mysql explain 中type的歸納