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

來源:互聯網
上載者:User

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。

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.