????????索引是應(yīng)用程序設(shè)計(jì)和開發(fā)的一個(gè)重要方面。 若索引太多饺蚊， 應(yīng)用程序的性能可能會(huì)受到影響。 而索引太少包竹， 對(duì)查詢性能又會(huì)產(chǎn)生影響苗缩。 要找到一個(gè)合適的平衡點(diǎn)挤渐， 這對(duì)應(yīng)用程序的性能至關(guān)重要。

? ??????一些開發(fā)人員總是在事后才想起添加索引——我一直認(rèn)為软免， 這源于一種錯(cuò)誤的開發(fā)模式。 如果知道數(shù)據(jù)的使用榛泛， 從一開始就應(yīng)該在需要處添加索引曹锨。 開發(fā)人員往往對(duì)于數(shù)據(jù)庫的使用停留在應(yīng)用的層面齐鲤， 比如編寫 SQL 語句、 存儲(chǔ)過程之類淆九， 他們甚至可能不知道索引的存在， 或者認(rèn)為事后讓相關(guān)OBA加上即可。 OBA往往不夠了解業(yè)務(wù)的數(shù)據(jù)流唧席，而添加索引需要通過監(jiān)控大量的 SQL 語句進(jìn)而從中找到問題， 這個(gè)步驟所需的時(shí)間肯定是遠(yuǎn)大于初始添加索引所需要的時(shí)間徒仓， 并且可能會(huì)遺漏一部分的索引。 當(dāng)然索引也并不是越多越好殃饿。

5.1?lnnoDB 存儲(chǔ)引擎索引概述

?InnoDB 存儲(chǔ)引擎支持以下幾種常見的索引：

1.B+樹索引

2.全文索引

3.哈希索引

????????前面已經(jīng)提到過， lnnoDB 存儲(chǔ)引擎支持的哈希索引是自適應(yīng)的奈惑， lnnoDB 存儲(chǔ)引擎會(huì)根據(jù)表的使用情況自動(dòng)為表生成哈希索引， 不能人為干預(yù)是否在一張表中生成哈希索引雷滋。

? ?????B+樹索引就是傳統(tǒng)意義上的索引焕檬，這是目前關(guān)系型數(shù)據(jù)庫系統(tǒng)中查找最為常用和最為有效的索引。B+樹索引的構(gòu)造類似于二叉樹，根據(jù)鍵值(KeyValue)快速找到數(shù)據(jù)碰辅。?

????????注意B+樹中的B不是代表二叉(binary),而是代表平衡(balance),因?yàn)锽+樹是從最早的平衡二叉樹演化而來，但是B+樹不是一個(gè)二叉樹循衰。? ???

????????另一個(gè)常常被DBA 忽視的問題是： B+ 樹索引并不能找到一個(gè)給定鍵值的具體行。? ?B+ 樹索引能找到的只是被查找數(shù)據(jù)行所在的頁。然后數(shù)據(jù)庫通過把頁讀入到內(nèi)存胁出，再在內(nèi)存中進(jìn)行查找，最后得到要查找的數(shù)據(jù)抑淫。

5.2 數(shù)據(jù)結(jié)構(gòu)與算法

????????????B+ 樹索引是最為常見筐喳，也是在數(shù)據(jù)庫中使用最為頻繁的一種索引。在介紹該索引之前先介紹與之密切相關(guān)的一些算法與數(shù)據(jù)結(jié)構(gòu)荣月，這有助于讀者更好的理解B+ 樹索引的工作方式管呵。

5.2.1 二分查找法

? ??????二分查找法(binary search) 也稱為折半查找法，用來查找一組有序的記錄數(shù)組中的某一記錄哺窄，其基本思想是：將記錄按有序化（遞增或遞減）排列捐下，在查找過程中采用跳躍式方式查找，即先以有序數(shù)列的中點(diǎn)位置為比較對(duì)象萌业，如果要找的元素值小于該中點(diǎn)元素，則將待查序列縮小為左半部分，否則為右半部分侵贵。通過一次比較漱抓，將查找區(qū)間縮小一半。

5.3 B+樹

????????前面討論的都是B+樹的數(shù)據(jù)結(jié)構(gòu)及其一般操作拷肌，B+樹索引的本質(zhì)就是B+樹在數(shù)據(jù)庫中的實(shí)現(xiàn)。但是B+索引在數(shù)據(jù)庫中有一個(gè)特點(diǎn)是高扇出性靶病，因此在數(shù)據(jù)庫中裳涛，B+樹的高度一般都在2-4 層郊闯，這也就是說查找某一鍵值的行記錄時(shí)最多只需要2-4次IO, 這倒不錯(cuò)。因?yàn)楫?dāng)前一般的機(jī)械磁盤每秒至少可以做100 次IO, 2 ~4 次的IO 意味著查詢時(shí)間只需0.02~0.04 秒。

????????數(shù)據(jù)庫中的B+ 樹索引可以分為聚集索引(clustered inex) 和輔助索引(secondaryindex) 9, 但是不管是聚集還是輔助的索引躬它，其內(nèi)部都是B+ 樹的凸舵，即高度平衡的菇夸，葉子節(jié)點(diǎn)存放著所有的數(shù)據(jù)出皇。聚集索引與輔助索引不同的是，葉子節(jié)點(diǎn)存放的是否是一整行的信息煮剧。

5.4.1 聚集索引

? ??????之前已經(jīng)介紹過了， InnoDB 存儲(chǔ)引擎表是索引組織表簿透，即表中數(shù)據(jù)按照主鍵順序存放胶背。而聚集索引(clustered index) 就是按照每張表的主鍵構(gòu)造一棵B+ 樹，同時(shí)葉子節(jié)點(diǎn)中存放的即為整張表的行記錄數(shù)據(jù)斤吐，也將聚集索引的葉子節(jié)點(diǎn)稱為數(shù)據(jù)頁斜纪。聚集索引的這個(gè)特性決定了索引組織表中數(shù)據(jù)也是索引的一部分。同B+ 樹數(shù)據(jù)結(jié)構(gòu)一樣歼冰，每個(gè)數(shù)據(jù)頁都通過一個(gè)雙向鏈表來進(jìn)行鏈接。

????????由于實(shí)際的數(shù)據(jù)頁只能按照一棵B+ 樹進(jìn)行排序全释，因此每張表只能擁有一個(gè)聚集索引。在多數(shù)情況下，查詢優(yōu)化器傾向于采用聚集索引峭判。因?yàn)榫奂饕軌蛟贐+ 樹索引的葉子節(jié)點(diǎn)上直接找到數(shù)據(jù)谨设。此外，由于定義了數(shù)據(jù)的邏輯順序，聚集索引能夠特別快地訪問針對(duì)范圍值的查詢衙传。查詢優(yōu)化器能夠快速發(fā)現(xiàn)某一段范圍的數(shù)據(jù)頁需要掃描。

? ??????聚集索引的存儲(chǔ)并不是物理上連續(xù)的蠢壹，而是邏輯上連續(xù)的制恍。這其中有兩點(diǎn)： 一是前面說過的頁通過雙向鏈表鏈接藻三，頁按照主鍵的順序排序卿城；另一點(diǎn)是每個(gè)頁中的記錄也是通過雙向鏈表進(jìn)行維護(hù)的威始，物理存儲(chǔ)上可以同樣不按照主鍵存儲(chǔ)。

????????聚集索引的另一個(gè)好處是沸停，它對(duì)于主鍵的排序查找和范圍查找速度非巢颍快疗疟。葉子節(jié)點(diǎn)的數(shù)據(jù)就是用戶所要查詢的數(shù)據(jù)赠叼。如用戶需要查詢一張注冊(cè)用戶的表双仍，查詢最后注冊(cè)的10 位用戶失暴，由于B+樹索引是雙向鏈表的，用戶可以快速找到最后一個(gè)數(shù)據(jù)頁，并取出10條記錄

5.4.2 輔助索引

