segundo - optimizar consultas lentas mysql

¿En qué orden se evalúan las JOINs de MySQL?

Question

Tengo la siguiente consulta:

SELECT c.*
FROM companies AS c
JOIN users AS u USING(companyid)
JOIN jobs AS j USING(userid)
JOIN useraccounts AS us USING(userid)
WHERE j.jobid = 123;

Tengo las siguientes preguntas:

1. ¿Es la sintaxis de USING sinónimo de sintaxis ON?
2. ¿Estas uniones se evalúan de izquierda a derecha? En otras palabras, ¿esta consulta dice: x = empresas ÚNASE a usuarios; y = x UNIR trabajos; z = y ÚNETE userac contadores;
3. Si la respuesta a la pregunta 2 es sí, ¿es seguro suponer que la tabla de empresas tiene columnas companyid, userid y jobid?
4. No entiendo cómo la cláusula WHERE se puede utilizar para seleccionar filas en la tabla de empresas cuando se refiere al alias "j"

¡Cualquier ayuda sería apreciada!

Answer 1

1. USING (fieldname) es una forma abreviada de decir ON table1.fieldname = table2.fieldname.

2. SQL no define el ''orden'' en el que se realizan las INSCRIPCIONES porque no es la naturaleza del idioma. Obviamente, se debe especificar un orden en la declaración, pero una UNIÓN INTERNA puede considerarse conmutativa: puede enumerarlos en cualquier orden y obtendrá los mismos resultados.

   Dicho esto, al construir un SELECT ... JOIN, particularmente uno que incluye LEFT JOINs, he encontrado que tiene sentido considerar el tercer JOIN como unirse a la nueva tabla con los resultados del primer JOIN, el cuarto JOIN como unirse al resultados de la segunda JOIN, y así sucesivamente.

   Más raramente, el orden especificado puede influir en el comportamiento del optimizador de consultas, debido a la forma en que influye en la heurística.

3. No. La forma en que se compila la consulta requiere que las empresas y los usuarios tengan una compañía, jobs tiene un ID de usuario y un jobid y useraccount tiene un ID de usuario. Sin embargo, solo una de las empresas o usuarios necesita un ID de usuario para que JOIN funcione.

4. La cláusula WHERE está filtrando el resultado completo, es decir, todas las columnas JOINed, utilizando una columna proporcionada por la tabla de trabajos.

Answer 2

1) Usar no es exactamente lo mismo que en, pero es mano corta donde ambas tablas tienen una columna con el mismo nombre al que se está uniendo ... vea: website

En mi opinión, es más difícil de leer, así que detallaré las uniones.

3) No está claro a partir de esta consulta, pero supongo que no.

2) Suponiendo que se está uniendo a través de las otras tablas (no todas directamente en las empresas), el orden en esta consulta sí importa ... ver las comparaciones a continuación:

Origional:

SELECT c.* 
    FROM companies AS c 
    JOIN users AS u USING(companyid) 
    JOIN jobs AS j USING(userid) 
    JOIN useraccounts AS us USING(userid) 
WHERE j.jobid = 123

Lo que creo que es probable que sugiera:

SELECT c.* 
    FROM companies AS c 
    JOIN users AS u on u.companyid = c.companyid
    JOIN jobs AS j on j.userid = u.userid
    JOIN useraccounts AS us on us.userid = u.userid 
WHERE j.jobid = 123

Puede cambiar las líneas que unen trabajos y cuentas de usuarios aquí.

Cómo se vería si todo se uniera a la compañía:

SELECT c.* 
    FROM companies AS c 
    JOIN users AS u on u.companyid = c.companyid
    JOIN jobs AS j on j.userid = c.userid
    JOIN useraccounts AS us on us.userid = c.userid
WHERE j.jobid = 123

Esto realmente no tiene sentido lógico ... a menos que cada usuario tenga su propia compañía.

4.) La magia de sql es que solo puedes mostrar ciertas columnas, pero todas son suyas para clasificar y filtrar ...

si regresaste

SELECT c.*, j.jobid....  

se podía ver claramente en qué se estaba filtrando, pero al servidor de la base de datos no le importa si publica una fila o no para filtrar.

Answer 3

Aquí hay una respuesta más detallada sobre la precedencia JOIN . En tu caso, los JOIN s son todos conmutativos. Probemos uno donde no lo sean.

Esquema de compilación:

CREATE TABLE users (
  name text
);

CREATE TABLE orders (
  order_id text,
  user_name text
);

CREATE TABLE shipments (
  order_id text,
  fulfiller text
);

Agregar datos:

INSERT INTO users VALUES (''Bob''), (''Mary'');

INSERT INTO orders VALUES (''order1'', ''Bob'');

INSERT INTO shipments VALUES (''order1'', ''Fulfilling Mary'');

Ejecutar consulta:

SELECT *
  FROM users
       LEFT OUTER JOIN orders
       ON orders.user_name = users.name
       JOIN shipments
       ON shipments.order_id = orders.order_id

Resultado:

Solo se devuelve la fila Bob

Análisis:

En esta consulta, se evaluó primero la LEFT OUTER JOIN se evaluó la JOIN en el resultado compuesto de la LEFT OUTER JOIN .

Segunda consulta:

SELECT *
  FROM users
       LEFT OUTER JOIN (
         orders
         JOIN shipments
         ON shipments.order_id = orders.order_id)
         ON orders.user_name = users.name

Resultado:

Una fila para Bob (con los datos de cumplimiento) y una fila para Mary con NULL para datos de cumplimiento.

Análisis:

El paréntesis cambió el orden de evaluación.

Más documentación de MySQL está en dev.mysql.com/doc/refman/5.5/en/nested-join-optimization.html

Answer 4

En MySQL, a menudo es interesante preguntar al optimizador de consultas qué planea hacer, con:

EXPLAIN SELECT [...]

Consulte "7.2.1 Optimización de consultas con EXPLAIN"

Answer 5

No estoy seguro acerca de la parte ON versus USING (aunque este website dice que son iguales)

En cuanto a la pregunta de pedido, es totalmente de implementación (y probablemente consulta) específica. Es muy probable que MYSQL elija un pedido al compilar la solicitud. Si desea hacer cumplir un pedido en particular, debe ''anular'' sus consultas:

SELECT c.*
FROM companies AS c 
    JOIN (SELECT * FROM users AS u 
        JOIN (SELECT * FROM  jobs AS j USING(userid) 
              JOIN useraccounts AS us USING(userid) 
              WHERE j.jobid = 123)
    )

como para la parte 4: la cláusula where limita qué filas de la tabla de trabajos son elegibles para ser UNIFICADAS. Por lo tanto, si hay filas que se unirían debido a los ID de usuario coincidentes pero no tienen el ID de trabajo correcto, se omitirán.

Answer 6

No puedo responder el bit sobre la sintaxis de USO. Eso es raro. Nunca lo había visto antes, siempre había usado una cláusula ON.

Pero lo que puedo decir es que el orden de las operaciones JOIN está determinado dinámicamente por el optimizador de consultas cuando construye su plan de consulta, basado en un sistema de heurística de optimización, algunos de los cuales son:

1. ¿El JOIN se realiza en un campo de clave principal? Si es así, esto tiene alta prioridad en el plan de consulta.

2. ¿El JOIN se realiza en un campo de clave externa? Esto también tiene una alta prioridad.

3. ¿Existe un índice en el campo unido? Si es así, topa con la prioridad.

4. ¿Se realiza una operación JOIN en un campo en la cláusula WHERE? ¿Se puede evaluar la expresión de la cláusula WHERE examinando el índice (en lugar de realizar un escaneo de tabla)? Esta es una gran oportunidad de optimización, por lo que obtiene un gran bache de prioridad.

5. ¿Cuál es la cardinalidad de la columna unida? Las columnas con alta cardinalidad brindan al optimizador más oportunidades para discriminar contra coincidencias falsas (aquellas que no satisfacen la cláusula WHERE o la cláusula ON), por lo que las uniones de alta cardinalidad generalmente se procesan antes de que se unan las uniones de baja cardinalidad.

6. ¿Cuántas filas reales hay en la tabla unida? Unirse contra una tabla con solo 100 valores va a crear menos explosión de datos que unirse contra una tabla con diez millones de filas.

De todos modos ... el punto es ... hay MUCHAS variables que entran en el plan de ejecución de la consulta. Si desea ver cómo MySQL optimiza sus consultas, use la sintaxis EXPLAIN.

Y aquí hay un buen artículo para leer:

http://www.informit.com/articles/article.aspx?p=377652

EN EDITAR:

Para responder a su 4ta pregunta: No está consultando la tabla de "empresas". Está consultando el producto cruzado unido de TODAS las cuatro tablas en sus cláusulas FROM y USING.

El alias "j.jobid" es solo el nombre completo de una de las columnas de esa colección de tablas unidas.

Answer 7

VEA http://dev.mysql.com/doc/refman/5.0/en/join.html

Y comienza a leer aquí:

Unirse a los cambios de procesamiento en MySQL 5.0.12

A partir de MySQL 5.0.12, las uniones naturales y las uniones con USING, incluidas las variantes de unión externa, se procesan según el estándar SQL: 2003. El objetivo era alinear la sintaxis y la semántica de MySQL con respecto a NATURAL JOIN y JOIN ... USING según SQL: 2003. Sin embargo, estos cambios en el procesamiento de combinación pueden dar como resultado columnas de salida diferentes para algunas combinaciones. Además, algunas consultas que parecían funcionar correctamente en versiones anteriores deben reescribirse para cumplir con el estándar.

Estos cambios tienen cinco aspectos principales:

• La forma en que MySQL determina las columnas de resultados de NATURAL o USING join operations (y por lo tanto el resultado de la cláusula FROM completa).

• Expansión de SELECT * y SELECT tbl_name. * En una lista de columnas seleccionadas.

• Resolución de nombres de columna en NATURAL o UTILIZACIÓN de uniones.

• La transformación de NATURAL o USING se une a JOIN ... ON.

• Resolución de nombres de columnas en la condición ON de JOIN ... ON.