如何使用Hybrid Plan加速寬表查詢 - PolarDB

本文介紹了使用Hybrid Plan加速寬表查詢的技術原理、適用情境、使用限制以及效能測試等內容。

技術原理

在查詢大寬表時，若對少數列通過WHERE或JOIN條件進行過濾，或查詢TOP K，最後輸出篩選出來的行的所有詳細資料。對於這類查詢，使用列存索引進行查詢的效果較好。但在使用列存索引查詢篩選出的列的詳細資料時，project需要擷取所有的列資訊，列存索引在擷取所有的列資訊時存在讀放大問題。這種情況下，通過行式索引來查詢詳細資料效果較好。這種在同一條查詢語句中既使用了列式索引，又使用了行式索引的查詢方式稱之為Hybrid Plan。

Hybrid Plan會加快寬表查詢速度，對於執行計畫中適合列式索引的部分，會通過列存索引來執行並擷取中間結果，中間結果中僅包含主鍵資訊。最後再通過主鍵結合InnoDB主索引來查詢project中所有的列資訊並輸出。原理如上圖所示。

適用情境

寬表查詢，且查詢結果中會輸出多列（15列以上）的情境。

前提條件

叢集的產品版本需為企業版，叢集版本需為PolarDB MySQL版8.0.1版本且修訂版本為8.0.1.1.37及以上。

使用限制

查詢語句中需要輸出的列涉及的表中需要有顯式定義的主鍵。
暫不支援對分區表使用Hybrid Plan加速寬表查詢功能。

說明

如果慢SQL中需要使用Hybrid Plan加速寬表查詢功能，但受限於表類型等因素，您可以聯絡我們尋求協助。

參數說明

您需要在資料庫中將imci_enable_hybrid_plan參數的值設定為ON來開啟Hybrid Plan加速寬表查詢功能，開啟後最佳化器會選擇合適的查詢語句使用Hybrid Plan加速寬表查詢。如果需要強制某個查詢語句使用Hybrid Plan加速，您可以在查詢語句中的HINT文法中將參數imci_optimizer_switch中的force_hybrid_index_search參數的值設定為ON來加速查詢。

參數名稱

參數說明

imci_enable_hybrid_plan

Hybrid Plan加速寬表查詢功能的控制開關。取值範圍如下：

ON（預設）：開啟Hybrid Plan加速寬表查詢功能。
OFF：關閉Hybrid Plan加速寬表查詢功能。

imci_optimizer_switch

是否在滿足條件的查詢語句中使用Hybrid Plan加速寬表查詢。取值範圍如下：

'force_hybrid_index_search=OFF'（預設）：不強制使用Hybrid Plan加速寬表查詢（通過最佳化器自動選擇）。
'force_hybrid_index_search=ON'：強制使用Hybrid Plan加速寬表查詢。

使用說明

此處以SELECT * FROM t1;為例來介紹如何使用Hybrid Plan加速寬表查詢功能。操作步驟如下：

串連PolarDB資料庫。具體操作步驟請參見串連資料庫叢集。
將參數imci_enable_hybrid_plan的值設定為ON，來開啟Hybrid Plan加速寬表查詢功能。開啟後最佳化器會選擇合適的查詢語句使用Hybrid Plan加速寬表查詢。
```
SET imci_enable_hybrid_plan=ON;
```

如果需要強制某個查詢語句使用Hybrid Plan加速，您可以在需要使用Hybrid Plan加速寬表查詢功能的SQL語句中添加HINT文法，並在HINT文法中將參數imci_optimizer_switch中的force_hybrid_index_search參數的值設定為ON。

SELECT /*+ SET_VAR(imci_optimizer_switch='force_hybrid_index_search=ON') */ * FROM t1;

您可以通過EXPLAIN語句來判斷在SQL語句中使用的Hybrid Plan加速寬表查詢功能是否生效。如果執行計畫中包含HybridIndexSearch，則表示Hybrid Plan加速寬表查詢功能生效。

EXPLAIN SELECT /*+ SET_VAR(imci_optimizer_switch='force_hybrid_index_search=ON') */ * FROM t1;

查詢結果如下：

