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

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

1. 索引

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

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

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

索引的缺點(diǎn)：

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

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

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

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

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

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

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

另外，InnoDB 通過(guò)主鍵聚簇?cái)?shù)據(jù)，如果沒(méi)有定義主鍵且沒(méi)有定義聚集索引， MySql 會(huì)選擇一個(gè)唯一的非空索引代替，如果沒(méi)有這樣的索引，會(huì)隱式定義個(gè) 6 字節(jié)的主鍵作為聚簇索引，用戶(hù)不能查看或訪問(wèn)。

簡(jiǎn)單點(diǎn)說(shuō)：

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

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

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

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

看圖可知：

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

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

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

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

而一顆 3 層的 B+樹(shù)能夠存儲(chǔ)多少索引呢？如下圖：

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

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

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

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

2. 鎖機(jī)制

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

1. 類(lèi)型維度

• 共享鎖（讀鎖 / S 鎖）
• 排它鎖（寫(xiě)鎖 / X 鎖）

類(lèi)型細(xì)分：

• 意向共享鎖

• 意向排他（互斥）鎖

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

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

• 表鎖
• 頁(yè)鎖（Mysql BerkeleyDB 引擎）
• 行鎖（InnoDB）

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

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

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

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

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

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

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

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

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

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

3. 事務(wù)

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

四個(gè)特性，最重要的就是一致性。而一致性由原子性，隔離性，持久性來(lái)保證。

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

然后再說(shuō)隔離性。

隔離級(jí)別：

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

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

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

原理剖析：

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

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

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

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

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

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

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

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

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