🐶 程序猿周周
?? 短視頻小廠BUG攻城獅
🤺 如果文章對你有幫助，記得關注、點贊、收藏，一鍵三連哦，你的支持將成為我最大的動力

本文是《后端面試小冊子》系列的第 1?? 篇文章，該系列將整理和梳理筆者作為 Java 后端程序猿在日常工作以及面試中遇到的實際問題，通過這些問題的系統學習，也幫助筆者順利拿到阿里、字節、華為、快手等多個大廠 Offer，也祝愿大家能夠早日斬獲自己心儀的 Offer。

---

PS：《后端面試小冊子》已整理成冊，目前共十三章節，總計約二十萬字，歡迎👏🏻關注公眾號【程序猿周周】獲取電子版和更多學習資料（最新系列文章也會在此陸續更新）。公眾號后臺可以回復關鍵詞「電?書」可獲得這份面試小冊子。文中所有內容都會在 Github 開源，項目地址 csnotes，如文中存在錯誤，歡迎指出。如果覺得文章還對你有所幫助，趕緊點個免費的 star 支持一下吧！

標題	地址
MySQL數據庫面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/122910606
Redis面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/122934938
計算機網絡面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/122973684
操作系統面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/122994599
Linux面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/122994862
Spring面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/123016872
Java基礎面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/123080189
Java集合面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/123171501
Java并發面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/123266624
Java虛擬機面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/123412605
Java異常面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/123462676
設計模式面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/123490442
Dubbo面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/123538243
Netty面試題總結（2022版）	https://blog.csdn.net/adminpd/article/details/123564362

1 基礎

1.1 基本概念

1、MySQL有哪些數據庫類型？

• 數值類型

有包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別表示 1 字節、2 字節、3 字節、4 字節、8 字節的整數類型。

1）任何整數類型都可以加上 UNSIGNED 屬性，表示無符號整數。

2）任何整數類型都可以指定長度，但它不會限制數據的合法長度，僅僅限制了顯示長度。

還有包括 FLOAT、DOUBLE、DECIMAL 在內的小數類型。

• 字符串類型

包括 VARCHAR、CHAR、TEXT、BLOB。

注意：VARCHAR(n) 和 CHAR(n) 中的 n 并不代表字節個數，而是代表字符的個數。

• 日期和時間類型

常用于表示日期和時間類型為 DATETIME、DATE 和 TIMESTAMP。

盡量使用 TIMESTAMP，空間效率高于 DATETIME。

ref MySQL 數據類型

2、CHAR 和 VARCHAR 區別？

1）首先可以明確的是 CHAR 是定長的，而 VARCHAR 是可以變長。

CHAR 會根據聲明的字符串長度分配空間，并會使用空格對字符串右邊進行尾部填充。所以在檢索 CHAR 類型數據時尾部空格會被刪除，如保存的是字符串 'char '，但最后查詢到的是 'char'。又因為長度固定，所以存儲效率高于 VARCHAR 類型。

VARCHAR 在 MySQL 5.0 之后長度支持到 65535 字節，但會在數據開頭使用額外 1~2 個字節存儲字符串長度（列長度小于 255 字節時使用 1 字節表示，否則 2 字節），在結尾使用 1 字節表示字符串結束。

2）再者，在存儲方式上，CHAR 對英文字符（ASCII）占用 1 字節，對一個漢字使用用 2 字節。而 VARCHAR 對每個字符均使用 2 字節。

雖然 VARCHAR 是根據字符串長度分配存儲空間的，但在內存中依舊使用聲明長度進行排序等作業，故在使用時仍需綜合考量字段長度。

3、CHAR 和 VARCHAR 如何選擇？

1）對于經常變更的數據來說，CHAR 比 VARCHAR更好，因為 CHAR 不容易產生碎片。

2）對于非常短的列或固定長度的數據（如 MD5），CHAR 比 VARCHAR 在存儲空間上更有效率。

4）使用時要注意只分配需要的空間，更長的列排序時會消耗更多內存。

4）盡量避免使用 TEXT/BLOB 類型，查詢時會使用臨時表，導致嚴重的性能開銷。

4、CHAR，VARCHAR 和 Text 的區別？

1）長度區別

• Char 范圍是 0～255。

• Varchar 最長是 64k（注意這里的 64k 是整個 row 的長度，要考慮到其它的 column，還有如果存在 not null 的時候也會占用一位，對不同的字符集，有效長度還不一樣，比如 utf-8 的，最多 21845，還要除去別的column），但 Varchar 在一般情況下存儲都夠用了。

• 如果遇到了大文本，考慮使用 Text，最大能到 4G（其中 TEXT 長度 65,535 bytes，約 64kb；MEDIUMTEXT 長度 16,777,215 bytes，約 16 Mb；而 LONGTEXT 長度 4,294,967,295 bytes，約 4Gb）。

2）效率區別

效率來說基本是 Char > Varchar > Text，但是如果使用的是 Innodb 引擎的話，推薦使用 Varchar 代替 Char。

3）默認值區別

Char 和 Varchar 支持設置默認值，而 Text 不能指定默認值。

1.2 數據庫設計

1、什么是三大范式？

• 第一范式（1NF）：字段（或屬性）是不可分割的最小單元，即不會有重復的列，體現原子性

• 第二范式（2NF）：滿足 1NF 前提下，存在一個候選碼，非主屬性全部依賴該候選碼，即存在主鍵，體現唯一性，專業術語則是消除部分函數依賴

• 第三范式（3NF）：滿足 2NF 前提下，非主屬性必須互不依賴，消除傳遞依賴

ref：如何理解關系型數據庫的常見設計范式？

除了三大范式外，還有BC范式和第四范式，但其規范過于嚴苛，在生產中往往使用不到。

