Comment résoudre le problème de la modification lente des champs dans les grandes tables MYSQL

Pour les grandes tables, les performances de l'opération ALTER TABLE de MYSQL deviendront un défi de taille. La façon dont MYSQL effectue la plupart des opérations de modification de la structure des tables consiste à créer une table vide avec une nouvelle structure de table, à récupérer toutes les données de l'ancienne table, à les insérer dans la nouvelle table, puis à supprimer l'ancienne table. Cette opération peut prendre beaucoup de temps si la mémoire est insuffisante et si la table est volumineuse et comporte de nombreux index. Les opérations ALTER TABLE prennent souvent des heures, voire des jours, ce qui est une situation que de nombreuses personnes ont vécue.

Normalement, la plupart des opérations ALTER TABLE arrêteront le service MYSQL. Pour les scénarios courants, seules deux techniques peuvent être utilisées :

• La première consiste à effectuer d'abord l'opération ALTER TABLE sur une machine qui ne fournit pas de services, puis à basculer avec la bibliothèque principale qui fournit des services

•  ;

  De plus, une technique est le « cliché instantané ». La technique du cliché instantané implique le processus de création d'une nouvelle table et d'opération sur la structure de table souhaitée, puis d'échanger les deux tables via des opérations de renommage et de suppression de table.

Toutes les opérations ALTER TABLE n'entraîneront pas la reconstruction de la table. Il existe deux manières différentes de modifier ou de supprimer la valeur par défaut d'une colonne, l'une étant rapide et l'autre plus lente.

Supposons que vous souhaitiez modifier la période de location par défaut d'un film de trois jours à cinq jours. Voici la manière très lente :

mysql> ALTER TABLE film modify column rental_duration tinyint(3) not null default 5;

Le nombre d'opérations pour 1000 lectures et 1000 insertions peut être obtenu à partir de "SHOW STATUS". En d’autres termes, il copie l’intégralité de la table dans une nouvelle table, y compris le type de données de la colonne, la taille et les attributs null.

Théoriquement, MYSQL peut sauter l'étape de création d'une nouvelle table. Les valeurs par défaut du tableau sont stockées dans le fichier .frm, afin que ce fichier puisse être modifié directement sans modifier le tableau lui-même. Bien que cette méthode d'optimisation soit réalisable, MYSQL ne l'utilise pas actuellement, donc la modification des opérations sur les colonnes nécessite de reconstruire la table.

Une autre méthode consiste à modifier la valeur par défaut de la colonne via l'opération ALTER COLUMN

mysql> ALTER TABLE film ALTER COLUMN rental_duration set DEFAULT 5;

Cette instruction modifiera directement le fichier .frm sans impliquer les données de la table. Cette opération est donc très rapide.

Modifiez uniquement le fichier .frm

Bien qu'il soit très rapide de modifier le fichier .frm de la table, MySQL reconstruit parfois la table inutilement, comme nous pouvons le voir dans l'exemple ci-dessus. En prenant certains risques, MYSQL peut être autorisé à effectuer d'autres types de modifications sans reconstruire la table.

Remarque : les techniques présentées ci-dessous ne sont ni officiellement prises en charge ni documentées et peuvent ne pas fonctionner correctement. Utilisez ces techniques à vos propres risques. >Il est recommandé de sauvegarder les données avant d'exécuter !

Les opérations suivantes peuvent ne pas nécessiter de reconstruire la table :

• Supprimer (et non ajouter) l'attribut AUTO_INCREMENT d'une colonne.

• Ajoutez, supprimez ou modifiez ENUM et SET toujours activés. Si vous supprimez une valeur constante déjà utilisée dans une ligne, la requête renverra une chaîne vide.

Étapes :

• Créez une table vide avec la même structure et apportez les modifications requises (par exemple : ajouter des constantes ENUM).

• Exécutez des TABLES FLUSH AVEC VERROUILLAGE EN LECTURE. Cela fermera toutes les tables en cours d'utilisation et empêchera leur ouverture.

• Échangez des fichiers .frm.

• Exécutez UNLOCK TABLES pour libérer le verrou de lecture dans la deuxième étape.

Ce qui suit est un exemple d'ajout d'une constante à la colonne de notation de la table du film. La colonne actuelle ressemble à ceci :

mysql> SHOW COLUMNS FROM film LIKE 'rating';

Field	Type	Null	Key	Default	Extra
rating	enum('G','PG',' PG-13 ' ,'R','NC-17')	OUI		G

Supposons que nous devions ajouter une classification de film PG-14 pour les parents qui se méfient davantage des films :

mysql> CREATE TABLE film_new like film;
mysql> ALTER TABLE film_new modify column rating ENUM('G','PG','PG-13','R','NC-17','PG-14') DEFAULT 'G';
mysql> FLUSH TABLES WITH READ LOCK;

Notez que nous ajoutons une nouvelle valeur à la fin de la liste des constantes. Si vous mettez la nouvelle valeur au milieu, par exemple : après PG-13, la signification des données existantes sera modifiée : la valeur R existante deviendra PG-14, et la NC-17 existante deviendra R, attendez.

Ensuite, utilisez la commande du système d'exploitation pour échanger le fichier .frm :

/var/lib/mysql/sakila# mv film.frm film_tmp.frm
/var/lib/mysql/sakila# mv film_new.frm film.frm
/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

Retournez à la ligne de commande Mysql Vous pouvez maintenant déverrouiller la table et voir l'effet du changement :

mysql> UNLOCK TABLES;
mysql> SHOW COLUMNS FROM film like 'rating'\G

******. **** ******** 1. ligne************************

Champ : note
Type : énumération ( 'G','PG' ,'PG-13','R','NC-17','PG-14')

La dernière chose à faire est de supprimer la table auxiliaire créée pour terminer cette opération :

mysql> DROP TABLE film_new;

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!