什么是索引：####
要想深入的了解索引啼器，需要先知道什么是索引，在我剛接觸數(shù)據(jù)庫的時(shí)候甲献，只是知道什么是CRUD，對于索引一無所知颂翼。CRUD是一個(gè)DB的基本功能晃洒，而索引則是讓我們的CRUD更快的工具或者方法，當(dāng)然主要是查詢朦乏。舉一個(gè)栗子：
在我們很小的時(shí)候我們都用過新華字典球及，查過新華字典的都知道，字典前幾頁都是一些字或者拼音對應(yīng)的頁碼呻疹，這個(gè)就是字典的索引吃引。如果沒有索引，假設(shè)我們想找一個(gè)字刽锤，那么我們需要通篇的去翻字典际歼，但是一旦我們有了索引，就可以找到對應(yīng)的頁姑蓝，從而直接找到我們想要的東西鹅心。Mysql的索引也是做這個(gè)事情的。
眾所周知Mysql主流的存儲引擎有兩種MyISAM和InnoDB,MyISAM在我的公司用的還是比較少的纺荧，主要是使用InnoDB引擎的Mysql旭愧。為什么呢？那就需要看看二者有什么區(qū)別宙暇。
一输枯、MyISAM和InnoDB的比較：####
</br>
下面看一下兩種引擎的主要不同之處：
| tips | MyISAM | InnoDB |
|---|---|---|
| Full Text 索引 | 支持 | 不支持 |
| count（*）性能（不使用where過濾） | 內(nèi)置計(jì)數(shù)器，性能比較高 | 掃描全表占贫，性能較差（慎用）桃熄，使用where過濾，同時(shí)不使用主鍵進(jìn)行count型奥，性能較好 |
| 對事務(wù)的支持 | 不支持 | 支持事務(wù)瞳收，具有ACDI特性，同時(shí)具有四種隔離級別 |
| 索引結(jié)構(gòu) | 索引采用B+樹厢汹，同時(shí)數(shù)據(jù)和索引是分離的 | 主鍵索引和數(shù)據(jù)是一起的螟深，其他索引是和數(shù)據(jù)分離的 |
| 鎖級別 | MyISAM主要是表鎖，所以性能不高 | InnoDB主要是行鎖烫葬，操作的粒度降低界弧，性能比較好 |
| 外鍵支持 | 不支持外鍵 | 支持外鍵 |
其實(shí)兩者還是有很多的不同的凡蜻，上面的只是一些關(guān)鍵的case，可能不全垢箕，歡迎補(bǔ)充划栓。
二、Mysql索引種類：###
</br>
- 唯一索引：
- 創(chuàng)建索引
create unique index 索引名 on 表名(列名);
alter table 表名 add unique index 索引名 (列名);
- 刪除索引
drop index 索引名 on 表名条获；
alter table 表名 drop index 索引名;
- 主鍵索引：
其實(shí)主鍵索引是唯一索引的一種茅姜，一般指定主鍵的表會默認(rèn)創(chuàng)建主鍵索引，如果不指定主鍵月匣，其實(shí)DB會默認(rèn)的生成主鍵，但是這種主鍵性能不高奋姿，所以在建表的時(shí)候還是指定主鍵吧锄开。==！
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
- 全文索引：
就是對Char称诗、Text等結(jié)構(gòu)建索引萍悴，只有MyISAM支持，現(xiàn)在應(yīng)該很少用了寓免，大家都用基于Lucene的索引中間件解決這種Case了癣诱，沒用過也不過多糾纏。
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
4.單列索引：
對單個(gè)列建立索引袜香，對于經(jīng)常用來進(jìn)行檢索的字段撕予，做好建立索引，能很大程度的優(yōu)化查詢蜈首，但是索引的建立是有一定規(guī)則的实抡，一會兒會提到這些規(guī)則。
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
5.組合索引：
組合索引是將多個(gè)列組合到一起建立一個(gè)索引欢策，組合索引有很著名的“左前綴規(guī)則”吆寨，同時(shí)也因?yàn)檫@個(gè)規(guī)則，其實(shí)有時(shí)候單列索引和組合索引是有重復(fù)的踩寇。什么是左前綴規(guī)則啄清，一會兒會交代。
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
三俺孙、Mysql索引建立原則：###
1.首先辣卒，最重要的一點(diǎn)是，索引不是越多越好睛榄。索引會增加插入更新代價(jià)添寺，所以過多的索引會拖慢數(shù)據(jù)的寫入。一般懈费，一張表的索引不要超過五個(gè)计露。
2.單個(gè)索引中的字段數(shù)不要超過5個(gè)，多索引，容易產(chǎn)生鎖等待和沖突票罐。
3.建立索引的字段叉趣，最好是區(qū)分度比較高的字段，例如：性別這種字段该押，其實(shí)是不適合建索引的疗杉，因?yàn)榧词菇怂饕龑δ愕牟樵円矝]什么幫助。區(qū)分度的計(jì)算可通過：select count(distinct coloum)) /count(*) from table_name來實(shí)現(xiàn)蚕礼。
4.用于Join的字段烟具，一定要建索引，這樣對查詢效率會有很大的提高奠蹬。
5.組合索引其實(shí)可以替代部分單列索引,要盡量避免冗余和重復(fù)朝聋。例如對ABC建索引，其實(shí)可以達(dá)到對A單獨(dú)建索引和AB建索引的情況囤躁。
6.Like的查詢有時(shí)候是不能用到索引的冀痕。例如'%s',前綴模糊的查詢是不走索引的，這種查詢語句應(yīng)該盡量避免狸演，DB是主要業(yè)務(wù)數(shù)據(jù)的存儲言蛇，模糊查詢應(yīng)該盡量少用。
7.不要使用索引計(jì)算宵距，因?yàn)椴樵冏咚饕幕钭詈檬侵苯用欣吧校恳淮味加袑λ饕M(jìn)行計(jì)算，是不合理的满哪，例如：
SELECT * from table where FROM_UNIXTIME( coloum, '%Y%m%d' ) = “xxxx”
改寫成:
SELECT * from table where coloum = FROM_UNIXTIME( 'xxxx'跟伏， '%Y%m%d' ) ;
性能會提高很多。
上述這些原則可能覆蓋的不是很全面翩瓜，也希望有知道的同學(xué)幫我補(bǔ)充受扳。
四、Mysql索引原理：###
首先說mysql的索引結(jié)構(gòu)兔跌，其實(shí)mysql支持很多種索引類型勘高，其中主要的是Hash和Btree索引，后者是主要的索引結(jié)構(gòu)坟桅。
- 簡單介紹B-tree B+ tree樹
B-tree結(jié)構(gòu)視圖B-tree結(jié)構(gòu)視圖
一棵m階的B-tree樹华望，則有以下性質(zhì)
Ki表示關(guān)鍵字值，上圖中仅乓，k1<k2<…<ki<k0<Kn(可以看出赖舟，一個(gè)節(jié)點(diǎn)的左子節(jié)點(diǎn)關(guān)鍵字值<該關(guān)鍵字值<右子節(jié)點(diǎn)關(guān)鍵字值)
Pi表示指向子節(jié)點(diǎn)的指針，左指針指向左子節(jié)點(diǎn)夸楣，右指針指向右子節(jié)點(diǎn)宾抓。即是：p1[指向值]<k1<p2[指向值]<k2……
所有關(guān)鍵字必須唯一值（這也是創(chuàng)建MyISAM 和innodb表必須要主鍵的原因）子漩，每個(gè)節(jié)點(diǎn)包含一個(gè)說明該節(jié)點(diǎn)多少個(gè)關(guān)鍵字，如上圖第二行的i和n
節(jié)點(diǎn)：每個(gè)節(jié)點(diǎn)最可以有m個(gè)子節(jié)點(diǎn)石洗。
根節(jié)點(diǎn)若非葉子節(jié)點(diǎn)幢泼，至少2個(gè)子節(jié)點(diǎn)，最多m個(gè)子節(jié)點(diǎn)
每個(gè)非根讲衫，非葉子節(jié)點(diǎn)至少[m/2]子節(jié)點(diǎn)或叫子樹（[]表示向上取整）缕棵，最多m個(gè)子節(jié)點(diǎn)
關(guān)鍵字：根節(jié)點(diǎn)的關(guān)鍵字個(gè)數(shù)1~m-1
非根非葉子節(jié)點(diǎn)的關(guān)鍵字個(gè)數(shù)[m/2]-1m-1,如m=3，則該類節(jié)點(diǎn)關(guān)鍵字個(gè)數(shù)：2-12
關(guān)鍵字?jǐn)?shù)k和指向子節(jié)點(diǎn)個(gè)數(shù)指針p的關(guān)系：k+1=p 涉兽，注意根據(jù)儲存數(shù)據(jù)的具體需求招驴，左右指針為空時(shí)要有標(biāo)志位表示沒有 B+tree結(jié)構(gòu)示意圖如下：B+tree結(jié)構(gòu)示意圖
B+樹是B-樹的變體，也是一種多路搜索樹： * 非葉子結(jié)點(diǎn)的子樹指針與關(guān)鍵字個(gè)數(shù)相同 * 為所有葉子結(jié)點(diǎn)增加一個(gè)鏈指針（紅點(diǎn)標(biāo)志的箭頭） - MyISAM的索引結(jié)構(gòu)：
MyISAM索引用的B+ tree來儲存數(shù)據(jù)枷畏，MyISAM索引的指針指向的是鍵值的地址别厘，地址存儲的是數(shù)據(jù)，如下圖：
MyISAM索引用的B+ tree
結(jié)構(gòu)講解：上圖3階樹矿辽，主鍵是Col2，Col值就是改行數(shù)據(jù)保存的物理地址郭厌，其中紅色部分是說明標(biāo)注袋倔。
1標(biāo)注部分也許會迷惑，前面不是說關(guān)鍵字15右指針的指向鍵值要大于15折柠，怎么下面還有15關(guān)鍵字宾娜？因?yàn)锽+tree的所有葉子節(jié)點(diǎn) 包含所有關(guān)鍵字且是按照升序排列（主鍵索引唯一，輔助索引可以不唯一）扇售，所以等于關(guān)鍵字的數(shù)據(jù)值在右子樹
2標(biāo)注是相應(yīng)關(guān)鍵字存儲對應(yīng)數(shù)據(jù)的物理地址前塔，注意這也是之后和InnoDB索引不同的地方之一
2標(biāo)注也是一個(gè)所說MyISAM表的索引和數(shù)據(jù)是分離的，索引保存在”表名.MYI”文件內(nèi)承冰，而數(shù)據(jù)保存在“表名.MYD”文件內(nèi)华弓，2標(biāo)注 的物理地址就是“表名.MYD”文件內(nèi)相應(yīng)數(shù)據(jù)的物理地址。（InnoDB表的索引文件和數(shù)據(jù)文件在一起）
輔助索引和主鍵索引沒什么大的區(qū)別困乒，輔助索引的索引值是可以重復(fù)的（但I(xiàn)nnoDB輔助索引和主鍵索引有很明顯的區(qū)別寂屏，這里 先提醒注意一下）
3.InnoDB的索引結(jié)構(gòu)：
（1）結(jié)構(gòu)圖
結(jié)構(gòu)上：由上圖可以看出InnoDB的索引結(jié)構(gòu)很MyISAM的有很明顯的區(qū)別
MyISAM表的索引和數(shù)據(jù)是分開的，用指針指向數(shù)據(jù)的物理地址娜搂，而InnoDB表中索引和數(shù)據(jù)是儲存在一起迁霎。看紅框1可看出一行 數(shù)據(jù)都保存了百宇。
還有一個(gè)上圖多了三行的隱藏?cái)?shù)據(jù)列（虛線表）考廉，這是因?yàn)镸yISAM不支持事務(wù)，InnoDB處理事務(wù)在性能上并發(fā)控制上比較好携御， 看圖中的紅框2中的DB_TRX_ID是事務(wù)ID昌粤，自動增長既绕；db_roll_ptr是回滾指針，用于事務(wù)出錯(cuò)時(shí)數(shù)據(jù)回滾恢復(fù)婚苹；db_row_id 是記錄行號岸更，這個(gè)值其實(shí)在主鍵索引中就是主鍵值，這里標(biāo)出重復(fù)是為了容易介紹膊升，還有的是若不是主鍵索引（輔助索引）怎炊， db_row_id會找表中unique的列作為值，若沒有unique列則系統(tǒng)自動創(chuàng)建一個(gè)廓译。關(guān)于InnoDB跟多事務(wù)MVCC點(diǎn) 此：http://www.phpben.com/?post=72
（2）加入上表中Col1是主鍵（下圖標(biāo)錯(cuò)）评肆，而Col2是輔助索引，則相應(yīng)的輔助索引結(jié)構(gòu)圖：
可以看出InnoDB輔助索引并沒有保存相應(yīng)的所有列數(shù)據(jù)非区，而是保存了主鍵的鍵值（圖中1瓜挽、2、3….）這樣做利弊也是很明顯：
在已有主鍵索引征绸，避免數(shù)據(jù)冗余久橙，同時(shí)在修改數(shù)據(jù)的時(shí)候只需修改輔助索引值。
但輔助索引查找數(shù)據(jù)事要檢索兩次管怠，先找到相應(yīng)的主鍵索引值然后在去檢索主鍵索引找到對應(yīng)的數(shù)據(jù)淆衷。這也是網(wǎng)上很多 mysql性能優(yōu)化時(shí)提到的“主鍵盡可能簡短”的原因，主鍵越長輔助索引也就越大渤弛，當(dāng)然主鍵索引也越大祝拯。
四、Mysql索引的使用：###
</br>
見下回.....