2、什么是范式和反范式，以及各自優缺點？

范式是符合某一種級別的關系模式的集合。構造數據庫必須遵循一定的規則。在關系數據庫中，這種規則就是范式。

名稱	優點	缺點
范式	范式化的表減少了數據冗余，數據表更新操作快、占用存儲空間少。	查詢時通常需要多表關聯查詢，更難進行索引優化
反范式	反范式的過程就是通過冗余數據來提高查詢性能，可以減少表關聯和更好進行索引優化	存在大量冗余數據，并且數據的維護成本更高

所以在平時工作中，我們通常是將范式和反范式相互結合使用。

2 索引

首先了解一下什么是索引，索引是對數據庫表中一列或多列的值進行排序的數據結構，用于快速訪問數據庫表中的特定信息。

1、索引的幾種類型或分類？

1）從物理結構上可以分為聚集索引和非聚集索引兩類：

• 聚簇索引指索引的鍵值的邏輯順序與表中相應行的物理順序一致，即每張表只能有一個聚簇索引，也就是我們常說的主鍵索引；

• 非聚簇索引的邏輯順序則與數據行的物理順序不一致。

2）從應用上可以劃分為一下幾類：

• 普通索引：MySQL 中的基本索引類型，沒有什么限制，允許在定義索引的列中插入重復值和空值，純粹為了提高查詢效率。通過 ALTER TABLE table_name ADD INDEX index_name (column) 創建；

• 唯一索引：索引列中的值必須是唯一的，但是允許為空值。通過 ALTER TABLE table_name ADD UNIQUE index_name (column) 創建；

• 主鍵索引：特殊的唯一索引，也成聚簇索引，不允許有空值，并由數據庫幫我們自動創建；

• 組合索引：組合表中多個字段創建的索引，遵守最左前綴匹配規則；

• 全文索引：只有在 MyISAM 引擎上才能使用，同時只支持 CHAR、VARCHAR、TEXT 類型字段上使用。

2、索引的優缺點？

先來說說優點：創建索引可以大大提高系統的性能。

• 通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

• 可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。

• 可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

• 在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。

• 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

既然增加索引有如此多的優點，為什么不對表中的每一個列都創建一個索引呢？這是因為索引也是有缺點的：

• 創建和維護索引需要耗費時間，這種時間隨著數據量的增加而增加，這樣就降低了數據的維護速度。

• 索引需要占物理空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間。如果要建立聚簇索引，那么需要的空間就會更大。

3、索引設計原則？

• 選擇唯一性索引；

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。

• 為常作為查詢條件的字段建立索引；

如果某個字段經常用來做查詢條件，那么該字段的查詢速度會影響整個表的查詢速度。因此，為這樣的字段建立索引，可以提高整個表的查詢速度。

• 為經常需要排序、分組和聯合操作的字段建立索引；

經常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作會浪費很多時間。如果為其建立索引，可以有效地避免排序操作。

• 限制索引的數目；

每個索引都需要占?用磁盤空間，索引越多，需要的磁盤空間就越大，修改表時，對索引的重構和更新很麻煩。

• 小表不建議索引（如數量級在百萬以內）；

由于數據較小，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果。

• 盡量使用數據量少的索引；

如果索引的值很長，那么查詢的速度會受到影響。此時盡量使用前綴索引。

• 刪除不再使用或者很少使用的索引。

4、索引的數據結構？

索引的數據結構和具體存儲引擎的實現有關，MySQL 中常用的是 Hash 和 B+ 樹索引。

• Hash 索引底層就是 Hash 表，進行查詢時調用 Hash 函數獲取到相應的鍵值（對應地址），然后回表查詢獲得實際數據.

• B+ 樹索引底層實現原理是多路平衡查找樹，對于每一次的查詢都是從根節點出發，查詢到葉子節點方可以獲得所查鍵值，最后查詢判斷是否需要回表查詢.

5、Hash 和 B+ 樹索引的區別？

Hash
1）Hash 進行等值查詢更快，但無法進行范圍查詢。因為經過 Hash 函數建立索引之后，索引的順序與原順序無法保持一致，故不能支持范圍查詢。同理，也不支持使用索引進行排序。

2）Hash 不支持模糊查詢以及多列索引的最左前綴匹配,因為 Hash 函數的值不可預測，如 AA 和 AB 的算出的值沒有相關性。

3）Hash 任何時候都避免不了回表查詢數據.

4）雖然在等值上查詢效率高，但性能不穩定，因為當某個鍵值存在大量重復時，產生 Hash 碰撞，此時查詢效率反而可能降低。

B+ Tree

1）B+ 樹本質是一棵查找樹，自然支持范圍查詢和排序。

2）在符合某些條件（聚簇索引、覆蓋索引等）時候可以只通過索引完成查詢，不需要回表。

3）查詢效率比較穩定，因為每次查詢都是從根節點到葉子節點，且為樹的高度。

6、為何使用 B+ 樹而非二叉查找樹做索引？

我們知道二叉樹的查找效率為 O(logn)，當樹過高時，查找效率會下降。另外由于我們的索引文件并不小，所以是存儲在磁盤上的。

文件系統需要從磁盤讀取數據時，一般以頁為單位進行讀取，假設一個頁內的數據過少，那么操作系統就需要讀取更多的頁，涉及磁盤隨機 I/O 訪問的次數就更多。將數據從磁盤讀入內存涉及隨機 I/O 的訪問，是數據庫里面成本最高的操作之一。

因而這種樹高會隨數據量增多急劇增加，每次更新數據又需要通過左旋和右旋維護平衡的二叉樹，不太適合用于存儲在磁盤上的索引文件。

7、為何使用 B+ 樹而非 B 樹做索引？

