重新學(xué)習(xí)Mysql數(shù)據(jù)庫2：『淺入淺出』MySQL 和 InnoDB

本文轉(zhuǎn)自互聯(lián)網(wǎng)

本系列文章將整理到我在GitHub上的《Java面試指南》倉庫，更多精彩內(nèi)容請到我的倉庫里查看

https://github.com/h3pl/Java-Tutorial

喜歡的話麻煩點下Star哈

文章首發(fā)于我的個人博客：

www.how2playlife.com

本文是微信公眾號【Java技術(shù)江湖】的《重新學(xué)習(xí)MySQL數(shù)據(jù)庫》其中一篇，本文部分內(nèi)容來源于網(wǎng)絡(luò)，為了把本文主題講得清晰透徹，也整合了很多我認為不錯的技術(shù)博客內(nèi)容，引用其中了一些比較好的博客文章，如有侵權(quán)，請聯(lián)系作者。

該系列博文會告訴你如何從入門到進階，從sql基本的使用方法，從MySQL執(zhí)行引擎再到索引、事務(wù)等知識，一步步地學(xué)習(xí)MySQL相關(guān)技術(shù)的實現(xiàn)原理，更好地了解如何基于這些知識來優(yōu)化sql，減少SQL執(zhí)行時間，通過執(zhí)行計劃對SQL性能進行分析，再到MySQL的主從復(fù)制、主備部署等內(nèi)容，以便讓你更完整地了解整個MySQL方面的技術(shù)體系，形成自己的知識框架。

如果對本系列文章有什么建議，或者是有什么疑問的話，也可以關(guān)注公眾號【Java技術(shù)江湖】聯(lián)系作者，歡迎你參與本系列博文的創(chuàng)作和修訂。

作為一名開發(fā)人員，在日常的工作中會難以避免地接觸到數(shù)據(jù)庫，無論是基于文件的 sqlite 還是工程上使用非常廣泛的 MySQL、PostgreSQL，但是一直以來也沒有對數(shù)據(jù)庫有一個非常清晰并且成體系的認知，所以最近兩個月的時間看了幾本數(shù)據(jù)庫相關(guān)的書籍并且閱讀了 MySQL 的官方文檔，希望對各位了解數(shù)據(jù)庫的、不了解數(shù)據(jù)庫的有所幫助。

本文中對于數(shù)據(jù)庫的介紹以及研究都是在 MySQL 上進行的，如果涉及到了其他數(shù)據(jù)庫的內(nèi)容或者實現(xiàn)會在文中單獨指出。

數(shù)據(jù)庫的定義

很多開發(fā)者在最開始時其實都對數(shù)據(jù)庫有一個比較模糊的認識，覺得數(shù)據(jù)庫就是一堆數(shù)據(jù)的集合，但是實際卻比這復(fù)雜的多，數(shù)據(jù)庫領(lǐng)域中有兩個詞非常容易混淆，也就是 數(shù)據(jù)庫和 實例：

• 數(shù)據(jù)庫：物理操作文件系統(tǒng)或其他形式文件類型的集合；
• 實例：MySQL 數(shù)據(jù)庫由后臺線程以及一個共享內(nèi)存區(qū)組成；

對于數(shù)據(jù)庫和實例的定義都來自于 MySQL 技術(shù)內(nèi)幕：InnoDB 存儲引擎 一書，想要了解 InnoDB 存儲引擎的讀者可以閱讀這本書籍。

數(shù)據(jù)庫和實例

在 MySQL 中，實例和數(shù)據(jù)庫往往都是一一對應(yīng)的，而我們也無法直接操作數(shù)據(jù)庫，而是要通過數(shù)據(jù)庫實例來操作數(shù)據(jù)庫文件，可以理解為數(shù)據(jù)庫實例是數(shù)據(jù)庫為上層提供的一個專門用于操作的接口。

在 Unix 上，啟動一個 MySQL 實例往往會產(chǎn)生兩個進程， mysqld 就是真正的數(shù)據(jù)庫服務(wù)守護進程，而 mysqld_safe 是一個用于檢查和設(shè)置 mysqld 啟動的控制程序，它負責(zé)監(jiān)控 MySQL 進程的執(zhí)行，當(dāng) mysqld 發(fā)生錯誤時， mysqld_safe 會對其狀態(tài)進行檢查并在合適的條件下重啟。

MySQL 的架構(gòu)

