Wis dina nalika sampeyan ora perlu kuwatir babagan ngoptimalake kinerja database. Wektu ora mandheg. Saben pengusaha teknologi anyar pengin nggawe Facebook sabanjure, nalika nyoba ngumpulake kabeh data sing bisa ditindakake. Bisnis mbutuhake data iki kanggo nglatih model sing luwih apik sing mbantu nggawe dhuwit. Ing kahanan kasebut, programer kudu nggawe API sing ngidini dheweke bisa kanthi cepet lan andal nggarap informasi sing akeh banget.
Yen sampeyan wis ngrancang aplikasi utawa backend basis data kanggo sawetara wektu, sampeyan mbokmenawa wis nulis kode kanggo mbukak pitakon paginated. Contone, kaya iki:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Carane iku?
Nanging yen iki carane sampeyan nindakake pagination, Aku njaluk ngapura yen sampeyan ora nindakake ing cara sing paling efisien.
Apa sampeyan pengin mbantah aku? . , ΠΈ Dheweke wis nggunakake teknik sing dakkarepake saiki.
Sebutake paling ora siji pangembang backend sing durung nate digunakake OFFSET ΠΈ LIMIT kanggo nindakake pitakon paginated. Ing MVP (Minimum Viable Product) lan ing proyek ngendi jumlah cilik saka data digunakake, pendekatan iki cukup ditrapake. Iku "mung dianggo," dadi kanggo ngomong.
Nanging yen sampeyan kudu nggawe sistem sing dipercaya lan efisien saka awal, sampeyan kudu ngati-ati sadurunge babagan efisiensi pitakon database sing digunakake ing sistem kasebut.
Dina iki kita bakal ngomong babagan masalah sing umum digunakake (banget banget) implementasi mesin pitakon paginated, lan carane entuk kinerja dhuwur nalika nglakokake pitakon kasebut.
Apa sing salah karo OFFSET lan LIMIT?
Kaya sing wis diandharake, OFFSET ΠΈ LIMIT Dheweke nindakake kanthi apik ing proyek sing ora perlu nggarap data sing akeh.
Masalah muncul nalika database mundak akeh kanggo ukuran sing ora pas ing memori server. Nanging, nalika nggarap database iki, sampeyan kudu nggunakake pitakon paginated.
Kanggo masalah iki nyata, kudu ana kahanan sing DBMS nggunakake operasi Scan Tabel Lengkap sing ora efisien ing saben pitakon paginated (nalika operasi selipan lan pambusakan bisa kedadeyan, lan kita ora butuh data sing wis lawas!).
Apa iku "scan tabel lengkap" (utawa "scan sequential table", Sequential Scan)? Iki minangka operasi sajrone DBMS maca kanthi urut saben baris tabel, yaiku, data sing ana ing kono, lan mriksa manawa tundhuk karo kondisi tartamtu. Jinis pindai tabel iki dikenal minangka sing paling alon. Kasunyatane yaiku nalika dieksekusi, akeh operasi input / output sing ditindakake sing melu subsistem disk server. Kahanan kasebut dadi luwih elek amarga latensi sing ana gandhengane karo nggarap data sing disimpen ing disk, lan kasunyatan manawa nransfer data saka disk menyang memori minangka operasi intensif sumber daya.
Contone, sampeyan duwe cathetan 100000000 pangguna lan sampeyan mbukak pitakon kanthi konstruk OFFSET 50000000. Iki tegese DBMS kudu mbukak kabeh cathetan kasebut (lan kita ora butuh!), Lebokake ing memori, lan sawise njupuk, ucapake, 20 asil dilaporake ing LIMIT.
Ayo dadi kaya iki: "pilih baris saka 50000 nganti 50020 saka 100000". Tegese, sistem pisanan kudu mbukak 50000 baris kanggo ngrampungake pitakon. Apa sampeyan ndeleng carane akeh karya rasah dheweke kudu nindakake?
Yen sampeyan ora ngandel, deleng conto sing digawe nggunakake fitur kasebut .
Tuladha ing db-fiddle.com
Ana, ing sisih kiwa, ing lapangan Schema SQL, ana kode sing nglebokake 100000 larik menyang database, lan ing sisih tengen, ing lapangan Query SQL, loro pitakon ditampilake. Sing pisanan, alon, katon kaya iki:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Lan nomer loro, sing minangka solusi efektif kanggo masalah sing padha, kaya mangkene:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Kanggo ngrampungake panjaluk kasebut, mung klik tombol kasebut Run ing sisih ndhuwur kaca. Sawise nindakake iki, kita mbandhingake informasi babagan wektu eksekusi query. Pranyata yen nglakokake pitakon sing ora efektif mbutuhake paling ora 30 kaping luwih suwe tinimbang nglakokake sing nomer loro (wektu iki beda-beda gumantung saka run to run; contone, sistem bisa nglaporake yen query pisanan njupuk 37 ms kanggo ngrampungake, nanging eksekusi kapindho - 1 ms).
Lan yen ana luwih akeh data, mula kabeh bakal katon luwih elek (kanggo yakin babagan iki, deleng my kanthi 10 yuta larik).
Apa sing lagi wae kita rembugan kudu menehi katrangan babagan carane pitakon database bener diproses.
Wigati dimangerteni manawa luwih dhuwur regane OFFSET - luwih suwe panyuwunan bakal ditindakake.
Apa sing kudu digunakake tinimbang kombinasi OFFSET lan LIMIT?
Tinimbang kombinasi OFFSET ΠΈ LIMIT Sampeyan kudu nggunakake struktur sing dibangun miturut skema ing ngisor iki:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Iki minangka eksekusi pitakon kanthi pagination adhedhasar kursor.
Tinimbang nyimpen sing saiki sacara lokal OFFSET ΠΈ LIMIT lan ngirimake karo saben panjalukan, sampeyan kudu nyimpen kunci utami pungkasan sing ditampa (biasane iki ID) lan LIMIT, minangka asil, pitakon sing padha karo ing ndhuwur bakal dipikolehi.
Kenging punapa? Intine yaiku kanthi jelas nemtokake pengenal saka baris pungkasan sing diwaca, sampeyan ngandhani DBMS sampeyan kudu miwiti nggoleki data sing dibutuhake. Kajaba iku, telusuran, amarga nggunakake tombol, bakal ditindakake kanthi efisien, sistem ora kudu diganggu dening garis ing njaba kisaran sing ditemtokake.
Ayo goleki perbandingan kinerja ing ngisor iki saka macem-macem pitakon. Mangkene pitakon sing ora efektif.
Panjaluk alon
Lan iki versi optimized saka panjalukan iki.
Panjaluk cepet
Kaloro pitakon ngasilake jumlah data sing padha. Nanging sing pisanan njupuk 12,80 detik kanggo ngrampungake, lan kaloro njupuk 0,01 detik. Apa sampeyan ngrasakake bedane?
Masalah bisa
Supaya metode pitakon sing diusulake bisa digunakake kanthi efektif, tabel kudu duwe kolom (utawa kolom) sing ngemot indeks urutan sing unik, kayata pengenal integer. Ing sawetara kasus tartamtu, iki bisa nemtokake sukses nggunakake pitakon kasebut kanggo nambah kacepetan nggarap database.
Mesthi, nalika mbangun pitakon, sampeyan kudu nganggep arsitektur tartamtu saka tabel lan milih mekanisme sing paling apik ing tabel sing ana. Contone, yen sampeyan kudu nggarap pitakon kanthi volume gedhe saka data sing gegandhengan, sampeyan bisa nemokake iku menarik artikel.
Yen kita ngadhepi masalah ilang kunci utama, umpamane, yen kita duwe meja kanthi hubungan akeh-kanggo-akeh, banjur pendekatan tradisional nggunakake OFFSET ΠΈ LIMIT, dijamin cocog karo kita. Nanging panggunaane bisa nyebabake pitakon sing alon. Ing kasus kaya mengkono, aku bakal nyaranake nggunakake kunci utama sing nambah otomatis, sanajan mung perlu kanggo nangani pitakon paginated.
Yen sampeyan kasengsem ing topik iki - , ΠΈ - sawetara bahan migunani.
Hasil
Kesimpulan utama sing bisa digambar yaiku, apa wae ukuran database sing kita gunakake, mesthine kudu nganalisa kacepetan eksekusi query. Saiki, skalabilitas solusi penting banget, lan yen kabeh wis dirancang kanthi bener saka wiwitan nggarap sistem tartamtu, iki, ing mangsa ngarep, bisa nylametake pangembang saka akeh masalah.
Kepiye cara nganalisa lan ngoptimalake pitakon database?
Source: www.habr.com