Halo semua! Saya seorang pengembang backend yang menulis layanan mikro di Java + Spring. Saya bekerja di salah satu tim pengembangan produk internal di Tinkoff.
Di tim kami, pertanyaan tentang optimasi query di DBMS sering muncul. Anda selalu ingin menjadi sedikit lebih cepat, namun Anda tidak selalu bisa bertahan dengan indeks yang dibuat dengan cermatβAnda harus mencari beberapa solusi. Dalam salah satu penjelajahan web untuk mencari optimasi yang masuk akal saat bekerja dengan database, saya menemukan , penulis Penjelasan Kinerja SQL. Ini adalah jenis blog langka di mana Anda dapat membaca semua artikel secara berurutan.
Saya ingin menerjemahkan artikel pendek Marcus untuk Anda. Sampai batas tertentu, hal ini dapat disebut sebagai manifesto yang berupaya menarik perhatian pada masalah lama, namun masih relevan, yaitu masalah kinerja operasi offset menurut standar SQL.
Di beberapa tempat saya akan melengkapi penulis dengan penjelasan dan komentar. Saya akan menyebut semua tempat seperti itu sebagai βkira-kira.β untuk lebih jelasnya
Pengenalan kecil
Saya rasa banyak orang tahu betapa bermasalah dan lambatnya bekerja dengan pemilihan halaman melalui offset. Tahukah Anda bahwa ini dapat dengan mudah diganti dengan desain yang lebih efisien?
Jadi, kata kunci offset memberitahu database untuk melewatkan n catatan pertama dalam permintaan. Namun, database masih perlu membaca n catatan pertama ini dari disk, dalam urutan yang diberikan (catatan: terapkan pengurutan jika ditentukan), dan baru setelah itu dimungkinkan untuk mengembalikan catatan dari n+1 dan seterusnya. Hal yang paling menarik adalah masalahnya bukan pada implementasi spesifik di DBMS, tetapi pada definisi asli sesuai standar:
β¦barisnya diurutkan terlebih dahulu menurut dan kemudian dibatasi dengan menghilangkan jumlah baris yang ditentukan dalam dari awal...
-SQL:2016, Bagian 2, 4.15.3 Tabel turunan (catatan: saat ini merupakan standar yang paling banyak digunakan)
Poin kuncinya di sini adalah offset membutuhkan satu parameter - jumlah rekaman yang dilewati, dan hanya itu. Mengikuti definisi ini, DBMS hanya dapat mengambil semua record dan kemudian membuang yang tidak diperlukan. Jelasnya, definisi offset ini memaksa kita untuk melakukan pekerjaan ekstra. Dan tidak peduli apakah itu SQL atau NoSQL.
Sedikit lagi rasa sakitnya
Masalah offset tidak berakhir di situ, dan inilah alasannya. Jika, di antara membaca dua halaman data dari disk, operasi lain menyisipkan record baru, apa yang akan terjadi dalam kasus ini?
Ketika offset digunakan untuk melewatkan catatan dari halaman sebelumnya, dalam situasi menambahkan catatan baru di antara pembacaan halaman yang berbeda, kemungkinan besar Anda akan mendapatkan duplikat (catatan: ini mungkin terjadi ketika kita membaca halaman demi halaman menggunakan urutan berdasarkan konstruksi, lalu ditengah-tengah keluaran kita mungkin akan mendapat masukan baru).
Gambar tersebut dengan jelas menggambarkan situasi ini. Basis membaca 10 catatan pertama, setelah itu catatan baru dimasukkan, yang mengimbangi semua catatan yang dibaca dengan 1. Kemudian basis mengambil halaman baru dari 10 catatan berikutnya dan dimulai bukan dari tanggal 11, sebagaimana mestinya, tetapi dari 10, menduplikasi rekor ini. Ada anomali lain yang terkait dengan penggunaan ungkapan ini, tapi ini yang paling umum.
Seperti yang telah kita ketahui, ini bukanlah masalah DBMS tertentu atau implementasinya. Masalahnya adalah dalam menentukan pagination menurut standar SQL. Kami memberi tahu DBMS halaman mana yang harus diambil atau berapa banyak catatan yang harus dilewati. Basis data tidak dapat mengoptimalkan permintaan semacam itu, karena informasi yang tersedia terlalu sedikit.
Perlu juga diklarifikasi bahwa ini bukan masalah pada kata kunci tertentu, melainkan pada semantik kueri. Ada beberapa sintaksis lain yang identik dalam sifat masalahnya:
- Kata kunci offset seperti yang disebutkan sebelumnya.
- Konstruksi dua kata kunci limit [offset] (meskipun limit itu sendiri tidak terlalu buruk).
- Memfilter berdasarkan batas bawah, berdasarkan penomoran baris (misalnya, nomor_baris(), nomor baris, dll.).
Semua ekspresi ini hanya memberi tahu Anda berapa banyak baris yang harus dilewati, tanpa informasi atau konteks tambahan.
Nanti di artikel ini, kata kunci offset digunakan sebagai ringkasan dari semua opsi ini.
Hidup tanpa OFFSET
Sekarang mari kita bayangkan seperti apa dunia kita tanpa semua masalah ini. Ternyata hidup tanpa offset tidak begitu sulit: dengan memilih, Anda hanya dapat memilih baris-baris yang belum kita lihat (catatan: yaitu baris-baris yang tidak ada di halaman sebelumnya), dengan menggunakan kondisi di mana.
Dalam hal ini, kita mulai dari fakta bahwa pemilihan dieksekusi pada set yang diurutkan (urutan lama yang baik oleh). Karena kita mempunyai kumpulan yang terurut, kita dapat menggunakan filter yang cukup sederhana untuk mendapatkan hanya data yang berada di belakang catatan terakhir dari halaman sebelumnya:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYItulah prinsip keseluruhan dari pendekatan ini. Tentu saja, segalanya menjadi lebih menyenangkan ketika mengurutkan berdasarkan banyak kolom, namun idenya tetap sama. Penting untuk dicatat bahwa desain ini dapat diterapkan pada banyak orang -keputusan.
Pendekatan ini disebut metode pencarian atau penomoran halaman keyset. Ini memecahkan masalah hasil mengambang (catatan: situasi dengan penulisan di antara halaman yang dibaca dijelaskan sebelumnya) dan, tentu saja, yang kita semua sukai, ini bekerja lebih cepat dan lebih stabil daripada offset klasik. Stabilitas terletak pada kenyataan bahwa waktu pemrosesan permintaan tidak bertambah sebanding dengan jumlah tabel yang diminta (catatan: jika Anda ingin mempelajari lebih lanjut tentang cara kerja berbagai pendekatan penomoran halaman, Anda dapat . Anda juga dapat menemukan tolok ukur komparatif untuk berbagai metode di sana).
Salah satu slide bahwa penomoran halaman dengan kunci, tentu saja, tidak mahakuasa - ia memiliki keterbatasan. Yang paling signifikan adalah dia tidak memiliki kemampuan membaca halaman acak (catatan: tidak konsisten). Namun, di era pengguliran tanpa akhir (catatan: di bagian depan), hal ini tidak menjadi masalah. Menentukan nomor halaman untuk diklik adalah keputusan yang buruk dalam desain UI (catatan: pendapat penulis artikel).
Bagaimana dengan alatnya?
Penomoran halaman pada kunci seringkali tidak cocok karena kurangnya dukungan instrumental untuk metode ini. Sebagian besar alat pengembangan, termasuk berbagai kerangka kerja, tidak memungkinkan Anda memilih dengan tepat bagaimana penomoran halaman akan dilakukan.
Situasi ini diperparah oleh kenyataan bahwa metode yang dijelaskan memerlukan dukungan ujung ke ujung dalam teknologi yang digunakan - mulai dari DBMS hingga eksekusi permintaan AJAX di browser dengan pengguliran tanpa akhir. Daripada hanya menentukan nomor halaman, Anda kini harus menentukan sekumpulan kunci untuk semua halaman sekaligus.
Namun, jumlah kerangka kerja yang mendukung penomoran halaman pada kunci secara bertahap bertambah. Inilah yang kami miliki saat ini:
- untuk Jawa;
- untuk Ruby;
- ΠΈ untuk Django;
- untuk Python;
- β kriteria API untuk implementasi JPA;
- untuk Perl;
- , pembuat peta untuk Node.js .
(Catatan: beberapa link telah dihapus karena pada saat penerjemahan beberapa perpustakaan belum diperbarui sejak 2017-2018. Jika tertarik, Anda dapat melihat sumber aslinya.)
Pada saat inilah bantuan Anda dibutuhkan. Jika Anda mengembangkan atau mendukung kerangka kerja yang menggunakan penomoran halaman, maka saya bertanya, saya mendorong, saya mohon Anda untuk memberikan dukungan asli untuk penomoran halaman pada kunci. Jika Anda memiliki pertanyaan atau butuh bantuan, saya akan dengan senang hati membantu (, , ) (catatan: dari pengalaman saya dengan Marcus, saya dapat mengatakan bahwa dia sangat antusias menyebarkan topik ini).
Jika Anda menggunakan solusi siap pakai yang menurut Anda layak mendapat dukungan untuk penomoran halaman berdasarkan kunci, buat permintaan atau bahkan tawarkan solusi siap pakai, jika memungkinkan. Anda juga dapat menautkan ke artikel ini.
Kesimpulan
Alasan mengapa pendekatan yang sederhana dan berguna seperti penomoran halaman berdasarkan kunci tidak tersebar luas bukan karena pendekatan tersebut sulit untuk diterapkan secara teknis atau memerlukan banyak usaha. Alasan utamanya adalah banyak yang terbiasa melihat dan bekerja dengan offset - pendekatan ini ditentukan oleh standar itu sendiri.
Akibatnya, hanya sedikit orang yang berpikir untuk mengubah pendekatan pagination, dan karena itu, dukungan instrumental dari kerangka kerja dan perpustakaan kurang berkembang. Oleh karena itu, jika ide dan tujuan penomoran halaman bebas offset dekat dengan Anda, bantu sebarkan!
Sumber:
Pengarang: Markus Winand
Sumber: www.habr.com