共計 4018 個字符，預計需要花費 11 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章主要介紹了 MYSQL_多版本并發控制、存儲引擎、索引的示例，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

多版本并發控制

mysql 的大多數事務型存儲引擎實現的都不是簡單的行級鎖。基于提升并發性能的考慮，它們一般都同時實現了多版本并發控制。

可以認為 MVCC 是行級鎖的一種變種，但是它很多情況下避免了加鎖操作，因為開銷更低。

InnoDB 的 MVCC，是通過在每行記錄最后保存的兩個隱藏的列來實現，這兩個列，一個保存了行的創建時間，一個保存行的過期時間（或刪除時間），當然存儲的并不是實際的時間值，而是系統版本好。每開始一個新的事務，系統版本號都會自動遞增。事務開始時刻的系統版本號會作為事務的版本號，用來查詢到的每行版本號進行比較。

REPEATABLE READ 隔離級別下，MVCC 的實現：

SELECT

InnoDB 之查找版本早于當前事務版本號的數據行，這樣可以確保事務讀取的行，要么是在事務開始前已經存在，要么是事務自身插入或者修改過的。

行的刪除版本要么未定義，要么大于當前事務版本號，這可以確保事務讀取到的行在事務開始之前未被刪除。

INSERT

InnoDB 為新插入的每一行保存當前系統版本號作為行版本號。

DELETE

InnoDB 為刪除的每一行保存當前系統版本號作為行刪除標識。

UPDATE

InnoDB 為插入一航新記錄，保存當前系統版本號作為行版本號，同時保存當前系統版本號到原來的行作為行刪除版本號。

MVCC 只在 REPEATABLE READ 跟 READ COMMITED 兩個隔離級別工作。其他兩個隔離級別都和 MVCC 不兼容。因為 READ UNCOMMITED 總是讀取最新的數據行，而不是符合當前事務版本的數據行。而 SERIALIZABLE 則會對所有讀取的數據的行都加鎖。

存儲引擎 InnoDB 存儲引擎

InnoDB 是 MYSQL 的默認事務型引擎，也是最重要、使用最廣泛的存儲引擎。除非有非常特別的原因需要使用其他的存儲引擎，否則應該優先考慮 InnoDB 引擎。

InnoDB 采用 MVCC 來支持高并發，并且實現了四個標準的隔離級別。默認級別是 REPEATABLE READ（可重復讀），并且通過間隙鎖 +MVCC 策略防止幻讀的實現，間隙鎖使得 InnoDB 不僅僅鎖定查詢設計的行，還會對索引中的間隙進行鎖定，以防止幻影行的插入。

間隙鎖：當我們用范圍條件而不是相等條件檢索數據，并請求共享或排他鎖時，InnoDB 會給符合條件的已有數據記錄的索引項加鎖；對于鍵值在條件范圍內但并不存在的記錄，叫做“間隙（GAP)”，InnoDB 也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key 鎖）。
參考：間隙鎖（Next-Key 鎖）

主索引是聚簇索引，在索引中保存了數據，從而避免直接讀取磁盤，因此對查詢性能有很大的提升。

InnoDB 內部做了很多優化，包括從磁盤讀取數據時采用的可預測性預讀，能夠自動在內存中創建 hash 索引以加速度操作的自適應哈希索引，以及能夠加速插入操作的插入緩沖區等。

MyISAM 存儲引擎

在 mysql5.1 以及之前的版本，MyISAM 是默認的存儲引擎。MyISAM 提供了大量的特性，包括全文索引、壓縮、空間函數等，但是不支持事務和行級鎖，而且有一個毫無疑問的缺陷是崩潰之后無法安全恢復。

對于只讀的數據、或者表比較小、可以忍受修復操作，則依然可以使用 MyISAM 引擎。

創建 MyISAM 表的時候，如果指定了 DELAY_KEY_WRITE 選項，在每次修改執行完成時，不會立刻將修改的索引數據寫入磁盤，而是會寫到內存中的鍵緩沖區，只有在清理鍵緩沖區或者關閉表的時候才會將對應的索引塊寫入到磁盤。這種方式可以極大地提升寫入性能，但是在數據庫或者主機崩潰時會造成索引損壞，需要執行修復操作。

比較

事務：InnoDB 支持事務，MyISAM 不支持事務。

鎖粒度：InnoDB 支持表級鎖跟行級鎖，而 MyISAM 只支持表級鎖。

外鍵：InnoDB 支持外鍵。

備份：InnoDB 支持熱備份，但需要工具。

崩潰恢復：MyISAM 崩潰后發生損壞的概率比 InnoDB 高很多，而且恢復的速度也比較慢。

其他特性：MyISAM 支持全文索引、壓縮、空間函數等特性。

備份的類型

冷備（cold backup）：需要關 mysql 服務，讀寫請求均不允許狀態下進行；

溫備（warm backup）：服務在線，但僅支持讀請求，不允許寫請求；

熱備（hot backup）：備份的同時，業務不受影響。

索引

索引（也叫做“鍵 (key)”）是存儲引擎用于快速查找記錄中的一種數據結構。

B-Tree 索引

大多數 mysql 引擎都支持這種索引。

雖然使用術語“B-Tree，但是不同的存儲引擎可能使用不同的存儲結構，NDB 集群存儲引擎內部實際用的是 T -Tree，InnoDB 則使用 B +Tree。

