語(yǔ)句執(zhí)行過(guò)程

1.MySQL的框架有幾個(gè)組件, 各是什么作用?

連接器：負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。
查詢緩存：查詢請(qǐng)求先訪問(wèn)緩存(key 是查詢的語(yǔ)句，value 是查詢的結(jié)果)。命中直接返回。不推薦使用緩存，更新會(huì)把緩存清除(關(guān)閉緩存：參數(shù) query_cache_type 設(shè)置成 DEMAND)。
分析器：對(duì) SQL 語(yǔ)句做解析，判斷sql是否正確。
優(yōu)化器：決定使用哪個(gè)索引，多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序。
執(zhí)行器：執(zhí)行語(yǔ)句，先判斷用戶有無(wú)查詢權(quán)限，使用表定義的存儲(chǔ)引擎。

2.Server層和存儲(chǔ)引擎層各是什么作用?

Server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過(guò)程、觸發(fā)器、視圖等。
存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB，它從 MySQL 5.5.5 版本開(kāi)始成為了默認(rèn)存儲(chǔ)引擎。

3.you have an error in your SQL syntax 這個(gè)保存是在詞法分析里還是在語(yǔ)法分析里報(bào)錯(cuò)?

語(yǔ)法分析

4.對(duì)于表的操作權(quán)限驗(yàn)證在哪里進(jìn)行?

執(zhí)行器

5.什么是WAL?

WAL 的全稱是 Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是先寫(xiě)日志，再寫(xiě)磁盤(pán).
當(dāng)有一條記錄需要更新的時(shí)候，InnoDB 引擎就會(huì)先把記錄寫(xiě)到 redo log里面，并更新內(nèi)存，這個(gè)時(shí)候更新就算完成了。同時(shí)，InnoDB 引擎會(huì)在適當(dāng)?shù)臅r(shí)候，將這個(gè)操作記錄更新到磁盤(pán)里面，而這個(gè)更新往往是在系統(tǒng)比較空閑的時(shí)候做

6.redo log相關(guān)點(diǎn)(InnoDB特有)

InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個(gè)文件，每個(gè)文件的大小是 1GB，那么總共就可以記錄 4GB 的操作。從頭開(kāi)始寫(xiě)，寫(xiě)到末尾就又回到開(kāi)頭循環(huán)寫(xiě).
write pos 是當(dāng)前記錄的位置，一邊寫(xiě)一邊后移，寫(xiě)到第 3 號(hào)文件末尾后就回到 0 號(hào)文件開(kāi)頭。checkpoint 是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件。
write pos 和 checkpoint 之間的是還空著的部分，可以用來(lái)記錄新的操作。如果 write pos 追上 checkpoint，表示redo log滿了，這時(shí)候不能再執(zhí)行新的更新，得停下來(lái)先擦掉一些記錄，把 checkpoint 推進(jìn)一下。有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫(kù)發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為 crash-safe。

redo log執(zhí)行過(guò)程示意圖.png

7.bin log(Server層, 歸檔日志)

• 7.1 binlog的格式有哪幾種？
• 1.statement
  binlog 里面記錄的就是 SQL 語(yǔ)句的原文
• 2.row （推薦）
  記錄的是操作的說(shuō)明, 但是記錄每一行的操作說(shuō)明占用空間大且影響性能
• 3.mixed
  自動(dòng)分類操作，記錄為合適的類型

8.為什么會(huì)有兩種日志

因?yàn)樽铋_(kāi)始 MySQL 里并沒(méi)有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒(méi)有 crash-safe 的能力，binlog 日志只能用于歸檔。而 InnoDB 是另一個(gè)公司以插件形式引入 MySQL 的，既然只依靠 binlog 是沒(méi)有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來(lái)實(shí)現(xiàn) crash-safe 能力。

9.binlog 和 redo log的區(qū)別在哪?

• 1.redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實(shí)現(xiàn)的，所有引擎都可以使用。
• 2.redo log 是物理日志，記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”；binlog 是邏輯日志，記錄的是這個(gè)語(yǔ)句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。
• 3.redo log 是循環(huán)寫(xiě)的，空間固定會(huì)用完；binlog 是可以追加寫(xiě)入的?！白芳訉?xiě)”是指 binlog 文件寫(xiě)到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。

