Combien d'index de base de données est trop?

d'habitude, je fais comme ça.

1. obtenez un journal des requêtes réelles exécutées sur les données un jour typique.
2. ajouter des index de sorte que les requêtes les plus importantes frappent les index dans leur plan d'exécution.
3. essayez d'éviter d'indexer des champs qui ont beaucoup de mises à jour ou inserts
4. après quelques index, obtenir un nouveau log et répéter.

comme avec toute optimisation, Je m'arrête lorsque la performance demandée est atteinte (cela implique évidemment que le point 0. serait exigences spécifiques de performance).

tout le monde vous a donné de bons conseils. J'ai une suggestion à vous faire à mesure que vous avancez. À un moment donné, vous devez prendre une décision quant à votre meilleure stratégie d'indexation. En fin de compte, cependant, la meilleure stratégie D'indexation planifiée peut quand même finir par créer des index qui ne finissent pas par être utilisés. Une stratégie qui vous permet de trouver des index qui ne sont pas utilisés est de surveiller l'utilisation de l'index. Vous faites ceci comme suit: -

alter index my_index_name monitoring usage;

vous pouvez alors contrôler si le index est utilisé ou non à partir de ce point en interrogeant V$object_usage. Vous trouverez des informations à ce sujet dans le Oracle® Database Administrator's Guide .

rappelez-vous juste que si vous avez une stratégie d'entreposage de laisser tomber des index avant de mettre à jour une table, puis de les recréer, vous devrez mettre l'index en place pour le contrôle à nouveau, et vous perdrez tout historique de contrôle pour cet index.

Dans l'entreposage de données, il est très fréquent d'avoir un nombre élevé d'index. J'ai travaillé avec des tables de faits ayant deux cents colonnes et 190 indexés.

bien qu'il y ait des frais généraux à cela, il doit être compris dans le contexte que dans un entrepôt de données, nous n'insérons généralement qu'une seule ligne, nous ne la mettons jamais à jour, mais il peut alors participer à des milliers de requêtes sélectionnées qui pourraient bénéficier de l'indexation sur l'une des colonnes.

Pour une flexibilité maximale, un entrepôt de données utilise généralement des index bitmap à une seule colonne, sauf sur les colonnes à haute cardinalité, où les index btree (comprimés) peuvent être utilisés.

les frais généraux d'entretien de l'index sont principalement associés aux frais d'écriture d'un grand nombre de blocs et les blocs se divisent à mesure que de nouvelles lignes sont ajoutées avec des valeurs qui sont" au milieu " des gammes de valeurs existantes pour cette colonne. Cette situation peut être atténuée par le partitionnement et l'alignement des nouvelles charges de données. avec le schéma de partitionnement, et en utilisant des inserts de chemin direct.

pour répondre à votre question plus directement, je pense qu'il est probablement bien d'indexer l'évident au début, mais ne craignez pas d'ajouter plus d'indices sur si les requêtes contre la table bénéficieraient.

Dans une paraphrase de Einstein sur la simplicité, ajouter autant d'index que vous avez besoin et rien de plus.

sérieusement, cependant, chaque index que vous ajoutez nécessite une maintenance chaque fois que des données sont ajoutées au tableau. Sur les tableaux qui sont principalement lus seulement, beaucoup d'index sont une bonne chose. Sur des tables qui sont très dynamiques, moins il y en a, c'est mieux.

mon conseil est de couvrir les cas communs et évidents et puis, comme vous rencontrez des problèmes lorsque vous avez besoin de plus de vitesse pour obtenir des données à partir de tableaux spécifiques, évaluer et ajouter des indices à ce point.

aussi, c'est une bonne idée de réévaluer vos schémas d'indexation tous les quelques mois, juste pour voir s'il y a quelque chose de nouveau qui a besoin d'indexation ou des indices que vous avez créé qui ne sont pas utilisés pour quoi que ce soit et devraient être éliminés.

en plus des points que tout le monde a soulevés, L'Optimiseur basé sur les coûts encourt un coût lors de la création d'un plan pour un énoncé SQL s'il y a plus d'indices parce qu'il ya plus de combinaisons à considérer. Vous pouvez réduire cela en utilisant correctement les variables bind pour que les instructions SQL restent dans le cache SQL. Oracle peut alors faire une analyse douce et réutiliser le plan qu'il a trouvé la dernière fois.

Comme toujours, rien n'est simple. S'il y a des colonnes asymétriques et histogrammes impliqués alors cela peut être une mauvaise idée.

dans nos applications web, nous avons tendance à limiter les combinaisons de recherches que nous autorisons. Sinon, vous devriez tester littéralement chaque combinaison pour la performance pour s'assurer que vous n'avez pas eu un problème caché que quelqu'un trouvera un jour. Nous avons également mis en place des limites de ressources afin de mettre fin aux problèmes qui pourraient se poser ailleurs dans la demande si quelque chose ne va pas.

j'ai fait quelques tests simples sur mon projet réel et la base de données réelle MySql. J'ai déjà répondu dans ce thème: Quel est le coût de l'indexation de plusieurs colonnes de base de données?

mais je pense que ce sera mieux si je le cite ici:

j'ai fait quelques tests simples en utilisant mon réel projet et véritable base de données MySql.

mes résultats sont: Ajouter l'indice moyen (1-3 colonnes dans un index) à un tableau - rend les inserts plus lents de 2,1%. Donc, si vous ajoutez 20 Index, vos inserts être plus lent par 40 à 50%. Mais votre sélectionne sera 10 à 100 fois plus rapide.

alors est-il correct d'ajouter de nombreux index? - Il dépend :) je vous ai donné mes résultats - Vous décider!

finalement combien d'index vous avez besoin dépendent du comportement de vos applications qui roulent sur le dessus de votre serveur de base de données.

