6 conseils simples pour optimiser les performances des instructions SQL SELECT

Le réglage des performances des instructions SELECT est parfois une tâche très chronophage et, à mon avis, cela suit le principe de Pareto. 20 % de l'effort vous apportera probablement 80 % d'amélioration des performances, et cela peut vous prendre 80 % du temps pour obtenir les 20 % restants de l'amélioration des performances. À moins que vous ne travailliez sur Vénus, où chaque jour équivaut à 243 jours sur Terre, il y a de fortes chances que les délais de livraison ne vous laissent pas suffisamment de temps pour régler vos requêtes SQL.

Sur la base de mes années d'expérience dans l'écriture et l'exécution d'instructions SQL, j'ai commencé à développer une liste de contrôle à laquelle je me réfère lorsque j'essaie d'améliorer les performances des requêtes. J'y fais référence avant de planifier des requêtes et de lire la documentation de la base de données que j'utilise, qui peut parfois être complexe. Ma liste de contrôle n'est en aucun cas complète ou scientifique, il s'agit plutôt d'un calcul conservateur, mais je peux dire qu'en suivant ces étapes simples, j'obtiens la plupart du temps des améliorations de performances. Liste de contrôle ci-dessous.

Vérifier les index

Des index doivent être ajoutés à tous les champs utilisés dans les parties WHERE et JOIN de l'instruction SQL. Faites ce test de performances SQL de 3 minutes. Quelle que soit votre note, assurez-vous de lire ces résultats avec des informations.

Limitez la taille de l'ensemble de données de travail

Vérifiez les tables utilisées dans ces instructions SELECT pour voir si vous pouvez appliquer une clause WHERE pour le filtrage. Un exemple typique est une requête qui fonctionne bien lorsqu’il n’y a que quelques milliers de lignes dans la table. Mais à mesure que l’application grandissait, les requêtes ralentissaient. La solution peut être aussi simple que de limiter la requête pour afficher les données du mois en cours.

Lorsque votre instruction de requête a une sous-requête, veillez à utiliser le filtrage sur l'instruction interne de la sous-requête, et non sur l'instruction externe.

Sélectionnez uniquement les champs dont vous avez besoin.

Les champs supplémentaires augmentent généralement la texture des données renvoyées, ce qui entraîne le renvoi d'un plus grand nombre de données au client SQL. Aussi :

• Lors de l'utilisation d'applications dotées de capacités de reporting et d'analyse, les performances de reporting sont parfois faibles car l'outil de reporting doit regrouper les données qu'il reçoit sous forme détaillée.

• Parfois, la requête peut s'exécuter assez rapidement, mais votre problème peut être lié au réseau, car de grandes quantités de données détaillées sont envoyées via le réseau au serveur de reporting.

• Lorsque vous utilisez un SGBD orienté colonnes, seules les colonnes que vous sélectionnez sont lues à partir du disque. Moins vous incluez de colonnes dans votre requête, plus la surcharge d’E/S est faible.

Supprimer les tables inutiles

La raison de la suppression des tables inutiles est la même que celle de la suppression des champs inutiles dans l'instruction de requête.

L'écriture d'instructions SQL est un processus qui nécessite généralement un grand nombre de processus itératifs d'écriture et de test d'instructions SQL. Pendant le développement, vous pouvez ajouter des tables à une requête, ce qui peut n'avoir aucun impact sur les données renvoyées par le code SQL. Une fois que SQL fonctionne correctement, je constate que de nombreuses personnes ne révisent pas leurs scripts et ne suppriment pas les tables qui n'ont aucun impact sur les données finales renvoyées. En supprimant les opérations JOINS avec des tables inutiles, vous réduisez un grand nombre de processus que la base de données doit exécuter. Parfois, comme lors de la suppression de colonnes, vous constaterez que les données que vous avez réduites reviennent via la base de données.

Supprimer la requête de jointure externe

C'est plus facile à dire qu'à faire, cela dépend de l'impact de la modification du contenu de la table. Une solution consiste à supprimer l’opération OUTER JOINS en plaçant des espaces réservés dans les lignes des deux tables. Supposons que vous disposiez des tables suivantes, qui garantissent que toutes les données sont renvoyées en définissant OUTER JOINS :

CUSTOMER_ID	CUSTOMER_NAME
1	John Doe
2	Mary Jane
3	Peter Pan
4	Joe Soap

CUSTOMER_ID	SALES_PERSON
NULL	Newbee Smith
2	Oldie Jones
1	Another Oldie
NULL	Greenhorn

La solution consiste à ajouter un espace réservé à la ligne de la table client et à mettre à jour toutes les valeurs NULL de la table des ventes avec l'espace réservé.

CUSTOMER_ID	CUSTOMER_NAME
0	NO CUSTOMER
1	John Doe
2	Mary Jane
3	Peter Pan
4	Joe Soap

CUSTOMER_ID	SALES_PERSON
0	Newbee Smith
2	Oldie Jones
1	Another Oldie
0	Greenhorn

Non seulement vous supprimez la dépendance à l'égard de l'opération OUTER JOIN, mais vous standardisez également la manière dont les vendeurs sans clients sont représentés. Les autres développeurs n'ont pas besoin d'écrire des instructions supplémentaires telles que ISNULL(customer_id, « No customer yet »).

Supprimez les champs calculés dans les clauses JOIN et WHERE

C'est une autre astuce qui peut parfois être plus facile à dire qu'à faire, cela dépend de la modification du schéma de la table. les autorisations. Le champ calculé utilisé dans l'instruction de jointure peut être créé en tant que nouveau champ dans la table. Étant donné l'instruction SQL suivante :

FROM sales a 
JOIN budget b ON    ((YEAR(a.sale_date)* 100) + MONTH(a.sale_date)) = b.budget_year_month

L'ajout d'une colonne utilisant l'année et le mois dans la table des ventes peut améliorer les performances. L'instruction SQL mise à jour sera la suivante :

SELECT * FROM PRODUCTSFROM sales a 
JOIN budget b ON    a.sale_year_month = b.budget_year_month

Résumé

Les suggestions ci-dessus peuvent se résumer aux points suivants :

• Vérifiez l'index

• Opérez sur l'ensemble de données minimum requis

• Supprimez les champs et les tables inutiles

• Supprimez les opérations de calcul dans vos clauses JOIN et WHERE

Si toutes ces suggestions ne parviennent pas à améliorer les performances de vos requêtes SQL, la dernière suggestion est de passer à Venus. Tout ce dont vous avez besoin est une journée pour régler vos instructions SQL.

Ce qui précède est le contenu de 6 conseils simples pour optimiser les performances de l'instruction SQL SELECT. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !