好看的玄幻小说,我欲封天耳根小说零

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案

面試官：MySQL連環(huán)炮，你扛得住嘛？

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

阿Q的想法就是需要對自己掌握的知識進行歸納整理，系統(tǒng)的、分類的去復(fù)習(xí)相關(guān)的知識點。這樣也能更好的把自己陌生的內(nèi)容框出來，在暴漏自己短板的同時，更針對性的做準(zhǔn)備。

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

1、三大范式

2、DML 語句和 DDL 語句區(qū)別

3、主鍵和外鍵的區(qū)別

4、drop、delete、truncate 區(qū)別

5、基礎(chǔ)架構(gòu)

6、MyISAM 和 InnoDB 有什么區(qū)別？

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

8、為什么 MySQL 的自增主鍵不連續(xù)

9、redo log 是做什么的?

10、redo log 的刷盤時機

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

12、什么是 binlog

13、binlog 記錄格式

14、binlog 寫入機制

15、redolog 和 binlog 的區(qū)別是什么

16、兩階段提交

17、什么是 undo log.

18、什么是 relaylog

19、索引

20、Hash 索引

21、B樹和B+ 樹

22、主鍵索引

23、二級索引

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

25、回表

26、覆蓋索引和聯(lián)合索引

27、最左前綴匹配原則

28、索引下推

29、隱式轉(zhuǎn)換

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

31、避免索引失效

32、建立索引的規(guī)則

33、事務(wù)極其特性

34、并發(fā)事務(wù)帶來的問題

35、事務(wù)的隔離級別

36、MVCC

37、Mysql 中的鎖

38、查詢語句執(zhí)行過程

39、更新語句執(zhí)行過程

40、sql 優(yōu)化

41、主從同步數(shù)據(jù)

42、主從延遲要怎么解決

43、為什么不要使用長事務(wù)

1、三大范式

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

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

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

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

2、DML 語句和 DDL 語句區(qū)別

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

3、主鍵和外鍵的區(qū)別

主鍵：用于唯一標(biāo)識一行數(shù)據(jù)，不能有重復(fù)，不允許為空，且一個表只能有一個主鍵；
外鍵：用來和其他表建立聯(lián)系，外鍵是另一表的主鍵，外鍵是可以有重復(fù)的，可以是空值。一個表可以有多個外鍵；

4、drop、delete、truncate 區(qū)別

（1）用法不同

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

（2）屬于不同的數(shù)據(jù)庫語言

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

（3）執(zhí)行速度不同

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

一般來說：drop > truncate > delete

5、基礎(chǔ)架構(gòu)

下圖是 MySQL 的一個簡要架構(gòu)圖，從下圖你可以很清晰的看到客戶端的一條 SQL 語句在 MySQL 內(nèi)部是如何執(zhí)行的。

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

6、MyISAM 和 InnoDB 有什么區(qū)別？

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

（1）是否支持行級鎖

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

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

（2）是否支持事務(wù)

MyISAM 不提供事務(wù)支持，InnoDB 提供事務(wù)支持，實現(xiàn)了 SQL 標(biāo)準(zhǔn)定義的四個隔離級別，具有提交和回滾事務(wù)的能力。

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

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

（3）是否支持外鍵

MyISAM 不支持，而 InnoDB 支持。

（4）是否支持數(shù)據(jù)庫異常崩潰后的安全恢復(fù)

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

（5）是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

（6）索引實現(xiàn)

雖然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作為索引結(jié)構(gòu)，但是兩者的實現(xiàn)方式不太一樣。

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

（7）性能差別

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

InnoDB 和 MyISAM 性能對比

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

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

8、為什么 MySQL 的自增主鍵不連續(xù)

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

9、redo log 是做什么的?

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

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

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

10、redo log 的刷盤時機

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

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

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

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

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

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

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

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

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

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

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

12、什么是 binlog

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

不管用什么存儲引擎，只要發(fā)生了表數(shù)據(jù)更新，都會產(chǎn)生 binlog 日志。它的主要作用就是數(shù)據(jù)備份、主從復(fù)制。

