MySQL的EXPLAIN有什么作用？

面試官您好，EXPLAIN命令是我在進行SQL性能優化時，使用最頻繁、也最重要的一個工具。

它的核心作用可以一句話概括：模擬MySQL的查詢優化器來執行一條SQL語句，并向我們展示出它最終決定采用的“執行計劃”（Execution Plan）。

通過EXPLAIN，我們可以在不真正執行查詢的情況下，就能“洞察”到MySQL內部打算如何處理我們的SQL。這就像我們拿到了一份“作戰地圖”，可以清晰地看到：

• 查詢會訪問哪些表？
• 訪問的順序是怎樣的？
• 是否使用了索引？如果用了，是哪個索引？
• 數據是如何被掃描和過濾的？
• 是否存在一些潛在的性能瓶頸，比如全表掃描、文件排序等？

如何使用？

非常簡單，只需要在我們的SELECT, UPDATE, DELETE, INSERT等語句前，加上EXPLAIN關鍵字即可。

EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY create_time;

如何解讀EXPLAIN的輸出？—— 關注核心字段

EXPLAIN的輸出結果是一張表，里面包含了很多列。在進行性能分析時，我會重點關注以下幾個最關鍵的字段：

• 1. type (訪問類型) —— 這是最重要的字段，沒有之一

  • 它描述了MySQL是如何查找表中數據的。它的性能從好到壞，依次是：
    • system > const > eq_ref > ref > range > index > ALL
  • 我們的優化目標：至少要讓查詢達到 range 級別，最好的情況是 ref 或 eq_ref。
  • ALL：這是一個災難信號，它表示MySQL正在進行全表掃描。如果在大表上看到ALL，就必須立刻進行優化，通常是需要添加合適的索引。

• 2. possible_keys 和 key

  • possible_keys：顯示MySQL認為可能可以用于這個查詢的索引列表。
  • key：顯示MySQL最終決定使用的那個索引。如果這一列是NULL，就說明沒有使用任何索引。
  • 作用：這兩列可以幫助我們判斷我們設計的索引是否被優化器采納了。

• 3. key_len (索引長度)

  • 它表示實際使用了索引的字節數。這個值可以幫助我們判斷聯合索引被利用了多少。
  • 作用：比如，一個聯合索引(a, b, c)，如果key_len只等于a列的長度，就說明查詢只用到了索引的第一個前綴。我們可以通過計算來判斷聯合索引是否被充分利用。

• 4. rows (預估掃描行數)

  • 這是優化器估算的，為了找到目標數據，需要掃描的行數。
  • 作用：這個值越小越好。如果這個值非常大，即使type不是ALL，也可能意味著索引的區分度不高，查詢效率低下。

• 5. Extra (額外信息) —— 包含了大量的“壞味道”

  • 這一列提供了非常多關于查詢優化的重要提示。如果看到以下這些值，通常都意味著需要進行優化：
    • Using filesort：這是一個嚴重的性能問題。它表示MySQL無法利用索引來完成排序（ORDER BY），只能在內存或磁盤上進行額外的文件排序操作。
    • Using temporary：這同樣是一個性能瓶頸。它表示MySQL為了處理查詢（比如GROUP BY），需要創建一個臨時表。
    • Using where：表示在存儲引擎層返回數據后，MySQL的Server層還需要進行額外的WHERE條件過濾。如果配合 Using index 出現，說明索引下推（ICP）生效了，是好事。但如果單獨出現，可能意味著索引利用不充分。
  • 最好的情況：
    • Using index：這是一個絕佳的信號，它表示查詢命中了 “覆蓋索引”。MySQL無需回表，只通過掃描索引樹就獲取了所有需要的數據，性能是最佳的。

總結一下，EXPLAIN是MySQL提供給我們的一個強大的“X光機”。在編寫任何可能涉及性能問題的SQL時，我都會先用EXPLAIN來“透視”一下它的執行計劃，通過解讀type, key, rows, Extra等關鍵指標，來診斷并優化潛在的性能瓶瓶頸。這是一個數據庫開發和運維人員必須掌握的核心技能。

---

給你張表，發現查詢速度很慢，你有哪些解決方案

