最近后臺好多小伙伴發私信咨詢阿Q，問馬上就是金九銀十的面試黃金期了，該如何在三個月內突擊一下，以便更好地通過面試呢？

阿Q的想法就是需要對自己掌握的知識進行歸納整理，系統的、分類的去復習相關的知識點。這樣也能更好的把自己陌生的內容框出來，在暴漏自己短板的同時，更針對性的做準備。

為了節省大家的時間，阿Q決定幫大家先將 MySQL 面試相關的知識進行匯總整理，一改往日知識點東一棒槌西一榔頭的問題。那么，你準備好迎接 MySQL 連環炮了嘛？

1、三大范式

2、DML 語句和 DDL 語句區別

3、主鍵和外鍵的區別

4、drop、delete、truncate 區別

5、基礎架構

6、MyISAM 和 InnoDB 有什么區別？

7、推薦自增id作為主鍵問題

8、為什么 MySQL 的自增主鍵不連續

9、redo log 是做什么的?

10、redo log 的刷盤時機

11、redo log 是怎么記錄日志的

12、什么是 binlog

13、binlog 記錄格式

14、binlog 寫入機制

15、redolog 和 binlog 的區別是什么

16、兩階段提交

17、什么是 undo log.

18、什么是 relaylog

19、索引

20、Hash 索引

21、B樹和B+ 樹

22、主鍵索引

23、二級索引

24、聚簇索引與非聚簇索引

25、回表

26、覆蓋索引和聯合索引

27、最左前綴匹配原則

28、索引下推

29、隱式轉換

30、普通索引和唯一索引該怎么選擇?

31、避免索引失效

32、建立索引的規則

33、事務極其特性

34、并發事務帶來的問題

35、事務的隔離級別

36、MVCC

37、Mysql 中的鎖

38、查詢語句執行過程

39、更新語句執行過程

40、sql 優化

41、主從同步數據

42、主從延遲要怎么解決

43、為什么不要使用長事務

1、三大范式

1NF(第一范式)：屬性（對應于表中的字段）不能再被分割，也就是這個字段只能是一個值，不能再分為多個其他的字段了。1NF 是所有關系型數據庫的最基本要求 ，也就是說關系型數據庫中創建的表一定滿足第一范式。

2NF(第二范式)：2NF 要求數據庫表中的每個實例或行必須可以被惟一地區分，2NF 在 1NF 的基礎上增加了一個列，這個列稱為主鍵，非主屬性都依賴于主鍵。

3NF(第三范式)：3NF 在 2NF 的基礎之上，要求每列都和主鍵列直接相關，而不是間接相關，即不存在其他表的非主鍵信息。

在開發過程中，并不一定要滿足三大范式，有時候為了提高查詢效率，可以在表中冗余其他表的字段。

2、DML 語句和 DDL 語句區別

• DML 是數據庫操作語言（Data Manipulation Language）的縮寫，是指對數據庫中表記錄的操作，主要包括表記錄的插入、更新、刪除和查詢，是開發人員日常使用最頻繁的操作。
• DDL （Data Definition Language）是數據定義語言的縮寫，簡單來說，就是對數據庫內部的對象進行創建、刪除、修改的操作語言。它和 DML 語言的最大區別是 DML 只是對表內部數據的操作，而不涉及到表的定義、結構的修改，更不會涉及到其他對象。DDL 語句更多的被數據庫管理員（DBA）所使用，一般的開發人員很少使用。

3、主鍵和外鍵的區別

• 主鍵：用于唯一標識一行數據，不能有重復，不允許為空，且一個表只能有一個主鍵；
• 外鍵：用來和其他表建立聯系，外鍵是另一表的主鍵，外鍵是可以有重復的，可以是空值。一個表可以有多個外鍵；

4、drop、delete、truncate 區別

（1）用法不同

• drop(丟棄數據): drop table 表名 ，直接將表結構都刪除掉，在刪除表的時候使用。
• truncate (清空數據) : truncate table 表名 ，只刪除表中的數據，再插入數據的時候自增長 id 又從 1 開始，在清空表中數據的時候使用。
• delete（刪除數據） : delete from 表名 where 列名=值，刪除某一行的數據，如果不加 where 子句和truncate table 表名作用類似。

（2）屬于不同的數據庫語言

• truncate 和 drop 屬于 DDL(數據定義語言)語句，操作立即生效，原數據不放到 rollback segment 中，不能回滾，操作不觸發 trigger。
• delete 語句是 DML (數據庫操作語言)語句，這個操作會放到 rollback segment 中，事務提交之后才生效。

