本節(jié)課內(nèi)容：

1.什么是表設(shè)計(jì)的第一、第二、第三范式?
2.什么叫反范式化設(shè)計(jì)?
3.工作中的反范式實(shí)踐
4.InnoDB中的聚集索引和輔助索引
5.什么是回表和MRR?
6. InnoDB中的AHI自適應(yīng)哈希索引
7. InnoDB中的全文索引
8.面試題:什么是密集索引和稀疏索引?
9.辨析覆蓋索引/索引覆蓋
10.高性能的索引創(chuàng)建策略
11.索引選擇性和前綴索引
12.面試題:什么是三星索引?
13.高性能的索引維護(hù)實(shí)踐

1. 數(shù)據(jù)庫(kù)表設(shè)計(jì)

在數(shù)據(jù)庫(kù)表設(shè)計(jì)上有個(gè)很重要的設(shè)計(jì)準(zhǔn)則，稱(chēng)為范式設(shè)計(jì)。

1.1 范式化設(shè)計(jì)

1.1.1 什么是范式？

范式來(lái)自英文Normal Form，簡(jiǎn)稱(chēng)NF。MySQL是關(guān)系型數(shù)據(jù)庫(kù)，但是要想設(shè)計(jì)—個(gè)好的關(guān)系，必須使關(guān)系滿(mǎn)足一定的約束條件，此約束已經(jīng)形成了規(guī)范，分成幾個(gè)等級(jí)，一級(jí)比一級(jí)要求得嚴(yán)格。滿(mǎn)足這些規(guī)范的數(shù)據(jù)庫(kù)是簡(jiǎn)潔的、結(jié)構(gòu)明晰的，同時(shí)，不會(huì)發(fā)生插入(insert)、刪除(delete)和更新(update)操作異常。反之則是亂七八糟，不僅給數(shù)據(jù)庫(kù)的編程人員制造麻煩，而且面目可憎，可能存儲(chǔ)了大量不需要的冗余信息。

