MySQL 八股文【持續更新ing】

---

前言

一名27屆后端開發選手的八股學習日常總結。

---

一、MySQL的存儲引擎有哪些？他們之間有什么區別？

• InnoDB存儲引擎
  特點：
  • 支持事務（ACID）（通過begin、commit、rollback 控制）
    • A（Atomicity）原子性
    • C（Consistency）一致性
    • I（Isolation）隔離性
    • D（Durability）持久性
  • 支持行級鎖（從鎖粒度來劃分）
    • 通過對索引項加鎖來實現行級鎖、如果查詢條件沒有建立索引則會升級為表級鎖。
  • 外鍵約束
  • 支持聚簇索引、提高檢索效率
    • 默認用表的主鍵建立聚簇索引
    • 沒有主鍵則去查找唯一非空屬性建立聚簇索引
    • 沒有唯一非空屬性則使用隱藏的Row_id建立聚簇索引
    • B+樹實現
    • 聚簇索引的B+樹葉子結點存放數據本身，非葉子節點不存放數據（進存放索引，例如：主鍵id）
  • 支持MVCC（多版本并發控制）來提高數據庫的并發性能，保證事務的ACID。
  • 提供undolog和 redolog來實現回滾和崩潰恢復
• MyISAM存儲引擎
  特點：
  • 不支持事務、外鍵
  • 僅支持表級鎖（不支持行級鎖）
  • 支持非聚簇索引，不支持聚簇索引
    • 也是B+樹實現
    • 非聚簇索引的B+樹葉子結點存放數據行地址（索引文件和數據文件分開存放）
  • 適合 讀取多、更新少的場景，例如數據倉庫
  • 具有較高的讀性能和較快的表級鎖定
• Memory存儲引擎
  • 是內存存儲引擎，數據存儲在內存中，服務重啟數據會丟失
  • 僅支持hash索引（存儲在內存中）
  • 適用于臨時數據存儲或快速緩存
  • 存放一些公共的、常用的、且不經常發生改變的數據
• NDB（NDBCluster）
  • 支持高可用性和數據分布，適合大規模分布式應用
  • 提供行級鎖和自動分區

二、MySQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么區別？

1.InnoDB 中的聚簇索引

• 聚簇索引的B+樹的葉子節點中存放的是數據行、非葉子節點存放的的是索引
• 葉子結點包含所有的數據
• 每一個表只能有一個聚簇索引，通常是主鍵索引，如果沒有設定主鍵，回去選擇一個唯一非空列作為索引去建立聚簇索引，如果也不存在唯一非空列，則使用隱藏的Row_id作為索引
• 適合于范圍查詢和排序

2.InnoDB 中的非聚簇索引

• InnoDB中的非聚簇索引的索引葉子節點存儲的是數據行的主鍵和對應的索引列，需要通過主鍵進行回表查詢完整的數據行
• 一個表中可以有多個非聚簇索引（這里又稱為 非主鍵索引、輔助索引、二級索引），適合快速查找特定列的數據

三、MySQL的索引類型有哪些？

按照數據結構劃分：

• B+樹索引
• hash索引
• 全文索引（倒排索引）

從常見的基于 InnoDB B+樹 索引角度來看，分為：

• 聚簇索引
• 非聚簇索引

按照索引性質劃分：

• 普通索引（二級索引、輔助索引）
• 主鍵索引
• 聯合索引
• 唯一索引
• 全文索引

四、為什么MySQL選擇使用 B+ 樹作為索引結構？

• 高效的查詢性能
  • B+ 樹是一種自平衡樹，每個葉子結點到根節點的路徑長度相同，B+ 樹在插入和刪除節點時會進行分裂和合并操作，以保持樹的平衡，但是他有一定的冗余節點，使得刪除的時候樹結構的變化小，更高效
  • 查找、插入、刪除等操作的時間復雜度為O（logn）
• 樹的高度增長不會過快，這樣可以減少查詢磁盤的IO次數
  • B+ 樹不像紅黑樹，數據越多樹的高度增長就越快。B+ 樹是多叉樹，非葉子節點僅保存主鍵或索引值和頁面指針，使得每一頁能容納更多的記錄，因此內存中可以放更多索引
