[[431243]]

一.說一說三大范式

• 「第一范式」：數據庫中的字段具有「原子性」，不可再分，并且是單一職責
• 「第二范式」：「建立在第一范式的基礎上」，第二范式要求數據庫表中的每個實例或行必須「可以被惟一地區分」。為實現區分通常需要為表加上一個列，以存儲各個實例的惟一標識。這個惟一屬性列被稱為主鍵
• 「第三范式」：「建立在第一，第二范式的基礎上」，確保每列都和主鍵列直接相關，而不是間接相關不存在其他表的非主鍵信息

但是在我們的日常開發當中，「并不是所有的表一定要滿足三大范式」，有時候冗余幾個字段可以少關聯幾張表，帶來的查詢效率的提升有可能是質變的

二.MyISAM 與 InnoDB 的區別是什么?

• 「InnoDB支持事務，MyISAM不支持」。
• 「InnoDB 支持外鍵，而 MyISAM 不支持」。
• 「InnoDB是聚集索引」，使用B+Tree作為索引結構，數據文件是和索引綁在一起的，必須要有主鍵。「MyISAM是非聚集索引」，也是使用B+Tree作為索引結構，索引和數據文件是分離的，索引保存的是數據文件的指針。主鍵索引和輔助索引是獨立的。
• 「InnoDB 不保存表的具體行數」。「MyISAM 用一個變量保存了整個表的行數」。
• Innodb 有 「redolog」 日志文件，MyISAM 沒有
• 「Innodb存儲文件有frm、ibd，而Myisam是frm、MYD、MYI」
• Innodb：frm是表定義文件，ibd是數據文件
• Myisam：frm是表定義文件，myd是數據文件，myi是索引文件
• 「InnoDB 支持表、行鎖，而 MyISAM 支持表級鎖」
• 「InnoDB 必須有唯一索引(主鍵)」,如果沒有指定的話 InnoDB 會自己生成一個隱藏列Row_id來充當默認主鍵，「MyISAM 可以沒有」

三.為什么推薦使用自增 id 作為主鍵?

1.普通索引的 B+ 樹上存放的是主鍵索引的值，如果該值較大，會「導致普通索引的存儲空間較大」

2.使用自增 id 做主鍵索引新插入數據只要放在該頁的最尾端就可以，直接「按照順序插入」，不用刻意維護

3.頁分裂容易維護，當插入數據的當前頁快滿時，會發生頁分裂的現象，如果主鍵索引不為自增 id，那么數據就可能從頁的中間插入，頁的數據會頻繁的變動，「導致頁分裂維護成本較高」

四.一條查詢語句是怎么執行的?

1.通過連接器跟客戶端「建立連接」

2.通過查詢「緩存查詢」之前是否有查詢過該 sql

有則直接返回結果

沒有則執行第三步

3.通過分析器「分析該 sql 的語義」是否正確，包括格式，表等等

4.通過優化器「優化該語句」，比如選擇索引，join 表的連接順序

5.「驗證權限」，驗證是否有該表的查詢權限

• 沒有則返回無權限的錯誤
• 有則執行第六步

6.通過執行器調用存儲引擎執行該 sql，然后返回「執行結果」

五.使用 Innodb 的情況下，一條更新語句是怎么執行的?

用以下語句來舉例，c 字段無索引，id 為主鍵索引

update T set c=c+1 where id=2; 

1.執行器先找引擎取 id=2 這一行。id 是主鍵，引擎直接用樹搜索找到這一行

• 如果 id=2 這一行所在的數據頁本來就「在內存中」，就「直接返回」給執行器
• 「不在內存」中，需要先從磁盤「讀入內存」，然后再「返回」

2.執行器拿到引擎給的行數據，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行數據，再調用引擎接口「寫入這行新數據」

3.引擎將這行新數據更新到內存中，同時將這個更新操作「記錄到 redo log 里面」，此時 redo log 處于 「prepare」 狀態。然后告知執行器執行完成了，隨時可以提交事務

