MySql 三大知識點，索引、鎖、事務，原理分析

1.索引

索引，類似書籍的目錄，可以根據目錄的某個頁碼立即找到對應的內容。

索引的優點：1. 天生排序，2. 快速查找。

索引的缺點：1. 占用空間，2. 降低更新表的速度。

注意點：小表使用全表掃描更快，中大表才使用索引。超級大表索引基本無效。

索引從實現上說，分成 2 種：聚集索引和輔助索引（也叫二級索引或者非聚集索引）

從功能上說，分為 6 種：普通索引，唯一索引，主鍵索引，復合索引，外鍵索引，全文索引。

詳細說說 6 種索引：

1、普通索引：最基本的索引，沒有任何約束。
2、唯一索引：與普通索引類似，但具有唯一性約束。
3、主鍵索引：特殊的唯一索引，不允許有空值。
4、復合索引：將多個列組合在一起創建索引，可以覆蓋多個列。
5、外鍵索引：只有InnoDB類型的表才可以使用外鍵索引，保證數據的一致性、完整性和實現級聯操作。
6、全文索引：MySQL 自帶的全文索引只能用于 InnoDB、MyISAM ，并且只能對英文進行全文檢索，一般使用全文索引引擎（ES，Solr）。

注意：主鍵就是唯一索引，但是唯一索引不一定是主鍵，唯一索引可以為空，但是空值只能有一個，主鍵不能為空。

另外，InnoDB 通過主鍵聚簇數據，如果沒有定義主鍵且沒有定義聚集索引， MySql 會選擇一個唯一的非空索引代替，如果沒有這樣的索引，會隱式定義個 6 字節的主鍵作為聚簇索引，用戶不能查看或訪問。

簡單點說：

1、設置主鍵時，會自動生成一個唯一索引，如果之前沒有聚集索引，那么主鍵就是聚集索引。
2、沒有設置主鍵時，會選擇一個不為空的唯一索引作為聚集索引，如果還沒有，那就生成一個隱式的 6 字節的索引。

MySql 將數據按照頁來存儲，默認一頁為 16kb，當你在查詢時，不會只加載某一條數據，而是將這個數據所在的頁都加載到 pageCache 中，這個其實和 OS 的就近訪問原理類似。

MySql 的索引使用 B+ 樹結構。在說 B+ 樹之前，先說說 B 樹，B 樹是一個多路平衡查找樹，相較于普通的二叉樹，不會發生極度不平衡的狀況，同時也是多路的。

B 樹的特點是：他會將數據也保存在非頁子節點。

看圖可知：

而這個特點會導致非頁子節點不能存儲大量的索引。

而 B+ Tree 就是針對這個對 B tree 做了優化。如下圖所示：

我們看到，B+ Tree 將所有的 data 數據都保存到了葉子節點中，非也子節點只保存索引和指針。

我們假設一個非頁子節點是 16kb，每個索引，即主鍵是 bigint，即 8b，指針為 8b。那么每頁能存儲大約 1000 個索引（16kb/ 8b + 8b）.

而一顆 3 層的 B+樹能夠存儲多少索引呢？

如下圖：

大約能夠存儲 10 億個索引。通常 B+ 樹的高度在 2-4 層，由于 MySql 在運行時，根節點是常駐內存的，因此每次查找只需要大約 2 -3 次 IO。可以說，B+ 樹的設計，就是根據機械磁盤的特性來進行設計的。

知道了索引的設計，我們能夠知道另外一些信息：

1、MySql 的主鍵不能太大，如果使用 UUID 這種，將會浪費 B+ 樹的非葉子節點。
2、MySql 的主鍵最好是自增的，如果使用 UUID 這種，每次插入都會調整 B+樹，從而導致頁分裂，嚴重影響性能。

那么，如果項目中使用了分庫分表，我們通常都會需要一個主鍵進行 sharding，那怎么辦呢？在實現上，我們可以保留自增主鍵，而邏輯主鍵用來作為唯一索引即可。

2.鎖機制

關于 Mysql 的鎖，各種概念就會噴涌而出，事實上，鎖有好幾種維度，我們來解釋一下。

1.類型維度

共享鎖（讀鎖 / S 鎖） 排它鎖（寫鎖 / X 鎖）

類型細分：

• 意向共享鎖

• 意向排他（互斥）鎖

悲觀鎖（使用鎖，即 for update）

