Unlimited Plugins, WordPress themes, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Code
  2. Databases
Code

Top 20 + MySQL Best Practices

by
Difficulty:IntermediateLength:LongLanguages:

German (Deutsch) translation by Max Benjamin (you can also view the original English article)

Datenbank-Operatione n oft der größte Engpass für die meisten web-Anwendungen heute. Es ist nicht nur der DBA (Datenbankadministratoren), die Gedanken um diese performance-Probleme. Wir als Programmierer müssen unseren Teil tun, durch die Strukturierung Tabellen richtig schreiben optimierte Abfragen und besseren code. In diesem Artikel werde ich Liste einige MySQL-Optimierung Techniken für die Programmierer.

Bevor wir beginnen, bewusst sein, dass Sie finden, die eine Tonne von nützlichen MySQL-Skripte und Dienstprogramme auf Envato Markt.

MySQL scripts and utilities on Envato Market
MySQL Skripte und Dienstprogramme auf Envato Markt

1. Optimieren Sie Ihre Abfragen Für die Abfrage-Cache

Die meisten MySQL-Server query-caching aktiviert. Es ist eine der effektivsten Methoden zur Verbesserung der performance, die leise, werden von der Datenbank-engine. Wenn die gleiche Abfrage mehrfach ausgeführt wird, das Ergebnis ist der Wert aus dem cache, die ist Recht schnell.

Das Hauptproblem ist, es ist so einfach und vom Programmierer verborgen, die meisten von uns dazu neigen, es zu ignorieren. Einige Dinge, die wir tun können tatsächlich verhindern, dass der query-cache von der Erfüllung seiner Aufgabe.

 Der Grund der Abfrage-cache funktioniert nicht in der ersten Zeile ist die Nutzung von CURDATE () - Funktion. Dies gilt für alle nicht-deterministischen Funktionen wie JETZT() und RAND() etc... Da ist das Rückgabeergebnis der Funktion ändern kann, MySQL entscheidet deaktivieren das Zwischenspeichern von Abfragen für die Abfrage. Alles, was wir brauchten, um zu tun ist, fügen Sie eine zusätzliche Zeile von PHP, bevor die Abfrage, um dies zu verhindern.


 2. ERKLÄREN Sie Ihren SELECT-Abfragen

Mithilfe der EXPLAIN-Schlüsselwort kann Ihnen Einblick auf das, was MySQL macht, um die Abfrage ausführen. Dies kann helfen, Sie erkennen die Engpässe und andere Probleme mit Ihrer Abfrage oder Tabellen-Strukturen.

Die Ergebnisse einer ERKLÄREN Abfrage wird Ihnen zeigen, welche Indizes genutzt sind, wie die Tabelle gescannt und sortiert usw...

Nehmen Sie eine SELECT-Abfrage (vorzugsweise ein Komplex mit joins), und fügen Sie das Schlüsselwort EXPLAIN vorangestellt ist. Sie können einfach phpmyadmin dafür. Es zeigt Ihnen die Ergebnisse in einer schönen Tabelle. Zum Beispiel, sagen wir, ich vergaß hinzuzufügen, einen index auf eine Spalte, die ich durchführen anschließt:

Nach dem hinzufügen des Indexes in das Feld group_id:

 Statt jetzt Scannen 7883 Zeilen, es wird nur für scan 9 und 16 Zeilen aus 2 Tabellen. Eine gute Faustregel ist, multiplizieren Sie alle zahlen, die unter die "Reihen" - Spalte, und die Abfrage-performance wird etwas werden, proportional zu der resultierenden Zahl.


3. LIMIT 1 Wenn Immer eine Eindeutige Zeile

Manchmal, wenn Sie Abfragen Ihren Tabellen, die Sie bereits wissen, Sie suchen nur eine Zeile. Möglicherweise werden Sie Holen einen eindeutigen Datensatz, oder Sie könnten einfach nur die überprüfung der Existenz von einer beliebigen Anzahl von Datensätzen, um Ihre WHERE-Klausel.

 In solchen Fällen, LIMIT 1 hinzufügen, um der Abfrage kann die performance erhöhen. Diese Art der Datenbank-engine stop-scanning für die Aufzeichnungen nach, es findet sich nur 1, anstatt durch die ganze Tabelle oder einen index.


