MYSQL_多版本并發(fā)控制、存儲引擎、索引的示例

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

多版本并發(fā)控制

mysql的大多數(shù)事務(wù)型存儲引擎實現(xiàn)的都不是簡單的行級鎖?；谔嵘l(fā)性能的考慮，它們一般都同時實現(xiàn)了多版本并發(fā)控制。

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

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

REPEATABLE READ隔離級別下，MVCC的實現(xiàn)：

• SELECT

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

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

• INSERT

• InnoDB為新插入的每一行保存當(dāng)前系統(tǒng)版本號作為行版本號。

• DELETE

• InnoDB為刪除的每一行保存當(dāng)前系統(tǒng)版本號作為行刪除標(biāo)識。

• UPDATE

• InnoDB為插入一航新記錄，保存當(dāng)前系統(tǒng)版本號作為行版本號，同時保存當(dāng)前系統(tǒng)版本號到原來的行作為行刪除版本號。

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

存儲引擎

InnoDB存儲引擎

InnoDB是MYSQL的默認(rèn)事務(wù)型引擎，也是最重要、使用最廣泛的存儲引擎。除非有非常特別的原因需要使用其他的存儲引擎，否則應(yīng)該優(yōu)先考慮InnoDB引擎。

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

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

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

InnoDB內(nèi)部做了很多優(yōu)化，包括從磁盤讀取數(shù)據(jù)時采用的可預(yù)測性預(yù)讀，能夠自動在內(nèi)存中創(chuàng)建hash索引以加速度操作的自適應(yīng)哈希索引，以及能夠加速插入操作的插入緩沖區(qū)等。

MyISAM存儲引擎

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

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

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

比較

• 事務(wù)：InnoDB支持事務(wù)，MyISAM不支持事務(wù)。

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

• 外鍵：InnoDB支持外鍵。

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

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

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

備份的類型

• 冷備（cold backup）：需要關(guān)mysql服務(wù)，讀寫請求均不允許狀態(tài)下進(jìn)行；

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

• 熱備（hot backup）：備份的同時，業(yè)務(wù)不受影響。

索引

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

B-Tree索引

大多數(shù)mysql引擎都支持這種索引。

雖然使用術(shù)語“B-Tree"，但是不同的存儲引擎可能使用不同的存儲結(jié)構(gòu)，NDB集群存儲引擎內(nèi)部實際用的是T-Tree，InnoDB則使用B+Tree。

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

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

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

B-Tree索引的數(shù)據(jù)結(jié)構(gòu)

B-Tree

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

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

• 一個節(jié)點中的key從左到右非遞減排列。

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

查找算法：首先在根節(jié)點進(jìn)行二分查找，如果找到則返回對應(yīng)節(jié)點的data，否則在相應(yīng)區(qū)間的指針指向的節(jié)點遞歸進(jìn)行查找。

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

B+Tree

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

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

• 內(nèi)節(jié)點不存儲data，只存儲key；外節(jié)點不存儲指針。

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

一般在數(shù)據(jù)庫系統(tǒng)或文件系統(tǒng)中使用的B+Tree結(jié)構(gòu)都在經(jīng)典B+Tree的基礎(chǔ)上進(jìn)行了優(yōu)化，增加了順序訪問指針。

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

優(yōu)勢

紅黑樹等平衡樹也可以用來實現(xiàn)索引，但是文件系統(tǒng)及數(shù)據(jù)庫系統(tǒng)普遍采用B-Tree作為索引結(jié)構(gòu)，主要有以下兩個原因：

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

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

參考：MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理

哈希索引

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

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

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

• 無法用于排序與分組。

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

• 當(dāng)出現(xiàn)哈希沖突時，存儲引擎必須遍歷鏈表中的所有行指針。

空間數(shù)據(jù)索引（R-Tree)

MyISAM表支持空間索引，可以用作地理數(shù)據(jù)存儲?？臻g索引會從所有維度來索引數(shù)據(jù)，查詢時可以根據(jù)任意維度來組合查詢。

必須使用Mysql的GIS相關(guān)函數(shù)如MBRONTAINS()等來維護(hù)數(shù)據(jù)。

全文索引

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

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

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

索引的優(yōu)點

• 大大減少了服務(wù)器需要掃描的數(shù)據(jù)行數(shù)。

• 幫助服務(wù)器避免進(jìn)行排序和創(chuàng)建臨時表（B+Tree索引是有序的，可以用來Order by和group by操作）。

• 將隨機(jī)I/O變?yōu)轫樞騃/O（B+Tree索引是有序的，也就將相鄰的數(shù)據(jù)都存儲到一起）。

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“MYSQL_多版本并發(fā)控制、存儲引擎、索引的示例”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!