PHP-MySQL-Optimierung

Der in diesem Artikel vorgestellte Inhalt befasst sich mit der PHP-MySQL-Optimierung, die jetzt mit allen geteilt werden kann. Freunde in Not können sich auf

beziehen einige Artikel MySQL-Tipps

• 1. Schlüsselwörter in SQL-Anweisungen werden am besten in Großbuchstaben geschrieben , erstens ist es einfach Zweitens: Wenn die SQL-Anweisung ausgeführt wird, konvertiert MySQL sie in Großbuchstaben. Das manuelle Schreiben von Großbuchstaben kann die Abfrageeffizienz erhöhen (obwohl dies sehr gering ist).

2. Wenn wir Datenzeilen in der Datenbank hinzufügen oder löschen, ist die Daten-ID zu groß. Verwenden Sie

• ALTER TABLE tablename AUTO_INCREMENT=N , bewirkt, dass die automatisch inkrementierende ID mit dem Zählen ab N beginnt.

3. Fügen Sie das Attribut

• ZEROFILL zum Typ int hinzu, um automatisch 0 zu den Daten hinzuzufügen

4. Beim Importieren einer großen Datenmenge ist es am besten, zuerst den Index zu löschen, die Daten einzufügen und dann den Index hinzuzufügen. Andernfalls verbringt MySQL viel Zeit damit, den Index zu aktualisieren.
5. Beim Erstellen einer Datenbank und beim Schreiben von SQL-Anweisungen können wir in der IDE eine Datei mit dem Suffix .sql erstellen, was das Schreiben erleichtert . Noch wichtiger: Wenn Ihre Datenbank verloren geht, können Sie diese Datei immer noch finden, indem Sie

• /path/mysql -uusername -ppassword Datenbankname < im aktuellen Verzeichnis verwenden SQL-Anweisung der gesamten Datei (beachten Sie, dass auf -u und -p der Benutzername und das Passwort ohne Leerzeichen folgen).

---

Optimierung des Datenbankdesigns

1 , Das Datenbankdesign entspricht dem dritten Paradigma und es kann ein gewisses Maß an Datenredundanz zur Vereinfachung der Abfrage vorhanden sein. 2. Wählen Sie den Datentyp int > enum, varchar > aus unsign int-Typ mit der Funktion ip2long() zum Speichern. 3. Versuchen Sie für den Typ char(n), den n-Wert so klein wie möglich zu halten, wenn die Daten vollständig sind. 4. Verwenden Sie beim Erstellen einer Tabelle den Partitionsbefehl, um eine einzelne

Tabelle

zu partitionieren, um die Abfrageeffizienz erheblich zu verbessern. MySQL unterstützt die Partitionstypen RANGE, LIST, HASH und KEY. Die am häufigsten verwendeten Partitionierungsmethoden sind: CREATE TABLE Tabellenname{　}ENGINE innodb/myisam CHARSET utf8 //Datenbank-Engine und Codierung auswählenPARTITION BY RANGE/LIST(column) ,//Nach Bereich und vordefinierter Liste Partition　PARTITION-Teilname VALUES WENIGER ALS /IN(n),//Benennen Sie die Partition und begrenzen Sie den Bereich der Partition im Detail
5. Achten Sie bei der Auswahl einer Datenbank-Engine darauf

Der Unterschied zwischen innodb und myisam

. Speicherstruktur: MyISAM wird in drei Dateien auf der Festplatte gespeichert. Alle Tabellen in InnoDB werden in derselben Datendatei gespeichert, im Allgemeinen 2 GB Transaktionsunterstützung: MyISAM bietet keine Transaktionsunterstützung. InnoDB bietet Transaktionsunterstützung. Unterschiede bei Tabellensperren: MyISAM unterstützt nur Sperren auf Tabellenebene. InnoDB unterstützt Transaktionen und Sperren auf Zeilenebene. Volltextindex: MyISAM unterstützt den Volltextindex vom Typ FULLTEXT (gilt nicht für Chinesisch, daher muss die Sphinx-Volltextindex-Engine verwendet werden). InnoDB unterstützt es nicht.
Die spezifische Anzahl der Zeilen in der Tabelle: MyISAM speichert die Gesamtzahl der Zeilen in der Tabelle und die Abfrage von count(*) ist sehr schnell. InnoDB speichert nicht die Gesamtzahl der Zeilen in der Tabelle und muss neu berechnet werden.
Fremdschlüssel: MyISAM unterstützt es nicht. InnoDB unterstützt

---

Indexoptimierung

1. InnoDB ist ein Clustered-Index, und wann muss ein Primärschlüssel vorhanden sein Speichern des Index. Wenn nicht angegeben, generiert die Engine automatisch einen versteckten Primärschlüssel und einen Primärindex
. Die physische Adresse des Primärschlüssels wird im Index gespeichert Daten werden im Primärschlüssel gespeichert. Bei jeder Verwendung des Index müssen Sie zuerst den Hauptindex und dann die Daten unter dem Hauptindex finden. Der Vorteil besteht darin, dass die Suche durch den Primärschlüssel sehr schnell ist. Der Nachteil besteht darin, dass der Sekundärindex langsamer ist, da Sie den Primärindex zuerst über den Sekundärindex finden müssen (der Sekundärindex ist der Speicherort von). den Primärindex.) und dann die Primärindexdaten durchsuchen. Und wenn der Primärschlüssel unregelmäßig ist, müssen beim Einfügen neuer Werte mehr Datenblöcke verschoben werden, was sich auf die Effizienz auswirkt. Versuchen Sie daher, als Primärschlüssel einen int-Typ zu verwenden, der regelmäßig zunimmt. Da die Daten unmittelbar nach dem Primärschlüssel platziert werden, überspringt InnoDB bei Spalten (Text/Blob) mit besonders großen Datenmengen während der Abfrage viele Datenblöcke, was ebenfalls zu einer Verlangsamung führt.

2. Jeder Index des Myisam-Index zeigt auf die gleiche Adresse jeder Zeile auf der Festplatte. Es handelt sich bei allen um leichte Zeigerdaten. Der Nachteil besteht darin, dass nicht jeder Index über den Primärschlüssel erstellt wird und die Abfrage nicht so schnell ist wie die Suche nach dem Primärschlüssel im Clustered-Index. Da jedoch die Adresse gespeichert wird, verschiebt und ändert sich der Vergleichsaspekt beim Einfügen eines neuen Werts.
3. Wenn Sie eine Abfrage mit mehreren Bedingungen durchführen und Indizes für mehrere Bedingungen separat erstellen, wählt MySQL beim Ausführen einer SQL-Abfrage nur den nächstgelegenen Index aus, der verwendet werden soll Der Index muss eingerichtet werden, auch wenn Datenredundanz verursacht wird.
Die BTREE-Erstellungsmethode des gemeinsamen Index: Indizieren Sie die erste Bedingung, indizieren Sie die zweite Bedingung im BTREE-Bereich des ersten Index usw. Wenn Sie also den Index verwenden, Verwenden Sie die zweite Bedingung ohne Verwendung der ersten Bedingung verwendet nicht den gemeinsamen Index . Bei der Verwendung von Indizes müssen die Bedingungen in der richtigen Reihenfolge sein und nacheinander verwendet werden.
4. Die Indexlänge hat auch einen großen Einfluss auf die Abfrage. Wir sollten versuchen, eine kurze Indexlänge zu erstellen. Wir können die Abfragespalte
SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) verwenden. FROM Tabellenname zum Testen der Abfrage. Beim Erstellen eines Indexes für die Spalte Spalte wählen wir n Längen aus, die nahe an der Sättigung liegen, um einen Index zu erstellen.
ALTER TABLE Tabellenname ADD INDEX (column(n)); um den ersten Teil einer bestimmten Spalte mit n Zeichen zu indizieren, um einen Index zu erstellen. Wenn die ersten n Zeichen gleich sind, können wir die Zeichenfolge sogar umkehren, speichern und dann einen Index erstellen.
5. So bewahren Sie die durch häufige Änderungen verursachte Indexfragmentierung auf: ALTER TABLE tablename ENGINE oldengine; das heißt, Sie können die Tabellenspeicher-Engine erneut anwenden, um die automatische Wartung zu aktivieren.

---