? ??????對(duì)于輔助索引(Secondary Index, 也稱非聚集索引）， 葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)甲脏。 葉子節(jié)點(diǎn)除了包含鍵值以外， 每個(gè)葉子節(jié)點(diǎn)中的索引行中還包含了 個(gè)書簽(bookmark)。 該書簽用來告訴InnoDB存儲(chǔ)引擎哪里可以找到與索引相對(duì)應(yīng)的行數(shù)據(jù)薇正。 由于InnoDB存儲(chǔ)引擎表是索引組織表稳衬， 因此lnnoDB存儲(chǔ)引擎的輔助索引的書簽就是相應(yīng)行數(shù)據(jù)的聚集索引鍵猖辫。 圖5-15顯示了InnoDB存儲(chǔ)引擎中輔助索引與聚集索引的關(guān)系岸晦。

? ??????輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織侍郭， 因此每張表上可以有多個(gè)輔助索引找默。 當(dāng)通過輔助索引來尋找數(shù)據(jù)時(shí)昼窗，lnnoDB存儲(chǔ)引擎會(huì)遍歷輔助索引并通過葉級(jí)別的指針獲得指向主鍵索引的主鍵扣溺，然后再通過主鍵索引來找到一個(gè)完整的行記錄。

? ??????當(dāng)然颓遏，在一些情況下，使用堆表的確會(huì)比索引組織表更快颁井，但是我覺得大部分原因是由于存在 OLAP(On-Line Analy tical Processing, 在線分析處理）的應(yīng)用棒掠。其次就是前面提到的毒坛，表中數(shù)據(jù)是否需要更新度秘，并且更新是否影響到物理地址的變更骂倘。此外另一個(gè)不能忽視的是對(duì)于排序和范圍查找居扒，索引組織表通過B+樹的中間節(jié)點(diǎn)就可以找到要查找的所有頁概漱，然后進(jìn)行讀取，而堆表的特性決定了這對(duì)其是不能實(shí)現(xiàn)的喜喂。最后瓤摧，非聚集索引的離散讀，的確存在上述的情況玉吁，但是一般的數(shù)據(jù)庫都通過實(shí)現(xiàn)預(yù)讀(read ahead) 技術(shù)來避免多次的離散讀操作照弥。因此，具體是建堆表還是索引組織表进副，這取決于應(yīng)用这揣， 不存在哪個(gè)更優(yōu)的問題悔常。這和InnoDB存儲(chǔ)引擎好還是MyISAM存儲(chǔ)引擎好這個(gè)問題的答案是一樣的，It all depends给赞。

5.4.3 B+ 樹索引的分裂

? ??????在5.3節(jié)中介紹 B+樹的分裂是最為簡單的一種情況机打， 這和數(shù)據(jù)庫中 B+ 樹索引的情況可能略有不同。 此外5.3節(jié)頁沒有涉及并發(fā)片迅， 而這才是 B+ 樹索引實(shí)現(xiàn)最為困難的部分残邀。

????????B+ 樹索引頁的分裂并不總是從頁的中間記錄開始， 這樣可能會(huì)導(dǎo)致頁空間的浪費(fèi)柑蛇。例如下面的記錄：1芥挣、 2 、 3耻台、 4 空免、 5 、 6盆耽、 7 鼓蜒、 8、 9

? ??????插入是根據(jù)自增順序進(jìn)行的征字， 若這時(shí)插人10這條記錄后需要進(jìn)行頁的分裂操作都弹， 那么根據(jù)5.3.1節(jié)介紹的分裂方法， 會(huì)將記錄5作為分裂點(diǎn)記錄(split record), 分裂后得到下面兩個(gè)頁：Pl: 1匙姜、 2 畅厢、 3、 4

P2: 5氮昧、6框杜、7、8袖肥、9咪辱、 10

? ??????然而由千插入是順序的，Pl這個(gè)頁中將不會(huì)再有記錄被插人椎组， 從而導(dǎo)致空間的浪費(fèi)油狂。 而P2又會(huì)再次進(jìn)行分裂。

? ??????InnoDB存儲(chǔ)引擎的Page Header中有以下幾個(gè)部分用來保存插入的順序信息：

PAGE_LAST_INSERT

PAGE_DIRECTION

PAGE_N_DIRECTION

? ??????通過這些信息寸癌， InnoDB 存儲(chǔ)引擎可以決定是向左還是向右進(jìn)行分裂专筷， 同時(shí)決定將分裂點(diǎn)記錄為哪一個(gè)。 若插入是隨機(jī)的蒸苇， 則取頁的中間記錄作為分裂點(diǎn)的記錄磷蛹， 這和之前介紹的相同。 若往同一方向進(jìn)行插入的記錄數(shù)量為5, 并且目前已經(jīng)定位(cursor)到的記錄 (InnoDB 存儲(chǔ)引擎插入時(shí)溪烤， 首先需要進(jìn)行定位味咳， 定位到的記錄為待插入記錄的前一條記錄）之后還有3條記錄庇勃， 則分裂點(diǎn)的記錄為定位到的記錄后的第三條記錄， 否則分裂點(diǎn)記錄就是待插人的記錄槽驶。

5.4.4 B+樹索引的管理

1. 索引管理

????????索引的創(chuàng)建和刪除可以通過兩種方法匪凉， 一種是ALTER TABLE, 另一種是CREATE/DROP INDEX。

????????用戶可以設(shè)置對(duì)整個(gè)列的數(shù)據(jù)進(jìn)行索引捺檬， 也可以只索引一個(gè)列的開頭部分?jǐn)?shù)據(jù).若用戶想要查看表中索引的信息再层，可以使用命令SHOW INDEX。

? ??????通過命令SHOWINDEX FROM可以觀察到表t上有4個(gè)索引堡纬，分別為主鍵索引聂受、c列上的輔助索引、b列的前100字節(jié)構(gòu)成的輔助索引烤镐，以及(a蛋济、c)的聯(lián)合輔助索引。接著具體闡述命令SHOWINDEX展現(xiàn)結(jié)果中每列的含義炮叶。

Table: 索引所在的表名碗旅。

Non_unique: 非唯一的索引，可以看到primary key是 o, 因?yàn)楸仨毷俏ㄒ坏摹?/p>

?Key_name: 索引的名字镜悉，用戶可以通過這個(gè) 名字來執(zhí)行 DROP INDEX祟辟。

Seq_in_index: 索引中該 列的位置，如果看聯(lián)合索引idx_a_c就比較直觀了侣肄。 0 Column_name: 索引列的名稱旧困。

Collation : 列以什么方式存儲(chǔ)在索引中〖诠可以是A或NULL吼具。B+樹索引總是A,即排序的。如果使用了Heap 存儲(chǔ)引擎矩距，并且建立了Hash索引拗盒，這里就會(huì)顯示NULL了。因?yàn)镠ash根據(jù)Hash桶存放索引數(shù)據(jù)锥债，而不是對(duì)數(shù)據(jù)進(jìn)行排序陡蝇。

Cardinality: 非常關(guān)鍵的值，表示索引中唯一值的數(shù)目的估計(jì)值赞弥。Cardinality表的行數(shù)應(yīng)盡可能接近1, 如果非常小毅整，那么用戶需要考慮是否 可以刪除此索引。

?Sub_part : 是否是列的部分被索引绽左。如果看idx_b這個(gè)索引，這里顯示100, 表示只對(duì)b列的前100字符進(jìn)行索引艇潭。如果索引整個(gè)列拼窥，則該 字段為NULL戏蔑。

?Packed: 關(guān)鍵字如何被壓縮。如果沒有被壓縮鲁纠，則為NULL总棵。

Null: 是否索引的列含有NULL值「暮可以看到idx_b這里為Yes, 因?yàn)槎x的列b允許NULL值情龄。

Index_ type : 索引的類型。InnoDB存儲(chǔ)引擎只支持B+樹索引捍壤，所以這里顯示的都是 BTREE骤视。

Comment: 注釋。

