Geus aya poé nalika anjeun teu kudu salempang ngeunaan optimalisasi kinerja database. Waktos teu nangtung kénéh. Unggal pangusaha téknologi anyar hoyong nyiptakeun Facebook salajengna, bari nyobian ngumpulkeun sadaya data anu aranjeunna tiasa didamel. Usaha peryogi data ieu pikeun ngalatih modél anu langkung saé anu ngabantosan aranjeunna ngadamel artos. Dina kaayaan sapertos kitu, programer kedah nyiptakeun API anu ngamungkinkeun aranjeunna gancang sareng dipercaya damel sareng seueur inpormasi.
Lamun anjeun geus ngarancang aplikasi atawa backends database pikeun lila wae, Anjeun meureun geus nulis kode pikeun ngajalankeun queries paginated. Contona, saperti kieu:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Kumaha éta?
Tapi lamun ieu kumaha anjeun ngalakukeun pagination Anjeun, Hapunten disebutkeun yen anjeun teu ngalakukeun hal eta dina cara paling éfisién.
Rék ngabantah kuring? . , и Aranjeunna parantos nganggo téknik anu kuring badé ngobrolkeun ayeuna.
Sebutkeun sahenteuna hiji pamekar backend anu henteu pernah dianggo OFFSET и LIMIT pikeun ngalakukeun queries paginated. Dina MVP (Minimum Viable Product) jeung dina proyék-proyék dimana jumlah leutik data dipaké, pendekatan ieu cukup lumaku. Ieu "ngan jalan," kitu mun nyarita.
Tapi lamun kudu nyieun sistem dipercaya jeung efisien ti scratch, Anjeun kudu ngurus sateuacanna ngeunaan efisiensi querying database dipaké dina sistem misalna.
Dinten ieu kami bakal ngobrol ngeunaan masalah sareng palaksanaan mesin query paginated anu biasa dianggo (goréng teuing), sareng kumaha carana ngahontal prestasi anu luhur nalika ngalaksanakeun pamundut sapertos kitu.
Naon salahna OFFSET sareng LIMIT?
Sakumaha parantos nyarios, OFFSET и LIMIT Aranjeunna ngalaksanakeun saé dina proyék anu henteu kedah dianggo sareng data anu ageung.
Masalahna timbul nalika pangkalan data naék kana ukuran anu henteu pas dina mémori pangladén. Nanging, nalika damel sareng pangkalan data ieu, anjeun kedah nganggo patarosan paginated.
Pikeun masalah ieu manifest sorangan, kudu aya kaayaan nu resorts DBMS ka operasi Scan Table pinuh teu episien dina unggal query paginated (bari sisipan jeung ngahapus operasi bisa lumangsung, sarta kami teu butuh data luntur!).
Naon téh "scan tabel pinuh" (atawa "scan tabel sequential", Sequential Scan)? Ieu mangrupa operasi salila DBMS sequentially maca unggal baris tabel, nyaeta, data anu dikandung dina eta, sarta pariksa aranjeunna pikeun minuhan kaayaan nu tangtu. Jenis scan tabel ieu dipikanyaho paling laun. Kanyataanna nyaéta nalika dieksekusi, seueur operasi input / output anu dilakukeun anu ngalibatkeun subsistem disk server. Kaayaan ieu langkung parah ku latency anu aya hubunganana sareng data anu disimpen dina disk, sareng kanyataan yén nransfer data tina disk ka mémori mangrupikeun operasi anu intensif sumberdaya.
Salaku conto, anjeun gaduh rékaman 100000000 pangguna sareng anjeun ngajalankeun pamundut kalayan konstruksi. OFFSET 50000000. Ieu ngandung harti yén DBMS kedah ngamuat sadaya rékaman ieu (sareng kami henteu peryogi aranjeunna!), nempatkeun éta dina mémori, teras saatos éta nyandak, sebutkeun, 20 hasil dilaporkeun dina LIMIT.
Sebutkeun sigana sapertos kieu: "pilih baris ti 50000 dugi ka 50020 tina 100000". Hartina, sistem mimitina kudu ngamuat 50000 baris pikeun ngalengkepan pamundut. Naha anjeun ningali sabaraha padamelan anu teu perlu anjeunna kedah laksanakeun?
Upami anjeun henteu percanten ka kuring, tingali conto anu kuring ciptakeun nganggo fitur-fitur .
Contona dina db-fiddle.com
Aya, di kénca, di sawah Schema SQL, aya kode anu nyelapkeun 100000 jajar kana pangkalan data, sareng di katuhu, di lapangan. Query SQL, dua queries ditémbongkeun. Nu kahiji, slow, kasampak kawas kieu:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Sareng anu kadua, anu mangrupikeun solusi anu efektif pikeun masalah anu sami, sapertos kieu:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Dina raraga minuhan requests ieu, ngan klik dina tombol Run di luhur kaca. Saatos ngalakukeun ieu, urang ngabandingkeun inpormasi ngeunaan waktos palaksanaan pamundut. Tétéla yén ngaéksekusi hiji query teu epektip nyokot sahenteuna 30 kali leuwih lila ti executing nu kadua (waktos ieu beda-beda ti run to run; contona, sistem bisa ngalaporkeun yén query kahiji nyandak 37 md pikeun réngsé, tapi palaksanaan kadua - 1 ms).
Sareng upami aya langkung seueur data, maka sadayana bakal katingali langkung parah (janten yakin kana ieu, tingali kuring kalawan 10 juta jajar).
Naon anu urang bahas kedah masihan anjeun sababaraha wawasan kumaha query database leres-leres diolah.
Punten dicatet yén langkung luhur nilaina OFFSET - beuki lila pamundut bakal nyandak pikeun ngalengkepan.
Naon anu kuring kedah dianggo tibatan kombinasi OFFSET sareng LIMIT?
Gantina kombinasi a OFFSET и LIMIT Éta patut ngagunakeun struktur anu diwangun dumasar kana skéma ieu:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Ieu palaksanaan query kalawan pagination dumasar kursor.
Gantina nyimpen anu ayeuna sacara lokal OFFSET и LIMIT sareng ngirimkeunana sareng unggal pamundut, anjeun kedah nyimpen konci primér anu terakhir nampi (biasana ieu ID) jeung LIMIT, salaku hasilna, queries sarupa di luhur bakal diala.
Naha? Intina nyaéta ku sacara eksplisit nangtukeun identifier tina baris terakhir anu dibaca, anjeun nyarioskeun ka DBMS anjeun dimana éta kedah ngamimitian milarian data anu diperyogikeun. Sumawona, panéangan, berkat panggunaan konci, bakal dilaksanakeun sacara éfisién; sistem henteu kedah diganggu ku garis di luar rentang anu ditangtukeun.
Hayu urang tingali perbandingan kinerja di handap ieu tina sababaraha patarosan. Ieu patarosan anu teu efektif.
Paménta lalaunan
Sareng ieu mangrupikeun versi anu dioptimalkeun pikeun pamundut ieu.
Paménta gancang
Duanana queries balik persis jumlah data sarua. Tapi anu kahiji butuh 12,80 detik pikeun réngsé, sareng anu kadua butuh 0,01 detik. Naha anjeun ngarasa bédana?
masalah poténsial
Pikeun metode query anu diusulkeun tiasa dianggo sacara efektif, tabél kedah ngagaduhan kolom (atanapi kolom) anu ngandung indéks sekuensial anu unik, sapertos identifier integer. Dina sababaraha kasus husus, ieu bisa nangtukeun kasuksésan ngagunakeun queries misalna pikeun ngaronjatkeun kagancangan gawé bareng database.
Alami, nalika ngawangun queries, Anjeun kudu tumut kana akun arsitéktur husus tabel sarta milih mékanisme nu bakal dianggo pangalusna dina tabel aya. Contona, upami anjeun kedah dianggo dina queries kalawan volume badag data patali, anjeun bisa manggihan eta metot artikel.
Upami urang disanghareupan masalah leungit konci primér, contona, upami urang gaduh méja kalayan hubungan seueur-ka-loba, maka pendekatan tradisional ngagunakeun OFFSET и LIMIT, dijamin cocog sareng urang. Tapi pamakéan na bisa ngakibatkeun queries berpotensi slow. Dina kasus sapertos kitu, kuring bakal nyarankeun ngagunakeun konci primér otomatis-incrementing, sanajan ngan diperlukeun pikeun nanganan queries paginated.
Upami anjeun resep kana topik ieu - , и - sababaraha bahan mangpaat.
hasil
Kacindekan utama anu urang tiasa ngagambar nyaéta, euweuh urusan naon ukuran database anu urang bahas, sok perlu analisa laju palaksanaan query. Ayeuna, skalabilitas solusi penting pisan, sareng upami sadayana dirarancang leres ti mimiti damel dina sistem anu tangtu, ieu, ka hareup, tiasa nyalametkeun pamekar tina seueur masalah.
Kumaha anjeun nganalisis sareng ngaoptimalkeun pamundut database?
sumber: www.habr.com