Heading

Dans cet article, nous allons nous concentrer sur les astuces les plus simples et les meilleures que les développeurs peuvent mettre en œuvre pour leurs efforts quotidiens d’optimisation des performances SQL. Cet article se concentrera sur l’optimisation des performances des requêtes MySQL, mais les mêmes concepts peuvent être appliqués à de nombreuses autres bases de données relationnelles.

Aujourd’hui plus que jamais, les ingénieurs logiciels doivent avoir de vastes connaissances en matière d’optimisation des performances SQL.
Ce changement se produit aussi bien dans les petites startups que dans les grandes entreprises. Aujourd’hui, ce sont les développeurs qui écrivent les requêtes SQL et la couche d’accès à la base de données.

Cela n’a pas vraiment d’importance si vous utilisez une couche d’abstraction de base de données (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django, ou autres) ou si vous écrivez des requêtes SQL natives, vous serez éventuellement mis au défi d’accorder les requêtes que vous envoyez à votre base de données.

Alors, que pouvez-vous faire pour optimiser vos requêtes SQL ?

Créer des index, mais faites-le à bon escient

L’indexation est probablement la partie la plus importante du processus de réglage des requêtes. Donc, tout d’abord, assurez-vous de bien connaître les différents aspects à prendre en compte pour choisir les index optimaux. pour votre base de données.

Lorsque vous réfléchissez aux index à créer, vous devez prêter une attention particulière à la clause WHERE de la requête et aux JOIN de table, car ces énoncés incluent les parties indexables critiques de la requête.

De même, les principaux goulots d’étranglement peuvent provenir des parties GROUP BY et ORDER BY. Cela dit, un hic potentiel sera que vous ne pourrez peut-être pas les indexer dans certains cas, comme nous l’avons expliqué ici. Par conséquent, vous devrez peut-être repenser la conception de votre requête avant de créer les index, pour vous assurer que vous écrivez d’excellentes requêtes, mais aussi des requêtes indexables.

Une fois que vous avez compris l’indexation pour une requête, ne vous arrêtez pas là. Élargissez votre vue et examinez les autres requêtes importantes de votre application. Veillez à combiner les index chaque fois que possible, et à supprimer les index qui ne sont pas utilisés. Regarder la portée de l’application dans son ensemble sera toujours mieux que de regarder la portée d’une seule requête.

Vous devez également garder à l’esprit que la création de plus d’index que nécessaire peut également se retourner contre vous, car ils peuvent ralentir les opérations d’écriture (comme les instructions INSERT / UPDATE). Créez donc des index pour optimiser les performances de vos requêtes SQL, mais faites-le à bon escient.

Ne faites pas obstacle aux index

Nous sommes souvent sollicités par des clients qui nous demandent « pourquoi la base de données n’utilise pas mon index ? ». Eh bien, c’est une excellente question, avec une infinité de réponses possibles. Mais, dans cet article, nous allons essayer de couvrir plusieurs scénarios communs que nous voyons souvent, alors espérons que vous les trouverez utiles pour votre propre cas d’utilisation.

Exemple #1 – Évitez d’envelopper les colonnes indexées avec des fonctions

Considérez cette requête, qui compte le nombre de hot dogs achetés aux États-Unis sur 2018. Au cas où vous seriez curieux, 18 000 000 000 de hot dogs ont été vendus aux États-Unis en 2018.

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE YEAR(purchase_time) = ‘2018’

Comme vous pouvez le voir, nous utilisons la fonction YEAR pour récupérer la partie année de la colonne purchase_time. Cet appel de fonction empêchera la base de données de pouvoir utiliser un index pour la recherche de la colonne purchase_time, car nous avons indexé la valeur de purchase_time, mais pas la valeur de retour de YEAR(purchase_time).

Pour surmonter ce défi et accorder cette requête SQL, vous pouvez indexer le résultat de la fonction, en utilisant Generated Columns, qui sont disponibles à partir de MySQL 5.7.5.

Une autre solution peut être de trouver une autre façon d’écrire la même requête, sans utiliser l’appel de fonction. Dans cet exemple, nous pouvons transformer cette condition en une condition de plage à 2 voies, qui renverra les mêmes résultats:

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE purchased_at >= '2018-01-01' AND purchased_at < '2019-01-01'

Exemple #2 – éviter les conditions OR

Considérez cette requête, qui sélectionne la quantité de publications sur Facebook postées après la veille du nouvel an, ou postées par un utilisateur nommé Mark.

SELECT COUNT(*)FROM fb_postsWHERE username = ‘Mark’ OR post_time > ‘2018-01-01’

Avoir un index sur les deux colonnes username et post_time peut sembler utile, mais dans la plupart des cas, la base de données ne l’utilisera pas, du moins pas en totalité. La raison en sera le lien entre les deux conditions – l’opérateur OR, qui fait que la base de données récupère les résultats de chaque partie de la condition séparément.

