Algunas semanas atrás en el trabajo nos topamos con un problema, realizábamos una consulta que consistía en hacer un join entre una tabla muy grande contra un par de tablas pequeñas que contenían únicamente descripciones. Como ustedes ha de imaginarse teníamos nuestra base de datos normalizada y para exportar los datos con el conjunto de descripciones era necesario realizar este cruce.
Para que la consulta fuese bien (que no arrojase un timeout en el mejor de los casos) habíamos agregado índices a las tablas secundarias (aquellas que contenían las descripciones) y la consulta de lograr ejecutarse llegó a tardar unos 25 segundos. Estas tablas eran de tipo InnoDB y los índices eran de tipo B-Tree (es el único tipo de índice para las tablas de tipo InnoDB). La consulta llegaba a tardar en el mejor de los casos (que no devolviese un timeout) 25 segundos. Es por esta razón que buscando una alternativa abordamos las tablas en memoria.
Las tablas en memoria son almacenadas en la memoria HEAP razón por la cual son tablas temporales ya que una vez se haya reiniciado o apagado la máquina estas perderán los datos, es decir se mantiene la estructura de la tabla mas no su contenido. Las tablas en memoria en MySQL pueden tener 2 tipos de índices B-Tree o Hash.
¿Cuando utilizar cada tipo de índice?
El índice B-Tree puede utilizarse con operadores del tipo =, >, >=, <, <= o BETWEEN, de igual manera puede ser usado con el operador LIKE, mientras que el índice Hash es usado únicamente para comparaciones de igualdad como =, >= o <=. Así que de realizar comparaciones de igualdad es recomendable utilizar el índice Hash ya que es muy rápido mientras que cuando se realicen comparaciones del tipo >, <, LIKE, BETWEEN u optimizar operaciones de ORDER BY la opción es utilizar B-Tree.
En nuestro caso únicamente realizábamos operaciones de igualdad ya que comparábamos en base a un código así que utilizamos índices de tipo Hash, obteniendo excelentes resultados, tanto es así que nunca llegamos a obtener un timeout y el tiempo de respuesta de nuestras consultas se redujeron a 5 segundos.
¿Cómo crear una tabla en memoria?
La creación de una tabla en memoria es muy sencilla, es igual a la creación de cualquier otra tabla del tipo InnoDB, lo único es sencillamente cambiar el tipo de “engine” e indicar “MEMORY”, por ejemplo:
|
1 2 3 4 5 |
CREATE TABLE `PRODUCTO_MEM` ( `CODIGO` int(11) NOT NULL, `DESCRIPCION` varchar(45) NOT NULL, KEY `index1` (`CODIGO`) USING HASH ) ENGINE=MEMORY DEFAULT CHARSET=latin1; |
Por último ¿Como hacer para que se carguen los datos de la tabla en memoria al arrancar?
Por muy tonto que parezca para nosotros esto era importante ya que nos asegurábamos de que de reiniciarse el ordenador (era windows :-s ) estando o no nosotros, las tablas en memoria estarían creadas y cualquier consulta que se efectuase no daría error. Para que la tabla en memoria se llene de datos al arrancar el servidor MySQL es bastante sencillo, lo único que debemos hacer es editar el fichero my.ini o my.cnf y debajo de donde dice [mysqld] agregar la siguiente linea
|
1 2 3 4 5 6 7 |
... [mysqld] init-file="/Users/my_home/script.sql" ... |
Donde script.sql tendrá las sentencias sql de inserción por ejemplo
|
1 2 |
INSERT INTO `my_db`.`PRODUCTO_MEM` (`CODIGO`,`DESCRIPCION`) VALUES (1, 'descripcion del producto 1'); INSERT INTO `my_db`.`PRODUCTO_MEM` (`CODIGO`,`DESCRIPCION`) VALUES (2, 'descripcion del producto 2'); |
Espero que esto les pueda servir para optimizar el consultas en base de datos o sencillamente resolver cualquier inconveniente.
¿Has trabajado antes con tablas en memoria? Compartirías con nosotros tu experiencia.