從程序員的角度以MySQL為例探索數(shù)據(jù)庫的奧秘

數(shù)據(jù)庫基本原理

我對(duì)DB的理解

第一：數(shù)據(jù)庫的組成：存儲(chǔ) + 實(shí)例

不必多說，數(shù)據(jù)當(dāng)然需要存儲(chǔ)；存儲(chǔ)了還不夠，顯然需要提供程序?qū)Υ鎯?chǔ)的操作進(jìn)行封裝，對(duì)外提供增刪改查的API，即實(shí)例。

一個(gè)存儲(chǔ)，可以對(duì)應(yīng)多個(gè)實(shí)例，這將提高這個(gè)存儲(chǔ)的負(fù)載能力以及高可用；多個(gè)存儲(chǔ)可以分布在不同的機(jī)房、地域，將實(shí)現(xiàn)容災(zāi)。

第二：按Block or Page讀取數(shù)據(jù)

用大腿想也知道，數(shù)據(jù)庫不可能按行讀取數(shù)據(jù)（Why? ? ^_^）。實(shí)質(zhì)上，數(shù)據(jù)庫，如Oracle/MySQL，都是基于固定大?。ū热?6K）的物理塊（Block or Page，我這里就不區(qū)分統(tǒng)一稱為Block）來實(shí)現(xiàn)調(diào)度和管理的。要知道Block是數(shù)據(jù)庫的概念，如何對(duì)應(yīng)到文件系統(tǒng)呢？顯然需要指出“這個(gè)Block的地址在哪里”，當(dāng)查找到地址后，讀取固定大小的數(shù)據(jù)就相當(dāng)于完成了Block的讀取了。

數(shù)據(jù)庫很聰明的，它不會(huì)僅僅只讀取需要讀取的Block，它還會(huì)替我們把附近的Block塊都讀取加載至內(nèi)存。實(shí)際上，這是為了減少IO次數(shù)，提高命中率。事實(shí)上，一個(gè)Block塊的附近Block也是熱點(diǎn)數(shù)據(jù)，這種處理方式很有必要！

第三：磁盤IO是數(shù)據(jù)庫的性能瓶頸

毫無疑問，數(shù)據(jù)在磁盤上，少不了磁盤IO。什么磁頭旋轉(zhuǎn)，定位磁道，尋址的過程，就不說了，我們是程序員，也管不了這些。但是這個(gè)過程確實(shí)是非常耗時(shí)的，和內(nèi)存讀取不是一個(gè)數(shù)量級(jí)，所以后來出現(xiàn)了很多方式來減少IO，提升數(shù)據(jù)庫性能。

比如，增加內(nèi)存，讓數(shù)據(jù)庫把數(shù)據(jù)更多的加載至內(nèi)存。內(nèi)存雖好，但也不能濫用，為什么這么說呢？假設(shè)數(shù)據(jù)庫中有100G數(shù)據(jù)，如果都加載至內(nèi)存，也就說數(shù)據(jù)庫要管理100G磁盤數(shù)據(jù)+100G內(nèi)存數(shù)據(jù)，你說累不累？（數(shù)據(jù)庫要處理磁盤和內(nèi)存的映射關(guān)系，數(shù)據(jù)的同步，還要對(duì)內(nèi)存數(shù)據(jù)進(jìn)行清理，如果涉及數(shù)據(jù)庫事務(wù)，又是一系列復(fù)雜操作……）不過這里需要指出的是，為了加快內(nèi)存查找速度，數(shù)據(jù)庫一般對(duì)內(nèi)存進(jìn)行HASH存放。

比如，利用索引，索引相比內(nèi)存，是一個(gè)性價(jià)比非常高的東西，后文詳細(xì)介紹MySQL的索引原理。

比如，利用性能更好的磁盤…（和咱們就沒關(guān)系呢）

第四，提出一些問題思考下：

為什么我們說利用delete刪除一個(gè)表的數(shù)據(jù)較trancate一個(gè)表要慢？

