內容很硬！強烈建議小伙伴們花 10 分鐘左右閱讀一遍！

MySQL 基礎

關系型數據庫介紹

顧名思義，關系型數據庫就是一種建立在關系模型的基礎上的數據庫。關系模型表明了數據庫中所存儲的數據之間的聯系（一對一、一對多、多對多）。

關系型數據庫中，我們的數據都被存放在了各種表中（比如用戶表），表中的每一行就存放著一條數據（比如一個用戶的信息）。

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

有哪些常見的關系型數據庫呢？

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

MySQL 介紹

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

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

存儲引擎

存儲引擎相關的命令

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

mysql>?show?engines;


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

查看 MySQL 當前默認的存儲引擎

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

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

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


MyISAM 和 InnoDB 的區(qū)別

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默認存儲引擎，可謂是風光一時。

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

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

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

1.是否支持行級鎖

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

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

2.是否支持事務

MyISAM 不提供事務支持。

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

3.是否支持外鍵

MyISAM 不支持，而 InnoDB 支持。

拓展一下：

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

4.是否支持數據庫異常崩潰后的安全恢復

MyISAM 不支持，而 InnoDB 支持。

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

拓展一下：

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

5.是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

講真，這個對比有點廢話，畢竟 MyISAM 連行級鎖都不支持。

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

關于 MyISAM 和 InnoDB 的選擇問題

大多數時候我們使用的都是 InnoDB 存儲引擎，在某些讀密集的情況下，使用 MyISAM 也是合適的。不過，前提是你的項目不介意 MyISAM 不支持事務、崩潰恢復等缺點（可是~我們一般都會介意?。。?。

《MySQL 高性能》上面有一句話這樣寫到:

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

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

因此，對于咱們日常開發(fā)的業(yè)務系統(tǒng)來說，你幾乎找不到什么理由再使用 MyISAM 作為自己的 MySQL 數據庫的存儲引擎。

鎖機制與 InnoDB 鎖算法

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

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

表級鎖和行級鎖對比：

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

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

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

查詢緩存

執(zhí)行查詢語句的時候，會先查詢緩存。不過，MySQL 8.0 版本后移除，因為這個功能不太實用

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;
如上，開啟查詢緩存后在同樣的查詢條件以及數據情況下，會直接在緩存中返回結果。這里的查詢條件包括查詢本身、當前要查詢的數據庫、客戶端協議版本號等一些可能影響結果的信息。因此任何兩個查詢在任何字符上的不同都會導致緩存不命中。此外，如果查詢中包含任何用戶自定義函數、存儲函數、用戶變量、臨時表、MySQL 庫中的系統(tǒng)表，其查詢結果也不會被緩存。

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

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

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


事務

何為事務？

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

可以簡單舉一個例子不？

事務最經典也經常被拿出來說例子就是轉賬了。假如小明要給小紅轉賬 1000 元，這個轉賬會涉及到兩個關鍵操作就是：

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

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

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

何為數據庫事務？

數據庫事務在我們日常開發(fā)中接觸的最多了。如果你的項目屬于單體架構的話，你接觸到的往往就是數據庫事務了。

平時，我們在談論事務的時候，如果沒有特指分布式事務，往往指的就是數據庫事務。

那數據庫事務有什么作用呢？

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

#?開啟一個事務
START?TRANSACTION;
#?多條?SQL?語句
SQL1,SQL2...
##?提交事務
COMMIT;


另外，關系型數據庫（例如：MySQL、SQL Server、Oracle 等）事務都有 ACID 特性：

何為 ACID 特性呢？

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

數據事務的實現原理呢？

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

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

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

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

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

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

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

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

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

事務隔離級別有哪些?

SQL 標準定義了四個隔離級別：

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

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

MySQL 的默認隔離級別是什么?

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

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

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

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

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

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

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

參考

• 《高性能 MySQL》

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