MySQL 從第一個版本發(fā)布到現(xiàn)在已經(jīng)有了 20 多年的歷史，在這么多年的發(fā)展和演變中，整個應(yīng)用的體系結(jié)構(gòu)變得越來越復(fù)雜：

最上層用于連接、線程處理的部分并不是 MySQL 『發(fā)明』的，很多服務(wù)都有類似的組成部分；第二層中包含了大多數(shù) MySQL 的核心服務(wù)，包括了對 SQL 的解析、分析、優(yōu)化和緩存等功能，存儲過程、觸發(fā)器和視圖都是在這里實現(xiàn)的；而第三層就是 MySQL 中真正負責(zé)數(shù)據(jù)的存儲和提取的存儲引擎，例如： InnoDB、 MyISAM 等，文中對存儲引擎的介紹都是對 InnoDB 實現(xiàn)的分析。

數(shù)據(jù)的存儲

在整個數(shù)據(jù)庫體系結(jié)構(gòu)中，我們可以使用不同的存儲引擎來存儲數(shù)據(jù)，而絕大多數(shù)存儲引擎都以二進制的形式存儲數(shù)據(jù)；這一節(jié)會介紹 InnoDB 中對數(shù)據(jù)是如何存儲的。

在 InnoDB 存儲引擎中，所有的數(shù)據(jù)都被 邏輯地存放在表空間中，表空間（tablespace）是存儲引擎中最高的存儲邏輯單位，在表空間的下面又包括段（segment）、區(qū)（extent）、頁（page）：

同一個數(shù)據(jù)庫實例的所有表空間都有相同的頁大小；默認情況下，表空間中的頁大小都為 16KB，當(dāng)然也可以通過改變 innodb_page_size 選項對默認大小進行修改，需要注意的是不同的頁大小最終也會導(dǎo)致區(qū)大小的不同：

從圖中可以看出，在 InnoDB 存儲引擎中，一個區(qū)的大小最小為 1MB，頁的數(shù)量最少為 64 個。

如何存儲表

MySQL 使用 InnoDB 存儲表時，會將 表的定義和 數(shù)據(jù)索引等信息分開存儲，其中前者存儲在 .frm 文件中，后者存儲在 .ibd 文件中，這一節(jié)就會對這兩種不同的文件分別進行介紹。

.frm 文件

無論在 MySQL 中選擇了哪個存儲引擎，所有的 MySQL 表都會在硬盤上創(chuàng)建一個 .frm 文件用來描述表的格式或者說定義； .frm 文件的格式在不同的平臺上都是相同的。

 CREATE TABLE test_frm(    column1 CHAR(5),    column2 INTEGER);

當(dāng)我們使用上面的代碼創(chuàng)建表時，會在磁盤上的 datadir 文件夾中生成一個 test_frm.frm 的文件，這個文件中就包含了表結(jié)構(gòu)相關(guān)的信息：

MySQL 官方文檔中的 11.1 MySQL .frm File Format 一文對于 .frm文件格式中的二進制的內(nèi)容有著非常詳細的表述，在這里就不展開介紹了。

.ibd 文件

InnoDB 中用于存儲數(shù)據(jù)的文件總共有兩個部分，一是系統(tǒng)表空間文件，包括 ibdata1、 ibdata2 等文件，其中存儲了 InnoDB 系統(tǒng)信息和用戶數(shù)據(jù)庫表數(shù)據(jù)和索引，是所有表公用的。

當(dāng)打開 innodb_file_per_table 選項時， .ibd 文件就是每一個表獨有的表空間，文件存儲了當(dāng)前表的數(shù)據(jù)和相關(guān)的索引數(shù)據(jù)。

如何存儲記錄

與現(xiàn)有的大多數(shù)存儲引擎一樣，InnoDB 使用頁作為磁盤管理的最小單位；數(shù)據(jù)在 InnoDB 存儲引擎中都是按行存儲的，每個 16KB 大小的頁中可以存放 2-200 行的記錄。

當(dāng) InnoDB 存儲數(shù)據(jù)時，它可以使用不同的行格式進行存儲；MySQL 5.7 版本支持以下格式的行存儲方式：

