在關(guān)系型數(shù)據(jù)庫中設(shè)計索引其實并不是復雜的事情,很多開發(fā)者都覺得設(shè)計索引能夠提升數(shù)據(jù)庫的性能,相關(guān)的知識必定非常復雜.

然而這種想法是不正確的,索引其實并不是一個多么高深莫測的東西,只要我們掌握一定的辦法,理解索引的實現(xiàn)就能在不需要 DBA 的情況下設(shè)計出高效的索引.

本文會介紹 數(shù)據(jù)庫索引設(shè)計與優(yōu)化 中設(shè)計索引的一些辦法,讓各位讀者能夠快速的在現(xiàn)有的工程中設(shè)計出合適的索引.

磁盤 IO

一個數(shù)據(jù)庫必須保證其中存儲的所有數(shù)據(jù)都是可以隨時讀寫的,同時因為 MySQL 中所有的數(shù)據(jù)其實都是以文件的形式存儲在磁盤上的,而從磁盤上隨機拜訪對應(yīng)的數(shù)據(jù)非常耗時,所以數(shù)據(jù)庫程序和操作系統(tǒng)提供了緩沖池和內(nèi)存以提高數(shù)據(jù)的拜訪速度.

除此之外,我們還必要知道數(shù)據(jù)庫對數(shù)據(jù)的讀取并不是以行為單位進行的,無論是讀取一行還是多行,都會將該行或者多行所在的頁全部加載進來,然后再讀取對應(yīng)的數(shù)據(jù)記錄；也就是說,讀取所耗費的時間與行數(shù)無關(guān),只與頁數(shù)有關(guān).

在 MySQL 中,頁的大小一般為 16KB,不過也可能是 8KB、32KB 或者其他值,這跟 MySQL 的存儲引擎對數(shù)據(jù)的存儲方式有很大的關(guān)系,文中不會展開介紹,不過索引或行記錄是否在緩存池中極大的影響了拜訪索引或者數(shù)據(jù)的成本.

隨機讀取

數(shù)據(jù)庫等待一個頁從磁盤讀取到緩存池的所需要的本錢巨大的,無論我們是想要讀取一個頁面上的多條數(shù)據(jù)還是一條數(shù)據(jù),都需要消耗約 10ms 左右的時間：

10ms 的時間在計算領(lǐng)域其實是一個非常巨大的本錢,假設(shè)我們使用腳本向裝了 SSD 的磁盤上順序?qū)懭胱止?jié),那么在 10ms 內(nèi)可以寫入大概 3MB 左右的內(nèi)容,但是數(shù)據(jù)庫程序在 10ms 之內(nèi)只能將一頁的數(shù)據(jù)加載到數(shù)據(jù)庫緩沖池中,從這里可以看出隨機讀取的代價是巨大的.

這 10ms 的一次隨機讀取是依照每秒 50 次的讀取計算得到的,其中等待時間為 3ms、磁盤的實際繁忙時間約為 6ms,最終數(shù)據(jù)頁從磁盤傳輸?shù)骄彌_池的時間為 1ms 左右,在對查詢進行估算時并不需要準確的知道隨機讀取的時間,只需要知道估算出的 10ms 就可以了.

內(nèi)存讀取

假如在數(shù)據(jù)庫的緩存池中沒有找到對應(yīng)的數(shù)據(jù)頁,那么會去內(nèi)存中尋找對應(yīng)的頁面：

當對應(yīng)的頁面存在于內(nèi)存時,數(shù)據(jù)庫程序就會使用內(nèi)存中的頁,這能夠?qū)?shù)據(jù)的讀取時間降低一個數(shù)量級,將 10ms 降低到 1ms；MySQL 在執(zhí)行讀操作時,會先從數(shù)據(jù)庫的緩沖區(qū)中讀取,如果不存在與緩沖區(qū)中就會嘗試從內(nèi)存中加載頁面,如果前面的兩個步調(diào)都失敗了,最后就只能執(zhí)行隨機 IO 從磁盤中獲取對應(yīng)的數(shù)據(jù)頁.

次序讀取

從磁盤讀取數(shù)據(jù)并不是都要付出很大的代價,當數(shù)據(jù)庫管理法式一次性從磁盤中順序讀取大量的數(shù)據(jù)時,讀取的速度會異常的快,大概在 40MB/s 左右.