10.InnoDB執(zhí)行時(shí)的內(nèi)部流程

• 1.執(zhí)行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹(shù)搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁(yè)本來(lái)就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤(pán)讀入內(nèi)存，然后再返回。
• 2.執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上 1，比如原來(lái)是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫(xiě)入這行新數(shù)據(jù)。
• 3.引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到 redo log 里面，此時(shí) redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)。
• 4.執(zhí)行器生成這個(gè)操作的 binlog，并把 binlog 寫(xiě)入磁盤(pán)。

• 5.執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫(xiě)入的 redo log 改成提交（commit）狀態(tài)，更新完成。

  
  
  執(zhí)行過(guò)程圖示

11.什么是日志的兩階段提交?

將 redo log 的寫(xiě)入拆成了兩個(gè)步驟：prepare 和 commit，這就是"兩階段提交"。

• 11.1 當(dāng)日志寫(xiě)入lodolog之后，還未寫(xiě)入binlog，數(shù)據(jù)庫(kù)crash，如何保證數(shù)據(jù)的一致性？
  binlog 還沒(méi)寫(xiě)，redo log 也還沒(méi)提交，所以崩潰恢復(fù)的時(shí)候，這個(gè)事務(wù)會(huì)回滾。這時(shí)候，binlog 還沒(méi)寫(xiě)，所以也不會(huì)傳到備庫(kù)。
• 11.2 日志剛寫(xiě)入binlog但是還未提交，發(fā)生crash？
  如果 redo log 里面的事務(wù)是完整的，也就是已經(jīng)有了 commit 標(biāo)識(shí)，則直接提交；
  如果 redo log 里面的事務(wù)只有完整的 prepare，則判斷對(duì)應(yīng)的事務(wù) binlog 是否存在并完整：
  a. 如果是，則提交事務(wù)；b. 否則，回滾事務(wù)。
• 11.3binlog的寫(xiě)入機(jī)制
  事務(wù)執(zhí)行過(guò)程中，先把日志寫(xiě)到 binlog cache，事務(wù)提交的時(shí)候，再把 binlog cache 寫(xiě)到 binlog 文件中。

12.事務(wù)的特性?

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔離性、持久性）。

13.事務(wù)的隔離級(jí)別?

