Partage de technologie

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Table des matières

Demande d'indexation

Quels index MySQL possède-t-il ?

Quelle est la différence entre un index normal et un index unique ?Lequel a les meilleures performances de mise à jour ?

Comment définir l'index de clé primaire de l'index clusterisé ? Question : Que se passera-t-il si vous ne le définissez pas ?

Quels types de champs choisissons-nous habituellement pour créer des index ?

Est-il préférable d’avoir plus d’index ?

Comment optimiser l'index ? (couvrant l'optimisation de l'index, la prévention des échecs d'index, l'incrément de clé primaire, l'optimisation de l'index de préfixe)

Si un index est créé, sera-t-il utilisé lors de l'interrogation ? (Échec de l'index, l'optimiseur sélectionne un plan d'exécution en fonction du coût)

Si je définis un champ de date de type varchar et que l'une des données est « 20230922 », et s'il existe un index sur ce champ de date, alors si la condition Where de ma requête est où time=20230922 sans guillemets simples, le l'index est toujours touché ?

La dernière version de MySQL a-t-elle résolu des cas d'échec d'index ? (Indice de fonction : la valeur calculée par la fonction peut également être indexée, mécanisme d'analyse de saut d'index (préfixe le plus à gauche))

Quel est le principe de correspondance le plus à gauche ?

À quoi dois-je faire attention lors de l'établissement d'un index conjoint ? (Placer les plus différenciés à l'extrême gauche, le principe de correspondance le plus à gauche, et ne pas utiliser l'index après la requête de plage)

Ordre de requête du principe de correspondance le plus à gauche

Qu’est-ce que le pushdown d’index ? Ajouté dans MySQL5.6 pour optimiser les requêtes de données

Comment créer un index où a>1 et b=2 et c <3 ?

(A,B,C) index conjoint sélectionnez * à partir de tbn où a=? et b dans (?,?) et c> ?

Comment créer un index conjoint où a>100 et b=100 et c=123 sont ordonnés par d ?

sélectionnez l'identifiant, le nom à partir de XX où âge > 10 et le nom comme « xx% », il existe un index commun (nom, âge), parlons du processus de requête

---

Demande d'indexation

MySQLQuels sont les index ?

J'ai appris que MySQL aclé primaireIndex, index unique, index ordinaire, index préfixe,Indice syndicalCes types d'index.

Le moteur Innodb exige que chaque table de base de données ait unclé primaireindice，Les valeurs des colonnes d'index ne sont pas autoriséesvaleur nulle。Par exemple, le champ id de la table est l'index de clé primaire

Indice unique : Assurez l’unicité de chaque ligne de données dans la colonne de données, mais autorisez les valeurs nulles.

AlorsPour les champs fréquemment interrogés, nous pouvons créer un index normal pour ce champ.，S'il y a plusieurs champs, vous pouvez envisager de créerIndice syndical,utiliserCouverture de l'indiceLes fonctionnalités améliorent l’efficacité des requêtes.

Pour les textes longs, les chaînes et autres types de champs, tels que les titres d'articles, les noms de produits, etc., nous ne pouvons indexer que la partie préfixe de ces champs, c'est-à-direCréez un index de préfixe pour réduire l'espace de stockage de l'index.

Quelle est la différence entre un index normal et un index unique ? Lequel a les meilleures performances de mise à jour ? 、

• Un index unique peut être légèrement plus rapide lors de l'interrogation d'une valeur unique, car il peut mettre fin à la recherche après avoir trouvé la première correspondance.

• Pour les opérations d'insertion et de mise à jour, un index normal peut être légèrement plus rapide car il ne nécessite pas de vérification d'unicité.

1. Les valeurs des colonnes d'index ordinaires peuvent être répétées, mais les valeurs des colonnes d'index uniques doivent être uniques. Lorsque nous insérons une valeur répétée dans un index unique, une erreur sera signalée en raison de la contrainte d'unicité.

2. Je penseLes performances de mise à jour de l'index ordinaire seront meilleures, car lorsque l'index ordinaire est mis à jour, si la page de données mise à jour ne l'est pasMémoire Si tel est le cas, vous pouvez directement mettre en cache l'opération de mise à jour dans le tampon de modification et l'opération de mise à jour sera terminée. (aucun contrôle d'unicité requis)

