Qu'est-ce que l'index de texte intégral MySQL

Dans MySQL, l'indexation de texte intégral est une technologie permettant de trouver n'importe quelle information dans l'intégralité d'un livre ou d'un article entier stocké dans la base de données. La plupart des requêtes dont nous avons besoin peuvent être complétées par comparaison numérique, filtrage de plage, etc. Cependant, si vous souhaitez filtrer la requête par correspondance de mots clés, vous avez besoin d'une requête basée sur la similarité au lieu de la comparaison numérique précise d'origine et du texte intégral ; l'indexation est conçue pour ce scénario.

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

Présentation du concept de

La recherche en texte intégral consiste à trouver toute information dans l'intégralité d'un livre ou d'un article stocké dans la technologie de base de données. Il peut obtenir des informations sur les chapitres, sections, paragraphes, phrases, mots, etc. dans le texte intégral selon les besoins, et peut également effectuer diverses statistiques et analyses. L'indexation de texte intégral est généralement mise en œuvre via une indexation inversée.

La plupart des requêtes dont nous avons besoin peuvent être complétées par comparaison numérique, filtrage de plage, etc. Cependant, si vous souhaitez filtrer la requête par correspondance de mots clés, vous avez besoin d'une requête basée sur la similarité au lieu de la comparaison numérique précise d'origine. L'indexation de texte intégral est conçue pour ce scénario.

Vous pourriez dire que vous pouvez utiliser like + % pour obtenir une correspondance floue, pourquoi avez-vous besoin d'une indexation de texte intégral ? like + % convient lorsque le texte est relativement petit, mais il est inimaginable pour récupérer une grande quantité de données textuelles. Face à une grande quantité de données, l'indexation de texte intégral peut être N fois plus rapide que + %. La vitesse n'est pas un ordre de grandeur, mais l'indexation de texte intégral peut avoir des problèmes de précision.

Vous n'avez peut-être pas prêté attention à l'indexation de texte intégral, mais vous devez être familier avec au moins une technologie d'indexation de texte intégral : divers moteurs de recherche. Bien que les objets d'indexation des moteurs de recherche soient constitués de quantités de données extrêmement importantes et qu'il n'y ait généralement pas de base de données relationnelle derrière eux, les principes de base de l'indexation de texte intégral sont les mêmes.

Prise en charge des versions

Avant de commencer, parlons de la version de l'index en texte intégral, du moteur de stockage et de la prise en charge des types de données

1. Dans les versions précédentes de MySQL 5.6, seul le moteur de stockage MyISAM prenait en charge le texte intégral index ;
2. MySQL 5.6 et versions ultérieures, les moteurs de stockage MyISAM et InnoDB prennent en charge l'index de texte intégral ;
3. L'index de texte intégral ne peut être construit que si le type de données du champ est char, varchar, text et leurs séries.

Lorsque vous testez ou utilisez l'index de texte intégral, vous devez d'abord vérifier si votre version de MySQL, votre moteur de stockage et votre type de données prennent en charge l'index de texte intégral.

Fonctionnement de l'index de texte intégral

L'opération d'indexation peut être effectuée par simple recherche, mais je vais la répéter ici.

Créer

1. Créer un index de texte intégral lors de la création d'une table

create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    tag varchar(255),    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

1. Créer un index de texte intégral sur une table existante

create fulltext index content_tag_fulltext    on fulltext_test(content,tag);

1. Créer un index de texte intégral via le SQL déclaration ALTER TABLE

alter table fulltext_test    add fulltext index content_tag_fulltext(content,tag);

Modifier

Modifier un O, le supprimer et reconstruire directement.

Supprimer

1. Utilisez directement DROP INDEX pour supprimer l'index de texte intégral

drop index content_tag_fulltext    on fulltext_test;

1. Supprimez l'index de texte intégral via l'instruction SQL ALTER TABLE

alter table fulltext_test    drop index content_tag_fulltext;

Utiliser l'index de texte intégral

et couramment utilisé Pour une utilisation de correspondance floue comme + % Different, l'index de texte intégral a son propre format de syntaxe, utilisez la correspondance et contre des mots-clés, tels que

select * from fulltext_test 
    where match(content,tag) against('xxx xxx');

Remarque : les colonnes spécifiées dans match() La fonction doit être exactement la même que les colonnes spécifiées dans l'index de texte intégral, sinon une erreur sera signalée et l'index de texte intégral ne peut pas être utilisé car l'index de texte intégral n'enregistre pas de quelle colonne provient le mot-clé. Si vous souhaitez utiliser un index de texte intégral pour une colonne, créez un index de texte intégral distinct pour cette colonne.

