Mostrar motores de almacenamiento MySQL/MariaDB

Un motor de almacenamiento es un módulo de software que utiliza un sistema de administración de bases de datos para crear, leer y actualizar datos de una base de datos. Hay dos tipos de motores de almacenamiento en MySQL: transaccionales y no transaccionales. Elegir el motor de almacenamiento adecuado es una decisión importante que tendrá un impacto en el desarrollo futuro. Para caso practico usaremos motores de almacenamiento como MyISAM, InnoDB, Memory y CSV. Porque selecciono estos cuatro motores, porque son los que verán la mayoría cuando se enfrenten a un MySQL.

Breves Descripción sobre los Engines

InnoDB, es el motor de almacenamiento más utilizado con soporte para transacciones. Es un motor de almacenamiento compatible con ACID (acrónimo de Atomicity, Consistency, Isolation and Durability). Admite bloqueo de nivel de fila, recuperación de fallos y control de simultaneidad de múltiples versiones. Es el único motor que proporciona una restricción de integridad referencial de clave externa. Oracle recomienda usar InnoDB para tablas, excepto para casos de uso especializados.

MyISAM, es el motor de almacenamiento original. Es un motor de almacenamiento rápido. No admite transacciones, proporciona bloqueo a nivel de tabla. Se utiliza principalmente en Web y almacenamiento de datos.

Memory, es el motor que crea tablas en la memoria. Usa índices hash por defecto, lo que las hace muy rápidas de recorrer y muy útiles para crear tablas temporales. Sin embargo, cuando el servidor se apaga estas se borran. La definición de las tablas se guarda en ficheros con extensión.frm, de modo que al reiniciar el servidor las tablas estarán definidas pero vacías de datos. Es ideal para crear tablas temporales o búsquedas rápidas. Los datos se pierden cuando se reinicia la base de datos.

CSV almacena datos en archivos CSV. Proporciona una gran flexibilidad porque los datos en este formato se integran fácilmente en otras aplicaciones. Cuando se crea una tabla el servidor, crea un fichero con el formato de la tabla en el directorio de datos con extensión frm, ademas de un fichero para contener los datos con extensión CSV en el que se guardan los datos de cada registro en una línea ASCII y separados por comas.

A lo que se vino 😉

Una vez conectados a un servidor MySQL podemos ver los motores de almacenamiento si se encuentran disponibles/habilitados, con un show engines

$ mysql -u root -p -h nombre_servidor 
mysql> SHOW ENGINES\G
************* 1. row *************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

************* 2. row *************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO

************* 3. row *************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO

************* 4. row *************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL

************* 5. row *************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

************* 6. row *************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

************* 7. row *************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES

************* 8. row *************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO

************* 9. row *************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

Para ver los motores de almacenamiento de determinadas tablas, ejecute la siguiente declaración

mysql> SELECT TABLE_NAME, ENGINE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_NAME IN ('TABLA1', 'TABLA2', 'TABLA13');

Si desea ver todas las tablas de una base de datos, ejecute

mysql> SELECT TABLE_NAME, ENGINE
FROM   INFORMATION_SCHEMA.TABLES
WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA='Data_Base';

Consideraciones para elegir un motor adecuado

Ningún motor de almacenamiento es ideal para todas las circunstancias. Algunos funcionan mejor en determinadas condiciones y peor en otras situaciones. Una solución más segura requiere más recursos; pero podría ser más lento, requerir más tiempo de CPU y espacio en disco. MySQL es muy flexible en el hecho de que proporciona varios motores de almacenamiento diferentes. Algunos de ellos, como el motor de archivo, se crean para usarse en situaciones específicas.

En algunos casos, la respuesta es clara. Siempre que estemos tratando con algunos sistemas de pago, estamos obligados a utilizar la solución más segura. No podemos permitirnos perder datos tan sensibles. InnoDB es el camino a seguir. Si queremos una búsqueda de texto completo, entonces podemos elegir entre MyISAM o InnoDB. Sólo InnoDB admite la restricción de integridad referencial de clave externa y si planeamos usar esta restricción, entonces la elección es clara.

Así que con esto, cierro la entrada. Espero les sea de utilidad, hasta otro post lectores. Buenas vibras, Happy Hacking!