So optimieren Sie SQL -Abfragen für schnellere Websites

Bildoptimierung und Datenbankabfrageoptimierung: Ein praktischer Leitfaden zur Verbesserung der Geschwindigkeit der WordPress -Websites

Dieser Artikel wurde ursprünglich im Blog Delicious Brains veröffentlicht und wird hier mit Erlaubnis

reproduziert

Sie wissen, dass eine schnelle Website glücklichere Benutzer, bessere Google -Rankings und höhere Conversion -Raten bedeutet. Sie denken vielleicht sogar, dass Ihre WordPress -Site schnell genug ist: Sie haben die Leistung der Website überprüft, von den besten Server -Setup -Praktiken bis hin zur Verlangsamung der Code -Fehlerbehebung und zum Ableiten von Bildern auf CDN, aber ist das einfach alles?

Für dynamische, datenbankgesteuerte Websites wie WordPress stehen Sie möglicherweise weiterhin vor einem Problem: Datenbankabfragen verursachen die Geschwindigkeit der Website.

In diesem Beitrag werde ich Sie durch die Identifizierung von Abfragen führen, die Engpässe verursachen, wie Sie die Probleme mit diesen Abfragen verstehen und andere Möglichkeiten, schnell zu reparieren und zu beschleunigen. Ich werde eine tatsächliche Abfrage verwenden, die wir kürzlich gelöst haben, die das Kundenportal von Deliciousbrains.com verlangsamt.

Abfrage und Identifizierung

Der erste Schritt, um langsame SQL -Abfragen zu beheben, besteht darin, sie zu finden. Ashley lobte zuvor das Debug -Plugin des Abfrage -Monitors in seinem Blog, und die Datenbank -Abfragefunktion des Plugins macht es zu einem wertvollen Tool zur Identifizierung langsamer SQL -Abfragen. Dieses Plugin berichtet über alle Datenbankabfragen, die während Seitenanforderungen durchgeführt wurden. Sie können sie durch den Code oder die Komponente (Plugin, Thema oder WordPress Core) filtern, die sie aufruft und doppelte und langsame Abfragen hervorheben:

Wenn Sie keine Debug -Plugins auf Ihrer Produktionsseite installieren möchten (möglicherweise machen Sie sich Sorgen, dass Sie einige Leistungsaufwand hinzufügen), können Sie sich für die Langweichung von MySQL Slow Query -Protokollen aktivieren, die alle Abfragen angeben, die einige Zeit für die Ausführung in Anspruch nehmen . Dies ist relativ einfach zu konfigurieren und den Protokollierungsort für die Abfrage festzulegen. Da es sich um eine Stimmung auf Serverebene handelt, sind die Leistungsauswirkungen geringer als das Debug-Plugin auf der Website, sollte jedoch bei Nichtgebrauch ausgeschaltet werden.

Fragen zur Abfrageverständnis

Nachdem Sie die teure Anfrage zur Verbesserung gefunden haben, besteht der nächste Schritt darin, zu verstehen, was die Anfrage verlangsamt. Bei der Entwicklung unserer Website stellten wir kürzlich fest, dass eine Abfrage ungefähr 8 Sekunden dauert, um auszuführen!

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

Wir verwenden WooCommerce- und WooCommerce -Software, um eine benutzerdefinierte Version des Plugins zu abonnieren, um unseren Plugin -Store auszuführen. Der Zweck dieser Abfrage ist es, alle Abonnements für Kunden zu erhalten, die wir die Kundennummer kennen. WooCommerce verfügt über ein ziemlich komplexes Datenmodell, auch wenn die Bestellung als benutzerdefinierte Post -Typ gespeichert wird, wird die ID des Kunden (der Speicher, für den jeder Kunde WordPress -Benutzer erstellt) nicht als Post_autor gespeichert, sondern als Teil der Post -Metadaten. Das Software -Abonnement -Plugin erstellt auch mehrere benutzerdefinierte Tabellenverbindungen. Schauen wir uns die Frage tiefer an.

Verwenden Sie MySQL -Tools

MySQL liefert eine bequeme DESCRIBE -Anweisung, mit der Informationen über die Struktur einer Tabelle ausgegeben werden können, z. B. ihre Spalten, Datentypen und Standardwerte. Wenn Sie also DESCRIBE wp_postmeta; tun, sehen Sie die folgenden Ergebnisse:

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20) unsigned	NO	PRI	NULL	auto_increment
post_id	bigint(20) unsigned	NO	MUL	0
meta_key	varchar(255)	YES	MUL	NULL
meta_value	longtext	YES		NULL

