Zusammenfassung gängiger Optimierungsvorgänge der MySQL-Datenbank (Erfahrungsaustausch)

Vorwort

Bei einer datenzentrierten Anwendung wirkt sich die Qualität der Datenbank direkt auf die Leistung des Programms aus. Daher ist die Datenbankleistung von entscheidender Bedeutung. Daher sollte jeder die Optimierungsvorgänge der MySQL-Datenbank verstehen. Dieser Artikel fasst hauptsächlich die gängigen Optimierungsvorgänge in der MySQL-Datenbank zusammen. Werfen wir einen Blick auf die ausführliche Einführung.

1. Inhaltsverzeichnis

Wenn wir den Index an die erste Stelle setzen, versteht es sich von selbst, dass es sich bei dieser Optimierungsmethode, die wir stillschweigend verwendet haben, um den Primärschlüsselindex handelt. Manchmal ist uns das vielleicht egal, aber wenn wir entsprechende Indizes definieren, wird die Leistung (Geschwindigkeit) der Datenbankabfragen um ein Vielfaches oder sogar Dutzende Male verbessert.

Normaler Index

Die Funktion dient zur Verbesserung der Abfragegeschwindigkeit.

Tabelle und Index erstellen

Tabelle erstellen Tabellenname(
Feldname Feldtyp [Integritätsbeschränkung],
~
index [Indexname] (Spaltenname)
);

Index erstellen

CREATE INDEX Indexname ON Tabulatorname (Spaltenname)

Löschen eines Indexes

DROP INDEX Indexname FROM Tab_Name

Index anzeigen

SHOW index FROM tab_name

Primärschlüsselindex

Der Zweck besteht darin, Abfragen und eindeutige Einschränkungen zu beschleunigen

Tabelle und Index erstellen

Tabelle erstellen Tabellenname(
Feldname Feldtyp [Integritätsbeschränkung],
~
PRIMARY KEY(Spaltenname)
);

Index erstellen

ALTER TABLE tab_name ADD PRIMARY KEY(spaltenname)

Löschen eines Indexes

ALTER TABLE tab_name DROP PRIMAY KEY(spaltenname)

Eindeutiger Index

Der Zweck besteht darin, Abfragen und eindeutige Einschränkungen zu beschleunigen

Tabelle und Index erstellen

Tabelle erstellen Tabellenname(
Feldname Feldtyp [Integritätsbeschränkung],
~
eindeutiger [Indexname] (Spaltenname)
);

Index erstellen

CREATE UNIQUE INDEX index_name ON tab_name (spaltenname)

Löschen eines Indexes

DROP UNIQUE INDEX Indexname FROM Tab_Name

2. Verwenden Sie SELECT * less

Manche Benutzer wählen bei der Datenbankabfrage möglicherweise alles aus, was sie abfragen möchten. Dies ist ein unangemessenes Verhalten. Wir sollten nur die Daten nehmen, die wir benötigen, und nicht alle, denn wenn wir eine Auswahl treffen, erhöht sich die Belastung des Webservers, die Netzwerkübertragungslast nimmt zu und natürlich die Abfragegeschwindigkeit wird reduziert.

ERKLÄREN AUSWÄHLEN

Es wird geschätzt, dass viele Leute diese Funktion nicht gesehen haben, aber es wird dringend empfohlen, sie hier zu verwenden. EXPLAIN zeigt, wie MySQL Indizes verwendet, um Auswahlanweisungen zu verarbeiten und Tabellen zu verknüpfen. Es kann dabei helfen, bessere Indizes auszuwählen und optimiertere Abfrageanweisungen zu schreiben. Die Hauptverwendung besteht darin, vor der Auswahl eine Erklärung hinzuzufügen.

EXPLAIN SELECT [Suchfeldname] FROM tab_name ...

4. Aktivieren Sie den Abfrage-Cache

