可能是全網最好的MySQL重要知識點

MySQL 是一種關係型資料庫，在Java企業級開發中非常常用，因為 MySQL 是開源免費的，並且方便擴充套件。阿里巴巴資料庫系統也大量用到了 MySQL，因此它的穩定性是有保障的。MySQL是開放原始碼的，因此任何人都可以在 GPL(General Public License) 的許可下下載並根據個性化的需要對其進行修改。MySQL的預設埠號是3306。

事務相關

什麼是事務？

事務是邏輯上的一組操作，要麼都執行，要麼都不執行。

事務最經典也經常被拿出來說例子就是轉賬了。假如小明要給小紅轉賬1000元，這個轉賬會涉及到兩個關鍵操作就是：將小明的餘額減少1000元，將小紅的餘額增加1000元。萬一在這兩個操作之間突然出現錯誤比如銀行系統崩潰，導致小明餘額減少而小紅的餘額沒有增加，這樣就不對了。事務就是保證這兩個關鍵操作要麼都成功，要麼都要失敗。

事物的四大特性(ACID)介紹一下？

• 原子性： 事務是最小的執行單位，不允許分割。事務的原子性確保動作要麼全部完成，要麼完全不起作用；

• 一致性： 執行事務前後，資料保持一致，多個事務對同一個資料讀取的結果是相同的；

• 隔離性： 併發訪問資料庫時，一個使用者的事務不被其他事務所干擾，各併發事務之間資料庫是獨立的；

• 永續性： 一個事務被提交之後。它對資料庫中資料的改變是持久的，即使資料庫發生故障也不應該對其有任何影響。

併發事務帶來哪些問題？

在典型的應用程式中，多個事務併發執行，經常會操作相同的資料來完成各自的任務（多個使用者對統一資料進行操作）。併發雖然是必須的，但可能會導致以下的問題：

• 髒讀（Dirty read）: 當一個事務正在訪問資料並且對資料進行了修改，而這種修改還沒有提交到資料庫中，這時另外一個事務也訪問了這個資料，然後使用了這個資料。因為這個資料是還沒有提交的資料，那麼另外一個事務讀到的這個資料是“髒資料”，依據“髒資料”所做的操作可能是不正確的。

• 丟失修改（Lost to modify）: 指在一個事務讀取一個資料時，另外一個事務也訪問了該資料，那麼在第一個事務中修改了這個資料後，第二個事務也修改了這個資料。這樣第一個事務內的修改結果就被丟失，因此稱為丟失修改。例如：事務1讀取某表中的資料A=20，事務2也讀取A=20，事務1修改A=A-1，事務2也修改A=A-1，最終結果A=19，事務1的修改被丟失。

• 不可重複讀（Unrepeatableread）: 指在一個事務內多次讀同一資料。在這個事務還沒有結束時，另一個事務也訪問該資料。那麼，在第一個事務中的兩次讀資料之間，由於第二個事務的修改導致第一個事務兩次讀取的資料可能不太一樣。這就發生了在一個事務內兩次讀到的資料是不一樣的情況，因此稱為不可重複讀。

• 幻讀（Phantom read）: 幻讀與不可重複讀類似。它發生在一個事務（T1）讀取了幾行資料，接著另一個併發事務（T2）插入了一些資料時。在隨後的查詢中，第一個事務（T1）就會發現多了一些原本不存在的記錄，就好像發生了幻覺一樣，所以稱為幻讀。

不可重複度和幻讀區別：

不可重複讀的重點是修改，幻讀的重點在於新增或者刪除。

例1（同樣的條件, 你讀取過的資料, 再次讀取出來發現值不一樣了 ）：事務1中的A先生讀取自己的工資為 1000的操作還沒完成，事務2中的B先生就修改了A的工資為2000，導 致A再讀自己的工資時工資變為 2000；這就是不可重複讀。

例2（同樣的條件, 第1次和第2次讀出來的記錄數不一樣 ）：假某工資單表中工資大於3000的有4人，事務1讀取了所有工資大於3000的人，共查到4條記錄，這時事務2 又插入了一條工資大於3000的記錄，事務1再次讀取時查到的記錄就變為了5條，這樣就導致了幻讀。

事務隔離級別有哪些?MySQL的預設隔離級別是？

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