Das ist cool, aber Sie wissen es wahrscheinlich schon. Aber wussten Sie, dass das Präfix DESCRIBE Anweisung tatsächlich für SELECT, INSERT, UPDATE, REPLACE, DELETE und EXPLAIN verwendet werden kann? Dies wird häufiger als Synonym

bezeichnet, was uns detaillierte Informationen darüber gibt, wie Anweisungen ausgeführt werden.

Folgendes sind die Ergebnisse unserer langsamen Abfrage:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	pm2	ref	meta_key	meta_key	576	const	28	Using where; Using temporary; Using filesort
1	SIMPLE	pm	ref	post_id,meta_key	meta_key	576	const	37456	Using where
1	SIMPLE	p	eq_ref	PRIMARY,type_status_date	PRIMARY	8	deliciousbrainsdev.pm.post_id	1	Using where
1	SIMPLE	l	ref	PRIMARY,order_id	order_id	8	deliciousbrainsdev.pm.post_id	1	Using index condition; Using where
1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	8	deliciousbrainsdev.l.key_id	1	NULL

Auf den ersten Blick ist dies nicht leicht zu erklären. Glücklicherweise haben SitePoint -Freunde einen umfassenden Leitfaden zum Verständnis der Aussage geschrieben.

Die wichtigste Spalte ist

, die beschreibt, wie Tabellen verbunden sind. Wenn Sie type sehen, bedeutet dies, dass MySQL die gesamte Tabelle von der Festplatte liest, die E/A -Rate erhöht und die CPU -Last erhöht. Dies wird als "Volltisch -Scan" bezeichnet (dazu später mehr). ALL Die Spalte

ist auch ein guter Hinweis darauf, dass MySQL es tun muss, da sie die Anzahl der Zeilen anzeigt, die sie ansieht, um das Ergebnis zu finden. rows

Es werden auch weitere Informationen zur Optimierung zur Verfügung gestellt. Zum Beispiel die Tabelle EXPLAIN (pm2), in der wir mitteilen, dass wir wp_postmeta verwenden, da wir das Ergebnis mithilfe der filesort -Klausel in der Anweisung sortieren müssen. Wenn wir auch die Anfragen gruppieren, werden wir den Overhead der Ausführung erhöhen. ORDER BY

visuelle Analyse

Mysql Workbench ist ein weiteres bequemes und kostenloses Tool, das für solche Untersuchungen verwendet werden kann. Für Datenbanken, die auf MySQL 5.6 und später ausgeführt werden, kann das Ergebnis von

als JSON ausgegeben werden, den MySQL Workbench in einen visuellen Ausführungsplan für die Anweisung umwandelt: EXPLAIN

Es wird automatisch Ihre Aufmerksamkeit erregt, indem Sie die Teile der Abfrage nach Kosten färben. Wir können sofort erkennen, dass es ein ernstes Problem bei der Verbindung mit der Tabelle

(alias l) gibt. wp_woocommerce_software_licences

Lösung Ein vollständiger Tabellen-Scan wird an einem Teil der Abfrage

durchgeführt, und Sie sollten versuchen, dies zu vermeiden, da die Spalte nicht-Index

als Verbindung zwischen der Tabelle order_id und der Tabelle wp_woocommerce_software_licences verwendet wird . Dies ist ein häufiges Problem bei langsamen Abfragen und kann leicht gelöst werden. wp_posts

Index hinzufügen

ist Teil der sehr wichtigen Identifikationsdaten in der Tabelle, und wenn wir so abfragen, sollten wir tatsächlich einen Index in dieser Spalte hinzufügen, ansonsten scannt MySQL die Tabelle schrittweise, bis die gewünschte Zeile gefunden wird. Fügen wir einen Index hinzu und sehen, was er tun wird: order_id

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

wow, wir haben die Abfrage erfolgreich reduziert, indem wir diesen Index länger als 5 Sekunden hinzufügen, gut gemacht!

Erfahren Sie Ihre Abfrage

Überprüfen Sie, ob die Abfragen nacheinander nacheinander unterbinden. Hat es unerwünschte Operationen durchgeführt? Welche Optimierungen können vorgenommen werden?

In diesem Fall verwenden wir order_id, um die Lizenztabelle mit der Post -Tabelle zu verbinden, während die Anweisung auf den Post -Typ shop_order einschränkt. Dies soll die Datenintegrität erzwingen, um sicherzustellen, dass wir nur den richtigen Auftragsdatensatz verwenden. Es ist jedoch tatsächlich der redundante Teil der Abfrage. Wir wissen, dass eine Softwarelizenzzeile in der Tabelle mit order_id im Zusammenhang mit der WooCommerce -Bestellung in der Post -Tabelle eine sichere Wette ist, da dies im PHP -Plugin -Code erzwungen wird. Lassen Sie uns die Verbindung löschen und prüfen, ob dies die Situation verbessert:

Dies ist keine große Einsparung, aber die Abfrage beträgt jetzt weniger als 3 Sekunden.

cache

Wenn Ihr Server nicht standardmäßig das Caching von MySQL -Abfrage aktiviert, lohnt es sich, die Aktivierung zu ermöglichen. Dies bedeutet, dass MySQL Aufzeichnungen aller ausgeführten Aussagen und ihrer Ergebnisse aufbewahrt. Wenn anschließend dieselbe Anweisung ausgeführt wird, werden die zwischengespeicherten Ergebnisse zurückgegeben. Der Cache läuft nicht ab, da MySQL den Cache beim Ändern der Tabelle aktualisiert.

Abfragemonitor fand heraus, dass unsere Abfrage 4 -mal in einer Seitenlast ausgeführt wurde. Während das Aktivieren von MySQL -Abfrage -Caching gut ist, sollte wiederholt das Lesen der Datenbank in einer Anforderung tatsächlich vermieden werden. Das statische Caching im PHP -Code ist eine einfache und sehr effiziente Möglichkeit, dieses Problem zu lösen. Grundsätzlich erhalten Sie sie aus der Datenbank, wenn Sie zum ersten Mal die Ergebnisse der Datenbankabfrage anfordern und in den statischen Eigenschaften der Klasse speichern. Nachfolgende Anrufe geben das Ergebnis aus den statischen Eigenschaften zurück:

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

Die Lebensdauer des Cache ist die Lebensdauer der Anfrage und insbesondere die Lebensdauer des sofortigen Objekts. Wenn Sie die Anfragenergebnisse zwischen Anfragen bestehen möchten, müssen Sie ein anhaltendes Caching von Objekten implementieren. Ihr Code muss jedoch für die Einrichtung des Cache und die Ungültigmachung des Cache -Eintrags verantwortlich sein, wenn sich die zugrunde liegenden Daten ändert.

Andere Methoden

Wir können andere Möglichkeiten einschlagen, um zu versuchen, die Abfrageausführung zu beschleunigen, was mehr Arbeit erfordert, als nur die Abfrage oder das Hinzufügen von Indizes. Einer der langsamsten Teile unserer Abfrage ist die Tabellenverbindungsarbeit von der Kunden -ID zu Produkt -ID, die wir für jeden Kunden tun müssen. Was ist, wenn wir nur einmal alle Verbindungen ausführen, was sollten wir dann tun, wenn wir nur Kundendaten erhalten müssen, wenn wir sie benötigen?

Sie können Daten entfernen, indem Sie eine Tabelle erstellen, in der Lizenzdaten sowie Benutzer-IDs und Produkt-IDs für alle Lizenzen gespeichert sind. Befragen Sie einfach die Tabelle für einen bestimmten Kunden. Sie müssen die Tabelle wieder aufbauen, wenn Sie MySQL -Trigger INSERT/UPDATE/DELETE/

in die Lizenztabelle (oder andere Tabellen, je nachdem, wie sich die Daten ändert Abfragen dieser Daten.

In ähnlicher Weise kann es schneller sein, die Abfrage in zwei oder mehr Anweisungen zu unterteilen und in PHP separat auszuführen und dann in Ihrem Code zu sammeln und zu filtern. Laravel führt ähnliche Operationen durch, indem sie eifrig Beziehungen in eloquent beladen.

Wenn Sie eine große Datenmenge haben und viele verschiedene benutzerdefinierte Post -Typen haben, ist WordPress möglicherweise anfällig für langsamere Abfragen in der Tabelle wp_posts. Wenn Sie es langsam feststellen, Ihren Post -Typ abfragen, sollten Sie das benutzerdefinierte Speichermodell des postentyps und verwenden Sie eine benutzerdefinierte Tabelle.

Ergebnis

Mit diesen Abfragungsoptimierungsmethoden gelang es uns, die Abfragezeit von 8 Sekunden auf etwas mehr als 2 Sekunden zu verkürzen und die Anzahl der Anrufe von 4 auf 1 zu verkürzen. Beachten Sie, dass diese Abfragezeiten in unserer Entwicklungsumgebung aufgezeichnet sind und in Produktionsumgebungen schneller sein werden.

Ich hoffe, diese Anleitung ist hilfreich, damit Sie langsame Abfragen verfolgen und beheben können. Die Optimierung der Abfragen mag eine schreckliche Aufgabe erscheinen, aber wenn Sie versuchen, einen schnellen Erfolg zu erzielen, werden Sie davon fasziniert sein und hoffen, sich weiter zu verbessern.

Das obige ist der detaillierte Inhalt vonSo optimieren Sie SQL -Abfragen für schnellere Websites. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!