Auf den meisten MySQL-Servern ist der Abfragecache aktiviert. Dies ist eine der effektivsten Möglichkeiten zur Verbesserung der Leistung und wird von der MySQL-Datenbank-Engine übernommen. Wenn viele identische Abfragen mehrmals ausgeführt werden, werden die Abfrageergebnisse in einen Cache gestellt, sodass nachfolgende identische Abfragen direkt auf die zwischengespeicherten Ergebnisse zugreifen können, ohne die Tabelle bearbeiten zu müssen.

Der erste Schritt besteht darin, query_cache_type auf ON zu setzen und dann abzufragen, ob die Systemvariable have_query_cache verfügbar ist:

Variablen wie „have_query_cache“ anzeigen

Weisen Sie anschließend dem Abfragecache Speichergröße zu, um den Maximalwert der zwischengespeicherten Abfrageergebnisse zu steuern. Zugehörige Vorgänge werden in der Konfigurationsdatei geändert.

5. NOT NULL verwenden

Viele Tabellen enthalten Spalten, die NULL sind (leerer Wert), auch wenn die Anwendung NULL nicht speichern muss, da NULL die Standardeigenschaft der Spalte ist. Normalerweise empfiehlt es sich, anzugeben, dass die Spalte NICHT NULL ist, es sei denn, es besteht wirklich ein Bedarf zum Speichern von NULL-Werten.

Abfragen mit NULL-fähigen Spalten sind für MySQL schwieriger zu optimieren, da NULL-fähige Spalten Indizes, Indexstatistiken und Wertevergleiche komplexer machen. Spalten, die NULL sein können, benötigen mehr Speicherplatz und erfordern eine spezielle Behandlung in MySQL. Beim Indizieren einer NULL-fähigen Spalte wird für jeden Indexdatensatz ein zusätzliches Byte benötigt, was in MyISAM sogar dazu führen kann, dass aus einem Index mit fester Größe (wie etwa einem Index für eine einzelne Ganzzahlspalte) ein Index mit variabler Größe wird.

Normalerweise ist die Leistungsverbesserung durch die Änderung einer NULL-fähigen Spalte in NOT NULL gering. Es besteht daher keine Notwendigkeit, diese Situation zuerst im vorhandenen Schema zu finden und zu ändern, es sei denn, Sie sind sicher, dass dies zu Problemen führen wird. Wenn Sie jedoch vorhaben, einen Index für eine Spalte zu erstellen, sollten Sie versuchen, zu vermeiden, dass dieser NULL-fähig ist. Natürlich gibt es Ausnahmen. Erwähnenswert ist beispielsweise, dass InnoDB ein separates Bit zum Speichern von NULL-Werten verwendet und daher eine gute Speichereffizienz für spärliche Daten aufweist. Dies gilt nicht für MyISAM.

6. Auswahl der Speicher-Engine

Im Hinblick auf die Auswahl zwischen MyISAM und InnoDB ist InnoDB möglicherweise die bessere Wahl, wenn Sie Transaktionsverarbeitung oder Fremdschlüssel benötigen. Wenn Sie eine Volltextindizierung benötigen, ist MyISAM normalerweise eine gute Wahl, da es in das System integriert ist. Wir testen jedoch nicht oft 2 Millionen Zeilen. Auch wenn es etwas langsamer ist, können wir mithilfe von Sphinx Volltextindizes von InnoDB erhalten.

Die Datengröße ist ein wichtiger Faktor, der Ihre Wahl der Speicher-Engine beeinflusst. Bei großen Datensätzen wird InnoDB häufig gewählt, da es die Transaktionsverarbeitung und Fehlerbehebung unterstützt. Die Größe der Datenbank bestimmt, wie lange die Wiederherstellung nach einem Fehler dauert. InnoDB kann zur Datenwiederherstellung Transaktionsprotokolle verwenden, was schneller geht. MyISAM benötigt möglicherweise

Anstatt Stunden oder sogar Tage für die Ausführung dieser Aufgaben zu benötigen, benötigt InnoDB nur wenige Minuten.

