MoyenneChoix unique

Question d'entraînement DP-900

Une entreprise utilise Azure SQL Database pour une application de e-commerce. La table Orders contient des millions de lignes. Les requêtes filtrent fréquemment sur OrderDate et OrderStatus, et trient par OrderDate décroissant. Quelle stratégie d'indexation améliorera le plus les performances des requêtes ?

Choisissez la meilleure réponse.

Bonne réponse

  • DCréer un index non clusterisé sur (OrderDate DESC, OrderStatus) et conserver l'index clusterisé existant sur OrderID

Explication

Crée un index couvrant pour le motif de requête le plus courant : le filtrage sur OrderDate et OrderStatus, et le tri par OrderDate décroissant. En spécifiant DESC dans la clé d'index, l'index est trié dans la même direction que le tri, permettant à SQL Server d'éviter une opération de tri et de récupérer les lignes dans l'ordre directement depuis l'index. Cet index non clusterisé peut satisfaire entièrement la requête sans toucher à l'index clusterisé (OrderID), réduisant les E/S et améliorant les performances.

Pourquoi chaque option est juste ou fausse

A

Fausse

Créer un index clusterisé sur OrderDate et un index non clusterisé sur OrderStatus

Reconstruire l'index clusterisé autour de OrderDate réordonnerait physiquement toute la table par cette colonne, ce qui peut fragmenter les données, créer des divisions de pages et ralentir les insertions car la clé clusterisée d'OrderID est perdue en tant que pointeur de ligne logique. L'index non clusterisé séparé sur OrderStatus seul ne peut pas traiter efficacement un filtre de plage sur OrderDate, et comme OrderDate n'est pas inclus dans cet index, le moteur de requête devrait effectuer des recherches de clés et un tri. Cette conception ne parvient pas non plus à exploiter l'ordre décroissant nécessaire pour la clause ORDER BY, elle n'est donc pas optimisée pour la charge de travail demandée.

B

Fausse

Créer un index non clusterisé sur (OrderDate, OrderStatus) et conserver l'index clusterisé existant sur OrderID

Bien qu'un index non clusterisé composite sur (OrderDate, OrderStatus) corresponde aux prédicats d'égalité et de plage, l'ordre de tri ascendant par défaut sur OrderDate entre en conflit avec le ORDER BY OrderDate DESC de la requête. L'index peut théoriquement être parcouru en sens inverse, mais SQL Server peut toujours introduire un opérateur de tri explicite si l'optimiseur choisit un parcours avant ou si l'index ne couvre pas entièrement toutes les colonnes sélectionnées. De plus, comme l'index est non clusterisé, toutes les colonnes au-delà de OrderDate, OrderStatus et de la clé clusterisée OrderID nécessiteraient une recherche, ce qui l'empêche d'être un index véritablement couvrant pour la requête.

C

Fausse

Créer un index clusterisé sur OrderID et un index non clusterisé sur (OrderStatus, OrderDate)

Cette option pose problème car l'index non clusterisé est trié par OrderStatus en premier, puis par OrderDate, ce qui signifie que la colonne principale OrderStatus n'aide pas à affiner les résultats pour un prédicat sur OrderDate seul. L'index clusterisé sur OrderID est inchangé, il ne prend donc pas en charge les exigences de tri ou de filtrage de la requête ; le moteur de requête devrait parcourir une grande partie de l'index et effectuer une opération de tri séparée. De plus, l'absence de DESC sur OrderDate signifie que l'index ne peut pas renvoyer les lignes dans l'ordre requis sans traitement supplémentaire, ce qui en fait un mauvais choix pour la requête spécifique.

D

Juste

Créer un index non clusterisé sur (OrderDate DESC, OrderStatus) et conserver l'index clusterisé existant sur OrderID

C'est correct car l'index non clusterisé utilise OrderDate comme colonne principale avec DESC, ce qui correspond exactement à l'exigence ORDER BY OrderDate DESC et permet au moteur de requête de lire les lignes dans le bon ordre sans tri. L'inclusion de OrderStatus comme deuxième colonne de clé permet à l'index de gérer efficacement le filtrage sur les deux colonnes, et comme OrderID est la clé clusterisée, elle est automatiquement ajoutée aux entrées de l'index non clusterisé, ce qui rend l'index couvrant pour une requête sélectionnant OrderID, OrderDate et OrderStatus. Conserver l'index clusterisé existant sur OrderID préserve l'unicité de la clé primaire et évite une réorganisation physique inutile de la table, de sorte que cette conception équilibre les performances de la requête avec un impact minimal sur les autres opérations.

Pour aller plus loin

Le concept fondamental ici est la manière dont SQL Server utilise l'ordre des colonnes de clé d'index et la direction du tri pour satisfaire à la fois les exigences de filtrage et de tri à partir d'un seul parcours structuré. Lorsqu'une requête a une clause WHERE sur OrderDate et un ORDER BY OrderDate DESC, un index avec OrderDate comme clé principale et un ordre de tri DESC permet à l'optimiseur d'effectuer un parcours ou une recherche d'index ordonné, éliminant le besoin d'un opérateur de tri séparé, ce qui représente un avantage significatif pour les grands nombres de lignes. La subtilité réside dans le fait qu'un index non clusterisé inclut automatiquement la clé de l'index clusterisé comme localisateur de ligne, donc si la clé clusterisée est OrderID, un index sur (OrderDate, OrderStatus) peut couvrir les requêtes qui ne demandent que ces trois colonnes. Cependant, si l'ordre des clés d'index est (OrderStatus, OrderDate), l'optimiseur ne peut généralement pas utiliser l'index pour une plage sur OrderDate, car la colonne principale sans égalité empêche un accès ordonné efficace. De plus, de nombreux développeurs supposent que la direction du tri de l'index n'a pas d'importance car SQL Server peut parcourir vers l'arrière, mais les parcours vers l'arrière sont moins efficaces et peuvent ne pas être choisis pour des ordres de tri mixtes ; faire correspondre explicitement l'ordre DESC est la conception la plus sûre. Dans les charges de travail OLTP du monde réel, la création du bon index couvrant pour une requête fréquente peut réduire la pression sur le CPU, les E/S et la mémoire, mais chaque index supplémentaire ajoute un coût de maintenance lors des insertions et des mises à jour, le compromis doit donc être évalué.

À propos de ces questions d'entraînement

Cette question fait partie des questions d'entraînement DP-900 originales de Courseiva, rédigées pour apprendre et jamais copiées d'examens réels ou de dumps.

Traduite de l'original anglais avec l'IA et vérifiée pour le sens. Les codes d'examen, les commandes et les noms de produits sont conservés tels qu'ils apparaissent à l'examen.