[Repost] Introducción a los motores de almacenamiento comunes de MySQL (InnoDB, MyISAM, MEMORY, MERGE, ARCHIVE) y cómo elegir
Uno: Introducción
El motor de almacenamiento de la base de datos es la organización de software subyacente de la base de datos. El sistema de gestión de bases de datos (DBMS). utiliza el motor de datos para crear, consultar, actualizar y eliminar datos. Los diferentes motores de almacenamiento proporcionan diferentes mecanismos de almacenamiento, tecnologías de indexación, niveles de bloqueo y otras funciones. El uso de diferentes motores de almacenamiento puede obtener funciones específicas. Hoy en día, muchos sistemas de gestión de bases de datos diferentes admiten muchos motores de datos diferentes.
¿Qué es un motor de almacenamiento?
Los datos en MySQL se almacenan en archivos (o memoria) utilizando una variedad de técnicas diferentes. Cada tecnología utiliza diferentes mecanismos de almacenamiento, técnicas de indexación y niveles de bloqueo y, en última instancia, proporciona una variedad de características y funcionalidades diferentes. Al elegir diferentes tecnologías, puede obtener velocidad o funcionalidad adicional que mejore la funcionalidad general de su aplicación.
Por ejemplo, si está procesando grandes cantidades de datos temporales, es posible que desee utilizar el motor de almacenamiento MySQL en memoria. El motor de almacenamiento en memoria puede almacenar todos los datos de la tabla en la memoria. Alternativamente, es posible que necesite una base de datos que admita transacciones (para garantizar que los datos se puedan revertir si una transacción no tiene éxito).
En MySQL, estas diferentes tecnologías y sus características asociadas se denominan motores de almacenamiento (también llamados tipos de tablas). MySQL está configurado de forma predeterminada con varios motores de almacenamiento diferentes que se pueden preconfigurar o habilitar en el servidor MySQL. Puede elegir un motor de almacenamiento para su servidor, base de datos y tablas, lo que le brinda la máxima flexibilidad para elegir cómo almacenar información, cómo recuperarla y qué rendimiento y funcionalidad necesita de sus datos.
Dos: vista del motor de almacenamiento relacionada
--Ver los motores de almacenamiento admitidos por la base de datos
Muestra el motor, el resultado se muestra en la siguiente figura; (el valor en la columna de soporte indica si se puede usar un motor: SÍ "significa que se puede usar, "NO " significa que no se puede usar, "DEFAULT " significa que el motor es el motor de almacenamiento predeterminado actual).
-- Ver el motor predeterminado de la base de datos mysql
Mostrar variables, como "storage_engine"; los resultados obtenidos son los siguientes. Puede ver que mi base de datos mysql usa el motor de almacenamiento InnoDB de forma predeterminada. :
--Ver la declaración de creación de la tabla para obtener información de creación de la tabla, motor, caracteres predeterminados, restricciones, etc.
show create table zd_grade Ejecute la instrucción anterior para obtener los datos predeterminados de la tabla:
CREATE TABLE `zd_grade` ( `ID` char(8) NOT NULL DEFAULT '' COMMENT 'Número de curso', `Course_ID` char(8) NOT NULL DEFAULT ' ' COMMENT 'Número de curso', `C_Grade` decimal(10, 2) NOT NULL DEFAULT '0.00' COMENTARIO 'Calificación china', `F_Grade` decimal( 10, 2) NOT NULL DEFAULT '0.00' COMENTARIO 'puntuación de idioma extranjero', `Exam_Time` fecha NOT NULL DEFAULT '0000-00-00' COMENTARIO 'fecha del examen', `Semister` varchar(255) NOT NULL DEFAULT '' COMENTARIO 'semestre', `Grade_Type` char(2) NOT NULL DEFAULT '' COMENTARIO 'Tipo de calificación', CLAVE PRIMARIA (`ID`, `Course_ID`, `Grade_Type`), CLAVE `index_grade_id` (`ID` ) USANDO HASH) ENGINE=InnoDB DEFAULT CHARSET=utf8 En MySQL, no es necesario usar el mismo motor de almacenamiento en todo el servidor para requisitos específicos, puede usar un motor de almacenamiento diferente para cada tabla; A continuación se presentarán varios de los motores más utilizados (InnoDB, MyISAM, MEMORY, MERGE, ARCHIVE).
Tres: varios motores de uso común (InnoDB, MyISAM, MEMORY, MERGE, ARCHIVE)
Motor de almacenamiento InnoDB
InnoDB es el motor preferido para bases de datos transaccionales. Se admiten tablas de transacciones seguras (ACID), bloqueo de filas y claves externas, como se muestra en la figura anterior. InnoDB es el motor MySQL predeterminado.
InnoDB utiliza MVCC (Control de concurrencia multiversión) para admitir una alta concurrencia e implementa cuatro niveles de aislamiento estándar. Su nivel predeterminado es LECTURA REPETIBLE y evita lecturas fantasma a través de una política de bloqueo de espacios (siguiente bloqueo de tecla). El bloqueo de espacios significa que InnoDB no solo bloquea las filas involucradas en la consulta, sino que también bloquea los espacios en el índice para evitar inserciones de filas fantasma.
Las tablas InnoDB se basan en índices agrupados. La estructura de índice de InnoDB es muy diferente de la de otros motores de almacenamiento de MySQL porque los índices agrupados tienen un alto rendimiento para consultas de clave primaria. Sin embargo, sus índices secundarios (índices no primarios) deben contener las columnas de clave primaria, por lo que si la clave primaria es grande, todos los demás índices también lo serán. Por lo tanto, si hay muchos índices en la tabla, la clave principal debe ser lo más pequeña posible.
InnoDB no crea un directorio; cuando se usa InnoDB, MySQL crea un archivo de datos autoextendible de 10 MB llamado ibdata1 y dos archivos de 5 MB llamados ib_logfile0 e ib_logfile1 en el directorio de datos de MySQL.
Las características clave de InnoDB incluyen:
InnoDB proporciona un motor de almacenamiento seguro para transacciones (compatible con ACID) para MySQL con capacidades de confirmación, reversión y recuperación de fallos.
InnoDB se bloquea a nivel de fila y proporciona lecturas sin bloqueo similares a las de Oracle en declaraciones SELECT. Estas características mejoran las implementaciones y el rendimiento multiusuario. Los tipos de InnoDB se pueden mezclar libremente con otros tipos de tablas MySQL en consultas SQL, incluso dentro de la misma consulta. El motor de almacenamiento InnoDB está completamente integrado con el servidor MySQL. El motor de almacenamiento InnoDB mantiene su propio grupo de búfer para almacenar en caché datos e índices en la memoria principal. Las tablas de InnoDB pueden ser de cualquier tamaño, incluso en sistemas operativos con un límite de tamaño de archivo de 2 GB. InnoDB admite restricciones de integridad de claves externas. Al almacenar datos en tablas, cada tabla se almacena en el orden de clave principal. Si no se ha especificado ninguna clave principal al definir la tabla, InnoDB genera un ROWID de 6 bytes para cada fila y lo utiliza como clave principal. InnoDB se utiliza en muchos sitios web de bases de datos grandes que requieren un alto rendimiento. Es uno de los motores de almacenamiento más utilizados en entornos web, de almacenamiento de datos y otros entornos de aplicaciones. MyISAM tiene altas velocidades de inserción y consulta, pero no admite transacciones.
Las tablas MyISAM se pueden comprimir y admiten búsqueda de texto completo. No se admiten transacciones ni claves externas. Si retrocede, se producirá una reversión incompleta y no una reversión atómica. Los bloqueos de tabla se realizan al actualizar los datos y la cantidad de concurrencia es relativamente pequeña. Si realiza una gran cantidad de SELECT, MyISAM es una mejor opción.
MyISAM tiene índices y datos separados, y los índices están comprimidos, por lo que el uso de memoria es mucho mayor. Puede cargar más índices, mientras que Innodb está estrechamente vinculado a índices y datos y no utiliza compresión, lo que puede hacer que Innodb sea más grande que MyISAM.
MyISAM almacena en caché los índices en la memoria, no los datos. InnoDB almacena datos en caché en la memoria y cuanto mayor sea la memoria del servidor, más memoria podrá utilizar InnoDB.
Las funciones principales de MyISAM incluyen:
Admite archivos grandes (longitud de archivo de hasta 63 bits) en sistemas de archivos y sistemas operativos que admiten archivos grandes Filas de tamaño dinámico para eliminación y actualización Menos fragmentación cuando se mezcla con operaciones de inserción. El número máximo de índices por tabla MyISAM es 64 y se puede cambiar recompilando. El número máximo de columnas por índice es 16. La longitud máxima de la clave es 1000 bytes; esto también se puede cambiar volviendo a compilar; si la longitud de la clave excede los 250 bytes, se utilizarán las claves BLOB y TEXT de más de 1024 bytes. Las columnas se pueden indexar como NULL. está permitido en las columnas de índice. Cada tabla de tipo MyISAM tiene una columna interna AUTO_INCREMENT, que se actualiza durante las operaciones INSERT y UPDATE, y la columna AUTO_INCREMENT se actualiza. Por lo tanto, la columna AUTO_INCREMENT de una tabla de tipo MyISAM se actualiza más rápido que la columna AUTO_INCREMENT de una tabla de tipo InnoDB. Puede colocar los archivos de datos y los archivos de índice en diferentes directorios. Cada columna de caracteres puede tener un conjunto de caracteres diferente. Longitud de registro fija o dinámica La longitud máxima de las columnas VARCHAR y CHAR es 64 KB. La creación de una base de datos utilizando el motor MyISAM producirá tres archivos. El nombre del archivo comienza con el nombre de la tabla y tiene la extensión Tipo de archivo: los archivos frm almacenan definiciones de tablas, los archivos de datos tienen la extensión .MYD (MYData) y los archivos de índice tienen la extensión .MYI (MYIndex).
Motor de almacenamiento de memoria
El punto de partida para utilizar el motor de almacenamiento de memoria MySQL es la velocidad. Para obtener el tiempo de respuesta más rápido, el medio de almacenamiento lógico utilizado es la memoria del sistema. Si bien almacenar datos de tablas en la memoria proporciona un alto rendimiento, cuando el demonio mysqld falla, todos los datos en la memoria se pierden.
El aumento de la velocidad también tiene algunas desventajas. Requiere que los datos almacenados en la tabla de datos en memoria estén en un formato de longitud constante, lo que significa que no se pueden utilizar tipos de datos de longitud constante como BLOB y TEXT. VARCHAR también es un tipo de longitud fija, pero se puede utilizar porque se trata como un tipo CHAR de longitud fija en MySQL. Las características principales de MEMORY son las siguientes:
Cada tabla MEMORY puede tener hasta 32 índices y cada índice tiene 16 columnas. La tabla MEMORY utiliza un formato de longitud de registro fija y no admite columnas BLOB o TEXT. . MEMORY admite columnas e índices AUTO_INCREMENT en columnas que pueden contener valores NULL y el servidor usa memoria en el procesamiento de consultas cuando se crean tablas internas durante el tiempo de inactividad. Cuando el contenido de la tabla MEMORY ya no sea necesario, para liberar la memoria que utiliza la tabla MEMORY, debe ejecutar DELETE FROM o TRUNCATE TABLE, o eliminar toda la tabla (usando DROP TABLE) MERGE el motor de almacenamiento
El motor de almacenamiento MERGE es una combinación de un conjunto de tablas MyISAM cuyas tablas MyISAM MERGE El motor de almacenamiento es una combinación de un conjunto de tablas MyISAM que deben tener la misma estructura. Aunque su uso no es tan destacado como otros motores, sí. puede resultar muy útil en algunas situaciones. Para decirlo sin rodeos, la tabla Merge es un agregador de múltiples tablas MyISAM idénticas, no hay datos en la tabla Merge y las tablas de tipo Merge se pueden consultar, actualizar y eliminar, lo que en realidad es una operación en la tabla MyISAM interna.
Se utiliza principalmente para información como registros del servidor. Una estrategia de almacenamiento común es dividir los datos en muchas tablas, teniendo cada tabla un nombre asociado con el final de un tiempo específico. Por ejemplo, podría almacenar datos de registro del servidor en 12 tablas idénticas, cada una con un nombre que corresponda a cada mes. Cuando es necesario generar informes basados en datos de las 12 tablas de registro, esto significa que es necesario escribir y actualizar consultas de varias tablas para reflejar la información de esas tablas.
En lugar de escribir estas consultas potencialmente propensas a errores, es mejor fusionar estas tablas y usar una sola consulta, y luego eliminar la tabla fusionada sin afectar los datos originales; eliminar la tabla fusionada solo elimina los datos fusionados; La definición de la tabla, no la definición de la tabla fusionada, tendrá un impacto en las tablas internas.
Por ejemplo, si tiene varias tablas de datos de registro, son el contenido de las entradas de registro de cada uno de los últimos años, que se definen de la siguiente manera, donde YY representa el año:
Cree la tabla log_YY( dt DATETIME NOT NULL, info VARCHAR(100) NOT NULL, INDEX (dt) ENGEX(100) NOT NULL, dt DATETIME(100) NOT NULL, dt DATETIME)INDEX (dt))ENGINE = MyISAM; supongamos que la colección de tablas de datos de registro actual incluye log_2004, log_2005, log_2006 y log_2007, puede crear una tabla de datos MERGE como se muestra a continuación para combinarlos en una unidad lógica:
Crear tabla log_YY( dt DATETIME NULL, información VARCHAR(100 NOT NULL, INDEX (dt)) merge( dt DATETIME NOT NULL, información VARCHAR(100) NOT NULL, INDEX(dt))ENGINE = MERGE UNION = (log_2004, log_2005, log_2006, log_2007); El valor de la opción ENGINE debe ser las columnas de las opciones MERGE y UNION. Una vez creada la tabla MERGE, se puede consultar como cualquier otra tabla, excepto que cada consulta se realizará en cada una de las tablas de datos de miembros que componen la tabla MERGE. Continúe.
Con la siguiente consulta, podemos conocer el número total de filas en cada tabla de datos de registro:
SELECT COUNT(*) FROM log_merge; determine los años Número de entradas de registro para cada año en:
SELECT YEAR(dt) AS y, COUNT(*) AS entradas FROM log_merge GROUP BY y Además de poder hacer referencia a múltiples tablas de datos simultáneamente; sin emitir múltiples consultas Además, la tabla de datos MERGE también proporciona las siguientes comodidades
La tabla de datos MERGE se puede utilizar para crear una unidad lógica cuyo tamaño exceda la longitud máxima permitida por una única tabla de datos MyISAM
Verá una pequeña cantidad de tablas de datos comprimidos incluidas en la tabla de datos MERGE. Por ejemplo, después de que finalice un año específico, ya no debe agregar registros al archivo de registro correspondiente, por lo que puede usar el. utilidad myisampack para comprimirla y ahorrar espacio. La tabla de datos MERGE seguirá funcionando como de costumbre
La tabla de datos MERGE también admite operaciones DELETE y UPDATE. Las operaciones INSERT son engorrosas porque MySQL necesita saber en qué tabla miembro insertar la nueva fila. La definición de una tabla MERGE puede incluir una opción INSERT_METHOD, cuyos valores pueden ser NO, FIRST y LAST, lo que significa que las operaciones INSERT están prohibidas y se insertarán nuevas filas de datos en la primera o última tabla listada en la UNION actual. opción.
Por ejemplo, la siguiente definición trata una operación INSERT en la tabla de datos log_merge como una operación INSERT en la tabla de datos log_2007----, que es la última tabla de datos listada en la opción UNION:
CREAR TABLA log_merge (dt DATETIME NOT NULL, info VARCHAR(100) NOT NULL, INDEX(dt))ENGINE = MERGE UNION = (log_2004, log_2005, log_2006, log_2007) INSERT_METHOD = LAST; Crea una nueva tabla de datos de miembros log_2009 y dale lo mismo; estructura de la tabla y luego modifique la tabla de datos log_merge para incluir log_2009:
log_2009:
CREATE TABLE log_2009 LIKE log_2008 ALTER TABLE log_merge UNION = (log_2004, log_2005, log_ 2006, log_2007 , log_2008, log_2009); motor de almacenamiento ARCHIVE
Archivar significa archivar, muchas funciones avanzadas ya no son compatibles y solo se admiten las funciones de inserción y consulta más básicas. Antes de MySQL 5.5, Archive no admitía índices, pero comenzó a admitir índices en MySQL 5.5 y versiones posteriores. Archive tiene un buen mecanismo de compresión. Utiliza la biblioteca de compresión zlib y comprime registros en tiempo real cuando se solicitan, por lo que a menudo se usa como repositorio.
Cuatro: Selección de motores de almacenamiento
Los diferentes motores de almacenamiento tienen sus propias funciones para adaptarse a las diferentes necesidades, como se muestra en la siguiente tabla:
Función p>
Si desea proporcionar capacidades de transacciones seguras (compatibles con ACID) para confirmación, reversión, recuperación de fallas y requiere control de concurrencia, InnoDB es una buena opción. Si la tabla de datos se utiliza principalmente para insertar y consultar registros, el motor MyISAM puede proporcionar una mayor eficiencia de procesamiento. Los índices de MYISAM están separados de los datos y sus índices se comprimen para utilizar mejor la memoria. Por lo tanto, su rendimiento de consultas es significativamente mejor que el de INNODB. Comprimir el índice también puede ahorrar algo de espacio en disco. MYISAM tiene una función de indexación de texto completo, que puede optimizar en gran medida la eficiencia de las consultas LIKE. Si solo realiza operaciones INSERT y SELECT, puede elegir Archive. Archive admite operaciones de inserción de alta concurrencia, pero no es seguro para transacciones. Archive es muy adecuado para almacenar datos archivados, como información de registro. Para algunas operaciones integrales en registros, generalmente se usa el motor de almacenamiento MERGE. Los motores de almacenamiento de memoria se utilizan generalmente en las siguientes situaciones: 1) Los datos de destino son pequeños y se accede a ellos con mucha frecuencia. Los datos se almacenan en la memoria, por lo que provocará el uso de la memoria. Puede controlar el tamaño de la tabla de memoria a través del parámetro max_heap_table_size. Después de configurar este parámetro, puede limitar el tamaño máximo de la tabla de memoria.
2) Si los datos son temporales y deben estar disponibles inmediatamente, se pueden almacenar en una tabla de Memoria.
3) La pérdida repentina de datos almacenados en la tabla de Memoria no tendrá un impacto negativo sustancial en los servicios de la aplicación.
4) Si los datos solo se almacenan temporalmente, la cantidad de datos no es grande y no se requiere una alta seguridad de los datos, entonces puede elegir el motor de memoria para almacenar los datos en la memoria y MySQL lo utiliza como consulta de almacenamiento Tabla temporal para resultados intermedios.
Cinco: Resumen
Al diseñar una base de datos, es necesario elegir con flexibilidad qué motor utilizar. Las diferentes tablas de una base de datos pueden utilizar una variedad de motores diferentes para satisfacer las necesidades reales y de rendimiento. Utilice un motor de almacenamiento adecuado para mejorar enormemente el rendimiento de toda la base de datos.
Blog de referencia: blogs.com/wcwen1990/p/6655416.htmlCopyright: este artículo es un artículo original del blogger de CSDN "Network Yangzi" y sigue el acuerdo de derechos de autor CC 4.0 BY-SA. Adjunte un enlace. al texto original para su reimpresión y esta declaración. Enlace original: /CSDN__LYY/article/details/80844606 [Volver a publicar] Introducción a los motores de almacenamiento comunes de MySQL (InnoDB, MyISAM, MEMORY, MERGE, ARCHIVE) y cómo elegir etiquetas: formato de datos de miembro, lectura fantasma, clave primaria en forma de árbol, completa arco de búsqueda de texto