Comment fonctionnent les index MySQL ?

Question

Je suis vraiment intéressé par le fonctionnement index MySQL qu’il ne pouvait pas scanner toute la table pour nous donner des résultats ? C’est hors-sujet, je sais, mais s’il y a quelqu'un qui pourrait m’expliquer que pittoresque, je serais très très reconnaissant.

Answer 1

Fondamentalement, un index sur une table fonctionne comme un index dans un livre (c'est là que le nom est venu à partir de):

Disons que vous avez un livre sur les bases de données et que vous voulez trouver quelques informations sur le, dire, de stockage. Sans index (en supposant qu'aucun autre aide, comme une table des matières) vous auriez à passer par les pages une par une, jusqu'à ce que vous avez trouvé le sujet (c'est un full table scan). D'autre part, un index est une liste de mots-clés, donc, si vous voulez consulter l'index et de voir qu' storage est mentionné sur les pages 113-120,231 et 354. Ensuite, vous pouvez retourner à ces pages directement, sans le chercher (c'est une recherche à l'aide d'un index, un peu plus rapide).

Bien sûr, l'utilité de l'index va être dépend de beaucoup de choses - quelques exemples, à l'aide de la comparaison ci-dessus:

• si vous aviez un livre sur les bases de données indexées et le mot "base de données", vous verriez que c'est mentionné sur les pages 1 à 59,61-290, et 292 à 400. Dans de tels cas, l'index n'est pas beaucoup d'aide et il serait plus rapide d'aller à travers les pages une par une (dans une base de données, c'est "une mauvaise sélectivité").
• Pour un 10-page de livre, il n'a pas de sens de faire un index, vous pouvez vous retrouver avec un 10-page de livre préfixé par un 5-page d'index, qui est tout simplement ridicule - il suffit de scanner le 10 pages et être fait avec elle.
• L'indice a également besoin d'être utile - il n'y a généralement pas de point d'indice par exemple, la fréquence de la lettre "L" par page.

Answer 2

La première chose que vous devez savoir est que les index sont un moyen d'éviter la numérisation de l'intégralité de la table pour obtenir le résultat que vous recherchez.

Il existe différents types d'index et ils sont mis en œuvre dans la couche de stockage, donc il n'y a pas de standard entre eux et ils dépendent aussi du moteur de stockage que vous utilisez.

InnoDB et le B+index d'Arborescence

Pour InnoDB, le plus commun type d'index est le B+Arbre en fonction de l'indice, qui stocke les éléments dans un ordre trié. Aussi, vous n'avez pas accès à la "vraie" table pour obtenir les valeurs indexées, ce qui rend votre requête en retour beaucoup plus rapide.

Le "problème" à propos de ce type d'index est que vous avez à la requête de la plus à gauche de la valeur à l'index. Donc, si votre index a deux colonnes, dire le nom et le prénom, l'ordre vous interroger ces champs importe beaucoup.

Donc, d'après le tableau suivant:

CREATE TABLE person (
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    INDEX (last_name, first_name)
);

Cette requête devrait profiter de l'index:

SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"

Mais la suivante ne serait pas

SELECT last_name, first_name FROM person WHERE first_name = "Constantine"

Parce que vous êtes à l'interrogation de l' first_name colonne premier et ce n'est pas la colonne de gauche de l'index.

Ce dernier exemple est encore pire:

SELECT last_name, first_name FROM person WHERE first_name LIKE "%Constantine"

Parce que maintenant, vous allez comparer la partie droite de la droite du champ de l'indice.

L'index de hachage

C'est un autre type d'index qui, malheureusement, seule la mémoire backend prend en charge. Il est rapide comme l'éclair, mais seulement utile pour plein de recherches, ce qui signifie que vous ne pouvez pas l'utiliser pour des opérations comme >, < ou LIKE.

Depuis elle ne fonctionne que pour la mémoire backend, vous n'aurez probablement pas l'utiliser très souvent. Les principaux cas je pense dès maintenant est celui que vous créez une table temporaire dans la mémoire, avec un ensemble de résultats à partir d'un autre de sélectionner et d'effectuer un grand nombre d'autres sélectionne dans cette table temporaire à l'aide de hachage index.

Si vous avez un gros VARCHAR domaine, vous pouvez "imiter" l'utilisation d'un index de hachage lors de l'utilisation d'un B-Arbre, par la création d'une autre colonne et l'enregistrement d'un hachage de la grande valeur ajoutée. Disons que vous êtes le stockage d'une url dans un champ et les valeurs sont assez grandes. Vous pouvez également créer un champ de type entier appelés url_hash et l'utilisation d'une fonction de hachage comme CRC32 ou toute autre fonction de hachage hash de l'url lors de l'insertion. Et puis, quand vous en avez besoin pour la requête de cette valeur, vous pouvez faire quelque chose comme ceci:

SELECT url FROM url_table WHERE url_hash=CRC32("http://gnu.org");

Le problème avec l'exemple ci-dessus est que, depuis l' CRC32 fonction génère une toute petite table de hachage, vous vous retrouverez avec beaucoup de collisions dans les valeurs de hachage. Si vous avez besoin des valeurs exactes, vous pouvez résoudre ce problème en procédant comme suit:

SELECT url FROM url_table 
WHERE url_hash=CRC32("http://gnu.org") AND url="http://gnu.org";

Il est toujours intéressant de hachage des choses, même si le nombre de collision est élevé cause vous n'aurez qu'à effectuer la comparaison (le fil) contre la répétition de hachages.

Malheureusement, en utilisant cette technique, vous avez encore besoin de frapper la table de comparer l' url champ.

Envelopper

Quelques faits que vous pourriez envisager, chaque fois que vous voulez parler de l'optimisation:

1. Comparaison des entiers est plus rapide que la comparaison de chaînes de caractères. Il peut être illustré avec l'exemple sur l'émulation de l'index de hachage en InnoDB.

2. Peut-être, en ajoutant des étapes dans un processus rend plus rapide, pas plus lent. Il peut être illustré par le fait que vous pouvez optimiser une SELECT par fractionnement en deux étapes, en faisant le premier stocker des valeurs dans une nouvelle table en mémoire, et ensuite exécuter le plus lourd des requêtes sur cette seconde table.

MySQL a d'autres indices de trop, mais je pense que le B+Arbre est le plus utilisé jamais et le hachage est une bonne chose à savoir, mais vous pouvez trouver d'autres dans la documentation de MySQL.

Je vous recommande fortement de lire la "Haute Performance MySQL", le livre, la réponse ci-dessus est certainement en fonction de son chapitre sur les index.

Answer 3

Fondamentalement, un index est une carte de tous vos clés, qui est triée dans l'ordre. Avec une liste dans l'ordre, puis, au lieu de vérifier à chaque touche, il peut faire quelque chose comme ceci:

1: Aller au moyen de la liste est supérieur ou inférieur à ce que je cherche?

2: Si la hausse de la, aller à mi-chemin entre le moyen et le bas, si bas, milieu et haut

3: Est supérieur ou inférieur? Saut à point milieu, etc.

À l'aide de cette logique, vous pouvez trouver un élément dans une liste triée en 7 étapes, au lieu de vérifier chaque élément.

Évidemment il y a des complexités, mais qui vous donne l'idée de base.

Answer 4

Jetez un oeil à ce lien : http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Leur fonctionnement est trop large d’un sujet à couvrir dans un ce post.

Ici est une des meilleures explications d’index que j’ai vu. Malheureusement, c’est pour SQL Server et MySQL pas. Je ne sais pas comment les deux sont...