面試官您好，當遇到一個慢查詢問題時，我會遵循一個系統性的排查和優化流程，從最簡單、成本最低的SQL和索引層面入手，逐步深入到更復雜的架構層面。

我的優化思路，大致可以分為以下幾個層次：

第一層：診斷與分析 —— “找到問題的根源”

這是所有優化的起點。首先，我需要準確地定位到是哪條SQL慢，以及它為什么慢。

1. 開啟并分析慢查詢日志（Slow Query Log）：我會配置slow_query_log和long_query_time，來捕獲所有執行時間超過閾值的SQL，這是發現慢查詢最直接的手段。
2. 使用EXPLAIN分析執行計劃：我會對定位到的慢SQL，立即執行EXPLAIN命令。這是最核心的診斷工具。我會重點關注：
   • type列：是不是ALL（全表掃描）。
   • key列：是否用上了正確的索引。
   • rows列：預估掃描的行數是不是過大。
   • Extra列：是否出現了Using filesort（文件排序）或Using temporary（臨時表）這樣的性能殺手。

第二層：SQL與索引層面的優化 —— “成本最低、見效最快”

在分析出問題后，我首先會嘗試在SQL和索引層面進行優化，因為這通常是成本最低、改動最小的。

1. 索引優化：

   • 創建合適的索引：根據EXPLAIN的結果，如果發現是全表掃描，我會為WHERE子句、JOIN的關聯字段、ORDER BY的排序列創建或調整索引。
   • 設計高效的聯合索引：對于多條件的查詢，我會優先創建聯合索引，并遵循“區分度高、常用、等值查詢的列放前面”的原則來設計字段順序。
   • 利用覆蓋索引：我會嘗試通過調整索引，讓查詢命中覆蓋索引，從而徹底避免回表，這是巨大的性能提升。

2. SQL語句改寫 (避免索引失效)：

   • 我會嚴格檢查SQL寫法，確保沒有觸犯索引失效的規則，比如：
     • 不在索引列上使用函數或進行計算。
     • 避免隱式類型轉換。
     • LIKE查詢保證是右模糊（'abc%'）。
     • 謹慎使用OR和!=。

3. 查詢邏輯優化：

   • 避免SELECT *：只查詢業務真正需要的列，減少數據傳輸量，也更容易命中覆蓋索引。
   • 優化JOIN查詢：確保遵循“小表驅動大表”的原則，并且被驅動表的關聯字段上必須有索引。如果業務允許，甚至可以考慮通過冗余字段來消除JOIN。
   • 優化深分頁問題：對于LIMIT offset, count這樣的大偏移量分頁，將其改寫成基于“書簽”的查詢，比如WHERE id > (last_page_max_id) LIMIT count，效率會高得多。

第三層：數據庫與表結構層面的優化 —— “當單表成為瓶頸”

如果SQL和索引層面已經優化到極致，但性能依然不達標，那可能就是表本身的設計或數據量出了問題。

1. 表結構優化：
   • 如果一個表字段過多，我會考慮進行垂直拆分，將冷、熱數據分離到不同的表中，減小核心表的大小。
2. 分庫分表：
   • 當單表的數據量達到千萬甚至上億級別，讀寫壓力巨大時，就需要進行水平拆分。將一張大表，按照某個規則（如用戶ID哈希、時間范圍等）切分到多個表甚至多個數據庫實例中，將壓力分散開。

第四層：架構層面的優化 —— “引入外部力量”

最后，如果數據庫層面的壓力依然很大，我們就需要跳出數據庫，從整個應用架構來思考。

• 引入緩存：
  • 我會使用Redis等緩存技術，將熱點數據、或者一些計算成本高但不常變化的查詢結果緩存起來。
  • 大量的讀請求會直接命中緩存，無需再訪問數據庫，這能極大地降低數據庫的負載。
  • 當然，這也會引入緩存與數據庫雙寫一致性的挑戰，需要采用合適的策略（如Cache-Aside Pattern，先更新DB再刪緩存）來保證。

通過這樣一套從微觀到宏觀、層層遞進的優化策略，絕大多數的慢查詢問題都能得到有效的解決。

---

如果EXPLAIN用到的索引不正確的話，有什么辦法干預嗎？

