Problemas con derived tables en MySQL 5.7.

Hola a todos. Hoy queríamos comentar con vosotros unos problemas con derived tables que nos hemos encontrado en Mysql 5.7 (y superior).

GPS Open Source News

Empecemos por poneros en antecedentes por si no conocéis la funcionalidad que se introdujo en MySQL 4.1. En términos muy básicos, una tabla derivada es una tabla virtual que se devuelve desde una instrucción SELECT o como una select en la cláusula from. La estructura es la siguiente:

problemas en derived tables

Este concepto es similar a las tablas temporales, pero el uso de tablas derivadas en las instrucciones SELECT es mucho más simple porque no requieren todos los pasos que las tablas temporales si necesitan.

Ha habido mucho debate sobre si las tablas derivadas, las vistas o las tablas temporales son realmente más rápidas en términos de rendimiento. Los partidarios de las tablas derivadas mencionan que le permiten reemplazar el código requerido para administrar una tabla temporal con un solo comando y que puede ser más rápido que la select que reemplaza. El hecho es que no hay una respuesta simple, ya que diferentes consultas requieren diferentes optimizaciones; cada caso debe verse de forma independiente.

Precisamente con este tipo de tablas es donde hemos visto problemas de degradación de rendimiento cuando se está probando una migración de 5.6 a 5.7 o ya directamente a 8.

¿Qué problemas con derived tables hemos visto?

Hemos visto una serie de queries con una estructura similar a esta:

SELECT 
     cur.rate_id AS ID,
     cur.default_val AS DEFAULT_RATE_VALUE,
     cur.label AS ATTRID_100
     ,COALESCE(cur.units, 0) * COALESCE(cur.exchange_value, 0) AS UNITS
 FROM
     (SELECT 
         a.currency_id,
             a.from_date AS startdate,
             b.from_date AS enddate,
             a.exchange_value
     FROM
         currencies a
     LEFT JOIN currencies b ON (a.currency_id = b.currency_id
         AND a.from_date < b.from_date)
     LEFT JOIN currencies c ON (a.currency_id = c.currency_id
         AND a.from_date < c.from_date
         AND c.from_date < b.from_date)
     WHERE
         a.currency_id = 9
             AND c.currency_item_id IS NULL) cur 
 WHERE cur.startdate <= valor1
         AND (cur.enddate IS NULL
         OR cur.enddate >= valor2)) 
 ORDER BY cur.label; 

Esta query en 5.6 tiene los siguientes tiempos:

Si la probamos en 5.7 vemos:

Como se puede comprobar el tiempo es mucho mayor en 5.7, pero ¿por qué?

Al revisar el plan de ejecución vemos que en 5.6 se realiza una materialización de la subselect mediante una tabla temporal:

Problemas con derived tables. Plan de ejecucion

Esta parte se resuelve de forma muy distinta en 5.7. Realiza un nested loop y pasa de revisar 38k a 73 millones de filas, lo que explica el tiempo que tarda en resolverse:

Esto degrada la query y hace que los tiempos sean tan malos.

Sospechando que es un problema de derived tables hacemos una prueba para desactivar esta optimización a nivel de sesión:

SET SESSION optimizer_switch='derived_merge=off';

Con este simple cambio vemos que los tiempos en 5.7 son muy similares a 5.6:

Si se tiene un número elevado de consultas problemáticas de este tipo se puede pensar en desactivar la funcionalidad a nivel de gestor poniendo:

SET GLOBAL optimizer_switch='derived_merge=off';

En consultas en las que es necesario que este subconjunto de datos se devuelva agrupado su funcionamiento es bastante óptimo, pero en el ejemplo que os hemos puesto se ha visto que funciona con un rendimiento muy pobre.

Como conclusión podemos decir que esta funcionalidad es muy potente y en el 90% de los casos mejorará el rendimiento de nuestras consultas, pero tenemos que tener cuidado con ese otro 10% ya que puede penalizar enormemente el rendimiento de nuestro aplicativo. Si estáis pensando en utilizar esta forma de programar queries tened cuidado por si acaso :-D.

Un saludo y nos vemos en próximas entradas. No olvides apuntarte a nuestra newsletter para ver todas las entradas de MySQL. Con un sólo email al mes, estarás informado. Si quieres migrar tu entorno a una versión superior de MySQL o están teniendo problemas con la misma. Contacta con nosotros, ¡¡¡podemos ayudarte!!!