（3）執行速度不同

• delete命令執行的時候會產生數據庫的binlog日志，而日志記錄是需要消耗時間的，但是也有個好處方便數據回滾恢復。
• truncate命令執行的時候不會產生數據庫日志，因此比delete要快。除此之外，還會把表的自增值重置和索引恢復到初始大小等。
• drop命令會把表占用的空間全部釋放掉。

一般來說：drop > truncate > delete

5、基礎架構

下圖是 MySQL 的一個簡要架構圖，從下圖你可以很清晰的看到客戶端的一條 SQL 語句在 MySQL 內部是如何執行的。

• 連接器： 身份認證和權限相關(登錄 MySQL 的時候)。
• 查詢緩存： 執行查詢語句的時候，會先查詢緩存（MySQL 8.0 版本后移除，因為這個功能不太實用）。
• 分析器： 沒有命中緩存的話，SQL 語句就會經過分析器，分析器說白了就是要先看你的 SQL 語句要干嘛，再檢查你的 SQL 語句語法是否正確。
• 優化器： 按照 MySQL 認為最優的方案去執行。
• 執行器： 執行語句，然后從存儲引擎返回數據。執行語句之前會先判斷是否有權限，如果沒有權限的話，就會報錯。
• 插件式存儲引擎：主要負責數據的存儲和讀取，采用的是插件式架構，支持 InnoDB、MyISAM、Memory 等多種存儲引擎。

6、MyISAM 和 InnoDB 有什么區別？

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默認存儲引擎，MySQL 5.5 版本之后，InnoDB 是 MySQL 的默認存儲引擎。

（1）是否支持行級鎖

MyISAM 只有表級鎖，而 InnoDB 支持行級鎖和表級鎖，默認為行級鎖。

具體的鎖詳情請參考阿Q的此篇文章：面試必備常見存儲引擎與鎖的分類，請查收

（2）是否支持事務

MyISAM 不提供事務支持，InnoDB 提供事務支持，實現了 SQL 標準定義的四個隔離級別，具有提交和回滾事務的能力。

InnoDB 默認使用的 REPEATABLE-READ（可重讀）隔離級別是可以解決幻讀問題發生的（基于 MVCC 和 Next-Key Lock）。

關于 MySQL 事務以及解決幻讀的詳細介紹，可以看看阿Q寫的這篇文章：InnoDB 解決幻讀的方案--LBCC&MVCC

（3）是否支持外鍵

MyISAM 不支持，而 InnoDB 支持。

（4）是否支持數據庫異常崩潰后的安全恢復

MyISAM 不支持，而 InnoDB 支持。使用 InnoDB 的數據庫在異常崩潰后，數據庫重新啟動的時候會保證數據庫恢復到崩潰前的狀態。這個恢復的過程依賴于 redo log 。

（5）是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

（6）索引實現

雖然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作為索引結構，但是兩者的實現方式不太一樣。

• InnoDB 引擎中，其數據文件本身就是索引文件。其表數據文件本身就是按 B+Tree 組織的一個索引結構，樹的葉子節點 data 域保存了完整的數據記錄。
• MyISAM 索引文件和數據文件是分離的，索引保存的是數據文件的指針。

（7）性能差別

InnoDB 的性能比 MyISAM 更強大，不管是在讀寫混合模式下還是只讀模式下，隨著 CPU 核數的增加，InnoDB 的讀寫能力呈線性增長。MyISAM 因為讀寫不能并發，它的處理能力跟核數沒關系。

InnoDB 和 MyISAM 性能對比

7、推薦自增id作為主鍵問題

• 普通索引的 B+ 樹上存放的是主鍵索引的值，如果該值較大，會「導致普通索引的存儲空間較大」
• 使用自增 id 做主鍵索引新插入數據只要放在該頁的最尾端就可以，直接「按照順序插入」，不用刻意維護
• 頁分裂容易維護，當插入數據的當前頁快滿時，會發生頁分裂的現象，如果主鍵索引不為自增 id，那么數據就可能從頁的中間插入，頁的數據會頻繁的變動，「導致頁分裂維護成本較高」

8、為什么 MySQL 的自增主鍵不連續

• 在MySQL 5.7及之前的版本，自增值保存在內存里，并沒有持久化；
• 唯一鍵沖突：插入數據時先將自增主鍵+1，然后插入數據時唯一鍵沖突，插入數據失敗，但是未將自增主鍵改回；
• 事務回滾：和唯一鍵沖突類似，回滾操作時自增值也不回退，事實上，這么做的主要原因是為了提高性能。

