El motor de almacenamiento (storage-engine) se encarga de almacenar, manejar y recuperar información de una tabla. Los motores más conocidos son MyISAM e InnoDB.
En la elección se pretende conseguir la mejor relación de calidad acorde con nuestra aplicación. Si necesitamos transacciones, claves foráneas y bloqueos, tendremos que escoger InnoDB.
Por el contrario, escogeremos MyISAM en aquellos casos en los que predominen las consultas SELECT a la base de datos.
- Dota a MySQL de un motor de almacenamiento transaccional (conforme a ACID) con capacidades de commit (confirmación), rollback (cancelación) y recuperación de fallos.
- Realiza bloqueos a nivel de fila y también proporciona funciones de lectura consistente sin bloqueo al estilo Oracle en sentencias SELECT.
- Estas características incrementan el rendimiento y la capacidad de gestionar múltiples usuarios simultáneos.
- No se necesita un bloqueo escalado en InnoDB porque los bloqueos a nivel de fila ocupan muy poco espacio.
- También soporta restricciones FOREIGN KEY. En consultas SQL, aún dentro de la misma consulta, pueden incluirse libremente tablas del tipo InnoDB con tablas de otros tipos.
Para crear una tabla InnoDB se debe especificar la opción ENGINE = InnoDB o TYPE = InnoDB en la sentencia SQL de creación de tabla:
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;
Las principales ventajas serían:
- Soporte de transacciones
- Bloqueo de registros
- Nos permite tener las características ACID (Atomicity, Consistency, Isolation and Durability: Atomicidad, Consistencia, Aislamiento y Durabilidad en español), garantizando la integridad de nuestras tablas.
- Es probable que si nuestra aplicación hace un uso elevado de INSERT y UPDATE notemos un aumento de rendimiento con respecto a MyISAM.
- Mayor velocidad en general a la hora de recuperar datos.
- Recomendable para aplicaciones en las que dominan las sentencias SELECT ante los INSERT /UPDATE.
- Ausencia de características de atomicidad ya que no tiene que hacer comprobaciones de la integridad referencial, ni bloquear las tablas para realizar las operaciones, esto nos lleva como los anteriores puntos a una mayor velocidad.
- ¿Es un problema el espacio en disco o memoria RAM? Decántate por MyISAM
- ¿Necesitarás hacer búsquedas full-text? Tu motor ha de ser MyISAM
- ¿Prefieres o requieres diseño relacional de bases de datos? Entonces necesitas InnoDB
- ¿Tu tabla va a recibir INSERT, UPDATE y DELETE mucho más tiempo de lo que será consultada? Te recomendamos InnoDB
Resumen de diferencias:
- InnoDB: Soporta transacciones completas, lo que significa que permite operaciones ACID (Atomicidad, Consistencia, Aislamiento y Durabilidad). Esto es crucial para aplicaciones que requieren integridad de datos, como sistemas bancarios o de comercio electrónico.
- MyISAM: No soporta transacciones. Si ocurre un error durante una operación, es posible que los datos no se recuperen fácilmente, lo que lo hace menos adecuado para aplicaciones críticas.
- InnoDB: Utiliza bloqueo a nivel de fila (row-level locking), lo que permite que múltiples usuarios accedan y modifiquen diferentes filas de una tabla simultáneamente sin conflictos. Esto mejora el rendimiento en entornos con alta concurrencia.
- MyISAM: Utiliza bloqueo a nivel de tabla (table-level locking). Si un usuario está modificando una tabla, otros usuarios deben esperar hasta que se complete la operación, lo que puede ser un cuello de botella en aplicaciones con muchas escrituras simultáneas.
- InnoDB: Soporta claves foráneas y restricciones de integridad referencial, lo que garantiza que las relaciones entre tablas se mantengan consistentes.
- MyISAM: No soporta claves foráneas ni integridad referencial. Las relaciones entre tablas deben manejarse a nivel de aplicación.
- InnoDB: Tiene un mecanismo de recuperación de datos más robusto. En caso de un fallo del sistema, InnoDB puede recuperar los datos hasta el último estado consistente gracias a su log de transacciones (transaction log).
- MyISAM: Es más propenso a la corrupción de datos en caso de un fallo del sistema, ya que no tiene un mecanismo de recuperación basado en transacciones.
- InnoDB: Suele ser más lento en operaciones de lectura intensiva en comparación con MyISAM, pero es más eficiente en entornos con muchas operaciones de escritura y alta concurrencia.
- MyISAM: Es más rápido en operaciones de lectura intensiva, especialmente en consultas SELECT simples, pero puede tener problemas de rendimiento en entornos con muchas operaciones de escritura.
- InnoDB: Almacena los datos en un espacio de tablas compartido (tablespace), lo que puede facilitar la gestión del almacenamiento pero puede ser menos eficiente en términos de espacio.
- MyISAM: Almacena cada tabla en archivos separados (.MYD para datos y .MYI para índices), lo que puede facilitar la compresión y la copia de seguridad de tablas individuales.
- InnoDB: A partir de MySQL 5.6, InnoDB también soporta búsquedas de texto completo (full-text search), aunque inicialmente era una característica exclusiva de MyISAM.
- MyISAM: Tradicionalmente ha sido mejor para búsquedas de texto completo, aunque esta ventaja se ha reducido con las versiones más recientes de MySQL.
- InnoDB: Utiliza más memoria para gestionar el buffer pool y otras estructuras de datos, lo que puede mejorar el rendimiento pero también requiere más recursos del sistema.
- MyISAM: Utiliza menos memoria, lo que puede ser beneficioso en entornos con recursos limitados.
- InnoDB es más adecuado para aplicaciones que requieren transacciones, integridad referencial y alta concurrencia.
- MyISAM puede ser una mejor opción para aplicaciones con muchas operaciones de lectura y pocas escrituras, o donde la simplicidad y la velocidad de lectura son prioritarias.
En general, InnoDB es el motor predeterminado en MySQL desde la versión 5.5 debido a su robustez y características avanzadas, mientras que MyISAM se utiliza en casos específicos donde sus ventajas en rendimiento de lectura son críticas.