? ??????Cardinality值非常關(guān)鍵鹃觉，優(yōu)化器會(huì)根據(jù)這個(gè)值來判斷是否使用這個(gè)索引专酗。但是這個(gè)值并不是實(shí)時(shí)更新 的，即并非每次索引的更新都會(huì) 更新該值盗扇，因?yàn)檫@樣代價(jià)太大了祷肯。因此這個(gè)值是不太準(zhǔn)確的只是一個(gè)大概的值。上面顯示的結(jié)果主鍵的Cardinality為2, 但是很顯然我們的表中有4條記錄疗隶，這個(gè)值應(yīng)該 是4佑笋。如果需要更新索引Cardinality 的信息，可以使用ANALYZE TABLE命令斑鼻。

2. Fast Index Creation

? ?????lnnoDB存儲(chǔ)引擎從InnoDB 1.0.x版本開始支持一種稱為FastIndex Creation (快速索引創(chuàng)建）的索引創(chuàng)建方式——簡稱FIC允青。

????????對(duì)于輔助索引的創(chuàng)建，InnoDB存儲(chǔ)引擎會(huì)對(duì)創(chuàng)建索引的表加上一個(gè)S鎖卵沉。在創(chuàng)建的過程中颠锉，不需要重建表， 因此速度較之前提高很多史汗， 并且數(shù)據(jù)庫的可用性也得到了提高琼掠。刪除輔助索引操作就更簡單了，InnoDB存儲(chǔ)引擎只需更新內(nèi)部視圖停撞， 并將輔助索引的空間標(biāo)記為可用瓷蛙，同時(shí)刪除MySQL數(shù)據(jù)庫內(nèi)部視圖上對(duì)該表的索引定義即可。

????????這里需要特別注意的是戈毒， 臨時(shí)表的創(chuàng)建路徑是通過參數(shù)tmpdir 進(jìn)行設(shè)置的艰猬。用戶必須保證tmpdir有足夠的空間可以存放臨時(shí)表，否則會(huì)導(dǎo)致創(chuàng)建索引失敗埋市。

????????由于FIC在索引的創(chuàng)建的過程中對(duì)表加上了S鎖冠桃， 因此在創(chuàng)建的過程中只能對(duì)該表進(jìn)行讀操作， 若有大址的事務(wù)需要對(duì)目標(biāo)表進(jìn)行寫操作道宅， 那么數(shù)據(jù)庫的服務(wù)同樣不可用食听。此外胸蛛，F(xiàn)IC方式只限定于輔助索引， 對(duì)于主鍵的創(chuàng)建和刪除同樣需要重建一張表樱报。

3. Online Schema Change

? ??????Online Schema Change (在線架構(gòu)改變葬项， 簡稱OSC)最早是由Facebook實(shí)現(xiàn)的一種在線執(zhí)行DDL的方式， 并廣泛地應(yīng)用于Facebook的MySQL數(shù)據(jù)庫迹蛤。所謂“在線” 是指在事務(wù)的創(chuàng)建過程中民珍，可以有讀寫事務(wù)對(duì)表進(jìn)行操作， 這提高了原有MySQL數(shù)據(jù)庫在DDL操作時(shí)的并發(fā)性盗飒。

????????Facebook采用PHP腳本來現(xiàn)實(shí)OSC, 而并不是通過修改InnoDB存儲(chǔ)引擎源碼的方式嚷量。osc 最初由Facebook 的員工VamsiPonnekanti開發(fā)。此外箩兽， osc 借鑒了開源社區(qū)之前的工具The openarkkit toolkit oak-online-alter-table津肛。實(shí)現(xiàn)osc 步驟如下：

init, 即初始化階段，會(huì)對(duì)創(chuàng)建的表做一些驗(yàn)證工作汗贫， 如檢查表是否有主鍵身坐， 是否存在觸發(fā)器或者外鍵等。

?createCopyTable, 創(chuàng)建和原始表結(jié)構(gòu)一樣的新表落包。

?alterCopyTable: 對(duì)創(chuàng)建的新表進(jìn)行ALTER TABLE 操作部蛇， 如添加索引或列等。

?createDeltasTable, 創(chuàng)建deltas表咐蝇， 該表的作用是為下一步創(chuàng)建的觸發(fā)器所使用涯鲁。之后對(duì)原表的所有DML操作會(huì)被記錄到createDeltasTable 中。

create Triggers, 對(duì)原表創(chuàng)建INSERT有序、UPDATE抹腿、DELETE 操作的觸發(fā)器。觸發(fā)操作產(chǎn)生的記錄被寫入到deltas表旭寿。

startSnpshotXact, 開始o(jì)sc 操作的事務(wù)警绩。

selectTablelntoOutfile, 將原表中的數(shù)據(jù)寫人到新表。為了減少對(duì)原表的鎖定時(shí)間盅称， 這里通過分片(chunked)將數(shù)據(jù)輸出到多個(gè)外部文件肩祥， 然后將外部文件的數(shù)據(jù)導(dǎo)人到copy表中。分片的大小可以指定缩膝， 默認(rèn)值是500 000混狠。

?dropNCindexs, 在導(dǎo)人到新表前， 刪除新表中所有的輔助索引疾层。

loadCopyTable, 將導(dǎo)出的分片文件導(dǎo)入到新表将饺。

replayChanges, 將osc 過程中原表DML操作的記錄應(yīng)用到新表中， 這些記錄被保存在deltas表中。

recreateNCindexes, 重新創(chuàng)建輔助索引俯逾。

replayChanges, 再次進(jìn)行DML日志的回放操作贸桶， 這些日志是在上述創(chuàng)建輔助索引中過程中新產(chǎn)生的日志舅逸。

swapTables, 將原表和新表交換名字桌肴， 整個(gè)操作需要鎖定2張表， 不允許新的數(shù)據(jù)產(chǎn)生琉历。由于改名是一個(gè)很快的操作坠七， 因此阻塞的時(shí)間非常短。

????????上述只是簡單介紹了osc 的實(shí)現(xiàn)過程旗笔， 實(shí)際腳本非常復(fù)雜彪置， 僅osc 的PHP核心代碼就有22 00 多行， 用到的MySQLInnoDB 的知識(shí)點(diǎn)非常多蝇恶， 建議OBA 和數(shù)據(jù)庫開發(fā)人員嘗試進(jìn)行閱讀拳魁， 這有助于更好地理解InnoDB存儲(chǔ)引擎的使用。

????????由于osc 只是一個(gè)PHP腳本撮弧， 因此其有一定的局限性潘懊。例如其要求進(jìn)行修改的表一定要有主鍵， 且表本身不能存在外鍵和觸發(fā)器贿衍。此外授舟， 在進(jìn)行osc 過程中， 允許SETsql_b in_l og=O, 因此所做的操作不會(huì)同步slave服務(wù)器贸辈，可能 導(dǎo)致主從不一致的情況释树。

4. Online DDL

? ??????雖然FIC可以讓InnoDB存儲(chǔ)引擎避免創(chuàng)建臨時(shí)表， 從而提高索引創(chuàng)建的效率擎淤。但正如前面小節(jié)所說的奢啥， 索引創(chuàng)建時(shí)會(huì)阻塞表上的DML操作。osc 雖然解決了上述的部分問題嘴拢， 但是還是有很大的局限性桩盲。MySQL5.6版本開始支持Online DDL (在線數(shù)據(jù)定義）操作，其允許輔助索引創(chuàng)建的同時(shí)炊汤，還允許其他諸如INSERT正驻、UPDATE、DELETE這類DML操作抢腐，這極大地提高了MySQL數(shù)據(jù)庫在生產(chǎn)環(huán)境中的可用性姑曙。

此外，不僅是輔助索引迈倍，以下這幾類DDL操作都可以 通過“在線” 的方式進(jìn)行操作：

輔助索引的創(chuàng)建與刪除

改變自增長值

添加或刪除外鍵約束

列的重命名

過新的 ALTER TABLE語法伤靠，用戶可以選擇索引的創(chuàng)建方式：

? ??????ALGORITHM指定了創(chuàng)建或刪除索引的算法，COPY表示按照MySQL5.l版本之前的工作模式， 即創(chuàng)建臨時(shí)表的方式宴合。INPLACE表示索引創(chuàng)建或刪除操作不需要?jiǎng)?chuàng)建臨時(shí)表焕梅。DEFAULT表示根據(jù)參數(shù)old_alter_ table來判斷是通過INPLACE還是COPY的算法，該參數(shù)的默認(rèn)值為OFF, 表示采用INPLACE的方式

