Sudah berlalu hari anda tidak perlu risau tentang mengoptimumkan prestasi pangkalan data. Masa tidak berhenti. Setiap usahawan teknologi baharu mahu mencipta Facebook seterusnya, sambil cuba mengumpul semua data yang mereka boleh dapatkan. Perniagaan memerlukan data ini untuk melatih model yang lebih baik yang membantu mereka menjana wang. Dalam keadaan sedemikian, pengaturcara perlu mencipta API yang membolehkan mereka bekerja dengan cepat dan boleh dipercayai dengan sejumlah besar maklumat.
Jika anda telah mereka bentuk backend aplikasi atau pangkalan data untuk sebarang tempoh masa, anda mungkin telah menulis kod untuk menjalankan pertanyaan bernombor. Sebagai contoh, seperti ini:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Caranya?
Tetapi jika ini adalah cara anda melakukan penomboran anda, saya minta maaf untuk mengatakan bahawa anda tidak melakukannya dengan cara yang paling cekap.
Adakah anda mahu membantah saya? . , ΠΈ Mereka sudah menggunakan teknik yang saya ingin bincangkan hari ini.
Namakan sekurang-kurangnya seorang pembangun bahagian belakang yang tidak pernah menggunakan OFFSET ΠΈ LIMIT untuk melakukan pertanyaan bernombor. Dalam MVP (Minimum Viable Product) dan dalam projek di mana sejumlah kecil data digunakan, pendekatan ini agak terpakai. Ia "hanya berfungsi," boleh dikatakan.
Tetapi jika anda perlu mencipta sistem yang boleh dipercayai dan cekap dari awal, anda harus berhati-hati terlebih dahulu tentang kecekapan menyoal pangkalan data yang digunakan dalam sistem tersebut.
Hari ini kita akan bercakap tentang masalah dengan pelaksanaan yang biasa digunakan (terlalu buruk) bagi enjin pertanyaan bernombor, dan cara untuk mencapai prestasi tinggi apabila melaksanakan pertanyaan sedemikian.
Apa yang salah dengan OFFSET dan LIMIT?
Seperti yang telah dikatakan, OFFSET ΠΈ LIMIT Mereka berprestasi baik dalam projek yang tidak perlu berfungsi dengan jumlah data yang besar.
Masalah timbul apabila pangkalan data berkembang kepada saiz sedemikian sehingga ia tidak lagi sesuai dalam ingatan pelayan. Walau bagaimanapun, apabila bekerja dengan pangkalan data ini, anda perlu menggunakan pertanyaan bernombor.
Untuk masalah ini nyata, mesti ada situasi di mana DBMS menggunakan operasi Imbasan Jadual Penuh yang tidak cekap pada setiap pertanyaan bernombor (sementara operasi sisipan dan pemadaman mungkin berlaku dan kami tidak memerlukan data lapuk!).
Apakah itu "imbasan jadual penuh" (atau "imbasan jadual berurutan", Imbasan Berjujukan)? Ini ialah operasi di mana DBMS membaca secara berurutan setiap baris jadual, iaitu, data yang terkandung di dalamnya, dan menyemaknya untuk pematuhan dengan syarat tertentu. Imbasan jadual jenis ini dikenali sebagai yang paling perlahan. Hakikatnya ialah apabila ia dilaksanakan, banyak operasi input/output dilakukan yang melibatkan subsistem cakera pelayan. Keadaan ini bertambah buruk oleh kelewatan yang berkaitan dengan bekerja dengan data yang disimpan pada cakera, dan fakta bahawa memindahkan data dari cakera ke memori adalah operasi intensif sumber.
Sebagai contoh, anda mempunyai rekod 100000000 pengguna dan anda menjalankan pertanyaan dengan binaan OFFSET 50000000. Ini bermakna DBMS perlu memuatkan semua rekod ini (dan kami tidak memerlukannya!), meletakkannya dalam ingatan, dan selepas itu ambil, katakan, 20 keputusan dilaporkan dalam LIMIT.
Katakan ia mungkin kelihatan seperti ini: "pilih baris daripada 50000 hingga 50020 daripada 100000". Iaitu, sistem perlu memuatkan 50000 baris terlebih dahulu untuk menyelesaikan pertanyaan. Adakah anda melihat berapa banyak kerja yang tidak perlu dia perlu lakukan?
Jika anda tidak percaya saya, lihat contoh yang saya buat menggunakan ciri .
Contoh di db-fiddle.com
Di sana, di sebelah kiri, di padang Schema SQL, terdapat kod yang memasukkan 100000 baris ke dalam pangkalan data, dan di sebelah kanan, dalam medan Query SQL, dua pertanyaan ditunjukkan. Yang pertama, perlahan, kelihatan seperti ini:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Dan yang kedua, yang merupakan penyelesaian yang berkesan untuk masalah yang sama, adalah seperti ini:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Untuk memenuhi permintaan ini, hanya klik pada butang Run di bahagian atas halaman. Setelah melakukan ini, kami membandingkan maklumat tentang masa pelaksanaan pertanyaan. Ternyata melaksanakan pertanyaan yang tidak berkesan mengambil masa sekurang-kurangnya 30 kali lebih lama daripada melaksanakan pertanyaan kedua (kali ini berbeza dari run ke run; sebagai contoh, sistem mungkin melaporkan bahawa pertanyaan pertama mengambil masa 37 ms untuk diselesaikan, tetapi pelaksanaan kedua - 1 ms).
Dan jika terdapat lebih banyak data, maka segala-galanya akan kelihatan lebih teruk (untuk yakin tentang ini, lihatlah saya dengan 10 juta baris).
Perkara yang baru kita bincangkan seharusnya memberi anda sedikit gambaran tentang cara pertanyaan pangkalan data sebenarnya diproses.
Sila ambil perhatian bahawa semakin tinggi nilai OFFSET β semakin lama permintaan akan diambil untuk diselesaikan.
Apakah yang perlu saya gunakan dan bukannya gabungan OFFSET dan LIMIT?
Daripada gabungan OFFSET ΠΈ LIMIT Ia bernilai menggunakan struktur yang dibina mengikut skema berikut:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Ini adalah pelaksanaan pertanyaan dengan penomboran berasaskan kursor.
Daripada menyimpan yang semasa secara tempatan OFFSET ΠΈ LIMIT dan menghantarnya dengan setiap permintaan, anda perlu menyimpan kunci utama yang terakhir diterima (biasanya ini ID) Dan LIMIT, akibatnya, pertanyaan yang serupa dengan di atas akan diperolehi.
kenapa? Intinya ialah dengan menyatakan secara eksplisit pengecam baris terakhir yang dibaca, anda memberitahu DBMS anda di mana ia perlu mula mencari data yang diperlukan. Selain itu, carian, terima kasih kepada penggunaan kunci, akan dijalankan dengan cekap; sistem tidak perlu terganggu oleh garisan di luar julat yang ditentukan.
Mari kita lihat perbandingan prestasi berikut bagi pelbagai pertanyaan. Berikut ialah pertanyaan yang tidak berkesan.
Permintaan perlahan
Dan berikut ialah versi yang dioptimumkan untuk permintaan ini.
Permintaan cepat
Kedua-dua pertanyaan mengembalikan jumlah data yang sama. Tetapi yang pertama mengambil masa 12,80 saat untuk disiapkan, dan yang kedua mengambil masa 0,01 saat. Adakah anda merasakan perbezaannya?
Masalah yang mungkin berlaku
Untuk kaedah pertanyaan yang dicadangkan berfungsi dengan berkesan, jadual mesti mempunyai lajur (atau lajur) yang mengandungi indeks berjujukan yang unik, seperti pengecam integer. Dalam beberapa kes tertentu, ini mungkin menentukan kejayaan menggunakan pertanyaan sedemikian untuk meningkatkan kelajuan bekerja dengan pangkalan data.
Sememangnya, semasa membina pertanyaan, anda perlu mengambil kira seni bina khusus jadual dan memilih mekanisme yang akan berfungsi paling baik pada jadual sedia ada. Sebagai contoh, jika anda perlu bekerja dalam pertanyaan dengan jumlah besar data berkaitan, anda mungkin mendapati ia menarik artikel.
Jika kita berhadapan dengan masalah kehilangan kunci utama, sebagai contoh, jika kita mempunyai jadual dengan hubungan banyak-ke-banyak, maka pendekatan tradisional menggunakan OFFSET ΠΈ LIMIT, dijamin sesuai dengan kami. Tetapi penggunaannya boleh mengakibatkan pertanyaan yang lambat. Dalam kes sebegini, saya akan mengesyorkan menggunakan kunci utama peningkatan automatik, walaupun ia hanya diperlukan untuk mengendalikan pertanyaan bernombor.
Jika anda berminat dengan topik ini - , ΠΈ - beberapa bahan berguna.
Keputusan
Kesimpulan utama yang boleh kita buat ialah, tidak kira apa saiz pangkalan data yang kita bicarakan, ia sentiasa perlu untuk menganalisis kelajuan pelaksanaan pertanyaan. Pada masa kini, kebolehskalaan penyelesaian adalah sangat penting, dan jika semuanya direka dengan betul dari awal bekerja pada sistem tertentu, ini, pada masa hadapan, boleh menyelamatkan pemaju daripada banyak masalah.
Bagaimanakah anda menganalisis dan mengoptimumkan pertanyaan pangkalan data?
Sumber: www.habr.com