國(guó)慶在家無聊，我隨手翻了一下家里數(shù)據(jù)庫相關(guān)的書籍，這一翻我就看上癮了，因?yàn)榇髮W(xué)比較熟悉的一些數(shù)據(jù)庫范式我居然都忘了，懷揣著好奇心我就看了一個(gè)小國(guó)慶。

看的過程中我也做了一些小筆記，可能沒我之前系統(tǒng)文章那么有趣，但是絕對(duì)也是干貨十足，適合大家去回顧或者面試突擊的適合看看，也不多說先放圖。

存儲(chǔ)引擎

InnoDB

InnoDB 是 MySQL 默認(rèn)的事務(wù)型存儲(chǔ)引擎，只要在需要它不支持的特性時(shí)，才考慮使用其他存儲(chǔ)引擎。

InnoDB 采用 MVCC 來支持高并發(fā)，并且實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)隔離級(jí)別(未提交讀、提交讀、可重復(fù)讀、可串行化)。其默認(rèn)級(jí)別時(shí)可重復(fù)讀(REPEATABLE READ)，在可重復(fù)讀級(jí)別下，通過 MVCC + Next-Key Locking 防止幻讀。

主索引時(shí)聚簇索引，在索引中保存了數(shù)據(jù)，從而避免直接讀取磁盤，因此對(duì)主鍵查詢有很高的性能。

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

InnoDB 支持真正的在線熱備份，MySQL 其他的存儲(chǔ)引擎不支持在線熱備份，要獲取一致性視圖需要停止對(duì)所有表的寫入，而在讀寫混合的場(chǎng)景中，停止寫入可能也意味著停止讀取。

MyISAM

設(shè)計(jì)簡(jiǎn)單，數(shù)據(jù)以緊密格式存儲(chǔ)。對(duì)于只讀數(shù)據(jù)，或者表比較小、可以容忍修復(fù)操作，則依然可以使用它。

提供了大量的特性，包括壓縮表、空間數(shù)據(jù)索引等。

不支持事務(wù)。

不支持行級(jí)鎖，只能對(duì)整張表加鎖，讀取時(shí)會(huì)對(duì)需要讀到的所有表加共享鎖，寫入時(shí)則對(duì)表加排它鎖。但在表有讀取操作的同時(shí)，也可以往表中插入新的記錄，這被稱為并發(fā)插入(CONCURRENT INSERT)。

可以手工或者自動(dòng)執(zhí)行檢查和修復(fù)操作，但是和事務(wù)恢復(fù)以及崩潰恢復(fù)不同，可能導(dǎo)致一些數(shù)據(jù)丟失，而且修復(fù)操作是非常慢的。

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

InnoDB 和 MyISAM 的比較

• 事務(wù)：InnoDB 是事務(wù)型的，可以使用 Commit 和 Rollback 語句。
• 并發(fā)：MyISAM 只支持表級(jí)鎖，而 InnoDB 還支持行級(jí)鎖。
• 外鍵：InnoDB 支持外鍵。
• 備份：InnoDB 支持在線熱備份。
• 崩潰恢復(fù)：MyISAM 崩潰后發(fā)生損壞的概率比 InnoDB 高很多，而且恢復(fù)的速度也更慢。
• 其它特性：MyISAM 支持壓縮表和空間數(shù)據(jù)索引。

索引

B+ Tree 原理

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

B Tree 指的是 Balance Tree，也就是平衡樹，平衡樹是一顆查找樹，并且所有葉子節(jié)點(diǎn)位于同一層。

B+ Tree 是 B 樹的一種變形，它是基于 B Tree 和葉子節(jié)點(diǎn)順序訪問指針進(jìn)行實(shí)現(xiàn)，通常用于數(shù)據(jù)庫和操作系統(tǒng)的文件系統(tǒng)中。

B+ 樹有兩種類型的節(jié)點(diǎn)：內(nèi)部節(jié)點(diǎn)(也稱索引節(jié)點(diǎn))和葉子節(jié)點(diǎn)，內(nèi)部節(jié)點(diǎn)就是非葉子節(jié)點(diǎn)，內(nèi)部節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，只存儲(chǔ)索引，數(shù)據(jù)都存在葉子節(jié)點(diǎn)。