• 讀未提交(read uncommitted）是指，一個(gè)事務(wù)還沒(méi)提交時(shí)，它做的變更就能被別的事務(wù)看到。
• 讀提交(read committed) => rc是指，一個(gè)事務(wù)提交之后，它做的變更才會(huì)被其他事務(wù)看到。Oracle, SQL Server默認(rèn)隔離級(jí)別
• 可重復(fù)讀(repeatable read) => rr是指，一個(gè)事務(wù)執(zhí)行過(guò)程中看到的數(shù)據(jù)，總是跟這個(gè)事務(wù)在啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的。當(dāng)然在可重復(fù)讀隔離級(jí)別下，未提交變更對(duì)其他事務(wù)也是不可見(jiàn)的。MySQL默認(rèn)的隔離級(jí)別
• 串行化(serializable)，顧名思義是對(duì)于同一行記錄，“寫(xiě)”會(huì)加“寫(xiě)鎖”，“讀”會(huì)加“讀鎖”。當(dāng)出現(xiàn)讀寫(xiě)鎖沖突的時(shí)候，后訪問(wèn)的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行。
• 13.1 rr下會(huì)產(chǎn)生幻讀，那么是如何解決幻讀的？
  間隙鎖：跟間隙鎖存在沖突關(guān)系的，是“往這個(gè)間隙中插入一個(gè)記錄”這個(gè)操作。間隙鎖之間不存在沖突關(guān)系。

14. RC和RR的實(shí)現(xiàn)方式?

• “可重復(fù)讀”隔離級(jí)別下，這個(gè)視圖是在事務(wù)啟動(dòng)時(shí)創(chuàng)建的，整個(gè)事務(wù)存在期間都用這個(gè)視圖。
• “讀提交”隔離級(jí)別下，這個(gè)視圖是在每個(gè) SQL 語(yǔ)句開(kāi)始執(zhí)行的時(shí)候創(chuàng)建的。
• “讀未提交”隔離級(jí)別下直接返回記錄上的最新值，沒(méi)有視圖概念；
• “串行化”隔離級(jí)別下直接用加鎖的方式來(lái)避免并行訪問(wèn)。

15.事務(wù)隔離的實(shí)現(xiàn)?

鎖

• 15.1原子性的實(shí)現(xiàn)
  undo log: 每條記錄在更新的時(shí)候都會(huì)同時(shí)記錄一條回滾操作。記錄上的最新值，通過(guò)回滾操作，都可以得到前一個(gè)狀態(tài)的值。

16. 事務(wù)的啟動(dòng)方式?

• 1.顯式的啟動(dòng) begin 或 start transaction。配套的提交語(yǔ)句是 commit，回滾語(yǔ)句是 rollback。
• 2. set autocommit=0，這個(gè)命令會(huì)將這個(gè)線程的自動(dòng)提交關(guān)掉。意味著如果你只執(zhí)行一個(gè) select 語(yǔ)句，這個(gè)事務(wù)就啟動(dòng)了，而且并不會(huì)自動(dòng)提交。這個(gè)事務(wù)持續(xù)存在直到你主動(dòng)執(zhí)行 commit 或 rollback 語(yǔ)句，或者斷開(kāi)連接。(推薦設(shè)置為1)

17.三種常見(jiàn)的索引結(jié)構(gòu)?

• 1.哈希表
  哈希表: 是一種以鍵 - 值（key-value）存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，我們只要輸入待查找的鍵即 key，就可以找到其對(duì)應(yīng)的值即 Value。哈希的思路很簡(jiǎn)單，把值放在數(shù)組里，用一個(gè)哈希函數(shù)把 key 換算成一個(gè)確定的位置，然后把 value 放在數(shù)組的這個(gè)位置。
  局限性：哈希表并不是有序的, 所以只適用于只有等值查詢的場(chǎng)景.(新建數(shù)據(jù)時(shí)只需要往后添加就可以,添加速度比較快)
  問(wèn)題：不可避免地，多個(gè) key 值經(jīng)過(guò)哈希函數(shù)的換算，會(huì)出現(xiàn)同一個(gè)值的情況.(哈希碰撞)
  解決：處理這種情況的一種方法是，拉出一個(gè)鏈表。
  例子:

  
  
  哈希表舉例
  
  

  圖中，User2 和 User4 根據(jù)身份證號(hào)算出來(lái)的值都是 N，但沒(méi)關(guān)系，后面還跟了一個(gè)鏈表。假設(shè)，這時(shí)候你要查 ID_card_n2 對(duì)應(yīng)的名字是什么，處理步驟就是：首先，將 ID_card_n2 通過(guò)哈希函數(shù)算出 N；然后，按順序遍歷，找到 User2。

• 2.有序數(shù)組
  按照值的大小進(jìn)行排序,查找時(shí)用二分法可以快速得到,但是更新數(shù)據(jù)時(shí)需要將插入位置后面的元素全都移動(dòng),成本太高,比較適用于靜態(tài)數(shù)據(jù).
• 3.搜索樹(shù)
  每個(gè)節(jié)點(diǎn)的左兒子小于父節(jié)點(diǎn)，父節(jié)點(diǎn)又小于右兒子. innoDB的索引結(jié)構(gòu)即為B+樹(shù)

18.根據(jù)葉子節(jié)點(diǎn)內(nèi)容對(duì)索引進(jìn)行分類

• 1.主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index）。
• 2.非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級(jí)索引（secondary index）。
• Tips:非主鍵索引存放值而不是地址，是為了防止出現(xiàn)主鍵插入或頁(yè)分裂等情況需要重建時(shí)非主鍵也要跟著重建。
  
  B+索引圖示

19.什么是頁(yè)分裂、頁(yè)合并?

B+ 樹(shù)為了維護(hù)索引有序性，在插入新值的時(shí)候需要做必要的維護(hù)。以上面這個(gè)圖為例，如果插入新的行 ID 值為 700，則只需要在 R5 的記錄后面插入一個(gè)新記錄。如果新插入的 ID 值為 400，就相對(duì)麻煩了，需要邏輯上挪動(dòng)后面的數(shù)據(jù)，空出位置。而更糟的情況是，如果 R5 所在的數(shù)據(jù)頁(yè)已經(jīng)滿了，根據(jù) B+ 樹(shù)的算法，這時(shí)候需要申請(qǐng)一個(gè)新的數(shù)據(jù)頁(yè)，然后挪動(dòng)部分?jǐn)?shù)據(jù)過(guò)去。這個(gè)過(guò)程稱為頁(yè)分裂。
在這種情況下，性能自然會(huì)受影響。除了性能外，頁(yè)分裂操作還影響數(shù)據(jù)頁(yè)的利用率。原本放在一個(gè)頁(yè)的數(shù)據(jù)，現(xiàn)在分到兩個(gè)頁(yè)中，整體空間利用率降低大約50%。
當(dāng)相鄰兩個(gè)頁(yè)由于刪除了數(shù)據(jù)，利用率很低之后，會(huì)將數(shù)據(jù)頁(yè)做合并。合并的過(guò)程，可以認(rèn)為是分裂過(guò)程的逆過(guò)程。

