Mysql innodb引擎和myisam引擎使用索引區別

markzy5201190發表於2012-11-29
MySql索引
首先你要理解,innodb的主鍵索引都是聚簇索引,它的輔助索引裡面都包含有表的主鍵,
而且主鍵都在輔助索引的最後一列.而myisam的索引,都是普通索引,儲存的是列的值,
還有列在原表中的地址值---不儲存主鍵值.

好了,下面我們開始:
先建立兩個表:一個是innodb引擎的,一個是myisam引擎的
CREATE TABLE `innodb` ( `id` int(10) UNSIGNED NOT NULL, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `myisam` (`id` int(10) UNSIGNED NOT NULL, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`)) ENGINE=myisam DEFAULT CHARSET=latin1;
mysql> EXPLAIN SELECT * FROM innodb ORDER BY id /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: pktest
type: INDEX------ innodb是聚簇索引,所以mysql直接掃描primary key並取出所有的欄位.需要用到primary key,此處直接掃描索引,而不需要查詢表,比ALL更快
      possible_keys: NULL
KEY: PRIMARY ----由此可見,key的內容並不一定在possible keys中 
key_len: 4
ref: NULL
rows: 6
Extra:---這裡為何不顯示為using index呢? 
1 row IN SET (0.00 sec)
mysql> EXPLAIN SELECT * FROM myisam ORDER BY id /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: myisam
type: ALL----- myisam是普通的索引,只是在索引中儲存了行的rowid,此索引的id列應該是有序的.但是通過id列去定位到原表的rowid,然後由rowid查詢表的所有欄位,會帶來大量的隨機讀.此時優化器選擇了直接讀取原表排序的方法,沒有利用任何索引.
      possible_keys: NULL
KEY: NULL
key_len: NULL
ref: NULL
rows: 6
Extra: USING filesort---- 有filesort出現,全表掃描以後,再排序
      1 row IN SET (0.00 sec)
mysql> EXPLAIN SELECT id FROM innodb WHERE a=3 /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: innodb
type: ref
possible_keys: a
KEY: a
key_len: 5
ref: const
rows: 1
Extra: USING WHERE; USING INDEX---- 因為在這個表上有索引a,它包含了a列和id列,而且a列在前,id列在後,所以優化器只需要直接查詢該索引即可.所以會出現using index,因為直接用到了索引a,與原表無關
1 row IN SET (0.00 sec)
mysql> EXPLAIN SELECT id FROM myisam WHERE a=3 /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: myisam
type: ref
possible_keys: a
KEY: a
key_len: 5
ref: const
rows: 1
Extra: USING WHERE---- 對myisam引擎,它依然用到了索引a,但是隻是通過索引a去查詢到行在原表中的rowid,然後根據rowid得出id列的值.所以這裡沒有useing index.歸根結底是因為myisam的索引不會儲存主鍵,所以不得不進行二次掃描
1 row IN SET (0.00 sec)
mysql> EXPLAIN SELECT id FROM innodb WHERE a=3 ORDER BY id /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: innodb
type: ref
possible_keys: a
KEY: a
key_len: 5
ref: const
rows: 1
Extra: USING WHERE; USING INDEX; USING filesort---- 這 裡,同樣,在索引a中,含有(a,id)兩列,只需要查詢索引就ok了.
根據a=3得到需要的行,然後按照id排序.所以此處不應該出現 filesort,因為對於a,id列的排序,
直接再查詢一次primary key即可得出.所以這裡的key列處,應該是key: a, primary. 這樣效率會更高.
據說這是mysql 優化器的一個bug.以後會修復(在5.1.48已經沒有這種情況).

轉:http://blog.csdn.net/jiedushi/article/details/6528441

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26855487/viewspace-750280/，如需轉載，請註明出處，否則將追究法律責任。

相關文章