mysql視頻教程欄目介紹底層原理。

MYSQL

一條SQL執(zhí)行過程

先看看一條查詢SQL

• （這里提供一下官方對(duì)各存儲(chǔ)引擎的文檔說明 Mysql存儲(chǔ)引擎）

一條 update SQL執(zhí)行

update的執(zhí)行 從客戶端 => ··· => 執(zhí)行引擎 是一樣的流程，都要先查到這條數(shù)據(jù)，然后再去更新。要想理解 UPDATE 流程我們先來看看，Innodb的架構(gòu)模型。

Innodb 架構(gòu)

上一張 MYSQL 官方InnoDB架構(gòu)圖：

內(nèi)部模塊

連接器(JDBC、ODBC等) =>

[MYSQL 內(nèi)部

[Connection Pool] (授權(quán)、線程復(fù)用、連接限制、內(nèi)存檢測等) =>  [SQL interface] (DML、DDL、Views等) [Parser] (Query Translation、Object privilege) [Optimizer] (Access Paths、 統(tǒng)計(jì)分析) [Caches & Buffers] =>  [Pluggable Storage Engines]復(fù)制代碼

]

=> [File]

內(nèi)存結(jié)構(gòu)

這里有個(gè)關(guān)鍵點(diǎn)，當(dāng)我們?nèi)ゲ樵償?shù)據(jù)時(shí)候會(huì)先 拿著我們當(dāng)前查詢的 page 去 buffer pool 中查詢 當(dāng)前page是否在緩沖池中。如果在，則直接獲取。

而如果是update操作時(shí)，則會(huì)直接修改 Buffer中的值。這個(gè)時(shí)候，buffer pool中的數(shù)據(jù)就和我們磁盤中實(shí)際存儲(chǔ)的數(shù)據(jù)不一致了，稱為臟頁。每隔一段時(shí)間，Innodb存儲(chǔ)引擎就會(huì)把臟頁數(shù)據(jù)刷入磁盤。一般來說當(dāng)更新一條數(shù)據(jù)，我們需要將數(shù)據(jù)給讀取到buffer中修改，然后寫回磁盤，完成一次 落盤IO 操作。

為了提高update的操作性能，Mysql在內(nèi)存中做了優(yōu)化，可以看到，在架構(gòu)圖的緩沖池中有一塊區(qū)域叫做：change buffer。顧名思義，給change后的數(shù)據(jù)，做buffer的，當(dāng)更新一個(gè)沒有 unique index 的數(shù)據(jù)時(shí)，直接將修改的數(shù)據(jù)放到 change buffer，然后通過 merge 操作完成更新，從而減少了那一次 落盤的IO 操作。

• 我們上面說的有個(gè)條件：沒有唯一索引的數(shù)據(jù)更新時(shí)，為什么必須要沒有唯一索引的數(shù)據(jù)更新時(shí)才能直接放入change buffer呢？如果是有唯一約束的字段，我們在更新數(shù)據(jù)后，可能更新的數(shù)據(jù)和已經(jīng)存在的數(shù)據(jù)有重復(fù)，所以只能從磁盤中把所有數(shù)據(jù)讀出來比對(duì)才能確定唯一性。
• 所以當(dāng)我們的數(shù)據(jù)是 寫多讀少 的時(shí)候，就可以通過 增加 innodb_change_buffer_max_size 來調(diào)整 change buffer在buffer pool 中所占的比例，默認(rèn)25（即：25%）

問題又來了，merge是如何運(yùn)作的

有四種情況：

1. 有其他訪問，訪問到了當(dāng)前頁的數(shù)據(jù)，就會(huì)合并到磁盤
2. 后臺(tái)線程定時(shí)merge
3. 系統(tǒng)正常shut down之前，merge一次
4. redo log寫滿的時(shí)候，merge到磁盤

一、redo log是什么

談到redo，就要談到innodb的 crash safe，使用 WAL 的方式實(shí)現(xiàn)（write Ahead Logging，在寫之前先記錄日志）

這樣就可以在，當(dāng)數(shù)據(jù)庫崩潰的后，直接從 redo log中恢復(fù)數(shù)據(jù)，保證數(shù)據(jù)的正確性

redo log 默認(rèn)存儲(chǔ)在兩個(gè)文件中 ib_logfile0 ib_logfile1，這兩個(gè)文件都是固定大小的。為什么需要固定大小？

這是因?yàn)閞edo log的 順序讀取 的特性造成的，必須是連續(xù)的存儲(chǔ)空間

二、隨機(jī)讀寫與順序讀寫

看一張圖

一般我們的數(shù)據(jù)都是分散在磁盤上的：

機(jī)械硬盤的讀寫順序是：

1. 定位到磁道
2. 等待旋轉(zhuǎn)到對(duì)應(yīng)扇區(qū)
3. 開始讀寫

固態(tài)讀寫：

1. 直接定位到閃存芯片（這也是為啥固態(tài)比機(jī)械快）
2. 開始讀寫

