內(nèi)容很硬！強(qiáng)烈建議小伙伴們花 10 分鐘左右閱讀一遍！

MySQL 基礎(chǔ)

關(guān)系型數(shù)據(jù)庫介紹

顧名思義，關(guān)系型數(shù)據(jù)庫就是一種建立在關(guān)系模型的基礎(chǔ)上的數(shù)據(jù)庫。關(guān)系模型表明了數(shù)據(jù)庫中所存儲的數(shù)據(jù)之間的聯(lián)系（一對一、一對多、多對多）。

關(guān)系型數(shù)據(jù)庫中，我們的數(shù)據(jù)都被存放在了各種表中（比如用戶表），表中的每一行就存放著一條數(shù)據(jù)（比如一個(gè)用戶的信息）。

大部分關(guān)系型數(shù)據(jù)庫都使用 SQL 來操作數(shù)據(jù)庫中的數(shù)據(jù)。并且，大部分關(guān)系型數(shù)據(jù)庫都支持事務(wù)的四大特性(ACID)。

有哪些常見的關(guān)系型數(shù)據(jù)庫呢？

MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天記錄的存儲就是用的 SQLite） ......。

MySQL 介紹

MySQL 是一種關(guān)系型數(shù)據(jù)庫，主要用于持久化存儲我們的系統(tǒng)中的一些數(shù)據(jù)比如用戶信息。

由于 MySQL 是開源免費(fèi)并且比較成熟的數(shù)據(jù)庫，因此，MySQL 被大量使用在各種系統(tǒng)中。任何人都可以在 GPL(General Public License) 的許可下下載并根據(jù)個(gè)性化的需要對其進(jìn)行修改。MySQL 的默認(rèn)端口號是3306。

存儲引擎

存儲引擎相關(guān)的命令

查看 MySQL 提供的所有存儲引擎

mysql>?show?engines;


從上圖我們可以查看出 MySQL 當(dāng)前默認(rèn)的存儲引擎是 InnoDB，并且在 5.7 版本所有的存儲引擎中只有 InnoDB 是事務(wù)性存儲引擎，也就是說只有 InnoDB 支持事務(wù)。

查看 MySQL 當(dāng)前默認(rèn)的存儲引擎

我們也可以通過下面的命令查看默認(rèn)的存儲引擎。

mysql>?show?variables?like?'%storage_engine%';
查看表的存儲引擎

show?table?status?like?"table_name"?;


MyISAM 和 InnoDB 的區(qū)別

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默認(rèn)存儲引擎，可謂是風(fēng)光一時(shí)。

雖然，MyISAM 的性能還行，各種特性也還不錯(cuò)（比如全文索引、壓縮、空間函數(shù)等）。但是，MyISAM 不支持事務(wù)和行級鎖，而且最大的缺陷就是崩潰后無法安全恢復(fù)。

5.5 版本之后，MySQL 引入了 InnoDB（事務(wù)性數(shù)據(jù)庫引擎），MySQL 5.5 版本后默認(rèn)的存儲引擎為 InnoDB。小伙子，一定要記好這個(gè) InnoDB ，你每次使用 MySQL 數(shù)據(jù)庫都是用的這個(gè)存儲引擎吧？

言歸正傳！咱們下面還是來簡單對比一下兩者：

1.是否支持行級鎖

MyISAM 只有表級鎖(table-level locking)，而 InnoDB 支持行級鎖(row-level locking)和表級鎖,默認(rèn)為行級鎖。

也就說，MyISAM 一鎖就是鎖住了整張表，這在并發(fā)寫的情況下是多么滴憨憨啊！這也是為什么 InnoDB 在并發(fā)寫的時(shí)候，性能更牛皮了！

2.是否支持事務(wù)

MyISAM 不提供事務(wù)支持。

InnoDB 提供事務(wù)支持，具有提交(commit)和回滾(rollback)事務(wù)的能力。

3.是否支持外鍵

MyISAM 不支持，而 InnoDB 支持。

拓展一下：

一般我們也是不建議在數(shù)據(jù)庫層面使用外鍵的，應(yīng)用層面可以解決。不過，這樣會對數(shù)據(jù)的一致性造成威脅。具體要不要使用外鍵還是要根據(jù)你的項(xiàng)目來決定。