4. Index der Search-Fields

 Indizes sind nicht nur für den Primärschlüssel oder der eindeutige Schlüssel. Wenn es irgendwelche Spalten in Ihrer Tabelle, Sie werden suchen durch, sollten Sie fast immer index.

 Wie Sie sehen können, diese Regel gilt auch für eine Teilzeichenfolge suchen wie "last_name LIKE 'a%'". Bei der Suche vom Anfang des Strings, MySQL ist in der Lage zu nutzen, um den index auf die Spalte.

 Sie sollten auch verstehen, welche Art von sucht, kann nicht über den normalen Indizes. Zum Beispiel, wenn Sie auf der Suche nach einem Wort (z.B. "WHERE post_content LIKE '%apple -%'"), werden Sie nicht sehen, ein Vorteil von einem normalen index.  Sie werden besser mit mysql-fulltext-Suche oder den Aufbau Ihrer eigenen Indizierung Lösung.


5. Index und Benutzen Gleichen Spaltentypen für Joins

 Wenn Ihre Anwendung enthält viele JOIN-Abfragen, die Sie benötigen, um sicherzustellen, dass die Spalten, die Sie beitreten, indem Sie indiziert sind auf beide Tabellen. Dies betrifft, wie MySQL intern optimiert die join-operation.

Auch die Spalten, die miteinander verbunden sind, müssen vom selben Typ sein. Zum Beispiel, wenn Sie sich einer DECIMAL-Spalte, in eine INT-Spalte aus anderer Tabelle, MySQL wird nicht möglich sein, verwenden Sie mindestens einen der Indizes. Auch die Zeichen-Codierungen müssen von demselben Typ sein, für string-Spalten.


6. Nicht ORDER BY RAND()

Dies ist einer jener tricks, die cool klingen auf den ersten, und viele rookie-Programmierer fallen für diese Falle. Sie können nicht erkennen, welche Art von schrecklichen Engpass können Sie erstellen, sobald Sie beginnen, mit dieser in Ihren Abfragen.

 Wenn Sie wirklich brauchen, zufällige Zeilen aus Ihren Ergebnissen, es gibt viel bessere Möglichkeiten, es zu tun. Zugegeben, es braucht zusätzlichen code, aber Sie wird verhindern, dass ein Engpass werden, das wird exponentiell schlimmer, als Ihre Daten wächst.  Das problem ist, MySQL durchführen, RAND () - operation (das dauert Rechenleistung) für jede einzelne Zeile in der Tabelle vor dem Sortieren und geben Sie nur 1 Zeile.

 So wählen Sie eine zufällige Zahl, die kleiner ist als die Anzahl der Ergebnisse und die Verwendung der offset in Ihre LIMIT-Klausel.


7. Vermeiden Sie SELECT *

 Mehr Lesen der Daten aus den Tabellen, desto langsamer wird die Abfrage. Es erhöht die Zeit, die es braucht für die Operationen auf der Festplatte.  Auch wenn der Datenbank-server wird getrennt vom web-server, Sie haben mehr Netzwerk-Verzögerungen, da die Daten werden zwischen den Servern übertragen.

 Es ist eine gute Angewohnheit, immer angeben, welche Spalten, die Sie benötigen, wenn Sie tun Sie Ihre WÄHLEN.


 8. Haben fast Immer ein id-Feld

 In jeder Tabelle eine id-Spalte ist der Primärschlüssel, AUTO_INCREMENT und eine der Geschmacksrichtungen der INT. Auch möglichst NICHT, da der Wert kann nicht negativ sein.

