3萬字聊聊什么是MySQL

關(guān)注?歡少的成之路?回復(fù)算法，MySQL，8888，6666 領(lǐng)取海量學習資料。有機會參與領(lǐng)書活動！

大家好，我是Leo。目前在常州從事Java后端開發(fā)的工作。這篇是MySQL面試系列的總結(jié)篇。后續(xù)會不斷修訂該系列內(nèi)容。這個系列會向字節(jié)，網(wǎng)易，阿里，騰訊，美團，快手的相關(guān)朋友一起整理輸出。希望幫助更多的朋友早日入大廠！

思路

整篇的寫作大概就是這樣思路。一篇帶你熟悉MySQL！篇幅很大，建議先關(guān)注，收藏！

整個篇幅的知識點全部縮減。為面試系列打造！如果想具體深入研究，請關(guān)注公眾號瀏覽其他文章即可！

1. SQL是如何執(zhí)行的

1.1 查詢

平時我們都是關(guān)注SQL是如何執(zhí)行的，但是有沒有了解整個MySQL的結(jié)構(gòu)呢？這里我們介紹一下

整個MySQL主要分兩層。

• Service
• 存儲引擎層

這兩層主要由四部分構(gòu)成

• 連接器
• 分析器
• 優(yōu)化器
• 執(zhí)行器

連接器：?校驗用戶身份信息，校驗當前用戶的SQL語句權(quán)限，管理SQL連接的通道

分析器：?詞法分析，語法分析。用于處理客戶端的SQL語句，分析處理完之后寫入緩存，如果下次命中的話直接返回提高查詢效率。

優(yōu)化器：?生成執(zhí)行計劃，索引選擇（這里可以完美解釋我上面拋出的SQL執(zhí)行問題）

執(zhí)行器：?調(diào)用操作存儲引擎，撈取數(shù)據(jù)。

大概介紹了MySQL每一種結(jié)構(gòu)發(fā)揮的作用。這里擴展一下大家一直說的長連接和短連接的優(yōu)化！

**長連接：**建立連接之后，如果客戶端的有請求操作則一種使用同一個連接進行交互處理

**短連接：**建立連接之后，并且客戶端執(zhí)行完自己的需求之后，就關(guān)閉了連接。

長短連接總結(jié)：?數(shù)據(jù)庫建立連接這個過程是比較復(fù)雜的，所以建立盡量減少使用短連接的方式，也就是盡量使用長連接。但是長連接是比較容易漲內(nèi)存的。也會被系統(tǒng)誤認為內(nèi)存占用過大強行殺死。

優(yōu)化方案：?可以采用定期斷開長連接的方法優(yōu)化。還可以通過執(zhí)行 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做權(quán)限驗證。

1.2 修改

首先要介紹兩個日志。redo log?和?binlog。

redolog充當于我們平時生活中的記事本，備忘錄。

binlog充當于閻王殿的生死簿。

數(shù)據(jù)庫中也是一樣。當進行數(shù)據(jù)修改操作時，不會立即修改到磁盤。如果是立即修改的話，對磁盤IO影響是比較大的。所以平時的修改操作都會先寫到redo log?中，等系統(tǒng)認為不忙的時候再更新到binlog中。

區(qū)別

1. redolog是innodb引擎層特有的，binlog 是Server層自帶的。
2. redolog是物理日志，binlog是邏輯日志
3. redolog記錄在某個數(shù)據(jù)頁上做了什么修改，binlog記錄這個語句的原始邏輯。
4. redolog循環(huán)寫，binlog追加寫

修改流程

update vip set name=‘歡少的成長之路’ where ID=2

1. 先查找ID=2 這一行數(shù)據(jù)。查找方式有兩種，一種是直接從表中取，另一種就是如果緩存中存在就直接走緩存
2. 取到之后，直接修改name=歡少的成長之路?寫入新行
3. 新行更新到內(nèi)存中
4. 寫入redolog，并且當前處于prepare階段
5. 寫入binlog
6. commit提交事務(wù)

這里面可以跟面試官介紹一下兩階段提交，第四步和第五步為什么要分開寫入呢？這個就是兩階段提交的精髓

目的是為了讓數(shù)據(jù)保持一致?如果不使用“兩階段提交”，那么數(shù)據(jù)庫的狀態(tài)就有可能和用它的日志恢復(fù)出來的庫的狀態(tài)不一致，下面我們舉例論證一下。

• 先寫 redo log 后寫 binlog。假設(shè)在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 進程異常重啟，仍然能夠把數(shù)據(jù)恢復(fù)回來，所以恢復(fù)后這一行 name 的值是?歡少的成長之路。但是由于 binlog 沒寫完就 crash 了，這時候 binlog 里面就沒有記錄這個語句。因此，之后備份日志的時候，存起來的 binlog 里面就沒有這條語句。然后你會發(fā)現(xiàn)，如果需要用這個 binlog 來恢復(fù)臨時庫的話，由于這個語句的 binlog 丟失，這個臨時庫就會少了這一次更新，恢復(fù)出來的這一行 name 的值就是?歡少個人業(yè)務(wù)，與原庫的值不同。
• 先寫 binlog 后寫 redo log。如果在 binlog 寫完之后 crash，由于 redo log 還沒寫，崩潰恢復(fù)以后這個事務(wù)無效，所以這一行 name 的值是?歡少個人業(yè)務(wù)。但是 binlog 里面已經(jīng)記錄了把 name 從?歡少個人業(yè)務(wù)?改成?歡少的成長之路?這個日志。所以，在之后用 binlog 來恢復(fù)的時候就多了一個事務(wù)出來，恢復(fù)出來的這一行 c 的值就是?歡少的成長之路，與原庫的值不同。

1.3 總結(jié)

上述就是一個SQL如何執(zhí)行的介紹了，從查詢與修改分別介紹了流程以及涉及到的知識點。最重要的就是兩階段提交了。如果面試官問你的話來個反證法差不多就過關(guān)啦

2. 索引結(jié)構(gòu)

2.1 是什么

索引結(jié)構(gòu)是MySQL最底層的數(shù)據(jù)處理結(jié)構(gòu)了。主要分五塊

• 哈希
• 鏈表
• 二叉樹
• B樹
• B+樹

哈希

這個就是通過hash?算法，把每個數(shù)據(jù)都hash出一串key之后，然后存在數(shù)據(jù)頁的某一個位置。如果出現(xiàn)了相同的key就是發(fā)生了哈希碰撞。這個后續(xù)在算法章節(jié)會詳細介紹一下。

這里MySQL解決方案是采用鏈表+哈希的方式共同存儲在一個槽中。

鏈表

鏈表是一種物理存儲單元上非連續(xù)、非順序的存儲結(jié)構(gòu)，數(shù)據(jù)元素的邏輯順序是通過鏈表中的指針鏈接次序?qū)崿F(xiàn)的。

二叉樹

二叉樹是一種樹形結(jié)構(gòu)，每個節(jié)點只有兩顆子節(jié)點。它是一種最簡單且最重要的樹。二叉樹的遞歸定義為：二叉樹是一棵空樹，或者是一棵由一個根節(jié)點和兩棵互不相交的，分別稱作根的左子樹和右子樹組成的非空樹；左子樹和右子樹又同樣都是二叉樹

紅黑樹

紅黑樹是一種?特定類型的二叉樹，它是在計算機科學中用來組織數(shù)據(jù)比如數(shù)字的塊的一種結(jié)構(gòu)。若一棵二叉查找樹是紅黑樹，則它的任一子樹必為紅黑樹。

紅黑樹是一種平衡二叉查找樹的變體，它的左右子樹高差有可能大于 1，所以紅黑樹不是嚴格意義上的平衡二叉樹（AVL），但對之進行平衡的代價較低， 其平均統(tǒng)計性能要強于 AVL 。

由于每一棵紅黑樹都是一棵二叉排序樹，因此，在對紅黑樹進行查找時，可以采用運用于普通二叉排序樹上的查找算法，在查找過程中不需要顏色信息。

B+樹

B+樹是B樹的一個升級版，相對于B樹來說B+樹更充分的利用了節(jié)點的空間，讓查詢速度更加穩(wěn)定，其速度完全接近于二分法查找

2.2 優(yōu)缺點

哈希

• **優(yōu)點：**哈希索引在解決單值查詢的時候是非常快的，時間復(fù)雜度是O(1)。

• **缺點：**但是不支持范圍查詢，所以當前MySQL中也應(yīng)用到了哈希索引，但是并不是默認索引。

鏈表