其實(shí)不管機(jī)械還是固態(tài)，我們?nèi)ゴ鎯?chǔ)時(shí)，都是通過文件系統(tǒng)與磁盤打交道的，而他們打交道的方式就有兩個(gè)。隨機(jī)讀寫和順序讀寫

1. 隨機(jī)讀寫存儲(chǔ)的數(shù)據(jù)是分布在不同的 塊（默認(rèn) 1block=8扇區(qū)=4K）
2. 而順序存儲(chǔ)，顧名思義，數(shù)據(jù)是分布在一串連續(xù)的塊中，這樣讀取速度就大大提升了

三、回到我們架構(gòu)圖

看到buffer pool中的Log Buffer，其就是用來寫 redo log 之前存在的緩沖區(qū)

在這里，redo log具體的執(zhí)行策略有三種：

0. 不用寫Log Buffer，只需要每秒寫redo log 磁盤數(shù)據(jù)一次，性能高，但會(huì)造成數(shù)據(jù) 1s 內(nèi)的一致性問題。適用于強(qiáng)實(shí)時(shí)性，弱一致性，比如評(píng)論區(qū)評(píng)論
1. 寫Log Buffer，同時(shí)寫入磁盤，性能最差，一致性最高。 適用于弱實(shí)時(shí)性，強(qiáng)一致性,比如支付場景
2. 寫Log Buffer，同時(shí)寫到os buffer（其會(huì)每秒調(diào)用 fsync 將數(shù)據(jù)刷入磁盤），性能好，安全性也高。這個(gè)是實(shí)時(shí)性適中 一致性適中的，比如訂單類。

我們通過innodb_flush_log_at_trx_commit就可以設(shè)置執(zhí)行策略。默認(rèn)為 1

內(nèi)存結(jié)構(gòu)小結(jié)

1. Buffer Pool 用于加速讀
2. Change Buffer 用于沒有非唯一索引的加速寫
3. Log Buffer 用于加速redo log寫
4. 自適應(yīng)Hash索引主要用于加快查詢 頁。在查詢時(shí)，Innodb通過監(jiān)視索引搜索的機(jī)制來判斷當(dāng)前查詢是否能走Hash索引。比如LIKE運(yùn)算符和% 通配符就不能走。

硬盤結(jié)構(gòu)

一、System tablespace

存儲(chǔ)在一個(gè)叫ibdata1的文件中，其中包含：

1. InnoDB Data Dictionary，存儲(chǔ)了元數(shù)據(jù)，比如表結(jié)構(gòu)信息、索引等
2. doublewrite Buffer 當(dāng)Buffer Pool寫入數(shù)據(jù)頁時(shí)，不是直接寫入到文件，而是先寫入到這個(gè)區(qū)域。這樣做的好處的是，一但操作系統(tǒng)，文件系統(tǒng)或者mysql掛掉，可以直接從這個(gè)Buffer中獲取數(shù)據(jù)。
3. Change Buffer 當(dāng)Mysql shut down的時(shí)候，修改就會(huì)被存儲(chǔ)在磁盤這里
4. Undo Logs 記錄事務(wù)修改操作

二、File-Per-Table Tablespaces

每一張表都有一張 .ibd 的文件，存儲(chǔ)數(shù)據(jù)和索引。

1. 有了每表文件表空間可以使得 ALTER TABLE與 TRUNCATE TABLE 性能得到很好的提升。比如 ALTER TABLE，相較于對(duì)駐留在共享表空間中的表，在修改表時(shí)，會(huì)進(jìn)行表復(fù)制操作，這可能會(huì)增加表空間占用的磁盤空間量。此類操作可能需要與表中的數(shù)據(jù)以及索引一樣多的額外空間。該空間不會(huì)像每表文件表空間那樣釋放回操作系統(tǒng)。
2. 可以在單獨(dú)的存儲(chǔ)設(shè)備上創(chuàng)建每表文件表空間數(shù)據(jù)文件，以進(jìn)行I / O優(yōu)化，空間管理或備份。這就意味著表數(shù)據(jù)與結(jié)構(gòu)容易在不同數(shù)據(jù)庫中遷移。
3. 當(dāng)發(fā)生數(shù)據(jù)損壞，備份或二進(jìn)制日志不可用或無法重新啟動(dòng)MySQL服務(wù)器實(shí)例時(shí)，存儲(chǔ)在單個(gè)表空間數(shù)據(jù)文件中的表可以節(jié)省時(shí)間并提高成功恢復(fù)的機(jī)會(huì)。

當(dāng)然有優(yōu)點(diǎn)就有缺陷：

1. 存儲(chǔ)空間的利用率低，會(huì)存在碎片，在Drop table的時(shí)候會(huì)影響性能（除非你自己管理了碎片）
2. 因?yàn)槊總€(gè)表分成各自的表文件，操作系統(tǒng)不能同時(shí)進(jìn)行fsync一次性刷入數(shù)據(jù)到文件中
3. mysqld會(huì)持續(xù)保持每個(gè)表文件的 文件句柄， 以提供維持對(duì)文件的持續(xù)訪問

三、General Tablespaces

1. 通用表空間又叫共享表空間，他可以存儲(chǔ)多個(gè)表的數(shù)據(jù)
2. 如果存儲(chǔ)相同數(shù)量的表，消耗的存儲(chǔ)比 每表表空間 小
3. 在MySQL 5.7.24中棄用了將表分區(qū)放置在常規(guī)表空間中的支持，并且在將來的MySQL版本中將不再支持。

四、Temporary Tablespaces

存儲(chǔ)在一個(gè)叫 ibtmp1 的文件中。正常情況下Mysql啟動(dòng)的時(shí)候會(huì)創(chuàng)建臨時(shí)表空間，停止的時(shí)候會(huì)刪除臨時(shí)表空間。并且它能夠自動(dòng)擴(kuò)容。

五、Undo Tablespaces

1. 提供修改操作的 原子性，即當(dāng)修改到一半，出現(xiàn)異常，可以通過Undo 日志回滾。
2. 它存儲(chǔ)了，事務(wù)開始前的原始數(shù)據(jù)與這次的修改操作。
3. Undo log 存在于回滾段（rollback segment）中，回滾段又存在系統(tǒng)表空間“撤銷表空間“臨時(shí)表空間中，如架構(gòu)圖所示。

Redo Log

前面已經(jīng)介紹過

總結(jié)一下，我們執(zhí)行一句update SQL 會(huì)發(fā)生什么

1. 查詢到我們要修改的那條數(shù)據(jù)，我們這里稱做 origin，返給執(zhí)行器
2. 在執(zhí)行器中，修改數(shù)據(jù)，稱為 modification
3. 將modification刷入內(nèi)存，Buffer Pool的 Change Buffer
4. 引擎層：記錄undo log （實(shí)現(xiàn)事務(wù)原子性）
5. 引擎層：記錄redo log （崩潰恢復(fù)使用）
6. 服務(wù)層：記錄bin log（記錄DDL）
7. 返回更新成功結(jié)果
8. 數(shù)據(jù)等待被工作線程刷入磁盤

Bin log

說了 Undo、Redo也順便說一下Bin log.

1. 這一個(gè)log和 innodb引擎沒有多大關(guān)系，我們前面說的那兩種日志，都在是innodb引擎層的。而Bin log是處于服務(wù)層的。所以他能被各個(gè)引擎所通用
2. 他的主要作用是什么呢？首先，Bin log 是以事件的形式，記錄了各個(gè) DDL DML 語句，它是一種邏輯意義上的日志。
3. 能夠?qū)崿F(xiàn)主從復(fù)制， 從服務(wù)器拿到主服務(wù)器的bin log日志，然后執(zhí)行。
4. 做數(shù)據(jù)恢復(fù)，拿到某個(gè)時(shí)間段的日志，重新執(zhí)行一遍。

跟隨一條SQL語句完成全局預(yù)覽后，我們來看看回過頭來讓SQL變得更加豐富，我們來添加一個(gè)索引試試

華麗的分割線

索引篇

要想徹底弄明白InnoDB中的索引是個(gè)什么東西，就必須要了解它的文件存儲(chǔ)級(jí)別

Innodb中將文件存儲(chǔ)分為了四個(gè)級(jí)別

Pages, Extents, Segments, and Tablespaces

它們的關(guān)系是：

• 默認(rèn)的 extent 大小為 1M 即 64個(gè) 16KB的Page。平常我們文件系統(tǒng)所說的頁大小是 4KB，包含 8 個(gè) 512Byte的扇區(qū)。

存儲(chǔ)結(jié)構(gòu) B樹變體 B+樹

所以有時(shí)候，我們被要求主鍵為什么要是有序的原因就是，如果我們在一個(gè)有序的字段上，建立索引，然后插入數(shù)據(jù)。 在存儲(chǔ)的時(shí)候，innodb就會(huì)按著順序一個(gè)個(gè)存儲(chǔ)到 頁 上，存滿一個(gè)頁再去申請新的頁，然后接著存。

但如果我們的字段是無序的，存儲(chǔ)的位置就會(huì)在不同的頁上。當(dāng)我們的數(shù)據(jù)存儲(chǔ)到一個(gè)已經(jīng)被 存滿的頁上時(shí)，就會(huì)造成頁分裂，從而形成碎片。

幾種不同的索引組織形式

1. 聚簇索引，如上面B+樹圖所示，子節(jié)點(diǎn)上存儲(chǔ)行數(shù)據(jù)，并且索引的排列的順序和索引鍵值順序一致的話就是 聚簇索引。主鍵索引就是聚簇索引，除了主鍵索引，其他所以都是輔助索引
2. 輔助索引，如果我們創(chuàng)建了一個(gè)輔助索引，它的葉子節(jié)點(diǎn)上只存儲(chǔ)自己的值和主鍵索引的值。這就意味著，如果我們通過輔助索引查詢所有數(shù)據(jù)，就會(huì)先去查找輔助索引中的主鍵鍵值，然后再去主鍵索引里面，查到相關(guān)數(shù)據(jù)。這個(gè)過程稱為回表
3. rowid 如果沒有主鍵索引怎么辦呢？
   1. 沒有主鍵，但是有一個(gè) Unique key 而且都不是 NULL的，則會(huì)根據(jù)這個(gè) key來創(chuàng)建聚簇索引。
   2. 那上面兩種都沒有呢，別擔(dān)心，innodb自己維護(hù)了一個(gè)叫 rowid 的東西，根據(jù)這個(gè)id來創(chuàng)建 聚簇索引

