So verwalten Sie MySQL-Indizes und Datentabellen

Tabellenkonflikte finden und beheben

Das Schlimmste, was einer Datentabelle passieren kann, ist ein Konflikt. Bei Verwendung der MyISAM-Speicher-Engine werden Konflikte normalerweise durch Abstürze verursacht. Bei allen Speicher-Engines können jedoch Indexkonflikte auftreten, wenn ein Hardwarefehler, ein interner MySQL-Fehler oder ein Betriebssystemfehler vorliegt.

Konfliktierende Indizes können dazu führen, dass Abfragen falsche Ergebnisse zurückgeben, die Anzahl doppelter Indexfehler steigt, wenn keine doppelten Werte vorhanden sind, und können sogar vollständige Tabellenscans oder Abstürze verursachen. Wenn ein gelegentliches Ereignis auftritt, z. B. ein Fehler, von dem Sie glauben, dass er nicht auftritt, führen Sie den Befehl CHECK TABLE aus, um festzustellen, ob Konflikte in der Datentabelle vorliegen (beachten Sie, dass einige Datenbankmodule diesen Befehl nicht unterstützen, während andere mehrere Optionsparameter unterstützen, um anzugeben, wie die Tabelle überprüft werden soll). Normalerweise erkennt der Befehl CHECK TABLE die meisten Tabellen- und Indexfehler.

Sie können Datentabellenfehler mit dem Befehl REPAIR TABLE reparieren, aber nicht alle Speicher-Engines unterstützen diesen Befehl. Zu diesem Zeitpunkt müssen Sie eine ALTER-Anweisung ohne Operation ausführen, z. B. um die Engine einer Datentabelle auf dieselbe wie die aktuelle Engine zu ändern. Sie können beispielsweise die folgende Anweisung für eine InnoDB-Datentabelle ausführen:

ALTER TABLE innodb_tb1 ENGINE=INNODB;

Alternativ können Sie ein Speicher-Engine-spezifisches Offline-Reparaturtool wie myisamchk verwenden oder die Daten exportieren und erneut importieren. Wenn der Konflikt jedoch im Systembereich oder im Datenzeilenbereich der Datentabelle statt im Index auftritt, können Sie diese Methoden möglicherweise nicht verwenden. In diesem Fall müssen Sie möglicherweise die Daten aus Ihrer Sicherung wiederherstellen oder die Daten aus den in Konflikt stehenden Dateien wiederherstellen.

Wenn Sie in InnoDB auf Konflikte stoßen, handelt es sich um einen schwerwiegenden Fehler und Sie müssen zur Analyse des Problems die richtige Methode verwenden. Normalerweise treten bei InnoDB keine Konflikte auf. Es ist auf eine robuste Konfliktbewältigung ausgelegt. Der Konflikt kann auf einen Hardwarefehler (z. B. Speicherbereichsfehler oder Festplattenfehler), einen Betriebsfehler des DBA (z. B. Ausführen der Datenbankdatei außerhalb der MySQL-Umgebung) oder einen eigenen Fehler von InnoDB (die Wahrscheinlichkeit hierfür ist sehr gering) zurückzuführen sein. Ein häufiger Grund ähnelt dem Fehler beim Erstellen von Backups mit dem Dienstprogramm rsync. Es gibt derzeit keine Abfrage, die ausgeführt werden kann, da dies zu InnoDB-Datenkonflikten führen würde, die Sie eigentlich vermeiden wollten. Wenn Sie durch eine problematische Abfrage InnoDB-Datenkonflikte verursachen, ist dies nicht Ihre Schuld, sondern es handelt sich um einen InnoDB-Fehler.

Wenn tatsächlich ein Datenkonflikt auftritt, ist es am wichtigsten, die Ursache des Konflikts herauszufinden. Reparieren Sie vorher nicht einfach die Daten. Vielleicht verschwindet der Konflikt dann automatisch. Sie können InnoDB mithilfe des Parameters innodb_force_recovery in den erzwungenen Wiederherstellungsmodus versetzen, um Daten zu reparieren (siehe MySQL-Handbuch). Sie können auch das Open-Source-Tool Percona InnoDB Data Recovery Tool (www.percona.com/software/my…) verwenden, um Daten aus beschädigten Datendateien zu extrahieren.