目前關(guān)系數(shù)據(jù)庫(kù)有六種范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又稱(chēng)完美范式）。滿(mǎn)足最低要求的范式是第一范式（1NF）。在第一范式的基礎(chǔ)上進(jìn)一步滿(mǎn)足更多規(guī)范要求的稱(chēng)為第二范式（2NF），其余范式以次類(lèi)推。一般來(lái)說(shuō)，數(shù)據(jù)庫(kù)只需滿(mǎn)足第三范式(3NF）就行了。

1.1.2 第一范式（1NF）

1、每一列屬性都是不可再分的屬性值,確保每一列的原子性;
2、兩列的屬性相近或相似或一樣，盡量合并屬性一樣的列，確保不產(chǎn)生冗余數(shù)據(jù);
3、單一屬性的列為基本數(shù)據(jù)類(lèi)型構(gòu)成;
4、設(shè)計(jì)出來(lái)的表都是簡(jiǎn)單的二維表。

定義： 屬于第一范式關(guān)系的所有屬性都不可再分，即數(shù)據(jù)項(xiàng)不可分。
理解： 第一范式強(qiáng)調(diào)數(shù)據(jù)表的原子性，是其他范式的基礎(chǔ)

只第一范式來(lái)規(guī)范表格是遠(yuǎn)遠(yuǎn)不夠的，依然會(huì)存在數(shù)據(jù)冗余過(guò)大、刪除異常、插入異常、修改異常的問(wèn)題，此時(shí)就需要引入規(guī)范化概念，將其轉(zhuǎn)化為更標(biāo)準(zhǔn)化的表格，減少數(shù)據(jù)依賴(lài)。

1.1.2 第二范式（2NF）

1. 第二范式（2NF）是在第一范式（1NF）的基礎(chǔ)上建立起來(lái)的，即滿(mǎn)足第二范式（2NF）必須先滿(mǎn)足第一范式（1NF）。
2. 第二范式(2NF）要求實(shí)體的屬性完全依賴(lài)于主關(guān)鍵字。所謂完全依賴(lài)是指不能存在僅依賴(lài)主關(guān)鍵字一部分的屬性，如果存在，那么這個(gè)屬性和主關(guān)鍵字的這一部分應(yīng)該分離出來(lái)形成一個(gè)新的實(shí)體，新實(shí)體與原實(shí)體之間是一對(duì)多的關(guān)系

第二范式（2NF）要求數(shù)據(jù)庫(kù)表中的每個(gè)實(shí)例或行必須可以被惟一地區(qū)分。通常在實(shí)現(xiàn)來(lái)說(shuō)，需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的惟一標(biāo)識(shí)。例如員工信息表中加上了員工編號(hào)（emp_id）列，因?yàn)槊總€(gè)員工的員工編號(hào)是惟一的，因此每個(gè)員工可以被惟一區(qū)分。這個(gè)惟一屬性列被稱(chēng)為主關(guān)鍵字或主鍵、主碼。

也就是說(shuō)要求表中只具有一個(gè)業(yè)務(wù)主鍵，而且第二范式（2NF）要求實(shí)體的屬性完全依賴(lài)于主關(guān)鍵字。所謂完全依賴(lài)是指不能存在僅依賴(lài)主關(guān)鍵字一部分的屬性。

1.1.3 第三范式（3NF）

指每一個(gè)非主屬性既不部分依賴(lài)于也不傳遞依賴(lài)于業(yè)務(wù)主鍵，也就是在第二范式的基礎(chǔ)上消除了非主鍵對(duì)主鍵的傳遞依賴(lài)。例如，存在一個(gè)部門(mén)信息表，其中每個(gè)部門(mén)有部門(mén)編號(hào)（dept_id）、部門(mén)名稱(chēng)、部門(mén)簡(jiǎn)介等信息。那么在員工信息表中列出部門(mén)編號(hào)后就不能再將部門(mén)名稱(chēng)、部門(mén)簡(jiǎn)介等與部門(mén)有關(guān)的信息再加入員工信息表中。如果不存在部門(mén)信息表，則根據(jù)第三范式（3NF）也應(yīng)該構(gòu)建它，否則就會(huì)有大量的數(shù)據(jù)冗余。

其中
產(chǎn)品 ID與訂單編號(hào)存在關(guān)聯(lián)關(guān)系
產(chǎn)品名稱(chēng)與訂單編號(hào)存在關(guān)聯(lián)關(guān)系
產(chǎn)品ID與產(chǎn)品名稱(chēng)存在關(guān)聯(lián)關(guān)系
訂單表里如果如果產(chǎn)品ID發(fā)生改變，同一個(gè)表里產(chǎn)品名稱(chēng)也要跟著改變，這樣不符合第三范式，應(yīng)該把產(chǎn)品名稱(chēng)這一列從訂單表中刪除。

范式說(shuō)明
真正的數(shù)據(jù)庫(kù)范式定義上，相當(dāng)難懂，比如第二范式（2NF）的定義“若某關(guān)系R屬于第一范式，且每一個(gè)非主屬性完全函數(shù)依賴(lài)于任何一個(gè)候選碼，則關(guān)系R屬于第二范式?！?，這里面有著大堆專(zhuān)業(yè)術(shù)語(yǔ)的堆疊，比如“函數(shù)依賴(lài)”、“碼”、“非主屬性”、與“完全函數(shù)依賴(lài)”等等，而且有完備的公式定義，需要仔細(xì)研究

1.2 反范式設(shè)計(jì)

1.2.1 什么叫反范式化設(shè)計(jì)

完全符合范式化的設(shè)計(jì)真的完美無(wú)缺嗎？很明顯在實(shí)際的業(yè)務(wù)查詢(xún)中會(huì)大量存在著表的關(guān)聯(lián)查詢(xún)，而大量的表關(guān)聯(lián)很多的時(shí)候非常影響查詢(xún)的性能。

所謂得反范式化就是為了性能和讀取效率得考慮而適當(dāng)?shù)脤?duì)數(shù)據(jù)庫(kù)設(shè)計(jì)范式得要求進(jìn)行違反。允許存在少量得冗余，換句話(huà)來(lái)說(shuō)反范式化就是使用空間來(lái)?yè)Q取時(shí)間。

反范式設(shè)計(jì)-商品信息
下面是范式設(shè)計(jì)的商品信息表

商品信息和分類(lèi)信息經(jīng)常一起查詢(xún)，所以把分類(lèi)信息也放到商品表里面，冗余存放

1.3 范式化和反范式總結(jié)

1.3.1 范式化設(shè)計(jì)優(yōu)缺點(diǎn)

1、范式化的更新操作通常比反范式化要快。
2、當(dāng)數(shù)據(jù)較好地范式化時(shí)，就只有很少或者沒(méi)有重復(fù)數(shù)據(jù)，所以只需要修改更少的數(shù)據(jù)。
3、范式化的表通常更小，可以更好地放在內(nèi)存里，所以執(zhí)行操作會(huì)更快。
4、很少有多余的數(shù)據(jù)意味著檢索列表數(shù)據(jù)時(shí)更少需要DISTINCT或者GROUP BY語(yǔ)句。在非范式化的結(jié)構(gòu)中必須使用DISTINCT或者GROUPBY才能獲得一份唯一的列表，但是如果是一張單獨(dú)的表，很可能則只需要簡(jiǎn)單的查詢(xún)這張表就行了。
范式化設(shè)計(jì)的缺點(diǎn)是通常需要關(guān)聯(lián)。稍微復(fù)雜一些的查詢(xún)語(yǔ)句在符合范式的表上都可能需要至少一次關(guān)聯(lián)，也許更多。這不但代價(jià)昂貴，也可能使一些索引策略無(wú)效。例如，范式化可能將列存放在不同的表中，而這些列如果在一個(gè)表中本可以屬于同一個(gè)索引。

1.3.2 反范式化設(shè)計(jì)優(yōu)缺點(diǎn)

