Comment optimiser les requêtes SQL pour des sites plus rapides

Optimisation d'image et optimisation des requêtes de base de données: un guide pratique pour améliorer la vitesse des sites Web WordPress

Cet article a été initialement publié sur le blog Delicious Brains et est reproduit ici avec la permission

Vous savez qu'un site Web rapide signifie des utilisateurs plus heureux, de meilleurs classements Google et des taux de conversion plus élevés. Vous pourriez même penser que votre site WordPress est assez rapide: vous avez vérifié les performances du site, des meilleures pratiques de configuration de serveur pour ralentir le dépannage du code et décharger des images vers CDN, mais est-ce tout?

Pour les sites Web dynamiques basés sur la base de données comme WordPress, vous pouvez toujours faire face à un problème: les requêtes de base de données font ralentir la vitesse du site Web.

Dans cet article, je vous guiderai à travers la façon d'identifier les requêtes qui provoquent des goulots d'étranglement, comment comprendre les problèmes avec ces requêtes et d'autres moyens de réparer et d'accélérer rapidement. J'utiliserai une requête réelle que nous avons récemment résolue qui ralentit le portail client DeliciousBrains.com.

requête et identification

La première étape de la fixation des requêtes SQL lentes consiste à les trouver. Ashley a précédemment salué le plugin de débogage de moniteur de requête dans son blog, et la capacité de requête de la base de données du plugin en fait un outil précieux pour identifier les requêtes SQL lentes. Ce plugin rapporte toutes les requêtes de base de données effectuées lors des demandes de page. Il vous permet de les filtrer par le code ou le composant (plugin, thème ou noyau WordPress) qui les appelle et mettent en surbrillance des requêtes en double et lente:

Si vous ne souhaitez pas installer de plugins de débogage sur votre site de production (peut-être que vous craignez d'ajouter des frais généraux de performances), vous pouvez choisir d'activer les journaux de requête lente MySQL, qui journaux toutes les requêtes qui prennent un certain temps pour exécuter . Ceci est relativement facile à configurer et à définir l'emplacement de journalisation pour la requête. Comme il s'agit d'un réglage au niveau du serveur, l'impact des performances sera inférieur au plugin de débogage sur le site, mais il doit être désactivé lorsqu'il n'est pas utilisé.

Comprendre les questions de requête

Après avoir trouvé la requête coûteuse à s'améliorer, l'étape suivante consiste à essayer de comprendre ce qui fait ralentir la requête. Récemment, lors du développement de notre site Web, nous avons constaté qu'une requête prend environ 8 secondes à exécuter!

<code class="language-sql">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</code>

Nous utilisons le logiciel WooCommerce et WooCommerce pour s'abonner à une version personnalisée du plugin pour exécuter notre boutique de plugin. Le but de cette requête est d'obtenir tous les abonnements à des clients qui nous connaissons le numéro de client. WooCommerce dispose d'un modèle de données assez complexe, même si la commande est stockée en tant que type de publication personnalisé, l'ID du client (le magasin pour lequel chaque client crée un utilisateur WordPress) n'est pas stocké en tant que post_author, mais dans le cadre des métadonnées de poste. Le plugin d'abonnement logiciel crée également plusieurs connexions de table personnalisées. Jetons un aperçu de la requête.

Utilisez les outils MySQL

MySQL fournit une instruction pratique DESCRIBE qui peut être utilisée pour produire des informations sur la structure d'une table, telles que ses colonnes, ses types de données et ses valeurs par défaut. Donc, si vous le faites DESCRIBE wp_postmeta;, vous verrez les résultats suivants:

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

C'est cool, mais vous le savez probablement déjà. Mais saviez-vous que le préfixe d'instruction DESCRIBE peut réellement être utilisé pour les instructions SELECT, INSERT, UPDATE, REPLACE, DELETE et EXPLAIN? Ceci est plus souvent appelé son synonyme

, qui nous donnera des informations détaillées sur la façon dont les déclarations sont exécutées.

Voici les résultats de notre requête lente:

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

À première vue, ce n'est pas facile à expliquer. Heureusement, les amis de SitePoint ont écrit un guide complet sur la compréhension de la déclaration.

La colonne la plus importante est

, qui décrit comment les tables sont jointes. Si vous voyez type, cela signifie que MySQL lit l'ensemble du tableau du disque, augmentant le taux d'E / S et augmentant la charge du CPU. C'est ce qu'on appelle une "numérisation de table complète" (plus à ce sujet plus tard). ALL La colonne

est également une bonne indication que MySQL doit faire car elle montre le nombre de lignes qu'il examine afin de trouver le résultat. rows

Plus d'informations disponibles pour l'optimisation sont également fournies. Par exemple, la table EXPLAIN (pm2), qui nous dit que nous utilisons wp_postmeta parce que nous avons besoin que le résultat soit trié à l'aide de la clause filesort dans l'instruction. Si nous regroupons également les requêtes, nous augmenterons les frais généraux de l'exécution. ORDER BY

Analyse visuelle

MySQL Workbench est un autre outil pratique et gratuit qui peut être utilisé pour de telles enquêtes. Pour les bases de données fonctionnant sur MySQL 5.6 et ultérieurement, le résultat de

peut être sorti en tant que JSON, que MySQL Workbench se convertit en un plan d'exécution visuelle pour l'instruction: EXPLAIN

Il attire automatiquement votre attention en colorant les parties de la requête par coût. Nous pouvons immédiatement voir qu'il y a un problème grave avec la jonction du tableau

(alias l). wp_woocommerce_software_licences

Solution Une analyse de table complète est effectuée sur une partie de la requête

, et vous devriez essayer d'éviter cela car il utilise la colonne non index

comme connexion entre le tableau order_id et le tableau wp_woocommerce_software_licences . Il s'agit d'un problème courant avec des requêtes lentes et peut être facilement résolue. wp_posts

Ajouter un index

fait partie des données d'identification très importantes du tableau, et si nous interrogeons comme celle-ci, nous devons en effet ajouter un index sur cette colonne, sinon MySQL scannera progressivement le tableau jusqu'à ce que la ligne souhaitée soit trouvée. Ajoutons un index et voyons ce qu'il fera: order_id

<code class="language-sql">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</code>

wow, nous avons réussi à réduire la requête en ajoutant cet index pendant plus de 5 secondes, bravo!

Apprenez votre requête

Vérifiez la requête-connectez-vous une par une, sous-citeuse une par une. A-t-il effectué des opérations indésirables? Quelles optimisations peuvent être pratiquées?

Dans ce cas, nous utilisons order_id pour connecter la table de licence à la table de poste tout en restreignant l'instruction au type de poste shop_order. Il s'agit de forcer l'intégrité des données pour nous assurer que nous n'utilisons que l'enregistrement de commande correct. Cependant, c'est en fait la partie redondante de la requête. Nous savons qu'avoir une ligne de licence logicielle dans le tableau avec order_id lié ​​à l'ordre WooCommerce dans la table de poste est un pari sûr car cela est appliqué dans le code du plugin PHP. Supprimons la connexion et voyons si cela améliorera la situation:

Ce n'est pas une grosse économie, mais la requête est désormais inférieure à 3 secondes.

cache

Si votre serveur n'attire pas la mise en cache de requête MySQL par défaut, il vaut la peine d'activer. Cela signifie que MySQL gardera des enregistrements de toutes les instructions exécutées et de leurs résultats, et si la même instruction est ensuite exécutée, les résultats mis en cache seront renvoyés. Le cache n'expirera pas car MySQL actualise le cache lors du changement de la table.

Le moniteur de requête a constaté que notre requête était exécutée 4 fois dans une charge de page, et bien que l'activation de la mise en cache de requête MySQL soit bonne, la lecture répétée de la base de données dans une demande doit en fait être évité complètement. La mise en cache statique dans le code PHP est un moyen simple et très efficace de résoudre ce problème. Fondamentalement, vous les obtenez de la base de données lorsque la première fois que vous demandez les résultats de la requête de la base de données et les stockez dans les propriétés statiques de la classe, et les appels ultérieurs renverront le résultat des propriétés statiques:

<code class="language-sql">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</code>

La durée de vie du cache est la durée de vie de la demande, et plus précisément, la durée de vie de l'objet instancié. Si vous souhaitez persister les résultats de la requête entre les demandes, vous devez implémenter la mise en cache d'objets persistants. Cependant, votre code doit être responsable de la configuration du cache et de l'invalidation de l'entrée du cache lorsque les données sous-jacentes changent.

Autres méthodes

Nous pouvons prendre d'autres moyens pour essayer d'accélérer l'exécution de la requête, ce qui nécessite plus de travail que de simplement régler la requête ou l'ajout d'index. L'une des parties les plus lentes de notre requête est le travail de connexion de la table de l'ID client à l'ID de produit, ce que nous devons faire pour chaque client. Que se passe-t-il si nous effectuons toutes les connexions qu'une seule fois, alors que devons-nous faire si nous n'avons besoin d'obtenir les données des clients que lorsque nous en avons besoin?

Vous pouvez dés-normaliser les données en créant un tableau qui stocke les données de licence ainsi que les ID utilisateur et les ID de produit pour toutes les licences, interrogez simplement le tableau pour un client spécifique. Vous devez reconstruire le tableau lorsque vous utilisez MySQL Triggers INSERT / UPDATE / DELETE /

à la table de licence (ou à d'autres tables, selon la façon dont les données changent), mais cela améliorera considérablement les performances des performances des performances des performances de interroger ces données.

De même, si de nombreuses connexions ralentissent les requêtes dans MySQL, il peut être plus rapide de diviser la requête en deux instructions ou plus et de les exécuter séparément en PHP, puis de collecter et de filtrer les résultats de votre code. Laravel effectue des opérations similaires en chargeant avec impatience des relations dans Eloquent.

Si vous avez une grande quantité de données et que vous avez de nombreux types de messages personnalisés différents, WordPress peut être sujet à des requêtes plus lentes sur le tableau wp_posts. Si vous trouvez lent à interroger votre type de message, envisagez d'abandonner le modèle de stockage de type post personnalisé et d'utiliser une table personnalisée.

Résultat

Avec ces méthodes d'optimisation des requêtes, nous avons réussi à réduire le temps de requête de 8 secondes à un peu plus de 2 secondes et à réduire le nombre d'appels de 4 à 1. Notez que ces temps de requête sont enregistrés dans notre environnement de développement et seront plus rapides dans les environnements de production.

J'espère que ce guide vous sera utile de suivre et de réparer les requêtes lentes. L'optimisation des requêtes peut sembler une tâche terrible, mais une fois que vous avez essayé de réussir rapidement, vous commencerez à être fasciné par lui et espérez vous améliorer davantage.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!