Maison  >  Article  >  base de données  >  Qu'est-ce qu'un index de préfixe dans MySQL

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

青灯夜游
青灯夜游original
2023-04-04 15:59:463021parcourir

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));
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;
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;

最后,不断地调整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;

Quest-ce quun index de préfixe dans MySQL

Quest-ce quun index de préfixe dans MySQL

测试一下正常的带name条件查询,效率如下:

select * from tb_test where name like '1805.59281427%'

Quest-ce quun index de préfixe dans MySQL

我们以name字段为例,创建前缀索引,找出最合适的prefix_length值

首先,我们大致计算一下name字段全列的区分度。

Quest-ce quun index de préfixe dans MySQL

可以看到,结果为 0.9945,也就是说全局不相同的数据率在99.45%这个比例。

下面我们一起来看看,不同的prefix_length值下,对应的数据不重复比例。

prefix_length5,区分度为0.2237

Quest-ce quun index de préfixe dans MySQL

prefix_length10,区分度为0.9944

Quest-ce quun index de préfixe dans MySQL

prefix_length11,区分度为0.9945

Quest-ce quun index de préfixe dans MySQL

通过对比,我们发现当prefix_length11,最接近全局区分度,因此可以为name创建一个长度为11的前缀索引,创建索引语句如下:

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

下面,我们再试试上面那个语句查询!

Quest-ce quun index de préfixe dans MySQL

创建前缀索引之后,查询效率倍增

四、使用前缀索引需要注意的事项

是不是所有的字段,都适合用前缀索引呢?

答案显然不是,在上文我们也说到了,当某个索引的字符串列很大时,创建的索引也就变得很大,为了减小索引体积,提高索引的扫描速度,使用索引的前部分字符串作为索引值,这样索引占用的空间就会大大减少,并且索引的选择性也不会降低很多,这时前缀索引显现的作用就会非常明显,前缀索引本质是索引查询性能和存储空间的一种平衡。

对于 BLOB 和 TEXT 列进行索引,或者非常长的 VARCHAR 列,就必须使用前缀索引,因为 MySQL 不允许索引它们的全部长度。

但是如果某个字段内容,比如前缀部分相似度很高,此时的前缀索引显现效果就不会很明显,采用覆盖索引效果会更好!

【相关推荐:mysql视频教程

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!

Déclaration:
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn