Comment apprendre les principes des index MySQL ? Mon propre résumé de l'expérience d'indexation

Qu'est-ce qu'un indice ? L'Encyclopédie Baidu le décrit ainsi : Un index est un résultat de données dispersées créé pour accélérer la récupération des lignes de données dans une table. Il est composé de pages d'index autres que la page de données. La page d'index contient des pointeurs logiques pour accélérer la récupération des données physiques. Comment apprendre les principes de l'index MySQL, cet article l'expliquera en détail.

Résumé : Parlons des index MySQL. Qu'est-ce qu'un indice ? L'Encyclopédie Baidu le décrit ainsi : Un index est un résultat de données dispersées créé pour accélérer la récupération des lignes de données dans une table. Il est composé de pages d'index autres que la page de données. la page d'index contient des pointeurs logiques pour accélérer la récupération des données physiques. En fait, tout le monde connaît le concept d'indexation et sait que l'indexation peut améliorer l'efficacité des requêtes. Cependant, la plupart des chaussures pour enfants se posent les questions suivantes sur la façon de créer un index. sur quels champs le construire. Malentendu : il n'est pas nécessaire de créer un index lors de la création d'une nouvelle table, et les index sont ajoutés ultérieurement. Tous les champs après la condition Where sont indexés. Simple SQL ne nécessite pas d'index, seules les requêtes conjointes en ont besoin. index. L'ordre de l'index conjoint est l'ordre des champs après la condition Where, qui est moins différencié. De nouveaux index sont également créés sur des champs, tels que le statut, le sexe et d'autres champs.

Parlons des index MySQL.

Qu'est-ce qu'un indice ?

Baidu Encyclopedia le décrit comme ceci :

L'index est un résultat de données dispersées créé pour accélérer la récupération des lignes de données dans la table. Oui. , il est composé de pages d'index autres que des pages de données. Les lignes de chaque page d'index contiennent des pointeurs logiques pour accélérer la récupération des données physiques

En fait, tout le monde est très clair sur la notion d'index. , et nous savons que les index peuvent améliorer l'efficacité des requêtes, mais la plupart des chaussures pour enfants ont les malentendus courants suivants sur la façon de créer des index et sur les champs sur lesquels s'appuyer :

Il n'est pas nécessaire de créer un index lors de la création d'un nouveau table, et les index seront ajoutés plus tard

Les champs après la condition Where sont tous indexés

Le SQL simple ne nécessite pas d'index, seule la requête conjointe nécessite un index

L'ordre de l'index conjoint est l'ordre des champs après la condition Where

De nouveaux index sont également créés pour les champs avec une petite distinction, tels que le statut, le sexe et d'autres champs .

Indice de distinction

Avant de parler des questions ci-dessus, examinons un autre concept, celui de la discrimination.

Distinction : fait référence au taux de non-duplication des champs dans la base de données.

La distinction a une valeur de référence très importante lors de la création d'un nouvel index. Dans MySQL, la différence. Les règles de calcul sont les suivantes :

Le quotient du nombre total de champs après déduplication et du nombre total d'enregistrements dans la table entière.

Par exemple :

sélectionnez count(distinct(name))/count(*) from t_base_user;

Le résultat est le suivant suit :

count(distinct(name))/count(*)

1.0000

La valeur maximale de la distinction est de 1,000 et la valeur minimale est de 0,0000. Plus la valeur de la distinction est grande, c'est-à-dire plus le taux de non-duplication des données est élevé, meilleur est le nouvel effet d'index. la clé primaire et la clé unique est la plus élevée, est 1,0000. La valeur de distinction sur des champs tels que le statut et le sexe est la plus petite. (Cela dépend de la quantité de données. S'il n'y a que quelques éléments de données, la discrimination est assez élevée. Si la quantité de données est importante, la discrimination est essentiellement de 0,0000. C'est-à-dire qu'après avoir ajouté des index à ces champs , l'effet n'est pas bon. Raison. )

Il est à noter que : S'il n'y a aucun enregistrement dans le tableau, le résultat du calcul de la discrimination est une valeur nulle. ​sont répartis entre 0,0000 et 1,0000.

Comment construire un index

(1) : Distinction
Je recommande fortement que lors de la construction d'un index, vous deviez d'abord calculer la discrimination de ce champ pour les raisons suivantes :