Selbst wenn Sie einen Benutzer-Tabelle, die einen eindeutigen Benutzernamen-Feld, machen Sie nicht, dass Ihre primary key. VARCHAR-Felder als Primärschlüssel werden langsamer. Und Sie haben eine bessere Struktur in Ihrem code bezieht sich auf alle Benutzer mit id-s intern.

 Es gibt auch hinter den kulissen-Operationen durchgeführt, indem die MySQL-engine, verwendet, die die primary key-Feld intern. Die werden umso wichtiger, je komplizierter das setup der Datenbank ist. (Cluster, partitionieren usw...).

 Eine mögliche Ausnahme von der Regel sind der "Zuordnungstabellen", für die viele-zu-viele Arten von Assoziationen zwischen 2 Tabellen.  Zum Beispiel ist ein "posts_tags" Tabelle mit 2 Spalten: post_id, tag_id, das für die Beziehungen zwischen zwei Tabellen mit dem Namen "post" und "tags". Diese Tabellen haben einen Primärschlüssel, enthält die beiden id-Felder.


 9. Verwenden Sie über ENUM VARCHAR

 ENUM-Spalten sehr schnell und kompakt. Intern werden Sie gespeichert, wie TINYINT, dennoch können Sie enthalten und display string-Werte. Dies macht Sie zu einem idealen Kandidaten für bestimmte Felder.

 Wenn Sie ein Feld, das enthält nur ein paar verschiedene Arten von Werten, verwenden ENUM statt VARCHAR. Zum Beispiel könnte es eine Spalte namens "status", und enthalten nur Werte wie "aktiv", "inaktiv", "aus", "abgelaufen" etc...

 Es gibt sogar einen Weg, um einen "Vorschlag" von MySQL selbst, wie strukturieren Sie Ihre Tabelle.  Wenn Sie eine VARCHAR-Feld, es kann eigentlich schlage vor, Sie zu ändern, dass die Spalte vom Typ ENUM statt. Dies geschieht mit Hilfe der PROZEDUR ANALYSE () - Aufruf. Das bringt uns zu:


10. Holen Sie sich Anregungen mit der PROZEDUR ANALYSE()

 PROCEDURE ANALYSE() läßt MySQL analysieren Sie die Spalten, die Strukturen und die tatsächlichen Daten in der Tabelle zu kommen mit bestimmten Vorschläge für Sie. Es ist nur sinnvoll, wenn es tatsächliche Daten in den Tabellen, weil das spielt eine große Rolle in der Entscheidungsfindung.

Zum Beispiel, wenn Sie ein INT-Feld für den Primärschlüssel, aber nicht zu viele Zeilen sind, könnte es schlage vor, Sie verwenden ein MEDIUMINT statt. Oder wenn Sie mit einem VARCHAR-Feld, erhalten Sie möglicherweise einen Vorschlag für die Konvertierung in einen ENUM, wenn es nur wenige eindeutige Werte.

 Sie können auch ausführen, indem man das "Schlagen Tabelle "Struktur" - link in phpmyadmin, in einem Ihrer Tabelle Aussicht.

 Halten Sie im Verstand dies sind nur Vorschläge. Und wenn Ihr zu Tisch geht, größer zu wachsen, Sie können nicht einmal das Recht, Vorschläge zu Folgen. Die Entscheidung ist letztendlich Ihre.


11. Verwenden Sie NICHT NULL, Wenn Sie Können

 Es sei denn, Sie haben einen sehr spezifischen Grund für die Verwendung eines NULL-Wert, sollten Sie immer Ihre Spalten als not NULL.

 Zunächst Fragen Sie sich, ob es einen Unterschied zwischen einem leeren string-Wert vs. a-NULL-Wert (für INT-Felder: 0 vs. NULL).  Wenn es keinen Grund gibt, beides zu haben, brauchen Sie nicht, ein NULL-Feld. (Wussten Sie, dass Oracle der Ansicht NULL und der leere string als das gleiche?)

 NULL-Spalten benötigen zusätzlichen Speicherplatz und können die Komplexität der Vergleich Aussagen. Nur vermeiden Sie, wenn Sie können. Ich verstehe allerdings, einige Leute haben sehr spezifische Gründe, um NULL-Werte, was nicht immer eine schlechte Sache.

Aus der MySQL-docs:

 "NULL-Spalten erfordern zusätzlichen Speicherplatz in der Zeile zu erfassen, ob Ihre Werte sind NULL. Für MyISAM-Tabellen, jede NULL-Spalte nimmt ein bisschen extra, aufgerundet auf das nächste byte."