Antelope 是 InnoDB 最開始支持的文件格式，它包含兩種行格式 Compact 和 Redundant，它最開始并沒有名字；Antelope 的名字是在新的文件格式 Barracuda 出現(xiàn)后才起的，Barracuda 的出現(xiàn)引入了兩種新的行格式 Compressed 和 Dynamic；InnoDB 對于文件格式都會向前兼容，而官方文檔中也對之后會出現(xiàn)的新文件格式預(yù)先定義好了名字：Cheetah、Dragon、Elk 等等。

兩種行記錄格式 Compact 和 Redundant 在磁盤上按照以下方式存儲：

Compact 和 Redundant 格式最大的不同就是記錄格式的第一個部分；在 Compact 中，行記錄的第一部分倒序存放了一行數(shù)據(jù)中列的長度（Length），而 Redundant 中存的是每一列的偏移量（Offset），從總體上上看，Compact 行記錄格式相比 Redundant 格式能夠減少 20% 的存儲空間。

行溢出數(shù)據(jù)

當(dāng) InnoDB 使用 Compact 或者 Redundant 格式存儲極長的 VARCHAR 或者 BLOB 這類大對象時，我們并不會直接將所有的內(nèi)容都存放在數(shù)據(jù)頁節(jié)點中，而是將行數(shù)據(jù)中的前 768 個字節(jié)存儲在數(shù)據(jù)頁中，后面會通過偏移量指向溢出頁。

但是當(dāng)我們使用新的行記錄格式 Compressed 或者 Dynamic 時都只會在行記錄中保存 20 個字節(jié)的指針，實際的數(shù)據(jù)都會存放在溢出頁面中。

當(dāng)然在實際存儲中，可能會對不同長度的 TEXT 和 BLOB 列進行優(yōu)化，不過這就不是本文關(guān)注的重點了。

想要了解更多與 InnoDB 存儲引擎中記錄的數(shù)據(jù)格式的相關(guān)信息，可以閱讀 InnoDB Record Structure

數(shù)據(jù)頁結(jié)構(gòu)

頁是 InnoDB 存儲引擎管理數(shù)據(jù)的最小磁盤單位，而 B-Tree 節(jié)點就是實際存放表中數(shù)據(jù)的頁面，我們在這里將要介紹頁是如何組織和存儲記錄的；首先，一個 InnoDB 頁有以下七個部分：

每一個頁中包含了兩對 header/trailer：內(nèi)部的 Page Header/Page Directory 關(guān)心的是頁的狀態(tài)信息，而 Fil Header/Fil Trailer 關(guān)心的是記錄頁的頭信息。

在頁的頭部和尾部之間就是用戶記錄和空閑空間了，每一個數(shù)據(jù)頁中都包含 Infimum 和 Supremum 這兩個 虛擬的記錄（可以理解為占位符），Infimum 記錄是比該頁中任何主鍵值都要小的值，Supremum 是該頁中的最大值：

User Records 就是整個頁面中真正用于存放行記錄的部分，而 Free Space 就是空余空間了，它是一個鏈表的數(shù)據(jù)結(jié)構(gòu)，為了保證插入和刪除的效率，整個頁面并不會按照主鍵順序?qū)λ杏涗涍M行排序，它會自動從左側(cè)向右尋找空白節(jié)點進行插入，行記錄在物理存儲上并不是按照順序的，它們之間的順序是由 next_record 這一指針控制的。

B+ 樹在查找對應(yīng)的記錄時，并不會直接從樹中找出對應(yīng)的行記錄，它只能獲取記錄所在的頁，將整個頁加載到內(nèi)存中，再通過 Page Directory 中存儲的稀疏索引和 n_owned、 next_record 屬性取出對應(yīng)的記錄，不過因為這一操作是在內(nèi)存中進行的，所以通常會忽略這部分查找的耗時。

InnoDB 存儲引擎中對數(shù)據(jù)的存儲是一個非常復(fù)雜的話題，這一節(jié)中也只是對表、行記錄以及頁面的存儲進行一定的分析和介紹，雖然作者相信這部分知識對于大部分開發(fā)者已經(jīng)足夠了，但是想要真正消化這部分內(nèi)容還需要很多的努力和實踐。

索引

索引是數(shù)據(jù)庫中非常非常重要的概念，它是存儲引擎能夠快速定位記錄的秘密武器，對于提升數(shù)據(jù)庫的性能、減輕數(shù)據(jù)庫服務(wù)器的負擔(dān)有著非常重要的作用； 索引優(yōu)化是對查詢性能優(yōu)化的最有效手段，它能夠輕松地將查詢的性能提高幾個數(shù)量級。

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