20.基于主鍵索引和普通索引的查詢有什么區(qū)別， 什么是回表？

• 如果語(yǔ)句是 select * from T where ID=500，即主鍵查詢方式，則只需要搜索 ID 這棵 B+ 樹(shù)；
• 如果語(yǔ)句是 select * from T where k=5，即普通索引查詢方式，則需要先搜索 k 索引樹(shù)，得到 ID 的值為 500，再到 ID 索引樹(shù)搜索一次。這個(gè)過(guò)程稱為回表。

21.為什么推薦用自增主鍵做索引？

• 1.主鍵長(zhǎng)度越小，普通索引的葉子節(jié)點(diǎn)就越小，普通索引占用的空間也就越小。
• 2.每次插入一條新記錄，都是追加操作，都不涉及到挪動(dòng)其他記錄，也不會(huì)觸發(fā)葉子節(jié)點(diǎn)的分裂。

22.什么是索引覆蓋?

查詢里面，索引已經(jīng)“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。
select ID from T where k between 3 and 5;
這時(shí)只需要查 ID 的值，而 ID 的值已經(jīng)在 k 索引樹(shù)上了，因此可以直接提供查詢結(jié)果，不需要回表

23.什么是最左前綴原則？

MySQL中的索引可以以一定順序引用多列，這種索引叫作聯(lián)合索引。如User表的name和city加聯(lián)合索引就是(name,city)，而最左前綴原則指的是，如果查詢的時(shí)候查詢條件精確匹配索引的左邊連續(xù)一列或幾列，則此列就可以被用到。

24.什么是索引下推？

索引下推（index condition pushdown) => ICP， 可以在索引遍歷過(guò)程中，對(duì)索引中包含的字段先做判斷，直接過(guò)濾掉不滿足條件的記錄，減少回表次數(shù)。

25.如何重建索引?

mysql> alter table T engine=InnoDB;

26.什么是索引擴(kuò)展？

MySQL InnoDB的二級(jí)索引（Secondary Index）會(huì)自動(dòng)補(bǔ)齊主鍵，將主鍵列追加到二級(jí)索引列后面。詳細(xì)一點(diǎn)來(lái)說(shuō)，InnoDB的二級(jí)索引（Secondary Index）除了存儲(chǔ)索引列key值，還存儲(chǔ)著主鍵的值。
如存在主鍵索引 k_i1_i2, 二級(jí)索引 k_d ,二級(jí)索引k_d（d）的元組在InnoDB內(nèi)部實(shí)際被擴(kuò)展成（d,i1,i2），即包含主鍵值。
因此在設(shè)計(jì)主鍵的時(shí)候，常見(jiàn)的一條設(shè)計(jì)原則是要求主鍵字段盡量簡(jiǎn)短，以避免二級(jí)索引過(guò)大(因?yàn)槎?jí)索引會(huì)自動(dòng)補(bǔ)齊主鍵字段)

27. 根據(jù)加鎖范圍，鎖可以分為哪幾類？

