面試官：MySQL連環(huán)炮，你扛得住嘛？
最近后臺好多小伙伴發(fā)私信咨詢我，問馬上就是金九銀十的面試黃金期了，該如何在三個(gè)月內(nèi)突擊一下，以便更好地通過面試呢？
俺的想法就是需要對自己掌握的知識進(jìn)行歸納整理，系統(tǒng)的、分類的去復(fù)習(xí)相關(guān)的知識點(diǎn)。這樣也能更好的把自己陌生的內(nèi)容框出來，在暴漏自己短板的同時(shí)，更針對性的做準(zhǔn)備。
為了節(jié)省大家的時(shí)間，我決定幫大家先將 MySQL 面試相關(guān)的知識進(jìn)行匯總整理，一改往日知識點(diǎn)東一棒槌西一榔頭的問題。那么，你準(zhǔn)備好迎接 MySQL 連環(huán)炮了嘛？
【2023最新網(wǎng)絡(luò)安全大廠面經(jīng)分享】
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 的刷盤時(shí)機(jī)
11、redo log 是怎么記錄日志的
12、什么是 binlog
13、binlog 記錄格式
14、binlog 寫入機(jī)制
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)于表中的字段）不能再被分割，也就是這個(gè)字段只能是一個(gè)值，不能再分為多個(gè)其他的字段了。1NF 是所有關(guān)系型數(shù)據(jù)庫的最基本要求?，也就是說關(guān)系型數(shù)據(jù)庫中創(chuàng)建的表一定滿足第一范式。
2NF(第二范式)：2NF 要求數(shù)據(jù)庫表中的每個(gè)實(shí)例或行必須可以被惟一地區(qū)分，2NF 在 1NF 的基礎(chǔ)上增加了一個(gè)列，這個(gè)列稱為主鍵，非主屬性都依賴于主鍵。
3NF(第三范式)：3NF 在 2NF 的基礎(chǔ)之上，要求每列都和主鍵列直接相關(guān)，而不是間接相關(guān)，即不存在其他表的非主鍵信息。
在開發(fā)過程中，并不一定要滿足三大范式，有時(shí)候?yàn)榱颂岣卟樵冃?，可以在表中冗余其他表的字段?/p>
2、DML 語句和 DDL 語句區(qū)別
DML 是數(shù)據(jù)庫操作語言（Data Manipulation Language）的縮寫，是指對數(shù)據(jù)庫中表記錄的操作，主要包括表記錄的插入、更新、刪除和查詢，是開發(fā)人員日常使用最頻繁的操作。
DDL （Data Definition Language）是數(shù)據(jù)定義語言的縮寫，簡單來說，就是對數(shù)據(jù)庫內(nèi)部的對象進(jìn)行創(chuàng)建、刪除、修改的操作語言。它和 DML 語言的最大區(qū)別是 DML 只是對表內(nèi)部數(shù)據(jù)的操作，而不涉及到表的定義、結(jié)構(gòu)的修改，更不會(huì)涉及到其他對象。DDL 語句更多的被數(shù)據(jù)庫管理員（DBA）所使用，一般的開發(fā)人員很少使用。
3、主鍵和外鍵的區(qū)別
主鍵：用于唯一標(biāo)識一行數(shù)據(jù)，不能有重復(fù)，不允許為空，且一個(gè)表只能有一個(gè)主鍵；
外鍵：用來和其他表建立聯(lián)系，外鍵是另一表的主鍵，外鍵是可以有重復(fù)的，可以是空值。一個(gè)表可以有多個(gè)外鍵；
4、drop、delete、truncate 區(qū)別
（1）用法不同
drop(丟棄數(shù)據(jù)):?drop table 表名?，直接將表結(jié)構(gòu)都刪除掉，在刪除表的時(shí)候使用。truncate?(清空數(shù)據(jù)) :?truncate table 表名?，只刪除表中的數(shù)據(jù)，再插入數(shù)據(jù)的時(shí)候自增長 id 又從 1 開始，在清空表中數(shù)據(jù)的時(shí)候使用。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ù)庫操作語言)語句，這個(gè)操作會(huì)放到 rollback segment 中，事務(wù)提交之后才生效。
（3）執(zhí)行速度不同
delete命令執(zhí)行的時(shí)候會(huì)產(chǎn)生數(shù)據(jù)庫的binlog日志，而日志記錄是需要消耗時(shí)間的，但是也有個(gè)好處方便數(shù)據(jù)回滾恢復(fù)。truncate命令執(zhí)行的時(shí)候不會(huì)產(chǎn)生數(shù)據(jù)庫日志，因此比delete要快。除此之外，還會(huì)把表的自增值重置和索引恢復(fù)到初始大小等。drop命令會(huì)把表占用的空間全部釋放掉。
一般來說：drop?>?truncate?>?delete
5、基礎(chǔ)架構(gòu)
下圖是 MySQL 的一個(gè)簡要架構(gòu)圖，從下圖你可以很清晰的看到客戶端的一條 SQL 語句在 MySQL 內(nèi)部是如何執(zhí)行的。
連接器：?身份認(rèn)證和權(quán)限相關(guān)(登錄 MySQL 的時(shí)候)。
查詢緩存：?執(zhí)行查詢語句的時(shí)候，會(huì)先查詢緩存（MySQL 8.0 版本后移除，因?yàn)檫@個(gè)功能不太實(shí)用）。
分析器：?沒有命中緩存的話，SQL 語句就會(huì)經(jīng)過分析器，分析器說白了就是要先看你的 SQL 語句要干嘛，再檢查你的 SQL 語句語法是否正確。
優(yōu)化器：?按照 MySQL 認(rèn)為最優(yōu)的方案去執(zhí)行。
執(zhí)行器：?執(zhí)行語句，然后從存儲引擎返回?cái)?shù)據(jù)。執(zhí)行語句之前會(huì)先判斷是否有權(quán)限，如果沒有權(quán)限的話，就會(huì)報(bào)錯(cuò)。
插件式存儲引擎：主要負(fù)責(zé)數(shù)據(jù)的存儲和讀取，采用的是插件式架構(gòu)，支持 InnoDB、MyISAM、Memory 等多種存儲引擎。
6、MyISAM 和 InnoDB 有什么區(qū)別？
MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默認(rèn)存儲引擎，MySQL 5.5 版本之后，InnoDB 是 MySQL 的默認(rèn)存儲引擎。
（1）是否支持行級鎖
MyISAM 只有表級鎖，而 InnoDB 支持行級鎖和表級鎖，默認(rèn)為行級鎖。
具體的鎖詳情請參考阿Q的此篇文章：面試必備常見存儲引擎與鎖的分類，請查收
（2）是否支持事務(wù)
MyISAM 不提供事務(wù)支持，InnoDB 提供事務(wù)支持，實(shí)現(xiàn)了 SQL 標(biāo)準(zhǔn)定義的四個(gè)隔離級別，具有提交和回滾事務(wù)的能力。
InnoDB 默認(rèn)使用的 REPEATABLE-READ（可重讀）隔離級別是可以解決幻讀問題發(fā)生的（基于 MVCC 和 Next-Key Lock）。
關(guān)于 MySQL 事務(wù)以及解決幻讀的詳細(xì)介紹，可以看看阿Q寫的這篇文章：InnoDB 解決幻讀的方案--LBCC&MVCC
（3）是否支持外鍵
MyISAM 不支持，而 InnoDB 支持。
（4）是否支持?jǐn)?shù)據(jù)庫異常崩潰后的安全恢復(fù)
MyISAM 不支持，而 InnoDB 支持。使用 InnoDB 的數(shù)據(jù)庫在異常崩潰后，數(shù)據(jù)庫重新啟動(dòng)的時(shí)候會(huì)保證數(shù)據(jù)庫恢復(fù)到崩潰前的狀態(tài)。這個(gè)恢復(fù)的過程依賴于?redo log?。
（5）是否支持 MVCC
MyISAM 不支持，而 InnoDB 支持。
（6）索引實(shí)現(xiàn)
雖然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作為索引結(jié)構(gòu)，但是兩者的實(shí)現(xiàn)方式不太一樣。
InnoDB 引擎中，其數(shù)據(jù)文件本身就是索引文件。其表數(shù)據(jù)文件本身就是按 B+Tree 組織的一個(gè)索引結(jié)構(gòu)，樹的葉子節(jié)點(diǎn) data 域保存了完整的數(shù)據(jù)記錄。
MyISAM 索引文件和數(shù)據(jù)文件是分離的，索引保存的是數(shù)據(jù)文件的指針。
（7）性能差別
InnoDB 的性能比 MyISAM 更強(qiáng)大，不管是在讀寫混合模式下還是只讀模式下，隨著 CPU 核數(shù)的增加，InnoDB 的讀寫能力呈線性增長。MyISAM 因?yàn)樽x寫不能并發(fā)，它的處理能力跟核數(shù)沒關(guān)系。
7、推薦自增id作為主鍵問題
普通索引的 B+ 樹上存放的是主鍵索引的值，如果該值較大，會(huì)「導(dǎo)致普通索引的存儲空間較大」
使用自增 id 做主鍵索引新插入數(shù)據(jù)只要放在該頁的最尾端就可以，直接「按照順序插入」，不用刻意維護(hù)
頁分裂容易維護(hù)，當(dāng)插入數(shù)據(jù)的當(dāng)前頁快滿時(shí)，會(huì)發(fā)生頁分裂的現(xiàn)象，如果主鍵索引不為自增 id，那么數(shù)據(jù)就可能從頁的中間插入，頁的數(shù)據(jù)會(huì)頻繁的變動(dòng)，「導(dǎo)致頁分裂維護(hù)成本較高」
8、為什么 MySQL 的自增主鍵不連續(xù)
在MySQL 5.7及之前的版本，自增值保存在內(nèi)存里，并沒有持久化；
唯一鍵沖突：插入數(shù)據(jù)時(shí)先將自增主鍵+1，然后插入數(shù)據(jù)時(shí)唯一鍵沖突，插入數(shù)據(jù)失敗，但是未將自增主鍵改回；
事務(wù)回滾：和唯一鍵沖突類似，回滾操作時(shí)自增值也不回退，事實(shí)上，這么做的主要原因是為了提高性能。
9、redo log 是做什么的?
redo log（重做日志）是InnoDB存儲引擎獨(dú)有的，它讓MySQL擁有了崩潰恢復(fù)能力。
比如?MySQL?實(shí)例掛了或宕機(jī)了，重啟時(shí)，InnoDB存儲引擎會(huì)使用redo log恢復(fù)數(shù)據(jù)，保證數(shù)據(jù)的持久性與完整性。
更新表數(shù)據(jù)的時(shí)候，如果發(fā)現(xiàn)?Buffer Pool?里存在要更新的數(shù)據(jù)，就直接在?Buffer Pool?里更新。然后會(huì)把“在某個(gè)數(shù)據(jù)頁上做了什么修改”記錄到重做日志緩存（redo log buffer）里，接著刷盤到?redo log?文件里。
10、redo log 的刷盤時(shí)機(jī)
紅色部分為 redo log buffer 屬于內(nèi)存
黃色部分為 page cache ，此時(shí)已經(jīng)寫入磁盤了，但是未進(jìn)行持久化
綠色部分是硬盤，已經(jīng)完成持久化
InnoDB 存儲引擎為 redo log 的刷盤策略提供了 innodb_flush_log_at_trx_commit 參數(shù)，它支持三種策略
設(shè)置為0的時(shí)候，表示每次事務(wù)提交時(shí)不進(jìn)行刷盤操作，只是保留在 redo log buffer中，mysql 崩潰會(huì)丟失1s的數(shù)據(jù)；
設(shè)置為1的時(shí)候，表示每次事務(wù)提交時(shí)都將進(jìn)行刷盤操作（默認(rèn)值），持久化到磁盤；
設(shè)置為2的時(shí)候，表示每次事務(wù)提交時(shí)都只把redo log buffer內(nèi)容寫入page cache，OS宕機(jī)會(huì)丟失1s的數(shù)據(jù)，因?yàn)槲催M(jìn)行持久化；
innodb_flush_log_at_trx_commit 參數(shù)默認(rèn)為 1 ，也就是說當(dāng)事務(wù)提交時(shí)會(huì)調(diào)用 fsync(同步操作) 對 redo log 進(jìn)行刷盤。
另外 InnoDB 存儲引擎有一個(gè)后臺線程，每隔1秒，就會(huì)把 redo log buffer 中的內(nèi)容寫到文件系統(tǒng)緩存（page cache），然后調(diào)用 fsync 刷盤。
redo log buffer占用的空間即將達(dá)到 innodb_log_buffer_size 一半的時(shí)候，后臺線程會(huì)主動(dòng)刷盤。
11、redo log 是怎么記錄日志的
硬盤上存儲的?redo log?日志文件不只一個(gè)，而是以一個(gè)日志文件組的形式出現(xiàn)的，每個(gè)的redo日志文件大小都是一樣的。
比如可以配置為一組4個(gè)文件，每個(gè)文件的大小是?1GB，整個(gè)?redo log?日志文件組可以記錄4G的內(nèi)容。
它采用的是環(huán)形數(shù)組形式，從頭開始寫，寫到末尾又回到頭循環(huán)寫，如下圖所示。
所以，如果數(shù)據(jù)寫滿了但是還沒有來得及將數(shù)據(jù)真正的刷入磁盤當(dāng)中，那么就會(huì)發(fā)生「內(nèi)存抖動(dòng)」現(xiàn)象，從肉眼的角度來觀察會(huì)發(fā)現(xiàn) mysql 會(huì)宕機(jī)一會(huì)兒，此時(shí)就是正在刷盤了。
12、什么是 binlog
binlog 是歸檔日志，屬于 Server 層的日志，是一個(gè)二進(jìn)制格式的文件，記錄內(nèi)容是語句的原始邏輯，類似于“給 ID=2 這一行的 c 字段加 1”。
不管用什么存儲引擎，只要發(fā)生了表數(shù)據(jù)更新，都會(huì)產(chǎn)生?binlog?日志。它的主要作用就是數(shù)據(jù)備份、主從復(fù)制。
binlog會(huì)記錄所有涉及更新數(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會(huì)判斷這條SQL語句是否可能引起數(shù)據(jù)不一致：如果是，就用row格式，否則就用statement格式。
14、binlog 寫入機(jī)制
事務(wù)執(zhí)行過程中，先把日志寫到binlog cache，事務(wù)提交的時(shí)候，再把binlog cache寫到binlog文件中。
因?yàn)橐粋€(gè)事務(wù)的binlog不能被拆開，無論這個(gè)事務(wù)多大，也要確保一次性寫入，所以系統(tǒng)會(huì)給每個(gè)線程分配一個(gè)塊內(nèi)存作為binlog cache。
我們可以通過binlog_cache_size參數(shù)控制單個(gè)線程 binlog cache 大小，如果存儲內(nèi)容超過了這個(gè)參數(shù)，就要暫存到磁盤（Swap）。
binlog 也提供了 sync_binlog 參數(shù)來控制寫入 page cache 和磁盤的時(shí)機(jī)：
0：每次提交事務(wù)都只寫入到文件系統(tǒng)的 page cache，由系統(tǒng)自行判斷什么時(shí)候執(zhí)行
fsync，機(jī)器宕機(jī)，page cache里面的 binlog 會(huì)丟失。1：每次提交事務(wù)都會(huì)執(zhí)行
fsync，就如同?redo log 日志刷盤流程?一樣。N(N>1)：每次提交事務(wù)都寫入到文件系統(tǒng)的 page cache，但累積
N個(gè)事務(wù)后才fsync。如果機(jī)器宕機(jī)，會(huì)丟失最近N個(gè)事務(wù)的binlog日志。
15、redolog 和 binlog 的區(qū)別是什么
redolog?是?Innodb?獨(dú)有的日志，而?binlog?是?server?層的，所有的存儲引擎都有使用到；
redolog?記錄了具體的數(shù)值，對某個(gè)頁做了什么修改，binlog?記錄的操作內(nèi)容；
binlog?大小達(dá)到上限或者 flush log?會(huì)生成一個(gè)新的文件，而?redolog?有固定大小只能循環(huán)利用；
binlog 日志沒有 crash-safe 的能力，只能用于歸檔，而 redo log 有 crash-safe 能力；
redo log?在事務(wù)執(zhí)行過程中可以不斷寫入（刷盤設(shè)置為1，后臺線程1s執(zhí)行一次或者 redo log buffer 占用的空間即將達(dá)到 innodb_log_buffer_size 一半的時(shí)候），而 binlog 只有在提交事務(wù)時(shí)才寫入文件緩存系統(tǒng)；
16、兩階段提交
假設(shè)執(zhí)行 sql 過程中寫完 redo log 日志后，binlog 日志寫期間發(fā)生了異常，會(huì)出現(xiàn)什么情況呢？
由于 binlog 沒寫完就異常，這時(shí)候 binlog 里面沒有對應(yīng)的修改記錄。因此，之后用 binlog 日志恢復(fù)數(shù)據(jù)時(shí)，就會(huì)少這一次更新，最終數(shù)據(jù)不一致。
為了解決兩份日志之間的邏輯一致問題，InnoDB 存儲引擎使用兩階段提交方案。
將 redo log 的寫入拆成了兩個(gè)步驟 prepare 和 commit，這就是兩階段提交。使用兩階段提交后，寫入 binlog 時(shí)發(fā)生異常也不會(huì)有影響，因?yàn)?MySQL 根據(jù) redo log日志恢復(fù)數(shù)據(jù)時(shí)，發(fā)現(xiàn) redo log 還處于 prepare 階段，并且沒有對應(yīng) binlog 日志，就會(huì)回滾該事務(wù)。
再看一個(gè)場景，redo log 設(shè)置 commit 階段發(fā)生異常，那會(huì)不會(huì)回滾事務(wù)呢？
并不會(huì)回滾事務(wù)，雖然 redo log 是處于 prepare 階段，但是能通過事務(wù)id找到對應(yīng)的 binlog 日志，所以 MySQL 認(rèn)為是完整的，就會(huì)提交事務(wù)恢復(fù)數(shù)據(jù)。
17、什么是 undo log.
我們知道如果想要保證事務(wù)的原子性，就需要在異常發(fā)生時(shí)，對已經(jīng)執(zhí)行的操作（INSERT、DELETE、UPDATE）進(jìn)行回滾，在 MySQL 中，恢復(fù)機(jī)制是通過回滾日志（undo log） 實(shí)現(xiàn)的，所有事務(wù)進(jìn)行的修改都會(huì)先記錄到這個(gè)回滾日志中，然后再執(zhí)行相關(guān)的操作。
每次對記錄進(jìn)行改動(dòng)都會(huì)記錄一條 undo log，每條 undo log 也都有一個(gè)DB_ROLL_PTR屬性，可以將這些 undo log 都連起來，串成一個(gè)鏈表，形成版本鏈。
版本鏈的頭節(jié)點(diǎn)就是當(dāng)前記錄最新的值。
18、什么是 relaylog
relaylog 是中繼日志，在主從同步的時(shí)候使用到，它是一個(gè)中介臨時(shí)的日志文件，用于存儲從 master 節(jié)點(diǎn)同步過來的 binlog 日志內(nèi)容。
master 主節(jié)點(diǎn)的 binlog 傳到 slave 從節(jié)點(diǎn)后，被寫入 relay log 里，從節(jié)點(diǎn)的 slave sql 線程從 relaylog 里讀取日志然后應(yīng)用到 slave 從節(jié)點(diǎn)本地。
從服務(wù)器 I/O 線程將主服務(wù)器的二進(jìn)制日志讀取過來記錄到從服務(wù)器本地文件，然后 SQL 線程會(huì)讀取 relay-log 日志的內(nèi)容并應(yīng)用到從服務(wù)器，從而使從服務(wù)器和主服務(wù)器的數(shù)據(jù)保持一致。
19、索引
索引其實(shí)是一種數(shù)據(jù)結(jié)構(gòu)，能夠幫助我們快速的檢索數(shù)據(jù)庫中的數(shù)據(jù)。
索引的作用就相當(dāng)于書的目錄。打個(gè)比方: 我們在查字典的時(shí)候，如果沒有目錄，那我們就只能一頁一頁的去找我們需要查的那個(gè)字，速度很慢。如果有目錄了，我們只需要先去目錄里查找字的位置，然后直接翻到那一頁就行了。
20、Hash 索引
哈希表是鍵值對的集合，通過鍵(key)即可快速取出對應(yīng)的值(value)，因此哈希表可以快速檢索數(shù)據(jù)（接近 O（1））。
但是！哈希算法有個(gè) Hash 沖突問題，也就是說多個(gè)不同的 key 最后得到的 index 相同。通常情況下，我們常用的解決辦法是?鏈地址法。
鏈地址法就是將哈希沖突數(shù)據(jù)存放在鏈表中。就比如 JDK1.8 之前 HashMap 就是通過鏈地址法來解決哈希沖突的。不過，JDK1.8 以后 HashMap 為了減少鏈表過長的時(shí)候搜索時(shí)間過長引入了紅黑樹。
為了減少 Hash 沖突的發(fā)生，一個(gè)好的哈希函數(shù)應(yīng)該“均勻地”將數(shù)據(jù)分布在整個(gè)可能的哈希值集合中。
既然哈希表這么快，為什么 MySQL 沒有使用其作為索引的數(shù)據(jù)結(jié)構(gòu)呢？?主要是因?yàn)?Hash 索引不支持順序和范圍查詢。假如我們要對表中的數(shù)據(jù)進(jìn)行排序或者進(jìn)行范圍查詢，那 Hash 索引可就不行了，并且每次 IO 只能取一個(gè)。
21、B樹和B+ 樹
B 樹的所有節(jié)點(diǎn)既存放鍵(key) 也存放數(shù)據(jù)(data)，而 B+樹只有葉子節(jié)點(diǎn)存放 key 和 data，其他內(nèi)節(jié)點(diǎn)只存放 key。
B 樹的葉子節(jié)點(diǎn)都是獨(dú)立的；B+樹的葉子節(jié)點(diǎn)有一條引用鏈指向與它相鄰的葉子節(jié)點(diǎn)。
B 樹的檢索的過程相當(dāng)于對范圍內(nèi)的每個(gè)節(jié)點(diǎn)的關(guān)鍵字做二分查找，可能還沒有到達(dá)葉子節(jié)點(diǎn)，檢索就結(jié)束了。而 B+樹的檢索效率就很穩(wěn)定了，任何查找都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)的過程，葉子節(jié)點(diǎn)的順序檢索很明顯。
22、主鍵索引
數(shù)據(jù)表的主鍵列使用的就是主鍵索引，一種特殊的唯一索引。
在 MySQL 的 InnoDB 的表中，當(dāng)沒有顯示的指定表的主鍵時(shí)，InnoDB 會(huì)自動(dòng)先檢查表中是否有唯一索引且不允許存在 null 值的字段，如果有，則選擇該字段為默認(rèn)的主鍵，否則 InnoDB 將會(huì)自動(dòng)創(chuàng)建一個(gè) 6Byte 的自增主鍵。
23、二級索引
二級索引又稱為輔助索引，是因?yàn)槎壦饕娜~子節(jié)點(diǎn)存儲的數(shù)據(jù)是主鍵。也就是說，通過二級索引，可以定位主鍵的位置。
唯一索引，普通索引，前綴索引等索引屬于二級索引。
唯一索引(Unique Key) ：唯一索引也是一種約束。索引列的值必須唯一，但允許有空值；如果是組合索引，則列值的組合必須唯一。一張表允許創(chuàng)建多個(gè)唯一索引。建立唯一索引的目的大部分時(shí)候都是為了該屬性列的數(shù)據(jù)的唯一性，而不是為了查詢效率。
普通索引(Index) ：普通索引的唯一作用就是為了快速查詢數(shù)據(jù)，一張表允許創(chuàng)建多個(gè)普通索引，并允許數(shù)據(jù)重復(fù)和 NULL。
前綴索引(Prefix) ：前綴索引只適用于字符串類型的數(shù)據(jù)。前綴索引是對文本的前幾個(gè)字符創(chuàng)建索引，相比普通索引建立的數(shù)據(jù)更小， 因?yàn)橹蝗∏皫讉€(gè)字符。
組合索引：指多個(gè)字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段，索引才會(huì)被使用。使用組合索引時(shí)遵循最左前綴集合（后文介紹）；
全文索引(Full Text) ：全文索引主要是為了檢索大文本數(shù)據(jù)中的關(guān)鍵字的信息，是目前搜索引擎數(shù)據(jù)庫使用的一種技術(shù)。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。
MySQL 中的全文索引，有兩個(gè)變量，最小搜索長度和最大搜索長度，對于長度小于最小搜索長度和大于最大搜索長度的詞語，都不會(huì)被索引。
24、聚簇索引與非聚簇索引
聚簇索引即索引結(jié)構(gòu)和數(shù)據(jù)一起存放的索引，并不是一種單獨(dú)的索引類型。InnoDB 的主鍵索引的葉子節(jié)點(diǎn)中存放的就是數(shù)據(jù)行，所以它屬于聚簇索引。
在 MySQL 中，InnoDB 引擎的表的 .ibd 文件就包含了該表的索引和數(shù)據(jù)，對于 InnoDB 引擎表來說，該表的索引(B+樹)的每個(gè)非葉子節(jié)點(diǎn)存儲索引，葉子節(jié)點(diǎn)存儲索引和索引對應(yīng)的數(shù)據(jù)。
非聚簇索引即索引結(jié)構(gòu)和數(shù)據(jù)分開存放的索引，并不是一種單獨(dú)的索引類型。二級索引(輔助索引)就屬于非聚簇索引。MySQL 的 MyISAM 引擎，不管主鍵還是非主鍵，使用的都是非聚簇索引。
輔助索引是我們?nèi)藶閯?chuàng)建的索引，它的葉子節(jié)點(diǎn)中存放的是主鍵，當(dāng)我們通過輔助索引查找到主鍵之后，再通過查找的主鍵去回表查找主鍵索引。
25、回表
回表就是先通過數(shù)據(jù)庫索引掃描出該索引樹中數(shù)據(jù)所在的行，取到主鍵 id，再通過主鍵 id 取出主鍵索引數(shù)中的數(shù)據(jù)，即基于非主鍵索引的查詢需要多掃描一棵索引樹。
26、覆蓋索引和聯(lián)合索引
如果一個(gè)索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。指的是通過索引就能查詢到我們所需要的數(shù)據(jù)，而不需要根據(jù)索引再去查詢數(shù)據(jù)表中的數(shù)據(jù)（ 回表），這樣就減少了數(shù)據(jù)庫的 io 操作，提高查詢效率。
使用表中的多個(gè)字段創(chuàng)建索引，就是聯(lián)合索引，也叫組合索引或復(fù)合索引。
27、最左前綴匹配原則
最左前綴匹配原則指的是在使用聯(lián)合索引時(shí)，MySQL 會(huì)根據(jù)聯(lián)合索引中的字段順序，從左到右依次到查詢條件中去匹配，如果查詢條件中存在與聯(lián)合索引中最左側(cè)字段相匹配的字段，則就會(huì)使用該字段過濾一批數(shù)據(jù)，直至聯(lián)合索引中全部字段匹配完成，或者在執(zhí)行過程中遇到范圍查詢，如 >、<、between 和 以%開頭的like查詢 等條件，才會(huì)停止匹配。
所以，我們在使用聯(lián)合索引時(shí)，可以將區(qū)分度高的字段放在最左邊，這也可以過濾更多數(shù)據(jù)。
28、索引下推
索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一項(xiàng)索引優(yōu)化功能，可以在非聚簇索引遍歷過程中，對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表次數(shù)。
29、隱式轉(zhuǎn)換
當(dāng)操作符與不同類型的操作數(shù)一起使用時(shí)，會(huì)發(fā)生類型轉(zhuǎn)換以使操作數(shù)兼容。某些轉(zhuǎn)換是隱式發(fā)生的。例如，MySQL 會(huì)根據(jù)需要自動(dòng)將字符串轉(zhuǎn)換為數(shù)字，反之亦然。以下規(guī)則描述了比較操作的轉(zhuǎn)換方式：
兩個(gè)參數(shù)至少有一個(gè)是NULL時(shí)，比較的結(jié)果也是NULL，特殊的情況是使用<=>對兩個(gè)NULL做比較時(shí)會(huì)返回1，這兩種情況都不需要做類型轉(zhuǎn)換；
兩個(gè)參數(shù)都是字符串，會(huì)按照字符串來比較，不做類型轉(zhuǎn)換；
兩個(gè)參數(shù)都是整數(shù)，按照整數(shù)來比較，不做類型轉(zhuǎn)換；
十六進(jìn)制的值和非數(shù)字做比較時(shí)，會(huì)被當(dāng)做二進(jìn)制串；
有一個(gè)參數(shù)是TIMESTAMP或DATETIME，并且另外一個(gè)參數(shù)是常量，常量會(huì)被轉(zhuǎn)換為timestamp；
有一個(gè)參數(shù)是decimal類型，如果另外一個(gè)參數(shù)是decimal或者整數(shù)，會(huì)將整數(shù)轉(zhuǎn)換為decimal后進(jìn)行比較，如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù)，則會(huì)把decimal轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較；
所有其他情況下，兩個(gè)參數(shù)都會(huì)被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較；
30、普通索引和唯一索引該怎么選擇?
查詢
當(dāng)普通索引為條件時(shí)查詢到數(shù)據(jù)會(huì)一直掃描，直到掃完整張表；
當(dāng)唯一索引為查詢條件時(shí)，查到該數(shù)據(jù)會(huì)直接返回，不會(huì)繼續(xù)掃表；
更新
普通索引會(huì)直接將操作更新到 change buffer 中，然后結(jié)束
唯一索引需要判斷數(shù)據(jù)是否沖突
所以唯一索引更加適合查詢的場景，普通索引更適合插入的場景。
31、避免索引失效
索引失效也是慢查詢的主要原因之一，常見的導(dǎo)致索引失效的情況有下面這些：
使用 SELECT * 進(jìn)行查詢;
創(chuàng)建了組合索引，但查詢條件未準(zhǔn)守最左匹配原則;
在索引列上進(jìn)行計(jì)算、函數(shù)、類型轉(zhuǎn)換等操作;
以 % 開頭的 LIKE 查詢比如 like '%abc';
查詢條件中使用 or，且 or 的前后條件中有一個(gè)列沒有索引，涉及的索引都不會(huì)被使用到
match() 函數(shù)中的指定的列必須與全文索引中指定的列完全相同，否則會(huì)報(bào)錯(cuò)，無法使用全文索引。
全文索引時(shí)要注意搜索長度會(huì)導(dǎo)致索引失效
32、建立索引的規(guī)則
不為 NULL 的字段 ：索引字段的數(shù)據(jù)應(yīng)該盡量不為 NULL，因?yàn)閷τ跀?shù)據(jù)為 NULL 的字段，數(shù)據(jù)庫較難優(yōu)化。如果字段頻繁被查詢，但又避免不了為 NULL，建議使用 0,1,true,false 這樣語義較為清晰的短值或短字符作為替代。
被頻繁查詢的字段 ：我們創(chuàng)建索引的字段應(yīng)該是查詢操作非常頻繁的字段。
被作為條件查詢的字段 ：被作為 WHERE 條件查詢的字段，應(yīng)該被考慮建立索引。
頻繁需要排序的字段 ：索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時(shí)間。
被經(jīng)常頻繁用于連接的字段 ：經(jīng)常用于連接的字段可能是一些外鍵列，對于外鍵列并不一定要建立外鍵，只是說該列涉及到表與表的關(guān)系。對于頻繁被連接查詢的字段可以考慮建立索引，提高多表連接查詢的效率。
被頻繁更新的字段應(yīng)該慎重建立索引；
盡可能的考慮建立聯(lián)合索引而不是單列索引；
考慮在字符串類型的字段上使用前綴索引代替普通索引；
刪除長期未使用的索引；
33、事務(wù)極其特性
一個(gè)事情由n個(gè)單元組成，這n個(gè)單元在執(zhí)行過程中，要么同時(shí)成功，要么同時(shí)失敗，這就把n個(gè)單元放在了一個(gè)事務(wù)之中。舉個(gè)簡單的例子：在不考慮試題正確與否的前提下，一張?jiān)嚲碛啥鄠€(gè)題目構(gòu)成，當(dāng)你答完題交給老師的時(shí)候是將一整張?jiān)嚲斫唤o老師，而不是將每道題單獨(dú)交給老師，在這里試卷就可以理解成一個(gè)事務(wù)。
事務(wù)的特性：
A：原子性（
Atomicity），原子性是指事務(wù)是一個(gè)不可分割的工作單位，事務(wù)中的操作，要么都發(fā)生，要么都不發(fā)生。C：一致性（
Consistency），在一個(gè)事務(wù)中，事務(wù)前后數(shù)據(jù)的完整性必須保持一致。I：隔離性（
Isolation），存在于多個(gè)事務(wù)中，事務(wù)的隔離性是指多個(gè)用戶并發(fā)訪問數(shù)據(jù)庫時(shí)，一個(gè)用戶的事務(wù)不能被其它用戶的事務(wù)所干擾，多個(gè)并發(fā)事務(wù)之間數(shù)據(jù)要相互隔離。D：持久性（
Durability），持久性是指一個(gè)事務(wù)一旦被提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久性的，接下來即使數(shù)據(jù)庫發(fā)生故障也不應(yīng)該對其有任何影響。
34、并發(fā)事務(wù)帶來的問題
臟讀（Dirty read）：B事務(wù)讀取到了A事務(wù)尚未提交的數(shù)據(jù)；
丟失修改（Lost to modify）：在一個(gè)事務(wù)讀取一個(gè)數(shù)據(jù)時(shí)，另外一個(gè)事務(wù)也訪問了該數(shù)據(jù)，那么在第一個(gè)事務(wù)中修改了這個(gè)數(shù)據(jù)后，第二個(gè)事務(wù)也修改了這個(gè)數(shù)據(jù)。這樣第一個(gè)事務(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ù)的隔離機(jī)制。
read uncommitted（讀未提交）: 一個(gè)事務(wù)還沒提交時(shí)，它做的變更就能被別的事務(wù)看到，讀取尚未提交的數(shù)據(jù)，哪個(gè)問題都不能解決；
read committed（讀已提交）：一個(gè)事務(wù)提交之后，它做的變更才會(huì)被其他事務(wù)看到，讀取已經(jīng)提交的數(shù)據(jù)，可以解決臟讀 ---- oracle默認(rèn)的；
repeatable read（可重復(fù)讀）：一個(gè)事務(wù)執(zhí)行過程中看到的數(shù)據(jù)，總是跟這個(gè)事務(wù)在啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的，可以解決臟讀和不可重復(fù)讀 ---mysql默認(rèn)的；
serializable（串行化）：顧名思義是對于同一行記錄，“寫”會(huì)加“寫鎖”，“讀”會(huì)加“讀鎖”。當(dāng)出現(xiàn)讀寫鎖沖突的時(shí)候，后訪問的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行。可以解決臟讀、不可重復(fù)讀和虛讀---相當(dāng)于鎖表。
雖然 serializable 級別可以解決所有的數(shù)據(jù)庫并發(fā)問題，但是它會(huì)在讀取的每一行數(shù)據(jù)上都加鎖，這就可能導(dǎo)致大量的超時(shí)和鎖競爭問題，從而導(dǎo)致效率下降。所以我們在實(shí)際應(yīng)用中也很少使用 serializable，只有在非常需要確保數(shù)據(jù)的一致性而且可以接受沒有并發(fā)的情況下，才考慮采用該級別。
36、MVCC
鎖的粒度過大會(huì)導(dǎo)致性能的下降， MySQL 的 InnoDB 引擎下存在一種性能更優(yōu)越的 MVCC 方法。
MVCC 是?Multi-Version Concurremt Control?的簡稱，意思是基于多版本的并發(fā)控制協(xié)議，通過版本號避免同一數(shù)據(jù)在不同事務(wù)間的競爭。它主要是為了提高數(shù)據(jù)庫的并發(fā)讀寫性能，不用加鎖就能讓多個(gè)事務(wù)并發(fā)讀寫。
MVCC 的實(shí)現(xiàn)依賴于隱藏列、Undo log、 Read View?。
從上面對 SQL 標(biāo)準(zhǔn)定義了四個(gè)隔離級別的介紹可以看出，標(biāo)準(zhǔn)的 SQL 隔離級別定義里，REPEATABLE-READ(可重復(fù)讀)是不可以防止幻讀的。
但是 InnoDB 實(shí)現(xiàn)的 REPEATABLE-READ 隔離級別其實(shí)是可以解決幻讀問題發(fā)生的，主要有下面兩種情況：
快照讀 ：由 MVCC 機(jī)制來保證不出現(xiàn)幻讀。
當(dāng)前讀 ：使用 Next-Key Lock（臨鍵鎖） 進(jìn)行加鎖來保證不出現(xiàn)幻讀，Next-Key Lock 是行鎖（Record Lock）和間隙鎖（Gap Lock）的結(jié)合，行鎖只能鎖住已經(jīng)存在的行，為了避免插入新行，需要依賴間隙鎖。
InnoDB 存儲引擎在分布式事務(wù)的情況下一般會(huì)用到 SERIALIZABLE 隔離級別。
關(guān)于 MySQL 事務(wù)以及解決幻讀的詳細(xì)介紹，可以看看阿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)限，直接返回錯(cuò)誤信息，如果有權(quán)限，在 MySQL8.0 版本以前，會(huì)先查詢緩存，以這條 SQL 語句為 key 在內(nèi)存中查詢是否有結(jié)果，如果有直接緩存，如果沒有，執(zhí)行下一步。
通過分析器進(jìn)行詞法分析，提取 SQL 語句的關(guān)鍵元素，比如提取上面這個(gè)語句是查詢 select，提取需要查詢的表名為 tb_student，需要查詢所有的列，查詢條件是這個(gè)表的 id='1'。然后判斷這個(gè) SQL 語句是否有語法錯(cuò)誤，比如關(guān)鍵詞是否正確等等，如果檢查沒問題就執(zhí)行下一步。
接下來就是優(yōu)化器進(jìn)行確定執(zhí)行方案，上面的 SQL 語句，可以有兩種執(zhí)行方案：
a.先查詢學(xué)生表中姓名為“張三”的學(xué)生，然后判斷是否年齡是 18。
b.先找出學(xué)生中年齡 18 歲的學(xué)生，然后再查詢姓名為“張三”的學(xué)生。那么優(yōu)化器根據(jù)自己的優(yōu)化算法進(jìn)行選擇執(zhí)行效率最好的一個(gè)方案（優(yōu)化器認(rèn)為，有時(shí)候不一定最好）。那么確認(rèn)了執(zhí)行計(jì)劃后就準(zhǔn)備開始執(zhí)行了。
進(jìn)行權(quán)限校驗(yàn)，如果沒有權(quán)限就會(huì)返回錯(cuò)誤信息，如果有權(quán)限就會(huì)調(diào)用數(shù)據(jù)庫引擎接口，返回引擎的執(zhí)行結(jié)果。
查詢語句的執(zhí)行流程如下：權(quán)限校驗(yàn)（如果命中緩存）--->查詢緩存--->分析器--->優(yōu)化器--->權(quán)限校驗(yàn)--->執(zhí)行器--->引擎
39、更新語句執(zhí)行過程
update?tb_student?A?set?A.age='19'?where?A.name='?張三?';
這條語句基本上也會(huì)沿著上一個(gè)查詢的流程走，只不過執(zhí)行更新的時(shí)候要記錄日志，這就會(huì)引入日志模塊了，MySQL 自帶的日志模塊是 binlog（歸檔日志） ，所有的存儲引擎都可以使用，我們常用的 InnoDB 引擎還自帶了一個(gè)日志模塊 redo log（重做日志），我們就以 InnoDB 模式下來探討這個(gè)語句的執(zhí)行流程。
先查詢到張三這一條數(shù)據(jù)，如果有緩存，也是會(huì)用到緩存。
然后拿到查詢的語句把 age 改為 19，然后調(diào)用引擎 API 接口寫入這一行數(shù)據(jù)，InnoDB 引擎把數(shù)據(jù)保存在內(nèi)存中同時(shí)記錄 redo log，此時(shí) redo log 進(jìn)入 prepare 狀態(tài)，然后告訴執(zhí)行器執(zhí)行完成了隨時(shí)可以提交。
執(zhí)行器收到通知后記錄 binlog，然后調(diào)用引擎接口，提交 redo log 為提交狀態(tài)。
更新完成。
更新語句執(zhí)行流程如下：分析器---->權(quán)限校驗(yà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)致引擎放棄使用索引而進(jìn)行全表掃描；
對字段進(jìn)行 null 值判斷，
使用!=或<>
or 來連接條件（使用union all代替）
in 和 not in 也要慎用
不要使用模糊查詢（可用全文索引）
減少表達(dá)式操作
函數(shù)操作
任何地方都不要使用 select * from t ，用具體的字段列表代替“* ”，不要返回用不到的任何字段；
一個(gè)表的索引數(shù)最好不要超過6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要；
很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇；
盡量減少多表聯(lián)合查詢；
分頁優(yōu)化；
正確使用索引；
41、主從同步數(shù)據(jù)
master 主庫將此次更新的事件類型寫入到主庫的 binlog 文件中
master?創(chuàng)建 log dump 線程通知 slave?需要更新數(shù)據(jù)
slave?向 master 節(jié)點(diǎn)發(fā)送請求，將該 binlog 文件內(nèi)容存到本地的 relaylog 中
slave 開啟 sql 線程讀取 relaylog 中的內(nèi)容，將其中的內(nèi)容在本地重新執(zhí)行一遍，完成主從數(shù)據(jù)同步
同步策略：
全同步復(fù)制：主庫強(qiáng)制同步日志到從庫，等全部從庫執(zhí)行完才返回客戶端，性能差；
半同步復(fù)制：主庫收到至少一個(gè)從庫確認(rèn)就認(rèn)為操作成功，從庫寫入日志成功返回 ack 確認(rèn)；
42、主從延遲要怎么解決
MySQL 5.6 版本以后，提供了一種并行復(fù)制的方式，通過將 SQL 線程轉(zhuǎn)換為多個(gè) work 線程來進(jìn)行重放
提高機(jī)器配置(王道)
在業(yè)務(wù)初期就選擇合適的分庫、分表策略，避免單表單庫過大帶來額外的復(fù)制壓力
避免長事務(wù)
避免讓數(shù)據(jù)庫進(jìn)行各種大量運(yùn)算
對于一些對延遲很敏感的業(yè)務(wù)直接使用主庫讀
43、為什么不要使用長事務(wù)
并發(fā)情況下，數(shù)據(jù)庫連接池容易被撐爆
容易造成大量的阻塞和鎖超時(shí)，長事務(wù)還占用鎖資源，也可能拖垮整個(gè)庫
執(zhí)行時(shí)間長，容易造成主從延遲
回滾所需要的時(shí)間比較長，事務(wù)越長整個(gè)時(shí)間段內(nèi)的事務(wù)也就越多
undolog 日志越來越大，長事務(wù)意味著系統(tǒng)里面會(huì)存在很老的事務(wù)視圖。由于這些事務(wù)隨時(shí)可能訪問數(shù)據(jù)庫里面的任何數(shù)據(jù)，所以這個(gè)事務(wù)提交之前，數(shù)據(jù)庫里面它可能用到的回滾記錄都必須保留，這就會(huì)導(dǎo)致大量占用存儲空間。
今天的內(nèi)容到這里就結(jié)束了，跪求一鍵三連，期望靚仔在評論區(qū)打出老鐵666，鼓勵(lì)一下。
參考鏈接：
https://javaguide.cn/database/mysql/mysql-questions-01.html
https://mp.weixin.qq.com/s/WOINO8U7iNmhpyuATTrdnQ