在上一節(jié)中，我們談了行記錄的存儲和頁的存儲，在這里我們就要從更高的層面看 InnoDB 中對于數(shù)據(jù)是如何存儲的；InnoDB 存儲引擎在絕大多數(shù)情況下使用 B+ 樹建立索引，這是關(guān)系型數(shù)據(jù)庫中查找最為常用和有效的索引，但是 B+ 樹索引并不能找到一個給定鍵對應(yīng)的具體值，它只能找到數(shù)據(jù)行對應(yīng)的頁，然后正如上一節(jié)所提到的，數(shù)據(jù)庫把整個頁讀入到內(nèi)存中，并在內(nèi)存中查找具體的數(shù)據(jù)行。

B+ 樹是平衡樹，它查找任意節(jié)點所耗費的時間都是完全相同的，比較的次數(shù)就是 B+ 樹的高度；在這里，我們并不會深入分析或者動手實現(xiàn)一個 B+ 樹，只是對它的特性進行簡單的介紹。

聚集索引和輔助索引

數(shù)據(jù)庫中的 B+ 樹索引可以分為聚集索引（clustered index）和輔助索引（secondary index），它們之間的最大區(qū)別就是，聚集索引中存放著一條行記錄的全部信息，而輔助索引中只包含索引列和一個用于查找對應(yīng)行記錄的『書簽』。

聚集索引

InnoDB 存儲引擎中的表都是使用索引組織的，也就是按照鍵的順序存放；聚集索引就是按照表中主鍵的順序構(gòu)建一顆 B+ 樹，并在葉節(jié)點中存放表中的行記錄數(shù)據(jù)。

 CREATE TABLE users(    id INT NOT NULL,    first_name VARCHAR(20) NOT NULL,    last_name VARCHAR(20) NOT NULL,    age INT NOT NULL,    PRIMARY KEY(id),    KEY(last_name, first_name, age)    KEY(first_name));

如果使用上面的 SQL 在數(shù)據(jù)庫中創(chuàng)建一張表，B+ 樹就會使用 id 作為索引的鍵，并在葉子節(jié)點中存儲一條記錄中的 所有信息。

圖中對 B+ 樹的描述與真實情況下 B+ 樹中的數(shù)據(jù)結(jié)構(gòu)有一些差別，不過這里想要表達的主要意思是：聚集索引葉節(jié)點中保存的是整條行記錄，而不是其中的一部分。

聚集索引與表的物理存儲方式有著非常密切的關(guān)系，所有正常的表應(yīng)該 有且僅有一個聚集索引（絕大多數(shù)情況下都是主鍵），表中的所有行記錄數(shù)據(jù)都是按照 聚集索引的順序存放的。

當(dāng)我們使用聚集索引對表中的數(shù)據(jù)進行檢索時，可以直接獲得聚集索引所對應(yīng)的整條行記錄數(shù)據(jù)所在的頁，不需要進行第二次操作。

輔助索引

數(shù)據(jù)庫將所有的非聚集索引都劃分為輔助索引，但是這個概念對我們理解輔助索引并沒有什么幫助；輔助索引也是通過 B+ 樹實現(xiàn)的，但是它的葉節(jié)點并不包含行記錄的全部數(shù)據(jù)，僅包含索引中的所有鍵和一個用于查找對應(yīng)行記錄的『書簽』，在 InnoDB 中這個書簽就是當(dāng)前記錄的主鍵。

輔助索引的存在并不會影響聚集索引，因為聚集索引構(gòu)成的 B+ 樹是數(shù)據(jù)實際存儲的形式，而輔助索引只用于加速數(shù)據(jù)的查找，所以一張表上往往有多個輔助索引以此來提升數(shù)據(jù)庫的性能。

一張表一定包含一個聚集索引構(gòu)成的 B+ 樹以及若干輔助索引的構(gòu)成的 B+ 樹。

如果在表 users 中存在一個輔助索引 (first_name, age)，那么它構(gòu)成的 B+ 樹大致就是上圖這樣，按照 (first_name, age) 的字母順序?qū)Ρ碇械臄?shù)據(jù)進行排序，當(dāng)查找到主鍵時，再通過聚集索引獲取到整條行記錄。

上圖展示了一個使用輔助索引查找一條表記錄的過程：通過輔助索引查找到對應(yīng)的主鍵，最后在聚集索引中使用主鍵獲取對應(yīng)的行記錄，這也是通常情況下行記錄的查找方式。

