??mysql的索引策略中有一條是聚簇索引，而聚簇索引并不是唯一索引，普通索引之類的索引類型，而是一種數(shù)據(jù)的存儲方式。大多數(shù)索引存在的形式為B-tree，葉子節(jié)點的索引則和其對應(yīng)的數(shù)據(jù)行數(shù)據(jù)緊湊的存儲在一起，這就是術(shù)語聚簇的含義。實現(xiàn)數(shù)據(jù)存儲形式的是存儲引擎，但并不是所有存儲引擎都支持聚簇索引，而著名的InnoDB則是支持的引擎之一，下面都以InnoDB為例。
創(chuàng)新互聯(lián)建站是專業(yè)的紫云網(wǎng)站建設(shè)公司，紫云接單;提供網(wǎng)站建設(shè)、網(wǎng)站制作,網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進行紫云網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團隊,希望更多企業(yè)前來合作!
??而存儲引擎不能管理兩份同樣的數(shù)據(jù)，所以聚簇索引在同一張數(shù)據(jù)表中只能存在一個，其他的索引只能是非聚簇索引，也就是二級索引。數(shù)據(jù)表的如果有指定primary key，那么InnoDB就會把primary key作為聚簇索引來存儲，如果沒有，則會取第一個not null，unique的索引作為聚簇索引，unique的索引也不存在的話，InnoDB就會自行的，隱式的指定一個row ID列作為聚簇索引存儲，但這個row ID不會被用戶管理。
聚簇索引一些重要的優(yōu)點：
??1. 在有聚簇索引的數(shù)據(jù)表中，使用聚簇索引進行查詢的時候，因為索引和數(shù)據(jù)聚集在同一個B-tree中，能夠直接從索引獲取到數(shù)據(jù)行，比非聚簇索引的性能要好。
??2. 反之在沒有聚簇索引的數(shù)據(jù)表中，因為不能通過unique的值去聚集數(shù)據(jù)，所以需要通過非聚簇索引查詢數(shù)據(jù)的物理地址或者全表掃描來獲取數(shù)據(jù)，這樣每一行數(shù)據(jù)可能都會導(dǎo)致一次磁盤I/O。
在提升性能的同時，聚簇索引也存在著缺點：
??1. 更新聚簇索引的代價會很大，因為需要將數(shù)據(jù)行和主鍵進行重排，移動到新的位置，并且二級索引可能也需要更新。
??2. 聚簇索引的插入速度嚴(yán)重依賴插入順序，嚴(yán)格的升序主鍵是性能最好的方式，但如果主鍵是亂序的插入，例如用uuid作為主鍵，當(dāng)主鍵值需要插入到某一頁已經(jīng)寫滿的page中，存儲引擎就需要將page分裂成兩個頁面來容納數(shù)據(jù)，這一個 頁分裂(page split) 操作，page split會使得數(shù)據(jù)表占用更多的磁盤空間。
??3. 通過二級索引獲取需要兩次索引查找，因為二級索引保存的是聚簇索引的主鍵，而不是指向數(shù)據(jù)的邏輯指針，所以獲取主鍵后需要再進行一次搜索才能獲取數(shù)據(jù)。
??聚簇索引和二級索引的數(shù)據(jù)分布方式不同，在MyISAM和InnoDB的數(shù)據(jù)文件組織方式中也有體現(xiàn)。
??MyISAM的數(shù)據(jù)由3個文件組成：1. .frm(表結(jié)構(gòu)描述文件) ，2. .MYD(數(shù)據(jù)行文件) ，3. .MYI(索引文件) 。
??InnoDB則有2個文件：1. .frm(表結(jié)構(gòu)描述文件) ，2. .MYD(數(shù)據(jù)行文件和索引信息) 。
??MyISAM引擎沒有使用索引和數(shù)據(jù)的聚集的分布方式，所以主鍵和其他索引的是沒有區(qū)別，就都存儲在索引文件中。
InnoDB的鎖機制是使用索引來實現(xiàn)，表現(xiàn)的等級為行級鎖，而MyISAM則是表級鎖，這也跟數(shù)據(jù)分布方式有關(guān)。InnoDB的主鍵索引與數(shù)據(jù)緊湊的聚集在一起，并且包含了事務(wù)ID，用于事務(wù)MVCC的回滾指針，而MyISAM則是數(shù)據(jù)與索引分離，無法實現(xiàn)如此細(xì)粒度的鎖。
??1. InnoDB暫時不能由用戶選定索引作為聚簇索引，InnoDB有自己的聚簇索引選取規(guī)則，所以在創(chuàng)建表的時候最好設(shè)置一個與業(yè)務(wù)無關(guān)的主鍵id作為聚簇索引，這樣修改二級索引和數(shù)據(jù)的時候，無需移動數(shù)據(jù)位置，提升性能。
??2. 聚簇索引的主鍵id不要使用uuid，uuid會使得數(shù)據(jù)的插入添加額外的頁分裂操作，降低性能，最好使用單調(diào)自增的id。
引入一個面試問題:
看完以下以后再回顧，會發(fā)現(xiàn)迎刃而解
Mysql 可以為每一張表設(shè)置 存儲引擎 這里我們只說 InnoDB 存儲引擎.
由于實際情況，數(shù)據(jù)頁只能按照一棵 B+樹 進行排序， 因此每張表只能擁有一個 聚集索引（即 主鍵）。
栗子：
每個葉子節(jié)點的索引行中包含了一個書簽（bookmark）. 該書簽是用來告訴 InnoDB存儲引擎哪里可以找到該索引對應(yīng)的數(shù)據(jù)行或者說 行數(shù)據(jù)！ 由于InnoDB存儲引擎表， 是按照主鍵來構(gòu)建的， 所以 ，該書簽內(nèi)其實包含或者說指向了 數(shù)據(jù)行所對應(yīng)的聚集索引鍵
也就是說 輔助索引的 葉結(jié)點保存了 指向?qū)?yīng)數(shù)據(jù)的 聚集索引， 可以通過該聚集索引 找到對應(yīng)的數(shù)據(jù)行
輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因為每張表上可以有多個輔助索引。
當(dāng)通過輔助索引來尋找數(shù)據(jù)時，InnoDB 存儲引擎會遍歷輔助索引并通過葉級別的指針獲得指向主鍵索引(聚集索引)的主鍵，然后再通過聚集索引找到一個完整的數(shù)據(jù)行。
例如：
聚集索引輔助索引關(guān)系:
： 又叫做組合索引 ， 輔助索引的一種 ， 和普通創(chuàng)建索引的方式一樣，不同的是 可以同時添加多列來作為索引項;
從本質(zhì)上來說，聯(lián)合索引也是一課B+樹
個人理解： 所謂最左原則， 是因為 存儲引擎構(gòu)建組合索引時 是根據(jù)最左邊的那一列索引項進行排序的 ，所以使用組合索引，必須滿足 條件中必須存在 最左邊那一列的索引項，這樣 才可以找到對應(yīng)的索引，繼而 去尋找對應(yīng)的數(shù)據(jù)
： 又叫做 索引覆蓋，InnoDB中支持覆蓋索引，即 從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。
比如 這里沒有根據(jù)最左原則使用組合索引，但是 優(yōu)化器依然進行選擇
共勉，歡迎指導(dǎo)謝謝~
? ?本節(jié)課主要關(guān)注InnoDB，但是這里討論的原理對于任何支持聚簇索引的存儲引擎都是適用的。
? ?葉子節(jié)點包含了全部數(shù)據(jù)，其他節(jié)點只包含索引列。InnoDB將通過主鍵聚集數(shù)據(jù)，也就是說上圖中的“被索引的列”就是主鍵列。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引InnoDB會隱式定義一個主鍵來作為聚簇索引。
? ?如果主鍵比較大的話，那輔助索引將會變的更大，因為 輔助索引的葉子存儲的是主鍵值；過長的主鍵值，會導(dǎo)致非葉子節(jié)點占用占用更多的物理空間
所以建議使用int的auto_increment作為主鍵
? ?主鍵的值是順序的，所以 InnoDB 把每一條記錄都存儲在上一條記錄的后面。當(dāng)達到頁的最大值時，下一條記錄就會寫入新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會近似于被順序的記錄填滿。
? ?聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想 象，它會干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁，當(dāng)然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡單了，它只需要一 頁一頁地寫，索引結(jié)構(gòu)相對緊湊，磁盤碎片少，效率也高。
? ?因為MyISAM的主索引并非聚簇索引，那么他的數(shù)據(jù)的物理地址必然是凌亂的，拿到這些物理地址，按照合適的算法進行I/O讀取，于是開始不停的尋道不停的旋轉(zhuǎn)。聚簇索引則只需一次I/O。（強烈的對比）
? ?不過，如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類的操作的話，還是MyISAM占優(yōu)勢些，因為索引所占空間小，這些操作是需要在內(nèi)存中完成的。
? ?MyISM使用的是非聚簇索引， 非聚簇索引的兩棵B+樹看上去沒什么不同 ，節(jié)點的結(jié)構(gòu)完全一致只是存儲的內(nèi)容不同而已，主鍵索引B+樹的節(jié)點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表數(shù)據(jù)存儲在獨立的地方，這兩顆B+樹的葉子節(jié)點都使用一個地址指向真正的表數(shù)據(jù)，對于表數(shù)據(jù)來說，這兩個鍵沒有任何差別。由于 索引樹是獨立的，通過輔助鍵檢索無需訪問主鍵的索引樹 。
? ?所以說，聚簇索引性能最好而且具有唯一性，所以非常珍貴，必須慎重設(shè)置。 一般要根據(jù)這個表最常用的SQL查詢方式來進行選擇，某個字段作為聚簇索引，或組合聚簇索引 ，這個要看實際情況。
? ?聚簇索引和非聚簇索引的數(shù)據(jù)分布有區(qū)別，主鍵索引和二級索引的數(shù)據(jù)分布也有區(qū)別，通常會讓人感到困擾和以外，下面通過一個列子來講解InnoDB和MyISAM是如何存儲數(shù)據(jù)的：
? ?該表的主鍵取值1~10000，按照隨機順序插入并使用optimize table命令做了優(yōu)化。換句話說，數(shù)據(jù)在磁盤上的存儲方式已是最優(yōu)，但行的順序是隨機的。列col2的值是從1~100之間隨機賦值，所以有很多重復(fù)的值。
? ?MyISAM的數(shù)據(jù)分布很簡單，所以先介紹它。MyISAM按照數(shù)據(jù)插入的順序存儲在磁盤上，如下圖所示：
在行的旁邊顯示行號，從0開始遞增。因為行是定長的，所以MyISAM可以從表的開頭跳過所需的字節(jié)找到需要的行。
col2上的索引
? ?事實上，MyISAM中主鍵索引和其他索引在結(jié)構(gòu)上沒有什么不同。主鍵索引就是一個名為PRIMARY的唯一非空索引。
? ?InnoDB支持聚簇索引，所以使用不同的方式存儲同樣的數(shù)據(jù)。
? ?第一眼看上去，感覺和前面的沒什么區(qū)別，但是該圖顯示了整個表，而不是只有索引。因為在InnoDB中，聚簇索引就是表，所以不像MyISAM那樣需要獨立的行存儲，這也是為什么MyISAM索引和數(shù)據(jù)結(jié)構(gòu)是分開的。
? ?聚簇索引的每一個葉子節(jié)點都包含了主鍵值。事務(wù)ID、用于事務(wù)和MVCC的回滾指針以及所有的剩余列。如果主鍵是一個列前綴索引，InnoDB也會包含完整的主鍵列和剩下的其他列。
? ?還有一點和MyISAM不同的是，InnoDB的二級索引和聚簇索引很不相同。InnoDB的二級索引的葉子節(jié)點中存儲的不是“行指針”，而是主鍵值，并以此作為指向行的“指針”。這樣的策略減少了當(dāng)出現(xiàn)行移動或者數(shù)據(jù)頁分裂時二級索引的維護工作。使用主鍵值當(dāng)作指針會讓二級索引占用更多的空間，換來的好處是，InnoDB在移動時無需更新二級索引中的這個“指針”。
? ?我們在來看一下 col2索引 。
? ?每一個葉子節(jié)點包含了索引列（這里是col2），緊接著是主鍵值（col1），上圖我們省略了非葉子節(jié)點這樣的細(xì)節(jié)。InnoDB非葉子節(jié)點包含了索引列和一個指向下一級節(jié)點的指針。
? ?最后，以一張圖表示InnoDB和MyISAM保存數(shù)據(jù)和索引的區(qū)別。
? ?前面講過，最好使用AUTO_INCREMENT自增列來聚集數(shù)據(jù)，避免隨機的、不連續(xù)的、值分布范圍大的列做聚簇索引，特別是對于I/O密集型的應(yīng)用。例如，從性能角度考慮，使用UUID來作為聚簇索引則會很糟糕：他使得聚簇索引的插入變得完全隨機，這是最壞的情況，使得數(shù)據(jù)沒有任何聚集特性。
? ?為了演示這一點，我們做兩個基準(zhǔn)測試：
1、使用證書ID插入userinfo表，和uuid作為主鍵的userinfo_uuid表
? ?userinfo_uuid表跟userinfo表除了主鍵給為UUID，其他字段都一樣
? ?測試這兩個表的設(shè)計，首先在一個有足夠內(nèi)存容納索引的服務(wù)器上向這兩個表各插入100萬條記錄。然后向兩個表繼續(xù)插入300萬數(shù)據(jù)，使索引的大小超過服務(wù)器的內(nèi)存容量。測試結(jié)果如下：
? ?向UUID主鍵插入行不僅花費的時間更長，而且索引占用的空間也更大。這一方面是由于主鍵字段更長，另一方面毫無疑問是由于頁分裂和碎片導(dǎo)致的。
? ?為了明白為什么會這樣，來看看往第一個表中插入數(shù)據(jù)時，索引發(fā)生了什么變化。
自整型主鍵插入
? ?因為主鍵是順序的，所以InnoDB把每一條記錄都存在上一條記錄的后面。當(dāng)達到頁的最大容量后，下一條記錄就會寫入到新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會近似于被順序的記錄填滿，這也正是所期望的結(jié)果。
UUID插入
? ?因為新行的主鍵值不一定比之前插入的大，所以InnoDB無法簡單的總是把新行插入到索引的最后，而是需要為新的行尋找合適的位置，通常是已有數(shù)據(jù)的中間位置，并且分配空間。這會正價很多的額外工作，并導(dǎo)致數(shù)據(jù)分布不夠優(yōu)化。
缺點：
把這些隨機值載入到聚簇索引后，也許需要做一次OPTIMIZE TABLE來重建表并優(yōu)化頁的填充。
結(jié)論 ：使用InnoDB時應(yīng)盡可能地按主鍵順序插入數(shù)據(jù)，并且盡可能地單調(diào)增加聚簇鍵的值來插入新行。
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。
在模糊搜索中很有效，搜索全文中的某一個字段，可以參考這篇博文
:
我們先進行下面一個實驗看看InnoDB下的主鍵索引的一個現(xiàn)象。
查看:
我們插入進去的時候，數(shù)據(jù)的id都是亂序的，為什么這里最后select查詢出來的結(jié)果都是進行了排序？
這是因為InnoDB索引底層實現(xiàn)的是B+tree，B+tree具有下列的特點：
所以上面的排序是為了使用B+tree的結(jié)構(gòu) ，B+tree為了范圍搜索，將主鍵按照從小到大排序后，拆分成節(jié)點。后續(xù)還有新的節(jié)點進入的時候，和B-tree相同的操作，會進行分裂。
一般來說，聚簇索引的B+tree都是三層
InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。
為什么這里輔助索引葉子結(jié)點不直接存儲數(shù)據(jù)呢？
MYISAM只有非聚簇索引，索引最終指向的都是物理地址。
Q:既然有回表的存在，那么聚簇索引的優(yōu)勢在哪里？
Q:主鍵索引作為聚簇索引需要注意什么
在查詢語句中使用LIke關(guān)鍵字進行查詢時，如果匹配字符串的第一個字符為"%"，索引不會使用。如果“%”不是在第一位，索引就會使用
多列索引是在表的多個字段上創(chuàng)建的索引，滿足最左前綴匹配原則，索引才會被使用
查詢語句只有Or關(guān)鍵字時候，如果OR前后的兩個條件都是索引，這這次查詢將會使用索引，否則Or前后有一個條件的列不是索引，那么查詢中將不使用索引
mysql的聚簇索引是指innodb引擎的特性，mysiam并沒有，如果需要該索引，只要將索引指定為主鍵（primary key）就可以了。
比如：
create?table?blog_user
(
user_Name?char(15)?not?null?check(user_Name?!=''),
user_Password?char(15)?not?null,
user_emial?varchar(20)?not?null?unique,
primary?key(user_Name)??????????
)engine=innodb?default?charset=utf8?auto_increment=1;
其中的?primary key(user_Name) 這個就是聚簇索引索引了；
1 ：clustered index 其實數(shù)據(jù)存儲結(jié)構(gòu)，索引和記錄（全部）內(nèi)容保存同一個結(jié)構(gòu)中。“聚簇”就是索引和記錄緊密在一起，分開就不是聚簇索引了，所以一張表只能有唯一的聚簇索引。
--（除此之外的表上的每個非聚簇索引都是 ，又叫輔助索引（secondary indexes））
————————————————
2 非聚簇索引：secondary index ，葉子節(jié)點保存了主鍵值，要定位記錄還要再查一遍聚簇索引。
————————————————
3 覆蓋索引：覆蓋索引是指索引的葉子節(jié)點已包含所有要查詢的列，因此不需要訪問表數(shù)據(jù)（回表~~有學(xué)個名詞，查詢聚簇索引）
查找時能不能直接定位：聚簇索引的葉節(jié)點就是數(shù)據(jù)節(jié)點，而非聚簇索引的頁節(jié)點仍然是索引檢點，并保留一個鏈接指向?qū)?yīng)數(shù)據(jù)塊。
————————————————
4 聯(lián)合索引又叫復(fù)合索引。
對于復(fù)合索引:Mysql從左到右的使用索引中的字段，一個查詢可以只使用索引中的一部份，但只能是最左側(cè)部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 。
重慶分公司
重慶分公司