4.是否支持?jǐn)?shù)據(jù)庫異常崩潰后的安全恢復(fù)

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的數(shù)據(jù)庫在異常崩潰后，數(shù)據(jù)庫重新啟動(dòng)的時(shí)候會保證數(shù)據(jù)庫恢復(fù)到崩潰前的狀態(tài)。這個(gè)恢復(fù)的過程依賴于 redo log 。

拓展一下：

• MySQL InnoDB 引擎使用 redo log(重做日志) 保證事務(wù)的持久性，使用 undo log(回滾日志) 來保證事務(wù)的原子性。
• MySQL InnoDB 引擎通過 鎖機(jī)制、MVCC 等手段來保證事務(wù)的隔離性（ 默認(rèn)支持的隔離級別是 REPEATABLE-READ ）。
• 保證了事務(wù)的持久性、原子性、隔離性之后，一致性才能得到保障。

5.是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

講真，這個(gè)對比有點(diǎn)廢話，畢竟 MyISAM 連行級鎖都不支持。

MVCC 可以看作是行級鎖的一個(gè)升級，可以有效減少加鎖操作，提供性能。

關(guān)于 MyISAM 和 InnoDB 的選擇問題

大多數(shù)時(shí)候我們使用的都是 InnoDB 存儲引擎，在某些讀密集的情況下，使用 MyISAM 也是合適的。不過，前提是你的項(xiàng)目不介意 MyISAM 不支持事務(wù)、崩潰恢復(fù)等缺點(diǎn)（可是~我們一般都會介意?。。?/p>《MySQL 高性能》上面有一句話這樣寫到:

不要輕易相信“MyISAM 比 InnoDB 快”之類的經(jīng)驗(yàn)之談，這個(gè)結(jié)論往往不是絕對的。在很多我們已知場景中，InnoDB 的速度都可以讓 MyISAM 望塵莫及，尤其是用到了聚簇索引，或者需要訪問的數(shù)據(jù)都可以放入內(nèi)存的應(yīng)用。

一般情況下我們選擇 InnoDB 都是沒有問題的，但是某些情況下你并不在乎可擴(kuò)展能力和并發(fā)能力，也不需要事務(wù)支持，也不在乎崩潰后的安全恢復(fù)問題的話，選擇 MyISAM 也是一個(gè)不錯(cuò)的選擇。但是一般情況下，我們都是需要考慮到這些問題的。

因此，對于咱們?nèi)粘ｉ_發(fā)的業(yè)務(wù)系統(tǒng)來說，你幾乎找不到什么理由再使用 MyISAM 作為自己的 MySQL 數(shù)據(jù)庫的存儲引擎。

鎖機(jī)制與 InnoDB 鎖算法

MyISAM 和 InnoDB 存儲引擎使用的鎖：

• MyISAM 采用表級鎖(table-level locking)。
• InnoDB 支持行級鎖(row-level locking)和表級鎖,默認(rèn)為行級鎖

表級鎖和行級鎖對比：

• 表級鎖： MySQL 中鎖定 粒度最大 的一種鎖，對當(dāng)前操作的整張表加鎖，實(shí)現(xiàn)簡單，資源消耗也比較少，加鎖快，不會出現(xiàn)死鎖。其鎖定粒度最大，觸發(fā)鎖沖突的概率最高，并發(fā)度最低，MyISAM 和 InnoDB 引擎都支持表級鎖。
• 行級鎖： MySQL 中鎖定 粒度最小 的一種鎖，只針對當(dāng)前操作的行進(jìn)行加鎖。行級鎖能大大減少數(shù)據(jù)庫操作的沖突。其加鎖粒度最小，并發(fā)度高，但加鎖的開銷也最大，加鎖慢，會出現(xiàn)死鎖。

InnoDB 存儲引擎的鎖的算法有三種：

• Record lock：記錄鎖，單個(gè)行記錄上的鎖
• Gap lock：間隙鎖，鎖定一個(gè)范圍，不包括記錄本身
• Next-key lock：record gap 臨鍵鎖，鎖定一個(gè)范圍，包含記錄本身

查詢緩存

執(zhí)行查詢語句的時(shí)候，會先查詢緩存。不過，MySQL 8.0 版本后移除，因?yàn)檫@個(gè)功能不太實(shí)用

