前言

作為一名工作了4年的程序猿，今天我將站在程序員的角度以MySQL為例探索數據庫的奧秘！

數據庫基本原理

我對DB的理解

第一：數據庫的組成：存儲 + 實例

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

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

第二：按Block or Page讀取數據

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

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

第三：磁盤IO是數據庫的性能瓶頸

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

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

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

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

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

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

【一個按行查找刪除，多費勁；一個基于Block的體系結構刪除】

為什么我們說要小表驅動大表？

【小表驅動大表會快？什么鬼？MN和NM不是一樣的么？有鬼的地方，就有索引！】

探索MySQL索引背后的原理

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

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

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

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

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

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

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

Hello，B+Tree

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

MyISAM引擎的B+Tree索引結構

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

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

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

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

深入B+Tree

提幾個問題：

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

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

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

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

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

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

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

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

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

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

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

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

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

想建立索引，看看區分度

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

Hash索引

這里并不是要深入分析Hash索引，而是要說明一下Hash的思想真是無處不在！

在MySQL的Memory存儲引擎中，存在hash函數，給一個key，通過hash函數進行計算得到地址，所以通常情況下，hash索引查找，會非常快，O（1）的速度。但是也存在hash沖突，和HashMap一樣，通過單鏈表的形式解決。

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

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

SQL優化神器：explain

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

要想徹底解決這個問題，我想只有把索引背后的數據結構和原理做適當的理解，遇到書寫SQL或者SQL慢查詢的時候，我們有基礎去分析，再利用好explain工具去驗證，就應該問題不大呢。

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

數據表student：

注意復合索引(age,address)

符合最左前綴匹配

復合索引失效

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