Sql最佳化之回表

京东云开发者發表於2024-04-08
SQL

前言：

MySQL的效能是大家在使用時十分關心的問題，比如在高併發訪問時，並且有慢sql存在的情況下，MySQL的效能會明顯下降，這會導致資料庫響應時間變慢，甚至導致資料庫當機。那麼為了避免Mysql效能問題，比較常用的方式建立適當的索引，提升sql語句的執行效率。而本文簡單介紹一下和索引有關的回表，從實際案例出發，講講什麼是回表，如何避免回表，如何減少回表。

實際案例：

前置倉產能動態ETA時效降級需求，提供一個資料同步介面，接送大資料同步過來的前置倉的倉負債和配負債，需要支援批次，最多可支援10個批次。

表結構如下，可以看到唯一索引是 UNIQUE KEY `uniq_shop` (`shop_id`,`shop_type`)

CREATE TABLE `shop_load_degrade_strategy` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `shop_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '門店ID',
  `shop_type` int(11) NOT NULL DEFAULT '0' COMMENT '門店型別',
  `current_store_load` int(10) NOT NULL DEFAULT '0' COMMENT '當前倉負載',
  `active_store_load` int(10) NOT NULL DEFAULT '0' COMMENT '正在使用的倉負載',
  `active_store_degrade_delay` int(11) NOT NULL DEFAULT '0' COMMENT '正在使用的倉降級時間分鐘數',
  `current_delivery_load` int(10) NOT NULL DEFAULT '0' COMMENT '當前配送負載',
  `active_delivery_load` int(10) NOT NULL DEFAULT '0' COMMENT '正在使用的配送負載',
  `active_delivery_degrade_delay` int(11) NOT NULL DEFAULT '0' COMMENT '正在使用的配送降級時間分鐘數',
  `current_load_active_auto` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '倉配負載自動生效，1開啟',
  `enable` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT'是否開啟，1開啟',`artificial_active_time`datetimeDEFAULTNULLCOMMENT'運營手動生效時間',`active_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'最後生效時間',`approver`varchar(100)DEFAULTNULLCOMMENT'審批人',`status`tinyint(4)NOTNULLDEFAULT'0'COMMENT'狀態：1啟用，0失效',`yn`tinyint(3)unsignedNOTNULLDEFAULT'0'COMMENT'1刪除',`update_pin`varchar(50)NOTNULLDEFAULT''COMMENT'最後修改人',`update_time`datetimeNOTNULLCOMMENT'修改時間',`create_pin`varchar(50)NOTNULLDEFAULT''COMMENT'建立人',`create_time`datetimeNOTNULLCOMMENT'建立時間',`ts`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'時間戳',PRIMARYKEY(`id`),UNIQUEKEY`uniq_shop`(`shop_id`,`shop_type`),KEY`idx_active_time`(`active_time`),KEY`idx_update_pin`(`update_pin`))ENGINE=InnoDBDEFAULTCHARSET=utf8 COMMENT='門店負載降級策略表';

資料處理流程

最簡單的處理方式就是，逐條資料處理

public void handRecords(List<ShopLoadDegradeStrategy> records){
    for(ShopLoadDegradeStrategy shopLoadDegradeStrategy: records){
        dbShopLoadDegradeStrategy = findByshopIdAndShopType(shopLoadDegradeStrategy);
        if(null != dbShopLoadDegradeStrategy)
            shopLoadDegradeStrategy.setId(dbShopLoadDegradeStrategy.getId())            
            updateById(shopLoadDegradeStrategy);
        }else{
            insert(shopLoadDegradeStrategy);
        }
    }
}

最初的最佳化思想的減少網路傳輸次數

