Un dels escenaris típics de totes les aplicacions que coneixem és buscar dades segons uns criteris i mostrar-les de forma fàcil de llegir. També hi pot haver opcions addicionals per ordenar, agrupar i paginar. La tasca és, en teoria, trivial, però a l'hora de resoldre-la, molts desenvolupadors cometen una sèrie d'errors, que després fan que la productivitat es redueixi. Intentem considerar diverses opcions per resoldre aquest problema i formular recomanacions per triar la implementació més eficaç.
Opció de paginació #1
L'opció més senzilla que em ve al cap és la visualització pàgina per pàgina dels resultats de la cerca en la seva forma més clàssica.
Suposem que la vostra aplicació utilitza una base de dades relacional. En aquest cas, per mostrar informació en aquest formulari, haureu d'executar dues consultes SQL:
- Obteniu files per a la pàgina actual.
- Calcula el nombre total de línies corresponents als criteris de cerca; això és necessari per mostrar pàgines.
Vegem la primera consulta utilitzant com a exemple una base de dades MS SQL de prova per al servidor 2016. Per a aquest propòsit utilitzarem la taula Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
La consulta anterior retornarà les 50 primeres comandes de la llista, ordenades per data d'addició descendent, és a dir, les 50 comandes més recents.
S'executa ràpidament a la base de proves, però mirem el pla d'execució i les estadístiques d'E/S:
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.Podeu obtenir estadístiques d'E/S per a cada consulta executant l'ordre SET STATISTICS IO ON al temps d'execució de la consulta.
Com podeu veure al pla d'execució, l'opció que consumeix més recursos és ordenar totes les files de la taula d'origen per data afegida. I el problema és que com més files apareguin a la taula, més "difícil" serà l'ordenació. A la pràctica, aquestes situacions s'han d'evitar, així que afegim un índex a la data d'addició i veiem si el consum de recursos ha canviat:
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.
Evidentment, ha millorat molt. Però tots els problemes estan resolts? Canviem la consulta per cercar comandes on el cost total de les mercaderies superi els 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.Tenim una situació curiosa: el pla de consultes no és molt pitjor que l'anterior, però el nombre real de lectures lògiques és gairebé el doble que amb una exploració de taula completa. Hi ha una sortida: si fem un índex compost a partir d'un índex ja existent i afegim el preu total de les mercaderies com a segon camp, tornarem a obtenir 165 lectures lògiques:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Aquesta sèrie d'exemples es pot continuar durant molt de temps, però els dos pensaments principals que vull expressar aquí són:
- Afegir qualsevol criteri nou o ordre d'ordenació a una consulta de cerca pot tenir un impacte significatiu en la velocitat de la consulta.
- Però si només hem de restar una part de les dades, i no tots els resultats que coincideixen amb els termes de cerca, hi ha moltes maneres d'optimitzar aquesta consulta.
Ara passem a la segona consulta esmentada al principi: la que compta el nombre de registres que compleixen el criteri de cerca. Prenguem el mateix exemple: cerquem comandes de més de 100 $:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Donat l'índex compost indicat anteriorment, obtenim:
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.El fet que la consulta passi per tot l'índex no és sorprenent, ja que el camp SubTotal no es troba en la primera posició, de manera que la consulta no el pot utilitzar. El problema es resol afegint un altre índex al camp SubTotal i, com a resultat, només dóna 48 lectures lògiques.
Podeu donar alguns exemples més de sol·licituds de recompte de quantitats, però l'essència segueix sent la mateixa: rebre una dada i comptar l'import total són dues peticions fonamentalment diferents, i cadascun requereix les seves pròpies mesures d'optimització. En general, no podreu trobar una combinació d'índexs que funcioni igual de bé per a ambdues consultes.
En conseqüència, un dels requisits importants que s'hauria d'aclarir a l'hora de desenvolupar aquesta solució de cerca és si és realment important que una empresa vegi el nombre total d'objectes trobats. Sovint passa que no. I la navegació per números de pàgina específics, al meu entendre, és una solució amb un abast molt reduït, ja que la majoria dels escenaris de paginació semblen "anar a la pàgina següent".
Opció de paginació #2
Suposem que als usuaris no els importa saber el nombre total d'objectes trobats. Intentem simplificar la pàgina de cerca:
De fet, l'únic que ha canviat és que no hi ha manera de navegar fins a números de pàgina concrets, i ara aquesta taula no necessita saber quantes n'hi pot haver per mostrar-la. Però sorgeix la pregunta: com sap la taula si hi ha dades per a la pàgina següent (per mostrar correctament l'enllaç "Següent")?
La resposta és molt senzilla: podeu llegir de la base de dades un registre més del necessari per a la visualització, i la presència d'aquest registre "addicional" mostrarà si hi ha una part següent. D'aquesta manera, només cal que executeu una sol·licitud per obtenir una pàgina de dades, cosa que millora significativament el rendiment i facilita la compatibilitat amb aquesta funcionalitat. A la meva pràctica, hi va haver un cas en què el fet de negar-me a comptar el nombre total de registres va accelerar l'entrega de resultats entre 4 i 5 vegades.
Hi ha diverses opcions d'interfície d'usuari per a aquest enfocament: ordres "enrere" i "endavant", com a l'exemple anterior, un botó "carregar més", que simplement afegeix una nova part als resultats mostrats, "desplaçament infinit", que funciona. en el principi de "carregar més" ", però el senyal per obtenir la següent part és que l'usuari desplaça tots els resultats que es mostren fins al final. Sigui quina sigui la solució visual, el principi de mostreig de dades segueix sent el mateix.
Matisos de la implementació de paginació
Tots els exemples de consulta que es donen anteriorment utilitzen l'enfocament "desplaçament + recompte", quan la consulta mateixa especifica en quin ordre les files del resultat i quantes files s'han de retornar. Primer, mirem com organitzar millor el pas de paràmetres en aquest cas. A la pràctica, he trobat diversos mètodes:
- El número de sèrie de la pàgina sol·licitada (pageIndex), la mida de la pàgina (pageSize).
- El número de sèrie del primer registre que es retornarà (startIndex), el nombre màxim de registres del resultat (count).
- El número de seqüència del primer registre que es retorna (startIndex), el número de seqüència de l'últim registre que es retorna (endIndex).
A primera vista pot semblar que això és tan elemental que no hi ha cap diferència. Però això no és així: l'opció més convenient i universal és la segona (startIndex, count). Hi ha diverses raons per a això:
- Per a l'enfocament de correcció d'entrada +1 que s'ha donat anteriorment, la primera opció amb pageIndex i pageSize és extremadament incòmode. Per exemple, volem mostrar 50 publicacions per pàgina. Segons l'algorisme anterior, cal llegir un registre més del necessari. Si aquest "+1" no està implementat al servidor, resulta que per a la primera pàgina hem de sol·licitar registres de l'1 al 51, per a la segona, del 51 al 101, etc. Si especifiqueu una mida de pàgina de 51 i augmenteu pageIndex, la segona pàgina tornarà de 52 a 102, etc. En conseqüència, en la primera opció, l'única manera d'implementar correctament un botó per anar a la pàgina següent és que el servidor revisi la línia "extra", que serà un matís molt implícit.
- La tercera opció no té cap sentit, ja que per executar consultes a la majoria de bases de dades encara haureu de passar el recompte en lloc de l'índex de l'últim registre. Restar startIndex de endIndex pot ser una simple operació aritmètica, però aquí és superflu.
Ara hauríem de descriure els desavantatges d'implementar la paginació mitjançant "offset + quantitat":
- Recuperar cada pàgina posterior serà més car i més lenta que l'anterior, perquè la base de dades encara haurà de passar per tots els registres "des del principi" segons els criteris de cerca i ordenació, i després aturar-se al fragment desitjat.
- No tots els SGBD poden suportar aquest enfocament.
Hi ha alternatives, però també són imperfectes. El primer d'aquests enfocaments s'anomena "paginació del conjunt de claus" o "mètode de cerca" i és el següent: després de rebre una part, podeu recordar els valors del camp a l'últim registre de la pàgina i, a continuació, utilitzar-los per obtenir la següent porció. Per exemple, vam executar la consulta següent:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
I a l'últim registre hem obtingut el valor de la data de comanda '2014-06-29'. Aleshores, per obtenir la pàgina següent, podeu provar de fer això:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
El problema és que OrderDate és un camp no únic i és probable que la condició especificada anteriorment perdi moltes files obligatòries. Per afegir claredat a aquesta consulta, heu d'afegir un camp únic a la condició (suposem que 75074 és l'últim valor de la clau primària de la primera part):
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
Aquesta opció funcionarà correctament, però en general serà difícil d'optimitzar ja que la condició conté un operador OR. Si el valor de la clau primària augmenta a mesura que augmenta OrderDate, la condició es pot simplificar deixant només un filtre per SalesOrderID. Però si no hi ha una correlació estricta entre els valors de la clau primària i el camp pel qual s'ordena el resultat, aquest OR no es pot evitar a la majoria dels SGBD. Una excepció de la qual conec és PostgreSQL, que admet totalment la comparació de tuples, i la condició anterior es pot escriure com a "WHERE (OrderDate, SalesOrderID) <('2014-06-29', 75074)". Donada una clau composta amb aquests dos camps, una consulta com aquesta hauria de ser bastant fàcil.
Un segon enfocament alternatiu es pot trobar, per exemple, a o — quan una sol·licitud, a més de dades, retorna un identificador especial amb el qual podeu obtenir la següent part de dades. Si aquest identificador té una vida útil il·limitada (com a Comsos DB), aquesta és una bona manera d'implementar la paginació amb transició seqüencial entre pàgines (opció núm. 2 esmentada anteriorment). Els seus possibles desavantatges: no és compatible amb tots els SGBD; l'identificador del següent fragment resultant pot tenir una vida útil limitada, que generalment no és adequat per implementar la interacció de l'usuari (com ara l'API de desplaçament ElasticSearch).
Filtrat complex
Complicarem encara més la tasca. Suposem que hi ha un requisit per implementar l'anomenada cerca facetada, que és molt familiar per a tothom de les botigues en línia. Els exemples anteriors basats en la taula de comandes no són gaire il·lustratius en aquest cas, així que anem a canviar a la taula Producte de la base de dades AdventureWorks:
Quina és la idea darrere de la cerca facetada? El cas és que per a cada element de filtre es mostra el nombre de registres que compleixen aquest criteri tenint en compte els filtres seleccionats en totes les altres categories.
Per exemple, si seleccionem la categoria Bicicletes i el color Negre en aquest exemple, la taula només mostrarà les bicicletes negres, però:
- Per a cada criteri del grup Categories, el nombre de productes d'aquesta categoria es mostrarà en negre.
- Per a cada criteri del grup “Colors” es mostrarà el nombre de bicicletes d'aquest color.
Aquí teniu un exemple de la sortida del resultat per a aquestes condicions:
Si també marqueu la categoria "Roba", la taula també mostrarà la roba negra que hi ha en estoc. També es tornarà a calcular el nombre de productes negres a la secció "Color" segons les noves condicions, només que a la secció "Categories" no canviarà res... Espero que aquests exemples siguin suficients per entendre l'algorisme de cerca facetada habitual.
Ara imaginem com es pot implementar això sobre una base relacional. Cada grup de criteris, com ara Categoria i Color, requerirà una consulta independent:
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
Què hi ha de dolent amb aquesta solució? És molt senzill: no s'escala bé. Cada secció de filtre requereix una consulta independent per calcular les quantitats, i aquestes consultes no són les més fàcils. A les botigues en línia, algunes categories poden tenir diverses dotzenes de seccions de filtres, cosa que pot suposar un greu problema de rendiment.
Normalment, després d'aquestes declaracions, se m'ofereixen algunes solucions, a saber:
- Combina tots els recomptes de quantitats en una sola consulta. Tècnicament, això és possible utilitzant la paraula clau UNION, però no ajudarà gaire al rendiment: la base de dades encara haurà d'executar cadascun dels fragments des de zero.
- Quantitats de memòria cau. Això em suggereix gairebé cada vegada que descriu un problema. L'advertència és que això és generalment impossible. Suposem que tenim 10 "facetes", cadascuna de les quals té 5 valors. Aquesta és una situació molt “modesta” en comparació amb el que es pot veure a les mateixes botigues en línia. L'elecció d'un element de faceta afecta les quantitats d'altres 9, és a dir, per a cada combinació de criteris les quantitats poden ser diferents. En el nostre exemple, hi ha un total de 50 criteris que l'usuari pot seleccionar, per tant, hi haurà 250 combinacions possibles.No hi ha prou memòria ni temps per omplir aquesta matriu de dades. Aquí podeu oposar-vos i dir que no totes les combinacions són reals i que l'usuari rarament selecciona més de 5-10 criteris. Sí, és possible fer una càrrega mandrosa i emmagatzemar una quantitat només del que s'ha seleccionat mai, però com més seleccions hi hagi, menys eficient serà aquesta memòria cau i més notables seran els problemes de temps de resposta (sobretot si el el conjunt de dades canvia regularment).
Afortunadament, aquest problema fa temps que té solucions força efectives que funcionen de manera previsible en grans volums de dades. Per a qualsevol d'aquestes opcions, té sentit dividir el recàlcul de facetes i rebre la pàgina de resultats en dues trucades paral·leles al servidor i organitzar la interfície d'usuari de manera que la càrrega de dades per facetes "no interfereixi" amb la visualització de Resultats de la cerca.
- Anomena un recàlcul complet de "faces" tan rarament com sigui possible. Per exemple, no torneu a calcular-ho tot cada vegada que canvien els criteris de cerca, sinó que cerqueu el nombre total de resultats que coincideixen amb les condicions actuals i demaneu a l'usuari que els mostri: "S'han trobat 1425 registres, es mostren?" L'usuari pot continuar canviant els termes de cerca o fer clic al botó "mostra". Només en el segon cas s'executaran totes les sol·licituds d'obtenció de resultats i de recàlcul de quantitats en totes les "faces". En aquest cas, com podeu veure fàcilment, haureu de fer front a una sol·licitud per obtenir el nombre total de resultats i la seva optimització. Aquest mètode es pot trobar a moltes botigues en línia petites. Evidentment, això no és una panacea per a aquest problema, però en casos senzills pot ser un bon compromís.
- Utilitzeu motors de cerca per trobar resultats i comptar facetes, com ara Solr, ElasticSearch, Sphinx i altres. Tots ells estan dissenyats per construir "facetes" i fer-ho de manera bastant eficient a causa de l'índex invertit. Com funcionen els motors de cerca, per què en aquests casos són més efectius que les bases de dades de propòsit general, quines pràctiques i inconvenients hi ha: aquest és un tema per a un article separat. Aquí m'agradaria cridar la vostra atenció sobre el fet que el motor de cerca no pot substituir l'emmagatzematge de dades principal, sinó que s'utilitza com a complement: qualsevol canvi a la base de dades principal que sigui rellevant per a la cerca es sincronitza a l'índex de cerca; El cercador sol interactuar només amb el cercador i no accedeix a la base de dades principal. Un dels punts més importants aquí és com organitzar aquesta sincronització de manera fiable. Tot depèn dels requisits de "temps de reacció". Si el temps entre un canvi a la base de dades principal i la seva "manifestació" a la cerca no és crític, podeu crear un servei que cerqui els registres canviats recentment cada pocs minuts i els indexi. Si voleu el menor temps de resposta possible, podeu implementar alguna cosa així per enviar actualitzacions al servei de cerca.
Troballes
- La implementació de la paginació del costat del servidor és una complicació important i només té sentit per a conjunts de dades de creixement ràpid o simplement grans. No hi ha una recepta absolutament exacta sobre com avaluar "gran" o "de creixement ràpid", però seguiria aquest enfocament:
- Si rebre una col·lecció completa de dades, tenint en compte el temps del servidor i la transmissió de la xarxa, s'ajusta normalment als requisits de rendiment, no té sentit implementar la paginació al costat del servidor.
- Pot haver-hi una situació en què no s'esperen problemes de rendiment en un futur proper, ja que hi ha poques dades, però la recollida de dades està en constant creixement. Si algun conjunt de dades en el futur pot deixar de satisfer el punt anterior, és millor començar a paginar immediatament.
- Si no hi ha cap requisit estricte per part de l'empresa per mostrar el nombre total de resultats o per mostrar números de pàgina, i el vostre sistema no té un motor de cerca, és millor no implementar aquests punts i considerar l'opció 2.
- Si hi ha un requisit clar per a la cerca per facetes, teniu dues opcions sense sacrificar el rendiment:
- No torneu a calcular totes les quantitats cada vegada que canvien els criteris de cerca.
- Utilitzeu motors de cerca com Solr, ElasticSearch, Sphinx i altres. Però s'ha d'entendre que no pot substituir la base de dades principal i s'ha d'utilitzar com a complement a l'emmagatzematge principal per resoldre problemes de cerca.
- A més, en el cas de la cerca facetada, té sentit dividir la recuperació de la pàgina de resultats de la cerca i el recompte en dues sol·licituds paral·leles. Comptar quantitats pot trigar més temps que obtenir resultats, mentre que els resultats són més importants per a l'usuari.
- Si utilitzeu una base de dades SQL per a la cerca, qualsevol canvi de codi relacionat amb aquesta part s'hauria de provar bé pel rendiment de la quantitat adequada de dades (superant el volum de la base de dades en directe). També s'aconsella utilitzar el control del temps d'execució de consultes a totes les instàncies de la base de dades, i especialment a la "en directe". Fins i tot si tot anava bé amb els plans de consulta en l'etapa de desenvolupament, a mesura que el volum de dades creix, la situació pot canviar notablement.
Font: www.habr.com