livre - supprimer index mysql




Comment fonctionne l'indexation de base de données? (6)

Description simple!

L'index n'est rien d'autre qu'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 User avec trois colonnes - Name , Age et Address . Supposons que la table User comporte des milliers de lignes.

Maintenant, supposons que nous voulions lancer une requête pour trouver tous les détails des utilisateurs nommés "John". Si nous lançons la requête suivante:

SELECT * FROM User 
WHERE Name = 'John'

Le logiciel de base de données devrait littéralement examiner chaque ligne du tableau User pour voir si le Name de cette ligne est 'John'. Cela prendra beaucoup de temps.

C'est là index nous aide: index est utilisé pour accélérer les requêtes de recherche en réduisant essentiellement le nombre d'enregistrements / lignes dans une table à examiner .

Comment créer un index:

CREATE INDEX name_index
ON User (Name)

Un index est constitué des valeurs de colonne (par exemple, John) d'une table et ces valeurs sont stockées dans une structure de données .

Alors maintenant, la base de données utilisera l'index pour trouver des employés nommés John, car l'index sera probablement trié par ordre alphabétique du nom de l'utilisateur. Et, comme il est trié, cela signifie que la recherche d'un nom est beaucoup plus rapide, car tous les noms commençant par un «J» seront placés l'un à côté de l'autre dans l'index!

Étant donné que l'indexation est si importante que la taille de votre jeu de données augmente, quelqu'un peut-il expliquer comment fonctionne l'indexation au niveau de la base de données?

Pour plus d'informations sur les requêtes visant à indexer un champ, consultez la section Comment indexer une colonne de base de données .


Exemple classique "Index dans les livres"

Considérons un "livre" de 1000 pages, divisé par 100 sections, chaque section comportant X pages.

Simple, hein?

Maintenant, sans page d'index, pour trouver une section particulière commençant par la lettre "S", vous n'avez pas d'autre choix que de parcourir l'intégralité du livre. c'est à dire: 1000 pages

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

Mais alors, en plus de 1000 pages, il vous faudra environ 10 pages supplémentaires pour afficher la page d’index, donc 1010 pages au total.

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

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


Juste une suggestion rapide .. L’indexation vous coûtant des écritures et de l’espace de stockage supplémentaires. Par conséquent, si votre application nécessite davantage d’opérations d’insertion / de mise à jour, vous pouvez utiliser des tables sans index, mais si elle nécessite davantage d’opérations d’extraction de données, optez pour index table.


La première fois que j'ai lu cela, cela m'a été très utile. Merci.