【一個(gè)按行查找刪除，多費(fèi)勁；一個(gè)基于Block的體系結(jié)構(gòu)刪除】為什么我們說要小表驅(qū)動(dòng)大表？【小表驅(qū)動(dòng)大表會(huì)快？什么鬼？MN和NM不是一樣的么？有鬼的地方，就有索引！】

探索MySQL索引背后的原理

對(duì)于絕大數(shù)的應(yīng)用系統(tǒng)，讀寫比例在10:1，甚至100:1，而且insert/update很難出現(xiàn)性能問題，遇到最多的，最棘手的就是select了，select優(yōu)化是重中之重，顯然少不了索引！

說起MySQL的索引，我們會(huì)冒出很多這些東西：BTree索引/B+Tree索引/Hash索引/聚集索引/非聚集索引…這么多，暈頭！

索引到底是什么，想解決什么問題？

老生常談了，官網(wǎng)說MySQL索引是一種數(shù)據(jù)結(jié)構(gòu)，索引的目的就是為了提高查詢效率。

說白了，不使用索引的話，磁盤IO次數(shù)比較多！要想減少磁盤IO次數(shù)，怎么辦？

我們想通過不斷縮小想要獲取的數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果，把每次查找數(shù)據(jù)的磁盤IO次數(shù)控制在一個(gè)很小的數(shù)量級(jí)，最好是常數(shù)數(shù)量級(jí)。

為了應(yīng)對(duì)上述問題，B+Tree索引出來了！

Hello，B+Tree

在MySQL中，不同存儲(chǔ)引擎對(duì)索引的實(shí)現(xiàn)方式是不同的，這里將重點(diǎn)分析MyISAM和Innodb。

MyISAM引擎的B+Tree索引結(jié)構(gòu)

我們知道對(duì)于MyISAM引擎而言，數(shù)據(jù)文件和索引文件是分離的。從圖中也可以看出，通過索引查找到后，就得到了數(shù)據(jù)的物理地址，然后根據(jù)地址定位數(shù)據(jù)文件中的記錄即可。這種方式也叫"非聚集索引"。

而對(duì)于Innodb引擎而言，數(shù)據(jù)文件本身是索引文件！通俗點(diǎn)說，葉子節(jié)點(diǎn)上，MyISAM存儲(chǔ)的是記錄的物理地址，而Innodb上存儲(chǔ)的是數(shù)據(jù)內(nèi)容，這種方式即"聚集索引"。

另外一點(diǎn)需要注意的是，對(duì)于Innodb而言，主鍵索引中葉子節(jié)點(diǎn)存儲(chǔ)的是數(shù)據(jù)內(nèi)容，而普通索引的葉子節(jié)點(diǎn)中存儲(chǔ)的是主鍵值！也就是說，對(duì)于Innodb的普通索引字段查找，先通過普通索引的B+Tree查找到主鍵后，然后通過主鍵索引的B+Tree進(jìn)行查找。從這里你可以看出，對(duì)于Innodb而言，主鍵的建立非常重要！

而對(duì)于MyISAM而言，主鍵索引和普通索引僅僅的區(qū)別在于主鍵只需要查找到一條記錄即可停止，而普通索引允許重復(fù)，找到一條記錄后需要繼續(xù)查找，在結(jié)構(gòu)上沒有區(qū)別，如上圖所示。

深入B+Tree

提幾個(gè)問題：

為什么B+Tree把真實(shí)的數(shù)據(jù)放到葉子節(jié)點(diǎn)，而不是內(nèi)層節(jié)點(diǎn)？

為什么我們說索引字段要盡可能短，最好是單調(diào)遞增的？

為什么復(fù)合索引存在最左匹配原則？

范圍查詢（>,<,between,like）對(duì)最左匹配有什么影響？

關(guān)于B+Tree的一些數(shù)學(xué)理論，咱們就不玩了，至少一點(diǎn)可以肯定的是：數(shù)據(jù)表的數(shù)據(jù)量N=F(樹的高度h，每個(gè)Block存儲(chǔ)的索引的個(gè)數(shù)m)。在N一定的情況下，索引字段越小，那么m會(huì)越大，這意味著h將越??！樹越低，當(dāng)然查找的更快！