12. Prepared Statements

 Es gibt mehrere Vorteile für die Verwendung von vorbereiteten Anweisungen, sowohl für performance-und Sicherheitsgründen.

 Vorbereitete Anweisungen-filter die Variablen, die Sie binden, um Sie standardmäßig, das ist toll, für den Schutz Ihre Anwendung gegen SQL-injection-Angriffe.  Selbstverständlich können Sie die filter-Variablen auch manuell, aber diese Methoden werden mehr anfällig für menschliche Fehler und Vergesslichkeit, die durch den Programmierer. Dies ist weniger ein Problem, wenn einige Art von framework oder ORM.

 Da unser Fokus liegt auf performance, ich sollte auch erwähnen, die Vorteile in diesem Bereich. Diese Vorteile sind mehr signifikant ist, wenn die gleiche query mehrmals verwenden in Ihrer Anwendung.  Sie können unterschiedliche Werte zuzuweisen, um die gleiche vorbereitete Anweisung, die noch MySQL haben nur zu analysieren, es einmal.

Auch die neuesten Versionen von MySQL überträgt vorbereitete Anweisungen in einer nativen binary form, die sind effizienter und können auch helfen, verringern Sie Netzwerk-Verzögerungen.

 Es gab eine Zeit, wenn viele Programmierer verwendet, um zu vermeiden, vorbereitete Anweisungen, die auf Zweck, für eine einzige wichtige Grund. Sie wurden nicht zwischengespeichert werden durch die MySQL-Abfrage-cache. Aber seit irgendwann um version 5.1 query-caching wird auch unterstützt.

 Die Verwendung von vorbereiteten Anweisungen in PHP-Sie überprüfen die mysqli-Erweiterung, oder verwenden Sie eine Datenbank-Abstraktionsschicht wie PDO.


13. Unbuffered Queries

 Normalerweise, wenn Sie führen Sie eine Abfrage von einem Skript aus, wird es warten, bis die Ausführung der Abfrage zu beenden, bevor es weitergehen kann. Sie können das ändern, indem Sie mit der ungepufferten Anfragen.

Es gibt eine große Erklärung in der PHP-Dokumentation für die mysql_unbuffered_query () - Funktion:

 "mysql_unbuffered_query() sendet die SQL-query-Abfrage an MySQL, ohne automatisch abrufen und die Pufferung der Ergebniszeilen als mysql_query() nicht. Dies spart eine erhebliche Menge an Speicher bei SQL-Abfragen erstellen, die große Ergebnis-sets, und Sie können anfangen zu arbeiten auf das Ergebnis gesetzt unmittelbar nach der ersten Zeile abgerufen wurde, wie Sie nicht haben, zu warten, bis die komplette SQL-Abfrage durchgeführt wurde."

 Jedoch, es kommt mit bestimmten Einschränkungen. Sie haben entweder Lesen Sie alle Zeilen oder rufen Sie mysql_free_result() auf, bevor Sie können ausführen einer anderen Abfrage. Auch Sie sind nicht erlaubt, mysql_num_rows() oder mysql_data_seek() auf das Ergebnis gesetzt.


 14. Speichern von IP-Adressen als UNSIGNED INT

 Viele Programmierer erstellen ein VARCHAR(15) Feld, ohne zu realisieren, können Sie tatsächlich IP-Adressen speichern als integer-Werte. Mit einem INT gehen Sie nach unten zu nur 4 Byte an Speicherplatz und haben eine Feste Größe-Feld statt.

 Sie müssen sicherstellen, dass Ihre Säule ist ein UNSIGNED INT, da IP-Adressen verwenden, die ganze Bandbreite einer 32-bit unsigned integer.

 In Ihren Abfragen, die Sie verwenden können, die INET_ATON() zu konvertieren und die IP in eine ganze Zahl, und INET_NTOA() für das vice versa. Gibt es auch ähnliche Funktionen in PHP aufgerufen ip2long() und long2ip().


15. Fester Länge (Statische) Tabellen sind Schneller