面試官您好，您提出的這個問題非常好，它觸及了我們在SQL優化中一個真實且可能遇到的挑戰：MySQL查詢優化器并非100%完美，它有時確實會“犯錯”，選擇一個并非最優的索引。

當EXPLAIN的結果顯示優化器選錯了索引時，我們確實有辦法進行干預。最直接的辦法，就是使用 FORCE INDEX。

第一步：診斷病因 —— 為什么優化器會選錯？

在強制干預之前，我首先會嘗試去理解為什么優化器會做出錯誤的選擇。這通常是由于它的成本估算出現了偏差。常見的原因有：

1. 統計信息不準確或陳舊：

   • MySQL優化器是基于表的統計信息（如行數、鍵的基數/區分度等）來估算成本的。
   • 如果表經過了大量的增刪改操作，而統計信息又沒有及時更新，那么優化器就可能基于過時的數據，做出了錯誤的判斷。

2. 對數據分布的理解有偏差：

   • 優化器可能假設數據是均勻分布的，但實際上數據的分布可能非常不均勻（數據傾斜）。這會導致它對掃描行數的估算出現巨大偏差。

3. 優化器自身的局限性：

   • 在一些極其復雜的查詢中（比如多表JOIN、復雜的子查詢），優化器的成本模型可能無法完美地評估所有可能的執行路徑，從而選擇了一個次優的計劃。

第二步：選擇合適的干預手段

在了解了可能的原因后，我會根據情況選擇不同的干預手段，從“治本”到“治標”：

• 1. 治本之法：更新統計信息與優化索引

  • ANALYZE TABLE：這是我的首選。我會先嘗試執行ANALYZE TABLE a_table;來強制更新表的統計信息。很多時候，僅僅是更新了統計信息，優化器在下一次執行時就會“茅塞頓開”，自動選擇正確的索引了。這是一種最根本、最優雅的解決方案。
  • 刪除或修改索引：有時候優化器選錯，是因為我們建立了一些冗余或設計不合理的索引，對它造成了“迷惑”。我會審查并刪除那些不必要的索引。
  • 使用覆蓋索引：我會嘗試通過修改或創建新的聯合索引，讓查詢能夠命中覆蓋索引。覆蓋索引的成本極低，優化器會非常傾向于選擇它。

• 2. 治標之法：使用索引提示（Index Hint）

  • 如果更新統計信息等方法都無效，而我又急需讓查詢恢復正常，我才會考慮使用索引提示來強制干預優化器的選擇。這是一種“硬編碼”的方式，需要謹慎使用。

  • FORCE INDEX (index_name)：

    • 作用：正如您所說，這是最強硬的干預手段。它會強制MySQL優化器必須使用我們指定的這個索引，完全忽略其他所有可能的索引。
    • 用法：SELECT * FROM my_table FORCE INDEX (idx_name) WHERE ...
    • 風險：這種方式缺乏靈活性。如果未來數據分布發生變化，或者表結構、索引被修改，這個被我們“寫死”的索引可能不再是最優選擇，甚至變得非常糟糕，而我們代碼中的FORCE INDEX卻阻止了優化器去選擇更好的方案。

  • USE INDEX (index_name)：

    • 作用：這是一個 “建議性” 的提示。它告訴優化器：“我建議你使用這個索引”。優化器在絕大多數情況下會聽從這個建議，但如果它經過計算，發現使用這個索引的成本高得離譜（比如需要全表掃描），它仍然有權忽略這個建議。
    • 靈活性：比FORCE INDEX稍好一些。

  • IGNORE INDEX (index_name)：

    • 作用：告訴優化器：“請忽略這個（或這些）索引”，讓它在剩下的索引中去做選擇。當我們明確知道某個索引會誤導優化器時，這個提示非常有用。

總結與實踐原則

所以，當遇到優化器選錯索引的情況時，我的處理流程是：

1. 先診斷：分析為什么會選錯，是不是統計信息過時了。
2. 優先治本：嘗試通過 ANALYZE TABLE 或優化索引設計（如創建覆蓋索引）來引導優化器做出正確選擇。
3. 最后才治標：在萬不得已的情況下，才使用索引提示（USE INDEX 或 FORCE INDEX）進行強制干預，并需要在代碼中留下詳細的注釋，說明為什么需要這樣做，以便未來的維護。

參考小林 coding