如何使用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加速宽表查询功能可以获得明显的性能提升效果。