PolarDB：Gunakan rencana hibrida untuk mempercepat kueri tabel lebar

Cara kerja rencana hibrida

Untuk kueri yang menggunakan klausa WHERE, JOIN, atau LIMIT pada sejumlah kecil kolom dalam tabel lebar, Indeks Kolom dalam Memori (IMCI) dapat secara signifikan meningkatkan performa kueri. Namun, jika IMCI digunakan dalam kueri yang melibatkan detail kolom yang difilter, proyek harus mendapatkan semua informasi kolom, yang menyebabkan beban baca berlebih. Dalam kasus ini, indeks baris lebih cocok. Metode yang menggabungkan pengindeksan kolom dan pengindeksan baris dalam kueri disebut Rencana Hibrida.

Rencana Hibrida mempercepat kueri tabel lebar. IMCI digunakan untuk mengeksekusi dan mendapatkan hasil perantara selama bagian dari rencana eksekusi di mana indeks kolom bekerja dengan baik. Hasil perantara hanya berisi informasi kunci utama. Kemudian, semua informasi kolom dalam proyek di-query dan dikembalikan berdasarkan kunci utama dan indeks utama dari engine InnoDB, seperti ditunjukkan pada gambar sebelumnya.

Skema

Kueri dilakukan pada tabel lebar dan hasilnya mencakup lebih dari 15 kolom.

Prasyarat

Cluster merupakan edisi Enterprise dan menjalankan PolarDB for MySQL 8.0.1 dengan versi revisi 8.0.1.1.37 atau lebih baru.

Batasan

• Tabel yang terlibat dalam kueri berdasarkan Rencana Hibrida harus memiliki kunci utama yang didefinisikan secara eksplisit.

• Rencana Hibrida tidak dapat digunakan pada tabel yang dipartisi.

Parameter

Untuk mengaktifkan fitur Rencana Hibrida, atur parameter imci_enable_hybrid_plan ke ON. Setelah fitur diaktifkan, optimizer akan menggunakan fitur tersebut pada pernyataan kueri yang sesuai untuk mempercepat kueri tabel lebar. Untuk secara paksa menggunakan fitur Rencana Hibrida untuk pernyataan kueri tertentu, tambahkan petunjuk ke pernyataan kueri untuk mengatur opsi force_hybrid_index_search dalam parameter imci_optimizer_switch ke ON.

Prosedur

Berikut ini menjelaskan cara menggunakan fitur Rencana Hibrida untuk mempercepat eksekusi pernyataan SELECT * FROM t1; pada tabel lebar. Ikuti langkah-langkah berikut:

1. Sambungkan ke cluster PolarDB. Untuk informasi lebih lanjut, lihat Sambungkan ke Cluster.

2. Atur parameter imci_enable_hybrid_plan ke ON untuk mengaktifkan fitur Rencana Hibrida. Setelah fitur diaktifkan, optimizer akan menggunakan fitur tersebut pada pernyataan kueri yang sesuai untuk mempercepat kueri tabel lebar.

   SET imci_enable_hybrid_plan=ON;

3. Untuk secara paksa menggunakan fitur Rencana Hibrida untuk kueri tertentu, tambahkan petunjuk ke pernyataan SQL untuk mengatur opsi force_hybrid_index_search dalam parameter imci_optimizer_switch ke ON.

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

   Anda dapat menggunakan pernyataan EXPLAIN untuk memeriksa apakah fitur Rencana Hibrida telah berlaku pada pernyataan SQL. Jika rencana eksekusi berisi HybridIndexSearch, fitur Rencana Hibrida telah berlaku pada pernyataan SQL.

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

   Contoh hasil:

   +----+-------------------------+------+---------------------------------------------------------------+
   | 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 berikut menggunakan dataset OnTime dari ClickHouse untuk membandingkan performa kueri dalam skenario berikut: IMCI atau fitur Rencana Hibrida tidak digunakan, hanya IMCI yang digunakan, dan fitur Rencana Hibrida digunakan.

Tabel dalam dataset OnTime adalah tabel lebar umum yang berisi 109 kolom. Dalam pengujian ini, definisi tabel diubah dan kunci utama baru didefinisikan secara eksplisit. Pernyataan berikut menjelaskan skema yang dimodifikasi:

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'

Eksekusi pernyataan SQL berikut:

SELECT * FROM ontime ORDER BY ArrTime LIMIT 1000;

Tabel berikut menjelaskan jumlah waktu yang diperlukan untuk mengeksekusi kueri pada cold start dalam skenario berikut: IMCI atau fitur Rencana Hibrida tidak digunakan, hanya IMCI yang digunakan, dan fitur Rencana Hibrida digunakan.

Berdasarkan tabel di atas, fitur Rencana Hibrida dapat secara signifikan mempercepat kueri pada tabel lebar.