MySQL ist das zweitbeliebteste relationale Open-Source-Datenbankverwaltungssystem der Welt. Es wird weltweit wegen seiner konstant schnellen Leistung, hohen Zuverlässigkeit und Benutzerfreundlichkeit eingesetzt. Dieser Artikel stellt einige der Best Practices für MySQL vor.

Mit ihnen können Sie SQL üben und sich einige SQL-Übungen vornehmen, sich online über SQL-Limit und SQL-Übungen sowie über die häufigsten SQL-Übungsprobleme informieren.

Es wird Ihnen helfen, Zeilennummer-SQL, SQL-Top, MySQL-Limit-Offset und SQL-Online-Praxis zu verstehen.

Wenn Sie nach einer netten und einfachen Möglichkeit suchen, den Besuchern Ihrer WordPress-Website eine große Anzahl von Zeilen und Spalten mit farbenfrohen, informativen und reaktionsschnellen interaktiven Tabellen und Diagrammen anzuzeigen, können Sie sie mit dem wpDataTables-Plugin verwenden, die Preise und all das überprüfen Funktionalitäten gibt es hier.

1 Verwenden Sie immer den richtigen Datentyp

Eine der wichtigsten Best Practices von MySQL ist die Verwendung von Datentypen basierend auf der Art der Daten. Die Verwendung irrelevanter Datentypen kann mehr Platz verbrauchen oder zu Fehlern führen.

Beispiel: Die Verwendung von varchar (20) anstelle des Datentyps DATETIME zum Speichern von Datums- und Uhrzeitwerten führt zu Fehlern bei datums- und zeitbezogenen Berechnungen. Außerdem ist es möglich, dass ungültige Daten gespeichert werden.

2 Verwenden Sie CHAR (1) über VARCHAR (1)

VARCHAR (1) benötigt zusätzliche Bytes zum Speichern von Informationen. Wenn Sie also ein einzelnes Zeichen aneinanderreihen, ist es besser, CHAR (1) zu verwenden.

3 Verwenden Sie den Datentyp CHAR, um nur Daten fester Länge zu speichern

Beispiel: Wenn die Länge der Daten weniger als 1000 beträgt, verbraucht die Verwendung von char (1000) anstelle von varchar (1000) mehr Speicherplatz.

4 Vermeiden Sie regionale Datumsformate

Wenn Sie den Datentyp DATETIME oder DATE verwenden, verwenden Sie immer das Datumsformat JJJJ-MM-TT oder das ISO-Datumsformat, das für Ihre SQL-Engine geeignet ist. Regionale Formate wie TT-MM-JJJJ oder MM-TT-JJJJ werden nicht richtig gespeichert.

5 Indexschlüsselspalten

Es ist wünschenswert, dass die Abfrage das Ergebnis schnell zurückgibt, also stellen Sie sicher, dass Sie die Spalten indizieren, die in JOIN-Klauseln verwendet werden.

Falls Sie die UPDATE-Anweisung verwenden, die mehr als eine Tabelle betrifft, stellen Sie sicher, dass Sie alle Spalten indizieren, die zum Verbinden der Tabellen verwendet werden.

6 Verwenden Sie keine Funktionen über indizierten Spalten…

… denn dann verliert der Index seinen Zweck.

Angenommen, Sie möchten Daten abrufen, bei denen die ersten beiden Zeichen des Kundencodes AK sind. Schreiben:

SELECT columns FROM table WHERE customer_code like 'AK%'

und nicht schreiben

SELECT columns FROM table WHERE left (customer_code,2)='AK’

Wieso den? Das erste Beispiel verwendet den Index, was zu einer schnelleren Reaktionszeit führt.

**7 Verwenden Sie SQL SELECT * nur bei Bedarf**

Befolgen Sie die Liste der Best Practices für MySQL und verwenden Sie nicht einfach blind SELECT * im Code. Wenn die Tabelle viele Spalten hat, werden alle zurückgegeben. Dies verlangsamt die Antwortzeit, insbesondere wenn Sie das Ergebnis an eine Front-End-Anwendung senden.

Geben Sie stattdessen explizit die tatsächlich benötigten Spaltennamen aus.

Hinweis: Denken Sie daran, dass alle SELECT-Anweisungen eine WHERE-Klausel erfordern.

8 Verwenden Sie die ORDER BY-Klausel nur bei Bedarf

Wenn Sie das Ergebnis in der Frontend-Anwendung anzeigen möchten, lassen Sie die Ergebnismenge ORDER. Wenn Sie dies in SQL tun, kann die Antwortzeit in der Mehrbenutzerumgebung verlangsamt werden.

9 Wählen Sie eine geeignete Datenbank-Engine

