SUSCRIBIR consultas frente a consultas múltiples

¿Las consultas JOIN son más rápidas que varias consultas? (Ejecuta su consulta principal, y luego ejecuta muchos otros SELECT basados ​​en los resultados de su consulta principal)

Lo estoy preguntando porque UNIRSE a ellos complicaría MUCHO el diseño de mi aplicación

Si son más rápidos, ¿puede alguien aproximarse aproximadamente por cuánto? Si es 1.5x no me importa, pero si es 10x, supongo que sí.

Esto es demasiado vago para darle una respuesta relevante para su caso específico. Depende de muchas cosas. Jeff Atwood (fundador de este sitio) realmente escribió sobre esto . Sin embargo, en la mayoría de los casos, si tienes los índices correctos y haces tus JOINs de manera adecuada, generalmente será más rápido hacer 1 viaje que varios.

Para las combinaciones internas, una sola consulta tiene sentido, ya que solo obtiene filas coincidentes. Para las combinaciones a la izquierda, las consultas múltiples son mucho mejores … observe la siguiente evaluación comparativa que hice:

  1. Consulta única con 5 uniones

    consulta: 8.074508 segundos

    tamaño del resultado: 2268000

  2. 5 consultas en una fila

    tiempo de consulta combinado: 0.00262 segundos

    tamaño del resultado: 165 (6 + 50 + 7 + 12 + 90)

.

Tenga en cuenta que obtenemos los mismos resultados en ambos casos (6 x 50 x 7 x 12 x 90 = 2268000)

Las combinaciones a la izquierda usan de forma exponencial más memoria con datos redundantes.

El límite de memoria puede no ser tan malo si solo hace una combinación de dos tablas, pero generalmente tres o más y vale la pena realizar diferentes consultas.

Como nota al margen, mi servidor MySQL está justo al lado de mi servidor de aplicaciones … por lo que el tiempo de conexión es insignificante. Si su tiempo de conexión está en segundos, entonces tal vez haya un beneficio

Franco

Llegué a esta pregunta buscando una respuesta yo mismo, y después de leer las respuestas dadas, solo puedo estar de acuerdo en que la mejor manera de comparar el rendimiento de las consultas DB es obtener números del mundo real porque hay que tener en cuenta muchas variables PERO, también creo que comparar los números entre ellos no conduce a ningún bien en casi todos los casos. Lo que quiero decir es que los números siempre deben compararse con un número aceptable y definitivamente no deben compararse entre sí.

Puedo entender si una forma de consultar lleva, por ejemplo, 0.02 segundos y la otra tarda 20 segundos, eso es una gran diferencia. ¿Pero qué pasa si una forma de consulta toma 0.0000000002 segundos, y la otra tarda 0.0000002 segundos? En ambos casos, una forma es 1000 veces más rápida que la otra, pero ¿es realmente “enorme” en el segundo caso?

En resumen, como personalmente lo veo: si funciona bien, busque la solución fácil.

Realicé una prueba rápida seleccionando una fila de una tabla de filas de 50,000 y uniéndome a una fila de una tabla de 100.000 filas. Básicamente se veía como:

$id = mt_rand(1, 50000); $row = $db->fetchOne("SELECT * FROM table1 WHERE id = " . $id); $row = $db->fetchOne("SELECT * FROM table2 WHERE other_id = " . $row['other_id']); 

vs

 $id = mt_rand(1, 50000); $db->fetchOne("SELECT table1.*, table2.* FROM table1 LEFT JOIN table1.other_id = table2.other_id WHERE table1.id = " . $id); 

El método de dos selecciones tomó 3.7 segundos para 50,000 lecturas mientras que el JOIN tomó 2.0 segundos en mi computadora lenta doméstica. INNER JOIN y LEFT JOIN no hicieron la diferencia. La obtención de múltiples filas (por ejemplo, usando IN SET) arrojó resultados similares.

Construya tanto consultas separadas como combinaciones, luego cronometra cada una de ellas; nada ayuda más que los números del mundo real.

Entonces aún mejor: agregue “EXPLICAR” al comienzo de cada consulta. Esto le indicará cuántas subconsultas utiliza MySQL para responder a su solicitud de datos, y cuántas filas escaneadas para cada consulta.

Dependiendo de la complejidad de la base de datos en comparación con la complejidad del desarrollador, puede ser más simple hacer muchas llamadas SELECT.

Intente ejecutar algunas estadísticas de base de datos tanto en JOIN como en las SELECCIONES múltiples. Vea si en su entorno, JOIN es más rápido / más lento que SELECT.

Por otra parte, si cambiarlo a un JOIN significaría un día / semana / mes extra de trabajo de desarrollo, me quedaría con múltiples SELECTs