• **優(yōu)點：**上面我們介紹到了，它是非連續(xù)的，非順序的。所以在進行數(shù)據(jù)修改操作時，無需找到當前節(jié)點的前后進行移動操作。直接把數(shù)據(jù)修改了之后，把指針指向最新節(jié)點就好了。
• 缺點：?如果當一個鏈表過于龐大的話，我們查詢數(shù)據(jù)時，要一個一個遍歷。鏈表沒有數(shù)組那種的高效查詢，也沒有樹形結(jié)構(gòu)的對半查詢。

二叉樹

• 優(yōu)點：?查詢數(shù)據(jù)時，采用對半查找。查詢效率非常高。解決了鏈表留下來的難題。
• 缺點：?當一個數(shù)出現(xiàn)持續(xù)遞增時，會有傾斜的狀態(tài)，比如 0 1 2 3 4 5 6 。這樣就是一個傾斜樹，查詢效率與鏈表相當。不符合MySQL的大數(shù)據(jù)存儲

紅黑樹

• 優(yōu)點：?一定程序上解決了二叉樹偏移的問題，但是問題解決的不夠根本
• 缺點：?出現(xiàn)了層級較多這個問題。層級較多會影響查詢性能。

B+樹

• 優(yōu)點：?在B樹的基礎(chǔ)上作了優(yōu)化，也是紅黑樹之后的一個進化版。主要優(yōu)化點就是數(shù)據(jù)節(jié)點的自旋。在插入時，當節(jié)點樹大于某一個限制后會自動自旋，變成另一個節(jié)點樹。而且具有排序的功能。節(jié)點與節(jié)點之間有連接關(guān)系，這是對查詢非常有利的。

2.3 總結(jié)

第二部分，大概介紹了MySQL的五個索引結(jié)構(gòu)。從概念，到優(yōu)缺點的介紹。通過優(yōu)缺點為切入點進行分析了MySQL為什么會以B+樹為默認索引結(jié)構(gòu)的原因。

3. 內(nèi)部索引

3.1 聚簇索引

概念

聚簇索引也是主鍵索引。一個表只能有一個聚簇索引。當表有聚簇索引時，數(shù)據(jù)行是保存在索引的葉子頁的。

優(yōu)點

數(shù)據(jù)訪問更快，數(shù)據(jù)都保存在一棵樹上，可以避免為了查詢其他列進行回表操作。

缺點

聚簇索引極大的提高了IO密集型應(yīng)用的性能，但是如果都放入內(nèi)存中，訪問的順序就沒那么必要了。聚簇索引也就失去了優(yōu)勢。

更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動到新的位置。

基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動行的時候，可能面臨“頁分裂”的問題。當行的主鍵值要求必須將這一行插入到某個已滿的頁中時，存儲引擎會將該頁分裂成兩個頁面來容納該行，這就是一次分裂操作。頁分裂會導(dǎo)致表占用更多的磁盤空間。

聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導(dǎo)致數(shù)據(jù)存儲不連續(xù)的時候

3.2 非聚簇索引

概念

非聚簇索引也是二級索引。

葉子節(jié)點不存儲數(shù)據(jù)，存儲的是數(shù)據(jù)行地址，也就是說根據(jù)索引查找到數(shù)據(jù)行的位置再去磁盤查找數(shù)據(jù)，這就有點類似一本書的目錄，比如要找到第三章第一節(jié)，那就現(xiàn)在目錄里面查找，找到對應(yīng)的頁碼后再去對應(yīng)的頁碼看文章。

優(yōu)缺點

非聚簇索引在做查找時，往往需要二次查詢。第一次查找到主鍵值，再通過主鍵值找到數(shù)據(jù)行對應(yīng)的數(shù)據(jù)頁，再通過數(shù)據(jù)頁中的Page Directory找到數(shù)據(jù)行。

一個表中可以存在多個非聚簇索引。

如果主鍵比較大的話，那非聚簇索引將會變得更大，因為非聚簇索引的葉子節(jié)點存儲的是主鍵值，過長的主鍵值，會導(dǎo)致非葉子節(jié)點占用更多的物理空間

3.3 普通索引

最基本的索引，沒有任何限制，是我們經(jīng)常使用到的索引。他的任務(wù)是加快對數(shù)據(jù)的訪問速度。因此，應(yīng)該只為那些最經(jīng)常出現(xiàn)在查詢條件（WHEREcolumn=）或排序條件（ORDERBYcolumn）中的數(shù)據(jù)列創(chuàng)建索引。只要有可能，就應(yīng)該選擇一個數(shù)據(jù)最整齊、最緊湊的數(shù)據(jù)列（如一個整數(shù)類型的數(shù)據(jù)列）來創(chuàng)建索引

3.4 唯一索引

與普通索引類似，不同的是，唯一索引的列值必須唯一，但允許為空值。主鍵索引是特殊的唯一索引，不允許有空值。

3.5 聯(lián)合索引

將幾個列作為一條索引進行檢索，使用最左匹配原則。舉一個用戶登錄的例子。可以把登錄賬號和登錄密碼設(shè)為聯(lián)合索引。這樣可以提供性能的同時，節(jié)省索引的維護成本。

3.6 索引下推

索引下推是在MySQL5.6引入的優(yōu)化。可以在索引遍歷過程中，對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表字數(shù)。

3.7 總結(jié)

第三部分大概介紹了MySQL內(nèi)部索引的概念，優(yōu)缺點，應(yīng)用等。面試中常問的就是聚簇索引與非聚簇索引的區(qū)別。所以這里也是把這兩塊寫的最詳細的地方。

4. 日志

4.1 錯誤日志

MySQL錯誤日志是記錄MySQL 運行過程中較為嚴重的警告和錯誤信息，以及MySQL每次啟動和關(guān)閉的詳細信息。錯誤日志的命名通常為hostname.err

通過如下SQL，可以找到錯誤日志的位置。

show variables like '%log_error%';

錯誤日志如果不清理或刪除，那么它會一直增長。在MySQL 5.5.7之前，可以通過mysqladmin –uroot –p flush-logs命令刪除錯誤日志。MySQL 5.5.7以及之后，只能通過下面方式來歸檔、備份錯誤日志

shell> mv host_name.err host_name.err-old
 
shell> mysqladmin -u root -p flush-logs
 
shell> mv host_name.err-old backup-directory

錯誤日志可以任意命名。只需要在/etc/my.cnf配置文件中，添加了參數(shù)log_error=/u02/mysql/mysql.err，重新啟動MySQL即可。

4.2 查詢?nèi)罩?/span>

MySQL的查詢?nèi)罩居涗浟怂蠱ySQL數(shù)據(jù)庫請求的信息。無論這些請求是否得到了正確的執(zhí)行。默認文件名為hostname.log。默認情況下MySQL查詢?nèi)罩臼顷P(guān)閉的。生產(chǎn)環(huán)境，如果開啟MySQL查詢?nèi)罩荆瑢π阅苓€是有蠻大的影響的

不常用就不做過多介紹了

4.3 慢日志

MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，它用來記錄在MySQL中響應(yīng)時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局小?/p>

long_query_time的默認值為10，意思是運行10S以上的語句。默認情況下，Mysql數(shù)據(jù)庫并不啟動慢查詢?nèi)罩荆枰覀兪謩觼碓O(shè)置這個參數(shù)，當然，如果不是調(diào)優(yōu)需要的話，一般不建議啟動該參數(shù)，因為開啟慢查詢?nèi)罩緯蚨嗷蛏賻硪欢ǖ男阅苡绊憽Ｂ樵內(nèi)罩局С謱⑷罩居涗泴懭胛募仓С謱⑷罩居涗泴懭霐?shù)據(jù)庫表。

慢查詢?nèi)罩旧婕暗闹匾獏?shù)：

• slow_query_log ：是否開啟慢查詢?nèi)罩荆?表示開啟，0表示關(guān)閉。
• long_query_time ：慢查詢閾值，當查詢時間多于設(shè)定的閾值時，記錄日志。

慢查詢?nèi)罩旧婕暗闹匾ぞ?/strong>：

• mysqldumpslow

常用指令

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

-s?是表示按照何種方式排序，

c: 訪問計數(shù)

l: 鎖定時間

r: 返回記錄

t: 查詢時間

al:平均鎖定時間

ar:平均返回記錄數(shù)

at:平均查詢時間

-t?是top n的意思，即為返回前面多少條的數(shù)據(jù)

后面是目錄

more：?另外建議在使用這些命令時結(jié)合 | 和more 使用 ，否則有可能出現(xiàn)刷屏的情況。

4.4 redolog 重做日志

提到redolog，肯定是要聊到redo log buffer?和redo log file。前者是日志的緩存，是易失性的。后者是日志文件，是持久性的。

