Última modificación: 10 de julio de 2020
En este capítulo, hablaremos de los motores de almacenamiento de MySQL.
Un motor de almacenamiento es un módulo de software que un sistema de gestión de bases de datos utiliza para crear, leer, actualizar datos de una base de datos. Hay dos tipos de motores de almacenamiento en MySQL: transaccional y no transaccional.
Para MySQL 5.5 y posteriores, el motor de almacenamiento por defecto es InnoDB.El motor de almacenamiento por defecto para MySQL antes de la versión5.5 era MyISAM. La elección del motor de almacenamiento correcto es una decisión estratégica importante, que tendrá un impacto en el desarrollo futuro. En este tutorial, utilizaremos los motores de almacenamiento MyISAM, InnoDB, Memory yCSV. Si usted es nuevo en MySQL y está estudiando el sistema de gestión de bases de datos MySQL, entonces esto no es una gran preocupación. Si usted está planeando una base de datos de producción, thenthings se vuelven más complicados.
Lista de motores de almacenamiento
MySQL soporta motores de almacenamiento:
- InnoDB
- MyISAM
- Memory
- CSV
- Merge
- Archive
- Federated
- Blackhole
- Example
InnoDB es el motor de almacenamiento más utilizado con soporte de transacciones. Es un motor de almacenamiento compatible con ACID. Admite el bloqueo a nivel de fila, la recuperación de fallos y el control de concurrencia multiversión. Es el único motor que proporciona una restricción de integridad referencial de clave extranjera. Oracle recomienda utilizar InnoDB para las tablas, excepto en casos de uso especializado.
MyISAM es el motor de almacenamiento original. Es un motor de almacenamiento rápido y no soporta transacciones. MyISAM proporciona table-levellocking. Se utiliza sobre todo en la Web y el almacenamiento de datos.
El motor de almacenamiento en memoria crea tablas en la memoria. Es el motor más rápido. Proporciona bloqueo a nivel de tabla. No admite transacciones.El motor de almacenamiento en memoria es ideal para crear tablas temporales o búsquedas rápidas.Los datos se pierden cuando se reinicia la base de datos.
CSV almacena los datos en archivos CSV. Proporciona una gran flexibilidad porque los datos en este formato se integran fácilmente en otras aplicaciones.
Merge opera sobre tablas MyISAM subyacentes. Las tablas Merge ayudan a gestionar grandes volúmenes de datos con mayor facilidad. Agrupa lógicamente una serie de tablas MyISAM idénticas y las referencia como un solo objeto.Es bueno para entornos de almacenamiento de datos.
El motor de almacenamiento de archivos está optimizado para la inserción de alta velocidad. Comprime los datos a medida que se insertan. No admite transacciones. Es ideal para almacenar y recuperar grandes cantidades de datos históricos y archivados poco consultados.
El motor de almacenamiento Blackhole acepta pero no almacena datos. Las recuperaciones siempre devuelven un conjunto vacío. La funcionalidad se puede utilizar en el diseño de bases de datos distribuidas donde los datos se replican automáticamente, pero no se almacenan localmente. Este motor de almacenamiento puede utilizarse para realizar pruebas de rendimiento u otras pruebas.
El motor de almacenamiento federado ofrece la posibilidad de separar servidores MySQL para crear una base de datos lógica a partir de muchos servidores físicos.Las consultas en el servidor local se ejecutan automáticamente en las tablas remotas (federadas). No se almacenan datos en las tablas locales.Es bueno para entornos distribuidos.
mysql> SHOW ENGINES\G*************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 2. row *************************** Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO...
El comando SHOW ENGINES muestra todos los motores disponibles que soporta el servidor.
Elegir el motor adecuado
Ningún motor de almacenamiento es ideal para todas las circunstancias. Algunos funcionan mejor en determinadas condiciones y funcionan peor en otras situaciones. Hay compensaciones que deben ser consideradas. Una solución más segura requiere más recursos; puede ser más lenta, tomar 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 Archive, están creados para ser utilizados en situaciones específicas.
En algunos casos la respuesta es clara. Cuando se trata de 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 completa, podemos elegir entre MyISAM o InnoDB. Sólo InnoDB soporta la restricción de integridad referencial de clave extranjera y si planeamos usar esta restricción, entonces la elección es clara.
Especificación y modificación de los motores de almacenamiento
El motor de almacenamiento se especifica en el momento de la creación de la tabla.
mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), -> Cost INTEGER) ENGINE='MyISAM';
La palabra clave ENGINEespecifica el motor de almacenamiento utilizado para esta tabla en particular.
Si no se especifica el motor de almacenamiento explícitamente, se utiliza el motor de almacenamiento por defecto. Antes de MySQL 5.5 el motor de almacenamiento por defecto era MyISAM. Para MySQL 5.5 y posteriores, el motor de almacenamiento por defecto es InnoDB.
Es posible migrar a un motor de almacenamiento diferente. Tenga en cuenta que migrar una tabla grande puede llevar mucho tiempo. También podemos encontrarnos con algunos problemas al migrar tablas. Algunas características podrían no estar soportadas en ambas tablas.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| InnoDB |+--------+1 row in set (0,05 sec)
Esta sentencia SQL averigua el motor de almacenamiento utilizado para una tabla Cars en la base de datos mydb. También podríamos utilizar la sentencia SQL SELECT CREATE TABLE Cars. La information_schema es una tabla que almacena información técnica sobre nuestras tablas.
mysql> ALTER TABLE Cars ENGINE='MyISAM';
Esta sentencia SQL cambia el motor de almacenamiento de la tabla Cars a MyISAM.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| MyISAM |+--------+1 row in set (0,00 sec)
Ahora el motor de almacenamiento de la tabla es MyISAM.
En esta parte del tutorial de MySQL, hemos cubierto los motores de almacenamiento.