Comment fonctionne l'indexation de base de données?

voix
1k

Étant donné que indexingest aussi important que votre ensemble de données augmente en taille, quelqu'un peut - il expliquer comment fonctionne l'indexation à un database-agnosticniveau?

Pour plus d' informations sur les requêtes pour indexer un champ, consultez Comment puis-je indexer une colonne de base de données .

Créé 04/08/2008 à 11:07
source utilisateur
Dans d'autres langues...                            


10 réponses

voix
2k

Pourquoi est-il nécessaire?

Lorsque les données sont stockées sur des dispositifs de stockage sur disque, il est stocké sous forme de blocs de données. Ces blocs sont accessibles dans leur intégralité, ce qui les rend l'opération d'accès au disque atomique. blocs de disque sont structurés de la même manière que les listes chaînées; contiennent tous les deux une section de données, un pointeur vers l'emplacement du noeud suivant (ou le bloc), et les deux ne doivent pas être stockés de manière contiguë.

En raison du fait qu'un certain nombre d'enregistrements ne peut être triée sur un terrain, nous pouvons affirmer que la recherche sur un champ non trié nécessite une recherche linéaire qui nécessite des N/2accès de bloc (en moyenne), où Nest le nombre de blocs la table couvre. Si ce champ est un champ non-clé ( par exemple ne contient pas d' entrées uniques) doit alors être recherchée au tablespace ensemble Nbloc accès.

Alors qu'avec un champ triée, une recherche binaire peut être utilisé, qui a log2 Nbloc accès. En outre , puisque les données sont triées donné un champ non-clé, le reste de la table n'a pas besoin à rechercher des valeurs en double, une fois une valeur plus élevée se trouve. Ainsi , l'augmentation de la performance est importante.

Qu'est-ce que l'indexation?

L'indexation est une façon de trier un certain nombre de dossiers sur plusieurs champs. Création d'un index sur un champ dans une table crée une autre structure de données qui contient la valeur du champ, et un pointeur sur le dossier, il se rapporte. Cette structure d'index est ensuite triée, qui permet des recherches binaires à effectuer sur elle.

L'inconvénient de l'indexation est que ces indices ont besoin d'espace supplémentaire sur le disque car les index sont stockés ensemble dans une table en utilisant le moteur MyISAM, ce fichier peut rapidement atteindre les limites de taille du système de fichiers sous-jacent si plusieurs champs dans la même table sont indexés .

Comment ça marche?

Tout d'abord, nous allons décrivent un schéma de la table de base de données exemple;

Nom du champ Type de données Taille sur le disque
id (clé primaire) INT 4 octets Unsigned
firstName char (50) 50 octets
nomFamille char (50) 50 octets
emailAddress Char (100) 100 octets

Remarque : char a été utilisé à la place de varchar pour permettre une taille précise de la valeur du disque. Cette base de données de l' échantillon contient cinq millions de lignes et est non indexée. Les performances de plusieurs requêtes va maintenant analyser. Ceux - ci sont une requête en utilisant l' identifiant (un champ de clé triée) et un en utilisant le prenom (un champ non-clé non triés).

Exemple 1 - trié vs champs non triés

Compte tenu de notre base de données exemple de r = 5,000,000dossiers d'une taille fixe donnant une longueur d'enregistrement des R = 204octets et ils sont stockés dans une table en utilisant le moteur MyISAM qui utilise la taille de bloc par défaut B = 1,024octets. Le facteur de blocage de la table serait d' bfr = (B/R) = 1024/204 = 5enregistrements par bloc de disque. Le nombre total de blocs nécessaires pour tenir la table des N = (r/bfr) = 5000000/5 = 1,000,000blocs.

Une recherche linéaire sur le champ id , il faudrait une moyenne de N/2 = 500,000bloc accède à trouver une valeur, étant donné que le champ id est un domaine clé. Mais depuis le champ id est également triée, une recherche binaire peut être effectuée nécessitant une moyenne de log2 1000000 = 19.93 = 20bloc accès. Instantanément , nous pouvons voir c'est une amélioration drastique.

Maintenant , le prenom champ est ni , ni un champ Sorted clé, donc une recherche binaire est impossible, ni les valeurs sont uniques, et donc la table , il faudra chercher à la fin pour un exact N = 1,000,000bloc accès. Il est cette situation que l' indexation vise à corriger.

Étant donné qu'un enregistrement d'index ne contient que le champ indexé et un pointeur vers l'enregistrement original, il va de soi que ce sera plus petit que le record multi-champ qu'il pointe. Ainsi , l'indice lui - même nécessite moins de blocs de disque que la table d' origine, ce qui nécessite donc moins accès bloc à itérer. Le schéma d'un index sur la firstName champ est décrit ci - dessous;

Nom du champ Type de données Taille sur le disque
firstName char (50) 50 octets
(Pointeur d'enregistrement) 4 octets spéciaux

Remarque : Les pointeurs en MySQL sont 2, 3, 4 ou 5 octets de longueur en fonction de la taille de la table.

Exemple 2 - indexation

Compte tenu de notre base de données de l' échantillon de r = 5,000,000dossiers avec une longueur d'enregistrement d'index d' R = 54octets et en utilisant la taille de bloc par défaut B = 1,024octets. Le facteur de blocage de l'indice serait d' bfr = (B/R) = 1024/54 = 18enregistrements par bloc de disque. Le nombre total de blocs nécessaires pour maintenir l'index est N = (r/bfr) = 5000000/18 = 277,778blocs.

Maintenant , une recherche en utilisant le prenom champ peut utiliser l'index pour augmenter les performances. Cela permet une recherche binaire de l'indice avec une moyenne d' log2 277778 = 18.08 = 19accès de bloc. Pour trouver l'adresse de l'enregistrement réel, ce qui nécessite un accès bloc plus à lire, ce qui porte le total à 19 + 1 = 20bloquer les accès, loin du bloc 1.000.000 les accès nécessaires pour trouver un prenom match à la table non indexée.

Quand faut-il être utilisé?

Étant donné que la création d'un index nécessite de l'espace disque supplémentaire (277,778 blocs supplémentaires de l'exemple ci-dessus, a ~ 28% d'augmentation), et que trop d'indices peuvent causer des problèmes découlant des systèmes de fichiers des limites de taille, une réflexion approfondie doit être utilisé pour sélectionner le bon champs à indexer.

Étant donné que les indices ne sont utilisés que pour accélérer la recherche d'un champ correspondant dans les dossiers, il va de soi que les champs d'indexation utilisés uniquement pour la sortie serait tout simplement un gaspillage d'espace disque et le temps de traitement lorsque vous effectuez une insertion ou de suppression, et donc devrait être évité. De plus étant donné la nature d'une recherche binaire, la cardinalité ou l'unicité des données est importante. L'indexation sur un champ avec une cardinalité de 2 diviserait les données en deux, alors qu'un cardinalité de 1000 retournerait environ 1 000 dossiers. Avec un si faible cardinalité l'efficacité est réduite à une sorte linéaire, et l'optimiseur de requête éviter d'utiliser l'index si la cardinalité est inférieure à 30% du nombre record, ce qui rend effectivement l'indice un gaspillage d'espace.

Créé 04/08/2008 à 11:41
source utilisateur

voix
168

La première fois que je lis cela, il a été très utile pour moi. Je vous remercie.

Depuis lors , j'ai gagné un aperçu sur les inconvénients de la création d' index: si vous écrivez dans une table ( UPDATEou INSERT IGNORE ) avec un index, vous avez en fait deux opérations d'écriture dans le système de fichiers. Un pour les données de la table et l' autre pour les données d'index (et le recours de celui - ci (et - si groupés - les changements de position des données de table)). Si la table et l' index sont situés sur le même disque dur que cela coûte plus de temps. Ainsi , une table sans un index (un tas), permettrait d'opérations d'écriture plus rapide. (si vous aviez deux indices vous finiriez avec trois opérations d'écriture, etc.)

Cependant, la définition de deux endroits différents sur deux disques durs différents pour les données d'index et les données de table peuvent diminuer / éliminer le problème du coût accru du temps. Cela nécessite la définition des groupes de fichiers supplémentaires avec selon les fichiers sur les disques durs souhaités et la définition de la table / emplacement de l'index comme vous le souhaitez.

Un autre problème avec des indices est leur fragmentation au fil du temps est inséré des données. REORGANIZEHelps, vous devez écrire des routines de l' avoir fait.

Dans certains scénarios un tas est plus utile qu'une table avec des index,

par exemple: - Si vous avez beaucoup d'écritures rivalisant mais on lit la nuit en dehors des heures d'ouverture pour les rapports.

En outre, une différenciation entre les index clusterisés et non agglomérée est assez important.

M'a aidé: - Qu'est-ce en cluster et l' index non cluster signifie réellement?

Créé 30/04/2013 à 15:31
source utilisateur

voix
124

Un index est juste une structure de données qui rend la recherche plus rapide pour une colonne spécifique dans une base de données. Cette structure est généralement un b-arbre ou une table de hachage, mais il peut être toute autre structure logique.

Pour plus d' informations, je vous recommande: Comment les index de base de données fonctionnent -ils ? Et, comment les indices d' aide?

Créé 20/02/2014 à 15:40
source utilisateur

voix
86

Maintenant, disons que nous voulons exécuter une requête pour trouver tous les détails de tous les employés qui sont nommés « Abc »?

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

Que se passerait-il sans un indice?

Logiciel de base de données aurait littéralement à regarder chaque ligne unique dans la table des employés pour voir si le EMPLOYEE_NAME pour cette ligne est « Abc ». Et, parce que nous voulons que chaque ligne avec le nom « Abc » à l' intérieur, nous ne pouvons pas arrêter de regarder une fois que nous venons de trouver une ligne avec le nom « Abc », car il pourrait y avoir d' autres lignes avec le nom Abc . Ainsi, chaque ligne jusqu'à la dernière ligne doit être recherchée - ce qui signifie des milliers de lignes dans ce scénario devra être examiné par la base de données pour trouver les lignes avec le nom « Abc ». C'est ce qu'on appelle un scan de table

Comment un indice de base de données peut améliorer les performances

Le point entier d'avoir un indice est d'accélérer les requêtes de recherche en coupant essentiellement sur le nombre d'enregistrements / lignes dans une table qui doivent être examinés. Un index est une structure de données (le plus souvent un arbre B) qui stocke les valeurs d'une colonne spécifique dans une table.

Comment l'indice B-arbres travaillent?

La raison pour laquelle les arbres B- sont les plus populaires structure de données pour les index est dû au fait qu'ils sont efficace du temps - car look-ups, des suppressions et des insertions peuvent tous être effectués en temps logarithmique. Et, une autre raison majeure des arbres B- sont utilisés plus souvent parce que les données qui sont stockées à l'intérieur de l'arbre B peut être triée. Le SGBDR détermine généralement la structure des données qui est effectivement utilisée pour un index. Mais, dans certains scénarios avec certains SGBDR de, vous pouvez spécifier réellement quelle structure les données que vous voulez que votre base de données à utiliser lors de la création de l'indice lui-même.

Comment fonctionne un index de table de hachage?

Les indices de hachage raison sont utilisés parce que les tables de hachage sont extrêmement efficaces quand il est juste à la recherche des valeurs. Ainsi, les requêtes qui comparent l'égalité à une chaîne peut récupérer des valeurs très rapidement si elles utilisent un index de hachage.

Par exemple, la requête, nous avons discuté plus tôt pourrait bénéficier d'un index de hachage créé sur la colonne EMPLOYEE_NAME. La façon dont un indice de hachage fonctionnerait est que la valeur de la colonne sera la clé dans la table de hachage et la valeur réelle cartographié à cette clé serait juste un pointeur vers les données de ligne dans le tableau. Étant donné qu'une table de hachage est essentiellement un tableau associatif, une entrée typique ressemblerait à quelque chose comme « Abc => 0x28939", où 0x28939 est une référence à la ligne de table où Abc est stocké dans la mémoire. Vous cherchez une valeur comme « Abc » dans un index de table de hachage et récupérer une référence à la ligne en mémoire est évidemment beaucoup plus rapide que le balayage de la table pour trouver toutes les lignes avec une valeur de « Abc » dans la colonne EMPLOYEE_NAME.

Les inconvénients d'un indice de hachage

Les tables de hachage ne sont pas triés structures de données, et il existe de nombreux types de requêtes qui index de hachage ne peuvent même aider. Par exemple, supposons que vous voulez savoir tous les employés qui sont âgés de moins de 40 ans. Comment pourriez-vous faire avec un index de table de hachage? Eh bien, il est impossible car une table de hachage est seulement bon pour rechercher des paires de valeurs clés - ce qui signifie requêtes qui vérifient l'égalité

Quelle est exactement à l' intérieur d' un index de base de données? Donc, maintenant vous savez qu'un index de base de données est créée sur une colonne dans une table, et que l'indice stocke les valeurs dans cette colonne spécifique. Mais, il est important de comprendre qu'un index de base de données ne stocke pas les valeurs dans les autres colonnes de la même table. Par exemple, si nous créons un index sur la colonne EMPLOYEE_NAME, cela signifie que les valeurs de la colonne Employee_Age et Employee_Address ne sont pas également stockées dans l'index. Si nous ne stockons que toutes les autres colonnes de l'index, alors il serait tout comme la création d'une autre copie de toute la table - qui prendrait de l' espace beaucoup trop et serait très inefficace.

Comment une base de données de savoir quand utiliser un index? Lorsqu'une requête comme « SELECT * FROM employee WHERE EMPLOYEE_NAME = « Abc » » est exécuté, la base de données vérifiera s'il y a un index sur la colonne (s) interrogée. En supposant que la colonne de EMPLOYEE_NAME n'ont un indice créé sur elle, la base de données devra décider s'il est effectivement logique d'utiliser l'index pour trouver les valeurs en cours de recherche - parce qu'il ya des scénarios où il est en fait moins efficace d'utiliser l'indice de base de données et plus efficace juste pour balayer toute la table.

Quel est le coût d'avoir un indice de base de données?

Il prend de la place - et plus votre table, plus votre index. Un autre coup de performance avec des indices est le fait que chaque fois que vous ajoutez, supprimez ou les lignes de mise à jour dans la table correspondante, les mêmes opérations devront faire à votre index. Rappelez-vous qu'un index doit contenir le même jusqu'à les données minute que tout ce qui est dans la colonne de table (s) que l'indice couvre.

En règle générale, un indice ne doit être créé sur une table si les données de la colonne indexée seront interrogés fréquemment.

Voir également

  1. Les colonnes font généralement de bons indices?
  2. Comment les index de base de données fonctionnent
Créé 13/08/2016 à 18:36
source utilisateur

voix
64

Un exemple classique « Index dans Livres »

Considérons un "Livre" de 1000 pages, divisé par 100 sections, chaque section avec des pages X.

Simple, hein?

Maintenant, sans une page d'index, pour trouver une section particulière qui commence par la lettre « S », vous avez pas d'autre option que la numérisation à travers l'ensemble du livre. à savoir: pages 1000

Mais avec une page d'index au début, vous êtes là. Et plus, lire une section particulière qui compte, il suffit de regarder par-dessus la page d'index, encore et encore, chaque fois. Après avoir trouvé l'indice correspondant, vous pouvez efficacement accéder à la section en sautant d'autres sections.

Mais, en plus de 1000 pages, vous aurez besoin d'un autre ~ 10 pages pour afficher la page d'index, donc totalement 1010 pages.

Ainsi, l'indice est une section distincte qui stocke les valeurs de la colonne indexée + pointeur sur la ligne indexée dans un ordre de tri pour un look-ups efficaces.

Les choses sont simples dans les écoles, est-ce pas? : P

Créé 23/04/2017 à 14:43
source utilisateur

voix
40

Simple Description de !!!!!!!!!!

L'indice est rien, mais une structure de données qui stocke les valeurs d'une colonne spécifique dans une table. Un index est créé sur une colonne d'une table.

Exemple, nous avons une table de base de données appelée utilisateur avec trois colonnes - Nom, âge et adresse. Supposons que la table utilisateur a des milliers de lignes.

Maintenant, disons que nous voulons exécuter une requête pour trouver tous les détails de tous les utilisateurs qui sont nommés « John ». Si nous courons la requête suivante.

SELECT * FROM User 
WHERE Name = 'John'

Le logiciel de base de données devrait littéralement regarder chaque ligne unique dans la table utilisateur pour voir si le nom de cette ligne est « John ». Cela prendra beaucoup de temps.
C'est là indice nous aide « index est utilisé pour accélérer les requêtes de recherche en coupant essentiellement sur le nombre d'enregistrements / lignes dans une table qui doit être examiné ».
Comment créer un index

CREATE INDEX name_index
ON User (Name)

Un index est constitué de valeurs de colonne (par exemple , John) d'une table, et que ces valeurs sont stockées dans une structure de données.
Alors maintenant , la base de données utilise l'index pour trouver des employés du nom de John parce que l'indice sera vraisemblablement classées par ordre alphabétique le nom des utilisateurs. Et, parce qu'il est trié, cela signifie la recherche d'un nom est beaucoup plus rapide parce que tous les noms commençant par un « J » sera juste à côté de l'autre dans l'indice!

Créé 02/08/2016 à 01:30
source utilisateur

voix
18

Juste une suggestion rapide .. Comme les coûts d'indexation vous écrit supplémentaires et l'espace de stockage, donc si votre application nécessite un fonctionnement plus insert / mise à jour, vous pouvez utiliser des tables sans index, mais si elle nécessite plusieurs opérations de récupération de données, vous devriez aller indexé table.

Créé 14/01/2015 à 06:44
source utilisateur

voix
14

Il suffit de penser Index Base de données comme l'indice d'un livre. Si vous avez un livre sur les chiens et que vous voulez trouver une information sur disons, les bergers allemands, vous pouvez bien sûr feuilleter toutes les pages du livre et de trouver ce que vous cherchez, mais cela est bien sûr beaucoup de temps et pas très vite. Une autre option est que, vous pouvez simplement aller à la section Index du livre et trouver ce que vous cherchez en utilisant le nom de l'entité que vous êtes à la recherche (en l'occurrence, les bergers allemands) et aussi à la recherche du numéro de page trouver rapidement ce que vous recherchez. Dans la base de données, le numéro de page est appelée un pointeur qui dirige la base de données à l'adresse sur le disque où l'entité est située. En utilisant la même analogie de berger allemand, nous pourrions avoir quelque chose comme ça ( « berger allemand »,

En bref, un indice est une structure de données qui stocke les valeurs d'une colonne spécifique dans une table afin d'accélérer la recherche de la requête.

Créé 21/12/2016 à 17:16
source utilisateur

voix
7

index SQL est quelque chose lié à speedup la recherche dans la base de données SQL. Index permet programmeur de récupérer les données de base de données très rapide. Supposons que vous êtes un étudiant ou d'un lecteur de livre. Votre livre contient 50.000 pages. Le premier jour vous lisez un sujet « ABC » jour suivant que vous voulez lire une autre sujet « xyz ». vous ne serez jamais manuellement par page par page. Qu'est-ce que vous allez faire dans cette situation est d'utiliser l'index du livre pour regarder l'un sujet spécifique puis Aller directement à votre sujet. Index sauvé vos beaucoup de temps pour rechercher le sujet. Même dans l'index SQL, index permet de rechercher des millions d'enregistrements très rapidement la base de données.

Créé 15/02/2018 à 10:17
source utilisateur

voix
2

Un index de base de données est une structure de données qui améliore la vitesse des opérations de récupération de données sur une table de base de données au prix d'écritures supplémentaires et l'espace de stockage pour maintenir la structure de données d'index. Les index sont utilisés pour localiser rapidement des données sans avoir à rechercher toutes les lignes dans une table de base de données à chaque fois une table de base de données est accessible. Les index peuvent être créés en utilisant une ou plusieurs colonnes d'une table de base de données, fournissant la base pour les recherches aléatoires rapides et un accès efficace des dossiers commandés.

Créé 09/07/2018 à 05:33
source utilisateur

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more