9、redo log 是做什么的?

redo log（重做日志）是InnoDB存儲引擎獨有的，它讓MySQL擁有了崩潰恢復能力。

比如 MySQL 實例掛了或宕機了，重啟時，InnoDB存儲引擎會使用redo log恢復數據，保證數據的持久性與完整性。

更新表數據的時候，如果發現 Buffer Pool 里存在要更新的數據，就直接在 Buffer Pool 里更新。然后會把“在某個數據頁上做了什么修改”記錄到重做日志緩存（redo log buffer）里，接著刷盤到 redo log 文件里。

10、redo log 的刷盤時機

• 紅色部分為 redo log buffer 屬于內存
• 黃色部分為 page cache ，此時已經寫入磁盤了，但是未進行持久化
• 綠色部分是硬盤，已經完成持久化

InnoDB 存儲引擎為 redo log 的刷盤策略提供了 innodb_flush_log_at_trx_commit 參數，它支持三種策略

• 設置為0的時候，表示每次事務提交時不進行刷盤操作，只是保留在 redo log buffer中，mysql 崩潰會丟失1s的數據；
• 設置為1的時候，表示每次事務提交時都將進行刷盤操作（默認值），持久化到磁盤；
• 設置為2的時候，表示每次事務提交時都只把redo log buffer內容寫入page cache，OS宕機會丟失1s的數據，因為未進行持久化；

innodb_flush_log_at_trx_commit 參數默認為 1 ，也就是說當事務提交時會調用 fsync(同步操作) 對 redo log 進行刷盤。

另外 InnoDB 存儲引擎有一個后臺線程，每隔1秒，就會把 redo log buffer 中的內容寫到文件系統緩存（page cache），然后調用 fsync 刷盤。

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

11、redo log 是怎么記錄日志的

硬盤上存儲的 redo log 日志文件不只一個，而是以一個日志文件組的形式出現的，每個的redo日志文件大小都是一樣的。

比如可以配置為一組4個文件，每個文件的大小是 1GB，整個 redo log 日志文件組可以記錄4G的內容。

它采用的是環形數組形式，從頭開始寫，寫到末尾又回到頭循環寫，如下圖所示。

所以，如果數據寫滿了但是還沒有來得及將數據真正的刷入磁盤當中，那么就會發生「內存抖動」現象，從肉眼的角度來觀察會發現 mysql 會宕機一會兒，此時就是正在刷盤了。

12、什么是 binlog

binlog 是歸檔日志，屬于 Server 層的日志，是一個二進制格式的文件，記錄內容是語句的原始邏輯，類似于“給 ID=2 這一行的 c 字段加 1”。

不管用什么存儲引擎，只要發生了表數據更新，都會產生 binlog 日志。它的主要作用就是數據備份、主從復制。

binlog會記錄所有涉及更新數據的邏輯操作，屬于邏輯日志，并且是順序寫。

13、binlog 記錄格式

binlog 日志有三種格式，可以通過binlog_format參數指定。

• statement ：記錄的內容是SQL語句原文，存在數據一致性問題；
• row：記錄包含操作的具體數據，能保證同步數據的一致性；
• mixed：記錄的內容是前兩者的混合，MySQL會判斷這條SQL語句是否可能引起數據不一致：如果是，就用row格式，否則就用statement格式。

14、binlog 寫入機制

事務執行過程中，先把日志寫到binlog cache，事務提交的時候，再把binlog cache寫到binlog文件中。

因為一個事務的binlog不能被拆開，無論這個事務多大，也要確保一次性寫入，所以系統會給每個線程分配一個塊內存作為binlog cache。

我們可以通過binlog_cache_size參數控制單個線程 binlog cache 大小，如果存儲內容超過了這個參數，就要暫存到磁盤（Swap）。

binlog 也提供了 sync_binlog 參數來控制寫入 page cache 和磁盤的時機：

• 0：每次提交事務都只寫入到文件系統的 page cache，由系統自行判斷什么時候執行fsync，機器宕機，page cache里面的 binlog 會丟失。
• 1：每次提交事務都會執行fsync，就如同 redo log 日志刷盤流程 一樣。
• N(N>1)：每次提交事務都寫入到文件系統的 page cache，但累積N個事務后才fsync。如果機器宕機，會丟失最近N個事務的binlog日志。

15、redolog 和 binlog 的區別是什么

