mysql - optimizer - optimize sql query online

Las consultas "IN" de MySQL son terriblemente lentas con subconsultas pero rápidas con valores explícitos

Question

Tengo una consulta de MySQL (Ubu 10.04, Innodb, Core i7, 16Gb RAM, unidades SSD, params MySQL optimizado):

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (SELECT l.id FROM em_link l WHERE l.campaignid = ''2900'' AND l.link != ''open'')

La tabla em_link_data tiene alrededor de 7 millones de filas, em_link tiene unos pocos miles. Esta consulta tardará unos 18 segundos en completarse. Sin embargo, si sustituyo los resultados de la subconsulta y hago esto:

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (24899,24900,24901,24902);

entonces la consulta se ejecutará en menos de 1 milisegundo. La subconsulta sola se ejecuta en menos de 1 ms, la columna linkid está indexada.

Si reescribo la consulta como una combinación, también menos de 1 ms. ¿Por qué una consulta "IN" es tan lenta con una subconsulta en ella y por qué tan rápida con valores en ella? No puedo volver a escribir la consulta (software comprado), ¡así que esperaba que hubiera alguna modificación o sugerencia para acelerar esta consulta! Cualquier ayuda es apreciada.

Answer 1

El problema es que MySQL ejecuta consultas desde afuera hacia adentro, mientras que usted puede pensar que su subconsulta se realiza una vez y luego sus resultados se pasan a la expresión DÓNDE de la consulta externa (consulte la documentación de MySQL ).

Si no puede volver a escribir su consulta, debe hacer las siguientes optimizaciones:

• agregue un índice en campaignid y link como dijo FrustratedWithFormsDesigner
• compruebe que la subconsulta utiliza índices correctamente haciendo EXPLAIN SELECT ...
• habilite y modifique la caché de consultas, ya que eso debería acelerar la subconsulta a la que se llama varias veces

Una idea más sería instalar el proxy de MySQL y escribir un pequeño script que intercepte su consulta y la vuelva a escribir para usar una unión.

Answer 2

Las subconsultas se ejecutan cada vez que las evalúas (en MySQL de todos modos, no todos los RDBMS), es decir, ¡básicamente estás ejecutando 7 millones de consultas! El uso de UNIR, si es posible, lo reducirá a 1. Incluso si agregar indexación mejora el rendimiento de esos, aún los está ejecutando.

Answer 3

Sí, IN con subconsultas es lento. Utilice una unión en su lugar.

SELECT
COUNT(DISTINCT subscriberid)
FROM em_link_data JOIN em_link ON em_link_data.linkid=em_link.id
WHERE em_link.campaignid = ''2900'' AND em_link.link != ''open''

Y asegúrese de haber definido los índices en em_link_data.linkid y em_link.id .

Answer 4

Si su subconsulta es rápida, entonces campaignid y link están absolutamente indexados. l.id es PK y agrupado por lo tanto es rápido. Pero por lo que recuerdo (desde la última vez que revisé este tema), mysql describe sus optimizaciones internas para subconsultas "in" para usar el índice de resultados de subconsultas para mejorar el rendimiento y también usa caché para el lado izquierdo de "IN" para arrastrarlo dentro de la subconsulta más rápido y si los índices se establecen como verdaderos, no debe haber tanta diferencia para usar la unión interna o "IN" en lugar del almacenamiento en caché y puede deberse a un problema de caché y datos masivos. http://dev.mysql.com/doc/internals/en/transformation-scalar-in.html

No conozco la situación del software, pero si puede utilizar INNER JOIN y tiene (probablemente) algunas definiciones adicionales antes de la cláusula IN de la cláusula WHERE de su consulta externa, asegúrese de mover esas cláusulas a antes de su INNER principal JOIN a través de un temporal INNER JOIN se comporta de forma similar a una cláusula "where" intermedia de forma secuencial y reduce el número de comparaciones cruzadas en un JOIN como este:

SELECT ... FROM t
INNER JOIN (SELECT 1) AS tmp ON t.asd=23
INNER JOIN t2 ON ...

Comparaciones de muestra de búsquedas de combinación normal y temporal: 1000 * 1000> 1000 + (100 * 1000)

También parece que la subconsulta se filtra por valores constantes, por lo tanto, si fuera yo, pondría las cláusulas en una subconsulta para generar el conjunto de resultados y reducir el número de comparaciones en una UNIÓN como esta:

SELECT ... FROM t
INNER JOIN (SELECT ... FROM t2 WHERE constant clauses) AS tbl2 ON ...

De todos modos, en la consulta "IN", la comparación de cualquier columna de la tabla en la subconsulta con cualquier columna de la tabla en la consulta externa requiere que las columnas de ambos lados estén indexadas con precisión (con respecto a los índices compuestos) pero aún así puede ser un caché problema.

EDITADO: También tenía curiosidad de preguntar: ¿Puede tener sentido hacer un índice compuesto en l.campaignid, l.link y l.id?