索引的設(shè)計

索引的設(shè)計其實是一個非常重要的內(nèi)容，同時也是一個非常復(fù)雜的內(nèi)容；索引的設(shè)計與創(chuàng)建對于提升數(shù)據(jù)庫的查詢性能至關(guān)重要，不過這不是本文想要介紹的內(nèi)容，有關(guān)索引的設(shè)計與優(yōu)化可以閱讀 數(shù)據(jù)庫索引設(shè)計與優(yōu)化 一書，書中提供了一種非常科學(xué)合理的方法能夠幫助我們在數(shù)據(jù)庫中建立最適合的索引，當(dāng)然作者也可能會在之后的文章中對索引的設(shè)計進行簡單的介紹和分析。

鎖

我們都知道鎖的種類一般分為樂觀鎖和悲觀鎖兩種，InnoDB 存儲引擎中使用的就是悲觀鎖，而按照鎖的粒度劃分，也可以分成行鎖和表鎖。

并發(fā)控制機制

樂觀鎖和悲觀鎖其實都是并發(fā)控制的機制，同時它們在原理上就有著本質(zhì)的差別；

• 樂觀鎖是一種思想，它其實并不是一種真正的『鎖』，它會先嘗試對資源進行修改，在寫回時判斷資源是否進行了改變，如果沒有發(fā)生改變就會寫回，否則就會進行重試，在整個的執(zhí)行過程中其實都 沒有對數(shù)據(jù)庫進行加鎖；
• 悲觀鎖就是一種真正的鎖了，它會在獲取資源前對資源進行加鎖，確保同一時刻只有有限的線程能夠訪問該資源，其他想要嘗試獲取資源的操作都會進入等待狀態(tài)，直到該線程完成了對資源的操作并且釋放了鎖后，其他線程才能重新操作資源；

雖然樂觀鎖和悲觀鎖在本質(zhì)上并不是同一種東西，一個是一種思想，另一個是一種真正的鎖，但是它們都是一種并發(fā)控制機制。

樂觀鎖不會存在死鎖的問題，但是由于更新后驗證，所以當(dāng) 沖突頻率和 重試成本較高時更推薦使用悲觀鎖，而需要非常高的 響應(yīng)速度并且 并發(fā)量非常大的時候使用樂觀鎖就能較好的解決問題，在這時使用悲觀鎖就可能出現(xiàn)嚴重的性能問題；在選擇并發(fā)控制機制時，需要綜合考慮上面的四個方面（沖突頻率、重試成本、響應(yīng)速度和并發(fā)量）進行選擇。

鎖的種類

對數(shù)據(jù)的操作其實只有兩種，也就是讀和寫，而數(shù)據(jù)庫在實現(xiàn)鎖時，也會對這兩種操作使用不同的鎖；InnoDB 實現(xiàn)了標(biāo)準的行級鎖，也就是共享鎖（Shared Lock）和互斥鎖（Exclusive Lock）；共享鎖和互斥鎖的作用其實非常好理解：

• 共享鎖（讀鎖）：允許事務(wù)對一條行數(shù)據(jù)進行讀?。?/li>
• 互斥鎖（寫鎖）：允許事務(wù)對一條行數(shù)據(jù)進行刪除或更新；

而它們的名字也暗示著各自的另外一個特性，共享鎖之間是兼容的，而互斥鎖與其他任意鎖都不兼容：

稍微對它們的使用進行思考就能想明白它們?yōu)槭裁匆@么設(shè)計，因為共享鎖代表了讀操作、互斥鎖代表了寫操作，所以我們可以在數(shù)據(jù)庫中 并行讀，但是只能 串行寫，只有這樣才能保證不會發(fā)生線程競爭，實現(xiàn)線程安全。

鎖的粒度

無論是共享鎖還是互斥鎖其實都只是對某一個數(shù)據(jù)行進行加鎖，InnoDB 支持多種粒度的鎖，也就是行鎖和表鎖；為了支持多粒度鎖定，InnoDB 存儲引擎引入了意向鎖（Intention Lock），意向鎖就是一種表級鎖。

與上一節(jié)中提到的兩種鎖的種類相似的是，意向鎖也分為兩種：

