ALTER TABLE ADD COLUMN prend beaucoup de temps

Question

Je venais juste d'essayer d'ajouter une colonne appelée "location" à une table (main_table) dans une base de données. La commande que j'ai exécutée était

ALTER TABLE main_table ADD COLUMN location varchar (256);

La table principale contient > 2 000 000 lignes. Ça tourne depuis plus de 2 heures et n'est toujours pas terminé.

J'ai essayé d'utiliser mytop pour surveiller l'activité de cette base de données afin de m'assurer que la requête n'est pas bloquée par un autre processus de requête, mais il semble que non. Est-ce censé prendre autant de temps ? En fait, je viens de redémarrer la machine avant d'exécuter cette commande. Maintenant cette commande est toujours en cours. Je ne sais pas quoi faire.

Answer 1

Votre instruction ALTER TABLE implique que MySQL devra réécrire chaque ligne de la table, y compris la nouvelle colonne. Étant donné que vous avez plus de 2 millions de lignes, je m'attends à ce que cela prenne un temps significatif, pendant lequel votre serveur sera probablement principalement limité par les entrées/sorties. En général, il est plus performant de faire ce qui suit :

CREATE TABLE main_table_new LIKE main_table;
ALTER TABLE main_table_new ADD COLUMN location VARCHAR(256);
INSERT INTO main_table_new SELECT *, NULL FROM main_table;
RENAME TABLE main_table TO main_table_old, main_table_new TO main_table;
DROP TABLE main_table_old;

De cette façon, vous ajoutez la colonne sur la table vide, et écrivez essentiellement les données dans cette nouvelle table sur laquelle vous êtes sûr que personne d'autre ne regardera sans occuper autant de ressources.

Answer 2

Je pense que la réponse appropriée à cela est d'utiliser une fonctionnalité comme pt-online-schema-change ou gh-ost.

Nous avons effectué la migration de plus de 4 milliards de lignes avec cela, bien que cela puisse prendre jusqu'à 10 jours, avec moins d'une minute d'indisponibilité.

Percona fonctionne de manière très similaire comme mentionné ci-dessus

• Créez une table temporaire
• Crée des déclencheurs sur la première table (pour les inserts, mises à jour, suppressions) afin qu'ils soient répliqués dans la table temporaire
• Effectuer la migration des données par petits lots
• Lorsque c'est fait, renommer la table en nouvelle table, et supprimer l'autre table

Answer 3

Vous pouvez accélérer le processus en désactivant temporairement les vérifications d'unicité et les contrôles de clés étrangères. Vous pouvez également changer l'algorithme utilisé.

Si vous voulez que la nouvelle colonne soit à la fin de la table, utilisez algorithm=instant:

SET unique_checks = 0;
SET foreign_key_checks = 0;
ALTER TABLE main_table ADD location varchar(256), algorithm=instant;
SET unique_checks = 1;
SET foreign_key_checks = 1;

Sinon, si vous avez besoin que la colonne soit à un emplacement spécifique, utilisez algorithm=inplace:

SET unique_checks = 0;
SET foreign_key_checks = 0;
ALTER TABLE main_table ADD location varchar(256) AFTER othercolumn, algorithm=inplace;
SET unique_checks = 1;
SET foreign_key_checks = 1;

Pour référence, mon PC a mis environ 2 minutes pour modifier une table avec 20 millions de lignes en utilisant l'algorithme inplace. Si vous utilisez un programme comme Workbench, vous voudrez peut-être augmenter la période de temporisation par défaut dans vos paramètres avant de commencer l'opération.

Si vous constatez que l'opération est suspendue indéfiniment, vous devrez peut-être parcourir la liste des processus et arrêter le processus qui a un verrou sur la table. Vous pouvez le faire en utilisant ces commandes:

SHOW FULL PROCESSLIST;
KILL NUMÉRO_DU_PROCESSUS;

Answer 4

La modification de table prend beaucoup de temps avec de grosses données comme dans votre cas, évitez donc de l'utiliser dans de telles situations, et utilisez un code comme celui-ci :

select main_table.*, 
  cast(null as varchar(256)) as null_location, -- toute colonne pouvant accepter null
  cast('' as varchar(256)) as not_null_location, -- toute colonne ne pouvant pas accepter null
  cast(0 as int) as not_null_int, -- colonne int ne pouvant pas accepter null
into new_table 
from main_table;

drop table main_table;
rename table new_table TO main_table;

Answer 5

Essayez de changer le moteur de la table de InnoDB à MyISAM, puis revenez à InnoDB et essayez.

ALTER TABLE `tablename` ENGINE = MyISAM ; 
ALTER TABLE `tablename` ENGINE = InnoDB;

Ensuite, exécutez ceci

ALTER TABLE `tablename` AJOUTER COLONNE `columnname` `datatype` NULL;