在Java相關(guān)崗位的面試中，mysql類的問題經(jīng)常會被cue到，無論大家是想要進入開發(fā)崗位還是運維崗位，總會有那么相關(guān)的幾道題，所以，熟知mysql語句面試題，對于日后的面試有很大的幫助，大家可以跟著小編一起在來熟悉一下：

1.MySQL 怎么知道 binlog 是完整的?

一個事務(wù)的 binlog 是有完整格式的：

statement 格式的 binlog，最后會有 COMMIT;

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

2.什么是 WAL 技術(shù)，有什么優(yōu)點?

WAL，中文全稱是 Write-Ahead Logging，它的關(guān)鍵點就是日志先寫內(nèi)存，再寫磁盤。MySQL 執(zhí)行更新操作后，在真正把數(shù)據(jù)寫入到磁盤前，先記錄日志。

好處是不用每一次操作都實時把數(shù)據(jù)寫盤，就算 crash 后也可以通過redo log 恢復(fù)，所以能夠?qū)崿F(xiàn)快速響應(yīng) SQL 語句。

3.binlog 日志的三種格式

binlog 日志有三種格式

• Statement：基于SQL語句的復(fù)制((statement-based replication,SBR))
• Row：基于行的復(fù)制。(row-based replication,RBR)
• Mixed：混合模式復(fù)制。(mixed-based replication,MBR)

Statement格式

• 每一條會修改數(shù)據(jù)的 SQL 都會記錄在 binlog 中
• 優(yōu)點：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能。
• 缺點：由于記錄的只是執(zhí)行語句，為了這些語句能在備庫上正確運行，還必須記錄每條語句在執(zhí)行的時候的一些相關(guān)信息，以保證所有語句能在備庫得到和在主庫端執(zhí)行時候相同的結(jié)果。

Row格式

• 不記錄 SQL 語句上下文相關(guān)信息，僅保存哪條記錄被修改。
• 優(yōu)點：binlog 中可以不記錄執(zhí)行的 SQL 語句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)。不會出現(xiàn)某些特定情況下的存儲過程、或 function、或trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。
• 缺點:可能會產(chǎn)生大量的日志內(nèi)容。

Mixed格式

• 實際上就是 Statement 與 Row 的結(jié)合。一般的語句修改使用 statment 格式保存 binlog，如一些函數(shù)，statement 無法完成主從復(fù)制的操作，則采用 row 格式保存 binlog，MySQL 會根據(jù)執(zhí)行的每一條具體的 SQL 語句來區(qū)分對待記錄的日志形式。

4.redo log日志格式

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

• write pos 是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第 3 號文件末尾后就回到 0 號文件開頭。
• checkpoint 是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件。
• write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作。
• 如果 write pos 追上 checkpoint，表示“粉板”滿了，這時候不能再執(zhí)行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進一下。
• 有了 redo log，當(dāng)數(shù)據(jù)庫發(fā)生宕機重啟后，可通過 redo log將未落盤的數(shù)據(jù)(check point之后的數(shù)據(jù))恢復(fù)，保證已經(jīng)提交的事務(wù)記錄不會丟失，這種能力稱為crash-safe。

5.原本可以執(zhí)行得很快的 SQL 語句，執(zhí)行速度卻比預(yù)期的慢很多，原因是什么?如何解決?

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

• MySQL 數(shù)據(jù)庫本身被堵住了，比如：系統(tǒng)或網(wǎng)絡(luò)資源不夠。
• SQL 語句被堵住了，比如：表鎖，行鎖等，導(dǎo)致存儲引擎不執(zhí)行對應(yīng)的 SQL 語句。
• 確實是索引使用不當(dāng)，沒有走索引。
• 表中數(shù)據(jù)的特點導(dǎo)致的，走了索引，但回表次數(shù)龐大。

解決：