B-Tree 索引能夠加快訪問數據的速度，因為存儲引擎不需要進行全表掃描來獲取需要的數據，取而代之的是從索引的根節點開始搜索，因此查找速度會快很多。

B-Tree 對索引列是順序組織存儲的，很適合查找范圍數據。因為索引樹是有序的，所以除了用戶查找，還可以用來排序和分組。

可以指定多個列作為索引列，多個索引列共同組成索引鍵。B-Tree 索引適用于全鍵值、鍵值范圍或鍵前綴查找，其中鍵前綴查找只適用與根據最左前綴查找。查找一定得按照索引的最左列開始。

B-Tree 索引的數據結構 B -Tree

為了描述 B -Tree，首先定義一條數據記錄為二元組 [key,data]，key 作為記錄的鍵值，對于不同數據記錄，key 是互不相同的，data 為數據記錄除 key 外的數據。

所有節點具有相同的深度，也就是說 B -Tree 是平衡的。

一個節點中的 key 從左到右非遞減排列。

如果某個指針的左右相鄰 key 分別是 keyi 和 keyi+1，且不為 null，則該指針指向節點的所有 key 大于等于 keyi 且小于等于 keyi+1。

查找算法：首先在根節點進行二分查找，如果找到則返回對應節點的 data，否則在相應區間的指針指向的節點遞歸進行查找。

由于插入刪除新的數據記錄會破壞 B -Tree 的性質，因此在插入刪除時，需要對樹進行一個分裂、合并、旋轉等操作以保持 B-Tree 性質。

B+Tree

與 B -Tree 相比，B+Tree 有以下特點：

每個節點的指針上限為 2d 而不是 2d+1（d 為 B -Tree 的度）。

內節點不存儲 data，只存儲 key；外節點不存儲指針。

帶有順序訪問指針的 B +Tree

一般在數據庫系統或文件系統中使用的 B +Tree 結構都在經典 B +Tree 的基礎上進行了優化，增加了順序訪問指針。

這個優化的目的是為了提供區間訪問的性能，例如圖中如果要查詢 key 為 18 到 49 的所有記錄。

優勢

紅黑樹等平衡樹也可以用來實現索引，但是文件系統及數據庫系統普遍采用 B -Tree 作為索引結構，主要有以下兩個原因：

更好的檢索次數：平衡樹檢索數據的時間復雜度等于樹高 h，而樹高大致為 O(h) = O(logN)，其中 d 為每個節點的出度。紅黑樹的出度為 2，而 B -Tree 的出度一般都很大，紅黑樹的樹高 h 明顯比 B -Tree 打非常多，因此檢索次數也就更多。B+Tree 相比較 B -Tree 更合適外存索引，因為 B +Tree 內節點去掉了 data 域，因此可以擁有更大的出度，檢索效率會更高。

利用計算機預讀特性：為了減少磁盤 I/O，磁盤往往不是嚴格按需讀取，而是每次都會預讀。這樣做的理論依據是計算機科學中著名的局部性原理：當一個數據被用到時，其附近的數據也通常會馬上被使用。預讀過程中，磁盤進行順序讀取，順序讀取不需要進行磁盤尋道，并且只需要很短的旋轉時間，因此速度會非常快。操作系統一般將內存和磁盤分割成固態大小的塊，每一塊稱為一頁，內存與磁盤以頁為單位交換數據。數據庫系統將索引的一個節點的大小設置為頁的大小，使得一次 I/O 就能完全載入一個節點，并且可以利用預讀特性，相鄰的節點也能夠被預先載入。

參考：MySQL 索引背后的數據結構及算法原理

哈希索引

InnoDB 引擎有一個特殊的功能叫“自適應哈希索引”，當某個索引值被使用得非常頻繁，會在 B +Tree 索引之上再創建一個哈希索引，這樣就讓 B +Tree 索引具有哈希索引的一些優點，比如快速的哈希查找。

哈希索引能在 O(1) 時間進行查找，但是失去了有序性，它具有以下限制：

哈希索引只包含哈希值跟行指針，而不存儲字段值，所以不能使用索引中的值來 I 避免都去行。

無法用于排序與分組。

只支持精確查找，無法用于部分查找與范圍查找。

當出現哈希沖突時，存儲引擎必須遍歷鏈表中的所有行指針。

空間數據索引（R-Tree)

MyISAM 表支持空間索引，可以用作地理數據存儲。空間索引會從所有維度來索引數據，查詢時可以根據任意維度來組合查詢。

必須使用 Mysql 的 GIS 相關函數如 MBRONTAINS() 等來維護數據。

全文索引

全文索引是一種特殊類型的索引，它查找的是文本中的關鍵字，而不是直接比較索引中的值。查找條件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排序索引實現，它記錄著關鍵詞到期所在文檔的映射。

MyISAM 存儲引擎支持全文索引，InnoDB 存儲引擎在 Mysql 5.6.4 版本中也開始支持全文索引。

索引的優點

大大減少了服務器需要掃描的數據行數。

幫助服務器避免進行排序和創建臨時表（B+Tree 索引是有序的，可以用來 Order by 和 group by 操作）。

將隨機 I / O 變為順序 I /O（B+Tree 索引是有序的，也就將相鄰的數據都存儲到一起）。

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“MYSQL_多版本并發控制、存儲引擎、索引的示例”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!