public void handRecords(List<ShopLoadDegradeStrategy> records){
   //批次查詢
   List<ShopLoadDegradeStrategy> dbShopLoadDegradeStrategys = findAllByshopIdAndShopTypeList(records);
   updateShopLoadDegradeStrategys,insertShopLoadDegradeStrategys = handleShopLoadDegradeStrategys(records,dbShopLoadDegradeStrategys);
   updateForEach(updateShopLoadDegradeStrategys);
   //批次插入
   batchInsert(insertShopLoadDegradeStrategys);
}

具體sql語句：

批次按照門店Id和門店型別查詢資料是否存在

select * from shop_load_degrade_strategy where (shop_id=? and shop_type=?) or (shop_id=? and shop_type=?)

存在更新資料, 因為shop_id和shop_type是組合的唯一索引，所以按照主鍵更新和按照唯一索引更新的結果是一樣的。但是過程真的一樣嗎？

update shop_load_degrade_strategy set current_store_load=?,current_delivery_load=?  where shop_id=? and shop_type=?

看是很簡單的兩個sql語句，explain分析sql執行計劃，可以看出用到了索引，並且sql語句執行也很快，不存在慢sql的問題。還有最佳化的空間嗎？

最佳化過程分析

先簡單介紹一下索引按物理儲存分類

mysql的B+樹

1）聚集索引（聚簇索引） InnoDB的聚簇索引就是按照主鍵順序構建 B+Tree結構。葉子節點儲存資料行的資料。

2）輔助索引（二級索引） InnoDB的輔助索引就是按照索引列構建 B+Tree結構。葉子節點儲存的是索引列的值以及對應行的主鍵值

覆蓋索引並不是說是索引結構，覆蓋索引是一種很常用的最佳化手段。因為在使用輔助索引的時候，我們只可以拿到主鍵值，相當於獲取資料還需要再根據主鍵查詢主鍵索引再獲取到資料。但是試想下這麼一種情況，在上面shop_load_degrade_strategy表中的組合索引查詢時，如果我只需要id，shop_id,shop_type欄位的，那是不是意味著我們查詢到組合索引的葉子節點就可以直接返回了，而不需要回表。這種情況就是覆蓋索引。

查詢sql語句最佳化為：

select id,shop_id,shop_type from shop_load_degrade_strategy where (shop_id=? and shop_type=?) or (shop_id=? and shop_type=?)

最佳化前
1.select * from shop_load_degrade_strategy where (shop_id=? and shop_type=?) or (shop_id=? and shop_type=?)

最佳化前：

1.先查詢輔助索引找到對應的主鍵Id

2.然後透過id回表查詢資料表的資料。

最佳化後

1.只需要查詢輔助索引就可以，透過查詢輔助索引就可以查詢到所以需要的列，想要使用覆蓋索引，查詢的結果的列只能包含在輔助索引列和主鍵索引列。透過檢視sql計劃可以到Extra的值為Using index。

總結：最佳化前要查詢除了索引欄位（shop_id,shop_type)和主鍵（id）之外的欄位，不能用到覆蓋索引這種方式。所以在編寫sql的時候，查詢的資料列一定是滿足業務的最小列集合，首先減少網路傳輸的資料量，同時也會減少應用的記憶體使用，還有看看能不能使用到覆蓋索引這種方式進行最佳化，有些情況下甚至會在輔助索引中增加聯合索引的列來使用到覆蓋索引減少回表。

更新sql語句的最佳化

最佳化後：
update shop_load_degrade_strategy set current_store_load=?,current_delivery_load=?  where id=？
最佳化前：
update shop_load_degrade_strategy set current_store_load=?,current_delivery_load=?  where shop_id=? and shop_type=?

最佳化前的更新語句雖然使用到了輔助索引作為查詢條件來進行資料更新，但是mysql執行sql的過程為：

1.先查詢輔助索引找到索引對應的主鍵Id

2.然後透過id回表查詢資料表的資料。

3.最後更新資料。

最佳化後的sql執行過程為：

1.透過id回表查詢資料表的資料。

2.最會更新對應id的資料，