• READ-UNCOMMITTED(讀取未提交)： 最低的隔離級別，允許讀取尚未提交的資料變更，可能會導致髒讀、幻讀或不可重複讀。

• READ-COMMITTED(讀取已提交)： 允許讀取併發事務已經提交的資料，可以阻止髒讀，但是幻讀或不可重複讀仍有可能發生。

• REPEATABLE-READ(可重複讀)： 對同一欄位的多次讀取結果都是一致的，除非資料是被本身事務自己所修改，可以阻止髒讀和不可重複讀，但幻讀仍有可能發生。

• SERIALIZABLE(可序列化)： 最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止髒讀、不可重複讀以及幻讀。

MySQL InnoDB 儲存引擎的預設支援的隔離級別是 REPEATABLE-READ（可重讀）。我們可以透過SELECT @@tx_isolation;命令來檢視

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

這裡需要注意的是：與 SQL 標準不同的地方在於InnoDB 儲存引擎在 REPEATABLE-READ（可重讀）事務隔離級別下使用的是Next-Key Lock 鎖演算法，因此可以避免幻讀的產生，這與其他資料庫系統(如 SQL Server)是不同的。所以說InnoDB 儲存引擎的預設支援的隔離級別是 REPEATABLE-READ（可重讀） 已經可以完全保證事務的隔離性要求，即達到了 SQL標準的SERIALIZABLE(可序列化)隔離級別。

因為隔離級別越低，事務請求的鎖越少，所以大部分資料庫系統的隔離級別都是READ-COMMITTED(讀取提交內容):，但是你要知道的是InnoDB 儲存引擎預設使用 REPEATABLE-READ（可重讀）並不會有任何效能損失。

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

索引相關

為什麼索引能提高查詢速度

以下內容整理自：《資料庫兩大神器【索引和鎖】》作者 ：Java3y

先從 MySQL 的基本儲存結構說起

MySQL的基本儲存結構是頁 (記錄都存在頁裡邊) ：

• 各個資料頁可以組成一個雙向連結串列

• 每個資料頁中的記錄又可以組成一個單向連結串列

- 每個資料頁都會為儲存在它裡邊兒的記錄生成一個頁目錄，在透過主鍵查詢某條記錄的時候可以在頁目錄中使用二分法快速定位到對應的槽，然後再遍歷該槽對應分組中的記錄即可快速找到指定的記錄
- 以其他列(非主鍵)作為搜尋條件：只能從最小記錄開始依次遍歷單連結串列中的每條記錄。

所以說，如果我們寫select * from user where indexname = 'xxx'這樣沒有進行任何最佳化的sql語句，預設會這樣做：

1. 定位到記錄所在的頁：需要遍歷雙向連結串列，找到所在的頁

2. 從所在的頁內中查詢相應的記錄：由於不是根據主鍵查詢，只能遍歷所在頁的單連結串列了

很明顯，在資料量很大的情況下這樣查詢會很慢！這樣的時間複雜度為O（n）。

索引做了些什麼可以讓我們查詢加快速度呢？其實就是將無序的資料變成有序(相對)：

要找到id為8的記錄簡要步驟：

很明顯的是：沒有用索引我們是需要遍歷雙向連結串列來定位對應的頁，現在透過 “目錄” 就可以很快地定位到對應的頁上了！（二分查詢，時間複雜度近似為O(logn)）

其實底層結構就是B+樹，B+樹作為樹的一種實現，能夠讓我們很快地查詢出對應的記錄。

以下內容整理自：《Java工程師修煉之道》

什麼是最左字首原則？

MySQL中的索引可以以一定順序引用多列，這種索引叫作聯合索引。如User表的name和city加聯合索引就是(name,city)，而最左字首原則指的是，如果查詢的時候查詢條件精確匹配索引的左邊連續一列或幾列，則此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 無法命中索引

這裡需要注意的是，查詢的時候如果兩個條件都用上了，但是順序不同，如 city= xx and name ＝xx，那麼現在的查詢引擎會自動最佳化為匹配聯合索引的順序，這樣是能夠命中索引的。

由於最左字首原則，在建立聯合索引時，索引欄位的順序需要考慮欄位值去重之後的個數，較多的放前面。ORDER BY子句也遵循此規則。

注意避免冗餘索引