內(nèi)部節(jié)點(diǎn)中的 key 都按照從小到大的順序排列，對(duì)于內(nèi)部節(jié)點(diǎn)中的一個(gè) key，左子樹中的所有 key 都小于它，右子樹中的 key 都大于等于它，葉子節(jié)點(diǎn)的記錄也是按照從小到大排列的。

每個(gè)葉子節(jié)點(diǎn)都存有相鄰葉子節(jié)點(diǎn)的指針。

 

操作

查找

查找以典型的方式進(jìn)行，類似于二叉查找樹。起始于根節(jié)點(diǎn)，自頂向下遍歷樹，選擇其分離值在要查找值的任意一邊的子指針。在節(jié)點(diǎn)內(nèi)部典型的使用是二分查找來確定這個(gè)位置。

插入

• Perform a search to determine what bucket the new record should go into.
• If the bucket is not full(a most b - 1 entries after the insertion，b 是節(jié)點(diǎn)中的元素個(gè)數(shù)，一般是頁的整數(shù)倍),add tht record.
• Otherwise,before inserting the new record split the bucket. original node has 「(L+1)/2」itemsnew node has 「(L+1)/2」items Move 「(L+1)/2」-th key to the parent,and insert the new node to the parent.Repeat until a parent is found that need not split.
• If the root splits,treat it as if it has an empty parent ans split as outline above.
• B-trees grow as the root and not at the leaves.

刪除

和插入類似，只不過是自下而上的合并操作。

樹的常見特性

AVL 樹

平衡二叉樹，一般是用平衡因子差值決定并通過旋轉(zhuǎn)來實(shí)現(xiàn)，左右子樹樹高差不超過1，那么和紅黑樹比較它是嚴(yán)格的平衡二叉樹，平衡條件非常嚴(yán)格(樹高差只有1)，只要插入或刪除不滿足上面的條件就要通過旋轉(zhuǎn)來保持平衡。由于旋轉(zhuǎn)是非常耗費(fèi)時(shí)間的。所以 AVL 樹適用于插入/刪除次數(shù)比較少，但查找多的場(chǎng)景。

紅黑樹

通過對(duì)從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)路徑上各個(gè)節(jié)點(diǎn)的顏色進(jìn)行約束，確保沒有一條路徑會(huì)比其他路徑長(zhǎng)2倍，因而是近似平衡的。所以相對(duì)于嚴(yán)格要求平衡的AVL樹來說，它的旋轉(zhuǎn)保持平衡次數(shù)較少。適合，查找少，插入/刪除次數(shù)多的場(chǎng)景。(現(xiàn)在部分場(chǎng)景使用跳表來替換紅黑樹，可搜索“為啥 redis 使用跳表(skiplist)而不是使用 red-black?”)

B/B+ 樹

多路查找樹，出度高，磁盤IO低，一般用于數(shù)據(jù)庫系統(tǒng)中。

B + 樹與紅黑樹的比較

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

(一)磁盤 IO 次數(shù)

B+ 樹一個(gè)節(jié)點(diǎn)可以存儲(chǔ)多個(gè)元素，相對(duì)于紅黑樹的樹高更低，磁盤 IO 次數(shù)更少。

(二)磁盤預(yù)讀特性

為了減少磁盤 I/O 操作，磁盤往往不是嚴(yán)格按需讀取，而是每次都會(huì)預(yù)讀。預(yù)讀過程中，磁盤進(jìn)行順序讀取，順序讀取不需要進(jìn)行磁盤尋道。每次會(huì)讀取頁的整數(shù)倍。

操作系統(tǒng)一般將內(nèi)存和磁盤分割成固定大小的塊，每一塊稱為一頁，內(nèi)存與磁盤以頁為單位交換數(shù)據(jù)。數(shù)據(jù)庫系統(tǒng)將索引的一個(gè)節(jié)點(diǎn)的大小設(shè)置為頁的大小，使得一次 I/O 就能完全載入一個(gè)節(jié)點(diǎn)。

B + 樹與 B 樹的比較

B+ 樹的磁盤 IO 更低

