本トピックでは、ハイブリッドプランによるワイドテーブルクエリの高速化について、その仕組み、利用シーン、制限事項、パフォーマンステストの結果を交えて解説します。
仕組み
大規模なワイドテーブルをクエリする際、`WHERE` 句や `JOIN` 句を使用して少数の列でフィルタリングしたり、`TOP K` 行をクエリしたりした後、選択された行のすべての詳細を出力することがあります。このようなタイプのクエリでは、最初のフィルタリングには列ストアインデックスの使用が効果的です。しかし、フィルタリングされた行のすべての詳細列をフェッチする場合、列ストアインデックスのみを使用すると読み取り増幅の問題が発生します。このような状況では、ローストアインデックスを使用して行全体の詳細をフェッチする方が効率的です。ハイブリッドプランは、同じ SQL 文で列ストアインデックスとローストアインデックスの両方を使用するクエリメソッドです。
ハイブリッドプランは、ワイドテーブルクエリを高速化します。実行計画の中で列ストア処理に適した部分について、オプティマイザーはインメモリ列インデックス (IMCI) を使用して中間結果を取得します。これらの中間結果には、プライマリキー情報のみが含まれます。その後、システムはこれらのプライマリキーを InnoDB のプライマリインデックスとともに使用して、すべての射影列を検索し、出力します。上の図は、このプロセスを示しています。
利用シーン
15 以上の列を返すワイドテーブルクエリ。
適用性
ご利用のクラスターは、次のいずれかのバージョンを実行する Enterprise Edition のクラスターである必要があります。
PolarDB for MySQL 8.0.1、リビジョン 8.0.1.1.37 以降。
PolarDB for MySQL 8.0.2、リビジョン 8.0.2.2.23.1 以降。
制限事項
クエリの出力列を提供するテーブルには、明示的に定義されたプライマリキーが必要です。
テーブルタイプなどの制限により、ハイブリッドプランで低速な SQL 文を高速化できない場合は、お問い合わせください。
パラメーターの説明
Hybrid Plan 機能を有効にするには、imci_enable_hybrid_plan パラメーターを ON に設定します。この機能を有効にすると、オプティマイザーは適切なクエリに対して自動的に Hybrid Plan を使用します。特定のクエリで Hybrid Plan を強制的に使用するには、imci_optimizer_switch パラメーターの force_hybrid_index_search オプションを ON に設定するヒントワードを SQL 文に追加します。
パラメーター名 | 説明 |
imci_enable_hybrid_plan | ハイブリッドプラン機能を制御します。有効な値：
|
imci_optimizer_switch | クエリにハイブリッドプランを使用するかどうかを指定します。有効な値：
|
注意事項
このセクションでは、SELECT * FROM t1; を例に、Hybrid Plan を使用してワイドテーブルクエリを高速化する方法を説明します。 次のステップを実行します。
PolarDB データベースに接続します。詳細については、「データベースクラスターへの接続」をご参照ください。
imci_enable_hybrid_planパラメーターを ON に設定して Hybrid Plan 機能を有効にすると、オプティマイザーは適切なクエリに対して自動的に Hybrid Plan を使用します。SET imci_enable_hybrid_plan=ON;特定のクエリで強制的にハイブリッドプランを使用するには、SQL 文にヒントワードを追加します。ヒントワードで、
imci_optimizer_switchパラメーターのforce_hybrid_index_searchオプションを ON に設定します。SELECT /*+ SET_VAR(imci_optimizer_switch='force_hybrid_index_search=ON') */ * FROM t1;EXPLAIN 文を使用して、SQL 文に対してハイブリッドプランがアクティブであるかを確認します。実行計画に HybridIndexSearch が含まれている場合、この機能がアクティブであることを示します。
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 データセットを使用して、3 つのシナリオ (ローストアインデックスのみを使用、インメモリ列インデックス (IMCI) のみを使用、ハイブリッドプランを使用) でのクエリパフォーマンスを比較します。
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;この SQL 文は、3 つの各シナリオで実行されます。すべてのクエリはコールドスタートから実行されます。クエリ時間は次のとおりです。
行ストアのみ | 列ストアインデックスのみ | ハイブリッドプランによる列ストアワイドテーブルクエリの高速化 |
232.48 秒 | 2.56 秒 | 0.33 秒 |
表の結果は、ハイブリッドプランがワイドテーブルクエリのパフォーマンスを大幅に向上させることを示しています。