• 考慮采用 force index 強行選擇一個索引
• 考慮修改語句，引導(dǎo) MySQL 使用我們期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，語義的邏輯是相同的。
• 第三種方法是，在有些場景下，可以新建一個更合適的索引，來提供給優(yōu)化器做選擇，或刪掉誤用的索引。
• 如果確定是索引根本沒必要，可以考慮刪除索引。

6.InnoDB 數(shù)據(jù)頁結(jié)構(gòu)

一個數(shù)據(jù)頁大致劃分七個部分

• File Header：表示頁的一些通用信息，占固定的38字節(jié)。
• page Header：表示數(shù)據(jù)頁專有信息，占固定的56字節(jié)。
• inimum+Supermum：兩個虛擬的偽記錄，分別表示頁中的最小記錄和最大記錄，占固定的26字節(jié)。
• User Records：真正存儲我們插入的數(shù)據(jù)，大小不固定。
• Free Space：頁中尚未使用的部分，大小不固定。
• Page Directory：頁中某些記錄的相對位置，也就是各個槽對應(yīng)的記錄在頁面中的地址偏移量。
• File Trailer：用于檢驗頁是否完整，占固定大小 8 字節(jié)。

數(shù)據(jù)相關(guān)

7.MySQL 是如何保證數(shù)據(jù)不丟失的?

只要redolog 和 binlog 保證持久化磁盤就能確保MySQL異常重啟后回復(fù)數(shù)據(jù)

在恢復(fù)數(shù)據(jù)時，redolog 狀態(tài)為 commit 則說明 binlog 也成功，直接恢復(fù)數(shù)據(jù);如果 redolog 是 prepare，則需要查詢對應(yīng)的 binlog事務(wù)是否成功，決定是回滾還是執(zhí)行。

8.誤刪數(shù)據(jù)怎么辦?

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

• 權(quán)限控制與分配(數(shù)據(jù)庫和服務(wù)器權(quán)限)
• 制作操作規(guī)范
• 定期給開發(fā)進行培訓(xùn)
• 搭建延遲備庫
• 做好 SQL 審計，只要是對線上數(shù)據(jù)有更改操作的語句(DML和DDL)都需要進行審核
• 做好備份。備份的話又分為兩個點 (1)如果數(shù)據(jù)量比較大，用物理備份 xtrabackup。定期對數(shù)據(jù)庫進行全量備份，也可以做增量備份。(2)如果數(shù)據(jù)量較少，用 mysqldump 或者 mysqldumper。再利用 binlog 來恢復(fù)或者搭建主從的方式來恢復(fù)數(shù)據(jù)。定期備份binlog 文件也是很有必要的
• 如果發(fā)生了數(shù)據(jù)刪除的操作，又可以從以下幾個點來恢復(fù):
• DML 誤操作語句造成數(shù)據(jù)不完整或者丟失。可以通過 flashback，美團的 myflash，也是一個不錯的工具，本質(zhì)都差不多
• 都是先解析 binlog event，然后在進行反轉(zhuǎn)。把 delete 反轉(zhuǎn)為insert，insert 反轉(zhuǎn)為 delete，update前后 image 對調(diào)。
• 所以必須設(shè)置binlog_format=row 和 binlog_row_image=full，切記恢復(fù)數(shù)據(jù)的時候，應(yīng)該先恢復(fù)到臨時的實例，然后在恢復(fù)回主庫上。
• DDL語句誤操作(truncate和drop)，由于DDL語句不管 binlog_format 是 row 還是 statement ，在 binlog 里都只記錄語句，不記錄 image 所以恢復(fù)起來相對要麻煩得多。
• 只能通過全量備份+應(yīng)用 binlog 的方式來恢復(fù)數(shù)據(jù)。一旦數(shù)據(jù)量比較大，那么恢復(fù)時間就特別長
• rm 刪除：使用備份跨機房，或者最好是跨城市保存。

以上就是“mysql語句面試題，提高面試幾率”，你能回答上來嗎?如果想要了解更多的Java面試題相關(guān)內(nèi)容，可以關(guān)注動力節(jié)點Java官網(wǎng)。