sql - clausula - join vs subquery performance

Unirse vs sub-consulta (17)

Creo que lo que se ha subrayado menos en las respuestas citadas es el problema de los duplicados y los resultados problemáticos que pueden surgir de casos específicos (de uso).

(aunque Marcelo Cantos sí lo menciona)

Citaré el ejemplo de los cursos de Stanford sobre Lagunita sobre SQL.

Mesa de estudiantes

+------+--------+------+--------+ | sID | sName | GPA | sizeHS | +------+--------+------+--------+ | 123 | Amy | 3.9 | 1000 | | 234 | Bob | 3.6 | 1500 | | 345 | Craig | 3.5 | 500 | | 456 | Doris | 3.9 | 1000 | | 567 | Edward | 2.9 | 2000 | | 678 | Fay | 3.8 | 200 | | 789 | Gary | 3.4 | 800 | | 987 | Helen | 3.7 | 800 | | 876 | Irene | 3.9 | 400 | | 765 | Jay | 2.9 | 1500 | | 654 | Amy | 3.9 | 1000 | | 543 | Craig | 3.4 | 2000 | +------+--------+------+--------+

Aplicar tabla

(aplicaciones hechas a universidades y carreras específicas)

+------+----------+----------------+----------+ | sID | cName | major | decision | +------+----------+----------------+----------+ | 123 | Stanford | CS | Y | | 123 | Stanford | EE | N | | 123 | Berkeley | CS | Y | | 123 | Cornell | EE | Y | | 234 | Berkeley | biology | N | | 345 | MIT | bioengineering | Y | | 345 | Cornell | bioengineering | N | | 345 | Cornell | CS | Y | | 345 | Cornell | EE | N | | 678 | Stanford | history | Y | | 987 | Stanford | CS | Y | | 987 | Berkeley | CS | Y | | 876 | Stanford | CS | N | | 876 | MIT | biology | Y | | 876 | MIT | marine biology | N | | 765 | Stanford | history | Y | | 765 | Cornell | history | N | | 765 | Cornell | psychology | Y | | 543 | MIT | CS | N | +------+----------+----------------+----------+

Intentemos encontrar los puntajes de GPA para los estudiantes que han aplicado a CS major (independientemente de la universidad)

Usando una subconsulta:

select GPA from Student where sID in (select sID from Apply where major = ''CS''); +------+ | GPA | +------+ | 3.9 | | 3.5 | | 3.7 | | 3.9 | | 3.4 | +------+

El valor promedio para este conjunto de resultados es:

select avg(GPA) from Student where sID in (select sID from Apply where major = ''CS''); +--------------------+ | avg(GPA) | +--------------------+ | 3.6800000000000006 | +--------------------+

Usando una unión:

select GPA from Student, Apply where Student.sID = Apply.sID and Apply.major = ''CS''; +------+ | GPA | +------+ | 3.9 | | 3.9 | | 3.5 | | 3.7 | | 3.7 | | 3.9 | | 3.4 | +------+

valor promedio para este conjunto de resultados:

select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = ''CS''; +-------------------+ | avg(GPA) | +-------------------+ | 3.714285714285714 | +-------------------+

Es obvio que el segundo intento produce resultados confusos en nuestro caso de uso, dado que cuenta duplicados para el cálculo del valor promedio. También es evidente que el uso de distinct con la declaración basada en la unión no eliminará el problema, dado que erróneamente mantendrá una de cada tres ocurrencias de la puntuación de 3.9 . El caso correcto es tener en cuenta DOS (2) ocurrencias del puntaje 3.9 dado que en realidad tenemos DOS (2) estudiantes con ese puntaje que cumplen con nuestros criterios de consulta.

Parece que en algunos casos una subconsulta es la forma más segura de ir, además de cualquier problema de rendimiento.

Soy un usuario de MySQL de la vieja escuela y siempre he preferido JOIN en lugar de la subconsulta. Pero hoy en día todo el mundo usa sub-consulta, y lo odio; No se por que

Me faltan los conocimientos teóricos para juzgar por mí mismo si hay alguna diferencia. ¿Una consulta secundaria es tan buena como JOIN y, por lo tanto, no hay nada de qué preocuparse?

Ejecutar en una base de datos muy grande de un antiguo CMS de Mambo:

SELECT id, alias FROM mos_categories WHERE id IN ( SELECT DISTINCT catid FROM mos_content );

0 segundos