Wenn Sie eine Anwendung entwickeln, die Daten häufiger liest als schreibt (z. B. eine Suchmaschine), wählen Sie die Speicher-Engine MyISAM.

Die Wahl der falschen Speicher-Engine wirkt sich auf die Leistung aus.

10 Verwenden Sie bei Bedarf die EXISTS-Klausel

Verwenden Sie zum Überprüfen des Vorhandenseins von Daten die EXISTS-Klausel, die eine schnellere Antwortzeit hat. Verwenden Sie zum Beispiel:

If EXISTS(SELECT * from Table WHERE col=’some value’)

Verwende nicht :

If (SELECT count(*) from Table WHERE col=’some value’)>0

11 ERLÄUTERN Sie Ihre SELECT-Abfragen

Wenn Sie das Schlüsselwort EXPLAIN verwenden, erhalten Sie einen Einblick, was MySQL tut, um Ihre Abfrage auszuführen. Dies kann Ihnen helfen, Probleme mit Ihren Abfrage- oder Tabellenstrukturen (z. B. Engpässe) zu erkennen.

Eine EXPLAIN-Abfrage zeigt Ihnen, welche Indizes verwendet werden, wie die Tabelle gescannt, sortiert usw. wird.

Alles, was Sie tun müssen, ist das Schlüsselwort EXPLAIN vor einer SELECT-Abfrage (vorzugsweise einer komplexen mit Joins) hinzuzufügen. Wenn Sie dafür phpmyadmin verwenden, werden Ihre Ergebnisse auch in einer schönen Tabelle angezeigt.

12 Verwenden Sie LIMIT 1, wenn Sie eine eindeutige Zeile erhalten

Manchmal wissen Sie im Voraus, dass Sie beim Abfragen Ihrer Tabellen nur nach einer Zeile suchen. Beispielsweise könnten Sie einen eindeutigen Datensatz abrufen oder nur das Vorhandensein einer beliebigen Anzahl von Datensätzen überprüfen, die Ihre WHERE-Klausel erfüllen.

In solchen Fällen sollten Sie die MySQL-Limit-Funktion verwenden, um die Leistung zu steigern. Hier ist eine weitere Best Practice für MySQL: Fügen Sie einfach LIMIT 1 zu Ihrer Abfrage hinzu. Auf diese Weise muss die Datenbank-Engine nicht die gesamte Tabelle oder den gesamten Index durchlaufen. Es stoppt das Scannen, wenn es nur 1 Datensatz von dem findet, wonach Sie suchen.

// do I have anyusersfrom Alabama?