Une autre façon de considérer cette requête peut être de  » diviser  » la condition OR et de la  » combiner  » en utilisant une clause UNION. Cette alternative vous permettra d’indexer chacune des conditions séparément, de sorte que la base de données utilisera les index pour rechercher les résultats, puis combinera les résultats à l’aide de la clause UNION.

SELECT …FROM …WHERE username = ‘Mark’ UNIONSELECT …FROM …WHERE post_time > ‘2018-01-01’

Veuillez noter que si les enregistrements en double dans votre ensemble de résultats ne vous dérangent pas, vous pouvez également utiliser UNION ALL (qui sera plus performant que la clause UNION DISTINCT par défaut).

Exemple n°3 – Évitez de trier avec un ordre mixte

Considérez cette requête, qui sélectionne toutes les publications de Facebook et les trie par le nom d’utilisateur dans un ordre croissant, puis par la date de publication dans un ordre décroissant.

SELECT username, post_typeFROM fb_postsORDER BY username ASC , post_type DESC

MySQL (et tant d’autres bases de données relationnelles), ne peut pas utiliser d’index lors d’un tri avec un ordre mixte (à la fois ASC et DESC dans la même clause ORDER BY). Cela a changé avec la sortie de la fonctionnalité des index inversés et de MySQL 8.x.

Alors, que pouvez-vous faire si vous n’avez pas encore effectué la mise à niveau vers la dernière version de MySQL ? Tout d’abord, nous vous recommandons de reconsidérer le tri par ordre mixte. En avez-vous vraiment besoin ? Si non, évitez-le.

Donc, vous avez décidé que vous en aviez besoin, ou votre chef de produit a dit : « Pas moyen de s’en passer » ? Une autre option sera d’utiliser Generated columns (disponible sur MySQL 5.7.5+) pour créer une colonne inversée et trier sur cette colonne au lieu de l’originale. Par exemple, si vous triez sur une colonne numérique, vous pouvez créer une colonne générée avec la valeur numérique négative en corrélation avec le nombre original et trier sur cette nouvelle colonne dans l’ordre inverse. De cette façon, toutes les colonnes auront le même ordre de tri dans la clause ORDER BY, mais le tri se fera comme défini à l’origine par l’exigence de votre produit.

La dernière solution potentielle ne sera pas toujours une option, donc votre dernier recours sera la mise à niveau vers la dernière version de MySQL qui prend en charge le tri en ordre mixte à l’aide d’index.

Exemple n°4 – Évitez les conditions avec différents types de colonnes

Considérez cette requête, qui sélectionne le nombre de fruits rouges dans une forêt.

SELECT COUNT(*)FROM forestWHERE fruit_color = 5; /* 5 = red */

En supposant que le type de la colonne fruit_color soit VARCHAR, ou tout simplement tout ce qui n’est pas numérique, indexer cette colonne ne sera pas très utile, car le cast implicite requis empêchera la base de données d’utiliser l’index pour le processus de filtrage.

Alors, comment pouvez-vous accorder cette requête SQL ? Vous avez deux options pour optimiser cette requête. La première serait de comparer la colonne à une valeur constante qui correspond au type de la colonne, donc si c’est une colonne VARCHAR, comparez-la à ‘5’ (avec des guillemets simples) et non à 5 (qui est une comparaison numérique qui entraînera un cast implicite).

Une meilleure option sera d’ajuster le type de la colonne pour qu’il corresponde au type le plus approprié pour les valeurs que la colonne contient. Dans cet exemple, la colonne doit être modifiée en un type INT. Notez que la modification du type d’une colonne peut être une tâche compliquée, lisez donc les défis de cette tâche avant de vous diriger vers elle.

Évitez les recherches LIKE avec des jokers de préfixe

Considérez cette requête, qui recherche toutes les publications Facebook à partir d’un nom d’utilisateur qui inclut la chaîne ‘Mar’, nous recherchons donc toutes les publications écrites par des utilisateurs nommés Mark, Marcus, Almar, etc.

SELECT *FROM fb_postsWHERE username LIKE '%Mar%'

Avoir un caractère générique ‘%’ au début du motif empêchera la base de données d’utiliser un index pour la recherche de cette colonne. De telles recherches peuvent prendre un certain temps…

Dans ce cas, il existe deux options pour améliorer les performances de cette requête. La première est triviale – considérez si le caractère générique du préfixe est suffisamment important. Si vous pouvez vous en passer, débarrassez-vous-en.

Une autre option sera d’utiliser des index plein texte. Notez cependant que ces index et la syntaxe MATCH …. AGAINST ne sont pas exempts de défis et présentent quelques différences par rapport aux expressions LIKE familières de MySQL.

Conclusion

Dans cette première partie de notre série sur l’optimisation des requêtes SQL, nous avons couvert l’importance d’une indexation judicieuse, nous avons parcouru plusieurs exemples d’obstacles possibles lors de l’utilisation de colonnes indexées dans les requêtes, et nous avons également détaillé plusieurs autres conseils et astuces qui peuvent être utiles pour améliorer les performances des requêtes. Rendez-vous dans le prochain post.