Optimierung der Datenabfrage
Versuchen Sie, bei Datenbankoperationen so wenige Abfragen wie möglich zu vermeiden Versuchen Sie, dies nicht auf Datenbankebene zu tun, wenn Abfragen vorliegen. Anstatt Datenoperationen für das PHP-Skript auszuführen, kehren Sie zum PHP-Skript zurück, um die Daten zu bearbeiten und so den Druck auf die Datenbank zu verringern.
Sobald ein Datenbankleistungsproblem entdeckt wird, muss es rechtzeitig behoben werden. Verwenden Sie im Allgemeinen Langsame AbfrageWenn die Protokollabfrage „langsam“ ist, verwenden Sie EXPLAINAnalysieren Sie die Abfrage- und Indexnutzung. Verwenden Sie PROFILE, um den spezifischen Ressourcenverbrauch während der Anweisungsausführung zu analysieren.
Langsames Abfrageprotokoll:
1. Fügen Sie
slow_query_log_file=/ unter [mysqld] im my.ini- oder my.cnf-Pfad hinzu //Legen Sie den Protokollspeicherpfad fest
long_query_time=n //Legen Sie fest, ob die Ausführungszeit der Anweisung n Sekunden erreicht, wird sie aufgezeichnet
2. Setzen Sie dann SET slow_query_log='ON' in MySQL, um langsame Abfragen zu ermöglichen.
3. Nach dem Aufzeichnen des Protokolls verwenden wir den Dateinamen mysqldumpslow im Verzeichnis /bin/, um das Protokoll anzuzeigen. Die allgemeinen Parameter lauten wie folgt:
　-g-Muster Verwenden Sie reguläre Ausdrücke
　-t nGibt das erste n zurück Datenstücke
　-s c/t/l/r Sortieren nach Anzahl der Datensätze/Zeit/Abfragezeit/Anzahl der zurückgegebenen Datensätze

EXPLAIN-Anweisung
Verwendungsmethode: Fügen Sie EXPLAIN
EXPLAIN SELECT * FROM user;
vor der auszuführenden Abfrageanweisung hinzu, um das unten gezeigte Ergebnis zu erhalten:

Die Im Folgenden finden Sie eine Erklärung zu jedem Element:
id ist die ID der Abfrageanweisung. Bei mehreren Abfragen ist die Reihenfolge der Abfrageausführung ersichtlich Art der auszuführenden Abfrageanweisung. Sie entspricht mehreren Abfragen, einschließlich einfach/primär/union usw.
Tabelle Die von der Abfrageanweisung abgefragte Datentabelle

Typ Der Typ der erhaltenen Daten ist von hoch nach niedrig null>const>eq_ref>ref> ;range>index> ;allpossible-keys: Möglicherweise verwendete Indizes

key Verwendete Indizeskey_len Indexlänge
ref Welche Spalte mit dem verwendet werden soll Index Aus Tabelle auswählen.

Zeilen Ermitteln Sie die ungefähre Anzahl der Zeilen, die gescannt werden müssen, um die Daten zu finden. Dies zeigt die Qualität des Index
extra Häufig wird Filesort verwendet, um die Dateien nach dem Abfragen der Daten zu sortieren. Es ist langsam und muss den Index optimieren. Verwenden Sie dabei die Verwendung von where, um die gesamte Datenzeile zu lesen und dann zu beurteilen und zu filtern, ob sie den Anforderungen entspricht Wobei Bedingung:
Verwendung der Index-Indexabdeckung, d. h. in Die Zieldaten sind bereits in der Traktion gespeichert und der Index wird direkt gelesen, was sehr schnell ist.

PROFIL
Verwenden Sie SELECT @@frofiling, um den Öffnungsstatus von PROFIL zu überprüfen.
Wenn es nicht aktiviert ist, verwenden Sie SET profiling=1, um es zu aktivieren.
Nach dem Einschalten zeichnet MySQL automatisch die Profilinformationen auf, wenn Sie die Abfrageanweisung ausführen.
Wenden Sie Anzeigeprofile an, um alle SQL-Informationen anzuzeigen. Das Ergebnis sind dreispaltige Ergebnisse der Query_ID-Dauerabfrage, die Abfrage-ID, Zeit und verwendete SQL-Anweisung enthalten.
Wir können
SHOW PFROFILE [type[,type]][FOR QUREYQuery_ID][Limit rwo_count [OFFSET-Offset]]
Zu den gängigen Typen gehören ALL (alle) BLOCK IO (zeigt den IO-bezogenen Overhead an), CPU (CPU-Overhead), MEMORY (Speicher-Overhead) usw.

---

