MYSQL

內(nèi)部模塊

連接器(JDBC、ODBC等) =>

[MYSQL 內(nèi)部
[Connection Pool] (授權(quán)饵骨、線程復(fù)用翘悉、連接限制、內(nèi)存檢測(cè)等)
=>
[SQL Interface] (DML居触、DDL、Views等) [Parser] (Query Translation老赤、Object privilege) [Optimizer] (Access Paths轮洋、 統(tǒng)計(jì)分析) [Caches & Buffers]
=>
[Pluggable Storage Engines]
]

=> [File]

一條SQL執(zhí)行過(guò)程

先看看一條查詢SQL

查詢SQL執(zhí)行過(guò)程.png

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

一條 update SQL執(zhí)行

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

Innodb 架構(gòu)

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

innodb-architecture.png

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

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

當(dāng)是update時(shí)，則會(huì)直接修改 Buffer中的值历葛。這個(gè)時(shí)候正塌，緩沖池中的數(shù)據(jù)就和磁盤(pán)中存儲(chǔ)的數(shù)據(jù)不一致了，稱(chēng)為臟頁(yè)恤溶。每隔一段時(shí)間乓诽，Innodb存儲(chǔ)引擎就會(huì)把臟頁(yè)數(shù)據(jù)刷入磁盤(pán)。

一般來(lái)說(shuō)當(dāng)更新一條數(shù)據(jù)咒程，我們需要將數(shù)據(jù)給讀取到buffer中修改鸠天，然后寫(xiě)回磁盤(pán)，其中有一次 IO 操作帐姻。

圖中緩沖池中有一塊區(qū)域叫做：change buffer稠集。
當(dāng)更新一個(gè)沒(méi)有 unique index 的數(shù)據(jù)時(shí)，直接將修改的數(shù)據(jù)放到 change buffer卖宠，然后通過(guò) merge 操作完成更新巍杈，從而減少了 IO 操作。

• 為什么要沒(méi)有唯一索引的數(shù)據(jù)更新時(shí)才能這樣呢扛伍，因?yàn)槲覀兏聰?shù)據(jù)后筷畦，可能更新后的數(shù)據(jù)和已經(jīng)存在的數(shù)據(jù)有重復(fù)，所以必須從磁盤(pán)中把所有數(shù)據(jù)讀出來(lái)比對(duì)才行。
• 所以當(dāng)我們的數(shù)據(jù)是 寫(xiě)多讀少 的時(shí)候鳖宾，就可以通過(guò) 增加 innodb_change_buffer_max_size 來(lái)調(diào)整 change buffer在buffer pool 中所占的比例吼砂，默認(rèn)25（即：25%）

問(wèn)題又來(lái)了，merge是如何運(yùn)作的

有四種情況：

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

一、redo log是什么

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

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

• redo log 默認(rèn)存儲(chǔ)在兩個(gè)文件中 ib_logfile0 ib_logfile1胡嘿，這兩個(gè)文件都是固定大小的蛉艾。為什么需要固定大小衷敌？這是因?yàn)?code>redo log的順序讀取的特性造成的勿侯，必須是連續(xù)的存儲(chǔ)空間

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

看一張圖

磁盤(pán)結(jié)構(gòu)圖.png

一般我們的數(shù)據(jù)都是分散在磁盤(pán)上的：
機(jī)械硬盤(pán)：

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

固態(tài)缴罗；

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

而我們?nèi)ゴ鎯?chǔ)時(shí)助琐，是通過(guò)文件系統(tǒng)與磁盤(pán)打交道的，而他們打交道的方式就有兩個(gè)瞒爬。隨機(jī)讀寫(xiě)和順序讀寫(xiě)

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

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

innodb-architecture.png

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

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