Ihre Gewohnheiten beim Umgang mit Datenbanktabellen können ebenfalls ein Faktor sein, der erhebliche Auswirkungen auf die Leistung hat. Beispiel: COUNT() ist in einer MyISAM-Tabelle sehr schnell, kann in einer InnoDB-Tabelle jedoch mühsam sein. Primärschlüsselabfragen sind unter InnoDB sehr schnell, wir müssen jedoch darauf achten, dass es zu Leistungsproblemen kommt, wenn unser Primärschlüssel zu lang ist. Mit MyISAM sind viele Einfügungen schneller, mit InnoDB hingegen sind Aktualisierungen schneller – insbesondere bei hoher Parallelität.

Also, welches verwenden Sie? Erfahrungsgemäß ist MyISAM möglicherweise besser geeignet, wenn es sich um kleinere Anwendungen oder Projekte handelt. Natürlich gibt es Zeiten, in denen MyISAM in großen Umgebungen mit großem Erfolg verwendet werden kann, aber das ist nicht immer der Fall. Wenn Sie ein Projekt mit sehr großen Datenmengen durchführen möchten und Transaktionsverarbeitung oder Fremdschlüsselunterstützung benötigen, sollten Sie unbedingt direkt InnoDB verwenden. Bedenken Sie jedoch, dass InnoDB-Tabellen mehr Arbeitsspeicher und Speicherplatz erfordern und die Konvertierung einer MyISAM-Tabelle mit 100 GB in eine InnoDB-Tabelle zu sehr schlechten Erfahrungen führen kann.

7. Vermeiden Sie die Verwendung von „or“ zum Verbinden in der Where-Klausel

Wenn ein Feld einen Index hat und das andere nicht, verwirft die Engine den Index und führt einen vollständigen Tabellenscan durch, beispielsweise:

wähle die ID aus t, wobei num=10 oder Name = 'admin'

Sie können wie folgt abfragen:

wähle ID aus t, wobei Num = 10
Vereinigung alle
wähle die ID aus t, wobei Name = „admin“ ist

8. Verwenden Sie varchar/nvarchar häufiger

Verwenden Sie varchar/nvarchar statt char/nchar, da erstens Felder mit variabler Länge weniger Speicherplatz beanspruchen und dadurch Speicherplatz gespart werden kann. Zweitens ist die Suche in einem relativ kleinen Feld bei Abfragen offensichtlich effizienter.

9. Vermeiden Sie die Rückgabe großer Datenmengen

Hier sollten Sie die Verwendung von Limit in Betracht ziehen, um die zurückgegebene Datenmenge zu begrenzen. Wenn jedes Mal eine große Menge unnötiger Daten zurückgegeben wird, verringert sich auch die Abfragegeschwindigkeit.

10. Where-Klausel-Optimierung

Die Verwendung von Parametern in der Where-Klausel führt zu einem vollständigen Tabellenscan, da SQL lokale Variablen nur zur Laufzeit auflöst, der Optimierer die Auswahl eines Zugriffsplans jedoch nicht bis zur Laufzeit aufschieben kann; er muss die Auswahl zur Kompilierungszeit treffen. Wenn der Zugriffsplan jedoch zur Kompilierungszeit erstellt wird, ist der Wert der Variablen noch unbekannt und kann nicht als Eingabe für die Indexauswahl verwendet werden.

Versuchen Sie, Ausdrucksoperationen auf Feldern in der Where-Klausel zu vermeiden, und vermeiden Sie Funktionsoperationen auf Feldern in der Where-Klausel, da dies dazu führt, dass die Engine die Verwendung von Indizes aufgibt und einen vollständigen Tabellenscan durchführt. Führen Sie keine Funktionen, Rechenoperationen oder andere Ausdrucksoperationen auf der linken Seite des "=" in der Where-Klausel aus, da das System den Index sonst möglicherweise nicht richtig verwendet.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels Ihnen bei Ihrem Studium oder Ihrer Arbeit helfen kann. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.