1. Index à colonne unique
Vous pouvez vérifier la distinction du champ en fonction du degré de distinction, vous pouvez également savoir approximativement si le nouvel index sur le champ est efficace et dans quelle mesure. Plus la discrimination est grande, plus l’effet d’indexation est évident.

2. Index multi-colonnes (index conjoint)
En fait, il y a un problème d'ordre des champs dans l'index multi-colonnes. Généralement, ceux avec une différenciation plus élevée sont placés en premier, de sorte que. l'index conjoint est plus efficace, par exemple :

select * from t_base_user Where name="" and status=1;

Une instruction comme celle ci-dessus, if. un index conjoint est construit, doit être :

alter table t_base_user add index idx_name_status(name,status);

au lieu de :

alter table t_base_user add index idx_status_name(status, name);

(2) Principe de correspondance du préfixe le plus à gauche
MySQL continuera à correspondre à droite jusqu'à ce qu'il rencontre une requête de plage (>, < ;, entre, j'aime) et arrête la correspondance, comme

select * from t_base_user où type="10" et create_at<"2017-11-03" et status=1, (cette instruction est uniquement pour la démonstration)

Dans l'instruction ci-dessus, le statut n'ira pas à l'index, car MySQL a cessé de correspondre lorsque < . L'ordre peut être ajusté, mais le statut ne peut pas être atteint. Index, à ce stade, l'instruction doit être modifiée comme :

select * from t_base_user où type=10 et status=1 et create_at< "03/11/2017"

C'est l'indice de statut qui peut être parcouru.

(3) Opération de fonction
N'effectuez pas d'opération de fonction sur la colonne d'index, sinon l'index sera invalide. Parce que l'arbre b+ stocke toutes les valeurs de champ dans la table de données, mais lors de la récupération, vous devez appliquer des fonctions à tous les éléments à comparer, ce qui est évidemment trop coûteux.

(4) Expansion d'abord
Expansion d'abord, ne créez pas de nouvel index, essayez de modifier l'index existant. Comme suit :

sélectionnez * from t_base_user où name="andyqian" et email="andytohome"

L'index idx_name existe déjà dans la table t_base_user, si vous besoin d'ajouter idx_name_email L'index devrait consister à modifier l'index idx_name au lieu de créer un nouvel index.

Correction des malentendus

Comme mentionné ci-dessus, comment créer un nouvel index, nous pouvons désormais répondre aux malentendus dans la première étape.

Malentendu 1 : il n'est pas nécessaire de créer un index lors de la création d'une nouvelle table, et l'index sera ajouté plus tard

Réponse : une bonne conception de table de données doit envisager la création d'index à dès le début, plutôt que d'attendre plus tard. Lorsque quelque chose ne va pas et affecte l'utilisation professionnelle, un nouvel index est créé pour sauver la situation, et le coût de la création ultérieure de l'index est relativement élevé. (Cela laisse une opportunité aux accidents de production de prendre racine et de germer)

Incompréhension 2 : Les champs après la condition Where sont tous indexés

Réponse : Ce malentendu est relativement courant, mais les champs après La condition Where est indexée. Il n'est pas nécessaire de créer tous les index. Un trop grand nombre d'index entraînera également une forte augmentation du nombre de fichiers d'index et n'obtiendra pas l'effet souhaité. Pour plus de détails, veuillez vous référer à la section sur la création d'un index ci-dessus.

Malentendu 3 : Le SQL simple ne nécessite pas d'indexation, et les requêtes conjointes nécessitent une indexation

Réponse : Ce malentendu doit être expliqué attentivement. De nos jours, les sociétés Internet, en particulier sous la structure B/S, l'entreprise La logique est supprimée au niveau de la couche logique du code. Au niveau SQL final, il s'agit en fait de SQL simple, avec seulement quelques requêtes de connexion et davantage d'opérations sur une seule table (il y a beaucoup de logique d'écriture au niveau SQL. dans l'architecture C/S), vous avez dit que ces déclarations ne sont pas simples.

Incompréhension 4 : L'ordre de l'index conjoint est l'ordre des champs après la condition Where

Réponse : Comme nous venons de le dire, l'ordre de l'index conjoint est basé sur le préfixe le plus à gauche principe et le degré de distinction. La distinction n'a rien à voir avec l'ordre des champs après la condition Where.

Malentendu 5 : Créer de nouveaux index pour les champs avec moins de discrimination

Réponse : Créer de nouveaux index sur des champs avec moins de discrimination est fondamentalement inefficace et ajoutera également un grand nombre de fichiers d'index. ça ne vaut pas la perte ?

L'index est-il important ?

Ce qui précède présente le concept d'index MySQL et quelques techniques lors de la création d'un nouvel index. Une chose si théorique, pour les chaussures pour enfants qui ne sont pas utilisées ou qui sont relativement rarement utilisées, l'importance de l'indexation n'est peut-être pas si intuitive à l'heure actuelle, alors laissez-moi parler des pertes et des pièges que j'ai subis dans l'indexation ! C'est aussi un problème courant de ne pas créer d'index !

0. Provoque des requêtes lentes
Ce problème est un problème courant sans indexation (il y a également de nombreux détails ici, tels que : la conversion de type implicite, etc.)

1. 🎜>

Scénario :

En tant que fournisseur de services, lorsque vous vous connectez à une certaine heure, fournissez des services aux parties commerciales. Au début, je pensais qu'il s'agissait d'un simple service, et le test est terminé. Je suis toujours secrètement heureux de pouvoir enfin rentrer chez moi plus tôt aujourd'hui !

Description :

Dès son lancement effectif, la partie commerciale a demandé un appel dans l'environnement de production, et chaque demande a expiré, et les données avaient été débarquées. À ce moment-là, je ne pouvais que. J'ai examiné le code et j'ai finalement découvert qu'il y avait un problème en production. En raison d'une requête lente, cela a pris plus de 10 secondes. Vous n'imagineriez jamais à quel point cette instruction est simple. Il s'agit en fait d'une requête conditionnelle WHERE à table unique. déclaration. Vous dites que ce genre de raison rend le service indisponible. Avez-vous raison ou tort ? Êtes-vous en colère ? (C'est pourquoi je dis que pour une bonne conception de table de données, de nouveaux index doivent être pris en compte dès le début).

2. CPU du serveur de base de données à 100 %

Sur SQL avec une fréquence de requête relativement élevée, si la requête est lente en raison de la construction d'un index, le processeur du serveur de base de données sera à 100 % , affectant Mais l'ensemble du système.

Résumé

Il existe plusieurs types de problèmes mentionnés ci-dessus. Les problèmes causés par la non-établissement d'un index peuvent ralentir les requêtes et affecter l'efficacité du système. l'ensemble du système. Voir À ce stade, pensez-vous que l'indexation est importante ?

Enfin

Comme mentionné brièvement ci-dessus, qu'est-ce qu'un index ? Quelles sont ses utilisations, ainsi que quelques conseils lors de la création d'index, et soulignent également l'importance des index. L'indexation est donc si importante, comment l'éviter dans le codage quotidien ? Voici mes suggestions personnelles :

1. Lors de la création d'une table, vous devriez envisager d'ajouter des index, tels que des champs de clé étrangère, etc.

2. Après avoir écrit le SQL, assurez-vous de vérifier le plan d'exécution. Essayez d'éviter les analyses de table complètes.

3. Si vous ajoutez un index à une table existante, vous devez d'abord calculer la distinction du champ.

4. Index commun, mettre la plus grande distinction au devant.

5. Suivez le principe de priorité du préfixe de la colonne de gauche MySQL

[2]H Berenson, P. Bernstein, J. Gray, J.Melton, E. O. ' Neil et P. O'Neil. Une critique des niveaux d'isolement ANSI SQL. Dans Actes de la Conférence internationale SIGMOD sur la gestion des données, pages 1 à 10, mai 1995.
[3]Michael J. Cahill, Uwe Röhm et Alan D.Fekete. 2008. Isolation sérialisable pour les bases de données d'instantanés. Dans SIGMOD '08 : Actes de la conférence internationale ACM SIGMOD 2008 sur la gestion des données, pages 729-738, New York, NY, États-Unis. 4 ]Michael James Cahill. 2009. Isolation sérialisable pour les bases de données instantanées, Université de Sydney, École des technologies de l'information[5] A. Fekete, D. Liarokapis, E. O'Neil, P.O'Neil et D. . Shasha. Rendre l'isolement des instantanés sérialisable. www.codexueyuan.com Dans Transactions ACM sur les systèmes de bases de données, volume 39(2), pages 492 à 528, juin 2005.
Articles connexes :

mysql index--(mysql learning 2)_MySQL

Mysql-index learning (1)_MySQL

Vidéos associées :

Une brève introduction à l'indexation - un didacticiel vidéo pour vous guider dans MySQL en six jours

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!