根據(jù)加鎖的范圍，MySQL 里面的鎖大致可以分成全局鎖、表級(jí)鎖和行鎖三類

• 加鎖規(guī)則:
  
  加鎖規(guī)則

28. 全局鎖是做什么的?

全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖，整個(gè)數(shù)據(jù)庫(kù)處在只讀狀態(tài)，MySQL 提供了一個(gè)加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)
全局鎖的典型使用場(chǎng)景是，做全庫(kù)邏輯備份。也就是把整庫(kù)每個(gè)表都 select 出來(lái)存成文本。

29.MySQL有自帶的邏輯備份工具，為什么還存在FTWRL？

官方自帶的邏輯備份工具是 mysqldump。當(dāng) mysqldump 使用參數(shù)–single-transaction 的時(shí)候，導(dǎo)數(shù)據(jù)之前就會(huì)啟動(dòng)一個(gè)事務(wù)，來(lái)確保拿到一致性視圖。而由于 MVCC 的支持，這個(gè)過(guò)程中數(shù)據(jù)是可以正常更新的。
single-transaction 方法只適用于所有的表使用事務(wù)引擎的庫(kù)。如果有的表使用了不支持事務(wù)的引擎，那么備份就只能通過(guò) FTWRL 方法.

30.同樣是只讀，為什么不用set global readonly=true？

• 在有些系統(tǒng)中，readonly 的值會(huì)被用來(lái)做其他邏輯，比如用來(lái)判斷一個(gè)庫(kù)是主庫(kù)還是備庫(kù)。
• 在 slave 上 如果用戶有超級(jí)權(quán)限的話 readonly 是失效的
• 如果執(zhí)行 FTWRL 命令之后由于客戶端發(fā)生異常斷開(kāi)，那么 MySQL 會(huì)自動(dòng)釋放這個(gè)全局鎖，整個(gè)庫(kù)回到可以正常更新的狀態(tài)。而將整個(gè)庫(kù)設(shè)置為 readonly 之后，如果客戶端發(fā)生異常，則數(shù)據(jù)庫(kù)就會(huì)一直保持 readonly 狀態(tài)，這樣會(huì)導(dǎo)致整個(gè)庫(kù)長(zhǎng)時(shí)間處于不可寫(xiě)狀態(tài)，風(fēng)險(xiǎn)較高。

31.表級(jí)鎖介紹？

MySQL 里面表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（meta data lock，MDL)。

• MDL不需要顯式的增加；
• MDL作用是防止DDL和DML并發(fā)的沖突
• 當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加 MDL 讀鎖, 當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加 MDL 寫(xiě)鎖。讀鎖之間不互斥，因此你可以有多個(gè)線程同時(shí)對(duì)一張表增刪改查。讀寫(xiě)鎖之間、寫(xiě)鎖之間是互斥的，用來(lái)保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個(gè)線程要同時(shí)給一個(gè)表加字段，其中一個(gè)要等另一個(gè)執(zhí)行完才能開(kāi)始執(zhí)行。
• 事務(wù)中的MDL鎖會(huì)一直持續(xù)到事務(wù)結(jié)束才會(huì)釋放

32.什么是兩段鎖協(xié)議？

在 InnoDB 事務(wù)中，行鎖是在需要的時(shí)候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時(shí)才釋放。這個(gè)就是兩階段鎖協(xié)議。

33.什么是死鎖？

當(dāng)并發(fā)系統(tǒng)中不同線程出現(xiàn)循環(huán)資源依賴，涉及的線程都在等待別的線程釋放資源時(shí)，就會(huì)導(dǎo)致這幾個(gè)線程都進(jìn)入無(wú)限等待的狀態(tài)，稱為死鎖.

34.在RR的情況下，事務(wù)每次讀取的都是快照的數(shù)據(jù)，那么其他事務(wù)更新后，當(dāng)前事務(wù)再次更新時(shí)讀取的數(shù)據(jù)時(shí)什么?

更新數(shù)據(jù)都是先讀后寫(xiě)的，而這個(gè)讀，只能讀當(dāng)前的值，稱為“當(dāng)前讀”（current read）。

35.MVVC的規(guī)則?

