摘要：數(shù)據(jù)庫使用索引以找到特定值，然后順指針找到包含該值的行。唯一性索引保證在索引列中的全部數(shù)據(jù)是唯一的，對聚簇索引和非聚簇索引都可以使用。允許有重復記錄的聚簇索引非聚簇索引。預告本周五將更新面試數(shù)據(jù)庫的面試題，敬請期待。

你好，是我琉憶，PHP程序員面試筆試系列圖書的作者。

本周（2019.3.4至3.8）的一三五更新的文章如下：

周一：PHP面試MySQL數(shù)據(jù)庫的基礎知識
周三：PHP面試MySQL數(shù)據(jù)庫的索引
周五：PHP面試MySQL數(shù)據(jù)庫的面試真題

自己整理了一篇“索引有哪些優(yōu)缺點和使用原則？”的文章，關注公眾號：“琉憶編程庫”，回復：“索引”，我發(fā)給你。

以下內(nèi)容部分來自《PHP程序員面試筆試寶典》如需轉(zhuǎn)載請注明出處。

索引是一種多帶帶的、物理的對數(shù)據(jù)庫表中一列或多列的值進行排序的存儲結(jié)構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。索引的作用相當于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。它主要提供指向存儲在表的指定列中的數(shù)據(jù)值的指針，然后根據(jù)指定的排序順序?qū)@些指針排序。數(shù)據(jù)庫使用索引以找到特定值，然后順指針找到包含該值的行。這樣可以使對應于表的SQL語句執(zhí)行得更快，可快速訪問數(shù)據(jù)庫表中的特定信息。
索引的特點如下：①可以提高數(shù)據(jù)庫的檢索速度；②降低了數(shù)據(jù)庫插入、修改、刪除等維護任務的速度；③可以直接或間接創(chuàng)建；④只能創(chuàng)建在表上，不能創(chuàng)建在視圖上；⑤使用查詢處理器執(zhí)行SQL語句時，一個表上，一次只能使用一個索引；⑥可以在優(yōu)化隱藏中使用索引。

索引的分類和使用如下：
1．直接創(chuàng)建索引和間接創(chuàng)建索引
直接創(chuàng)建索引：CREATE INDEX mycolumn_index ON mytable (myclumn)。
間接創(chuàng)建索引：定義主鍵約束或者唯一性鍵約束，可以間接創(chuàng)建索引。

2．普通索引和唯一性索引
普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)。
唯一性索引：保證在索引列中的全部數(shù)據(jù)是唯一的，對聚簇索引和非聚簇索引都可以使用。
CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

3．單個索引和復合索引
單個索引：即非復合索引。
復合索引：又稱為組合索引，在索引建立語句中同時包含多個字段名，最多16個字段。

CREATE INDEX name_index ON username(firstname,lastname)

4．聚簇索引和非聚簇索引（聚集索引，群集索引）
聚簇索引：物理索引，與基表的物理順序相同，數(shù)據(jù)值的順序總是按照順序排列。
CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
ALLOW_DUP_ROW(允許有重復記錄的聚簇索引)

非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)。

索引的目的在于提高查詢效率，與我們查閱圖書所用的目錄是一個道理：先定位到章，然后定位到該章下的一個小節(jié)，然后找到頁數(shù)。相似的例子還有：查字典，查火車車次，飛機航班等

本質(zhì)都是：通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果，同時把隨機的事件變成順序的事件，也就是說，有了這種索引機制，我們可以總是用同一種查找方式來鎖定數(shù)據(jù)。

數(shù)據(jù)庫也是一樣，但顯然要復雜的多，因為不僅面臨著等值查詢，還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。數(shù)據(jù)庫應該選擇怎么樣的方式來應對所有的問題呢？我們回想字典的例子，能不能把數(shù)據(jù)分成段，然后分段查詢呢？最簡單的如果1000條數(shù)據(jù)，1到100分成第一段，101到200分成第二段，201到300分成第三段......這樣查第250條數(shù)據(jù)，只要找第三段就可以了，一下子去除了90%的無效數(shù)據(jù)。但如果是1千萬的記錄呢，分成幾段比較好？稍有算法基礎的同學會想到搜索樹，其平均復雜度是lgN，具有不錯的查詢性能。但這里我們忽略了一個關鍵的問題，復雜度模型是基于每次相同的操作成本來考慮的。而數(shù)據(jù)庫實現(xiàn)比較復雜，一方面數(shù)據(jù)是保存在磁盤上的，另外一方面為了提高性能，每次又可以把部分數(shù)據(jù)讀入內(nèi)存來計算，因為我們知道訪問磁盤的成本大概是訪問內(nèi)存的十萬倍左右，所以簡單的搜索樹難以滿足復雜的應用場景。