Aclamaciones,

BLT

La verdadera pregunta es: ¿estos registros tienen una relación uno a uno o una relación de uno a muchos ?

Respuesta TLDR:

Si es uno a uno, use una statement JOIN .

Si uno-a-muchos, use una (o muchas) SELECT con optimización del código del lado del servidor.

Por qué y cómo usar SELECT para la optimización

SELECT ‘(con múltiples consultas en lugar de uniones) en grupos grandes de registros basados ​​en una relación de uno a varios produce una eficiencia óptima, ya que JOIN ‘ ing tiene un problema de fuga de memoria exponencial. Tome todos los datos, luego use un lenguaje de scripting del lado del servidor para resolverlo:

 SELECT * FROM Address WHERE Personid IN(1,2,3); 

Resultados:

 Address.id : 1 // First person and their address Address.Personid : 1 Address.City : "Boston" Address.id : 2 // First person's second address Address.Personid : 1 Address.City : "New York" Address.id : 3 // Second person's address Address.Personid : 2 Address.City : "Barcelona" 

Aquí, obtengo todos los registros, en una sola statement. Esto es mejor que JOIN , que obtendría un pequeño grupo de estos registros, uno a la vez, como un subconjunto de otra consulta. Luego lo analizo con el código del lado del servidor que se ve algo así como …

 Address[] = $address; } ?> 

Cuándo no utilizar JOIN para la optimización

JOIN a un gran grupo de registros basado en una relación de uno a uno con un solo registro produce una eficiencia óptima en comparación con múltiples instrucciones SELECT , una después de la otra, que simplemente obtienen el siguiente tipo de registro.

Pero JOIN es ineficiente cuando obtiene registros con una relación de uno a muchos.

Ejemplo: la base de datos Blogs tiene 3 tablas de interés, Blogpost, Tag y Comment.

 SELECT * from BlogPost LEFT JOIN Tag ON Tag.BlogPostid = BlogPost.id LEFT JOIN Comment ON Comment.BlogPostid = BlogPost.id; 

Si hay 1 publicación de blog, 2 tags y 2 comentarios, obtendrá resultados como:

 Row1: tag1, comment1, Row2: tag1, comment2, Row3: tag2, comment1, Row4: tag2, comment2, 

Observe cómo se duplica cada registro. De acuerdo, entonces, 2 comentarios y 2 tags son 4 filas. ¿Qué pasa si tenemos 4 comentarios y 4 tags? No obtienes 8 filas; obtienes 16 filas:

 Row1: tag1, comment1, Row2: tag1, comment2, Row3: tag1, comment3, Row4: tag1, comment4, Row5: tag2, comment1, Row6: tag2, comment2, Row7: tag2, comment3, Row8: tag2, comment4, Row9: tag3, comment1, Row10: tag3, comment2, Row11: tag3, comment3, Row12: tag3, comment4, Row13: tag4, comment1, Row14: tag4, comment2, Row15: tag4, comment3, Row16: tag4, comment4, 

Agregue más tablas, más registros, etc., y el problema se inflará rápidamente a cientos de filas que están todas llenas de datos en su mayoría redundantes.

¿Qué te cuestan estos duplicados? Memoria (en el servidor SQL y el código que intenta eliminar los duplicados) y recursos de red (entre el servidor SQL y su servidor de códigos).

Fuente: https://dev.mysql.com/doc/refman/8.0/en/nested-join-optimization.html ; https://dev.mysql.com/doc/workbench/en/wb-relationship-tools.html

En mi experiencia, he descubierto que generalmente es más rápido ejecutar varias consultas, especialmente al recuperar grandes conjuntos de datos.

Al interactuar con la base de datos desde otra aplicación, como PHP, existe el argumento de un viaje al servidor sobre muchos.

Hay otras maneras de limitar el número de viajes realizados al servidor y aún ejecutar múltiples consultas que a menudo no solo son más rápidas sino que también hacen que la aplicación sea más fácil de leer, por ejemplo, mysqli_multi_query.

No soy un novato en lo que respecta a SQL, creo que los desarrolladores tienen una tendencia, especialmente los juniors, a pasar mucho tiempo intentando escribir uniones muy inteligentes porque se ven inteligentes, mientras que en realidad hay formas inteligentes de extraer datos que se ven sencillo.

El último párrafo fue una opinión personal, pero espero que esto ayude. Sin embargo, estoy de acuerdo con los demás que dicen que debes comparar. Ninguno de los enfoques es una bala de plata.

¿Será más rápido en términos de rendimiento? Probablemente. Pero también bloquea potencialmente más objetos de base de datos a la vez (en función de su base de datos y su esquema) y, por lo tanto, disminuye la concurrencia. En mi experiencia, las personas a menudo son engañadas por el argumento de “menos viajes de ida y vuelta” cuando en realidad en la mayoría de los sistemas OLTP donde la base de datos se encuentra en la misma LAN, el verdadero cuello de botella rara vez es la red.