Wenn jede einzelne Spalte in einer Tabelle ist "fester Länge", die Tabelle ist auch als "statisch" oder "Feste Länge". Beispiele von Spaltentypen, die NICHT behoben werden-Länge: VARCHAR, TEXT, BLOB.  Wenn Sie auch nur 1 von diesen Arten von Spalten, die Tabelle hört auf, das eine Feste Länge und werden anders behandelt, indem Sie die MySQL-engine.

Feste-Länge-Tabellen kann die performance verbessern, weil es schneller für die MySQL-engine zu suchen, durch die Datensätze.  Wenn es Lesen will eine bestimmte Zeile in einer Tabelle, es kann schnell berechnen die position. Wenn Sie die Zeile Größe ist nicht festgelegt, jedes mal, es muss eine suchen, hört er die primary key-index.

 Sie sind auch leichter zu cache und einfacher zu rekonstruieren, nach einem Absturz. Aber Sie können auch mehr Platz. Zum Beispiel, wenn Sie konvertieren von Datentyp VARCHAR(20) Feld in eine CHAR(20) Feld, es werden immer 20 Byte an Speicherplatz, unabhängig davon, was wird es in.

 Verwenden Sie die "Vertikale Partitionierung" Techniken, Sie können trennen die Spalten mit variabler Länge in eine separate Tabelle. Das bringt uns zu:


16. Vertikale Partitionierung

 Vertikale Partitionierung wird der Vorgang des aufspaltens Ihrer Struktur der Tabelle, die in einer vertikalen Art und Weise aus Gründen der Optimierung.

 Beispiel 1: haben Sie vielleicht eine Benutzer-Tabelle, die enthält Adressen, die nicht oft gelesen.  Sie können wählen, zu teilen Sie Ihre Tabelle und die Adresse speichern Infos auf einem separaten Tisch. Auf diese Weise werden Ihre wichtigsten Benutzer-Tabelle wird in der Größe schrumpfen. Wie Sie wissen, kleinere Tabellen auszuführen schneller.

 Beispiel 2: Sie haben eine "last_login" - Feld in der Tabelle. Es aktualisiert jedes mal, wenn ein Benutzer anmeldet auf der website.  Aber jedes update auf einer Tabelle bewirkt, dass der query-cache für die Tabelle geleert werden. Können Sie dieses Feld in eine andere Tabelle zu halten, updates für Ihre Benutzer-Tabelle auf ein minimum.

 Aber Sie müssen auch sicherstellen, dass Sie nicht ständig benötigen, fügen Sie diese 2 Tabellen nach der Partitionierung oder Sie könnte in der Tat leiden die Leistung ablehnen.


17. Teilen Sie die Große DELETE-oder INSERT-Abfragen

 Wenn Sie brauchen, um eine große DELETE-oder INSERT-Abfrage, die auf einem live-website, müssen Sie vorsichtig sein, nicht zu stören, die web-traffic. Wenn eine große Anfrage wie folgt, durchgeführt wird, kann es sperren Sie Ihre Tische und bringen Sie Ihre web-Anwendung zum Stillstand.

Apache läuft viele parallelen Prozesse/threads. Daher arbeitet am effizientesten, wenn die Drehbücher fertig stellen, ausführen, so bald wie möglich, so dass die Server nicht in Erfahrung zu viele offene verbindungen und Prozesse auf einmal, dass die Ressourcen, insbesondere Speicher.

 Wenn Sie am Ende sperren Sie Ihre Tabellen für einen längeren Zeitraum (etwa 30 Sekunden oder mehr), die auf eine hohe Verkehr web site, werden Sie führen eine Prozess-und Abfrage-pile-up, die eine lange Zeit dauern, um zu löschen oder sogar zum Absturz Ihres web server.

 Wenn Sie irgendeine Art von Wartung Skript, Bedürfnisse zu löschen, die große Anzahl von Zeilen verwenden Sie einfach die LIMIT-Klausel zu tun, die es in kleineren Chargen zu vermeiden Staus.


