Detaillierte Erläuterung der MySQL-Datenbankoptimierungsstrategie in PHP

Dieser Artikel beschreibt kurz die MySQL-Optimierungsstrategie für die PHP-Datenbankprogrammierung. Teilen Sie es als Referenz mit allen. Die Details lauten wie folgt:

Ich habe vor ein paar Tagen einen Artikel gesehen, in dem es hieß, dass der Flaschenhals von PHP in vielen Fällen nicht in PHP selbst liegt, sondern im Datenbank. Wir alle wissen, dass bei der PHP-Entwicklung das Hinzufügen, Löschen, Ändern und Überprüfen von Daten im Mittelpunkt steht. Um die Betriebseffizienz von PHP zu verbessern, müssen Programmierer nicht nur Code mit klarer Logik und hoher Effizienz schreiben, sondern auch in der Lage sein, Abfrageanweisungen zu optimieren. Obwohl wir nichts an der Lese- und Schreibgeschwindigkeit der Datenbank ändern können, kann PHP mithilfe einiger Datenbankklassenerweiterungen und Datenspeicherserver wie Memcache, Mongodb und Redis auch schnellere Zugriffsgeschwindigkeiten erzielen. Verstehen und lernen Sie also Diese Erweiterungen sind auch sehr notwendig. In diesem Artikel wird zunächst auf die gängigen Optimierungsstrategien von MySQL eingegangen.

Ein paar MySQL-Tipps

1. Schlüsselwörter in SQL-Anweisungen werden am besten in Großbuchstaben geschrieben. Erstens ist es einfacher, Schlüsselwörter zu unterscheiden. und das Operationsobjekt: 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, um die Anzahl der automatischen Inkrementierungs-IDs mit N zu beginnen.
3. Fügen Sie das Attribut ZEROFILL zum Typ int hinzu, um die Daten automatisch auszufüllen.
4 Beim Importieren einer großen Datenmenge ist es am besten, zuerst den Index zu löschen Daten und fügen Sie dann den Index hinzu. Andernfalls wird MySQL viel Zeit damit verbringen, 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 und /path/mysql -uusername -ppassword Datenbankname < Dateiname.sql im aktuellen Verzeichnis verwenden, um die SQL der gesamten Dateianweisung auszuführen (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 aus Gründen der Bequemlichkeit kann eine gewisse Datenredundanz vorhanden sein Abfrage.

2. Wählen Sie den Datentyp int > enum, varchar > aus Der int-Typ wird in den Unsign-Typ konvertiert und zur Speicherung verwendet.

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. Die Verwendung des Partitionsbefehls zum Partitionieren einer einzelnen Tabelle beim Erstellen einer Tabelle kann die Abfrageeffizienz erheblich verbessern. Unter diesen werden RANGE, LIST, HASH und KEY am häufigsten verwendet. Die Partitionierungsmethode ist:

CREATE TABLE tablename{
}ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码
PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区
PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围

5. Bei der Auswahl einer Datenbank-Engine sollten Sie auf den Unterschied zwischen innodb und myisam achten.

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: Nicht von MyISAM unterstützt. InnoDB unterstützt

Indexoptimierung

1. Beim Speichern des Index muss ein Primärschlüssel vorhanden sein , die Engine generiert automatisch einen versteckten Primärschlüssel und einen Primärindex. Die physische Adresse des Primärschlüssels wird im Index gespeichert. Jedes Mal, wenn Sie den Primärschlüssel verwenden, müssen Sie ihn finden den Primärindex und suchen Sie dann die Daten unter dem Primärindex.

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 der Myisam-Indizes ist gleich und zeigt auf die 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, separate Indizes für mehrere Bedingungen erstellen oder eine SQL-Abfrage ausführen, wählt MySQL nur den nächstgelegenen Index aus, der verwendet werden soll. Wenn also eine Abfrage mit mehreren Bedingungen erforderlich ist, Es muss ein gemeinsamer Index erstellt werden, auch wenn dies zu Datenredundanz führt.

联合索引的BTREE建立方法：对第一个条件建立索引，在第一个索引的BTREE区域对第二个条件建立索引，以此类推，所以，在使用索引时，不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序，有序列的使用。

4、索引长度对查询也有很大影响，我们应该尽量建立短的索引长度，我们可以使用查询列

SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename

来测试对column列建立索引时选取不同的长度，索引的覆盖率有多大，我们选择一下接近饱和的n个长度来建立索引
ALTER TABLE tablename ADD INDEX (column(n)); 来对某一列的前n个字符建立索引。若前n个字符相同，我们甚至可以对字符串进行反转存储，然后建立索引。

5、对于经常修改导致的索引碎片的维护方式：ALTER TABLE tablename ENGINE oldengine;即再次应用一下表存储引擎，使其自动维护；也可以用 OPTIMIZE tablename 命令来进行维护。

数据查询方面优化

数据库操作尽量少查询，有查询时尽量不在数据库层面上进行数据操作，而是返回到PHP脚本中操作数据，减轻数据库压力。

一旦发现有数据库性能问题，要及时解决，一般用慢查询日志记录查询很"慢"的语句，用EXPLAIN分析查询和索引使用情况，用PROFILE分析语句执行时的具体资源消耗。

慢查询日志：

1、在my.ini或my.cnf的[mysqld]下添加

slow_query_log_file=/path //设置日志存储路径
long_query_time=n //设置如果语句执行时间达到n秒，就会被记录下来

2、然后在MySQL里设置SET slow_query_log='ON'来开启慢查询。

3、记录下日志后，我们用/bin/目录下的mysqldumpslow filename来查看日志，其常用参数如下:

-g pattern 使用正则表达式
-t n返回前n条数据
-s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序

EXPLAIN语句

使用方法，在要执行的查询语句前面加EXPLAIN

EXPLAIN SELECT * FROM user;

得到形如下图的结果：

下面是对每一项的解释：

id 查询语句的id，简单查询无意义，多重查询时可以看出执行查询的顺序
select-type 执行的查询语句的类型，对应多重查询，有simple/primary/union等。
tabel 查询语句查询的数据表
type 获得数据的类型 常见的类型效率从高到低为 null>const>eq_ref>ref>range>index>all
possible-keys:可能使用到的索引
key 使用到的索引
key_len索引长度
ref 使用哪个列与索引一起从表中选择。
rows 查找到数据要扫描的大概行数，可看出索引的优劣
extra 常见的有
using filesort 查询到数据后进行文件排序，较慢，需要优化索引
using where 读取整行数据后进行判断过滤，是否符合where条件
using index 索引覆盖，即在牵引中已经有这存储了目标数据，直接读取索引，很快。

PROFILE

用SELECT @@frofiling来查看PROFILE的开启状态。
如果未开启，用SET profiling=1来开启。
开启之后，再执行查询语句，MySQL会自动记录profile信息。
应用show profiles查看所有的sql信息，结果为 Query_ID Duration Query三列结果，分别是查询ID，用时和所用的sql语句。
我们可以使用

SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]

