大家好，我是賀同學。

金三銀四到了，給大家整理一些數據庫必知必會的面試題。

基礎相關

1、關係型和非關係型數據庫的區別？

關係型數據庫的優點

• 容易理解，因爲它採用了關係模型來組織數據。

• 可以保持數據的一致性。

• 數據更新的開銷比較小。

• 支持複雜查詢（帶 where 子句的查詢）

非關係型數據庫（NOSQL）的優點

• 無需經過 SQL 層的解析，讀寫效率高。

• 基於鍵值對，讀寫性能很高，易於擴展

• 可以支持多種類型數據的存儲，如圖片，文檔等等。

• 擴展（可分爲內存性數據庫以及文檔型數據庫，比如 Redis，MongoDB，HBase 等，適合場景：數據量大高可用的日誌系統 / 地理位置存儲系統）。

2、詳細說一下一條 MySQL 語句執行的步驟

Server 層按順序執行 SQL 的步驟爲：

• 客戶端請求 -> 連接器（驗證用戶身份，給予權限）

• 查詢緩存（存在緩存則直接返回，不存在則執行後續操作）

• 分析器（對 SQL 進行詞法分析和語法分析操作）

• 優化器（主要對執行的 SQL 優化選擇最優的執行方案方法）

• 執行器（執行時會先看用戶是否有執行權限，有才去使用這個引擎提供的接口）-> 去引擎層獲取數據返回（如果開啓查詢緩存則會緩存查詢結果）

索引相關

3、MySQL 使用索引的原因？

根本原因

• 索引的出現，就是爲了提高數據查詢的效率，就像書的目錄一樣。

• 對於數據庫的表而言，索引其實就是它的 “目錄”。

擴展

• 創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

• 幫助引擎層避免排序和臨時表

• 將隨機 IO 變爲順序 IO，加速表和表之間的連接。

4、索引的三種常見底層數據結構以及優缺點

三種常見的索引底層數據結構：分別是哈希表、有序數組和搜索樹。

• 哈希表這種適用於等值查詢的場景，比如 memcached 以及其它一些 NoSQL 引擎，不適合範圍查詢。

• 有序數組索引只適用於靜態存儲引擎，等值和範圍查詢性能好，但更新數據成本高。

• N 叉樹由於讀寫上的性能優點以及適配磁盤訪問模式以及廣泛應用在數據庫引擎中。

• 擴展（以 InnoDB 的一個整數字段索引爲例，這個 N 差不多是 1200。棵樹高是 4 的時候，就可以存 1200 的 3 次方個值，這已經 17 億了。考慮到樹根的數據塊總是在內存中的，一個 10 億行的表上一個整數字段的索引，查找一個值最多隻需要訪問 3 次磁盤。其實，樹的第二層也有很大概率在內存中，那麼訪問磁盤的平均次數就更少了。）

5、索引的常見類型以及它是如何發揮作用的？

根據葉子節點的內容，索引類型分爲主鍵索引和非主鍵索引。

• 主鍵索引的葉子節點存的整行數據，在 InnoDB 裏也被稱爲聚簇索引。

• 非主鍵索引葉子節點存的主鍵的值，在 InnoDB 裏也被稱爲二級索引。

6、MyISAM 和 InnoDB 實現 B 樹索引方式的區別是什麼？

• InnoDB 存儲引擎：B+ 樹索引的葉子節點保存數據本身，其數據文件本身就是索引文件。

• MyISAM 存儲引擎：B+ 樹索引的葉子節點保存數據的物理地址，葉節點的 data 域存放的是數據記錄的地址，索引文件和數據文件是分離的。

7、InnoDB 爲什麼設計 B+ 樹索引？

兩個考慮因素：

• InnoDB 需要執行的場景和功能需要在特定查詢上擁有較強的性能。

• CPU 將磁盤上的數據加載到內存中需要花費大量時間。

爲什麼選擇 B+ 樹：

• 哈希索引雖然能提供 O（1）複雜度查詢，但對範圍查詢和排序卻無法很好的支持，最終會導致全表掃描。

• B 樹能夠在非葉子節點存儲數據，但會導致在查詢連續數據可能帶來更多的隨機 IO。

• 而 B+ 樹的所有葉節點可以通過指針來相互連接，減少順序遍歷帶來的隨機 IO。

• 普通索引還是唯一索引？

  由於唯一索引用不上 change buffer 的優化機制，因此如果業務可以接受，從性能角度出發建議你優先考慮非唯一索引。

8、什麼是覆蓋索引和索引下推？

覆蓋索引：

• 在某個查詢裏面，索引 k 已經 “覆蓋了” 我們的查詢需求，稱爲覆蓋索引。

• 覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段。

索引下推：

• MySQL 5.6 引入的索引下推優化（index condition pushdown)， 可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

9、哪些操作會導致索引失效？

• 對索引使用左或者左右模糊匹配，也就是 like %xx 或者 like %xx% 這兩種方式都會造成索引失效。原因在於查詢的結果可能是多個，不知道從哪個索引值開始比較，於是就只能通過全表掃描的方式來查詢。

• 對索引進行函數 / 對索引進行表達式計算，因爲索引保持的是索引字段的原始值，而不是經過函數計算的值，自然就沒辦法走索引。

• 對索引進行隱式轉換相當於使用了新函數。

• WHERE 子句中的 OR 語句，只要有條件列不是索引列，就會進行全表掃描。

10、字符串加索引

• 直接創建完整索引，這樣可能會比較佔用空間。

• 創建前綴索引，節省空間，但會增加查詢掃描次數，並且不能使用覆蓋索引。

• 倒序存儲，再創建前綴索引，用於繞過字符串本身前綴的區分度不夠的問題。

• 創建 hash 字段索引，查詢性能穩定，有額外的存儲和計算消耗，跟第三種方式一樣，都不支持範圍掃描。

日誌相關

11、MySQL 的 change buffer 是什麼？

• 當需要更新一個數據頁時，如果數據頁在內存中就直接更新；而如果這個數據頁還沒有在內存中的話，在不影響數據一致性的前提下，InnoDB 會將這些更新操作緩存在 change buffer 中。

• 這樣就不需要從磁盤中讀入這個數據頁了，在下次查詢需要訪問這個數據頁的時候，將數據頁讀入內存，然後執行 change buffer 中與這個頁有關的操作。通過這種方式就能保證這個數據邏輯的正確性。

• 注意唯一索引的更新就不能使用 change buffer，實際上也只有普通索引可以使用。

• 適用場景：

  • 對於寫多讀少的業務來說，頁面在寫完以後馬上被訪問到的概率比較小，此時 change buffer 的使用效果最好。這種業務模型常見的就是賬單類、日誌類的系統。

• 反過來，假設一個業務的更新模式是寫入之後馬上會做查詢，那麼即使滿足了條件，將更新先記錄在 change buffer，但之後由於馬上要訪問這個數據頁，會立即觸發 merge 過程。這樣隨機訪問 IO 的次數不會減少，反而增加了 change buffer 的維護代價。

12、MySQL 是如何判斷一行掃描數的？

• MySQL 在真正開始執行語句之前，並不能精確地知道滿足這個條件的記錄有多少條。

• 而只能根據統計信息來估算記錄數。這個統計信息就是索引的 “區分度。

13、MySQL 的 redo log 和 binlog 區別？

14、爲什麼需要 redo log？

• redo log 主要用於 MySQL 異常重啓後的一種數據恢復手段，確保了數據的一致性。

• 其實是爲了配合 MySQL 的 WAL 機制。因爲 MySQL 進行更新操作，爲了能夠快速響應，所以採用了異步寫回磁盤的技術，寫入內存後就返回。但是這樣，會存在 crash 後 內存數據丟失的隱患，而 redo log 具備 crash safe 的能力。

15、爲什麼 redo log 具有 crash-safe 的能力，是 binlog 無法替代的？

第一點：redo log 可確保 innoDB 判斷哪些數據已經刷盤，哪些數據還沒有

• redo log 和 binlog 有一個很大的區別就是，一個是循環寫，一個是追加寫。也就是說 redo log 只會記錄未刷盤的日誌，已經刷入磁盤的數據都會從 redo log 這個有限大小的日誌文件裏刪除。binlog 是追加日誌，保存的是全量的日誌。

• 當數據庫 crash 後，想要恢復未刷盤但已經寫入 redo log 和 binlog 的數據到內存時，binlog 是無法恢復的。雖然 binlog 擁有全量的日誌，但沒有一個標誌讓 innoDB 判斷哪些數據已經刷盤，哪些數據還沒有。

• 但 redo log 不一樣，只要刷入磁盤的數據，都會從 redo log 中抹掉，因爲是循環寫！數據庫重啓後，直接把 redo log 中的數據都恢復至內存就可以了。

第二點：如果 redo log 寫入失敗，說明此次操作失敗，事務也不可能提交

• redo log 每次更新操作完成後，就一定會寫入日誌，如果寫入失敗，說明此次操作失敗，事務也不可能提交。

• redo log 內部結構是基於頁的，記錄了這個頁的字段值變化，只要 crash 後讀取 redo log 進行重放，就可以恢復數據。

• 這就是爲什麼 redo log 具有 crash-safe 的能力，而 binlog 不具備。

16、當數據庫 crash 後，如何恢復未刷盤的數據到內存中？

根據 redo log 和 binlog 的兩階段提交，未持久化的數據分爲幾種情況：

• change buffer 寫入，redo log 雖然做了 fsync 但未 commit，binlog 未 fsync 到磁盤，這部分數據丟失。

• change buffer 寫入，redo log fsync 未 commit，binlog 已經 fsync 到磁盤，先從 binlog 恢復 redo log，再從 redo log 恢復 change buffer。

• change buffer 寫入，redo log 和 binlog 都已經 fsync，直接從 redo log 裏恢復。

17、redo log 寫入方式？

redo log 包括兩部分內容，分別是內存中的日誌緩衝 (redo log buffer) 和磁盤上的日誌文件 (redo log file)。

MySQL 每執行一條 DML 語句，會先把記錄寫入 redo log buffer（用戶空間） ，再保存到內核空間的緩衝區 OS-buffer 中，後續某個時間點再一次性將多個操作記錄寫到 redo log file（刷盤） 。這種先寫日誌，再寫磁盤的技術，就是 WAL。

