最近在學(xué)習(xí)MySQL的存儲引擎和索引的知識?？戳嗽S多篇介紹MyISAM和InnoDB的索引的例子，都能理解。
專注于為中小企業(yè)提供網(wǎng)站建設(shè)、成都網(wǎng)站制作服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)威寧免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了上千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。
像這張索引圖：
PS:該圖來自大神張洋的《MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理》一文。
但許多文章講述的都是單列索引，我很好奇 聯(lián)合索引對應(yīng)的結(jié)構(gòu)圖是怎樣的。
比方說聯(lián)合索引 (col1, col2,col3)，我知道在邏輯上是先按照col1進(jìn)行排序再按照col2進(jìn)行排序最后再按照col3進(jìn)行排序。因此如果是select * from table where col1 = 1 and col3 = 3的話，只有col1的索引部分能生效。但是其物理結(jié)構(gòu)上這個聯(lián)合索引是怎樣存在的，我想不懂。
上網(wǎng)查閱了許多資料，總算有點眉目了。
假設(shè)這是一個多列索引(col1, col2,col3)，對于葉子節(jié)點，是這樣的：
PS:該圖改自《MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理》一文的配圖。
也就是說，聯(lián)合索引(col1, col2,col3)也是一棵B+Tree，其非葉子節(jié)點存儲的是第一個關(guān)鍵字的索引，而葉節(jié)點存儲的則是三個關(guān)鍵字col1、col2、col3三個關(guān)鍵字的數(shù)據(jù)，且按照col1、col2、col3的順序進(jìn)行排序。
配圖可能不太讓人滿意，因為col1都是不同的，也就是說在col1就已經(jīng)能確定結(jié)果了。自己又畫了一個圖（有點丑），col1表示的是年齡，col2表示的是姓氏，col3表示的是名字。如下圖：
PS:對應(yīng)地址指的是數(shù)據(jù)記錄的地址。
如圖，聯(lián)合索引(年齡, 姓氏,名字)，葉節(jié)點上data域存儲的是三個關(guān)鍵字的數(shù)據(jù)。且是按照年齡、姓氏、名字的順序排列的。
因此，如果執(zhí)行的是：
select * from STUDENT where 姓氏='李' and 名字='安';
或者
select * from STUDENT where 名字='安';
那么當(dāng)執(zhí)行查詢的時候，是無法使用這個聯(lián)合索引的。因為聯(lián)合索引中是先根據(jù)年齡進(jìn)行排序的。如果年齡沒有先確定，直接對姓氏和名字進(jìn)行查詢的話，就相當(dāng)于亂序查詢一樣，因此索引無法生效。因此查詢是全表查詢。
如果執(zhí)行的是：
select * from STUDENT where 年齡=1 and 姓氏='李';
那么當(dāng)執(zhí)行查詢的時候，索引是能生效的，從圖中很直觀的看出，age=1的是第一個葉子節(jié)點的前6條記錄，在age=1的前提下，姓氏=’李’的是前3條。因此最終查詢出來的是這三條，從而能獲取到對應(yīng)記錄的地址。
如果執(zhí)行的是：
select * from STUDENT where 年齡=1 and 姓氏='黃' and 名字='安';
那么索引也是生效的。
而如果執(zhí)行的是：
select * from STUDENT where 年齡=1 and 名字='安';
那么，索引年齡部分能生效，名字部分不能生效。也就是說索引部分生效。
因此我對聯(lián)合索引結(jié)構(gòu)的理解就是B+Tree是按照第一個關(guān)鍵字進(jìn)行索引，然后在葉子節(jié)點上按照第一個關(guān)鍵字、第二個關(guān)鍵字、第三個關(guān)鍵字…進(jìn)行排序。
而之所以會有最左原則，是因為聯(lián)合索引的B+Tree是按照第一個關(guān)鍵字進(jìn)行索引排列的。
聯(lián)合索引在B+樹上的結(jié)構(gòu)介紹
先正面回答你的問題
數(shù)據(jù)是否重復(fù)不是建立索引的重要依據(jù)，甚至都不是依據(jù)。
只要不完全重復(fù)（所有元組的該元素都一樣），那么建立索引就是有意義的。
即使當(dāng)前數(shù)據(jù)完全重復(fù)，也不是不能建立索引，這種情況有點復(fù)雜，不細(xì)說了。
對于你后面的疑問，可以給你一個如何建立索引的忠告，“如何查就如何建”。
索引的建立，唯一的原因就是為了查詢（廣義的查詢），實際上建立索引會使得數(shù)據(jù)存儲所占空間變大，有時索引所占的空間會查過數(shù)據(jù)本身的空間。索引的建立也會使得數(shù)據(jù)插入時變慢，特殊情況下，慢的難以忍受，所以dba的重要工作之一，就是檢查索引層級并優(yōu)化。
索引建立的唯一好處，就是按照索引查詢時，變快了。type，status這2個字段是否適合建立索引，就要看你是否要按照這2個字段進(jìn)行檢索。而檢索的順序決定了如何建立索引。
對于索引類型和索引方式，我建議就
normal
和
btree
就適用于大多數(shù)情況。若你參與的是一個大數(shù)據(jù)處理項目，對數(shù)據(jù)存儲和檢索有特別要求，那么需要分析多個層面，比如數(shù)據(jù)吞吐量、數(shù)據(jù)的方差、平均差等等很多參數(shù)才考慮是否用聚集索引等（mysql好像還沒聚集索引），至于是否是唯一索引，我建議不使用，即使能判定數(shù)據(jù)是唯一的也不要用，全文索引也沒有必要。
MySQL 前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。
集一個索引包含多個列（最左前綴匹配原則）
索引列的值必須唯一，但允許有空值
全文索引為FUllText，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復(fù)值和空值，全文索引可以在CHAR,VARCHAR,TEXT類型列上創(chuàng)建
設(shè)定主鍵后數(shù)據(jù)會自動建立索引，InnoDB為聚簇索引
即一個索引只包含單個列，一個表可以有多個單列索引
覆蓋索引是指一個查詢語句的執(zhí)行只用從所有就能夠得到，不必從數(shù)據(jù)表中讀取，覆蓋索引不是索引樹，是一個結(jié)果，當(dāng)一條查詢語句符合覆蓋索引條件時候，MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù)，這樣避免了查到索引后的回表操作，減少了I/O效率
查看索引
列名解析：
刪除索引
查看：
刪除前:
刪除后：
普通的索引，沒有什么介紹
查看:（注意和前綴索引Sub_part的區(qū)別）
當(dāng)索引的列是unique的時候，會生成唯一索引，唯一索引關(guān)于null有下列兩種情況
SQLSERVER 下的唯一索引的列，允許null值，但最多允許有一個空值
MYSQL下的唯一索引的列，允許null值，并且允許多個空值
查看:
會建立兩個索引，一個非聚簇索引，一個是唯一索引
結(jié)果:
可以插入兩個空值（明人不說暗話，我喜歡MySQL）
一方面，它不會索引所有字段所有字符，會減小索引樹的大小.
另外一方面，索引只是為了區(qū)別出值，對于某些列，可能前幾位區(qū)別很大，我們就可以使用前綴索引。
一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。對于BLOB，TEXT，或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。
查看：
查看：
復(fù)合索引的最左前綴匹配原則 ：
對于復(fù)合索引，查詢在一定條件才會使用該索引
減少開銷。 建一個聯(lián)合索引(col1,col2,col3)，實際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對于大量數(shù)據(jù)的表，使用聯(lián)合索引會大大的減少開銷！
覆蓋索引。 對聯(lián)合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過遍歷索引取得數(shù)據(jù)，而無需回表，這減少了很多的隨機io操作。減少io操作，特別的隨機io其實是dba主要的優(yōu)化策略。所以，在真正的實際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。
效率高。 索引列越多，通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)，然后再排序，再分頁；如果是聯(lián)合索引，通過索引篩選出1000w10% 10% *10%=1w。
在模糊搜索中很有效，搜索全文中的某一個字段，可以參考這篇博文
:
我們先進(jìn)行下面一個實驗看看InnoDB下的主鍵索引的一個現(xiàn)象。
查看:
我們插入進(jìn)去的時候，數(shù)據(jù)的id都是亂序的，為什么這里最后select查詢出來的結(jié)果都是進(jìn)行了排序？
這是因為InnoDB索引底層實現(xiàn)的是B+tree，B+tree具有下列的特點：
所以上面的排序是為了使用B+tree的結(jié)構(gòu) ，B+tree為了范圍搜索，將主鍵按照從小到大排序后，拆分成節(jié)點。后續(xù)還有新的節(jié)點進(jìn)入的時候，和B-tree相同的操作，會進(jìn)行分裂。
一般來說，聚簇索引的B+tree都是三層
InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。
為什么這里輔助索引葉子結(jié)點不直接存儲數(shù)據(jù)呢？
MYISAM只有非聚簇索引，索引最終指向的都是物理地址。
Q:既然有回表的存在，那么聚簇索引的優(yōu)勢在哪里？
Q:主鍵索引作為聚簇索引需要注意什么
在查詢語句中使用LIke關(guān)鍵字進(jìn)行查詢時，如果匹配字符串的第一個字符為"%"，索引不會使用。如果“%”不是在第一位，索引就會使用
多列索引是在表的多個字段上創(chuàng)建的索引，滿足最左前綴匹配原則，索引才會被使用
查詢語句只有Or關(guān)鍵字時候，如果OR前后的兩個條件都是索引，這這次查詢將會使用索引，否則Or前后有一個條件的列不是索引，那么查詢中將不使用索引
重慶分公司
重慶分公司