如果一個頁面的大小為 4KB,那么 1s 的時間就可以讀取 10000 個頁,讀取一個頁面所花費的平均時間便是 0.1ms,相比隨機讀取的 10ms 已經(jīng)降低了兩個數(shù)量級,甚至比內(nèi)存中讀取數(shù)據(jù)還要快.

數(shù)據(jù)頁面的次序讀取有兩個非常重要的優(yōu)勢：

1. 同時讀取多個界面意味著總時間的消耗會大幅度減少,磁盤的吞吐量可以到達 40MB/s；

2. 數(shù)據(jù)庫管理程序會對一些即將使用的界面進行預讀,以減少查詢哀求的等待和響應(yīng)時間；

小結(jié)

數(shù)據(jù)庫查詢操作的時間大都消耗在從磁盤或者內(nèi)存中讀取數(shù)據(jù)的過程,由于隨機 IO 的代價巨大,如安在一次數(shù)據(jù)庫查詢中減少隨機 IO 的次數(shù)往往能夠大幅度的降低查詢所耗費的時間提高磁盤的吞吐量.

查詢進程

在上一節(jié)中,文章從數(shù)據(jù)頁加載的角度介紹了磁盤 IO 對 MySQL 查詢的影響,而在這一節(jié)中將介紹 MySQL 查詢的執(zhí)行進程中以及數(shù)據(jù)庫中的數(shù)據(jù)的特征對最終查詢性能的影響.

索引片(Index Slices)

索引片其實就是 SQL 查詢在執(zhí)行過程中掃描的一個索引片段,在這個范圍中的索引將被順序掃描,根據(jù)索引片包括的列數(shù)不同,數(shù)據(jù)庫索引設(shè)計與優(yōu)化 書中對將索引分為寬索引和窄索引：

主鍵列 id 在所有的 MySQL 索引中都是必定會存在的.

對于查詢 SELECT id, username, age FROM users WHERE username="draven" 來說,(id, username) 就是一個窄索引,因為該索引沒有包括存在于 SQL 查詢中的 age 列,而 (id, username, age) 就是該查詢的一個寬索引了,它包括這個查詢中所需要的全部數(shù)據(jù)列.

寬索引能夠避免二次的隨機 IO,而窄索引就必要在對索引進行順序讀取之后再根據(jù)主鍵 id 從主鍵索引中查找對應(yīng)的數(shù)據(jù)：

對于窄索引,每一個在索引中匹配到的記錄行最終都必要執(zhí)行另外的隨機讀取從聚集索引中獲得剩余的數(shù)據(jù),如果結(jié)果集非常大,那么就會導致隨機讀取的次數(shù)過多進而影響性能.

過濾因子

從上一小節(jié)對索引片的介紹,我們可以看到影響 SQL 查詢的除了查詢本身還與數(shù)據(jù)庫表中的數(shù)據(jù)特征有關(guān),如果使用的是窄索引那么對表的隨機拜訪就不可避免,在這時如何讓索引片變『薄』就是我們需要做的了.

一個 SQL 查詢掃描的索引片大小其實是由過濾因子決定的,也便是滿足查詢條件的記錄行數(shù)所占的比例：

對于 users 表來說,sex=”male” 就不是一個好的過濾因子,它會選擇整張表中一半的數(shù)據(jù),所以在一般情況下我們最好不要使用 sex 列作為整個索引的第一列；而 name=”draven” 的使用就可以得到一個比擬好的過濾因子了,它的使用能過濾整個數(shù)據(jù)表中 99.9% 的數(shù)據(jù)；當然我們也可以將這三個過濾進行組合,創(chuàng)建一個新的索引 (name, age, sex) 并同時使用這三列作為過濾條件：

當三個過濾條件都是等值謂詞時,幾個索引列的順序其實是無所謂的,索引列的順序不會影響同一個 SQL 語句對索引的選擇,也便是索引 (name, age, sex) 和 (age, sex, name) 對于上圖中的條件來說是完全一樣的,這兩個索引在執(zhí)行查詢時都有著完全相同的效果.