1、反范式設(shè)計(jì)可以減少表的關(guān)聯(lián)
2、可以更好的進(jìn)行索引優(yōu)化。
反范式設(shè)計(jì)缺點(diǎn)也很明顯，
1、存在數(shù)據(jù)冗余及數(shù)據(jù)維護(hù)異常，
2、對(duì)數(shù)據(jù)的修改需要更多的成本。

實(shí)際工作中的反范式實(shí)現(xiàn)
冗余、緩存、匯總（實(shí)時(shí)還是定時(shí)，根據(jù)需求的實(shí)時(shí)性來(lái)）

計(jì)數(shù)器表（寫(xiě)熱點(diǎn)的分散）

搜索引擎（新增 + 查詢(xún)）

1.3.3 性能提升-緩存和匯總

范式化和反范式化的各有優(yōu)劣，怎么選擇最佳的設(shè)計(jì)?

而現(xiàn)實(shí)也是，完全的范式化和完全的反范式化設(shè)計(jì)都是實(shí)驗(yàn)室里才有的東西，在真實(shí)世界中很少會(huì)這么極端地使用。在實(shí)際應(yīng)用中經(jīng)常需要混用。
最常見(jiàn)的反范式化數(shù)據(jù)的方法是復(fù)制或者緩存，在不同的表中存儲(chǔ)相同的特定列。
比如從父表冗余一些數(shù)據(jù)到子表的。前面我們看到的分類(lèi)信息放到商品表里面進(jìn)行冗余存放就是典型的例子。
緩存衍生值也是有用的。如果需要顯示每個(gè)用戶(hù)發(fā)了多少消息，可以每次執(zhí)行一個(gè)對(duì)用戶(hù)發(fā)送消息進(jìn)行count的子查詢(xún)來(lái)計(jì)算并顯示它，也可以在user表用戶(hù)中建一個(gè)消息發(fā)送數(shù)目的專(zhuān)門(mén)列，每當(dāng)用戶(hù)發(fā)新消息時(shí)更新這個(gè)值。
有需要時(shí)創(chuàng)建一張完全獨(dú)立的匯總表或緩存表也是提升性能的好辦法。

“緩存表”來(lái)表示存儲(chǔ)那些可以比較簡(jiǎn)單地從其他表獲?。ǖ敲看潍@取的速度比較慢）數(shù)據(jù)的表（例如，邏輯上冗余的數(shù)據(jù))。而“匯總表”時(shí),則保存的是使用GROUP BY語(yǔ)句聚合數(shù)據(jù)的表。
在使用緩存表和匯總表時(shí)，有個(gè)關(guān)鍵點(diǎn)是如何維護(hù)緩存表和匯總表中的數(shù)據(jù)，常用的有兩種方式，實(shí)時(shí)維護(hù)數(shù)據(jù)和定期重建，這個(gè)取決于應(yīng)用程序，不過(guò)一般來(lái)說(shuō)，緩存表用實(shí)時(shí)維護(hù)數(shù)據(jù)更多點(diǎn)，往往在一個(gè)事務(wù)中同時(shí)更新數(shù)據(jù)本表和緩存表，匯總表則用定期重建更多，使用定時(shí)任務(wù)對(duì)匯總表進(jìn)行更新。

1.3.4 性能提升-計(jì)數(shù)器表

計(jì)數(shù)器表在Web應(yīng)用中很常見(jiàn)。比如網(wǎng)站點(diǎn)擊數(shù)、用戶(hù)的朋友數(shù)、文件下載次數(shù)等。對(duì)于高并發(fā)下的處理，首先可以創(chuàng)建一張獨(dú)立的表存儲(chǔ)計(jì)數(shù)器，這樣可使計(jì)數(shù)器表小且快，并且可以使用一些更高級(jí)的技巧。

比如假設(shè)有一個(gè)計(jì)數(shù)器表，只有一行數(shù)據(jù)，記錄網(wǎng)站的點(diǎn)擊次數(shù)，網(wǎng)站的每次點(diǎn)擊都會(huì)導(dǎo)致對(duì)計(jì)數(shù)器進(jìn)行更新，問(wèn)題在于，對(duì)于任何想要更新這一行的事務(wù)來(lái)說(shuō)，這條記錄上都有一個(gè)全局的互斥鎖(mutex)。這會(huì)使得這些事務(wù)只能串行執(zhí)行，會(huì)嚴(yán)重限制系統(tǒng)的并發(fā)能力。

怎么改進(jìn)呢？可以將計(jì)數(shù)器保存在多行中，每次隨機(jī)選擇一行進(jìn)行更新。在具體實(shí)現(xiàn)上，可以增加一個(gè)槽（slot)字段，然后預(yù)先在這張表增加100行或者更多數(shù)據(jù)，當(dāng)對(duì)計(jì)數(shù)器更新時(shí)，選擇一個(gè)隨機(jī)的槽（slot)進(jìn)行更新即可。
這種解決思路其實(shí)就是寫(xiě)熱點(diǎn)的分散，在JDK的JDK1.8中新的原子類(lèi)LongAdder也是這種處理方式，而我們?cè)趯?shí)際的緩沖中間件Redis等的使用、架構(gòu)設(shè)計(jì)中，可以采用這種寫(xiě)熱點(diǎn)的分散的方式，當(dāng)然架構(gòu)設(shè)計(jì)中對(duì)于寫(xiě)熱點(diǎn)還有削峰填谷的處理方式，這種在MySQL的實(shí)現(xiàn)中也有體現(xiàn)，我們后面會(huì)講到。