可以發現，redo log buffer 寫入到 redo log file，是經過 OS buffer 中轉的。其實可以通過參數 innodb_flush_log_at_trx_commit 進行配置，參數值含義如下：

• 0：稱爲延遲寫，事務提交時不會將 redo log buffer 中日誌寫入到 OS buffer，而是每秒寫入 OS buffer 並調用寫入到 redo log file 中。

• 1：稱爲實時寫，實時刷”，事務每次提交都會將 redo log buffer 中的日誌寫入 OS buffer 並保存到 redo log file 中。

• 2：稱爲實時寫，延遲刷。每次事務提交寫入到 OS buffer，然後是每秒將日誌寫入到 redo log file。

18、redo log 的執行流程?

我們來看下 Redo log 的執行流程，假設執行的 SQL 如下：

update T set a =1 where id =666

1. MySQL 客戶端將請求語句 update T set a =1 where id =666，發往 MySQL Server 層。

2. MySQL Server 層接收到 SQL 請求後，對其進行分析、優化、執行等處理工作，將生成的 SQL 執行計劃發到 InnoDB 存儲引擎層執行。

3. InnoDB 存儲引擎層將 a 修改爲 1 的這個操作記錄到內存中。

4. 記錄到內存以後會修改 redo log 的記錄，會在添加一行記錄，其內容是需要在哪個數據頁上做什麼修改。

5. 此後，將事務的狀態設置爲 prepare ，說明已經準備好提交事務了。

6. 等到 MySQL Server 層處理完事務以後，會將事務的狀態設置爲 commit，也就是提交該事務。

7. 在收到事務提交的請求以後，redo log 會把剛纔寫入內存中的操作記錄寫入到磁盤中，從而完成整個日誌的記錄過程。

19、binlog 的概念是什麼，起到什麼作用， 可以保證 crash-safe 嗎?

• binlog 是歸檔日誌，屬於 MySQL Server 層的日誌。可以實現主從複製和數據恢復兩個作用。

• 當需要恢復數據時，可以取出某個時間範圍內的 binlog 進行重放恢復。

• 但是 binlog 不可以做 crash safe，因爲 crash 之前，binlog 可能沒有寫入完全 MySQL 就掛了。所以需要配合 redo log 纔可以進行 crash safe。

20、什麼是兩階段提交？

MySQL 將 redo log 的寫入拆成了兩個步驟：prepare 和 commit，中間再穿插寫入 binlog，這就是 "兩階段提交"。

而兩階段提交就是讓這兩個狀態保持邏輯上的一致。redolog 用於恢復主機故障時的未更新的物理數據，binlog 用於備份操作。兩者本身就是兩個獨立的個體，要想保持一致，就必須使用分佈式事務的解決方案來處理。

爲什麼需要兩階段提交呢?

• 如果不用兩階段提交的話，可能會出現這樣情況

• 先寫 redo log，crash 後 bin log 備份恢復時少了一次更新，與當前數據不一致。

• 先寫 bin log，crash 後，由於 redo log 沒寫入，事務無效，所以後續 bin log 備份恢復時，數據不一致。

• 兩階段提交就是爲了保證 redo log 和 binlog 數據的安全一致性。只有在這兩個日誌文件邏輯上高度一致了才能放心的使用。

在恢復數據時，redolog 狀態爲 commit 則說明 binlog 也成功，直接恢復數據；如果 redolog 是 prepare，則需要查詢對應的 binlog 事務是否成功，決定是回滾還是執行。

21、MySQL 怎麼知道 binlog 是完整的?

一個事務的 binlog 是有完整格式的：

• statement 格式的 binlog，最後會有 COMMIT；

• row 格式的 binlog，最後會有一個 XID event。

22、什麼是 WAL 技術，有什麼優點？

WAL，中文全稱是 Write-Ahead Logging，它的關鍵點就是日誌先寫內存，再寫磁盤。MySQL 執行更新操作後，在真正把數據寫入到磁盤前，先記錄日誌。

好處是不用每一次操作都實時把數據寫盤，就算 crash 後也可以通過 redo log 恢復，所以能夠實現快速響應 SQL 語句。

23、binlog 日誌的三種格式

binlog 日誌有三種格式

• Statement：基於 SQL 語句的複製 ((statement-based replication,SBR))

• Row：基於行的複製。(row-based replication,RBR)

• Mixed：混合模式複製。(mixed-based replication,MBR)

Statement 格式

每一條會修改數據的 SQL 都會記錄在 binlog 中

• 優點：不需要記錄每一行的變化，減少了 binlog 日誌量，節約了 IO，提高性能。

• 缺點：由於記錄的只是執行語句，爲了這些語句能在備庫上正確運行，還必須記錄每條語句在執行的時候的一些相關信息，以保證所有語句能在備庫得到和在主庫端執行時候相同的結果。

Row 格式

不記錄 SQL 語句上下文相關信息，僅保存哪條記錄被修改。

• 優點：binlog 中可以不記錄執行的 SQL 語句的上下文相關的信息，僅需要記錄那一條記錄被修改成什麼了。所以 rowlevel 的日誌內容會非常清楚的記錄下每一行數據修改的細節。不會出現某些特定情況下的存儲過程、或 function、或 trigger 的調用和觸發無法被正確複製的問題。