// was NICHT zu tun ist:
$r = mysql_query(„SELECT * FROM user WHERE state = ‚Alabama’");
if (mysql_num_rows($r) > 0) {
// …
}

// viel besser:
$r = mysql_query(„SELECT 1 FROM user WHERE state = ‚Alabama‘ LIMIT 1");
if (mysql_num_rows($r) > 0) {
// …
}

13 Indizieren und verwenden Sie die gleichen Spaltentypen für Joins

Ein weiterer wichtiger Tipp zu den Best Practices von MySQL – wenn Ihre Anwendung viele JOIN-Abfragen hat, stellen Sie sicher, dass die Spalten, mit denen Sie beitreten, in beiden Tabellen indiziert sind. Dies wirkt sich auf die interne Optimierung der Join-Operation durch MySQL aus.

Außerdem müssen die verbundenen Stützen vom gleichen Typ sein. Wenn Sie beispielsweise eine DECIMAL-Spalte mit einer INT-Spalte aus einer anderen Tabelle verknüpfen, kann MySQL keinen der Indizes verwenden. Sogar die Zeichencodierungen müssen für Zeichenfolgenspalten vom gleichen Typ sein.

// looking for companies in my state $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id");

// beide Zustandsspalten sollten indiziert sein
// und beide sollten den gleichen Typ und die gleiche Zeichenkodierung haben
// oder MySQL könnte vollständige Tabellenscans durchführen

14 MySQL vor dem Internet verstecken

Erfahrene Datenbankadministratoren und Sicherheitspersonal wissen es – hosten Sie die Datenbank niemals unter dem Stammverzeichnis des Webservers.

Für webfähige Anwendungen sollte MySQL hinter einer Firewall verborgen werden. Die Kommunikation sollte nur zwischen Anwendungsservern und Ihren Webservern aktiviert werden.

Eine weitere Option ist die Verwendung von MySQL Skip-Networking. Wenn es aktiviert ist, lauscht MySQL nur auf lokale Socket-Verbindungen und ignoriert alle TCP-Ports.

15 Verwenden Sie möglichst kleine Datentypen

Lass mich dir eine Geschichte erzählen. Als ich das College besuchte, war die Philosophie, dass „Erinnerung knapp ist“. Das waren die Zeiten der 256-MB-Festplatten. Heutzutage scheint sich niemand mehr um Arbeitsspeicher oder Festplattenspeicher zu kümmern. Die neue Philosophie lautet: „Speicher ist billig“. Auf Dollarbasis mag das stimmen, aber das Lesen großer Datentypen dauert immer noch länger als das Lesen kleinerer. Bei großen Datentypen müssen mehr Festplattensektoren in den Speicher eingelesen werden.

Die Moral ist, ignorieren Sie die Versuchung, sofort zum größten Datentyp zu springen, wenn Sie Ihre Tabellen entwerfen. Denken Sie darüber nach, ein int anstelle eines bigint zu verwenden.

Vermeiden Sie außerdem Largechar (255)-Textfelder, wenn ein Varchar oder ein kleineres Zeichen ausreicht.

Wenn Sie den richtigen Datentyp verwenden, passen mehr Datensätze in den Speicher- oder Indexschlüsselblock. Dies führt zu weniger Lesevorgängen und einer schnelleren Leistung.

16 Nutzen Sie das Abfrage-Caching

Das Zwischenspeichern von Abfragen ist eine der effektivsten Methoden zur Verbesserung der Leistung. Die meisten MySQL-Server haben es standardmäßig aktiviert.

Der Abfrage-Cache speichert den Text einer SELECT-Anweisung zusammen mit der entsprechenden Ergebnismenge. Wenn der Server später eine identische Anweisung erhält, ruft er die Ergebnisse aus dem Abfragecache ab, anstatt die Anweisung erneut zu analysieren und auszuführen. Der Abfrage-Cache wird von Sitzungen gemeinsam genutzt, sodass ein von einem Client generierter Ergebnissatz als Antwort auf dieselbe Abfrage gesendet werden kann, die von einem anderen Client ausgegeben wurde.

So großartig es auch ist, das Caching von Abfragen hat jedoch seine Grenzen. Nehmen Sie die folgende Aussage:

Das Problem dabei ist, dass Abfragen bestimmte nicht deterministische Funktionen wie NOW() und RAND() enthalten. MySQL kann solche Funktionen nicht im Voraus berechnen, sodass sie nicht zwischengespeichert werden.

Glücklicherweise gibt es dafür eine einfache Lösung: Sie können die Funktionsergebnisse in einer Variablen speichern.

17 Bearbeiten Sie keine Dump-Dateien

Dump-Dateien sind sehr irreführend und können zu Beschädigungen führen. Wieso den? Wenn Sie jemals die von mysqldump erstellten Dump-Dateien gesehen haben, werden Sie zustimmen, dass sie wie normale, harmlose Textdateien aussehen. Aus diesem Grund bearbeiten die meisten Leute sie in einem Standard-Texteditor, was zu Beschädigungen führt.

Wenn Sie jemals versucht haben, Dump-Dateien zu bearbeiten, haben Sie schnell gelernt, dass sie alles andere als eine Textdatei sind. Die einzige garantierte Möglichkeit, Probleme zu vermeiden, besteht also darin, die Dump-Dateien in Ruhe zu lassen.

18 Verwenden Sie die MyISAM-Blockgrößeneinstellung

Die Einstellung von Blockgrößen in den Indizes von MyISAM-Tabellen gehört zu den Best Practices von MySQL. Es kann in den .MYI-Dateien im Schlüsselpuffer sowie auf der Disc gefunden werden. Die Einstellung sieht so aus: myisam_block_size.

Es hat einen Standardwert von 1k. Es ist ziemlich klein, um auf einem modernen System optimal zu sein. Größere Blöcke werden von den meisten Dateisystemen verwendet. Und wir wissen, dass das Schreiben eines einzelnen Indexblocks ein Lesen und dann ein Schreiben erfordert. Das Betriebssystem muss niemals auf die zugrunde liegende Disc schreiben, solange die Blockgröße gleich oder größer als die Blockgröße des Dateisystems ist.

19 Schalten Sie delay_key_write ein

Delay_key_write ist standardmäßig ausgeschaltet. Dafür gibt es einen Grund. Wenn Sie mitten im Projekt einen Absturz erlebt haben, könnte Ihre Datenbank beschädigt werden.

Also, warum willst du es einschalten? Der Grund ist einfach. Weil delay_key_write sicherstellt, dass die Datenbank die MyISAM-Schlüsseldatei nicht nach jedem einzelnen Schreibvorgang leert. Wenn Sie also in naher Zukunft einen weiteren Schreibvorgang durchführen, sparen Sie viel Zeit.

Hier ist ein weiterer cooler Tipp, den wir aus den Best Practices von MySQL ausgewählt haben: Das Einschalten von delay_key_write ist für jede Version anders. Um zu sehen, wie man es in einer bestimmten Version einschaltet, konsultieren Sie das offizielle MySQL-Site-Handbuch.

20 Verwenden Sie Stack Trace, um Fehler zu isolieren

In Anlehnung an diese bewährten MySQL-Methoden ist dieser Tipp von Sky SQL ausgeliehen, weil er einfach zu einfach und zu bequem ist, um ihn auszulassen.

MySQL stack_trace kann verwendet werden, um verschiedene Fehler zu isolieren. Anstatt sich mit allen möglichen Verwendungen zu beschäftigen, möchte der Programmierer vielleicht zur Kenntnis nehmen, wie leicht ein Nullzeiger Ihren Code ruinieren kann.

Mit diesem Sky SQL-Tipp wird das Erkennen, Verfolgen und Reparieren viel einfacher.

21 MySQL ändert das ROOT-Passwort

Das Ändern des ROOT-Passworts mag einfach erscheinen, aber zu wissen, wie es geht, ist sowohl für Ihr Heimbetriebssystem als auch für MySQL-Server gleichermaßen wichtig.

Manchmal kommt es vor, dass übereifrige Anfänger und zerstreute erfahrene Benutzer nicht herausfinden können, warum sie bestimmte Einstellungen nicht ändern können und Fehler zurückgeben. Es kann so einfach sein, sich ROOT-Zugriff zu geben. Ein Benutzer sollte es nicht einmal googeln müssen.

So richten Sie das ROOT-Passwort ein und ändern das ROOT-Passwort eines Benutzers:

//Straightforward MySQL 101 $mysqladmin -u rootpassword [Type in selectedpassword] //Changingusers ROOT password $mysqladmin -u root -p [type oldpassword] newpass [hit enter and type new password. Pressenter] //Use mysqlsqlcommand $mysql -u root -p //prompt "mysql>" pops up. Enter: $use MySQL; //Enter usernameyouwant to change the password for $update user set password=PASSWORD (Type new PasswordHere) where User = 'username'; //Don'tforget the previoussemicolon, nowreload the settings for the user'sprivileges $flush privileges; $quit

22 Korrigieren Sie Ihre Konfigurationsdateien

MySQL Tuner ist ein Perl-Skript, das Ihre Leistung irgendwie optimieren kann, indem es Änderungen an Ihren Konfigurationsdateien vorschlägt.

Wenn einige Tipps und Tricks in MySQL erstaunlich praktisch sind, sind Tools wie MySQL Tuner ein Geschenk des Himmels, das es verdient, in eine eigene Kategorie aufgenommen zu werden.

Es ist etwas, das von Anfängern und Profis gleichermaßen verwendet werden kann. MySQL Tuner ist kein spezieller Tipp für MySQL, daher gibt es eine Vielzahl von Optimierungen und Mods, die angewendet werden können. Je mehr Sie es verwenden, desto mehr Optimierungen können Sie für Ihren eigenen Gebrauch anwenden.

Es mag zunächst einschüchternd wirken, aber deshalb finden Sie das Handbuch –man db sowie alle Hinweise, notwendige Lektüre und Einstellungen auf der offiziellen Projekt-Homepage. Mit all dem sollte MySQL Tuner schnell zu Ihrem bevorzugten Tool werden, um MySQL zu beschleunigen und Ihre Konfigurationsdateien zu testen.

Abschließende Gedanken zu den besten MySQL-Praktiken

In der Welt der Informatik ist MySQL zweifellos eines der wichtigsten und einflussreichsten Programme, die jemals erschienen sind. Es ist so kompliziert und vielseitig, dass gerade dann, wenn es so aussieht, als ob alle möglichen Tricks und Abkürzungen entdeckt wurden, jemand anderes mit einer neuen Art und Weise einsteigt, ein neues Argument oder eine neue Einstellung zu verwenden.

Wir haben nur Best Practices für MySQL skizziert, die jeder kennen sollte. Aber je mehr Sie MySQL verwenden, desto mehr werden Sie diese verwenden. Sie können SQL online üben und selbst SQL-Tests durchführen. Anschließend entwickeln Sie Ihre eigenen Tricks und finden Ihre eigenen Top-Tipps, auf die Sie sich verlassen können, wie z.

Wenn Ihnen dieser Artikel über Best Practices für MySQL gefallen hat, sollten Sie auch diese lesen:

Aufnahmequelle: wpdatatables.com