在此之前，先來了解一下 B+ 樹和 B 樹的區別：

• B 樹非葉子結點和葉子結點都存儲數據，因此查詢數據時，時間復雜度最好為 O(1)，最壞為 O(log n)。而 B+ 樹只在葉子結點存儲數據，非葉子結點存儲關鍵字，且不同非葉子結點的關鍵字可能重復，因此查詢數據時，時間復雜度固定為 O(log n)。

• B+ 樹葉子結點之間用鏈表相互連接，因而只需掃描葉子結點的鏈表就可以完成一次遍歷操作，B 樹只能通過中序遍歷。

為什么 B+ 樹比 B 樹更適合應用于數據庫索引？

• B+ 樹減少了 IO 次數。

由于索引文件很大因此索引文件存儲在磁盤上，B+ 樹的非葉子結點只存關鍵字不存數據，因而單個頁可以存儲更多的關鍵字，即一次性讀入內存的需要查找的關鍵字也就越多，磁盤的隨機 I/O 讀取次數相對就減少了。

• B+ 樹查詢效率更穩定

由于數據只存在在葉子結點上，所以查找效率固定為 O(log n)，所以 B+ 樹的查詢效率相比B樹更加穩定。

• B+ 樹更加適合范圍查找

B+ 樹葉子結點之間用鏈表有序連接，所以掃描全部數據只需掃描一遍葉子結點，利于掃庫和范圍查詢；B 樹由于非葉子結點也存數據，所以只能通過中序遍歷按序來掃。也就是說，對于范圍查詢和有序遍歷而言，B+ 樹的效率更高。

ref 為什么 B+ 樹比 B 樹更適合應用于數據庫索引？

8、什么是最左匹配原則？

顧名思義，最左優先，以最左邊為起點任何連續的索引都能匹配上。同時遇到范圍查詢（>、<、between、like）就會停止匹配。

如建立 (a,b,c,d) 索引，查詢條件 b = 2 是匹配不到索引的，但是如果查詢條件是 a = 1 and b = 2 或 a=1 又或 b = 2 and a = 1 就可以，因為優化器會自動調整 a,b 的順序。

再比如 a = 1 and b = 2 and c > 3 and d = 4，其中 d 是用不到索引的，因為 c 是一個范圍查詢，它之后的字段會停止匹配。

最左匹配的原理

上圖可以看出 a 是有順序的（1、1、2、2、3、3），而 b 的值是沒有順序的（1、2、1、4、1、2）。所以 b = 2 這種查詢條件無法利用索引。

同時我們還可以發現在 a 值相等的情況下（a = 1），b 又是順序排列的，所以最左匹配原則遇上范圍查詢就會停止，剩下的字段都無法使用索引。

ref 最左匹配原則

9、什么是覆蓋索引？

在 B+ 樹的索引中，葉子節點可能存儲了當前的鍵值，也可能存儲了當前的鍵值以及整行的數據，這就是聚簇索引和非聚簇索引。 在 InnoDB 中，只有主鍵索引是聚簇索引，如果沒有主鍵，則挑選一個唯一鍵建立聚簇索引。如果沒有唯一鍵，則隱式的生成一個鍵來建立聚簇索引。

當查詢使用聚簇索引時，在對應的葉子節點，可以獲取到整行數據，因此不用再次進行回表查詢。

10、什么是索引下推？

索引下推（Index condition pushdown） 簡稱 ICP，在 Mysql 5.6 版本上推出的一項用于優化查詢的技術。

在不使用索引下推的情況下，在使用非主鍵索引進行查詢時，存儲引擎通過索引檢索到數據，然后返回給 MySQL 服務器，服務器判斷數據是否符合條件。

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

索引條件下推優化可以減少存儲引擎查詢基礎表的次數，也可以減少 MySQL 服務器從存儲引擎接收數據的次數。

ref Mysql性能優化：什么是索引下推？

3 存儲

3.1 存儲引擎

1、有哪些常見的存儲引擎？

ref 幾種MySQL數據庫引擎優缺點對比

2、MyISAM 和 InnoDB 的區別？

1）InnoDB 支持事務，而 MyISAM 不支持。

2）InnoDB 支持外鍵，而 MyISAM 不支持。因此將一個含有外鍵的 InnoDB 表 轉為 MyISAM 表會失敗。

3）InnoDB 和 MyISAM 均支持 B+ Tree 數據結構的索引。但 InnoDB 是聚集索引，而 MyISAM 是非聚集索引。

4）InnoDB 不保存表中數據行數，執行 select count(*) from table 時需要全表掃描。而 MyISAM 用一個變量記錄了整個表的行數，速度相當快（注意不能有 WHERE 子句）。

那為什么 InnoDB 沒有使用這樣的變量呢？因為InnoDB的事務特性，在同一時刻表中的行數對于不同的事務而言是不一樣的。

5）InnoDB 支持表、行（默認）級鎖，而 MyISAM 支持表級鎖。

InnoDB 的行鎖是基于索引實現的，而不是物理行記錄上。即訪問如果沒有命中索引，則也無法使用行鎖，將要退化為表鎖。

6）InnoDB 必須有唯一索引（如主鍵），如果沒有指定，就會自動尋找或生產一個隱藏列 Row_id 來充當默認主鍵，而 Myisam 可以沒有主鍵。

ref MyISAM與InnoDB 的區別（9個不同點）

3、InnoDB 的四大特性?

• 插入緩沖insert buffer)
• 二次寫(double write)
• 自適應哈希索引(ahi)
• 預讀(read ahead)

4、InnoDB 為何推薦使用自增主鍵？

