Detalla los cuatro motores de almacenamiento comunes en Mysql y cómo elegirlos correctamente.

1. Tabla de seguridad de transacciones: BDB InnoDB.

2. Tablas seguras sin transacciones: MyISAM, MEMORY, MERGE, EXAMPLE, NDB cluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED, etc.

El motor de almacenamiento predeterminado de MySQL es MyISAM (el predeterminado es InnoDB en la versión 5.7).

Establezca los parámetros del motor de almacenamiento predeterminado en el archivo de configuración default-table-type.

Consultar el motor de almacenamiento admitido por la base de datos actual:

Mostrar el motor;

Mostrar variables similares a "have"; Ver el motor de almacenamiento predeterminado actual:

Muestra variables similares a "table_type"; especifica el motor de almacenamiento al crear una nueva tabla:

Crear tabla (...) motor = MyISAM Los siguientes son cuatro motores de almacenamiento de uso común. : MyISAM, InnoDB, MEMORIA y FUSIONAR.

1. Misam

1. Archivos de datos:

La tabla de datos MyISAM se almacena en tres archivos en el disco y los nombres de todos los archivos son. Igual que el nombre de la tabla, con la extensión:

(1).frm: almacena la definición de la estructura de la tabla de datos.

(2).MYD: almacena datos de la tabla.

(3).MYI: Índice de la tabla de almacenamiento.

Entre ellos, los archivos de datos y los archivos de índice se pueden colocar en diferentes directorios, y IO se puede distribuir uniformemente para obtener velocidades más rápidas. Especifique las rutas de los archivos de índice y de datos, que deben especificarse mediante las instrucciones del directorio de datos y del directorio de índice al crear la tabla. (La ruta del archivo debe ser absoluta y accesible)

La tabla de tipo MyISAM puede estar dañada por varias razones. La tabla dañada puede ser inaccesible, lo que indica que es necesario repararla o devolver un resultado de error después de acceder a ella. . Puede usar la declaración de tabla de verificación para verificar el estado de una tabla MyISAM y usar la declaración de tabla de reparación para reparar una tabla MyISAM dañada.

2. Formato de almacenamiento:

(1) Tabla estática (predeterminada): la longitud del campo permanece sin cambios (cada registro tiene una longitud fija). El almacenamiento es muy rápido, fácil de almacenar en caché y fácil de recuperar en caso de fallas; generalmente ocupa más espacio que una tabla dinámica.

(2) Tabla dinámica: ocupa un espacio relativamente pequeño, pero las actualizaciones y eliminaciones frecuentes de registros provocarán fragmentación. La tabla de optimización o el comando myisamchk -r deben ejecutarse periódicamente para mejorar el rendimiento. difícil de recuperar cuando ocurre una falla.

(3) Tabla de compresión: Creada con la herramienta myisampack, ocupa muy poco espacio en disco. Debido a que cada registro se comprime individualmente, solo hay una pequeña sobrecarga de acceso.

Los datos de la tabla estática se almacenan con espacios en blanco que se rellenan según las definiciones de ancho de columna, y estos espacios en blanco se eliminan antes de que los datos se devuelvan a la aplicación. Si hay espacios después del contenido que se va a guardar, estos espacios se eliminarán cuando se devuelva el resultado. (De hecho, es el comportamiento del tipo de datos char. Este problema también ocurrirá si existe este tipo de datos en la tabla dinámica).

(Las tablas estáticas y las tablas dinámicas se seleccionan automáticamente según el tipo de columna utilizado.)

3. Ventajas y desventajas:

(1) Ventajas: Velocidad de acceso rápida.

(2) No se admiten transacciones ni claves externas.

4. Aplicación:

Si la aplicación se compone principalmente de operaciones de lectura e inserción, con pocas operaciones de actualización y eliminación, y los requisitos de integridad y concurrencia de las transacciones no son muy altos, entonces elija Este motor de almacenamiento es muy adecuado. MyISAM es uno de los motores de almacenamiento más utilizados en entornos de aplicaciones como la web y los almacenes de datos.

Segundo, InnoDB

1. Modo de almacenamiento:

InnoDB almacena tablas e índices de las dos formas siguientes:

