Penurunan LIMIT OFFSET - PolarDB

Saat menjalankan kueri yang melibatkan sejumlah besar data, lapisan SQL mengonsumsi banyak sumber daya komputasi karena membaca data dari lapisan mesin penyimpanan dan melakukan komputasi. Untuk meningkatkan kinerja kueri, PolarDB for MySQL menurunkan LIMIT OFFSET ke lapisan mesin penyimpanan. Topik ini menjelaskan fitur penurunan LIMIT OFFSET.

Prasyarat

Versi kluster PolarDB for MySQL Anda adalah 8.0, dengan versi revisi memenuhi persyaratan berikut:

8.0.1.1.16 atau lebih baru
8.0.2.2.0 atau lebih baru

Untuk informasi lebih lanjut tentang cara memeriksa versi kluster, lihat Kueri versi mesin.

Latar Belakang

Di komunitas MySQL, klausa LIMIT dieksekusi di lapisan SQL. Lapisan SQL membaca data dari lapisan mesin penyimpanan, memproses kueri, dan mengembalikan hasil yang difilter berdasarkan OFFSET. Saat indeks sekunder di-query dan kolom dalam tabel utama perlu diakses, lapisan SQL harus mendapatkan data kolom dari tabel. Jika tidak ada kondisi WHERE yang diproses di lapisan SQL, termasuk saat predikat sepenuhnya diturunkan ke lapisan mesin penyimpanan, data difilter berdasarkan klausa LIMIT OFFSET tanpa komputasi tambahan. Dalam hal ini, sejumlah besar data ditransmisikan dari lapisan mesin penyimpanan ke lapisan SQL. Untuk kueri halaman, ini menyebabkan waktu respons lebih lama seiring bertambahnya jumlah halaman. PolarDB for MySQL menurunkan klausa LIMIT OFFSET ke mesin penyimpanan, sehingga data difilter di lapisan mesin penyimpanan dan tidak diambil saat menggunakan indeks sekunder.

Batasan

Nilai OFFSET harus lebih besar dari 512.

Catatan Untuk menonaktifkan batasan ini, atur parameter ignore_polar_optimizer_rule ke ON. Untuk informasi lebih lanjut, lihat Konfigurasikan parameter kluster dan node.

Parameter	Tingkat	Deskripsi
ignore_polar_optimizer_rule	Global dan sesi	Menentukan apakah akan menonaktifkan batas untuk nilai OFFSET. Nilai default: OFF. Nilai valid: ON: Batas dinonaktifkan. OFF: Batas diaktifkan.

Menggunakan fitur penurunan LIMIT OFFSET

Anda dapat menggunakan variabel limit_offset_pushdown dari parameter loose_optimizer_switch untuk mengaktifkan fitur penurunan LIMIT OFFSET. Untuk informasi lebih lanjut, lihat Konfigurasikan parameter kluster dan node.

Parameter	Tingkat	Deskripsi
loose_optimizer_switch	Global dan sesi	Menentukan apakah akan mengaktifkan fitur optimasi kueri PolarDB. Variabel terkait dengan fitur penurunan LIMIT OFFSET: limit_offset_pushdown: menentukan apakah akan mengaktifkan fitur penurunan LIMIT OFFSET. Nilai default: ON. Nilai valid: ON OFF detach_range_condition: menentukan apakah akan mengaktifkan fitur penurunan predikat. Nilai default: ON. Nilai valid: ON OFF

Pengujian kinerja

Dalam pengujian kinerja ini, skema TPC-H digunakan. Saat fitur penurunan LIMIT OFFSET diaktifkan dan Anda menjalankan pernyataan EXPLAIN untuk melihat rencana eksekusi, Menggunakan penurunan limit-offset dikembalikan untuk parameter Extra.

Common LIMIT OFFSET scenario

Dalam contoh berikut, Q1 dari tes TPC-H digunakan. Tabel utama diakses dan pernyataan tidak mengandung kondisi predikat.

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: Menggunakan penurunan limit-offset

LIMIT OFFSET scenario with predicate conditions

Untuk kueri yang mengandung kondisi predikat, jika kondisi predikat mencakup rentang berdasarkan mana mesin penyimpanan memindai data, kondisi predikat dihapus oleh fitur penurunan predikat penuh. Dalam hal ini, klausa LIMIT OFFSET dapat diturunkan.

Rencana eksekusi Q2 mengakses kunci utama dan berisi kondisi rentang berdasarkan kunci utama.

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: Menggunakan penurunan limit-offset

Rencana eksekusi Q3 mengakses indeks sekunder, berisi kondisi rentang berdasarkan kunci utama, dan harus mendapatkan data kolom lain dari tabel.

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: Menggunakan penurunan limit-offset

LIMIT OFFSET scenario with ORDER BY clauses and ordering indexes

Dalam kueri halaman standar, ORDER BY digunakan untuk mengurutkan hasil kueri. Saat indeks digunakan untuk mempercepat klausa ORDER BY, klausa LIMIT OFFSET dapat diturunkan setelah predikat dihapus di lapisan SQL.

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: Menggunakan penurunan limit-offset

Peningkatan kinerja

Tes TPC-H dilakukan dengan faktor skala 10. Q1, Q2, dan Q3 digunakan. Gambar berikut menunjukkan perbedaan kinerja antara mengaktifkan dan menonaktifkan fitur penurunan LIMIT OFFSET. Performance improvements