自增 ID 可以保證每次插入時 B+ 樹索引是從右邊擴展的，因此相比自定義 ID （如 UUID）可以避免 B+ 樹的頻繁合并和分裂。如果使用字符串主鍵和隨機主鍵，會使得數據隨機插入，效率比較差。

5、如何選擇存儲引擎？

默認使用 InnoDB，MyISAM 適用以插入為主的程序，比如博客系統、新聞門戶。

3.2 存儲結構

1、什么是 InnoDB 的頁、區、段？

• 頁（Page）

首先，InnoDB 將物理磁盤劃分為頁（page），每頁的大小默認為 16 KB，頁是最小的存儲單位。頁根據上層應用的需要，如索引、日志等，分為很多的格式。我們主要說數據頁，也就是存儲實際數據的頁。

• 區（Extent）

如果只有頁這一個層次的話，頁的個數是非常多的，存儲空間的分配和回收都會很麻煩，因為要維護這么多的頁的狀態是非常麻煩的。

所以，InnoDB 又引入了區（Extent) 的概念。一個區默認是 64 個連續的頁組成的，也就是 1MB。通過 Extent 對存儲空間的分配和回收就比較容易了。

• 段（Segment）

為什么要引入段呢，這要從索引說起。我們都知道索引的目的是為了加快查找速度，是一種典型的用空間換時間的方法。

B+ 樹的葉子節點存放的是我們的具體數據，非葉子結點是索引頁。所以 B+ 樹將數據分為了兩部分，葉子節點部分和非葉子節點部分，也就我們要介紹的段 Segment，也就是說 InnoBD 中每一個索引都會創建兩個 Segment 來存放對應的兩部分數據。

Segment 是一種邏輯上的組織，其層次結構從上到下一次為 Segment、Extent、Page。

2、頁由哪些數據組成？

首先看數據頁的基本格式，如下圖：

• File Header

用于描述數據頁的外部信息，比如屬于哪一個表空間、前后頁的頁號等。

• Page Header

用來描述數據頁中的具體信息，比如存在多少條紀錄，第一條紀錄的位置等。

• infimum 和 supremum 紀錄

infimum 和 supremum 是系統生成的紀錄，分別為最小和最大紀錄值，infimum 的下一條是用戶紀錄中鍵值最小的紀錄，supremum 的上一條是用戶紀錄中鍵值最大的紀錄，通過 next_record 字段來相連。

• User Records

用戶紀錄，也就是數據庫表中對應的數據，這里我們說常用的 Compact 格式。

InnoDB 除了我們插入的數據外，還有一些隱藏列，transaction_id（事務ID）、roll_pointer（回滾指針）是一定添加的。

row_id 則不一定，根據以下策略生成：優先使用用戶建表時指定的主鍵，若用戶沒有指定主鍵，則使用unique鍵。若unique鍵都沒有，則系統自動生成row_id，為隱藏列。

• Free Space

頁中目前空閑的存儲，可以插入紀錄。

• Page Dictionary

類似于字典的目錄結構，根據主鍵大小，每隔 4-8 個紀錄設置一個槽，用來紀錄其位置，當根據主鍵查找數據時，首先一步到位找到數據所在的槽，然后在槽中線性搜素。這種方法比從前到后遍歷頁的鏈表的效率更快。

• Page Tailer

File Header存儲刷盤前內存的校驗和，Page Tailer儲存刷盤后的校驗和。當刷盤的時候，出現異常，Page Tailer和File Header中的校驗和不一致，則說明出現刷盤錯誤。

3、頁中插入記錄的過程？

1）如果 Free Space 的空間足夠的話，直接分配空間來添加紀錄，并將插入前最后一條紀錄的 next_record 指向當前插入的紀錄，將當前插入紀錄的 next_record 指向 supremum 紀錄。

2）如果 Free Space的 空間不夠的話，則首先將之前刪除造成的碎片重新整理之后，按照上述步驟插入紀錄。

3）如果當前頁空間整理碎片之后仍然不足的話，則重新申請一個頁，將頁初始化之后，按照上述步驟插入紀錄

ref MySQL之InnoDB物理存儲結構

4、什么是 Buffer Pool？

Buffer Pool 是 InnoDB 存儲引擎層的緩沖池，不屬于 MySQL 的 Server 層，注意跟 8.0 刪掉的“查詢緩存”功能區分。

內存中以頁（page）為單位緩存磁盤數據，減少磁盤IO，提升訪問速度。緩沖池大小默認 128M，獨立的 MySQL 服務器推薦設置緩沖池大小為總內存的 80%。主要存儲數據頁、索引頁更新緩沖（change buffer）等。

• 預讀機制

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

預讀策略有兩種，為線性預讀（linear read-ahead）和隨機預讀（random read-ahead），其中 InnoDB 默認使用線性預讀，隨機預讀已經基本廢棄。

線性預讀認為如果前面的請求順序訪問當前區（extent）的頁，那么接下來的若干請求也會順序訪問下一個區的頁，并將下一個區加載到 Buffer Pool。在 5.4 版本以后默認開啟，默認值為 56，最大不能超過 64，表示順序訪問 N 個頁后觸發預讀（一個頁16K，一個區1M，一個區最多64個頁，所以最大值64）。

• 換頁算法

與傳統的 LRU 算法不同，因為面臨兩個問題：

1）預讀失效：由于提前把頁放入了緩沖池，但最終 MySQL 并沒有從頁中讀取數據。

要優化預讀失效，則讓預讀失敗的頁停留在緩沖池里的時間盡可能短，預讀成功的頁停留時間盡可能長。具體將 LRU 鏈分代實現，即新生代和老年代（old subList），預讀的頁加入緩沖池時只加入到老年代頭部，只有真正被預讀成功，則再加入新生代。

