深入掌握MySQL的知識

下文主要給大家帶來深入掌握MySQL的知識，希望這些內容能夠帶給大家實際用處，這也是我編輯深入掌握MySQL的知識這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

數據庫基本原理

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

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

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

第二，按Block or Page讀取數據

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

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

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

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

Hello，B+Tree

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

我們知道對于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：

對于以上關于深入掌握MySQL的知識，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。