• 缺點: 可能會產生大量的日誌內容。

Mixed 格式

實際上就是 Statement 與 Row 的結合。一般的語句修改使用 statment 格式保存 binlog，如一些函數，statement 無法完成主從複製的操作，則採用 row 格式保存 binlog，MySQL 會根據執行的每一條具體的 SQL 語句來區分對待記錄的日誌形式。

24、redo log 日誌格式

redo log buffer (內存中) 是由首尾相連的四個文件組成的，它們分別是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。

• write pos 是當前記錄的位置，一邊寫一邊後移，寫到第 3 號文件末尾後就回到 0 號文件開頭。

• checkpoint 是當前要擦除的位置，也是往後推移並且循環的，擦除記錄前要把記錄更新到數據文件。

• write pos 和 checkpoint 之間的是 “粉板” 上還空着的部分，可以用來記錄新的操作。

• 如果 write pos 追上 checkpoint，表示 “粉板” 滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進一下。

• 有了 redo log，當數據庫發生宕機重啓後，可通過 redo log 將未落盤的數據（check point 之後的數據）恢復，保證已經提交的事務記錄不會丟失，這種能力稱爲 crash-safe。

25、原本可以執行得很快的 SQL 語句，執行速度卻比預期的慢很多，原因是什麼？如何解決？

原因：從大到小可分爲四種情況

• MySQL 數據庫本身被堵住了，比如：系統或網絡資源不夠。

• SQL 語句被堵住了，比如：表鎖，行鎖等，導致存儲引擎不執行對應的 SQL 語句。

• 確實是索引使用不當，沒有走索引。

• 表中數據的特點導致的，走了索引，但回表次數龐大。

解決：

• 考慮採用 force index 強行選擇一個索引

• 考慮修改語句，引導 MySQL 使用我們期望的索引。比如把 “order by b limit 1” 改成 “order by b,a limit 1” ，語義的邏輯是相同的。

• 第三種方法是，在有些場景下，可以新建一個更合適的索引，來提供給優化器做選擇，或刪掉誤用的索引。

• 如果確定是索引根本沒必要，可以考慮刪除索引。

26、InnoDB 數據頁結構

一個數據頁大致劃分七個部分

• File Header：表示頁的一些通用信息，佔固定的 38 字節。

• page Header：表示數據頁專有信息，佔固定的 56 字節。

• inimum+Supermum：兩個虛擬的僞記錄，分別表示頁中的最小記錄和最大記錄，佔固定的 26 字節。

• User Records：真正存儲我們插入的數據，大小不固定。

• Free Space：頁中尚未使用的部分，大小不固定。

• Page Directory：頁中某些記錄的相對位置，也就是各個槽對應的記錄在頁面中的地址偏移量。

• File Trailer：用於檢驗頁是否完整，佔固定大小 8 字節。

數據相關

27、MySQL 是如何保證數據不丟失的？

• 只要 redolog 和 binlog 保證持久化磁盤就能確保 MySQL 異常重啓後回覆數據

• 在恢復數據時，redolog 狀態爲 commit 則說明 binlog 也成功，直接恢復數據；如果 redolog 是 prepare，則需要查詢對應的 binlog 事務是否成功，決定是回滾還是執行。

28、誤刪數據怎麼辦？

DBA 的最核心的工作就是保證數據的完整性，先要做好預防，預防的話大概是通過這幾個點：

• 權限控制與分配 (數據庫和服務器權限)

• 製作操作規範

• 定期給開發進行培訓

• 搭建延遲備庫

• 做好 SQL 審計，只要是對線上數據有更改操作的語句 (DML 和 DDL) 都需要進行審覈

• 做好備份。備份的話又分爲兩個點 (1) 如果數據量比較大，用物理備份 xtrabackup。定期對數據庫進行全量備份，也可以做增量備份。(2) 如果數據量較少，用 mysqldump 或者 mysqldumper。再利用 binlog 來恢復或者搭建主從的方式來恢復數據。定期備份 binlog 文件也是很有必要的

• 如果發生了數據刪除的操作，又可以從以下幾個點來恢復:

• DML 誤操作語句造成數據不完整或者丟失。可以通過 flashback，美團的 myflash，也是一個不錯的工具，本質都差不多

• 都是先解析 binlog event，然後在進行反轉。把 delete 反轉爲 insert，insert 反轉爲 delete，update 前後 image 對調。

• 所以必須設置 binlog_format=row 和 binlog_row_image=full，切記恢復數據的時候，應該先恢復到臨時的實例，然後在恢復回主庫上。

• DDL 語句誤操作 (truncate 和 drop)，由於 DDL 語句不管 binlog_format 是 row 還是 statement ，在 binlog 裏都只記錄語句，不記錄 image 所以恢復起來相對要麻煩得多。

• 只能通過全量備份 + 應用 binlog 的方式來恢復數據。一旦數據量比較大，那麼恢復時間就特別長

• rm 刪除：使用備份跨機房，或者最好是跨城市保存。

29、drop、truncate 和 delete 的區別

• DELETE 語句執行刪除的過程是每次從表中刪除一行，並且同時將該行的刪除操作作爲事務記錄在日誌中保存以便進行進行回滾操作。

