Quand et pourquoi les bases de données sont-elles coûteuses?

je fais quelques recherches dans les bases de données et je suis à la recherche de certaines limites de DBS relationnelles.

j'obtiens que jointures de grandes tables est très cher, mais je ne suis pas complètement sûr pourquoi. Que doit faire le SGBD pour exécuter une opération de jointure, où est le goulot d'étranglement?

Comment la dénormalisation peut-elle aider à surmonter cette dépense? Comment d'autres techniques d'optimisation (indexation, par exemple) peuvent-elles aider?

personnel les expériences sont les bienvenus! Si vous allez poster des liens vers des ressources, s'il vous plaît éviter Wikipedia. Je sais où les trouver déjà.

par rapport à cela, je m'interroge sur les approches dénormalisées utilisées par les bases de données de services cloud comme BigTable et SimpleDB. Voir cette question .

316
demandé sur Community 2008-10-06 13:52:19
la source

7 ответов

dénormaliser pour améliorer les performances? Ça a l'air convaincant, mais ça ne tient pas debout.

Chris Date, qui en compagnie du Dr Ted Codd était le promoteur original du modèle de données relationnelles, a manqué de patience avec des arguments mal informés contre la normalisation et systématiquement démoli en utilisant la méthode scientifique: il a obtenu de grandes bases de données et testé ces assertions.

je pense qu'il l'a écrit dans Base de données Relationnelle Écrits 1988-1991 mais ce livre a été plus tard roulé dans six édition de Introduction aux Systèmes de Base de données , qui est le texte définitif sur la base de la théorie et de la conception, dans sa huitième édition que je vous écris, et susceptibles de rester dans l'impression pour les décennies à venir. Chris Date était un expert dans ce domaine alors que la plupart d'entre nous couraient encore pieds nus.

il a trouvé que:

  • certains d'entre eux tiennent pour des cas spéciaux
  • tous ne paient pas pour un usage général
  • Tous sont bien pires pour les autres cas spéciaux

tout revient à atténuer la taille de l'ensemble de travail. Les jointures comportant des touches correctement sélectionnées avec des index correctement configurés sont bon marché, pas cher, parce qu'elles permettent un élagage significatif du résultat avant les lignes sont matérialisées.

la matérialisation du résultat implique des lectures de disque en vrac qui sont l'aspect le plus coûteux de l'exercice d'un ordre de grandeur. L'exécution d'une jointure, par contre, nécessite logiquement la récupération des seules clés 151970920". En pratique, même les valeurs clés ne sont pas récupérées: les valeurs de hachage des clés sont utilisées pour les comparaisons de jointures, ce qui atténue le coût des jointures à plusieurs colonnes et réduit radicalement le coût des jointures impliquant comparaison des chaînes. Non seulement il y aura beaucoup plus de place dans le cache, mais il y a beaucoup moins de lecture de disque à faire.

de plus, un bon optimiseur choisira la condition la plus restrictive et l'appliquera avant d'effectuer une jointure, tirant très efficacement parti de la haute sélectivité des jointures sur les index à haute cardinalité.

Certes, ce type d'optimisation peut aussi s'appliquer à des bases de données dénormalisées, mais le genre de personnes qui veulent pour dénormaliser un schéma ne pensez généralement pas à la cardinalité quand (si) ils mettent en place des index.