• 1.版本未提交，不可見(jiàn)；
• 2.版本已提交，但是是在視圖創(chuàng)建后提交的，不可見(jiàn)；
• 3.版本已提交，而且是在視圖創(chuàng)建前提交的，可見(jiàn)。

36.RC, RR的一致性視圖有什么區(qū)別？

• 在可重復(fù)讀隔離級(jí)別下，只需要在事務(wù)開(kāi)始的時(shí)候創(chuàng)建一致性視圖，之后事務(wù)里的其他查詢都共用這個(gè)一致性視圖；
• 在讀提交隔離級(jí)別下，每一個(gè)語(yǔ)句執(zhí)行前都會(huì)重新算出一個(gè)新的視圖。

37. 數(shù)據(jù)的更新過(guò)程？

當(dāng)需要更新一個(gè)數(shù)據(jù)頁(yè)時(shí)，如果數(shù)據(jù)頁(yè)在內(nèi)存中就直接更新，而如果這個(gè)數(shù)據(jù)頁(yè)還沒(méi)有在內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下，InnoDB 會(huì)將這些更新操作緩存在 change buffer 中，這樣就不需要從磁盤(pán)中讀入這個(gè)數(shù)據(jù)頁(yè)了。在下次查詢需要訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)的時(shí)候，將數(shù)據(jù)頁(yè)讀入內(nèi)存，然后執(zhí)行 change buffer 中與這個(gè)頁(yè)有關(guān)的操作。通過(guò)這種方式就能保證這個(gè)數(shù)據(jù)邏輯的正確性。

38.什么情況下change buffer中的數(shù)據(jù)會(huì)寫(xiě)入到磁盤(pán)？

• 將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁(yè)，得到最新結(jié)果的過(guò)程稱為 merge。
• 1.訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)會(huì)觸發(fā) merge
• 2.系統(tǒng)有后臺(tái)線程會(huì)定期 merge
• 3.在數(shù)據(jù)庫(kù)正常關(guān)閉（shutdown）的過(guò)程中，也會(huì)執(zhí)行 merge 操作。

39.什么條件下可以使用 change buffer ？

對(duì)于唯一索引來(lái)說(shuō)，所有的更新操作都要先判斷這個(gè)操作是否違反唯一性約束。比如，要插入 (4,400) 這個(gè)記錄，就要先判斷現(xiàn)在表中是否已經(jīng)存在 k=4 的記錄，而這必須要將數(shù)據(jù)頁(yè)讀入內(nèi)存才能判斷。如果都已經(jīng)讀入到內(nèi)存了，那直接更新內(nèi)存會(huì)更快，就沒(méi)必要使用 change buffer 了。
因此，唯一索引的更新就不能使用 change buffer，實(shí)際上也只有普通索引可以使用。

40. change buffer和redo log的比較

以該條語(yǔ)句為例子： mysql> insert into t(id,k) values(id1,k1),(id2,k2);
假設(shè)k1 所在的數(shù)據(jù)頁(yè)在內(nèi)存 (InnoDB buffer pool) 中，k2 所在的數(shù)據(jù)頁(yè)不在內(nèi)存中。

• 1.Page 1 在內(nèi)存中，直接更新內(nèi)存；
• 2.Page 2 沒(méi)有在內(nèi)存中，就在內(nèi)存的 change buffer 區(qū)域，記錄下“我要往 Page 2 插入一行”這個(gè)信息
• 3.將上述兩個(gè)動(dòng)作記入 redo log 中。
  redo log 主要節(jié)省的是隨機(jī)寫(xiě)磁盤(pán)的 IO 消耗（轉(zhuǎn)成順序?qū)懀?，?change buffer 主要節(jié)省的則是隨機(jī)讀磁盤(pán)的 IO 消耗。

41.索引錯(cuò)用的原因之一

由于索引統(tǒng)計(jì)信息不準(zhǔn)確導(dǎo)致的問(wèn)題，可以用 analyze table 來(lái)解決。

并發(fā)事務(wù)索引統(tǒng)計(jì)信息不準(zhǔn)確的模擬

