Problemas de rendimiento y salida de resultados de búsqueda

Uno de los escenarios típicos en todas las aplicaciones que conocemos es buscar datos según ciertos criterios y mostrarlos en un formato fácil de leer. También puede haber opciones adicionales para ordenar, agrupar y paginar. La tarea es, en teoría, trivial, pero al resolverla, muchos desarrolladores cometen una serie de errores que luego perjudican la productividad. Intentemos considerar varias opciones para resolver este problema y formular recomendaciones para elegir la implementación más efectiva.

Opción de paginación n.° 1

La opción más sencilla que me viene a la mente es mostrar los resultados de búsqueda página por página en su forma más clásica.

Digamos que su aplicación utiliza una base de datos relacional. En este caso, para mostrar información en este formulario, deberá ejecutar dos consultas SQL:

• Obtenga filas para la página actual.
• Calcule el número total de líneas correspondientes a los criterios de búsqueda; esto es necesario para mostrar las páginas.

Veamos la primera consulta usando una base de datos MS SQL de prueba como ejemplo. Trabajo de aventura Para el servidor 2016. Para ello utilizaremos la tabla Sales.SalesOrderHeader:

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

La consulta anterior devolverá los primeros 50 pedidos de la lista, ordenados por fecha de adición descendente, en otras palabras, los 50 pedidos más recientes.

Se ejecuta rápidamente en la base de prueba, pero veamos el plan de ejecución y las estadí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.

Puede obtener estadísticas de E/S para cada consulta ejecutando el comando SET STATISTICS IO ON en el tiempo de ejecución de la consulta.

Como puede ver en el plan de ejecución, la opción que consume más recursos es ordenar todas las filas de la tabla de origen por fecha de adición. Y el problema es que cuantas más filas aparezcan en la tabla, más “difícil” será la clasificación. En la práctica, tales situaciones deben evitarse, así que agreguemos un índice a la fecha de adición y veamos si el consumo de recursos ha cambiado:

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.

Obviamente ha mejorado mucho. Pero ¿se resuelven todos los problemas? Cambiemos la consulta para buscar pedidos donde el costo total de los bienes exceda los $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.

Tenemos una situación curiosa: el plan de consulta no es mucho peor que el anterior, pero el número real de lecturas lógicas es casi el doble que con un escaneo completo de la tabla. Hay una salida: si creamos un índice compuesto a partir de un índice ya existente y agregamos el precio total de los bienes como segundo campo, nuevamente obtendremos 165 lecturas lógicas:

CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);

Esta serie de ejemplos puede continuar durante mucho tiempo, pero los dos pensamientos principales que quiero expresar aquí son:

• Agregar cualquier criterio nuevo u orden de clasificación a una consulta de búsqueda puede tener un impacto significativo en la velocidad de la consulta de búsqueda.
• Pero si necesitamos restar solo una parte de los datos y no todos los resultados que coinciden con los términos de búsqueda, hay muchas formas de optimizar dicha consulta.

Pasemos ahora a la segunda consulta mencionada al principio: la que cuenta el número de registros que satisfacen el criterio de búsqueda. Tomemos el mismo ejemplo: buscando pedidos de más de $100:

SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100

Dado el índice compuesto indicado anteriormente, obtenemos:

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 hecho de que la consulta recorra todo el índice no es sorprendente, ya que el campo SubTotal no está en la primera posición, por lo que la consulta no puede utilizarlo. El problema se resuelve agregando otro índice en el campo SubTotal y, como resultado, solo proporciona 48 lecturas lógicas.

Puede dar algunos ejemplos más de solicitudes para contar cantidades, pero la esencia sigue siendo la misma: recibir un dato y contar la cantidad total son dos solicitudes fundamentalmente diferentes, y cada uno requiere sus propias medidas de optimización. En general, no podrá encontrar una combinación de índices que funcione igual de bien para ambas consultas.

En consecuencia, uno de los requisitos importantes que deben aclararse al desarrollar una solución de búsqueda de este tipo es si es realmente importante para una empresa ver el número total de objetos encontrados. Muchas veces sucede que no. Y la navegación por números de página específicos, en mi opinión, es una solución con un alcance muy limitado, ya que la mayoría de los escenarios de paginación parecen "ir a la página siguiente".

Opción de paginación n.° 2

Supongamos que a los usuarios no les importa saber el número total de objetos encontrados. Intentemos simplificar la página de búsqueda:

De hecho, lo único que ha cambiado es que no hay forma de navegar a números de página específicos, y ahora esta tabla no necesita saber cuántas puede haber para poder mostrarla. Pero surge la pregunta: ¿cómo sabe la tabla si hay datos para la página siguiente (para mostrar correctamente el enlace "Siguiente")?

La respuesta es muy simple: puede leer de la base de datos un registro más del necesario para mostrar, y la presencia de este registro "adicional" mostrará si hay una siguiente porción. De esta manera, solo necesita ejecutar una solicitud para obtener una página de datos, lo que mejora significativamente el rendimiento y facilita la compatibilidad con dicha funcionalidad. En mi práctica, hubo un caso en el que negarse a contar el número total de registros aceleró la entrega de resultados entre 4 y 5 veces.

Hay varias opciones de interfaz de usuario para este enfoque: comandos "atrás" y "adelante", como en el ejemplo anterior, un botón "cargar más", que simplemente agrega una nueva parte a los resultados mostrados, "desplazamiento infinito", que funciona según el principio de "cargar más", pero la señal para obtener la siguiente parte es que el usuario desplace todos los resultados mostrados hasta el final. Cualquiera que sea la solución visual, el principio del muestreo de datos sigue siendo el mismo.

Matices de la implementación de paginación.

Todos los ejemplos de consulta anteriores utilizan el enfoque "desplazamiento + recuento", cuando la consulta misma especifica en qué orden se deben devolver las filas de resultados y cuántas filas. Primero, veamos cuál es la mejor manera de organizar el paso de parámetros en este caso. En la práctica, me he encontrado con varios métodos:

• El número de serie de la página solicitada (pageIndex), tamaño de página (pageSize).
• El número de serie del primer registro que se devolverá (startIndex), el número máximo de registros en el resultado (recuento).
• El número de secuencia del primer registro que se devolverá (startIndex), el número de secuencia del último registro que se devolverá (endIndex).

A primera vista puede parecer que esto es tan elemental que no hay diferencia. Pero no es así: la opción más conveniente y universal es la segunda (startIndex, count). Hay varias razones para esto:

• Para el enfoque de revisión de entrada +1 indicado anteriormente, la primera opción con pageIndex y pageSize es extremadamente inconveniente. Por ejemplo, queremos mostrar 50 publicaciones por página. Según el algoritmo anterior, es necesario leer un registro más de lo necesario. Si este "+1" no está implementado en el servidor, resulta que para la primera página debemos solicitar los registros del 1 al 51, para la segunda, del 51 al 101, etc. Si especifica un tamaño de página de 51 y aumenta pageIndex, la segunda página volverá de 52 a 102, etc. En consecuencia, en la primera opción, la única forma de implementar correctamente un botón para ir a la página siguiente es hacer que el servidor revise la línea “extra”, lo que será un matiz muy implícito.
• La tercera opción no tiene ningún sentido, ya que para ejecutar consultas en la mayoría de las bases de datos aún necesitarás pasar el recuento en lugar del índice del último registro. Restar startIndex de endIndex puede ser una operación aritmética simple, pero aquí es superflua.

Ahora deberíamos describir las desventajas de implementar la paginación mediante “compensación + cantidad”:

• Recuperar cada página posterior será más costoso y más lento que la anterior, porque la base de datos aún necesitará revisar todos los registros "desde el principio" según los criterios de búsqueda y clasificación, y luego detenerse en el fragmento deseado.
• No todos los DBMS pueden soportar este enfoque.

Hay alternativas, pero también son imperfectas. El primero de estos enfoques se llama "paginación de conjunto de claves" o "método de búsqueda" y es el siguiente: después de recibir una parte, puede recordar los valores de campo en el último registro de la página y luego usarlos para obtener la siguiente porción. Por ejemplo, ejecutamos la siguiente consulta:

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Y en el último registro obtuvimos el valor de fecha del pedido '2014-06-29'. Luego, para obtener la página siguiente, puedes intentar hacer esto:

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