B+ 樹的內(nèi)部節(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針。因此其內(nèi)部節(jié)點(diǎn)相對(duì) B 樹更小。如果把所有同一內(nèi)部結(jié)點(diǎn)的關(guān)鍵字存放在同一盤塊中，那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多。一次性讀入內(nèi)存中的需要查找的關(guān)鍵字也就越多。相對(duì)來說IO讀寫次數(shù)也就降低了。

B+ 樹的查詢效率更加穩(wěn)定

由于非葉子結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路。所有關(guān)鍵字查詢的路徑長(zhǎng)度相同，導(dǎo)致每一個(gè)數(shù)據(jù)的查詢效率相當(dāng)。

B+ 樹元素遍歷效率高

B 樹在提高了磁盤IO性能的同時(shí)并沒有解決元素遍歷的效率低下的問題。正是為了解決這個(gè)問題，B+樹應(yīng)運(yùn)而生。B+樹只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹的遍歷。而且在數(shù)據(jù)庫中基于范圍的查詢是非常頻繁的，而 B 樹不支持這樣的操作(或者說效率太低)。

MySQL 索引

索引是在存儲(chǔ)引擎層實(shí)現(xiàn)的，而不是在服務(wù)器層實(shí)現(xiàn)的，所以不同存儲(chǔ)引擎具有不同的索引類型和實(shí)現(xiàn)。

B+ Tree 索引

是大多數(shù) MySQL 存儲(chǔ)引擎的默認(rèn)索引類型。

• 因?yàn)椴辉傩枰M(jìn)行全表掃描，只需要對(duì)樹進(jìn)行搜索即可，所以查找速度快很多。
• 因?yàn)?B+ Tree 的有序性，所以除了用于查找，還可以用于排序和分組。
• 可以指定多個(gè)列作為索引列，多個(gè)索引列共同組成鍵。
• 適用于全鍵值、鍵值范圍和鍵前綴查找，其中鍵前綴查找只適用于最左前綴查找。如果不是按照索引列的順序進(jìn)行查找，則無法使用索引。

InnoDB 的 B+Tree 索引分為主索引和輔助索引。主索引的葉子節(jié)點(diǎn) data 域記錄著完整的數(shù)據(jù)記錄，這種索引方式被稱為聚簇索引。因?yàn)闊o法把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以一個(gè)表只能有一個(gè)聚簇索引。

 

輔助索引的葉子節(jié)點(diǎn)的 data 域記錄著主鍵的值，因此在使用輔助索引進(jìn)行查找時(shí)，需要先查找到主鍵值，然后再到主索引中進(jìn)行查找，這個(gè)過程也被稱作回表。

 

哈希索引

哈希索引能以 O(1) 時(shí)間進(jìn)行查找，但是失去了有序性：

• 無法用于排序與分組;
• 只支持精確查找，無法用于部分查找和范圍查找。

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

全文索引

MyISAM 存儲(chǔ)引擎支持全文索引，用于查找文本中的關(guān)鍵詞，而不是直接比較是否相等。

查找條件使用 MATCH AGAINST，而不是普通的 WHERE。

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

InnoDB 存儲(chǔ)引擎在 MySQL 5.6.4 版本中也開始支持全文索引。

空間數(shù)據(jù)索引

MyISAM 存儲(chǔ)引擎支持空間數(shù)據(jù)索引(R-Tree)，可以用于地理數(shù)據(jù)存儲(chǔ)?？臻g數(shù)據(jù)索引會(huì)從所有維度來索引數(shù)據(jù)，可以有效地使用任意維度來進(jìn)行組合查詢。

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

索引優(yōu)化

獨(dú)立的列

在進(jìn)行查詢時(shí)，索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)，否則無法使用索引。

例如下面的查詢不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5; 

多列索引

在需要使用多個(gè)列作為條件進(jìn)行查詢時(shí)，使用多列索引比使用多個(gè)單列索引性能更好。例如下面的語句中，最好把 actor_id 和 film_id 設(shè)置為多列索引。

 

SELECT film_id, actor_ id FROM sakila.film_actor 
WHERE actor_id = 1 AND film_id = 1; 

索引列的順序

讓選擇性最強(qiáng)的索引列放在前面。

索引的選擇性是指：不重復(fù)的索引值和記錄總數(shù)的比值。最大值為 1，此時(shí)每個(gè)記錄都有唯一的索引與其對(duì)應(yīng)。選擇性越高，每個(gè)記錄的區(qū)分度越高，查詢效率也越高。

例如下面顯示的結(jié)果中 customer_id 的選擇性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

 

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, 
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity, 
COUNT(*) 
FROM payment; 
 
   staff_id_selectivity: 0.0001 
customer_id_selectivity: 0.0373 
               COUNT(*): 16049 

前綴索引

對(duì)于 BLOB、TEXT 和 VARCHAR 類型的列，必須使用前綴索引，只索引開始的部分字符。

