Máquinas de almacenamiento en Mysql

Tal vez más conocidas como ENGINES, se encargan del trabajo más pesado en el servidor mysql de Hup.me, que es decidir y ejecutar como se almacenan y como se recuperan los datos. Por esta razón, conocer sus características en cada caso en particular puede darnos muchas ventajas y posibilidades. Esta es una cuestión que debemos plantearnos durante el diseño de nuestra base de datos.

La respuesta: “Innodb es superior a MyISAM porque bla, bla…” no nos convence y somos partidarios de conocer cuales son las ventajas de cada una en particular y también de darle un repaso a todas sus posibilidades. Empezaremos por saber que engines tenemos disponibles.

Para ello ejecutamos:
mysql> SHOW ENGINES;

Para un servidor “estandar” de la serie 5.0 esta bien pudiera ser la respuesta:

Tendremos disponibles aquellas engines para las cuales support indica YES o DEFAULT. Es decir, en nuestro caso: MyISAM, Memory, InnoDB, Archive, CSV, Federated y Mrg_MyISAM.

Para saber que engine está usando una tabla en concreto debemos ejecutar:
mysql> SHOW TABLE STATUS LIKE ‘<nombre_tabla>’;

Hagamos un rápido resumen:
MyISAM

Motor por defecto, ofrece un buen compromiso entre rendimiento y características, como full-indexing, compresión y funciones espaciales (GIS). MyISAM no soporta transacciones ni bloqueos a nivel de fila.

Merge MyISAM (MRG_MYISAM)

Es una variación de MyISAM. Una tabla de este tipo es la combinación de varias tablas MyISAM en una sola tabla virtual. Esto es particularmente útil en tablas de registro (logs) y almacenamiento de datos de una aplicación.

InnoDB

Diseñada para añadir transaccionalidad, especificamente, para el procesamiento de muchas pequeñas (en tiempo) transacciones que suelen completarse en lugar de anularse. Su rendimiento y su capacidad para auto-recuperarse de una cuelgue lo hacen también muy popular en entornos no transaccionales.

Adquiere gran concurrencia de peticiones a través del uso de rutinas MVCC y tiene implementado los cuatro niveles de aislamiento del ANSI SQL (READ UNCOMMITED, READ COMMITED, REPEATABLE READ, SERIALIZABLE)

MEMORY

Tablas en memoria, también llamadas tablas HEAP, son muy útiles para accesos muy rápidos que o bien nunca cambian o no es necesario que persistan después de una caída del sistema. Son alrededor de un orden de magnitud más rápidas que las tablas MyISAM. Soportan índices hash. Emplean bloqueo a nivel de tabla y no soportan los tipos BLOB o TEXT. Además solo soportan filas de tamaño fijo de manera que almacenan los VARCHARs como CHARs (con el consiguiente malgasto de memoria).

MySQL emplea internamente este tipo de tablas en operaciones intermedias. Si estas operaciones precisan o tienen un campo TEXT o BLOB se convertirán en tablas tipo MyISAM.

ARCHIVE

Este tipo sólo soporta peticiones INSERT y SELECT. No soporta índices. Provoca mucha menos actividad de disco que MyISAM, porque su buffer de datos escribe y comprime cada fila con zlib cuando es un dato es insertado.

Archive soporta bloqueos a nivel de fila y un buffer especial para inserciones de alta concurrencia. Archive no es transaccional es simplemente una engine optimizada para inserciones de datos comprimidos.

CVS

Esta engine puede tratar ficheros con valores separados por comas (CSV) como tablas, pero no soporta índices. Esta engine te permite cargar y descargar ficheros de la base de datos mientras el servidor se mantiene en ejecución.

FEDERATED

Estas engines no almacenan los datos localmente. Cada tabla federated se refiere a otra tabla en un servidor Mysql remoto. De manera que se conecta al servidor remoto cada vez que se quiera hacer una operación sobre esta tabla.

No funciona bien para peticiones agregadas, uniones y otros tipos de operaciones básicas. Pero se suele emplear para hacer algunos “hacks” sobre servidores remotos y trucos durante operaciones de replicación.

Como bien se puede observar las posibilidades de cada engine están presentes y todas estas engines consideradas básicas (en cuanto a su disponibilidad en servidores mysql) han conseguido sobrevivir a tipos de engines desarrolladas con posterioridad debido a las diferentes posibilidades que las caracterizan. En general, podemos sacrificar características por rendimiento; en muchas ocasiones (la regla del 20-80 sigue vigente) aunque algunas necesidades pueden forzarnos a tener que emplear una engine en particular MyISAM para full-indexes, Federated para tablas remotas o InnoDB si precisamos emplear transacciones).

Existen además otras engines disponibles para mysql entre las que podemos nombrar: blackhole, ndb cluster, falcon, solidDB, PBXT o maria (que busca reemplazar a MyISAM) y muchas más creadas por otras terceras compañías.

Como último consejo apuntaré que no es necesariamente una buena idea usar diferentes engines de almacenamiento para diferentes tables. A veces te puede simplificar la vida escoger una sola engine para todas tus tablas.