tutorial - mysql usar join

Cuándo usar STRAIGHT_JOIN con MySQL

Question

Solo tenía una consulta bastante compleja con la que estaba trabajando y me llevó 8 segundos ejecutarla. EXPLAIN mostraba un orden de tabla extraño y mis índices no se usaban todos, incluso con la sugerencia FORCE INDEX. Encontré la palabra clave de combinación STRAIGHT_JOIN y comencé a reemplazar algunas de mis palabras clave INNER JOIN con ella. Noté una considerable mejora en la velocidad. Eventualmente acabo de reemplazar todas mis palabras clave INNER JOIN con STRAIGHT_JOIN para esta consulta y ahora se ejecuta en .01 segundos.

Mi pregunta es cuándo usas STRAIGHT_JOIN y cuándo usas INNER JOIN? ¿Hay alguna razón para no usar STRAIGHT_JOIN si está escribiendo buenas consultas?

Answer 1

Aquí hay un escenario que surgió recientemente en el trabajo.

Considere tres tablas, A, B, C.

A tiene 3,000 filas; B tiene 300,000,000 filas; y C tiene 2,000 filas.

Las claves foráneas se definen: B (a_id), B (c_id).

Supongamos que tiene una consulta que se ve así:

select a.id, c.id
from a
join b on b.a_id = a.id
join c on c.id = b.c_id

En mi experiencia, MySQL puede optar por ir C -> B -> A en este caso. C es más pequeño que A y B es enorme, y todos son equijoins.

El problema es que MySQL no necesariamente tiene en cuenta el tamaño de la intersección entre (C.id y B.c_id) vs (A.id y B.a_id). Si la unión entre B y C devuelve tantas filas como B, entonces es una opción muy pobre; si comenzar con A hubiera filtrado B a tantas filas como A, entonces habría sido una opción mucho mejor.

En general, desea realizar sus uniones en una orden que minimice el número de filas en el conjunto resultante. Entonces, comenzar con una tabla pequeña y unir de tal manera que la unión resultante también sea pequeña, es ideal. Las cosas van en forma de pera si se comienza con una pequeña mesa y unirla a una mesa más grande termina tan grande como la gran mesa.

Sin embargo, depende de las estadísticas. Si la distribución de datos cambia, el cálculo puede cambiar. También depende de los detalles de implementación del mecanismo de combinación.

Answer 2

De MySQL JOIN referencia :

"STRAIGHT_JOIN es similar a JOIN, excepto que la tabla de la izquierda siempre se lee antes que la tabla de la derecha. Esto se puede usar para los (pocos) casos para los que el optimizador de unión coloca las tablas en el orden incorrecto".

Answer 3

En mi corta experiencia, una de las situaciones en las que STRAIGHT_JOIN redujo mi consulta de 30 segundos a 100 milisegundos es que la primera tabla en el plan de ejecución no era la tabla que tiene el orden por columnas

-- table sales (45000000) rows
-- table stores (3) rows
SELECT whatever
FROM 
    sales 
    INNER JOIN stores ON sales.storeId = stores.id
ORDER BY sales.date, sales.id 
LIMIT 50;
-- there is an index on (date, id)

SI el optimizador elige llegar primero a las stores , causará el Using index; Using temporary; Using filesort Using index; Using temporary; Using filesort Using index; Using temporary; Using filesort porque

si el ORDER BY o GROUP BY contiene columnas de tablas distintas de la primera tabla en la cola de unión, se crea una tabla temporal.

source

Aquí el optimizador necesita un poco de ayuda diciéndole que primero use las sales usando

sales STRAIGHT_JOIN stores

Answer 4

MySQL no es necesariamente bueno para elegir el orden de combinación en consultas complejas. Al especificar una consulta compleja como un straight_join, la consulta ejecuta las uniones en el orden en que se especifican. Al colocar la tabla como el denominador menos común primero y especificar straight_join, puede mejorar el rendimiento de la consulta.

Answer 5

Si su consulta finaliza con ORDER BY... LIMIT... , puede ser óptimo reformular la consulta para engañar al optimizador para que haga el LIMIT antes de JOIN .