1.3.5 反范式設(shè)計(jì)-分庫(kù)分表中的查詢(xún)

例如,用戶(hù)購(gòu)買(mǎi)了商品,需要將交易記錄保存下來(lái),那么如果按照買(mǎi)家的緯度分表,則每個(gè)買(mǎi)家的交易記錄都被保存在同一表中, 我們可以很快、 很方便地査到某個(gè)買(mǎi)家的購(gòu)買(mǎi)情況, 但是某個(gè)商品被購(gòu)買(mǎi)的交易數(shù)據(jù)很有可能分布在多張表中, 査找起來(lái)比較麻煩 。 反之, 按照商品維度分表, 則可以很方便地査找到該商品的購(gòu)買(mǎi)情況, 但若要査找到買(mǎi)家的交易記錄, 則會(huì)比較麻煩 。
所以常見(jiàn)的解決方式如下。
( 1 ) 在多個(gè)分片表查詢(xún)后合并數(shù)據(jù)集, 這種方式的效率很低。
( 2 ) 記錄兩份數(shù)據(jù), 一份按照買(mǎi)家緯度分表, 一份按照商品維度分表,
( 3 ) 通過(guò)搜索引擎解決, 但如果實(shí)時(shí)性要求很高, 就需要實(shí)現(xiàn)實(shí)時(shí)搜索
在某電商交易平臺(tái)下, 可能有買(mǎi)家査詢(xún)自己在某一時(shí)間段的訂單, 也可能有賣(mài)家査詢(xún)自已在某一時(shí)間段的訂單, 如果使用了分庫(kù)分表方案, 則這兩個(gè)需求是難以滿(mǎn)足的, 因此, 通用的解決方案是, 在交易生成時(shí)生成一份按照買(mǎi)家分片的數(shù)據(jù)副本和一份按照賣(mài)家分片的數(shù)據(jù)副本,查詢(xún)時(shí)分別滿(mǎn)足之前的兩個(gè)需求,因此,查詢(xún)的數(shù)據(jù)和交易的數(shù)據(jù)可能是分別存儲(chǔ)的,并從不同的系統(tǒng)提供接口。

總結(jié)經(jīng)驗(yàn)

1.實(shí)際設(shè)計(jì)中，先按照3范式設(shè)計(jì)，盡可能去通過(guò)設(shè)計(jì)索引和優(yōu)化sql，如果逐漸發(fā)現(xiàn)性能跟不上了，開(kāi)始考慮做反范式化設(shè)計(jì)。也就是，盡可能遵循范式化設(shè)計(jì)，當(dāng)范式化設(shè)計(jì)影響到性能的時(shí)候毫不猶豫啟動(dòng)反范式化設(shè)計(jì)。
2.反范式化的核心：空間換時(shí)間

2. 高性能索引

InnoDB中的索引是按照B+樹(shù)來(lái)組織的，我們知道B+樹(shù)的葉子節(jié)點(diǎn)用來(lái)放數(shù)據(jù)的，但是放什么數(shù)據(jù)呢？索引自然是要放的，因?yàn)锽+樹(shù)的作用本來(lái)就是為了快速檢索數(shù)據(jù)而提出的一種數(shù)據(jù)結(jié)構(gòu)，不放索引放什么呢？但是數(shù)據(jù)庫(kù)中的表，數(shù)據(jù)才是我們真正需要的數(shù)據(jù)，索引只是輔助數(shù)據(jù)，甚至于一個(gè)表可以沒(méi)有自定義索引。InnoDB中的數(shù)據(jù)到底是如何組織的？

2.1 聚集索引/聚簇索引

InnoDB中使用了聚集索引，就是將表的主鍵用來(lái)構(gòu)造一棵B+樹(shù)，并且將整張表的行記錄數(shù)據(jù)存放在該B+樹(shù)的葉子節(jié)點(diǎn)中。也就是所謂的索引即數(shù)據(jù)，數(shù)據(jù)即索引。由于聚集索引是利用表的主鍵構(gòu)建的，所以每張表只能擁有一個(gè)聚集索引。

聚集索引的葉子節(jié)點(diǎn)就是數(shù)據(jù)頁(yè)。換句話(huà)說(shuō)，數(shù)據(jù)頁(yè)上存放的是完整的每行記錄。因此聚集索引的一個(gè)優(yōu)點(diǎn)就是：通過(guò)過(guò)聚集索引能獲取完整的整行數(shù)據(jù)。另一個(gè)優(yōu)點(diǎn)是：對(duì)于主鍵的排序查找和范圍查找速度非?？?。
如果我們沒(méi)有定義主鍵呢？MySQL會(huì)使用唯一性索引，沒(méi)有唯一性索引，MySQL也會(huì)創(chuàng)建一個(gè)隱含列RowID來(lái)做主鍵，然后用這個(gè)主鍵來(lái)建立聚集索引。