前綴長(zhǎng)度的選取需要根據(jù)索引選擇性來確定。

覆蓋索引

索引包含所有需要查詢的字段的值。

具有以下優(yōu)點(diǎn)：

• 索引通常遠(yuǎn)小于數(shù)據(jù)行的大小，只讀取索引能大大減少數(shù)據(jù)訪問量。
• 一些存儲(chǔ)引擎(例如 MyISAM)在內(nèi)存中只緩存索引，而數(shù)據(jù)依賴于操作系統(tǒng)來緩存。因此，只訪問索引可以不使用系統(tǒng)調(diào)用(通常比較費(fèi)時(shí))。
• 對(duì)于 InnoDB 引擎，若輔助索引能夠覆蓋查詢，則無需訪問主索引。

索引的優(yōu)點(diǎn)

• 大大減少了服務(wù)器需要掃描的數(shù)據(jù)行數(shù)。
• 幫助服務(wù)器避免進(jìn)行排序和分組，以及避免創(chuàng)建臨時(shí)表(B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。臨時(shí)表主要是在排序和分組過程中創(chuàng)建，不需要排序和分組，也就不需要?jiǎng)?chuàng)建臨時(shí)表)。
• 將隨機(jī) I/O 變?yōu)轫樞?I/O(B+Tree 索引是有序的，會(huì)將相鄰的數(shù)據(jù)都存儲(chǔ)在一起)。

索引的使用條件

• 對(duì)于非常小的表、大部分情況下簡(jiǎn)單的全表掃描比建立索引更高效;
• 對(duì)于中到大型的表，索引就非常有效;
• 但是對(duì)于特大型的表，建立和維護(hù)索引的代價(jià)將會(huì)隨之增長(zhǎng)。這種情況下，需要用到一種技術(shù)可以直接區(qū)分出需要查詢的一組數(shù)據(jù)，而不是一條記錄一條記錄地匹配，例如可以使用分區(qū)技術(shù)。

為什么對(duì)于非常小的表，大部分情況下簡(jiǎn)單的全表掃描比建立索引更高效?

如果一個(gè)表比較小，那么顯然直接遍歷表比走索引要快(因?yàn)樾枰乇?。

注：首先，要注意這個(gè)答案隱含的條件是查詢的數(shù)據(jù)不是索引的構(gòu)成部分，否也不需要回表操作。其次，查詢條件也不是主鍵，否則可以直接從聚簇索引中拿到數(shù)據(jù)。

查詢性能優(yōu)化

使用 explain 分析 select 查詢語句

explain 用來分析 SELECT 查詢語句，開發(fā)人員可以通過分析 Explain 結(jié)果來優(yōu)化查詢語句。

select_type

常用的有 SIMPLE 簡(jiǎn)單查詢，UNION 聯(lián)合查詢，SUBQUERY 子查詢等。

table

要查詢的表

possible_keys

The possible indexes to choose

可選擇的索引

key

The index actually chosen

實(shí)際使用的索引

rows

Estimate of rows to be examined

掃描的行數(shù)

type

索引查詢類型，經(jīng)常用到的索引查詢類型：

const：使用主鍵或者唯一索引進(jìn)行查詢的時(shí)候只有一行匹配 ref：使用非唯一索引 range：使用主鍵、單個(gè)字段的輔助索引、多個(gè)字段的輔助索引的最后一個(gè)字段進(jìn)行范圍查詢 index：和all的區(qū)別是掃描的是索引樹 all：掃描全表：

system

觸發(fā)條件：表只有一行，這是一個(gè) const type 的特殊情況

const

觸發(fā)條件：在使用主鍵或者唯一索引進(jìn)行查詢的時(shí)候只有一行匹配。

 

SELECT * FROM tbl_name WHERE primary_key=1; 
 
SELECT * FROM tbl_name 
  WHERE primary_key_part1=1 AND primary_key_part2=2; 

 

eq_ref

觸發(fā)條件：在進(jìn)行聯(lián)接查詢的，使用主鍵或者唯一索引并且只匹配到一行記錄的時(shí)候

 

SELECT * FROM ref_table,other_table 
  WHERE ref_table.key_column=other_table.column; 
 
SELECT * FROM ref_table,other_table 
  WHERE ref_table.key_column_part1=other_table.column 
  AND ref_table.key_column_part2=1; 