Aktualisieren der Indexstatistiken

Bevor der MySQL-Abfrageoptimierer entscheidet, wie ein Index verwendet wird, ruft er zwei APIs auf, um die Verteilung der Indexwerte zu ermitteln. Die erste ist die Methode „records_in_range“, die einen Bereich als Argument verwendet und die Anzahl der Ergebnisse in diesem Bereich zurückgibt. Das zurückgegebene Ergebnis ist für die MyISAM-Engine exakt, für InnoDB jedoch eine Schätzung.

Die zweite API ist die Infomethode, die verschiedene Datentypen zurückgibt, darunter auch Indexkandidaten (d. h. eine Schätzung der Anzahl der Datensätze, die jedem Index entsprechen).

Wenn die Speicher-Engine dem Abfrageoptimierer ungenaue Informationen zur Datenzeilenanzahl liefert oder der Abfrageplan zu komplex ist, um die genaue Zeilenanzahl zu schätzen, verwendet der Optimierer Indexstatistiken, um die Anzahl der Datenzeilen zu schätzen. Der MySQL-Optimierer trifft Entscheidungen auf Grundlage der Abfragekosten und das wichtigste Kostenkriterium ist die Datenmenge, die von der Abfrage durchsucht wird. Wenn Indexstatistiken nie generiert wurden oder veraltet sind, kann der Optimierer falsche Entscheidungen treffen. Die Lösung besteht darin, den Befehl ANALYZE TABLE auszuführen, der die Indexstatistiken neu erstellt.

Jede Speicher-Engine implementiert Indexstatistiken anders, daher können die Häufigkeit und die Kosten für die Ausführung des Befehls ANALUZE TABLE variieren. Typische Speicher-Engines verarbeiten Indexstatistiken wie folgt:

• Die Speicher-Engine speichert keine Indexstatistiken.
• MyISAM speichert Indexstatistiken auf der Festplatte und ANALYZE TABLE verwendet einen vollständigen Indexscan beim Berechnen von Kandidatendatenzeilen. Während dieses Vorgangs wird die gesamte Tabelle gesperrt.
• Ab MySQL 5.5 speichert InnoDB Indexstatistiken nicht mehr auf der Festplatte. Stattdessen werden die Indizes nach dem Zufallsprinzip ausgewählt und die Ergebnisse im Speicher abgelegt.
  

Kandidaten für einen Index können mit dem Befehl SHOW INDEX FROM untersucht werden. Zum Beispiel:

Dieser Befehl liefert viele Informationen über Indizes. Weitere Einzelheiten finden Sie im MySQL-Handbuch. Von besonderem Interesse ist hier die Spalte „Kardinalität“. Diese Spalte zeigt, wie vielen eindeutigen Werten der Index nach Schätzung der Speicher-Engine entspricht. In MySQL 5.0 und höher sind diese Informationen auch in der Tabelle INFORMATION_SCHEMA.STATISTICS verfügbar, was sehr praktisch ist. Sie können beispielsweise INFORMATION_SCHEMA abfragen, um Indizes mit geringer Filterbarkeit zu finden. Bitte beachten Sie jedoch, dass diese Zwischentabellen bei Servern mit sehr großen Datenmengen zu einer deutlichen Erhöhung der Serverlast führen können.

Es lohnt sich, die InnoDB-Statistiken genauer zu untersuchen. Die statistischen Ergebnisse werden durch eine zufällige Stichprobennahme von Indexdatenseiten berechnet, wobei davon ausgegangen wird, dass die verbleibenden, nicht abgetasteten Daten ebenfalls ähnlich verteilt sind. In älteren InnoDB-Versionen betrug die Anzahl der abgetasteten Seiten 8, in neueren Versionen kann dies jedoch über die Variable innodb_stats_sample_pages angepasst werden. Wenn Sie diesen Wert auf einen Wert größer als 8 festlegen, können insbesondere bei großen Tabellen repräsentativere Indexstatistiken erstellt werden. Die Kosten können jedoch unterschiedlich hoch sein.