my.cnf 加入以下配置，重啟 MySQL 開啟查詢緩存

query_cache_type=1
query_cache_size=600000
MySQL 執(zhí)行以下命令也可以開啟查詢緩存

set global query_cache_type=1;
set global query_cache_size=600000;
如上，開啟查詢緩存后在同樣的查詢條件以及數(shù)據(jù)情況下，會直接在緩存中返回結(jié)果。這里的查詢條件包括查詢本身、當(dāng)前要查詢的數(shù)據(jù)庫、客戶端協(xié)議版本號等一些可能影響結(jié)果的信息。因此任何兩個(gè)查詢在任何字符上的不同都會導(dǎo)致緩存不命中。此外，如果查詢中包含任何用戶自定義函數(shù)、存儲函數(shù)、用戶變量、臨時(shí)表、MySQL 庫中的系統(tǒng)表，其查詢結(jié)果也不會被緩存。

緩存建立之后，MySQL 的查詢緩存系統(tǒng)會跟蹤查詢中涉及的每張表，如果這些表（數(shù)據(jù)或結(jié)構(gòu)）發(fā)生變化，那么和這張表相關(guān)的所有緩存數(shù)據(jù)都將失效。

緩存雖然能夠提升數(shù)據(jù)庫的查詢性能，但是緩存同時(shí)也帶來了額外的開銷，每次查詢后都要做一次緩存操作，失效后還要銷毀。 因此，開啟查詢緩存要謹(jǐn)慎，尤其對于寫密集的應(yīng)用來說更是如此。如果開啟，要注意合理控制緩存空間大小，一般來說其大小設(shè)置為幾十 MB 比較合適。此外，還可以通過 sql_cache 和 sql_no_cache 來控制某個(gè)查詢語句是否需要緩存：

select?sql_no_cache?count(*)?from?usr;


事務(wù)

何為事務(wù)？

一言蔽之，事務(wù)是邏輯上的一組操作，要么都執(zhí)行，要么都不執(zhí)行。

可以簡單舉一個(gè)例子不？

事務(wù)最經(jīng)典也經(jīng)常被拿出來說例子就是轉(zhuǎn)賬了。假如小明要給小紅轉(zhuǎn)賬 1000 元，這個(gè)轉(zhuǎn)賬會涉及到兩個(gè)關(guān)鍵操作就是：

1. 將小明的余額減少 1000 元
2. 將小紅的余額增加 1000 元。

事務(wù)會把這兩個(gè)操作就可以看成邏輯上的一個(gè)整體，這個(gè)整體包含的操作要么都成功，要么都要失敗。

這樣就不會出現(xiàn)小明余額減少而小紅的余額卻并沒有增加的情況。

何為數(shù)據(jù)庫事務(wù)？

數(shù)據(jù)庫事務(wù)在我們?nèi)粘ｉ_發(fā)中接觸的最多了。如果你的項(xiàng)目屬于單體架構(gòu)的話，你接觸到的往往就是數(shù)據(jù)庫事務(wù)了。

平時(shí)，我們在談?wù)撌聞?wù)的時(shí)候，如果沒有特指分布式事務(wù)，往往指的就是數(shù)據(jù)庫事務(wù)。

那數(shù)據(jù)庫事務(wù)有什么作用呢？

簡單來說：數(shù)據(jù)庫事務(wù)可以保證多個(gè)對數(shù)據(jù)庫的操作（也就是 SQL 語句）構(gòu)成一個(gè)邏輯上的整體。構(gòu)成這個(gè)邏輯上的整體的這些數(shù)據(jù)庫操作遵循：要么全部執(zhí)行成功,要么全部不執(zhí)行 。

#?開啟一個(gè)事務(wù)
START?TRANSACTION;
#?多條?SQL?語句
SQL1,SQL2...
##?提交事務(wù)
COMMIT;


另外，關(guān)系型數(shù)據(jù)庫（例如：MySQL、SQL Server、Oracle 等）事務(wù)都有 ACID 特性：

何為 ACID 特性呢？

