UNIR consultas vs múltiples consultas

Para las uniones internas, una sola consulta tiene sentido, ya que solo obtiene filas coincidentes. Para las uniones a la izquierda, múltiples consultas es mucho mejor... mira el siguiente punto de referencia que hice:

1. Consulta única con 5 Joins

   Consulta: 8.074508 segundos

   Tamaño del resultado: 2268000

2. 5 consultas en 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 uniones izquierdas usan exponencialmente 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 diferentes consultas.

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

Frank

En realidad 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 de BD es obtener números del mundo real porque solo hay muchas variables a tener en cuenta, pero también creo que comparar los números entre ellos no conduce a nada bueno 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 compararse con cada uno otro.

Puedo entender si una forma de consulta toma digamos 0.02 segundos y la otra toma 20 segundos, esa es una enorme diferencia. Pero ¿qué pasa si una forma de consulta toma 0.0000000002 segundos, y la otra toma 0.0000002 segundos ? En ambos casos una forma es la friolera de 1000 veces más rápido que el otro, pero es realmente todavía "friolera" en el segundo caso ?

Conclusión como yo personalmente lo veo: si funciona bien, ir a la solución fácil.

Hizo una prueba rápida seleccionando una fila de una tabla de 50,000 filas y uniéndose con una fila de una tabla de 100,000 filas. Básicamente parecía:

$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 two select tomó 3.7 segundos para 50,000 lecturas, mientras que el JOIN tomó 2.0 segundos en mi computadora lenta en casa. LA UNIÓN INTERNA y la UNIÓN IZQUIERDA no marcaron la diferencia. Obtener varias filas (por ejemplo, usando IN SET) produjo resultados similares.

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

Entonces aún mejor add agrega "EXPLAIN" al principio de cada consulta. Esto le dirá cuántas subconsultas MySQL está utilizando 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 sencillo hacer muchas llamadas SELECT.

Intente ejecutar algunas estadísticas de la base de datos tanto en la COMBINACIÓN como en las selecciones múltiples. Vea si en su entorno la UNIÓN es más rápida / lenta que la SELECCIÓN.

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

Salud,

BLT

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

Respuesta de TLDR:

Si uno a uno, use una instrucción JOIN.

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

Por Qué y Cómo Utilizar SELECT para la Optimización

SELECT'ing (con múltiples consultas en lugar de uniones) en un gran grupo de registros basados en una relación uno-a-muchos 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 ordenarlos:

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í, estoy obteniendo todos los registros, en una instrucción select. Esto es mejor que JOIN, que sería obtener un pequeño grupo de estos registros, uno a la vez, como un subcomponente de otra consulta. Luego lo analizo con código del lado del servidor que parece algo así...

<?php
    foreach($addresses as $address) {
         $persons[$address['Personid']]->Address[] = $address;
    }
?>

Cuándo No Usar JOIN para la Optimización

JOIN'ing un gran grupo de registros basados en una relación uno-a-uno con un solo registro produce una eficiencia óptima en comparación con múltiples sentencias SELECT, una tras otra, que simplemente obtienen el siguiente tipo de registro.

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

Ejemplo: La base de datos Blogs tiene 3 tablas de interés, Blogpost, Etiqueta, y Comentario.

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

Si hay 1 blogpost, 2 etiquetas 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. Bien, por lo tanto, 2 comentarios y 2 etiquetas es 4 filas. ¿Qué pasa si tenemos 4 comentarios y 4 etiquetas? No obtienes 8 filas get 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 llenas de en su mayoría datos redundantes.

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

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 encontrado que generalmente es más rápido ejecutar varias consultas, especialmente cuando se recuperan 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 formas de limitar el número de viajes realizados al servidor y aún así 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 novato cuando se trata de SQL, creo que hay una tendencia para los desarrolladores, especialmente los jóvenes a pasar mucho tiempo tratando de escribir uniones muy inteligentes porque se ven inteligentes, mientras que en realidad hay formas inteligentes de extraer datos que parecen simples.

El último párrafo fue una opinión personal, pero espero que esto ayude. Estoy de acuerdo con los demás, sin embargo, que dicen que debe benchmark. Ninguno de los dos enfoques es una bala de plata.

¿Será más rápido en términos de rendimiento? Probablemente. Pero también potencialmente bloquea más objetos de base de datos a la vez (dependiendo 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 de la base de datos " cuando en realidad en la mayoría de los sistemas OLTP donde la base de datos está 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 Oracle database 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 respuesta binaria. La cuestión de lo que es mejor para el rendimiento depende de su entorno. Por cierto, si su selección única con un identificador no es sub-segundo, algo puede estar mal con su configuración.

La verdadera pregunta es ¿cómo desea acceder a los datos. Single selects admite el enlace tardío. Por ejemplo, si solo desea información de los empleados, puede seleccionarla en la tabla Empleados. La clave foránea las relaciones se pueden usar para recuperar recursos relacionados en un momento posterior y según sea necesario. Los selects ya tendrán una clave a la que apuntar, por lo que deberían ser extremadamente rápidos, y solo tendrás que recuperar lo que necesites. La latencia de la red siempre debe tenerse en cuenta.

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

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

Las diferencias de rendimiento estarán vinculadas en gran medida a la relación de la información que está consultando. Une el trabajo, y son rápidos cuando los datos están relacionados y indexas las cosas correctamente, pero a menudo resultan en 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á lugar a 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 mencionó una sola consulta para publicaciones, etiquetas y comentarios. Los comentarios están relacionados con un post, al igual que las etiquetas...pero las etiquetas no están relacionadas comentar.

+------------+     +---------+     +---------+
|  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 sean al menos dos consultas separadas. Si intentas unir etiquetas y comentarios, porque no hay una relación directa entre los dos, terminas con todas las combinaciones posibles de etiquetas y comentarios. many * many == manymany. Aparte de eso, ya que las publicaciones y las etiquetas no están relacionadas, puedes hacer esas dos consultas en paralelo, lo que lleva a una ganancia potencial.

Sin embargo, consideremos un escenario diferente: Quieres 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 un join. Aparte de ser una consulta mucho más natural, la mayoría de los sistemas de bases de datos (incluido MySQL) tienen muchas personas inteligentes que ponen mucho trabajo duro en la optimización de consultas como esta. Para consultas separadas, ya que cada consulta depende de los resultados de la anterior, las consultas no se pueden hacer en paralelo, y el tiempo total se convierte no solo en el tiempo de ejecución real de las consultas, sino también en el tiempo dedicado a la búsqueda resultados, tamizándolos en busca de ID para la siguiente consulta, enlazando filas, etc.

Sí, una consulta usando JOINS 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 lo sabrá con seguridad?..