Acht „Master-Tipps“ zur Optimierung der MySQL-Datenbankleistung

In diesem Artikel werden Ideen zur Verbesserung der MySQL-Datenbankleistung untersucht und spezifische Lösungen aus 8 Aspekten bereitgestellt.

1. Wählen Sie die am besten geeigneten Feldattribute aus.

MySQL kann den Zugriff auf große Datenmengen gut unterstützen, aber im Allgemeinen gilt: Je kleiner die Tabelle in der Datenbank, desto schneller werden die darauf ausgeführten Abfragen. Um eine bessere Leistung zu erzielen, können wir daher beim Erstellen einer Tabelle die Breite der Felder in der Tabelle so klein wie möglich einstellen. Wenn Sie beispielsweise das Feld „Postleitzahl“ auf CHAR(255) festlegen, wird der Datenbank offensichtlich unnötiger Speicherplatz hinzugefügt. Selbst die Verwendung des Typs VARCHAR ist überflüssig, da CHAR(6) in Ordnung ist. Ebenso sollten wir, wenn möglich, MEDIUMINT anstelle von BIGIN verwenden, um Ganzzahlfelder zu definieren.

Eine weitere Möglichkeit zur Verbesserung der Effizienz besteht darin, Felder nach Möglichkeit auf NOT NULL zu setzen, damit die Datenbank bei zukünftigen Abfragen keine NULL-Werte vergleichen muss.

Für einige Textfelder, wie zum Beispiel „Provinz“ oder „Geschlecht“, können wir sie als ENUM-Typen definieren. Denn in MySQL wird der ENUM-Typ als numerische Daten behandelt und numerische Daten werden viel schneller verarbeitet als Texttypen. Auf diese Weise können wir die Leistung der Datenbank verbessern.

2. Verwenden Sie Joins (JOIN) anstelle von Unterabfragen (Sub-Queries).

MySQL unterstützt SQL-Unterabfragen ab 4.1. Mit dieser Technik können Sie mit einer SELECT-Anweisung eine einzelne Spalte mit Abfrageergebnissen erstellen und dieses Ergebnis dann als Filterbedingung in einer anderen Abfrage verwenden. Wenn wir beispielsweise Kunden löschen möchten, die keine Bestellungen in der Basis-Kundeninformationstabelle haben, können wir mithilfe einer Unterabfrage zunächst die IDs aller Kunden, die Bestellungen aufgegeben haben, aus der Verkaufsinformationstabelle abrufen und dann die Ergebnisse an übergeben die Hauptabfrage, wie unten gezeigt:

DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

Durch die Verwendung von Unterabfragen können viele SQL-Vorgänge ausgeführt werden, für deren Ausführung logischerweise mehrere Schritte gleichzeitig erforderlich sind. Außerdem können Transaktions- oder Tabellensperren vermieden werden, und das Schreiben ist ebenfalls einfach. In einigen Fällen können Unterabfragen jedoch durch effizientere Verknüpfungen (JOIN) ersetzt werden. Angenommen, wir möchten alle Benutzer abrufen, die keine Bestelldatensätze haben, können wir die folgende Abfrage verwenden:

SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

Wenn Sie die Verbindung (JOIN) verwenden, um diese Abfrage abzuschließen, ist die Geschwindigkeit viel schneller. Insbesondere wenn in der Salesinfo-Tabelle ein Index für CustomerID vorhanden ist, ist die Leistung wie folgt:

SELECT * FROM customerinfo
LINKS BEITRAGEN salesinfoON customerinfo.CustomerID=salesinfo.
Kunden-ID
WHERE salesinfo.CustomerID IS NULL

Legen Sie den Datenpfad fest und platzieren Sie die Datenbankdatendateien im freigegebenen NFS-Verzeichnis (NAS-Server).

Die PID- und innioDB-Dateien müssen im lokalen Verzeichnis des Servers abgelegt werden, um den Dienst normal zu starten und zu stoppen:

1125

vi

/etc/ my.cnf

[mysqld]

ich

eine Datenbank

installieren

?

innodb_data_file_path=ibdata1: 50M;ibdata2:50M:autoextend

1106 cp ./support-files/mysql.server /etc/rc.d/init.d/

vi /etc/rc.d/init.d/mysql.server

Kompilierungsbezogen Elemente ab 222. Die beiden Zeilen von , platzieren die PID-Datei im lokalen Verzeichnis des Servers:

pid_file=/usr/local/mysql/data/mysqlmanager-`/bin/hostname`.pid

server_pid_file=/usr/local/mysql /data/`/bin/ hostname`.pid

installiert die Basisdatenbank von MySQL:

1123 mount 10.4.66.251:/data /data

1124 mkdir /data/mysqldata

1127 ./scripts/mysql_install_db --user=mysql

1145 chown -R mysql .mysql /data/mysqldata/

wenn normal, können Sie sehen, dass MySQL normal startet

1146 /etc/rc.d/init.d/mysql.server start

1146 /etc/rc.d/init.d/mysql.server Stoppen Sie

die Konfiguration der HA-Hochverfügbarkeit, stellen Sie sie nicht ein. NFS-Mount und MySQL-Dienst werden beim Starten des Servers automatisch ausgeführt.

5. Sperrtabellen

Obwohl Transaktionen aufgrund ihrer Exklusivität eine sehr gute Möglichkeit sind, die Integrität der Datenbank aufrechtzuerhalten, wirkt sie sich manchmal auf die aus Leistung der Datenbank, insbesondere über sehr lange Zeiträume in großen Anwendungssystemen. Da die Datenbank während der Ausführung der Transaktion gesperrt wird, können andere Benutzeranforderungen nur warten, bis die Transaktion beendet ist. Wenn ein Datenbanksystem nur von wenigen Benutzern verwendet wird, stellen die Auswirkungen von Transaktionen kein großes Problem dar. Wenn jedoch Tausende von Benutzern gleichzeitig auf ein Datenbanksystem zugreifen, beispielsweise auf eine E-Commerce-Website, führt dies zu ernsthaften Problemen Reaktionsverzögerung.

Tatsächlich können wir in manchen Fällen eine bessere Leistung erzielen, indem wir die Tabelle sperren. Das folgende Beispiel verwendet die Sperrtabellenmethode, um die Transaktionsfunktion im vorherigen Beispiel abzuschließen.

LOCK TABLE-Inventar SCHREIBEN
WÄHLEN Sie die Menge AUS dem Lagerbestand aus
WHEREItem='book';
...
Inventar aktualisieren SET Menge=11
WHEREItem='book';
TABELLEN ENTSPERREN

Hier verwenden wir eine SELECT-Anweisung, um die Anfangsdaten abzurufen, und verwenden durch einige Berechnungen eine UPDATE-Anweisung, um die neuen Werte in der Tabelle zu aktualisieren. Die LOCK TABLE-Anweisung mit dem Schlüsselwort WRITE stellt sicher, dass vor der Ausführung des UNLOCK TABLES-Befehls kein weiterer Zugriff auf den Bestand zum Einfügen, Aktualisieren oder Löschen erfolgt.

6.

Die Verwendung von Fremdschlüsseln zum Sperren der Tabelle kann die Integrität der Daten aufrechterhalten, jedoch nicht die Relevanz der Daten garantieren. Zu diesem Zeitpunkt können wir Fremdschlüssel verwenden. Beispielsweise kann ein Fremdschlüssel sicherstellen, dass jeder Verkaufsdatensatz auf einen bestehenden Kunden verweist. Hier kann der Fremdschlüssel die CustomerID in der Tabelle „customerinfo“ der Tabelle „customerID“ in der Tabelle „salesinfo“ zuordnen. Jeder Datensatz ohne gültige CustomerID wird nicht aktualisiert oder in salesinfo eingefügt.

TABELLE Kundeninformationen ERSTELLEN
(
CustomerID INT NOT NULL ,
PRIMÄRSCHLÜSSEL (Kunden-ID)
) TYPE = INNODB;