ref

觸發(fā)條件：使用非唯一索引

 

SELECT * FROM ref_table WHERE key_column=expr; 
 
SELECT * FROM ref_table,other_table 
  WHERE ref_table.key_column=other_table.column; 
 
SELECT * FROM ref_table,other_table 
  WHERE ref_table.key_column_part1=other_table.column 
  AND ref_table.key_column_part2=1; 

 

range

觸發(fā)條件：只有在使用主鍵、單個(gè)字段的輔助索引、多個(gè)字段的輔助索引的最后一個(gè)字段進(jìn)行范圍查詢才是 range

 

SELECT * FROM tbl_name 
  WHERE key_column = 10; 
 
SELECT * FROM tbl_name 
  WHERE key_column BETWEEN 10 and 20; 
 
SELECT * FROM tbl_name 
  WHERE key_column IN (10,20,30); 
 
SELECT * FROM tbl_name 
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30); 

 

index

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

觸發(fā)條件：

• 只掃描索引樹

1)查詢的字段是索引的一部分，覆蓋索引。 2)使用主鍵進(jìn)行排序

 

all

觸發(fā)條件：全表掃描，不走索引

優(yōu)化數(shù)據(jù)訪問

減少請(qǐng)求的數(shù)據(jù)量

• 只返回必要的列：最好不要使用 SELECT * 語句。
• 只返回必要的行：使用 LIMIT 語句來限制返回的數(shù)據(jù)。
• 緩存重復(fù)查詢的數(shù)據(jù)：使用緩存可以避免在數(shù)據(jù)庫中進(jìn)行查詢，特別在要查詢的數(shù)據(jù)經(jīng)常被重復(fù)查詢時(shí)，緩存帶來的查詢性能提升將會(huì)是非常明顯的。

減少服務(wù)器端掃描的行數(shù)

最有效的方式是使用索引來覆蓋查詢。

重構(gòu)查詢方式

切分大查詢

一個(gè)大查詢?nèi)绻淮涡詧?zhí)行的話，可能一次鎖住很多數(shù)據(jù)、占滿整個(gè)事務(wù)日志、耗盡系統(tǒng)資源、阻塞很多小的但重要的查詢。

 

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH); 
 
rows_affected = 0 
do { 
    rows_affected = do_query( 
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000") 
} while rows_affected > 0 

分解大連接查詢

將一個(gè)大連接查詢分解成對(duì)每一個(gè)表進(jìn)行一次單表查詢，然后在應(yīng)用程序中進(jìn)行關(guān)聯(lián)，這樣做的好處有：

• 讓緩存更高效。對(duì)于連接查詢，如果其中一個(gè)表發(fā)生變化，那么整個(gè)查詢緩存就無法使用。而分解后的多個(gè)查詢，即使其中一個(gè)表發(fā)生變化，對(duì)其它表的查詢緩存依然可以使用。
• 分解成多個(gè)單表查詢，這些單表查詢的緩存結(jié)果更可能被其它查詢使用到，從而減少冗余記錄的查詢。
• 減少鎖競(jìng)爭(zhēng);
• 在應(yīng)用層進(jìn)行連接，可以更容易對(duì)數(shù)據(jù)庫進(jìn)行拆分，從而更容易做到高性能和可伸縮。
• 查詢本身效率也可能會(huì)有所提升。例如下面的例子中，使用 IN() 代替連接查詢，可以讓 MySQL 按照 ID 順序進(jìn)行查詢，這可能比隨機(jī)的連接要更高效。

 

SELECT * FROM tag 
JOIN tag_post ON tag_post.tag_id=tag.id 
JOIN post ON tag_post.post_id=post.id 
WHERE tag.tag='mysql'; 
 
SELECT * FROM tag WHERE tag='mysql'; 
SELECT * FROM tag_post WHERE tag_id=1234; 
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904); 

事務(wù)

事務(wù)是指滿足 ACID 特性的一組操作，可以通過 Commit 提交一個(gè)事務(wù)，也可以使用 Rollback 進(jìn)行回滾。

ACID

事務(wù)最基本的莫過于 ACID 四個(gè)特性了，這四個(gè)特性分別是：

• Atomicity：原子性
• Consistency：一致性
• Isolation：隔離性
• Durability：持久性

原子性

事務(wù)被視為不可分割的最小單元，事務(wù)的所有操作要么全部成功，要么全部失敗回滾。

