Introducción detallada a los cuatro motores de almacenamiento comúnmente utilizados en Mysql y cómo elegirlos correctamente
1. Tablas seguras para transacciones:
2 Tablas no seguras para transacciones: MyISAM, MEMORY, MERGE, EXAMPLE, NDB Cluster. , ARCHIVO, CSV , BLACKHOLE, FEDERADO, etc.
El motor de almacenamiento predeterminado de MySQL es MyISAM (por defecto InnoDB en la versión 5.7).
El archivo de configuración puede establecer los parámetros predeterminados del motor de almacenamiento: default-table-type.
Consultar los motores de almacenamiento compatibles con la base de datos actual:
mostrar motores;
mostrar variables como 'have'; p>
mostrar variables como 'have'; Ver el motor de almacenamiento predeterminado actual:
mostrar variables como 'table_type'; Especificar el motor de almacenamiento al crear una nueva tabla:
crear tabla(...) motor=MyISAM La siguiente es una introducción detallada a cuatro motores de almacenamiento de uso común: MyISAM, InnoDB, MEMORY y MERGE.
1. MyISAM
1. Archivos de datos:
Las tablas de datos MyISAM se almacenan en tres archivos en el disco y sus nombres de archivo son los mismos que los de MyISAM. nombres de tablas. y tienen la extensión:
Son:
Las tablas de datos MyISAM se almacenan en tres archivos. Son:
(1).frm: almacena la definición de la estructura de la tabla de datos.
(2).MYD: Almacenar datos de la tabla.
(3).MYI: índice de la tabla de almacenamiento.
Los archivos de datos y los archivos de índice se pueden colocar en diferentes directorios para distribuir IO de manera uniforme y lograr velocidades más rápidas. Al crear una tabla, debe especificar las rutas a los archivos de índice y archivos de datos a través del directorio de datos y las declaraciones del directorio de índice. (
Por diversas razones, las tablas de tipo MyISAM pueden dañarse. Las tablas dañadas pueden volverse inaccesibles, solicitar reparación o devolver resultados incorrectos cuando se accede a ellas. Puede utilizar la instrucción check table para comprobar el estado de un MyISAM estado de salud de la tabla y use la instrucción de tabla de reparación para reparar la tabla MyISAM dañada
2 Formato de almacenamiento:
(1) Tabla estática (predeterminada): la longitud del campo no es variable. (cada registro). Almacenamiento muy rápido, fácil de almacenar en caché y fácil de recuperar en caso de falla, generalmente ocupa más espacio que las tablas dinámicas.
(2) Las tablas dinámicas ocupan relativamente poco. espacio, pero se actualizan y eliminan con frecuencia. Los registros se fragmentarán y la tabla de optimización o el comando myisamchk -r deben ejecutarse periódicamente para mejorar el rendimiento, y la recuperación en caso de falla es difícil
(3) Tabla comprimida: creada con la herramienta myisampack, ocupa mucho espacio en el disco. Pequeño Debido a que cada registro se comprime individualmente, solo hay una pequeña sobrecarga de acceso al almacenar datos de la tabla estática. , el espacio suplementario se define de acuerdo con el ancho de la columna y se devuelve a la aplicación. El programa lo elimina antes. Si originalmente hay espacios después del contenido que debe almacenarse, los espacios también se eliminarán cuando se devuelva el resultado. (Este es en realidad el comportamiento del tipo de datos char. Lo mismo ocurrirá con las tablas dinámicas que usan este tipo de datos. problema)
(Las tablas estáticas y las tablas dinámicas se seleccionarán automáticamente según los tipos de columnas utilizadas )
3.
3. Ventajas y desventajas:
(1) Ventajas: Acceso rápido
(2) No. transacciones de soporte o claves foráneas.
4. Aplicabilidad:
Si la aplicación se basa principalmente en operaciones de lectura e inserción, con solo una pequeña cantidad de operaciones de actualización y eliminación, y los requisitos de integridad y concurrencia de las transacciones son no es muy alto, entonces es muy apropiado elegir este motor de almacenamiento. myISAM es uno de los motores de almacenamiento más utilizados en entornos web, de almacenamiento de datos y otros entornos de aplicaciones.
II.InnoDB
1. Método de almacenamiento:
Hay dos formas de almacenar tablas e índices en InnoDB:
(1 ) Utilice *** disfrute del almacenamiento del espacio de tabla: la estructura de la tabla creada de esta manera se almacena en el archivo .frm, y los datos y los índices se almacenan en los espacios de tabla definidos por innodb_data_home_dir e innodb_data_file_path. Estos espacios de tabla pueden ser varios archivos.
(2) Utilice almacenamiento de espacio de tablas múltiples: la estructura de la tabla creada de esta manera todavía se almacena en el archivo .frm, pero los datos y los índices de cada tabla se almacenan en el archivo .idb por separado. Si es una tabla particionada, cada partición corresponde a un archivo .idb separado y el nombre del archivo es "nombre de tabla nombre de partición". Al crear una partición, puede especificar la ubicación del archivo de datos de cada partición para que la IO de la misma. La tabla se puede distribuir uniformemente en varios en un disco.
Para utilizar el almacenamiento de espacio de varias tablas, debe configurar el parámetro innodb_file_per_table y reiniciar el servidor antes de que surta efecto, y solo tendrá efecto en las tablas recién creadas. No hay límite de tamaño para archivos de datos en múltiples espacios de tablas. No es necesario establecer el tamaño inicial ni parámetros como el límite máximo de archivo y el tamaño extendido. Incluso en el método de almacenamiento de espacio de tablas múltiples, el espacio de tabla ***disfrutar sigue siendo necesario. InnoDB colocará el diccionario de datos interno y el registro de trabajo en este archivo, por lo que cuando se utiliza la función de espacio de tablas múltiples para hacer una copia de seguridad de la tabla. Copie el .idb directamente. Los archivos no funcionarán.
ALTER TABLE tbl_name DISCARD TABLESPACE;
ALTER TABLE tbl_name IMPORT TABLESPACE; pero esto solo se puede restaurar a la base de datos original donde se encuentra la tabla si necesita restaurar a otras bases de datos. , debe usar mysqldump y mysqlimport para lograrlo.
2. Archivos de datos:
Los archivos de datos de InnoDB están determinados por el método de almacenamiento de la tabla.
(1) *** Disfrute del archivo de espacio de tabla: definido por los parámetros innodb_data_home_dir e innodb_data_file_path, utilizado para almacenar diccionarios de datos, registros, etc.
(2).frm: definición de estructura de tabla de almacenamiento.
(3).idb: se utiliza para almacenar datos de tabla e índices cuando se usa almacenamiento de espacio de tabla múltiple; cuando se usa *** para disfrutar del almacenamiento de espacio de tabla, este archivo no está disponible.
3. Restricciones de clave externa:
InnoDB es el único motor MySQL que admite restricciones de clave externa. Las restricciones de clave externa permiten que la propia base de datos garantice la integridad y coherencia de los datos a través de claves externas, pero la introducción de claves externas puede reducir la velocidad y el rendimiento. Al crear una clave externa, la tabla principal debe tener un índice correspondiente y la tabla secundaria creará automáticamente un índice correspondiente al crear una clave externa.
Ejemplo de uso de restricciones de clave externa:
Crear tabla `dep` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
CLAVE PRIMARIA (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE; TABLA `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
` name` varchar(20) DEFAULT NULL,
`dep_id` smallint(6) NOT NULL,
CLAVE PRIMARIA (`id`),
CLAVE `idx_fk_dep_id` (`dep_id`),
CONSTRAINT `fk_emp_dep` CLAVE EXTRANJERA (`dep_id`) REFERENCIAS `dep` (`id`) EN CASCADA DE ACTUALIZACIÓN
)ENGINE=InnoDB DEFAULT CHARSET=utf8 CLAVE: Defina el nombre de la restricción del índice.
RESTRICCIÓN: Define el nombre de la restricción de clave externa. (Debe ser único en la base de datos; si no se especifica, el sistema generará automáticamente el nombre de la restricción)
ACTIVADO: especifique el impacto de las operaciones de la tabla principal en la tabla secundaria (el valor predeterminado no definido es restricción).
Restringido y no operativo: si la tabla secundaria tiene registros relacionados, la tabla principal no se puede actualizar ni eliminar.
En cascada: Al actualizar o eliminar la tabla principal, también se actualizan o eliminan los registros correspondientes de la tabla secundaria.
Establecer en vacío: cuando la tabla principal se actualiza o elimina, los campos correspondientes de la tabla secundaria se establecerán en vacío.
Cuando otra tabla hace referencia a la tabla creada mediante una clave externa, está prohibido eliminar el índice correspondiente o la clave primaria de la tabla. Al importar datos de varias tablas, si necesita ignorar el orden de importación de las tablas, puede deshabilitar temporalmente la verificación de claves externas al realizar operaciones de carga de datos y cambiar tablas, también puede deshabilitar temporalmente las restricciones de claves externas para acelerar el procesamiento.
Cerrar el comando:
set Foreign_key_checks=0; abrir el comando:
set Foreign_key_checks=1 4. Ventajas y desventajas:
( 1) Ventajas: Proporciona seguridad en las transacciones mediante capacidades de confirmación, reversión y recuperación ante fallos.
(2) Desventajas: la eficiencia del procesamiento de escritura de InnoDB es menor que MyISAM y requiere más espacio en disco para retener datos e índices.
5. Aplicabilidad:
Si la aplicación tiene requisitos relativamente altos para la integridad de las transacciones y requiere coherencia de los datos en condiciones concurrentes, y las operaciones de datos incluyen inserción y consulta además de un gran número. de operaciones de actualización y eliminación, entonces el motor de almacenamiento InnoDB debería ser una opción más adecuada. Además de garantizar que las transacciones no fallen por completo, el motor de almacenamiento InnoDB puede reducir eficazmente los bloqueos provocados por eliminaciones y actualizaciones. Además de reducir eficazmente los bloqueos causados por eliminaciones y actualizaciones, el motor de almacenamiento InnoDB también puede garantizar que las transacciones se confirmen y reviertan por completo. InnoDB es una opción adecuada para sistemas que requieren una mayor precisión de los datos, como los sistemas de facturación o financieros.
3. MEMORIA
1. Archivos de datos:
Cada tabla de MEMORIA corresponde a un solo archivo de disco .frm, que se utiliza para almacenar la definición de estructura de La tabla se almacena en la memoria. Utilice índices HASH en lugar de índices BTREE de forma predeterminada.
2. Ventajas y Desventajas:
(1) Ventajas: Debido a que los datos están en la memoria, la velocidad de acceso es muy rápida.
(2) Desventajas: una vez que se cierra el servicio, los datos de la tabla se perderán; el tamaño de la tabla es limitado.
3. Aplicación:
El motor de almacenamiento de memoria se utiliza principalmente para tablas de códigos cuyo contenido cambia con poca frecuencia, o como una tabla de resultados intermedia para operaciones estadísticas, para facilitar el análisis efectivo de resultados intermedios. Obtenga los resultados estadísticos finales.
4. FUSIÓN
1. Principio del motor:
El motor de almacenamiento de fusión es una combinación de un conjunto de tablas MyISAM. Estas tablas MyISAM deben ser estructuralmente idénticas. La tabla de combinación en sí no tiene datos. La tabla de tipo de combinación puede realizar operaciones de consulta, actualización y eliminación. Estas operaciones en realidad se realizan en la tabla MyISAM interna.
La definición de operaciones de inserción en tablas fusionadas se realiza a través de la cláusula insert_method: usar first o last permite que las operaciones de inserción se apliquen a la primera o última tabla en consecuencia, sin definirla ni definirla como No significa Insertar La operación no se puede realizar en esta tabla fusionada. Eliminar la tabla fusionada solo eliminará la definición fusionada y no tiene ningún impacto en las tablas internas.
2. Archivos de datos:
(1) .frm: definición de tabla de almacenamiento.
(2).MRG: almacena información sobre la tabla fusionada, incluido el contenido de la tabla fusionada y la base para insertar nuevos datos. Puede modificar la tabla de combinación modificando el archivo .mrg, pero la modificación debe actualizarse actualizando la tabla.
3. Ejemplo de uso:
Crear tabla `m1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
CLAVE PRIMARIA (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREAR TABLA ` m2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar (20) DEFAULT NULL,
CLAVE PRIMARIA (`id `)
)ENGINE=MyISAM DEFAULT CHARSET=UTF8;
CREAR TABLA `m` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
CLAVE PRIMARIA (`id`)
)ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_ METHOD=ÚLTIMA UNIÓN =(`m1`, `m2`); 4. Aplicación:
Se utiliza para fusionar lógicamente una serie de tablas MyISAM equivalentes y hacer referencia a ellas como un solo objeto. La ventaja de las tablas MERGE es que pueden superar el límite de tamaño de una sola tabla MyISAM y, al distribuir diferentes tablas en varios discos, la eficiencia del acceso a las tablas MERGE se puede mejorar de manera efectiva. Esto es ideal para entornos VLDB como almacenes de datos.