寫入機制

redo log buffer?要做的是一個事務(wù)在插入一條數(shù)據(jù)的時候，需要先寫入日志。但是又不能在還沒有提交事務(wù)的時候直接寫到redo log文件中。這個日志的臨時存放處就是redo log buffer。真正在寫入redo log文件的過程是在commit這一步完成的。（執(zhí)行一個SQL語句也是一個事務(wù)）

如果還沒等到commit這一步，主要會有兩種可能

1. MySQL宕機了，這份緩沖區(qū)日志丟失了也就丟失了，也不會有什么損失。
2. 持久化到磁盤了！

接著持久化磁盤

• redo log buffer：物理上這是MySQL的進程內(nèi)存
• FS page cache：寫入到磁盤，但是還沒有進行持久化。物理上是page cache文件系統(tǒng)。
• hard disk，這個就是持久化到磁盤了

---

• 圖中的紅色區(qū)域是內(nèi)存操作，不涉及到磁盤IO。所以性能的非常快的。write也是非常快的
• 圖中的黃色部分。fsync的速度就慢了很多。因為持久化到磁盤

寫入策略

redo log buffer的寫入策略，是由innodb_flush_log_at_trx_commit

• 設(shè)置為 0 的時候，表示每次事務(wù)提交時都只是把 redo log 留在 redo log buffer 中 ;
• 設(shè)置為 1 的時候，表示每次事務(wù)提交時都將 redo log 直接持久化到磁盤；
• 設(shè)置為 2 的時候，表示每次事務(wù)提交時都只是把 redo log 寫到 page cache。

InnoDB 有一個后臺線程，每隔 1 秒，就會把 redo log buffer 中的日志，調(diào)用 write 寫到文件系統(tǒng)的 page cache，然后調(diào)用 fsync 持久化到磁盤。

刷新策略

redo log buffer的刷新策略，是由innodb_log_buffer_size?控制的。

• redo log buffer 占用的空間即將達到 innodb_log_buffer_size 一半的時候，后臺線程會主動寫盤。

（注意，由于這個事務(wù)并沒有提交，所以這個寫盤動作只是 write，而沒有調(diào)用 fsync，也就是只留在了文件系統(tǒng)的 page cache。）

• 并行的事務(wù)提交的時候，順帶將這個事務(wù)的 redo log buffer 持久化到磁盤

（假設(shè)一個事務(wù) A 執(zhí)行到一半，已經(jīng)寫了一些 redo log 到 buffer 中，這時候有另外一個線程的事務(wù) B 提交，如果 innodb_flush_log_at_trx_commit 設(shè)置的是 1，那么按照這個參數(shù)的邏輯，事務(wù) B 要把 redo log buffer 里的日志全部持久化到磁盤。這時候，就會帶上事務(wù) A 在 redo log buffer 里的日志一起持久化到磁盤。）

組提交機制

日志邏輯序列號，簡稱LSN。LSN是單調(diào)遞增的。用來對應(yīng) redo log 的一個個寫入點。每次寫入長度為 length 的 redo log， LSN 的值就會加上 length。LSN 也會寫到 InnoDB 的數(shù)據(jù)頁中，來確保數(shù)據(jù)頁不會被多次執(zhí)行重復(fù)的 redo log。

如上圖所述，

• trx1是最先到達的，會被選為這組的leader。
• 等 trx1 要開始寫盤的時候，這個組里面已經(jīng)有了三個事務(wù)，這時候 LSN 也變成了 160；
• trx1 去寫盤的時候，帶的就是 LSN=160，因此等 trx1 返回時，所有 LSN 小于等于 160 的 redo log，都已經(jīng)被持久化到磁盤；
• 這時候 trx2 和 trx3 就可以直接返回了。

所以，一次組提交里面，組員越多，節(jié)約磁盤 IOPS 的效果越好。但如果只有單線程壓測，那就只能老老實實地一個事務(wù)對應(yīng)一次持久化操作了。

在并發(fā)更新場景下，第一個事務(wù)寫完 redo log buffer 以后，接下來這個 fsync 越晚調(diào)用，組員可能越多，節(jié)約 IOPS 的效果就越好。

4.5 binlog 歸檔日志

寫入機制

binlog寫入日志這個是比較簡單的。提到binlog，必然提到binlog cache。那么binlog cache是什么？

binlog cache是一個二進制日志文件的緩沖區(qū)，他是由一個參數(shù)?binlog_cache_size?控制大小的緩沖區(qū)。

一個事務(wù)在執(zhí)行是時候是不允許被拆開的，因此無論事務(wù)多大，都是要一次性保存執(zhí)行的。那么這個就涉及到了binlog cache?的保存問題。如果所占的內(nèi)存大小超過了這個binlog_cache_size?參數(shù)的設(shè)定。就會采用暫存到磁盤。事務(wù)在提交的時候，會先把binlog cache里的數(shù)據(jù)寫入到binlog中，并清空binlog cache數(shù)據(jù)。

由上圖我們可以得知每個binlog cache是由單獨的一個線程享有的。也就是說多個線程帶著多個binlog cache寫入binlog file是非常快的，因為并沒有涉及到磁盤IO的開銷。

當進行到了fsync的時候，才是將數(shù)據(jù)持久化到磁盤操作。這個時候才會占用磁盤IO，也就是我們常說的IOPS。

何時write？何時fsync？

主要由sync_binlog控制的。

• 當它等于0時，每次提交事務(wù)都只 write，不 fsync
• 當它等于1時，每次提交事務(wù)都會執(zhí)行 fsync
• 當它大于1時， 每次提交事務(wù)都 write，但累積 N 個事務(wù)后才 fsync

因此，在出現(xiàn) IO 瓶頸的場景里，將 sync_binlog 設(shè)置成一個比較大的值，可以提升性能。在實際的業(yè)務(wù)場景中，考慮到丟失日志量的可控性，一般不建議將這個參數(shù)設(shè)成 0，比較常見的是將其設(shè)置為 100~1000 中的某個數(shù)值。

但是，將 sync_binlog 設(shè)置為 N，對應(yīng)的風險是：如果主機發(fā)生異常重啟，會丟失最近 N 個事務(wù)的 binlog 日志。

組提交

binlog也是可以組提交的。主要分成兩部分

• 先把 binlog 從 binlog cache 中寫到磁盤上的 binlog 文件；
• 調(diào)用 fsync 持久化。

如上圖所述，可以看第二步。

如果多個事務(wù)都已經(jīng)write了（也就是說寫入到redo log buffer了），再到第四步的時候就可以一起持久化到磁盤了。不是提升IOPS的這個優(yōu)化過程嘛！

不過通常情況下第 3 步執(zhí)行得會很快，所以 binlog 的 write 和 fsync 間的間隔時間短，導(dǎo)致能集合到一起持久化的 binlog 比較少，因此 binlog 的組提交的效果通常不如 redo log 的效果那么好。

如果你想提升 binlog 組提交的效果，可以通過設(shè)置?binlog_group_commit_sync_delay?和?binlog_group_commit_sync_no_delay_count?來實現(xiàn)。這兩個只要有一個滿足條件就會調(diào)用 fsync。

• binlog_group_commit_sync_delay 參數(shù)，表示延遲多少微秒后才調(diào)用 fsync;
• binlog_group_commit_sync_no_delay_count 參數(shù)，表示累積多少次以后才調(diào)用 fsync。

WAL機制主要得益于

• redo log 和 binlog 都是順序?qū)懀疟P的順序?qū)懕入S機寫速度要快；
• 組提交機制，可以大幅度降低磁盤的 IOPS 消耗。

4.6 undolog 回滾日志

undo log主要有兩個作用：回滾和多版本控制(MVCC)

在數(shù)據(jù)修改的時候，不僅記錄了redo log，還記錄undo log，如果因為某些原因?qū)е率聞?wù)失敗或回滾了，可以用undo log進行回滾

undo log主要存儲的也是邏輯日志，比如我們要insert一條數(shù)據(jù)了，那undo log會記錄的一條對應(yīng)的delete日志。我們要update一條記錄時，它會記錄一條對應(yīng)相反的update記錄。

這也應(yīng)該容易理解，畢竟回滾嘛，跟需要修改的操作相反就好，這樣就能達到回滾的目的。因為支持回滾操作，所以我們就能保證：“一個事務(wù)包含多個操作，這些操作要么全部執(zhí)行，要么全都不執(zhí)行”。【原子性】

因為undo log存儲著修改之前的數(shù)據(jù)，相當于一個前版本，MVCC實現(xiàn)的是讀寫不阻塞，讀的時候只要返回前一個版本的數(shù)據(jù)就行了。

