So erstellen Sie einen Index für eine große MySQL-Produktionstabelle ohne Tabellensperre

Problemhintergrund:

Das Erstellen eines Index für eine große MySQL-Tabelle kann eine entmutigende Aufgabe sein, insbesondere in einer Produktionsumgebung, in der ein unterbrechungsfreier Zugriff von entscheidender Bedeutung ist. Herkömmliche CREATE INDEX-Anweisungen können zu einer vollständigen Tabellensperre führen und alle gleichzeitigen Vorgänge blockieren.

Überlegungen zur MySQL-Version:

• In MySQL 5.6 und höher Indexaktualisierungen werden online ausgeführt, sodass Lese- und Schreibvorgänge während der Indexerstellung fortgesetzt werden können.
• In MySQL 5.5 und früher, einschließlich InnoDB- und MyISAM-Tabellen, blockieren Indexaktualisierungen jedoch Schreibvorgänge in die Tabelle.

Circular Masters-Ansatz:

Für MySQL-Versionen vor 5.6 ist ein effektiver Ansatz die Circular Masters-Technik:

1. Einen sekundären Master einrichten ( Master B) repliziert vom primären Master (Master A).
2. Führen Sie die Schemaaktualisierung auf Master B durch (damit er während des Upgrades zurückfällt).
3. Stellen Sie sicher, dass die Schemaänderung kompatibel ist mit replizierenden Befehlen aus dem Downversion-Schema auf Master A.
4. Schalten Sie alle Clients atomar von Master A auf Master B um.
5. Aktualisieren Sie das Schema auf Master A und machen Sie es zum sekundären Master.

Perconas pt-online-schema-change Tool:

Dieses Tool automatisiert den Circular-Masters-Ansatz durch:

• Erstellen einer neuen Tabelle mit dem aktualisierten Schema.
• Die neue Tabelle mithilfe eines Triggers mit der Originaltabelle synchron halten.
• Zeilen stapelweise aus der Originaltabelle kopieren.
• Die Originaltabelle ersetzen mit der neuen Tabelle.

AWS RDS-Überlegungen:

Für MySQL-Datenbanken, die auf dem RDS von Amazon gehostet werden, kann die Funktion „Read Replica Promotion“ zur Vereinfachung verwendet werden Schemaänderungen ohne Tabellensperre. Dazu müssen Änderungen an einem schreibgeschützten Slave vorgenommen und dieser dann zum neuen Master befördert werden.

Alternative Techniken:

• Verwenden Sie eine temporäre Tabelle: Erstellen Sie eine temporäre Tabelle mit dem neuen Index, fügen Sie Daten aus der Originaltabelle ein und ersetzen Sie die Originaltabelle durch die temporäre Tabelle.
• Partitionieren Sie die Tabelle: Teilen Sie die Tabelle Teilen Sie es in kleinere Partitionen auf, erstellen Sie den Index für jede Partition separat und führen Sie die Partitionen dann wieder zusammen.
• Verwenden Sie einen Hintergrundprozess: Erstellen Sie einen separaten Hintergrundprozess, der nach und nach den Index erstellt, während die Tabelle bleibt für den normalen Betrieb zugänglich. Dieser Ansatz wird nicht in allen MySQL-Versionen unterstützt.