Cómo insertar datos rápidamente en MariaDB

Este artículo describe diferentes técnicas para insertar datos rápidamente en MariaDB.

Fondo

Al insertar nuevos datos en MariaDB, las cosas que toman tiempo son: (en orden de importancia):

• Sincronizar datos con el disco (como parte del final de las transacciones)
• Añadiendo nuevo keys. Cuanto mayor sea el índice, más tiempo se necesitará para mantener keys actualizado.
• Contrastando con el extranjero keys (si existen).
• Añadiendo filas al motor de almacenamiento.
• Envío de datos al servidor.

A continuación se describen las diferentes técnicas (nuevamente, en orden de importancia) que puede utilizar para insertar datos rápidamente en una tabla.

Inhabilitando keys

Puede deshabilitar temporalmente la actualización de índices no únicos. Esto es principalmente útil cuando hay cero (o muy pocas) filas en la tabla en la que está insertando datos.

ALTERTABLE table_name DISABLEKEYS;BEGIN;... inserting datawithINSERTorLOADDATA....COMMIT;ALTERTABLE table_name ENABLEKEYS;

En muchos motores de almacenamiento (al menos MyISAM y Aria), ENABLE KEYS funciona escaneando los datos de la fila y recopilando keys, ordenándolos y luego creando los bloques de índice. Este es un orden de magnitud más rápido que crear el índice una fila a la vez y también usa menos key memoria intermedia.

Nota: Cuando inserta en un mesa vacía con INSERT o LOAD DATA, MariaDB automáticamente hace un DISABLE KEYS antes y un ENABLE KEYS después.

Al insertar grandes cantidades de datos, las comprobaciones de integridad consumen mucho tiempo. Es posible deshabilitar el UNIQUE índices y el extranjero keys cheques usando el unique_checks y el foreign_key_checks variables del sistema:

SET @@session.unique_checks=0;SET @@session.foreign_key_checks=0;

Para las tablas XtraDB / InnoDB, el modo de bloqueo AUTO_INCREMENT se puede establecer temporalmente en 2, que es la configuración más rápida:

SET @@global.innodb_autoinc_lock_mode=2;

Además, si la tabla tiene desencadenadores INSERT o PERSISTENT columnas, es posible que desee eliminarlas, insertar todos los datos y volver a crearlas.

Cargando archivos de texto

los la manera más rápida para insertar datos en MariaDB es a través del LOAD DATA INFILE mando.

La forma más simple del comando es:

LOADDATAINFILE'file_name'INTOTABLE table_name;

También puede leer un archivo localmente en la máquina donde se ejecuta el cliente usando:

LOADDATALOCALINFILE'file_name'INTOTABLE table_name;

Esto no es tan rápido como leer el archivo en el lado del servidor, pero la diferencia no es tan grande.

LOAD DATA INFILE es muy rápido porque:

1. no hay análisis de SQL.
2. los datos se leen en grandes bloques.
3. si la tabla está vacía al comienzo de la operación, todos los índices no únicos se deshabilitan durante la operación.
4. Se le dice al motor que primero almacene las filas en caché y luego las inserte en bloques grandes (al menos MyISAM y Aria lo admiten).
5. para tablas vacías, algunos motores transaccionales (como Aria) no registran los datos insertados en el registro de transacciones porque uno puede revertir la operación simplemente haciendo un TRUNCATE en la tabla.

Debido a las ventajas de velocidad anteriores, hay muchos casos en los que necesita insertar muchos filas a la vez, donde puede ser más rápido crear un archivo localmente, agregar las filas allí y luego usar LOAD DATA INFILE para cargarlos; en comparación con el uso INSERT para insertar las filas.

En MariaDB 5.3 también obtendrá informes de progreso para LOAD DATA INFILE.

mysqlimport

Puede importar muchos archivos en paralelo con mysqlimport. Por ejemplo:

mysqlimport --use-threads=10 database text-file-name [text-file-name...]

Internamente, mysqlimport usa LOAD DATA INFILE para leer los datos.

Insertar datos con declaraciones INSERT

Usando grandes transacciones

Cuando haga muchas inserciones seguidas, debe envolverlas con BEGIN / END para evitar realizar una transacción completa (que incluye una sincronización de disco) para cada fila. Por ejemplo, hacer un comienzo / final cada 1000 inserciones acelerará sus inserciones en casi 1000 veces.

BEGIN;INSERT...INSERT...END;BEGIN;INSERT...INSERT...END;...

La razón por la que puede querer tener muchos BEGIN/END declaraciones en lugar de solo una es que la primera utilizará menos espacio de registro de transacciones.

Inserciones de valor múltiple

Puede insertar muchas filas a la vez con inserciones de filas de varios valores:

INSERTINTO table_name values(1,"row 1"),(2,"row 2"),...;

El límite de la cantidad de datos que puede tener en una declaración está controlado por el max_allowed_packet variable de servidor.

Insertar datos en varias tablas a la vez

Si necesita insertar datos en varias tablas a la vez, la mejor manera de hacerlo es habilitar declaraciones de varias filas y enviar muchas inserciones al servidor a la vez:

INSERTINTO table_name_1 (auto_increment_key,data)VALUES(NULL,"row 1");INSERTINTO table_name_2 (auto_increment, reference,data)values(NULL, LAST_INSERT_ID(),"row 2");

LAST_INSERT_ID() es una función que devuelve el último auto_increment valor insertado.

Por defecto, la línea de comando mysql El cliente enviará lo anterior como declaraciones múltiples.

Para probar esto en el mysql cliente tienes que hacer:

delimiter;;select1;select2;;delimiter;

Nota: Para que las declaraciones de consultas múltiples funcionen, su cliente debe especificar el CLIENT_MULTI_STATEMENTS bandera a mysql_real_connect().

Variables de servidor que se pueden usar para ajustar la velocidad de inserción

Consulte las opciones de mysqld para obtener la lista completa de variables del servidor.

El contenido reproducido en este sitio es propiedad de sus respectivos dueños, y MariaDB no revisa este contenido con anticipación. Los puntos de vista, la información y las opiniones expresadas por este contenido no representan necesariamente las de MariaDB o de cualquier otra parte.