1. 不用寫(xiě)Log Buffer屁药，只需要每秒寫(xiě)redo log 磁盤(pán)數(shù)據(jù)一次，性能高柏锄，但會(huì)造成數(shù)據(jù) 1s 內(nèi)的一致性問(wèn)題酿箭。適用于強(qiáng)實(shí)時(shí)性，弱一致性趾娃，比如評(píng)論區(qū)評(píng)論
2. 寫(xiě)Log Buffer缭嫡，同時(shí)寫(xiě)入磁盤(pán)，性能最差抬闷，一致性最高妇蛀。 適用于弱實(shí)時(shí)性耕突，強(qiáng)一致性,比如支付場(chǎng)景
3. 寫(xiě)Log Buffer，同時(shí)寫(xiě)到os buffer（其會(huì)每秒調(diào)用 fsync 將數(shù)據(jù)刷入磁盤(pán)）评架，性能好眷茁，安全性也高。這個(gè)是實(shí)時(shí)性適中 一致性適中的纵诞，比如訂單類(lèi)上祈。

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

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

innodb-architecture.png

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

硬盤(pá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寫(xiě)入數(shù)據(jù)頁(yè)時(shí)矾瘾，不是直接寫(xiě)入到文件，而是先寫(xiě)入到這個(gè)區(qū)域箭启。這樣做的好處的是壕翩，一但操作系統(tǒng)，文件系統(tǒng)或者mysql掛掉傅寡，可以直接從這個(gè)Buffer中獲取數(shù)據(jù)放妈。
3. Change Buffer 當(dāng)Mysql shut down的時(shí)候，修改就會(huì)被存儲(chǔ)在磁盤(pán)這里
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ì)增加表空間占用的磁盤(pán)空間量。此類(lèi)操作可能需要與表中的數(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ù)庫(kù)中遷移。
3. 當(dāng)發(fā)生數(shù)據(jù)損壞，備份或二進(jìn)制日志不可用或無(wú)法重新啟動(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ù)訪問(wèn)

三丙挽、General Tablespaces

1. 通用表空間又叫共享表空間肺孵，他可以存儲(chǔ)多個(gè)表的數(shù)據(jù)
2. 如果存儲(chǔ)相同數(shù)量的表，消耗的存儲(chǔ)比 每表表空間 小
3. 在MySQL 5.7.24中棄用了將表分區(qū)放置在常規(guī)表空間中的支持颜阐，并且在將來(lái)的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)異常芒率，可以通過(guò)Undo 日志回滾。
2. 它存儲(chǔ)了篙顺，事務(wù)開(kāi)始前的原始數(shù)據(jù)與這次的修改操作偶芍。
3. Undo log 存在于回滾段（rollback segment）中，回滾段又存在系統(tǒng)表空間``撤銷(xiāo)表空間``臨時(shí)表空間中德玫，如架構(gòu)圖所示匪蟀。

Redo Log

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

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

1. 查詢到我們要修改的那條數(shù)據(jù)化焕，我們這里稱(chēng)做 origin萄窜，返給執(zhí)行器
2. 在執(zhí)行器中，修改數(shù)據(jù)撒桨，稱(chēng)為 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ù)等待被工作線程刷入磁盤(pán)

Mysql更新語(yǔ)句執(zhí)行流程.png

Bin log

說(shuō)了 Undo、Redo也順便說(shuō)一下Bin log.

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

華麗的分割線

索引篇

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

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

Pages, Extents, Segments, and Tablespaces

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

InnoDB文件級(jí)別.png

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

插入數(shù)據(jù)

如果我們?cè)谝粋€(gè)有序的字段上蕴潦，建立索引，然后插入數(shù)據(jù)俘闯。
在存儲(chǔ)的時(shí)候品擎，innodb就會(huì)按著順序一個(gè)個(gè)存儲(chǔ)到 頁(yè) 上，存滿一個(gè)頁(yè)再去申請(qǐng)新的頁(yè)备徐，然后接著存。

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

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

B+樹(shù)結(jié)構(gòu).png

幾種不同的索引組織形式

