Maison > Article > base de données > 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.
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.
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 desé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 ?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
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;
测试一下正常的带name条件查询,效率如下:
select * from tb_test where name like '1805.59281427%'
我们以name字段为例,创建前缀索引,找出最合适的prefix_length值。
首先,我们大致计算一下name
字段全列的区分度。
可以看到,结果为 0.9945
,也就是说全局不相同的数据率在99.45%
这个比例。
下面我们一起来看看,不同的prefix_length
值下,对应的数据不重复比例。
当prefix_length
为5
,区分度为0.2237
当prefix_length
为10
,区分度为0.9944
当prefix_length
为11
,区分度为0.9945
通过对比,我们发现当prefix_length
为11
,最接近全局区分度,因此可以为name
创建一个长度为11
的前缀索引,创建索引语句如下:
alter table tb_test add key(name(11));
下面,我们再试试上面那个语句查询!
创建前缀索引之后,查询效率倍增!
是不是所有的字段,都适合用前缀索引呢?
答案显然不是,在上文我们也说到了,当某个索引的字符串列很大时,创建的索引也就变得很大,为了减小索引体积,提高索引的扫描速度,使用索引的前部分字符串作为索引值,这样索引占用的空间就会大大减少,并且索引的选择性也不会降低很多,这时前缀索引显现的作用就会非常明显,前缀索引本质是索引查询性能和存储空间的一种平衡。
对于 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!