2）緩沖池污染：當批量掃描大量數據時，可能導致把緩沖池的所有頁都替換出去，導致大量熱數據被換出，MySQL 性能急劇下降。

InnoDB 緩沖池加入了一個老生代停留時間窗口的機制，只有滿足預讀成功并且在老生代停留時間大于該窗口才會被放入新生代頭部。

https://blog.csdn.net/wuhenyouyuyouyu/article/details/93377605

5、什么是 Change Buffer？

?如果每次寫操作，數據庫都直接更新磁盤中的數據，會很占磁盤IO。為了減少磁盤IO，InnoDB在Buffer Pool中開辟了一塊內存，用來存儲變更記錄，為了防止異常宕機丟失緩存，當事務提交時會將變更記錄持久化到磁盤（redo log），等待時機更新磁盤的數據文件（刷臟），用來緩存寫操作的內存，就是Change Buffer

Change Buffer默認占Buffer Pool的25%，最大設置占用50%。

https://www.modb.pro/db/112469

3.3 InnoDB

1、InnoDB 架構設計？

以下主要從內存和線程的角度分析 InnoDB 的架構。

內存中的數據區域劃分：

ref深入理解InnoDB – 架構篇

2、InnoDB 有哪些線程？

線程的作用：

1）負責刷新內存池中的數據，保證緩沖池的內存緩沖的是最近的數據

2）已修改的數據文件刷新到磁盤文件

3）保證數據庫發生異常的情況下InnoDB能恢復到正常狀態。

線程分類：

1）Master Thread

負責將緩沖池中的數據異步刷新到磁盤，保證數據的一致性，包括臟頁的刷新，合并插入緩沖（INSERT BUFFER），UNDO頁的回收等。

2）IO Thread

負責 AIO 請求的回調處理。

3）Purge Thread

事務提交后，undo log 可能不再需要，由 Purge Thread 負責回收并重新分配的這些已經使用的 undo 頁。

4）Page Cleaner Thread

將Master Threader中刷新臟頁的工作移至該線程，如上面說的FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint。

3、什么是 doublewrite？

4、什么是自適應哈希？

InnoDB 會監控對表上各索引頁的查詢執行情況，如發現建立哈希索引可以提升速度，則建立哈希索引，這是過程不需要用戶干預。（默認開啟）

4 事務

1、什么是數據庫的事務？

數據庫的事務是一個不可分割的數據庫操作序列，也是數據庫并發控制的基本單位，其執行的結果必須使數據庫從一種一致性狀態變到另一種一致性狀態。事務是邏輯上的一組操作，要么都執行，要么都不執行。

事務的典型應用場景，如轉賬。

2、什么是事務的四大特性（ACID）？

• 原子性： 事務是最小的執行單位，不允許分割。事務的原子性確保動作要么全部完成，要么完全不起作用
• 一致性： 事務執行前后，數據保持一致，多個事務對同一個數據讀取的結果是相同的
• 隔離性： 并發訪問數據庫時，一個用戶的事務不被其他事務所干擾，各并發事務之間數據庫是獨立的
• 持久性： 一個事務被提交之后。它對數據庫中數據的改變是持久的，即使數據庫發生故障也不應該對其有任何影響。

4、事務的并發問題？

臟讀、幻讀和不可重復讀。

ref 并發事務帶來的問題

5、什么是臟讀、幻讀和不可重復度？

• 臟讀：一個事務讀取到另一個事務尚未提交的數據。 事務 A 讀取事務 B 更新的數據，然后 B 回滾操作，那么 A 讀取到的數據是臟數據。

• 不可重復讀：一個事務中兩次讀取的數據的內容不一致。 事務 A 多次讀取同一數據，事務 B 在事務 A 多次讀取的過程中，對數據作了更新并提交，導致事務 A 多次讀取同一數據時，結果 不一致。

• 幻讀：一個事務中兩次讀取的數據量不一致。 系統管理員 A 將數據庫中所有學生的成績從具體分數改為 ABCDE 等級，但是系統管理員 B 就在這個時候插入了一條具體分數的記錄，當系統管理員 A 改結束后發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。

不可重復讀的和幻讀很容易混淆，不可重復讀側重于修改，幻讀側重于新增或刪除。 解決不可重復讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表。

ref MySQL的四種事務隔離級別

6、事務的隔離級別有哪些？

串行化的隔離級別最高，讀未提交的級別最低，級別越高，則執行效率就越低，所以在選擇隔離級別時應該結合實際情況。

MySQL 支持以上四種隔離級別，默認為 Repeatable read (可重復讀)；而 Oracle 只支持 Serializeble(串行化) 級別和 Read committed(讀已提交) 兩種，其中默認為讀已提交。

ref 事務的隔離級別

7、ACID 特性是如何實現的？

分四個維度去理解，如原子性是 undo 日志，持久性是 redo 日志。（PS 日志具體原理在后續章節講述。）

ref ACID特性的實現原理

5 鎖

1、數據庫鎖的作用以及有哪些鎖？

當數據庫有并發事務的時候，可能會產生數據的不一致，這時候需要一些機制來保證訪問的次序，鎖機制就是這樣的一個機制。即鎖的作用是解決并發問題。

從鎖的粒度劃分，可以將鎖分為表鎖、行鎖以及頁鎖。

• 行級鎖：是鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少數據庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大。

行級鎖開銷大，加鎖慢，且會出現死鎖。但鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。

• 表級鎖：是粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分MySQL引擎支持。

• 頁級鎖：是粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折中的頁級，一次鎖定相鄰的一組記錄。

開銷和加鎖時間界于表鎖和行鎖之間，會出現死鎖。鎖定粒度界于表鎖和行鎖之間，并發度一般。

從使用性質劃分，可以分為共享鎖、排它鎖以及更新鎖。