• redolog 是 Innodb 獨有的日志，而 binlog 是 server 層的，所有的存儲引擎都有使用到；
• redolog 記錄了具體的數值，對某個頁做了什么修改，binlog 記錄的操作內容；
• binlog 大小達到上限或者 flush log 會生成一個新的文件，而 redolog 有固定大小只能循環利用；
• binlog 日志沒有 crash-safe 的能力，只能用于歸檔，而 redo log 有 crash-safe 能力；
• redo log 在事務執行過程中可以不斷寫入（刷盤設置為1，后臺線程1s執行一次或者 redo log buffer 占用的空間即將達到 innodb_log_buffer_size 一半的時候），而 binlog 只有在提交事務時才寫入文件緩存系統；

16、兩階段提交

假設執行 sql 過程中寫完 redo log 日志后，binlog 日志寫期間發生了異常，會出現什么情況呢？

由于 binlog 沒寫完就異常，這時候 binlog 里面沒有對應的修改記錄。因此，之后用 binlog 日志恢復數據時，就會少這一次更新，最終數據不一致。

為了解決兩份日志之間的邏輯一致問題，InnoDB 存儲引擎使用兩階段提交方案。

將 redo log 的寫入拆成了兩個步驟 prepare 和 commit，這就是兩階段提交。使用兩階段提交后，寫入 binlog 時發生異常也不會有影響，因為 MySQL 根據 redo log日志恢復數據時，發現 redo log 還處于 prepare 階段，并且沒有對應 binlog 日志，就會回滾該事務。

再看一個場景，redo log 設置 commit 階段發生異常，那會不會回滾事務呢？

并不會回滾事務，雖然 redo log 是處于 prepare 階段，但是能通過事務id找到對應的 binlog 日志，所以 MySQL 認為是完整的，就會提交事務恢復數據。

17、什么是 undo log.

我們知道如果想要保證事務的原子性，就需要在異常發生時，對已經執行的操作（INSERT、DELETE、UPDATE）進行回滾，在 MySQL 中，恢復機制是通過回滾日志（undo log） 實現的，所有事務進行的修改都會先記錄到這個回滾日志中，然后再執行相關的操作。

每次對記錄進行改動都會記錄一條 undo log，每條 undo log 也都有一個DB_ROLL_PTR屬性，可以將這些 undo log 都連起來，串成一個鏈表，形成版本鏈。

版本鏈的頭節點就是當前記錄最新的值。

18、什么是 relaylog

relaylog 是中繼日志，在主從同步的時候使用到，它是一個中介臨時的日志文件，用于存儲從 master 節點同步過來的 binlog 日志內容。

master 主節點的 binlog 傳到 slave 從節點后，被寫入 relay log 里，從節點的 slave sql 線程從 relaylog 里讀取日志然后應用到 slave 從節點本地。

從服務器 I/O 線程將主服務器的二進制日志讀取過來記錄到從服務器本地文件，然后 SQL 線程會讀取 relay-log 日志的內容并應用到從服務器，從而使從服務器和主服務器的數據保持一致。

19、索引

索引其實是一種數據結構，能夠幫助我們快速的檢索數據庫中的數據。

索引的作用就相當于書的目錄。打個比方: 我們在查字典的時候，如果沒有目錄，那我們就只能一頁一頁的去找我們需要查的那個字，速度很慢。如果有目錄了，我們只需要先去目錄里查找字的位置，然后直接翻到那一頁就行了。

20、Hash 索引

哈希表是鍵值對的集合，通過鍵(key)即可快速取出對應的值(value)，因此哈希表可以快速檢索數據（接近 O（1））。

但是！哈希算法有個 Hash 沖突問題，也就是說多個不同的 key 最后得到的 index 相同。通常情況下，我們常用的解決辦法是 鏈地址法。

鏈地址法就是將哈希沖突數據存放在鏈表中。就比如 JDK1.8 之前 HashMap 就是通過鏈地址法來解決哈希沖突的。不過，JDK1.8 以后 HashMap 為了減少鏈表過長的時候搜索時間過長引入了紅黑樹。

為了減少 Hash 沖突的發生，一個好的哈希函數應該“均勻地”將數據分布在整個可能的哈希值集合中。

既然哈希表這么快，為什么 MySQL 沒有使用其作為索引的數據結構呢？ 主要是因為 Hash 索引不支持順序和范圍查詢。假如我們要對表中的數據進行排序或者進行范圍查詢，那 Hash 索引可就不行了，并且每次 IO 只能取一個。