1. 聚簇索引，如上面B+樹(shù)圖所示赶么，子節(jié)點(diǎn)上存儲(chǔ)行數(shù)據(jù)肩豁，并且索引的排列的順序和索引鍵值順序一致的話就是 聚簇索引。主鍵索引就是聚簇索引辫呻，除了主鍵索引清钥，其他所以都是輔助索引
2. 輔助索引，如果我們創(chuàng)建了一個(gè)輔助索引放闺，它的葉子節(jié)點(diǎn)上只存儲(chǔ)自己的值和主鍵索引的值祟昭。這就意味著，如果我們通過(guò)輔助索引查詢所有數(shù)據(jù)怖侦，就會(huì)先去查找輔助索引中的主鍵鍵值篡悟，然后再去主鍵索引里面谜叹，查到相關(guān)數(shù)據(jù)。這個(gè)過(guò)程稱(chēng)為回表
3. rowid 如果沒(méi)有主鍵索引怎么辦呢搬葬？
   1. 沒(méi)有主鍵荷腊，但是有一個(gè) Unique key 而且都不是 null的，則會(huì)根據(jù)這個(gè) key來(lái)創(chuàng)建聚簇索引急凰。
   2. 那上面兩種都沒(méi)有呢女仰，別擔(dān)心，innodb自己維護(hù)了一個(gè)叫 rowid 的東西香府，根據(jù)這個(gè)id來(lái)創(chuàng)建 聚簇索引

索引如何起作用

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

1. 離散度低不建索引，也就是數(shù)據(jù)之間相差不大的就沒(méi)必要建立索引勿璃。（因?yàn)榻⑺饕芘祝诓樵兊臅r(shí)候，innodb大多數(shù)據(jù)都是相同的补疑，我走索引 和全表沒(méi)什么差別就會(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. 如果沒(méi)有索引條件下推闭专，因?yàn)楹竺媸?like '%xx'的查詢條件，所以這里首先根據(jù) name 走 idx聯(lián)合索引 查詢到幾條數(shù)據(jù)后旧烧，再回表查詢到全量row數(shù)據(jù)影钉，然后在server層進(jìn)行 like 過(guò)濾找到數(shù)據(jù)
   3. 如果有，則直接在引擎層對(duì)like也進(jìn)行過(guò)濾了粪滤，相當(dāng)于把server層這個(gè)過(guò)濾操作下推到引擎層了斧拍。如圖所示：

索引下推.png

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

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

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

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

鎖篇

四大特性

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

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

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

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

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

是否存在 | Read Uncommited | Read Committed | Repeatable Read | Serializable |
臟讀 | ?? | × | × | × |
不可重復(fù)度 | ?? | ?? | × | × |
幻讀 | ?? | ?? | ??(Innodb中×) | × |

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

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

鎖模型

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

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

InnoDB的MVCC原則.png

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

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

下面我們來(lái)深入研究一下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)

上面這四把鎖是最基本鎖的類(lèi)型

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

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

6. Insert Intention Locks 插入鎖
7. AUTO-INC Locks 自增鍵鎖
8. Predicate Locks for Spatial Indexes 專(zhuān)用于給Spatial Indexes用的

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

二命黔、讀寫(xiě)鎖深入解釋

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

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

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

索引的存在启搂，我們上面講過(guò)了硼控，這里再回顧一下，有下面幾種情況

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

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

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

• 如果是給輔助索引加寫(xiě)鎖灾杰，比如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,+∞] 左開(kāi)右閉的區(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 情況 則他的行鎖的都是臨鍵鎖

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

說(shuō)到了鎖那么必然逃不過(guò)要說(shuō)一下死鎖

發(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)過(guò)多少次等待
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ù)庫(kù) 執(zhí)行什么指令 的 狀態(tài)與時(shí)間

死鎖預(yù)防

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

分庫(kù)分表

動(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)行分庫(kù)分表存儲(chǔ))核心流程,SQL解析=>執(zhí)行優(yōu)化=>SQL數(shù)據(jù)庫(kù)路由=>SQL改變(比如分表螟凭，改表名)=>SQL執(zhí)行=>結(jié)果歸并）
=>
代理層 -- Mycat（將所有與數(shù)據(jù)庫(kù)的連接獨(dú)立出來(lái)虚青。全部由Mycat連接，其他服務(wù)訪問(wèn)Mycat獲取數(shù)據(jù)）
=>
服務(wù)層 -- 特殊的SQL版本