• 范圍查詢能力強
  • B+ 樹特別適合范圍查詢，因為葉子結點通過雙向鏈表連接，從根節點定位到葉子結點查找的范圍的起點之后只需要順序掃描鏈表即可完成后續的數據遍歷

五、MySQL索引的最左匹配原則是什么？

MySQL索引的最左匹配原則指的是：在使用聯合索引時，查詢條件必須按照索引建立的順序去篩選，例如建立索引的順序是（a、b、c），那么作為條件查詢的時候應該盡量保持where a = ？ and b = ? and c ...。

底層原理：

MySQL建立的聯合索引中，是按照順序建立的，例如聯合索引（first_name，last_name，age）的排列方式大致示意圖：

                          (Alice, Black, 35)|-------------------------------------------------------------|                |                     |                |(Alice, Brown, 25)  (Alice, Smith, 30) (Bob, Smith, 20) (Bob, White, 19)   

六、MySQL 三層 B+ 樹能存多少數據？

在MySQL的InnoDB存儲引擎中，B+樹默認數據頁大小是一頁 16kB。

聚簇索引中，每一個非葉子結點中存放：

• 索引鍵（一般取 主鍵id：bigInt --> 8B）、每個指針（6B），那么每個中間節點可以存放（16kB /（6B + 8B）） = （16 * 1024 B） / （14B）= 1170
• 即每一層的中間節點都可以指向1170個子節點。
• 最后葉子結點的個數為：1170 * 1170 * 16 = 21902400，一棵三層的B+ 樹在MySQL中可以存儲大約2000萬條記錄。

圖示：

七、MySQL中的回表是什么

回表一般是指在使用InnoDB引擎的二級索引（非聚簇索引）作為條件進行查詢時，由于二級索引中只存儲了索引字段的值和對應的主鍵值，無法得到其他數據，如果要查詢數據行中的其他數據，需要根據主鍵去聚簇索引中查找實際的數據行，這個過程叫做回表。

注意：僅僅是指InnoDB中的非聚簇索引中，MyISAM中的非聚簇索引是數據文件和索引文件分開存放，不存在回表的概念。

八、MySQL中使用索引一定有效嗎？如何排查索引效果？

• 不一定，索引可能會失效，索引常見失效情況如下：
  • 查詢條件中破壞了最左匹配原則（沒有按照索引建立順序且使用了范圍查詢，或者使用了沒有建立索引的列）
  • 索引列參與了計算或者函數
  • not in 和 not exists使用不當
  • 某些情況下的數據類型強制轉換
  • 。。。
• 一般使用Explain關鍵字去查看索引使用情況
  • explain select ....
    其中下面這些列可以看出走索引的情況
  • type：表示查詢時數據訪問方式：性能從優到劣排序如下：
    • system > const > eq_ref > ref > range > index > all
      • system：表中僅一行數據（系統表）。
      • const：通過主鍵或唯一索引查到一行數據（如 WHERE id = 1）。
      • eq_ref：唯一索引關聯（如 JOIN 時使用主鍵匹配）。
      • ref：非唯一索引查找（可能返回多行）。
      • range(優化目標)：索引范圍掃描（如 BETWEEN、IN）。
      • index：全索引掃描（遍歷索引樹）。
      • ALL：全表掃描（需優化）。
  • key：實際使用到的索引
    • 若為null，則表示未使用索引
  • key_len：索引使用的字節數，反應索引字段的利用率
    • 可以根據建立的索引列屬性計算出哪些索引被用上了
  • Extra：表示附加信息，反應執行細節。
    • Using where：使用 WHERE 過濾數據。
    • Using index：覆蓋索引（無需回表）。
    • Using temporary：使用臨時表（常見于排序或分組）。
    • Using filesort：外部排序（需優化索引）。
    • Using join buffer：使用連接緩沖區（關聯字段無索引）。

九、在MySQL中建索引時需要注意哪些事項？