3. mais,L'index unique doit avoir des contraintes uniques si la page de données mise à jour ne se trouve pas dans le fichier.MémoireSi tel est le cas, vous devez lire la page de données correspondante du disque vers la mémoire pour déterminer s'il existe un conflit. Cela impliquera une randomisation du disque.IOAccéder.

4. Étant donné que les index ordinaires peuvent utiliser la fonctionnalité de tampon de modification, la mise à jour des index ordinaires est plus rapide que celle des index uniques.Accès aléatoire au disque réduit, donc les performances de mise à jour sont meilleures

index clusterisédeclé primaireComment définir l’index ? Question : Que se passera-t-il si vous ne le définissez pas ?

Lorsque InnoDB crée un index clusterisé, il sélectionnera différentes colonnes comme index selon différents scénarios :

1. S'il existe une clé primaire, la clé primaire sera utilisée par défaut comme clé d'index de l'index clusterisé.

2. S'il n'y a pas de clé primaire, sélectionnezLe premier ne contient pas Valeur NULLELa seule colonne de est la suivanteindex clusteriséclé d'index

3. En l'absence de l'un des éléments ci-dessus, InnoDB générera automatiquement une colonne rowid à incrémentation automatique implicite comme clé d'index de l'index clusterisé.

Quels types de champs choisissons-nous habituellement pour créer des index ?

Scénarios où l'indexation est applicable :

1. Les champs ont des restrictions d'unicité, tel que le code produit

2. Champs fréquemment utilisés dans les conditions de requête WHERE, ce qui peut améliorer la vitesse de requête de la table entière. Si la condition de requête n'est pas un champ, un index conjoint peut être établi.

3. Champs souvent utilisés dans GROUPBY et ORDER BY, de sorte qu'il n'est pas nécessaire de trier à nouveau lors de la recherche, car les enregistrements de l'arborescence B+ sont tous triés une fois l'index établi.

Scénarios non adaptés à l'indexation

1. Champs non utilisés dans les conditions WHERE, GROUP BY, ORDER BY, la valeur de l'index est un positionnement rapide. Si le champ ne peut pas être positionné, il n'est généralement pas nécessaire de créer un index, car l'index occupera de l'espace physique.

2. Champs peu distinctifs , il n'est pas nécessaire de créer un index, par exemple, le champ genre ne contient que des hommes et des femmes. Si les enregistrements des hommes et des femmes sont répartis uniformément dans la table de la base de données, quelle que soit la valeur recherchée, la moitié des données peut être obtenue. être obtenu.Dans ces cas-là, il vaut mieux ne pas indexer car MySQLIl y en a encore unoptimiseur de requêtes, lorsque l'optimiseur de requêtes constate qu'une certaine valeur apparaît dans un pourcentage élevé de lignes de données de la table, il ignore généralement l'index et effectueAnalyse complète du tableau。

3. Champs fréquemment mis à jour, par exemple, n'indexez pas le solde utilisateur des projets e-commerce car les champs d'indexation sont fréquemment modifiés.Maintenir B+Arbrel'ordre, une reconstruction fréquente de l'index est nécessaire, et ce processus affectera les performances de la base de données.

4. Il n'est pas recommandé d'utiliser des valeurs non ordonnées(comme la carte d'identité, l'UUID) en tant qu'index, lorsque la clé primaire est incertaine, cela entraînera une division fréquente des nœuds feuilles et une fragmentation du stockage sur disque.

• Le tableau de données est plus petit : Lorsque la quantité de données dans une table est faible ou lorsqu'une requête nécessite l'analyse d'une grande partie des données de la table, l'optimiseur de base de données peut choisir une analyse complète de la table au lieu d'utiliser un index. Dans ce cas, le coût de maintenance de l’indice peut être supérieur au gain de performance.

Est-il préférable d’avoir plus d’index ?

Non, bien que les index puissent améliorer l'efficacité des requêtes, la création d'un index supplémentaire signifie qu'un nouvel index arborescent B+ sera généré, ce qui occupera de l'espace de stockage. Surtout lorsque la quantité de données de la table est très importante, l'index prendra plus d'espace.

Plus il y a d'index, les performances d'écriture de la base de données diminueront, car chaque fois que vous ajoutez, supprimez ou modifiez la table, vous devez conserver l'ordre de chaque index d'arborescence B+.

Comment optimiser l'index ?indice de couvertureOptimiser et prévenir les échecs d'index,clé primaireOptimisation incrémentielle de l'index de préfixe)

J'ai utilisé ces méthodes d'optimisation

