MyISAM 和 InnoDB 中索引使用的區別，myisaminnodb

MyISAM 和 InnoDB 中索引使用的區別，myisaminnodb
兩個小型表 item、category：

CREATE TABLE `item` (  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(150) NOT NULL,  `category_id` mediumint(8) unsigned NOT NULL,  PRIMARY KEY (`id`),  KEY `name` (`name`),  KEY `category_id` (`category_id`)) CHARSET=utf8CREATE TABLE `category` (  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(150) NOT NULL,  PRIMARY KEY (`id`),  KEY `name` (`name`)) CHARSET=utf8

category 插入 100 條資料，item 插入 1000 條。
當表的儲存引擎為 InnoDB 執行

EXPLAIN SELECT item.id,category.name AS category_name FROM item JOIN category ON item.category_id=category.id;

結果：

+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+| id | select_type | table    | type  | possible_keys | key         | key_len | ref                | rows | Extra       |+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+|  1 | SIMPLE      | category | index | PRIMARY       | name        | 452     | NULL               |  103 | Using index ||  1 | SIMPLE      | item     | ref   | category_id   | category_id | 3       | dbname.category.id |    5 | Using index |+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+

然後將表的儲存引擎切換到 MyISAM 時(使用 alter table engine=myisam)還是執行

EXPLAIN SELECT item.id,category.name AS category_name FROM item JOIN category ON item.category_id=category.id;

結果：

+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+| id | select_type | table    | type   | possible_keys | key     | key_len | ref                     | rows | Extra |+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+|  1 | SIMPLE      | item     | ALL    | category_id   | NULL    | NULL    | NULL                    | 1003 |       ||  1 | SIMPLE      | category | eq_ref | PRIMARY       | PRIMARY | 3       | dbname.item.category_id |    1 |       |+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+

MyISAM 的 item 使用的是全表掃描。同樣的資料結果、同樣的資料、同樣的索引(MyISAM 和 InnoDB 的索引預設都是 B-TREE)，為什麼差別就這麼大呢？
來自 SchoolMessenger 的進階資料庫結構描述師 Bill Karwin 對此做出如下解釋：
在 InnoDB 中，所有二級索引內部包含表的主鍵列。因此這兩張表的 name 列的索引(name)隱式地持有兩個列：一個本欄位 name 和一個主鍵 id。
這意味著 EXPLAIN 對於 category 表的訪問的解釋為一個 "index-scan"(type 為 "index" 印證了這個)。通過對索引的掃描，它也可以訪問到 id 列，藉此尋找第二張表 item 的相關記錄。
同理，對於 item 表的 category_id 欄位上的索引實際是 category_id、id，所以只需要簡單讀取該索引即可拿到 item.id，完全不需要去讀取該表(Extra 值為 "Using index" 印證了這個說法)。
MyISAM 並不像 InnoDB 那樣在二級索引中儲存主鍵，因此它也就不能得到同樣的最佳化。對於 category 表的訪問 type 是 "ALL" 也就意味著將要進行一次全表掃描。
我期望對於 MyISAM 的 item 表的訪問是 "ref"，因為它使用 category_id 列的索引來尋找行。但當表中資料量比較少或者你在建立該索引後還沒完成 ANALYZE TABLE item 時最佳化器可能會給出扭曲的結果。
Bill Karwin 追加回複：
看上去相比較表掃描最佳化器更喜歡一個索引掃描，因此它在 InnoDB 裡做了一次索引掃描，並把 category 表放在前面。最佳化器放棄了我們在查詢中給它的表的順序，它對這些表進行了重新排序。
在 MyISAM 引擎下的兩個表裡，不管最佳化器選擇先訪問誰都要做一次表掃描，但是通過把 category 表放在第二步裡，它放棄了 item 表的二級索引，串連的是 category 表的主鍵索引。最佳化器更傾向於尋找一個 unique 或者 primary 的索引(type "eq_ref")。
原文連結：http://stackoverflow.com/questions/16024226/mysql-difference-in-index-usage-between-myisam-and-innodb。