5. 跳表，回表

5.1 為什么

跳表

跳表?也是為了?快速查找?而提出的一種數(shù)據(jù)結(jié)構(gòu)

我們在鏈表中查詢數(shù)據(jù)的時候，時間復(fù)雜度是O(n)，為了解決效率問題，跳表就產(chǎn)生了。它本質(zhì)上是一種多級鏈表，通過增加數(shù)據(jù)的冗余來換取查找的時間復(fù)雜度，屬于空間換時間的思想。不過呢，其實空間也不會消耗太多，因為冗余的只是節(jié)點指針。

優(yōu)點分析

• 相比紅黑樹來說，跳表實現(xiàn)簡單，你面試的時候是可以手寫出來的，而且插入和刪除的操作也不難。紅黑樹里面大量的自旋操作常常讓人迷惑。
• 數(shù)據(jù)是自排序的，這點和MYSQL里面的B+樹很像，默認是從小到大排序的。利用這一點就是快速進行范圍查找，而不用真正地排序。

鏈表，跳表比較

查詢流程

如果要在這里面找 21

• 鏈表：過程為 3→ 6 → 7 → 9 → 12 → 17 → 19 → 21 。
• 跳表：6→9→17→21

跳表的主要思想就是這樣逐漸建立索引，加速查找與插入。從最上層開始，如果key小于或等于當層后繼節(jié)點的key,則平移一位；如果key更大，則層數(shù)減1，繼續(xù)比較。最終一定會到第一層

插入流程

先確定該元素要占據(jù)的層數(shù) K（采用丟硬幣的方式，這完全是隨機的）。

然后在 Level 1 ... Level K 各個層的鏈表都插入元素。

用Update數(shù)組記錄插入位置，同樣從頂層開始，逐層找到每層需要插入的位置，再生成層數(shù)并插入。

例子：插入 119， K = 2

刪除流程

與插入類似

回表

回表這里我們舉一個常見的例子。從剛接觸代碼起，我們就已經(jīng)開始寫登錄注冊了。那么我們登錄的時候賬號，密碼是如何設(shè)置的呢？

數(shù)據(jù)量小還好，一旦數(shù)據(jù)量起來的肯定是要添加索引的。問題來了，索引如何建立！

如果只給賬號設(shè)置索引的話就碰到了回表操作。

MySQL底層是B+樹。如果給賬號設(shè)置索引的話，賬號這個字段就成了一個節(jié)點樹。而我們查詢的時候會查詢賬號+密碼。密碼不在這顆樹上，所以就需要回表去查詢密碼這個字段然后拼湊在一起。

5.2 如何避免

回表意味著增加磁盤IO的開銷，所以避免回表也是優(yōu)化MySQL的一種方式。還是舉登錄這個例子，賬號密碼屬于高頻查詢。給賬號+密碼創(chuàng)建一個聯(lián)合索引就可以避免回表了。

剩下的就根據(jù)各自的業(yè)務(wù)場景需求啦。比如軟件設(shè)計師官網(wǎng)的登錄。利用的是身份證+密碼。每一種都不一樣。

6. 主鍵自增ID

主鍵自增也是高頻面試話題，今天我們就來介紹一下，使用MySQL自增ID可以節(jié)省我們多少成本，ID為什么會不連續(xù)，上限的一系列問題吧。

6.1 優(yōu)缺點

• 使用主鍵自增ID比UUID節(jié)省一半磁盤空間
• 范圍like查詢，自增ID性能優(yōu)于UUID。(不過有緩存的情況下，雙方性能相差不大)
• 寫入測試，自增ID是UUID的4倍

6.2 實現(xiàn)原理

存放位置

自增值是保存在表結(jié)構(gòu)定義里的，實際上表結(jié)構(gòu)定義是存放在后綴名為.frm?文件中，但不會保存自增值。

自增值的保存策略

• MyISAM 引擎的自增值保存在數(shù)據(jù)文件中。
• innodb5.7及之前的版本，自增值都是保存在內(nèi)存中，沒有持久化每次重啟后，第一次打開表的時候都會去找自增值的最大值然后將最大值+1作為當前的自增值ID。
• innodb8.0版本，將自增值保存在了redolog中，重啟的時候依靠redolog恢復(fù)重啟之前的值

自增值修改機制

如果一個字段為自增字段，在插入一行數(shù)據(jù)的時

1. 如果插入數(shù)據(jù)時 id 字段指定為 0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT 值填到自增字段；
2. 如果插入數(shù)據(jù)時 id 字段指定了具體的值，就直接使用語句里指定的值。

• 如果要插入的值小于自增值，那么這個表的自增值不變
• 如果要插入的值大于或等于自增值，就需要把當前自增值修改為新的自增值

自增值生成算法是：從 auto_increment_offset 開始，以 auto_increment_increment 為步長，持續(xù)疊加，直到找到第一個大于 X 的值，作為新的自增值。默認值都是 1

自增值的修改時機

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

假設(shè)表中存在一條（1，1，1）數(shù)據(jù)。如果在插入一條（null，1，1）。執(zhí)行流程如下

1. 執(zhí)行器調(diào)用 InnoDB 引擎接口寫入一行，傳入的這一行的值是 (0,1,1);
2. InnoDB 發(fā)現(xiàn)用戶沒有指定自增 id 的值，獲取表 t 當前的自增值 2，然后把2代入 （2,1,1）
3. 然后再把自增值改成3
4. 執(zhí)行插入操作，因為c是唯一索引。所以插入（2,1,1）會報錯。

這個時候問題就出來了，2的值沒插進去，自增值也沒有被改回去，就形成了不連續(xù)的情況

還有一種情況就是事務(wù)問題。介紹完2個機制1個策略了。直接快刀斬亂麻不啰嗦了。

為了避免兩個事務(wù)申請到相同的自增 id，肯定要加鎖，然后順序申請。

• 事務(wù)A在執(zhí)行一個插入語句的時候會申請一個ID值，此時如果申請一個3，那么這時自增后的值為4
• 事務(wù)B也在執(zhí)行一個插入語句也申請了一個ID值，此時拿到了ID為4，同時主鍵自增為5

上面是兩個事務(wù)在執(zhí)行插入語句，如果其中一個事務(wù)執(zhí)行失敗或者進行了回滾。那么就缺失了一條產(chǎn)生了一條空隙。原本ID為4的數(shù)據(jù)沒有了。而且自增值也是5，也沒有改回4。

6.3 ID不連續(xù)的問題

1. 插入不成功自增值沒有被改回去，導(dǎo)致不連續(xù)
2. 事務(wù)回滾問題

6.4 為什么MySQL沒有把ID改回去

MySQL之所以沒有把ID改回去是因為考慮性能問題！

我們可以舉兩個反例。如果要退回去的話，肯定要判斷退回去的這個ID是否存在。那么如何判斷，肯定是要查表的。

每次在申請ID之前，先判斷表ID是否存在的話，性能是大打折扣。本來申請ID是一個很快的操作，現(xiàn)在還要去主鍵索引上判斷ID是否存在。

性能：?敢削我性能我錘死你。

還要一種情況就是，完成一個事務(wù)提交確認無誤之后，再釋放鎖。這樣的話雖然可以保證安全性，但是鎖的粒度太大，系統(tǒng)并發(fā)能力大大下降。

所以業(yè)務(wù)方面進行一個平衡，還是選擇了性能，沒有把ID改回去。

6.6 自增鎖

這里可以跟面試官簡單的介紹一些歷史

5.0版本

系統(tǒng)采用的就是我們上面介紹的比較安全的，并發(fā)度偏低的方法。一個語句申請了自增鎖，會等語句執(zhí)行結(jié)束之后才釋放。

5.1.22版本

對比5.0版本，這里引進了一個策略innodb_autoinc_lock_mode

• 如果設(shè)置為0，采用的自增鎖策略就是等語句執(zhí)行之后釋放，屬于5.0版本的策略
• 如果設(shè)置為1，insert語句申請之后會立馬釋放。如果是inser-select 還是要等語句執(zhí)行完成之后再釋放的。
• 如果設(shè)置為2，所有申請的自增鎖，申請后立即釋放，增加并發(fā)度

分析一下insert-select的問題

大家可以回想一下寫入日志的事務(wù)。如果binlog格式為statement，那么他們?nèi)绾斡涗洠?/p>

如果insert-select采用申請后立即釋放的方案。多個數(shù)據(jù)不過申請主鍵ID，會出現(xiàn)兩個事務(wù)交叉的形式。也就是說一個事務(wù)的ID數(shù)據(jù)不是連續(xù)的。