• TRUNCATE TABLE  則一次性地從表中刪除所有的數據並不把單獨的刪除操作記錄記入日誌保存，刪除行是不能恢復的。並且在刪除的過程中不會激活與表有關的刪除觸發器。執行速度快。

• drop 語句將表所佔用的空間全釋放掉。

• 在速度上，一般來說，drop> truncate > delete。

• 如果想刪除部分數據用 delete，注意帶上 where 子句，回滾段要足夠大；

• 如果想刪除表，當然用 drop；如果想保留表而將所有數據刪除，如果和事務無關，用 truncate 即可；

• 如果和事務有關，或者想觸發 trigger，還是用 delete；如果是整理表內部的碎片，可以用 truncate 跟上 reuse stroage，再重新導入 / 插入數據。

30、在 MySQL 中有兩個 kill 命令

• 一個是 kill query + 線程 id，表示終止這個線程中正在執行的語句

• 一個是 kill connection + 線程 id，這裏 connection 可缺省，表示斷開這個線程的連接

kill 不掉的原因

• kill 命令被堵了，還沒到位

• kill 命令到位了，但是沒被立刻觸發

• kill 命令被觸發了，但執行完也需要時間

31、如何理解 MySQL 的邊讀邊發

• 如果客戶端接受慢，會導致 MySQL 服務端由於結果發不出去，這個事務的執行時間會很長。

• 服務端並不需要保存一個完整的結果集，取數據和發數據的流程都是通過一個 next_buffer 來操作的。

• 內存的數據頁都是在 Buffer_Pool 中操作的。

• InnoDB 管理 Buffer_Pool 使用的是改進的 LRU 算法，使用鏈表實現，實現上，按照 5:3 的比例把整個 LRU 鏈表分成了 young 區域和 old 區域。

32、MySQL 的大表查詢爲什麼不會爆內存？

• 由於 MySQL 是邊讀變發，因此對於數據量很大的查詢結果來說，不會再 server 端保存完整的結果集，所以，如果客戶端讀結果不及時，會堵住 MySQL 的查詢過程，但是不會把內存打爆。

• InnoDB 引擎內部，由於有淘汰策略，InnoDB 管理 Buffer_Pool 使用的是改進的 LRU 算法，使用鏈表實現，實現上，按照 5:3 的比例把整個 LRU 鏈表分成了 young 區域和 old 區域。對冷數據的全掃描，影響也能做到可控制。

33、MySQL 臨時表的用法和特性

• 只對當前 session 可見。

• 可以與普通表重名。

• 增刪改查用的是臨時表。

• show tables 不顯示普通表。

• 在實際應用中，臨時表一般用於處理比較複雜的計算邏輯。

• 由於臨時表是每個線程自己可見的，所以不需要考慮多個線程執行同一個處理時臨時表的重名問題，在線程退出的時候，臨時表會自動刪除。

34、MySQL 存儲引擎介紹（InnoDB、MyISAM、MEMORY）

• InnoDB 是事務型數據庫的首選引擎，支持事務安全表 (ACID)，支持行鎖定和外鍵。MySQL5.5.5 之後，InnoDB 作爲默認存儲引擎

• MyISAM 基於 ISAM 的存儲引擎，並對其進行擴展。它是在 Web、數據存儲和其他應用環境下最常用的存儲引擎之一。MyISAM 擁有較高的插入、查詢速度，但不支持事務。在 MySQL5.5.5 之前的版本中，MyISAM 是默認存儲引擎

• MEMORY 存儲引擎將表中的數據存儲到內存中，爲查詢和引用其他表數據提供快速訪問。

35、都說 InnoDB 好，那還要不要使用 MEMORY 引擎？

• 內存表就是使用 memory 引擎創建的表

• 爲什麼我不建議你在生產環境上使用內存表。這裏的原因主要包括兩個方面：鎖粒度問題；數據持久化問題。

• 由於重啓會丟數據，如果一個備庫重啓，會導致主備同步線程停止；如果主庫跟這個備庫是雙 M 架構，還可能導致主庫的內存表數據被刪掉。

36、如果數據庫誤操作, 如何執行數據恢復?

數據庫在某個時候誤操作，就可以找到距離誤操作最近的時間節點的 bin log，重放到臨時數據庫裏，然後選擇誤刪的數據節點，恢復到線上數據庫。

主從備份相關

37、MySQL 是如何保證主備同步？

主備關係的建立：

• 一開始創建主備關係的時候，是由備庫指定的，比如基於位點的主備關係，備庫說 “我要從 binlog 文件 A 的位置 P” 開始同步，主庫就從這個指定的位置開始往後發。

• 而主備關係搭建之後，是主庫決定要發給數據給備庫的，所以主庫有新的日誌也會發給備庫。

MySQL 主備切換流程：

• 客戶端讀寫都是直接訪問 A，而節點 B 是備庫，只要將 A 的更新都同步過來，到本地執行就可以保證數據是相同的。

• 當需要切換的時候就把節點換一下，A 的節點 B 的備庫

一個事務完整的同步過程：

• 備庫 B 和主庫 A 建立來了長鏈接，主庫 A 內部專門線程用於維護了這個長鏈接。