LOCK部分為索引創(chuàng)建或刪除時(shí)對(duì)表添加鎖的情況卦洽， 可有的選擇為：

(I) NONE

執(zhí)行索引創(chuàng)建或者刪除操作時(shí)贞言，對(duì)目標(biāo)表不添加任何的鎖， 即事務(wù)仍然可以進(jìn)行讀寫操作阀蒂，不會(huì)收到阻塞该窗。因此這種模式可以獲得最大的并發(fā)度。

(2) SHARE

這和之前的FIC類似蚤霞， 執(zhí)行索引創(chuàng)建或刪除操作時(shí)酗失， 對(duì)目標(biāo)表加上一個(gè)并發(fā)地讀事務(wù)，依然可以執(zhí)行昧绣，但是遇到寫事務(wù)规肴，就會(huì)發(fā)生等待操作。如果存儲(chǔ)引擎不支持SHARE模式夜畴，會(huì)返回一個(gè)錯(cuò)誤信息拖刃。

(3) EXCLUSIVE

在EXCLUSIVE模式下， 執(zhí)行索引創(chuàng)建或刪除操作時(shí)斩启，對(duì)目標(biāo)表加上一個(gè)X鎖序调。讀寫事務(wù)都 不能進(jìn)行，因此會(huì)阻塞所有的線程兔簇，這和COPY方式運(yùn)行得到的狀態(tài)類似发绢，但是不需要像COPY方式那樣創(chuàng)建一張臨時(shí)表。

(4) DEFAULT

DEFAULT模式首先會(huì)判斷當(dāng)前操作是否可以使用NONE模式垄琐，若不能边酒，則判斷是否可以使用SHARE模式，最 后判斷是否可以使用EXCLUSIVE模式狸窘。也就是說DEFAULT會(huì)通過判斷事務(wù)的最大并發(fā)性來判斷執(zhí)行DDL的模式墩朦。

????????InnoDB存儲(chǔ)引擎實(shí)現(xiàn)OnlineDDL的原理是在執(zhí)行創(chuàng)建或者刪除操作的同時(shí)，將 INSER T翻擒、 UPDATE氓涣、DELE TE這類DML操作日志寫入到一個(gè)緩存中。待完成索引創(chuàng)建后再將重做應(yīng)用到表上陋气，以此 達(dá)到數(shù)據(jù)的 一致性劳吠。這個(gè)緩存的大小由參數(shù)innodb_ online _alter_ log_ max_ size控制，默認(rèn)的大小 為128MB 巩趁。

5.5 Cardinality值

5.5.1 什么是Cardinality

? ??????并不是在所有的查詢條件中出現(xiàn)的列都需要添加索引痒玩。對(duì)于什么時(shí)候添加B+樹索引， 一般的經(jīng)驗(yàn)是，在訪問表中很少一部分時(shí)使用B+樹索引才有意義蠢古。對(duì)于性別字段奴曙，地區(qū)字段、類型字段草讶，它們可取值的范圍很小洽糟，稱為低選擇性。

? ??????按性別進(jìn)行查詢時(shí)到涂，可取值的范圍一般只有'M'脊框、'F'颁督。因此上述SQL語句得到的結(jié)果可能是該表50%的數(shù)據(jù)（假設(shè)男女比例1 : 1), 這時(shí)添加B+樹索引是完全沒有必要的践啄。相反，如果某個(gè)字段的取值范圍很廣沉御，幾乎沒有重復(fù)屿讽，即屬于高選擇性，則此時(shí)使用B+樹索引是最適合的吠裆。例如伐谈，對(duì)于姓名字段，基本上在一個(gè)應(yīng)用中不允許重名的出現(xiàn)试疙。

? ??????怎樣查看索引是否是高選擇性的呢诵棵？可以通過SHOW INDEX結(jié)果中的列Cardinality來觀察。Cardinality值非常關(guān)鍵祝旷，表示索引中不重復(fù)記錄數(shù)扭的預(yù)估值履澳。同時(shí)需要注意的是Cardinality是一個(gè)預(yù)估值，而不是一個(gè)準(zhǔn)確值怀跛，基本上用戶也不可能得到一個(gè)準(zhǔn)確的值距贷。在實(shí)際應(yīng)用中，CardinalityIn_ row s_ in_ table應(yīng)盡可能地接近1吻谋。如果非常小忠蝗，那么用戶需要考慮是否還有必要?jiǎng)?chuàng)建這個(gè)索引。故在訪問高選擇性屬性的字段并從表中取出很少一部分?jǐn)?shù)據(jù)時(shí)漓拾，對(duì)這個(gè)字段添加B+樹索引是非常有必要的阁最。

5.5.2 lnnoDB存儲(chǔ)引擎的Cardinality統(tǒng)計(jì)

????????因?yàn)镸ySQL數(shù)據(jù)庫中有各種不同的存儲(chǔ)引擎， 而每種存儲(chǔ)引擎對(duì)于B+樹索引的實(shí)現(xiàn)又各不相同骇两， 所以對(duì)Cardinality的統(tǒng)計(jì)是放在存儲(chǔ) 引擎層進(jìn)行的速种。數(shù)據(jù)庫對(duì)于 Cardinality的統(tǒng)計(jì)都是通過采樣(Sample)的方法來完成的。

InnoDB存儲(chǔ)引擎內(nèi)部對(duì)更新Cardinality信息的策略為：

表中 1/16 的數(shù)據(jù)已發(fā)生過變化脯颜。

stat_modified_counter>2 000 000 000哟旗。

????????第一種策略為自從上次統(tǒng)計(jì)Cardinality信息后， 表中 1/16 的數(shù)據(jù)已經(jīng)發(fā)生過變化，這時(shí)需要更新Cardinality信息闸餐。 第二種情況考慮的是饱亮， 如果對(duì)表中某一行數(shù)據(jù)頻繁地進(jìn)行更新操作， 這時(shí)表中的數(shù)據(jù)實(shí)際并沒有增加舍沙， 實(shí)際發(fā)生變化的還是這一行數(shù)據(jù)近上， 則第一種更新策略就無法適用這這種情況。 故在InnoDB存儲(chǔ)引擎內(nèi)部有一個(gè)計(jì)數(shù)器stat_ modified_ counter, 用來表示發(fā)生變化的次數(shù)拂铡， 當(dāng)stat_modified_ counter大于2 000 000 000 時(shí)壹无， 則同樣需要更新Cardinality信息。

????????接著考慮InnoDB存儲(chǔ)引擎內(nèi)部是怎樣來進(jìn)行Cardinality信息的統(tǒng)計(jì)和更新操作的 呢感帅？同樣是通過采樣的方法斗锭。 默認(rèn)InnoDB存儲(chǔ)引擎對(duì)8個(gè)葉子節(jié)點(diǎn)(Leaf Page)進(jìn)行采用。 采樣的過程如下：

取得B+樹索引中葉子節(jié)點(diǎn)的數(shù)量失球， 記為A岖是。

隨機(jī)取得B+樹索引中的8個(gè)葉子節(jié)點(diǎn)。 統(tǒng)計(jì)每個(gè)頁不同記錄的個(gè)數(shù)实苞， 即為P1,P2, …豺撑，P8。

根據(jù)采樣信息給出Cardinality的預(yù)估值：Cardinality= (Pl+ P2+…+P8) *A/8黔牵。

? ??????通過上述的說明可以發(fā)現(xiàn)聪轿，在InnoDB存儲(chǔ)引擎中，Cardinality值是通過對(duì)8個(gè)葉子節(jié)點(diǎn)預(yù)估而得的猾浦，不是一個(gè)實(shí)際精確的值陆错。再者，每次對(duì)Cardinality值的統(tǒng)計(jì)跃巡，都是通過隨機(jī)取8個(gè)葉子節(jié)點(diǎn)得到的危号，這同時(shí)又暗示了另一個(gè)Cardinality現(xiàn)象，即每次得到的Cardinality值可能是不同的素邪。