如果內(nèi)層節(jié)點(diǎn)存放真實(shí)的數(shù)據(jù)，顯然m會(huì)變小，樹將變高。

在實(shí)際應(yīng)用中，我們應(yīng)該盡可能采用單調(diào)遞增的字段作為主鍵，一方面不會(huì)使得索引的數(shù)據(jù)結(jié)構(gòu)變大，減小了索引占用的空間；另一方面也不會(huì)頻繁的分裂B+Tree，使得效率下降。

比如復(fù)合索引(name,age,sex)，B+Tree會(huì)優(yōu)先比較name來確定下一步的搜索方向。如果突然來了個(gè)(age,sex)，根本上就無從下手。這也是符合常理的，對(duì)于一本書，我們說“找到第幾章第幾節(jié)的XXX”，從沒有聽說過“找到第幾節(jié)的XXX”！這是復(fù)合索引的重要特性，即最左匹配特性。

假設(shè)存在復(fù)合索引(name,age,sex)，我們?cè)谶M(jìn)行select的時(shí)候，并沒有按照這個(gè)順序進(jìn)行，而是sex = 'man' and name = '***z' and age = 27，是否會(huì)使用索引呢？數(shù)據(jù)庫是很聰明的，在SQL優(yōu)化的時(shí)候，會(huì)自動(dòng)幫助我們調(diào)整！但是如果缺失了復(fù)合索引的第一列，數(shù)據(jù)庫也將無能為力呢。

對(duì)于最左匹配，MySQL會(huì)一直向右匹配直到遇到范圍查詢就停止匹配。什么意思？比如復(fù)合索引(name,age,sex)，對(duì)于name = 'zhangfengzhe' and age > 26 and sex = 'man'，實(shí)際上只利用到了復(fù)合索引的name列。

想利用索引，就得“干凈”

什么叫“干凈”？就是不要讓索引參與計(jì)算！比如在索引上應(yīng)用函數(shù)，很可能導(dǎo)致索引失效。為什么呢？

其實(shí)不用想，B+Tree上存儲(chǔ)的是數(shù)據(jù)，要比較的話，需要把所有的數(shù)據(jù)都應(yīng)用上函數(shù)，顯然成本太大。

想建立索引，看看區(qū)分度

索引雖然物美價(jià)廉，但是也別亂來。count(distinct col) / count(*)可以算一下col的區(qū)分度，顯然對(duì)于主鍵而言，就是1。區(qū)分度太低的話，可以考慮下，是否還有必要建立索引呢？

Hash索引

這里并不是要深入分析Hash索引，而是要說明一下Hash的思想真是無處不在！在MySQL的Memory存儲(chǔ)引擎中，存在hash函數(shù)，給一個(gè)key，通過hash函數(shù)進(jìn)行計(jì)算得到地址，所以通常情況下，hash索引查找，會(huì)非常快，O（1）的速度。但是也存在hash沖突，和HashMap一樣，通過單鏈表的形式解決。

思考下，hash索引是否支持范圍查詢呢？

顯然是不支持的，它只能給一個(gè)KEY去查找。就如同HashMap一樣，查找key包含"zhangfengzhe"的，會(huì)很快么？

SQL優(yōu)化神器：explain

SQL優(yōu)化的場(chǎng)景很多，網(wǎng)上的技巧也很多，完全記不??！

要想徹底解決這個(gè)問題，我想只有把索引背后的數(shù)據(jù)結(jié)構(gòu)和原理做適當(dāng)?shù)睦斫?，遇到書寫SQL或者SQL慢查詢的時(shí)候，我們有基礎(chǔ)去分析，再利用好explain工具去驗(yàn)證，就應(yīng)該問題不大呢。

explain查詢的結(jié)果，可以告訴你哪些索引正在被使用，表是如何被掃描的等等。這里我將演示個(gè)Demo。

數(shù)據(jù)表student：

注意復(fù)合索引(age,address)

符合最左前綴匹配

復(fù)合索引失效

OK，到這里，準(zhǔn)備結(jié)束了，查詢?nèi)菀?，?yōu)化不易，且寫且珍惜！