en général, plus vous insérez, plus vos index sont douloureux. Chaque fois que vous faites un insert, tous les index qui incluent cette table doivent être mis à jour.

maintenant si votre application a une bonne quantité de lecture, ou encore plus si c'est presque toute la lecture, puis les index sont le chemin à suivre comme il y aura être majeur des améliorations de performances pour très peu de frais.

il n'y a pas de réponse statique à mon avis, ce genre de chose tombe sous "performance tuning".

il se peut que tout ce que fait votre application soit recherché par une clé primaire, ou cela pourrait être l'option en ce que les requêtes sont effectuées sur des combinaisons non enregistrées de champs et n'importe lequel en particulier pourrait être utilisé à tout moment.

au-delà de la simple indexation, il y a la réorganisation de votre base de données pour inclure des champs de recherche calculés, des tables de fractionnement, etc - il est vraiment dépendant de vos formes de chargement et paramètres de requête, combien/quelles données "vraiment" a besoin d'être reconstruit par une requête.

si la totalité de votre base de données est frontée par des façades stockées-procédure de tournage devient un peu plus facile, que vous n'avez pas à se soucier de chaque requête ad-hoc. Ou vous pouvez avoir une compréhension profonde du genre de requêtes qui vont frapper votre base de données, et peut limiter l'accord à ceux.

pour SQL Server j'ai trouvé le Conseiller de mise au point de moteur de base de données utile - vous mettez en place des charges de travail "typiques" et il peut faire des recommandations sur l'Ajout/Suppression des index et des statistiques. Je suis sûr que d'autres DBs ont des outils similaires, soit "officiels" ou tiers.

c'est vraiment une question plus théorique que pratique. L'impact des index sur vos performances dépend du matériel que vous avez, de la version D'Oracle, des types d'index, etc. Hier J'ai entendu Oracle a annoncé un stockage dédié, fait par HP, qui est censé effectuer 10 fois plus rapide avec base de données 11g. Pour votre cas, il peut y avoir plusieurs solutions: 1. Avoir une grande quantité d'indices (>20) et les reconstruire quotidiennement (nuit). Cela serait particulièrement utile si la table obtient des milliers de mises à jour / suppressions quotidiennes. 2. Partition de votre table (si cela s'applique à votre modèle de données). 3. Utilisez un tableau séparé pour les données nouvelles/mises à jour, et exécutez un processus de nuit qui combine les données ensemble. Cela nécessiterait un changement dans la logique de l'application. 4. Passez à IOT (index organized table), si vos données le supportent.

bien sûr, il pourrait y avoir beaucoup plus de solutions pour un tel cas. Ma première suggestion à vous, serait de cloner le DB à un environnement de développement, et exécuter certains tests de stress contre elle.

si vous faites la plupart des lectures (et quelques mises à jour) alors il n'y a vraiment aucune raison de ne pas indexer tout ce que vous aurez besoin d'indexer. Si vous mettez à jour souvent, alors vous pouvez avoir besoin d'être prudent sur le nombre d'index que vous avez. Il n'y a pas de numéro, mais vous remarquerez quand les choses commencent à ralentir. Assurez-vous que votre index regroupé est celui qui est le plus logique selon les données.

une chose que vous pouvez considérer est de construire des index pour cibler une combinaison standard de recherches. Si colonne1 est souvent recherchés, et colonne2 est souvent utilisé avec elle, et colonne3 est parfois utilisé avec colonne1 colonne2 et, ensuite, d'un index sur colonne1, colonne2, et colonne3 dans cet ordre peut être utilisé pour n'importe quel de ces trois circonstances, si ce n'est qu'un indice qui doit être maintenu.

un indice impose un coût lorsque la table sous-jacente est mise à jour. Un index fournit un avantage lorsqu'il est utilisé pour spped une requête. Pour chaque indice, vous devez équilibrer le coût par rapport à l'avantage. Combien ralentissement de l'exécution de la requête sans l'index? Quelle part d'une prestation est plus rapide? Pouvez-vous ou vos utilisateurs tolérer la vitesse lente lorsque l'index est manquant?

pouvez-vous tolérer le temps supplémentaire qu'il faut pour effectuer une mise à jour?

Vous devez comparer les coûts et les avantages. C'est particulier à votre situation. Il n'y a pas de Nombre Magique d'indices qui dépasse le seuil du "trop".

il y a aussi le coût de l'espace nécessaire pour stocker l'indice, mais vous avez dit que dans votre situation ce n'est pas un problème. La même chose est vraie dans la plupart des situations, étant donné comment l'espace disque bon marché est devenu.

nombre de colonnes? On m'a toujours dit de faire des index à une seule colonne, pas des index à plusieurs colonnes. Donc pas plus d'indices que le nombre de colonnes, IMHO.

ce qu'il en ressort vraiment est, ne pas ajouter un index à moins que vous sachiez (et cela signifie souvent la collecte de statistiques d'utilisation) qu'il sera utilisé beaucoup plus souvent qu'il est mis à jour.

tout indice qui ne répond pas à ces critères vous coûtera plus cher à reconstruire que la pénalité de performance de ne pas l'avoir dans le cas Impair il a été utilisé.

Sql server vous donne de bons outils qui vous permettent de voir quels index sont réellement utilisés. Cet article, http://www.mssqltips.com/tip.asp?tip=1239 , vous donne quelques requêtes qui vous permettent d'avoir une meilleure idée de combien un index est utilisé, par opposition à combien il est mis à jour.

il est entièrement basé sur les colonnes qui sont utilisées dans la Clause Where. Et en règle générale, nous devons avoir des index sur les colonnes de clés étrangères pour éviter les impasses. Le rapport de L'AWR devrait être analysé périodiquement pour comprendre le besoin d'index.