組合條件的過濾因子就可以達到十萬分之 6 了,如果整張表中有 10w 行數(shù)據(jù),也只需要在掃描薄索引片后進行 6 次隨機讀取,這種直接使用乘積來計算組合條件的過濾因子其實有一個比擬重要的問題：列與列之間不應(yīng)該有太強的相關(guān)性,如果不同的列之間有相關(guān)性,那么得到的結(jié)果就會比直接乘積得出的結(jié)果大一些,比如：所在的城市和郵政編碼就有非常強的相關(guān)性,兩者的過濾因子直接相乘其實與實際的過濾因子會有很大的偏差,不過這在多數(shù)情況下都不是太大的問題.

對于一張表中的同一個列,分歧的值也會有分歧的過濾因子,這也就造成了同一列的分歧值最終的查詢性能也會有很大差別：

當我們評估一個索引是否合適時,需要考慮極端情況下查詢語句的性能,好比 0% 或者 50% 等；最差的輸入往往意味著最差的性能,在平均情況下表現(xiàn)良好的 SQL 語句在極端的輸入下可能就完全無法正常工作,這也是在設(shè)計索引時需要注意的問題.

總而言之,必要掃描的索引片的大小對查詢性能的影響至關(guān)重要,而掃描的索引記錄的數(shù)量,就是總行數(shù)與組合條件的過濾因子的乘積,索引片的大小最終也決定了從表中讀取數(shù)據(jù)所必要的時間.

婚配列與過濾列

假設(shè)在 users 表中有 name、age 和 (name, sex, age) 三個輔助索引；當 WHERE 條件中存在類似 age = 21 或者 name = “draven” 這種等值謂詞時,它們都會成為匹配列(Matching Column)用于選擇索引樹中的數(shù)據(jù)行,然則當我們使用以下查詢時：

SELECT * FROM usersWHERE name = "draven" AND sex = "male" AND age > 20;

雖然我們有 (name, sex, age) 索引包括了上述查詢條件中的全部列,但是在這里只有 name 和 sex 兩列才是匹配列,MySQL 在執(zhí)行上述查詢時,會選擇 name 和 sex 作為匹配列,掃描所有滿足條件的數(shù)據(jù)行,然后將 age 當做過濾列(Filtering Column)：

過濾列雖然不能夠減少索引片的大小,但是能夠減少從表中隨機讀取數(shù)據(jù)的次數(shù),所以在索引中也飾演著非常重要的角色.

索引的設(shè)計

作者相信文章前面的內(nèi)容已經(jīng)為索引的設(shè)計提供了充足的理論基礎(chǔ)和知識,從總體來看如何減少隨機讀取的次數(shù)是設(shè)計索引時需要重視的最重要的問題,在這一節(jié)中,我們將介紹 數(shù)據(jù)庫索引設(shè)計與優(yōu)化 一書中歸納出的設(shè)計最佳索引的辦法.

三星索引

三星索引是對于一個查詢語句可能的最好索引,如果一個查詢語句的索引是三星索引,那么它只需要進行一次磁盤的隨機讀及一個窄索引片的順序掃描就可以得到全部的結(jié)果集；因此其查詢的響應(yīng)時間比普通的索引會少幾個數(shù)量級；根據(jù)書中對三星索引的定義,我們可以理解為主鍵索引對于 WHERE id = 1 就是一個特殊的三星索引,我們只需要對主鍵索引樹進行一次索引拜訪并且順序讀取一條數(shù)據(jù)記錄查詢就結(jié)束了.

為了滿足三星索引中的三顆星,我們分別必要做以下幾件事情：

1. 第一顆星必要取出所有等值謂詞中的列,作為索引開頭的最開始的列(任意順序)；

2. 第二顆星必要將 ORDER BY 列加入索引中；

3. 第三顆星必要將查詢語句剩余的列全部加入到索引中；

三星索引的觀點和星級的給定來源于 數(shù)據(jù)庫索引設(shè)計與優(yōu)化 書中第四章三星索引一節(jié).

如果對于一個查詢語句我們按照上述的三個條件進行設(shè)計,那么就可以得到該查詢的三星索引,這三顆星中的最后一顆星往往都是最容易獲得的,滿足第三顆星的索引也就是上面提到的寬索引,能夠避免大量的隨機 IO,如果我們遵循這個順序為一個 SQL 查詢設(shè)計索引那么我們就可以得到一個完美的索引了；這三顆星的獲得其實也沒有表面上這么簡單,每一顆星都有自己的意義：