5.6 B+樹索引的使用

5.6.1 不同應(yīng)用中B+樹索引的使用

? ???根據(jù)第1章的介紹外莲，用戶已經(jīng)知道數(shù)據(jù)庫中存在兩種類型的應(yīng)用，OLTP和OLAP 應(yīng)用兔朦。在OLTP應(yīng)用中偷线，查詢操作只從數(shù)據(jù)庫中取得一小部分?jǐn)?shù)據(jù)，一般可能都在10條? 記錄以下沽甥，甚至在很多時(shí)候只取1條記錄声邦，如根據(jù)主鍵值來取得用戶信息，根據(jù)訂單號(hào)取得訂單的詳細(xì)信息摆舟，這都是典型OLTP應(yīng)用的查詢語句亥曹。在這種情況下邓了，B+樹索引建立后，對(duì)該索引的使用應(yīng)該只是通過該索引取得表中少部分的數(shù)據(jù)媳瞪。這時(shí)建立索引才有意義骗炉。否則優(yōu)化器可能選擇不執(zhí)行索引。????????對(duì)于OLAP應(yīng)用蛇受，情況可能就稍顯復(fù)雜了句葵。不過概括來說，在OLAP應(yīng)用中兢仰，都需要訪問表中大批的數(shù)據(jù)乍丈，根據(jù)這些數(shù)據(jù)來產(chǎn)生查詢的結(jié)果，這些查詢多是面向分析的查詢把将，目的是為決策者提供支持轻专。如這個(gè)月每個(gè)用戶的消費(fèi)情況，銷售額同比秸弛、環(huán)比增長 的情況铭若。因此在OLAP中索引的添加根據(jù)的應(yīng)該是宏觀的信息，而不是微觀递览，因?yàn)樽罱K要得到的結(jié)果是提供給決策者的。例如不需要在OLAP中對(duì)姓名字段進(jìn)行索引瞳腌，因?yàn)楹?少需要對(duì)單個(gè)用戶進(jìn)行查詢绞铃。但是對(duì)于OLAP中的復(fù)雜查詢，要涉及多張表之間的聯(lián)接操作嫂侍，因此索引的添加依然是有意義的儿捧。但是，如果聯(lián)接操作使用的是HashJoin, 那么 索引可能又變得不是非常重要了挑宠，所以這需要OBA或開發(fā)人員認(rèn)真并仔細(xì)地研究自己 的應(yīng)用菲盾。不過在OLAP應(yīng)用中，通常需要根據(jù)時(shí)間維度來進(jìn)行數(shù)據(jù)的篩選各淀。

5.6.2 聯(lián)合索引

? ??????聯(lián)合索引是指對(duì)表上的多個(gè)列進(jìn)行索引懒鉴。前面討論的情況都是只對(duì)表上的一個(gè)列進(jìn)行索引。聯(lián)合索引的創(chuàng)建方法與單個(gè)索引創(chuàng)建的方法一樣碎浇，不同之處僅在于有多個(gè)索引列临谱。

? ??????從本質(zhì)上來說，聯(lián)合索引也是一棵I I LI I _I I B+樹奴璃，不同的是聯(lián)合索引的鍵值的數(shù)量不是1,而是大于等于2悉默。接著來討論兩個(gè)整型列組成的聯(lián)合索引，假定兩個(gè)鍵值的名稱分別為a苟穆、b,如圖5-22所示抄课。

? ??????聯(lián)合索引的第二個(gè)好處是已經(jīng)對(duì)第二個(gè)鍵值進(jìn)行了排序處理唱星。例如，在很多情況下 應(yīng)用程序都需要查詢某個(gè)用戶的購物情況跟磨，并按照時(shí)間進(jìn)行排序魏颓，最后取出最近三次的購買記錄，這時(shí)使用聯(lián)合索引可以避免多一次的排序操作吱晒，因?yàn)樗饕旧碓谌~子節(jié)點(diǎn)已 經(jīng)排序了甸饱。

5.6.3 覆蓋索引