InnoDB berechnet Indexstatistiken, wenn eine Tabelle zum ersten Mal geöffnet wird, wenn ANALUZE TABLE ausgeführt wird und wenn sich die Speichergröße der Tabelle erheblich ändert (1/16 einer Änderung oder 2 Milliarden eingefügte Zeilen).

InnoDB berechnet auch Indexstatistiken für bestimmte Abfragen von INFORMATION_SCHEMA-Tabellen, für die Ausführung von SHOW TABLE STATUS, für die Ausführung von SHOW INDEX-Abfragen oder für den MySQL-Befehlszeilenclient mit aktivierter Einstellung zur automatischen Vervollständigung. Dies kann bei Servern mit großen Datenmengen oder sehr langsamen E/A-Geschwindigkeiten tatsächlich zu schwerwiegenden Problemen führen. Durch Clientprogramme oder Überwachungstools verursachtes Resampling kann viele Sperren verursachen und die Serverlast erhöhen, was sich auch auf die Startzeit der Endbenutzer auswirken kann. Da der Befehl SHOW INDEX die Indexstatistiken aktualisiert, können Sie die Indexstatistiken nicht beobachten, wenn Sie sie nicht ändern. Sie können diese Probleme vermeiden, indem Sie die Option innodb_stats_on_metadata deaktivieren (standardmäßig deaktiviert). Mit dem folgenden Befehl können die Systemvariablen überprüft werden, die sich auf die InnoDB-Indexstatistiken beziehen.

GLOBALE VARIABLEN ANZEIGEN, WO Variablenname wie 'innodb_stats%'

Wenn Sie Percona Server mit der Percona XtraDB-Speicher-Engine verwenden, die eine Alternative zu InnoDB darstellt, können Sie weitere Konfigurationen vornehmen. Mit der Option innodb_stats_auto_update können Sie die automatische Stichprobennahme deaktivieren und so die automatischen Statistikberechnungen effektiv einfrieren, sofern Sie ANALYZE TABLE nicht manuell ausführen. Dadurch werden Sie von unzuverlässigen Abfragen befreit. Diese Funktion wurde aufgrund von Anfragen von Kunden mit großen Bereitstellungen hinzugefügt.

Um eine höhere Abfrageplanstabilität und einen schnelleren Systemstart zu erreichen, können Sie Datentabellen auf Systemebene zum Speichern von Indexstatistiken verwenden. Bei dieser Methode ist keine Neuberechnung der Indexstatistiken beim Neustart des Systems oder beim ersten Start von InnoDB zum Öffnen der Datentabelle erforderlich. Diese Funktion ist in Percona 5.1 und in der Standardversion von MySQL 5.6 verfügbar. Diese Percona Server-Funktion wird über die Option innodb_use_sys_stats_table aktiviert. Ab MySQL Version 5.6 wird es durch die Option innodb_stats_persistent gesteuert, die standardmäßig aktiviert ist. Gleichzeitig gibt es eine weitere Variable, die eine einzelne Tabelle steuert. Die Variable innodb_stats_auto_recalc ist standardmäßig auf ON eingestellt. Dadurch werden die Indexstatistiken der Tabelle neu berechnet, wenn sich die Datentabelle um mehr als 10 % ändert (das Handbuch finden Sie unter: dev.mysql.com/doc/refman/…).

Wenn Sie die automatische Aktualisierung der Indexstatistiken nicht konfigurieren, sollten Sie die Indexstatistiken in regelmäßigen Abständen mit dem Befehl ANALYZE TABLE aktualisieren, es sei denn, Sie wissen, dass eine Nichtaktualisierung nicht zu schlechten Abfrageplänen führt.

Oben finden Sie Einzelheiten zur Verwaltung von MySQL-Indizes und Datentabellen. Weitere Informationen zur Verwaltung von MySQL-Indizes und Datentabellen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!