(1) Disfrute del almacenamiento de espacios de tablas usando * * *: las estructuras de tablas creadas de esta manera se guardan en. Los archivos, datos e índices frm se guardan en espacios de tablas definidos por innodb_data_home_dir e innodb_data_file_path, que pueden ser varios archivos.

(2) Utilice almacenamiento de espacio de múltiples tablas: la estructura de la tabla creada de esta manera aún se guarda. frm, pero los datos e índices de cada tabla se guardan en. archivo idb. Si es una tabla particionada, cada partición corresponde a una tabla separada. El nombre del archivo es un archivo idb llamado "nombre de tabla nombre de partición". Puede especificar la ubicación de los archivos de datos de cada partición cuando crea las particiones para distribuir uniformemente la E/S de la tabla en varios discos.

Para utilizar el modo de almacenamiento de espacio de varias tablas, debe configurar el parámetro innodb_file_per_table y reiniciar el servidor para que surta efecto, y solo tendrá efecto en las tablas recién creadas. No hay límite de tamaño para archivos de datos con múltiples espacios de tabla y no es necesario establecer parámetros como el tamaño inicial, el límite máximo y el tamaño extendido del archivo. Incluso en el modo de almacenamiento de múltiples espacios de mesa, aún necesita * * * disfrutar del espacio de la mesa. InnoDB coloca el diccionario de datos interno y el registro de trabajo en este archivo, por lo que no es posible copiarlo. Al realizar una copia de seguridad de tablas con múltiples características de espacio de tabla, utilice el archivo idb directamente. Puede restaurar la copia de seguridad de los datos en la base de datos mediante el comando:

ALTER TABLE tbl_name descarta el espacio de tabla;

ALTER TABLE tbl_name importa el espacio de tabla pero este solo se puede restaurar en; la base de datos original de la tabla. Si necesita restaurar a otras bases de datos, debe usar mysqldump y mysqlimport.

2. Archivos de datos:

Los archivos de datos de InnoDB están determinados por el modo de almacenamiento de la tabla.

(1)***Archivo de espacio de tabla: definido por los parámetros innodb_data_home_dir e innodb_data_file_path, utilizado para almacenar el diccionario y el registro de datos.

(2).frm: Definición de estructura de tabla de almacenamiento.

(3).idb: se utiliza para almacenar datos e índices de tablas cuando se utiliza el modo de almacenamiento de espacio de múltiples tablas. Si utiliza almacenamiento de espacios de tabla, no existe dicho archivo.

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 base de datos garantice la integridad y coherencia de los datos a través de claves externas, pero la introducción de claves externas 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,

` nombre ' varchar(20) por defecto es NULL,

Clave principal (` id `)

)ENGINE = InnoDB DEFAULT CHARSET = utf8;

Crear tabla "emp" (

` id ' int(11)NOT NULL AUTO _ INCREMENT,

