MYSQL OPTIMIZAR CONSULTAS USANDO TABLAS TEMPORALES EN MEMORIA.

Las tablas temporales en memoria en MYSQL nos permiten optimizar el acceso a los datos en nuestros servidores mysql al poder crear una versión en memoria de un bloque de información almacenado en una tabla física. Esta característica nos permite reducir el impacto que puede causar el uso repetitivo de consultas que obtienen el mismo conjunto de valores. Para ejemplificar su utilidad imaginemos el siguiente escenario:

De una tabla de histórico de ventas que almacena 10 millones de registros nos piden obtener para un periodo determinado una tabla con los siguientes datos:

Clientes atendidos en el periodo: 2,000
Volumén de ventas: 3,000
Importe de ventas: 9,000
Ventas canceladas: 1,000

 

Una primera solución sería unir distintas consultas para obtener la información de esta forma:

Select Clientes atendidos en el periodo concepto,
count(distinct client_id) valor
from ventas
where fecha >= periodoinicial and fecha <= periodofinal
union
Select Volumén de ventas concepto,
count(distinct articulo_id) valor
from ventas
where fecha >= periodoinicial and fecha <= periodofinal
union
Select Importe de ventas concepto,
sum(importe_factura) valor
from ventas
where fecha >= periodoinicial and fecha <= periodofinal
union
Select Centas canceladas concepto,
count(distinct articulo_id) valor
from ventas
where fecha >= periodoinicial and fecha <= periodofinal
and estatus = Cancelada;

La desventaja al usar esta solución es que cada consulta se ejecutará sobre los 10 millones de registros contenidos en la tabla de ventas impactando con ello el rendimiento de nuestro servidor. Es para estos casos que podemos hacer uso de tablas en memoria.

Para dar solución al requerimiento inicial usando el enfoque de tablas en memoria podemos realizar lo siguiente:

-- Creamos una tabla temporal con el resultado de la consulta.
CREATE TEMPORARY TABLE tmp_vtas AS
  (SELECT client_id, articulo_id, estatus
    FROM ventas
   WHERE fecha >= periodoinicial
     AND fecha <= periodofinal)
-- Crea la tabla temporal destino
CREATE TEMPORARY TABLE rep_vtas (Concepto varchar(50), valor numeric(6,2));
-- Genera los datos a reportar
insert into rep_vtas
Select Clientes atendidos en el periodo concepto,
           count(distinct client_id) valor
from tmp_vtas;
insert into rep_vtas
Select Volumén de ventas concepto,
           count(distinct articulo_id)  valor
from tmp_vtas;
insert into rep_vtas
Select Importe de ventas concepto,
           sum(importe_factura)  valor
from tmp_vtas;
insert into rep_vtas
Select Centas canceladas concepto,
           count(distinct articulo_id)  valor
from tmp_vtas
where estatus = Cancelada;
-- devuelve el resultado
select * from rep_vtas;

Como podemos observar el volumen de valores que se evalúan en cada select es menor a los que son evaluados cuando realizamos la selección sobre la tabla de ventas con 10 millones de registros, además de que solo obtenemos los campos que requerimos para elaborar nuestras consultas reduciendo con ello el impacto en nuestro servidor.

Si bien el uso de tablas temporales en memoria no serán la solución para todos los casos de optimización de rendimiento, si son una característica que puede ayudarnos a optimizar nuestro servidor de base  de datos.

 

Por último, las características de estas tablas temporales son las siguientes:

  • Solo existen mientras la sesión en que fueron creadas se mantiene activa. Una vez cerrada la conexión estas tablas son eliminadas de forma automática.
  • Si la tabla es muy grande dejará de residir en memoria y pasará a ser almacenada en disco.
  • Los límites de tamaño están definidos por las opciones de configuración: tmp_table_size y max_heap_table_size.
  • No se puede usar más de una vez en una sentencia SQL.

Si deseas saber más de esto puedes revisar la documentación en línea: http://dev.mysql.com/doc/refman/5.7/en/create-table.html

 

Sé el primero en comentar

Deja un comentario