? ??????InnoDB存儲(chǔ)引擎支持覆蓋索引(covering index, 或稱索引覆蓋），即從輔助索引中就可以得到查詢的記錄仑濒，而不需要查詢聚集索引中的記錄叹话。使用覆蓋索引的一個(gè)好處是輔助索引不包含整行 記錄的所有信息，故其大小要遠(yuǎn)小于聚集索引墩瞳，因此可以減少大量的IO操作驼壶。

? ??????對(duì)于 InnoDB存儲(chǔ)引擎的輔助索引而言，由于其包含了主鍵信息喉酌，因此其葉子節(jié)點(diǎn)存放的數(shù)據(jù)為(primary key I , primary key2, …, keyl, key2, …）热凹。

5.6.4 優(yōu)化器選擇不使用索引的情況

? ??????在某些情況下， 當(dāng)執(zhí)行EXPLAIN命令進(jìn)行SQL語句的分析時(shí)泪电， 會(huì)發(fā)現(xiàn)優(yōu)化器并沒 有選擇索引去查找數(shù)據(jù)般妙， 而是通過掃描聚集索引， 也就是直接進(jìn)行全表的掃描來得到數(shù)據(jù)相速。 這種情況多發(fā)生于范圍查找碟渺、JOIN鏈接操作等情況下。

5.6.5 索引提示

????????MySQL數(shù)據(jù)庫支持索引提示(INDEXHINT), 顯式地告訴優(yōu)化器使用哪個(gè)索引突诬。個(gè)人總結(jié)以下兩種情況可能需要用到INDEXHINT:

????????MySQL數(shù)據(jù)庫的優(yōu)化器錯(cuò)誤地選擇了某個(gè)索引苫拍， 導(dǎo)致SQL語句運(yùn)行的很慢。這種情況在最新的MySQL數(shù)據(jù)庫版本中非常非常的少見旺隙。優(yōu)化器在絕大部分情況下工作得都非常有效和正確绒极。這時(shí)有經(jīng)驗(yàn)的OBA或開發(fā)人員可以強(qiáng)制優(yōu)化器使用某個(gè)索引， 以此來提高SQL運(yùn)行的速度蔬捷。

????????某SQL語句可以選擇的索引非常多垄提， 這時(shí)優(yōu)化器選擇執(zhí)行計(jì)劃時(shí)間的開銷可能會(huì)大于SQL語句本身。例如抠刺， 優(yōu)化器分析Range查詢本身就是比較耗時(shí)的操作塔淤。這時(shí)DBA或開發(fā)人員分析最優(yōu)的索引選擇， 通過Index Hint來強(qiáng)制使優(yōu)化器不進(jìn)行各個(gè)執(zhí)行路徑的成本分析速妖， 直接選擇指定的索引來完成查詢高蜂。

5.6.6 Multi-Range Read優(yōu)化

? ??????MySQL5.6版本開始支持Multi-Range Read (MRR)優(yōu)化。 Multi-Range Read優(yōu)化的目的就是為了減少磁盤的隨機(jī)訪問罕容， 并且將隨機(jī)訪問轉(zhuǎn)化為較為順序的數(shù)據(jù)訪問备恤， 這對(duì)于IO-bound類型的 SQL查詢語句可帶來性能極大的提升稿饰。 Multi-Range Read優(yōu)化可適 用于range, ref, eq_ref類型的查詢。

?MRR優(yōu)化有以下幾個(gè)好處：

1 MRR使數(shù)據(jù)訪問變得較為順序露泊。 在查詢輔助索引時(shí)喉镰， 首先根據(jù)得到的查詢結(jié)果 ，按照主鍵進(jìn)行排序惭笑， 并按照主鍵排序的順序進(jìn)行書簽查找侣姆。

2 減少緩沖池中頁被替換的次數(shù)。

3 批量處理對(duì)鍵值的查詢操作 沉噩。

對(duì)于lnnoDB 和MylSAM存儲(chǔ)引擎的范圍查詢和JOIN查詢操作捺宗， MRR的工作方式如下：

1 將查詢得到的 輔助索引鍵值存放于一個(gè)緩存中， 這時(shí)緩存中的數(shù)據(jù)是根據(jù)輔助索引鍵值排序的川蒙。

2 將緩存中的鍵值根據(jù)RowID進(jìn)行排序蚜厉。

3 根據(jù)RowID的排序順序來訪問實(shí)際的數(shù)據(jù)文件。

5.6.7 Index Condition Pushdown CICP)優(yōu)化

????????和Multi-Range Read 樣畜眨，Index Condition Pushdown同樣是 MySQLS.6開始支持的一種根據(jù)索引進(jìn)行查詢的優(yōu)化方式昼牛。之前的數(shù)據(jù)庫當(dāng)進(jìn)行索引查詢時(shí)，首先根據(jù)索引來查找記錄康聂，然后再根據(jù)WHERE條件來過濾記錄贰健。 在支持Index Condition Pushdown后，MySQL數(shù)據(jù)庫會(huì)在取出索引的同時(shí)早抠，判斷是否可以進(jìn)行WHERE條件的過濾 霎烙，也就是將WHERE 的部分過濾操作放在了存儲(chǔ)引擎層。 在某些查詢下蕊连， 可以大大減少上層SQL層對(duì)記錄的索取(fetch), 從而提高數(shù)據(jù)庫的整體性能。

? ??????Index Condition Pushdown優(yōu)化支持range游昼、ref甘苍、eq_ref、ref_or_null類型的查詢烘豌， 當(dāng) 前支持MylSAM和InnoDB存儲(chǔ)引擎 载庭。 當(dāng)優(yōu)化器選擇Index Condition Pushdown優(yōu)化時(shí)， 可在執(zhí)行計(jì)劃的列Extra看到Using index condition提示廊佩。

5.7 哈希算法

????????哈希算法是一種常見算法囚聚，時(shí)間復(fù)雜度為0 (1), 且不只存在于索引中，每個(gè)數(shù)據(jù)庫應(yīng)用中都存在該數(shù)據(jù)庫結(jié)構(gòu)标锄。設(shè)想一個(gè)問題顽铸，當(dāng)前服務(wù)器的內(nèi)存為 128GB 時(shí)，用戶怎么從內(nèi)存中得到某一個(gè)被緩存的頁呢料皇？雖然內(nèi)存中查詢速度很快谓松，但是也不可能每次都要遍歷所有內(nèi)存來進(jìn)行查找星压，這時(shí)對(duì)于字典操作只需0 (1)的哈希算法就有了很好的用武之地。

5.7.1 ?哈希表

????????哈希表(HashTable)也稱散列表鬼譬， 由直接尋址表改進(jìn)而來娜膘。 我們先來看直接尋址 表。 當(dāng)關(guān)鍵字的全域U比較小時(shí)优质， 直接尋址是一種簡單而有效的技術(shù)竣贪。 假設(shè)某應(yīng)用要用到 個(gè)動(dòng)態(tài)集合， 其中每個(gè)元素都有一個(gè)取自全域U={0, 1, …，m-1}關(guān)鍵字。 同時(shí)假設(shè)沒有兩個(gè)元素具有相同的關(guān)鍵字镀钓。

????????直接尋址技術(shù)存在一個(gè)很明顯的問題融撞， 如果域U很大， 在一臺(tái)典型計(jì)算機(jī)的可用容量的限制下慢宗， 要在機(jī)器中存儲(chǔ)大小為U的一張表T就有點(diǎn)不實(shí)際， 甚至是不可能的。 如果實(shí)際要存儲(chǔ)的關(guān)鍵字集合K相對(duì)于U來說很小畏纲， 那么分配給T的大部分空間都要浪費(fèi)掉。

????????因此春缕， 哈希表出現(xiàn)了盗胀。 在哈希方式下， 該元素處于h(k) 中锄贼， 即利用哈希函數(shù)h,根據(jù)關(guān)鍵字k計(jì)算出槽的位置票灰。 函數(shù)h將關(guān)鍵字域U映射到哈希表T [O .. m-1]的槽位上， 如圖5-39所示宅荤。

? ??????哈希表技術(shù)很好地解決了直接尋址遇到的問題屑迂， 但是這樣做有一個(gè)小問題， 如圖5-39所示的兩個(gè)關(guān)鍵字可能映射到同一個(gè)槽上冯键。 般將這種情況稱之為發(fā)生了碰撞(collision)惹盼。 在數(shù)據(jù)庫中一般采用最簡單的碰撞解決技術(shù)， 這種技術(shù)被稱為鏈接法(chaining)惫确。

? ? ? ?在鏈接法中手报， 把散列到同一槽中的所有元素都放在一個(gè)鏈表中， 如圖5-40所示改化。 槽j中有一個(gè)指針掩蛤， 它指向由所有散列到j(luò)的元素構(gòu)成的鏈表的頭；如果不存在這樣的元素陈肛，則j中為NULL揍鸟。

????????最后要考慮的是哈希函數(shù)。 哈希函數(shù)h必須可以很好地進(jìn)行散列燥爷。 最好的情況是能避免碰撞的發(fā)生蜈亩。 即使不能避免懦窘， 也應(yīng)該使碰撞在最小程度下產(chǎn)生。 一般來說稚配， 都將關(guān)鍵字轉(zhuǎn)換成自然數(shù)畅涂， 然后通過除法散列、 乘法散列或全域散列來實(shí)現(xiàn)道川。 數(shù)據(jù)庫中一般采用除法散列的方法午衰。

????????在哈希函數(shù)的除法散列法中， 通過取k除以 m 的余數(shù)冒萄， 將關(guān)鍵字k映射到 m個(gè)槽的某一個(gè)去臊岸， 即哈希函數(shù)為：h(k) = k mod m

5. 7 .2 lnnoDB 存儲(chǔ)引擎中的哈希算法

? ??????InnoDB 存儲(chǔ)引擎使用哈希算法來對(duì)字典進(jìn)行查找， 其沖突機(jī)制采用鏈表方式尊流， 哈希函數(shù)采用除法散列方式帅戒。 對(duì)于緩沖池頁的哈希表來說， 在緩沖池中的 Pag頁都有一個(gè)chain指針崖技，它指向相同哈希函數(shù)值的頁逻住。 而對(duì)于除法散列，m的取值為略大于2倍的緩沖池頁數(shù)量的質(zhì)數(shù)迎献。 例如： 當(dāng)前參數(shù)innodb _buffer _pool_ size的大小為lOM, 則共有640個(gè)16KB的頁瞎访。 對(duì)于緩沖池頁內(nèi)存的哈希表來說，需要分配640X2=1280個(gè)槽吁恍，但是由千1280不是質(zhì)數(shù)扒秸，需要取比1280略大的一個(gè)質(zhì)數(shù)，應(yīng)該是 1399, 所以在啟動(dòng)時(shí)會(huì)分配 1399個(gè)槽的哈希表冀瓦，用來哈希查詢所在緩沖池中的頁伴奥。

? ??????那么InnoDB存儲(chǔ)引擎的緩沖池對(duì)于其中的頁是怎么進(jìn)行查找的呢？上面只是給出了一般的算法翼闽， 怎么將要查找的頁轉(zhuǎn)換成自然數(shù)呢渔伯？

? ??????其實(shí)也很簡單，InnoDB存儲(chǔ)引擎的表空間都有一個(gè)space_id, 用戶所要查詢的應(yīng)該是某個(gè)表空間的某個(gè)連續(xù)16KB的頁肄程， 即偏移量offset。lnnoDB存儲(chǔ)引擎將space_id左移20位选浑， 然后加上這個(gè)space_id和offset, 即關(guān)鍵字K=space_ id<<20+space id+offset,然后通過除法散列到各個(gè)槽中去蓝厌。

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

? ??????自適應(yīng)哈希索引采用之前討論的哈希表的方式實(shí)現(xiàn)。不同的是古徒， 這僅是數(shù)據(jù)庫自身創(chuàng)建并使用的拓提， DBA本身并不能對(duì)其進(jìn)行干預(yù)。自適應(yīng)哈希索引經(jīng)哈希函數(shù)映射到一個(gè)哈希表中隧膘， 因此對(duì)于字典類型的查找非炒快速寺惫，如SELECT * FROM TABLE WHERE index_col='xxx'。但是對(duì)于范圍查找就無能為力了蹦疑。西雀。通過命令SHOW ENGINE INNODB STATUS可以看到當(dāng)前自適應(yīng)哈希索引的使用狀況。

? ??????現(xiàn)在可以看到自適應(yīng)哈希索引的使用信息了歉摧，包括自適應(yīng)哈希索引的大小艇肴，使用情況、每秒使用自適應(yīng)哈希索引搜索的情況叁温。需要注意的是再悼，哈希索引只能用來搜索等值的查詢。

? ??????而對(duì)于其他查找類型膝但，如范圍查找冲九，是不能使用哈希索引的。因此跟束，這里出現(xiàn)了non-hash searches/s的情況莺奸。通過hashsearches:non-hash searches可以大概了解使用哈希索引后的效率。

????????由于自適應(yīng)哈希索引是由InnoDB存儲(chǔ)引擎自己控制的泳炉，因此這里的這些信息只供參考憾筏。不過可以通過參數(shù)innodb_adaptive_ hash_ index來禁用或啟動(dòng)此特性，默認(rèn)為開啟花鹅。

5.8 全文檢索

5.8.1 概述

? ??????通過前面章節(jié)的介紹氧腰，已經(jīng)知道B+樹索引的特點(diǎn)，可以通過索引字段的前綴(prefix)進(jìn)行查找刨肃。例如古拴，對(duì)于下面的查詢B+樹索引是支持的：SELECT* FROM blog WHERE content like'xxx%＇

? ??????上述SQL語句可以查詢博客內(nèi)容以XXX開頭的文章，并且只要content添加了B+ 樹索引真友，就能利用索引進(jìn)行快速查詢黄痪。然而實(shí)際這種查詢不符合用戶的要求，因?yàn)樵诟嗟那闆r下盔然，用戶需要查詢的是博客內(nèi)容包含單詞xxx的文章桅打，即：SELECT* FROM blog WHERE content like'%xxx%'