1. 原子性（Atomicity） ：事務(wù)是最小的執(zhí)行單位，不允許分割。事務(wù)的原子性確保動(dòng)作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）：執(zhí)行事務(wù)前后，數(shù)據(jù)保持一致，例如轉(zhuǎn)賬業(yè)務(wù)中，無論事務(wù)是否成功，轉(zhuǎn)賬者和收款人的總額應(yīng)該是不變的；
3. 隔離性（Isolation）：并發(fā)訪問數(shù)據(jù)庫時(shí)，一個(gè)用戶的事務(wù)不被其他事務(wù)所干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫是獨(dú)立的；
4. 持久性（Durabilily）：一個(gè)事務(wù)被提交之后。它對數(shù)據(jù)庫中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫發(fā)生故障也不應(yīng)該對其有任何影響。

數(shù)據(jù)事務(wù)的實(shí)現(xiàn)原理呢？

我們這里以 MySQL 的 InnoDB 引擎為例來簡單說一下。

MySQL InnoDB 引擎使用 redo log(重做日志) 保證事務(wù)的持久性，使用 undo log(回滾日志) 來保證事務(wù)的原子性。

MySQL InnoDB 引擎通過 鎖機(jī)制、MVCC 等手段來保證事務(wù)的隔離性（ 默認(rèn)支持的隔離級別是 REPEATABLE-READ ）。

保證了事務(wù)的持久性、原子性、隔離性之后，一致性才能得到保障。

并發(fā)事務(wù)帶來哪些問題?

在典型的應(yīng)用程序中，多個(gè)事務(wù)并發(fā)運(yùn)行，經(jīng)常會操作相同的數(shù)據(jù)來完成各自的任務(wù)（多個(gè)用戶對同一數(shù)據(jù)進(jìn)行操作）。并發(fā)雖然是必須的，但可能會導(dǎo)致以下的問題。

• 臟讀（Dirty read）: 當(dāng)一個(gè)事務(wù)正在訪問數(shù)據(jù)并且對數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫中，這時(shí)另外一個(gè)事務(wù)也訪問了這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。因?yàn)檫@個(gè)數(shù)據(jù)是還沒有提交的數(shù)據(jù)，那么另外一個(gè)事務(wù)讀到的這個(gè)數(shù)據(jù)是“臟數(shù)據(jù)”，依據(jù)“臟數(shù)據(jù)”所做的操作可能是不正確的。
• 丟失修改（Lost to modify）: 指在一個(gè)事務(wù)讀取一個(gè)數(shù)據(jù)時(shí)，另外一個(gè)事務(wù)也訪問了該數(shù)據(jù)，那么在第一個(gè)事務(wù)中修改了這個(gè)數(shù)據(jù)后，第二個(gè)事務(wù)也修改了這個(gè)數(shù)據(jù)。這樣第一個(gè)事務(wù)內(nèi)的修改結(jié)果就被丟失，因此稱為丟失修改。例如：事務(wù) 1 讀取某表中的數(shù)據(jù) A=20，事務(wù) 2 也讀取 A=20，事務(wù) 1 修改 A=A-1，事務(wù) 2 也修改 A=A-1，最終結(jié)果 A=19，事務(wù) 1 的修改被丟失。
• 不可重復(fù)讀（Unrepeatable read）: 指在一個(gè)事務(wù)內(nèi)多次讀同一數(shù)據(jù)。在這個(gè)事務(wù)還沒有結(jié)束時(shí)，另一個(gè)事務(wù)也訪問該數(shù)據(jù)。那么，在第一個(gè)事務(wù)中的兩次讀數(shù)據(jù)之間，由于第二個(gè)事務(wù)的修改導(dǎo)致第一個(gè)事務(wù)兩次讀取的數(shù)據(jù)可能不太一樣。這就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的情況，因此稱為不可重復(fù)讀。
• 幻讀（Phantom read）: 幻讀與不可重復(fù)讀類似。它發(fā)生在一個(gè)事務(wù)（T1）讀取了幾行數(shù)據(jù)，接著另一個(gè)并發(fā)事務(wù)（T2）插入了一些數(shù)據(jù)時(shí)。在隨后的查詢中，第一個(gè)事務(wù)（T1）就會發(fā)現(xiàn)多了一些原本不存在的記錄，就好像發(fā)生了幻覺一樣，所以稱為幻讀。

