L'ordre des champs dans une clause WHERE affecte-t-il les performances dans MySQL?

SQL a été conçu pour être un langage déclaratif, pas un langage procédural. Ainsi, l'optimiseur de requête devrait Pas considérer l'ordre des prédicats de clause where pour déterminer comment les appliquer.

Je vais probablement simplifier la discussion suivante d'un optimiseur de requête SQL. J'ai écrit il y a un an, dans ce sens (c'était beaucoup de plaisir!). Si vous voulez vraiment creuser dans l'optimisation de requête moderne, voir SQL Tuning de Dan Tow, à partir de O'Reilly.

Dans un optimiseur de requête SQL simple, L'instruction SQL est d'abord compilée dans un arbre d'opérations algèbre relationnelle. Ces opérations prennent chacune une ou plusieurs tables en entrée et produisent une autre table en sortie. Scan est une analyse séquentielle qui lit une table dans la base de données. Tri produit un tableau trié. Select produit une table dont les lignes sont sélectionnées à partir d'une autre table selon une condition de sélection. Projet produit une table avec seulement certaines colonnes d'une autre table. Cross Product prend deux tables et produit une table de sortie composée de tous les appariements imaginables de leurs lignes.

De manière confuse, la clause SQL SELECT est compilée dans une algèbre relationnelle Project , tandis que la clause WHERE se transforme en une algèbre relationnelle Select . La clause FROM se transforme en une ou plusieurs jointures , chacune prenant deux tables et produisant une table hors. Il existe d'autres opérations d'algèbre relationnelle impliquant l'union, l'intersection, la différence et l'appartenance, mais restons simples.

Cet arbre a vraiment besoin d'être optimisé. Par exemple, si vous avez:

select E.name, D.name 
from Employee E, Department D 
where E.id = 123456 and E.dept_id = D.dept_id

Avec 5 000 employés dans 500 départements, l'exécution d'un arbre non optimisé produira aveuglément toutes les combinaisons possibles d'un employé et D'un département (un produit croisé), puis sélectionnez la seule combinaison nécessaire. Le Scan {[12] } de L'employé produira une table d'enregistrement de 5 000, le Scan du Ministère produira une table d'enregistrement de 500, le Cross Product {[12] } de ces deux tables produira une table d'enregistrement de 2 500 000, et le Select sur E.id prendra cette table d'enregistrement 2,500,000 et défaussera tout sauf un, l'enregistrement qui était recherché.

[les processeurs de requête réels essayeront bien sûr de ne pas matérialiser toutes ces tables intermédiaires en mémoire.]

Donc la requête optimizer parcourt l'arbre et applique Diverses optimisations. L'un est de casser chaque Select une chaîne de Sélectionne, un pour chaque original Select's haut niveau, les uns et par-ed ensemble. (C'est ce qu'on appelle la "forme normale conjonctive".) Ensuite, les sélections individuelles plus petites sont déplacées dans l'arbre et fusionnées avec d'autres opérations d'algèbre relationnelle pour former des opérations plus efficaces.

Dans l'exemple ci-dessus, l'optimiseur de première pousse le Select sur E.id = 123456 en dessous de l'opération coûteuse Cross Product . Cela signifie que le produit croisé ne produit que 500 lignes (une pour chaque combinaison de cet employé et d'un département). Ensuite, le niveau supérieur Select for E. dept_id = D. dept_id filtre les 499 lignes indésirables. Pas mal.

S'il y a un index sur le champ ID de L'employé, l'optimiseur peut combiner le Scan {[12] } de L'employé avec le Select sur E.id = 123456 pour former un index rapide Lookup . Cela signifie qu'une seule ligne D'employé est lue en mémoire à partir du disque au lieu de 5 000. Les choses s'améliorent.

La dernière majeur de l'optimisation est de prendre le Select sur E. dept_id = D. dept_id et de le combiner avec la Produit vectoriel. Cela le transforme en une opération d'algèbre relationnelle Equijoin . Cela ne fait pas grand-chose par lui-même. Mais s'il y a un index sur le département.dept_id, puis le niveau inférieur séquentielle Scan du Ministère de l'alimentation de la équi-jointure peut être transformé en un très rapide index Lookup de nos un employé du Département de l'enregistrement.

Les optimisations inférieures impliquent de pousser les opérationsProject vers le bas. Si le niveau supérieur de votre requête a juste besoin E.name et D.name, et les conditions ont besoin E.id, E. dept_id et D. dept_id, alors les opérations Scan n'ont pas besoin de construire des tables intermédiaires avec toutes les autres colonnes, économisant de l'espace au cours de l'exécution de la requête. Nous avons transformé une requête horriblement lente en deux recherches d'index et pas grand-chose d'autre.

OBTENIR PLUS vers la question initiale, disons que vous avez:

select E.name 
from Employee E 
where E.age > 21 and E.state = 'Delaware'

L'arbre d'algèbre relationnelle non optimisé, lorsqu'il est exécuté, Balayerait les employés 5,000 et produirait, disons, les 126 au Delaware qui ont plus de 21 ans. L'optimiseur de requête a aussi une idée des valeurs dans la base de données. Il pourrait savoir que la colonne E. state a les 14 états qui la société a des emplacements dans, et quelque chose sur les distributions E. age. Alors d'abord il voit si le champ est indexé. Si E. state l'est, il est logique d'utiliser cet index pour sélectionner le petit nombre d'employés que le processeur de requête soupçonne être dans le Delaware en fonction de ses dernières statistiques calculées. Si seulement E. age est, le processeur de requête décide probablement que cela ne vaut pas la peine, puisque 96% de tous les employés ont 22 ans et plus. Donc, si E. state est indexé, notre processeur de requête casse le sélectionnez et fusionne le E. state = 'Delaware' avec le Scan pour le transformer en un Index Scan beaucoup plus efficace .

Disons dans cet exemple qu'il n'y a pas d'index sur E. state et E. age. L'opération Select combinée a lieu après le "Scan" séquentiel de L'employé. Est-ce que cela fait une différence quelle condition dans le Select est faite en premier? Probablement pas une bonne affaire. Le processeur de requête peut les laisser dans l'ordre d'origine dans l'instruction SQL, ou il pourrait être un peu plus sophistiqué et regarder la dépense attendue. D'après les statistiques, il constaterait à nouveau que la condition E. state = 'Delaware' devrait être plus sélective, de sorte qu'elle inverserait les conditions et le ferait en premier, de sorte qu'il n'y ait que 126 E. age > 21 comparaisons au lieu de 5 000. Ou il pourrait se rendre compte que les comparaisons d'égalité de chaîne sont beaucoup plus chères que les comparaisons d'entiers et laissent l'ordre seul.

En tout cas, tout cela est très complexe et votre ordre de condition syntaxique est très peu susceptible de faire une différence. Je ne m'inquiéterais pas à ce sujet sauf si vous avez un vrai problème de performance et que votre fournisseur de base de données utilise l'ordre de condition comme indice.