聚合如何寫“數(shù)據(jù)庫”和“數(shù)據(jù)庫索引”這兩個東西是服務(wù)器端開發(fā)領(lǐng)域使用最廣泛的兩個概念,熟練使用數(shù)據(jù)庫和數(shù)據(jù)庫索引是開發(fā)人員在行業(yè)中生存的必備技能。
使用索引很簡單,只要你會寫創(chuàng)建表的語句,就一定可以寫創(chuàng)建索引的語句,要知道這個世界上沒有服務(wù)器端程序員不會創(chuàng)建表,但是使用索引是一回事,深入理解索引的原理,能夠正確使用索引又是另一回事,這是完全不同的境界 (我還沒有達(dá)到這個境界)。
1、為什么要給表加上主鍵?
2、為什么加索引后會使查詢變快?
3、為什么加索引后會使寫入、修改、刪除變慢?
4、什么情況下要同時(shí)在兩個字段上建索引?這些問題他們可能不一定能說出答案。
知道這些問題的答案有什么好處呢?
如果開發(fā)的應(yīng)用使用的數(shù)據(jù)庫表中只有1萬條數(shù)據(jù),那么了解與不了解真的沒有差別,然而,如果開發(fā)的應(yīng)用有幾百上千萬甚至億級別的數(shù)據(jù),那么不深入了解索引的原理,寫出來程序就根本跑不動,就好比如果給貨車裝個轎車的引擎,這貨車還能拉的動貨嗎?
接下來就講解一下上面提出的幾個問題,希望對閱讀者有幫助。網(wǎng)上很多講解索引的文章對索引的描述是這樣的「索引就像書的目錄,通過書的目錄就準(zhǔn)確的定位到了書籍具體的內(nèi)容」,這句話描述的非常正確,但就像脫了褲子放屁,說了跟沒說一樣,通過目錄查找書的內(nèi)容自然是要比一頁一頁的翻書找來的快,同樣使用的索引的人難到會不知道,通過索引定位到數(shù)據(jù)比直接一條一條的查詢來的快,不然他們?yōu)槭裁匆ㄋ饕?/p>
想要理解索引原理必須清楚一種數(shù)據(jù)結(jié)構(gòu)「平衡樹」(非二叉),也就是b tree或者 b+ tree,重要的事情說三遍:“平衡樹,平衡樹,平衡樹”。
當(dāng)然,有的數(shù)據(jù)庫也使用哈希桶作用索引的數(shù)據(jù)結(jié)構(gòu),然而,主流的RDBMS都是把平衡樹當(dāng)做數(shù)據(jù)表默認(rèn)的索引數(shù)據(jù)結(jié)構(gòu)的。
我們平時(shí)建表的時(shí)候都會為表加上主鍵,在某些關(guān)系數(shù)據(jù)庫中,如果建表時(shí)不指定主鍵,數(shù)據(jù)庫會拒絕建表的語句執(zhí)行。
事實(shí)上,一個加了主鍵的表,并不能被稱之為「表」。
一個沒加主鍵的表,它的數(shù)據(jù)無序的放置在磁盤存儲器上,一行一行的排列的很整齊,跟我認(rèn)知中的「表」很接近。
如果給表上了主鍵,那么表在磁盤上的存儲結(jié)構(gòu)就由整齊排列的結(jié)構(gòu)轉(zhuǎn)變成了樹狀結(jié)構(gòu),也就是上面說的「平衡樹」結(jié)構(gòu),換句話說,就是整個表就變成了一個索引。
沒錯,再說一遍,整個表變成了一個索引,也就是所謂的「聚集索引」。
這就是為什么一個表只能有一個主鍵,一個表只能有一個「聚集索引」,因?yàn)橹麈I的作用就是把「表」的數(shù)據(jù)格式轉(zhuǎn)換成「索引(平衡樹)」的格式放置。
上圖就是帶有主鍵的表(聚集索引)的結(jié)構(gòu)圖。
圖畫的不是很好,將就著看。
其中樹的所有結(jié)點(diǎn)(底部除外)的數(shù)據(jù)都是由主鍵字段中的數(shù)據(jù)構(gòu)成,也就是通常我們指定主鍵的id字段。
最下面部分是真正表中的數(shù)據(jù)。
假如我們執(zhí)行一個SQL語句:
首先根據(jù)索引定位到1256這個值所在的葉結(jié)點(diǎn),然后再通過葉結(jié)點(diǎn)取到id等于1256的數(shù)據(jù)行。
這里不講解平衡樹的運(yùn)行細(xì)節(jié),但是從上圖能看出,樹一共有三層,從根節(jié)點(diǎn)至葉節(jié)點(diǎn)只需要經(jīng)過三次查找就能得到結(jié)果。
如下圖:
假如一張表有一億條數(shù)據(jù),需要查找其中某一條數(shù)據(jù),按照常規(guī)邏輯,一條一條的去匹配的話,最壞的情況下需要匹配一億次才能得到結(jié)果,用大O標(biāo)記法就是O(n)最壞時(shí)間復(fù)雜度,這是無法接受的,而且這一億條數(shù)據(jù)顯然不能一次性讀入內(nèi)存供程序使用,因此,這一億次匹配在不經(jīng)緩存優(yōu)化的情況下就是一億次IO開銷,以現(xiàn)在磁盤的IO能力和CPU的運(yùn)算能力,有可能需要幾個月才能得出結(jié)果。
如果把這張表轉(zhuǎn)換成平衡樹結(jié)構(gòu)(一棵非常茂盛和節(jié)點(diǎn)非常多的樹),假設(shè)這棵樹有10層,那么只需要10次IO開銷就能查找到所需要的數(shù)據(jù),速度以指數(shù)級別提升,用大O標(biāo)記法就是O(log n),n是記錄總樹,底數(shù)是樹的分叉數(shù),結(jié)果就是樹的層次數(shù)。
換言之,查找次數(shù)是以樹的分叉數(shù)為底,記錄總數(shù)的對數(shù),用公式來表示就是
用程序來表示就是Math.Log(100000000,
10),100000000是記錄數(shù),10是樹的分叉數(shù)(真實(shí)環(huán)境下分叉數(shù)遠(yuǎn)不止
10),結(jié)果就是查找次數(shù),這里的結(jié)果從億降到了個位數(shù)。
因此,利用索引會使數(shù)據(jù)庫查詢有驚人的性能提升。
然而,事物都是有兩面的,索引能讓數(shù)據(jù)庫查詢數(shù)據(jù)的速度上升,而使寫入數(shù)據(jù)的速度下降,原因很簡單的,因?yàn)槠胶鈽溥@個結(jié)構(gòu)必須一直維持在一個正確的狀態(tài),增刪改數(shù)據(jù)都會改變平衡樹各節(jié)點(diǎn)中的索引數(shù)據(jù)內(nèi)容,樹結(jié)構(gòu),因此,在每次數(shù)據(jù)改變時(shí),DBMS必須去重新梳理樹(索引)的結(jié)構(gòu)以確保它的正確,這會帶來不小的性能開銷,也就是為什么索引會給查詢以外的操作帶來副作用的原因。
講完聚集索引,接下來聊一下非聚集索引,也就是我們平時(shí)經(jīng)常提起和使用的常規(guī)索引。每次給字段建一個新索引,字段中的數(shù)據(jù)就會被復(fù)制一份出來,用于生成索引。
因此,給表添加索引,會增加表的體積,占用磁盤存儲空間。
非聚集索引和聚集索引的區(qū)別在于,通過聚集索引可以查到需要查找的數(shù)據(jù),而通過非聚集索引可以查到記錄對應(yīng)的主鍵值,再使用主鍵的值通過聚集索引查找到需要的數(shù)據(jù),如下圖:
不管以任
式查詢表,最終都會利用主鍵通過聚集索引來定位到數(shù)據(jù),聚集索引(主鍵)是通往真實(shí)數(shù)據(jù)所在的唯一路徑。然而,有一種例外可以不使用聚集索引就能查詢出所需要的數(shù)據(jù),這種非主流的方法 稱之為「覆蓋索引」查詢,也就是平時(shí)所說的復(fù)合索引或者多字段索引查詢。
文章上面的內(nèi)容已經(jīng)指出,當(dāng)為字段建立索引以后,字段中的內(nèi)容會被同步到索引之中,如果為一個索引指定兩個字段,那么這個兩個字段的內(nèi)容都會被同步至索引之中。
先看下面這個SQL語句:這句SQL語句的執(zhí)行過程如下:
1、首先,通過非聚集索引index_birthday查找birthday等于1991-11-1的所有記錄的主鍵ID值
2、然后,通過得到的主鍵ID值執(zhí)行聚集索引查找,找到主鍵ID值對就的真實(shí)數(shù)據(jù)(數(shù)據(jù)行)存儲的位置 我們把birthday字段上的索引改成雙字段的覆蓋索引
這句SQL語句的執(zhí)行過程就會變?yōu)?數(shù)據(jù)庫索引的大致工作原理就是像文中所述,然而細(xì)節(jié)方面可能會略有偏差,這但并不會對概念闡述的結(jié)果產(chǎn)生影響。