mysql - tips - Izquierda Unirse superando Inner Join?

mysql views performance

Question

He estado perfilando algunas consultas en una aplicación en la que estoy trabajando, y encontré una consulta que estaba recuperando más filas de las necesarias, y el conjunto de resultados se recortó en el código de la aplicación.

Al cambiar una UNIÓN IZQUIERDA a una UNIÓN INTERIOR, se recortó el conjunto de resultados para que fuera justo lo que se necesitaba y, presumiblemente, también sería más eficiente (ya que se seleccionaron menos filas). En realidad, la consulta LEFT JOIN''ed estaba superando a INNER JOIN''ed, tomando la mitad del tiempo para completarla.

CONJUNTO IZQUIERDO: (127 filas totales, la consulta tomó 0.0011 segundos)

INNER JOIN: (10 filas totales, Query tomó 0.0024 segundos)

(Ejecuté las consultas varias veces y esos son promedios).

Ejecutar EXPLAIN en ambos no revela nada que explique las diferencias de rendimiento:

Para el INNER JOIN:

id  select_type     table   type    possible_keys   key     key_len     ref        rows     Extra
1   SIMPLE  contacts        index       NULL        name        302     NULL         235    Using where
1   SIMPLE  lists           eq_ref      PRIMARY     PRIMARY     4   contacts.list_id     1   
1   SIMPLE  lists_to_users  eq_ref      PRIMARY     PRIMARY     8   lists.id,const  1    
1   SIMPLE  tags            eq_ref      PRIMARY     PRIMARY     4   lists_to_users.tag_id   1    
1   SIMPLE  users           eq_ref      email_2     email_2     302     contacts.email 1    Using where

Para el LEFT JOIN:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          contacts index      NULL        name        302     NULL    235     Using where
1   SIMPLE        lists     eq_ref      PRIMARY     PRIMARY     4   contacts.list_id    1    
1   SIMPLE    lists_to_users eq_ref     PRIMARY     PRIMARY     8   lists.id,const  1    
1   SIMPLE         tags     eq_ref      PRIMARY     PRIMARY     4   lists_to_users.tag_id   1    
1   SIMPLE        users     eq_ref      email_2     email_2     302     contacts.email  1   

Y la consulta en sí misma:

SELECT `contacts`.*, `lists`.`name` AS `group`, `lists`.`id` AS `group_id`, `lists`.`shared_yn`, `tags`.`name` AS `context`, `tags`.`id` AS `context_id`, `tags`.`color` AS `context_color`, `users`.`id` AS `user_id`, `users`.`avatar` 
FROM `contacts`  
LEFT JOIN `lists` ON lists.id=contacts.list_id  
LEFT JOIN `lists_to_users` ON lists_to_users.list_id=lists.id AND lists_to_users.user_id=''1'' AND lists_to_users.creator=''1''  
LEFT JOIN `tags` ON tags.id=lists_to_users.tag_id 
INNER JOIN `users` ON users.email=contacts.email 
WHERE (contacts.user_id=''1'') 
ORDER BY `contacts`.`name` ASC

(La cláusula de la que estoy hablando es la última UNIÓN INTERNA en la tabla de ''usuarios'')

La consulta se ejecuta en una base de datos MySQL 5.1, si hace una diferencia.

¿Alguien tiene una pista sobre por qué la consulta LEFT JOIN''ed supera a INNER JOIN en este caso?

ACTUALIZACIÓN: Debido a la sugerencia de Tomalak de que las tablas pequeñas que estoy usando estaban haciendo más compleja la UNIÓN INTERIOR, había creado una base de datos de prueba con algunos datos simulados. La tabla de ''usuarios'' tiene 5000 filas y la tabla de contactos tiene ~ 500,000 filas. Los resultados son los mismos (también los tiempos no han cambiado, lo que es sorprendente si tenemos en cuenta que las tablas son mucho más grandes ahora).

También ejecuté ANALYZE y OPTIMIZE en la tabla de contactos. No hizo ninguna diferencia discernible.

Answer 1

Imo te estás cayendo en la trampa conocida como optimización prematura. Los optimizadores de consultas son increíblemente inconstantes. Mi sugerencia es seguir adelante hasta que puedas identificar con certeza que una unión en particular es problemática.

Answer 2