2.2 輔助索引/二級(jí)索引

上邊介紹的聚簇索引只能在搜索條件是主鍵值時(shí)才能發(fā)揮作用，因?yàn)锽+樹(shù)中的數(shù)據(jù)都是按照主鍵進(jìn)行排序的,那如果我們想以別的列作為搜索條件怎么辦？我們一般會(huì)建立多個(gè)索引，這些索引被稱(chēng)為輔助索引/二級(jí)索引。
對(duì)于輔助索引(Secondary Index，也稱(chēng)二級(jí)索引、非聚集索引)，葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)。葉子節(jié)點(diǎn)除了包含鍵值以外，每個(gè)葉子節(jié)點(diǎn)中的索引行中還包含了相應(yīng)行數(shù)據(jù)的聚集索引鍵。

比如輔助索引index(node)，那么葉子節(jié)點(diǎn)中包含的數(shù)據(jù)就包括了(主鍵、note)。

2.3 回表

輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因此每張表上可以有多個(gè)輔助索引。當(dāng)通過(guò)輔助索引來(lái)尋找數(shù)據(jù)時(shí)，InnoDB存儲(chǔ)引擎會(huì)遍歷輔助索引并通過(guò)葉級(jí)別的指針獲得指向主鍵索引的主鍵，然后再通過(guò)主鍵索引（聚集索引）來(lái)找到一個(gè)完整的行記錄。這個(gè)過(guò)程也被稱(chēng)為回表。也就是根據(jù)輔助索引的值查詢(xún)一條完整的用戶(hù)記錄需要使用到2棵B+樹(shù)----一次輔助索引，一次聚集索引。

為什么我們還需要一次回表操作呢?直接把完整的用戶(hù)記錄放到輔助索引d的葉子節(jié)點(diǎn)不就好了么？

如果把完整的用戶(hù)記錄放到葉子節(jié)點(diǎn)是可以不用回表，但是太占地方了，相當(dāng)于每建立一棵B+樹(shù)都需要把所有的用戶(hù)記錄再都拷貝一遍，這就有點(diǎn)太浪費(fèi)存儲(chǔ)空間了。而且每次對(duì)數(shù)據(jù)的變化要在所有包含數(shù)據(jù)的索引中全部都修改一次，性能也非常低下。

很明顯，回表的記錄越少，性能提升就越高，需要回表的記錄越多，使用二級(jí)索引的性能就越低，甚至讓某些查詢(xún)寧愿使用全表掃描也不使用二級(jí)索引。
那什么時(shí)候采用全表掃描的方式，什么時(shí)候使用采用二級(jí)索引 + 回表的方式去執(zhí)行查詢(xún)呢？這個(gè)就是查詢(xún)優(yōu)化器做的工作，查詢(xún)優(yōu)化器會(huì)事先對(duì)表中的記錄計(jì)算一些統(tǒng)計(jì)數(shù)據(jù)，然后再利用這些統(tǒng)計(jì)數(shù)據(jù)根據(jù)查詢(xún)的條件來(lái)計(jì)算一下需要回表的記錄數(shù)，需要回表的記錄數(shù)越多，就越傾向于使用全表掃描，反之傾向于使用二級(jí)索引 + 回表的方式。

2.4 MRR

從上文可以看出，每次從二級(jí)索引中讀取到一條記錄后，就會(huì)根據(jù)該記錄的主鍵值執(zhí)行回表操作。而在某個(gè)掃描區(qū)間中的二級(jí)索引記錄的主鍵值是無(wú)序的，也就是說(shuō)這些二級(jí)索引記錄對(duì)應(yīng)的聚簇索引記錄所在的頁(yè)面的頁(yè)號(hào)是無(wú)序的。
每次執(zhí)行回表操作時(shí)都相當(dāng)于要隨機(jī)讀取一個(gè)聚簇索引頁(yè)面，而這些隨機(jī)IO帶來(lái)的性能開(kāi)銷(xiāo)比較大。MySQL中提出了一個(gè)名為Disk-Sweep Multi-Range Read (MRR，多范圍讀取)的優(yōu)化措施，即先讀取一部分二級(jí)索引記錄，將它們的主鍵值排好序之后再統(tǒng)一執(zhí)行回表操作。

相對(duì)于每讀取一條二級(jí)索引記錄就立即執(zhí)行回表操作，這樣會(huì)節(jié)省一些IO開(kāi)銷(xiāo)。使用這個(gè) MRR優(yōu)化措施的條件比較苛刻，所以我們直接認(rèn)為每讀取一條二級(jí)索引記錄就立即執(zhí)行回表操作。MRR的詳細(xì)信息，可以查詢(xún)官方文檔。

2.5 聯(lián)合索引/復(fù)合索引