• 共享鎖（Share Lock）：S 鎖，又稱讀鎖，用于所有的只讀數據操作。

S 鎖并非獨占，允許多個并發事務對同一資源加鎖，但加 S 鎖的同時不允許加 X 鎖，即資源不能被修改。S 鎖通常讀取結束后立即釋放，無需等待事務結束。

• 排他鎖（Exclusive Lock）：X 鎖，又稱寫鎖，表示對數據進行寫操作。

X 鎖僅允許一個事務對同一資源加鎖，且直到事務結束才釋放，其他任何事務必須等到 X 鎖被釋放才能對該頁進行訪問。

使用 select * from table_name for update; 語句產生 X 鎖。

• 更新鎖：U 鎖，用來預定要對資源施加 X 鎖，允許其他事務讀，但不允許再施加 U 鎖或 X 鎖。

當被讀取的頁將要被更新時，則升級為 X 鎖，U 鎖一直到事務結束時才能被釋放。故 U 鎖用來避免使用共享鎖造成的死鎖現象。

ref 數據庫鎖分類和總結

從主觀上劃分，又可以分為樂觀鎖和悲觀鎖。

• 樂觀鎖（Optimistic Lock）：顧名思義，從主觀上認定資源是不會被修改的，所以不加鎖讀取數據，僅當更新時用版本號機制等確認資源是否被修改。

樂觀鎖適用于多讀的應用類型，可以系統提高吞吐量。

• 悲觀鎖（Pessimistic Lock）：正如其名，具有強烈的獨占和排它特性，每次讀取數據時都會認為會被其它事務修改，所以每次操作都需要加上鎖。

2、隔離級別和鎖的關系？

1）在 Read Uncommitted 級別下，讀取數據不需要加共享鎖，這樣就不會跟被修改的數據上的排他鎖沖突；

2）在 Read Committed 級別下，讀操作需要加共享鎖，但是在語句執行完以后釋放共享鎖；

3）在 Repeatable Read 級別下，讀操作需要加共享鎖，但是在事務提交之前并不釋放共享鎖，也就是必須等待事務執行完畢以后才釋放共享鎖；

4）在 SERIALIZABLE 級別下，限制性最強，因為該級別鎖定整個范圍的鍵，并一直持有鎖，直到事務完成。

3、InnoDB 中的鎖算法？

• Record lock：單個行記錄上的鎖
• Gap lock：間隙鎖，鎖定一個范圍，不包括記錄本身
• Next-key lock：record + gap 鎖定一個范圍，包含記錄本身

ref 鎖機制與InnoDB鎖算法

4、什么是快照讀和當前讀？

快照讀就是讀取的是快照數據，不加鎖的簡單 Select 都屬于快照讀。

SELECT * FROM player WHERE ...

當前讀就是讀的是最新數據，而不是歷史的數據。加鎖的 SELECT，或者對數據進行增刪改都會進行當前讀。

SELECT * FROM player LOCK IN SHARE MODE;
SELECT FROM player FOR UPDATE;
INSERT INTO player values ...
DELETE FROM player WHERE ...
UPDATE player SET ...

5、什么是 MVCC 以及實現？

MVCC 的英文全稱是 Multiversion Concurrency Control，中文意思是多版本并發控制，可以做到讀寫互相不阻塞，主要用于解決不可重復讀和幻讀問題時提高并發效率。

其原理是通過數據行的多個版本管理來實現數據庫的并發控制，簡單來說就是保存數據的歷史版本。可以通過比較版本號決定數據是否顯示出來。讀取數據的時候不需要加鎖可以保證事務的隔離效果。

ref MVCC 原理
ref MVCC詳解

6 進階功能

6.1 視圖

6.2 存儲過程

1、什么是存儲過程？

存儲過程是一個預編譯的SQL語句，優點是允許模塊化的設計，就是說只需要創建一次，以后在該程序中就可以調用多次。如果某次操作需要執行多次SQL，使用存儲過程比單純SQL語句執行要快。

2、存儲過程和函數的區別？

1）返回值的區別：函數有1個返回值，而存儲過程是通過參數返回的，可以有多個或者沒有。

2）調用的區別：，函數可以在查詢語句中直接調用，而存儲過程必須單獨調用。

ref 存儲過程（procedure）和函數（Function）的區別

6.3 觸發器

7、集群

7.1 日志

1、MySQL 中有哪些常見日志？

• 重做日志（redo log）：物理日志

作用是確保事務的持久性。 redo 日志記錄事務執行后的狀態，用來恢復未寫入 data file 的已提交事務數據。

• 回滾日志（undo log）：邏輯日志

作用是保證數據的原子性。 保存了事務發生之前的數據的一個版本，可以用于回滾，同時可以提供多版本并發控制下的讀（MVCC），也即非鎖定讀。

• 二進制日志（binlog）：邏輯日志

常用于主從同步或數據同步中，也可用于數據庫基于時間點的還原。

• 錯誤日志（errorlog）

記錄著 MySQL 啟動和停止，以及服務器在運行過程中發生的錯誤的相關信息。在默認情況下，系統記錄錯誤日志的功能是關閉的，錯誤信息被輸出到標準錯誤輸出。

• 普通查詢日志（general query log）

記錄了服務器接收到的每一個命令，無論命令語句是否正確，因此會帶來不小開銷，所以也是默認關閉的。

• 慢查詢日志（slow query log）

記錄執行時間過長和沒有使用索引的查詢語句（默認 10s），同時只會記錄執行成功的語句。

• 中繼日志（relay log）

在從節點中存儲接收到的 binlog 日志內容，用于主從同步。

ref MySQL中的幾種日志了解

7.2 主從復制

1、什么是主從復制？

