Bewährte Methoden für die Problembehandlung von „Azure Database for MySQL – flexibler Server“
Verwenden Sie die folgenden Abschnitte, um Ihre Datenbanken von „Azure Database for MySQL – flexibler Server“ reibungslos auszuführen, und verwenden Sie diese Informationen als Leitprinzipien, um sicherzustellen, dass die Schemas optimal konzipiert sind und die beste Leistung für Ihre Anwendungen bieten.
Überprüfen der Indexanzahl
In einer geschäftigen Datenbankumgebung können Sie eine I/O-Nutzung beobachten, was ein Indikator für schlechte Datenzugriffsmuster sein kann. Nicht verwendete Indizes können sich negativ auf die Leistung auswirken, da sie Speicherplatz und Cache verbrauchen und Schreibvorgänge (EINFÜGEN / LÖSCHEN / AKTUALISIEREN) verlangsamen. Nicht verwendete Indizes verbrauchen unnötig zusätzlichen Speicherplatz und erhöhen die Größe der Sicherungsdatei.
Bevor Sie einen Index entfernen, müssen Sie genügend Informationen sammeln, um sicherzustellen, dass er nicht mehr verwendet wird. Diese Überprüfung kann Ihnen helfen, das versehentliche Entfernen eines Indexes zu vermeiden, der für eine Abfrage wichtig ist, die nur vierteljährlich oder jährlich ausgeführt wird. Sie sollten außerdem berücksichtigen, ob ein Index verwendet wird, um Eindeutigkeit oder Reihenfolge zu erzwingen.
Hinweis
Denken Sie daran, Indizes regelmäßig zu überprüfen und alle erforderlichen Aktualisierungen basierend auf Änderungen an den Tabellendaten auszuführen.
SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;
(oder)
use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));
Auflisten der größten Indizes auf dem Server
Die Ausgabe aus der folgenden Abfrage enthält Informationen zu den am häufigsten verwendeten Indizes in allen Tabellen und Schemas auf dem Datenbankserver. Diese Informationen helfen beim Identifizieren des Verhältnis von Schreibvorgängen zu Lesevorgängen für jeden Index und den Latenzen für Lesevorgänge sowie einzelne Schreibvorgänge, welche darauf hindeuten können, dass eine weitere Optimierung für die zugrunde liegende Tabelle und abhängige Abfragen erforderlich ist.
SELECT
object_schema AS table_schema,
object_name AS table_name,
index_name, count_star AS all_accesses,
count_read,
count_write,
Concat(Truncate(count_read / count_star * 100, 0), ':',
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio,
count_fetch AS rows_selected ,
count_insert AS rows_inserted,
count_update AS rows_updated,
count_delete AS rows_deleted,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency ,
Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency,
Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency,
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency,
Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS delete_latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star > 0
ORDER BY sum_timer_wait DESC
Überprüfen des Primärschlüssel-Designs
„Azure Database for MySQL – flexibler Server“ verwendet das InnoDB-Speichermodul für alle nicht temporären Tabellen. Bei InnoDB werden Daten in einem gruppierten Index mithilfe einer B-Baumstruktur-Struktur gespeichert. Die Tabelle ist physisch basierend auf Primärschlüsselwerten organisiert, was bedeutet, dass Zeilen in der Primärschlüsselreihenfolge gespeichert werden.
Jeder Sekundärschlüsseleintrag in einer InnoDB-Tabelle enthält einen Zeiger auf den Primärschlüsselwert, in dem die Daten gespeichert werden. Mit anderen Worten, ein sekundärer Indexeintrag enthält eine Kopie des Primärschlüsselwerts, auf den der Eintrag zeigt. Daher haben Primärschlüsselauswahlen direkte Auswirkungen auf die Menge des Speicheraufwands in Ihren Tabellen.
Wenn ein Schlüssel aus tatsächlichen Daten (z. B. Benutzername, E-Mail, SSN usw.) abgeleitet wird, wird er als natürlicher Schlüssel bezeichnet. Wenn ein Schlüssel künstlich ist und nicht aus Daten abgeleitet wird (z. B. eine autoinkrementierte ganze Zahl), wird er als synthetischer Schlüssel oder Ersatzschlüssel bezeichnet.
Es wird im Allgemeinen empfohlen, die Verwendung natürlicher Primärschlüssel zu vermeiden. Diese Schlüssel sind häufig sehr breit und enthalten lange Werte aus einer oder mehreren Spalten. Dies kann wiederum einen schwerwiegenden Speicheraufwand mit dem Primärschlüsselwert verursachen, der in jeden Sekundärschlüsseleintrag kopiert wird. Darüber hinaus folgen natürliche Schlüssel normalerweise nicht einer vordefinierten Reihenfolge, was die Leistung erheblich reduziert und die Seitenfragmentierung provoziert, wenn Zeilen eingefügt oder aktualisiert werden. Um diese Probleme zu vermeiden, verwenden Sie monoton steigende Ersatzschlüssel anstelle natürlicher Schlüssel. Eine automatisch ansteigende (big)integer-Spalte ist ein gutes Beispiel für einen monoton steigenden Ersatzschlüssel. Wenn Sie eine bestimmte Kombination von Spalten benötigen, müssen Sie diese Spalten als eindeutigen sekundären Schlüssel deklarieren.
Während der ersten Phasen des Erstellens einer Anwendung denken Sie möglicherweise nicht bis zu der Vorstellung voraus, dass Ihre Tabelle sich zwei Milliarden Zeilen nähert. Daher können Sie eine signierte 4-Byte-Ganzzahl für den Datentyp einer ID (Primärschlüsselspalte) verwenden. Überprüfen Sie unbedingt alle Primärschlüssel der Tabelle, und wechseln Sie zu 8-Byte-Ganzzahlspalten (BIGINT), um das Potenzial für ein hohes Volumen oder Wachstum zu berücksichtigen.
Hinweis
Weitere Informationen zu Datentypen und ihren Maximalwerten finden Sie im MySQL-Referenzhandbuch unter Datentypen.
Verwenden von abdeckenden Indizes
Im vorherigen Abschnitt wird erläutert, wie Indizes in MySQL als B-Bäume und in einem gruppierten Index organisiert werden, und die Blattknoten enthalten die Datenseiten der zugrunde liegenden Tabelle. Sekundäre Indizes weisen dieselbe B-Baumstruktur wie gruppierte Indizes auf, und Sie können sie in einer Tabelle oder Ansicht mit einem gruppierten Index oder einem Heap definieren. Jede Indexzeile im nicht gruppierten Index enthält den nicht gruppierten Schlüsselwert und einen Zeilenlokator. Dieser Lokator verweist im gruppierten Index oder Heap auf die Datenzeile mit dem Schlüsselwert. Daher muss ein Lookupvorgang, der einen sekundären Index umfasst, vom Stammknoten über die Verzweigungsknoten zum richtigen Blattknoten navigieren, um den Primärschlüsselwert zu übernehmen. Das System führt dann einen zufälligen IO-Lesevorgang im Primärschlüsselindex aus (mit der erneuten Navigation vom Stammknoten über die Verzweigungsknoten zum richtigen Blattknoten), um die Datenzeile abzurufen.
Um dieses zusätzliche zufällige IO-Lesevorgang im Primärschlüsselindex zu vermeiden, wenn die Datenzeile abgerufen wird, verwenden Sie einen abgedeckten Index, der alle Felder enthält, die von der Abfrage benötigt werden. Im Allgemeinen ist die Verwendung dieses Ansatzes für I/O-gebundene Workloads und zwischengespeicherte Workloads vorteilhaft. Verwenden Sie daher als bewährte Methode die Abdeckung von Indizes, da sie in den Arbeitsspeicher passen und kleiner und effizienter sind, um alle Zeilen zu lesen.
Erwägen Sie beispielsweise eine Tabelle, die Sie verwenden, um alle Mitarbeiter zu finden, die nach dem 1. Januar 2000 dem Unternehmen beigetreten sind.
mysql> show create table employee\G
****************** 1. row ******************
Table: employee
Create Table: CREATE TABLE `employee` (
`empid` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(10) DEFAULT NULL,
`lname` varchar(10) DEFAULT NULL,
`joindate` datetime DEFAULT NULL,
`department` varchar(10) DEFAULT NULL,
PRIMARY KEY (`empid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`
`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';
Wenn Sie einen EXPLAIN-Plan für diese Abfrage ausführen, würden Sie feststellen, dass derzeit keine Indizes verwendet werden, und eine Where-Klausel allein wird verwendet, um die Mitarbeiterdatensätze zu filtern.
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.01 sec)
Wenn Sie jedoch einen Index hinzufügen möchten, der die Spalte in der Where-Klausel abdeckt, zusammen mit den projizierten Spalten, sehen Sie, dass der Index verwendet wird, um die Spalten viel schneller und effizienter zu finden.
mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);
Wenn Sie nun den EXPLAIN-Plan auf derselben Abfrage ausführen, wird der Wert „Using Index“ im Feld „Extra“ angezeigt, was bedeutet, dass InnoDB die Abfrage mithilfe des zuvor erstellten Indexes ausführt, was diesen als abdeckenden Index bestätigt.
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: range
possible_keys: cvg_idx_ex
key: cvg_idx_ex
key_len: 6
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)
Hinweis
Es ist wichtig, die richtige Reihenfolge der Spalten im abdeckenden Index auszuwählen, um die Abfrage korrekt zu bedienen. Die allgemeine Regel besteht darin, die Spalten zuerst zum Filtern zu verwenden (WHERE-Klausel), dann zum Sortieren/Gruppieren (ORDER BY und GROUP BY) und schließlich zur Datenprojektion (SELECT).
Im vorherigen Beispiel haben wir gesehen, dass ein abdeckender Index für eine Abfrage effizientere Datensatzabrufpfade bietet und die Leistung in einer hochgradig gleichzeitig genutzten Datenbankumgebung optimiert.