CREATE TABLE salesinfo
(
SalesID INT NICHT NULL,
CustomerID INT NOT NULL,
PRIMÄRSCHLÜSSEL (CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENZEN customerinfo
(CustomerID) AUF DELETECASCADE
) TYPE = INNODB;

Beachten Sie im Beispiel den Parameter „ON DELETE CASCADE“. Dieser Parameter stellt sicher, dass beim Löschen eines Kundendatensatzes in der Tabelle „customerinfo“ auch alle Datensätze, die sich auf den Kunden in der Tabelle „salesinfo“ beziehen, automatisch gelöscht werden. Wenn Sie Fremdschlüssel in MySQL verwenden möchten, müssen Sie beim Erstellen der Tabelle daran denken, den Tabellentyp als transaktionssicheren InnoDB-Typ zu definieren. Dieser Typ ist nicht der Standardtyp für MySQL-Tabellen. Die definierte Methode besteht darin, TYPE=INNODB zur CREATE TABLE-Anweisung hinzuzufügen. Wie im Beispiel gezeigt.

7. Die Verwendung von Indizes

ist eine gängige Methode zur Verbesserung der Datenbankleistung. Dadurch kann der Datenbankserver bestimmte Zeilen viel schneller abrufen als ohne Index, insbesondere wenn die Abfrageanweisung MAX(), MIN() und ORDERBY enthält. Die Leistungsverbesserung ist deutlicher. Welche Felder sollten also indiziert werden? Im Allgemeinen sollten Indizes auf Feldern erstellt werden, die für die JOIN-, WHERE-Beurteilung und ORDER BY-Sortierung verwendet werden. Versuchen Sie, kein Feld in der Datenbank zu indizieren, das eine große Anzahl doppelter Werte enthält. Bei einem Feld vom Typ ENUM ist es sehr wahrscheinlich, dass es eine große Anzahl doppelter Werte gibt, wie zum Beispiel das Feld „Provinz“. Die Erstellung eines Indexes für ein solches Feld ist im Gegenteil nicht hilfreich Reduzieren Sie die Datenbankleistung. Wir können beim Erstellen der Tabelle gleichzeitig entsprechende Indizes erstellen oder später mit ALTER TABLE oder CREATE INDEX Indizes erstellen. Darüber hinaus MySQL
Volltextindizierung und -suche werden ab Version 3.23.23 unterstützt. Der Volltextindex ist ein Index vom Typ FULLTEXT in MySQL, kann jedoch nur für Tabellen vom Typ MyISAM verwendet werden. Bei einer großen Datenbank geht es sehr schnell, die Daten in eine Tabelle ohne FULLTEXT-Index zu laden und dann mit ALTER TABLE oder CREATE INDEX den Index zu erstellen. Wenn Sie jedoch Daten in eine Tabelle laden, die bereits über einen FULLTEXT-Index verfügt, ist der Ausführungsprozess sehr langsam.

8. Optimierte Abfrageanweisungen

In den meisten Fällen kann die Verwendung von Indizes die Abfragegeschwindigkeit verbessern, aber wenn SQL-Anweisungen unsachgemäß verwendet werden, kann der Index nicht seine ihm gebührende Rolle spielen. Im Folgenden sind einige Aspekte aufgeführt, auf die geachtet werden sollte. Zunächst ist es am besten, Vergleichsoperationen zwischen Feldern desselben Typs durchzuführen. Vor MySQL-Version 3.23 war dies sogar eine erforderliche Bedingung. Beispielsweise kann ein indiziertes INT-Feld nicht mit einem BIGINT-Feld verglichen werden. Als Sonderfall ist jedoch ein Vergleich möglich, wenn ein Feld vom Typ CHAR und ein Feld vom Typ VARCHAR dieselbe Größe haben. Zweitens: Versuchen Sie, keine Funktionen für die Bearbeitung indizierter Felder zu verwenden.

Wenn Sie beispielsweise die Funktion YEAE() für ein Feld vom Typ DATE verwenden, funktioniert der Index nicht ordnungsgemäß. Obwohl die folgenden beiden Abfragen dieselben Ergebnisse liefern, ist die letztere daher viel schneller als die erstere.

SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01"

Die gleiche Situation tritt auch bei der Berechnung numerischer Felder auf:

SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;

Die beiden oben genannten Abfragen liefern ebenfalls die gleichen Ergebnisse, aber die letztere Abfrage ist viel schneller als die vorherige. Drittens verwenden wir bei der Suche nach Zeichenfeldern manchmal LIKE-Schlüsselwörter und Platzhalter. Obwohl dieser Ansatz einfach ist, geht er auch auf Kosten der Systemleistung. Die folgende Abfrage vergleicht beispielsweise jeden Datensatz in der Tabelle.

WÄHLEN Sie * AUS Büchern
WHERE-Name wie „MySQL%“.

Wenn Sie jedoch die folgende Abfrage verwenden, sind die zurückgegebenen Ergebnisse dieselben, aber die Geschwindigkeit ist viel schneller: ..

SELECT * FROM Books
WHERE name>="MySQL" und name<"MySQM"

Schließlich sollten Sie darauf achten, dass MySQL keine automatische Typkonvertierung in der Abfrage durchführt, da der Konvertierungsprozess auch den Index unwirksam macht.