(Esta respuesta no se aplica solo a la pregunta original sobre STRAIGHT_JOIN , ni se aplica a todos los casos de STRAIGHT_JOIN ).

Comenzando con el ejemplo de @Accountant م , esto debería ejecutarse más rápido en la mayoría de las situaciones. (Y evita la necesidad de pistas).

SELECT  whatever
    FROM  ( SELECT id FROM sales
                ORDER BY  date, id
                LIMIT  50
          ) AS x
    JOIN  sales   ON sales.id = x.id
    JOIN  stores  ON sales.storeId = stores.id
    ORDER BY  sales.date, sales.id;

Notas:

• Primero, se obtienen 50 identificaciones. Esto será especialmente rápido con INDEX(date, id) .
• Luego, volver a las sales te permite obtener solo 30 "lo que quieras" sin arrastrarlos en una mesa temporal.
• dado que una subconsulta es, por definición, desordenada, la ORDER BY debe repetirse.
• Sí, es más complicado. Pero es generalmente más rápido.

Me opongo al uso de hits porque "incluso si es más rápido hoy, puede no ser más rápido mañana".

Answer 6

Te diré por qué tuve que usar STRAIGHT_JOIN:

• Tuve un problema de rendimiento con una consulta.
• Simplificando la consulta, la consulta fue repentinamente más eficiente
• Tratando de descubrir qué parte específica estaba trayendo el problema, simplemente no pude. (2 uniones izquierda juntas fueron lentas, y cada una fue independientemente rápida)
• Luego ejecuté EXPLAIN con consulta lenta y rápida (addind una de las combinaciones a la izquierda)
• Sorprendentemente, MySQL cambió por completo los pedidos JOIN entre las 2 consultas.

Por lo tanto obligué a una de las uniones a straight_join a FORCE la unión anterior para que se leyera primero. ¡Esto impidió que MySQL cambiara el orden de ejecución y funcionó como un amuleto!

Answer 7

STRAIGHT_JOIN , usando esta cláusula, puede controlar el orden JOIN : qué tabla se escanea en el bucle externo y cuál se encuentra en el bucle interno.

Answer 8

No recomendaría el uso de STRAIGHT_JOIN sin una buena razón. Mi propia experiencia es que el optimizador de consultas MySQL elige un plan de consulta pobre con más frecuencia de la que me gustaría, pero no con la suficiente frecuencia como para omitirlo en general, que es lo que harías si siempre usaras STRAIGHT_JOIN.

Mi recomendación es dejar todas las consultas como JOINs regulares. Si descubre que una consulta está utilizando un plan de consulta subóptimo, le sugiero primero intentar reescribir o reestructurar la consulta un poco para ver si el optimizador elegirá un mejor plan de consulta. Además, al menos para innodb, asegúrese de que las estadísticas del índice no estén desactualizadas ( ANALYZE TABLE ). Eso puede hacer que el optimizador elija un plan de consulta pobre. Las sugerencias del optimizador generalmente deberían ser su último recurso.

Otra razón para no utilizar consejos de consulta es que su distribución de datos puede cambiar con el tiempo, o su selectividad de índice puede cambiar, etc. a medida que su tabla crece. Sus sugerencias de consulta que son óptimas ahora, pueden volverse subóptimas con el tiempo. Pero el optimizador no podrá adaptar el plan de consulta debido a sus sugerencias ahora desactualizadas. Te mantienes más flexible si permites que el optimizador tome las decisiones.

Answer 9

--use 120s, 18 million data
    explain SELECT DISTINCT d.taid
    FROM tvassist_recommend_list_everyday_diverse d, tvassist_taid_all t
    WHERE d.taid = t.taid
      AND t.client_version >= ''21004007''
      AND t.utdid IS NOT NULL
      AND d.recommend_day = ''20170403''
    LIMIT 0, 10000

--use 3.6s repalce by straight join
 explain SELECT DISTINCT d.taid
    FROM tvassist_recommend_list_everyday_diverse d
    STRAIGHT_JOIN 
      tvassist_taid_all t on d.taid = t.taid 
    WHERE 
     t.client_version >= ''21004007''
       AND d.recommend_day = ''20170403''

      AND t.utdid IS NOT NULL  
    LIMIT 0, 10000