主從復制是用來建立一個與主數據庫完全一樣的數據庫環境，即從數據庫。主數據庫一般是準實時的業務數據庫。

2、主從復制的作用？

• 讀寫分離，使數據庫能支撐更大的并發。
• 高可用，做數據的熱備，作為后備數據庫，主數據庫服務器故障后，可切換到從數據庫繼續工作，避免數據丟失。

3、主從復制的架構？

• 一主一從或一主多從

在主庫的請求壓力非常大時，可通過配置一主多從復制架構實現讀寫分離，把大量對實時性要求不是很高的請求通過負載均衡分發到多個從庫上去讀取數據，降低主庫的讀取壓力。而且在主庫出現宕機時，可將一個從庫切換為主庫繼續提供服務。

• 主主復制

雙主復制架構適用于需要進行主從切換的場景。 兩個數據庫互為主從，當主庫宕機恢復后，由于它還是原來從庫（現在主庫）的從機，所以它還是會復制新的主庫上的數據。那么無論主庫的角色怎么切換，原來的主庫都不會脫離復制環境。

• 多主一從（5.7 開始支持）
• 聯級復制

因為每個從庫在主庫上都會有一個獨立的 Binlog Dump 線程來推送 binlog 日志，所以隨著從庫數量的增加，主庫的 IO 壓力和網絡壓力也會隨之增加，這時，聯級復制架構應運而生。

聯級復制架構只是在一主多從的基礎上，再主庫和各個從庫之間增加了一個二級主庫 Master2，這個二級主庫僅僅用來將一級主庫推送給它的 Binlog 日志再推送給各個從庫，以此來減輕一級主庫的推送壓力。

4、主從復制的實現原理？

數據庫有個 binlog 二進制文件，記錄了數據可執行的所有 SQL 語句。主從同步的目標就是把主數據庫的 binlog 文件中的 SQL 語句復制到從數據庫，讓其在從數據的 relaylog 文件中再執行一次這些 SQL 語句即可。

具體實現需要三個線程：

• binlog 輸出線程：每當有從庫連接到主庫的時候，主庫都會創建一個線程然后發送 binlog內 容到從庫。

在從庫里，當復制開始的時候，從庫就會創建兩個線程進行處理：

• 從庫 IO 線程：當 START SLAVE 語句在從庫開始執行之后，從庫創建一個 IO 線程，該線程連接到主庫并請求主庫發送 binlog 里面的更新記錄到從庫上。從庫 IO 線程讀取主庫的 binlog 輸出線程發送的更新并拷貝這些更新到本地文件，其中包括 relaylog 文件。

• 從庫 SQL 線程：從庫創建一個 SQL 線程，這個線程讀取從庫 IO 線程寫到 relaylog 的更新事件并執行。

ref MySQL主從復制面試之和原理

5、什么是異步復制和半同步？

MySQL 的主從復制有兩種復制方式，分別是異步復制和半同步復制：

• 異步復制

MySQL 默認的主從復制方式就是異步復制，因為 Master 根本不考慮數據是否達到了 Slave，或 Slave 是否成功執行。

如過需要實現完全同步方式，即 Master 需要等待一個或所有 Slave 執行成功后才響應成功，那集群效率可想而知。故 MySQL 5.6 之后出現了一種折中的方式——半同步。

• 半同步復制

一主一從，一主多從情況下，Master 節點只要確認至少有一個 Slave 接受到了事務，即可向發起請求的客戶端返回執行成功的操作。同時 Master 是不需要等待 Slave 成功執行完這個事務，Slave 節點接受到這個事務，并成功寫入到本地 relay 日志中就算成功。

另外，在半同步復制時，如果主庫的一個事務提交成功了，在推送到從庫的過程當中，從庫宕機了或網絡故障，導致從庫并沒有接收到這個事務的Binlog，此時主庫會等待一段時間（這個時間由rpl_semi_sync_master_timeout的毫秒數決定），如果這個時間過后還無法推送到從庫，那 MySQL 會自動從半同步復制切換為異步復制，當從庫恢復正常連接到主庫后，主庫又會自動切換回半同步復制。

半同步復制的“半”體現在，雖然主從庫的Binlog是同步的，但主庫不會等待從庫執行完Relay-log后才返回，而是確認從庫接收到Binlog，達到主從Binlog同步的目的后就返回了，所以從庫的數據對于主庫來說還是有延時的，這個延時就是從庫執行Relay-log的時間。所以只能稱為半同步。

refMySQL主從復制（異步復制與半同步復制）

6、主從中常見問題以及解決？

問題
1）主庫宕機后，數據可能丟失。

2）從庫只有一個sql Thread，主庫寫壓力大，復制很可能延時。

解決
1）半同步復制：確保事務提交后 binlog 至少傳輸到一個從庫 ，解決數據丟失的問題。

2）并行復制：從庫多線程apply binlog，解決從庫復制延遲的問題。

8 SQL

8.1 語法

SQL 是一門 ANSI 標準計算機語言，用來訪問和操作數據庫系統。通常 SQL 語句可以分為兩類：

• 數據操作語言（DML）：SELECT、DELETE、INSERT INTO、UPDATE
• 數據定義語言（DDL）：CREATE、DROP、ALTER

實踐中，還有一種

• 數據控制語言（Data Control Language）：GRANT，REVOKE，COMMIT，ROLLBACK

1、常見的聚合查詢？

使用聚合函數的查詢就是聚合查詢。所有的聚合函數（UDAF）都應該支持分組查詢，內置的聚合函數有：

sum(列名) 求和  　　　　
max(列名) 最大值 　　　　
min(列名) 最小值 　　　　
avg(列名) 平均值 　　　　
first(列名)   第一條記錄   
last(列名)    最后一條記錄  
count(列名)   統計記錄數   注意和count(*)的區別