1. Pour SQL qui doit interroger des données dans plusieurs champs, nous pouvons créerIndice syndical, donc la méthode de requête devientindice de couverture, évitant ainsi le support de table et réduisant un grand nombre d'opérations d'E/S.

2. notreclé primaireLes indices sont de préférence des valeurs croissantes, parce que notre index stocke les données dans l'ordre, si la valeur de la clé primaire est une valeur aléatoire, cela peut entraîner un fractionnement de page. Le fractionnement de page entraînera un grand nombre de fragments de mémoire, de sorte que la structure de l'index ne sera pas compacte, ce qui entraînera un fractionnement de page. affecter l’efficacité des requêtes.

3. nous voulonsÉvitez d'écrire l'échec de l'index SQL Les instructions, telles que n'effectuent pas de correspondance floue à gauche ou à gauche sur les colonnes d'index, n'effectuent pas de calculs, de fonctions et d'opérations de conversion de type sur les index. Pour utiliser correctement les index conjoints, vous devez suivre le principe de correspondance le plus à gauche, etc.Dans la clause WHERE, si la colonne de condition avant le OU est une colonne d'index et que la colonne de condition après le OU n'est pas une colonne d'index, l'index échouera.

• Utiliser différent de (<>) ou NOT : ces opérateurs invalident généralement l'index car ils analysent la table entière.

• Opérateur OR : si OR est utilisé dans la condition de requête et que les conditions des deux côtés de OR impliquent des index différents, alors ces index ne peuvent pas être utilisés.

  • utiliser OR opérateur, siOR Les conditions des deux côtés impliquent des index différents et le moteur de base de données ne peut pas utiliser plusieurs index en même temps pour optimiser la requête dans la plupart des cas.C'estparce que OR L'opérateur n'a qu'à remplir les conditions de chaque côté, ce qui augmente la complexité de l'optimisation des requêtes.

1. Index pour une grande chaîne, on peut envisager d'utiliserindex de préfixeSeule la partie préfixe de la colonne d'index est indexée pour économiser de l'espace de stockage d'index et améliorer les performances des requêtes.

2. Il est préférable de définir l'index sur NON NUL : Afin de mieux utiliser l'index, la colonne d'index doit être définie sur la contrainte NOT NULL. Il y a deux raisons :

   1. La présence de NULL dans les colonnes d'index rendra la sélection d'index par l'optimiseur plus compliquée, ce qui rendra plus difficile l'optimisation d'opérations telles que le nombre.

   2. La valeur NULL est une valeur dénuée de sens, mais elle occupera de l'espace physique. Il existe une colonne de valeur nulle.Au moins 1 octet d'espace sera utilisé pour stocker NULL liste de valeurs

Si un index est créé, sera-t-il utilisé lors de l'interrogation (échec de l'index,optimiseurSélectionnez le plan d'exécution en fonction du coût)

Non.

1. J'ai apprisMême si la requête utilise un index, elle ne peut pas l'utiliser.

   1. Par exemple : lorsque notre instruction de requête effectue des opérations de correspondance floue gauche, de calcul d'expression, de fonction et de conversion de type implicite sur le champ d'index, l'instruction de requête ne peut pas parcourir l'index et la méthode de requête devient une analyse de table complète.

   2. Et nous utilisonsIndice syndicalLors de l'interrogation, si le principe de correspondance le plus à gauche n'est pas suivi, un échec d'index se produira également.。

2. L'optimiseur estChoisissez une méthode de requête en fonction de considérations de coût, lors de l'utilisation de l'index secondaire pour la requête, l'optimiseur calculera le coût du retour de la table et le coût de l'analyse complète de la table. Si le coût du retour de la table est trop élevé, l'optimiseur choisira de ne pas utiliser l'index, mais d'utiliser le. analyse complète du tableau.

Si je définis un champ de date de type varchar et que l'une des données est « 20230922 », et s'il existe un index sur ce champ de date, alors si la condition Where de ma requête est où time=20230922 sans guillemets simples, le l'index est toujours touché ?

N'atteindra pas l'index.

Parce que MySQL rencontreComparaison de chaînes et de nombresarrivera quandconversion de type implicite, volontéConvertir un objet chaîne en nombre, ce processus de conversion implique en réalitéfonction . Dans la requête que vous avez mentionnée, le champ de date est une chaîne, donc lorsqu'une conversion de type implicite se produit, elle sera appliquée au champ d'index de date. Si le calcul de la fonction est effectué sur l'index, l'index deviendra invalide.