一旦兩個事務(wù)同時執(zhí)行，要么先寫事務(wù)A，要么先寫事務(wù)B。無論哪一種binlog拿去從庫執(zhí)行的時候，insert-select恢復(fù)出來的時候ID是連續(xù)的，這個庫的數(shù)據(jù)就出現(xiàn)了不一致的情況。

至今MySQL引用的是5.1.22的版本

6.7 ID上限后如何處理

這個知識點也是大廠比較常問的一個話題。因為大廠的數(shù)據(jù)量是比較大的，的確會遇到這樣的場景。

主鍵ID自增上限后，就會出現(xiàn)覆蓋掉原數(shù)據(jù)的情況。上線是4294967295，近43億。

從這個角度看，我們還是應(yīng)該在 InnoDB 表中主動創(chuàng)建自增主鍵。因為，表自增 id 到達上限后，再插入數(shù)據(jù)時報主鍵沖突錯誤，是更能被接受的。

畢竟覆蓋數(shù)據(jù)，就意味著數(shù)據(jù)丟失，影響的是數(shù)據(jù)可靠性；報主鍵沖突，是插入失敗，影響的是可用性。而一般情況下，可靠性優(yōu)先于可用性。

一個表中沒有主鍵的話，MySQL會默認建立一個隱藏字段，這個字段就是row_id。默認情況下建立的主鍵ID都是8個字節(jié)的，這個row_id是6個字節(jié)的。

7. 存儲引擎

7.1 innodb與myisam索引區(qū)別

• MyISAM 是非事務(wù)的存儲引擎，適合用于頻繁查詢的應(yīng)用。表鎖，不會出現(xiàn)死鎖，適合小數(shù)據(jù)，小并發(fā)。
• innodb是支持事務(wù)的存儲引擎，合于插入和更新操作比較多的應(yīng)用，設(shè)計合理的話是行鎖（最大區(qū)別就在鎖的級別上），適合大數(shù)據(jù)，大并發(fā)。

7.2 為什么采用innodb為默認索引

• innodb支持事務(wù)
• innodb比myisam支持更大的鎖粒度，支持并發(fā)

8. 索引失效

• like查詢以 % 開頭

• <>

• OR語句前后沒有同時使用索引

• 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換

• 使用is null函數(shù)時，不能利用索引，只能全表掃描。（其他函數(shù)也要注意）

• SQL中有or，也會失效

注意varchar就必須加單引號，如果不加就會誤認為int類型，雖然查詢效果是一致的。但是索引失效了，增加了查詢性能，也多消耗了磁盤IO的開銷。

9. 事務(wù)

9.1 是什么

什么是事務(wù)呢？事務(wù)就是銀行的需求一樣，如果在執(zhí)行過程中斷電或者不符合條件的情況被停止執(zhí)行，則已經(jīng)執(zhí)行的sql語句全部回滾。也就是說 事務(wù)操作過程要不全部成功，要不全部失敗！事務(wù)ACID的特性可以確保銀行不會弄丟你的錢

9.2 ACID

• 原子性：要不全部成功，要不全部失敗，不可能只執(zhí)行其中一部分操作，這就是事務(wù)的原子性
• 一致性：一致性主要體現(xiàn)在數(shù)據(jù)一致性，事務(wù)最終沒有提交，事務(wù)所修改的數(shù)據(jù)不會保存在數(shù)據(jù)庫中
• 隔離性：當前事務(wù)執(zhí)行的修改在最終提交之前，對其他事務(wù)是不可見的。
• 持久性：一旦事務(wù)提交，將修改的數(shù)據(jù)持久化到數(shù)據(jù)庫中就算數(shù)據(jù)庫斷電崩潰也不會丟失。

9.3 MVCC實現(xiàn)原理

MVCC是多版本并發(fā)控制。通過保存數(shù)據(jù)在某一個時間點的快照來實現(xiàn)的。也就是說不管需要執(zhí)行多長時間。每次事務(wù)執(zhí)行的數(shù)據(jù)都是一致的。相反！　根據(jù)事務(wù)開始時間的不同選擇的快照也是不同的，所以每個事務(wù)對同一張表，同一個時刻看到的數(shù)據(jù)有可能是不一樣的。（如果沒有這一方面的概念聽起來可能有點迷惑）

多版本并發(fā)控制實現(xiàn)的不同，典型的實現(xiàn)有樂觀鎖并發(fā)控制與悲觀鎖并發(fā)控制。

MVCC通過每行記錄后面保存兩個隱藏的列來實現(xiàn)的，一個是保存行的創(chuàng)建時間，一個是保存行的過期時間。存儲的不是時間值，而是系統(tǒng)的版本號。每開始一個新的事務(wù)，系統(tǒng)版本號會自動增加。事務(wù)開始時刻的系統(tǒng)版本號也就是事務(wù)的版本號，用來查詢到每行記錄的版本號進行對比。

優(yōu)點：保存這兩個額外的系統(tǒng)版本號的好處就是 操作數(shù)據(jù)的時候不需要單獨上鎖，這樣設(shè)計使得數(shù)據(jù)操作很簡單，性能也很好。并且也能保證只會讀取到符合標準的行。?缺點：每行記錄都需要額外的存儲空間，需要做更多的檢查行的操作，以及額外的維護工作

MVCC只在repertable read（可重復(fù)讀）和read committed（提交讀）兩種隔離級別下工作。其他兩種隔離級別都和mvcc不兼容！

Tip：read uncommitted總是讀取最新的數(shù)據(jù)行，而不符合當前事務(wù)版本的數(shù)據(jù)行。serializable則會對所有讀取的行都加鎖

9.4 事務(wù)隔離級別

通過set transaction isolationlevel?//設(shè)置隔離級別，設(shè)置隔離級別會在下一個事務(wù)開始的時候生效

• read uncommitted（未提交讀）：事務(wù)中的修改即使沒有提交對其他事務(wù)都是可見的，也可以稱為臟讀，這個級別會導(dǎo)致很多問題，從性能上來說不會比其他隔離級別好太多，但缺乏其他隔離級別的很多好處。除非真的有特定的需求，一般很少用
• reda committed（提交讀）：大多數(shù)數(shù)據(jù)庫默認的都是read committed，但是MySQL默認的不是這個！一個事務(wù)從執(zhí)行到提交前，其他事務(wù)都是不可見的，有時候也可以叫不可重復(fù)讀，因為兩次執(zhí)行同樣的查詢可能會得到不一樣的查詢結(jié)果
• repeatable read（可重復(fù)讀）：repeatable read解決了read committed臟讀的問題，這個隔離級別也是MySQL默認的隔離級別。該級別保證了同一個事務(wù)多次執(zhí)行可以讀取同樣的數(shù)據(jù)，但是有個缺陷就是存在幻讀！幻讀就是當事務(wù)在某個范圍內(nèi)讀取數(shù)據(jù)時，這時另一個事務(wù)在這個范圍插入了數(shù)據(jù)，當讀取的事務(wù)再次讀取該范圍時會產(chǎn)生幻行。通過多版本并發(fā)控制（MVCC）解決了幻讀的問題。
• serializable（可串行化）：這是最高的隔離級別，它通過強制事務(wù)在從串行上執(zhí)行，避免了前面說的幻讀問題，簡單來說就在在讀取數(shù)據(jù)時加一個鎖，這就暴露了另一個問題，大量的加鎖會導(dǎo)致出現(xiàn)爭鎖超時的問題。只有特定的需求情況下或者可以接收沒有并發(fā)的情況下才考慮這種隔離級別。

9.5 事務(wù)日志

事務(wù)日志這里常問的大概就是 redo log，undo log。具體的 我就不過多介紹了，寫在 第四模塊了。這里再提一遍，只是提升一下面試官問到事務(wù)的時候，絕對會問到事務(wù)日志的。建議多看看！

10. 幻讀

10.1 是什么,為什么會有

這種從事務(wù)開啟到事務(wù)結(jié)束，如果同一個數(shù)據(jù)看到不同的結(jié)果。我們就稱為?幻讀。

下面我們舉一個例子

事務(wù)A 按照一定條件進行數(shù)據(jù)讀取， 期間事務(wù)B 插入了相同搜索條件的新數(shù)據(jù)，事務(wù)A再次按照原先條件進行讀取時，發(fā)現(xiàn)了事務(wù)B 新插入的數(shù)據(jù) 稱為幻讀

10.2 還能想到哪些

臟讀

臟讀是在未提交讀隔離級別下容易遇到的問題。事務(wù)中的修改即使沒有提交對其他事務(wù)都是可見的，也可以稱為臟讀

10.3 解決方案