????????根據(jù)B+樹索引的特性，上述SQL語句即便添加了B+樹索引也是需要進(jìn)行索引的掃描來得到結(jié)果愈案。類似這樣的需求在互聯(lián)網(wǎng)應(yīng)用中還有很多挺尾。例如，搜索引擎需要根據(jù)用戶輸入的關(guān)鍵字進(jìn)行全文查找站绪，電子商務(wù)網(wǎng)站需要根據(jù)用戶的查詢條件遭铺，在可能需要在商品的詳細(xì)介紹中進(jìn)行查找，這些都不是B+樹索引所能很好地完成的工作。

????????全文檢索(Full-TextSearch)是將存儲(chǔ)于數(shù)據(jù)庫中的整本書或整篇文章中的任意內(nèi)容信息查找出來的技術(shù)魂挂。它可以根據(jù)需要獲得全文中有關(guān)章甫题、節(jié)、段涂召、句坠非、詞等信息，也可以進(jìn)行各種統(tǒng)計(jì)和分析芹扭。

? ??????在之前的MySQL數(shù)據(jù)庫中麻顶，InnoDB存儲(chǔ)引擎并不支持全文檢索技術(shù)。大多數(shù)的用戶轉(zhuǎn)向MyISAM存儲(chǔ)引擎舱卡，這可能需要進(jìn)行表的拆分辅肾，并將需要進(jìn)行全文檢索的數(shù)據(jù)存儲(chǔ)為 MyISAM 表。這樣的確能夠解決邏輯業(yè)務(wù)的需求轮锥，但是卻喪失了 InnoDB 存儲(chǔ)引擎的事務(wù)性矫钓，而這在生產(chǎn)環(huán)境應(yīng)用中同樣是非常關(guān)鍵的。

????????從 InnoDB 1.2.x 版本開始舍杜，InnoDB 存儲(chǔ)引擎開始支持全文檢索新娜，其支持 MyISAM存儲(chǔ)引擎的全部功能，并且還支持其他的一些特性既绩，這些將在后面的小節(jié)中進(jìn)行介紹概龄。

5.8.2 倒排索引

? ??????全文檢索通常使用倒排索引 (inverted index) 來實(shí)現(xiàn)。倒排索引同 B+ 樹索引一樣饲握，也是一種索引結(jié)構(gòu)私杜。它在輔助表(auxiliarytable)中存儲(chǔ)了單詞與單詞自身在一個(gè)或多個(gè)文檔中所在位置之間的映射。這通常利用關(guān)聯(lián)數(shù)組實(shí)現(xiàn)救欧，其擁有兩種表現(xiàn)形式：

inverted file index, 其表現(xiàn)形式為｛單詞衰粹，單詞所在文檔的 ID}

?full inverted index, 其表現(xiàn)形式為｛單詞，（單詞所在文檔的ID, 在具體文檔中的位置）｝

5.8.3 InnoDB全文檢索

? ??????InnoDB存儲(chǔ)引擎從1.2.x版本開始支持全文檢索的技術(shù)笆怠，其采用full inverted index 的方式铝耻。在InnoDB存儲(chǔ)引擎中，將(Documentld, Position)視為一個(gè)"ilist"蹬刷。因此在全文檢索的表中瓢捉，有兩個(gè)列，一個(gè)是word字段办成，另一個(gè)是 ilist字段泊柬，并且在word字段上有設(shè)有索引。此外诈火，由于InnoDB存儲(chǔ)引擎在 ilist字段中存放了 Position信息，故可以進(jìn)行Proximity Search, 而MyISAM存儲(chǔ)引擎不支持該特性。