LEFT JOIN está devolviendo más filas que INNER JOIN porque estos 2 son diferentes.
Si LEFT JOIN no encuentra la entrada relacionada en la tabla que está buscando, devolverá valores NULL para la tabla.
Pero si INNER JOIN no encuentra la entrada relacionada, no devolverá toda la fila.

Pero para su pregunta, ¿tiene query_cache habilitado? Intenta ejecutar la consulta con

SELECT SQL_NO_CACHE `contacts`.*, ...

Aparte de eso, llenaría las tablas con más datos, corrí

ANALYZE TABLE t1, t2;
OPTIMIZE TABLE t1, t2;

Y mira lo que pasa.

Answer 3

La cardinalidad de la tabla influye en el optimizador de consultas. Supongo que las tablas pequeñas que tiene hacen que la unión interna sea una operación más compleja. Tan pronto como tenga más registros de los que el servidor de bases de datos desea guardar en la memoria, la combinación interna probablemente superará a la combinación de la izquierda.

Answer 4

Probablemente se deba a que INNER JOIN tiene que verificar cada fila en ambas tablas para ver si los valores de columna (correo electrónico en su caso) coinciden. LEFT JOIN devolverá todo de una tabla independientemente. Si está indexado, sabrá qué hacer más rápido también.

Answer 5

Si crees que la implementación de LEFT JOIN es INNER JOIN + más trabajo, entonces este resultado es confuso. ¿Qué ocurre si la implementación de INNER JOIN es (LEFT JOIN + filtering)? Ah, está claro ahora.

En los planes de consulta, la única diferencia es esta: usuarios ... extra: using where . Esto significa filtrar. Hay un paso de filtrado adicional en la consulta con la unión interna.

Este es un tipo diferente de filtrado que el que normalmente se usa en una cláusula where. Es simple crear un índice en A para admitir esta acción de filtrado.

SELECT *
FROM A
WHERE A.ID = 3

Considera esta consulta:

SELECT *
FROM A
  LEFT JOIN B
  ON A.ID = B.ID
WHERE B.ID is not null

Esta consulta es equivalente a la unión interna. No hay índice en B que ayude a esa acción de filtrado. La razón es que la cláusula where indica una condición en el resultado de la unión, en lugar de una condición en B.

Answer 6

Prueba esto:

SELECT `contacts`.*, `lists`.`name` AS `group`, `lists`.`id` AS `group_id`, `lists`.`shared_yn`, `tags`.`name` AS `context`, `tags`.`id` AS `context_id`, `tags`.`color` AS `context_color`, `users`.`id` AS `user_id`, `users`.`avatar` 
FROM `contacts`  
INNER JOIN `users` ON contacts.user_id=''1'' AND users.email=contacts.email
LEFT JOIN `lists` ON lists.id=contacts.list_id  
LEFT JOIN `lists_to_users` ON lists_to_users.user_id=''1'' AND lists_to_users.creator=''1'' AND lists_to_users.list_id=lists.id
LEFT JOIN `tags` ON tags.id=lists_to_users.tag_id 
ORDER BY `contacts`.`name` ASC

Eso debería darte un rendimiento extra porque:

• Coloca todas las uniones internas antes de que aparezca cualquier unión "izquierda" o "derecha". Esto filtra algunos registros antes de aplicar las siguientes combinaciones externas
• El cortocircuito de los operadores "Y" (el orden de los asuntos "Y"). Si la comparación entre las columnas y los literales es falsa, no ejecutará el escaneo de tabla requerido para la comparación entre las tablas PK y FK

Si no encuentra ninguna mejora en el rendimiento, reemplace todo el conjunto de columnas por un "COUNT (*)" y realice las pruebas izquierda / interna. De esta forma, independientemente de la consulta, recuperará solo 1 fila individual con 1 columna individual (el recuento), por lo que puede descartar que el número de bytes devueltos sea la causa de la lentitud de su consulta:

SELECT COUNT(*)
FROM `contacts`  
INNER JOIN `users` ON contacts.user_id=''1'' AND users.email=contacts.email
LEFT JOIN `lists` ON lists.id=contacts.list_id  
LEFT JOIN `lists_to_users` ON lists_to_users.user_id=''1'' AND lists_to_users.creator=''1'' AND lists_to_users.list_id=lists.id
LEFT JOIN `tags` ON tags.id=lists_to_users.tag_id 

Buena suerte