il est important de comprendre que les balayages de table (l'examen de chaque ligne dans la table dans le cours de la production de la jointure) sont rares dans la pratique. Un optimiseur de requête choisira un scan de table seulement quand un ou plusieurs des suivants tient.

  • il y a moins de 200 lignes dans la relation (dans ce cas un scan sera moins cher)
  • il n'y a pas d'index appropriés sur les colonnes de jointure (s'il est significatif de se joindre sur ces colonnes, pourquoi ne sont-ils pas indexés? fix it)
  • Un type de contrainte est nécessaire avant que les colonnes peuvent être comparées (WTF?! fixez-le ou rentrez chez vous) Voir notes de fin de chapitre pour ADO.NET numéro
  • un des arguments de la comparaison est une expression (pas d'index)

effectuer une opération est plus cher que de ne pas l'effectuer. Cependant, effectuer l'opération wrong , étant forcé dans l'entrée / sortie disque inutile et ensuite jeter les scories avant d'effectuer la jointure dont vous avez vraiment besoin, est beaucoup plus cher. Même lorsque la" mauvaise " opération est prédéfinie et que les indices ont été raisonnablement appliqués, il reste une pénalité importante. Dénormaliser pour précalculer une jointure-nonobstant les anomalies de mise à jour entraînées - est un engagement à un particulièrement jointure. Si vous avez besoin d'un différent joindre, cet engagement va vous coûter grand .

si quelqu'un veut me rappeler que c'est un monde qui change, je pense que vous trouverez que de plus gros ensembles de données sur gruntier hardware exagère simplement la diffusion des résultats de Date.

pour tous ceux d'entre vous qui travaillent sur des systèmes de facturation ou des générateurs de courrier indésirable (honte à vous) et sont indignés mettant la main au clavier pour dire moi que vous savez pour un fait que la dénormalisation est plus rapide, désolé, mais vous vivez dans l'un des cas spéciaux, à savoir le cas où vous traitez tous des données, dans l'ordre. Ce n'est pas un cas général, et vous êtes justifié dans votre stratégie.

Vous êtes pas justifié tort de généraliser. Voir la fin de la section notes pour plus d'informations sur l'utilisation appropriée de la dénormalisation dans scénarios d'entreposage de données.

j'aimerais aussi répondre à

Jointures sont juste les produits cartésiens avec quelques lipgloss

quelles conneries. Les Restrictions sont appliquées le plus tôt possible, les plus restrictives d'abord. Vous avez lu la théorie, mais vous n'avez pas compris. Les jointures sont traitées comme" produits cartésiens auxquels s'appliquent les prédicats " seulement par l'optimiseur de requête. Il s'agit d'une représentation symbolique (une normalisation, en fait) pour faciliter la décomposition symbolique afin que l'optimiseur puisse produire toutes les transformations équivalentes et les classer par coût et sélectivité afin qu'il puisse sélectionner le meilleur plan d'interrogation.

la seule façon d'obtenir l'optimiseur pour produire un produit cartésien est de ne pas fournir un prédicat: SELECT * FROM A,B


Notes


David Aldridge fournit des renseignements supplémentaires importants.

il existe en effet une variété d'autres stratégies que les index et les scans de table, et un optimiseur moderne leur coûtera tout avant de produire un plan d'exécution.

un conseil pratique: si elle peut être utilisée comme clé étrangère, alors indexez-la, de sorte qu'une stratégie d'index est disponible à l'optimiseur.

j'étais plus intelligent que l'optimiseur MSSQL. Ça a changé il y a deux versions. Maintenant, il enseigne généralement me . Il s'agit, dans un sens très réel, d'un système expert, codifiant toute la sagesse de beaucoup de gens très intelligents dans un domaine suffisamment fermé pour qu'un système fondé sur des règles soit efficace.


"Bollocks" peut avoir été sans tact. On me demande d'être moins hautain et on me rappelle que les maths ne mentent pas. C'est vrai, mais pas tous les implications des modèles mathématiques doivent nécessairement être pris à la lettre. Les racines carrées des nombres négatifs sont très pratiques si vous évitez soigneusement d'examiner leur absurdité (jeu de mots là) et assurez-vous damnement que vous les annulez tous avant d'essayer d'interpréter votre équation.

la raison pour laquelle j'ai répondu si sauvagement était que la déclaration telle qu'elle est libellée dit que

Jointures sont les produits cartésiens...

ce n'est peut-être pas ce que l'on voulait dire, mais est ce qui a été écrit, et c'est catégoriquement faux. Un produit cartésien est une relation. Une jointure est une fonction. Plus précisément, une jointure est une fonction liée à la valeur. Avec un prédicat vide il produira un produit cartésien, et vérifier qu'il le fait est une vérification d'exactitude pour un moteur de requête de base de données, mais personne n'écrit des jointures libres dans la pratique parce qu'ils n'ont pas pratique valeur à l'extérieur d'une salle de classe.

j'ai appelé cela parce que je ne veux pas que les lecteurs tombent dans le piège antique de confondre le modèle avec la chose modélisée. Un modèle est une approximation, délibérément simplifiée pour faciliter la manipulation.


la coupure pour la sélection d'une stratégie de jointure table-scan peut varier entre les moteurs de la base de données. Il est affecté par un certain nombre de décisions de mise en œuvre telles que l'arbre-noeud fill-facteur, taille de la valeur clé et subtilités de l'algorithme, mais en gros, l'indexation à haute performance a un temps d'exécution de k log n + c . Le terme C est un overhead fixe principalement fait de temps d'installation, et la forme de la courbe signifie que vous n'obtenez pas un gain (par rapport à une recherche linéaire) jusqu'à ce que n soit par centaines.


parfois la dénormalisation est une bonne idea

la dénormalisation est un engagement envers une stratégie commune particulière. Comme mentionné précédemment, cela interfère avec autres stratégies de jointure. Mais si vous avez des seaux d'espace disque, des modèles prévisibles d'accès, et une tendance à traiter une grande partie ou la totalité de celui-ci, alors précomputer une jointure peut être très utile.

vous pouvez également calculer les chemins d'accès que votre opération utilise généralement et précalculer toutes les jointures pour ces accès chemin. C'est la prémisse derrière les entrepôts de données, ou du moins c'est quand ils sont construits par des gens qui savent pourquoi ils font ce qu'ils font, et pas seulement pour le respect des mots à la mode.

un entrepôt de données correctement conçu est produit périodiquement par une transformation en masse à partir d'un système normalisé de traitement des transactions. Cette séparation des bases de données opérationnelles et de rapports a l'effet très souhaitable d'éliminer l'affrontement entre OLTP et OLAP (traitement des transactions en ligne ie l'entrée de données et de traitement analytique en ligne ie reporting).

un point important ici est qu'en dehors des mises à jour périodiques, l'entrepôt de données est lire seulement . Cela rend sans objet la question des anomalies de mise à jour.

ne faites pas l'erreur de dénormaliser votre base de données OLTP (la base de données sur laquelle la saisie de données se produit). Il pourrait être plus rapide pour les courses de facturation, mais si vous faites que vous obtiendrez mettre à jour les anomalies. T'as déjà essayé de faire en sorte que Reader's Digest arrête de t'envoyer des trucs?

L'espace disque est bon marché ces jours-ci, alors assommez-vous. Mais la dénormalisation n'est qu'une partie de l'histoire des entrepôts de données. Des gains de performance beaucoup plus importants sont obtenus à partir de valeurs cumulées pré-calculées: les totaux mensuels, ce genre de chose. C'est toujours sur la réduction de l'ensemble de travail.


ADO.NET problème de type inadéquations

supposons que vous ayez une table SQL Server contenant une colonne indexée de type varchar, et que vous utilisiez AddWithValue pour passer un paramètre contraignant une requête sur cette colonne. C # strings sont Unicode, donc le type de paramètre inféré sera NVARCHAR, qui ne correspond pas à VARCHAR.

VARCHAR à NVARCHAR est une conversion de plus en plus large donc il se produit implicitement - mais dire au revoir à l'indexation, et bonne chance de trouver pourquoi.


"Compter le disque hits" (Rick James)

si tout est caché en RAM, JOINs sont plutôt bon marché. C'est-à-dire, la normalisation n'a pas beaucoup pénalité de performance .

si un schéma" normalisé "provoque JOINs à frapper le disque beaucoup, mais l'équivalent du schéma" dénormalisé " n'aurait pas à frapper le disque, alors la dénormalisation gagne une compétition de performance.

commentaire de l'auteur original: Les moteurs de bases de données modernes sont très efficaces pour organiser le séquençage des accès afin de minimiser les erreurs de cache pendant les opérations de jointure. Ce qui précède, s'il est vrai, pourrait être mal interprété en ce sens que les jointures sont nécessairement coûteuses sur la base de données volumineuses. Cela conduirait à l'origine d'une mauvaise décision de la part des développeurs inexpérimentés.

428
répondu Peter Wone 2016-01-28 06:17:14
la source

ce que la plupart des commentateurs ne remarquent pas, c'est le large éventail de méthodologies de jointure disponibles dans un RDBMS complexe, et les dénormaliseurs passent invariablement sous silence le coût plus élevé de la maintenance des données dénormalisées. Toutes les jointures ne sont pas basées sur des index, et les bases de données ont beaucoup d'algotithmes optimisés et de méthodologies pour les jointures qui sont destinées à réduire les coûts de jointure.

Dans tous les cas, le coût d'une jointure dépend de son type et de quelques autres facteurs. Il n'a pas besoin d'être chère tout quelques exemples.

  • une jointure de hachage, dans laquelle les données en vrac est équijoiné, est très bon marché en effet, et le coût ne devient significatif si la table de hachage ne peut pas être mise en cache dans la mémoire. Aucun index n'est requis. L'équipartition entre les ensembles de données joints peut être d'une grande aide.
  • le coût d'une jonction tri-Fusion est déterminé par le coût du tri plutôt que par la fusion -- une méthode d'accès basée sur un index peut pratiquement éliminer le coût du tri.
  • le coût d'une boucle emboîtée sur un index est déterminé par la hauteur de l'arbre b et l'accès au bloc de table lui-même. C'est rapide, mais ne convient pas pour les gros joint.
  • une jointure de boucle emboîtée basée sur un cluster est beaucoup moins cher, avec moins d'IO logiques requis par ligne de jointure -- si les tables jointes sont toutes les deux dans le même cluster alors la jointure devient très bon marché par la colocation de lignes jointes.

Les bases de données sont conçues pour se joindre, et ils sont très flexibles dans la façon dont ils le font et généralement très performant à moins qu'ils obtiennent le mécanisme de jointure mal.

42
répondu David Aldridge 2008-10-06 17:00:19
la source

je pense que toute la question est basée sur une fausse prémisse. Les joints sur les grandes tables sont pas nécessairement coûteux. En fait, faire des jointures efficacement est l'une des principales raisons pour lesquelles les bases de données relationnelles existent du tout. Se joint sur les grands ensembles souvent sont coûteux, mais très rarement voulez-vous joindre l'ensemble du contenu de la grande table A avec l'ensemble du contenu de la grande table B. Au Lieu de cela, vous écrivez la requête telle que seules les lignes importantes de chaque table sont utilisées et l'ensemble réel conservé par la jointure reste plus petit.

de plus, vous avez les efficacités mentionnées par Peter Wone, de sorte que seules les parties importantes de chaque enregistrement doivent être en mémoire jusqu'à ce que le résultat final soit matérialisé. En outre, dans les grandes requêtes avec beaucoup de jointures vous voulez généralement commencer avec les ensembles de table plus petits et travailler votre chemin jusqu'aux grands, de sorte que l'ensemble conservé dans la mémoire reste aussi faible que possible aussi longtemps que possible.

lorsqu'elles sont faites correctement, les jointures sont généralement la meilleure façon pour comparer, combiner ou filtrer sur de grandes quantités de données.

25
répondu Joel Coehoorn 2008-10-06 20:45:06
la source

le goulot d'étranglement est à peu près toujours I/O disque, et encore plus spécifiquement - random disk I/O (par comparaison, les lectures séquentielles sont assez rapides et peuvent être mises en cache avec des stratégies de lecture à l'avance).

Joins can augmenter recherche aléatoire-si vous sautez autour de la lecture de petites parties d'une grande table. Mais, les optimiseurs de requête cherchent cela et le transformeront en scan séquentiel de table (en rejetant les lignes inutiles) s'il pense que ce serait mieux.

une seule table dénormalisée a un problème similaire - les lignes sont grandes, et donc moins d'ajustement sur une seule page de données. Si vous avez besoin de rangées qui sont situées loin d'une autre (et la taille de la rangée les rend plus éloignés) alors vous aurez plus d'I/O aléatoire encore, un scan de table peut être forcé d'éviter cela. Mais, cette fois, votre table scan doit lire plus de données en raison de la Grande Taille de la rangée. Ajoutez à cela le fait que vous êtes copier des données d'un seul endroit à plusieurs endroits, et le RDBMS a beaucoup plus à lire (et à mettre en cache).

avec 2 tables, vous obtenez également 2 index groupés - et peut généralement indexer plus (en raison de moins d'insertion/mise à jour de frais généraux) qui peut vous obtenir des performances drastiquement accrues (principalement, encore une fois, parce que les index sont (relativement) petits, rapide à lire hors disque (ou pas cher à mettre en cache), et réduire la quantité de lignes de table que vous devez lire à partir du disque).

à propos du seul au-dessus avec une jointure vient de calculer les lignes correspondantes. Sql Server utilise 3 types différents de jointures, principalement basés sur la taille des ensembles de données, pour trouver des lignes correspondantes. Si l'optimiseur choisit le mauvais type de jointure (dû à des statistiques inexactes, des index inadéquats, ou juste un bug d'optimiseur ou un cas de bord) il peut affecter radicalement les temps de requête.

  • une jointure de boucle est peu coûteuse pour (au moins 1) petit ensemble de données.
  • A jointure de fusion nécessite une sorte de deux ensembles de données en premier. Si vous vous inscrivez sur une colonne indexée, alors l'index est déjà trié et aucun autre travail ne doit être fait. Dans le cas contraire, il y a du CPU et de la mémoire dans le tri.
  • la jointure de hachage nécessite à la fois de la mémoire (pour stocker le hachtable) et du processeur (pour construire le hachage). Encore une fois , c'est assez rapide par rapport au disque I/O. cependant , s'il n'y a pas assez de RAM pour stocker le hashtable, Sql Server utilise tempdb pour stocker des parties du hashtable et des lignes trouvées, puis ne traite que des parties du hashtable à la fois. Comme pour tout disque, c'est assez lent.

dans le cas optimal, ceux - ci ne causent pas d'E/S disque et sont donc négligeables du point de vue de la performance.

tout dans l'ensemble, au pire - il devrait en fait être plus rapide de lire la même quantité de logique données de x tableaux joints, comme il est d'un la table dénormalisée simple à cause des lectures plus petites de disque. Pour lire la même quantité de données" physiques , il pourrait y avoir un léger au-dessus.

puisque le temps de requête est habituellement dominé par les coûts D'E/S, et la taille de vos données ne change pas (moins certains très minuscules frais généraux de ligne) avec la dénormalisation, il n'y a pas une quantité énorme d'avantage à avoir en fusionnant juste les tables ensemble. Le type de dénormalisation qui tend à augmenter performance, IME, cache les valeurs calculées au lieu de lire les 10 000 lignes nécessaires pour les calculer.

10
répondu Mark Brackett 2008-11-04 02:33:32
la source

l'ordre dans lequel vous rejoignez les tables est extrêmement important. Si vous avez deux ensembles de données, essayez de construire la requête de la façon la plus petite sera tout d'abord utilisé pour réduire la quantité de données de la requête pour travailler sur.

pour certaines bases de données, cela n'a pas d'importance, par exemple MS SQL connaît l'ordre de jointure approprié la plupart du temps. Pour certains (comme IBM Informix) l'ordre fait toute la différence.

4
répondu Ilya Kochetov 2008-10-06 13:58:51
la source

décider s'il faut dénormaliser ou normaliser est un processus assez simple quand on considère la classe de complexité de la jointure. Par exemple, j'ai tendance à concevoir mes bases de données avec normalisation lorsque les requêtes sont O(K log n) où k est relatif à la grandeur de sortie désirée.

un moyen facile de dénormaliser et d'optimiser les performances est de penser à la façon dont les changements à votre structure de normalisation affectent votre structure dénormalisée. Il peut être problématique toutefois, comme il peut exiger la logique transactionnelle de travailler sur une structure dénormalisée.

le débat pour la normalisation et la dénormalisation ne va pas s'arrêter puisque les problèmes sont vastes. Il y a beaucoup de problèmes où la solution naturelle nécessite les deux approches.

en règle générale, j'ai toujours stocké une structure normalisée et des caches dénormalisées qui peuvent être reconstruites. Finalement, ces caches me sauvent le cul pour résoudre la future normalisation problème.

0
répondu MathGladiator 2009-09-20 16:25:28
la source

l'Élaboration de ce que les autres ont dit,

jointures sont juste des produits cartésiens avec un peu de lipgloss. {1,2,3,4}X{1,2,3} donne 12 combinaisons (nXn=n^2). Cet ensemble reconstitué Sert de référence pour l'application des conditions. Le SGBD applique les conditions(comme lorsque la gauche et la droite sont 2 ou 3) pour nous donner la ou les conditions correspondantes. En fait, il est plus optimisé, mais le problème est le même. Les changements dans la taille des ensembles augmenteraient la taille du résultat exponentiel. La quantité de mémoire et les cycles cpu consommés sont tous affectés en termes exponentiels.

quand nous dénormalisons, nous évitons complètement ce calcul, pensez à avoir un autocollant coloré, attaché à chaque page de votre livre. Vous pouvez déduire l'information sans utiliser une référence. La pénalité que nous payons est que nous compromettons l'essence du SGBD (organisation optimale des données)

-6
répondu questzen 2008-10-06 15:09:55
la source