冗餘索引指的是索引的功能相同，能夠命中就肯定能命中 ，那麼 就是冗餘索引如（name,city ）和（name ）這兩個索引就是冗餘索引，能夠命中後者的查詢肯定是能夠命中前者的 在大多數情況下，都應該儘量擴充套件已有的索引而不是建立新索引。

MySQLS.7 版本後，可以透過查詢 sys 庫的 schema_redundant_indexes 表來檢視冗餘索引

Mysql如何為表欄位新增索引？

1.新增PRIMARY KEY（主鍵索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.新增UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.新增INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.新增FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.新增多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

儲存引擎

一些常用命令

檢視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區別

MyISAM是MySQL的預設資料庫引擎（5.5版之前）。雖然效能極佳，而且提供了大量的特性，包括全文索引、壓縮、空間函式等，但MyISAM不支援事務和行級鎖，而且最大的缺陷就是崩潰後無法安全恢復。不過，5.5版本之後，MySQL引入了InnoDB（事務性資料庫引擎），MySQL 5.5版本後預設的儲存引擎為InnoDB。

大多數時候我們使用的都是 InnoDB 儲存引擎，但是在某些情況下使用 MyISAM 也是合適的比如讀密集的情況下。（如果你不介意 MyISAM 崩潰回覆問題的話）。

兩者的對比：

1. 是否支援行級鎖 : MyISAM 只有表級鎖(table-level locking)，而InnoDB 支援行級鎖(row-level locking)和表級鎖,預設為行級鎖。

2. 是否支援事務和崩潰後的安全恢復：MyISAM 強調的是效能，每次查詢具有原子性,其執行比InnoDB型別更快，但是不提供事務支援。但是InnoDB 提供事務支援事務，外部鍵等高階資料庫功能。具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

3. 是否支援外來鍵： MyISAM不支援，而InnoDB支援。

4. 是否支援MVCC ：僅 InnoDB 支援。應對高併發事務, MVCC比單純的加鎖更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 兩個隔離級別下工作;MVCC可以使用 樂觀(optimistic)鎖 和 悲觀(pessimistic)鎖來實現;各資料庫中MVCC實現並不統一。

5. ......

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

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

一般情況下我們選擇 InnoDB 都是沒有問題的，但是某事情況下你並不在乎可擴充套件能力和併發能力，也不需要事務支援，也不在乎崩潰後的安全恢復問題的話，選擇MyISAM也是一個不錯的選擇。但是一般情況下，我們都是需要考慮到這些問題的。

樂觀鎖與悲觀鎖的區別

悲觀鎖

總是假設最壞的情況，每次去拿資料的時候都認為別人會修改，所以每次在拿資料的時候都會上鎖，這樣別人想拿這個資料就會阻塞直到它拿到鎖（共享資源每次只給一個執行緒使用，其它執行緒阻塞，用完後再把資源轉讓給其它執行緒）。傳統的關係型資料庫裡邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。Java中synchronized和ReentrantLock等獨佔鎖就是悲觀鎖思想的實現。

樂觀鎖

總是假設最好的情況，每次去拿資料的時候都認為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個資料，可以使用版本號機制和CAS演算法實現。樂觀鎖適用於多讀的應用型別，這樣可以提高吞吐量，像資料庫提供的類似於write_condition機制，其實都是提供的樂觀鎖。在Java中java.util.concurrent.atomic包下面的原子變數類就是使用了樂觀鎖的一種實現方式CAS實現的。

兩種鎖的使用場景

從上面對兩種鎖的介紹，我們知道兩種鎖各有優缺點，不可認為一種好於另一種，像樂觀鎖適用於寫比較少的情況下（多讀場景），即衝突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。但如果是多寫的情況，一般會經常產生衝突，這就會導致上層應用會不斷的進行retry，這樣反倒是降低了效能，所以一般多寫的場景下用悲觀鎖就比較合適。

樂觀鎖常見的兩種實現方式

樂觀鎖一般會使用版本號機制或CAS演算法實現。

1. 版本號機制

一般是在資料表中加上一個資料版本號version欄位，表示資料被修改的次數，當資料被修改時，version值會加一。當執行緒A要更新資料值時，在讀取資料的同時也會讀取version值，在提交更新時，若剛才讀取到的version值為當前資料庫中的version值相等時才更新，否則重試更新操作，直到更新成功。

