Comment créer un index sur une grande table de production MySQL sans verrouillage de table ?

Comment créer un index sur une grande table de production MySQL sans verrouillage de table

Contexte du problème :

Créer un index sur une grande table MySQL peut être une tâche ardue, en particulier dans un environnement de production où un accès ininterrompu est crucial. Les instructions CREATE INDEX traditionnelles peuvent entraîner un verrouillage complet de la table, bloquant toutes les opérations simultanées.

Considérations sur la version MySQL :

• Dans MySQL 5.6 et versions ultérieures, les mises à jour d'index sont effectuées en ligne, permettant aux opérations de lecture et d'écriture de se poursuivre pendant la création de l'index.
• Cependant, dans MySQL 5.5 et versions antérieures, y compris les tables InnoDB et MyISAM, les mises à jour de l'index bloqueront les écritures dans la table.

Approche des maîtres circulaires :

Pour les versions MySQL antérieures à 5.6, une approche efficace est la technique des maîtres circulaires :

1. Configurer un maître secondaire ( Maître B) répliquant à partir du maître principal (Maître A).
2. Effectuez la mise à jour du schéma sur le Maître B (en lui permettant de prendre du retard lors de la mise à niveau).
3. Assurez-vous que le changement de schéma est compatible avec des commandes de réplication du schéma de downversion sur le maître A.
4. Basculez atomiquement tous les clients du maître A au maître B.
5. Mettez à jour le schéma sur le maître A et faites-en le maître secondaire.

Outil pt-online-schema-change de Percona :

Cet outil automatise l'approche des maîtres circulaires en :

• Création d'une nouvelle table avec le schéma mis à jour.
• Garder la nouvelle table synchronisée avec la table d'origine à l'aide d'un déclencheur.
• Copier les lignes par lots à partir de la table d'origine.
• Remplacement de la table d'origine. avec la nouvelle table.

Considérations AWS RDS :

Pour les bases de données MySQL hébergées sur le RDS d'Amazon, la fonctionnalité « Read Replica Promotion » peut être utilisée pour faciliter changements de schéma sans verrouillage de table. Cela implique d'apporter des modifications sur un esclave en lecture seule, puis de le promouvoir pour devenir le nouveau maître.

Techniques alternatives :

• Utiliser un esclave temporaire table : Créez une table temporaire avec le nouvel index, insérez les données de la table d'origine et remplacez la table d'origine par la table temporaire.
• Partitionnez la table : Divisez la table en partitions plus petites, créez l'index sur chaque partition séparément, puis fusionnez à nouveau les partitions.
• Utilisez un processus en arrière-plan : Créez un processus en arrière-plan distinct qui crée progressivement l'index pendant que la table reste accessible pour les opérations normales. Cette approche n'est pas prise en charge dans toutes les versions de MySQL.

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!