mysql的外掛程式式的儲存引擎

標籤：

轉載 https://www.zhihu.com/question/19866767/answer/14942009

http://whuai.blog.51cto.com/3539000/862197

• Oracle： 資料檔案包括：控制檔案、資料檔案、重做記錄檔、參數檔案、歸檔檔案、密碼檔案。這是根據檔案功能行進行劃分，並且所有檔案都是二進位編碼後的檔案，對資料庫演算法效率有極大的提高。由於Oracle檔案管理的統一性，就可以對SQL執行過程中的解析和最佳化，指定統一的標準：
  RBO（基於規則的最佳化器）、CBO（基於成本的最佳化器）
  通過最佳化器的選擇，以及無敵的HINT規則，給與了SQL最佳化極大的自由，對CPU、記憶體、IO資源進行方方面面的最佳化。
• MySQL：最大的一個特色，就是自由選擇儲存引擎。每個表都是一個檔案，都可以選擇合適的儲存引擎。常見的引擎有 InnoDB、 MyISAM、 NDBCluster等。但由於這種開放外掛程式式的儲存引擎，比如要求資料庫與引擎之間的松耦合關係。從而導致檔案的一致性大大降低。在SQL執行最佳化方面，也就有著一些不可避免的瓶頸。在多表關聯、子查詢最佳化、統計函數等方面是軟肋，而且只支援極簡單的HINT。
• SQL Server ：資料架構基本是縱向劃分，分為：Protocol Layer（協議層）， Relational Engine（關聯式引擎）， Storage Engine（儲存引擎）， SQLOS。SQL執行過程就是逐層解析的過程，其中Relational Engine中的最佳化器，是基於成本的（CBO），其工作過程跟Oracle是非常相似的。在成本之上也是支援很豐富的HINT，包括：串連提示、查詢提示、表提示。
• • 如果你用其他資料庫，例如oracle或者sqlserver，你會發現，一個資料庫下有很多表，而建立表的時候不需要指定什麼儲存引擎的
    為什麼不需要指定呢，不指定是不是代表oracle和sqlserver裡面沒有儲存引擎，其實凡是資料庫都有儲存引擎，oracle和sqlserver也有
    整個oracle或sqlserver只有一個儲存引擎，所以你建立表的時候就不需要指定儲存引擎了，因為儲存引擎只有一個，肯定就是用那個了
    而mysql，他是有多個儲存引擎的，你不用這個儲存引擎可以用另一個儲存引擎，任你選擇
    
    1、基於表：他不是基於資料庫，也就是說，你建立資料庫的時候是不能指定儲存引擎的，create database xxx  engine=xx，只能是基於表的
    建立表的時候指定，為什麼不能指定資料庫的儲存引擎而只能指定表的儲存引擎呢？？
    這個問題其實很簡單：一個資料庫下面有多個表，每個表的用途可能都不一樣，有些用來做歸檔，有些是用來做曆史查詢的，有些是需要事務支援的，那麼你建立表的時候就可以根據你的表業務情況指定相應的儲存引擎來適配你的業務
    如果基於資料庫的話，那麼就不能這樣做了，無論資料庫下面的業務表是什麼業務，都只能服從建立資料庫時指定的儲存引擎，那麼就沒有意義了
    
    2、外掛程式式
    外掛程式式的意思是，你可以隨時改變你的表所使用的儲存引擎，比如你建表的時候用的innodb，用了一段時間，用得不爽，你可以隨時改儲存引擎，engine=xx，改為別的儲存引擎，這個就是外掛程式式，隨時可以更換儲存引擎，隨時插拔

    • 在MYSQL中，同樣有很多類型的系統對象，包括表、視圖、預存程序、儲存函數等，但由於MYSQL的外掛程式式儲存引擎及其它實現方面的特點，其每一種對象的緩衝方式都不同，或者說這些對象的緩衝不是通過一種統一的方式來管理的，每一種對象的緩衝都是有自己的特點，並且緩衝的內容也有很大的差異，下面先只敘述一下表對象的緩衝方式。

      表字典對象緩衝，顧名思義，是將某個表對象的字典資訊（定義內容）緩衝到記憶體中，用來提高對錶訪問的效率。在某一個表第一次被訪問過之後，在伺服器沒有關閉並且表定義沒有被修改的條件下，訪問這個表時，只需要從記憶體中找到這個已經緩衝起來的對象並且做相應操作即可，而不需要再次從系統資料表中讀取它的定義並且解析，然後再做相應的操作。

      當某一個使用者要查詢某一個表的資料時，系統首先會找到這個表。上面已經提到過，因為MYSQL實現了表的緩衝，所以首先會從緩衝中尋找這個表，表字典對象的緩衝是通過HASH表來管理的，MYSQL系統中，專門有一個HASH表（原始碼中的名字是table_def_cache）用來儲存群組織表對象。

      所以首先通過表的名字（包括了模式名）來構造一個HASH索引值（KEY），用來從HASH表中搜尋對象。

      但是對於表對象的緩衝，不只是簡單的將一些表的定義通過HASH儲存起來就算完了，那這樣的話緩衝可能沒有任何意義，或者說局限性非常大，這樣可能導致一個使用者在表對象上做了什麼標誌或者修改等都會影響到其它使用者，這種影響是不可預期的，更重要的原因是，MYSQL是外掛程式式的資料庫，每一個使用者得到表對象之後還需要將表執行個體化，這個執行個體化的對象只有自己才能使用，所以不是簡單的所有使用者都使用同一個緩衝對象即可完成的。它的緩衝其實是用了一種可以稱為“共用私人化緩衝”，看上去這個說法是矛盾的，其實並不是這樣的，它在緩衝過程中用到一個叫TABLE_SHARE的結構體，一個這個結構體唯一對應MYSQL中的一個表對象，這裡是不區分任何儲存引擎的，它實際上就是對具體一個表的定義的翻譯或映射，也就是說當需要開啟一個表的時候，這個表首先是在MYSQL的系統資料表中儲存的（當然系統資料表是分不同的儲存引擎的，不同的儲存引擎有自己的系統資料表，這裡所說的MYSQL的系統資料表應該是一種統稱，其實是具體某一個儲存引擎的系統資料表），如果要使用了，首先需要從系統資料表中將這個表的所有資訊都讀入到記憶體中來，這些資訊包括表名、模式名、所有的列資訊、列的預設值、表的字元集、對應的frm檔案的路徑、所屬的儲存引引擎（MYSQL中的表可以單獨定義自己的儲存引擎）、主鍵等等，當然還有很多其它資訊，所有這些資訊讀入記憶體中的時候首先就是通過結構體TABLE_SHARE來儲存的，相當於這個結構體是一個表對象緩衝的第一層，同時從名字就可以看出，這個結構體是所以使用者都可以共用的一個表對象，所以它是靜態，不允許修改的（記憶體中），從系統資料表中讀取進來之後直到這個表從緩衝中刪除，中間不會做任何的修改。

      那麼使用者要訪問一個表，只是構造了TABLE_SHARE是遠遠不夠的，而且這個結構體對象也不是直接給使用者使用的對象，在構造了這個結構體之後，首先需要將其緩衝起來，因為這個結構體就是我們這裡討論的核心，它就是我們要緩衝的對象，所以首先需要根據上面計算得到的KEY將這個表對象緩衝到table_def_cache中，這個快取作業到這裡就結束了。

      但是如果這個問之前已經被訪問過了，那麼就不需要再像上面一樣構造這個共用結構體了，而是直接通過HASH的KEY值在table_def_cache中找到這個共用結構體即可。

      從上面的敘述中知道，當系統得到一個SHARE對象之後，系統會真正的構造一個新的對象交給當前的操作，這個對象上面已經說過了，肯定不是TABLE_SHARE，因為這個是緩衝對象，它是靜態，唯讀，真正與使用者互動的是TABLE_SHARE的一個衍生品，它對應的結構體名字為TABLE，它是真正的在操作中被使用的對象，那麼是如何從TABLE_SHARE變為TABLE的呢？

      其實這兩個結構體的很多成員是相同的，並且可以直接複製過去，上面已經說了，TABLE_SHARE是一個靜態緩衝對象，那麼相對而言，TABLE就可以稱作是一個相對動態、一個進行中一些操作的執行個體了，TABLE中有一個成員就是直接指向了TABLE_SHARE的；還有一些成員比如record，這個是用來構造插入操作中的一條記錄的，這個系統會根據這個表定義的每一個列及其資料類型等提前構造好；field用來儲存所有這個表中的列資訊的，這個資訊其實是完全將SHARE中的資訊複製過來的。其它的一些小的細節就不敘述了，不過還有兩個很重要的東西必須要說一下。

      因為上面已經提到了，TABLE這個對象是一個動態，被執行個體化的對象，它相當於是一個被開啟的表，它已經不是在MYSQL的上層了，而是具體到了某一個儲存引擎了，所以這裡還需要對這個物件建構它的有關儲存引擎的資訊並且開啟這個表。

      因為MYSQL是一個外掛程式式的資料庫管理系統，對於表對象的管理，MYSQL層與儲存引擎層就是在這裡分開的，TABLE算是它們之間的橋樑，下層是儲存引擎，上層就是MYSQL了，對於一個MYSQL的儲存引擎，都要提供一些公用的介面來驅動其儲存引擎，這些介面包括：close_connection、savepoint_set、savepoint_rollback、savepoint_release、commit、rollback、create（建立控制代碼）、ha_create （建立一個表）、ha_open（開啟表）、ha_close（關閉表）、ha_write_row（插入一條記錄）、ha_delete_row（刪除一條記錄）、ha_reset（將執行個體恢複到新開啟的狀態）等操作，這些介面都是上層調用來操作對應的儲存引擎的，也可以被稱作是MYSQL與儲存引擎之間交流的通道。

      那麼從SHARE到TABLE之間的過渡或者叫做SHARE的執行個體化過程，首先就需要調用函數create來建立一個對應的儲存引擎控制代碼，建立之後就通過函數ha_open來開啟這個表，開啟表主要是對這個新建立的儲存引擎控制代碼進行一些初始化操作。在開啟之後，這個表的執行個體化也就算完成了，而這個已經被開啟的執行個體控制代碼就掛在TABLE結構體中，從這裡也可以看出，TABLE是與一個操作對應的執行個體化的對象，它只能在同一時間內被一個操作所使用。

      在被執行個體化之後，這個就可以直接與儲存引擎進行互動了，比如插入一條記錄，可以直接調用TABLE下面已經被執行個體化的儲存引擎控制代碼下的函數ha_write_row即可。

      當一個操作完成之後，它所執行個體化的表就不需要了，此時系統不是將這個本地的執行個體直接釋放掉，而是將其儲存下來了，儲存下來是為了下次某一個使用者再次訪問這個表的時候不需要再次進行執行個體化了，直接拿過來用即可，當然可能需要一些額外的操作，比如將執行個體狀態恢複，調用函數ha_reset即可。

      系統儲存執行個體是直接將其放在SHARE的一個free_tables鏈表中，但首先要從used_tables鏈表上摘下來，這兩個鏈表都是用來儲存這個表的所有執行個體的，used_tables用來儲存正在使用的執行個體，free_tables用來儲存所有當前未使用的執行個體，有可能在並發比較高的情況下，可能在used_tables中有多個，但free_tables中沒有，都執行完成之後則相反，那麼如果此時再有使用者又操作這個表，系統可以直接從free_tables找一個拿來用即可。

      現在可以知道，在MYSQL中，表對象的緩衝其實是用兩個部分，一部分是SHARE的緩衝，也就是說多個不同的表的SHARE對象的緩衝；另一部分就是每一個SHARE結構被執行個體化之後的執行個體對象的緩衝，MYSQL用來管理緩衝空間大小的方法是通過計數來實現的，預設情況下，系統中總的SHARE個數不能超過400個，所有SHARE的所有表執行個體的個數也不能超過400個。

      上面提到的都是關於表對象SHARE結構的緩衝，既然是緩衝，肯定相應的有它被刪除或者淘汰的問題，當然在這裡也不例外。那麼在什麼情況下SHARE結構會被淘汰或者刪除呢？很明顯，如果只是對這個表進行增刪改等沒有涉及到修改表定義的操作，SHARE是不會被刪除的，只有可能會被淘汰，因為如果查詢太多表的話，表對象緩衝個數是有限制的，當到達這個數目之後，系統會自動將一些不經常使用的SHARE淘汰掉，這個很容易理解。

      那麼一般情況下，只有對錶結構、依賴關係、表定義等方面進行修改之後，因為這個表的版本被更新了，如果繼續將其緩衝的話是不安全的，或者是錯誤的，或者導致一些不可預知的問題，所以這種情況下這個表對象的緩衝SHARE對象必須要從緩衝中刪除，同時要刪除它上面所以被執行個體化的表對象緩衝結構，這樣當其它使用者在等待表對象的修改操作完成之後（因為修改過程中這個表是被上了鎖的，進行操作需要等待），又一次像上面敘述的一樣，首先是從緩衝中找這個表的緩衝對象，如果找不到的話再從資料字典（系統資料表）中讀取進來，然後繼續操作即可。

      到這裡關於表的緩衝及一些其它的內容就敘述完了。

      總結：

      1）上面提到的表的緩衝機制有很大的好處的，因為它不是全字典緩衝（全字典緩衝的意思就是在資料庫啟動時將所有的資料字典資訊都一次性載入記憶體中來，這樣在使用過程中就效率非常高，但在DDL操作方面有很大的不足），它是用到的時候再載入，修改之後直接刪除有可能再重新載入，這樣的實現方式減少了DDL操作或DDL的復原導致的字典緩衝維護工作的代價。

      2）有效利用了記憶體空間，因為可以通過設定表對象緩衝空間的大小來控制記憶體的使用方式，同時只有用到的對象才會被載入到記憶體中，提高了記憶體的利用率。

      3）上面所敘述的MYSQL表緩衝實現方案雖然說是比較先進的，但是在效率方面還是有些最佳化的空間的，比如上面提到的，在用來控制緩衝空間大小是根據執行個體的個數來計算的，在系統中預設最大值是400個，如果超過這個值系統會自動淘汰一些不常用的執行個體，但是如果一個表的定義非常大，那麼並發情況下，有可能會建立很多個執行個體，假設接近400個，那麼這樣算下來有可能會將作業系統的記憶體用光，這個是不可控制的，也是不可預期的。對於SHARE的緩衝也是一樣，如果一個使用者訪問了很多不同的定義很大的表，也會有同樣的問題。

      4）從上面也可以看出，為了實現外掛程式式的資料庫，其實還是有一些效率的代價的，在表的緩衝方面，中間加入了一層SHARE的緩衝，真正用到的時候還需要執行個體化，因為每一個使用者的操作及不同時間的狀態都是不同的，所以每一個使用者必須要再在SHARE的基礎上執行個體化一個新的對象出來，這樣就給記憶體、CPU帶來了一定程式上的浪費及壓力。

      問題的解決：

      1）SHARE緩衝：我個人認為有一個更好的辦法來很精確通過具體的空間大小來管理表緩衝空間，因為對於SHARE而言，它是靜態，它是個結構體，通過使用計數來控制記憶體的使用，有可能會造成記憶體用光的情況，那麼對於SHARE對象，完全可以把它流式化（扁平化），也就是說等把這個結構體的大小計算出來，申請相應的空間，將結構體中的所有資訊都按照固定的順序寫入到這塊記憶體中，那麼這樣一個SHARE所佔的空間大小就固定了，這樣可以完全通過設定空間大小來管理表對象緩衝空間了，這樣上面提到的記憶體用光的問題就自然解決了，當然這個大小需要根據電腦的記憶體大小合理的設定，至少不會出現不可預料的問題。

      2）TABLE緩衝：TABLE執行個體的緩衝同樣存在上面的問題，解決方案與上面的思想差不多，因為這個對象是一直被用的，它是一個執行個體，所以就不能直接像上面一樣，將其流式化，而是可以通過申請一片串連的空間，這個執行個體中所有的指標或者其成員的值都指向（有可能要對齊）這個空間中的指定位置，這樣這個結構體的使用沒有任何改變，但其佔用的空間大小是固定的，同樣可以通過使用者手動設定TABLE執行個體緩衝空間的大小來管理緩衝空間，這樣也避免了表定義太大導致記憶體用光的問題。

      本文出自 “懷瑾握瑜” 部落格，請務必保留此出處http://whuai.blog.51cto.com/3539000/862197

mysql的外掛程式式的儲存引擎