舉一個簡單的例子： 假設資料庫中帳戶資訊表中有一個 version 欄位，當前值為 1 ；而當前帳戶餘額欄位（ balance ）為 $100 。

1. 操作員 A 此時將其讀出（ version=1 ），並從其帳戶餘額中扣除 $50（ $100-$50 ）。

2. 在操作員 A 操作的過程中，操作員B 也讀入此使用者資訊（ version=1 ），並從其帳戶餘額中扣除 $20 （ $100-$20 ）。

3. 操作員 A 完成了修改工作，將資料版本號加一（ version=2 ），連同帳戶扣除後餘額（ balance=$50 ），提交至資料庫更新，此時由於提交資料版本大於資料庫記錄當前版本，資料被更新，資料庫記錄 version 更新為 2 。

4. 操作員 B 完成了操作，也將版本號加一（ version=2 ）試圖向資料庫提交資料（ balance=$80 ），但此時比對資料庫記錄版本時發現，操作員 B 提交的資料版本號為 2 ，資料庫記錄當前版本也為 2 ，不滿足 “ 提交版本必須大於記錄當前版本才能執行更新 “ 的樂觀鎖策略，因此，操作員 B 的提交被駁回。

這樣，就避免了操作員 B 用基於 version=1 的舊資料修改的結果覆蓋操作員A 的操作結果的可能。

2. CAS演算法

即compare and swap（比較與交換），是一種有名的無鎖演算法。無鎖程式設計，即不使用鎖的情況下實現多執行緒之間的變數同步，也就是在沒有執行緒被阻塞的情況下實現變數的同步，所以也叫非阻塞同步（Non-blocking Synchronization）。CAS演算法涉及到三個運算元

• 需要讀寫的記憶體值 V

• 進行比較的值 A

• 擬寫入的新值 B

當且僅當 V 的值等於 A時，CAS透過原子方式用新值B來更新V的值，否則不會執行任何操作（比較和替換是一個原子操作）。一般情況下是一個自旋操作，即不斷的重試。

樂觀鎖的缺點

ABA 問題是樂觀鎖一個常見的問題

1 ABA 問題

如果一個變數V初次讀取的時候是A值，並且在準備賦值的時候檢查到它仍然是A值，那我們就能說明它的值沒有被其他執行緒修改過了嗎？很明顯是不能的，因為在這段時間它的值可能被改為其他值，然後又改回A，那CAS操作就會誤認為它從來沒有被修改過。這個問題被稱為CAS操作的 "ABA"問題。

JDK 1.5 以後的 AtomicStampedReference 類就提供了此種能力，其中的 compareAndSet 方法就是首先檢查當前引用是否等於預期引用，並且當前標誌是否等於預期標誌，如果全部相等，則以原子方式將該引用和該標誌的值設定為給定的更新值。

2 迴圈時間長開銷大

自旋CAS（也就是不成功就一直迴圈執行直到成功）如果長時間不成功，會給CPU帶來非常大的執行開銷。 如果JVM能支援處理器提供的pause指令那麼效率會有一定的提升，pause指令有兩個作用，第一它可以延遲流水線執行指令（de-pipeline）,使CPU不會消耗過多的執行資源，延遲的時間取決於具體實現的版本，在一些處理器上延遲時間是零。第二它可以避免在退出迴圈的時候因記憶體順序衝突（memory order violation）而引起CPU流水線被清空（CPU pipeline flush），從而提高CPU的執行效率。

3 只能保證一個共享變數的原子操作

CAS 只對單個共享變數有效，當操作涉及跨多個共享變數時 CAS 無效。但是從 JDK 1.5開始，提供了AtomicReference類來保證引用物件之間的原子性，你可以把多個變數放在一個物件裡來進行 CAS 操作.所以我們可以使用鎖或者利用AtomicReference類把多個共享變數合併成一個共享變數來操作。

鎖機制與InnoDB鎖演算法

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

• MyISAM 採用表級鎖(table-level locking)。

• InnoDB 支援行級鎖(row-level locking)和表級鎖,預設為行級鎖

表級鎖和行級鎖對比：

