從程式員的角度深入了解MySQL

前言

作為一名工作了多·年的程式猿，今天我将站在程式員的角度以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的體系結構删除】

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

【小表驅動大表會快？什麼鬼？M*N和N*M不是一樣的麼？有鬼的地方，就有索引！】

探索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，到這裡，準備結束了，查詢容易，優化不易，且寫且珍惜！

為什麼某些人會一直比你優秀，是因為他本身就很優秀還一直在持續努力變得更優秀，而你是不是還在滿足于現狀内心在竊喜! 關注我，私信回複我“666"或者“Java架構"擷取免費的Java架構學習資料（裡面有高可用、高并發、高性能及分布式、Jvm性能調優、Spring源碼，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多個知識點的架構資料）合理利用自己每一分每一秒的時間來學習提升自己，不要再用"沒有時間“來掩飾自己思想上的懶惰！趁年輕，使勁拼，給未來的自己一個交代！