unir optimizar inner horizontalmente ejemplo consultas consulta concatenar sql performance

optimizar - union sql server



Rendimiento de la unión interna en comparación con la unión cruzada (10)

Desde el comienzo de los optimizadores de tiempo se han construido en torno a la clásica sintaxis del producto restrict-project-cartesian. Prácticamente todos los proveedores copiaron el diseño iniciado por System R. Luego, a regañadientes, los proveedores adoptaron la sintaxis ANSI "última y mejor" y actualizaron sus motores de ejecución de SQL. Contrario a lo que el folleto de marketing puede decirle ("use la sintaxis más reciente"), no se ha cambiado mucho en el nivel de implementación física: todavía está [indexado] bucles anidados, o hash o sort-merge join. Por lo tanto, no hay ninguna base para asumir la superioridad de una sintaxis sobre la otra.

Para mi gusto personal, la nueva sintaxis es redundant , ruidosa e inconsistent . En cuanto a ser sancionado por el comité, "entre en cualquier parque en cada ciudad y no encontrará ninguna estatua de comité".

El efecto de emitir una combinación interna es lo mismo que establecer una combinación cruzada con la condición de unión en la cláusula WHERE. Noté que mucha gente en mi compañía usa combinaciones cruzadas, donde usaría combinaciones internas. No noté ninguna ganancia de rendimiento significativa después de cambiar algunas de estas consultas y me preguntaba si era solo una coincidencia o si el DBMS optimiza dichos problemas de forma transparente (MySql en nuestro caso). Y aquí un ejemplo concreto para la discusión:

SELECT User.* FROM User, Address WHERE User.addressId = Address.id; SELECT User.* FROM User INNER JOIN Address ON (User.addressId = Address.id);

El orden en el que unes las tablas o pones tus condiciones de ENCENDIDO / DONDE no deberían importar.

El optimizador de consultas debe optimizar y usar el mejor orden de todos modos (y elegir la mejor manera de filtrar los datos, dónde comenzar, etc.)

Sin embargo, como muchos otros, sugiero usar la sintaxis INNER JOIN, ya que hace las cosas mucho más legibles, es más transparente con la sintaxis de las uniones LEFT o FULL también.

Aquí hay algo más de texto sobre este tema: http://linus.brimstedt.se/?/article/articleview/SQL Syntax

/SEGUNDO

El primer ejemplo es funcionalmente el mismo que el segundo ejemplo. Sin embargo, esta sintaxis debe evitarse por varias razones. En primer lugar, es mucho más fácil obtener una combinación cruzada al usar esta sintaxis, especialmente cuando hay varias combinaciones en la tabla. Si ve mucho de este tipo de consulta con la palabra clave distinct, probablemente tenga a alguien que esté tratando de arreglar las combinaciones cruzadas.

A continuación, la sintaxis de unión izquierda y derecha que usa el estilo anterior está en desuso y ya no será compatible. Además, no funciona correctamente ahora de todos modos. Algunas veces malinterpreta la combinación externa y devuelve el conjunto de resultados incorrecto. Entonces cualquier consulta que tenga usando = o = en la cláusula where debe ser reemplazada inmediatamente.

En tercer lugar, las uniones estándar ANSI son más fáciles de comprender y mantener. La comprensión de las uniones es una de las habilidades básicas más críticas que cualquier persona que consulte una base de datos relacional necesita tener. Según mi experiencia, algunas personas que usan el estilo anterior realmente no entienden las uniones y cómo funcionan y, por lo tanto, escriben consultas que en realidad no hacen lo que pretendían.

Encuentro que los lugares de trabajo que permiten la primera sintaxis (tablas separadas por comas) tienden a tener un tiempo considerable dedicado a la depuración de casos en los que se devuelven más filas de las previstas. Las combinaciones cruzadas involuntarias son la perdición de un sistema, y ​​pueden traer hasta la base de datos más afinada a sus rodillas. Ha detenido nuestro sistema de pre-producción en al menos dos ocasiones en el último año.

La segunda sintaxis (sintaxis de unión) obliga al escritor a pensar primero en cómo se unen las tablas, y luego solo devuelve las filas interesantes. Es imposible hacer una combinación cruzada accidentalmente usando esta sintaxis y, por lo tanto, se reduce el riesgo de consultas accidentales de bajo rendimiento.

Sin embargo, aparte de eso, nunca he notado ninguna diferencia de velocidad entre las dos sintaxis en ningún sistema que haya tenido.

Explicar ambas consultas da el mismo resultado

mysql> explain select * from t T1, t T2 where T1.ID=T2.ID; +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | SIMPLE | T1 | ALL | PRIMARY | NULL | NULL | NULL | 3 | | | 1 | SIMPLE | T2 | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 2 rows in set (0.00 sec) mysql> explain select * from t T1 join t T2 on T1.ID=T2.ID; +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | SIMPLE | T1 | ALL | PRIMARY | NULL | NULL | NULL | 3 | | | 1 | SIMPLE | T2 | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 2 rows in set (0.00 sec)

Pero es preferible utilizar la sintaxis de unión interna ya que es más clara y precisa. Mysql puede ajustar internamente las consultas de combinación izquierda y derecha para seleccionar menos datos en comparación con la combinación cruzada.

Las combinaciones cruzadas producen resultados que consisten en cada combinación de filas de dos o más tablas. Eso significa que si la tabla A tiene 6 filas y la tabla B tiene 3 filas, una combinación cruzada dará como resultado 18 filas. No existe una relación establecida entre las dos tablas: literalmente solo produces todas las combinaciones posibles.

Con una combinación interna, los valores de columna de una fila de una tabla se combinan con valores de columna de otra fila de otra (o la misma) tabla para formar una sola fila de datos.

Si se agrega una cláusula WHERE a una combinación cruzada, se comporta como una unión interna ya que WHERE impone un factor limitante.

Siempre que sus consultas cumplan con las pautas de rendimiento específicas del vendedor y del sentido común, me gustaría pensar en la decisión sobre qué tipo de unión usar como simple cuestión de gusto.

No hay ninguna diferencia más que la unión interna que es mucho más clara porque define la unión, dejando la cláusula where como la condición límite real.

Un beneficio adicional de la primera sintaxis es que puede ser más general en su condición límite. No solo igualdad.

Pero si usa la igualdad, ¿por qué confiar en el optimizador? Asegúrese de que no genere primero la unión cruzada y luego elimine las filas. Usa el segundo.

Use EXPLAIN para ver el plan de consultas para ambas consultas y vea si hay alguna diferencia. Es muy posible que MySQL use el mismo plan de ejecución en ambos casos. Uso la sintaxis INNER JOIN principalmente porque es mucho más clara.