4.執行器「生成這個操作的 binlog」，并把 binlog 「寫入磁盤」

5.執行器調用引擎的「提交事務」接口，引擎把剛剛寫入的 redo log 改成提交(commit)狀態，「更新完成」

六.Innodb 事務為什么要兩階段提交?

• 先寫 redolog 后寫binlog。假設在 redolog 寫完，binlog 還沒有寫完的時候，MySQL 進程異常重啟，這時候 binlog 里面就沒有記錄這個語句。然后你會發現，如果需要用這個 binlog 來恢復臨時庫的話，由于這個語句的 「binlog 丟失」，這個臨時庫就會少了這一次更新，恢復出來的這一行 c 的值就是 0，與原庫的值不同。
• 先寫 binlog 后寫 redolog。如果在 binlog 寫完之后 crash，由于 redolog 還沒寫，崩潰恢復以后這個事務無效，所以這一行c的值是0。但是 binlog 里面已經記錄了“把c從0改成1”這個日志。所以，在之后用 binlog 來恢復的時候就「多了一個事務出來」，恢復出來的這一行 c 的值就是 1，與原庫的值不同。

可以看到，「如果不使用“兩階段提交”，那么數據庫的狀態就有可能和用它的日志恢復出來的庫的狀態不一致」。

七.什么是索引?

相信大家小時候學習漢字的時候都會查字典，想想你查字典的步驟，我們是通過漢字的首字母 a～z 一個一個在字典目錄中查找，最終找到該字的頁數。想想，如果沒有目錄會怎么樣，最差的結果是你有可能翻到字典的最后一頁才找到你想要找的字。

索引就「相當于我們字典中的目錄」，可以極大的提高我們在數據庫的查詢效率。

八.索引失效的場景有哪些?

以下隨便列舉幾個，不同版本的 mysql 場景不一

1.最左前綴法則(帶頭索引不能死，中間索引不能斷

2.不要在索引上做任何操作(計算、函數、自動/手動類型轉換)，不然會導致索引失效而轉向全表掃描

3.不能繼續使用索引中范圍條件(bettween、<、>、in等)右邊的列，如：

select a from user where c > 5 and b = 4； 

4.索引字段上使用(!= 或者 < >)判斷時，會導致索引失效而轉向全表掃描

5.索引字段上使用 is null / is not null 判斷時，會導致索引失效而轉向全表掃描。

6.索引字段使用like以通配符開頭(‘%字符串’)時，會導致索引失效而轉向全表掃描，也是最左前綴原則。

7.索引字段是字符串，但查詢時不加單引號，會導致索引失效而轉向全表掃描

8.索引字段使用 or 時，會導致索引失效而轉向全表掃描

九.為什么采用 B+ 樹,而不是 B-樹

B+ 樹只在葉子結點儲存數據，非葉子結點不存具體數據，只存 key，查詢更穩定，增大了廣度，而一個節點就是磁盤一個內存頁，內存頁大小固定，那么相比 B 樹，B- 樹這些「可以存更多的索引結點」，寬度更大，樹高矮，節點小，拉取一次數據的磁盤 IO 次數少，并且 B+ 樹只需要去遍歷葉子節點就可以實現整棵樹的遍歷。而且在數據庫中基于范圍的查詢是非常頻繁的，效率更高。

十.WAl 是什么?有什么好處?

WAL 就是 Write-Ahead Logging，其實就是「所有的修改都先被寫入到日志中，然后再寫磁盤」，用于保證數據操作的原子性和持久性。

好處:

1.「讀和寫可以完全地并發執行」，不會互相阻塞

2.先寫入 log 中，磁盤寫入從「隨機寫變為順序寫」，降低了 client 端的延遲就。并且，由于順序寫入大概率是在一個磁盤塊內，這樣產生的 io 次數也大大降低

3.寫入日志當數據庫崩潰的時候「可以使用日志來恢復磁盤數據」

十一.什么是回表?

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

十二.什么是索引下推?

如果存在某些被索引的列的判斷條件時，MySQL 將這一部分判斷條件傳遞給存儲引擎，然后由存儲引擎通過判斷索引是否符合 MySQL 服務器傳遞的條件，「只有當索引符合條件時才會將數據檢索出來返回給 MySQL 服務器」 。

十三.什么是覆蓋索引?

覆蓋索引(covering index)指一個查詢語句的執行只用從索引中就能夠取得，不必從數據表中讀取，可以減少回表的次數。比如:

select id from t where age = 1; 

id 為主鍵索引，age 為普通索引，age 這個索引樹存儲的就是逐漸信息，可以直接返回

十四.什么是最左前綴原則?

最左前綴其實說的是，在 where 條件中出現的字段，「如果只有組合索引中的部分列，則這部分列的觸發索引順序」，是按照定義索引的時候的順序從前到后觸發，最左面一個列觸發不了，之后的所有列索引都無法觸發。

比如「有一個 (a,b,c) 的組合索引」

where a = 1 and b = 1 

此時 a,b 會命中該組合索引

where a = 1 and c = 1 

此時 a 會命中該組合索引, c 不會

where b = 1 and c = 1 

此時不會命中該組合索引

十五.普通索引和唯一索引該怎么選擇?

查詢

• 當普通索引為條件時查詢到數據會一直掃描,直到掃完整張表
• 當唯一索引為查詢條件時,查到該數據會直接返回,不會繼續掃表

更新

• 普通索引會直接將操作更新到 change buffer 中,然后結束
• 唯一索引需要判斷數據是否沖突

所以「唯一索引更加適合查詢的場景,普通索引更適合插入的場景」

十六.什么是事務?其特性是什么?

事務是指是程序中一系列操作必須全部成功完成，有一個失敗則全部失敗。

特性

「1.原子性(Atomicity)」：要么全部執行成功，要么全部不執行。

「2.一致性(Consistency)」：事務前后數據的完整性必須保持一致。

「3.隔離性(Isolation)」：隔離性是當多個事務同事觸發時，不能被其他事務的操作所干擾，多個并發事務之間要相互隔離。

「4.持久性(Durability)」：事務完成之后的改變是永久的。

十七.事務的隔離級別?

1.「讀提交」:即能夠「讀取到那些已經提交」的數據

2.「讀未提交」:即能夠「讀取到沒有被提交」的數據

3.「可重復讀」:可重復讀指的是在一個事務內，最開始讀到的數據和事務結束前的「任意時刻讀到的同一批數據都是一致的」

4.「可串行化」:最高事務隔離級別，不管多少事務，都是「依次按序一個一個執行」

「臟讀」

• 臟讀指的是「讀到了其他事務未提交的數據」，未提交意味著這些數據可能會回滾，也就是可能最終不會存到數據庫中，也就是不存在的數據。讀到了并一定最終存在的數據，這就是臟讀

「不可重復讀」

• 對比可重復讀，不可重復讀指的是在同一事務內，「不同的時刻讀到的同一批數據可能是不一樣的」。

「幻讀」

• 幻讀是針對數據插入(INSERT)操作來說的。假設事務A對某些行的內容作了更改，但是還未提交，此時事務B插入了與事務A更改前的記錄相同的記錄行，并且在事務A提交之前先提交了，而這時，在事務A中查詢，會發現「好像剛剛的更改對于某些數據未起作用」，但其實是事務B剛插入進來的這就叫幻讀

十八.binlog 是做什么的?

binlog 是歸檔日志，屬于 Server 層的日志，是一個二進制格式的文件，用于「記錄用戶對數據庫更新的SQL語句信息」。

主要作用

• 主從復制
• 數據恢復

十九.undolog 是做什么的?

undolog 是 InnoDB 存儲引擎的日志，用于保證數據的原子性，「保存了事務發生之前的數據的一個版本，也就是說記錄的是數據是修改之前的數據，可以用于回滾」，同時可以提供多版本并發控制下的讀(MVCC)。

主要作用

• 事務回滾
• 實現多版本控制(MVCC)

二十.relaylog 是做什么的?

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

master 主節點的 binlog 傳到 slave 從節點后，被寫入 relay log 里，從節點的 slave sql 線程從 relaylog 里讀取日志然后應用到 slave 從節點本地。從服務器 I/O 線程將主服務器的二進制日志讀取過來記錄到從服務器本地文件，然后 SQL 線程會讀取 relay-log 日志的內容并應用到從服務器，從而「使從服務器和主服務器的數據保持一致」。

二十一.redolog 是做什么的?

redolog 是 「InnoDB 存儲引擎所特有的一種日志」，用于記錄事務操作的變化，記錄的是數據修改之后的值，不管事務是否提交都會記錄下來。

可以做「數據恢復并且提供 crash-safe 能力」

當有增刪改相關的操作時，會先記錄到 Innodb 中，并修改緩存頁中的數據，「等到 mysql 閑下來的時候才會真正的將 redolog 中的數據寫入到磁盤當中」。

二十二.redolog 是怎么記錄日志的?

InnoDB 的 redo log 是固定大小的，比如可以配置為一組4個文件，每個文件的大小是1GB，那么總共就可以記錄4GB的操作。「從頭開始寫，寫到末尾就又回到開頭循環寫」。

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

二十三.redolog 和 binlog 的區別是什么?

1.「redolog」 是 「Innodb」 獨有的日志，而 「binlog」 是 「server」 層的，所有的存儲引擎都有使用到

2.「redolog」 記錄了「具體的數值」，對某個頁做了什么修改，「binlog」 記錄的「操作內容」

3.「binlog」 大小達到上限或者 flush log 「會生成一個新的文件」，而 「redolog」 有固定大小「只能循環利用」

4.「binlog 日志沒有 crash-safe 的能力」，只能用于歸檔。而 redo log 有 crash-safe 能力。

二十四.說一說 mvcc 吧，有什么作用?

MVCC:多版本并發控制，是現代數據庫(包括 MySQL、Oracle、PostgreSQL 等)引擎實現中常用的處理讀寫沖突的手段，目的在于「提高數據庫高并發場景下的吞吐性能」。

在 MVCC 協議下，每個讀操作會看到一個一致性的快照，「這個快照是基于整個庫的」，并且可以實現非阻塞的讀，用于「支持讀提交和可重復讀隔離級別的實現」。

MVCC 允許數據具有多個版本，這個版本可以是時間戳或者是全局遞增的事務 ID，在同一個時間點，不同的事務看到的數據是不同的，這個修改的數據是「記錄在 undolog 中」的。

二十五.一條 Sql 語句查詢一直慢會是什么原因?

「1.沒有用到索引」

比如函數導致的索引失效，或者本身就沒有加索引

「2.表數據量太大」

考慮分庫分表吧

「3.優化器選錯了索引」

「考慮使用」 force index 強制走索引

二十六.一條 Sql 語句查詢偶爾慢會是什么原因?

「1. 數據庫在刷新臟頁」

比如 「redolog 寫滿了」，「內存不夠用了」釋放內存如果是臟頁也需要刷，mysql 「正常空閑狀態刷臟頁」

「2. 沒有拿到鎖」

二十七.Mysql 主從之間是怎么同步數據的?

1.master 主庫將此次更新的事件類型「寫入到主庫的 binlog 文件」中

2.master 「創建 log dump 線程通知 slave」 需要更新數據

3.「slave」 向 master 節點發送請求，「將該 binlog 文件內容存到本地的 relaylog 中」

4.「slave 開啟 sql 線程」讀取 relaylog 中的內容，「將其中的內容在本地重新執行一遍」，完成主從數據同步

「同步策略」：

1.「全同步復制」：主庫強制同步日志到從庫，等全部從庫執行完才返回客戶端，性能差

2.「半同步復制」：主庫收到至少一個從庫確認就認為操作成功，從庫寫入日志成功返回ack確認

二十八.主從延遲要怎么解決?

1.MySQL 5.6 版本以后，提供了一種「并行復制」的方式，通過將 SQL 線程轉換為多個 work 線程來進行重放

2.「提高機器配置」(王道)

3.在業務初期就選擇合適的分庫、分表策略，「避免單表單庫過大」帶來額外的復制壓力

4.「避免長事務」

5.「避免讓數據庫進行各種大量運算」

6.對于一些對延遲很敏感的業務「直接使用主庫讀」

二十九.刪除表數據后表的大小卻沒有變動,這是為什么?

在使用 delete 刪除數據時，其實對應的數據行并不是真正的刪除，是「邏輯刪除」，InnoDB 僅僅是將其「標記成可復用的狀態」，所以表空間不會變小

三十.為什么 VarChar 建議不要超過255?

當定義varchar長度小于等于255時，長度標識位需要一個字節(utf-8編碼)

當大于255時，長度標識位需要兩個字節，并且建立的「索引也會失效」

三十一.分布式式事務怎么實現?

1.「本地消息表」

2.「消息事務」

3.「二階段提交」

4.「三階段提交」

5.「TCC」

6.「最大努力通知」

7.「Seata 框架」

七種分布式事務的解決方案，一次講給你聽

三十二.Mysql 中有哪些鎖?

以下并不全，主要理解下鎖的意義即可

• 基于鎖的屬性分類：共享鎖、排他鎖
• 基于鎖的粒度分類：表鎖、行鎖、記錄鎖、間隙鎖、臨鍵鎖
• 基于鎖的狀態分類：意向共享鎖、意向排它鎖、死鎖

三十三.為什么不要使用長事務?

1.并發情況下，數據庫「連接池容易被撐爆」

2.「容易造成大量的阻塞和鎖超時」

長事務還占用鎖資源，也可能拖垮整個庫，

3.執行時間長，容易造成「主從延遲」

4.「回滾所需要的時間比較長」

事務越長整個時間段內的事務也就越多

5.「undolog 日志越來越大」

長事務意味著系統里面會存在很老的事務視圖。由于這些事務隨時可能訪問數據庫里面的任何數據，所以這個事務提交之前，數據庫里面它可能用到的回滾記錄都必須保留，這就會導致大量占用存儲空間。

三十四.buffer pool 是做什么的?

buffer pool 是一塊內存區域，為了「提高數據庫的性能」，當數據庫操作數據的時候，把硬盤上的數據加載到 buffer pool，不直接和硬盤打交道，操作的是 buffer pool 里面的數據，數據庫的增刪改查都是在 buffer pool 上進行

buffer pool 里面緩存的數據內容也是一個個數據頁

其中「有三大雙向鏈表」:

「free 鏈表」

• 用于幫助我們找到空閑的緩存頁

「flush 鏈表」

• 用于找到臟緩存頁，也就是需要刷盤的緩存頁

「lru 鏈表」

• 用來淘汰不常被訪問的緩存頁，分為熱數據區和冷數據區，冷數據區主要存放那些不常被用到的數據

預讀機制:

Buffer Pool 有一項特技叫預讀，存儲引擎的接口在被 Server 層調用時，會在響應的同時進行預判，將下次可能用到的數據和索引加載到 Buffer Pool

三十五.說說你的 Sql 調優思路吧

1.「表結構優化」

1.1拆分字段

1.2字段類型的選擇

1.3字段類型大小的限制

1.4合理的增加冗余字段

1.5新建字段一定要有默認值

2.「索引方面」

2.1索引字段的選擇

2.2利用好mysql支持的索引下推，覆蓋索引等功能

2.3唯一索引和普通索引的選擇

3.「查詢語句方面」

3.1避免索引失效

3.2合理的書寫where條件字段順序

3.3小表驅動大表

3.4可以使用force index()防止優化器選錯索引

4.「分庫分表」