不可重復(fù)讀和幻讀區(qū)別：

不可重復(fù)讀的重點(diǎn)是修改比如多次讀取一條記錄發(fā)現(xiàn)其中某些列的值被修改，幻讀的重點(diǎn)在于新增或者刪除比如多次讀取一條記錄發(fā)現(xiàn)記錄增多或減少了。

事務(wù)隔離級別有哪些?

SQL 標(biāo)準(zhǔn)定義了四個(gè)隔離級別：

• READ-UNCOMMITTED(讀取未提交)： 最低的隔離級別，允許讀取尚未提交的數(shù)據(jù)變更，可能會導(dǎo)致臟讀、幻讀或不可重復(fù)讀。
• READ-COMMITTED(讀取已提交)： 允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)生。
• REPEATABLE-READ(可重復(fù)讀)： 對同一字段的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務(wù)自己所修改，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生。
• SERIALIZABLE(可串行化)： 最高的隔離級別，完全服從 ACID 的隔離級別。所有的事務(wù)依次逐個(gè)執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾，也就是說，該級別可以防止臟讀、不可重復(fù)讀以及幻讀。

隔離級別	臟讀	不可重復(fù)讀	幻讀
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL 的默認(rèn)隔離級別是什么?

MySQL InnoDB 存儲引擎的默認(rèn)支持的隔離級別是 REPEATABLE-READ（可重讀）。我們可以通過SELECT @@tx_isolation;命令來查看，MySQL 8.0 該命令改為SELECT @@transaction_isolation;

mysql>?SELECT?@@tx_isolation;
-----------------
|?@@tx_isolation??|
-----------------
|?REPEATABLE-READ?|
-----------------
這里需要注意的是：與 SQL 標(biāo)準(zhǔn)不同的地方在于 InnoDB 存儲引擎在 REPEATABLE-READ（可重讀） 事務(wù)隔離級別下使用的是 Next-Key Lock 鎖算法，因此可以避免幻讀的產(chǎn)生，這與其他數(shù)據(jù)庫系統(tǒng)(如 SQL Server)是不同的。所以說 InnoDB 存儲引擎的默認(rèn)支持的隔離級別是 REPEATABLE-READ（可重讀） 已經(jīng)可以完全保證事務(wù)的隔離性要求，即達(dá)到了 SQL 標(biāo)準(zhǔn)的 SERIALIZABLE(可串行化) 隔離級別。

問題更正：MySQL InnoDB 的 REPEATABLE-READ（可重讀）并不保證避免幻讀，需要應(yīng)用使用加鎖讀來保證。而這個(gè)加鎖度使用到的機(jī)制就是 Next-Key Locks。

因?yàn)楦綦x級別越低，事務(wù)請求的鎖越少，所以大部分?jǐn)?shù)據(jù)庫系統(tǒng)的隔離級別都是 READ-COMMITTED(讀取提交內(nèi)容) ，但是你要知道的是 InnoDB 存儲引擎默認(rèn)使用 REPEATABLE-READ（可重讀） 并不會有任何性能損失。

InnoDB 存儲引擎在 分布式事務(wù) 的情況下一般會用到 SERIALIZABLE(可串行化) 隔離級別。

拓展一下(以下內(nèi)容摘自《MySQL 技術(shù)內(nèi)幕：InnoDB 存儲引擎(第 2 版)》7.7 章)：

InnoDB 存儲引擎提供了對 XA 事務(wù)的支持，并通過 XA 事務(wù)來支持分布式事務(wù)的實(shí)現(xiàn)。分布式事務(wù)指的是允許多個(gè)獨(dú)立的事務(wù)資源（transactional resources）參與到一個(gè)全局的事務(wù)中。事務(wù)資源通常是關(guān)系型數(shù)據(jù)庫系統(tǒng)，但也可以是其他類型的資源。全局事務(wù)要求在其中的所有參與的事務(wù)要么都提交，要么都回滾，這對于事務(wù)原有的 ACID 要求又有了提高。另外，在使用分布式事務(wù)時(shí)，InnoDB 存儲引擎的事務(wù)隔離級別必須設(shè)置為 SERIALIZABLE。

參考

• 《高性能 MySQL》

• https://www.omnisci.com/technical-glossary/relational-database