binlog會記錄所有涉及更新數(shù)據(jù)的邏輯操作，屬于邏輯日志，并且是順序?qū)憽?/p>

13、binlog 記錄格式

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

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

14、binlog 寫入機制

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

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

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

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

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

15、redolog 和 binlog 的區(qū)別是什么

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

16、兩階段提交

假設(shè)執(zhí)行 sql 過程中寫完 redo log 日志后，binlog 日志寫期間發(fā)生了異常，會出現(xiàn)什么情況呢？

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

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

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

再看一個場景，redo log 設(shè)置 commit 階段發(fā)生異常，那會不會回滾事務(wù)呢？

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

17、什么是 undo log.

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

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

版本鏈的頭節(jié)點就是當(dāng)前記錄最新的值。

18、什么是 relaylog

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

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

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

19、索引

索引其實是一種數(shù)據(jù)結(jié)構(gòu)，能夠幫助我們快速的檢索數(shù)據(jù)庫中的數(shù)據(jù)。

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

20、Hash 索引

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

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

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

為了減少 Hash 沖突的發(fā)生，一個好的哈希函數(shù)應(yīng)該“均勻地”將數(shù)據(jù)分布在整個可能的哈希值集合中。

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

21、B樹和B+ 樹

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

22、主鍵索引

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

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

23、二級索引

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

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

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

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

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

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

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

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

輔助索引是我們?nèi)藶閯?chuàng)建的索引，它的葉子節(jié)點中存放的是主鍵，當(dāng)我們通過輔助索引查找到主鍵之后，再通過查找的主鍵去回表查找主鍵索引。

25、回表

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

26、覆蓋索引和聯(lián)合索引

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

使用表中的多個字段創(chuàng)建索引，就是聯(lián)合索引，也叫組合索引或復(fù)合索引。

27、最左前綴匹配原則

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

所以，我們在使用聯(lián)合索引時，可以將區(qū)分度高的字段放在最左邊，這也可以過濾更多數(shù)據(jù)。

28、索引下推

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

29、隱式轉(zhuǎn)換

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

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

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

查詢

當(dāng)普通索引為條件時查詢到數(shù)據(jù)會一直掃描，直到掃完整張表；

當(dāng)唯一索引為查詢條件時，查到該數(shù)據(jù)會直接返回，不會繼續(xù)掃表；

更新
普通索引會直接將操作更新到 change buffer 中，然后結(jié)束
唯一索引需要判斷數(shù)據(jù)是否沖突

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

31、避免索引失效

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

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

32、建立索引的規(guī)則

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

33、事務(wù)極其特性

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

事務(wù)的特性：

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

34、并發(fā)事務(wù)帶來的問題

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

35、事務(wù)的隔離級別

為了解決以上隔離性引發(fā)的并發(fā)問題，數(shù)據(jù)庫提供了事務(wù)的隔離機制。

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

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

36、MVCC

鎖的粒度過大會導(dǎo)致性能的下降， MySQL 的 InnoDB 引擎下存在一種性能更優(yōu)越的 MVCC 方法。

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

MVCC 的實現(xiàn)依賴于隱藏列、Undo log、 Read View 。

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

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

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

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

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

37、Mysql 中的鎖

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

38、查詢語句執(zhí)行過程

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

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

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

進行權(quán)限校驗，如果沒有權(quán)限就會返回錯誤信息，如果有權(quán)限就會調(diào)用數(shù)據(jù)庫引擎接口，返回引擎的執(zhí)行結(jié)果。

查詢語句的執(zhí)行流程如下：權(quán)限校驗（如果命中緩存）--->查詢緩存--->分析器--->優(yōu)化器--->權(quán)限校驗--->執(zhí)行器--->引擎

39、更新語句執(zhí)行過程

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

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

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

更新語句執(zhí)行流程如下：分析器---->權(quán)限校驗---->執(zhí)行器--->引擎---redo log(prepare 狀態(tài))---> binlog --->redo log(commit狀態(tài))

40、sql 優(yōu)化

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