前面我們對(duì)索引的描述，隱含了一個(gè)條件，那就是構(gòu)建索引的字段只有一個(gè)，但實(shí)踐工作中構(gòu)建索引的完全可以是多個(gè)字段。所以，將表上的多個(gè)列組合起來(lái)進(jìn)行索引我們稱(chēng)之為聯(lián)合索引或者復(fù)合索引，比如index(a,b)就是將a,b兩個(gè)列組合起來(lái)構(gòu)成一個(gè)索引。
千萬(wàn)要注意一點(diǎn)，建立聯(lián)合索引只會(huì)建立1棵B+樹(shù)，多個(gè)列分別建立索引會(huì)分別以每個(gè)列則建立B+樹(shù)，有幾個(gè)列就有幾個(gè)B+樹(shù)，比如，index(note)、index(b)，就分別對(duì)note,b兩個(gè)列各構(gòu)建了一個(gè)索引。

index(note,b)在索引構(gòu)建上，包含了兩個(gè)意思：
1、先把各個(gè)記錄按照note列進(jìn)行排序。
2、在記錄的note列相同的情況下，采用b列進(jìn)行排序

2.6 自適應(yīng)哈希索引

InnoDB存儲(chǔ)引擎除了我們前面所說(shuō)的各種索引，還有一種自適應(yīng)哈希索引，我們知道B+樹(shù)的查找次數(shù),取決于B+樹(shù)的高度,在生產(chǎn)環(huán)境中,B+樹(shù)的高度一般為3-4層,故需要3~4次的IO查詢(xún)。

所以在InnoDB存儲(chǔ)引擎內(nèi)部自己去監(jiān)控索引表，如果監(jiān)控到某個(gè)索引經(jīng)常用，那么就認(rèn)為是熱數(shù)據(jù)，然后內(nèi)部自己創(chuàng)建一個(gè)hash索引，稱(chēng)之為自適應(yīng)哈希索引( Adaptive Hash Index,AHI)，創(chuàng)建以后，如果下次又查詢(xún)到這個(gè)索引，那么直接通過(guò)hash算法推導(dǎo)出記錄的地址，直接一次就能查到數(shù)據(jù)，比重復(fù)去B+tree索引中查詢(xún)?nèi)拇喂?jié)點(diǎn)的效率高了不少。

InnoDB存儲(chǔ)引擎使用的哈希函數(shù)采用除法散列方式，其沖突機(jī)制采用鏈表方式。注意，對(duì)于自適應(yīng)哈希索引僅是數(shù)據(jù)庫(kù)自身創(chuàng)建并使用的，我們并不能對(duì)其進(jìn)行干預(yù)。通過(guò)命令show engine innodb status\G可以看到當(dāng)前自適應(yīng)哈希索引的使用狀況，如:

哈希索引只能用來(lái)搜索等值的查詢(xún),如 SELECT* FROM table WHERE index co=xxx。而對(duì)于其他查找類(lèi)型,如范圍查找,是不能使用哈希索引的,
因此這里會(huì)顯示non- hash searches/s的統(tǒng)計(jì)情況。通過(guò) hash searches: non-hash searches可以大概了解使用哈希索引后的效率。

由于AHI是由 InnoDB存儲(chǔ)引擎控制的,因此這里的信息只供我們參考。不過(guò)我們可以通過(guò)觀察 SHOW ENGINE INNODB STATUS的結(jié)果及參數(shù) innodb_adaptive_hash_index來(lái)考慮是禁用或啟動(dòng)此特性,默認(rèn)AHI為開(kāi)啟狀態(tài)。

什么時(shí)候需要禁用呢？如果發(fā)現(xiàn)監(jiān)視索引查找和維護(hù)哈希索引結(jié)構(gòu)的額外開(kāi)銷(xiāo)遠(yuǎn)遠(yuǎn)超過(guò)了自適應(yīng)哈希索引帶來(lái)的性能提升就需要關(guān)閉這個(gè)功能。

同時(shí)在MySQL 5.7中，自適應(yīng)哈希索引搜索系統(tǒng)被分區(qū)。每個(gè)索引都綁定到一個(gè)特定的分區(qū)，每個(gè)分區(qū)都由一個(gè)單獨(dú)的 latch 鎖保護(hù)。分區(qū)由 innodb_adaptive_hash_index_parts 配置選項(xiàng)控制 。在早期版本中，自適應(yīng)哈希索引搜索系統(tǒng)受到單個(gè) latch 鎖的保護(hù)，這可能成為繁重工作負(fù)載下的爭(zhēng)用點(diǎn)。innodb_adaptive_hash_index_parts 默認(rèn)情況下，該選項(xiàng)設(shè)置為8。最大設(shè)置為512。當(dāng)然禁用或啟動(dòng)此特性和調(diào)整分區(qū)個(gè)數(shù)這個(gè)應(yīng)該是DBA的工作，我們了解即可。

InnoDB引擎有三大特性（這個(gè)是面試的時(shí)候很容易被問(wèn)到的。）

2.7 全文檢索之倒排索引