21、B樹和B+ 樹

• B 樹的所有節點既存放鍵(key) 也存放數據(data)，而 B+樹只有葉子節點存放 key 和 data，其他內節點只存放 key。
• B 樹的葉子節點都是獨立的；B+樹的葉子節點有一條引用鏈指向與它相鄰的葉子節點。
• B 樹的檢索的過程相當于對范圍內的每個節點的關鍵字做二分查找，可能還沒有到達葉子節點，檢索就結束了。而 B+樹的檢索效率就很穩定了，任何查找都是從根節點到葉子節點的過程，葉子節點的順序檢索很明顯。

22、主鍵索引

數據表的主鍵列使用的就是主鍵索引，一種特殊的唯一索引。

在 MySQL 的 InnoDB 的表中，當沒有顯示的指定表的主鍵時，InnoDB 會自動先檢查表中是否有唯一索引且不允許存在 null 值的字段，如果有，則選擇該字段為默認的主鍵，否則 InnoDB 將會自動創建一個 6Byte 的自增主鍵。

23、二級索引

二級索引又稱為輔助索引，是因為二級索引的葉子節點存儲的數據是主鍵。也就是說，通過二級索引，可以定位主鍵的位置。

唯一索引，普通索引，前綴索引等索引屬于二級索引。

• 唯一索引(Unique Key) ：唯一索引也是一種約束。索引列的值必須唯一，但允許有空值；如果是組合索引，則列值的組合必須唯一。一張表允許創建多個唯一索引。建立唯一索引的目的大部分時候都是為了該屬性列的數據的唯一性，而不是為了查詢效率。
• 普通索引(Index) ：普通索引的唯一作用就是為了快速查詢數據，一張表允許創建多個普通索引，并允許數據重復和 NULL。
• 前綴索引(Prefix) ：前綴索引只適用于字符串類型的數據。前綴索引是對文本的前幾個字符創建索引，相比普通索引建立的數據更小， 因為只取前幾個字符。
• 組合索引：指多個字段上創建的索引，只有在查詢條件中使用了創建索引時的第一個字段，索引才會被使用。使用組合索引時遵循最左前綴集合（后文介紹）；
• 全文索引(Full Text) ：全文索引主要是為了檢索大文本數據中的關鍵字的信息，是目前搜索引擎數據庫使用的一種技術。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

MySQL 中的全文索引，有兩個變量，最小搜索長度和最大搜索長度，對于長度小于最小搜索長度和大于最大搜索長度的詞語，都不會被索引。

24、聚簇索引與非聚簇索引

聚簇索引即索引結構和數據一起存放的索引，并不是一種單獨的索引類型。InnoDB 的主鍵索引的葉子節點中存放的就是數據行，所以它屬于聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd 文件就包含了該表的索引和數據，對于 InnoDB 引擎表來說，該表的索引(B+樹)的每個非葉子節點存儲索引，葉子節點存儲索引和索引對應的數據。

非聚簇索引即索引結構和數據分開存放的索引，并不是一種單獨的索引類型。二級索引(輔助索引)就屬于非聚簇索引。MySQL 的 MyISAM 引擎，不管主鍵還是非主鍵，使用的都是非聚簇索引。

輔助索引是我們人為創建的索引，它的葉子節點中存放的是主鍵，當我們通過輔助索引查找到主鍵之后，再通過查找的主鍵去回表查找主鍵索引。

25、回表

回表就是先通過數據庫索引掃描出該索引樹中數據所在的行，取到主鍵 id，再通過主鍵 id 取出主鍵索引數中的數據，即基于非主鍵索引的查詢需要多掃描一棵索引樹。

26、覆蓋索引和聯合索引

如果一個索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。指的是通過索引就能查詢到我們所需要的數據，而不需要根據索引再去查詢數據表中的數據（ 回表），這樣就減少了數據庫的 io 操作，提高查詢效率。

使用表中的多個字段創建索引，就是聯合索引，也叫組合索引或復合索引。

27、最左前綴匹配原則

最左前綴匹配原則指的是在使用聯合索引時，MySQL 會根據聯合索引中的字段順序，從左到右依次到查詢條件中去匹配，如果查詢條件中存在與聯合索引中最左側字段相匹配的字段，則就會使用該字段過濾一批數據，直至聯合索引中全部字段匹配完成，或者在執行過程中遇到范圍查詢，如 >、<、between 和 以%開頭的like查詢 等條件，才會停止匹配。