索引如何起作用

搞清楚什么是索引，結(jié)構(gòu)是什么之后。 我們來看看，什么時(shí)候我們要用到索引，理解了這些能更好的幫助我們創(chuàng)建正確高效的索引

1. 離散度低不建索引，也就是數(shù)據(jù)之間相差不大的就沒必要建立索引。（因?yàn)榻⑺饕诓樵兊臅r(shí)候，innodb大多數(shù)據(jù)都是相同的，我走索引 和全表沒什么差別就會(huì)直接全表查詢）。比如 性別字段。這樣反而浪費(fèi)了大量的存儲(chǔ)空間。

2. 聯(lián)合字段索引，比如 idx(name, class_name)

   1. 當(dāng)執(zhí)行 select * from stu where class_name = xx and name = lzw 查詢時(shí)，也能走 idx 這個(gè)索引的，因?yàn)閮?yōu)化器將SQL優(yōu)化為了 name = lzw and class_name = xx
   2. 當(dāng)需要有 select ··· where name = lzw 的時(shí)候，不需要?jiǎng)?chuàng)建一個(gè)單獨(dú)的 name索引，會(huì)直接走 idx這個(gè)索引
   3. 覆蓋索引。如果我們此次查詢的所有數(shù)據(jù)全都包含在索引里面了，就不需要再 回表去查詢了。比如：select class_name from stu where name =lzw

3. 索引條件下推(index_condition_pushdown)

   1. 有這樣一條SQL，select * ?from stu where name = lzw and class_name like ‘%xx’
   2. 如果沒有索引條件下推，因?yàn)楹竺媸?like ‘%xx’的查詢條件，所以這里首先根據(jù) name 走 idx聯(lián)合索引 查詢到幾條數(shù)據(jù)后，再回表查詢到全量row數(shù)據(jù)，然后在server層進(jìn)行 like 過濾找到數(shù)據(jù)
   3. 如果有，則直接在引擎層對(duì)like也進(jìn)行過濾了，相當(dāng)于把server層這個(gè)過濾操作下推到引擎層了。如圖所示：

建立索引注意事項(xiàng)

1. 在where、order、join的on 使用次數(shù)多的時(shí)候，加上索引
2. 離散度高的字段才能建立索引
3. 聯(lián)合索引把離散度高的放前面（因?yàn)槭紫雀鶕?jù)第一個(gè)字段匹配，能迅速定位數(shù)據(jù)位置。）
4. 頻繁更新的字段不能建索引（造成頁分裂，索引按順序存儲(chǔ)，如果存儲(chǔ)頁滿了，再去插入就會(huì)造成頁分裂）
5. 使用比如replace、sum、count等函數(shù)的時(shí)候不會(huì)使用索引，所以沒必要額外建
6. 出現(xiàn)隱式轉(zhuǎn)化的時(shí)候，比如字符串轉(zhuǎn)int，也用不到索引
7. 特別長的字段，可以截取前面幾位創(chuàng)建索引（可以通過 select count(distinct left(name, 10))/count(*) 來看離散度，決定到底提取前幾位）

• tips: 執(zhí)行一個(gè)SQL，不能確切說他是否能不能用到索引，畢竟這一切都是 優(yōu)化器決定的。比如你使用了 Cost Base Optimizer 基于開銷的優(yōu)化器，那種開銷小就用哪種優(yōu)化。

弄懂了索引，我們就有能力打開 鎖篇 的副本了

又一個(gè)華麗的分割線

鎖篇

四大特性

先回顧一下我們耳熟能詳?shù)膸讉€(gè)基本概念：

1. 原子性（通過Undo log實(shí)現(xiàn)）
2. 一致性
3. 隔離性
4. 持久性（崩潰恢復(fù)，Redo log + double write 實(shí)現(xiàn)）

讀一致性問題應(yīng)該由數(shù)據(jù)庫的事務(wù)隔離級(jí)別來解決 （SQL92 標(biāo)準(zhǔn)）

前提，在一個(gè)事務(wù)中：

1. 臟讀（讀到了別人還沒有commit的數(shù)據(jù)，然后別人又回滾掉了）
2. 不可重復(fù)讀（第一次讀取到了數(shù)據(jù)，然后別人修改commit了，再次去讀取就讀到了別人已經(jīng)commit的數(shù)據(jù)）
3. 幻讀（在范圍查詢的時(shí)候，讀到別人新添加的數(shù)據(jù)）

SQL92 標(biāo)準(zhǔn)規(guī)定： (并發(fā)度從左到右，依次降低)