如果是新插入的數(shù)據(jù)可以采用間隙鎖的方式解決幻讀的問題。

如果是修改一個數(shù)據(jù)的話可以采用加鎖的方式解決幻讀的問題。

隔離級別為，串行化的情況下，幻讀是不存在的。因為串行化鎖的是整個表。

具體的實現(xiàn)，原理，方式會在11模塊介紹MySQL的鎖

11. 鎖（未做）

11.1 全局鎖

命令

11.4 間隙鎖

11.5 讀寫鎖

11.6 共享鎖

11.7 排他鎖

11.8 意向鎖

11.9 元數(shù)據(jù)鎖

11.2 表級鎖

11.3 行級鎖

12. 最左匹配原則

眾所周知，MySQL是滿足最左匹配原則的。也是面試高頻的一個點，一般會讓你介紹一下什么是最左匹配原則以及最左匹配原則的應(yīng)用技巧。下面我們來介紹一下。

假設(shè)組合索引為A，B，C。我們分情況一一介紹

• A，A一起使用完全滿足最左匹配原則
• A，B一起使用完全滿足最左匹配原則
• B，A一起使用完全滿足最左匹配原則
• A，B，C一起使用完全滿足最左匹配原則
• A，C一起使用?部分?滿足最左匹配原則
• B，C一起使用?不?滿足最左匹配原則

根據(jù)上述情況我們總結(jié)一下，最左原則。即：SQL語句中的對應(yīng)條件的先后順序無關(guān)。只要出現(xiàn)最左側(cè)的索引樹就為最左匹配原則。在explain執(zhí)行計劃中，可以通過key這一列查看是否命中，是否符合最左匹配原則。

個人建議：這里說一下題外話，建議每寫一個SQL我們要保存有走執(zhí)行計劃的習慣，如果沒命中索引，就把SQL優(yōu)化一下，時間一長，慢慢的就對SQL優(yōu)化有了簡單的認識，再配上一些理論，你的個人實力絕對會上一層樓的！

13. 如何保證MySQL主從同步

MySQL的主從同步問題，這里我們可以跟面試官介紹一下，binlog的三種格式問題，就是因為這三種格式的存在才保證了MySQL的主從同步問題。

命令參數(shù)

binlog_format=‘row’

statement

首先就是第一種statement。記錄的是大概的信息，幾乎是我們的執(zhí)行信息，我們看不到具體的邏輯是什么。所以如果同步到從庫上，很容易會發(fā)現(xiàn)數(shù)據(jù)不一致的情況。

這里格式的優(yōu)點就是，記錄日志比較簡潔，占用空間較小，但是風險較大，一旦數(shù)據(jù)丟失無法找到相應(yīng)的數(shù)據(jù)。

row

第二種就是row格式的binlog日志。這種格式的優(yōu)點就是，日志豐富，只要有row格式的binlog日志，想干什么操作都可以，丟的數(shù)據(jù)也可以隨時（一般是15天）找回來。唯一的缺點就是日志過于豐富，內(nèi)存占用過大，如果是在線上的話，磁盤寫完之后，風險也是比較大的。需要做一些特殊處理。

比如日志定期備份轉(zhuǎn)移，設(shè)置一個失效時間。保存15天內(nèi)的數(shù)據(jù)，15天外的一概不管！

mixed

這里格式的出現(xiàn)，是上述的結(jié)合體，為什么這么說呢？mixed格式，使用了statement格式的優(yōu)點，同時也使用了row格式的優(yōu)點。

我想很多讀者會感到比較疑惑，世上豈會有十全十美的事呢？下面我們詳細介紹一下。

mixed會多做一個判斷，他會判斷，這個binlog會不會引起數(shù)據(jù)不一致這個問題。如果會引起，那么就采用row格式的。如果不會引起，那么就采用statement格式的日志。

主從同步

主庫，從庫在做數(shù)據(jù)一致性同步的時候主要依靠的就是binlog日志，如果在我們做操作時，日志保存的比較詳細，那么就足矣可以保證主從一致性問題。

這里我們擴展兩個問題

①：主從切換時的數(shù)據(jù)安全性問題。有一個A庫和B庫，客戶端一開始訪問的是A庫，這個時候做了主從切換，主庫從A切換到了B。（數(shù)據(jù)同步線程具有超級管理員權(quán)限）客戶端訪問B庫的這一過程中，如果把從庫設(shè)置成readonly模式

• 可以防止其他運營的類的查詢語句的誤操作。造成數(shù)據(jù)不一致的問題。
• 可以防止A和B在切換的時候也會有一些邏輯性的BUG問題

②：主從同步的循環(huán)復(fù)制問題。節(jié)點 A 上更新了一條語句，然后再把生成的 binlog 發(fā)給節(jié)點 B，節(jié)點 B 執(zhí)行完這條更新語句后也會生成 binlog。（我建議你把參數(shù) log_slave_updates 設(shè)置為 on，表示備庫執(zhí)行 relay log 后生成 binlog）。那么，如果節(jié)點 A 同時是節(jié)點 B 的備庫，相當于又把節(jié)點 B 新生成的 binlog 拿過來執(zhí)行了一次，然后節(jié)點 A 和 B 間，會不斷地循環(huán)執(zhí)行這個更新語句，也就是循環(huán)復(fù)制了。解決方案如下：

• 規(guī)定兩個庫的 server id 必須不同，如果相同，則它們之間不能設(shè)定為主備關(guān)系；
• 一個備庫接到 binlog 并在重放的過程中，生成與原 binlog 的 server id 相同的新的 binlog；
• 每個庫在收到從自己的主庫發(fā)過來的日志后，先判斷 server id，如果跟自己的相同，表示這個日志是自己生成的，就直接丟棄這個日志。

這里不做詳細介紹了，為面試打造！詳細的技術(shù)點看其他文章。

14. MySQL高可用主要體現(xiàn)在哪些

講到高可用的話，肯定少不了兩個策略。可靠性優(yōu)先策略，可用性優(yōu)先策略。

面試官問的話肯定要先從源頭說起。比如為什么會有高可用，你可以聊一下從硬件的問題，大事務(wù)的問題，大表DDL的問題，從庫復(fù)制能力的問題。這些一系列的問題導(dǎo)致延時，為了高可用的考慮才引進了兩個策略。下面介紹了這兩個策略是什么。

可靠性優(yōu)先策略

• 判斷從庫B的seconds_behind_master?是否小于某個值，如果大于某個值的話延遲太大會影響業(yè)務(wù)數(shù)據(jù)的，所以一定要小于某個值的時候才可以繼續(xù)下一步
• 把主庫A改成只讀狀態(tài)，readonly改為true
• 再判斷seconds_behind_master的值，直到這個值變成0為止。（因為只有延時足夠低，數(shù)據(jù)才足夠安全）
• 把從庫B改成讀寫狀態(tài)，也就是把readonly改為flase
• 最后把業(yè)務(wù)的請求都打到B上

這里的第二步把主庫A改成了只讀模式，這是不可用的時間，這段時間都是不可寫的，有數(shù)據(jù)的話只能等待。

可用性優(yōu)先策略

與可靠性優(yōu)先策略對比，唯一的區(qū)別就是。這里不等同步完成之后再切換過去和狀態(tài)修改。而是直接把一系列步驟一次性到位。這樣是比較危險的，主備切換的可用性優(yōu)先策略會導(dǎo)致數(shù)據(jù)不一致。因此，大多數(shù)情況下，我都建議你使用可靠性優(yōu)先策略。畢竟對數(shù)據(jù)服務(wù)來說的話，數(shù)據(jù)的可靠性一般還是要優(yōu)于可用性的。

具體的策略根據(jù)業(yè)務(wù)來定！ 聽的不是很明白的 群里討論

15. 分布式事務(wù)主鍵ID

前段時間公司上了一套熱門方案，分庫分表，讀寫分離，一主多從這類技術(shù)棧。考慮到了全局性的唯一ID的問題。我們采用的是雪花算法進行生成唯一ID。

• 第一個部分，是 1 個 bit：0，這個是無意義的。
• 第二個部分是 41 個 bit：表示的是時間戳。
• 第三個部分是 5 個 bit：表示的是機房 id，10001。
• 第四個部分是 5 個 bit：表示的是機器 id，1 1001。
• 第五個部分是 12 個 bit：表示的序號，就是某個機房某臺機器上這一毫秒內(nèi)同時生成的 id 的序號，0000 00000000。

這個算法可以保證，一個機房的一臺機器上，在同一毫秒內(nèi)，生成了一個唯一的 id。可能一個毫秒內(nèi)會生成多個 id，但是有最后 12 個 bit 的序號來區(qū)分開來。