SELECT DISTINCT mos_content.catid, mos_categories.alias FROM mos_content, mos_categories WHERE mos_content.catid = mos_categories.id;

~ 3 segundos

Un EXPLICACIÓN muestra que examinan exactamente el mismo número de filas, pero una toma 3 segundos y la otra es casi instantánea. ¿Moraleja de la historia? Si el rendimiento es importante (¿cuándo no lo es?), Pruébelo de varias maneras y vea cuál es la más rápida.

Y...

SELECT DISTINCT mos_categories.id, mos_categories.alias FROM mos_content, mos_categories WHERE mos_content.catid = mos_categories.id;

0 segundos

Nuevamente, los mismos resultados, el mismo número de filas examinadas. Mi conjetura es que DISTINCT mos_content.catid toma mucho más tiempo para averiguar que DISTINCT mos_categories.id.

En el año 2010, me hubiera unido al autor de estas preguntas y hubiera votado con fuerza para JOIN a JOIN . Pero con mucha más experiencia (especialmente en MySQL) puedo afirmar: sí, las subconsultas pueden ser mejores. He leído múltiples respuestas aquí. Algunos indicaron que las subconsultas son más rápidas, pero carecía de una buena explicación. Espero poder proporcionar una con esta respuesta (muy) tardía:

En primer lugar, permítanme decir lo más importante: hay diferentes formas de subconsultas.

Y la segunda afirmación importante: el tamaño importa.

Si utiliza subconsultas, debe tener en cuenta cómo el servidor DB ejecuta la subconsulta. ¡Especialmente si la subconsulta se evalúa una vez o por cada fila! Por otro lado, un moderno DB-Server es capaz de optimizar mucho. En algunos casos, una subconsulta ayuda a optimizar una consulta, pero una versión más reciente de DB-Server puede hacer que la optimización sea obsoleta.

Subconsultas en campos de selección

SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo

Tenga en cuenta que se ejecuta una subconsulta para cada fila resultante de foo . Evite esto si es posible, puede ralentizar drásticamente su consulta en grandes conjuntos de datos. Pero si la subconsulta no tiene ninguna referencia a foo , el servidor de base de datos puede optimizarlo como contenido estático y podría evaluarse solo una vez.

Subconsultas en la declaración Where

SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)

Si tiene suerte, el DB lo optimiza internamente en JOIN . Si no, su consulta será muy, muy lenta en grandes conjuntos de datos, porque ejecutará la subconsulta para cada fila en foo , no solo los resultados como en el tipo de selección.

Subconsultas en el estado de unión

SELECT moo, bar FROM foo LEFT JOIN ( SELECT MIN(bar), me FROM wilco GROUP BY me ) ON moo = me

Esto es interesante. Combinamos JOIN con una sub-consulta. Y aquí obtenemos la verdadera fuerza de las subconsultas. Imagine un conjunto de datos con millones de filas en wilco pero solo unas pocas me distinguen. En lugar de unirnos contra una gran mesa, ahora tenemos una tabla temporal más pequeña para unirnos. Esto puede resultar en consultas mucho más rápidas, dependiendo del tamaño de la base de datos. Puede tener el mismo efecto con CREATE TEMPORARY TABLE ... e INSERT INTO ... SELECT ... , lo que puede proporcionar una mejor legibilidad en consultas muy complejas (pero puede bloquear conjuntos de datos en un nivel de aislamiento de lectura repetible).

Subconsultas anidadas

SELECT moo, bar FROM ( SELECT moo, CONCAT(roger, wilco) AS bar FROM foo GROUP BY moo HAVING bar LIKE ''SpaceQ%'' ) AS temp_foo GROUP BY bar ORDER BY bar

Puede anidar subconsultas en múltiples niveles. Esto puede ayudar en grandes conjuntos de datos, si tiene que agrupar u ordenar los resultados. Por lo general, el servidor DB crea una tabla temporal para esto, pero a veces no es necesario clasificar en toda la tabla, solo en el conjunto de resultados. Esto podría proporcionar un rendimiento mucho mejor dependiendo del tamaño de la tabla.

Conclusión

Las consultas secundarias no sustituyen a un JOIN y no debe usarlas de esta manera (aunque sea posible). En mi humilde opinión, el uso correcto de una subconsulta es el uso como reemplazo rápido de CREATE TEMPORARY TABLE ... Una buena subconsulta reduce un conjunto de datos de una manera que no puede lograr en una declaración ON de un JOIN . Si una subconsulta tiene una de las palabras clave GROUP BY o DISTINCT y es preferible que no se encuentre en los campos de selección o en la instrucción where, entonces podría mejorar mucho el rendimiento.

