Salah sahiji skénario has dina sadaya aplikasi anu urang wawuh nyaéta milarian data dumasar kana kriteria anu tangtu sareng nampilkeunana dina bentuk anu gampang dibaca. Aya ogé pilihan tambahan pikeun asihan, ngagolongkeun, sareng paging. Tugasna, dina téori, sepele, tapi nalika ngarengsekeunana, seueur pamekar ngadamel sababaraha kasalahan, anu engké nyababkeun produktivitas sangsara. Hayu urang coba mertimbangkeun rupa-rupa pilihan pikeun ngarengsekeun masalah ieu sarta ngarumuskeun saran pikeun milih palaksanaan paling éféktif.
Pilihan Paging #1
Pilihan pangbasajanna anu aya dina pikiran nyaéta tampilan halaman-demi-halaman hasil pamilarian dina bentuk anu paling klasik.
Anggap aplikasi anjeun ngagunakeun database relational. Dina hal ieu, pikeun nembongkeun informasi dina formulir ieu, anjeun bakal kudu ngajalankeun dua queries SQL:
- Meunang baris pikeun kaca ayeuna.
- Itung jumlah total garis pakait jeung kriteria pilarian - ieu diperlukeun pikeun nembongkeun kaca.
Hayu urang nempo query munggaran ngagunakeun database test MS SQL salaku conto pikeun 2016 server. Pikeun tujuan ieu kami bakal nganggo tabel Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Paménta di luhur bakal mulangkeun 50 pesenan anu munggaran tina daptar, diurutkeun dumasar kana turunna tanggal tambihan, dina basa sanés, 50 pesenan panganyarna.
Éta ngajalankeun gancang dina dasar tés, tapi hayu urang tingali rencana palaksanaan sareng statistik I / O:
Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Anjeun tiasa kéngingkeun statistik I / O pikeun tiap pamundut ku ngajalankeun paréntah SET STATISTICS IO ON dina runtime query.
Sakumaha anjeun tiasa tingali tina rencana palaksanaan, pilihan anu paling intensif sumberdaya nyaéta nyortir sadaya jajar tabel sumber dumasar kana tanggal tambah. Jeung masalahna nyaeta beuki baris muncul dina tabél, nu "harder" asihan bakal. Dina prakna, kaayaan sapertos kitu kedah dihindari, janten hayu urang tambahkeun indéks kana tanggal tambihan sareng tingali upami konsumsi sumberdaya parantos robih:
Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Jelas éta parantos langkung saé. Tapi sagala masalah direngsekeun? Hayu urang robih patarosan pikeun milarian pesenan dimana total biaya barang ngaleuwihan $100:
SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Simkuring boga kaayaan lucu: rencana query teu pira leuwih goreng ti saméméhna, tapi jumlah sabenerna logis berbunyi ampir dua kali leuwih badag batan scan tabel pinuh. Aya jalan kaluar - upami urang ngadamel indéks komposit tina indéks anu parantos aya sareng nambihan total harga barang salaku kolom kadua, urang bakal nampi deui 165 bacaan logis:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Runtuyan conto ieu tiasa diteruskeun kanggo waktos anu lami, tapi dua pikiran utama anu kuring hoyong terangkeun di dieu nyaéta:
- Nambahkeun sagala kriteria anyar atawa urutan sortir kana pamundut pilarian bisa boga dampak signifikan dina laju pamundut pilarian.
- Tapi lamun urang kudu subtract ukur bagian tina data, sarta teu kabeh hasil nu cocog istilah pilarian, aya loba cara pikeun ngaoptimalkeun query misalna.
Ayeuna hayu urang teraskeun kana pamundut kadua anu disebatkeun di awal - anu ngitung jumlah rékaman anu nyugemakeun kriteria milarian. Hayu urang nyandak conto anu sami - milarian pesenan anu langkung ti $100:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Dibikeun indéks komposit anu dituduhkeun di luhur, urang kéngingkeun:
Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Kanyataan yén pamundut ngaliwatan sakabéh indéks teu héran, saprak widang SubTotal henteu dina posisi kahiji, jadi query teu bisa maké éta. Masalahna direngsekeun ku nambahkeun indéks sejen dina widang SubTotal, sarta salaku hasilna méré ukur 48 bacaan logis.
Anjeun tiasa masihan sababaraha conto deui pamundut pikeun ngitung kuantitas, tapi hakekatna tetep sami: narima sapotong data jeung cacah jumlah total dua requests fundamentally béda, sarta masing-masing merlukeun ukuran sorangan pikeun optimasi. Sacara umum, anjeun moal bisa manggihan kombinasi indexes nu gawéna sarua ogé pikeun duanana queries.
Sasuai, salah sahiji sarat penting anu kudu netelakeun nalika ngamekarkeun solusi pilarian misalna naha éta bener penting pikeun bisnis ningali jumlah total objék kapanggih. Ieu sering kajadian nu euweuh. Sareng navigasi ku nomer halaman khusus, dina pamanggih kuring, mangrupikeun solusi kalayan wengkuan anu sempit, sabab kalolobaan skenario paging sapertos "buka ka halaman salajengna."
Pilihan Paging #2
Hayu urang nganggap yén pamaké teu paduli ngeunaan nyaho jumlah total objék kapanggih. Hayu urang nyederhanakeun halaman milarian:
Nyatana, hiji-hijina hal anu parantos robih nyaéta teu aya deui jalan pikeun nganapigasi ka nomer halaman khusus, sareng ayeuna tabel ieu henteu peryogi terang sabaraha seueur anu aya pikeun nampilkeunana. Tapi patarosan timbul - kumaha tabél terang upami aya data pikeun halaman salajengna (supaya leres ningalikeun tautan "Salajengna")?
Jawabanna basajan pisan: anjeun tiasa maca tina pangkalan data hiji catetan langkung ti anu dipikabutuh pikeun tampilan, sareng ayana catetan "tambahan" ieu bakal nunjukkeun naha aya bagian salajengna. Ku cara ieu, anjeun ngan ukur kedah ngajalankeun hiji pamundut pikeun kéngingkeun hiji halaman data, anu sacara signifikan ningkatkeun kinerja sareng ngagampangkeun ngadukung fungsionalitas sapertos kitu. Dina prakték kuring, aya kasus nalika nampik ngitung jumlah total rékaman nyepetkeun pangiriman hasil ku 4-5 kali.
Aya sababaraha pilihan antarbeungeut pangguna pikeun pendekatan ieu: paréntah "deui" sareng "maju", sapertos conto di luhur, tombol "beban langkung", anu ngan saukur nambihan bagian anyar kana hasil anu ditampilkeun, "gulungan tanpa wates", anu tiasa dianggo. dina prinsip "beban langkung" ", tapi sinyal pikeun meunangkeun porsi salajengna nyaéta pikeun pamaké pikeun ngagulung sakabéh hasil ditampilkeun nepi ka ahir. Naon waé solusi visual, prinsip sampling data tetep sami.
Nuances palaksanaan paging
Sadaya conto query anu dipasihkeun di luhur nganggo pendekatan "offset + count", nalika query nyalira nangtukeun dina urutan hasil baris sareng sabaraha baris anu kedah dipulangkeun. Kahiji, hayu urang nempo kumaha pangalusna pikeun ngatur parameter passing dina hal ieu. Dina prakna, kuring mendakan sababaraha metode:
- Nomer séri halaman anu dipénta (pageIndex), ukuran halaman (pageSize).
- Jumlah séri rékaman munggaran dipulangkeun (startIndex), jumlah maksimum rékaman dina hasil (cacah).
- Jumlah runtuyan rékaman pangheulana dipulangkeun (startIndex), jumlah runtuyan rékaman panungtungan balik (endIndex).
Dina glance kahiji sigana yén ieu téh jadi dasar nu teu aya bédana. Tapi teu kitu - pilihan nu pangmerenahna tur universal nyaéta kadua (startIndex, count). Aya sababaraha alesan pikeun ieu:
- Pikeun pendekatan koréksi éntri +1 anu dipasihkeun di luhur, pilihan kahiji kalayan pageIndex sareng pageSize pisan teu merenah. Contona, urang rék nembongkeun 50 tulisan per kaca. Numutkeun algoritma di luhur, anjeun kedah maca hiji catetan langkung ti anu diperyogikeun. Upami "+1" ieu henteu dilaksanakeun dina server, tétéla yén pikeun halaman kahiji urang kedah nyuhunkeun rékaman tina 1 dugi ka 51, pikeun anu kadua - ti 51 dugi ka 101, jsb. Upami anjeun netepkeun ukuran halaman 51 sareng ningkatkeun pageIndex, teras halaman kadua bakal uih deui tina 52 ka 102, jsb. Sasuai, dina pilihan kahiji, hiji-hijina jalan pikeun leres nerapkeun tombol pikeun muka kaca salajengna nyaeta mun server proofread garis "tambahan", nu bakal nuansa pisan implisit.
- Pilihan katilu teu asup akal pisan, saprak ngajalankeun queries di paling database anjeun masih bakal kudu lulus count tinimbang indéks tina catetan panungtungan. Ngurangan startIndex tina endIndex tiasa janten operasi aritmetika anu saderhana, tapi éta superfluous di dieu.
Ayeuna urang kedah ngajelaskeun kalemahan ngalaksanakeun paging ngaliwatan "offset + kuantitas":
- Retrieving unggal kaca saterusna bakal leuwih mahal tur laun ti saméméhna, sabab database masih bakal perlu ngaliwatan sagala rékaman "ti mimiti" nurutkeun kriteria pilarian tur asihan, lajeng eureun di sempalan nu dipikahoyong.
- Henteu sadayana DBMS tiasa ngadukung pendekatan ieu.
Aya alternatif, tapi maranéhna ogé teu sampurna. Kahiji tina pendekatan ieu disebut "keyset paging" atawa "metoda néangan" na nyaéta saperti kieu: sanggeus narima porsi, anjeun tiasa apal nilai widang dina catetan panungtungan dina kaca, lajeng dipaké pikeun ménta. bagian salajengna. Contona, urang ngajalankeun query handap:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Sarta dina catetan panungtungan urang meunang nilai tanggal urutan '2014-06-29'. Teras pikeun kéngingkeun halaman salajengna anjeun tiasa nyobian ngalakukeun ieu:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Masalahna nyaéta OrderDate mangrupikeun lapangan anu henteu unik sareng kaayaan anu dijelaskeun di luhur sigana bakal sono seueur barisan anu diperyogikeun. Pikeun nambihan unambiguity kana pamundut ieu, anjeun kedah nambihan kolom anu unik kana kaayaan éta (anggap yén 75074 mangrupikeun nilai anu terakhir tina konci primér tina bagian kahiji):
SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Pilihan ieu bakal dianggo leres, tapi sacara umum bakal hese ngaoptimalkeun saprak kaayaan ngandung hiji operator OR. Lamun nilai konci primér naek sakumaha OrderDate naek, kaayaan bisa disederhanakeun ku nyésakeun ngan hiji filter ku SalesOrderID. Tapi upami teu aya korelasi anu ketat antara nilai konci primér sareng lapangan anu hasilna diurutkeun, ieu OR teu tiasa dihindari dina kalolobaan DBMS. Pangecualian anu kuring terang nyaéta PostgreSQL, anu pinuh ngadukung ngabandingkeun tuple, sareng kaayaan di luhur tiasa diserat salaku "WHERE (OrderDate, SalesOrderID) <('2014-06-29', 75074)". Dibikeun konci komposit sareng dua widang ieu, pamundut sapertos kieu kedahna gampang.
Hiji pendekatan alternatif kadua bisa kapanggih, contona, dina atawa - nalika pamundut, salian data, mulih identifier husus kalawan nu bisa meunang bagian salajengna data. Upami identifier ieu gaduh umur anu henteu terbatas (sapertos dina Comsos DB), maka ieu mangrupikeun cara anu saé pikeun nerapkeun paging kalayan transisi sequential antara halaman (pilihan #2 didadarkeun di luhur). Kakurangan anu mungkin: teu dirojong dina sadaya DBMS; identifier salajengna-chunk anu dihasilkeun tiasa gaduh umur kawates, anu umumna henteu cocog pikeun ngalaksanakeun interaksi pangguna (sapertos API ngagugulung ElasticSearch).
Nyaring kompléks
Hayu urang ngahesekeun tugas salajengna. Anggap aya sarat pikeun nerapkeun nu disebut search faceted, nu pisan wawuh ka dulur ti toko online. Conto di luhur dumasar kana tabel pesenan henteu pisan ilustrasi dina hal ieu, ku kituna hayu urang ngalih ka tabel Produk tina database AdventureWorks:
Naon pamanggih balik pilarian faceted? Kanyataan yén pikeun tiap elemen filter, jumlah rékaman anu minuhan kriteria ieu ditémbongkeun nyokot kana akun saringan dipilih dina sakabéh kategori séjén.
Salaku conto, upami urang milih kategori Bikes sareng warna Hideung dina conto ieu, tabél ngan ukur nunjukkeun bikes hideung, tapi:
- Pikeun unggal kriteria dina grup Kategori, jumlah produk tina kategori éta bakal dipidangkeun hideung.
- Pikeun unggal kriteria grup "Warna", jumlah sapédah warna ieu bakal ditingalikeun.
Ieu conto kaluaran hasil pikeun kaayaan sapertos kieu:
Upami anjeun ogé pariksa kategori "Pakaian", tabél ogé bakal nunjukkeun baju hideung anu aya di saham. Jumlah produk hideung dina bagian "Warna" ogé bakal recalculated nurutkeun kaayaan anyar, ngan dina bagian "Kategori" nanaon bakal robah ... Kuring miharep conto ieu cukup ngartos algoritma pilarian faceted dawam.
Ayeuna hayu urang ngabayangkeun kumaha ieu bisa dilaksanakeun dina dasar relational. Unggal grup kriteria, sapertos Kategori sareng Warna, bakal meryogikeun pamundut anu misah:
SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC
SELECT Color, COUNT(1) FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC
Naon anu lepat sareng solusi ieu? Éta saderhana pisan - henteu skalana saé. Unggal bagian filter merlukeun query misah pikeun ngitung kuantitas, sarta queries ieu sanes nu panggampangna. Di toko online, sababaraha kategori tiasa gaduh sababaraha belasan bagian saringan, anu tiasa janten masalah kinerja anu serius.
Biasana saatos pernyataan ieu kuring ditawarkeun sababaraha solusi, nyaéta:
- Gabungkeun sadaya jumlah kuantitas kana hiji pamundut. Téhnisna ieu tiasa nganggo kecap konci UNION, tapi éta moal ngabantosan kinerja - pangkalan data masih kedah ngaéksekusi unggal fragmen ti mimiti.
- Jumlah cache. Ieu disarankeun pikeun kuring ampir unggal waktos kuring ngajelaskeun masalah. caveat teh nya eta ieu umumna teu mungkin. Hayu urang nyebutkeun urang boga 10 "facets", nu masing-masing boga 5 nilai. Ieu mangrupikeun kaayaan anu "modest" dibandingkeun sareng anu tiasa ditingali dina toko online anu sami. Pilihan hiji unsur facet mangaruhan kuantitas dina 9 séjén, dina basa sejen, pikeun tiap kombinasi kriteria kuantitas bisa béda. Dina conto urang, aya total 50 kriteria nu pamaké bisa milih, ku kituna, bakal aya 250 kombinasi mungkin. Aya teu cukup memori atawa waktu pikeun ngeusian Asép Sunandar Sunarya saperti data. Di dieu anjeun tiasa ngabantah sareng nyarios yén henteu sadayana kombinasi anu nyata sareng pangguna jarang milih langkung ti 5-10 kriteria. Sumuhun, kasebut nyaéta dimungkinkeun pikeun ngalakukeun loading puguh na cache kuantitas ngan naon geus kungsi dipilih, tapi beuki selections aya, nu kirang efisien cache sapertos bakal jeung beuki noticeable masalah waktos respon bakal (utamana lamun susunan data robah sacara teratur).
Untungna, masalah sapertos geus lila miboga solusi cukup éféktif nu bisa diprediksi dina volume badag data. Pikeun salah sahiji pilihan ieu, asup akal pikeun ngabagi itungan ulang tina facets sareng nampi halaman hasil kana dua sauran paralel ka server sareng ngatur antarbeungeut pangguna ku cara anu ngamuat data ku aspek "henteu ngaganggu" tampilan hasil teangan.
- Nelepon hiji recalculation lengkep "facets" sakumaha jarang-gancang. Salaku conto, ulah ngitung deui sadayana unggal waktos kritéria pamilarian robih, tapi milarian jumlah total hasil anu cocog sareng kaayaan ayeuna sareng ajakan pangguna pikeun nunjukkeun aranjeunna - "1425 rékaman kapanggih, nunjukkeun?" Pangguna tiasa teras-terasan ngarobih istilah pamilarian atanapi klik tombol "tunjukkeun". Ngan dina kasus kadua bakal sagala requests pikeun meunangkeun hasil na recalculating kuantitas dina sakabéh "facets" bakal dieksekusi. Dina hal ieu, anjeun bisa kalayan gampang tingali, anjeun bakal kudu nungkulan pamundut pikeun ménta jumlah total hasil jeung optimasi na. Metoda ieu bisa kapanggih dina loba toko online leutik. Jelas, ieu sanés panacea pikeun masalah ieu, tapi dina kasus saderhana tiasa janten kompromi anu saé.
- Anggo mesin pencari pikeun milarian hasil sareng ngitung rupa, sapertos Solr, ElasticSearch, Sphinx sareng anu sanésna. Sakabéh éta dirancang pikeun ngawangun "facets" na ngalakukeun ieu rada éfisién alatan indéks inverted. Kumaha mesin pencari dianggo, naha dina kasus sapertos aranjeunna langkung efektif tibatan database tujuan umum, naon prakték sareng pitfalls aya - ieu mangrupikeun topik pikeun tulisan anu misah. Di dieu Abdi hoyong ngagambar perhatian anjeun kana kanyataan yén search engine teu bisa ngagantikeun gudang data utama, dipaké salaku tambahan: sagala parobahan dina database utama nu relevan pikeun pilarian disingkronkeun kana indéks pilarian; Mesin pencari biasana ngan ukur berinteraksi sareng mesin pencari sareng henteu ngaksés pangkalan data utama. Salah sahiji titik anu paling penting di dieu nyaéta kumaha ngatur sinkronisasi ieu sacara dipercaya. Eta sadayana gumantung kana sarat "waktos réaksi". Lamun waktu antara parobahan dina database utama jeung "manifestasi" na dina pilarian teu kritis, Anjeun bisa nyieun layanan nu maluruh rékaman anyar robah unggal sababaraha menit sarta indéks aranjeunna. Upami anjeun hoyong waktos respon shortest mungkin, anjeun tiasa nerapkeun hal kawas pikeun ngirim apdet ka ladenan pilarian.
papanggihan
- Ngalaksanakeun paging sisi server mangrupikeun komplikasi anu penting sareng ngan ukur asup akal pikeun set data anu gancang atanapi ngan saukur ageung. Teu aya resep anu leres-leres kumaha cara ngevaluasi "badag" atanapi "tumuwuh gancang", tapi kuring bakal nuturkeun pendekatan ieu:
- Lamun narima kumpulan data lengkep, nyokot kana akun waktu server jeung transmisi jaringan, fits sarat kinerja normal, teu aya gunana pikeun nerapkeun paging di sisi server.
- Meureun aya kaayaan dimana euweuh masalah kinerja diperkirakeun dina mangsa nu bakal datang, saprak aya saeutik data, tapi pendataan ieu terus tumuwuh. Lamun sababaraha set data dina mangsa nu bakal datang bisa jadi euweuh nyugemakeun titik saméméhna, éta hadé pikeun ngamimitian paging langsung.
- Upami teu aya sarat anu ketat pikeun usaha pikeun nunjukkeun jumlah total hasil atanapi ningalikeun nomer halaman, sareng sistem anjeun henteu gaduh mesin pencari, langkung saé henteu nerapkeun titik-titik ieu sareng mertimbangkeun pilihan #2.
- Upami aya sarat anu jelas pikeun milarian rupa-rupa, anjeun gaduh dua pilihan tanpa ngorbankeun kinerja:
- Entong ngitung deui sadaya kuantitas unggal waktos kritéria pamilarian robih.
- Anggo mesin pencari sapertos Solr, ElasticSearch, Sphinx sareng anu sanésna. Tapi kudu dipikaharti yén éta teu bisa ngagantikeun database utama, sarta kudu dipaké salaku tambahan pikeun neundeun utama pikeun ngajawab masalah pilarian.
- Ogé, dina kasus pilarian faceted, ngajadikeun rasa mun dibeulah dimeunangkeun kaca hasil teangan jeung cacah kana dua requests paralel. Ngitung kuantitas tiasa langkung lami tibatan kéngingkeun hasil, sedengkeun hasilna langkung penting pikeun pangguna.
- Upami Anjeun keur make database SQL pikeun néangan, kudu sagala parobahan kode patali bagian ieu ogé diuji pikeun pagelaran dina jumlah luyu data (ngaleuwihan volume dina database live). Éta ogé sasaena ngagunakeun ngawaskeun waktu palaksanaan query dina sagala instansi tina database, sarta hususna dina "live". Sanaos sadayana henteu kunanaon sareng rencana pamundut dina tahap pamekaran, nalika volume data tumbuh, kaayaan tiasa robih sacara signifikan.
sumber: www.habr.com