• 意向共享鎖：事務(wù)想要在獲得表中某些記錄的共享鎖，需要在表上先加意向共享鎖；
• 意向互斥鎖：事務(wù)想要在獲得表中某些記錄的互斥鎖，需要在表上先加意向互斥鎖；

隨著意向鎖的加入，鎖類型之間的兼容矩陣也變得愈加復(fù)雜：

意向鎖其實不會阻塞全表掃描之外的任何請求，它們的主要目的是為了表示 是否有人請求鎖定表中的某一行數(shù)據(jù)。

有的人可能會對意向鎖的目的并不是完全的理解，我們在這里可以舉一個例子：如果沒有意向鎖，當(dāng)已經(jīng)有人使用行鎖對表中的某一行進行修改時，如果另外一個請求要對全表進行修改，那么就需要對所有的行是否被鎖定進行掃描，在這種情況下，效率是非常低的；不過，在引入意向鎖之后，當(dāng)有人使用行鎖對表中的某一行進行修改之前，會先為表添加意向互斥鎖（IX），再為行記錄添加互斥鎖（X），在這時如果有人嘗試對全表進行修改就不需要判斷表中的每一行數(shù)據(jù)是否被加鎖了，只需要通過等待意向互斥鎖被釋放就可以了。

鎖的算法

到目前為止已經(jīng)對 InnoDB 中鎖的粒度有一定的了解，也清楚了在對數(shù)據(jù)庫進行讀寫時會獲取不同的鎖，在這一小節(jié)將介紹鎖是如何添加到對應(yīng)的數(shù)據(jù)行上的，我們會分別介紹三種鎖的算法：Record Lock、Gap Lock 和 Next-Key Lock。

Record Lock

記錄鎖（Record Lock）是加到 索引記錄上的鎖，假設(shè)我們存在下面的一張表 users：

 CREATE TABLE users(    id INT NOT NULL AUTO_INCREMENT,    last_name VARCHAR(255) NOT NULL,    first_name VARCHAR(255),    age INT,    PRIMARY KEY(id),    KEY(last_name),    KEY(age));

如果我們使用 id 或者 last_name 作為 SQL 中 WHERE 語句的過濾條件，那么 InnoDB 就可以通過索引建立的 B+ 樹找到行記錄并添加索引，但是如果使用 first_name 作為過濾條件時，由于 InnoDB 不知道待修改的記錄具體存放的位置，也無法對將要修改哪條記錄提前做出判斷就會鎖定整個表。

Gap Lock

記錄鎖是在存儲引擎中最為常見的鎖，除了記錄鎖之外，InnoDB 中還存在間隙鎖（Gap Lock），間隙鎖是對索引記錄中的一段連續(xù)區(qū)域的鎖；當(dāng)使用類似 SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE; 的 SQL 語句時，就會阻止其他事務(wù)向表中插入 id = 15 的記錄，因為整個范圍都被間隙鎖鎖定了。

間隙鎖是存儲引擎對于性能和并發(fā)做出的權(quán)衡，并且只用于某些事務(wù)隔離級別。

雖然間隙鎖中也分為共享鎖和互斥鎖，不過它們之間并不是互斥的，也就是不同的事務(wù)可以同時持有一段相同范圍的共享鎖和互斥鎖，它唯一阻止的就是 其他事務(wù)向這個范圍中添加新的記錄。

Next-Key Lock

Next-Key 鎖相比前兩者就稍微有一些復(fù)雜，它是記錄鎖和記錄前的間隙鎖的結(jié)合，在 users 表中有以下記錄：

 +------|-------------|--------------|-------+|   id | last_name   | first_name   |   age ||------|-------------|--------------|-------||    4 | stark       | tony         |    21 ||    1 | tom         | hiddleston   |    30 ||    3 | morgan      | freeman      |    40 ||    5 | jeff        | dean         |    50 ||    2 | donald      | trump        |    80 |+------|-------------|--------------|-------+

如果使用 Next-Key 鎖，那么 Next-Key 鎖就可以在需要的時候鎖定以下的范圍：

 (-∞, 21](21, 30](30, 40](40, 50](50, 80](80, ∞)

既然叫 Next-Key 鎖，鎖定的應(yīng)該是當(dāng)前值和后面的范圍，但是實際上卻不是，Next-Key 鎖鎖定的是當(dāng)前值和前面的范圍。