En estos días, muchos dbs pueden optimizar subconsultas y uniones. Por lo tanto, solo tienes que examinar tu consulta usando explicar y ver cuál es más rápido. Si no hay mucha diferencia en el rendimiento, prefiero usar la subconsulta ya que son simples y fáciles de entender.

En la mayoría de los casos, los JOIN son más rápidos que las consultas secundarias y es muy raro que una consulta secundaria sea más rápida.

En JOIN , RDBMS puede crear un plan de ejecución que sea mejor para su consulta y puede predecir qué datos deben cargarse para ser procesados y ahorrar tiempo, a diferencia de la subconsulta en la que ejecutará todas las consultas y cargará todos sus datos para realizar la consulta. tratamiento.

Lo bueno de las subconsultas es que son más legibles que las de JOIN : es por eso que la mayoría de las personas nuevas de SQL las prefieren; Es el camino fácil; pero en lo que se refiere al rendimiento, las UNIONES son mejores en la mayoría de los casos, aunque tampoco son difíciles de leer.

En primer lugar, para comparar los dos primeros, debe distinguir las consultas con subconsultas para:

una clase de subconsultas que siempre tienen una consulta equivalente correspondiente escrita con uniones
una clase de subconsultas que no pueden reescribirse usando uniones

Para la primera clase de consultas, un buen RDBMS verá las combinaciones y subconsultas como equivalentes y producirá los mismos planes de consulta.

En estos días incluso mysql hace eso.

Sin embargo, a veces no lo hace, pero esto no significa que las uniones siempre ganarán; tuve casos al usar subconsultas en mysql que mejoraron el rendimiento. (Por ejemplo, si hay algo que impide que el planificador de mysql calcule correctamente el costo y si el planificador no ve la variante de combinación y la variante de subconsulta como iguales, las subconsultas pueden superar las uniones forzando una ruta determinada).

La conclusión es que debe probar sus consultas para las variantes de unión y subconsulta si desea estar seguro de cuál funcionará mejor.

Para la segunda clase, la comparación no tiene sentido, ya que esas consultas no pueden reescribirse utilizando uniones y, en estos casos, las subconsultas son una forma natural de realizar las tareas requeridas y no debe discriminarlas.

La diferencia solo se ve cuando la segunda tabla de unión tiene significativamente más datos que la tabla principal. Tuve una experiencia como la siguiente ...

Teníamos una tabla de usuarios de cien mil entradas y sus datos de membresía (amistad) alrededor de 3 mil entradas. Fue una declaración conjunta para tomar amigos y sus datos, pero con un gran retraso. Pero funcionaba bien donde solo había una pequeña cantidad de datos en la tabla de miembros. Una vez que lo cambiamos para usar una subconsulta, funcionó bien.

Pero mientras tanto, las consultas de unión están trabajando con otras tablas que tienen menos entradas que la tabla principal.

Así que creo que las declaraciones de unión y sub consulta funcionan bien y depende de los datos y la situación.

Las consultas secundarias son la forma lógica correcta de resolver los problemas del formulario, "Obtener datos de A, condicionales a los datos de B". En tales casos, tiene más sentido lógico pegar B en una subconsulta que hacer una unión. También es más seguro, en un sentido práctico, ya que no tiene que ser cauteloso al obtener datos duplicados de A debido a múltiples coincidencias con B.

En la práctica, sin embargo, la respuesta generalmente se reduce al rendimiento. Algunos optimizadores chupan limones cuando se les da una unión frente a una subconsulta, y otros chupan limones de otra manera, y esto es específico del optimizador, específico de la versión de DBMS y específico de la consulta.

Históricamente, las uniones explícitas generalmente ganan, por lo tanto, la sabiduría establecida de que las uniones son mejores, pero los optimizadores están mejorando todo el tiempo, por lo que prefiero escribir las consultas primero de una manera lógicamente coherente y luego reestructurar si las restricciones de rendimiento lo justifican.

Las subconsultas generalmente se usan para devolver una sola fila como un valor atómico, aunque se pueden usar para comparar valores contra múltiples filas con la palabra clave IN. Se les permite en casi cualquier punto significativo de una declaración SQL, incluida la lista de destino, la cláusula WHERE, etc. Una sub-consulta simple podría usarse como condición de búsqueda. Por ejemplo, entre un par de tablas:

SELECT title FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = ''Bar'' AND first_name = ''Foo'');

Tenga en cuenta que utilizar un operador de valor normal en los resultados de una subconsulta requiere que solo se devuelva un campo. Si está interesado en verificar la existencia de un solo valor dentro de un conjunto de otros valores, use IN:

SELECT title FROM books WHERE author_id IN (SELECT id FROM authors WHERE last_name ~ ''^[A-E]'');

Obviamente, esto es diferente de decir un IZQUIERDO IZQUIERDO donde solo deseas unir cosas de la tabla A y B, incluso si la condición de unión no encuentra ningún registro coincidente en la tabla B, etc.

Si solo te preocupa la velocidad, tendrás que consultar con tu base de datos y escribir una buena consulta y ver si hay alguna diferencia significativa en el rendimiento.

Las subconsultas tienen la capacidad de calcular funciones de agregación en una mosca. Por ejemplo, Encuentre el precio mínimo del libro y obtenga todos los libros que se venden con este precio. 1) Usando Subconsultas:

SELECT titles, price FROM Books, Orders WHERE price = (SELECT MIN(price) FROM Orders) AND (Books.ID=Orders.ID);

2) utilizando JOINs

SELECT MIN(price) FROM Orders; ----------------- 2.99 SELECT titles, price FROM Books b INNER JOIN Orders o ON b.ID = o.ID WHERE o.price = 2.99;

Según mi observación, como en dos casos, si una tabla tiene menos de 100,000 registros, la unión funcionará rápidamente.

Pero en el caso de que una tabla tenga más de 100,000 tablas, entonces es mejor obtener una subconsulta.

Tengo una tabla que tiene 500,000 registros que creé debajo de la consulta y su tiempo de resultado es como

SELECT * FROM crv.workorder_details wd inner join crv.workorder wr on wr.workorder_id = wd.workorder_id;

Resultado: 13.3 segundos

select * from crv.workorder_details where workorder_id in (select workorder_id from crv.workorder)

Resultado: 1.65 Segundos

Solo pienso en el mismo problema, pero estoy usando una subconsulta en la parte FROM. Necesito conectarme y consultar desde tablas grandes, la tabla "esclava" tiene un registro de 28 millones, pero el resultado es solo 128, ¡así que grandes resultados de datos! Estoy usando la función MAX () en él.

Primero estoy usando LEFT JOIN porque creo que es la forma correcta, mysql puede optimizar, etc. La segunda vez, solo para probar, reescribí para subseleccionar contra JOIN.

Tiempo de ejecución LEFT JOIN: 1.12s Tiempo de ejecución SUB-SELECT: 0.06s

¡18 veces más rápido que la subselección que la unión! Solo en el chokito adv. La subselección se ve terrible pero el resultado ...

Utilice EXPLAIN para ver cómo su base de datos ejecuta la consulta en sus datos. Hay una gran "depende" en esta respuesta ...

PostgreSQL puede reescribir una subconsulta a una combinación o una combinación a una subconsulta cuando cree que una es más rápida que la otra. Todo depende de los datos, índices, correlación, cantidad de datos, consulta, etc.

Versión de MySQL: 5.5.28-0ubuntu0.12.04.2-log

También tenía la impresión de que JOIN siempre es mejor que una subconsulta en MySQL, pero EXPLAIN es una mejor manera de emitir un juicio. Aquí hay un ejemplo en el que las consultas secundarias funcionan mejor que las JOIN.

Aquí está mi consulta con 3 sub-consultas:

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date FROM `vote-ranked-listory` vrl INNER JOIN lists l ON l.list_id = vrl.list_id INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type=''PREVIOUS_POSITION'' INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5 WHERE vrl.position <= 15 AND l.status=''ACTIVE'' AND l.is_public=1 AND vrl.ontology_id < 1000000000 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=43) IS NULL AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=55) IS NULL AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL ORDER BY vrl.moved_date DESC LIMIT 200;

EXPLICAR muestra:

La misma consulta con JOINs es:

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date FROM `vote-ranked-listory` vrl INNER JOIN lists l ON l.list_id = vrl.list_id INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type=''PREVIOUS_POSITION'' INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5 LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43 LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55 INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403 WHERE vrl.position <= 15 AND l.status=''ACTIVE'' AND l.is_public=1 AND vrl.ontology_id < 1000000000 AND lt1.list_id IS NULL AND lt2.tag_id IS NULL ORDER BY vrl.moved_date DESC LIMIT 200;