• tips: Innodb中，Repeatable Read的幻讀，也不可能存在，是因?yàn)樗约航鉀Q了

Innodb中如何解決 可重復(fù)讀(RR) 中產(chǎn)生幻讀的情況

鎖模型

1. LBCC (Lock Based Concurrency Control) 讀之前加個(gè)鎖，但這樣可能會(huì)導(dǎo)致性能問題 ?=> 讀的時(shí)候加鎖導(dǎo)致其他事務(wù)都不能讀寫了，性能低下
2. MVCC(Multi Version Concurrency Control) 讀的時(shí)候記錄當(dāng)時(shí)快照，別人來讀取快照就行 => 性能消耗，存儲(chǔ)消耗

這兩種方案在Innodb中結(jié)合使用。這里簡要說明一下 RR 的 MVCC實(shí)現(xiàn)，圖中 回滾id 初始值不應(yīng)該為0而是NULL，這里為了方便寫成0

• RC的MVCC實(shí)現(xiàn)是對(duì) 同一個(gè)事務(wù)的多個(gè)讀 創(chuàng)建一個(gè)版本 而 RR 是 同一個(gè)事務(wù)任何一條都創(chuàng)建一個(gè)版本

通過MVCC與LBCC的結(jié)合，InnoDB能解決對(duì)于不加鎖條件下的 幻讀的情況。而不必像 Serializable 一樣，必須讓事務(wù)串行進(jìn)行，無任何并發(fā)。

下面我們來深入研究一下InnoDB鎖是如何實(shí)現(xiàn) RR 事務(wù)隔離級(jí)別的

鎖深入 MVCC在Innodb的實(shí)現(xiàn)

一、Innodb 的鎖

1. Shared and Exclusive Locks 共享和排它鎖 =>（S、X）
2. Intention Locks 意向鎖 => 這里指的是兩把鎖，其實(shí)就是表級(jí)別的 共享和排它鎖 => (IS、IX)

上面這四把鎖是最基本鎖的類型

3. Record Locks 記錄鎖
4. Gap Locks 間隙鎖
5. Next-key Locks 臨鎖

這三把鎖，理解成對(duì)于上面四把鎖實(shí)現(xiàn)的三種算法方式，我們這里暫且把它們稱為：高階鎖

6. Insert Intention Locks 插入鎖
7. auto-INC Locks 自增鍵鎖
8. Predicate Locks for Spatial Indexes 專用于給Spatial Indexes用的

上面三把是額外擴(kuò)展的鎖

二、讀寫鎖深入解釋

1. 要使用共享鎖，在語句后面加上lock in share mode 。排它鎖默認(rèn) Insert、Update、delete會(huì)使用。顯示使用在語句后加for update。
2. 意向鎖都是由數(shù)據(jù)庫自己維護(hù)的。（主要作用是給表打一個(gè)標(biāo)記，記錄這個(gè)表是否被鎖住了） => 如果沒有這個(gè)鎖，別的事務(wù)想鎖住這張表的時(shí)候，就要去全表掃描是否有鎖，效率太低。所以才會(huì)有意向鎖的存在。

補(bǔ)充：Mysql中鎖，到底鎖的是什么

鎖的是索引，那么這個(gè)時(shí)候可能有人要問了：那如果我不創(chuàng)建索引呢？

索引的存在，我們上面講過了，這里再回顧一下，有下面幾種情況

1. 你建了一個(gè) Primary key， 就是聚集索引 （存儲(chǔ)的是 完整的數(shù)據(jù)）
2. 沒有主鍵，但是有一個(gè) Unique key 而是都不是 null的，則會(huì)根據(jù)這個(gè) key來創(chuàng)建 聚簇索引
3. 那上面兩種都沒有呢，別擔(dān)心，innodb自己維護(hù)了一個(gè)叫 rowid 的東西，根據(jù)這個(gè)id來創(chuàng)建 聚簇索引

所以一個(gè)表里面，必然會(huì)存在一個(gè)索引，所以鎖當(dāng)然總有索引拿來鎖住了。

當(dāng)要給一張你沒有顯示創(chuàng)建索引的表，進(jìn)行加鎖查詢時(shí)，數(shù)據(jù)庫其實(shí)是不知道到底要查哪些數(shù)據(jù)的，整張表可能都會(huì)用到。所以索性就鎖整張表。

• 如果是給輔助索引加寫鎖，比如select * from where name = ’xxx‘ for update 最后要回表查主鍵上的信息，所以這個(gè)時(shí)候除了鎖輔助索引還要鎖主鍵索引

三、高階鎖深入解釋

首先上三個(gè)概念，有這么一組數(shù)據(jù)：主鍵是 ?1，3，6，9 在存儲(chǔ)時(shí)候有如下：x 1 x 3 x x 6 x x x 9 x···

記錄鎖，鎖的是每個(gè)記錄，也就是 1，3，6，9 間隙鎖，鎖的是記錄間隙，每個(gè) x，(-∞,1), (1,3), (3,6), (6,9), (9,+∞) 臨鎖，鎖的是 (-∞,1], (1,3], (3,6], (6,9], (9,+∞] 左開右閉的區(qū)間