Pour les colonnes d'index de type entier, par exempleid Colonne dont la valeur est stockée directement dans l'index sans que le calcul de la fonction ne se produise.Cela signifie utiliser dans la requêteidLors de l'appariement, il n'est pas nécessaire deidEffectuez des calculs ou des conversions fonctionnelles et comparez simplement des valeurs entières.

MySQLLa dernière version a-t-elle résolu des cas d'échec d'index (Fonction index :calcul de fonctionLa valeur après peut également être indexée et le mécanisme d'analyse de saut d'index (préfixe le plus à gauche))

J'ai appris que MySQL8.0 peut ajouter des champsindice de fonction, cette nouvelle fonctionnalité peut résoudre le problème de l'échec de l'index lors de l'utilisation de fonctions sur l'index.

Une autre nouvelle fonctionnalité estanalyse des sauts d'index, Avant la version 5.7, lors de l'utilisation d'un index conjoint, si le principe de correspondance le plus à gauche n'est pas respecté, un échec de l'index se produira. Cependant, après l'introduction de la fonctionnalité d'analyse par saut d'index dans la version 8.0, les index conjoints peuvent toujours être utilisés même si le principe de correspondance le plus à gauche est utilisé. n'est pas suivi.

Quel est le principe de correspondance le plus à gauche ?

Supposons qu'il existe un index conjoint (a, b, c). Son ordre de stockage est de trier d'abord par a, puis de trier par b lorsque a est identique, puis de trier par c lorsque b est identique. En raison de cette fonctionnalité, lors de l'utilisation d'index conjoints, il existe un principe de correspondance le plus à gauche. Les règles spécifiques sont :

1. L'index fédéré de MySQL commencera à partir deLa colonne d'index la plus à gauche commence à correspondre aux conditions de requête, puis correspond dans l'ordre de gauche à droite. Si les conditions de requête n'utilisent pas de colonne, toutes les colonnes à droite de la colonne ne peuvent pas être indexées.

2. Lorsqu'une colonne est utilisée dans la condition de requête,Cependant, la valeur de cette colonne contient une requête par plage, et les champs de la requête par plage peuvent être utilisésIndice syndical, mais l'index conjoint ne peut pas être utilisé dans les champs situés derrière le champ de requête de plage.

Par conséquent, lorsque nous utilisons des index conjoints, nous devons respecter le principe de correspondance le plus à gauche, sinon certains champs d'index pourraient ne pas être indexés.