• 在備庫 B 上通過 changemaster 命令設置主庫 A 的 IP 端口用戶名密碼以及從哪個位置開始請求 binlog 包括文件名和日誌偏移量

• 在備庫 B 上執行 start-slave 命令備庫會啓動兩個線程：io_thread 和 sql_thread 分別負責建立連接和讀取中轉日誌進行解析執行

• 備庫讀取主庫傳過來的 binlog 文件備庫收到文件寫到本地成爲中轉日誌

• 後來由於多線程複製方案的引入，sql_thread 演化成了多個線程。

38、什麼是主備延遲

主庫和備庫在執行同一個事務的時候出現時間差的問題，主要原因有：

• 有些部署條件下，備庫所在機器的性能要比主庫性能差。

• 備庫的壓力較大。

• 大事務，一個主庫上語句執行 10 分鐘，那麼這個事務可能會導致從庫延遲 10 分鐘。

39、爲什麼要有多線程複製策略？

• 因爲單線程複製的能力全面低於多線程複製，對於更新壓力較大的主庫，備庫可能是一直追不上主庫的，帶來的現象就是備庫上 seconds_behind_master 值越來越大。

• 在實際應用中，建議使用可靠性優先策略，減少主備延遲，提升系統可用性，儘量減少大事務操作，把大事務拆分小事務。

40、MySQL 的並行策略有哪些？

• 按表分發策略：如果兩個事務更新不同的表，它們就可以並行。因爲數據是存儲在表裏的，所以按表分發，可以保證兩個 worker 不會更新同一行。缺點：如果碰到熱點表，比如所有的更新事務都會涉及到某一個表的時候，所有事務都會被分配到同一個 worker 中，就變成單線程複製了。

• 按行分發策略：如果兩個事務沒有更新相同的行，它們在備庫上可以並行。如果兩個事務沒有更新相同的行，它們在備庫上可以並行執行。顯然，這個模式要求 binlog 格式必須是 row。缺點：相比於按表並行分發策略，按行並行策略在決定線程分發的時候，需要消耗更多的計算資源。

41、MySQL 的一主一備和一主多從有什麼區別？

在一主一備的雙 M 架構裏，主備切換隻需要把客戶端流量切到備庫；而在一主多從架構裏，主備切換除了要把客戶端流量切到備庫外，還需要把從庫接到新主庫上。

42、主庫出問題如何解決?

• 基於位點的主備切換：存在找同步位點這個問題

• MySQL 5.6 版本引入了 GTID，徹底解決了這個困難。那麼，GTID 到底是什麼意思，又是如何解決找同步位點這個問題呢？

• GTID：全局事務 ID，是一個事務在提交的時候生成的，是這個事務的唯一標識；它由兩部分組成，格式是：GTID=server_uuid:gno

• 每個 MySQL 實例都維護了一個 GTID 集合，用來對應 “這個實例執行過的所有事務”。

• 在基於 GTID 的主備關係裏，系統認爲只要建立主備關係，就必須保證主庫發給備庫的日誌是完整的。因此，如果實例 B 需要的日誌已經不存在，A’就拒絕把日誌發給 B。

43、MySQL 讀寫分離涉及到過期讀問題的幾種解決方案?

• 強制走主庫方案

• sleep 方案

• 判斷主備無延遲方案

• 配合 semi-sync 方案

• 等主庫位點方案

• GTID 方案。

• 實際生產中，先客戶端對請求做分類，區分哪些請求可以接受過期讀，而哪些請求完全不能接受過期讀；然後，對於不能接受過期讀的語句，再使用等 GTID 或等位點的方案。

44、MySQL 的併發鏈接和併發查詢有什麼區別？

• 在執行 show processlist 的結果裏，看到了幾千個連接，指的是併發連接。而 "當前正在執行" 的語句，纔是併發查詢。

• 併發連接數多影響的是內存，併發查詢太高對 CPU 不利。一個機器的 CPU 核數有限，線程全衝進來，上下文切換的成本就會太高。

• 所以需要設置參數：innodb_thread_concurrency 用來限制線程數，當線程數達到該參數，InnoDB 就會認爲線程數用完了，會阻止其他語句進入引擎執行。

性能相關

45、短時間提高 MySQL 性能的方法

• 第一種方法：先處理掉那些佔着連接但是不工作的線程。或者再考慮斷開事務內空閒太久的連接。kill connection + id

• 第二種方法：減少連接過程的消耗：慢查詢性能問題在 MySQL 中，會引發性能問題的慢查詢，大體有以下三種可能：索引沒有設計好；SQL 語句沒寫好；MySQL 選錯了索引（force index）。

46、爲什麼 MySQL 自增主鍵 ID 不連續？

• 唯一鍵衝突

• 事務回滾

• 自增主鍵的批量申請

• 深層次原因是：MySQL 不判斷自增主鍵是否存在，從而減少加鎖的時間範圍和粒度，這樣能保持更高的性能，確保自增主鍵不能回退，所以纔有自增主鍵不連續。

• 自增主鍵怎麼做到唯一性？自增值加 1 來通過自增鎖控制併發。

47、InnoDB 爲什麼要用自增 ID 作爲主鍵？

• 自增主鍵的插入模式，符合遞增插入，每次都是追加操作，不涉及挪動記錄，也不會觸發葉子節點的分裂。

