Optimierungszusammenfassung der MySQL-Datenbank

Die Verwendung der MySQL-Datenbank in der PHP-Entwicklung ist für jeden PHP-Programmierer zur Gewohnheit geworden. Wenn wir möchten, dass PHP MySQL schneller und komfortabler betreibt, müssen wir die MySQL-Datenbank optimieren. Daher wird auch häufig nach MySQL-Optimierung gefragt Lasst uns gemeinsam einen Blick darauf werfen!

1. Optimieren Sie Ihren MySQL-Abfrage-Cache

Beim Abfragen auf dem MySQL-Server können Sie den Hochgeschwindigkeits-Abfrage-Cache aktivieren. Eine der effektivsten Möglichkeiten, die Leistung zu verbessern, ist es, die Datenbank-Engine im Hintergrund arbeiten zu lassen. Wenn dieselbe Abfrage mehrmals ausgeführt wird, geht es recht schnell, wenn die Ergebnisse aus dem Cache abgerufen werden.
Aber das Hauptproblem besteht darin, dass es so leicht zu verbergen ist, dass die meisten von uns Programmierern es ignorieren. Bei einigen Verarbeitungsaufgaben können wir tatsächlich verhindern, dass der Abfragecache funktioniert.

// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
 $today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
 // query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. Verwenden Sie EXPLAIN, um Ihre SELECT-Abfrage klarer zu gestalten

Die Verwendung des Schlüsselworts EXPLAIN ist ein weiterer MySQL-Optimierungstipp, mit dem Sie nachvollziehen können, welche Art von Abfragevorgängen MySQL ausführt. Dies kann Ihnen dabei helfen, Engpässe zu finden und zu zeigen, wo die Abfrage- oder Tabellenstruktur fehlerhaft ist.
Die Ergebnisse der EXPLAIN-Abfrage können Ihnen Aufschluss darüber geben, auf welche Indizes verwiesen wird, wie die Tabelle gescannt und sortiert wird usw.
Implementieren Sie eine SELECT-Abfrage (vorzugsweise eine komplexere mit Verknüpfungen) und fügen Sie darin Ihre Schlüsselworterklärung hinzu. Hier können wir phpMyAdmin verwenden, das Ihnen die Tabellenergebnisse mitteilt. Wenn ich beispielsweise beim Durchführen von Verknüpfungen vergesse, eine Spalte zu einem Index hinzuzufügen, kann mir EXPLAIN dabei helfen, das Problem zu finden.

Nach dem Hinzufügen des Index zum Feld „group_id“

3. Verwenden Sie LIMIT 1, um die eindeutige Zeile

Wenn Sie eine Tabelle abfragen möchten, wissen Sie manchmal, dass Sie nur eine Zeile betrachten müssen. Möglicherweise suchen Sie nach einem sehr eindeutigen Datensatz oder überprüfen einfach die Anzahl der vorhandenen Datensätze, die Ihre WHERE-Klausel erfüllen.
In diesem Fall wird Ihre Abfrage durch das Hinzufügen eines LIMIT 1 effizienter. Auf diese Weise stoppt die Datenbank-Engine den Scanvorgang, nachdem nur 1 gefunden wurde, anstatt die gesamte Tabelle oder den gesamten Index zu scannen.

 // do I have any users from Alabama?   
 // what NOT to do:   
 $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }     
 // much better:   
 $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }

4. Suchfelder im Index

Ein Index ist nicht nur ein Primärschlüssel oder ein eindeutiger Schlüssel. Wenn Sie eine beliebige Spalte in der Tabelle durchsuchen möchten, sollten Sie immer auf den Index verweisen.

Stellen Sie sicher, dass die verbundenen Indizes vom gleichen Typ sind

Wenn die Anwendung Bei mehreren Join-Abfragen müssen Sie sicherstellen, dass die von Ihnen verknüpften Spalten in beiden Tabellen indiziert sind. Dies wirkt sich darauf aus, wie MySQL Inner-Join-Vorgänge optimiert. Außerdem müssen die hinzugefügten Spalten vom gleichen Typ sein. Wenn Sie beispielsweise eine DECIMAL-Spalte verknüpfen und gleichzeitig eine Int-Spalte in einer anderen Tabelle verknüpfen, kann MySQL mindestens einen der Indikatoren nicht verwenden. Auch die Zeichenkodierung muss mit dem String-Typ übereinstimmen.

 // 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");   

 // both state columns should be indexed   
 // and they both should be the same type and character encoding   
 // or MySQL might do full table scans