delete 語(yǔ)句刪掉了所有的數(shù)據(jù)，然后再通過(guò) call idata() 插入了 10 萬(wàn)行數(shù)據(jù)，看上去是覆蓋了原來(lái)的 10 萬(wàn)行。但是，session A 開(kāi)啟了事務(wù)并沒(méi)有提交，所以之前插入的 10 萬(wàn)行數(shù)據(jù)是不能刪除的。這樣，之前的數(shù)據(jù)每一行數(shù)據(jù)都有兩個(gè)版本，舊版本是 delete 之前的數(shù)據(jù)，新版本是標(biāo)記為 deleted 的數(shù)據(jù)。索引 a 上的數(shù)據(jù)其實(shí)就有兩份。不過(guò)這個(gè)是主鍵，主鍵是直接按照表的行數(shù)來(lái)估計(jì)的。而表的行數(shù)，優(yōu)化器直接用的是 show table status 的值。

42.什么是臟頁(yè)？

當(dāng)內(nèi)存數(shù)據(jù)頁(yè)跟磁盤(pán)數(shù)據(jù)頁(yè)內(nèi)容不一致的時(shí)候，我們稱這個(gè)內(nèi)存頁(yè)為“臟頁(yè)”。內(nèi)存數(shù)據(jù)寫(xiě)入到磁盤(pán)后，內(nèi)存和磁盤(pán)上的數(shù)據(jù)頁(yè)的內(nèi)容就一致了，稱為“干凈頁(yè)”。

43.什么時(shí)候會(huì)刷臟頁(yè)或者說(shuō)臟頁(yè)里的數(shù)據(jù)會(huì)落盤(pán)？

• 1.redo log 寫(xiě)滿了，要 flush 臟頁(yè)”，這種情況是 InnoDB 要盡量避免的。因?yàn)槌霈F(xiàn)這種情況的時(shí)候，整個(gè)系統(tǒng)就不能再接受更新了，所有的更新都必須堵住。如果你從監(jiān)控上看，這時(shí)候更新數(shù)會(huì)跌為 0。
• 2.內(nèi)存不夠用了，要先將臟頁(yè)寫(xiě)到磁盤(pán)，比較常見(jiàn)
• 3.MySQL空閑時(shí)
• 4.MySQL關(guān)閉時(shí)，會(huì)把所有數(shù)據(jù)flush到磁盤(pán)。

44. 為什么delete數(shù)據(jù)之后，表沒(méi)有變小？

delete 操作只會(huì)把對(duì)應(yīng)的位置標(biāo)記為可復(fù)用，但是不會(huì)回收空間，所以磁盤(pán)文件大小不會(huì)變。

45.如何解決44的問(wèn)題？

可以通過(guò)重建表。轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作
可以通過(guò) 25 的命令進(jìn)行重建。

46. 5.6版本online DDL之后重建表的流程?

• 1.建立一個(gè)臨時(shí)文件，掃描表 A 主鍵的所有數(shù)據(jù)頁(yè)；
• 2.用數(shù)據(jù)頁(yè)中表 A 的記錄生成 B+ 樹(shù)，存儲(chǔ)到臨時(shí)文件中；
• 3.生成臨時(shí)文件的過(guò)程中，將所有對(duì) A 的操作記錄在一個(gè)日志文件（row log）中
• 4.臨時(shí)文件生成后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表 A 相同的數(shù)據(jù)文件
• 5.用臨時(shí)文件替換表 A 的數(shù)據(jù)文件。

47. optimize table、analyze table 和 alter table 這三種方式重建表的區(qū)別?

• alter就是46的流程
• analyze table t 其實(shí)不是重建表，只是對(duì)表的索引信息做重新統(tǒng)計(jì)，沒(méi)有修改數(shù)據(jù)，這個(gè)過(guò)程中加了 MDL 讀鎖；
• optimize table t 等于 recreate+analyze

48.為什么MyISAM比InnoDB的count(*)要更快？

• MyISAM 引擎把一個(gè)表的總行數(shù)存在了磁盤(pán)上，因此執(zhí)行 count(*) 的時(shí)候會(huì)直接返回這個(gè)數(shù)，效率很高；
• 而 InnoDB 引擎就麻煩了，它執(zhí)行 count(*) 的時(shí)候，需要把數(shù)據(jù)一行一行地從引擎里面讀出來(lái)，然后累積計(jì)數(shù)。(因?yàn)镸VVC的原因，不好維護(hù)自己的計(jì)數(shù)器)