15.1 優(yōu)點

（1）高性能高可用：生成時不依賴于數(shù)據(jù)庫，完全在內(nèi)存中生成。

（2）容量大：每秒中能生成數(shù)百萬的自增ID。

（3）ID自增：存入數(shù)據(jù)庫中，索引效率高。

15.2 缺點

依賴與系統(tǒng)時間的一致性，如果系統(tǒng)時間被回調(diào)，或者改變，可能會造成id沖突或者重復(fù)。

15.3 體量考慮

真實開發(fā)過程中，除了一線互聯(lián)網(wǎng)大廠會有那么多的機器，估計我們不會接觸那么多的機器，我們可以改進算法，生成18個bit的ID就夠我們使用的了。

這里生成多少位的，取決于公司的體量吧

17. 分庫分表

當數(shù)據(jù)的體量達到一定級別之后，代碼優(yōu)化，已經(jīng)達不到真實的性能要求了。下一步就可以考慮分庫分表了。

我見過很多人不管什么問題，上來就分庫分表是不對的。微信公眾號也有很多篇文章的標題也是比較搞笑的《老大讓我優(yōu)化數(shù)據(jù)庫，我上來就分庫分表，他過來就是一jio》

下面可以介紹一下分庫分表下的兩種拆分以及何時拆分

17.1 水平拆分

水平拆分，主要拆的一個數(shù)據(jù)量級的問題。如果一個表中的數(shù)據(jù)超過500萬行，那么就可以考慮進行拆分了。水平拆分的方式類似于醫(yī)院男女科一樣。

來了100個人報名。50個男，50個女。50個男肯定選擇男科報名，50個女選擇女科報名。有可能例子不恰當大概的意思差不多。

如果表中有原數(shù)據(jù)，可以采用把ID取模處理。偶數(shù)去A表，基數(shù)去B表。這個例子應(yīng)該比較經(jīng)典吧。

水平拆分的優(yōu)點：

• 表關(guān)聯(lián)基本能夠在數(shù)據(jù)庫端全部完成。不會存在某些超大型數(shù)據(jù)量和高負載的表遇到瓶頸的問題；
• 應(yīng)用程序端整體架構(gòu)改動相對較少； 事務(wù)處理相對簡單；
• 只要切分規(guī)則能夠定義好，基本上較難遇到擴展性限制；

水平切分的缺點：

• 切分規(guī)則相對更為復(fù)雜，很難抽象出一個能夠滿足整個數(shù)據(jù)庫的切分規(guī)則
• 后期數(shù)據(jù)的維護難度有所增加，人為手工定位數(shù)據(jù)更困難；
• 應(yīng)用系統(tǒng)各模塊耦合度較高，可能會對后面數(shù)據(jù)的遷移拆分造成一定的困難。

17.2 垂直拆分

就是根據(jù)不同的業(yè)務(wù)進行拆分的，拆分成不同的數(shù)據(jù)庫，比如會員數(shù)據(jù)庫、訂單數(shù)據(jù)庫、支付數(shù)據(jù)庫、消息數(shù)據(jù)庫等，垂直拆分在大型電商項目中使用比較常見。

優(yōu)點：拆分后業(yè)務(wù)清晰，拆分規(guī)則明確，系統(tǒng)之間整合或擴展更加容易。

缺點：部分業(yè)務(wù)表無法join，跨數(shù)據(jù)庫查詢比較繁瑣(必須通過接口形式通訊(http+json))、會產(chǎn)生分布式事務(wù)的問題,提高了系統(tǒng)的復(fù)雜度。舉栗子：不可能出現(xiàn)，在訂單服務(wù)中，訂單服務(wù)直接連接會員服務(wù)的數(shù)據(jù)庫這種情況。

17.3 拆分解決方案

我這里用的是mycat中間件進行拆分。mycat支持10種分片策略

• 1、求模算法

• 2、分片枚舉

• 3、范圍約定

• 4、日期指定

• 5、固定分片hash算法

• 6、通配取模

• 7、ASCII碼求模通配

• 8、編程指定

• 9、字符串拆分hash解析

詳細的就不介紹了，我會選擇一篇分庫分表詳細的介紹一下。大概的就是這些了。

18. MySQL刷臟頁機制

內(nèi)存上的數(shù)據(jù)和磁盤上的數(shù)據(jù)頁的內(nèi)容一致時，稱為 “干凈頁”。

內(nèi)存上的數(shù)據(jù)和磁盤上的數(shù)據(jù)頁的內(nèi)容不一致時，稱為 “臟頁”。

MySQL刷臟頁的這個機制，會遇到查詢卡頓的情況。為什么這么說呢，我們舉一個場景吧。我老家里是開超市的，刷臟頁的這個機制就好比我們家的賬本，如果在超市營業(yè)期間，有人來賒賬，我們就可以直接把賒賬信息填寫在那種臨時記事本上，等晚上下班了再把數(shù)據(jù)轉(zhuǎn)移到超市大賒賬本上。

那么如果這段期間，臨時記事本用光了，就必須停下手里的工作把臨時記事本上的數(shù)據(jù)全部轉(zhuǎn)移到大賒賬本之后，再進行下面的操作，在轉(zhuǎn)移的過程中幾乎是屬于卡頓情況的。

刷臟頁是怎么刷的

首頁你要告訴MySQL當前的計算機能刷多少的IO能力，這樣innodb才能使出吃奶的力氣進行刷臟頁上的數(shù)據(jù)，這樣也算是性能最大化吧。這個值不能過高也不能過低，過高的導(dǎo)致查詢性能過低，如果過低就導(dǎo)致，刷臟頁的數(shù)據(jù)跟不上添加的數(shù)據(jù)。最終影響系統(tǒng)的使用性能。

刷臟頁比例如何設(shè)置

涉及的參數(shù)是?innodb_io_capacity，innodb_max_dirty_pages_pct

第二個參數(shù)是控制刷臟頁的比例，默認值為75，也就是75%。

假設(shè)臟頁比例為M，我們范圍是從0-100開始計算的，innodb每次寫入的時候都有一個序號，這個序號跟checkpoint之間的差值我們設(shè)為N。N會算出一個范圍0-100之間的數(shù)據(jù)。然后再根據(jù)F2（N）算法繼續(xù)計算，這個算法比較復(fù)雜，我們只需要能說出N越大，算出來的值就越大就好了。

然后用F1（M）和 F2（N）取一個最大值假設(shè)為R，之后引擎就可以按照 innodb_io_capacity 定義的能力乘以 R% 來控制刷臟頁的速度。

臟頁比例是通過?Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total?得到的

擴展一個參數(shù)

innodb_flush_neighbors。MySQL在刷臟頁時，會有一個連坐機制，當前面那個參數(shù)為1時，就會啟用連坐機制，如果為0時，就不會啟用連坐機制。

這個連坐機制是什么呢？如果刷一個臟頁為AA，AA的旁邊的數(shù)據(jù)頁也是臟頁，那么刷到AA的同時會把旁邊的BB也一起刷掉。

具體的計算比較復(fù)雜，建議一筆帶過，大概的流程能說出來就可以了。具體的技術(shù)文章在我公眾號中。

19. 刪除數(shù)據(jù)，表空間大小不變

這個問題應(yīng)該是被問爛了。這個問題如果學過C語言的時候應(yīng)該會更容易理解一些。

MySQL中刪除數(shù)據(jù)是采用刪除標記的方式。并不是直接刪除對應(yīng)的數(shù)據(jù)，所以給你的感覺數(shù)據(jù)的確沒有了，但是數(shù)據(jù)頁中仍然存在那塊數(shù)據(jù)內(nèi)存。

這里擴展一下空間復(fù)用的問題。

如果刪除的那個數(shù)據(jù)是在300-700之間，并且插入的那個值的ID也是 300-700之間時，才會去復(fù)用這個空間，如果不是這個范圍的就不會復(fù)用此空間。只有同時刪除一整頁數(shù)據(jù)的時候，下一次才會百分之百的復(fù)用，這樣的幾率還是比較小的。

如果不是百分之百的復(fù)用那么就會存在一種空洞的現(xiàn)象！我們復(fù)現(xiàn)一下，一條1 - 5的記錄中，1,2,5被復(fù)用了，3,4沒有被復(fù)用，這種情況就是空洞。

插入也會造成空洞，空洞的主要影響就是數(shù)據(jù)不緊湊，從而造成查詢性能變慢。

解決方案

1. 重建表
2. 重新刷新表索引

20. 200G數(shù)據(jù)，100G內(nèi)存會不會OOM

答案肯定是不會OOM的

首先我們介紹一下，當我們查詢200G的數(shù)據(jù)的流程問題。