• 索引不是越多越好，索引會占用存儲空間，每次修改都需要維護索引中的數據，也會耗時
• 字段值大量重復的字段不要建立索引，例如：性別字段
• 長字段不應該建立索引，因為占據的內存大，掃描的時候比較耗時，可以考慮建立前綴索引
• 當表的修改頻率遠大于查詢頻率是，應該考試是否建立索引
• 對于需要頻繁作為條件查詢的字段應該建立索引，且可以考慮優先建立聯合索引，減少索引數量，利用覆蓋索引提高效率，避免回表查詢
• 對經常在order by、group by、distinct 后面的字段建立索引，加快效率

十、MySQL中的索引數量是否越多越好？為什么？

索引并不是越多越好。

• 每建立一個索引都需要新建一個B+樹，所以如果索引過多會占據很大的空間
• 對于索引的更新操作會導致B+樹進行節點的修改、旋轉，如果修改操作較多，比較耗時，并且B+樹可能會有頁分裂、合并等操作，時間開銷上面會更大

十一、如何使用MySQL的Explain語句進行查詢分析？

直接在查詢語句前面加上 Explain關鍵字即可

輸出的列表示的含義：
1. id

• 作用：標識查詢中 SELECT 的執行順序。
• 規則：
  • id 相同：執行順序從上到下（如多表關聯查詢）。
  • id 不同：值越大優先級越高，越先執行（如子查詢）。
  • id 為 NULL：表示是其他查詢的聯合結果（如 UNION）。

---

2. select_type

• 作用：表示 SELECT 的類型，反映查詢的復雜度。
• 常見值：
  • SIMPLE：簡單查詢（無子查詢或 UNION）。
  • PRIMARY：外層主查詢。
  • SUBQUERY：子查詢中的第一個 SELECT。
  • DERIVED：派生表（例如 FROM 子句中的子查詢）。
  • UNION：UNION 中的第二個或之后的 SELECT。
  • UNION RESULT：UNION 的結果集。

---

3. type

• 作用：數據訪問方式，性能從優到劣排序如下：
  • system：表中僅一行數據（系統表）。
  • const：通過主鍵或唯一索引查到一行數據（如 WHERE id = 1）。
  • eq_ref：唯一索引關聯（如 JOIN 時使用主鍵匹配）。
  • ref：非唯一索引查找（可能返回多行）。
  • range(優化目標)：索引范圍掃描（如 BETWEEN、IN）。
  • index：全索引掃描（遍歷索引樹）。
  • ALL：全表掃描（需優化）。

---

4. table

• 作用：顯示當前行操作的表名。
• 特殊值：
  • <derivedN>：派生表（來自 id 為 N 的子查詢）。
  • <unionM,N>：UNION 結果（由 id 為 M 和 N 的查詢合并）。

---

5. possible_keys

• 作用：可能用到的索引列表。
• 注意：若為 NULL，說明沒有適合的索引，需檢查表結構或查詢條件。

---

6. key

• 作用：實際使用的索引。
• 注意：
  • 可能不在 possible_keys 中（優化器可能選擇更優索引）。
  • 若為 NULL，表示未使用索引（全表掃描）。

---

7. key_len

• 作用：索引使用的字節數，反映索引字段的利用率。
• 計算規則：
  • 字符串類型：長度 × 字符集字節數（如 VARCHAR(255) UTF8MB4 最大長度為 255 × 4 + 2 = 1022）。
  • 數值/時間類型：固定長度（如 INT 為 4 字節）。

---

8. ref

• 作用：顯示與索引比較的列或常量。
• 常見值：
  • const：常量值（如 WHERE id = 1）。
  • 列名（如 WHERE t1.col = t2.col）。

---

9. rows

• 作用：預估需要掃描的行數（越小越好）。
• 注意：基于統計信息估算，可能與實際值有偏差。

---

10. partitions

• 作用：匹配的分區（若表未分區則為 NULL）。
• 適用場景：針對分區表的查詢優化。

---

11. filtered

• 作用：查詢條件過濾后剩余數據的百分比。
• 用途：估算與其他表關聯時需處理的行數（rows × filtered）。

---

12. Extra

