Lewatlah sudah hari-hari ketika Anda tidak perlu khawatir tentang mengoptimalkan kinerja database. Waktu tidak berhenti. Setiap pengusaha teknologi baru ingin menciptakan Facebook berikutnya, sambil mencoba mengumpulkan semua data yang bisa mereka peroleh. Bisnis memerlukan data ini untuk melatih model dengan lebih baik sehingga dapat membantu mereka menghasilkan uang. Dalam kondisi seperti itu, pemrogram perlu membuat API yang memungkinkan mereka bekerja dengan cepat dan andal dengan informasi dalam jumlah besar.
Jika Anda telah merancang backend aplikasi atau database untuk waktu yang lama, Anda mungkin telah menulis kode untuk menjalankan kueri yang diberi nomor halaman. Misalnya seperti ini:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Bagaimana keadaannya?
Namun jika ini adalah cara Anda melakukan penomoran halaman, mohon maaf karena Anda tidak melakukannya dengan cara yang paling efisien.
Apakah Anda ingin menolak saya? . , ΠΈ Mereka sudah menggunakan teknik yang ingin saya bicarakan hari ini.
Sebutkan setidaknya satu pengembang backend yang belum pernah menggunakan OFFSET ΠΈ LIMIT untuk melakukan kueri paginasi. Dalam MVP (Produk yang Layak Minimum) dan dalam proyek yang menggunakan sejumlah kecil data, pendekatan ini cukup dapat diterapkan. Bisa dikatakan, ini βberhasilβ.
Namun jika Anda perlu membuat sistem yang andal dan efisien dari awal, Anda harus memperhatikan efisiensi kueri database yang digunakan dalam sistem tersebut terlebih dahulu.
Hari ini kita akan membahas masalah dengan implementasi mesin kueri yang diberi nomor halaman yang umum digunakan (terlalu buruk), dan cara mencapai kinerja tinggi saat menjalankan kueri tersebut.
Apa yang salah dengan OFFSET dan LIMIT?
Seperti yang sudah dikatakan, OFFSET ΠΈ LIMIT Mereka bekerja dengan baik dalam proyek yang tidak perlu bekerja dengan data dalam jumlah besar.
Masalah muncul ketika database tumbuh sedemikian rupa sehingga tidak lagi muat di memori server. Namun, saat bekerja dengan database ini, Anda perlu menggunakan kueri yang diberi nomor halaman.
Agar masalah ini terwujud, harus ada situasi di mana DBMS menggunakan operasi Pemindaian Tabel Lengkap yang tidak efisien pada setiap kueri yang diberi nomor halaman (sementara operasi penyisipan dan penghapusan dapat terjadi, dan kami tidak memerlukan data yang ketinggalan jaman!).
Apa yang dimaksud dengan βpemindaian tabel penuhβ (atau βpemindaian tabel berurutanβ, Pemindaian Berurutan)? Ini adalah operasi di mana DBMS secara berurutan membaca setiap baris tabel, yaitu data yang terkandung di dalamnya, dan memeriksa kepatuhannya terhadap kondisi tertentu. Jenis pemindaian tabel ini dikenal paling lambat. Faktanya adalah ketika dijalankan, banyak operasi input/output dilakukan yang melibatkan subsistem disk server. Situasi ini diperburuk oleh latensi yang terkait dengan bekerja dengan data yang disimpan di disk, dan fakta bahwa mentransfer data dari disk ke memori adalah operasi yang menghabiskan banyak sumber daya.
Misalnya, Anda memiliki catatan 100000000 pengguna dan Anda menjalankan kueri dengan konstruksi OFFSET 50000000. Ini berarti bahwa DBMS harus memuat semua catatan ini (dan kita bahkan tidak memerlukannya!), menyimpannya dalam memori, dan setelah itu mengambil, katakanlah, 20 hasil yang dilaporkan dalam LIMIT.
Misalkan tampilannya seperti ini: "pilih baris dari 50000 hingga 50020 dari 100000". Artinya, sistem harus memuat 50000 baris terlebih dahulu untuk menyelesaikan kueri. Apakah Anda melihat berapa banyak pekerjaan tidak perlu yang harus dia lakukan?
Jika tidak percaya, lihat contoh yang saya buat menggunakan fitur-fitur tersebut .
Contoh di db-fiddle.com
Di sana, di sebelah kiri, di lapangan Schema SQL, ada kode yang memasukkan 100000 baris ke dalam database, dan di sebelah kanan, di bidang Query SQL, dua kueri ditampilkan. Yang pertama, lambat, terlihat seperti ini:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Dan yang kedua, yang merupakan solusi efektif untuk masalah yang sama, adalah seperti ini:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Untuk memenuhi permintaan ini, cukup klik tombolnya Run di bagian atas halaman. Setelah melakukan ini, kami membandingkan informasi tentang waktu eksekusi kueri. Ternyata mengeksekusi kueri yang tidak efisien membutuhkan waktu setidaknya 30 kali lebih lama daripada mengeksekusi kueri kedua (kali ini bervariasi dari satu proses ke proses lainnya; misalnya, sistem mungkin melaporkan bahwa kueri pertama membutuhkan waktu 37 ms untuk diselesaikan, namun eksekusi kueri kedua - 1 mdtk).
Dan jika ada lebih banyak data, maka semuanya akan terlihat lebih buruk (untuk memastikannya, lihat milik saya dengan 10 juta baris).
Apa yang baru saja kita diskusikan akan memberi Anda beberapa wawasan tentang bagaimana query database sebenarnya diproses.
Harap dicatat bahwa semakin tinggi nilainya OFFSET β semakin lama waktu yang dibutuhkan untuk menyelesaikan permintaan.
Apa yang harus saya gunakan selain kombinasi OFFSET dan LIMIT?
Daripada kombinasi OFFSET ΠΈ LIMIT Sebaiknya gunakan struktur yang dibangun sesuai dengan skema berikut:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Ini adalah eksekusi kueri dengan penomoran halaman berbasis kursor.
Daripada menyimpan yang sekarang secara lokal OFFSET ΠΈ LIMIT dan mengirimkannya dengan setiap permintaan, Anda perlu menyimpan kunci utama yang terakhir diterima (biasanya demikian ID) Dan LIMIT, alhasil akan diperoleh query serupa dengan di atas.
Mengapa? Intinya adalah dengan secara eksplisit menentukan pengidentifikasi baris terakhir yang dibaca, Anda memberi tahu DBMS Anda di mana DBMS harus mulai mencari data yang diperlukan. Selain itu, pencarian, berkat penggunaan kunci, akan dilakukan secara efisien; sistem tidak perlu terganggu oleh garis di luar rentang yang ditentukan.
Mari kita lihat perbandingan kinerja berbagai kueri berikut ini. Inilah kueri yang tidak efektif.
Permintaan lambat
Dan inilah versi yang dioptimalkan dari permintaan ini.
Permintaan cepat
Kedua kueri mengembalikan jumlah data yang persis sama. Namun yang pertama membutuhkan waktu 12,80 detik untuk menyelesaikannya, dan yang kedua membutuhkan waktu 0,01 detik. Apakah Anda merasakan perbedaannya?
Kemungkinan masalah
Agar metode kueri yang diusulkan berfungsi secara efektif, tabel harus memiliki kolom (atau kolom) yang berisi indeks unik dan berurutan, seperti pengidentifikasi bilangan bulat. Dalam beberapa kasus tertentu, hal ini mungkin menentukan keberhasilan penggunaan kueri tersebut untuk meningkatkan kecepatan bekerja dengan database.
Biasanya, saat membuat kueri, Anda perlu mempertimbangkan arsitektur spesifik tabel dan memilih mekanisme yang paling berfungsi pada tabel yang ada. Misalnya, jika Anda perlu mengerjakan kueri dengan data terkait dalam jumlah besar, Anda mungkin menganggapnya menarik artikel.
Jika kita dihadapkan pada masalah hilangnya kunci utama, misalnya jika kita memiliki tabel dengan hubungan banyak ke banyak, maka pendekatan tradisional menggunakan OFFSET ΠΈ LIMIT, dijamin cocok untuk kita. Namun penggunaannya mungkin berpotensi menghasilkan kueri yang lambat. Dalam kasus seperti itu, saya akan merekomendasikan penggunaan kunci utama yang bertambah secara otomatis, meskipun kunci tersebut hanya diperlukan untuk menangani kueri yang diberi nomor halaman.
Jika Anda tertarik dengan topik ini - , ΠΈ - beberapa bahan bermanfaat.
Hasil
Kesimpulan utama yang dapat kita tarik adalah, berapa pun ukuran database yang kita bicarakan, analisis kecepatan eksekusi kueri selalu diperlukan. Saat ini, skalabilitas solusi sangatlah penting, dan jika semuanya dirancang dengan benar sejak awal pengerjaan sistem tertentu, hal ini di masa depan dapat menyelamatkan pengembang dari banyak masalah.
Bagaimana Anda menganalisis dan mengoptimalkan kueri basis data?
Sumber: www.habr.com