【MySQl】MyISAM和InnoDB索引對(duì)比

部分內(nèi)容轉(zhuǎn)自：http://www.2cto.com/database/201211/172380.html

比較好的文章：http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

MyISAM

MyISAM引擎使用B+Tree作為索引結(jié)構(gòu)，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。下圖是MyISAM索引的原理圖：

這里設(shè)表一共有三列，假設(shè)我們以Col1為主鍵，則上圖是一個(gè)MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件僅僅保存數(shù)據(jù)記錄的地址。在MyISAM中，主索引和輔助索引（Secondary key）在結(jié)構(gòu)上沒有任何區(qū)別，只是主索引要求key是唯一的，而輔助索引的key可以重復(fù)。如果我們?cè)贑ol2上建立一個(gè)輔助索引，則此索引的結(jié)構(gòu)如下圖所示：

?

同樣也是一顆B+Tree，data域保存數(shù)據(jù)記錄的地址。因此，MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，讀取相應(yīng)數(shù)據(jù)記錄。 MyISAM的索引方式也叫做“非聚集”的，之所以這么稱呼是為了與InnoDB的聚集索引區(qū)分。
InnoDB 雖然InnoDB也使用B+Tree作為索引結(jié)構(gòu)，但具體實(shí)現(xiàn)方式卻與MyISAM截然不同。 第一個(gè)重大區(qū)別是InnoDB的數(shù)據(jù)文件本身就是索引文件。從上文知道，MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址。而在InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，這棵樹的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄。這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。 上圖是InnoDB主索引（同時(shí)也是數(shù)據(jù)文件）的示意圖，可以看到葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄。這種索引叫做聚集索引。因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵，如果不存在這種列，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱含字段作為主鍵，這個(gè)字段長度為6個(gè)字節(jié)，類型為長整形。

第二個(gè)與MyISAM索引的不同是InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如，下圖為定義在Col3上的一個(gè)輔助索引： 這里以英文字符的ASCII碼作為比較準(zhǔn)則。聚集索引這種實(shí)現(xiàn)方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。