El problema es que OrderDate no es un campo único y es probable que la condición especificada anteriormente omita muchas filas obligatorias. Para agregar claridad a esta consulta, debe agregar un campo único a la condición (suponga que 75074 es el último valor de la clave principal de la primera 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 general será difícil de optimizar ya que la condición contiene un operador OR. Si el valor de la clave principal aumenta a medida que aumenta OrderDate, entonces la condición se puede simplificar dejando solo un filtro por SalesOrderID. Pero si no existe una correlación estricta entre los valores de la clave primaria y el campo por el cual se ordena el resultado, este OR no se puede evitar en la mayoría de los DBMS. Una excepción que conozco es PostgreSQL, que admite totalmente comparaciones de tuplas, y la condición anterior se puede escribir como "DÓNDE (FechaDePedido, IDOrdenDeVentas) <('2014-06-29', 75074)". Dada una clave compuesta con estos dos campos, una consulta como esta debería ser bastante sencilla.

Un segundo enfoque alternativo se puede encontrar, por ejemplo, en API de desplazamiento de ElasticSearch o base de datos cosmos — cuando una solicitud, además de los datos, devuelve un identificador especial con el que se puede obtener la siguiente porción de datos. Si este identificador tiene una vida útil ilimitada (como en Comsos DB), entonces esta es una excelente manera de implementar la paginación con transición secuencial entre páginas (opción n.° 2 mencionada anteriormente). Sus posibles desventajas: no es compatible con todos los DBMS; el identificador del siguiente fragmento resultante puede tener una vida útil limitada, lo que generalmente no es adecuado para implementar la interacción del usuario (como la API de desplazamiento ElasticSearch).

Filtrado complejo

Compliquemos aún más la tarea. Supongamos que es necesario implementar la llamada búsqueda por facetas, que es muy familiar para todos en las tiendas en línea. Los ejemplos anteriores basados ​​en la tabla de pedidos no son muy ilustrativos en este caso, así que pasemos a la tabla Producto de la base de datos AdventureWorks:

¿Cuál es la idea detrás de la búsqueda por facetas? El caso es que para cada elemento filtrante se muestra el número de registros que cumplen con este criterio. teniendo en cuenta los filtros seleccionados en todas las demás categorías.

Por ejemplo, si seleccionamos la categoría Bicicletas y el color Negro en este ejemplo, la tabla solo mostrará bicicletas negras, pero:

• Para cada criterio en el grupo Categorías, la cantidad de productos de esa categoría se mostrará en negro.
• Para cada criterio del grupo “Colores” se mostrará el número de bicicletas de este color.

A continuación se muestra un ejemplo del resultado resultante para tales condiciones:

Si también marca la categoría “Ropa”, la tabla también mostrará la ropa negra que hay en stock. La cantidad de productos negros en la sección "Color" también se recalculará de acuerdo con las nuevas condiciones, solo que en la sección "Categorías" nada cambiará... Espero que estos ejemplos sean suficientes para comprender el algoritmo habitual de búsqueda por facetas.

Ahora imaginemos cómo se puede implementar esto de forma relacional. Cada grupo de criterios, como Categoría y Color, requerirá una consulta independiente:

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é hay de malo en esta solución? Es muy simple: no escala bien. Cada sección de filtro requiere una consulta separada para calcular cantidades y estas consultas no son las más fáciles. En las tiendas online, algunas categorías pueden tener varias docenas de secciones de filtros, lo que puede suponer un grave problema de rendimiento.

Generalmente después de estas declaraciones me ofrecen algunas soluciones, a saber:

• Combine todos los recuentos de cantidades en una sola consulta. Técnicamente, esto es posible usando la palabra clave UNION, pero no ayudará mucho al rendimiento: la base de datos aún tendrá que ejecutar cada uno de los fragmentos desde cero.
• Cantidades de caché. Esto me lo sugieren casi cada vez que describo un problema. La advertencia es que esto es generalmente imposible. Digamos que tenemos 10 "facetas", cada una de las cuales tiene 5 valores. Se trata de una situación muy “modesta” comparada con lo que se puede ver en las mismas tiendas online. La elección de un elemento facetario afecta las cantidades de otros 9, es decir, para cada combinación de criterios las cantidades pueden ser diferentes. En nuestro ejemplo, hay un total de 50 criterios que el usuario puede seleccionar, por lo que habrá 250 combinaciones posibles. No hay suficiente memoria ni tiempo para llenar tal conjunto de datos. Aquí se puede objetar y decir que no todas las combinaciones son reales y que el usuario rara vez selecciona más de 5 a 10 criterios. Sí, es posible realizar una carga diferida y almacenar en caché solo una cantidad de lo que se ha seleccionado alguna vez, pero cuantas más selecciones haya, menos eficiente será dicho caché y más notorios serán los problemas de tiempo de respuesta (especialmente si el el conjunto de datos cambia periódicamente).

Afortunadamente, este problema cuenta desde hace tiempo con soluciones bastante efectivas que funcionan de manera predecible en grandes volúmenes de datos. Para cualquiera de estas opciones, tiene sentido dividir el recálculo de facetas y recibir la página de resultados en dos llamadas paralelas al servidor y organizar la interfaz de usuario de tal manera que la carga de datos por facetas "no interfiera" con la visualización de Resultados de la búsqueda.

• Llame a un nuevo cálculo completo de "facetas" lo menos posible. Por ejemplo, no vuelva a calcular todo cada vez que cambien los criterios de búsqueda, sino busque el número total de resultados que coincidan con las condiciones actuales y solicite al usuario que los muestre: "1425 registros encontrados, ¿mostrar?" El usuario puede continuar cambiando los términos de búsqueda o hacer clic en el botón "mostrar". Sólo en el segundo caso se ejecutarán todas las solicitudes para obtener resultados y volver a calcular cantidades en todas las "facetas". En este caso, como podrás comprobar fácilmente, tendrás que atender una solicitud para obtener el número total de resultados y su optimización. Este método se puede encontrar en muchas pequeñas tiendas online. Obviamente, esto no es una panacea para este problema, pero en casos simples puede ser un buen compromiso.
• Utilice motores de búsqueda para encontrar resultados y contar facetas, como Solr, ElasticSearch, Sphinx y otros. Todos ellos están diseñados para construir "facetas" y lo hacen de manera bastante eficiente debido al índice invertido. Cómo funcionan los motores de búsqueda, por qué en tales casos son más efectivos que las bases de datos de uso general, qué prácticas y trampas existen: este es un tema para un artículo aparte. Aquí me gustaría llamar su atención sobre el hecho de que el motor de búsqueda no puede reemplazar el almacenamiento de datos principal, sino que se utiliza como complemento: cualquier cambio en la base de datos principal que sea relevante para la búsqueda se sincroniza con el índice de búsqueda; El motor de búsqueda suele interactuar únicamente con el motor de búsqueda y no accede a la base de datos principal. Uno de los puntos más importantes aquí es cómo organizar esta sincronización de forma fiable. Todo depende de los requisitos de “tiempo de reacción”. Si el tiempo entre un cambio en la base de datos principal y su “manifestación” en la búsqueda no es crítico, puede crear un servicio que busque registros modificados recientemente cada pocos minutos y los indexe. Si desea el menor tiempo de respuesta posible, puede implementar algo como bandeja de salida transaccional para enviar actualizaciones al servicio de búsqueda.

Hallazgos

1. La implementación de paginación del lado del servidor es una complicación importante y sólo tiene sentido para conjuntos de datos de rápido crecimiento o simplemente grandes. No existe una receta absolutamente exacta sobre cómo evaluar “grande” o “de rápido crecimiento”, pero yo seguiría este enfoque:
   • Si recibir una colección completa de datos, teniendo en cuenta la hora del servidor y la transmisión de la red, se ajusta normalmente a los requisitos de rendimiento, no tiene sentido implementar paginación en el lado del servidor.
   • Puede darse una situación en la que no se esperen problemas de rendimiento en un futuro próximo, ya que hay pocos datos, pero la recopilación de datos crece constantemente. Si es posible que algún conjunto de datos en el futuro ya no satisfaga el punto anterior, es mejor comenzar a paginar de inmediato.
2. Si no existe un requisito estricto por parte de la empresa para mostrar el número total de resultados o mostrar los números de página, y su sistema no tiene un motor de búsqueda, es mejor no implementar estos puntos y considerar la opción n.° 2.
3. Si existe un requisito claro para la búsqueda por facetas, tiene dos opciones sin sacrificar el rendimiento:
   • No vuelva a calcular todas las cantidades cada vez que cambien los criterios de búsqueda.
   • Utilice motores de búsqueda como Solr, ElasticSearch, Sphinx y otros. Pero debe entenderse que no puede reemplazar la base de datos principal, sino que debe usarse como complemento del almacenamiento principal para resolver problemas de búsqueda.
4. Además, en el caso de la búsqueda por facetas, tiene sentido dividir la recuperación de la página de resultados de la búsqueda y el recuento en dos solicitudes paralelas. Contar cantidades puede llevar más tiempo que obtener resultados, aunque los resultados son más importantes para el usuario.
5. Si está utilizando una base de datos SQL para realizar búsquedas, se debe probar bien el rendimiento de cualquier cambio de código relacionado con esta parte en la cantidad adecuada de datos (que exceda el volumen en la base de datos activa). También es recomendable utilizar el seguimiento del tiempo de ejecución de la consulta en todas las instancias de la base de datos, y especialmente en la que está "en vivo". Incluso si todo estuvo bien con los planes de consulta en la etapa de desarrollo, a medida que crece el volumen de datos, la situación puede cambiar notablemente.

Fuente: habr.com