ref SQL 聚合查詢

2、幾種關聯查詢？

1） 內連接（自然連接）：只返回匹配的行，如 Inner Join、Union Join。

2）外連接：返回一個表的全集，如 Left、Right、Full 和 Cross。

ref SQL多表查詢

3、Where 和 Having 的區別？

where 子句的作用是在對查詢結果進行分組前，將不符合條件的行去掉，即在分組之前過濾數據，where條件中不能包含聚組函數，使用where條件過濾出特定的行。

having 子句的作用是篩選滿足條件的組，即在分組之后過濾數據，條件中經常包含聚組函數，使用having 條件過濾出特定的組，也可以使用多個分組標準進行分組。

總結一下條件的過濾順序：on->join->where->group by->having。

4、SQL 關鍵字的執行順序？

ref SQL的執行順序

5、In 和 Exists 的區別？

in 適合內表比外表數據小的情況，exists 適合內表比外表數據大的情況。如果查詢的內外表大小相當，則二者效率差別不大。

ref SQL語句中exists和in的區別

6、Union 和 Union All 的區別？

• Union：對兩個結果集進行并集操作，不包括重復行，同時進行默認規則的排序

• Union All：對兩個結果集進行并集操作，包括重復行，不進行排序

Union 因為要進行重復值掃描，所以效率低。如果合并沒有刻意要刪除重復行，那么就使用Union All。

ref union和union all的區別

7、Drop、Delete 和 Truncate 的區別？

雖然通過 delete、truncate、drop 這三個關鍵字都可以用來刪除數據，但場景不同。

從執行速度上講：drop > truncate >> DELETE。

區別	Delete	Truncate	Drop
SQL類型	屬于DML	屬于DDL	屬于DDL
支持回滾	支持	不支持	不支持
刪除內容	表結構還在，刪除表的全部或部分數據	表結構還在，刪除表中所有數據	從數據庫中刪除表的所有數據，包括索引和權限
執行速度	速度慢，需要逐行刪除	速度快	速度最快

ref delete、truncate、drop的區別有哪些，該如何選擇

8.2 優化

1、一條 SQL 是如何執行的？

ref sql執行過程

2、如何判斷 SQL 是否走了索引？

EXPLAIN 命令是查看查詢優化器如何決定執行查詢的主要方法，使用 EXPLAIN 只需在查詢語句開頭增加 EXPLAIN 這個關鍵字即。

其結果中的幾個重要參數：

• id

ID 代表執行 select 子句或操作表的順序，如果包含子查詢，則會出現多個 ID。值越大，優先級越高，越先被執行。值相同的按照由上至下的順序執行。

• select_type（查詢類型）

查詢類型主要用于區別普通查詢、聯合查詢以及子查詢等復雜查詢。

• table

• type

查詢掃描情況，最好到最差依次是：system>const>eq_ref>ref>range>index>All，一般情況下至少保證達到 range 級別，最好能達到 ref。

• possible_keys

顯示可能應用在這張表中的索引，一個或多個。查詢到的索引不一定是真正被使用。

• key

實際使用的索引，如果為 null 則表示沒有使用索引。因此會出現 possible_keys 列有可能被用到的索引，但是 key 列為 null。

• key_len

表示索引中使用的字節數，在不損失精確性的情況下長度越短越好。key_len 顯示的值為索引字段的最大可能長度，并非實際使用長度。即 key_len 是根據表定義計算而來。

• ref

顯示索引的哪一列被使用了,如果可能的話是一個常數,哪些列或常量被用于查找索引列上的值。

• rows

根據表統計信息及索引選用情況，估算出找到所需的記錄所需要讀取的行數。

• Extra

ref EXPLAIN的參數解析及簡單應用

3、索引失效的幾種情況？

1）like 以%開頭，索引無效；當like前綴沒有%，后綴有%時，索引有效；

2）or 語句前后沒有同時使用索引。當 or 左右查詢字段只有一個是索引，該索引失效，只有左右查詢字段均為索引時，才會生效；

3）聯合索引不使用第一列，索引失效；

4）數據類型出現隱式轉化。如 varchar 不加單引號的話可能會自動轉換為 int 型，使索引無效，產生全表掃描；

5）在索引列上使用 IS NULL 或 IS NOT NULL操作。最好給列設置默認值。

6）在索引字段上使用not，<>，!=。不等于操作符是永遠不會用到索引的，因此對它的處理只會產生全表掃描。 優化方法： key<>0 改為 key>0 or key<0。

7）對索引字段進行計算操作、字段上使用函數。

8）當 MySQL 覺得全表掃描更快時（數據少）;

ref Mysql索引查詢失效的情況

4、Where 子句如何優化？

5、超大分頁或深度分頁如何處理？

說道 MySQL 的分頁，我們首先想到的就是 offset、limit 操作，但隨著頁數的增加，查詢性能指數級增大。

這是由于 MySQL 并不是跳過 offset 的行數，而是取 offset + limit 行，然后丟棄前 offset 行，返回 limit 行，當offset特別大的時候，效率就非常的低下。

此處我們就可以采用覆蓋索引+延遲關聯技術來減少偏移量的定位進行優化：

##查詢語句
select id from product limit 10000000, 10
##優化方式一
SELECT * FROM product WHERE ID > =(select id from product limit 10000000, 1) limit 10
##優化方式二
SELECT * FROM product a JOIN (select id from product limit 10000000, 10) b ON a.ID = b.id

ref mysql優化：覆蓋索引（延遲關聯）

6、大表查詢如何優化？

可以從分庫分表、讀寫分離以及緩存三個維度分別闡述。

8.3 實踐

幾種常見名次問題

ref MySQL排名函數實現