聚簇索引：是對磁盤上實際數據重新組織以按指定的一個或多個列的值排序的算法。特點是存儲數據的順序和索引順序一致，且一個表只能有一個聚簇索引，因為物理存儲只能有一個順序。主鍵索引一般都是聚簇索引

非聚簇索引：表數據存儲順序與索引順序無關。對于非聚簇索引，葉結點包含索引字段值及指向數據頁數據行的邏輯指針，其行數量與數據表行數據量一致。非聚簇索引記錄的物理順序與邏輯順序沒有必然的聯系，與數據的存儲物理結構沒有關系；一個表對應的非聚簇索引可以有多條，根據不同列的約束可以建立不同要求的非聚簇索引；

一般情況下主鍵會默認創建聚簇索引，且一張表只允許存在一個聚簇索引。因為物理存儲只能有一個順序。

聚簇索引的葉子節點就是數據節點（Innodb的B+樹的主鍵對應的數據節點），而非聚簇索引的葉子節點仍然是索引節點，只不過有指向對應數據塊的指針。

聚簇索引主鍵的插入速度要比非聚簇索引主鍵的插入速度慢很多。

相比之下，聚簇索引適合排序，非聚簇索引不適合用在排序的場合。因為聚簇索引本身已經是按照物理順序放置的，排序很快。非聚簇索引則沒有按序存放，需要額外消耗資源來排序。

建立聚簇索引的語句：

CREATE CLUSTER INDEX index_name ON table_name(column_name1,...);

全文索引（也稱全文檢索）是目前搜索引擎使用的一種關鍵技術。它能夠利用【分詞技術】等多種算法智能分析出文本文字中關鍵詞的頻率和重要性，然后按照一定的算法規則智能地篩選出我們想要的搜索結果。

select * from 表名 where?標題 like '%xxx%' or 內容?like '%xxx%' or 作者?like '%xxx%';

這種搜索效率無比底下

全文索引是為了使得“關鍵詞搜索”功能更加的高效能。

我們有這么一張數據表：?

文章id 文章標題 文章內容

1 超級塞亞人 ?我是超級塞亞人我喜歡吃蘋果，我不是233大國的人，也不是地球人

2 我233大國威武，我233大國13億人，我233大國

3 我喜歡游泳 游泳有很多好方法

4 動畫片 我兒子喜歡看動畫片，尤其是七龍珠，因為里面有塞亞人，而且塞亞人喜歡吃蘋果,他們不是地球人

5 運動 我喜歡運動，喜歡跑步，喜歡游泳，喜歡健身，喜歡xxoo

6 打炮 我是一個二戰的老兵，這是我的回憶錄，我最幸福的時光就是在233大國吃著蘋果打炮

7 。。。 ?

8 。。。 ?

9 。。。 ?

然后，根據以上的文章內容，如果建立了一個索引文件（這里忽略索引文件的數據結構，僅僅以一種易于理解的方式呈現）：?

關鍵詞 ? 文章id

塞亞人 ? ?1,4

蘋果 ? ? ?1,4,6

233大國 ? ? ?1,2,6

地球 ? ? ? ?1,4

游泳 ? ? ? ?3,5

七龍珠 ? ? ?4

喜歡 ? ? 1,4,5,6 ??

那么當我想搜索 ?“塞亞人”的時候，這個索引文件直接告訴我在文章id為1和4的文章里有這個詞。?

這個索引文件就是“全文索引”。

如何使用全文索引和分詞的方式來幫助優化你的搜索呢？

需要工作的程序：索引程序，分詞程序，數據庫。?

工作原理：?

1、索引程序從數據庫讀取數據，比如上面例子中的數據表，索引程序通過sql語句：select 文章id,文章標題，文章內容 from 文章表.獲得文章的相關數據?

2、索引程序對需要索引的內容進行“分詞”，而這里的分詞就是調用分詞程序啦！?

3、索引程序對分好詞的一個個詞條加入索引文件。

在你寫的代碼里，原來到數據庫－－－－like %xxx%－－－－－的語句就變成了到索引文件里去查找，從而找到相應的數據（這點相信你已經理解啦！）

創建全文索引的兩種方法：

1.在建表語句中

2.在已知表中

ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);

具體如何使用全文索引呢？

不用全文索引時的寫法：SELECT * FROM article WHERE content LIKE ‘%查詢字符串%’;

使用全文索引：SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查詢字符串’);

注意：

1、MySql自帶的全文索引只能對英文進行全文檢索，目前無法對中文進行全文檢索。如果需要對包含中文在內的文本數據進行全文檢索，我們需要采用Sphinx（斯芬克斯）/Coreseek技術來處理中文。

2、使用MySql自帶的全文索引時，如果查詢字符串的長度過短將無法得到期望的搜索結果。MySql全文索引所能找到的詞默認最小長度為4個字符。另外，如果查詢的字符串包含停止詞，那么該停止詞將會被忽略。

3、如果可能，請盡量先創建表并插入所有數據后再創建全文索引，而不要在創建表時就直接創建全文索引，因為前者比后者的全文索引效率要高。

需要：

1).主鍵自動建立唯一索引
2).頻繁作為查詢條件的字段應該創建索引
3).查詢中與其它表關聯的字段，外鍵關系建立索引
4).單鍵/組合索引的選擇問題(在高并發下傾向創建組合索引)
5).查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
6).查詢中統計或者分組字段

不需要：