自己整理了一篇“索引有哪些優(yōu)缺點和使用原則？”的文章，關注公眾號：“琉憶編程庫”，回復：“索引”，我發(fā)給你。

任何一種數(shù)據(jù)結(jié)構都不是憑空產(chǎn)生的，一定會有它的背景和使用場景，我們現(xiàn)在總結(jié)一下，我們需要這種數(shù)據(jù)結(jié)構能夠做些什么，其實很簡單，那就是：每次查找數(shù)據(jù)時把磁盤IO次數(shù)控制在一個很小的數(shù)量級，最好是常數(shù)數(shù)量級。那么我們就想到如果一個高度可控的多路搜索樹是否能滿足需求呢？就這樣，b+樹應運而生。

如上圖，是一顆b+樹，關于b+樹的定義可以參見B+樹，這里只說一些重點，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數(shù)據(jù)項（深藍色所示）和指針（黃色所示），如磁盤塊1包含數(shù)據(jù)項17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。真實的數(shù)據(jù)存在于葉子節(jié)點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節(jié)點只不存儲真實的數(shù)據(jù)，只存儲指引搜索方向的數(shù)據(jù)項，如17、35并不真實存在于數(shù)據(jù)表中。

如圖所示，如果要查找數(shù)據(jù)項29，那么首先會把磁盤塊1由磁盤加載到內(nèi)存，此時發(fā)生一次IO，在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內(nèi)存時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO，同時內(nèi)存中做二分查找找到29，結(jié)束查詢，總計三次IO。真實的情況是，3層的b+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個數(shù)據(jù)項都要發(fā)生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。

1.索引字段要盡量的?。?/strong>通過上面的分析，我們知道IO次數(shù)取決于b+數(shù)的高度h，假設當前數(shù)據(jù)表的數(shù)據(jù)為N，每個磁盤塊的數(shù)據(jù)項的數(shù)量是m，則有h=㏒(m+1)N，當數(shù)據(jù)量N一定的情況下，m越大，h越?。欢鴐 = 磁盤塊的大小 / 數(shù)據(jù)項的大小，磁盤塊的大小也就是一個數(shù)據(jù)頁的大小，是固定的，如果數(shù)據(jù)項占的空間越小，數(shù)據(jù)項的數(shù)量越多，樹的高度越低。這就是為什么每個數(shù)據(jù)項，即索引字段要盡量的小，比如int占4字節(jié)，要比bigint8字節(jié)少一半。這也是為什么b+樹要求把真實的數(shù)據(jù)放到葉子節(jié)點而不是內(nèi)層節(jié)點，一旦放到內(nèi)層節(jié)點，磁盤塊的數(shù)據(jù)項會大幅度下降，導致樹增高。當數(shù)據(jù)項等于1時將會退化成線性表。
2.索引的最左匹配特性（即從左往右匹配）：當b+樹的數(shù)據(jù)項是復合的數(shù)據(jù)結(jié)構，比如(name,age,sex)的時候，b+數(shù)是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數(shù)據(jù)來檢索的時候，b+樹會優(yōu)先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(20,F)這樣的沒有name的數(shù)據(jù)來的時候，b+樹就不知道下一步該查哪個節(jié)點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢。比如當(張三,F)這樣的數(shù)據(jù)來檢索時，b+樹可以用name來指定搜索方向，但下一個字段age的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了， 這個是非常重要的性質(zhì)，即索引的最左匹配特性。

更多PHP相關的面試知識、考題可以關注公眾號獲?。?strong>琉憶編程庫

對本文有什么問題或建議都可以進行留言，我將不斷完善追求極致，感謝你們的支持。