49. 索引失效是由什么導(dǎo)致的?

• 1.對(duì)索引字段做函數(shù)操作
• 2.隱式類型轉(zhuǎn)換(比如 字段 為 varchar 類型，但是傳值時(shí)傳遞的為 int 類型)
• 3.隱式字符編碼轉(zhuǎn)換(比如做連接查詢時(shí)是字符集不一致)

50. 主從同步的流程？

主庫(kù) ： A 從庫(kù) ： B
備庫(kù) B 跟主庫(kù) A 之間維持了一個(gè)長(zhǎng)連接。主庫(kù) A 內(nèi)部有一個(gè)線程，專門(mén)用于服務(wù)備庫(kù) B 的這個(gè)長(zhǎng)連接。一個(gè)事務(wù)日志同步的完整過(guò)程是這樣的：

• 1.在備庫(kù) B 上通過(guò) change master 命令，設(shè)置主庫(kù) A 的 IP、端口、用戶名、密碼，以及要從哪個(gè)位置開(kāi)始請(qǐng)求 binlog，這個(gè)位置包含文件名和日志偏移量。
• 2.在備庫(kù) B 上執(zhí)行 start slave 命令，這時(shí)候備庫(kù)會(huì)啟動(dòng)兩個(gè)線程，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負(fù)責(zé)與主庫(kù)建立連接
• 3.主庫(kù) A 校驗(yàn)完用戶名、密碼后，開(kāi)始按照備庫(kù) B 傳過(guò)來(lái)的位置，從本地讀取 binlog，發(fā)給 B。
• 4.備庫(kù) B 拿到 binlog 后，寫(xiě)到本地文件，稱為中轉(zhuǎn)日志（relay log）。

• 5.sql_thread 讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行。

  
  
  主從同步流程

51.主從切換的過(guò)程？

seconds_behind_master: 備用機(jī)比主機(jī)延遲多少秒

• 1.判斷備庫(kù) B 現(xiàn)在的 seconds_behind_master，如果小于某個(gè)值（比如 5 秒）繼續(xù)下一步，否則持續(xù)重試這一步；（如果值比較大，會(huì)導(dǎo)致主備都一直處于只讀狀態(tài)時(shí)間較長(zhǎng)）
• 2.把主庫(kù) A 改成只讀狀態(tài)，即把 readonly 設(shè)置為 true；
• 3.判斷備庫(kù) B 的 seconds_behind_master 的值，直到這個(gè)值變成 0 為止；
• 4.把備庫(kù) B 改成可讀寫(xiě)狀態(tài)，也就是把 readonly 設(shè)置為 false；
• 5.把業(yè)務(wù)請(qǐng)求切到備庫(kù) B。
  以上是在可靠性優(yōu)先的情況下進(jìn)行的切換，如果是可用性優(yōu)先則直接切換，不判斷延遲時(shí)間

52.可不可以使用join，為什么？

• 如果可以使用 Index Nested-Loop Join 算法，也就是說(shuō)可以用上被驅(qū)動(dòng)表上的索引，其實(shí)是沒(méi)問(wèn)題的；
• 如果使用 Block Nested-Loop Join 算法，掃描行數(shù)就會(huì)過(guò)多。尤其是在大表上的 join 操作，這樣可能要掃描被驅(qū)動(dòng)表很多次，會(huì)占用大量的系統(tǒng)資源。所以這種 join 盡量不要用。
• 使用的什么算法可以從explain的Extra字段看到， 如果要用join，要是用小表做驅(qū)動(dòng)表，大表為被驅(qū)動(dòng)表

53.為什么union all 比union更高效？

union all不需要進(jìn)行去重

Extra:

• 1.當(dāng)需要讀一條記錄的時(shí)候，并不是將這個(gè)記錄本身從磁盤(pán)讀出來(lái)，而是以頁(yè)為單位，將其整體讀入內(nèi)存。在 InnoDB 中，每個(gè)數(shù)據(jù)頁(yè)的大小默認(rèn)是 16KB。