• 每次插入新的記錄就會順序添加到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁。

• 而有業務邏輯的字段做主鍵，不容易保證有序插入，由於每次插入主鍵的值近似於隨機

• 因此每次新紀錄都要被插到現有索引頁得中間某個位置， 頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，寫數據成本較高。

48、如何最快的複製一張表？

• 爲了避免對源表加讀鎖，更穩妥的方案是先將數據寫到外部文本文件，然後再寫回目標表

• 一種方法是，使用 mysqldump 命令將數據導出成一組 INSERT 語句

• 另一種方法是直接將結果導出成. csv 文件。MySQL 提供語法，用來將查詢結果導出到服務端本地目錄：select * from db1.t where a>900 into outfile '/server_tmp/t.csv'; 得到. csv 導出文件後，你就可以用下面的 load data 命令將數據導入到目標表 db2.t 中：load data infile '/server_tmp/t.csv' into table db2.t;

• 物理拷貝：在 MySQL 5.6 版本引入了可傳輸表空間 (transportable tablespace) 的方法，可以通過導出 + 導入表空間的方式，實現物理拷貝表的功能。

49、grant 和 flush privileges 語句

• grant 語句會同時修改數據表和內存，判斷權限的時候使用的內存數據，因此，規範使用是不需要加上 flush privileges 語句。

• flush privileges 語句本身會用數據表的數據重建一份內存權限數據，所以在權限數據可能存在不一致的情況下再使用。

50、要不要使用分區表？

• 分區並不是越細越好。實際上，單表或者單分區的數據一千萬行，只要沒有特別大的索引，對於現在的硬件能力來說都已經是小表了。

• 分區也不要提前預留太多，在使用之前預先創建即可。比如，如果是按月分區，每年年底時再把下一年度的 12 個新分區創建上即可。對於沒有數據的歷史分區，要及時的 drop 掉。

51、join 用法

• 使用 left join 左邊的表不一定是驅動表

• 如果需要 left join 的語義，就不能把被驅動表的字段放在 where 條件裏面做等值判斷或不等值判斷，必須都寫在 on 裏面

• 標準的 group by 語句，是需要在 select 部分加一個聚合函數，比如 select a,count(*) from t group by a order by null;

52、MySQL 有哪些自增 ID？各自場景是什麼？

• 表的自增 ID 達到上限之後，在申請值不會變化，進而導致聯繫插入數據的時候報主鍵衝突錯誤。

• row_id 達到上限之後，歸 0 在重新遞增，如果出現相同的 row_id 後寫的數據會覆蓋之前的數據。

• Xid 只需要不在同一個 binlog 文件出現重複值即可，理論上會出現重複值，但概率極小可忽略不計。

• InnoDB 的 max_trx_id 遞增值每次 MySQL 重啓會保存起來。

• Xid 是由 server 層維護的。InnoDB 內部使用 Xid，就是爲了能夠在 InnoDB 事務和 server 之間做關聯。但是，InnoDB 自己的 trx_id，是另外維護的。

• thread_id 是我們使用中最常見的，而且也是處理得最好的一個自增 id 邏輯了。使用了 insert_unique 算法

53、Xid 在 MySQL 內部是怎麼生成的呢？

MySQL 內部維護了一個全局變量 global_query_id，每次執行語句（包括 select 語句）的時候將它賦值給 Query_id，然後給這個變量加 1。如果當前語句是這個事務執行的第一條語句，那麼 MySQL 還會同時把 Query_id 賦值給這個事務的 Xid。

而 global_query_id 是一個純內存變量，重啓之後就清零了。所以你就知道了，在同一個數據庫實例中，不同事務的 Xid 也是有可能相同的。但是 MySQL 重啓之後會重新生成新的 binlog 文件，這就保證了，同一個 binlog 文件裏，Xid 一定是惟一的。

鎖相關

54、說一下 MySQL 的鎖

• MySQL 在 server 層 和 存儲引擎層 都運用了大量的鎖

• MySQL server 層需要講兩種鎖，第一種是 MDL(metadata lock) 元數據鎖，第二種則 Table Lock 表鎖。

• MDL 又名元數據鎖，那麼什麼是元數據呢，任何描述數據庫的內容就是元數據，比如我們的表結構、庫結構等都是元數據。那爲什麼需要 MDL 呢？

• 主要解決兩個問題：事務隔離問題；數據複製問題

• InnoDB 有五種表級鎖：IS（意向讀鎖）；IX（意向寫鎖）；S（讀）；X（寫）；AUTO-INC

• 在對錶進行 select/insert/delete/update 語句時候不會加表級鎖

• IS 和 IX 的作用是爲了判斷表中是否有已經被加鎖的記錄

• 自增主鍵的保障就是有 AUTO-INC 鎖，是語句級別的：爲表的某個列添加 AUTO_INCREMENT 屬性，之後在插⼊記錄時，可以不指定該列的值，系統會⾃動爲它賦上單調遞增的值。

• InnoDB 4 種行級鎖

• RecordLock：記錄鎖

• GapLock：間隙鎖解決幻讀；前一次查詢不存在的東西在下一次查詢出現了，其實就是事務 A 中的兩次查詢之間事務 B 執行插入操作被事務 A 感知了