6. Verwenden Sie nicht den Befehl BY RAND()

这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND（）命令时就开始创建了。
如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个独立的行执行BY RAND()命令（这会消耗处理器的处理能力），然后给你仅仅返回一行。

 // what NOT to do:   
 $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");   
 // much better:   
 $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");

7. 尽量避免SELECT *命令

从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列，这是一个非常良好的习惯。

 // not preferred   
 $r = mysql_query("SELECT * FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";   
 // better:   
 $r = mysql_query("SELECT username FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";    
 // the differences are more significant with bigger result sets

8. 从PROCEDURE ANALYSE()中获得建议

PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了，能为你的重大决策服务。

9. 准备好的语句

准备好的语句，可以从性能优化和安全两方面对大家有所帮助。
准备好的语句在过滤已经绑定的变量默认情况下，能给应用程序以有效的保护，防止SQL注入攻击。当然你也可以手动过滤，不过由于大多数程序员健忘的性格，很难达到效果。

 // create a prepared statement   
 if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {    
     // bind parameters   
     $stmt->bind_param("s", $state);    
     // execute   
     $stmt->execute();    
     // bind result variables   
     $stmt->bind_result($username);     
     // fetch value   
     $stmt->fetch();    
     printf("%s is from %s\n", $username, $state);     
     $stmt->close();   
 }

10. 将IP地址存储为无符号整型

许多程序员在创建一个VARCHAR（15）时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时，你只占用4个字节的空间，这是一个固定大小的领域。
你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

11.永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT标志。
就算是你users表有一个主键叫“email”的字段，你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。

12. Verwenden Sie ENUM anstelle von VARCHAR

Der ENUM-Typ ist sehr schnell und kompakt. Tatsächlich wird TINYINT gespeichert, aber es erscheint als Zeichenfolge. Auf diese Weise ist es ideal, dieses Feld zum Erstellen einiger Auswahllisten zu verwenden.
Wenn Sie ein Feld wie „Geschlecht“, „Land“, „Ethnie“, „Status“ oder „Abteilung“ haben und wissen, dass die Werte dieser Felder begrenzt und fest sind, dann sollten Sie dies tun Verwenden Sie stattdessen ENUM, nicht VARCHAR.
MySQL hat auch einen „Vorschlag“ (siehe Punkt 10), der Ihnen sagt, wie Sie Ihre Tabellenstruktur neu organisieren können. Wenn Sie ein VARCHAR-Feld haben, werden Sie in diesem Ratschlag dazu aufgefordert, es in einen ENUM-Typ zu ändern. Mit PROCEDURE ANALYSE() können Sie relevante Vorschläge erhalten.

13. Lassen Sie sich von der PROCEDURE ANALYSE() Programmer Station beraten.

PROCEDURE ANALYSE() hilft Ihnen bei der Analyse Ihrer Felder und deren tatsächlichen Daten und gibt Ihnen einige nützliche Vorschläge. Diese Vorschläge sind nur dann nützlich, wenn die Tabelle tatsächliche Daten enthält, da für einige wichtige Entscheidungen Daten als Grundlage erforderlich sind.
Wenn Sie beispielsweise ein INT-Feld als Primärschlüssel erstellen, aber nicht viele Daten vorhanden sind, empfiehlt PROCEDURE ANALYSE() Ihnen, den Typ dieses Felds in MEDIUMINT zu ändern. Oder wenn Sie ein VARCHAR-Feld verwenden, weil nicht viele Daten vorhanden sind, erhalten Sie möglicherweise den Vorschlag, es in ENUM zu ändern. Diese Vorschläge sind alle möglich, da nicht genügend Daten vorliegen und die Entscheidungsfindung daher nicht genau genug ist.
In phpmyadmin können Sie beim Anzeigen der Tabelle auf „Tabellenstruktur vorschlagen“ klicken, um diese Vorschläge anzuzeigen.

Beachten Sie unbedingt, dass dies nur Vorschläge sind, nur wenn Ihre Tabelle immer mehr Daten enthält. Diese Empfehlungen werden zutreffend sein. Denken Sie daran, dass Sie derjenige sind, der die endgültige Entscheidung trifft

14. Verwenden Sie so oft wie möglich die PHP-Programmierstation NOT NULL

Es sei denn, Sie haben einen ganz bestimmten Grund, NULL-Werte zu verwenden, sollten Sie Ihre Felder immer NICHT NULL halten. Das mag etwas kontrovers erscheinen, bitte lesen Sie weiter.
Fragen Sie sich zunächst, was der Unterschied zwischen „Leer“ und „NULL“ ist (wenn es INT ist, also 0 und NULL)? Wenn Sie der Meinung sind, dass es keinen Unterschied zwischen ihnen gibt, sollten Sie NULL nicht verwenden . (Wussten Sie schon? In Oracle sind NULL- und leere Zeichenfolgen dasselbe!)
Glauben Sie nicht, dass NULL keinen Platz benötigt, es benötigt zusätzlichen Platz und wenn Sie Vergleiche durchführen, wird Ihr Programm komplexer. Dies bedeutet natürlich nicht, dass Sie nicht NULL verwenden können. Die Realität ist sehr kompliziert und es wird immer noch Situationen geben, in denen Sie NULL-Werte verwenden müssen.
Das Folgende ist ein Auszug aus der MySQL-eigenen Dokumentation:

15. Vorbereitete Anweisungen

Vorbereitete Anweisungen sind sehr Ähnlich wie bei gespeicherten Prozeduren handelt es sich um eine Sammlung von SQL-Anweisungen, die im Hintergrund ausgeführt werden. Die Verwendung vorbereiteter Anweisungen bietet viele Vorteile, unabhängig davon, ob es sich um Leistungs- oder Sicherheitsprobleme handelt.
Vorbereitete Anweisungen können einige von Ihnen gebundene Variablen überprüfen, was Ihr Programm vor „SQL-Injection“-Angriffen schützen kann. Natürlich können Sie Ihre Variablen auch manuell überprüfen. Manuelle Überprüfungen sind jedoch problematisch und werden von Programmierern oft vergessen. Wenn wir ein Framework oder ORM verwenden, wird dieses Problem besser.
In Bezug auf die Leistung können Sie dadurch erhebliche Leistungsvorteile erzielen, wenn dieselbe Abfrage mehrmals verwendet wird. Sie können einige Parameter für diese vorbereiteten Anweisungen definieren, und MySQL analysiert sie nur einmal.
Obwohl die neueste Version von MySQL bei der Übertragung vorbereiteter Anweisungen das Binärformat verwendet, wird die Netzwerkübertragung dadurch sehr effizient.
Natürlich gibt es einige Fälle, in denen wir die Verwendung von Prepared Statements vermeiden müssen, da diese kein Abfrage-Caching unterstützen. Es heißt aber, dass es ab Version 5.1 unterstützt wird. Um vorbereitete Anweisungen in PHP zu verwenden, können Sie dessen Handbuch lesen: mysqli-Erweiterung oder eine Datenbankabstraktionsschicht verwenden, wie zum Beispiel: PDO.

Unbuffered query

Wenn Sie unter normalen Umständen eine SQL-Anweisung in Ihrem Skript ausführen, stoppt Ihr Programm dort, bis keine SQL-Anweisung mehr zurückgegeben wird, und dann setzt Ihr Programm die Ausführung unten fort. Sie können ungepufferte Abfragen verwenden, um dieses Verhalten zu ändern.
Zu diesem Thema gibt es eine sehr gute Erklärung in der PHP-Dokumentation: mysql_unbuffered_query() Funktion:
Die Übersetzung des obigen Satzes bedeutet, dass mysql_unbuffered_query() eine SQL-Anweisung an MySQL sendet Ergebnisse nicht automatisch abrufen und zwischenspeichern wie mysql_query(). Dies spart viel Speicher, insbesondere bei Abfragen, die eine große Anzahl von Ergebnissen generieren, und Sie müssen nicht warten, bis alle Ergebnisse zurückgegeben werden. Sie müssen nur die erste Datenzeile zurückgeben und können beginnen Funktioniert sofort. Die Abfrageergebnisse sind verfügbar.
Dies ist jedoch mit einigen Einschränkungen verbunden. Denn entweder müssen Sie alle Zeilen lesen oder mysql_free_result() aufrufen, um die Ergebnisse vor der nächsten Abfrage zu löschen. Außerdem funktionieren mysql_num_rows() oder mysql_data_seek() nicht. Daher müssen Sie sorgfältig überlegen, ob Sie ungepufferte Abfragen verwenden.

17. Speichern Sie die IP-Adresse als UNSIGNED INT

Viele Programmierer erstellen ein VARCHAR(15)-Feld, um Zeichenketten zu speichern IP statt geformtes IP. Wenn Sie zum Speichern eine Ganzzahl verwenden, sind nur 4 Bytes erforderlich, und Sie können Felder mit fester Länge haben. Darüber hinaus bringt Ihnen dies Vorteile bei der Abfrage, insbesondere wenn Sie WHERE-Bedingungen wie diese verwenden müssen: IP zwischen ip1 und ip2.
Wir müssen UNSIGNED INT verwenden, da die IP-Adresse die gesamte 32-Bit-Ganzzahl ohne Vorzeichen verwendet.
Für Ihre Abfrage können Sie INET_ATON() verwenden, um eine Zeichenfolgen-IP in eine Ganzzahl umzuwandeln, und INET_NTOA() verwenden, um eine Ganzzahl in eine Zeichenfolgen-IP umzuwandeln. In PHP gibt es auch solche Funktionen ip2long() und long2ip().

18. Tabellen mit fester Länge werden schneller sein

Wenn alle Felder in der Tabelle „feste Länge“ haben „ , wird die gesamte Tabelle als „statisch“ oder „feste Länge“ betrachtet. Beispielsweise enthält die Tabelle keine Felder der folgenden Typen: VARCHAR, TEXT, BLOB. Solange Sie eines dieser Felder einschließen, ist die Tabelle keine „statische Tabelle fester Länge“ mehr und die MySQL-Engine verarbeitet sie auf andere Weise.
Tabellen mit fester Länge verbessern die Leistung, da MySQL schneller sucht. Da diese festen Längen die Berechnung des Offsets der nächsten Daten erleichtern, erfolgt das Lesen natürlich schneller. Und wenn das Feld keine feste Länge hat, muss das Programm jedes Mal, wenn Sie das nächste finden möchten, den Primärschlüssel finden.
Außerdem lassen sich Tabellen mit fester Länge einfacher zwischenspeichern und neu erstellen. Der einzige Nebeneffekt besteht jedoch darin, dass Felder mit fester Länge etwas Platz verschwenden, da Felder mit fester Länge unabhängig davon, ob Sie sie verwenden oder nicht, so viel Platz beanspruchen. PHP-Programmierstation
Mithilfe der „vertikalen Teilung“-Technologie (siehe nächster Punkt) können Sie Ihre Tabelle in zwei Teile aufteilen, eine mit fester Länge und eine mit variabler Länge.

19. Vertikale Aufteilung

„Vertikale Aufteilung“ ist eine Möglichkeit, die Tabellen in der Datenbank spaltenweise in mehrere Tabellen umzuwandeln Methode, die die Komplexität der Tabelle und die Anzahl der Felder reduzieren und so den Zweck der Optimierung erreichen kann. (Ich habe früher Projekte in einer Bank durchgeführt und eine Tabelle mit mehr als 100 Feldern gesehen, was beängstigend war)
Beispiel 1: Es gibt ein Feld in der Benutzertabelle, das die Privatadresse ist. Im Vergleich zu ,. und wenn Sie eine Datenbank betreiben, müssen Sie dieses Feld mit Ausnahme persönlicher Informationen nicht häufig lesen oder neu schreiben. Warum also nicht ihn in eine andere Tabelle einfügen? Dadurch wird Ihre Tabelle besser.

Verwandte Empfehlungen:

Zusammenfassung der MySQL-Optimierung- Gesamtzahl der Abfragen

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