了解不同存儲(chǔ)引擎的索引實(shí)現(xiàn)方式對(duì)于正確使用和優(yōu)化索引都非常有幫助，例如知道了InnoDB的索引實(shí)現(xiàn)后，就很容易明白為什么不建議使用過長的字段作為主鍵，因?yàn)樗休o助索引都引用主索引，過長的主索引會(huì)令輔助索引變得過大。再例如，用非單調(diào)的字段作為主鍵在InnoDB中不是個(gè)好主意，因?yàn)镮nnoDB數(shù)據(jù)文件本身是一顆B+Tree，非單調(diào)的主鍵會(huì)造成在插入新記錄時(shí)數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調(diào)整，十分低效，而使用自增字段作為主鍵則是一個(gè)很好的選擇。 聯(lián)合索引或者叫做復(fù)合索引、組合索引，在《MySQL技術(shù)內(nèi)幕InnoDB存儲(chǔ)引擎》的‘5.6.4聯(lián)合索引’章節(jié)有一些描述（第二版內(nèi)容無更新），是指對(duì)表上的多個(gè)列做索引，聯(lián)合索引也是一顆B+樹，聯(lián)合索引的鍵值的數(shù)量不是1，而是大于等于2，show index如下： mysql> show create table t2; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` (`column_a` int(11) DEFAULT NULL,`column_b` int(11) DEFAULT NULL,`column_c` int(11) DEFAULT NULL,`column_d` varchar(10) DEFAULT NULL,KEY `index_a_b_c` (`column_a`,`column_b`,`column_c`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)mysql> show index from t2 \G *************************** 1. row ***************************Table: t2Non_unique: 1Key_name: index_a_b_cSeq_in_index: 1Column_name: column_aCollation: ACardinality: 9Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: Index_comment: *************************** 2. row ***************************Table: t2Non_unique: 1Key_name: index_a_b_cSeq_in_index: 2Column_name: column_bCollation: ACardinality: 9Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: Index_comment: *************************** 3. row ***************************Table: t2Non_unique: 1Key_name: index_a_b_cSeq_in_index: 3Column_name: column_cCollation: ACardinality: 9Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: Index_comment: 3 rows in set (0.00 sec)mysql> select count(*) from t2; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.01 sec)

show index語法的說明：

1、Table：索引所在的表名。 2、Non_unique：非唯一的索引，可以看到primary key是0，因?yàn)楸仨毷俏ㄒ坏摹?3、Key_name：索引的名稱，我們可以通過這個(gè)名稱來drop index。 4、Seq_in_index：索引中該列的位置，如果看聯(lián)合索引就比較直觀。（例如上面例子） 5、Column_name：索引的列。 6、Collation：列以什么方式存儲(chǔ)在索引中。可以是‘A’或者NULL。B+樹索引總是A，即排序的。如果使用了Heap存儲(chǔ)索引，并且建立了Hash索引，這里就會(huì)顯示NULL。因?yàn)镠ash根據(jù)Hash桶來存放索引數(shù)據(jù)，而不是對(duì)數(shù)據(jù)進(jìn)行排序。 7、Cardinality：非常關(guān)鍵的值，表示索引中唯一值得數(shù)目的估計(jì)值，優(yōu)化器會(huì)根據(jù)這個(gè)值來判斷查詢是否使用這個(gè)索引。Cardinality/表的行數(shù)的比值應(yīng)盡可能接近1，如果非常小，那么需要考慮是否還需要建這個(gè)索引。這個(gè)值不是實(shí)時(shí)更新的，因?yàn)殚_銷會(huì)很大，可以通過運(yùn)行ANALYZE TABLE或myisamchk -a可以更新這個(gè)值。 8、Sub_part：是否是列的部分被索引。假設(shè)如果看index_a這個(gè)索引，這里顯示10，表示只索引a列的前10個(gè)字符。如果索引整個(gè)列，則該字段為NULL。 9、Packed：關(guān)鍵字如何被壓縮。如果沒有被壓縮，則為NULL。 10、Null：是否索引的列含有NULL值。可以看到index_a_b_c這里為YES，因?yàn)槲覀兌x的a、b、c列允許NULL值。 11、Index_type：索引的類型。InnoDB存儲(chǔ)引擎只支持B+樹索引，所以這里顯示的都是BTREE。 12、Comment：注釋。

聯(lián)合索引形象的說明可以比喻成手機(jī)中的電話薄，因?yàn)槁?lián)合索引是多個(gè)鍵值的B+樹情況，和單列索引的鍵值順序排序相同，使用聯(lián)合索引也是通過葉節(jié)點(diǎn)邏輯上的順序地讀出所有數(shù)據(jù)，比如表中column1和column2要建一個(gè)index_1_2，那這個(gè)聯(lián)合索引會(huì)先按照column1順序存放，在column1值相同的數(shù)據(jù)，再使用coiumn2順序存放。例如：(1,1),(1,2),(2,1),(2,4),(3,1),(3,2)，按照（column1，column2）的順序進(jìn)行存放：

聯(lián)合索引也可以認(rèn)為是多列組成的輔助索引，同樣引用主鍵作為data域。 另外聯(lián)合索引的使用同樣需要掌握好規(guī)律，如最開始的index_a_b_c這個(gè)組合索引，真正能夠使用到索引的是： mysql> explain select * from t2 where column_a=1 and column_b=2 and column_c=3; +----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------------+ | 1 | SIMPLE | t2 | ref | index_a_b_c | index_a_b_c | 15 | const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec)mysql> explain select * from t2 where column_a=1 and column_b=2; +----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+ | 1 | SIMPLE | t2 | ref | index_a_b_c | index_a_b_c | 10 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)mysql> explain select * from t2 where column_a=1; +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+ | 1 | SIMPLE | t2 | ref | index_a_b_c | index_a_b_c | 5 | const | 1 | Using where | +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)

另外針對(duì)column_a和column_c兩列的查詢，實(shí)際上只是用到了組合索引中的column_a部分：

mysql> explain select * from t2 where column_a=1 and column_c=3; +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+ | 1 | SIMPLE | t2 | ref | index_a_b_c | index_a_b_c | 5 | const | 2 | Using where | +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)

可以看到key_len是5，查詢通過索引index_a_b_c的column_a中找到相應(yīng)column_a=1行的葉子節(jié)點(diǎn)邏輯位置區(qū)域，但是由于條件column_c無法被使用到索引，遍歷了column_a=1的所有行。

所以大家習(xí)慣說的聯(lián)合索引的"最左前綴"的原則，簡單的理解就是只從聯(lián)合索引組合的最左側(cè)的列開始的組合順序組合。

理解了聯(lián)合索引機(jī)制，什么用不到就更好理解：

mysql> explain select * from t2 where column_b=2 and column_c=3; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 9 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)mysql> explain select * from t2 where column_b=2; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 9 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)mysql> explain select * from t2 where column_c=3; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 9 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)

?

posted on 2014-01-14 12:08 poorX 閱讀(...) 評(píng)論(...) 編輯 收藏

轉(zhuǎn)載于:https://www.cnblogs.com/jiangxu67/p/3518752.html

總結(jié)

以上是生活随笔為你收集整理的【MySQl】MyISAM和InnoDB索引对比的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。