對字段進行 null 值判斷，

使用!=或<>

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

in 和 not in 也要慎用

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

減少表達式操作

函數(shù)操作

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

41、主從同步數(shù)據(jù)

master 主庫將此次更新的事件類型寫入到主庫的 binlog 文件中
master 創(chuàng)建 log dump 線程通知 slave 需要更新數(shù)據(jù)
slave 向 master 節(jié)點發(fā)送請求，將該 binlog 文件內(nèi)容存到本地的 relaylog 中
slave 開啟 sql 線程讀取 relaylog 中的內(nèi)容，將其中的內(nèi)容在本地重新執(zhí)行一遍，完成主從數(shù)據(jù)同步

同步策略：

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

42、主從延遲要怎么解決

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

43、為什么不要使用長事務(wù)

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

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

新聞名稱：面試官：MySQL連環(huán)炮，你扛得住嘛？
網(wǎng)站鏈接：http://www.fisionsoft.com.cn/article/dpjdsjg.html

新聞中心

1、三大范式

2、DML 語句和 DDL 語句區(qū)別

3、主鍵和外鍵的區(qū)別

4、drop、delete、truncate 區(qū)別

（1）用法不同

（2）屬于不同的數(shù)據(jù)庫語言

（3）執(zhí)行速度不同

5、基礎(chǔ)架構(gòu)

6、MyISAM 和 InnoDB 有什么區(qū)別？

（1）是否支持行級鎖

（2）是否支持事務(wù)

（3）是否支持外鍵

（4）是否支持數(shù)據(jù)庫異常崩潰后的安全恢復(fù)

（5）是否支持 MVCC

（6）索引實現(xiàn)

（7）性能差別

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

8、為什么 MySQL 的自增主鍵不連續(xù)

9、redo log 是做什么的?

10、redo log 的刷盤時機

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

12、什么是 binlog

13、binlog 記錄格式

14、binlog 寫入機制

15、redolog 和 binlog 的區(qū)別是什么

16、兩階段提交

17、什么是 undo log.

18、什么是 relaylog

19、索引

20、Hash 索引

21、B樹和B+ 樹

22、主鍵索引

23、二級索引

24、聚簇索引與非聚簇索引

25、回表

26、覆蓋索引和聯(lián)合索引

27、最左前綴匹配原則

28、索引下推

29、隱式轉(zhuǎn)換

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

31、避免索引失效

32、建立索引的規(guī)則

33、事務(wù)極其特性

34、并發(fā)事務(wù)帶來的問題

35、事務(wù)的隔離級別

36、MVCC

37、Mysql 中的鎖

38、查詢語句執(zhí)行過程

39、更新語句執(zhí)行過程

40、sql 優(yōu)化

41、主從同步數(shù)據(jù)

42、主從延遲要怎么解決

43、為什么不要使用長事務(wù)

其他資訊

2、DML 語句和 DDL 語句區(qū)別

3、主鍵和外鍵的區(qū)別

4、drop、delete、truncate 區(qū)別

5、基礎(chǔ)架構(gòu)

6、MyISAM 和 InnoDB 有什么區(qū)別？

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

8、為什么 MySQL 的自增主鍵不連續(xù)

9、redo log 是做什么的?

10、redo log 的刷盤時機

12、什么是 binlog

13、binlog 記錄格式

14、binlog 寫入機制

15、redolog 和 binlog 的區(qū)別是什么

16、兩階段提交

19、索引

21、B樹和B+ 樹

22、主鍵索引

23、二級索引

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

25、回表

26、覆蓋索引和聯(lián)合索引

27、最左前綴匹配原則

28、索引下推

29、隱式轉(zhuǎn)換

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

32、建立索引的規(guī)則

34、并發(fā)事務(wù)帶來的問題

35、事務(wù)的隔離級別

37、Mysql 中的鎖

38、查詢語句執(zhí)行過程

39、更新語句執(zhí)行過程

40、sql 優(yōu)化

41、主從同步數(shù)據(jù)

43、為什么不要使用長事務(wù)