Apa itu Limit Offset pushdown, dan apa saja batasannya - PolarDB

Kueri berhalaman melambat seiring bertambahnya nomor halaman karena model eksekusi MySQL standar mengirimkan semua baris kandidat ke lapisan SQL—baris-baris tersebut kemudian langsung dibuang berdasarkan nilai OFFSET. PolarDB for MySQL mengurangi beban ini dengan mendorong evaluasi LIMIT OFFSET ke lapisan mesin penyimpanan, sehingga baris yang tidak diperlukan difilter sebelum dikirim.

Cara kerja

Pada MySQL standar, klausa LIMIT dievaluasi di lapisan SQL. Mesin penyimpanan mengirimkan semua baris kandidat ke lapisan SQL, yang kemudian melewatkan baris berdasarkan nilai OFFSET dan mengembalikan set hasil akhir. Untuk kueri berhalaman dengan nilai OFFSET besar, hal ini berarti mesin penyimpanan mengirimkan banyak baris yang langsung dibuang—dan biayanya meningkat secara linear seiring bertambahnya nomor halaman.

Dengan LIMIT OFFSET pushdown diaktifkan, PolarDB memfilter baris di lapisan mesin penyimpanan sebelum mencapai lapisan SQL. Fitur ini berlaku dalam dua skenario utama:

Tidak ada kondisi WHERE di lapisan SQL: Ketika predikat sepenuhnya didorong ke bawah melalui fitur penurunan predikat detach_range_condition, tidak ada pemfilteran yang tersisa di lapisan SQL. Dengan demikian, LIMIT OFFSET dapat langsung didorong ke bawah.
Akses indeks sekunder dengan lookup tabel: Ketika kueri menggunakan indeks sekunder dan juga memerlukan kolom dari tabel utama, LIMIT OFFSET pushdown melewatkan lookup tabel yang tidak perlu di lapisan mesin penyimpanan, sehingga menghindari pengambilan baris yang pada akhirnya akan dibuang.

Prasyarat

Sebelum mengaktifkan fitur ini, pastikan kluster PolarDB for MySQL Anda memenuhi persyaratan versi berikut:

Versi 8.0, revisi 8.0.1.1.16 atau lebih baru
Versi 8.0, revisi 8.0.2.2.0 atau lebih baru

Untuk memeriksa versi kluster Anda, lihat Kueri versi engine.

Batasan

LIMIT OFFSET pushdown hanya berlaku ketika nilai OFFSET lebih besar dari 512. Untuk nilai OFFSET kecil, jumlah baris yang dilewati cukup sedikit sehingga overhead koordinasi pushdown lebih besar daripada manfaatnya dibanding jalur eksekusi standar.

Untuk menghapus batasan ini, atur ignore_polar_optimizer_rule ke ON. Untuk petunjuknya, lihat Tentukan parameter kluster dan node.

Parameter	Tingkat	Bawaan	Deskripsi
`ignore_polar_optimizer_rule`	Global dan session	OFF	Mengontrol batasan ambang batas OFFSET. Atur ke `ON` untuk menonaktifkan ambang batas dan mengizinkan pushdown untuk nilai OFFSET apa pun.

Aktifkan atau nonaktifkan LIMIT OFFSET pushdown

LIMIT OFFSET pushdown diaktifkan secara default. Gunakan parameter loose_optimizer_switch untuk mengubah statusnya. Untuk petunjuk mengubah parameter, lihat Tentukan parameter kluster dan node.

Parameter	Tingkat	Variabel	Bawaan	Deskripsi
`loose_optimizer_switch`	Global dan session	`limit_offset_pushdown`	ON	Mengaktifkan atau menonaktifkan LIMIT OFFSET pushdown.
`loose_optimizer_switch`	Global dan session	`detach_range_condition`	ON	Mengaktifkan atau menonaktifkan penurunan predikat. LIMIT OFFSET pushdown bergantung pada fitur ini ketika terdapat kondisi WHERE.

Verifikasi bahwa pushdown aktif

Jalankan EXPLAIN pada kueri Anda dan periksa bidang Extra. Saat LIMIT OFFSET pushdown aktif, output mencakup Using limit-offset pushdown. Jika string ini tidak muncul, kueri tersebut tidak memenuhi kondisi pushdown—lihat Saat pushdown tidak berlaku untuk alasan paling umum.

Contoh berikut menggunakan skema TPC-H.

Tidak ada kondisi predikat (Q1)

Pemindaian tabel penuh tanpa klausa WHERE. Tabel utama diakses secara langsung. Karena tidak ada pemfilteran yang tersisa di lapisan SQL, LIMIT OFFSET didorong ke bawah.

EXPLAIN
SELECT *
FROM lineitem
LIMIT 10000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 59440464
     filtered: 100.00
        Extra: Using limit-offset pushdown

Nilai Extra: Using limit-offset pushdown mengonfirmasi bahwa pemfilteran baris terjadi di lapisan mesin penyimpanan, bukan di lapisan SQL.

Kondisi range pada kunci utama (Q2)

Ketika kondisi WHERE didasarkan pada kunci utama, fitur penurunan predikat (detach_range_condition) menghapusnya dari lapisan SQL, sehingga memungkinkan LIMIT OFFSET didorong ke bawah.

EXPLAIN SELECT * FROM lineitem WHERE l_orderkey > 10 AND l_orderkey < 60000000 LIMIT 10000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: PRIMARY,i_l_orderkey,i_l_orderkey_quantity
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 29720232
     filtered: 100.00
        Extra: Using limit-offset pushdown

Indeks sekunder dengan lookup tabel (Q3)

Ketika indeks sekunder digunakan dan kueri memerlukan kolom dari tabel utama, LIMIT OFFSET pushdown melewatkan lookup tabel utama yang tidak perlu untuk baris yang berada di luar jendela hasil.

EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
          key: i_l_suppkey_partkey
      key_len: 5
          ref: NULL
         rows: 11123302
     filtered: 100.00
        Extra: Using limit-offset pushdown

ORDER BY dengan indeks

Ketika klausa ORDER BY dipenuhi oleh indeks, predikat dihapus di lapisan SQL dan LIMIT OFFSET didorong ke bawah.

EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
          key: i_l_suppkey_partkey
      key_len: 5
          ref: NULL
         rows: 11123302
     filtered: 100.00
        Extra: Using limit-offset pushdown

Saat pushdown tidak berlaku

LIMIT OFFSET pushdown mengharuskan tidak ada pekerjaan pemfilteran yang tersisa di lapisan SQL. Jika Extra tidak menampilkan Using limit-offset pushdown, periksa apakah salah satu kondisi berikut berlaku:

Kondisi	Alasan pushdown dilewati
Nilai OFFSET adalah 512 atau kurang	Melewatkan jumlah baris kecil lebih murah tanpa overhead pushdown. Atur `ignore_polar_optimizer_rule` ke `ON` untuk mengabaikan batasan ini.
Kondisi WHERE tidak dapat sepenuhnya didorong ke mesin penyimpanan	Pemfilteran yang tersisa di lapisan SQL mencegah LIMIT OFFSET didorong ke bawah. Hal ini terjadi ketika kueri menggunakan filesort atau ORDER BY yang tidak diindeks.

Perbaikan performa

Gambar berikut menunjukkan peningkatan waktu kueri yang diukur dengan dataset TPC-H pada faktor skala 10, membandingkan Q1, Q2, dan Q3 dengan dan tanpa LIMIT OFFSET pushdown diaktifkan.