Top-ySQL-Schemaprüfungen zur Steigerung der Datenbankleistung

Ein Datenbankschema definiert die logische Struktur Ihrer Datenbank, einschließlich Tabellen, Spalten, Beziehungen, Indizes und Einschränkungen, die die Organisation und den Zugriff auf Daten beeinflussen. Es geht nicht nur darum, wie die Daten gespeichert werden, sondern auch darum, wie sie mit Abfragen, Transaktionen und anderen Vorgängen interagieren.

Diese Überprüfungen können Ihnen helfen, den Überblick über neue oder anhaltende Probleme zu behalten, bevor sie zu größeren Problemen werden. Nachfolgend können Sie tiefer in diese Schemaprüfungen eintauchen und genau herausfinden, wie Sie etwaige Probleme beheben können, wenn Ihre Datenbank die Prüfung nicht besteht. Denken Sie daran: Bevor Sie Schemaänderungen vornehmen, sichern Sie immer Ihre Daten, um sie vor potenziellen Risiken zu schützen, die bei Änderungen auftreten könnten.

1. Primärschlüsselprüfung (Fehlende Primärschlüssel)

Der Primärschlüssel ist ein wichtiger Teil jeder Tabelle, da er jede Zeile eindeutig identifiziert und effiziente Abfragen ermöglicht. Ohne einen Primärschlüssel können bei Tabellen Leistungsprobleme auftreten und bestimmte Tools wie Replikations- und Schemaänderungsdienstprogramme funktionieren möglicherweise nicht ordnungsgemäß.

Es gibt mehrere Probleme, die Sie vermeiden können, indem Sie beim Entwerfen von Schemata einen Primärschlüssel definieren:

1. Wenn kein primärer oder eindeutiger Schlüssel angegeben wird, erstellt MySQL einen internen Schlüssel, auf den für die Verwendung nicht zugegriffen werden kann.
2. Das Fehlen eines Primärschlüssels könnte die Replikationsleistung verlangsamen, insbesondere bei zeilenbasierter oder gemischter Replikation.
3. Primärschlüssel ermöglichen eine skalierbare Datenarchivierung und -bereinigung. Tools wie pt-online-schema-change erfordern einen Primärschlüssel oder einen eindeutigen Schlüssel.
4. Primärschlüssel identifizieren Zeilen eindeutig, was aus Anwendungssicht von entscheidender Bedeutung ist.

Beispiel

Um eine PRIMARY KEY-Einschränkung für die Spalte „ID“ zu erstellen, wenn die Tabelle bereits erstellt ist, verwenden Sie das folgende SQL:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

So definieren Sie einen Primärschlüssel für mehrere Spalten:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Hinweis: Wenn Sie den Befehl ALTER TABLE verwenden, müssen die Primärschlüsselspalten beim ersten Erstellen der Tabelle so deklariert worden sein, dass sie keine NULL-Werte enthalten.

2. Überprüfung der Tabellen-Engine (veraltete Tabellen-Engine)

Die MyISAM-Speicher-Engine ist veraltet und Tabellen, die sie noch verwenden, sollten zu InnoDB migriert werden. InnoDB ist aufgrund seiner überlegenen Leistung, Datenwiederherstellungsfunktionen und Transaktionsunterstützung die standardmäßige und empfohlene Engine für die meisten Anwendungsfälle. Die Migration von MyISAM zu InnoDB kann die Leistung in schreibintensiven Anwendungen erheblich verbessern, eine bessere Fehlertoleranz bieten und erweiterte MySQL-Funktionen wie Volltextsuche und Fremdschlüssel ermöglichen.

Warum InnoDB bevorzugt wird:

• Crash-Recovery-Funktionen ermöglichen eine automatische Wiederherstellung nach Datenbankserver- oder Host-Abstürzen ohne Datenbeschädigung.
• Sperrt nur die von einer Abfrage betroffenen Zeilen und ermöglicht so eine wesentlich bessere Leistung in Umgebungen mit hoher Parallelität.
• Zwischenspeichert sowohl Daten als auch Indizes im Speicher, was für leseintensive Arbeitslasten bevorzugt wird.
• Vollständig ACID-konform, gewährleistet Datenintegrität und unterstützt Transaktionen.
• Die InnoDB-Engine erhält den Großteil des Fokus von der MySQL-Entwicklergemeinschaft und ist damit die aktuellste und am besten unterstützte Engine.

So migrieren Sie zu InnoDB

ALTER TABLE Persons ADD PRIMARY KEY (ID);

3. Tabellensortierungsprüfung (gemischte Sortierungen)

Die Verwendung unterschiedlicher Sortierungen über Tabellen hinweg oder sogar innerhalb einer Tabelle kann zu Leistungsproblemen führen, insbesondere bei Zeichenfolgenvergleichen und Verknüpfungen. Wenn sich die Sortierungen zweier String-Spalten unterscheiden, muss MySQL die Strings möglicherweise zur Laufzeit konvertieren, was die Verwendung von Indizes verhindern und Ihre Abfragen verlangsamen kann.

Wenn Sie Änderungen an gemischten Kollationstabellen vornehmen, können einige Probleme auftreten:

• Sortierungen können auf Spaltenebene unterschiedlich sein, sodass Nichtübereinstimmungen auf Tabellenebene keine Probleme verursachen, wenn die relevanten Spalten in einem Join über übereinstimmende Sortierungen verfügen.
• Das Ändern der Sortierung einer Tabelle, insbesondere mit einem Zeichensatzwechsel, ist nicht immer einfach. Möglicherweise ist eine Datenkonvertierung erforderlich und nicht unterstützte Zeichen könnten zu beschädigten Daten führen.
• Wenn Sie beim Erstellen einer Tabelle keine Sortierung oder keinen Zeichensatz angeben, werden die Datenbankstandards übernommen. Wenn auf Datenbankebene keine festgelegt sind, gelten die Serverstandards. Um diese Probleme zu vermeiden, ist es wichtig, die Sortierung in Ihrem gesamten Datensatz zu standardisieren, insbesondere für Spalten, die häufig in Join-Vorgängen verwendet werden.

So ändern Sie die Sortiereinstellungen

Bevor Sie Änderungen an den Sortiereinstellungen Ihrer Datenbank vornehmen, testen Sie Ihren Ansatz in einer Nicht-Produktionsumgebung, um unbeabsichtigte Folgen zu vermeiden. Wenn Sie sich über irgendetwas unsicher sind, wenden Sie sich am besten an einen DBA.

Rufen Sie den Standardzeichensatz und die Standardsortierung für alle Datenbanken ab:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Überprüfen Sie die Sortierung bestimmter Tabellen:

ALTER TABLE <table_name> ENGINE=InnoDB;

Suchen Sie den Standardzeichensatz des Servers:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Suchen Sie die Standardsortierung des Servers:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Aktualisieren Sie die Sortierung für eine bestimmte Datenbank:

SELECT @@GLOBAL.character_set_server;

Aktualisieren Sie die Sortierung für eine bestimmte Tabelle:

SELECT @@GLOBAL.collation_server;

4. Tabellenzeichensatzprüfung (gemischter Zeichensatz)

Gemischte Zeichensätze ähneln gemischten Sortierungen insofern, als sie zu Leistungs- und Kompatibilitätsproblemen führen können. Ein gemischter Zeichensatz liegt vor, wenn verschiedene Spalten oder Tabellen unterschiedliche Codierungsformate zum Speichern von Daten verwenden.

• Gemischte Zeichensätze können die Join-Leistung bei Zeichenfolgenspalten beeinträchtigen, indem sie die Verwendung von Indizes verhindern oder Wertkonvertierungen erfordern.
• Zeichensätze können auf Spaltenebene definiert werden. Solange die an einem Join beteiligten Spalten über übereinstimmende Zeichensätze verfügen, wird die Leistung nicht durch Nichtübereinstimmungen auf Tabellenebene beeinträchtigt.
• Das Ändern des Zeichensatzes einer Tabelle kann eine Datenkonvertierung erfordern, die zu beschädigten Daten führen kann, wenn nicht unterstützte Zeichen gefunden werden.
• Wenn kein Zeichensatz oder keine Sortierung angegeben ist, erben Tabellen die Standardeinstellungen der Datenbank und Datenbanken erben den Standardzeichensatz und die Sortierung des Servers.

So ändern Sie die Charaktereinstellungen

Bevor Sie die Zeicheneinstellungen Ihrer Datenbank anpassen, testen Sie die Änderungen unbedingt in einer Staging-Umgebung, um unerwartete Probleme zu vermeiden. Wenn Sie sich bei den einzelnen Schritten nicht sicher sind, wenden Sie sich an einen DBA.

Rufen Sie den Standardzeichensatz und die Standardsortierung für alle Datenbanken ab:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

Den Zeichensatz einer Spalte abrufen:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Suchen Sie den Standardzeichensatz des Servers:

ALTER TABLE <table_name> ENGINE=InnoDB;

Suchen Sie die Standardsortierung des Servers:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

So zeigen Sie die Struktur einer Tabelle an:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Beispielausgabe:

SELECT @@GLOBAL.character_set_server;

So ändern Sie einen Spaltenzeichensatz:

SELECT @@GLOBAL.collation_server;

5. Überprüfung der automatischen Inkrementierung von Spalten (Art der automatisch inkrementierten Spalten)

Für Tabellen, von denen erwartet wird, dass sie unbegrenzt wachsen und die automatische Inkrementierung für Primärschlüssel verwenden, wird empfohlen, zum Datentyp UNSIGNED BIGINT zu wechseln. Dadurch kann die Spalte einen viel größeren Wertebereich verarbeiten, sodass künftig keine kostspieligen Tabellenänderungen erforderlich sind, sobald der Maximalwert erreicht ist. Durch die Angabe von UNSIGNED werden nur positive Werte gespeichert, wodurch sich der Bereich des Datentyps effektiv verdoppelt.

