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 d'index bloquera 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. Configurez 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. (ce qui lui permet de prendre du retard lors de la mise à niveau).
3. Assurez-vous que le changement de schéma est compatible avec les commandes de réplication du schéma de version descendante sur le maître A.
4. Basculez atomiquement tous les clients du maître A vers le maître B.
5. Mettre à jour le schéma sur le maître A et en faire le maître secondaire.

Percona's Outil pt-online-schema-change :

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

• Créant 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 table.
• Remplacement de la table d'origine par 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 les modifications de schéma sans verrouillage de table. Cela implique d'apporter des modifications à un esclave en lecture seule, puis de le promouvoir pour qu'il devienne 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 temporaire. 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 cela crée progressivement l'index tandis que la table reste accessible pour les opérations normales. Cette approche n'est pas prise en charge dans toutes les versions de MySQL.