mysql - tarda - Cómo mejorar el rendimiento de las consultas con muchos JOINs

optimizar consultas mysql explain

Question

Tengo una consulta (con el propósito de hacer una vista) que está usando algunas combinaciones para obtener cada columna. El rendimiento se degrada rápidamente (¿exponencialmente?) Para cada conjunto de combinaciones agregadas.

¿Cuál sería un buen enfoque para hacer esta consulta más rápido? Por favor vea los comentarios dentro de la consulta.

Si ayuda, esto está utilizando el esquema de base de datos de WordPress.

Aquí hay una captura de pantalla de EXPLAIN

TABLA DE PRODUCTOS

+--+----+
|id|name|
+--+----+
|1 |test|
+--+----+

MESA DE METADATA

+----------+--------+-----+
|product_id|meta_key|value|
+----------+--------+-----+
|1 |price |9.99 |
+----------+--------+-----+
|1 |sku |ABC |
+----------+--------+-----+

TABLA DE TERMINACIONES DE REFERENCIA

+---------+----------------+
|object_id|term_taxonomy_id|
+---------+----------------+
|1 |1 |
+---------+----------------+
|1 |2 |
+---------+----------------+

TERM_TAXONOMY TABLE

+----------------+-------+--------+
|term_taxonomy_id|term_id|taxonomy|
+----------------+-------+--------+
|1 |1 |size |
+----------------+-------+--------+
|2 |2 |stock |
+----------------+-------+--------+

TABLA DE TÉRMINOS

+-------+-----+
|term_id|name |
+-------+-----+
|1 |500mg|
+-------+-----+
|2 |10 |
+-------+-----+

CONSULTA

SELECT
products.id,
products.name,
price.value AS price,
sku.value AS sku,
size.name AS size
FROM products
/* These joins are performing quickly */
INNER JOIN `metadata` AS price ON products.id = price.product_id AND price.meta_key = ''price''
INNER JOIN `metadata` AS sku ON products.id = sku.product_id AND sku.meta_key = ''sku''
/* Here''s the part that is really slowing it down - I run this chunk about 5 times with different strings to match */
INNER JOIN `term_relationships` AS tr ON products.id = tr.object_id
INNER JOIN `term_taxonomy` AS tt
ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = ''size''
INNER JOIN `terms` AS size
ON tt.term_id = size.term_id

Answer 1

Asegúrese de que todas las columnas en las que hay "condicionales" las declaraciones condicionales estén allí, deben estar indexadas. Esto mejorará significativamente la velocidad.

Answer 2

La siguiente secuencia de comandos está formateada según las reglas de SQL Server. Puede cambiar esto según las reglas de MySQL y probarla.

SELECT
P.id,
P.name,
PIVOT_METADATA.price,
PIVOT_METADATA.sku,
size.name AS size
FROM products P (NOLOCK)
INNER JOIN term_relationships AS tr (NOLOCK)
ON P.id = tr.object_id
INNER JOIN term_taxonomy AS tt (NOLOCK)
ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = ''size''
INNER JOIN terms AS size (NOLOCK)
ON tt.term_id = size.term_id
INNER JOIN METADATA (NOLOCK)
PIVOT
(
MAX(value)
FOR [meta_key] IN (price,sku)
)AS PIVOT_METADATA
ON P.id = PIVOT_METADATA.product_id

Lo que creo que podría ser el cuello de botella en su consulta : se está uniendo a Metadata 2 veces. Ya que hay relaciones de uno a muchos en sus tablas, la combinación de Metadatos 2 no se ve afectada, pero después de eso, al unir más tablas, aumenta el número de filas debido a la relación de uno a muchos, y por lo tanto el rendimiento disminuye. .

Lo que he intentado lograr : me estoy asegurando de que se cumplan la mayor cantidad posible de relaciones 1 a 1. Para hacer esto, he hecho un Pivot en Metadata y un precio hecho y sku como columnas. Ahora mi identificación de producto tendrá una sola fila en el pivote de Metadatos. Además, me he asegurado de unirme a este picot al final.

Darle una oportunidad. Comparta el rendimiento esperado, la cantidad de registros que tiene y también el rendimiento que obtiene con mi respuesta.

Answer 3