當(dāng)我們更新一條記錄，比如 SELECT * FROM users WHERE age = 30 FOR UPDATE;，InnoDB 不僅會在范圍 (21, 30] 上加 Next-Key 鎖，還會在這條記錄后面的范圍 (30, 40] 加間隙鎖，所以插入 (21, 40] 范圍內(nèi)的記錄都會被鎖定。

Next-Key 鎖的作用其實是為了解決幻讀的問題，我們會在下一節(jié)談事務(wù)的時候具體介紹。

死鎖的發(fā)生

既然 InnoDB 中實現(xiàn)的鎖是悲觀的，那么不同事務(wù)之間就可能會互相等待對方釋放鎖造成死鎖，最終導(dǎo)致事務(wù)發(fā)生錯誤；想要在 MySQL 中制造死鎖的問題其實非常容易：

兩個會話都持有一個鎖，并且嘗試獲取對方的鎖時就會發(fā)生死鎖，不過 MySQL 也能在發(fā)生死鎖時及時發(fā)現(xiàn)問題，并保證其中的一個事務(wù)能夠正常工作，這對我們來說也是一個好消息。

事務(wù)與隔離級別

在介紹了鎖之后，我們再來談?wù)剶?shù)據(jù)庫中一個非常重要的概念 —— 事務(wù)；相信只要是一個合格的軟件工程師就對事務(wù)的特性有所了解，其中被人經(jīng)常提起的就是事務(wù)的原子性，在數(shù)據(jù)提交工作時，要么保證所有的修改都能夠提交，要么就所有的修改全部回滾。

但是事務(wù)還遵循包括原子性在內(nèi)的 ACID 四大特性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability）；文章不會對這四大特性全部展開進行介紹，相信你能夠通過 Google 和數(shù)據(jù)庫相關(guān)的書籍輕松獲得有關(guān)它們的概念，本文最后要介紹的就是事務(wù)的四種隔離級別。

幾種隔離級別

事務(wù)的隔離性是數(shù)據(jù)庫處理數(shù)據(jù)的幾大基礎(chǔ)之一，而隔離級別其實就是提供給用戶用于在性能和可靠性做出選擇和權(quán)衡的配置項。

ISO 和 ANIS SQL 標(biāo)準制定了四種事務(wù)隔離級別，而 InnoDB 遵循了 SQL:1992 標(biāo)準中的四種隔離級別： READ UNCOMMITED、 READ COMMITED、 REPEATABLE READ 和 SERIALIZABLE；每個事務(wù)的隔離級別其實都比上一級多解決了一個問題：

• RAED UNCOMMITED：使用查詢語句不會加鎖，可能會讀到未提交的行（Dirty Read）；
• READ COMMITED：只對記錄加記錄鎖，而不會在記錄之間加間隙鎖，所以允許新的記錄插入到被鎖定記錄的附近，所以再多次使用查詢語句時，可能得到不同的結(jié)果（Non-Repeatable Read）；
• REPEATABLE READ：多次讀取同一范圍的數(shù)據(jù)會返回第一次查詢的快照，不會返回不同的數(shù)據(jù)行，但是可能發(fā)生幻讀（Phantom Read）；
• SERIALIZABLE：InnoDB 隱式地將全部的查詢語句加上共享鎖，解決了幻讀的問題；

MySQL 中默認的事務(wù)隔離級別就是 REPEATABLE READ，但是它通過 Next-Key 鎖也能夠在某種程度上解決幻讀的問題。

接下來，我們將數(shù)據(jù)庫中創(chuàng)建如下的表并通過個例子來展示在不同的事務(wù)隔離級別之下，會發(fā)生什么樣的問題：

 CREATE TABLE test(    id INT NOT NULL,    UNIQUE(id));

臟讀

在一個事務(wù)中，讀取了其他事務(wù)未提交的數(shù)據(jù)。

當(dāng)事務(wù)的隔離級別為 READ UNCOMMITED 時，我們在 SESSION 2 中插入的 未提交數(shù)據(jù)在 SESSION 1 中是可以訪問的。

不可重復(fù)讀

在一個事務(wù)中，同一行記錄被訪問了兩次卻得到了不同的結(jié)果。

當(dāng)事務(wù)的隔離級別為 READ COMMITED 時，雖然解決了臟讀的問題，但是如果在 SESSION 1 先查詢了 一行數(shù)據(jù)，在這之后 SESSION 2 中修改了同一行數(shù)據(jù)并且提交了修改，在這時，如果 SESSION 1 中再次使用相同的查詢語句，就會發(fā)現(xiàn)兩次查詢的結(jié)果不一樣。

