Inserción masiva de información en bases de datos

En muchas de las soluciones de software que se implementan es típico crear alguna funcionalidad que nos permita la carga o inserción de registros de fuentes externas. Esta información normalmente la esperamos en algún formato estructurado como JSON, XML aunque lo más común es aceptar un formato de texto plano con los datos separados por algún carácter especial; Nos referimos a un archivo de “valores separados por comas” o un CSV (por sus siglas en inglés).

Mencionaremos tres estrategias incluyendo pequeños fragmentos de código. Para todos los ejemplos usaremos:

• Motor de base de datos MySQL
• Código en PHP
• Tabla “users” con los campos de “name”, “email”, “phone”
• Archivo de entrada “data.csv” con información semejante a

Miss Zetta Gutmann,kohler.lila@friesen.com,+13122771420
Silas Wunsch,lcasper@bergnaum.com,463-871-8700
Kavon Kulas II,fleta.hickle@ruecker.com,+17044832707
Cleve Bartoletti,franecki.lurline@hotmail.com,+12832863752
Mr. Alf Kemmer,helmer.friesen@hotmail.com,580.542.9383
Serenity Mayert,smiller@hotmail.com,+1-854-357-3414
...

Estrategias de inserción

Inserción simple o directa

El más simple de los escenarios es la inserción directa de información. Este es la estrategia más simple de implementar y quizá, en muchos casos, sea más que suficiente si estamos hablando de volúmenes de información relativamente pequeños.

Esta estrategia consiste en ir leyendo la información e inmediatamente realizar la inserción de la misma en la base de datos. Básicamente estaríamos generando comandos de inserción del tipo INSERT INTO <t> (<c1>, <c2>, ... <cN> VALUES (<v1>, <v2>, ... <vN>) en dónde t representa el nombre de nuestra tabla en la base de datos, c1, c2,…cN representan los nombres de los campos en la tabla y v1, v2,… vN representan los valores de la información a insertar.

Si lo vemos en un fragmento de código simplificado en php tendríamos algo como lo siguiente:

$dsn = 'mysql:dbname=csv-import;host=127.0.0.1';
$user = 'root';
$password = '';
$filename = 'data.csv';

$pdo = new PDO($dsn, $user, $password);

$sqlStatement = "INSERT INTO users (name, email, phone) VALUES (?, ?, ?)";
$stmt= $pdo->prepare($sqlStatement);

$csvFile = fopen($filename, "r");

while (($values = fgetcsv($csvFile)) !== false) {
$stmt->execute($values);
}

fclose($csvFile);

Es una estrategia simple, directa y fácil de implementar. Sin embargo para volúmenes considerables de información se vuelve un poco lenta, pues por cada registro estamos realizando la ejecución de un comando (INSERT) hacia la base de datos.

Inserción por lotes (batch)

Haciendo una pequeña modificación a la estrategia anterior, podemos reducir considerablemente la cantidad de comando lanzados hacia la base de datos al momento de hacer la inserción de información. Esto mediante la agrupación de varios registros en un mismo comando.

En esta estrategia iremos leyendo la información del archivo de entrada e iremos almacenando en memoria un grupo de registros para posteriormente hacer la inserción con un mismo comando. Estaríamos generando comandos de inserción del tipo INSERT INTO <t> (<c1>, <c2>, ... <cN> VALUES (<r1v1>, <r1v2>, ... <r1vN>), (<r2v1>, <r2v2>, ... <r2vN>),... (<rMv1>, <rMv2>, ... <rMvN>) en dónde t representa el nombre de nuestra tabla en la base de datos, c1, c2,…cN representan los nombres de los campos en la tabla y r1v1, r1v2,… r1vN representan los valores de la información a insertar del primer registro, r2v1… los valores correspondientes al segundo registro y así sucesivamente hasta los valores correspondientes del último registro del lote a insertar.

Esto quiere decir que se ejecutará un sólo comando INSERT hacia la base de datos por cada M registros que se vayan a insertar a la misma; siendo M, el tamaño del lote (batch).

Pareciera que es casi lo mismo y en principio sí lo es; sin embargo este pequeño cambio tiene un efecto sumamente significativo en la velocidad de inserción de información.

Ejemplificado en un fragmento de código simplificado:

$dsn = 'mysql:dbname=csv-import;host=127.0.0.1';
$user = 'root';
$password = '';
$filename = 'data.csv';
$batchSize = 1000;

$pdo = new PDO($dsn, $user, $password);

$csvFile = fopen($filename, "r");

$batchValues = [];
while (($values = fgetcsv($csvFile)) !== false) {
  $batchValues[] = $values;
  if (sizeof($batchValues) == $batchSize) {
    $sqlStatement = "INSERT INTO users (name, email, phone) VALUES " . implode(',', array_fill(0, $batchSize, '(?,?,?)'));
    $stmt= $pdo->prepare($sqlStatement)->execute(array_merge(...$batchValues));
    $batchValues=[];
  }
}
if (sizeof($batchValues) > 0) {
  $sqlStatement = $sql = "INSERT INTO users (name, email, phone) VALUES " . implode(',', array_fill(0, sizeof($batchValues), '(?,?,?)'));
  $stmt= $pdo->prepare($sqlStatement);
  $stmt->execute($batchValues);
}

fclose($csvFile);

Esta estrategia es muy parecida y tiene sólo un par de consideraciones adicionales. Por un lado representa un incremento considerable en la velocidad de inserción de información, sin embargo los requerimientos de memoria para la ejecución se incrementan, pues es necesario mantener la información en memoria antes de enviarla a la base de datos.

Inserción vía “`LOAD DATA“`

Otra herramienta con la que podemos apoyarnos es directamente de las capacidades del motor de base de datos. Para el caso de MySQL este cuenta con comandos especialmente diseñados para este propósito LOAD DATA.

Viendo un ejemplo simplificado en código tenemos lo siguiente:

$dsn = 'mysql:dbname=csv-import;host=127.0.0.1';
$user = 'root';
$password = '';
$filename = 'data.csv';

$pdo = new PDO($dsn, $user, $password, [
  PDO::MYSQL_ATTR_LOCAL_INFILE => true
]);

$sqlStatement = "LOAD DATA LOCAL INFILE '{$filename}' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";

$pdo->exec($sqlStatement);

Con esta estrategia dejamos la responsabilidad de la inserción de información directamente al motor de base de datos; nosotros sólo somos responsables de proporcionar la información cruda y el motor de base de datos hará la ingesta de la misma.

La velocidad de inserción es muy superior a cualquiera de los otros dos métodos mencionados. De igual forma el requerimiento de memoria es mínimo. La única observación es que para que esto sea posible es necesario una configuración específica tanto en el cliente como en el servidor de la base de datos y esto en ocasiones no es posible pues no administramos o controlamos ambos lado de la implementación.

Esta es una tabla comparativa ejecutando el código mostrado en distintos escenarios. Se insertaron 10 mil, 100 mil y un millón de registros anotando tanto el tiempo de ejecución como la memoria consumida por el proceso.

Podemos ver que la mejor manera de insertar de forma masiva información en una base de datos es vía LOAD DATA, sin embargo cabe destacar que esto es sólo una referencia y cada proyecto tendrá sus requerimientos específicos en dónde haga mayor o menor sentido cada una de estas estrategias aquí mostradas.