樂觀鎖（使用版本號字段，類似 CAS 機制，即用戶自己控制。缺點：并發很高的時候，多了很多無用的重試）

2.鎖的粒度（粒度維度）

表鎖 頁鎖（Mysql BerkeleyDB 引擎） 行鎖（InnoDB）

3.鎖的算法（算法維度）

Record Lock（單行記錄） Gap Lock（間隙鎖，鎖定一個范圍，但不包含鎖定記錄） Next-Key Lock（Record Lock + Gap Lock，鎖定一個范圍，并且鎖定記錄本身， MySql 防止幻讀，就是使用此鎖實現）

4.默認的讀操作，上鎖嗎？

默認是 MVCC 機制（“一致性非鎖定讀”）保證 RR 級別的隔離正確性，是不上鎖的。

可以選擇手動上鎖：select xxxx for update (排他鎖);?

select xxxx lock in share mode(共享鎖)，稱之為“一致性鎖定讀”。

使用鎖之后，就能在 RR 級別下，避免幻讀。當然，默認的 MVCC 讀，也能避免幻讀。

既然 RR 能夠防止幻讀，那么，SERIALIZABLE 有啥用呢？

防止丟失更新。例如下圖：

這個時候，我們必須使用 SERIALIZABLE 級別進行串行讀取。

最后，行鎖的實現原理就是鎖住聚集索引，如果你查詢的時候，沒有正確地擊中索引，MySql 優化器將會拋棄行鎖，使用表鎖。

3.事務

事務是數據庫永恒不變的話題， ACID：原子性，一致性，隔離性，持久性。

四個特性，最重要的就是一致性。而一致性由原子性，隔離性，持久性來保證。

原子性由 Undo log 保證。Undo Log 會保存每次變更之前的記錄，從而在發生錯誤時進行回滾。隔離性由 MVCC 和 Lock 保證。這個后面說。持久性由 Redo Log 保證。每次真正修改數據之前，都會將記錄寫到 Redo Log 中，只有 Redo Log 寫入成功，才會真正的寫入到 B+ 樹中，如果提交之前斷電，就可以通過 Redo Log 恢復記錄。

然后再說隔離性。

隔離級別：

1、未提交讀（RU）

2、已提交讀（RC）

3、可重復讀（RR）

4、串行化（serializable）

每個級別都會解決不同的問題，通常是3 個問題：臟讀，不可重復讀，幻讀。一張經典的圖：

這里有個注意點，關于幻讀，在數據庫規范里，RR 級別會導致幻讀，但是，由于 Mysql 的優化，MySql 的 RR 級別不會導致幻讀：在使用默認的 select 時，MySql 使用 MVCC 機制保證不會幻讀；

你也可以使用鎖，在使用鎖時，例如 for update（X 鎖），lock in share mode（S 鎖），MySql 會使用 Next-Key Lock 來保證不會發生幻讀。前者稱為快照讀，后者稱為當前讀。

原理剖析：

1、RU 發生臟讀的原因：RU 原理是對每個更新語句的行記錄進行加鎖，而不是對整個事務進行加鎖，所以會發生臟讀。而 RC 和 RR 會對整個事務加鎖。 2、RC 不能重復讀的原因：RC 每次執行 SQL 語句都會生成一個新的 Read View，每次讀到的都是不同的。而 RR 的事務從始至終都是使用同一個 Read View。 3、RR 不會發生幻讀的原因： 上面說過了。

那 RR 和 Serializble 有什么區別呢？答：丟失更新。本文關于鎖的部分已經提到。

MVCC 介紹：全稱多版本并發控制。

innoDB 每個聚集索引都有 4 個隱藏字段，分別是主鍵（RowID），最近更改的事務 ID（MVCC 核心），Undo Log 的指針（隔離核心），索引刪除標記（當刪除時，不會立即刪除，而是打標記，然后異步刪除）；

本質上，MVCC 就是用 Undo Log 鏈表實現。

MVCC 的實現方式：事務以排它鎖的方式修改原始數據，把修改前的數據存放于 Undo Log，通過回滾指針與數據關聯，如果修改成功，什么都不做，如果修改失敗，則恢復 Undo Log 中的數據。

多說一句，通常我們認為 MVCC 是類似樂觀鎖的方式，即使用版本號，而實際上，innoDB 不是這么實現的。當然，這不影響我們使用 MySql。