Tiempo de importación en MySQL

Una de las formas para importar datos en MySQL es el comando LOAD DATA INFILE. Es más rápido que un dump, ya que se leen los datos en bruto, en lugar de sentencias SQL.

El tiempo de importación depende del motor que use la tabla, por ejemplo, MyISAM puede ser 40 times más rápido que Innodb. Vamos a probarlo:

Preparación

Voy a utilizar MySQL 5.1.36 (64 bits MacOS X) para hacer las pruebas. Necesitaré una tabla grande, así que partiré de la tabla City de la Base de datos world y crearé una tabla más grande que se llame «city_huge»:

CREATE TABLE city_huge LIKE CITY;

INSERT INTO city_huge 
    SELECT NULL, name, CountryCode, District, Population FROM city;
# Ejecuta 100 veces esta sentencia,
# así city_huge será 100 veces más grande que city.
# Un consejo, usa un script, una tabla temporal, 
# un procedimiento almacenado...
# o dile a tu mono que lo haga por tí.

SELECT COUNT(*) FROM city_huge;
#   +----------+
#   | COUNT(*) |
#   +----------+
#   |   407900 | 
#   +----------+

# Haz una copia de seguridad de tu tabla:
SELECT * FROM city_huge INTO OUTFILE 'city_huge.bak';

# Trunca la tabla, empezaremos con la tabla vacía.
TRUNCATE TABLE city_huge;

Importar diréctamente

Vamos a reestablecer la copia de seguridad en la tabla city_huge, usando MyISAM, InnoDB y MEMORY:

LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (5.85 sec)
# Eso era usando MyISAM.

# Vaciamos la tabla y cambiamos el motor a InnoDB:
TRUNCATE TABLE city_huge;
ALTER TABLE city_huge ENGINE = InnoDB;
LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (3 min 59.53 sec)

# Con Memory:
TRUNCATE TABLE city_huge;
SET @@max_heap_size= 128 * 1024 * 1024;
ALTER TABLE city_huge ENGINE = MEMORY;
LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (2.18 sec)

MyISAM	0:5.85
InnoDB	3:59.53
MEMORY	0:2.18

Wow, MyISAM es unas 40 veces más rápido. MEMORY es incluso más rápido.

Alter Table

De acuerdo, InnoDB es un poco lento, pero a veces no se puede usar otro motor de bases de datos. En esos casos, podemos intentar importar en un motor y luego cambiar el motor de la tabla a InnoDB.

Sería algo así:

TRUNCATE TABLE city_huge;
ALTER TABLE city_huge ENGINE = MyISAM;
LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (5.85 sec)
ALTER TABLE city_huge ENGINE = InnoDB;
#   Query OK, ... (4 min 11.24 sec)

Ooops, 4 min 17 sec es más que 3 min 59 sec.

Vamos a intentarlo con Memory:

TRUNCATE TABLE city_huge;
SET @@max_heap_size= 128 * 1024 * 1024;
ALTER TABLE city_huge ENGINE = MEMORY;
LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (2.18 sec)
ALTER TABLE city_huge ENGINE = InnoDB;
# Query OK, ... (3 min 28.39 sec)

Sí, 3 min 31 sec es más rápido que 4 min. 30 seconds, es cerca de un 10% más rápido.

Exención de responsabilidad

Esta prueba se ha hecho usando la configuración por defecto, estoy seguro de que ajustando InnoDB se mejorarán los resultados. Tampoco soy muy estricto con mis pruebas, así que te animo a que prepares tus propias pruebas.

Esta solución no es algo aplicable siempre, el motor MEMORY necesita grandes cantidades de memoria. Esta NO siempre es la mejor manera de hacer las cosas, InnoDB debería ser más rápido por sí sólo.

De todas formas, es divertido encontrar estos comportamientos no tan obvios :D.

Leer Comentarios versión Inglesa