Yksi tyypillisistä skenaarioista kaikissa meille tutuissa sovelluksissa on tietojen etsiminen tiettyjen kriteerien mukaan ja sen näyttäminen helposti luettavassa muodossa. Lajitteluun, ryhmittelyyn ja sivuttamiseen voi myös olla lisävaihtoehtoja. Tehtävä on teoriassa triviaali, mutta sen ratkaisemisessa monet kehittäjät tekevät useita virheitä, jotka myöhemmin aiheuttavat tuottavuuden kärsimistä. Yritetään pohtia erilaisia vaihtoehtoja tämän ongelman ratkaisemiseksi ja muotoilla suosituksia tehokkaimman toteutuksen valitsemiseksi.
Sivutusvaihtoehto #1
Yksinkertaisin mieleen tuleva vaihtoehto on näyttää hakutulokset sivulta sivulta klassisimmassa muodossaan.
Oletetaan, että sovelluksesi käyttää relaatiotietokantaa. Tässä tapauksessa sinun on suoritettava kaksi SQL-kyselyä tietojen näyttämiseksi tässä lomakkeessa:
- Hae rivit nykyiselle sivulle.
- Laske hakuehtoja vastaavien rivien kokonaismäärä - tämä on välttämätöntä sivujen näyttämiseksi.
Katsotaanpa ensimmäistä kyselyä esimerkkinä testi-MS SQL -tietokannasta 2016 palvelimelle. Käytämme tähän tarkoitukseen Sales.SalesOrderHeader-taulukkoa:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Yllä oleva kysely palauttaa luettelon 50 ensimmäistä tilausta, jotka on lajiteltu laskevan lisäyspäivämäärän mukaan, eli 50 viimeisintä tilausta.
Se toimii nopeasti testipohjalla, mutta katsotaanpa suoritussuunnitelmaa ja I/O-tilastoja:
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.Voit saada I/O-tilastot jokaiselle kyselylle suorittamalla SET STATISTICS IO ON -komennon kyselyn ajon aikana.
Kuten toteutussuunnitelmasta näkyy, resursseja vaativin vaihtoehto on lajitella kaikki lähdetaulukon rivit lisäyspäivämäärän mukaan. Ja ongelma on, että mitä enemmän rivejä taulukossa on, sitä "kovempaa" lajittelu on. Käytännössä tällaisia tilanteita tulisi välttää, joten lisätään indeksi lisäyspäivämäärään ja katsotaan onko resurssien kulutus muuttunut:
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.
Ilmeisesti se on parantunut paljon. Mutta onko kaikki ongelmat ratkaistu? Muutetaan kyselyä etsimään tilauksia, joissa tavaroiden kokonaishinta ylittää 100 dollaria:
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.Meillä on hassu tilanne: kyselysuunnitelma ei ole paljoa huonompi kuin edellinen, mutta todellinen loogisten lukujen määrä on lähes kaksi kertaa suurempi kuin täydellä taulukkoskannauksella. Tie on olemassa - jos teemme yhdistelmäindeksin jo olemassa olevasta indeksistä ja lisäämme tavaran kokonaishinnan toiseksi kenttään, saamme jälleen 165 loogista lukua:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Tätä esimerkkisarjaa voidaan jatkaa pitkään, mutta kaksi tärkeintä ajatusta, jotka haluan ilmaista tässä:
- Uuden ehdon tai lajittelujärjestyksen lisääminen hakukyselyyn voi vaikuttaa merkittävästi hakukyselyn nopeuteen.
- Mutta jos meidän on vähennettävä vain osa tiedoista, ei kaikkia hakutermejä vastaavia tuloksia, on monia tapoja optimoida tällainen kysely.
Siirrytään nyt heti alussa mainittuun toiseen kyselyyn - siihen, joka laskee hakukriteerin täyttävien tietueiden määrän. Otetaanpa sama esimerkki - yli 100 dollarin tilausten etsiminen:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Kun otetaan huomioon yllä oleva yhdistelmäindeksi, saamme:
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.Se, että kysely käy läpi koko indeksin, ei ole yllättävää, koska Välisumma-kenttä ei ole ensimmäisellä paikalla, joten kysely ei voi käyttää sitä. Ongelma ratkaistaan lisäämällä toinen indeksi Välisumma-kenttään, jolloin se antaa vain 48 loogista lukua.
Voit antaa vielä muutaman esimerkin määrien laskentapyynnöistä, mutta olemus pysyy samana: tiedon vastaanottaminen ja kokonaismäärän laskeminen ovat kaksi pohjimmiltaan erilaista pyyntöä, ja jokainen vaatii omat optimointitoimenpiteensä. Yleensä et voi löytää hakemistojen yhdistelmää, joka toimisi yhtä hyvin molemmissa kyselyissä.
Näin ollen yksi tärkeimmistä vaatimuksista, joka tulisi selvittää tällaista hakuratkaisua kehitettäessä, on se, onko yritykselle todella tärkeää nähdä löydettyjen kohteiden kokonaismäärä. Usein käy niin, että ei. Ja navigointi tiettyjen sivunumeroiden mukaan on mielestäni erittäin kapea ratkaisu, koska useimmat sivutusskenaariot näyttävät "siirry seuraavalle sivulle".
Sivutusvaihtoehto #2
Oletetaan, että käyttäjät eivät välitä löydettyjen kohteiden kokonaismäärän tiedosta. Yritetään yksinkertaistaa hakusivua:
Itse asiassa ainoa asia, joka on muuttunut, on se, että tietyille sivunumeroille ei voi navigoida, ja nyt tämän taulukon ei tarvitse tietää kuinka monta niitä voi olla näyttääkseen sen. Mutta herää kysymys - mistä taulukko tietää, onko seuraavalle sivulle tietoja (jotta "Seuraava"-linkki näkyy oikein)?
Vastaus on hyvin yksinkertainen: voit lukea tietokannasta yhden tietueen enemmän kuin näyttöön tarvitaan, ja tämän "lisätietueen" läsnäolo näyttää, onko seuraavaa osaa. Tällä tavalla sinun tarvitsee suorittaa vain yksi pyyntö saadaksesi yhden sivun tietoja, mikä parantaa merkittävästi suorituskykyä ja helpottaa tällaisten toimintojen tukemista. Käytännössäni oli tapaus, jossa tietueiden kokonaismäärän laskemisesta kieltäytyminen nopeutti tulosten toimittamista 4-5 kertaa.
Tälle lähestymistavalle on useita käyttöliittymävaihtoehtoja: "takaisin" ja "eteenpäin" -komennot, kuten yllä olevassa esimerkissä, "lataa lisää" -painike, joka yksinkertaisesti lisää näytettäviin tuloksiin uuden osan, "infinite scroll", joka toimii "lataa lisää" periaatteella, mutta signaali seuraavan osan saamiseksi on se, että käyttäjä vierittää kaikki näytetyt tulokset loppuun. Olipa visuaalinen ratkaisu mikä tahansa, datanäytteenoton periaate pysyy samana.
Sivutuksen toteutuksen vivahteet
Kaikissa yllä annetuissa kyselyesimerkeissä käytetään "offset + count" -lähestymistapaa, jolloin kysely itse määrittää, missä järjestyksessä tulosrivit ja kuinka monta riviä on palautettava. Ensin tarkastellaan, kuinka parhaiten järjestää parametrien välitys tässä tapauksessa. Käytännössä olen törmännyt useisiin menetelmiin:
- Pyydetyn sivun sarjanumero (pageIndex), sivun koko (pageSize).
- Ensimmäisen palautettavan tietueen sarjanumero (startIndex), tietueiden enimmäismäärä tuloksessa (count).
- Ensimmäisen palautettavan tietueen järjestysnumero (startIndex), viimeisen palautettavan tietueen järjestysnumero (endIndex).
Ensi silmäyksellä saattaa tuntua, että tämä on niin alkeellista, ettei siinä ole mitään eroa. Mutta tämä ei ole niin - kätevin ja yleisin vaihtoehto on toinen (startIndex, count). Tähän on useita syitä:
- Yllä esitetyssä +1-merkinnän oikolukumenetelmässä ensimmäinen vaihtoehto pageIndex- ja pageSize-ominaisuuksilla on erittäin hankala. Haluamme esimerkiksi näyttää 50 viestiä sivulla. Yllä olevan algoritmin mukaan sinun on luettava yksi tietue enemmän kuin on tarpeen. Jos tätä "+1" ei ole toteutettu palvelimella, käy ilmi, että ensimmäisellä sivulla meidän on pyydettävä tietueita 1 - 51, toiselle - 51 - 101 jne. Jos määrität sivukooksi 51 ja suurennat pageIndexiä, toinen sivu palaa 52:sta 102:een jne. Näin ollen ensimmäisessä vaihtoehdossa ainoa tapa ottaa painike käyttöön seuraavalle sivulle siirtymistä varten on saada palvelin oikolukemaan "ylimääräinen" rivi, mikä on hyvin epäsuora vivahde.
- Kolmannessa vaihtoehdossa ei ole lainkaan järkeä, koska kyselyjen suorittaminen useimmissa tietokannoista edellyttää silti läpäisemistä viimeisen tietueen indeksin sijaan. StartIndexin vähentäminen endIndexistä voi olla yksinkertainen aritmeettinen operaatio, mutta se on tässä tarpeetonta.
Nyt meidän pitäisi kuvata sivuttamisen haittoja "offset + määrä" kautta:
- Jokaisen seuraavan sivun hakeminen tulee kalliimmaksi ja hitaammaksi kuin edellinen, koska tietokannan on silti käytävä kaikki tietueet "alusta" läpi haku- ja lajittelukriteerien mukaan ja pysähdyttävä sitten haluttuun fragmenttiin.
- Kaikki DBMS:t eivät tue tätä lähestymistapaa.
Vaihtoehtoja on, mutta ne ovat myös epätäydellisiä. Ensimmäinen näistä lähestymistavoista on nimeltään "keyset pageing" tai "seek method" ja se on seuraava: saatuasi osan, voit muistaa sivun viimeisen tietueen kenttäarvot ja käyttää niitä sitten saadaksesi seuraava osa. Suoritimme esimerkiksi seuraavan kyselyn:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Ja viimeisessä tietueessa saimme tilauspäivämäärän arvon '2014-06-29'. Sitten voit yrittää saada seuraavan sivun seuraavasti:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Ongelmana on, että OrderDate ei ole ainutlaatuinen kenttä ja yllä määritellystä ehdosta puuttuu todennäköisesti paljon vaadittuja rivejä. Jos haluat lisätä tähän kyselyyn yksiselitteisyyttä, sinun on lisättävä ehtoon yksilöllinen kenttä (oletetaan, että 75074 on ensisijaisen avaimen viimeinen arvo ensimmäisestä osasta):
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
Tämä vaihtoehto toimii oikein, mutta yleensä sitä on vaikea optimoida, koska ehto sisältää OR-operaattorin. Jos ensisijaisen avaimen arvo kasvaa tilauspäivämäärän kasvaessa, ehtoa voidaan yksinkertaistaa jättämällä vain SalesOrderID-suodatin. Mutta jos ensisijaisen avaimen arvojen ja kentän, jonka mukaan tulos lajitellaan, välillä ei ole tiukkaa korrelaatiota, tätä TAI ei voida välttää useimmissa DBMS-järjestelmissä. Poikkeuksen, jonka tiedän, on PostgreSQL, joka tukee täysin tuple-vertailuja, ja yllä oleva ehto voidaan kirjoittaa muodossa "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Kun otetaan huomioon näiden kahden kentän yhdistelmäavain, tällaisen kyselyn pitäisi olla melko helppoa.
Toinen vaihtoehtoinen lähestymistapa löytyy esimerkiksi kohdasta tai — kun pyyntö palauttaa tietojen lisäksi erityisen tunnisteen, jolla saat seuraavan osan tiedoista. Jos tällä tunnisteella on rajoittamaton käyttöikä (kuten Comsos DB:ssä), tämä on loistava tapa toteuttaa sivutus peräkkäisellä siirtymisellä sivujen välillä (edellä mainittu vaihtoehto #2). Sen mahdolliset haitat: sitä ei tueta kaikissa DBMS:issä; tuloksena olevan seuraavan kappaleen tunnisteen käyttöikä voi olla rajoitettu, mikä ei yleensä sovellu käyttäjän vuorovaikutuksen toteuttamiseen (kuten ElasticSearch-vierityssovellusliittymä).
Monimutkainen suodatus
Monimutkaistaan tehtävää entisestään. Oletetaan, että on olemassa vaatimus toteuttaa niin sanottu fasetoitu haku, joka on kaikille verkkokaupoista hyvin tuttu. Yllä olevat tilaustaulukkoon perustuvat esimerkit eivät ole tässä tapauksessa kovin havainnollisia, joten siirrytään AdventureWorks-tietokannasta tuotetaulukkoon:
Mikä on fasetoidun haun idea? Tosiasia on, että jokaiselle suodatinelementille näytetään tämän kriteerin täyttävien tietueiden lukumäärä ottaen huomioon kaikissa muissa luokissa valitut suodattimet.
Jos esimerkiksi valitsemme tässä esimerkissä Pyörät-luokan ja värin Musta, taulukko näyttää vain mustat pyörät, mutta:
- Jokaisen Luokat-ryhmän kriteerin kohdalla kyseisen kategorian tuotteiden määrä näytetään mustalla.
- Jokaisen "Värit"-ryhmän kriteerin kohdalla näytetään tämän väristen polkupyörien lukumäärä.
Tässä on esimerkki tulostuloksesta tällaisissa olosuhteissa:
Jos valitset myös "Vaatteet" -kategorian, taulukosta näkyy myös varastossa olevat mustat vaatteet. Myös mustien tuotteiden määrä "Väri"-osiossa lasketaan uusien ehtojen mukaan, vain "Kategoriat"-osiossa mikään ei muutu... Toivottavasti nämä esimerkit riittävät ymmärtämään tavanomaisen fasetoidun hakualgoritmin.
Kuvittele nyt, kuinka tämä voidaan toteuttaa suhteellisesti. Jokainen kriteeriryhmä, kuten luokka ja väri, vaativat erillisen kyselyn:
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
Mitä vikaa tässä ratkaisussa on? Se on hyvin yksinkertaista - se ei skaalaudu hyvin. Jokainen suodatinosio vaatii erillisen kyselyn määrien laskemiseksi, eivätkä nämä kyselyt ole helpoimpia. Verkkokaupoissa joissakin luokissa voi olla useita kymmeniä suodatinosioita, mikä voi olla vakava suorituskyvyn ongelma.
Yleensä näiden lausuntojen jälkeen minulle tarjotaan joitain ratkaisuja, nimittäin:
- Yhdistä kaikki määrät yhdeksi kyselyksi. Teknisesti tämä on mahdollista käyttämällä UNION-avainsanaa, mutta se ei juurikaan auta suorituskykyä - tietokannan on silti suoritettava jokainen fragmentti tyhjästä.
- Välimuistin määrät. Tätä ehdotetaan minulle melkein joka kerta, kun kuvailen ongelmaa. Varoitus on, että tämä on yleensä mahdotonta. Oletetaan, että meillä on 10 "puolta", joista jokaisella on 5 arvoa. Tämä on erittäin "vaatimaton" tilanne verrattuna samoihin verkkokauppoihin. Yhden fasetin elementin valinta vaikuttaa yhdeksän muun suureen, toisin sanoen kullekin kriteeriyhdistelmälle suuret voivat olla erilaisia. Esimerkissämme on yhteensä 9 kriteeriä, jotka käyttäjä voi valita, joten mahdollisia yhdistelmiä on 50. Muisti tai aika ei riitä tällaisen tietojoukon täyttämiseen. Täällä voit vastustaa ja sanoa, että kaikki yhdistelmät eivät ole todellisia ja käyttäjä valitsee harvoin yli 250-5 kriteeriä. Kyllä, on mahdollista tehdä laiska lataus ja tallentaa välimuistiin vain se määrä, mikä on koskaan valittu, mutta mitä enemmän valintoja on, sitä vähemmän tehokas tällainen välimuisti on ja sitä havaittavampia vasteaikaongelmat ovat (varsinkin jos tietojoukko muuttuu säännöllisesti).
Onneksi tällaisella ongelmalla on jo pitkään ollut varsin tehokkaita ratkaisuja, jotka toimivat ennustettavasti suurilla tietomäärillä. Jokaisessa näistä vaihtoehdoista on järkevää jakaa fasettien uudelleenlaskenta ja tulossivun vastaanottaminen kahdeksi rinnakkaiseksi kutsuksi palvelimelle ja järjestää käyttöliittymä siten, että tietojen lataaminen fasettien mukaan "ei häiritse" Hakutulokset.
- Kutsu "puolien" täydellinen uudelleenlaskenta niin harvoin kuin mahdollista. Älä esimerkiksi laske kaikkea uudelleen aina, kun hakuehdot muuttuvat, vaan etsi sen sijaan nykyisiä ehtoja vastaavien tulosten kokonaismäärä ja kehota käyttäjää näyttämään ne - "1425 tietuetta löytyi, näytä?" Käyttäjä voi joko jatkaa hakutermien muuttamista tai napsauttaa "näytä"-painiketta. Vain toisessa tapauksessa kaikki pyynnöt tulosten saamiseksi ja määrien uudelleenlaskemiseksi kaikilla "puolilla" suoritetaan. Tässä tapauksessa, kuten voit helposti nähdä, sinun on käsiteltävä pyyntö saada tulosten kokonaismäärä ja sen optimointi. Tämä menetelmä löytyy monista pienistä verkkokaupoista. Tämä ei tietenkään ole ihmelääke tähän ongelmaan, mutta yksinkertaisissa tapauksissa se voi olla hyvä kompromissi.
- Käytä hakukoneita löytääksesi tuloksia ja laskeaksesi puolia, kuten Solr, ElasticSearch, Sphinx ja muut. Kaikki ne on suunniteltu rakentamaan "puolia" ja tekevät tämän melko tehokkaasti käänteisen indeksin ansiosta. Miten hakukoneet toimivat, miksi ne ovat tällaisissa tapauksissa tehokkaampia kuin yleiskäyttöiset tietokannat, mitä käytäntöjä ja sudenkuoppia siellä on - tämä on erillisen artikkelin aihe. Haluan tässä kiinnittää huomionne siihen, että hakukone ei voi korvata päätietovarastoa, vaan sitä käytetään lisäyksenä: kaikki haun kannalta merkitykselliset muutokset päätietokannassa synkronoidaan hakuhakemistoon; Hakukone on yleensä vuorovaikutuksessa vain hakukoneen kanssa eikä pääse päätietokantaan. Yksi tärkeimmistä kohdista tässä on, kuinka tämä synkronointi järjestetään luotettavasti. Kaikki riippuu "reaktioajan" vaatimuksista. Jos aika päätietokannan muutoksen ja sen "ilmenemisen" välillä ei ole kriittinen, voit luoda palvelun, joka etsii äskettäin muuttuneet tietueet muutaman minuutin välein ja indeksoi ne. Jos haluat lyhimmän mahdollisen vasteajan, voit toteuttaa jotain vastaavaa lähettääksesi päivityksiä hakupalveluun.
Tulokset
- Palvelinpuolen sivujen toteuttaminen on merkittävä ongelma, ja se on järkevää vain nopeasti kasvaville tai yksinkertaisesti suurille tietojoukoille. Ei ole olemassa täysin tarkkaa reseptiä kuinka arvioida "iso" tai "nopeasti kasvava", mutta noudattaisin tätä lähestymistapaa:
- Jos täydellisen datakokoelman vastaanottaminen, ottaen huomioon palvelimen ajan ja verkon lähetyksen, vastaa normaalisti suorituskykyvaatimuksia, ei ole mitään järkeä toteuttaa hakua palvelinpuolella.
- Saattaa olla tilanne, jossa suorituskykyongelmia ei ole odotettavissa lähitulevaisuudessa, koska dataa on vähän, mutta tiedonkeruu kasvaa jatkuvasti. Jos jokin tietojoukko tulevaisuudessa ei ehkä enää täytä edellistä kohtaa, on parempi aloittaa sivutus heti.
- Jos yrityksellä ei ole tiukkaa vaatimusta tulosten kokonaismäärän tai sivunumeroiden näyttämisestä, eikä järjestelmässäsi ole hakukonetta, on parempi olla toteuttamatta näitä kohtia ja harkita vaihtoehtoa #2.
- Jos fasetoidulle haulle on selkeä vaatimus, sinulla on kaksi vaihtoehtoa suorituskyvystä tinkimättä:
- Älä laske kaikkia määriä uudelleen aina, kun hakukriteerit muuttuvat.
- Käytä hakukoneita, kuten Solr, ElasticSearch, Sphinx ja muita. Mutta on ymmärrettävä, että se ei voi korvata päätietokantaa, ja sitä tulisi käyttää päämuistin lisäyksenä hakuongelmien ratkaisemiseen.
- Lisäksi fasetoidussa haussa on järkevää jakaa hakutulossivun haku ja laskeminen kahteen rinnakkaiseen pyyntöön. Summien laskeminen voi kestää kauemmin kuin tulosten saaminen, kun taas tulokset ovat käyttäjälle tärkeämpiä.
- Jos käytät hakuun SQL-tietokantaa, kaikki tähän osaan liittyvät koodimuutokset tulee testata hyvin, jotta ne toimivat asianmukaisella tietomäärällä (yli reaaliaikaisen tietokannan volyymi). On myös suositeltavaa käyttää kyselyn suoritusajan seurantaa kaikissa tietokannan esiintymissä, ja erityisesti "live" -esiintymissä. Vaikka kehitysvaiheessa kaikki olisi kyselysuunnitelmien kanssa kunnossa, datamäärän kasvaessa tilanne voi muuttua huomattavasti.
Lähde: will.com