METADATA_TABLE y TERM_RELATIONSHIP_TABLE no tienen ninguna clave principal. Cuando haya grandes registros en estas tablas, su rendimiento de consulta se verá afectado.

Puntos de control para aumentar su rendimiento.

1. Todas las tablas deben tener clave primaria. Esto se debe a que las filas de la tabla se ordenarán físicamente.
2. Para las pequeñas y las consultas que involucran pocas tablas sería suficiente mantener la clave primaria en la tabla. Si aún desea mejorar el rendimiento, cree un índice no agrupado para columnas como el campo * object_Id de la tabla term_relationships *. El índice no agrupado debe crearse para las columnas de la tabla que participan en la operación de unión.

Sin embargo, el punto a tener en cuenta es que el índice no agrupado debe ser muy inferior en aquellas tablas en las que se realizan múltiples inserciones y actualizaciones. Esta no es una pregunta simple y no se puede responder solo en función del tiempo de ejecución. Hay otros factores que afectan la respuesta, especialmente si el entorno donde se está ejecutando un procedimiento almacenado es muy transaccional.

Puedes encontrar más here

Answer 4

Prueba esto:

SELECT p.id, p.name, MAX(CASE m.meta_key WHEN ''price'' THEN m.value ELSE '''' END) AS price,
MAX(CASE m.meta_key WHEN ''sku'' THEN m.value ELSE '''' END) AS sku, s.name AS size
FROM products p
INNER JOIN `metadata` AS m ON p.id = m.product_id
INNER JOIN `term_relationships` AS tr ON p.id = tr.object_id
INNER JOIN `term_taxonomy` AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = ''size''
INNER JOIN `terms` AS s ON tt.term_id = s.term_id
GROUP BY p.id;

Si aún encuentra que su consulta es lenta, agregue el plan EXPLAIN de mi consulta para que pueda encontrar qué columnas necesitan INDEX .

Answer 5

Su problema de rendimiento es probablemente causado por la unión con la tabla ''term_taxonomy''.
Todas las demás combinaciones parecen usar la clave principal (donde probablemente tenga índices de trabajo).

Así que mi sugerencia es agregar un índice compuesto en term_taxonomy_id y term_id (o si debe: taxonomía ). Me gusta esto:

CREATE UNIQUE INDEX idx_term_taxonomy_id_taxonomy
ON term_taxonomy( term_taxonomy_id, taxonomy);

Espero que esto te ayudará.

Answer 6

Sugeriría esos:

• Considere reducir esas uniones desde el nivel de negocios;
• Si no es posible hacerlo desde "arriba" (nivel empresarial), y los datos no son en tiempo real, sugeriría preparar una tabla de memoria (sé que la solución no es la ideal). Y seleccione sus datos de la tabla de memoria directamente.

En mi experiencia:

• "joins" es el asesino para el rendimiento, cuanto más grandes sean sus datos, más dolor sentirá;
• Intente deshacerse de las uniones, no intente mejorar el rendimiento de las consultas manteniendo las uniones a menos que tenga que hacerlo. Por lo general, trataré de solucionar esos problemas desde "arriba" a "abajo"
• La última sugerencia es si todo lo anterior no funciona. Consideraré "map / reduce + búsqueda de texto completo", si vale la pena hacerlo.

(Perdóneme que no proporcioné una solución para mejorar el rendimiento de su consulta).

Answer 7

 Declare @query as NVARCHAR(MAX)
set @query = (''SELECT
products.id,
products.name,
price.value AS price,
sku.value AS sku,
size.name AS size
FROM products
INNER JOIN metadata AS price ON products.id = price.product_id AND price.meta_key = price
INNER JOIN metadata AS sku ON products.id = sku.product_id AND sku.meta_key = sku
INNER JOIN term_relationships AS tr ON products.id = tr.object_id
INNER JOIN term_taxonomy AS tt
ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = size
INNER JOIN terms AS size
ON tt.term_id = size.term_id
into #t'')
exec(@query);
select * from #t

Espero que la forma anterior reduzca el uso del tiempo, o que la creación de una tabla temporal con todos los campos que seleccione y la actualización de la tabla temporal al unirse a la tabla temporal a todas las demás tablas también podría ser efectiva, bueno, no estoy seguro. Pero incluso estoy esperando tu resultado, ya que tu pregunta parece interesante.