La unión interna de MySQL es demasiado larga.

06 Jun, 2023 Programación 0

Mi esquema es el siguiente:

Tabla A
id
fieldA1
fieldA2
fieldA3
fieldA4

Tabla B
id
fieldB1
fieldB2
fieldB3
fieldB4

La particularidad es que la tabla A tiene alrededor de 100,000 entradas y la tabla B tiene alrededor de 5,000,000 de entradas. Ambas tablas tienen el mismo número de ID distintos (100,000). Hay 5,000,000 combinaciones distintas para (ID, fieldB2) en la tabla B.

Quiero hacer una consulta que seleccione campos de ambas tablas y devuelva un máximo de 100,000 resultados (uno por ID, o menos dependiendo del filtro, que puede ser cualquier campo de la tabla A o B excepto fieldB2).

Como hay varias filas para cada ID de la tabla B, no me importa qué valor se devuelva, pero debe ser rápido (es decir, por ejemplo, para fieldB3 quiero cualquier valor para field3 asociado a un dispositivo). La única excepción es para el fieldB1, para el cual quiero la lista de todos los valores existentes para cada ID en el resultado. Si el rendimiento es malo para obtener la lista, puedo hacer una segunda solicitud más tarde solo para este punto, por lo que en mi investigación aún no he intentado obtener esta lista.

Lo que he intentado es:

select distinct id, fieldB1, fieldB2, fieldB3, fieldB4
    from B
    where id IN (SELECT id from A where fieldA1 > 0);

Y esto:

select distinct B.id, fieldB1, fieldB2, fieldB3, fieldB4, fieldA2, fieldA3, fieldA4 from B
INNER JOIN (SELECT id from A where fieldA1 > 0) ON A.id = B.id;

La consulta tarda más de 5 segundos (ambas). Sin el distinct, la consulta es solo ~ 10% más rápida.

Tengo 3 índices:
– uno en ID (tabla A)
– uno en ID (tabla B)
– uno en fieldA1 (tabla A)

¿Hay alguna forma de hacer esta consulta más rápido? (¿sin la lista de agregación para fieldB1 al menos?)

davy.ai

June 6, 2023 at 4:57 pm

Hay algunas optimizaciones potenciales que se pueden realizar en las consultas proporcionadas. Un enfoque es utilizar una subconsulta para obtener los valores distintos de id de A antes de unirse con B, como sigue:

SELECT A.id, B.fieldB_1, B.fieldB_2, B.fieldB_3, B.fieldB_4, A.fieldA_2, A.fieldA_3, A.fieldA_4 
FROM (SELECT DISTINCT id FROM A WHERE fieldA_1 > 0) A 
INNER JOIN B ON A.id = B.id 
GROUP BY A.id

Esta consulta primero selecciona los valores distintos de id de A que cumplen los criterios de filtro deseados. Luego une estos valores de id con B en función de la columna id, y selecciona las columnas deseadas de ambas tablas. Para asegurarse de que se devuelva solo una fila por id, el conjunto de resultados se agrupa por la columna id (lo que se puede optimizar creando un índice en esta columna).

Otro enfoque es utilizar una subconsulta correlacionada para seleccionar solo una fila de B para cada valor de id. La siguiente consulta demuestra este enfoque:

SELECT A.id, 
       (SELECT GROUP_CONCAT(DISTINCT fieldB_1) FROM B WHERE B.id = A.id GROUP BY B.id) AS fieldB_1_list,
       MAX(B.fieldB_2) AS fieldB_2, 
       MAX(B.fieldB_3) AS fieldB_3, 
       MAX(B.fieldB_4) AS fieldB_4, 
       A.fieldA_1, A.fieldA_2, A.fieldA_3, A.fieldA_4 
FROM A 
LEFT JOIN B ON A.id = B.id 
WHERE A.fieldA_1 > 0 
GROUP BY A.id

Esta consulta utiliza una unión izquierda en lugar de una unión interna para asegurarse de que todos los valores de id de A estén incluidos en el conjunto de resultados, incluso si no hay filas coincidentes en B. Para obtener la lista de valores distintos de fieldB_1 para cada id, la consulta utiliza la función GROUP_CONCAT dentro de una subconsulta, agrupando por la columna id. Para las columnas restantes, la consulta selecciona el valor máximo para cada una, que puede ser arbitrario ya que los valores para cada id no están especificados. El conjunto de resultados se agrupa por la columna id para asegurarse de que se devuelva solo una fila por id.

En general, el segundo enfoque puede ser más rápido ya que evita la necesidad de una operación distinta y puede aprovechar la unión izquierda para incluir todos los valores de id de A. Dependiendo de la distribución de datos específica y del plan de ejecución de la consulta, puede ser útil crear índices adicionales en las columnas fieldB_2, fieldB_3 y fieldB_4 en B para mejorar el rendimiento.

Answer

davy.ai

June 6, 2023 at 4:57 pm
Hay algunas optimizaciones potenciales que se pueden realizar en las consultas proporcionadas. Un enfoque es utilizar una subconsulta para obtener los valores distintos de id de A antes de unirse con B, como sigue:
```
SELECT A.id, B.fieldB_1, B.fieldB_2, B.fieldB_3, B.fieldB_4, A.fieldA_2, A.fieldA_3, A.fieldA_4 
FROM (SELECT DISTINCT id FROM A WHERE fieldA_1 > 0) A 
INNER JOIN B ON A.id = B.id 
GROUP BY A.id
```
Esta consulta primero selecciona los valores distintos de id de A que cumplen los criterios de filtro deseados. Luego une estos valores de id con B en función de la columna id, y selecciona las columnas deseadas de ambas tablas. Para asegurarse de que se devuelva solo una fila por id, el conjunto de resultados se agrupa por la columna id (lo que se puede optimizar creando un índice en esta columna).

Otro enfoque es utilizar una subconsulta correlacionada para seleccionar solo una fila de B para cada valor de id. La siguiente consulta demuestra este enfoque:
```
SELECT A.id, 
       (SELECT GROUP_CONCAT(DISTINCT fieldB_1) FROM B WHERE B.id = A.id GROUP BY B.id) AS fieldB_1_list,
       MAX(B.fieldB_2) AS fieldB_2, 
       MAX(B.fieldB_3) AS fieldB_3, 
       MAX(B.fieldB_4) AS fieldB_4, 
       A.fieldA_1, A.fieldA_2, A.fieldA_3, A.fieldA_4 
FROM A 
LEFT JOIN B ON A.id = B.id 
WHERE A.fieldA_1 > 0 
GROUP BY A.id
```
Esta consulta utiliza una unión izquierda en lugar de una unión interna para asegurarse de que todos los valores de id de A estén incluidos en el conjunto de resultados, incluso si no hay filas coincidentes en B. Para obtener la lista de valores distintos de fieldB_1 para cada id, la consulta utiliza la función GROUP_CONCAT dentro de una subconsulta, agrupando por la columna id. Para las columnas restantes, la consulta selecciona el valor máximo para cada una, que puede ser arbitrario ya que los valores para cada id no están especificados. El conjunto de resultados se agrupa por la columna id para asegurarse de que se devuelva solo una fila por id.

En general, el segundo enfoque puede ser más rápido ya que evita la necesidad de una operación distinta y puede aprovechar la unión izquierda para incluir todos los valores de id de A. Dependiendo de la distribución de datos específica y del plan de ejecución de la consulta, puede ser útil crear índices adicionales en las columnas fieldB_2, fieldB_3 y fieldB_4 en B para mejorar el rendimiento.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

La unión interna de MySQL es demasiado larga.

Answer

davy.ai