1).表記錄太少
2).經常增刪改的表(因為不僅要保存數據，還要保存一下索引文件) 索引本來是一種事先在寫的階段形成一定的數據結構，從而使得在讀的階段效率較高的方式，但是如果一個字段是寫多讀少，則會降低寫的速度。
3).數據重復且分布平均的表字段（比如性別），因此應該只為最經常查詢和最經常排序的數據列建立索引。

4).where條件里用不到的字段不創建索引

假設有條件語句A=a AND B=b，如果A和B是兩個單獨的索引，在AND條件下只有一個索引起作用，對于B則要逐個判斷，而如果使用組合索引(A, B)，只要遍歷一棵樹就可以了，大大增加了效率。但是對于A=a OR B=b，由于是 或 的關系，因而組合索引是不起作用的，此時可以使用單獨索引，這個時候，兩個索引可以同時起作用。

在建立聯合索引的時候，如何安排索引內的字段順序？

? ? 評估標準是：索引的復用能力。因為可以支持最左前綴，所以當已經有了(a,b)這個聯合索引后，一般就不需要單獨在a上建立索引了。

? ? 因此，第一原則是，如果通過調整順序，可以少維護一個索引，那么這個順序往往就是需要優先考慮采用的。

? ? 那么，如果既有聯合查詢，又有基于a、b各自的查詢呢？查詢條件里面只有b的語句，是無法使用(a,b)這個聯合索引的，這時候你不得不維護另外一個索引，也就是說你需要同時維護(a,b)、(b) 這兩個索引。

? ? 這時候，我們要考慮的原則就是空間了。也就是說，如果b的大小是比較小的，如boolean、int類型， 那么可以再多建立一個b索引

下面通過一個例子來加深理解

假設有這么一個表：

CREATE TABLE `geek` (
? `a` int(11) NOT NULL,
? `b` int(11) NOT NULL,
? `c` int(11) NOT NULL,
? `d` int(11) NOT NULL,
? PRIMARY KEY (`a`,`b`),
? KEY `c` (`c`),
? KEY `ca` (`c`,`a`),
? KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

有以下經常使用的查詢語句
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

這里我們需要思考，ca與cb索引是否都是必要的？

? ? 索引 ca 的組織是先按c排序，再按a排序，同時記錄主鍵(b)，根據最左前綴原則，實際上，ca索引的功能同c索引的功能是差不多的，因此可以得出ca索引不是必要的

? ? 索引 cb 的組織是先按c排序，再按b排序，同時記錄主鍵(a)，因此該索引需要保留

假如有如下數據表：

對于表中每一行數據，索引中包含了last_name、first_name、dob列的值，下圖展示了索引是如何組織數據存儲的。

可以看到，索引首先根據第一個字段來排列順序，當名字相同時，則根據第三個字段，即出生日期來排序，正是因為這個原因，才有了索引的“最左原則”。

在介紹這兩者的區別之前，我們先來介紹change buffer：

什么是change buffer？
? ? 當需要更新一個記錄，就是要更新一個數據頁：

? ? 1. 如果數據頁在內存中（buffer pool中時）就直接更新
? ? 2. 如果這個數據頁還沒有在內存中（沒有在buffer pool中）。InooDB 會將這些更新操作緩存在 change buffer 中。在下次查詢需要訪問這個數據頁時，將數據頁讀入內存，然后執行 change buffer 中與這個頁有關的操作
? ? 將 change buffer 中的操作應用到原數據頁，得到最新結果的過程稱為 merge，它可以避免大量的磁盤隨機訪問I/O，merge的流程如下（并不會直接把數據寫會磁盤）：

? ? ? ? ? ?1、從磁盤讀入數據頁到內存（老版本的數據頁）；

? ? ? ? ? ?2、從change buffer里找出這個數據頁的change buffer 記錄(可能有多個），依次應用，得到新版數據頁；

? ? ? ? ? ?3、寫redo log。這個redo log包含了數據的變更和change buffer的變更。

? ? 而唯一索引的更新就不能使用 change buffer：對于唯一索引，所有的更新操作都要先判斷這個操作是否違反唯一性約束。那么必須將數據頁讀入內存才能判斷。比如，要插入(4,400) 這個記錄，就要先判斷現在表中是否已經存在 k=4 的記錄

? ? 都已經讀入內存中了，那直接更新內存會更快，沒有必要使用change buffer了。

? ? 所以，只有普通索引才能使用change buffer，考慮使用普通索引還是唯一索引，如果能保證不會數據重復，那么最好使用普通索引（可以使用change buffer，且兩類索引查詢能力沒有區別）

注意：不是所有的場景用change buffer都能加速：

? ? 1. 設想一個對于寫多讀少的業務來說，change buffer 記錄的變更越多越劃算，例如賬單類日志類

? ? 2. 反過來，一個業務的更新模式是寫入之后馬上會做查詢，change buffer里的內容不多，由于馬上做查詢要訪問數據頁，這樣的io次數不會減少

如果某次寫入使用了change buffer機制，之后主機異常重啟，是否會丟失change buffer和數據？

? ? 雖然寫入時只更新了內存，但是在事務提交的時候，change buffer的操作也會記錄到redo log，所以崩潰恢復的時候，change buffer也能找回來，即數據可以找回來。

所以普通索引和唯一索引，應該怎么選擇？

? ? 查詢時：兩種索引查詢性能幾乎沒差別

? ? 更新時：大部分場景下，因為有change buffer的存在，普通索引的更新速度會比唯一索引的快（特別適用于寫多讀少的場景）（如果所有的更新后面，都馬上伴隨著對這個記錄的查詢，那么應該關閉change buffer）