Un dos escenarios típicos de todas as aplicacións que coñecemos é a busca de datos segundo uns criterios e a súa visualización nun formato fácil de ler. Tamén pode haber opcións adicionais para ordenar, agrupar e paginar. A tarefa é, en teoría, trivial, pero á hora de solucionala, moitos desenvolvedores cometen unha serie de erros, que despois provocan que a produtividade se vexa afectada. Tentemos considerar varias opcións para resolver este problema e formular recomendacións para escoller a implementación máis eficaz.
Opción de paginación #1
A opción máis sinxela que se me ocorre é a visualización páxina por páxina dos resultados da busca na súa forma máis clásica.
Digamos que a súa aplicación usa unha base de datos relacional. Neste caso, para mostrar información neste formulario, terá que executar dúas consultas SQL:
- Obter filas para a páxina actual.
- Calcula o número total de liñas correspondentes aos criterios de busca; isto é necesario para mostrar páxinas.
Vexamos a primeira consulta usando unha base de datos MS SQL de proba como exemplo para o servidor 2016. Para este fin utilizaremos a táboa Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
A consulta anterior devolverá os primeiros 50 pedidos da lista, ordenados por data de adición descendente, é dicir, os 50 pedidos máis recentes.
É executado rapidamente na base de probas, pero vexamos o plan de execución e as estatísticas de 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.Pode obter estatísticas de E/S para cada consulta executando o comando SET STATISTICS IO ON no tempo de execución da consulta.
Como podes ver no plan de execución, a opción máis intensiva en recursos é ordenar todas as filas da táboa de orixe por data engadida. E o problema é que cantas máis filas aparezan na táboa, máis "difícil" será a ordenación. Na práctica, este tipo de situacións deberían evitarse, entón imos engadir un índice á data de adición e ver se cambiou o consumo de recursos:
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.
Evidentemente, mellorou moito. Pero todos os problemas están resoltos? Cambiamos a consulta para buscar pedidos nos que o custo total dos produtos supere os 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.Temos unha situación curiosa: o plan de consulta non é moito peor que o anterior, pero o número real de lecturas lóxicas é case o dobre que cunha análise de táboa completa. Hai unha saída: se facemos un índice composto a partir dun índice xa existente e engadimos o prezo total dos bens como segundo campo, volveremos ter 165 lecturas lóxicas:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Esta serie de exemplos pódese continuar durante moito tempo, pero as dúas ideas principais que quero expresar aquí son:
- Engadir calquera novo criterio ou orde de ordenación a unha consulta de busca pode ter un impacto significativo na velocidade da consulta.
- Pero se necesitamos restar só parte dos datos, e non todos os resultados que coinciden cos termos da busca, hai moitas formas de optimizar esa consulta.
Agora imos pasar á segunda consulta mencionada ao principio: a que conta o número de rexistros que satisfacen o criterio de busca. Poñamos o mesmo exemplo: buscando pedidos que superen os 100 $:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Dado o índice composto anteriormente indicado, obtemos:
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.O feito de que a consulta pase por todo o índice non é sorprendente, xa que o campo SubTotal non está na primeira posición, polo que a consulta non pode usalo. O problema resólvese engadindo outro índice no campo SubTotal e, como resultado, dá só 48 lecturas lóxicas.
Podes dar algúns exemplos máis de solicitudes para contar cantidades, pero a esencia segue sendo a mesma: recibir un dato e contar o importe total son dúas solicitudes fundamentalmente diferentes, e cada un require as súas propias medidas de optimización. En xeral, non poderás atopar unha combinación de índices que funcione igual de ben para ambas as consultas.
En consecuencia, un dos requisitos importantes que se deben aclarar ao desenvolver unha solución de busca deste tipo é se é realmente importante para unha empresa ver o número total de obxectos atopados. A miúdo ocorre que non. E a navegación por números de páxina específicos, na miña opinión, é unha solución cun alcance moi reducido, xa que a maioría dos escenarios de paginación parecen "ir á páxina seguinte".
Opción de paginación #2
Supoñamos que os usuarios non se preocupan por coñecer o número total de obxectos atopados. Intentemos simplificar a páxina de busca:
De feito, o único que cambiou é que non hai forma de navegar a números de páxina específicos, e agora esta táboa non precisa saber cantas pode haber para mostrala. Pero xorde a pregunta: como sabe a táboa se hai datos para a páxina seguinte (para mostrar correctamente a ligazón "Seguinte")?
A resposta é moi sinxela: podes ler na base de datos un rexistro máis do necesario para a súa visualización, e a presenza deste rexistro "adicional" mostrará se hai unha seguinte parte. Deste xeito, só precisa executar unha solicitude para obter unha páxina de datos, o que mellora significativamente o rendemento e facilita a compatibilidade con esta funcionalidade. Na miña práctica, houbo un caso en que negarse a contar o número total de rexistros acelerou a entrega dos resultados entre 4 e 5 veces.
Existen varias opcións de interface de usuario para este enfoque: comandos "atrás" e "adelante", como no exemplo anterior, un botón "cargar máis", que simplemente engade unha nova parte aos resultados mostrados, "desprazamento infinito", que funciona polo principio de "cargar máis" ", pero o sinal para obter a seguinte parte é que o usuario desprácese todos os resultados mostrados ata o final. Sexa cal sexa a solución visual, o principio de mostraxe de datos segue sendo o mesmo.
Matices da implementación de paginación
Todos os exemplos de consulta indicados anteriormente usan o enfoque "compensación + reconto", cando a propia consulta especifica en que orde as filas do resultado e cantas filas hai que devolver. En primeiro lugar, vexamos a mellor forma de organizar o paso de parámetros neste caso. Na práctica, atopeime con varios métodos:
- O número de serie da páxina solicitada (pageIndex), o tamaño da páxina (pageSize).
- O número de serie do primeiro rexistro que se devolverá (startIndex), o número máximo de rexistros no resultado (count).
- O número de secuencia do primeiro rexistro que se devolverá (startIndex), o número de secuencia do último rexistro que se devolverá (endIndex).
A primeira vista pode parecer que isto é tan elemental que non hai diferenzas. Pero isto non é así: a opción máis conveniente e universal é a segunda (startIndex, count). Hai varias razóns para iso:
- Para o enfoque de corrección de entradas +1 indicado anteriormente, a primeira opción con pageIndex e pageSize é moi inconveniente. Por exemplo, queremos mostrar 50 publicacións por páxina. Segundo o algoritmo anterior, cómpre ler un rexistro máis do necesario. Se este "+1" non está implementado no servidor, resulta que para a primeira páxina debemos solicitar rexistros do 1 ao 51, para a segunda - do 51 ao 101, etc. Se especificas un tamaño de páxina de 51 e aumentas pageIndex, a segunda páxina volverá de 52 a 102, etc. En consecuencia, na primeira opción, a única forma de implementar correctamente un botón para ir á páxina seguinte é que o servidor revise a liña "extra", que será un matiz moi implícito.
- A terceira opción non ten ningún sentido, xa que para executar consultas na maioría das bases de datos aínda terás que pasar o reconto en lugar do índice do último rexistro. Restar startIndex de endIndex pode ser unha simple operación aritmética, pero aquí é superfluo.
Agora debemos describir as desvantaxes de implementar a paginación a través de "offset + cantidade":
- Recuperar cada páxina posterior será máis caro e máis lento que a anterior, porque a base de datos aínda terá que pasar por todos os rexistros "desde o principio" segundo os criterios de busca e clasificación, e despois deterse no fragmento desexado.
- Non todos os DBMS poden soportar este enfoque.
Hai alternativas, pero tamén son imperfectas. O primeiro destes enfoques chámase "paxinación do conxunto de claves" ou "método de busca" e é o seguinte: despois de recibir unha parte, pode lembrar os valores do campo no último rexistro da páxina e, a continuación, utilizalos para obter a seguinte porción. Por exemplo, realizamos a seguinte consulta:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
E no último rexistro conseguimos o valor da data do pedido ‘2014-06-29’. Despois, para obter a seguinte páxina, podes tentar facelo:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
O problema é que OrderDate é un campo non único e é probable que a condición especificada anteriormente non falte moitas filas obrigatorias. Para engadir sen ambigüidade a esta consulta, cómpre engadir un campo único á condición (supoña que 75074 é o último valor da clave primaria da primeira parte):
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
Esta opción funcionará correctamente, pero en xeral será difícil de optimizar xa que a condición contén un operador OR. Se o valor da clave primaria aumenta a medida que aumenta OrderDate, a condición pódese simplificar deixando só un filtro por SalesOrderID. Pero se non hai unha correlación estrita entre os valores da clave primaria e o campo polo que se ordena o resultado, este OU non se pode evitar na maioría dos DBMS. Unha excepción que coñezo é PostgreSQL, que admite totalmente a comparación de tuplas, e a condición anterior pódese escribir como "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Dada unha clave composta con estes dous campos, unha consulta como esta debería ser bastante sinxela.
Un segundo enfoque alternativo pódese atopar, por exemplo, en ou — cando unha solicitude, ademais dos datos, devolve un identificador especial co que pode obter a seguinte porción de datos. Se este identificador ten unha vida útil ilimitada (como en Comsos DB), entón esta é unha boa forma de implementar a paginación con transición secuencial entre páxinas (opción n.º 2 mencionada anteriormente). As súas posibles desvantaxes: non é compatible en todos os DBMS; o identificador do seguinte fragmento resultante pode ter unha vida útil limitada, o que xeralmente non é adecuado para implementar a interacción do usuario (como a API de desprazamento ElasticSearch).
Filtrado complexo
Imos complicar aínda máis a tarefa. Supoñamos que hai un requisito para implementar a chamada busca facetada, que é moi familiar para todos desde as tendas en liña. Os exemplos anteriores baseados na táboa de pedidos non son moi ilustrativos neste caso, así que imos cambiar á táboa de produtos da base de datos AdventureWorks:
Cal é a idea detrás da busca facetada? O caso é que para cada elemento de filtro móstrase o número de rexistros que cumpren este criterio tendo en conta os filtros seleccionados en todas as demais categorías.
Por exemplo, se seleccionamos a categoría Bicicletas e a cor Negra neste exemplo, a táboa só mostrará bicicletas negras, pero:
- Para cada criterio do grupo Categorías, o número de produtos desa categoría mostrarase en negro.
- Para cada criterio do grupo “Cores” indicarase o número de bicicletas desta cor.
Aquí tes un exemplo do resultado para tales condicións:
Se tamén marcas a categoría "Roupa", a táboa tamén mostrará a roupa negra que hai en stock. O número de produtos negros na sección "Cor" tamén se volverá calcular segundo as novas condicións, só que na sección "Categorías" non cambiará nada... Espero que estes exemplos sexan suficientes para entender o habitual algoritmo de busca facetada.
Agora imaxinemos como se pode implementar isto nunha base relacional. Cada grupo de criterios, como Categoría e Cor, requirirá unha consulta separada:
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
Que ten de malo esta solución? É moi sinxelo: non escala ben. Cada sección de filtro require unha consulta separada para calcular as cantidades, e estas consultas non son as máis sinxelas. Nas tendas en liña, algunhas categorías poden ter varias ducias de seccións de filtro, o que pode ser un problema serio de rendemento.
Normalmente despois destas declaracións ofrécense algunhas solucións, a saber:
- Combina todas as cantidades nunha soa consulta. Tecnicamente, isto é posible usando a palabra clave UNION, pero non axudará moito ao rendemento: a base de datos aínda terá que executar cada un dos fragmentos desde cero.
- Cantidades de caché. Isto suxíreseme case cada vez que describo un problema. A advertencia é que isto é xeralmente imposible. Digamos que temos 10 "facetas", cada unha das cales ten 5 valores. Esta é unha situación moi "modesta" en comparación co que se pode ver nas mesmas tendas en liña. A elección dun elemento faceta afecta ás cantidades noutros 9, é dicir, para cada combinación de criterios as cantidades poden ser diferentes. No noso exemplo, hai un total de 50 criterios que o usuario pode seleccionar, polo que haberá combinacións posibles de 250. Non hai memoria nin tempo suficiente para encher esa matriz de datos. Aquí pode opoñerse e dicir que non todas as combinacións son reais e que o usuario raramente selecciona máis de 5-10 criterios. Si, é posible facer unha carga preguiceira e almacenar na caché só unha cantidade do que se seleccionou algunha vez, pero cantas máis seleccións haxa, menos eficiente será esa caché e máis notables serán os problemas de tempo de resposta (especialmente se o o conxunto de datos cambia regularmente).
Afortunadamente, tal problema ten durante moito tempo solucións bastante eficaces que funcionan de forma previsible en grandes volumes de datos. Para calquera destas opcións, ten sentido dividir o recálculo de facetas e a recepción da páxina de resultados en dúas chamadas paralelas ao servidor e organizar a interface de usuario de forma que a carga de datos por facetas "non interfira" coa visualización de resultados da busca.
- Chama un recálculo completo de "facetas" o máis raramente posible. Por exemplo, non recalcule todo cada vez que cambie o criterio de busca, senón que busque o número total de resultados que coincidan coas condicións actuais e solicite ao usuario que os mostre: "Atopáronse 1425 rexistros, mostrar?" O usuario pode continuar cambiando os termos de busca ou facer clic no botón "mostrar". Só no segundo caso executaranse todas as solicitudes de obtención de resultados e recalculo de cantidades en todas as "facetas". Neste caso, como podes ver facilmente, terás que facer fronte a unha solicitude para obter o número total de resultados e a súa optimización. Este método pódese atopar en moitas pequenas tendas en liña. Obviamente, esta non é unha panacea para este problema, pero en casos sinxelos pode ser un bo compromiso.
- Use motores de busca para atopar resultados e contar facetas, como Solr, ElasticSearch, Sphinx e outros. Todos eles están deseñados para construír "facetas" e facelo de forma bastante eficiente debido ao índice invertido. Como funcionan os motores de busca, por que en tales casos son máis eficaces que as bases de datos de propósito xeral, que prácticas e trampas hai - este é un tema para un artigo separado. Aquí gustaríame chamar a súa atención sobre o feito de que o buscador non pode substituír o almacenamento de datos principal, úsase como complemento: calquera cambio na base de datos principal que sexa relevante para a busca sincronízase no índice de busca; O buscador adoita interactuar só co buscador e non accede á base de datos principal. Un dos puntos máis importantes aquí é como organizar esta sincronización de forma fiable. Todo depende dos requisitos de "tempo de reacción". Se o tempo entre un cambio na base de datos principal e a súa "manifestación" na busca non é crítico, podes crear un servizo que busque os rexistros modificados recentemente cada poucos minutos e os indexe. Se queres o menor tempo de resposta posible, podes implementar algo así para enviar actualizacións ao servizo de busca.
Descubrimentos
- A implementación de páxinas no servidor é unha complicación importante e só ten sentido para conxuntos de datos de crecemento rápido ou simplemente grandes. Non hai unha receita absolutamente exacta sobre como avaliar "grande" ou "de crecemento rápido", pero seguiría este enfoque:
- Se recibir unha colección completa de datos, tendo en conta o tempo do servidor e a transmisión da rede, se axusta aos requisitos de rendemento normalmente, non ten sentido implementar a paginación no lado do servidor.
- Pode haber unha situación na que non se esperan problemas de rendemento nun futuro próximo, xa que hai poucos datos, pero a recollida de datos está en constante crecemento. Se algún conxunto de datos no futuro pode deixar de satisfacer o punto anterior, é mellor comezar a paginar inmediatamente.
- Se non hai un requisito estrito por parte da empresa para mostrar o número total de resultados ou para mostrar números de páxina, e o seu sistema non ten un motor de busca, é mellor non implementar estes puntos e considerar a opción #2.
- Se hai un requisito claro para a busca por facetas, tes dúas opcións sen sacrificar o rendemento:
- Non recalcule todas as cantidades cada vez que cambien os criterios de busca.
- Use motores de busca como Solr, ElasticSearch, Sphinx e outros. Pero débese entender que non pode substituír a base de datos principal, e debe usarse como un complemento ao almacenamento principal para resolver problemas de busca.
- Ademais, no caso da busca por facetas, ten sentido dividir a recuperación da páxina de resultados da busca e o reconto en dúas solicitudes paralelas. Contar cantidades pode levar máis tempo que obter resultados, mentres que os resultados son máis importantes para o usuario.
- Se está a usar unha base de datos SQL para a busca, calquera cambio de código relacionado con esta parte debe ser probado ben para o rendemento na cantidade adecuada de datos (superando o volume da base de datos en directo). Tamén é recomendable utilizar o seguimento do tempo de execución da consulta en todas as instancias da base de datos, e especialmente na "en directo". Aínda que todo fose ben cos plans de consulta na fase de desenvolvemento, a medida que o volume de datos crece, a situación pode cambiar notablemente.
Fonte: www.habr.com