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

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.

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.

sélectivité dans l'indexation !

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.

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

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

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

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

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

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;

select * from tb_test where name like '1805.59281427%'

alter table tb_test add key(name(11));