Primaire composite et cardinalité

Question

J'ai quelques questions sur les clés primaires composites et la cardinalité des colonnes. J'ai fait des recherches sur le web, mais je n'ai pas trouvé de réponse définitive, alors j'essaie à nouveau. Les questions sont les suivantes :

Le contexte : Tables OLAP préparées de grande taille (50 à 500 millions de lignes), non NOSQL, non Columnar. MySQL et DB2

1) L'ordre des clés dans un PK est-il important ?

2) Si la cardinalité des colonnes varie fortement, laquelle doit être utilisée en premier. Par exemple, si j'ai CLIENT/CAMPAIGN/PROGRAMME où CLIENT est fortement cardinal, CAMPAIGN est modéré, PROGRAMME est presque comme un index bitmap, quel est le meilleur ordre ?

3) Quel est le meilleur ordre pour Join, s'il y a une clause Where et lorsqu'il n'y a pas de clause Where (pour les vues).

Merci d'avance.

Answer 1

Vous avez "MySQL et DB2". Cette réponse est pour DB2, MySQL n'a rien de tout cela.

Oui, bien sûr, c'est logique, mais l'optimiseur prend en compte bien plus que cela.

En général, l'ordre des colonnes dans la clause WHERE (jointure) n'ont pas (et ne devraient pas avoir) d'importance.

Cependant, il existe deux éléments liés à l'ordre des prédicats qui peuvent être à l'origine de votre question.

1. Ce qui compte, c'est l'ordre des colonnes. dans l'index par rapport à laquelle la clause WHERE est traitée. Oui, dans ce cas, il est préférable de spécifier les colonnes dans l'ordre de la plus grande cardinalité à la plus petite. Cela permet à l'optimiseur de cibler un plus petit nombre de lignes.

   • Et dans le même ordre d'idées, ne prenez pas la peine d'implémenter des indices pour les colonnes à faible cardinalité et à colonne unique (il n'y en a pas). Si l'index est correct, alors il sera utilisé plus souvent.
     .

2. L'ordre de tables jointes (pas les colonnes dans la jointure) a beaucoup d'importance, c'est probablement la considération la plus importante. En fait, la fermeture transitive de la jointure est automatique, et l'optimiseur évalue tous les ordres de jointure possibles, et choisit ce qu'il pense être le meilleur, sur la base des statistiques (c'est pourquoi UPDATE STATS est si important).

   Indépendamment du nombre de lignes dans les tables, si vous joignez 100 lignes de la table_A sur un mauvais index à 1 000 000 de lignes de la table_B sur un bon index, vous voulez l'ordre A:B, et non B:A. Si vous obtenez moins que le maximum d'IOPS, vous pouvez y remédier.

   La séquence correcte des étapes est, sans surprise :

   • vérifier que l'indice est correct selon (1). N'ajoutez pas simplement un autre index, corrigez ceux que vous avez.

   • vérifier que les statistiques de mise à jour sont exécutées régulièrement

   • essayez toujours d'abord le fonctionnement par défaut de l'optimiseur. Activez les stats et mesurez les E/S. Utilisez des ensembles de valeurs représentatifs (que l'utilisateur utilisera en production).

   • vérifiez le plan d'aménagement, pour vous assurer que le code est correct. Bien entendu, cela permettra également d'identifier l'ordre d'assemblage choisi.

   • si les performances ne sont pas assez bonnes, et que vous pensez que l'ordre de jointure choisi par l'optimisateur pour ces fixe des valeurs n'est pas optimale, mettez JTC OFF (la syntaxe dépend de votre version de DB2), puis spécifiez l'ordre que vous souhaitez dans la clause WHERE. Mesurez les E/S. Utilisez des ensembles représentatifs

   • se faire une opinion. Choisissez celui qui offre les meilleures performances globales. N'effectuez jamais de réglages pour des requêtes uniques.

Answer 2

1) L'ordre des clés dans un PK est-il important ?

Oui, cela change l'ordre de l'enregistrement pour l'index qui est utilisé pour la police de la PRIMARY KEY .

2) Si la cardinalité des colonnes varie fortement, laquelle doit être utilisée en premier. Par exemple, si j'ai CLIENT/CAMPAIGN/PROGRAMME où CLIENT est fortement cardinal, CAMPAIGN est modéré, PROGRAMME est presque comme un index bitmap, quel est le meilleur ordre ?

Pour les requêtes sélectives, cela dépend totalement des requêtes que vous allez utiliser. Si vous recherchez les trois colonnes en même temps, l'ordre n'est pas important ; si vous recherchez deux ou une colonne, elles doivent être en tête de l'index.

Pour les insertions, il est préférable que la colonne de tête corresponde à l'ordre dans lequel les enregistrements sont insérés.

3) Quel est le meilleur ordre pour Join, s'il y a une clause Where et lorsqu'il n'y a pas de clause Where (pour les vues).

Là encore, cela dépend de la WHERE clause.