ÉtablirIndice syndicalY a-t-il quelque chose auquel nous devons prêter attention ? (Les plus différenciés sont placés à l'extrême gauche, le principe de correspondance le plus à gauche, et l'index n'est pas utilisé après la requête de plage)

1. la plupartMettez les champs avec une plus grande distinction dansIndice syndicalextrême gauche, utileAméliorer l'effet de filtrage d'index, les champs tels que UUID sont plus adaptés à l'indexation ou au classement en haut de la colonne d'index commun.

2. Si un champ avec une faible discrimination est placé à l’extrême gauche de l’index conjoint, l’optimiseur de requêtes peut choisir une analyse complète de la table au lieu d’utiliser l’index.

3. Le principe de correspondance le plus à gauche de l'index conjoint, dansLorsque vous rencontrez une requête de plage (telle que >, <), la correspondance s'arrêtera, c'est-à-dire que les champs de la requête par plage peuvent utiliser l'index conjoint, mais les champs derrière le champ de requête par plage ne peuvent pas utiliser l'index conjoint.Cependant, pour les quatre requêtes de plage >=, <=, BETWEEN et comme la correspondance de préfixe, la correspondance ne s'arrêtera pas.

   1. Dans MySQL, BETWEEN contient les valeurs limites value1 et value2, similaires à >= et =<.

   2. Lien de référence https://zhuanlan.zhihu.com/p/573138586

Ordre de requête du principe de correspondance le plus à gauche

select * from T where c=1 and a=2 and b=3;

abc peut être indexé car L'ordre dans lequel les champs de condition de requête n'affectent pas, l'optimiseur MySQL nous aidera à ajuster l'ordre des requêtes des champs, afin qu'il soit également conforme au principe de correspondance le plus à gauche.

sous l'indexpousser Qu'est-ce que c'est? Ajouté dans MySQL5.6 pour optimiser les requêtes de données

La compression de l'index peut réduireindice secondaireL'opération de retour de table pendant la requête améliore l'efficacité de la requête car elle La couche serveur est responsable de certaines des choses gérées par la couche moteur de stockage.Je suis allé m'en occuper.

• Lorsque l'optimisation push-down sans conditions d'index est utilisée, le moteur de stockage récupère les données via l'index, puis les renvoie au serveur MySQL.Serveur MySQL Portez des jugements sur les conditions de filtrage.

• Lors de l'utilisation de l'optimisation push-down des conditions d'index, s'il existe certaines conditions de jugement pour les colonnes indexées, MySQL Server transmettra cette partie des conditions de jugement au moteur de stockage, puis le moteur de stockage jugera si l'index répond aux conditions transmises. Serveur MySQL. Ce n'est que lorsque l'index remplit les conditions que les données seront récupérées et renvoyées au serveur MySQL.

L'optimisation du refoulement des conditions d'index peut réduire le nombre de fois où le moteur de stockage interroge la table sous-jacente, et peut également réduire MySQL Nombre de fois où le serveur a reçu des données du moteur de stockage.

select * from t_user where age > 20 and reward = 100000;

Comment créer un index où a>1 et b=2 et c <3 ?

1. Créer (abc), (acb), (ab), (ac) un index commun, seul un index peut

2. Créer des index conjoints (cab), (cba), (ca), (cb), seul c peut indexer

3. Créer (ba) un index commun, b et a peuvent être indexés

4. Créer un index conjoint (bc), b et c peuvent être indexés

5. créer(bac) Indice syndical, b et a peuvent tous deux être indexés, mais ils sont plus lents que (ba) l'index conjoint a un avantage supplémentaire, le champ c peutindex déroulant, réduira le nombre de retours de table ;

6. créer(bca) Indice syndical, b et c peuvent être indexés, mais cela présente un avantage supplémentaire par rapport à l'index conjoint (bc), le champ a peutindex déroulant, réduira le nombre de retours de table ;

(A,B,C) indice conjoint select * from tbn where a=? and b in (?,?) and c>? Sera-t-il indexé ?

Cette requête utilisera l'index conjoint (A,B,C), car la condition est basée sur la colonne d'index A、B、C La commande arrive, ce qui constitue le scénario d’utilisation idéal.

1. pour A=?: Cette condition est une correspondance exacte. MySQL utilisera l'index pour localiser la condition qui satisfait la condition. A=? record de.

2. pour B IN (?, ?): Cette condition précise B La colonne peut prendre deux valeurs possibles. MySQL utilisera l'index pour trouver toutes les correspondancesA=? etB La colonne est un enregistrement avec l'une de ces deux valeurs.

3. pour C>? : Cette condition est une requête de plage.déjà basé surA etB Sur la base du filtre, MySQL continuera à utiliser l'index pour trouverC Enregistrements avec des valeurs de colonne supérieures à la valeur spécifiée.

où a>100 et b=100 et c=123 trier par d comment créerIndice syndical?

Je penseÉtablir Bcda en ordreIndice syndicalMieux, à ce moment les champs b et c peuvent être indexés, etd peut utiliser l'ordre d'index pour éviter le tri des fichiers (tri supplémentaire), bien que le dernier champ a ne puisse pas être indexé (a est dans le désordre), il peut être poussé vers le bas à l'aide de l'index pour réduire le nombre de retours de table.

sélectionnez l'identifiant, le nom de XX où l'âge > 10 et un nom comme « xx% »,Indice syndical(nom, âge), parlez du processus de requête

L'ordre de l'index conjoint est le nom d'abord, puis l'âge. Structurellement, il est d'abord trié par nom, puis trié par âge si les noms sont égaux.Par conséquent, l'optimiseur doit d'abord faire correspondre le nom. Le nom est une requête floue correcte à ce stade, et aucun échec d'index ne se produira, donc ce SQL peut utiliser l'indexation conjointe.

Plus précisément, seul le nom peut être indexé.Après la requête floue de droite du nom, les valeurs du champ âge ne sont pas dans l'ordre, donc l'âge ne peut pas être indexé, mais l'âge peut être indexé.index déroulant。

Les derniers champs interrogés sont id et name. Ces deux champs peuvent être trouvés sur l'index conjoint, il n'est donc pas nécessaire de renvoyer la table. Il s'agit d'une requête de couverture d'index.

La requête floue de droite de nom est une requête de plage et les champs suivants ne peuvent pas être indexés