Depuis lors, j'ai eu quelques idées sur les inconvénients de la création d'index: si vous écrivez dans une table ( UPDATE ou INSERT ) avec un index, vous avez en réalité deux opérations d'écriture dans le système de fichiers. Une pour les données de table et une autre pour les données d'index (et leur utilisation (et - en cas de mise en cluster - l'utilisation de ces données)). Si table et index sont situés sur le même disque dur, cela prend plus de temps. Ainsi, une table sans index (un tas) permettrait des opérations d'écriture plus rapides. (si vous aviez deux index, vous auriez trois opérations d'écriture, etc.)

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

Un autre problème avec les index est leur fragmentation au fil du temps, à mesure que les données sont insérées. REORGANIZE aide, vous devez écrire des routines pour le faire.

Dans certains scénarios, un segment de mémoire est plus utile qu'une table avec des index,

Par exemple: - Si vous avez beaucoup d'écritures concurrentes mais qu'une seule lecture nocturne tous les soirs en dehors des heures de bureau est réservée.

En outre, une différenciation entre les index en cluster et les index non en cluster est plutôt importante.

M'a aidé: - Que signifient les index cluster et non cluster?


Un index est simplement une structure de données qui accélère la recherche d'une colonne spécifique dans une base de données. Cette structure est généralement un arbre binaire ou une table de hachage, mais il peut s'agir de toute autre structure logique.


Pourquoi est-ce nécessaire?

Lorsque les données sont stockées sur des périphériques de stockage sur disque, elles sont stockées sous forme de blocs de données. Ces blocs sont accessibles dans leur intégralité, ce qui en fait l’opération d’accès au disque atomique. Les blocs de disque sont structurés de la même manière que les listes chaînées. les deux contiennent une section pour les données, un pointeur sur l'emplacement du prochain nœud (ou bloc), et n'ont pas besoin d'être stockés de manière contiguë.

Etant donné qu'un certain nombre d'enregistrements ne peuvent être triés que sur un seul champ, nous pouvons affirmer que la recherche sur un champ non trié nécessite une recherche linéaire qui nécessite N/2 accès en bloc (en moyenne), où N est le nombre de blocs couverts par la table. Si ce champ est un champ non-clé (c'est-à-dire ne contient pas d'entrées uniques), alors le tablespace entier doit être recherché à N accès de bloc.

Tandis qu'avec un champ trié, une recherche binaire peut être utilisée, laquelle dispose de log2 N accès au bloc. De plus, étant donné que les données sont triées en fonction d'un champ non clé, il n'est pas nécessaire de rechercher les valeurs en double dans le reste de la table, une fois qu'une valeur plus élevée est trouvée. Ainsi, l’augmentation des performances est substantielle.

Qu'est-ce que l'indexation?

L'indexation est un moyen de trier un nombre d'enregistrements sur plusieurs champs. La 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 l'enregistrement auquel elle se rapporte. Cette structure d'index est ensuite triée, ce qui permet d'effectuer des recherches binaires.

L’inconvénient de l’indexation est que ces index requièrent de l’espace supplémentaire sur le disque car ils sont stockés ensemble dans une table à l’aide du moteur MyISAM, ce fichier peut atteindre rapidement les limites de taille du système de fichiers sous-jacent si plusieurs champs de la même table sont indexés. .

Comment ça marche?

Premièrement, décrivons un exemple de schéma de table de base de données;

Field name       Data type      Size on disk
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes

Remarque : Un caractère a été utilisé à la place de varchar pour permettre une taille précise sur la valeur du disque. Cet exemple de base de données contient cinq millions de lignes et n'est pas indexé. Les performances de plusieurs requêtes vont maintenant être analysées. Il s’agit d’une requête utilisant l’ id (un champ de clé trié) et d’une autre utilisant le prénom (un champ non trié de clé).

Exemple 1 - champs triés vs non triés

Étant donné notre exemple de base de données de r = 5,000,000 enregistrements de taille fixe donnant une longueur d’enregistrement de R = 204 octets, ils sont stockés dans une table à l’aide du moteur MyISAM, qui utilise la taille de bloc par défaut B = 1,024 octets. Le facteur de blocage de la table serait bfr = (B/R) = 1024/204 = 5 enregistrements par bloc de disque. Le nombre total de blocs requis pour contenir la table est N = (r/bfr) = 5000000/5 = 1,000,000 blocs.

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

Maintenant, le champ firstName n'est ni trié ni un champ clé; une recherche binaire est donc impossible, pas plus que les valeurs ne sont uniques. La table nécessitera donc de rechercher jusqu'à la fin un N = 1,000,000 accès au bloc exact. C’est cette situation que l’indexation vise à corriger.

Etant donné qu'un enregistrement d'index ne contient que le champ indexé et un pointeur sur l'enregistrement d'origine, il va de soi qu'il sera plus petit que l'enregistrement à plusieurs champs sur lequel il pointe. Ainsi, l'index lui-même nécessite moins de blocs de disque que la table d'origine, ce qui nécessite par conséquent moins d'accès de bloc pour parcourir. Le schéma d'un index sur le champ firstName est décrit ci-dessous;

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes

Remarque : Les pointeurs dans MySQL ont une longueur de 2, 3, 4 ou 5 octets, en fonction de la taille du tableau.

Exemple 2 - indexation

Étant donné notre exemple de base de données de r = 5,000,000 enregistrements avec une longueur d’index de R = 54 octets et en utilisant la taille de bloc par défaut B = 1,024 octets. Le facteur de blocage de l'index serait bfr = (B/R) = 1024/54 = 18 enregistrements par bloc de disque. Le nombre total de blocs requis pour conserver l'index est N = (r/bfr) = 5000000/18 = 277,778 blocs.

Désormais, une recherche utilisant le champ firstName peut utiliser l'index pour améliorer les performances. Cela permet une recherche binaire de l'index avec une moyenne de log2 277778 = 18.08 = 19 accès en bloc. Pour trouver l'adresse de l'enregistrement réel, ce qui nécessite un autre accès de bloc à lire, ce qui porte le total à 19 + 1 = 20 accès de bloc, ce qui est très différent des 1 000 000 accès de bloc nécessaires pour trouver une correspondance firstName dans la table non indexée. .

Quand doit-il être utilisé?

Étant donné que la création d'un index nécessite davantage d'espace disque (277 778 blocs supplémentaires, par rapport à l'exemple ci-dessus, soit une augmentation d'environ 28%), et qu'un trop grand nombre d'index peut entraîner des problèmes liés aux limites de taille des systèmes de fichiers, vous devez soigneusement sélectionner le paramètre correct. champs à indexer.

Etant donné que les index ne sont utilisés que pour accélérer la recherche d'un champ correspondant dans les enregistrements, il va sans dire que les champs d'indexation utilisés uniquement pour la sortie constitueraient simplement une perte d'espace disque et de temps de traitement lors d'une opération d'insertion ou de suppression, et donc devrait être évité. De plus, étant donné la nature d'une recherche binaire, la cardinalité ou le caractère unique des données est important. L'indexation sur un champ avec une cardinalité de 2 diviserait les données en deux, alors qu'une cardinalité de 1 000 renverrait environ 1 000 enregistrements. Avec une cardinalité aussi faible, l'efficacité est réduite à un tri linéaire et l'optimiseur de requêtes évitera d'utiliser l'index si la cardinalité est inférieure à 30% du nombre d'enregistrements, ce qui en fait un gaspillage d'espace.





database-indexes