• 作用：附加信息，反映執行細節。
• 常見值：
• Using where：使用 WHERE 過濾數據。
• Using index：覆蓋索引（無需回表）。
• Using temporary：使用臨時表（常見于排序或分組）。
• Using filesort：外部排序（需優化索引）。
• Using join buffer：使用連接緩沖區（關聯字段無索引）。

十二、MySQL中如何進行SQL調優？

通過慢查詢日志找到執行速度比較慢的查詢語句然后利用explain分析查詢語句的執行計劃，優化查詢語句。

• 合理設計索引，多用聯合索引來代替單個索引，盡量走索引覆蓋查詢，避免回表次數
• 避免索引失效的幾種情況
  • 避免使用 select *
  • 遵循最左匹配原則來查詢
  • 避免索引列上的強制類型轉換
  • 避免使用前綴模糊查詢 like ‘%…’
  • 避免索引列參與計算
• 利用Redis緩存來優化MySQL效率，將頻繁訪問的數據放到緩存中減少數據庫的壓力。

十三、詳細描述MySQL的B+樹中查詢數據的全過程

首先講一下B+樹的基本結構：

• 非葉子節點：B+樹的非葉子節點存儲的是鍵值（索引列）和指向子節點的指針
• 葉子節點：B+ 樹的葉子節點存儲實際的數據行（如果是輔助索引的話這里存儲的是主鍵id，需要回表查詢，這里忽略這種情況，我們默認是查聚簇索引）。所有的葉子節點包含所有的數據，且用雙向鏈表連接。
  查找過程：
• 從根節點出發（根節點常駐內存），根據比較數據鍵值和節點中存儲的索引鍵值，確定數據落在它的哪一個子節點中（也就是哪個區間中）
• 確定分支后，將該子節點讀入內存在進行相同的比較去尋找該數據在下一層中的哪個節點中。
• 定位到葉子節點后，葉子節點存儲實際的數據行記錄，但是一頁又16KB大小，存儲的數據行很多
• 葉子節點中數據行以組的形式劃分，利用頁目錄結構，通過二分查找定位到對應的組
• 再利用鏈表遍歷找到對應的數據行

十四、MySQL中 count(*)、count(1)、count(字段名) 有什么區別？

count(*)：是統計表中所有行的行數，包括null值。性能最高
count(1)：也是統計數據的行數，包括null，性能和上面那個差不多
count(字段名)：統計該字段非null的行數。效率略差

對于count(字段名)：該查詢就是全表掃描（如果對應的字段沒有索引，如果有索引則使用索引），正常情況下還要判斷字段是否是null值。所以效率稍微低。

十五、MySQL 中 varchar 和 char 有什么區別？

• varchar(n)：可變長度的字符串。存儲的字符串長度與實際長度相等，并且在存儲數據時會額外增加1~2個字節（字符串長度超過255，則使用兩個字節）用于存儲字符串的長度信息。
• char(n)：固定長度的字符串，一般用于存儲固定長度的數據，例如手機號。MySQL也會在字符串的末尾填充空格已達到指定長度

理論上來講 char 會比 varchar 快，因為varchar長度不固定，處理需要多一次運算。不過除非是像手機號這種固定長度的數據才會用char，其他都建議使用varchar。

varchar 支持的最大長度：
因為最大行長度是 65535字節，如果允許為null，則需要1 bit 標記是否未 null（MySQL 對于null值是額外用一個null值列表存儲的。當前只有一個varchar字段，則需要用 1 個 bit 標記它的null值組成null值列表），又因為varchar列的長度是可變的，需要使用1~2個字節（字符串長度超過255，則使用兩個字節）用于存儲字符串的長度信息。
所以支持的最大長度是：65535 - 2 = 65533，如果允許為 null 則是 65532。
以上是字節數，實際的字符又取決于使用的字符集：

• UTF-8字符集：每個字符最大占用3字節。最大字符數大約：21844（65533 ÷ 3）
• UTF-16字符集：每個字符通常占用2字節。最大字符數大約：32766（65533 ÷ 2）
• varchar(n)：其中的 n 表示的是 字符的個數，而不是字節數。