• Next-KeyLock：鎖住某條記錄又想阻止其它事務在改記錄前面的間隙插入新紀錄

• InsertIntentionLock：插入意向鎖; 如果插入到同一行間隙中的多個事務未插入到間隙內的同一位置則無須等待

• 行鎖和表鎖的抉擇

• 全表掃描用行級鎖

55、什麼是幻讀？

值在同一個事務中，存在前後兩次查詢同一個範圍的數據，第二次看到了第一次沒有查詢到的數據。

幻讀出現的場景：

• 事務的隔離級別是可重複讀，且是當前讀。

• 幻讀指新插入的行。

幻讀帶來的問題：

• 對行鎖語義的破壞

• 破壞了數據一致性

解決：

• 加間隙鎖，鎖住行與行之間的間隙，阻塞新插入的操作。

• 帶來的問題：降低併發度，可能導致死鎖。

其它爲什麼系列

56、爲什麼 MySQL 會抖一下？

• 髒頁會被後臺線程自動 flush，也會由於數據頁淘汰而觸發 flush，而刷髒頁的過程由於會佔用資源，可能會讓你的更新和查詢語句的響應時間長一些。

57、爲什麼刪除了表，表文件的大小還是沒變？

• 數據項刪除之後 InnoDB 某個頁 page A 會被標記爲可複用。

• delete 命令把整個表的數據刪除，結果就是，所有的數據頁都會被標記爲可複用。但是磁盤上，文件不會變小。

• 經過大量增刪改的表，都是可能是存在空洞的。這些空洞也佔空間所以，如果能夠把這些空洞去掉，就能達到收縮表空間的目的。

• 重建表，就可以達到這樣的目的。可以使用 alter table A engine=InnoDB 命令來重建表。

58、count(*) 實現方式以及各種 count 對比

• 對於 count(主鍵 id) 來說，InnoDB 引擎會遍歷整張表，把每一行的 id 值都取出來，返回給 server 層。server 層拿到 id 後，判斷是不可能爲空的，就按行累加。

• 對於 count(1) 來說，InnoDB 引擎遍歷整張表，但不取值。server 層對於返回的每一行，放一個數字 “1” 進去，判斷是不可能爲空的，按行累加。單看這兩個用法的差別的話，你能對比出來，count(1) 執行得要比 count(主鍵 id) 快。因爲從引擎返回 id 會涉及到解析數據行，以及拷貝字段值的操作。

• 對於 count(字段) 來說：如果這個 “字段” 是定義爲 not null 的話，一行行地從記錄裏面讀出這個字段，判斷不能爲 null，按行累加；如果這個 “字段” 定義允許爲 null，那麼執行的時候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。也就是前面的第一條原則，server 層要什麼字段，InnoDB 就返回什麼字段。

• 但是 count * 是例外，並不會把全部字段取出來，而是專門做了優化，不取值。count(*) 肯定不是 null，按行累加。

• 所以結論是：按照效率排序的話，count(字段)<count(主鍵 id)<count(1)≈count(※)，所以建議儘量使用 count(*)。

59、orderby 排序內部原理

• MySQL 會爲每個線程分配一個內存（sort-buffer）用於排序該內存大小爲 sort_buffer_size；

• 如果排序的數據量小於 sort_buffer_size，排序就會在內存中完成；

  內部排序分爲兩種

• 全字段排序：到索引樹上找到滿足條件的主鍵 ID 根據主鍵 ID 去取出數據放到 sort_buffer 然後進行快速排序

• rowid 排序：通過控制排序的行數據的長度來讓 sort_buffer 中儘可能多的存放數據

• 如果數據量很大，內存中無法存下這麼多，就會使用磁盤臨時文件來輔助排序，稱爲外部排序；

• 外部排序，MySQL 會分爲好幾份單獨的臨時文件來存放排序後的數據，一般是磁盤文件中進行歸併，然後將這些文件合併成一個大文件；

60、如何高效的使用 MySQL 顯式隨機消息

• 隨機取出 Y1,Y2,Y3 之後，算出 Ymax,Ymin

• 得到 id 集後算出 Y1、Y2、Y3 對應的三個 id 最後 select * from t where id in (id1, id2, id3) 這樣掃描的行數應該是 C+Ymax+3

  mysql> select count(*) into @C from t;
  set @Y1 = floor(@C * rand());
  set @Y2 = floor(@C * rand());
  set @Y3 = floor(@C * rand());
  Ymax = max(Y1,Y2,Y3)
  Ymin = min(Y1,Y2,Y3)
  select id from t limit Ymin，(Ymax - Ymin)

參考：

• 極客時間《MySQL 實戰 45 講》

• https://www.nowcoder.com/discuss/744934?type=1&order=0&pos=25&page=1&ncTraceId=&channel=-1&source_id=discuss_tag_nctrack

今天的嘮嗑就到這裏了。

我是小賀，我們下期再見。

你好，我是 herongwei，一個精神小夥 & 鵝廠程序猿，熱愛編程，熱愛生活，熱愛分享，在平凡的人生中追求一點不平凡，歡迎關注，一起加油，點擊下方名片，瞭解更多。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/-SqqKmhZcOlQxM-rHiIpKg