y la salida es:

+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | lt3 | ref | list_tag_key,list_id,tag_id | tag_id | 5 | const | 2386 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | l | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY | 4 | ranker.lt3.list_id | 1 | Using where | | 1 | SIMPLE | vrlih | ref | PRIMARY | PRIMARY | 4 | ranker.lt3.list_id | 103 | Using where | | 1 | SIMPLE | vrl | ref | PRIMARY | PRIMARY | 8 | ranker.lt3.list_id,ranker.vrlih.ontology_id | 65 | Using where | | 1 | SIMPLE | lt1 | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.lt3.list_id,const | 1 | Using where; Using index; Not exists | | 1 | SIMPLE | lbs | eq_ref | PRIMARY,idx_list_burial_state,burial_score | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where | | 1 | SIMPLE | lt2 | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.lt3.list_id,const | 1 | Using where; Using index | +----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+

Una comparación de la columna de rows indica la diferencia y la consulta con JOINs está usando Using temporary; Using filesort Using temporary; Using filesort .

Por supuesto, cuando ejecuto las dos consultas, la primera se realiza en 0.02 segundos, la segunda no se completa incluso después de 1 minuto, por lo que EXPLICACIÓN explicó estas consultas correctamente.

Si no tengo el INNER JOIN en la tabla list_tag , es decir, si list_tag

AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL

desde la primera consulta y en consecuencia:

INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403

a partir de la segunda consulta, entonces EXPLAIN devuelve el mismo número de filas para ambas consultas y ambas consultas se ejecutan igualmente rápido.

La documentación de MSDN para SQL Server dice

Muchas instrucciones Transact-SQL que incluyen subconsultas pueden formularse alternativamente como uniones. Otras preguntas pueden plantearse solo con subconsultas. En Transact-SQL, generalmente no hay diferencia de rendimiento entre una declaración que incluye una subconsulta y una versión semánticamente equivalente que no lo hace. Sin embargo, en algunos casos donde se debe verificar la existencia, una unión produce un mejor rendimiento. De lo contrario, la consulta anidada debe procesarse para cada resultado de la consulta externa para garantizar la eliminación de duplicados. En tales casos, un enfoque de unión daría mejores resultados.

así que si necesitas algo como

select * from t1 where exists select * from t2 where t2.parent=t1.id

intenta usar unir en su lugar. En otros casos, no hace ninguna diferencia.

Yo digo: Crear funciones para subconsultas elimina el problema de cluttter y le permite implementar lógica adicional a las subconsultas. Así que recomiendo crear funciones para subconsultas siempre que sea posible.

El desorden en el código es un gran problema y la industria ha estado trabajando para evitarlo durante décadas.

Tomado del manual de MySQL ( 13.2.10.11 Subescrituras de reescritura como uniones ):

Una UNIÓN IZQUIERDA [EXTERNA] puede ser más rápida que una subconsulta equivalente porque el servidor podría optimizarla mejor, un hecho que no es específico del Servidor MySQL solo.

Por lo tanto, las subconsultas pueden ser más lentas de lo que la IZQUIERDA [EXTERNA] SE UNE, pero en mi opinión, su fuerza es ligeramente más legible.

Una regla general es que las uniones son más rápidas en la mayoría de los casos (99%).
Cuantas más tablas de datos tienen, las subconsultas son más lentas.
Cuanto menos tablas de datos tienen, las subconsultas tienen una velocidad equivalente a las uniones .
Las subconsultas son más simples, más fáciles de entender y más fáciles de leer.
La mayoría de los marcos web y de aplicaciones y sus "ORM" y "Registro activo" generan consultas con subconsultas , porque con las subconsultas es más fácil dividir la responsabilidad, mantener el código, etc.
Para sitios web más pequeños o subconsultas de aplicaciones están bien, pero para sitios web más grandes y aplicaciones, a menudo tendrá que volver a escribir consultas generadas para unir consultas, especialmente si una consulta usa muchas subconsultas en la consulta.

Algunas personas dicen que "algunos RDBMS pueden reescribir una subconsulta a una unión o una unión a una subconsulta cuando piensa que una es más rápida que la otra", pero esta declaración se aplica a casos simples, seguramente no para consultas complicadas con subconsultas que en realidad causan una Problemas en el rendimiento.