MyISAM和InnoDB中索引使用的區別
兩個小型表 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=utf8
CREATE 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")。
