php - soporta - optimizar consultas lentas mysql

Optimización de consultas para el elemento siguiente y anterior (11)

Estoy buscando la mejor manera de recuperar los registros siguientes y anteriores de un registro sin ejecutar una consulta completa. Tengo implementada una solución totalmente implementada, y me gustaría saber si hay mejores enfoques para hacer esto.

Digamos que estamos construyendo un sitio web para una verdulería ficticia. Además de sus páginas HTML, todas las semanas, quiere publicar una lista de ofertas especiales en su sitio. Quiere que esas ofertas residan en una tabla de base de datos real, y los usuarios deben poder ordenar las ofertas de tres maneras.

Cada elemento también debe tener una página de detalles con más información textual sobre la oferta y los botones "anterior" y "siguiente". Los botones "anterior" y "siguiente" deben apuntar a las entradas vecinas dependiendo de la clasificación que el usuario haya elegido para la lista .

texto alternativo http://www.pekkagaiser.com/stuff/Sort.gif?

Obviamente, el botón "siguiente" para "Tomates, Clase I" tiene que ser "Manzanas, clase 1" en el primer ejemplo, "Peras, clase I" en el segundo y ninguna en el tercero.

La tarea en la vista de detalles es determinar los elementos siguientes y anteriores sin ejecutar una consulta cada vez , con el orden de clasificación de la lista como la única información disponible (digamos que obtenemos eso a través de un parámetro GET ?sort=offeroftheweek_price , e ignoramos las implicaciones de seguridad).

Obviamente, simplemente pasar las identificaciones de los elementos siguiente y anterior como un parámetro es la primera solución que viene a la mente. Después de todo, ya sabemos las identificaciones en este punto. Pero, esta no es una opción aquí; funcionaría en este ejemplo simplificado, pero no en muchos de mis casos de uso del mundo real.

Mi enfoque actual en mi CMS es usar algo que he denominado "clasificar caché". Cuando se carga una lista, sortingcache las posiciones de los artículos en los registros en una tabla llamada sortingcache .

name (VARCHAR) items (TEXT) offeroftheweek_unsorted Lettuce; Tomatoes; Apples I; Apples II; Pears offeroftheweek_price Tomatoes;Pears;Apples I; Apples II; Lettuce offeroftheweek_class_asc Apples II;Lettuce;Apples;Pears;Tomatoes

obviamente, la columna de items está realmente poblada con ID numéricos.

En la página de detalles, ahora sortingcache registro de sortingcache apropiado, sortingcache la columna de items , sortingcache , busco el ID de artículo actual y devuelvo el vecino anterior y siguiente.

array("current" => "Tomatoes", "next" => "Pears", "previous" => null );

Esto obviamente es costoso, funciona solo para un número limitado de registros y crea datos redundantes, pero supongamos que en el mundo real, la consulta para crear las listas es muy costosa (lo es), ejecutándola en cada vista de detalle está fuera de la pregunta, y se necesita algo de almacenamiento en caché.

Mis preguntas:

¿Crees que esta es una buena práctica para averiguar los registros vecinos para variar las órdenes de consulta?
¿Conoces mejores prácticas en términos de rendimiento y simplicidad? ¿Sabes algo que lo hace completamente obsoleto?
En teoría de programación, ¿hay un nombre para este problema?
¿El nombre "Caché de clasificación" es apropiado y comprensible para esta técnica?
¿Hay algún patrón común reconocido para resolver este problema? ¿Cómo se llaman?

Nota: Mi pregunta no es acerca de compilar la lista, o cómo mostrar la vista de detalles. Esos son solo ejemplos. Mi pregunta es la funcionalidad básica de determinar los vecinos de un registro cuando una nueva consulta es imposible, y la manera más rápida y económica de llegar allí.

Si algo no está claro, deje un comentario y lo aclararé.

Comenzando una recompensa, tal vez haya algo más de información sobre esto.

Aquí hay una idea Puede descargar las costosas operaciones a una actualización cuando el tendero inserta / actualiza nuevas ofertas en lugar de cuando el usuario final selecciona los datos para ver. Esto puede parecer una forma no dinámica de manejar los datos de clasificación, pero puede aumentar la velocidad. Y, como sabemos, siempre hay una compensación entre el rendimiento y otros factores de codificación.

Cree una tabla para contener el próximo y el anterior para cada oferta y cada opción de clasificación. (Alternativamente, puede almacenar esto en la tabla de ofertas si siempre tendrá tres opciones de clasificación: la velocidad de consulta es una buena razón para desnormalizar su base de datos)

Entonces tendrías estas columnas:

Tipo de clasificación (sin clasificar, precio, clase y descripción del precio)
ID de oferta
ID anterior
Siguiente ID

Cuando se consulta la información detallada de la página de detalles de la oferta desde la base de datos, NextID y PrevID serían parte de los resultados. Por lo tanto, solo necesitaría una consulta para cada página de detalles.

Cada vez que se inserta, actualiza o elimina una oferta, deberá ejecutar un proceso que valide la integridad / precisión de la tabla sorttype.

Disculpe si he entendido mal, pero creo que desea conservar la lista ordenada entre los accesos del usuario al servidor. Si es así, su respuesta puede estar en su estrategia y tecnologías de almacenamiento en caché en lugar de en la consulta de base de datos / optimización del esquema.

Mi enfoque sería serializar () la matriz una vez que se recuperó por primera vez, y luego guardarla en caché en un área de almacenamiento separada; ya sea memcached / APC / hard-drive / mongoDb / etc. y retenga sus detalles de ubicación de caché para cada usuario individualmente a través de sus datos de sesión. El backend de almacenamiento real dependerá naturalmente del tamaño de la matriz, que no se detalla mucho, pero las escalas de memcached son excelentes en varios servidores y mongo aún más con un costo de latencia ligeramente mayor.

Tampoco indicas cuántas permutaciones de ordenación hay en el mundo real; por ejemplo, ¿necesita almacenar en caché las listas por usuario, o puede caché globalmente por tipo de permutación y luego filtrar lo que no necesita a través de PHP ?. En el ejemplo que das, simplemente guardo en memoria caché ambas permutaciones y almaceno cuál de las dos necesito para deserializar () en los datos de la sesión.

Cuando el usuario regrese al sitio, verifique el valor Time To Live de los datos almacenados en caché y vuelva a utilizarlo si aún es válido. También me gustaría ejecutar un disparador en INSERT / UPDATE / DELETE para las ofertas especiales que simplemente configuran un campo de marca de tiempo en una tabla separada. Esto indicaría inmediatamente si el caché estaba obsoleto y la consulta debía volver a ejecutarse por un costo de consulta muy bajo. Lo mejor de usar solo el disparador para establecer un solo campo es que no hay necesidad de preocuparse por eliminar los valores viejos / redundantes de esa tabla.

Si esto es adecuado dependerá del tamaño de los datos que se devuelven, con qué frecuencia se modificó y qué tecnologías de almacenamiento en caché están disponibles en su servidor.

El problema / estructura de datos se denomina gráfico bidireccional o puede decir que tiene varias listas vinculadas.

Si lo considera una lista vinculada, puede agregar campos a la tabla de elementos para cada clasificación y clave anterior / siguiente. Pero la Persona DB te matará por eso, es como GOTO.

Si lo piensas como un gráfico (bidireccional), vas con la respuesta de Jessica. El problema principal es que las actualizaciones de pedidos son costosas.

Item Next Prev A B - B C A C D B ...

Si cambia una posición de los artículos a la nueva orden A, C, B, D, tendrá que actualizar 4 filas.

En general, desnormalizo los datos de los índices. Se pueden almacenar en las mismas filas, pero casi siempre recupero mis ID de resultados, luego realizo un viaje por separado para los datos. Esto hace que almacenar en caché los datos sea muy simple. No es tan importante en PHP donde la latencia es baja y el ancho de banda es alto, pero esa estrategia es muy útil cuando tienes una aplicación de alta latencia y bajo ancho de banda, como un sitio web AJAX donde gran parte del sitio se procesa en JavaScript.

Siempre guardo en la memoria caché las listas de resultados y los resultados por separado. Si algo afecta los resultados de una consulta de lista, la caché de los resultados de la lista se actualiza. Si algo afecta los resultados en sí mismos, esos resultados particulares se actualizan. Esto me permite actualizar cualquiera sin tener que volver a generar todo, lo que resulta en el almacenamiento en caché efectivo.

Como mis listas de resultados rara vez cambian, genero todas las listas al mismo tiempo. Esto puede hacer que la respuesta inicial sea un poco más lenta, pero simplifica la actualización de la memoria caché (todas las listas se almacenan en una sola entrada de la memoria caché).

Debido a que tengo toda la lista en la memoria caché, es trivial encontrar elementos vecinos sin volver a visitar la base de datos. Con suerte, los datos de esos artículos también se almacenarán en caché. Esto es especialmente útil al ordenar datos en JavaScript. Si ya tengo una copia en caché en el cliente, puedo recurrir al instante.

Para responder a sus preguntas específicamente:

Sí, es una idea fantástica conocer a los vecinos con anticipación, o la información que el cliente pueda acceder a continuación, especialmente si el costo ahora es bajo y el costo para volver a calcular es alto. Entonces es simplemente una compensación de cálculo previo adicional y almacenamiento versus velocidad.
En términos de rendimiento y simplicidad, evite vincular cosas que son lógicamente diferentes. Los índices y los datos son diferentes, es probable que se modifiquen en diferentes momentos (por ejemplo, agregar un nuevo dato afectará a los índices, pero no a los datos existentes), y por lo tanto se debe acceder por separado. Esto puede ser un poco menos eficiente desde el punto de vista de un solo subproceso, pero cada vez que unes algo, pierdes eficacia de caché y asychronosity (la clave para escalar es la asychronosity).
El término para obtener datos antes de tiempo es precargado. La precarga puede ocurrir en el momento del acceso o en el fondo, pero antes de que los datos pre-obtenidos sean realmente necesarios. Del mismo modo con pre-cálculo. Ahora es una compensación de costo, costo de almacenamiento y costo obtener cuando sea necesario.
"Clasificación de caché" es un nombre apropiado.
No lo sé.

Además, cuando almacena cosas en la memoria caché, las almacena en el nivel más genérico posible. Algunas cosas pueden ser específicas del usuario (como los resultados de una consulta de búsqueda), mientras que otras pueden ser ajenas al usuario, como navegar por un catálogo. Ambos pueden beneficiarse del almacenamiento en caché. La consulta de catálogo puede ser frecuente y ahorrar un poco cada vez, y la consulta de búsqueda puede ser costosa y ahorrar muchas veces.

Entonces tienes dos tareas:

crear una lista ordenada de elementos (SELECT con diferentes ORDER BY)
mostrar detalles sobre cada elemento (SELECCIONAR detalles de la base de datos con posible almacenamiento en caché).

¿Cuál es el problema?

PD: si la lista ordenada puede ser demasiado grande, solo necesitas la funcionalidad PAGER implementada. Puede haber diferentes implementaciones, por ejemplo, puede agregar "LIMIT 5" en la consulta y proporcionar el botón "Mostrar los próximos 5". Cuando se presiona este botón, se agrega una condición como "WHERE price <0.89 LIMIT 5".

Hay tantas maneras de hacerlo como para despellejar al gato proverbial. Así que aquí hay un par de míos.

Si su consulta original es costosa, lo que usted dice que es, entonces cree otra tabla posiblemente una tabla de memoria que la rellene con los resultados de su costosa y raramente consulta principal.

Esta segunda tabla podría consultarse en cada vista y la clasificación es tan simple como establecer el orden de clasificación apropiado.

Como es necesario, repoblar la segunda tabla con los resultados de la primera tabla, manteniendo así los datos actualizados, pero minimizando el uso de la costosa consulta.

Alternativamente, si desea evitar incluso la conexión a la base de datos, entonces podría almacenar todos los datos en una matriz php y almacenarlos utilizando memcached. esto sería muy rápido y si sus listas no fueran demasiado grandes, sería eficiente en el uso de los recursos. y se puede ordenar fácilmente

corriente continua

He tenido pesadillas con este también. Su enfoque actual parece ser la mejor solución incluso para listas de 10k artículos. Almacenamiento en caché de los ID de la vista de lista en la sesión http y luego usar eso para mostrar el (personalizado al usuario actual) anterior / siguiente. Esto funciona bien, especialmente cuando hay demasiadas formas de filtrar y ordenar la lista inicial de elementos en lugar de solo 3.
Además, al almacenar toda la lista de ID, se muestra un texto "you are at X out of Y" usabilidad de "you are at X out of Y" .

Por cierto, esto es lo que hace JIRA también.

Para responder directamente a tus preguntas:

Sí, es una buena práctica, ya que escala sin ningún tipo de complejidad añadida cuando su filtro / clasificación y los tipos de elementos se vuelven más complejos. Lo estoy usando en un sistema de producción con 250 mil artículos con variaciones infinitas de filtro / orden. También es posible recortar las identidades caché a 1000, ya que el usuario probablemente nunca haga clic en anterior o siguiente más de 500 veces (lo más probable es que regrese y refine la búsqueda o paginar).
No sé de una mejor manera. Pero si los géneros fueran limitados y este fuera un sitio público (sin sesión de http), probablemente me gustaría desnormalizar.
No sé.
Sí, clasificar caché suena bien. En mi proyecto, lo llamo "anterior / siguiente en los resultados de búsqueda" o "navegación en los resultados de búsqueda".
No sé.

No estoy seguro de si entendí bien, entonces si no, solo dímelo;)

Digamos, que los datos son la consulta para la lista ordenada y la compensación actual en esa lista, es decir, tenemos una $query y un $n .

Una solución muy obvia para minimizar las consultas sería obtener todos los datos a la vez:

list($prev, $current, $next) = DB::q($query . '' LIMIT ?i, 3'', $n - 1)->fetchAll(PDO::FETCH_NUM);

Esa declaración obtiene los elementos anterior, actual y siguiente de la base de datos en el orden de clasificación actual y coloca la información asociada en las variables correspondientes.

Pero como esta solución es demasiado simple, supongo que malentendí algo.

Puede guardar los números de fila de las listas ordenadas en views , y puede llegar a los elementos anteriores y siguientes en la lista bajo (current_rownum-1) y (current_rownum + 1) números de fila.

Supuestos básicos:

Los especiales son semanales
Podemos esperar que el sitio cambie con poca frecuencia ... ¿probablemente todos los días?
Podemos controlar las actualizaciones de la base de datos con ether y API o responder mediante triggers

Si el sitio cambia a diario, sugiero que todas las páginas se generen estáticamente de la noche a la mañana. Una consulta para cada orden de clasificación recorre y hace todas las páginas relacionadas. Incluso si hay elementos dinámicos, es probable que pueda abordarlos incluyendo los elementos de página estáticos. Esto proporcionaría un servicio de página óptimo y sin carga de base de datos. De hecho, podría generar páginas separadas y elementos previos / siguientes que se incluyen en las páginas. Esto puede ser más loco con 200 formas de ordenar, pero con 3 soy un gran admirador de él.

?sort=price include(/sorts/$sort/tomatoes_class_1) /*tomatoes_class_1 is probably a numeric id; sanitize your sort key... use numerics?*/

Si por alguna razón esto no es posible, recurro a la memorización. Memcache es popular para este tipo de cosas (¡juego de palabras!). Cuando se inserta algo en la base de datos, puede emitir un disparador para actualizar su caché con los valores correctos. Haga esto de la misma forma en que lo haría si su elemento actualizado existiera en 3 listas vinculadas: vuelva a vincular según corresponda (this.next.prev = this.prev, etc.). A partir de eso, siempre que su caché no se llene en exceso, extraerá valores simples de la memoria en forma de clave primaria.

Este método requerirá una codificación adicional en los métodos de selección y actualización / inserción, pero debería ser bastante mínimo. Al final, [id of tomatoes class 1].price.next . Si esa clave está en tu caché, dorada. Si no, inserte en el caché y la pantalla.

¿Crees que esta es una buena práctica para averiguar los registros vecinos para variar las órdenes de consulta? Sí. Es aconsejable realizar un seguimiento de las próximas solicitudes previstas.
¿Conoces mejores prácticas en términos de rendimiento y simplicidad? ¿Sabes algo que lo hace completamente obsoleto? Esperemos que el anterior
En teoría de programación, ¿hay un nombre para este problema? ¿Mejoramiento?
¿El nombre "Caché de clasificación" es apropiado y comprensible para esta técnica? No estoy seguro de un nombre apropiado específico. Es el almacenamiento en caché, es una especie de caché, pero no estoy seguro de que decirme que tienes un "caché de clasificación" pueda transmitir una comprensión instantánea.
¿Hay algún patrón común reconocido para resolver este problema? ¿Cómo se llaman? ¿Caché?

Lo siento, mis respuestas de seguimiento son inútiles, pero creo que mis soluciones narrativas deberían ser bastante útiles.

Tengo una idea algo similar a la de Jessica. Sin embargo, en lugar de almacenar enlaces a los elementos de ordenación siguientes y anteriores, almacenará el orden de clasificación para cada tipo de clasificación. Para encontrar el registro anterior o siguiente, simplemente obtenga la fila con SortX = currentSort ++ u SortX = currentSort--.

Ejemplo:

Type Class Price Sort1 Sort2 Sort3 Lettuce 2 0.89 0 4 0 Tomatoes 1 1.50 1 0 4 Apples 1 1.10 2 2 2 Apples 2 0.95 3 3 1 Pears 1 1.25 4 1 3

Esta solución produciría tiempos de consulta muy cortos y ocuparía menos espacio de disco que la idea de Jessica. Sin embargo, como estoy seguro de que se da cuenta, el costo de actualizar una fila de datos es notablemente mayor, ya que tiene que volver a calcular y almacenar todos los órdenes de clasificación. Pero aún así, dependiendo de su situación, si las actualizaciones de datos son raras y especialmente si siempre ocurren a granel, entonces esta solución podría ser la mejor.

es decir

once_per_day add/delete/update all records recalculate sort orders

Espero que esto sea útil.