• 表級鎖： Mysql中鎖定 粒度最大 的一種鎖，對當前操作的整張表加鎖，實現簡單，資源消耗也比較少，加鎖快，不會出現死鎖。其鎖定粒度最大，觸發鎖衝突的機率最高，併發度最低，MyISAM和 InnoDB引擎都支援表級鎖。

• 行級鎖： Mysql中鎖定 粒度最小 的一種鎖，只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的衝突。其加鎖粒度最小，併發度高，但加鎖的開銷也最大，加鎖慢，會出現死鎖。

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

• Record lock：單個行記錄上的鎖

• Gap lock：間隙鎖，鎖定一個範圍，不包括記錄本身

• Next-key lock：record+gap 鎖定一個範圍，包含記錄本身

相關知識點：

• innodb對於行的查詢使用next-key lock

• Next-locking keying為了解決Phantom Problem幻讀問題

• 當查詢的索引含有唯一屬性時，將next-key lock降級為record key

• Gap鎖設計的目的是為了阻止多個事務將記錄插入到同一範圍內，而這會導致幻讀問題的產生

• 有兩種方式顯式關閉gap鎖：（除了外來鍵約束和唯一性檢查外，其餘情況僅使用record lock） A. 將事務隔離級別設定為RC B. 將引數innodb_locks_unsafe_for_binlog設定為1

大表最佳化

當MySQL單表記錄數過大時，資料庫的CRUD效能會明顯下降，一些常見的最佳化措施如下：

1. 限定資料的範圍

務必禁止不帶任何限制資料範圍條件的查詢語句。比如：我們當使用者在查詢訂單歷史的時候，我們可以控制在一個月的範圍內；

2. 讀/寫分離

經典的資料庫拆分方案，主庫負責寫，從庫負責讀；

3. 垂直分割槽

根據資料庫裡面資料表的相關性進行拆分。 例如，使用者表中既有使用者的登入資訊又有使用者的基本資訊，可以將使用者表拆分成兩個單獨的表，甚至放到單獨的庫做分庫。

簡單來說垂直拆分是指資料表列的拆分，把一張列比較多的表拆分為多張表。 如下圖所示，這樣來說大家應該就更容易理解了。

• 垂直拆分的優點： 可以使得列資料變小，在查詢時減少讀取的Block數，減少I/O次數。此外，垂直分割槽可以簡化表的結構，易於維護。

• 垂直拆分的缺點： 主鍵會出現冗餘，需要管理冗餘列，並會引起Join操作，可以透過在應用層進行Join來解決。此外，垂直分割槽會讓事務變得更加複雜；

4. 水平分割槽

保持資料表結構不變，透過某種策略儲存資料分片。這樣每一片資料分散到不同的表或者庫中，達到了分散式的目的。水平拆分可以支撐非常大的資料量。

水平拆分是指資料錶行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的資料拆成多張表來存放。舉個例子：我們可以將使用者資訊表拆分成多個使用者資訊表，這樣就可以避免單一表資料量過大對效能造成影響。

水平拆分可以支援非常大的資料量。需要注意的一點是：分表僅僅是解決了單一表資料過大的問題，但由於表的資料還是在同一臺機器上，其實對於提升MySQL併發能力沒有什麼意義，所以 水平拆分最好分庫 。

水平拆分能夠 支援非常大的資料量儲存，應用端改造也少，但 分片事務難以解決 ，跨節點Join效能較差，邏輯複雜。《Java工程師修煉之道》的作者推薦 儘量不要對資料進行分片，因為拆分會帶來邏輯、部署、運維的各種複雜度 ，一般的資料表在最佳化得當的情況下支撐千萬以下的資料量是沒有太大問題的。如果實在要分片，儘量選擇客戶端分片架構，這樣可以減少一次和中介軟體的網路I/O。

下面補充一下資料庫分片的兩種常見方案：

• 客戶端代理： 分片邏輯在應用端，封裝在jar包中，透過修改或者封裝JDBC層來實現。 噹噹網的 Sharding-JDBC 、阿里的TDDL是兩種比較常用的實現。

• 中介軟體代理： 在應用和資料中間加了一個代理層。分片邏輯統一維護在中介軟體服務中。 我們現在談的 Mycat 、360的Atlas、網易的DDB等等都是這種架構的實現。

• 作者：Snailclimb
  

• 來源：