首先這三種鎖都是 排它鎖， 并且 臨鍵鎖 = 記錄鎖 + 間隙鎖

1. 當(dāng) select * from xxx where id = 3 for update 時(shí)，產(chǎn)生記錄鎖
2. 當(dāng) select * from xxx where id = 5 for update 時(shí)，產(chǎn)生間隙鎖 => 鎖住了(3,6)，這里要格外注意一點(diǎn)：間隙鎖之間是不沖突的。
3. 當(dāng) select * from xxx where id = 5 for update 時(shí)，產(chǎn)生臨鍵鎖 => 鎖住了(3,6], mysql默認(rèn)使用臨鍵鎖，如果不滿足 1 ，2 情況 則他的行鎖的都是臨鍵鎖

• 回到開始的問題，在這里 Record Lock 行鎖防止別的事務(wù)修改或刪除，Gap Lock 間隙鎖防止別的事務(wù)新增，Gap Lock 和 Record Lock結(jié)合形成的Next-Key鎖共同解決RR級(jí)別在寫數(shù)據(jù)時(shí)的幻讀問題。

說到了鎖那么必然逃不過要說一下死鎖

發(fā)生死鎖后的檢查

1. show status like ‘innodb_row_lock_%’
   1. Innodb_row_lock_current_waits 當(dāng)前正在有多少等待鎖
   2. Innodb_row_lock_time 一共等待了多少時(shí)間
   3. Innodb_row_lock_time_avg 平均等多少時(shí)間
   4. Innodb_row_lock_time_max 最大等多久
   5. Innodb_row_lock_waits 一共出現(xiàn)過多少次等待
2. select * from information_schema.INNODB_TRX 能查看到當(dāng)前正在運(yùn)行和被鎖住的事務(wù)
3. show full ?processlist = select ?* ?from ? ?information_schema.processlist 能查詢出是 哪個(gè)用戶 在哪臺(tái)機(jī)器host的哪個(gè)端口上 連接哪個(gè)數(shù)據(jù)庫 執(zhí)行什么指令 的 狀態(tài)與時(shí)間

死鎖預(yù)防

1. 保證訪問數(shù)據(jù)的順序
2. 避免where的時(shí)候不用索引（這樣會(huì)鎖表，不僅死鎖更容易產(chǎn)生，而且性能更加低下）
3. 一個(gè)非常大的事務(wù)，拆成多個(gè)小的事務(wù)
4. 盡量使用等值查詢（就算用范圍查詢也要限定一個(gè)區(qū)間，而不要只開不閉，比如 id > 1 就鎖住后面所有）

優(yōu)化篇

分庫分表

動(dòng)態(tài)選擇數(shù)據(jù)源

編碼層 — 實(shí)現(xiàn) AbstracRoutingDataSource => 框架層 — 實(shí)現(xiàn) mybatis Plugin => 驅(qū)動(dòng)層 — Sharding-JDBC(配置多個(gè)數(shù)據(jù)源，根據(jù)自定義實(shí)現(xiàn)的策略對(duì)數(shù)據(jù)進(jìn)行分庫分表存儲(chǔ))核心流程,SQL解析=>執(zhí)行優(yōu)化=>SQL數(shù)據(jù)庫路由=>SQL改變(比如分表，改表名)=>SQL執(zhí)行=>結(jié)果歸并） => 代理層 — Mycat（將所有與數(shù)據(jù)庫的連接獨(dú)立出來。全部由Mycat連接，其他服務(wù)訪問Mycat獲取數(shù)據(jù)） => 服務(wù)層 — 特殊的SQL版本

MYSQL如何做優(yōu)化

說到底我們學(xué)習(xí)這么多知識(shí)都是為了能更好使用MYSQL，那就讓我們來實(shí)操一下，建立一個(gè)完整的優(yōu)化體系

要想獲得更好的查詢性能，可以從這張查詢執(zhí)行過程入手

一、客戶端連接池

添加連接池，避免每次都新建、銷毀連接那我們的連接池是不是越多越好呢？ 有興趣的盆友可以看看這篇文章：About Pool Sizing

我大概總結(jié)一下：

1. 我們并發(fā)的執(zhí)行SQL，并不會(huì)因?yàn)檫B接數(shù)量增多而變快。為什么呢？如果我有10000連接同時(shí)并發(fā)執(zhí)行，難道不比你10個(gè)連接執(zhí)行快得多嗎? 答案是否定的，不僅不快反而越來越慢。
   1. 在計(jì)算機(jī)中，我們都知道只有CPU才能真正去執(zhí)行線程。而操作系統(tǒng)因?yàn)橛脮r(shí)間分片的技術(shù)，讓我們以為一個(gè)CPU內(nèi)核執(zhí)行了多個(gè)線程。
   2. 但其實(shí)上一個(gè)CPU在某個(gè)時(shí)間段只能執(zhí)行一個(gè)線程，所以無論我們怎么增加并發(fā)，CPU還是只能在這個(gè)時(shí)間段里處理這么多數(shù)據(jù)。
   3. 那就算CPU處理不了這么多數(shù)據(jù)，又怎么會(huì)變慢？因?yàn)闀r(shí)間分片，當(dāng)多個(gè)線程看起來在”同時(shí)執(zhí)行”，其實(shí)他們之間的上下文切換十分耗時(shí)
   4. 所以，一旦線程的數(shù)量超過了CPU核心的數(shù)量，再增加線程數(shù)系統(tǒng)就只會(huì)更慢，而不是更快。