所以，我們在使用聯合索引時，可以將區分度高的字段放在最左邊，這也可以過濾更多數據。

28、索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一項索引優化功能，可以在非聚簇索引遍歷過程中，對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表次數。

29、隱式轉換

當操作符與不同類型的操作數一起使用時，會發生類型轉換以使操作數兼容。某些轉換是隱式發生的。例如，MySQL 會根據需要自動將字符串轉換為數字，反之亦然。以下規則描述了比較操作的轉換方式：

1. 兩個參數至少有一個是NULL時，比較的結果也是NULL，特殊的情況是使用<=>對兩個NULL做比較時會返回1，這兩種情況都不需要做類型轉換；
2. 兩個參數都是字符串，會按照字符串來比較，不做類型轉換；
3. 兩個參數都是整數，按照整數來比較，不做類型轉換；
4. 十六進制的值和非數字做比較時，會被當做二進制串；
5. 有一個參數是TIMESTAMP或DATETIME，并且另外一個參數是常量，常量會被轉換為timestamp；
6. 有一個參數是decimal類型，如果另外一個參數是decimal或者整數，會將整數轉換為decimal后進行比較，如果另外一個參數是浮點數，則會把decimal轉換為浮點數進行比較；
7. 所有其他情況下，兩個參數都會被轉換為浮點數再進行比較；

30、普通索引和唯一索引該怎么選擇?

• 查詢

當普通索引為條件時查詢到數據會一直掃描，直到掃完整張表；

當唯一索引為查詢條件時，查到該數據會直接返回，不會繼續掃表；

• 更新

• 普通索引會直接將操作更新到 change buffer 中，然后結束

• 唯一索引需要判斷數據是否沖突

所以唯一索引更加適合查詢的場景，普通索引更適合插入的場景。

31、避免索引失效

索引失效也是慢查詢的主要原因之一，常見的導致索引失效的情況有下面這些：

• 使用 SELECT * 進行查詢;
• 創建了組合索引，但查詢條件未準守最左匹配原則;
• 在索引列上進行計算、函數、類型轉換等操作;
• 以 % 開頭的 LIKE 查詢比如 like '%abc';
• 查詢條件中使用 or，且 or 的前后條件中有一個列沒有索引，涉及的索引都不會被使用到
• match() 函數中的指定的列必須與全文索引中指定的列完全相同，否則會報錯，無法使用全文索引。
• 全文索引時要注意搜索長度會導致索引失效

32、建立索引的規則

• 不為 NULL 的字段 ：索引字段的數據應該盡量不為 NULL，因為對于數據為 NULL 的字段，數據庫較難優化。如果字段頻繁被查詢，但又避免不了為 NULL，建議使用 0,1,true,false 這樣語義較為清晰的短值或短字符作為替代。
• 被頻繁查詢的字段 ：我們創建索引的字段應該是查詢操作非常頻繁的字段。
• 被作為條件查詢的字段 ：被作為 WHERE 條件查詢的字段，應該被考慮建立索引。
• 頻繁需要排序的字段 ：索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間。
• 被經常頻繁用于連接的字段 ：經常用于連接的字段可能是一些外鍵列，對于外鍵列并不一定要建立外鍵，只是說該列涉及到表與表的關系。對于頻繁被連接查詢的字段可以考慮建立索引，提高多表連接查詢的效率。
• 被頻繁更新的字段應該慎重建立索引；
• 盡可能的考慮建立聯合索引而不是單列索引；
• 考慮在字符串類型的字段上使用前綴索引代替普通索引；
• 刪除長期未使用的索引；

33、事務極其特性

一個事情由n個單元組成，這n個單元在執行過程中，要么同時成功，要么同時失敗，這就把n個單元放在了一個事務之中。舉個簡單的例子：在不考慮試題正確與否的前提下，一張試卷由多個題目構成，當你答完題交給老師的時候是將一整張試卷交給老師，而不是將每道題單獨交給老師，在這里試卷就可以理解成一個事務。

事務的特性：

• A：原子性（Atomicity），原子性是指事務是一個不可分割的工作單位，事務中的操作，要么都發生，要么都不發生。
• C：一致性（Consistency），在一個事務中，事務前后數據的完整性必須保持一致。
• I：隔離性（Isolation），存在于多個事務中，事務的隔離性是指多個用戶并發訪問數據庫時，一個用戶的事務不能被其它用戶的事務所干擾，多個并發事務之間數據要相互隔離。
• D：持久性（Durability），持久性是指一個事務一旦被提交，它對數據庫中數據的改變就是永久性的，接下來即使數據庫發生故障也不應該對其有任何影響。