從以上描述可以看出，減少回表也是我們最佳化sql語句的一種很重要的思想。透過減少回表這個思想和處理資料的方式，引出說明一下mysql索引下推這個概念，其核心思想就是減少回表

什麼是索引下推

索引下推(Index Condition Pushdown，索引條件下推，簡稱ICP)，是MySQL5.6版本的新特性，它可以在對聯合索引遍歷過程中，對索引中包含的所有欄位先做判斷，過濾掉不符合條件的記錄之後再回表，能有效的減少回表次數（目前我們使用的mysql版本較高，一般大家可能感覺這是正常的，但是mysql5.6之前都不是這樣實現的，下面會細細道來）。

適用條件

我們先來了解一下索引下推的使用條件及限制：

•只支援select。
•當需要訪問全表時，ICP用於range，ref，eq_ref和ref_or_null訪問型別。
•ICP可用於InnoDB和MyISAM表，包括分割槽的InnoDB和MyISAM表。（5.6版本不適用分割槽表查詢，5.7版本後可以用於分割槽表查詢）。
•對於InnDB引擎只適用於二級索引（也叫輔助索引），因為InnDB的聚簇索引會將整行資料讀到InnDB的緩衝區，這樣一來索引條件下推的主要目的減少IO次數就失去了意義。因為資料已經在記憶體中了，不再需要去讀取了。
•在虛擬生成列上建立的輔助索引不支援ICP（注：InnoDB支援虛擬生成列的輔助索引）。
•使用了子查詢的條件無法下推。
•使用儲存過程或函式的條件無法下推（因為因為儲存引擎沒有呼叫儲存過程或函式的能力）。

原理介紹

1.未使用ICP的情況下：
•儲存引擎讀取索引記錄；
•根據索引中的主鍵值，定位並讀取完整的行記錄；
•儲存引擎把記錄交給Server層去檢測該記錄是否滿足WHERE條件。

1.使用ICP的情況下：
•儲存引擎讀取索引記錄（不是完整的行記錄）；
•判斷WHERE條件部分能否用索引中的列來做檢查，條件不滿足，則處理下一行索引記錄；
•條件滿足，使用索引中的主鍵去定位並讀取完整的行記錄（就是所謂的回表）；
•儲存引擎把記錄交給Server層，Server層檢測該記錄是否滿足WHERE條件的其餘部分。

具體例子

聚簇索引

輔助索引

假設有一條查詢語句

select * from shop_load_degrade_strategy where shop_id <102 and shop_type=1;

根據索引最左匹配原則，上面這個sql語句在查索引樹的時候，只能用102，查到第一個滿足條件的記錄：id為7,8兩條記錄。

那接下來我們具體看一下 使用與未使用ICP的情況。

未使用ICP情況下：

1.儲存引擎根據聯合索引先找到shop_id<102 的主鍵id（7、8），再逐一進行回表掃描，去聚簇索引找到完整的行記錄，再根據條件shop_type=1對拿到的資料進行篩，可以看到有兩次回表。

使用ICP情況下：

1.儲存引擎會根據（shop_id，shop_type）聯合索引，找到shop_id <102，由於聯合索引中包含shop_type

列，所以儲存引擎直接再聯合索引裡按照條件shop_type=1進行過濾，就只會找到主鍵id（7），然後根據過濾後的資料再依次進行回表掃描。可以看到只有一次回表。透過sql執行計劃可以看出 Extra的值為“Using index condition”表示使用了索引下推。

結語：

回表操作：當所要查詢的欄位不在非主鍵索引樹上時，需要透過葉子節點的主鍵值去主鍵索引上獲取對應的行資料，這個過程稱為回表操作，可以看出Mysql透過索引下推來減少回表次數，從而提高sql效能。所以在專案開發編寫sql語句的時候，要時刻注意是否可以透過減少回表或者不回表來最佳化sql效能，減少Mysql資料效能的壓力。

參考文獻：

https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

相關文章