Una peculiaridad de MySQL es que cuando creas una nueva tabla, puedes especificar su tipo. MySQL soporta una gran cantidad de tipos de tablas, que se distinguen por una variedad de propiedades.
Dos de las más importantes son las tablas tipo MyISAM y InnoDB.
Si no se especifica el tipo de tabla cuando se crea una tabla, entonces el servidor MySQL decide por ti, en función de su configuración, ya sea para MyISAM o InnoDB. El tipo predeterminado se establece con la opción default-table-type en el archivo de configuración de MySQL.
Esta sección proporciona una breve descripción de los diferentes tipos de tablas reconocidos por MySQL, así como algunas de sus propiedades y cuando el uso de un tipo u otro es apropiado.
Tablas MyISAM
El tipo de tabla MyISAM está consolidado, es estable y fácil de administrar. Si no tienes una razón particular para elegir otro tipo, entonces deberías usar este tipo. Internamente, hay dos variantes de este tipo de tabla, el servidor MySQL elige el tipo apropiado por sí mismo:
MyISAM Static: Estas tablas se usan cuando todas las columnas de la tabla se han definido predeterminado tamaño. El acceso en tales tablas es particularmente eficiente. Esto es cierto incluso si la tabla tiene frecuentes cambios (es decir, cuando hay muchos comandos INSERT, UPDATE y DELETE sobre ella). Además, la seguridad de los datos es bastante alta, ya que, en el caso de archivos corruptos u otros problemas, es relativamente fácil de extraer registros.
MyISAM Dynamic: Si en la declaración de una tabla también hay solo un VARCHAR, xxxTEXT o campo xxxBLOB, entonces MySQL selecciona automáticamente este tipo de tabla. La ventaja significativa sobre la variante estática MyISAM es que el requisito de espacio suele ser significativamente menor: Las cadenas de caracteres y los objetos binarios requieren un espacio acorde con su tamaño real (más algunos bytes por encima).
Sin embargo, es un hecho que los registros de datos no son del mismo tamaño. Si los registros son luego alterados, entonces su ubicación dentro del archivo de la base de datos puede tener que cambiar. En el viejo lugar aparece un agujero en el archivo de base de datos. Además, es posible que los campos de un registro no estén todos almacenados dentro de un bloque contiguo dentro del archivo de la base de datos, es decir, puede estar en varias ubicaciones. Todo esto da como resultado cada vez más tiempos de acceso más largos a medida que la tabla editada se fragmenta cada vez más, a menos que use OPTIMIZE TABLE o el programa de optimización myisamchk que se puede ejecutar de vez en cuando.
MyISAM comprimido: Las tablas MyISAM dinámicas y estáticas se pueden comprimir con el programa auxiliar myisamchk. Esto generalmente resulta una contracción de los requisitos de almacenamiento para la tabla a menos de la mitad de la cantidad original (dependiendo del contenido de la tabla). Asegúrate, después de la ejecución de myisamchk, que cada registro de datos debe descomprimirse cuando se lee, pero aún es posible bajo ciertas condiciones, el acceso a la tabla es, sin embargo, más rápido, particularmente con una combinación de un disco duro lento y un procesador rápido.
El inconveniente decisivo de las tablas MyISAM comprimidas es que no se pueden realizar cambios sobre las filas de la tabla, es decir, son tablas son de solo lectura.
Tablas InnoDB
Además del formato MyISAM, MySQL admite un segundo formato de tabla, InnoDB. Esto es una alternativa moderna a MyISAM, que ofrece sobre todo las siguientes funciones adicionales:
Transacciones: las operaciones de la base de datos en tablas InnoDB se pueden ejecutar como transacciones. Esto permite ejecutar varios comandos SQL conectados lógicamente como una sola entidad. Si un error ocurre durante la ejecución, luego todos los comandos (no solo aquel durante el cual se produjo el error) son anulados. Además, las transacciones ofrecen otras ventajas que mejoran la seguridad de aplicaciones de bases de datos.
Las transacciones se pueden ejecutar en los cuatro niveles de aislamiento del estándar ANSI-SQL/92 (LEER NO COMPROMETIDO, LEÍDO COMPROMETIDO, REPETIBLE LEÍDO, SERIALIZABLE).
Bloqueo de nivel de fila: Al implementar transacciones, el controlador de tabla InnoDB utiliza una fila interna de nivel de bloqueo. Esto significa que, durante una transacción, toda la tabla no tiene que ser bloqueada para el acceso de otros usuarios (que es el caso de una tabla MyISAM durante un comando LOCK TABLE), pero solo los registros de datos se ven realmente afectados. Si muchos usuarios están simultáneamente haciendo cambios en una tabla grande, el bloqueo a nivel de fila puede traer una gran ventaja en eficiencia.
El controlador de tabla InnoDB reconoce automáticamente los deadlocks (es decir, la condición en la que dos los procesos se bloquean mutuamente) y en tal caso, termina uno de los dos procesos automáticamente.
Foreign Key Constraints (Restricciones de claves externas): Cuando se definen relaciones entre tablas, la tabla del controlador InnoDB asegura automáticamente que la integridad referencial de la tabla se conserve después de utilizar comandos DELETE. Por lo tanto, es imposible, por ejemplo, que un registro en la tabla A se refiera a un ya no existente en la tabla B (En la jerga de base de datos, esta función se denomina restricción de clave externa - Foreign Key Constraints-).
Recuperación tras error: Después de un error, las tablas InnoDB se devuelven de forma automática y muy rápida a un estado consistente (siempre que el sistema de archivos del ordenador no haya sido dañado). El controlador de tabla InnoDB ha sido un componente integral de MySQL desde la versión 3.23.34.
Limitaciones y desventajas
Si las tablas InnoDB tienen solo ventajas y no inconvenientes, uno podría volcar las tablas MyISAM en el basura y listo. Pero ese no es el caso, como lo revela la siguiente lista:
Administración de Tablespace: Si usas tablas con el controlador de tabla MyISAM, cada tabla se almacena en su propio archivo, que crece o se reduce según sea necesario, el controlador de tabla InnoDB almacena todos los datos e índices en un tablespace, que comprende uno o más archivos, que forma una especie de sistema de archivos virtual. Los archivos utilizados no pueden hacerse más pequeños más tarde. Tampoco es posible detener el servidor MySQL y luego copiar una tabla simplemente copiando su archivo. Por lo tanto, en la administración de tablas InnoDB, el comando mysqldump debe emplearse con más frecuencia que con las tablas MyISAM.
Tamaño de registro: un registro de datos puede ocupar un máximo de 8000 bytes. Este límite no es válido para TEXT y columnas BLOB, de las cuales solo los primeros 512 bytes se almacenan en la base de datos propiamente dicha. Los datos de tales tipos de columnas, más allá de este tamaño, se almacenan en páginas separadas del espacio maestro.
Requisito de almacenamiento: los requisitos de almacenamiento para tablas InnoDB son mucho mayores que los para tablas MyISAM equivalentes (hasta dos veces más grandes).
Índice de texto completo: para las tablas InnoDB no se puede usar un índice de texto completo.
Bloqueo de tabla: InnoDB usa sus propios algoritmos de bloqueo en la ejecución de transacciones. Por lo tanto, debes evitar LOCK TABLE ... READ/WRITE. En su lugar, debes usar SELECT ... IN SHARE MODE o SELECT ... FOR UPDATE. Estos comandos tienen la ventaja adicional de que bloquea solo registros individuales y no toda la tabla. Para versiones futuras de MySQL, los comandos específicos de InnoDB LOCK TABLE ... IN SHARE MODE y LOCK TABLE ... EXCLUSIVE MODE están planeados.
Tablas mysql: las tablas mysql para gestionar privilegios de acceso MySQL no se pueden transformar en tablas InnoDB. Deben permanecer en formato MyISAM.
MyISAM o InnoDB?
Puedes especificar individualmente para cada tabla de tu base de datos qué controlador de tabla se va a utilizar. Es decir, está permitido dentro de una sola base de datos usar tablas MyISAM e InnoDB. Esto te permite elegir el controlador de tabla óptimo para cada tabla, según su contenido.
Las tablas MyISAM se recomiendan cuando se quiera administrar tablas de forma eficiente respecto espacio que ocuparán y el espacio disponible. Las tablas InnoDB, por otro lado, tienen prioridad cuando su aplicación hace uso de transacciones, requiere una mayor seguridad o muchos usuarios deben acceder a ella usuarios simultáneamente para hacer cambios.
No hay una respuesta generalmente válida a la pregunta de qué tipo de tabla ofrece una respuesta más rápida. En principio, dado que las transacciones toman tiempo y las tablas InnoDB ocupan más espacio en el disco duro, MyISAM debería tener la ventaja. Pero con tablas InnoDB puede evitar el uso de comandos LOCK TABLE, que ofrece una ventaja para InnoDB, que está mejor optimizado para ciertas aplicaciones, sobre todo aplicaciones transaccionales.
Además, la velocidad de una aplicación depende en gran medida del hardware (particularmente la cantidad de RAM), las configuraciones en el archivo de configuración de MySQL y otros factores.