+----+-------------------------+------+---------------------------------------------------------------+
| ID | Operator                | Name | Extra Info                                                    |
+----+-------------------------+------+---------------------------------------------------------------+
|  1 | Select Statement        |      | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
|  2 | └─HybridIndexSearch     |      | Used table: t1                                                |
|  3 |   └─Compute Scalar      |      |                                                               |
|  4 |     └─Table Scan        | t1   |                                                               |
+----+-------------------------+------+---------------------------------------------------------------+
4 rows in set (0.02 sec)

效能測試

此處以ClickHouse官方提供的OnTime資料集為基礎，來測試在SQL語句中僅使用行存索引、僅使用列存索引以及使用Hybrid Plan加速寬表查詢三種情境下的查詢效果。

OnTime資料集中的表包含109列，是一個典型的大寬表。在該測試中修改了ClickHouse的表定義，並為這張表顯式定義了主鍵。修改後的表結構如下所示：

CREATE TABLE `ontime` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Year` year(4) DEFAULT NULL,
  `Quarter` tinyint(4) DEFAULT NULL,
  `Month` tinyint(4) DEFAULT NULL,
  `DayofMonth` tinyint(4) DEFAULT NULL,
  `DayOfWeek` tinyint(4) DEFAULT NULL,
  `FlightDate` date DEFAULT NULL,
  `UniqueCarrier` char(7) DEFAULT NULL,
  `AirlineID` int(11) DEFAULT NULL,
  `Carrier` char(2) DEFAULT NULL,
  `TailNum` varchar(50) DEFAULT NULL,
  `FlightNum` varchar(10) DEFAULT NULL,
  `OriginAirportID` int(11) DEFAULT NULL,
  `OriginAirportSeqID` int(11) DEFAULT NULL,
  `OriginCityMarketID` int(11) DEFAULT NULL,
  `Origin` char(5) DEFAULT NULL,
  `OriginCityName` varchar(100) DEFAULT NULL,
  `OriginState` char(2) DEFAULT NULL,
  `OriginStateFips` varchar(10) DEFAULT NULL,
  `OriginStateName` varchar(100) DEFAULT NULL,
  `OriginWac` int(11) DEFAULT NULL,
  `DestAirportID` int(11) DEFAULT NULL,
  `DestAirportSeqID` int(11) DEFAULT NULL,
  `DestCityMarketID` int(11) DEFAULT NULL,
  `Dest` char(5) DEFAULT NULL,
  `DestCityName` varchar(100) DEFAULT NULL,
  `DestState` char(2) DEFAULT NULL,
  `DestStateFips` varchar(10) DEFAULT NULL,
  `DestStateName` varchar(100) DEFAULT NULL,
  `DestWac` int(11) DEFAULT NULL,
  `CRSDepTime` int(11) DEFAULT NULL,
  `DepTime` int(11) DEFAULT NULL,
  `DepDelay` int(11) DEFAULT NULL,
  `DepDelayMinutes` int(11) DEFAULT NULL,
  `DepDel15` int(11) DEFAULT NULL,
  `DepartureDelayGroups` int(11) DEFAULT NULL,
  `DepTimeBlk` varchar(20) DEFAULT NULL,
  `TaxiOut` int(11) DEFAULT NULL,
  `WheelsOff` int(11) DEFAULT NULL,
  `WheelsOn` int(11) DEFAULT NULL,
  `TaxiIn` int(11) DEFAULT NULL,
  `CRSArrTime` int(11) DEFAULT NULL,
  `ArrTime` int(11) DEFAULT NULL,
  `ArrDelay` int(11) DEFAULT NULL,
  `ArrDelayMinutes` int(11) DEFAULT NULL,
  `ArrDel15` int(11) DEFAULT NULL,
  `ArrivalDelayGroups` int(11) DEFAULT NULL,
  `ArrTimeBlk` varchar(20) DEFAULT NULL,
  `Cancelled` tinyint(4) DEFAULT NULL,
  `CancellationCode` char(1) DEFAULT NULL,
  `Diverted` tinyint(4) DEFAULT NULL,
  `CRSElapsedTime` int(11) DEFAULT NULL,
  `ActualElapsedTime` int(11) DEFAULT NULL,
  `AirTime` int(11) DEFAULT NULL,
  `Flights` int(11) DEFAULT NULL,
  `Distance` int(11) DEFAULT NULL,
  `DistanceGroup` tinyint(4) DEFAULT NULL,
  `CarrierDelay` int(11) DEFAULT NULL,
  `WeatherDelay` int(11) DEFAULT NULL,
  `NASDelay` int(11) DEFAULT NULL,
  `SecurityDelay` int(11) DEFAULT NULL,
  `LateAircraftDelay` int(11) DEFAULT NULL,
  `FirstDepTime` varchar(10) DEFAULT NULL,
  `TotalAddGTime` varchar(10) DEFAULT NULL,
  `LongestAddGTime` varchar(10) DEFAULT NULL,
  `DivAirportLandings` varchar(10) DEFAULT NULL,
  `DivReachedDest` varchar(10) DEFAULT NULL,
  `DivActualElapsedTime` varchar(10) DEFAULT NULL,
  `DivArrDelay` varchar(10) DEFAULT NULL,
  `DivDistance` varchar(10) DEFAULT NULL,
  `Div1Airport` varchar(10) DEFAULT NULL,
  `Div1AirportID` int(11) DEFAULT NULL,
  `Div1AirportSeqID` int(11) DEFAULT NULL,
  `Div1WheelsOn` varchar(10) DEFAULT NULL,
  `Div1TotalGTime` varchar(10) DEFAULT NULL,
  `Div1LongestGTime` varchar(10) DEFAULT NULL,
  `Div1WheelsOff` varchar(10) DEFAULT NULL,
  `Div1TailNum` varchar(10) DEFAULT NULL,
  `Div2Airport` varchar(10) DEFAULT NULL,
  `Div2AirportID` int(11) DEFAULT NULL,
  `Div2AirportSeqID` int(11) DEFAULT NULL,
  `Div2WheelsOn` varchar(10) DEFAULT NULL,
  `Div2TotalGTime` varchar(10) DEFAULT NULL,
  `Div2LongestGTime` varchar(10) DEFAULT NULL,
  `Div2WheelsOff` varchar(10) DEFAULT NULL,
  `Div2TailNum` varchar(10) DEFAULT NULL,
  `Div3Airport` varchar(10) DEFAULT NULL,
  `Div3AirportID` int(11) DEFAULT NULL,
  `Div3AirportSeqID` int(11) DEFAULT NULL,
  `Div3WheelsOn` varchar(10) DEFAULT NULL,
  `Div3TotalGTime` varchar(10) DEFAULT NULL,
  `Div3LongestGTime` varchar(10) DEFAULT NULL,
  `Div3WheelsOff` varchar(10) DEFAULT NULL,
  `Div3TailNum` varchar(10) DEFAULT NULL,
  `Div4Airport` varchar(10) DEFAULT NULL,
  `Div4AirportID` int(11) DEFAULT NULL,
  `Div4AirportSeqID` int(11) DEFAULT NULL,
  `Div4WheelsOn` varchar(10) DEFAULT NULL,
  `Div4TotalGTime` varchar(10) DEFAULT NULL,
  `Div4LongestGTime` varchar(10) DEFAULT NULL,
  `Div4WheelsOff` varchar(10) DEFAULT NULL,
  `Div4TailNum` varchar(10) DEFAULT NULL,
  `Div5Airport` varchar(10) DEFAULT NULL,
  `Div5AirportID` int(11) DEFAULT NULL,
  `Div5AirportSeqID` int(11) DEFAULT NULL,
  `Div5WheelsOn` varchar(10) DEFAULT NULL,
  `Div5TotalGTime` varchar(10) DEFAULT NULL,
  `Div5LongestGTime` varchar(10) DEFAULT NULL,
  `Div5WheelsOff` varchar(10) DEFAULT NULL,
  `Div5TailNum` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=58592318 DEFAULT CHARSET=latin1 COMMENT='columnar=1'

該測試使用的SQL語句如下：

SELECT * FROM ontime ORDER BY ArrTime LIMIT 1000;

分別在僅使用行存索引、僅使用列存索引以及使用Hybrid Plan加速寬表查詢三種情境下執行該SQL語句。三種情境下的執行計畫均為冷啟動查詢，查詢時間如下：

僅使用行存	僅使用列存索引	使用Hybrid Plan加速列存寬表查詢
232.48秒	2.56秒	0.33秒

由上表中的查詢時間可以看出，對於涉及寬表的查詢，使用Hybrid Plan加速寬表查詢功能可以獲得明顯的效能提升效果。