基礎相關

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 是什么？

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

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

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

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

• 適用場景：

  - 對于寫多讀少的業務來說，頁面在寫完以后馬上被訪問到的概率比較小，此時 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

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

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

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

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

此后，將事務的狀態設置為 prepare ，說明已經準備好提交事務了。

等到 MySQL Server 層處理完事務以后，會將事務的狀態設置為?commit，也就是提交該事務。

在收到事務提交的請求以后，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)

總結

以上是生活随笔為你收集整理的数据库精选 60 道高频面试题（含答案），值得收藏的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。