????????正如之前所說的那樣冷守， 倒排索引需要將word存放到一張表中刀崖，這個(gè) 表稱為Auxiliary Table (輔助表） 。在InnoDB存儲(chǔ)引擎中拍摇， 為了提高全文檢索的并行性能亮钦，共有6張Auxiliary Table, 目前每張表根據(jù)word的Latin編碼進(jìn)行分區(qū)。

? ??????Auxiliary Table是持久的表充活，存放于磁盤上蜂莉。然而在InnoDB存儲(chǔ)引擎的全文索引中，還有另外一個(gè)重要的概念FTSIndex Cache (全文檢索索引緩存）混卵，其用來提高全文檢索的性能映穗。

? ??????FTS Index Cache是一個(gè)紅黑樹結(jié)構(gòu)，其根據(jù)(word, ilist)進(jìn)行排序幕随。這意味著插入的數(shù)據(jù)已經(jīng)更新了對(duì)應(yīng)的表蚁滋，但是對(duì)全文索引的更新可能在分詞操作后還在?FTS Index Cache中，AuxiliaryTable可能還沒有更新赘淮。InnoDB存儲(chǔ)引擎會(huì)批量對(duì)Auxilible 進(jìn)行更新辕录，而不是每次插入后更新一次AuxiliaryTable。當(dāng)對(duì)全文檢索進(jìn)行查詢時(shí)梢卸， Auxiliary Table首先會(huì)將在FTSIndex Cache中對(duì)應(yīng)的word字段合并到AuxiliaryTable 中走诞，然后再進(jìn)行查詢。這種merge操作非常類似之前介紹的InsertBuffer的功能蛤高，不同的是InsertBuffer是一個(gè)持久的對(duì)象蚣旱，并且其是B+樹的結(jié)構(gòu)。然而FTSIndex Cache的作用又和InsertBuffer是類似的襟齿，它提高了InnoDB存儲(chǔ)引擎的性能姻锁，并且由于其根據(jù)紅黑樹排序后進(jìn)行批量插人，其產(chǎn)生的AuxiliaryTable相對(duì)較小猜欺。

????????InnoDB存儲(chǔ)引擎允許用戶查看指定倒排索引的Auxiliary Table中分詞的信息位隶， 可以通過設(shè)置參數(shù)innodb_ft_aux_table來觀察倒排索引的Auxiliary Table。

? ??????當(dāng)數(shù)據(jù)庫關(guān)閉時(shí)开皿，在FTSIndex Cache中的數(shù)據(jù)庫會(huì)同步到磁盤上的Auxiliary Table中涧黄。然而 ，如果當(dāng)數(shù)據(jù)庫 發(fā)生右機(jī)時(shí)赋荆， 些FTSIndex Cache中的數(shù)據(jù)庫 可能未被同步到磁盤上笋妥。那么下次重啟數(shù)據(jù)庫 時(shí)，當(dāng)用戶對(duì)表進(jìn)行全文檢索（查詢或者插入操作）時(shí)窄潭，

????????lnnoDB存儲(chǔ)引擎會(huì)自動(dòng)讀取未完成的文檔春宣，然后進(jìn)行分詞操作， 再將分詞的結(jié)果放入到FTS Index Cache中。

????????參數(shù)innodb_ft_ cache_ size 用來控制FTSIndex Cache的大小 月帝，默認(rèn)值為32M躏惋。當(dāng)該緩存滿時(shí)，會(huì)將其中的(word, ilist)分詞信息同步到磁盤的Auxiliary Table中嚷辅。增大該參數(shù)可以提高全文檢索的性能簿姨，但是在者機(jī)時(shí)，未同步到磁盤中的索引信息可能需要更長的時(shí)間進(jìn)行恢復(fù)簸搞。

? ??????FTS Document ID是另外一個(gè)重要的概念扁位。在lnnoDB存儲(chǔ)引擎中，為了支持全文檢索趁俊，必須有一個(gè)列與word進(jìn)行映射域仇，在InnoDB中這個(gè)列被命名為FTS_DOC_ID,其類型必須是BIGINTUNSIGNED NOT NULL, 并且InnoDB存儲(chǔ)引擎自動(dòng)會(huì)在該列上加入一個(gè)名為FTS_DOC_ID_INDEX的UniqueIndex。上述這些操作都由lnnoDB存儲(chǔ)引擎自已完成则酝，用戶也可以在建表時(shí)自動(dòng)添加FTS_DOC _ID, 以及相應(yīng)的UniqueIndex殉簸。由千列名為FTS_DOC_ID的列具有特殊意義，因此創(chuàng)建時(shí)必須注意相應(yīng)的類型沽讹，否則MySQL數(shù)據(jù)庫會(huì)拋出錯(cuò)誤般卑。

? ??????文檔中分詞的插入操作是在事務(wù)提交時(shí)完成，然而對(duì)于刪除操作爽雄，其在事務(wù)提交時(shí)蝠检，不刪除磁盤Auxiliary Table中的記錄，而只是刪除 FTS Cache Index中的記錄挚瘟。 對(duì) 于Auxiliary Table中被刪除的記錄叹谁，InnoDB存儲(chǔ)引擎 會(huì)記錄 其FTS Document ID, 并將其保存在DELETED auxiliary table中 。 在設(shè)置參數(shù)innodb_ft_ aux_ table后乘盖， 用戶同樣可以訪問information_schema架構(gòu)下的表 INNODB_FT_DELETED來觀察刪除的FTS Document ID焰檩。

由于文檔的DML操作實(shí)際并不刪除索引中的數(shù)據(jù)，相反還會(huì)在對(duì)應(yīng)的DELETED表中 插入 記錄订框，因此隨著應(yīng) 用程序的允許析苫，索引 會(huì)變得非常大，即使索引中的有些數(shù)據(jù) 已經(jīng)被刪除穿扳，查詢也不會(huì)選擇這類記錄衩侥。 為此，InnoDB存儲(chǔ)引擎提供了一種方式矛物， 允許用戶手工地將已經(jīng)刪除的記錄從索引中徹底刪除茫死， 該命令就是OPTIMIZE TABLE。?

當(dāng)前l(fā)nnoDB存儲(chǔ)引擎的全文檢索還存在以下的限制：

1每張表只能有一個(gè)全文檢索的索引 履羞。

2由多列組合而成的全文檢索的索引列必須使用相同的字符集與排序規(guī)則峦萎。

3不支持沒有單詞界定符(delimiter)的語言屡久， 如中文、 日語骨杂、 韓語等涂身。

5.8.4 全文檢索

? ??????MySQL數(shù)據(jù)庫支持全文檢索(Full-Text Search)的查詢,MySQL數(shù)據(jù)庫通過MATCH()…AGAINST()語法支持全文檢索的查詢， MATCH指定了需要被查詢的列搓蚪， AGAINST指定了使用何種方法去進(jìn)行查詢。 下面將對(duì)各種查詢模式進(jìn)行詳細(xì)的介紹丁鹉。

1. Natural Language

????????全文檢索通過MATCH函數(shù)進(jìn)行查詢妒潭， 默認(rèn)采用Natural Language 模式， 其表示查詢帶有指定word 的文檔揣钦。

? ??????在WHERE條件中使用MATCH函數(shù)雳灾，查詢返回的結(jié)果是根據(jù)相關(guān)性(Relevance)進(jìn)行降序排序的，即相關(guān)性最高的結(jié)果放在第一位冯凹。相關(guān)性的值是一個(gè)非負(fù)的浮點(diǎn)數(shù)字谎亩，0表示沒有任何的相關(guān)性。根據(jù)MySQL官方的文檔可知宇姚，其相關(guān)性的計(jì)算依據(jù)以下四個(gè)條件：

1 word是否在文檔中出現(xiàn)匈庭。

2 word在文檔中出現(xiàn)的次數(shù)。

3 word在索引列中的數(shù)量浑劳。

4 多少個(gè)文檔包含該word阱持。

2.Boolean

? ??????MySQL數(shù)據(jù)庫允許使用IN BOOLEAN MODE修飾符來進(jìn)行全文檢索。 當(dāng)使用該修飾符時(shí)魔熏， 查詢字符串的前后字符會(huì)有特殊的含義.

+表示該word必須存在衷咽。

-表示該word必須被排除。

(no operator)表示該word是可選的蒜绽，但是如果出現(xiàn)镶骗，其相關(guān)性會(huì)更高

@distance表示查詢的多個(gè)單詞之間的距離是否在distance之內(nèi)，distance的單位是字節(jié)躲雅。這種全文檢索的查詢也稱為ProximitySearch鼎姊。如MATCH(body) AGAINST ("'Pease pot"@30'IN BOOLEAN MODE)表示字符串Pease和pot之間的距離需在30字節(jié)內(nèi)。

>表示出現(xiàn)該單詞時(shí)增加相關(guān)性吏夯。

<表示出現(xiàn)該單詞時(shí)降低相關(guān)性此蜈。

~ 表示允許出現(xiàn)該單詞，但是出現(xiàn)時(shí)相關(guān)性為負(fù)（全文檢索查詢?cè)试S負(fù)相關(guān)性）噪生。

*表示以該單詞開頭的單詞裆赵，如lik*,表示可以是lik、like,又或者likes跺嗽。

"表示短語战授。

3. Query Expansion

? ??????MySQL數(shù)據(jù)庫還支持全文檢索的擴(kuò)展查詢页藻。 這種查詢通常在查詢的關(guān)鍵詞太短， 用戶需要implied knowledge (隱含知識(shí)）時(shí)進(jìn)行植兰。 例如份帐， 對(duì)于單詞database的查詢， 用戶可能希望查詢的不僅僅是包含database的文檔楣导， 可能還指那些包含MySQL废境、 Oracle、 D82筒繁、 RDBMS的單詞噩凹。 而這時(shí)可以使用Query Expansion模式來開啟全文檢索的 implied knowledge。

????????通過在查詢短語中添加WITH QUERY EXPANSION或IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION可以開啟 blind query expansion (又稱為automatic relevance feedback)毡咏。 該查詢分為兩個(gè)階段驮宴。

第一階段：根據(jù)搜索的單詞進(jìn)行全文索引查詢。

第二階段：根據(jù)第一階段產(chǎn)生的分詞再進(jìn)行 次全文檢索的查詢呕缭。