Die 20 besten Empfehlungen für MySQL

Die MySQL- Datenbank hat sich aufgrund ihrer hohen Leistung, Konsistenz, hohen Zuverlässigkeit und Benutzerfreundlichkeit zur beliebtesten relationalen Open-Source-Datenbank der Welt entwickelt. Diese Vorteile, die es uns bietet, hängen jedoch häufig davon ab, wie wir damit arbeiten.

In diesem Tutorial lernen Sie eine Reihe von Tipps kennen, die sehr nützlich sind und es uns ermöglichen, die Sichtweise des Programmierers und des Datenbankadministrators optimal zu nutzen.

[/color] [color = rgb (169, 169, 169)] phpMyAdmin von XAMPP [/ color]

[/color] [color = # a9a9a9] phpMyAdmin von CPanel [/ color]

Mit diesen beiden Tools können wir all diese bewährten Methoden ausprobieren, die wir unten zeigen.

Dies sind die 20 Best Practices von MySQL:

1. Nomenklaturübereinkommen

Es gibt Codierungsregeln für jede Programmiersprache, aber in Bezug auf MySQL haben wir keine universelle Codierungspraxis gefunden, die jeder befolgt. Indem wir jedoch mehrere auf PHP basierende Open-Source-Frameworks überprüfen, filtern wir einige allgemeine Anwendungsregeln, die uns helfen, SQL-Abfragen schneller zu schreiben und Verwirrung und Konflikte sowohl in der Abfrage als auch in der von uns verwendeten Programmiersprache zu vermeiden.

2. Verwenden Sie immer den entsprechenden Datentyp

Verwenden Sie Datentypen, die auf der Art der Daten basieren. Wenn Sie irrelevante Datentypen verwenden, kann dies mehr Speicherplatz beanspruchen oder zu Fehlern führen.

3. Benutze CHAR (1) auf VARCHAR (1)

Wenn Sie eine einzelne Zeichenfolge speichern, verwenden Sie CHAR (1) anstelle von VARCHAR (1), da VARCHAR (1) ein zusätzliches Byte zum Speichern von Informationen benötigt. Worauf es bei der Verwaltung dieser Cartoons ankommt.

4. CHAR für feste Daten

Verwenden Sie CHAR-Daten, um nur Daten mit fester Länge zu speichern

Beispiel:

Die Verwendung von CHAR (1000) anstelle von VARCHAR (1000) belegt mehr Speicherplatz, wenn die Datenlänge unter 1000 liegt.

5. Vermeiden Sie die Verwendung regionaler Datumsformate

Bei Verwendung von Datentypen:

• DATETIME
• DATUM

Verwenden Sie immer das Format JJJJ-MM-TT oder das ISO-Datumsformat, das für Ihre SQL-Engine geeignet ist. Andere regionale Formate wie TT-MM-JJJ, TT-MM-JJJJ werden nicht korrekt gespeichert.

6. Optimieren Sie Ihre Anforderungen für den Cache

Auf den meisten MySQL-Servern ist das Cache-System aktiviert . Dies ist eine der effektivsten Methoden zur Leistungssteigerung, die mit der Datenbank-Engine einhergeht. Wenn dieselbe Anforderung mehrmals ausgeführt wird, wird das Ergebnis aus dem Cache abgerufen, der viel schneller ist.
Das folgende Beispiel ist in PHP:

 // Cache funktioniert NICHT $ r = mysql_query ("SELECT name FROM users WHERE record> = CURDATE ()"); 

 // Cache YES funktioniert $ today = date ("Ymd"); $ r = mysql_query ("SELECT name FROM users WHERE record> = '$ today'"); 

Der Grund, warum es im ersten Fall nicht funktioniert, ist die Verwendung von CURDATE (). Es kann auf alle nicht deterministischen Funktionen wie NOW () und RAND () angewendet werden. Da sich das von der Funktion zurückgegebene Ergebnis möglicherweise ändert, beschließt MySQL, den Cache in dieser Abfrage zu deaktivieren.

7. Vermeiden Sie die Verwendung von “SELECT *” in Ihren Konsultationen

Je mehr Daten aus den Tabellen gelesen werden, desto langsamer erfolgt in der Regel eine Abfrage. Angesichts der Tatsache, dass einige Produktionstabellen Dutzende von Spalten enthalten können, von denen einige aus großen Datentypen bestehen, ist es nicht ratsam, alle auszuwählen.

Es ist eine gute Angewohnheit , die erforderlichen Spalten in Ihrer SELECT-Anweisung anzugeben .

8. Verwenden Sie LIMIT 1, wenn Sie nur eine einzelne Zeile möchten

Manchmal, wenn Sie Ihre Tabellen konsultieren und wissen, dass Sie nur eine einzelne Zeile benötigen. In diesen Fällen müssen Sie ein einzelnes Ergebnis von der Datenbank anfordern, oder Sie überprüfen jede einzelne Übereinstimmung in der WHERE-Klausel.

In diesen Fällen kann das Hinzufügen von LIMIT 1 zu Ihrer Abfrage die Geschwindigkeit erheblich verbessern. Auf diese Weise stoppt die Datenbank das Scannen von Ergebnissen, wenn Sie eines finden, anstatt die gesamte Tabelle oder einen Index zu durchsuchen.

 // Ich habe User aus Madrid? // was NICHT zu tun ist: $ r = mysql_query ("SELECT * FROM user WHERE city = 'Madrid'"); if (mysql_num_rows ($ r)> 0) { // ... } // viel besser: $ r = mysql_query ("SELECT 1 FROM user WHERE city = 'Madrid' LIMIT 1"); if (mysql_num_rows ($ r)> 0) { // ... } 

[/color] [color = # a9a9a9] Empfehlung LIMIT [/ color]

9. Verwendung von ***** BY

Die Verwendung von ***** BY kann die Antwortzeit in Umgebungen mit mehreren Benutzern verlangsamen. Aus diesem Grund empfehlen wir, die Klausel ***** BY nur bei Bedarf zu verwenden.

Überbeanspruchen Sie es nicht.

10. Wählen Sie den geeigneten Basismotor

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

Wenn Sie eine Anwendung entwickeln, die Daten häufiger schreibt als liest
(Beispiel: Echtzeit-Banking), wählen Sie die InnoDB-Speicher-Engine.

Die Auswahl der falschen Speicher-Engine wirkt sich auf die Leistung Ihrer Abfragen aus.

11. Verwenden Sie bei Bedarf die EXISTS-Klausel

Wenn Sie das Vorhandensein von Daten überprüfen möchten, verwenden Sie nicht:

 If (SELECT count (*) aus Tabelle WHERE col = 'some value')> 0 

Verwenden Sie im Gegenzug die EXISTS-Klausel:

 If EXISTS (SELECT * aus der Tabelle WHERE col = 'some value') 

Welches ist schneller in der Reaktionszeit.

12. Verwenden Sie EXPLAIN in Ihren SELECT-Abfragen

Mit dem EXPLAIN-Schlüsselwort erhalten Sie viele interne Informationen darüber, wie MySQL Ihre Abfrage ausführt. Auf diese Weise können Sie Engpässe und andere Probleme mit Ihrer Abfrage oder der Struktur der Tabelle erkennen.

Das Ergebnis einer EXPLAIN-Abfrage zeigt an, welche Indizes verwendet werden, wie die Tabelle durchsucht wird, wie sie sortiert wird usw.

Wählen Sie eine SELECT-Abfrage (vorzugsweise eine komplexe mit Gewerkschaften) und fügen Sie das Wort EXPLAIN am Anfang von allem ein. Die Ergebnisse werden in einer einfachen Tabelle zurückgegeben. Angenommen, ich habe vergessen, einen Index für eine Spalte zu erstellen, dann wird der folgende Bildschirm angezeigt:

Nachdem Sie den Index zur Statustabelle hinzugefügt haben, sieht er folgendermaßen aus:

13. Indexa, und verwenden Sie denselben Spaltentyp für Join

Wenn Ihre Anwendung viele JOIN-Anweisungen enthält, müssen Sie sicherstellen, dass die von Ihnen verknüpften Spalten in beiden Tabellen indiziert sind. Dies beeinflusst, wie MySQL JOIN-Operationen intern optimiert.

Außerdem müssen die Spalten, die Sie verknüpfen, vom gleichen Typ sein. Wenn Sie beispielsweise eine Spalte vom Typ DECIMAL mit einer Spalte vom Typ INT einer anderen Tabelle verbinden, kann MySQL nicht mindestens einen der beiden Indizes verwenden. Auch die Zeichenkodierung muss für Spalten vom Typ String vom gleichen Typ sein.

 // suche nach firmen in meiner stadt $ r = mysql_query ("SELECT firmenname FROM benutzer LEFT JOIN companyias ON (users.city = companyias.city) WHERE users.id = $ user_id "); 

Beide Stadtspalten müssen indiziert sein, und beide müssen denselben Typ und dieselbe Zeichenkodierung aufweisen. Andernfalls muss MySQL einen vollständigen Scan der Tabellen durchführen.

14. Verwenden Sie NOT NULL, wenn Sie können

Sofern Sie keinen bestimmten Grund haben, den NULL-Wert zu verwenden, sollten Sie Ihre Spalten immer auf NOT NULL setzen.

Fragen Sie sich zunächst, ob es einen Unterschied zwischen einer leeren Zeichenfolge und einem NULL-Wert gibt (oder bei INT-Feldern: 0 gegen NULL). Wenn zwischen den beiden Werten kein Problem besteht, benötigen Sie kein NULL-Feld. NULL-Spalten benötigen zusätzlichen Speicherplatz und können Ihre Vergleichsanweisungen komplexer machen. Vermeiden Sie sie einfach, wann immer Sie können.

In jedem Fall haben wir Verständnis dafür, dass es in einigen sehr speziellen Fällen einen Grund gibt, NULL-Spalten zu verwenden, was nicht immer eine schlechte Sache ist.

15. Tabellen mit fester Größe (statisch) sind schneller

Wenn jede der Spalten in einer Tabelle eine feste Größe (“feste Länge”) hat, wird die gesamte Tabelle als “statisch” oder “feste Größe” betrachtet.

Einige Beispiele für Spaltentypen, die KEINE feste Größe haben, sind:

• VARCHAR
• TEXT
• BLOB

Wenn Sie nur einen dieser Spaltentypen angeben, hat die Tabelle keine feste Größe mehr und muss von der MySQL-Engine anders behandelt werden.

Tabellen mit fester Größe können die Produktivität steigern, da die MySQL-Engine Ihre Datensätze schneller durchsucht. Wenn Sie eine bestimmte Zeile der Tabelle lesen möchten, können Sie schnell die Position berechnen, die sie einnimmt. Wenn die Zeilengröße nicht festgelegt ist, müssen Sie bei jeder Suche zuerst den Index des Primärschlüssels überprüfen.

Sie sind auch leichter zu durchsuchen und nach einem Unfall wieder aufzubauen. Zum anderen könnten sie aber auch mehr Platz beanspruchen.

16. Vertikale Partitionierung

Vertikale Partitionierung ist der Vorgang, bei dem die Struktur Ihrer Tabelle aus Optimierungsgründen vertikal getrennt wird.

Beispiel 1:

Sicherlich haben Sie eine Benutzertabelle, die eine Postanschrift enthält, die nicht sehr häufig verwendet wird. Hier können Sie die Tabelle aufteilen und die Adressen in einer separaten Tabelle speichern. Auf diese Weise würde Ihre Hauptbenutzertabelle eine besser angepasste Größe haben. Wie Sie wissen, sind die Tabellen umso schneller, je kleiner sie sind.

Beispiel 2:

Sie haben ein “last_access” -Feld in Ihrer Tabelle. Es wird jedes Mal aktualisiert, wenn ein Benutzer auf Ihre Seite zugreift. Bei jedem Zugriff wird jedoch der Abfragecache dieser Tabelle freigegeben. Sie können dieses Feld in eine andere Tabelle einfügen, um die Änderungen in Ihrer Benutzertabelle auf ein Minimum zu beschränken.

Sie müssen jedoch auch sicherstellen, dass Sie die beiden Tabellen nach der Partitionierung nicht ständig verbinden müssen, da sonst die Leistung beeinträchtigt wird, genau im Gegenteil zu dem, wonach wir gesucht haben.

17. Speichern Sie IP-Adressen als UNSIGNED INT

Viele Programmierer würden ein VARCHAR (15) -Feld erstellen, ohne zu bemerken, dass sie IP-Adressen als Ganzzahlen speichern können. Wenn Sie ein INT verwenden, verwenden Sie nur 4 Bytes im Speicher und es hat auch eine feste Größe in der Tabelle.

Sie müssen jedoch sicherstellen, dass die Spalte UNSIGNED INT (vorzeichenlose Ganzzahl) lautet, da IP-Adressen den gesamten vorzeichenlosen 32-Bit-Bereich verwenden.

In Ihren Abfragen können Sie die Funktion INET_ATON () verwenden , um eine IP-Adresse in eine Ganzzahl zu konvertieren, und INET_NTOA (), um das Gegenteil zu tun. Es gibt auch ähnliche Funktionen in PHP namens ip2long () und long2ip () .

18. Erstellen Sie Ansichten, um die allgemeine Verwendung in Tabellen zu vereinfachen

Die Ansichten vereinfachen sowohl komplexe Schemata als auch die Sicherheitsimplementierung. Eine Möglichkeit, wie sie zum Sicherheitsteil beitragen, besteht darin, dass Sie die Namen der Felder vor den Entwicklern verbergen können.

Es kann auch verwendet werden, um nicht indizierte Spalten zu filtern, sodass nur die Felder in der Suche schneller angezeigt werden.

19. Verwenden Sie nicht ***** BY RAND ()

Dies ist einer dieser Tricks, die auf den ersten Blick großartig klingen und bei denen viele unerfahrene Programmierer zum Fallen neigen. Möglicherweise haben Sie den unglaublichen Engpass nicht erkannt, der entstehen kann, wenn Sie diese Technik in Ihren Anforderungen verwenden.

Wenn Sie wirklich zufällige Tabellen für Ihr Ergebnis benötigen, gibt es viel bessere Möglichkeiten. Es ist klar, dass sie mehr Code belegen, aber Sie verhindern einen möglichen Engpass, der exponentiell zunimmt, wenn Ihr Inhalt wächst.

Das Problem ist, dass MySQL für jede Zeile RAND () ausführen muss (was Rechenleistung erfordert), bevor sie bestellt und eine einzelne Zeile zurückgegeben werden kann.

 // der Weg, es NICHT zu tun: $ r = mysql_query ("SELECT benutzername FROM FROM benutzer ***** BY RAND () LIMIT 1"); // viel besser: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT username from user LIMIT $ rand, 1"); 

Sie wählen also eine Zufallszahl aus, die kleiner als die Anzahl der Ergebnisse ist, und verwenden sie als Offset in der LIMIT-Klausel.

20. Optimieren Sie die WHERE-Klausel

Hier einige Tipps zur Optimierung der WHERE-Klausel :

• Entfernen Sie unnötige Klammern. Zum Beispiel:

 FROM: (a <b AND b = c) AND a = 5 A: b> 5 UND b = c UND a = 5 

• COUNT (*) ist so optimiert, dass SELECT viel schneller zurückgegeben wird, sofern es sich um eine Tabelle handelt und WHERE nicht verwendet wird. Zum Beispiel:

 SELECT COUNT (*) FROM table. 

• Die Option SQL_SMALL_RESULT kann mit GROUP BY oder DISTINCT verwendet werden, um anzugeben, dass die Ergebnismenge klein ist. In diesem Fall verwendet MySQL sehr schnelle temporäre Tabellen, um die resultierende Tabelle zu speichern, anstatt die Sortierung zu verwenden.