34、并發事務帶來的問題

• 臟讀（Dirty read）：B事務讀取到了A事務尚未提交的數據；
• 丟失修改（Lost to modify）：在一個事務讀取一個數據時，另外一個事務也訪問了該數據，那么在第一個事務中修改了這個數據后，第二個事務也修改了這個數據。這樣第一個事務內的修改結果就被丟失，因此稱為丟失修改。
• 不可重復讀（Unrepeatable read）：B事務讀到了A事務已經提交的數據，即B事務在A事務提交之前和提交之后讀取到的數據內容不一致（AB事務操作的是同一條數據）；
• 幻讀/虛讀：B事務讀到了A事務已經提交的數據，即A事務執行插入操作，B事務在A事務前后讀到的數據數量不一致。

35、事務的隔離級別

為了解決以上隔離性引發的并發問題，數據庫提供了事務的隔離機制。

• read uncommitted（讀未提交）: 一個事務還沒提交時，它做的變更就能被別的事務看到，讀取尚未提交的數據，哪個問題都不能解決；
• read committed（讀已提交）：一個事務提交之后，它做的變更才會被其他事務看到，讀取已經提交的數據，可以解決臟讀 ---- oracle默認的；
• repeatable read（可重復讀）：一個事務執行過程中看到的數據，總是跟這個事務在啟動時看到的數據是一致的，可以解決臟讀和不可重復讀 ---mysql默認的；
• serializable（串行化）：顧名思義是對于同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”。當出現讀寫鎖沖突的時候，后訪問的事務必須等前一個事務執行完成，才能繼續執行。可以解決臟讀、不可重復讀和虛讀---相當于鎖表。

雖然 serializable 級別可以解決所有的數據庫并發問題，但是它會在讀取的每一行數據上都加鎖，這就可能導致大量的超時和鎖競爭問題，從而導致效率下降。所以我們在實際應用中也很少使用 serializable，只有在非常需要確保數據的一致性而且可以接受沒有并發的情況下，才考慮采用該級別。

36、MVCC

鎖的粒度過大會導致性能的下降， MySQL 的 InnoDB 引擎下存在一種性能更優越的 MVCC 方法。

MVCC 是 Multi-Version Concurremt Control 的簡稱，意思是基于多版本的并發控制協議，通過版本號避免同一數據在不同事務間的競爭。它主要是為了提高數據庫的并發讀寫性能，不用加鎖就能讓多個事務并發讀寫。

MVCC 的實現依賴于隱藏列、Undo log、 Read View 。

從上面對 SQL 標準定義了四個隔離級別的介紹可以看出，標準的 SQL 隔離級別定義里，REPEATABLE-READ(可重復讀)是不可以防止幻讀的。

但是 InnoDB 實現的 REPEATABLE-READ 隔離級別其實是可以解決幻讀問題發生的，主要有下面兩種情況：

• 快照讀 ：由 MVCC 機制來保證不出現幻讀。
• 當前讀 ：使用 Next-Key Lock（臨鍵鎖） 進行加鎖來保證不出現幻讀，Next-Key Lock 是行鎖（Record Lock）和間隙鎖（Gap Lock）的結合，行鎖只能鎖住已經存在的行，為了避免插入新行，需要依賴間隙鎖。

InnoDB 存儲引擎在分布式事務的情況下一般會用到 SERIALIZABLE 隔離級別。

關于 MySQL 事務以及解決幻讀的詳細介紹，可以看看阿Q寫的這篇文章：InnoDB 解決幻讀的方案--LBCC&MVCC

37、Mysql 中的鎖

具體的鎖詳情請參考阿Q的此篇文章：面試必備常見存儲引擎與鎖的分類，請查收

38、查詢語句執行過程

select * from tb_student  s where s.age='18' and s.name=' 張三 ';

• 先檢查該語句是否有權限，如果沒有權限，直接返回錯誤信息，如果有權限，在 MySQL8.0 版本以前，會先查詢緩存，以這條 SQL 語句為 key 在內存中查詢是否有結果，如果有直接緩存，如果沒有，執行下一步。
• 通過分析器進行詞法分析，提取 SQL 語句的關鍵元素，比如提取上面這個語句是查詢 select，提取需要查詢的表名為 tb_student，需要查詢所有的列，查詢條件是這個表的 id='1'。然后判斷這個 SQL 語句是否有語法錯誤，比如關鍵詞是否正確等等，如果檢查沒問題就執行下一步。
• 接下來就是優化器進行確定執行方案，上面的 SQL 語句，可以有兩種執行方案：

