systemadmin.es > DBA > JOIN y STRAIGHT_JOIN en MySQL

JOIN y STRAIGHT_JOIN en MySQL

No es muy común ver que el optimizador del MySQL se equivoque estrepitosamente de plan de ejecución, pero cuando lo hace los resultados pueden ser desastrosos.

Por ejemplo, para una JOIN como la siguiente:

SELECT ul.id, ul.name, s.body
FROM mails as s JOIN users as ul ON s.u_id=ul.id 
WHERE s.domain_id = 25 
ORDER BY mail_id desc 
LIMIT 50;

Podemos ver que nos da el siguiente plan de ejecución:

+----+-------------+-------+------+----------------+---------+---------+--------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys  | key     | key_len | ref                            | rows   | Extra                           |
+----+-------------+-------+------+----------------+---------+---------+--------------------------------+--------+---------------------------------+
|  1 | SIMPLE      | ul    | ALL  | PRIMARY        | NULL    | NULL    | NULL                           | 159641 | Using temporary; Using filesort |
|  1 | SIMPLE      | s     | ref  | IDK_USR,IDK_ID | IDK_USR | 14      | const,xxxxxxxxxxxxx.ul.user_id |      3 | Using where                     |
+----+-------------+-------+------+----------------+---------+---------+--------------------------------+--------+---------------------------------+

Dichas tablas son de unos tamaños ya considerables, por lo que estaba provocando problemas:

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|   159055 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from mails;
+----------+
| count(*) |
+----------+
|  1530821 |
+----------+
1 row in set (3.97 sec)

Como primera opción, con un ANALYZE de la tabla mails se solucionó:

ANALYZE TABLE mails;

Esto corrigió el plan de ejecución:

+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type   | possible_keys  | key     | key_len | ref                     | rows | Extra       |
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | s     | index  | IDK_USR,IDK_ID | PRIMARY | 4       | NULL                    |  121 | Using where |
|  1 | SIMPLE      | ul    | eq_ref | PRIMARY        | PRIMARY | 8       | xxxxxxxxxxxxx.s.user_id |    1 | Using where |
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
2 rows in set (0.00 sec)

Pero al poco volvió modificarse para volver a examinar toda la tabla de usuarios. Para forzar que la join se haga en el orden correcto podemos usar STRAIGHT_JOIN en lugar de JOIN. De esta manera indicamos que la tabla de la izquierda siempre se lea primero que la tabla de la derecha. La query quedaría:

SELECT ul.id, ul.name, s.body
FROM mails as s STRAIGHT_JOIN users as ul ON s.u_id=ul.id 
WHERE s.domain_id = 25 
ORDER BY mail_id desc 
LIMIT 50;

Otra opción es mediante un FORCE INDEX. Evidentemente estas opciones pueden llegar a ser perjudiciales si se utilizan mal o si algún día las tablas varían de tal forma que pasa a perjudicar al plan de ejecución.

4 comments to “JOIN y STRAIGHT_JOIN en MySQL”

  1. Curioso…

    ¿ Cuál es la clave primaria de la tabla mails ?

    Se leen las columnas `body`, `u_id`, `mail_id`, y `domain_id` de la tabla mails y MySQL lo lee todo de la clave primaria (type = index, key = PRIMARY). Me he encontrado en alguna ocasión con que el optimizador no funcionaba bien con índices muy grandes, o cuando el orden del índice no es el adecuado.

    Es sorprendente que MySQL prefiera escanear completamente una tabla, antes de usar un índice (aunque sea poco óptimo).

    Sólo por curiosidad, con el siguiente índice ¿Mejora la situación?

    ALTER TABLE mails ADD INDEX `mail_info`(domain_id, u_id, mail_id, body);

    ¿?

  2. Este índice ya esta creado sin el body, ¿porque crees necesario incluirlo?

  3. Ooops, confundí “type: index” con “extra: using index”

    Si todas las columnas que hay que leer de una tabla están en un índice, MySQL las lee del índice (“extra: using index”). Si el índice está en memoria, MySQL se ahorra un acceso a disco.

    Cuando no tenemos problemas de disco ni memoria, es una forma de optimizar con resultados inmediatos. Aunque en este caso, imagino que la columna “body” será muy grande y es posible que el índice no se aproveche.

    Por otro lado, “type: index” significa que escanea todo el índice, que es ligeramente más óptimo que escanear la tabla entera, es decir, poco optimo.

    Si está escaneando todo el índice es porque la cardinalidad del índice no es buena, y/o estas ordenando por él.

    Lo lógico (una vez forzado el orden del JOIN) es que filtrara por domain_id, pero en su lugar está usando la clave primaria mail_id… ¡¡está “aplicando” el “order by mail_id” antes del where!!

    Mi apuesta (con la info que hay), es que el “ORDER BY mail_id LIMIT 50” influye para que forzando el orden la consulta sea más rápido. Pero, esto no es del todo optimo porque si no MySQL elegiría esta estrategia.

    Si es más optimo ordenar primero por mail_id, entonces el índice debería ser:
    (mail_id, domain_id) o (mail_id, domain_id, u_id, body), eso es, las columnas en el orden en el que se van a leer.

    O, forzar a que use domain_id antes, cambiando a “ORDER BY domain_id, mail_id”.

    Si esto siguiera sin funcionar, igual serviría particionar por domain_id, o, no se, cuando es un caso tan particular es difícil saberlo.

    Este es un caso muy curioso, en estas situaciones es todo un reto saber qué está pasando internamente en el optimizador. Por experiencia, si con FORCE INDEX y STRAIGHT_JOIN mejoramos el rendimiento, es posible que exista una solución todavía más optima.

    PD: ¿Algo de esto mejora la ejecución?

  4. $sql1=”SELECT id_op, numero_op, mes, beneficiario, importe FROM (SELECT * FROM ordenes_pago LEFT JOIN comprobacion USING ( id_op ) GROUP BY id_op ) AS p
    WHERE mes_aux=’$mes’ order by numero_op “;

    tengo 2 tablas de las cuales la primera contiene informacion de ordenes de pago, la segunda contiene cierta informacion de la primera tabla, es decir, ordenes de pago validadas, lo que pretendo hacer es mostrar las las ordenes de pago de la primera tabla sin mostrar la informacion de la segunda tabla

Deja un comentario:

XHTML - Tags permitidos:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>