1. 第一顆星不只是將等值謂詞的列加入索引,它的作用是減少索引片的大小以減少必要掃描的數(shù)據(jù)行；

2. 第二顆星用于避免排序,削減磁盤 IO 和內(nèi)存的使用；

3. 第三顆星用于避免每一個索引對應(yīng)的數(shù)據(jù)行都必要進行一次隨機 IO 從聚集索引中讀取剩余的數(shù)據(jù)；

在實際場景中,問題往往沒有這么簡單,我們雖然可以總能夠通過寬索引避免大量的隨機拜訪,但是在一些復雜的查詢中我們無法同時獲得第一顆星和第二顆星.

SELECT id, name, age FROM usersWHERE age BETWEEN 18 AND 21 AND city = "Beijing"ORDER BY name;

在上述查詢中,我們總可以通過增加索引中的列以獲得第三顆星,但是如果我們想要獲得第一顆星就需要最小化索引片的大小,這時索引的前綴必需為 (city, age),在這時再想獲得第三顆星就不可能了,哪怕在 age 的后面添加索引列 name,也會因為 name 在范圍索引列 age 后面必需進行一次排序操作,最終得到的索引就是 (city, age, name, id)：

如果我們需要在內(nèi)存中避免排序的話,就需要交換 age 和 name 的位置了,在這時就可以得到索引 (city, name, age, id),當一個 SQL 查詢中同時擁有范圍謂詞和 ORDER BY 時,無論如何我們都是沒有方法獲得一個三星索引的,我們能夠做的就是在這兩者之間做出選擇,是犧牲第一顆星還是第二顆星.

總而言之,在設(shè)計單表的索引時,首先把查詢中所有的等值謂詞全部取出以任意順序放在索引最前面,在這時,如果索引中同時存在范圍索引和 ORDER BY 就需要權(quán)衡利弊了,希望最小化掃描的索引片厚度時,應(yīng)該將過濾因子最小的范圍索引列加入索引,如果希望避免排序就選擇 ORDER BY 中的全部列,在這之后就只需要將查詢中剩余的全部列加入索引了,通過這種固定的辦法和邏輯就可以最快地獲得一個查詢語句的二星或者三星索引了.

總結(jié)

在單表上對索引進行設(shè)計其實還是非常容易的,只需要遵循固定的套路就能設(shè)計出一個理想的三星索引,在這里強烈推薦 數(shù)據(jù)庫索引設(shè)計與優(yōu)化 這本書籍,其中包含了大量與索引設(shè)計與優(yōu)化的相關(guān)內(nèi)容；在之后的文章中讀者也會分析介紹書中提供的幾種估算辦法,來幫助我們通過預估問題設(shè)計出更高效的索引.

Java工程化、高性能及分布式、高性能、深入淺出.高架構(gòu).性能調(diào)優(yōu)、Spring,MyBatis,Netty源碼分析和大數(shù)據(jù)等多個知識點.如果你想拿高薪的,想學習的,想就業(yè)前景好的,想跟別人競爭能取得優(yōu)勢的,想進阿里面試但擔心面試不外的,你都可以來,群號為：647631030

注：加群要求

1、具有1-5工作經(jīng)驗的,面對目前流行的技術(shù)不知從何下手,必要突破技術(shù)瓶頸的可以加.

2、在公司待久了,過得很安適,但跳槽時面試碰壁.需要在短時間內(nèi)進修、跳槽拿高薪的可以加.

3、如果沒有工作經(jīng)驗,但基礎(chǔ)非常扎實,對java工作機制,常用設(shè)計思想,常用java開發(fā)框架掌握純熟的,可以加.

4、覺得本身很牛B,一般需求都能搞定.但是所學的知識點沒有系統(tǒng)化,很難在技術(shù)領(lǐng)域繼續(xù)突破的可以加.

5.阿里Java高級大牛直播講解知識點,分享知識,多年工作經(jīng)驗的梳理和總結(jié),帶著大家全面、科學地建立本身的技術(shù)體系和技術(shù)認知!

6.小號或者小白之類加群一概不給過,謝謝.