Resumen: MySQL tiene varios motores de almacenamiento, cada uno con sus propias ventajas y desventajas; se puede elegir el mejor según las necesidades: MyISAM, InnoDB, MERGE, MEMORY(HEAP), BDB(BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE.

MySQL tiene varios motores de almacenamiento, cada uno con sus propias ventajas y desventajas; se puede elegir el mejor según las necesidades:

MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE.

MySQL soporta varios motores de almacenamiento como procesadores para diferentes tipos de tablas. Los motores de almacenamiento de MySQL incluyen motores que manejan tablas seguras para transacciones y motores que manejan tablas no seguras para transacciones:

• MyISAM gestiona tablas no transaccionales. Proporciona almacenamiento y recuperación de alta velocidad, así como capacidad de búsqueda de texto completo. MyISAM es compatible con todas las configuraciones de MySQL; es el motor de almacenamiento predeterminado, a menos que configures MySQL para usar otro motor por defecto.

• El motor de almacenamiento MEMORY proporciona tablas "en memoria". El motor de almacenamiento MERGE permite que un conjunto de tablas MyISAM idénticas se procese como una sola tabla. Al igual que MyISAM, los motores MEMORY y MERGE manejan tablas no transaccionales; ambos motores también se incluyen de forma predeterminada en MySQL.

  Nota: El motor de almacenamiento MEMORY fue designado formalmente como motor HEAP.

• Los motores de almacenamiento InnoDB y BDB proporcionan tablas seguras para transacciones. BDB se incluye en la distribución binaria MySQL-Max publicada para los sistemas operativos que lo soportan. InnoDB también se incluye de forma predeterminada en todas las distribuciones binarias de MySQL 5.1; puedes permitir o deshabilitar cualquiera de los motores configurando MySQL según tu preferencia.

• El motor de almacenamiento EXAMPLE es un motor "stub" que no hace nada. Puedes crear tablas con este motor, pero no se almacenan ni se recuperan datos en ellas o desde ellas. El propósito de este motor es servir como ejemplo en el código fuente de MySQL, demostrando cómo comenzar a escribir un nuevo motor de almacenamiento. Del mismo modo, su principal interés es para los desarrolladores.

• NDB Cluster es el motor de almacenamiento utilizado por MySQL Cluster para implementar tablas particionadas en múltiples computadoras. Se proporciona en la distribución binaria MySQL-Max 5.1. Este motor de almacenamiento actualmente solo es compatible con Linux, Solaris y Mac OS X. En futuras distribuciones de MySQL, queremos agregar soporte para este motor en otras plataformas, incluido Windows.

• El motor de almacenamiento ARCHIVE se utiliza para almacenar grandes cantidades de datos sin índices y de forma muy compacta.

• El motor de almacenamiento CSV almacena los datos en archivos de texto con formato separado por comas.

• El motor de almacenamiento BLACKHOLE acepta datos pero no los almacena, y las consultas siempre devuelven un conjunto vacío.

• El motor de almacenamiento FEDERATED almacena datos en una base de datos remota. En MySQL 5.1, solo funciona con MySQL, utilizando la API de Cliente C de MySQL. En futuras distribuciones, queremos que sea capaz de conectarse a otras fuentes de datos utilizando otros controladores o métodos de conexión de cliente.

Los más utilizados son MyISAM e InnoDB

¿Cómo elegir entre los motores de almacenamiento MyISAM e InnoDB de MySQL?

Aunque los motores de almacenamiento en MySQL no son solo MyISAM e InnoDB, los más utilizados son esos dos. Puede que algunos administradores de sitios web no hayan prestado atención a los motores de almacenamiento de MySQL; en realidad, el motor de almacenamiento también es un punto importante en el diseño de bases de datos. Entonces, ¿qué motor de almacenamiento debería usar un sistema de blog?

A continuación, veamos las diferencias entre los dos motores de almacenamiento.

• Primero, InnoDB admite transacciones, MyISAM no; esto es muy importante. La transacción es un método de procesamiento avanzado; por ejemplo, en una serie de inserciones, eliminaciones y modificaciones, si alguna falla, se puede revertir y restaurar, mientras que MyISAM no puede hacerlo.
• Segundo, MyISAM es adecuado para aplicaciones centradas en consultas e inserciones; InnoDB es adecuado para aplicaciones con modificaciones frecuentes y que implican una alta seguridad.
• Tercero, InnoDB admite claves externas, MyISAM no.
• Cuarto, en versiones anteriores a 5.1, el motor de almacenamiento predeterminado de MySQL es MyISAM; en versiones posteriores a 5.1, el motor predeterminado es InnoDB.
• Quinto, InnoDB no admite índices de tipo FULLTEXT.
• Sexto, InnoDB no guarda el número de filas de la tabla; por ejemplo, al ejecutar select count(*) from table, InnoDB necesita escanear toda la tabla para calcular cuántas filas hay, pero MyISAM solo tiene que leer el número de filas guardado. Ten en cuenta que cuando la sentencia count(*) incluye una condición where, MyISAM también necesita escanear toda la tabla.
• Séptimo, para los campos de auto-incremento, en InnoDB debe haber un índice que contenga solo ese campo, pero en las tablas MyISAM se puede crear un índice compuesto junto con otros campos.
• Octavo, al vaciar toda la tabla, InnoDB elimina fila por fila, con una eficiencia muy lenta. MyISAM, en cambio, reconstruye la tabla.
• Noveno, InnoDB admite bloqueo a nivel de fila (en algunos casos aún bloquea toda la tabla, comoupdate table set a=1 where user like '%lee%'

Según las nueve diferencias anteriores, combinadas con las características de un blog personal, se recomienda que los sistemas de blog personal usen MyISAM, porque en un blog las operaciones principales son lectura y escritura, rara vez hay operaciones encadenadas. Por lo tanto, elegir el motor MyISAM hará que la eficiencia al abrir las páginas de tu blog sea mayor que la de un blog con motor InnoDB. Por supuesto, es solo una sugerencia personal; la mayoría de los blogs deben elegir con cautela según la situación real.

Algunas consideraciones sobre la elección entre MyISAM e InnoDB:

MYISAM e INNODB son dos motores de almacenamiento proporcionados por la base de datos MySQL. Ambos tienen sus propias ventajas y desventajas. INNODB soporta algunas funciones avanzadas de bases de datos relacionales, como transacciones y bloqueo a nivel de fila, que MYISAM no soporta. MYISAM tiene mejor rendimiento y ocupa menos espacio de almacenamiento. Por lo tanto, la elección del motor de almacenamiento depende de la aplicación específica.

Si tu aplicación necesita usar transacciones obligatoriamente, sin duda debes elegir el motor INNODB. Pero ten en cuenta que el bloqueo a nivel de fila de INNODB es condicional. Cuando la condición WHERE no utiliza la clave primaria, igualmente bloqueará toda la tabla. Por ejemplo, una sentencia DELETE como `DELETE FROM mytable`.

Si tu aplicación tiene altos requisitos de rendimiento de consulta, entonces debes usar MYISAM. Los índices y los datos de MYISAM están separados, y sus índices están comprimidos, lo que permite un mejor uso de la memoria. Por lo tanto, su rendimiento de consulta es claramente superior al de INNODB. Los índices comprimidos también ahorran espacio en disco. MYISAM tiene la funcionalidad de índice de texto completo, lo que puede optimizar enormemente la eficiencia de las consultas LIKE.

Algunos dicen que MYISAM solo se puede usar en aplicaciones pequeñas, pero en realidad eso es solo un prejuicio. Si la cantidad de datos es grande, esto debe resolverse mediante la mejora de la arquitectura, como la partición de tablas y bases de datos, en lugar de depender únicamente del motor de almacenamiento.

Otros comentarios:

Ahora generalmente se elige InnoDB, principalmente por el bloqueo de tabla completa de MyISAM, el problema de serialización de lectura y escritura, y la baja eficiencia al bloquear la tabla durante la concurrencia. MyISAM no suele elegirse para aplicaciones intensivas en lectura y escritura.

Sobre el motor de almacenamiento predeterminado de la base de datos MySQL:

MyISAM e InnoDB son dos motores de almacenamiento de MySQL. Si se trata de una instalación predeterminada, entonces debería ser InnoDB. Puedes encontrar `default-storage-engine=INNODB` en el archivo my.ini; por supuesto, puedes especificar el motor de almacenamiento correspondiente al crear la tabla. Puedes ver la información correspondiente mediante `show create table xx`.

Comparación entre InnoDB y MyISAM en MySQL

MyISAM：

Cada MyISAM se almacena en disco como tres archivos. El nombre del primer archivo comienza con el nombre de la tabla y la extensión indica el tipo de archivo. El archivo `.frm` almacena la definición de la tabla. La extensión del archivo de datos es `.MYD` (MYData).

Las tablas MyISAM se pueden comprimir y admiten búsqueda de texto completo. No admiten transacciones ni tampoco claves foráneas. Si se realiza una reversión de transacción, esta será incompleta, y no tiene atomicidad. Al realizar `UPDATE`, se bloquea la tabla, por lo que la concurrencia es relativamente baja. Si se ejecutan grandes cantidades de SELECT, MyISAM es una mejor opción.

Los índices y los datos de MyISAM están separados, y los índices están comprimidos, lo que aumenta considerablemente la utilización de memoria. Se pueden cargar más índices. En cambio, InnoDB tiene índices y datos estrechamente vinculados, y al no usar compresión, InnoDB resulta bastante más voluminoso que MyISAM.

MyISAM almacena en caché en memoria los índices, no los datos. En cambio, InnoDB almacena en caché en memoria los datos. Relativamente, cuanto mayor sea la memoria del servidor, mayores serán las ventajas que InnoDB pueda aprovechar.

Ventajas:La consulta de datos es relativamente rápida, adecuada para grandes cantidades de SELECT, y admite índice de texto completo.

Desventajas:No admite transacciones, no admite claves foráneas, la concurrencia es baja y no es adecuado para grandes cantidades de UPDATE.

InnoDB：

Este tipo es seguro para transacciones. Tiene las mismas características que el tipo BDB, y además admiten claves foráneas. Las tablas InnoDB son muy rápidas. Tiene características más ricas que BDB, por lo que si se necesita un motor de almacenamiento seguro para transacciones, se recomienda usarlo. Al realizar UPDATE, la tabla realiza bloqueo a nivel de fila, por lo que la concurrencia es relativamente alta. Si tus datos requieren grandes cantidades de INSERT o UPDATE, por consideraciones de rendimiento, deberías usar tablas InnoDB.

Ventajas:Admite transacciones, admite claves foráneas, tiene mayor concurrencia y es adecuado para grandes cantidades de UPDATE.

Desventajas:La consulta de datos es relativamente rápida, pero no es adecuado para grandes cantidades de SELECT.

Para las tablas de tipo InnoDB que admiten transacciones, la principal razón que afecta la velocidad es que la configuración predeterminada de AUTOCOMMIT está activada, y el programa no llama explícitamente a BEGIN para iniciar una transacción, lo que hace que cada inserción se confirme (COMMIT) automáticamente, afectando gravemente la velocidad. Puedes llamar a `BEGIN` antes de ejecutar las sentencias SQL; varias sentencias SQL forman una transacción (incluso si AUTOCOMMIT está activado), lo que mejorará enormemente el rendimiento.

Las diferencias básicas son:El tipo MyISAM no admite procesamiento avanzado como transacciones, mientras que el tipo InnoDB sí lo admite.

Las tablas de tipo MyISAM enfatizan el rendimiento, su velocidad de ejecución es más rápida que la del tipo InnoDB, pero no ofrecen soporte de transacciones. En cambio, InnoDB ofrece soporte de transacciones y funciones avanzadas de bases de datos como claves foráneas.

Otras comparaciones:

MyIASM es una nueva versión de las tablas IASM, con las siguientes extensiones:

• Portabilidad a nivel binario.
• Índices de columnas NULL.
• Menos fragmentación para filas de longitud variable en comparación con las tablas ISAM.
• Soporte para archivos grandes.
• Mejor compresión de índices.
• Mejor distribución estadística de claves.
• Mejor y más rápido manejo de auto_increment.

A continuación se presentan algunas diferencias en los detalles y la implementación específica:

• 1. InnoDB no admite índices de tipo FULLTEXT.
• 2. InnoDB no almacena el número exacto de filas de la tabla. Es decir, al ejecutar `select count(*) from table`, InnoDB tiene que escanear toda la tabla para calcular cuántas filas hay, mientras que MyISAM simplemente lee el número de filas ya guardado. Ten en cuenta que cuando la sentencia `count(*)` incluye una condición WHERE, la operación es la misma para ambos tipos de tablas.
• 3. Para campos de tipo AUTO_INCREMENT, InnoDB debe incluir un índice que contenga solo ese campo, pero en las tablas MyISAM se puede crear un índice conjunto con otros campos.
• 4. Al ejecutar `DELETE FROM table`, InnoDB no recrea la tabla, sino que elimina fila por fila.
• 5. La operación `LOAD TABLE FROM MASTER` no funciona con InnoDB. La solución es convertir primero la tabla InnoDB a MyISAM, importar los datos y luego volver a cambiarla a InnoDB; sin embargo, esto no se aplica a tablas que utilizan características adicionales de InnoDB (como claves foráneas).

Además, el bloqueo a nivel de fila de las tablas InnoDB no es absoluto. Si al ejecutar una sentencia SQL MySQL no puede determinar el rango a escanear, la tabla InnoDB también bloqueará toda la tabla, por ejemplo: `update table set num=1 where name like "%aaa%"`.

Ningún tipo de tabla es universal. Solo seleccionando el tipo de tabla adecuado según el tipo de negocio se puede aprovechar al máximo las ventajas de rendimiento de MySQL.

Comparación de actualizaciones entre InnoDB y MyISAM:

La organización de datos de InnoDB es un árbol B+ construido según la clave primaria. Si no se define explícitamente una clave primaria, InnoDB seleccionará una clave única NOT NULL como clave primaria. Si tampoco existe, InnoDB creará una clave primaria de 6 bytes. El índice de la clave primaria apunta a la página, no a la posición específica de la fila.

Una clave primaria que no sea incremental hará que la inserción sea muy lenta, por ejemplo usar número de teléfono o número de identificación como clave primaria. Por lo tanto, utiliza bien AUTO_INCREMENT.

Una tabla grande no da miedo; lo que da miedo es `count` o un `limit` con desplazamiento alto. Se puede reemplazar un `limit` grande por `limit max_id, xxxxx`.

Limit 0 1000 | limit 1001 1000 | limit 2001 1000

Limit 0 1000 | where id>max_id1 limit 1000 | where id>max_id2 limit 1000

Para InnoDB, dividir tablas según alguna columna no tiene sentido si se quiere mejorar el rendimiento en un solo servidor.

La velocidad de inserción y la velocidad de consulta a veces son una contradicción irreconciliable.

Decir que InnoDB no es adecuado para realizar `count` es incorrecto; MyISAM también es igual de lento, solo que MyISAM guarda en caché el número de filas de toda la tabla, por lo que `count` de la tabla completa es muy rápido. Si hay una condición de consulta y no es una consulta por clave primaria, entonces no hay diferencia. La razón por la que el `count` por clave primaria es lento es que InnoDB está organizado por clave primaria; al hacer `count` por clave primaria, se cargan los datos.

El almacenamiento por páginas de InnoDB facilita que InnoDB realice caché de toda la tabla y copias de seguridad en caliente.

Si la tabla tiene muchos índices, la velocidad de actualización de InnoDB es mayor que la de MyISAM, porque los índices secundarios de InnoDB están asociados a la clave primaria de la tabla, que es un valor lógico, mientras que todos los índices de MyISAM están asociados a la posición física de los datos. Al actualizar, es posible que la posición física de los datos cambie; si cambia, todos los índices deben actualizarse.

InnoDB no almacena el número exacto de filas de la tabla. Es decir, al ejecutar `select count(*) from table`, InnoDB debe escanear toda la tabla para calcular cuántas filas hay, mientras que MyISAM simplemente lee el número de filas guardado. Ten en cuenta que cuando la sentencia `count(*)` incluye una condición WHERE, la operación es la misma para ambos tipos de tablas.

Dirección original: https://my.oschina.net/junn/blog/183341