Qu'est-ce qu'un index de préfixe dans MySQL-tutoriel mysql-php.cn

Dans MySQL, l'index de préfixe est un type d'index spécial qui indexe les premiers caractères du texte ; ce type d'index peut réduire la taille de l'index dans une certaine mesure et gérer plus efficacement certaines opérations de requête spécifiques. Dans des circonstances normales, la longueur de l'index doit être aussi courte que possible, car les index courts peuvent réduire la taille de l'index. Cependant, si la longueur de l'index est trop courte, une défaillance de l'index peut se produire, entraînant ainsi une efficacité de requête plus lente lors de l'utilisation. index de préfixe Ce faisant, vous devez choisir la longueur d'index appropriée en fonction de la situation spécifique.

Qu'est-ce qu'un index de préfixe dans MySQL

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

1. Qu'est-ce qu'un index de préfixe ?

MySQL est un système de gestion de base de données relationnelle largement utilisé qui offre une grande efficacité et flexibilité dans le stockage et la récupération de données. Dans MySQL, un index est une structure de données utilisée pour accélérer les opérations de requête, ce qui peut grandement améliorer la vitesse et l'efficacité des requêtes. L'index de préfixe est un type d'index spécial dans MySQL, qui peut gérer plus efficacement certaines opérations de requête spécifiques.

L'index dit préfixe, pour parler franchement, consiste à indexer les premiers caractères du texte (Plus précisément, ces caractères sont spécifiés lors de la création de l'index Par exemple, les 10 premiers caractères du nom du produit). sont utilisés pour construire l'index. C'est ainsi qu'il est construit. L'index est plus petit et l'efficacité des requêtes est plus rapide !

L'indexation des préfixes signifie indexer uniquement la partie préfixe de la chaîne, pas la chaîne entière. Ce type d'index peut réduire la taille de l'index dans une certaine mesure, par exemple, si vous avez une table contenant dix millions de chaînes, chaque chaîne comporte 100 caractères, si vous utilisez la chaîne complète comme index, alors l'index La taille sera très important et l'efficacité des requêtes sera très faible. Mais si vous indexez uniquement les 10 premiers caractères de la chaîne, la taille de l'index sera considérablement réduite et l'efficacité des requêtes sera considérablement améliorée.

C'est quelque peu similaire à l'utilisation de la fonction Left sur les champs dans Oracle pour créer un index de fonction, sauf que l'index de préfixe de MySQL complète automatiquement la correspondance en interne pendant la requête et n'a pas besoin d'utiliser la fonction Left.

Dans MySQL, l'utilisation d'un index de préfixe nécessite de spécifier la longueur de l'index. Normalement, la longueur de l'index doit être aussi courte que possible, car des index courts peuvent réduire la taille de l'index. Cependant, si la longueur de l'index est trop courte, une défaillance de l'index peut se produire, ce qui ralentit l'efficacité des requêtes. Par conséquent, lorsque vous utilisez des index de préfixe, vous devez choisir la longueur d'index appropriée en fonction de la situation spécifique.

2. Pourquoi utiliser l'index de préfixe

Certains étudiants peuvent se demander pourquoi ne pas indexer l'ensemble du champ ?

De manière générale, lorsque la quantité de données dans un certain champ est trop importante et que la requête est très fréquente, l'utilisation d'un préfixe d'index peut réduire efficacement la taille du fichier d'index, permettant à chaque page d'index d'enregistrer davantage de valeurs d'index. vitesse des requêtes d’index.

Par exemple, certains noms de magasins clients sont très longs et d'autres très courts. Si l'index est entièrement construit avec une couverture complète, l'espace de stockage de l'index peut être très grand. Certaines tables peuvent même avoir un grand nombre de. index si beaucoup d'entre eux sont créés. L'espace de stockage de l'index est beaucoup plus grand que l'espace de stockage de la table de données. Par conséquent, pour ce champ avec du texte long, nous pouvons intercepter les premiers caractères pour construire l'index. Dans une certaine mesure, il peut non seulement répondre aux exigences d'efficacité des requêtes de données et économiser de l'espace de stockage d'index.

Mais d'un autre côté, l'index de préfixe a aussi ses inconvénients. Dans MySQL, l'index de préfixe ne peut pas être utilisé pour ORDER BY et GROUP BY, ni pour l'analyse de couverture lorsque la chaîne elle-même peut être relativement longue et que la chaîne elle-même peut être relativement longue. les premiers caractères sont exactement les mêmes, à l'heure actuelle, les avantages de l'index de préfixe ne sont plus évidents et il n'est pas nécessaire de créer un index de préfixe.

Cela revient donc à la notion de

sélectivité dans l'indexation !

Concernant la sélectivité des index des tables de base de données, je l'expliquerai dans un article séparé. Il faut juste retenir une chose :

Plus la sélectivité de l'index est élevée, plus l'efficacité des requêtes est élevée, car un index hautement sélectif peut permettre. MySQL pour effectuer des recherches lors de la recherche. Filtrez plus de lignes et interrogez les données plus rapidement !

Lorsque les premiers chiffres d'un certain contenu de champ sont très distinctifs, l'utilisation d'un index de préfixe à ce moment peut atteindre un coût très élevé en termes de performances de requête et de stockage d'espace.

Alors la question est, comment créer un index de préfixes ?

3. Comment créer un index de préfixe

La façon de créer un index de préfixe est très simple. Vous pouvez le créer de la manière suivante !

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

Copier après la connexion

Le paramètre

signifie la longueur du préfixe. Il est généralement confirmé des manières suivantes : prefix_length

La première étape consiste à calculer la discrimination de la colonne entière d'un champ.

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

Copier après la connexion

La deuxième étape consiste à calculer la longueur du préfixe qui ressemble le plus à la discrimination de la colonne entière

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

Copier après la connexion

最后，不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是我们想要的值。

下面以某个测试表为例，数据体量在 100 万以上，表结构如下！

CREATE TABLE `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Copier après la connexion