MySql 索引、鎖、事務(wù)知識點(diǎn)小結(jié)

本文總結(jié)了MySql 索引、鎖、事務(wù)知識點(diǎn)。分享給大家供大家參考，具體如下：

1. 索引

索引，類似書籍的目錄，可以根據(jù)目錄的某個頁碼立即找到對應(yīng)的記錄。

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

1. 天生排序。
2. 快速查找。

索引的缺點(diǎn)：

1. 占用空間。
2. 降低更新表的速度。

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

索引從實(shí)現(xiàn)上說，分成 2 種：聚集索引和輔助索引（也叫二級索引或者非聚集索引）

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

詳細(xì)說說 6 種索引：

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

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

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

簡單點(diǎn)說：

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

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

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

B 樹的特點(diǎn)是：他會將數(shù)據(jù)也保存在非頁子節(jié)點(diǎn)。

看圖可知：

而這個特點(diǎn)會導(dǎo)致非頁子節(jié)點(diǎn)不能存儲大量的索引。

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

我們看到，B+ Tree 將所有的 data 數(shù)據(jù)都保存到了葉子節(jié)點(diǎn)中，非也子節(jié)點(diǎn)只保存索引和指針。

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

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

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

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

1. MySql 的主鍵不能太大，如果使用 UUID 這種，將會浪費(fèi) B+ 樹的非葉子節(jié)點(diǎn)。
2. MySql 的主鍵最好是自增的，如果使用 UUID 這種，每次插入都會調(diào)整 B+樹，從而導(dǎo)致頁分裂，嚴(yán)重影響性能。

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

2. 鎖機(jī)制

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

1. 類型維度

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

類型細(xì)分：

• 意向共享鎖

• 意向排他（互斥）鎖

• 悲觀鎖（使用鎖，即 for update）
• 樂觀鎖（使用版本號字段，類似 CAS 機(jī)制，即用戶自己控制。缺點(diǎn)：并發(fā)很高的時候，多了很多無用的重試）

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

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

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

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

4. 默認(rèn)的讀操作，上鎖嗎？

• 默認(rèn)是 MVCC 機(jī)制（“一致性非鎖定讀”）保證 RR 級別的隔離正確性，是不上鎖的。

可以選擇手動上鎖：select xxxx for update (排他鎖); select xxxx lock in share mode(共享鎖)，稱之為“一致性鎖定讀”。

使用鎖之后，就能在 RR 級別下，避免幻讀。當(dāng)然，默認(rèn)的 MVCC 讀，也能避免幻讀。

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

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

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

最后，行鎖的實(shí)現(xiàn)原理就是鎖住聚集索引，如果你查詢的時候，沒有正確地?fù)糁兴饕?，MySql 優(yōu)化器將會拋棄行鎖，使用表鎖。

3. 事務(wù)

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

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

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

然后再說隔離性。

隔離級別：

1. 未提交讀（RU）
2. 已提交讀（RC）
3. 可重復(fù)讀（RR）
4. 串行化（serializable）

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

這里有個注意點(diǎn)，關(guān)于幻讀，在數(shù)據(jù)庫規(guī)范里，RR 級別會導(dǎo)致幻讀，但是，由于 Mysql 的優(yōu)化，MySql 的 RR 級別不會導(dǎo)致幻讀：在使用默認(rèn)的 select 時，MySql 使用 MVCC 機(jī)制保證不會幻讀；你也可以使用鎖，在使用鎖時，例如 for update（X 鎖），lock in share mode（S 鎖），MySql 會使用 Next-Key Lock 來保證不會發(fā)生幻讀。前者稱為快照讀，后者稱為當(dāng)前讀。

原理剖析：

• RU 發(fā)生臟讀的原因：RU 原理是對每個更新語句的行記錄進(jìn)行加鎖，而不是對整個事務(wù)進(jìn)行加鎖，所以會發(fā)生臟讀。而 RC 和 RR 會對整個事務(wù)加鎖。
• RC 不能重復(fù)讀的原因：RC 每次執(zhí)行 SQL 語句都會生成一個新的 Read View，每次讀到的都是不同的。而 RR 的事務(wù)從始至終都是使用同一個 Read View。
• RR 不會發(fā)生幻讀的原因： 上面說過了。

那 RR 和 Serializble 有什么區(qū)別呢？答：丟失更新。本文關(guān)于鎖的部分已經(jīng)提到。

MVCC 介紹：全稱多版本并發(fā)控制。

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

本質(zhì)上，MVCC 就是用 Undo Log 鏈表實(shí)現(xiàn)。

MVCC 的實(shí)現(xiàn)方式：事務(wù)以排它鎖的方式修改原始數(shù)據(jù)，把修改前的數(shù)據(jù)存放于 Undo Log，通過回滾指針與數(shù)據(jù)關(guān)聯(lián)，如果修改成功，什么都不做，如果修改失敗，則恢復(fù) Undo Log 中的數(shù)據(jù)。

多說一句，通常我們認(rèn)為 MVCC 是類似樂觀鎖的方式，即使用版本號，而實(shí)際上，innoDB 不是這么實(shí)現(xiàn)的。當(dāng)然，這不影響我們使用 MySql。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》

希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。