type常见有ALL(全部) BLOCK IO(显示IO相关开销) CPU(CPU开销) MEMORY(内存开销)等

大型存储方面优化

数据库主从复制和读写分离

1、master将改变记录到二进制日志中，slave将master的二进制拷贝到它的中继日志中，重新将数据返回到它自己的数据中，达到复制主服务器数据的目的。

主从复制可以用作：数据库负载均衡、数据库备份、读写分离等功能。

2、配置主服务器master

修改my.ini/my.conf

[mysqld]
log-bin=mysql-bin //启用二进制日志
server-id=102 //服务器唯一ID

3、配置从服务器slave

log-bin=mysql-bin //启用二进制日志
server-id=226 //服务器唯一ID

4、在主服务器上授权从服务器

GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'

5、在从服务器上使用

ändern Sie den Master in
master_host="masterip",
master_user="masteruser",
master_password="masterpasswd";

6 Der Slave-Befehl startet die Master-Slave-Replikation.

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 Lese- und Schreibvorgängen 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, werden die Tabellen, die in der Datenbank eng miteinander verbunden sind (z. B. dasselbe Modul, oft verbunden und abgefragt), in verschiedene Master unterteilt -Slave auf dem Server.

Horizontale Segmentierung: Wenn nicht viele Tabellen vorhanden sind und die Datenmenge in der Tabelle sehr groß ist, können Sie zur Beschleunigung der Abfrage Hashing und andere Algorithmen zum Teilen verwenden Teilen Sie eine Datentabelle 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.

Gemeinsame Segmentierung: In den meisten Fällen sind die Datentabelle und die Datenmenge in der Tabelle sehr groß, sodass eine gemeinsame Segmentierung erforderlich ist, d. h. eine vertikale und horizontale Tabellenaufteilung Gleichzeitig 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 tiefgreifend und tiefgreifend ist. Wenn Sie diese Methoden verstehen und sich daran erinnern, können Sie bei Bedarf eine gezielte Auswahl und Optimierung vornehmen beste Ergebnisse für die Datenbank.

Als nächstes werden wir die grundlegenden Nutzungsszenarien und Nutzungsmethoden der häufig verwendeten PHP-Datenbankklassenerweiterungen Memcache, Redis und Mongodb weiter zusammenfassen.

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der MySQL-Datenbankoptimierungsstrategie in PHP. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!