一致性

數(shù)據(jù)庫在事務(wù)執(zhí)行前后都保持一致性狀態(tài)，在一致性狀態(tài)下，所有事務(wù)對(duì)一個(gè)數(shù)據(jù)的讀取結(jié)果都是相同的。

隔離性

一個(gè)事務(wù)所做的修改在最終提交以前，對(duì)其他事務(wù)是不可見的。

持久性

一旦事務(wù)提交，則其所做的修改將會(huì)永遠(yuǎn)保存到數(shù)據(jù)庫中。即使系統(tǒng)發(fā)生崩潰，事務(wù)執(zhí)行的結(jié)果也不能丟。

ACID 之間的關(guān)系

事務(wù)的 ACID 特性概念很簡(jiǎn)單，但不好理解，主要是因?yàn)檫@幾個(gè)特性不是一種平級(jí)關(guān)系：

• 只有滿足一致性，事務(wù)的結(jié)果才是正確的。
• 在無并發(fā)的情況下，事務(wù)串行執(zhí)行，隔離性一定能夠滿足。此時(shí)只要能滿足原子性，就一定能滿足一致性。在并發(fā)的情況下，多個(gè)事務(wù)并行執(zhí)行，事務(wù)不僅要滿足原子性，還需要滿足隔離性，才能滿足一致性。
• 事務(wù)滿足持久化是為了能應(yīng)對(duì)數(shù)據(jù)庫崩潰的情況。

 

隔離級(jí)別

未提交讀(READ UNCOMMITTED)

事務(wù)中的修改，即使沒有提交，對(duì)其他事務(wù)也是可見的。

提交讀(READ COMMITTED)

一個(gè)事務(wù)只能讀取已經(jīng)提交的事務(wù)所做的修改。換句話說，一個(gè)事務(wù)所做的修改在提交之前對(duì)其他事務(wù)是不可見的。

可重復(fù)讀(REPEATABLE READ)

保證在同一個(gè)事務(wù)中多次讀取同樣數(shù)據(jù)的結(jié)果是一樣的。

可串行化(SERIALIZABLE)

強(qiáng)制事務(wù)串行執(zhí)行。

需要加鎖實(shí)現(xiàn)，而其它隔離級(jí)別通常不需要。

隔離級(jí)別 臟讀 不可重復(fù)讀 幻影讀 未提交讀 √ √ √ 提交讀 × √ √ 可重復(fù)讀 × × √ 可串行化 × × ×

鎖

鎖是數(shù)據(jù)庫系統(tǒng)區(qū)別于文件系統(tǒng)的一個(gè)關(guān)鍵特性。鎖機(jī)制用于管理對(duì)共享資源的并發(fā)訪問。

鎖類型

共享鎖(S Lock)

允許事務(wù)讀一行數(shù)據(jù)

排他鎖(X Lock)

允許事務(wù)刪除或者更新一行數(shù)據(jù)

意向共享鎖(IS Lock)

事務(wù)想要獲得一張表中某幾行的共享鎖

意向排他鎖

事務(wù)想要獲得一張表中某幾行的排他鎖

MVCC

多版本并發(fā)控制(Multi-Version Concurrency Control, MVCC)是 MySQL 的 InnoDB 存儲(chǔ)引擎實(shí)現(xiàn)隔離級(jí)別的一種具體方式，用于實(shí)現(xiàn)提交讀和可重復(fù)讀這兩種隔離級(jí)別。而未提交讀隔離級(jí)別總是讀取最新的數(shù)據(jù)行，無需使用 MVCC?？纱谢綦x級(jí)別需要對(duì)所有讀取的行都加鎖，單純使用 MVCC 無法實(shí)現(xiàn)。

基礎(chǔ)概念

版本號(hào)

• 系統(tǒng)版本號(hào)：是一個(gè)遞增的數(shù)字，每開始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)就會(huì)自動(dòng)遞增。
• 事務(wù)版本號(hào)：事務(wù)開始時(shí)的系統(tǒng)版本號(hào)。

隱藏的列

MVCC 在每行記錄后面都保存著兩個(gè)隱藏的列，用來存儲(chǔ)兩個(gè)版本號(hào)：

• 創(chuàng)建版本號(hào)：指示創(chuàng)建一個(gè)數(shù)據(jù)行的快照時(shí)的系統(tǒng)版本號(hào);
• 刪除版本號(hào)：如果該快照的刪除版本號(hào)大于當(dāng)前事務(wù)版本號(hào)表示該快照有效，否則表示該快照已經(jīng)被刪除了。