2. 當(dāng)然，這只是其中最核心的原因，磁盤同樣也會(huì)對(duì)速度有影響，同時(shí)也對(duì)我們連接數(shù)配置有影響。
   1. 比如我們用的機(jī)械硬盤，我們要通過旋轉(zhuǎn)，尋址到某個(gè)位置，再進(jìn)行I/O操作，這個(gè)時(shí)候，CPU就可以把時(shí)間，分片給其他線程，以提升處理效率和速度
   2. 所以，如果你用的是機(jī)械硬盤，我們通常可以多添加一些連接數(shù)，保持高并發(fā)
   3. 但如果你用的是 SSD 呢，因?yàn)镮/O等待時(shí)間非常短，所以我們就不能添加過多連接數(shù)
3. 通過來說你需要遵循這么一個(gè)公式：線程數(shù) = ((核心數(shù) * 2) + 有效磁盤數(shù))。比如一臺(tái) i7 4core 1hard disk的機(jī)器，就是 4 * 2 + 1 = 9
4. 看到這個(gè)公式不知道大家是不是很眼熟，這不僅適用于數(shù)據(jù)庫連接，也適用于任何很多CPU計(jì)算和I/O的場景 比如：設(shè)置最大線程數(shù)等

二、數(shù)據(jù)庫整體設(shè)計(jì)方案

第三方緩存

如果并發(fā)非常大，就不能讓他們?nèi)虻綌?shù)據(jù)庫上，在客戶端連接數(shù)據(jù)庫查詢時(shí)，添加如redis這種三方緩存

集群方式部署數(shù)據(jù)庫

既然我們一個(gè)數(shù)據(jù)庫承受不了巨大的并發(fā)，那為什么不多添加幾臺(tái)機(jī)器呢？ 主從復(fù)制原理圖

從圖中我們不難看出、Mysql主從復(fù)制 讀寫分離 異步復(fù)制的特性。

• tips: 在把Binary Log寫入relay log之后，slave都會(huì)把最新讀取到的Binary Log position記錄到master info上，下一次就直接從這個(gè)位置去取。

不同方式的主從復(fù)制

上面這種異步的主從復(fù)制，很明顯的一個(gè)問題就是，更新不及時(shí)的問題。當(dāng)寫入一個(gè)數(shù)據(jù)后，馬上有用戶讀取，讀取的還是之前的數(shù)據(jù)，也就是存在著延時(shí)。 要解決延時(shí)的問題，就需要引入 事務(wù)

1. 全同步復(fù)制，事務(wù)方式執(zhí)行，主節(jié)點(diǎn)先寫入，然后讓所有slave寫，必須要所有 從節(jié)點(diǎn) 把數(shù)據(jù)寫完，才返回寫成功，這樣的話會(huì)大大影響寫入的性能
2. 半同步復(fù)制，只要有一個(gè)salve寫入數(shù)據(jù)，就算成功。（如果需要半同步復(fù)制，主從節(jié)點(diǎn)都需要安裝semisync_mater.so和 semisync_slave.so插件）
3. GTID（global transaction identities）復(fù)制，主庫并行復(fù)制的時(shí)候，從庫也并行復(fù)制，解決主從同步復(fù)制延遲，實(shí)現(xiàn)自動(dòng)的failover動(dòng)作，即主節(jié)點(diǎn)掛掉，選舉從節(jié)點(diǎn)后，能快速自動(dòng)避免數(shù)據(jù)丟失。

集群高可用方案

1. 主從 HAPrxoy + keeplive
2. NDB
3. Glaera Cluster for MySQL
4. MHA（Master-Mater replication manager for MySQL），MMM（MySQL Master High Available）
5. MGR（MySQL Group Replication） => MySQL Cluster

分表

對(duì)數(shù)據(jù)進(jìn)行分類劃分，分成不同表，減少對(duì)單一表造成過多鎖操作影響性能

表結(jié)構(gòu)

1. 設(shè)計(jì)合理字段類型
2. 設(shè)計(jì)合理字段長度

三、優(yōu)化器與執(zhí)行引擎

慢日志

開啟show_query_log，執(zhí)行時(shí)間超過變量long_query_time的SQL會(huì)被記錄下來。 可以使用mysqldumpslow /var/lib/mysql/mysql-slow.log，還有很多插件可以提供比這個(gè)更優(yōu)雅的分析，這里就不詳細(xì)講了。

explain分析SQL

任何SQL在寫完之后都應(yīng)該explain一下

1. 驅(qū)動(dòng)表 ?– ?比如濫用left/right join導(dǎo)致性能低下