18. Kleinere Spalten Sind Schneller

 Mit Datenbank-engines, die Festplatte ist vielleicht der wichtigste Engpass. Sie halten die Dinge kleiner und kompakter ist meist hilfreich, in Bezug auf die Leistung, die Verringerung der Menge von disk-transfer.

Die MySQL-docs haben eine Liste von Storage-Anforderungen für alle Datentypen.

 Wenn eine Tabelle ist zu erwarten, dass sehr wenige Zeilen, aber es gibt keinen Grund, um die primary key-INT, statt MEDIUMINT, SMALLINT oder sogar in einigen Fällen ein Wert vom Datentyp TINYINT. Wenn Sie nicht brauchen Sie die Zeit-Komponente, verwenden Sie das DATUM anstelle von DATETIME.

 So stellen Sie sicher, lassen Sie angemessenen Raum zu wachsen, oder Sie könnten am Ende wie bei Slashdot.


 19. Wählen Sie die Richtige Speicher-Engine

 Die beiden wichtigsten Speicher-engines in MySQL sind MyISAM und InnoDB. Haben jeweils Ihre eigenen vor-und Nachteile.

 MyISAM ist gut für das Lesen-schwere Anwendungen, aber es funktioniert nicht sehr gut skalieren, wenn es eine Menge von Schreibzugriffen.   Sogar, wenn Sie die Aktualisierung eines Feldes einer Zeile, wird die gesamte Tabelle gesperrt, und kein anderer Prozeß kann sogar noch Lesen, bis die Abfrage abgeschlossen ist. MyISAM ist sehr schnell in der Berechnung SELECT COUNT(*) Arten von Abfragen.

 InnoDB ist eher ein komplizierter storage engine und können langsamer sein als MyISAM für die meisten kleinen Anwendungen. Aber es unterstützt Zeilen-basierte sperren, die besser skaliert. Es unterstützt auch einige erweiterte Funktionen wie Transaktionen.


 20. Die Verwendung einer Objekt-Relationalen Mapper

 Durch die Verwendung eines ORM (Object Relational Mapper), Sie können gewinnen bestimmte performance-Vorteile. Alles, was ein ORM tun können, können kodiert werden, auch manuell. Aber dies kann bedeuten, dass zu viel zusätzliche Arbeit und erfordern ein hohes Maß an know-how.

 ORM ' s sind Super für "Lazy Loading". Es bedeutet, dass Sie abholen können die Werte nur, wenn Sie benötigt werden. Aber Sie müssen vorsichtig sein mit Ihnen, oder können Sie am Ende schaffen, um viele mini-Abfragen, können die Leistung reduzieren.

 ORM kann auch Ihre batch-Abfragen in Transaktionen, die arbeiten viel schneller als das senden einzelner Abfragen an die Datenbank.

Derzeit mein Lieblings-ORM für PHP ist Doctrine. Ich schrieb einen Artikel auf, wie zu installieren Lehre mit CodeIgniter.


21. Seien Sie Vorsichtig mit Persistenten Verbindungen

 Persistente Verbindungen gedacht sind, reduzieren den Aufwand der Neuerstellung verbindungen zu MySQL. Wenn eine persistente Verbindung erstellt wird, wird es offen bleiben, auch nachdem die Ausführung des Skripts beendet. Seit Apache verwendet es die Kind-Prozesse, das nächste mal, der Prozess läuft für ein neues Skript, es wird die Wiederverwendung der gleichen MySQL-Verbindung.

 Es klingt in der Theorie großartig. Aber aus meiner persönlichen Erfahrung (und viele andere), diese Funktionen stellt sich heraus, dass nicht der Mühe Wert. Sie können schwerwiegende Probleme mit der Verbindung Grenzen, Gedächtnis-Probleme und so weiter.

Apache läuft extrem parallel, und schafft viele Kind-Prozesse. Dies ist der Hauptgrund dafür, dass persistente verbindungen nicht sehr gut funktionieren in dieser Umgebung. Bevor Sie erwägen, die mysql_pconnect () - Funktion, konsultieren Sie Ihren system-Administrator.

  •  Folgen Sie uns auf Twitter oder abonnieren Sie den Nettuts+ RSS-Feed für die besten web-Entwicklung-tutorials im web.


Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.