` name ' varchar(20) por defecto es NULL,

`dep_id` smallint(6) no está vacío,

Clave principal (` id `),

Palabra clave ` idx_fk_dep_id` (`dep_id `),

La restricción ` fk_emp_dep ` clave externa (` dep_id `) se refiere a ` dep` (`id `) en la cascada de actualización

)ENGINE = InnoDB DEFAULT CHARSET = utf8 KEY: Definir el nombre de la restricción de índice.

Restricción: Defina 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 un nombre de restricción).

ON: especifica el impacto de las operaciones de la tabla principal en la tabla secundaria (la restricción no está definida de forma predeterminada) .

Restricciones y sin operaciones: Cuando la tabla secundaria tiene registros relacionados, la tabla principal no se puede actualizar ni eliminar.

En cascada: Cuando se actualiza o elimina la tabla principal, también se actualizan o eliminan los registros correspondientes a la tabla secundaria.

Establecer nulo: cuando la tabla principal se actualiza o elimina, el campo correspondiente de la tabla secundaria se establece en nulo.

Cuando una clave externa creada por otra tabla hace referencia a una tabla, el índice correspondiente o la clave principal de la tabla no se puede eliminar. Al importar datos de varias tablas, si necesita ignorar el orden de importación de las tablas, puede desactivar temporalmente las comprobaciones de claves externas al realizar operaciones de carga de datos y modificar tablas; también puede desactivar temporalmente las restricciones de claves externas para acelerar el procesamiento; .

Comando de cierre:

establezca controles de _ clave externa = 0; comando de apertura:

establezca controles de _ clave externa = 1. Ventajas y desventajas; :

(1) Ventajas: proporciona la capacidad de confirmar, revertir y recuperar fallos para la seguridad de las transacciones.

(2) Desventajas: en comparación con MyISAM, InnoDB tiene una menor eficiencia de escritura y ocupa más espacio en disco para guardar datos e índices.

5. Aplicación:

Si la aplicación tiene altos requisitos de integridad de transacciones y coherencia de datos en condiciones concurrentes, y las operaciones de datos incluyen muchas actualizaciones además de las operaciones de inserción, consulta y eliminación, entonces el motor de almacenamiento InnoDB debería ser una opción más adecuada. El motor de almacenamiento InnoDB no solo puede reducir eficazmente los bloqueos causados ​​por eliminaciones y actualizaciones, sino que también garantiza la confirmación y reversión completa de las transacciones. InnoDB es una opción adecuada para sistemas que requieren alta precisión de datos, como sistemas de facturación o sistemas financieros.

En tercer lugar, la memoria

1. Archivos de datos:

Cada tabla de memoria corresponde a una sola. Archivo de disco frm, utilizado para almacenar la definición de estructura de la tabla, y los datos de la tabla se almacenan en la memoria. De forma predeterminada, se utiliza un índice hash en lugar de un índice BTREE.

2. Ventajas y Desventajas:

(1) Ventajas: La velocidad de acceso es rápida porque los datos se almacenan en la memoria.

(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 en memoria se utiliza principalmente para tablas de códigos cuyo contenido no cambia con frecuencia, o como tabla de resultados intermedia para operaciones estadísticas, para analizar de manera conveniente y eficiente. los resultados intermedios y obtener los resultados estadísticos finales.

Cuarto, fusionar

1. Principio del motor:

El motor de almacenamiento de fusión es una combinación de un conjunto de tablas MyISAM, y estas tablas deben tener las mismas. estructura. La tabla de combinación en sí no tiene datos y la tabla de tipo de combinación se puede consultar, actualizar y eliminar. En realidad, estas operaciones se realizan internamente en tablas MyISAM reales.

Defina la operación de inserción de la tabla fusionada a través de la cláusula insert_method: use primero o último para que la operación de inserción se aplique a la primera o última tabla en consecuencia, si no está definida o definida como No, significa; que no se puede utilizar en Realice una operación de inserción en esta tabla fusionada. Eliminar la tabla de combinación solo eliminará la definición de combinación y no tiene ningún impacto en las tablas internas.

2. Archivo de datos:

(1).frm: definición de tabla de almacenamiento.

(2).MRG: almacena la información de la tabla fusionada, incluidas las tablas que la componen y la base para insertar nuevos datos. Puedes modificarlo. mrg, pero después de la modificación debe actualizarlo actualizando la tabla.

3. Ejemplo de uso:

Crear tabla "m1" (

` id ' int(11)NOT NULL AUTO _ INCREMENT,

` name ' varchar(20) por defecto es NULL,

Clave principal (` id `)

)ENGINE=Juego de caracteres predeterminado de MyISAM = utf8

Crear tabla "m2" (

` id ' int(11)NOT NULL AUTO _ INCREMENT,

` name ' varchar(20) por defecto es NULL,

Clave principal (` id `)

)ENGINE=Juego de caracteres predeterminado de MyISAM = utf8

Crear tabla "m" (

` id ' int(11 )NOT NULL AUTO _ INCREMENT,

` nombre ' varchar(20) por defecto es NULL,

Clave principal (` id `)

)ENGINE = MRG _ MyISAM DEFAULT CHARSET = utf8 INSERT _ METHOD = LAST UNION =(` m 1 ', ` m2 ' 4. Aplicación:

Se utiliza para combinar lógicamente una serie de tablas MyISAM equivalentes y tratarlas como una sola; objeto Cita. La ventaja de fusionar tablas es que puede superar el límite de tamaño de una única tabla MyISAM. Al distribuir diferentes tablas en varios discos, la eficiencia del acceso a la tabla fusionada se puede mejorar de manera efectiva. Esto es ideal para entornos VLDB como almacenes de datos.