• 獲取一行，寫到 net_buffer 中。這塊內(nèi)存的大小是由參數(shù) net_buffer_length 定義的，默認是 16k。
• 重復(fù)獲取行，直到 net_buffer 寫滿，調(diào)用網(wǎng)絡(luò)接口發(fā)出去。
• 如果發(fā)送成功，就清空 net_buffer，然后繼續(xù)取下一行，并寫入 net_buffer。
• 如果發(fā)送函數(shù)返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地網(wǎng)絡(luò)棧（socket send buffer）寫滿了，進入等待。直到網(wǎng)絡(luò)棧重新可寫，再繼續(xù)發(fā)送。

從上述流程中我們可以得到，一個查詢在發(fā)送過程中，占用的 MySQL 內(nèi)部的內(nèi)存最大就是 net_buffer_length 這么大，并不會達到 200G；socket send buffer 也不可能達到 200G（默認定義 /proc/sys/net/core/wmem_default），如果 socket send buffer 被寫滿，就會暫停讀數(shù)據(jù)的流程。

綜上所述：MySQL查詢是?邊讀邊發(fā)?的！

21. 系統(tǒng)每天早上重啟一下，不然就提示連接數(shù)據(jù)庫失敗

22. count(*) 那些問題你了解嗎

23. 大數(shù)據(jù)表你是如何操作的

24. 線上故障排查思路

1. 首先檢查服務(wù)器上最大的CPU耗用，內(nèi)存占用等線程問題。如果是Java的tomcat或者數(shù)據(jù)庫服務(wù)。
2. 先檢查Tomcat的日志文件，鎖定是Java代碼問題還是數(shù)據(jù)庫的服務(wù)問題
3. 如果是Java問題那么就可以直接鎖定Tomcat日志了
4. 如果是數(shù)據(jù)庫的服務(wù)問題那么就可以把主要精力鎖定在數(shù)據(jù)庫上了
5. 我們繼續(xù)第三步擴展一下，找到Tomcat內(nèi)日志的詳細信息進行鎖定更小范圍性的查詢。
6. 繼續(xù)第四步擴展一下，鎖定到數(shù)據(jù)庫上的話還是比較麻煩的，我們首先看看有沒有死鎖，大事務(wù)，耗時SQL，慢查詢?nèi)罩拘畔ふ沂欠襁@些原因?qū)е聰?shù)據(jù)庫宕機等問題

以上內(nèi)容是公司這邊出問題了，我接觸到的一些淺的知識，后續(xù)將繼續(xù)維護更新！

25. 如何快速的復(fù)制一張表

26. 要不要使用分區(qū)表

27. insert語句鎖怎么那么多

28. 29條SQL語句性能調(diào)優(yōu)方案

• 對查詢進行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
• 應(yīng)盡量避免在 where 子句中對字段進行 null 值判斷，創(chuàng)建表時NULL是默認值，但大多數(shù)時候應(yīng)該使用NOT NULL，或者使用一個特殊的值，如0，-1作為默 認值。
• 應(yīng)盡量避免在 where 子句中使用!=或<>操作符， MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE。
• 應(yīng)盡量避免在 where 子句中使用 or 來連接條件， 否則將導(dǎo)致引擎放棄使用索引而進行全表掃描， 可以 使用UNION合并查詢：select id from t where num=10 union all select id from t where num=20
• in 和 not in 也要慎用，否則會導(dǎo)致全表掃描，對于連續(xù)的數(shù)值，能用 between 就不要用 in 了：Select id from t where num between 1 and 3
• 如果在 where 子句中使用參數(shù)，也會導(dǎo)致全表掃描。
• 應(yīng)盡量避免在 where 子句中對字段進行表達式操作，應(yīng)盡量避免在where子句中對字段進行函數(shù)操作
• 索引固然可以提高相應(yīng)的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數(shù)最好不要超過6個，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
• 盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。
• 盡可能的使用 varchar/nvarchar 代替 char/nchar ， 因為首先變長字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。
• 最好不要使用”“返回所有：select from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。
• 盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理。
• 使用表的別名(Alias)：當在SQL語句中連接多個表時,請使用表的別名并把別名前綴于每個Column上.這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。
• 使用“臨時表”暫存中間結(jié)果，簡化SQL語句的重要方法就是采用臨時表暫存中間結(jié)果，但是，臨時表的好處遠遠不止這些，將臨時結(jié)果暫存在臨時表，后面的查詢就在tempdb中了，這可以避免程序中多次掃描主表，也大大減少了程序執(zhí)行中“共享鎖”阻塞“更新鎖”，減少了阻塞，提高了并發(fā)性能。
• 常見的簡化規(guī)則如下：不要有超過5個以上的表連接（JOIN），考慮使用臨時表或表變量存放中間結(jié)果。少用子查詢，視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。
• 用OR的字句可以分解成多個查詢，并且通過UNION 連接多個查詢。他們的速度只同是否使用索引有關(guān),如果查詢需要用到聯(lián)合索引，用UNION all執(zhí)行的效率更高.多個OR的字句沒有用到索引，改寫成UNION的形式再試圖與索引匹配。一個關(guān)鍵的問題是否用到索引。
• 在IN后面值的列表中，將出現(xiàn)最頻繁的值放在最前面，出現(xiàn)得最少的放在最后面，減少判斷的次數(shù)。
• 盡量將數(shù)據(jù)的處理工作放在服務(wù)器上，減少網(wǎng)絡(luò)的開銷，如使用存儲過程。存儲過程是編譯好、優(yōu)化過、并且被組織到一個執(zhí)行規(guī)劃里、且存儲在數(shù)據(jù)庫中的SQL語句，是控制流語言的集合，速度當然快。反復(fù)執(zhí)行的動態(tài)SQL,可以使用臨時存儲過程，該過程（臨時表）被放在Tempdb中。
• 當服務(wù)器的內(nèi)存夠多時，配制線程數(shù)量 = 最大連接數(shù)+5，這樣能發(fā)揮最大的效率；否則使用 配制線程數(shù)量<最大連接數(shù)啟用SQL SERVER的線程池來解決,如果還是數(shù)量 = 最大連接數(shù)+5，嚴重的損害服務(wù)器的性能。
• 盡量使用exists代替select count(1)來判斷是否存在記錄，count函數(shù)只有在統(tǒng)計表中所有行數(shù)時使用，而且count(1)比count(*)更有效率。
• 當有一批處理的插入或更新時，用批量插入或批量更新，絕不會一條條記錄的去更新!
• 在所有的存儲過程中，能夠用SQL語句的，我絕不會用循環(huán)去實現(xiàn)! (例如：列出上個月的每一天，我會用connect by去遞歸查詢一下，絕不會去用循環(huán)從上個月第一天到最后一天)
• sql語句用大寫，因為oracle 總是先解析sql語句，把小寫的字母轉(zhuǎn)換成大寫的再執(zhí)行。
• 別名的使用，別名是大型數(shù)據(jù)庫的應(yīng)用技巧，就是表名、列名在查詢中以一個字母為別名，查詢速度要比建連接表快1.5倍。
• 避免使用臨時表，除非卻有需要，否則應(yīng)盡量避免使用臨時表，相反，可以使用表變量代替;大多數(shù)時候(99%)，表變量駐扎在內(nèi)存中，因此速度比臨時表更快，臨時表駐扎在TempDb數(shù)據(jù)庫中，因此臨時表上的操作需要跨數(shù)據(jù)庫通信，速度自然慢。
• 查詢緩沖并不自動處理空格，因此，在寫SQL語句時，應(yīng)盡量減少空格的使用，尤其是在SQL首和尾的空格(因為，查詢緩沖并不自動截取首尾空格)。
• 我們應(yīng)該為數(shù)據(jù)庫里的每張表都設(shè)置一個ID做為其主鍵，而且最好的是一個INT型的（推薦使用UNSIGNED），并設(shè)置上自動增加的AUTO_INCREMENT標志。
• 當只要一行數(shù)據(jù)時使用 LIMIT 1
• 選擇合適的索引結(jié)構(gòu)，往往能提升很高的性能。對癥下藥嘛！

28 總結(jié)

上述文章篇幅過于龐大，最近剛參加了本科的考試，事情挺多了，公司最近加班比較頻繁。暫時先發(fā)布一篇，后續(xù)會在原篇的基礎(chǔ)上出修改版一，修訂版二，積極吸取讀者的建議繼續(xù)加大，加深文章的質(zhì)量。

上述有些章節(jié)暫時空著，后續(xù)再慢慢輸出，時間，精力有限！

感謝一路支持的所有朋友們，我們下期見！