Testez l'index de texte intégral

Ajoutez des données de test

Avec les connaissances ci-dessus, vous pouvez tester l'index de texte intégral.

Créez d'abord la table de test et insérez les données de test

create table test (
    id int(11) unsigned not null auto_increment,
    content text not null,    primary key(id),
    fulltext key content_index(content)
) engine=MyISAM default charset=utf8;insert into test (content) values ('a'),('b'),('c');insert into test (content) values ('aa'),('bb'),('cc');insert into test (content) values ('aaa'),('bbb'),('ccc');insert into test (content) values ('aaaa'),('bbbb'),('cccc');

Exécutez la requête suivante selon la syntaxe d'utilisation de l'index de texte intégral

select * from test where match(content) against('a');select * from test where match(content) against('aa');select * from test where match(content) against('aaa');

Selon notre pensée inertielle, 4 enregistrements devraient être affichés, mais le résultat est que il n'y a même pas 1 enregistrement, seulement Ce n'est qu'en exécutant la requête ci-dessous

select * from test where match(content) against('aaaa');

que l'enregistrement aaaa sera trouvé.

Pourquoi ? Il existe de nombreuses raisons à ce problème, la plus courante étant la longueur minimale de recherche. De plus, lors de l'utilisation d'un index de texte intégral, il doit y avoir au moins 4 enregistrements dans la table de test, sinon des résultats inattendus se produiront.

L'index de texte intégral dans MySQL a deux variables, la longueur de recherche minimale et la longueur de recherche maximale. Les mots dont la longueur est inférieure à la longueur de recherche minimale et supérieure à la longueur de recherche maximale ne seront pas indexés. En termes simples, si vous souhaitez utiliser la recherche par index en texte intégral pour un mot, la longueur du mot doit être comprise dans la plage des deux variables ci-dessus.

Les valeurs par défaut de ces deux peuvent être visualisées à l'aide de la commande suivante

show variables like '%ft%';

可以看到这两个变量在 MyISAM 和 InnoDB 两种存储引擎下的变量名和默认值

// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;

// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;

可以看到最小搜索长度 MyISAM 引擎下默认是 4，InnoDB 引擎下是 3，也即，MySQL 的全文索引只会对长度大于等于 4 或者 3 的词语建立索引，而刚刚搜索的只有 aaaa 的长度大于等于 4。

配置最小搜索长度

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容

[mysqld]innodb_ft_min_token_size = 1ft_min_word_len = 1

然后重启 MySQL 服务器，并修复全文索引。注意，修改完参数以后，一定要修复下索引，不然参数不会生效。

两种修复方式，可以使用下面的命令修复

repair table test quick;

或者直接删掉重新建立索引，再次执行上面的查询，a、aa、aaa 就都可以查出来了。

但是，这里还有一个问题，搜索关键字 a 时，为什么 aa、aaa、aaaa 没有出现结果中，讲这个问题之前，先说说两种全文索引。

两种全文索引

自然语言的全文索引

默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。上面提到的，测试表中必须有 4 条以上的记录，就是这个原因。

这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。

布尔全文索引

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册

• + 必须包含该词
• - 必须不包含该词
• > 提高该词的相关性，查询的结果靠前
• < 降低该词的相关性，查询的结果靠后
• (*)星号 通配符，只能接在词后面

对于上面提到的问题，可以使用布尔全文索引查询来解决，使用下面的命令，a、aa、aaa、aaaa 就都被查询出来了。

select * test where match(content) against('a*' in boolean mode);

总结

好了，差不多写完了，又到了总结的时候。

MySQL 的全文索引最开始仅支持英语，因为英语的词与词之间有空格，使用空格作为分词的分隔符是很方便的。亚洲文字，比如汉语、日语、汉语等，是没有空格的，这就造成了一定的限制。不过 MySQL 5.7.6 开始，引入了一个 ngram 全文分析器来解决这个问题，并且对 MyISAM 和 InnoDB 引擎都有效。

事实上，MyISAM 存储引擎对全文索引的支持有很多的限制，例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等，这使得 MyISAM 的全文索引对于很多的应用场景并不适合。所以，多数情况下的建议是使用别的解决方案，例如 Sphinx、Lucene 等等第三方的插件，亦或是使用 InnoDB 存储引擎的全文索引。

几个注意点

1. 使用全文索引前，搞清楚版本支持情况；
2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题；
3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引；
4. 对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

【相关推荐：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!