1. 使用left/right join會(huì)直接指定驅(qū)動(dòng)表，在MYSQL中，默認(rèn)使用Nest loop join進(jìn)行表關(guān)聯(lián)（即通過驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后通過此集合中的每一條數(shù)據(jù)篩選下一個(gè)關(guān)聯(lián)表的數(shù)據(jù)，最后合并結(jié)果，得出我們常說的臨時(shí)表）。
2. 如果驅(qū)動(dòng)表的數(shù)據(jù)是 百萬千萬級(jí)別的，可想而知這聯(lián)表查詢得有多慢。但是反過來，如果以小表作為驅(qū)動(dòng)表，借助千萬級(jí)表的索引查詢就能變得很快。
3. 如果你不確定到底該用誰來作為驅(qū)動(dòng)表，那么請交給優(yōu)化器來決定，比如：select xxx from table1, table2, table3 where ···，優(yōu)化器會(huì)將查詢記錄行數(shù)少的表作為驅(qū)動(dòng)表。
4. 如果你就是想自己指定驅(qū)動(dòng)表，那么請拿好Explain武器，在Explain的結(jié)果中，第一個(gè)就是基礎(chǔ)驅(qū)動(dòng)表
5. 排序。同樣的，對(duì)不同表排序也是有很大的性能差異，我們盡量對(duì)驅(qū)動(dòng)表進(jìn)行排序，而不要對(duì)臨時(shí)表，也就是合并后的結(jié)果集進(jìn)行排序。即執(zhí)行計(jì)劃中出現(xiàn)了 using temporary，就需要進(jìn)行優(yōu)化。

2. 執(zhí)行計(jì)劃各參數(shù)含義

1. select_type（查詢的類型）：普通查詢和復(fù)雜查詢（聯(lián)合查詢、子查詢等）
   1. SIMPLE，查詢不包含子查詢或者union
   2. PRIMARY，如果查詢包含復(fù)雜查詢的子結(jié)構(gòu)，那么就需要用到主鍵查詢
   3. SUBQUERY，在select或者where中包含 子查詢
   4. DERIVED，在from中包含子查詢
   5. UNION RESULT，從union表查詢子查詢
2. table 使用到的表名
3. type（訪問類型），找到所需行的方式，從上往下，查詢速度越來越快
   1. const或者system 常量級(jí)別的掃描，查詢表最快的一種，system是const的一種特殊情況（表中只有一條數(shù)據(jù)）
   2. eq_ref 唯一性索引掃描
   3. ref 非唯一性索引掃描
   4. range 索引的范圍掃描，比如 between、等范圍查詢
   5. index （index full）掃描全部索引樹
   6. ALL 掃描全表
   7. NULL，不需要訪問表或者索引
4. possible_keys，給出使用哪個(gè)索引能找到表中的記錄。這里被列出的索引不一定使用
5. key：到底哪一個(gè)索引被真正使用到了。如果沒有則為NULL
6. key_len：使用的索引所占用的字節(jié)數(shù)
7. ref：哪個(gè)字段或者常數(shù)和索引（key）一起被使用
8. rows：一共掃描了多少行
9. filtered（百分比）：有多少數(shù)據(jù)在server層還進(jìn)行了過濾
10. Extra：額外信息
    1. only index 信息只需要從索引中查出，可能用到了覆蓋索引，查詢非常快
    2. using where 如果查詢沒有使用索引，這里會(huì)在server層過濾再使用 where來過濾結(jié)果集
    3. impossible where 啥也沒查出來
    4. using filesort ，只要沒有通過索引來排序，而是使用了其他排序的方式就是 filesort
    5. using temporary（需要通過臨時(shí)表來對(duì)結(jié)果集進(jìn)行暫時(shí)存儲(chǔ)，然后再進(jìn)行計(jì)算。）一般來說這種情況都是進(jìn)行了DISTINCT、排序、分組
    6. using index condition 索引下推，上文講過，就是把server層這個(gè)過濾操作下推到引擎層

四、存儲(chǔ)引擎

1. 當(dāng)僅僅是插入與查詢比較多的時(shí)候，可以使用MyISAM存儲(chǔ)引擎
2. 當(dāng)只是使用臨時(shí)數(shù)據(jù)，可以使用memory
3. 當(dāng)插入、更新、查詢等并發(fā)數(shù)很多時(shí)，可以使用InnoDB

總結(jié)

從五個(gè)層次回答MYSQL優(yōu)化，由上至下

1. SQL與索引
2. 存儲(chǔ)引擎與表結(jié)構(gòu)
3. 數(shù)據(jù)庫架構(gòu)
4. MySQL配置
5. 硬件與操作系統(tǒng)

除此之外，查數(shù)據(jù)慢，要不僅僅拘留于一味的 “優(yōu)化” 數(shù)據(jù)庫，而是要從業(yè)務(wù)應(yīng)用層面去分析。比如對(duì)數(shù)據(jù)進(jìn)行緩存，對(duì)請求進(jìn)行限流等。

我們下篇文章見

相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql視頻教程