什么是全文檢索（Full-Text Search）？

它是將存儲(chǔ)于數(shù)據(jù)庫(kù)中的整本書(shū)或整篇文章中的任意內(nèi)容信息查找出來(lái)的技術(shù)。它可以根據(jù)需要獲得全文中有關(guān)章、節(jié)、段、句、詞等信息，也可以進(jìn)行各種統(tǒng)計(jì)和分析。我們比較熟知的Elasticsearch、Solr等就是全文檢索引擎，底層都是基于Apache Lucene的。

舉個(gè)例子，現(xiàn)在我們要保存唐宋詩(shī)詞，數(shù)據(jù)庫(kù)中我們們會(huì)怎么設(shè)計(jì)？詩(shī)詞表我們可能的設(shè)計(jì)如下：

要根據(jù)朝代或者作者尋找詩(shī)，都很簡(jiǎn)單，比如"select 詩(shī)詞全文 from 詩(shī)詞表 where作者=‘李白’"，如果數(shù)據(jù)很多，查詢(xún)速度很慢，怎么辦？

我們可以在對(duì)應(yīng)的查詢(xún)字段上建立索引加速查詢(xún)。

但是如果我們現(xiàn)在有個(gè)需求：要求找到包含"望"字的詩(shī)詞怎么辦？

用“select 詩(shī)詞全文 from 詩(shī)詞表 where詩(shī)詞全文 like’%望%’”，這個(gè)意味著要掃描庫(kù)中的詩(shī)詞全文字段，逐條比對(duì)，找出所有包含關(guān)鍵詞"望"字的記錄。基本上，數(shù)據(jù)庫(kù)中一般的SQL優(yōu)化手段都是用不上的。數(shù)量少，大概性能還能接受，如果數(shù)據(jù)量稍微大點(diǎn)，就完全無(wú)法接受了，更何況在互聯(lián)網(wǎng)這種海量數(shù)據(jù)的情況下呢？怎么解決這個(gè)問(wèn)題呢，用倒排索引。

比如現(xiàn)在有：

蜀道難（唐）李白 蜀道之難難于上青天，側(cè)身西望長(zhǎng)咨嗟。
靜夜思（唐）李白 舉頭望明月，低頭思故鄉(xiāng)。
春臺(tái)望（唐）李隆基 暇景屬三春，高臺(tái)聊四望。
鶴沖天(宋)柳永 黃金榜上，偶失龍頭望。明代暫遺賢，如何向？未遂風(fēng)云便，爭(zhēng)不恣狂蕩。何須論得喪？才子詞人，自是白衣卿相。煙花巷陌，依約丹青屏障。幸有意中人，堪尋訪(fǎng)。且恁偎紅翠，風(fēng)流事，平生暢。青春都一餉。忍把浮名，換了淺斟低唱！

都有望字，于是我們可以這么保存

如果查哪個(gè)詩(shī)詞中包含上，怎么辦，上述的表格可以繼續(xù)填入新的記錄

其實(shí)，上述詩(shī)詞的中每個(gè)字都可以作為關(guān)鍵字，然后建立關(guān)鍵字和文檔之間的對(duì)應(yīng)關(guān)系，也就是標(biāo)識(shí)關(guān)鍵字被哪些文檔包含。

所以，倒排索引就是，將文檔中包含的關(guān)鍵字全部提取處理，然后再將關(guān)鍵字和文檔之間的對(duì)應(yīng)關(guān)系保存起來(lái)，最后再對(duì)關(guān)鍵字本身做索引排序。用戶(hù)在檢索某一個(gè)關(guān)鍵字是，先對(duì)關(guān)鍵字的索引進(jìn)行查找，再通過(guò)關(guān)鍵字與文檔的對(duì)應(yīng)關(guān)系找到所在文檔。

在存儲(chǔ)在關(guān)系型數(shù)據(jù)庫(kù)中的數(shù)據(jù)，需要我們事先分析將數(shù)據(jù)拆分為不同的字段，而在es這類(lèi)的存儲(chǔ)中，需要應(yīng)用程序根據(jù)規(guī)則自動(dòng)提取關(guān)鍵字，并形成對(duì)應(yīng)關(guān)系。

這些預(yù)先提取的關(guān)鍵字，在全文檢索領(lǐng)域一般被稱(chēng)為term（詞項(xiàng)），文檔的詞項(xiàng)提取在es中被稱(chēng)為文檔分析，這是全文檢索很核心的過(guò)程，必須要區(qū)分哪些是詞項(xiàng)，哪些不是，比如很多場(chǎng)景下，apple和apples是同一個(gè)東西，望和看其實(shí)是同一個(gè)動(dòng)作。

2.8 MySQL中的全文索引

MySQL 5.6 以前的版本，只有 MyISAM 存儲(chǔ)引擎支持全文索引。從InnoDB 1.2.x版本開(kāi)始，InnoDB存儲(chǔ)引擎開(kāi)始支持全文檢索，對(duì)應(yīng)的MySQL版本是5.6.x系列。

