disable - set foreign_key_checks 0 mysql server

MySQL deshabilitar y habilitar claves

Question

En mi base de datos MySQL, hay una tabla que tiene 2,000,000 de registros. Ahora, me gustaría insertar otros 6,000,000 de nuevos registros en esta tabla.

Para acelerar la inserción, pensé que debería usar las teclas de deshabilitar / habilitar de la siguiente manera:

ALTER TABLE cars DISABLE KEYS;
INSERT INTO cars ...
...
...
INSERT INTO cars ...
ALTER TABLE search_all_values ENABLE KEYS;
OPTIMIZE TABLE cars;

Pero de alguna manera siento que, las claves de deshabilitar / habilitar tendrían más sentido para ser utilizadas para la inserción de tablas vacías.

Mientras que en mi caso, ya tengo 2,000,000 de registros en la tabla, cuando ENABLE KEYS , mysql volverá a crear todos los índices (incluidos los registros existentes y los nuevos registros agregados) que probablemente no producirán una inserción de datos eficiente como un todo en mi caso. Como volver a crear todos los índices llevará mucho tiempo y probablemente también OPTIMIZE TABLE

Me gustaría preguntar su opinión acerca de ¿tengo razón y cómo puedo tener una inserción de datos eficiente en mi caso?

Answer 1

Indexar las nuevas claves tomará un tiempo. Depende de usted decidir si desea que se haga de una vez (deshabilitándolo primero) o de a uno por vez (manteniéndolo tal como está y dejándolo indexado a medida que se agrega cada registro).

Yo buscaría esto último, sin deshabilitar tus llaves. Si teme estresar mucho al servidor, puede intentar insertar en lotes, por ejemplo, solo una cierta cantidad de insertos por minuto.

Answer 2

Definitivamente debe elegir su enfoque según el tipo de motor ... optimizando para MyISAM o para InnoDB .

Recientemente ejecutamos un punto de referencia que compara diferentes formas de insertar datos y mide el tiempo transcurrido desde la inserción hasta que todos los índices se restauran por completo. Estaba en una mesa vacía, pero usamos hasta 10 millones de filas.

MyISAM con LOAD DATA INFILE y ALTER TABLE ... ENABLE/DISABLE KEYS ganó sin problemas en nuestra prueba (en un sistema Windows 7, MySQL 5.5.27 - ahora lo estamos probando en un sistema Linux).

ENABLE y DISABLE KEYS no funciona para InnoDB, solo es MyISAM. Para InnoDB, use SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0; si está seguro de que sus datos no contienen duplicados (no olvide configurarlos en 1 después de completar la carga).

No creo que necesite OPTIMIZE TABLE después de una inserción masiva: las filas de MySQL se ordenan por inserción y el índice se reconstruye de todos modos. No hay "fragmentación adicional" haciendo una inserción masiva.

Siéntase libre de comentar si cometí errores de hecho.

ACTUALIZACIÓN: de acuerdo con nuestros resultados de prueba más recientes y completos, el consejo para las teclas DISABLE / ENABLE es incorrecto.

Un compañero de trabajo hizo que un programa ejecutara múltiples pruebas diferentes, una tabla con InnoDB / MyISAM prellenado y vacío, velocidades de selección e inserciones con LOAD DATA LOCAL , INSERT INTO , REPLACE INTO y UPDATE , en tablas "densas" y "fragmentadas" (estoy no estoy muy seguro de cómo, creo que fue como DELETE FROM ... ORDER BY RAND() LIMIT ... con una semilla fija por lo que aún es comparable) e índices habilitados y diableados.

Lo probamos con muchas versiones diferentes de MySQL (5.0.27, 5.0.96, 5.1.algo, 5.5.27, 5.6.2) en Windows y Linux (aunque no las mismas versiones en ambos sistemas operativos). MyISAM solo ganó cuando la mesa estaba vacía. InnoDB fue más rápido cuando los datos ya estaban presentes y generalmente se desempeñó mejor (excepto en el espacio de disco duro: MyISAM es más pequeño en el disco).

Aún así, para beneficiarse realmente, tiene que probarlo usted mismo, con diferentes versiones, configuraciones diferentes y mucha paciencia, especialmente en lo que respecta a incoherencias extrañas (5.0.97 fue mucho más rápido que 5.5.27 con la misma configuración). Todavía estoy buscando la causa). Lo que encontramos fue que DISABLE KEYS y ENABLE KEYS son inútiles ya veces dañinas si no comienzas con una tabla vacía.