tipos - subconsultas sql
Subconsultas vs uniones (14)
Aquí hay un ejemplo de cómo se evalúan las subconsultas en MySQL 6.0 .
El nuevo optimizador convertirá este tipo de subconsultas en combinaciones.
Refactoré una sección lenta de una aplicación que heredamos de otra empresa para usar una unión interna en lugar de una subconsulta como
where id in (select id from ... )
La consulta refactorizada se ejecuta aproximadamente 100 veces más rápido. (~ 50 segundos a ~ 0.3). Yo esperaba una mejora, pero ¿alguien puede explicar por qué fue tan drástica? Las columnas utilizadas en la cláusula where estaban todas indexadas. ¿Ejecuta SQL la consulta en la cláusula where una vez por fila o algo así?
Actualización - Explicar resultados:
La diferencia está en la segunda parte de la consulta "where id in ()" -
2 DEPENDENT SUBQUERY submission_tags ref st_tag_id st_tag_id 4 const 2966 Using where
vs 1 fila indexada con la unión:
SIMPLE s eq_ref PRIMARY PRIMARY 4 newsladder_production.st.submission_id 1 Using index
Con una subconsulta, debe volver a ejecutar el 2º SELECCIONAR para cada resultado, y cada ejecución generalmente devuelve 1 fila.
Con un join, el 2nd SELECT devuelve muchas más filas, pero solo tiene que ejecutarlo una vez. La ventaja es que ahora puedes unirte a los resultados, y unirte a las relaciones es lo que se supone que es una buena base de datos. Por ejemplo, tal vez el optimizador pueda ver cómo aprovechar mejor un índice ahora.
Ejecute el plan de explicación en cada versión, le dirá por qué.
Está ejecutando la subconsulta una vez por cada fila, mientras que la unión ocurre en los índices.
Esta pregunta es algo general, así que aquí hay una respuesta general:
Básicamente, las consultas toman más tiempo cuando MySQL tiene toneladas de filas para ordenar.
Hacer esto:
Ejecute una EXPLAIN en cada una de las consultas (la de JOIN y la de la subconsulta) y publique los resultados aquí.
Creo que ver la diferencia en la interpretación de MySQL de esas consultas sería una experiencia de aprendizaje para todos.
La subconsulta probablemente estaba ejecutando una "exploración de tabla completa". En otras palabras, no se usa el índice y se devuelven demasiadas filas que el lugar de la consulta principal tuvo que filtrarse.
Solo una suposición sin detalles, por supuesto, pero esa es la situación común.
La subconsulta where debe ejecutar 1 consulta por cada fila devuelta. La unión interna solo tiene que ejecutar 1 consulta.
Mira el plan de consulta para cada consulta.
En donde y en Join normalmente se puede implementar utilizando el mismo plan de ejecución, por lo que normalmente hay una aceleración cero desde el cambio entre ellos.
No es tanto la subconsulta como la cláusula IN, aunque las uniones son la base de al menos el motor SQL de Oracle y se ejecutan extremadamente rápido.
Optimizer no hizo un muy buen trabajo. Usualmente se pueden transformar sin ninguna diferencia y el optimizador puede hacer esto.
Por lo general, es el resultado de que el optimizador no puede descubrir que la subconsulta se puede ejecutar como una unión, en cuyo caso ejecuta la subconsulta para cada registro en la tabla en lugar de unirse a la tabla en la subconsulta con la tabla que está consultando. Algunas de las bases de datos más "empresariales" son mejores en esto, pero todavía lo extrañan a veces.
Tomado del Manual de referencia ( 14.2.10.11 Reescribir subconsultas como uniones ):
Un JOINT [EXTERIOR] JOIN puede ser más rápido que una subconsulta equivalente porque el servidor podría optimizarlo mejor, un hecho que no es específico solo del servidor MySQL.
Así que las subconsultas pueden ser más lentas que IZQUIERDA [EXTERIOR] SE UNE.
Una "subconsulta correlacionada" (es decir, una en la que la condición donde depende de los valores obtenidos de las filas de la consulta que lo contiene) se ejecutará una vez para cada fila. Una subconsulta no correlacionada (una en la que la condición where es independiente de la consulta que lo contiene) se ejecutará una vez al comienzo. El motor SQL hace esta distinción automáticamente.
Pero, sí, explicar el plan le dará los detalles sucios.
antes de que las consultas se ejecuten contra el conjunto de datos que se colocan a través de un optimizador de consultas, el optimizador intenta organizar la consulta de tal manera que pueda eliminar tantas tuplas (filas) del conjunto de resultados tan rápido como sea posible. A menudo, cuando se utilizan subconsultas (especialmente las malas), las tuplas no se pueden eliminar del conjunto de resultados hasta que la consulta externa comience a ejecutarse.
Sin ver la consulta, es difícil decir qué tenía de malo el original, pero supongo que fue algo que el optimizador no pudo mejorar. Al ejecutar ''explicar'', se mostrará el método optimizadores para recuperar los datos.