Undo 日志

MVCC 使用到的快照存儲(chǔ)在 Undo 日志中，該日志通過回滾指針把一個(gè)數(shù)據(jù)行(Record)的所有快照連接起來。

 

實(shí)現(xiàn)過程

以下實(shí)現(xiàn)過程針對(duì)可重復(fù)讀隔離級(jí)別。

當(dāng)開始一個(gè)事務(wù)時(shí)，該事務(wù)的版本號(hào)肯定大于當(dāng)前所有數(shù)據(jù)行快照的創(chuàng)建版本號(hào)，理解這一點(diǎn)很關(guān)鍵。數(shù)據(jù)行快照的創(chuàng)建版本號(hào)是創(chuàng)建數(shù)據(jù)行快照時(shí)的系統(tǒng)版本號(hào)，系統(tǒng)版本號(hào)隨著創(chuàng)建事務(wù)而遞增，因此新創(chuàng)建一個(gè)事務(wù)時(shí)，這個(gè)事務(wù)的系統(tǒng)版本號(hào)比之前的系統(tǒng)版本號(hào)都大，也就是比所有數(shù)據(jù)行快照的創(chuàng)建版本號(hào)都大。

SELECT

多個(gè)事務(wù)必須讀取到同一個(gè)數(shù)據(jù)行的快照，并且這個(gè)快照是距離現(xiàn)在最近的一個(gè)有效快照。但是也有例外，如果有一個(gè)事務(wù)正在修改該數(shù)據(jù)行，那么它可以讀取事務(wù)本身所做的修改，而不用和其它事務(wù)的讀取結(jié)果一致。

把沒有對(duì)一個(gè)數(shù)據(jù)行做修改的事務(wù)稱為 T，T 所要讀取的數(shù)據(jù)行快照的創(chuàng)建版本號(hào)必須小于等于 T 的版本號(hào)，因?yàn)槿绻笥?T 的版本號(hào)，那么表示該數(shù)據(jù)行快照是其它事務(wù)的最新修改，因此不能去讀取它。除此之外，T 所要讀取的數(shù)據(jù)行快照的刪除版本號(hào)必須是未定義或者大于 T 的版本號(hào)，因?yàn)槿绻∮诘扔?T 的版本號(hào)，那么表示該數(shù)據(jù)行快照是已經(jīng)被刪除的，不應(yīng)該去讀取它。

INSERT

將當(dāng)前系統(tǒng)版本號(hào)作為數(shù)據(jù)行快照的創(chuàng)建版本號(hào)。

DELETE

將當(dāng)前系統(tǒng)版本號(hào)作為數(shù)據(jù)行快照的刪除版本號(hào)。

UPDATE

將當(dāng)前系統(tǒng)版本號(hào)作為更新前的數(shù)據(jù)行快照的刪除版本號(hào)，并將當(dāng)前系統(tǒng)版本號(hào)作為更新后的數(shù)據(jù)行快照的創(chuàng)建版本號(hào)。可以理解為先執(zhí)行 DELETE 后執(zhí)行 INSERT。

快照讀與當(dāng)前讀

在可重復(fù)讀級(jí)別中，通過MVCC機(jī)制，雖然讓數(shù)據(jù)變得可重復(fù)讀，但我們讀到的數(shù)據(jù)可能是歷史數(shù)據(jù)，是不及時(shí)的數(shù)據(jù)，不是數(shù)據(jù)庫當(dāng)前的數(shù)據(jù)!這在一些對(duì)于數(shù)據(jù)的時(shí)效特別敏感的業(yè)務(wù)中，就很可能出問題。

對(duì)于這種讀取歷史數(shù)據(jù)的方式，我們叫它快照讀 (snapshot read)，而讀取數(shù)據(jù)庫當(dāng)前版本數(shù)據(jù)的方式，叫當(dāng)前讀 (current read)。很顯然，在MVCC中：

快照讀

MVCC 的 SELECT 操作是快照中的數(shù)據(jù)，不需要進(jìn)行加鎖操作。

select * from table ….; 

當(dāng)前讀

