Cara menggunakan Hybrid Plan untuk mempercepat kueri tabel lebar - PolarDB

Topik ini menjelaskan cara Hybrid Plan mempercepat kueri pada tabel lebar, mencakup prinsip dasar, skenario penerapan, batasan, serta hasil pengujian performa.

Cara kerja

Saat melakukan kueri pada large wide table, Anda biasanya menerapkan filter berdasarkan beberapa kolom menggunakan klausa WHERE atau JOIN, atau mengambil TOP K baris, lalu menampilkan semua detail untuk baris yang dipilih. Untuk jenis kueri semacam ini, penggunaan columnstore index efektif pada tahap penyaringan awal. Namun, ketika mengambil semua kolom detail dari baris yang telah difilter, penggunaan hanya columnstore index menyebabkan masalah read amplification. Dalam situasi ini, penggunaan row store index untuk mengambil detail lengkap baris jauh lebih efisien. Hybrid Plan adalah metode kueri yang memanfaatkan columnstore index dan row store index secara bersamaan dalam satu pernyataan SQL.

Hybrid Plan mempercepat kueri pada tabel lebar. Untuk bagian-bagian rencana eksekusi yang cocok dengan pemrosesan columnstore, pengoptimal menggunakan In-Memory Column Index (IMCI) untuk mengambil hasil antara yang hanya berisi informasi primary key. Selanjutnya, sistem menggunakan primary key tersebut bersama primary index InnoDB untuk mencari dan menampilkan semua kolom yang diproyeksikan. Gambar di atas mengilustrasikan proses ini.

Skenario

Kueri tabel lebar yang mengembalikan 15 kolom atau lebih.

Penerapan

Kluster Anda harus merupakan kluster Enterprise Edition yang menjalankan salah satu versi berikut:

PolarDB for MySQL 8.0.1, revisi 8.0.1.1.37 atau yang lebih baru.
PolarDB for MySQL 8.0.2, revisi 8.0.2.2.23.1 atau yang lebih baru.

Batasan

Tabel yang menyediakan kolom output untuk kueri harus memiliki primary key yang didefinisikan secara eksplisit.

Catatan

Jika Hybrid Plan gagal mempercepat pernyataan SQL yang lambat karena batasan tertentu, seperti tipe tabel, Anda dapat menghubungi kami untuk mendapatkan bantuan.

Deskripsi parameter

Untuk mengaktifkan fitur Hybrid Plan, atur parameter imci_enable_hybrid_plan ke ON. Setelah diaktifkan, pengoptimal secara otomatis menggunakan Hybrid Plan untuk kueri yang sesuai. Untuk memaksa kueri tertentu menggunakan Hybrid Plan, tambahkan hint ke pernyataan SQL yang mengatur opsi force_hybrid_index_search pada parameter imci_optimizer_switch ke ON.

Nama Parameter

Deskripsi

imci_enable_hybrid_plan

Mengontrol fitur Hybrid Plan. Nilai yang valid:

ON (Default): Mengaktifkan fitur.
OFF: Menonaktifkan fitur Hybrid Plan yang mempercepat kueri pada tabel lebar.

imci_optimizer_switch

Menentukan apakah Hybrid Plan digunakan untuk suatu kueri. Nilai yang valid:

'force_hybrid_index_search=OFF' (Default): Pengoptimal secara otomatis menentukan apakah akan menggunakan Hybrid Plan.
'force_hybrid_index_search=ON': Memaksa kueri menggunakan Hybrid Plan.

Catatan penggunaan

Bagian ini menggunakan SELECT * FROM t1; sebagai contoh untuk menunjukkan cara menggunakan Hybrid Plan guna mempercepat kueri tabel lebar. Ikuti langkah-langkah berikut:

Lakukan koneksi ke database PolarDB. Untuk informasi selengkapnya, lihat Connect to a database cluster.
Atur parameter imci_enable_hybrid_plan ke ON untuk mengaktifkan fitur Hybrid Plan. Pengoptimal kemudian secara otomatis menggunakan Hybrid Plan untuk kueri yang sesuai.
```
SET imci_enable_hybrid_plan=ON;
```

Untuk memaksa kueri tertentu menggunakan Hybrid Plan, tambahkan hint ke pernyataan SQL. Di dalam hint tersebut, atur opsi force_hybrid_index_search dari parameter imci_optimizer_switch ke ON.

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

Gunakan pernyataan EXPLAIN untuk memeriksa apakah Hybrid Plan aktif pada pernyataan SQL tersebut. Jika rencana eksekusi mencakup HybridIndexSearch, hal ini menunjukkan bahwa fitur tersebut aktif.

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

Hasil berikut dikembalikan:

+----+-------------------------+------+---------------------------------------------------------------+
| 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)

Pengujian performa

Pengujian ini menggunakan set data OnTime dari ClickHouse untuk membandingkan performa kueri dalam tiga skenario: hanya menggunakan row store index, hanya menggunakan In-Memory Column Index (IMCI), dan menggunakan Hybrid Plan.

Tabel dalam set data OnTime merupakan contoh typical large wide table dengan 109 kolom. Untuk pengujian ini, definisi tabel dari ClickHouse dimodifikasi agar memiliki primary key yang didefinisikan secara eksplisit. Skema tabel yang dimodifikasi adalah sebagai berikut:

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'

Pengujian menggunakan pernyataan SQL berikut:

SELECT * FROM ontime ORDER BY ArrTime LIMIT 1000;

Pernyataan SQL tersebut dieksekusi dalam masing-masing dari tiga skenario. Semua kueri dilakukan dari cold start. Waktu kueri adalah sebagai berikut:

Hanya row store	Hanya indeks columnstore	Percepat kueri tabel lebar column store dengan Hybrid Plan
232,48 detik	2,56 detik	0,33 detik

Hasil dalam tabel menunjukkan bahwa Hybrid Plan secara signifikan meningkatkan performa untuk kueri tabel lebar.