MYSQL如何做優(yōu)化

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

查詢SQL執(zhí)行過(guò)程.png

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

一、客戶端連接池

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

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

1. 我們并發(fā)的執(zhí)行SQL何乎，并不會(huì)因?yàn)檫B接數(shù)量增多而變快句惯。為什么呢？如果我有10000連接同時(shí)并發(fā)執(zhí)行支救，難道不比你10個(gè)連接執(zhí)行快得多嗎? 答案是否定的抢野，不僅不快反而越來(lái)越慢。
   1. 在計(jì)算機(jī)中各墨，我們都知道只有CPU才能真正去執(zhí)行線程指孤。而操作系統(tǒng)因?yàn)橛?code>時(shí)間分片的技術(shù)，讓我們以為一個(gè)CPU內(nèi)核執(zhí)行了多個(gè)線程贬堵。
   2. 但其實(shí)上一個(gè)CPU在某個(gè)時(shí)間段只能執(zhí)行一個(gè)線程恃轩，所以無(wú)論我們?cè)趺丛黾硬l(fā)，CPU還是只能在這個(gè)時(shí)間段里處理這么多數(shù)據(jù)黎做。
   3. 那就算CPU處理不了這么多數(shù)據(jù)叉跛，又怎么會(huì)變慢？因?yàn)?code>時(shí)間分片引几，當(dāng)多個(gè)線程看起來(lái)在"同時(shí)執(zhí)行"昧互，其實(shí)他們之間的上下文切換十分耗時(shí)
   4. 所以，一旦線程的數(shù)量超過(guò)了CPU核心的數(shù)量伟桅，再增加線程數(shù)系統(tǒng)就只會(huì)更慢敞掘，而不是更快。
2. 當(dāng)然楣铁，這只是其中最核心的原因玖雁，磁盤(pán)同樣也會(huì)對(duì)速度有影響，同時(shí)也對(duì)我們連接數(shù)配置有影響盖腕。
   1. 比如我們用的機(jī)械硬盤(pán)赫冬，我們要通過(guò)旋轉(zhuǎn)，尋址到某個(gè)位置溃列，再進(jìn)行I/O操作劲厌，這個(gè)時(shí)候，CPU就可以把時(shí)間听隐，分片給其他線程补鼻，以提升處理效率和速度
   2. 所以，如果你用的是機(jī)械硬盤(pán)，我們通撤绶叮可以多添加一些連接數(shù)咨跌，保持高并發(fā)
   3. 但如果你用的是 SSD 呢，因?yàn)?code>I/O等待時(shí)間非常短硼婿，所以我們就不能添加過(guò)多連接數(shù)
3. 通過(guò)來(lái)說(shuō)你需要遵循這么一個(gè)公式：線程數(shù) = ((核心數(shù) * 2) + 有效磁盤(pán)數(shù))锌半。比如一臺(tái) i7 4core 1hard disk的機(jī)器，就是 4 * 2 + 1 = 9
4. 看到這個(gè)公式不知道大家是不是很眼熟寇漫，這不僅適用于數(shù)據(jù)庫(kù)連接刊殉，也適用于任何很多CPU計(jì)算和I/O的場(chǎng)景 比如：設(shè)置最大線程數(shù)等

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

第三方緩存

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

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

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

MYSQL主從復(fù)制原理圖.png

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

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

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

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

1. 全同步復(fù)制淘讥，事務(wù)方式執(zhí)行圃伶，主節(jié)點(diǎn)先寫(xiě)入，然后讓所有slave寫(xiě)蒲列，必須要所有 從節(jié)點(diǎn) 把數(shù)據(jù)寫(xiě)完窒朋，才返回寫(xiě)成功，這樣的話會(huì)大大影響寫(xiě)入的性能
2. 半同步復(fù)制蝗岖，只要有一個(gè)salve寫(xiě)入數(shù)據(jù)侥猩，就算成功。（如果需要半同步復(fù)制抵赢，主從節(jié)點(diǎn)都需要安裝semisync_mater.so和 semisync_slave.so插件）
3. GTID（global transaction identities）復(fù)制欺劳，主庫(kù)并行復(fù)制的時(shí)候洛退，從庫(kù)也并行復(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)行分類(lèi)劃分掸犬，分成不同表，減少對(duì)單一表造成過(guò)多鎖操作影響性能

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