Aquí hay un enlace con 100 consultas útiles, estas se prueban en la base de datos Oracle, pero recuerde que SQL es un estándar, lo que difiere entre Oracle, MS SQL Server, MySQL y otras bases de datos son el dialecto SQL:

http://javaforlearn.com/100-sql-queries-learn/

Hay varios factores que significa que no hay una respuesta binaria. La pregunta de qué es mejor para el rendimiento depende de su entorno. Por cierto, si su selección individual con un identificador no es inferior a un segundo, puede que algo esté mal con su configuración.

La pregunta real es cómo quiere acceder a los datos. Single selecciona soporte de enlace tardío. Por ejemplo, si solo desea información del empleado, puede seleccionar de la tabla Empleados. Las relaciones de clave externa se pueden usar para recuperar recursos relacionados en un momento posterior y según sea necesario. Los selectores ya tendrán una tecla para señalar, por lo que deberían ser extremadamente rápidos, y solo tienes que recuperar lo que necesitas. Latencia de red siempre debe tenerse en cuenta.

Joins recuperará todos los datos a la vez. Si está generando un informe o rellenando una cuadrícula, esto puede ser exactamente lo que quiere. Las uniones comstackdas y optomizadas simplemente van a ser más rápidas que las selecciones simples en este escenario. Recuerde, las uniones ad-hoc pueden no ser tan rápidas, debería comstackrlas (en un proceso almacenado). La respuesta de velocidad depende del plan de ejecución, que detalla exactamente qué pasos toma el DBMS para recuperar los datos.

Sí, una consulta que utiliza UNIONES sería más rápida. Aunque sin conocer las relaciones de las tablas que está consultando, el tamaño de su conjunto de datos o dónde están las claves principales, es casi imposible decir cuánto más rápido.

¿Por qué no probar ambos escenarios, entonces sabrá con certeza …

Si debe usar una combinación es ante todo si una unión tiene sentido . Solo en ese momento el rendimiento es algo que debe considerarse, ya que casi todos los demás casos tendrán un rendimiento significativamente peor .

Las diferencias de rendimiento estarán relacionadas en gran medida con la relación de la información que está consultando. Se une al trabajo, y son rápidos cuando se relacionan los datos y se indexan correctamente, pero a menudo dan como resultado cierta redundancia y, a veces, más resultados de los necesarios. Y si sus conjuntos de datos no están directamente relacionados, pegarlos en una sola consulta dará como resultado lo que se llama un producto cartesiano (básicamente, todas las combinaciones posibles de filas), que casi nunca es lo que desea.

Esto a menudo es causado por relaciones de muchos a uno a muchos. Por ejemplo, la respuesta de HoldOffHunger mencionaba una sola consulta para publicaciones, tags y comentarios. Los comentarios están relacionados con una publicación, al igual que las tags … pero las tags no están relacionadas con los comentarios.

 +------------+ +---------+ +---------+ | comment | | post | | tag | |------------|* 1|---------|1 *|---------| | post_id |-----| post_id |-----| post_id | | comment_id | | ... | | tag_id | | user_id | | | | ... | | ... | | | | ... | +------------+ +---------+ +---------+ 

En este caso, es inequívocamente mejor que esto sea al menos dos consultas separadas. Si intenta unir tags y comentarios, porque no hay una relación directa entre los dos, terminará con cada combinación posible de etiqueta y comentario. many * many == manymany . Aparte de eso, dado que las publicaciones y las tags no están relacionadas, puede hacer esas dos consultas en paralelo, lo que genera una ganancia potencial.

Sin embargo, consideremos un escenario diferente: desea que los comentarios se adjunten a una publicación y la información de contacto de los comentaristas.

  +----------+ +------------+ +---------+ | user | | comment | | post | |----------|1 *|------------|* 1|---------| | user_id |-----| post_id |-----| post_id | | username | | user_id | | ... | | ... | | ... | +---------+ +----------+ +------------+ 

Aquí es donde deberías considerar unirte. Además de ser una consulta mucho más natural, la mayoría de los sistemas de bases de datos (incluido MySQL) tienen mucha gente inteligente que trabaja arduamente para optimizar las consultas. Para consultas separadas, dado que cada consulta depende de los resultados de la anterior, las consultas no se pueden realizar en paralelo, y el tiempo total pasa a ser no solo el tiempo de ejecución real de las consultas, sino también el tiempo empleado en obtener resultados, cribar a través de ellos para ID para la siguiente consulta, unir filas, etc.