@

前言

請各大網友尊重本人原創知識分享，謹記本人博客：南國以南i、微信公眾號：白碼夢想家

提示：以下是本篇文章正文內容，下面案例可供參考

背景

Hello 朋友們，接下來為大家開啓，面試題相關係列☞《對線面試官》 自信出擊，讓 offer 手到擒來！！！

基礎知識

• 聚簇索引： 聚簇索引的葉子節點存儲的是整行數據，且數據的物理存儲順序與聚簇索引的順序一致。
• 非聚簇索引： 非聚簇索引的葉子節點存儲的是索引列的值和對應行的主鍵值，而不是整行數據。

聚族索引與非聚族索引區別：

葉節點是否存放一整行記錄，一個表中除了主鍵id字段是聚族索引，其它字段都是非聚族索引

回表定義

回表（Look-up）指的是在MySQL中， 在使用非聚集索引進行查詢時，才會涉及回表問題 。MySQL首先通過非聚簇索引找到對應的主鍵值，後利用這個主鍵值在聚簇索引中定位到具體的行，並獲取所需的其他列數據。
簡單來説，回表是一種二次查找的操作，用於獲取非聚集索引無法提供的其他列的值。

經典示例:

假設有一個名為users的表，其中包含三列：id（主鍵，聚簇索引）、name（非聚簇索引）和age。如果執行查詢SELECT * FROM users WHERE name = 'Alice';，則MySQL會首先通過name列的非聚簇索引找到所有name為'Alice'的記錄的主鍵值，然後利用這些主鍵值在id列的聚簇索引中查找並返回整行數據。

由圖可知流程，首先從非聚簇索引開始尋找聚簇索引，找到非聚簇索引上的聚簇索引後，就會到聚簇索引的B+樹上進行查詢，通過聚簇索引B+樹找到完整的數據。該過程比較專業的叫法也被稱為 “回表”。

回表的影響

回表操作會增加查詢的成本，因為它需要額外的I/O操作來訪問聚簇索引並獲取整行數據。特別是在數據量較大的情況下，回表操作可能會對查詢性能產生顯著影響。

如何避免回表？

如果無法避免回表操作，可以通過以下方法進行優化：

1. 使用覆蓋索引

定義：覆蓋索引（Covering Index）的方式。覆蓋索引是指創建一個包含了查詢所需的所有列的索引，這樣就可以直接從索引中獲取所需的數據，而無需回到表中查找。使用覆蓋索引可以減少I/O操作和提高查詢性能。

做法： 在創建索引時，確保索引包含了查詢中需要的所有列。這樣，MySQL可以直接從索引中獲取所需的數據，而無需回表。

注意： 覆蓋索引（Covering Index）和索引覆蓋（Index Covering）實際上是同一概念的不同表述方式，它們指的是同一個優化技術。因此，從本質上來説，覆蓋索引和索引覆蓋沒有區別。

應用場景：

1. 當查詢的列是索引的一部分時，如果這些列足以滿足查詢需求，就可以使用覆蓋索引。
2. 在進行分頁查詢時，如果查詢條件已經包含在索引中，並且只需要獲取索引中的部分列數據，就可以使用覆蓋索引來提高查詢效率。
3. 在進行統計查詢時，如果統計信息可以通過索引直接計算得出，也可以使用覆蓋索引來減少數據訪問量。

示例：

CREATE INDEX idx_name_age ON table_name(name, age);  
SELECT name, age FROM table_name WHERE name = 'Alice';

2. 使用聚簇索引

定義： 聚簇索引是一種特殊的索引，它將數據和索引存儲在一起。在InnoDB存儲引擎中，主鍵索引就是聚簇索引。

做法： 確保查詢中頻繁使用的列是主鍵或包含在聚簇索引中。這樣，當通過這些列進行查詢時，可以直接從聚簇索引中獲取數據，無需回表。

注意： 每個表只能有一個聚簇索引，因為數據只能有一種物理存儲順序。

3. 優化查詢條件

做法：儘量避免在查詢條件中使用不在索引列中的列，因為這會導致回表查詢。優化查詢條件，使之儘可能使用索引列。

示例：

-- 優化前（假設沒有針對customer_name的索引）  
SELECT * FROM customers WHERE customer_name = 'John Doe';  
  
-- 優化後（添加索引）  
ALTER TABLE customers ADD INDEX (customer_name);  
SELECT * FROM customers WHERE customer_name = 'John Doe';

4. 減少SELECT *的使用

做法： 儘量避免使用SELECT *，只查詢需要的列。這樣可以減少數據傳輸量，提高查詢效率，並在某些情況下避免回表。

示例：

-- 優化前  
SELECT * FROM table_name WHERE id = 1;  
  
-- 優化後  
SELECT id, name FROM table_name WHERE id = 1;

5. 使用聯合索引

定義： 聯合索引是將多個列組合成一個索引。

做法： 將查詢中經常一起出現的列組合成聯合索引。這樣，當這些列一起出現在查詢條件中時，可以減少回表次數。

示例：

CREATE INDEX idx_name_age ON table_name(name, age);  
SELECT * FROM table_name WHERE name = 'Alice' AND age = 20;

6. 使用子查詢或臨時表

做法： 將需要查詢的數據先存儲在臨時表或子查詢中，然後再進行關聯查詢。這樣可以減少回表次數，特別是當關聯查詢涉及多個表時。

7. 定期優化和重建索引

做法： 隨着數據的更新和增長，索引可能會變得不再緊湊，影響查詢性能。定期優化和重建索引可以保持索引性能。

8. EXPLAIN分析查詢計劃

做法： 通過EXPLAIN語句分析查詢計劃，瞭解查詢是如何執行的，從而找到優化的方法，減少回表查詢的次數。

9.調整表結構

做法： 如果回表操作非常頻繁，可以考慮調整表結構，將需要查詢的列放在索引中，或者使用聚簇索引來減少回表操作。

10. 使用緩存

做法： 如果查詢的數據具有一定的重複性，可以考慮使用緩存來減少回表操作。

通過以上方法，可以有效地避免MySQL中的回表操作，提高查詢性能和數據庫的整體性能。

回表總結

回表是MySQL數據庫中一種常見的操作，用於獲取非聚集索引無法提供的其他列的值。回表操作會增加額外的I/O操作和訪問時間，影響查詢的性能。為了避免回表操作，可以使用覆蓋索引的方式。如果無法避免回表操作，可以通過優化查詢語句、調整表結構和使用緩存等方式來優化回表操作。在實際應用中，需要根據具體的場景和需求來選擇合適的優化策略。

我是南國以南i記錄點滴每天成長一點點，學習是永無止境的！轉載請附原文鏈接！！！

參考鏈接、參考鏈接、