• a.先查詢學生表中姓名為“張三”的學生，然后判斷是否年齡是 18。
• b.先找出學生中年齡 18 歲的學生，然后再查詢姓名為“張三”的學生。那么優化器根據自己的優化算法進行選擇執行效率最好的一個方案（優化器認為，有時候不一定最好）。那么確認了執行計劃后就準備開始執行了。

• 進行權限校驗，如果沒有權限就會返回錯誤信息，如果有權限就會調用數據庫引擎接口，返回引擎的執行結果。

查詢語句的執行流程如下：權限校驗（如果命中緩存）--->查詢緩存--->分析器--->優化器--->權限校驗--->執行器--->引擎

39、更新語句執行過程

update tb_student A set A.age='19' where A.name=' 張三 ';

這條語句基本上也會沿著上一個查詢的流程走，只不過執行更新的時候要記錄日志，這就會引入日志模塊了，MySQL 自帶的日志模塊是 binlog（歸檔日志） ，所有的存儲引擎都可以使用，我們常用的 InnoDB 引擎還自帶了一個日志模塊 redo log（重做日志），我們就以 InnoDB 模式下來探討這個語句的執行流程。

• 先查詢到張三這一條數據，如果有緩存，也是會用到緩存。
• 然后拿到查詢的語句把 age 改為 19，然后調用引擎 API 接口寫入這一行數據，InnoDB 引擎把數據保存在內存中同時記錄 redo log，此時 redo log 進入 prepare 狀態，然后告訴執行器執行完成了隨時可以提交。
• 執行器收到通知后記錄 binlog，然后調用引擎接口，提交 redo log 為提交狀態。
• 更新完成。

更新語句執行流程如下：分析器---->權限校驗---->執行器--->引擎---redo log(prepare 狀態)---> binlog --->redo log(commit狀態)

40、sql 優化

1. 應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引；
2. 應盡量避免在 where 子句中使用以下語句，否則將導致引擎放棄使用索引而進行全表掃描；

對字段進行 null 值判斷，

使用!=或<>

or 來連接條件（使用union all代替）

in 和 not in 也要慎用

不要使用模糊查詢（可用全文索引）

減少表達式操作

函數操作

3. 任何地方都不要使用 select * from t ，用具體的字段列表代替“* ”，不要返回用不到的任何字段；
4. 一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要；
5. 很多時候用 exists 代替 in 是一個好的選擇；
6. 盡量減少多表聯合查詢；
7. 分頁優化；
8. 正確使用索引；

41、主從同步數據

• master 主庫將此次更新的事件類型寫入到主庫的 binlog 文件中
• master 創建 log dump 線程通知 slave 需要更新數據
• slave 向 master 節點發送請求，將該 binlog 文件內容存到本地的 relaylog 中
• slave 開啟 sql 線程讀取 relaylog 中的內容，將其中的內容在本地重新執行一遍，完成主從數據同步

同步策略：

• 全同步復制：主庫強制同步日志到從庫，等全部從庫執行完才返回客戶端，性能差；
• 半同步復制：主庫收到至少一個從庫確認就認為操作成功，從庫寫入日志成功返回 ack 確認；

42、主從延遲要怎么解決

• MySQL 5.6 版本以后，提供了一種并行復制的方式，通過將 SQL 線程轉換為多個 work 線程來進行重放
• 提高機器配置(王道)
• 在業務初期就選擇合適的分庫、分表策略，避免單表單庫過大帶來額外的復制壓力
• 避免長事務
• 避免讓數據庫進行各種大量運算
• 對于一些對延遲很敏感的業務直接使用主庫讀

43、為什么不要使用長事務

• 并發情況下，數據庫連接池容易被撐爆
• 容易造成大量的阻塞和鎖超時，長事務還占用鎖資源，也可能拖垮整個庫
• 執行時間長，容易造成主從延遲
• 回滾所需要的時間比較長，事務越長整個時間段內的事務也就越多
• undolog 日志越來越大，長事務意味著系統里面會存在很老的事務視圖。由于這些事務隨時可能訪問數據庫里面的任何數據，所以這個事務提交之前，數據庫里面它可能用到的回滾記錄都必須保留，這就會導致大量占用存儲空間。

好看的皮囊千篇一律，有趣的靈魂萬里挑一，讓我們在冷漠的城市里相互溫暖，我是阿Q，我們下期再見！