1. 設(shè)計(jì)合理字段類(lèi)型
2. 設(shè)計(jì)合理字段長(zhǎng)度

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

慢日志

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

explain分析SQL

任何SQL在寫(xiě)完之后都應(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)（即通過(guò)驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后通過(guò)此集合中的每一條數(shù)據(jù)篩選下一個(gè)關(guān)聯(lián)表的數(shù)據(jù)萍虽，最后合并結(jié)果睛廊，得出我們常說(shuō)的臨時(shí)表）。
2. 如果驅(qū)動(dòng)表的數(shù)據(jù)是 百萬(wàn)千萬(wàn)級(jí)別的杉编，可想而知這聯(lián)表查詢得有多慢超全。但是反過(guò)來(lái)，如果以小表作為驅(qū)動(dòng)表邓馒，借助千萬(wàn)級(jí)表的索引查詢就能變得很快嘶朱。
3. 如果你不確定到底該用誰(shuí)來(lái)作為驅(qū)動(dòng)表，那么請(qǐng)交給優(yōu)化器來(lái)決定绒净，比如：select xxx from table1, table2, table3 where ···见咒，優(yōu)化器會(huì)將查詢記錄行數(shù)少的表作為驅(qū)動(dòng)表。
4. 如果你就是想自己指定驅(qū)動(dòng)表挂疆，那么請(qǐ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（查詢的類(lèi)型）：普通查詢和復(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（訪問(wèn)類(lèi)型），找到所需行的方式缝彬，從上往下萌焰，查詢速度越來(lái)越快
   1. const或者system 常量級(jí)別的掃描，查詢表最快的一種谷浅，system是const的一種特殊情況（表中只有一條數(shù)據(jù)）
   2. eq_ref 唯一性索引掃描
   3. ref 非唯一性索引掃描
   4. range 索引的范圍掃描扒俯，比如 between、<壳贪、>等范圍查詢
   5. index （index full）掃描全部索引樹(shù)
   6. ALL 掃描全表
   7. NULL陵珍，不需要訪問(wèn)表或者索引
4. possible_keys，給出使用哪個(gè)索引能找到表中的記錄违施。這里被列出的索引不一定使用
5. key：到底哪一個(gè)索引被真正使用到了互纯。如果沒(méi)有則為NULL
6. key_len：使用的索引所占用的字節(jié)數(shù)
7. ref：哪個(gè)字段或者常數(shù)和索引（key）一起被使用
8. rows：一共掃描了多少行
9. filtered（百分比）：有多少數(shù)據(jù)在server層還進(jìn)行了過(guò)濾
10. Extra：額外信息
    1. only index 信息只需要從索引中查出，可能用到了覆蓋索引磕蒲，查詢非沉袅剩快
    2. using where 如果查詢沒(méi)有使用索引，這里會(huì)在server層過(guò)濾再使用 where來(lái)過(guò)濾結(jié)果集
    3. impossible where 啥也沒(méi)查出來(lái)
    4. using filesort 辣往，只要沒(méi)有通過(guò)索引來(lái)排序兔院，而是使用了其他排序的方式就是 filesort
    5. using temporary（需要通過(guò)臨時(shí)表來(lái)對(duì)結(jié)果集進(jìn)行暫時(shí)存儲(chǔ)，然后再進(jìn)行計(jì)算站削。）一般來(lái)說(shuō)這種情況都是進(jìn)行了DISTINCT坊萝、排序、分組
    6. using index condition 索引下推许起，上文講過(guò)十偶，就是把server層這個(gè)過(guò)濾操作下推到引擎層

四、存儲(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ù)庫(kù)架構(gòu)
4. MySQL配置
5. 硬件與操作系統(tǒng)

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

我們下篇文章見(jiàn)