mostrar - optimizar consultas mysql

Cómo deshabilitar el índice en innodb

Question

Estoy tratando de acelerar la inserción masiva en una tabla InnoDB deshabilitando temporalmente sus índices:

ALTER TABLE mytable DISABLE KEYS;

Pero da una advertencia :

+-------+------+-------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------+
| Note | 1031 | Table storage engine for ''mytable'' doesn''t have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

¿Cómo podemos deshabilitar los índices?

¿Qué alternativas existen para evitar el uso del índice al hacer inserciones masivas?

¿Cómo podemos acelerar el proceso?

Answer 1

¿Has intentado lo siguiente?

 SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

De las referencias de MySQL https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

Ver la sección " Consejos para cargar datos a granel "

Answer 2

Hay una muy buena razón por la cual no puede ejecutar DISABLE KEYS en una tabla InnoDB; InnoDB no está diseñado para usarlo, y MyISAM sí lo está.

De hecho, esto es lo que sucede cuando recargas un mysqldump:

Verá una CREATE TABLE para una tabla MyISAM seguida de un bloqueo de escritura.

Antes de ejecutar todas las inserciones masivas, se realiza una llamada a ALTER TABLE ... DISABLE KEYS .

Lo que hace es desactivar los índices secundarios en la tabla MyISAM.

Entonces, las inserciones a granel están hechas. Mientras se hace esto, la tecla PRIMARY KEY y todas las teclas UNIQUE en la tabla MyISAM están deshabilitadas. Antes de UNLOCK TABLEs , se realiza una llamada ALTER TABLE ... ENABLE KEYS para reconstruir todos los índices no únicos linealmente.

En mi humilde opinión, esta operación no estaba codificada en el motor de almacenamiento InnoDB porque todas las claves en un índice no exclusivo vienen con la entrada de la clave principal de gen_clust_index (también conocido como índice gen_clust_index ). Esa sería una operación muy costosa ya que construir un índice no único requeriría O (n log n) tiempo de ejecución para recuperar cada clave única para adjuntarla a una clave no única.

A la luz de esto, publicar una advertencia acerca de intentar DISABLE KEYS/ENABLE KEYS en una tabla InnoDB es mucho más fácil que codificar excepciones al mysqldump para casos especiales que involucren motores de almacenamiento que no sean MyISAM.

Answer 3

Para reducir los costos de volver a calcular los índices, debe insertar los datos utilizando DATA INFILE o usando insertos de fila múltiple de Mysql, como

INSERT INTO tbl_name (a, b, c) VALUES (1,2,3), (4,5,6), (7,8,9);

-> para insertar varias filas con una declaración.

Cuántas filas se pueden insertar con una instrucción depende de la configuración max_allowed_packet mysql.