數(shù)據庫基本原理

我對DB的理解

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

不必多說，數(shù)據當然需要存儲；存儲了還不夠，顯然需要提供程序對存儲的操作進行封裝，對外提供增刪改查的API，即實例。

一個存儲，可以對應多個實例，這將提高這個存儲的負載能力以及高可用；多個存儲可以分布在不同的機房、地域，將實現(xiàn)容災。

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

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

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

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

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

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

比如，利用索引，索引相比內存，是一個性價比非常高的東西，后文詳細介紹MySQL的索引原理。

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

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

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

【一個按行查找刪除，多費勁；一個基于Block的體系結構刪除】為什么我們說要小表驅動大表？【小表驅動大表會快？什么鬼？MN和NM不是一樣的么？有鬼的地方，就有索引！】

探索MySQL索引背后的原理

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

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

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

老生常談了，官網說MySQL索引是一種數(shù)據結構，索引的目的就是為了提高查詢效率。

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

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

為了應對上述問題，B+Tree索引出來了！

Hello，B+Tree

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

MyISAM引擎的B+Tree索引結構

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

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

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

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

深入B+Tree

提幾個問題：

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

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

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

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

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

如果內層節(jié)點存放真實的數(shù)據，顯然m會變小，樹將變高。

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

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

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

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

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

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

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

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

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

Hash索引

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

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

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

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

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

要想徹底解決這個問題，我想只有把索引背后的數(shù)據結構和原理做適當?shù)睦斫猓龅綍鴮慡QL或者SQL慢查詢的時候，我們有基礎去分析，再利用好explain工具去驗證，就應該問題不大呢。

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

數(shù)據表student：

注意復合索引(age,address)

符合最左前綴匹配

復合索引失效

OK，到這里，準備結束了，查詢容易，優(yōu)化不易，且寫且珍惜！