Optimierung von großem Speicher
Datenbank-Master-Slave-Replikation und Lese-/Schreib-Trennung
1 Der Master zeichnet die Änderungen in der Binärdatei auf log, und der Slave zeichnet die Änderungen des Masters auf. Die Binärdatei wird in sein Relay-Log kopiert und die Daten werden an seine eigenen Daten zurückgegeben, um den Zweck der Replikation der Hauptserverdaten zu erreichen.

Master-Slave-Replikation kann für Folgendes verwendet werden: Datenbanklastausgleich, Datenbanksicherung, Lese-/Schreibtrennung und andere Funktionen.
2. Konfigurieren Sie den Hauptserver-Master
Ändern Sie my.ini/my.conf
[mysqld]
log-bin=mysql-bin //Aktivieren Sie Binärprotokolle
server-id=102 //Eindeutige Server-ID
3. Slave-Server-Slave konfigurieren
log-bin=mysql-bin //Binärprotokoll aktivieren
server-id=226 //Eindeutige Server-ID
4 server Autorisieren Sie den Slave-Server
GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identifiziert durch 'root'
5. Verwenden Sie auf dem Slave-Server
ändern Sie den Master in
master_host=" masterip" ,
　master_user="masteruser",
　master_password="masterpasswd";
6. Verwenden Sie dann den Befehl „Start Slave“, um die Master-Slave-Replikation zu starten.
Vergessen Sie nicht, den Server nach jeder Konfigurationsänderung neu zu starten. Anschließend können Sie den Master-/Slave-Status auf dem Master- und Slave-Server anzeigen.
Um die Trennung von Lesen und Schreiben in der Datenbank zu erreichen, ist MySQL-Middleware wie mysql_proxy, atlas usw. erforderlich. Durch die Konfiguration dieser Middlewares zur Trennung von Lesen und Schreiben zwischen Master- und Slave-Server übernimmt der Slave-Server die Verantwortung für das Lesen und reduziert so die Belastung des Master-Servers.

Datenbank-Sharding
Wenn die Datenmenge in der Datentabelle in der Datenbank sehr groß ist, stehen sowohl die Indizierung als auch das Caching unter großem Druck . Wenn die Datenbank groß ist, teilen Sie sie auf, sodass sie auf mehreren Datenbankservern oder mehreren Tabellen gespeichert wird, um den Abfragedruck zu verringern.
Die Methoden umfassen vertikale Segmentierung, horizontale Segmentierung und kombinierte Segmentierung.
Vertikale Segmentierung : Wenn viele Datentabellen vorhanden sind, teilen Sie die Tabellen auf, die in der Datenbank eng miteinander verbunden sind (z. B. dasselbe Modul, häufig verbunden und abgefragt) und Legen Sie sie an verschiedenen Orten auf dem Master-Slave-Server ab.
Horizontale Segmentierung: Wenn nicht viele Tabellen vorhanden sind und die Datenmenge in der Tabelle sehr groß ist, können Sie Hashing und andere Methoden verwenden, um die Abfrage zu beschleunigen Algorithmen zum Aufteilen einer Datentabelle in mehrere. Teilen Sie sie in mehrere auf und legen Sie sie auf verschiedenen Servern ab, um Abfragen zu beschleunigen. Der Unterschied zwischen horizontalem Sharding und Datentabellenpartitionierung liegt im Unterschied bei den Speichermedien.
Gelenksegmentierung : In den meisten Fällen sind die Datentabelle und die Datenmenge in der Tabelle sehr groß, sodass eine gemeinsame Segmentierung erforderlich ist, dh vertikal und horizontal Bei der gleichzeitigen Segmentierung wird die Datenbank zur Speicherung in eine verteilte Matrix unterteilt.
Jede dieser Datenbankoptimierungsmethoden kann zum Schreiben eines Artikels verwendet werden. Man kann sagen, dass sie tiefgründig und tiefgründig ist. Wenn Sie diese Methoden verstehen und sich daran erinnern, können Sie bei Bedarf eine gezielte Auswahl und Optimierung durchführen, um eine hohe Datenbankeffizienz zu erreichen. .

Verwandte Empfehlungen:

Nutzung der PHP-Optimierungssitzung

PHP-Lernroute und 10 PHP-Optimierungstipps

Das obige ist der detaillierte Inhalt vonPHP-MySQL-Optimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!