MVCC 其它會(huì)對(duì)數(shù)據(jù)庫進(jìn)行修改的操作(INSERT、UPDATE、DELETE)需要進(jìn)行加鎖操作，從而讀取最新的數(shù)據(jù)?？梢钥吹?MVCC 并不是完全不用加鎖，而只是避免了 SELECT 的加鎖操作。

 

INSERT;  
UPDATE;  
DELETE; 

在進(jìn)行 SELECT 操作時(shí)，可以強(qiáng)制指定進(jìn)行加鎖操作。以下第一個(gè)語句需要加 S 鎖，第二個(gè)需要加 X 鎖。

 

- select * from table where ? lock in share mode;  
- select * from table where ? for update; 

事務(wù)的隔離級(jí)別實(shí)際上都是定義的當(dāng)前讀的級(jí)別，MySQL為了減少鎖處理(包括等待其它鎖)的時(shí)間，提升并發(fā)能力，引入了快照讀的概念，使得select不用加鎖。而update、insert這些“當(dāng)前讀”的隔離性，就需要通過加鎖來實(shí)現(xiàn)了。

鎖算法

Record Lock

鎖定一個(gè)記錄上的索引，而不是記錄本身。

如果表沒有設(shè)置索引，InnoDB 會(huì)自動(dòng)在主鍵上創(chuàng)建隱藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Lock

鎖定索引之間的間隙，但是不包含索引本身。例如當(dāng)一個(gè)事務(wù)執(zhí)行以下語句，其它事務(wù)就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE; 

Next-Key Lock

它是 Record Locks 和 Gap Locks 的結(jié)合，不僅鎖定一個(gè)記錄上的索引，也鎖定索引之間的間隙。例如一個(gè)索引包含以下值：10, 11, 13, and 20，那么就需要鎖定以下區(qū)間：

 

(-∞, 10] 
(10, 11] 
(11, 13] 
(13, 20] 
(20, +∞) 

在 InnoDB 存儲(chǔ)引擎中，SELECT 操作的不可重復(fù)讀問題通過 MVCC 得到了解決，而 UPDATE、DELETE 的不可重復(fù)讀問題通過 Record Lock 解決，INSERT 的不可重復(fù)讀問題是通過 Next-Key Lock(Record Lock + Gap Lock)解決的。

鎖問題

臟讀

臟讀指的是不同事務(wù)下，當(dāng)前事務(wù)可以讀取到另外事務(wù)未提交的數(shù)據(jù)。

例如：

• T1 修改一個(gè)數(shù)據(jù)，T2 隨后讀取這個(gè)數(shù)據(jù)。如果 T1 撤銷了這次修改，那么 T2 讀取的數(shù)據(jù)是臟數(shù)據(jù)。

 

不可重復(fù)讀

不可重復(fù)讀指的是同一事務(wù)內(nèi)多次讀取同一數(shù)據(jù)集合，讀取到的數(shù)據(jù)是不一樣的情況。

例如：

• T2 讀取一個(gè)數(shù)據(jù)，T1 對(duì)該數(shù)據(jù)做了修改。如果 T2 再次讀取這個(gè)數(shù)據(jù)，此時(shí)讀取的結(jié)果和第一次讀取的結(jié)果不同。

 

在 InnoDB 存儲(chǔ)引擎中，SELECT 操作的不可重復(fù)讀問題通過 MVCC 得到了解決，而 UPDATE、DELETE 的不可重復(fù)讀問題是通過 Record Lock 解決的，INSERT 的不可重復(fù)讀問題是通過 Next-Key Lock(Record Lock + Gap Lock)解決的。

Phantom Proble(幻影讀)

• The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

Phantom Proble 是指在同一事務(wù)下，連續(xù)執(zhí)行兩次同樣的 sql 語句可能返回不同的結(jié)果，第二次的 sql 語句可能會(huì)返回之前不存在的行。

幻影讀是一種特殊的不可重復(fù)讀問題。

總結(jié)

這都是些基礎(chǔ)知識(shí)，我沒想到再次回顧大半我都已忘卻了，也慶幸有這樣的假期能夠重新拾起來。

說實(shí)話做自媒體后我充電的時(shí)間少了很多，也少了很多時(shí)間研究技術(shù)棧深度，國(guó)慶假期我也思考反思了很久，后面準(zhǔn)備繼續(xù)壓縮自己業(yè)余時(shí)間，比如看手機(jī)看B站的時(shí)間壓縮一下，還是得按時(shí)充電，目前作息還算規(guī)律早睡早起都做到了，我們一起加油喲。