So ändern Sie die Charaktereinstellungen

So ändern Sie den Spaltentyp in UNSIGNED BIGINT:

ALTER DATABASE <db-name> COLLATE=<collation-name>;

6. Tabelle Fremdschlüsselprüfung (Vorhandensein von Fremdschlüsseln)

Fremdschlüssel sorgen für Datenkonsistenz, indem sie die Beziehung zwischen übergeordneten und untergeordneten Tabellen aufrechterhalten, wirken sich aber auch auf die Datenbankleistung aus. Bei jedem Schreibvorgang sind zusätzliche Suchvorgänge erforderlich, um die Integrität der zugehörigen Daten zu überprüfen. Dies kann insbesondere in Umgebungen mit hohem Verkehrsaufkommen zu Verlangsamungen führen.

Wenn die Leistung ein Problem darstellt, sollten Sie die Entfernung von Fremdschlüsseln in Betracht ziehen, insbesondere in Szenarien, in denen die Datenkonsistenz auf Anwendungsebene gehandhabt werden kann.

So entfernen Sie Fremdschlüssel

So löschen Sie eine Fremdschlüsseleinschränkung aus einer Tabelle:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

7. Prüfung auf doppelte Indizes

Doppelte Indizes in MySQL verbrauchen unnötigen Speicherplatz und verursachen zusätzlichen Overhead bei Schreibvorgängen, da jeder Index aktualisiert werden muss. Dies kann die Abfrageoptimierung erschweren und möglicherweise zu ineffizienten Ausführungsplänen führen, ohne einen wirklichen Nutzen zu bieten.

Identifizieren und entfernen Sie doppelte Indizes, um die Abfrageoptimierung zu optimieren und den Overhead zu reduzieren. Stellen Sie jedoch sicher, dass der Index nicht für kritische Abfragen verwendet wird, bevor Sie ihn entfernen.

8. Überprüfung nicht verwendeter Indizes

Nicht verwendete Indizes in MySQL können sich negativ auf die Datenbankleistung auswirken, indem sie Speicherplatz verbrauchen, den Verarbeitungsaufwand beim Einfügen, Aktualisieren und Löschen erhöhen und den Gesamtbetrieb verlangsamen. Während Indizes für die Beschleunigung von Abfragen wertvoll sind, können diejenigen, die nicht verwendet werden, Ihr System unnötig belasten.
Zu den weiteren Vorteilen des Entfernens ungenutzter oder doppelter Indizes gehören:

• Mit weniger Indizes hat der MySQL-Optimierer weniger Auswahlmöglichkeiten zur Auswertung, was die Abfrageausführung vereinfacht und die CPU-/Speichernutzung reduziert.
• Durch das Entfernen ungenutzter Indizes wird wertvoller Speicherplatz frei, der für wichtigere Daten verwendet werden kann, wodurch auch die E/A-Effizienz verbessert wird.
• Indexpflegeaufgaben, wie z. B. Neuaufbau oder Reorganisation, werden schneller und weniger ressourcenintensiv, wenn die Anzahl der Indizes minimiert wird. Dies führt zu reibungsloseren Abläufen, insbesondere in Umgebungen, die eine Verfügbarkeit rund um die Uhr erfordern.

Um nicht verwendete Indizierungen in MySQL oder MariabDB zu identifizieren, verwenden Sie bitte die folgende SQL-Anweisung:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

So entfernen Sie nicht verwendete oder doppelte Indizes

In MySQL 8.0 und höher können Sie Indizes unsichtbar machen, um zu testen, ob sie benötigt werden, ohne sie vollständig zu löschen:

ALTER TABLE <table_name> ENGINE=InnoDB;

Wenn die Leistung nicht beeinträchtigt wird, kann der Index sicher gelöscht werden:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Sie können einen Index bei Bedarf wieder sichtbar machen:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Schemaprüfungen jetzt mit Releem verfügbar

Mit dem neuesten Update umfasst Releem jetzt umfassende Schema-Gesundheitsprüfungen. Diese Prüfungen bieten Echtzeit-Einblicke in die strukturelle Integrität Ihrer Datenbank sowie umsetzbare Empfehlungen zur Behebung erkannter Probleme.

Durch die Automatisierung des Schemaüberwachungsprozesses macht Releem das Rätselraten bei manuellen Prüfungen überflüssig und erspart Datenbankingenieuren so jede Menge Zeit und Mühe. Anstatt stundenlang an Schemadetails zu arbeiten, können Sie sich jetzt auf dringendere Aufgaben konzentrieren.

Das obige ist der detaillierte Inhalt vonTop-ySQL-Schemaprüfungen zur Steigerung der Datenbankleistung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!