tablas - MySQL muy lento para la consulta de la tabla alternativa

porque se dañan las tablas en mysql

Question

¿Por qué tarda más de una hora simplemente actualizar esta tabla para agregar una columna? Esta tabla tiene 15M filas. Tiene 2 índices y una clave principal de clave única. La consulta ALTER TABLE ha estado en el estado "copy to tmp table" durante 1 hora y 15 minutos.

ALTER TABLE `frugg`.`item_catalog_map` 
ADD COLUMN `conversion_url` TEXT NULL DEFAULT NULL

Mesa:

mysql> describe item_catalog_map;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| catalog_unique_item_id | varchar(255)  | NO   | PRI | NULL    |       |
| catalog_id             | int(11)       | YES  | MUL | NULL    |       |
| item_id                | int(11)       | YES  | MUL | NULL    |       |
| price                  | decimal(10,2) | YES  |     | 0.00    |       |
+------------------------+---------------+------+-----+---------+-------+

mysql> show index from item_catalog_map;
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name             | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| item_catalog_map |          0 | PRIMARY              |            1 | catalog_unique_item_id | A         |    15485115 |     NULL | NULL   |      | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184FCC3C66FC |            1 | catalog_id             | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184F126F525E |            1 | item_id                | A         |    15485115 |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 1

El rendimiento de ALTER TABLE de MySQL puede convertirse en un problema con tablas muy grandes. MySQL realiza la mayoría de las alteraciones creando una tabla vacía con la nueva estructura deseada, insertando todos los datos de la tabla anterior en la nueva y eliminando la tabla anterior. Esto puede llevar mucho tiempo, especialmente si tiene poca memoria y la tabla es grande y tiene muchos índices. Muchas personas tienen experiencia con operaciones ALTER TABLE que han tardado horas o días en completarse.

De todos modos, si necesita continuar con la modificación de la tabla, tal vez los siguientes recursos podrían ayudarlo a:

• https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html
• https://github.com/soundcloud/lhm
• https://githubengineering.com/gh-ost-github-s-online-migration-tool-for-mysql/

Answer 2

Las herramientas Percona son un salvavidas para este tipo de cosas con grandes mesas.

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

ellos básicamente:

1. crear una tabla duplicada
2. crear desencadenador para sincronizar tablas
3. datos de copia masiva
4. verificar
5. intercambiar tablas

Toma para siempre, pero a quién le importa porque esto significa que puede cambiar columnas sin tiempo de inactividad.

Answer 3

Para minimizar el bloqueo de la tabla grande que quiero modificar, hago lo siguiente:

• Cree una nueva tabla vacía basada en la tabla existente y modifique esta nueva tabla vacía.
• Realice un mysqldump de la tabla grande de modo que tenga una instrucción de inserción completa por registro en la tabla grande (interruptores -c y -skip-extended-insert)
• Importe este mysqldump a una base de datos diferente (vacía) con el vacío rebautizado large_table.
• Tome un mysqldump de esta nueva tabla de cambio de nombre de la otra base de datos e impórtela en la base de datos original

• Cambie el nombre de large_table y large_table_new en la base de datos original.

  mysql> create table DATABASE_NAME.LARGE_TABLE_NEW like DATABASE_NAME.LARGE_TABLE;
  mysql> alter table DATABASE_NAME.LARGE_TABLE_NEW add column NEW_COLUMN_NAME COL_DATA_TYPE(SIZE) default null;
  
  $ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p DATABASE_NAME LARGE_TABLE > LARGE_TABLE.sql
  
  mysql> create table test.LARGE_TABLE like DATABASE_NAME.LARGE_TABLE;
  
  $ mysql -u root -p -D test < LARGE_TABLE.sql
  
  mysql> rename table test.LARGE_TABLE to test.LARGE_TABLE_NEW;
  
  $ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p test LARGE_TABLE_NEW > LARGE_TABLE_NEW.sql
  
  $ mysql -u root -p -D DATABASE_NAME < LARGE_TABLE_NEW.sql
  
  mysql> rename table DATABASE_NAME.LARGE_TABLE to DATABASE_NAME.LARGE_TABLE_OLD, DATABASE_NAME.LARGE_TABLE_NEW to DATABASE_NAME.LARGE_TABLE;

Answer 4

Si modifica varias columnas a la vez, puede llevar más tiempo de lo esperado. En cambio, intente alterar una vez la columna a la vez. No encuentro el aspecto técnico de eso, pero experimenté el problema y lo supere.

Answer 5

Si no le importa el tiempo de inactividad, mi sugerencia es utilizar tres declaraciones ALTER TABLE separadas. La primera instrucción elimina todos los índices secundarios existentes. La segunda declaración aplica todos los cambios relacionados con la columna. La última declaración agrega índices secundarios caídos y aplica otros cambios de índice.

Otros dos consejos:

1. Antes de aplicar los cambios de índice, ejecute las dos instrucciones siguientes y cambie los valores a 1 después de finalizar el cambio de índice.

   SET unique_checks=0;
   SET foreign_key_checks=0;

2. Cuando cree múltiples índices secundarios, colóquelos en una instrucción ALTER TABLE lugar de múltiples instrucciones ALTER TABLE separadas.

La siguiente imagen muestra la diferencia de rendimiento. El acercamiento 1 es su acercamiento y el acercamiento 2 es mi manera. El enfoque 2 toma aproximadamente 3,47% de tiempo comparado con el enfoque 1 para una tabla de 50 m. La solución solo funciona para MySQL (> = 5.5) Motor InnoDB.

Answer 6

Su mesa tiene 15 millones de filas, que es algo. ALTER TABLE implica copiar todos los datos de la tabla y recrear los índices. Como primera medida intente copiar el archivo de datos (item_catalog_map.MYD si es MyISAM) en su sistema de archivos y vea cuánto tiempo lleva. Este es el momento en que ALTER TABLE por lo menos tomará.