不可重復(fù)讀的原因就是，在 READ COMMITED 的隔離級別下，存儲引擎不會在查詢記錄時添加行鎖，鎖定 id = 3 這條記錄。

幻讀

在一個事務(wù)中，同一個范圍內(nèi)的記錄被讀取時，其他事務(wù)向這個范圍添加了新的記錄。

重新開啟了兩個會話 SESSION 1 和 SESSION 2，在 SESSION 1 中我們查詢?nèi)淼男畔?，沒有得到任何記錄；在 SESSION 2 中向表中插入一條數(shù)據(jù)并提交；由于 REPEATABLE READ 的原因，再次查詢?nèi)淼臄?shù)據(jù)時，我們獲得到的仍然是空集，但是在向表中插入同樣的數(shù)據(jù)卻出現(xiàn)了錯誤。

這種現(xiàn)象在數(shù)據(jù)庫中就被稱作幻讀，雖然我們使用查詢語句得到了一個空的集合，但是插入數(shù)據(jù)時卻得到了錯誤，好像之前的查詢是幻覺一樣。

在標(biāo)準的事務(wù)隔離級別中，幻讀是由更高的隔離級別 SERIALIZABLE 解決的，但是它也可以通過 MySQL 提供的 Next-Key 鎖解決：

REPERATABLE READ 和 READ UNCOMMITED 其實是矛盾的，如果保證了前者就看不到已經(jīng)提交的事務(wù)，如果保證了后者，就會導(dǎo)致兩次查詢的結(jié)果不同，MySQL 為我們提供了一種折中的方式，能夠在 REPERATABLE READ 模式下加鎖訪問已經(jīng)提交的數(shù)據(jù)，其本身并不能解決幻讀的問題，而是通過文章前面提到的 Next-Key 鎖來解決。

總結(jié)

文章中的內(nèi)容大都來自于 高性能 MySQL、 MySQL 技術(shù)內(nèi)幕：InnoDB 存儲引擎、 數(shù)據(jù)庫索引設(shè)計與優(yōu)化 以及 MySQL 的 官方文檔。

由于篇幅所限僅能對數(shù)據(jù)庫中一些重要內(nèi)容進行簡單的介紹和總結(jié)，文中內(nèi)容難免有所疏漏，如果對文章內(nèi)容的有疑問，可以在博客下面評論留言。

Innodb與Myisam引擎的區(qū)別與應(yīng)用場景

1. 區(qū)別：

（1）事務(wù)處理：

MyISAM是非事務(wù)安全型的，而InnoDB是事務(wù)安全型的（支持事務(wù)處理等高級處理）；

（2）鎖機制不同：

MyISAM是表級鎖，而InnoDB是行級鎖；

（3）select ,update ,insert ,delete 操作：

MyISAM：如果執(zhí)行大量的SELECT，MyISAM是更好的選擇

InnoDB：如果你的數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE，出于性能方面的考慮，應(yīng)該使用InnoDB表

（4）查詢表的行數(shù)不同：

MyISAM：select count( ) from table,MyISAM只要簡單的讀出保存好的行數(shù)，注意的是，當(dāng)count()語句包含 where條件時，兩種表的操作是一樣的

InnoDB ： InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行

（5）外鍵支持：

mysiam表不支持外鍵，而InnoDB支持

1. 為什么MyISAM會比Innodb 的查詢速度快。

INNODB在做SELECT的時候，要維護的東西比MYISAM引擎多很多；
1）數(shù)據(jù)塊，INNODB要緩存，MYISAM只緩存索引塊， 這中間還有換進換出的減少；
2）innodb尋址要映射到塊，再到行，MYISAM 記錄的直接是文件的OFFSET，定位比INNODB要快
3）INNODB還需要維護MVCC一致；雖然你的場景沒有，但他還是需要去檢查和維護

MVCC ( Multi-Version Concurrency Control )多版本并發(fā)控制

3. 應(yīng)用場景

MyISAM適合：(1)做很多count 的計算；(2)插入不頻繁，查詢非常頻繁；(3)沒有事務(wù)。

InnoDB適合：(1)可靠性要求比較高，或者要求事務(wù)；(2)表更新和查詢都相當(dāng)?shù)念l繁，并且行鎖定的機會比較大的情況。