注意，不管什么引擎，只有字段的數(shù)據(jù)類(lèi)型為 char、varchar、text 及其系列才可以建全文索引。

不過(guò)MySQL從設(shè)計(jì)之初就是關(guān)系型數(shù)據(jù)庫(kù)，存儲(chǔ)引擎雖然支持全文檢索，整體架構(gòu)上對(duì)全文檢索支持并不好而且限制很多，比如每張表只能有一個(gè)全文檢索的索引，不支持沒(méi)有單詞界定符( delimiter）的語(yǔ)言，如中文、日語(yǔ)、韓語(yǔ)等。

所以如果有大批量或者專(zhuān)門(mén)的全文檢索需求，還是應(yīng)該選擇專(zhuān)門(mén)的全文檢索引擎，畢竟Elastic靠著全文檢索起家，然后產(chǎn)品化、公司化后依賴(lài)全文檢索不斷擴(kuò)充產(chǎn)品線(xiàn)和應(yīng)用場(chǎng)景，并推出商業(yè)版本的解決方案然后融資上市，現(xiàn)在的市值已達(dá)100億美元。

具體如何使用InnoDB存儲(chǔ)引擎的全文檢索，只提供簡(jiǎn)單的使用說(shuō)明，更多的詳情請(qǐng)自行查閱相關(guān)官方文檔或者書(shū)籍，官方文檔路徑：https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

創(chuàng)建表時(shí)使用全文索引

創(chuàng)建表時(shí)創(chuàng)建全文索引
create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    tag varchar(255),
    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content,tag)  
) DEFAULT CHARSET=utf8;
在已存在的表上創(chuàng)建全文索引
create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);
通過(guò) SQL 語(yǔ)句 ALTER TABLE 創(chuàng)建全文索引
alter table fulltext_test
    add fulltext index content_tag_fulltext(content,tag);

2.9 總結(jié)：MySQL有哪些索引類(lèi)型

從數(shù)據(jù)結(jié)構(gòu)角度可分為B+樹(shù)索引、哈希索引、以及FULLTEXT索引（現(xiàn)在MyISAM和InnoDB引擎都支持了）和R-Tree索引（用于對(duì)GIS數(shù)據(jù)類(lèi)型創(chuàng)建SPATIAL索引）；

從物理存儲(chǔ)角度可分為聚集索引（clustered index）、非聚集索引（non-clustered index）；

從邏輯角度可分為主鍵索引、普通索引，或者單列索引、多列索引、唯一索引、非唯一索引等等。

面試題：Innodb存儲(chǔ)引擎的三大特性：
自適應(yīng)hash索引、雙寫(xiě)緩存區(qū)、BufferPool

面試題：什么是密集索引和稀疏索引？
密集索引的定義：葉子節(jié)點(diǎn)保存的不只是鍵值，還保存了位于同一行記錄里的其他列的信息，由于密集索引決定了表的物理排列順序，一個(gè)表只有一個(gè)物理排列順序，所以一個(gè)表只能創(chuàng)建一個(gè)密集索引。
稀疏索引：葉子節(jié)點(diǎn)僅保存了鍵位信息以及該行數(shù)據(jù)的地址，有的稀疏索引只保存了鍵位信息機(jī)器主鍵。

mysam存儲(chǔ)引擎，不管是主鍵索引，唯一鍵索引還是普通索引都是稀疏索引，innodb存儲(chǔ)引擎：有且只有一個(gè)密集索引。

所以，密集索引就是innodb存儲(chǔ)引擎里的聚簇索引，稀疏索引就是innodb存儲(chǔ)引擎里的普通二級(jí)索引。

2.10 辨析覆蓋索引/索引覆蓋

既然多個(gè)列可以組合起來(lái)構(gòu)建為聯(lián)合索引，那么輔助索引自然也可以由多個(gè)列組成。

覆蓋索引也是我們經(jīng)常見(jiàn)到的名詞，InnoDB存儲(chǔ)引擎支持覆蓋索引(covering index，或稱(chēng)索引覆蓋)，即從輔助索引中就可以得到查詢(xún)的記錄，而不需要查詢(xún)聚集索引中的記錄。使用覆蓋索引的一個(gè)好處是輔助索引不包含整行記錄的所有信息，故其大小要遠(yuǎn)小于聚集索引，因此可以減少大量的IO操作。所以記住，覆蓋索引可以視為索引優(yōu)化的一種方式，而并不是索引類(lèi)型的一種。

除了覆蓋索引這個(gè)概念外，在索引優(yōu)化的范圍內(nèi)，還有前綴索引、三星索引等一系列概念，都會(huì)在后文補(bǔ)充。

覆蓋索引不是真真正正的索引，屬于索引優(yōu)化的一種方式。把回表這個(gè)動(dòng)作給去除了。

2.11 深入思考索引在查詢(xún)中的使用

索引在查詢(xún)中的作用到底是什么？在我們的查詢(xún)中發(fā)揮著什么樣的作用呢？ 請(qǐng)記?。?/code>

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;