communauté Apprendre Bibliothèque d'outils Loisirs
recherche
Français
Maison > base de données > tutoriel mysql > Parlons de la façon dont l'index de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

Parlons de la façon dont l'index de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

青灯夜游
Libérer： 2022-11-02 13:36:47
original
2807 Les gens l'ont consulté

Parlons de la façon dont l'index de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

Requête floue, telle que l'interrogation d'utilisateurs dont le nom contient "xiao", la manière courante d'écrire est comme "%xiao%", elle analysera toute la table dans MySQL, la quantité de données est petite et la totalité L'analyse des tables est également très rapide. À mesure que les données augmentent, elles ralentiront et ES sera très lourd. Cet article vous présentera la solution pour ralentir les requêtes à correspondance floue : l'index de texte intégral MySQL.

Exigences

Besoin d'une correspondance floue pour interroger un mot

select * from t_phrase Where LOCATE('chang',phrase) = 0; ) > 0;

select * from t_chinese_phrase which phrase like '%长%'

expliquez et jetez un œil au plan d'exécution

Il ressort des résultats d'explication que même si nous J'ai construit un index pour la phrase, mais lors de l'interrogation, l'index n'est pas valide.

Raison : L'index de MySQL est une structure arborescente B+. L'utilisation par InnoDB de "%xx" lors d'une requête floue de données entraînera l'échec de l'index (je n'entrerai pas dans les détails ici)Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

Du point de vue du temps de requête, le temps passé : 90 ms.

Volume de données actuel : 93230 (9,3 W) nécessite déjà 90 ms. Ce temps n'est pas acceptable. Si la quantité de données augmente, ce temps continuera à augmenter.

Solution :

Lorsque la quantité de données n'est pas importante, utilisez l'index de texte intégral de MySQL ;

Lorsque la quantité de données est relativement importante ou que l'index de texte intégral de MySQL n'est pas à la hauteur des attentes, vous pouvez envisagez d'utiliser ES

Ce qui suit concerne principalement l'index de texte intégral MySQL


Introduction à l'index de texte intégral

1 Historique de développement

L'index de texte intégral de l'ancienne version de MySQL peut. ne doit être utilisé que sur les champs char, varchar et text du moteur de stockage MyISAM.

  • Le moteur InnoDB sur MySQL5.6.24 a également ajouté l'indexation en texte intégral.

  • 2. Index en texte intégral

Recherche en texte intégral (Recherche en texte intégral) est une technologie permettant de trouver toute information de contenu dans l'intégralité d'un livre ou d'un article entier stocké dans la base de données. Il peut obtenir des informations sur les chapitres, les sections, les paragraphes, les mots, etc. dans le texte intégral selon les besoins, et peut également effectuer diverses statistiques et analyses

  • 3. Créez un index de texte intégral
Si vous devez définir. Si vous créez un index de texte intégral pour une grande quantité de données, il est recommandé de l'ajouter d'abord. Les données sont ensuite indexées.

1. Créez un index de texte intégral lors de la création d'une table
create table 表名(
字段名1,
字段名2,
字段名3,
字段名4,
FULLTEXT full_index_name (字段名)
)ENGINE=InnoDB;
Copier après la connexion

2 Ajoutez un index de texte intégral à une table existante

créez le nom de l'index de texte intégral sur le nom de la table (nom du champ) ;</code. ><p></p>par exemple :<p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false">create table t_word ( id int unsigned auto_increment comment &amp;#39;自增id&amp;#39; primary key, uid char(32) not null comment &amp;#39;32位唯一id&amp;#39;, word varchar(256) null comment &amp;#39;英文单词&amp;#39;, translate varchar(256) null ); create fulltext index full_idx_translate on t_word (translate); create fulltext index full_idx_word on t_word (word); INSERT INTO t_word (id, uid, word, translate) VALUES (1, &amp;#39;9d592499c65648b0a9519206688ef3f9&amp;#39;, &amp;#39;lion&amp;#39;, &amp;#39;狮子&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (2, &amp;#39;ce26ac4239514bc6af481bcb1d9b67df&amp;#39;, &amp;#39;panda&amp;#39;, &amp;#39;熊猫&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (3, &amp;#39;a7d6042853c44904b68275daafb44702&amp;#39;, &amp;#39;tiger&amp;#39;, &amp;#39;老虎&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (4, &amp;#39;f13bd0a8ecea44fc9ade1625eeb4cc3c&amp;#39;, &amp;#39;goat&amp;#39;, &amp;#39;山羊&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (5, &amp;#39;27d5cbfc93a046388d712085e567474f&amp;#39;, &amp;#39;sheep&amp;#39;, &amp;#39;绵羊&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (6, &amp;#39;ed35df138cf348aa937781be8ee21cbf&amp;#39;, &amp;#39;lamb&amp;#39;, &amp;#39;羊羔&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (7, &amp;#39;fba5861d9527440990276e999f47ef8f&amp;#39;, &amp;#39;buffalo&amp;#39;, &amp;#39;水牛&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (8, &amp;#39;3a72e76f210841b1939fff0d3d721375&amp;#39;, &amp;#39;bull&amp;#39;, &amp;#39;公牛&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (9, &amp;#39;272e0b28ea7a48248a86f17533bf9943&amp;#39;, &amp;#39;cow&amp;#39;, &amp;#39;母牛&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (10, &amp;#39;47127adface54e418e4c1b9980af6d16&amp;#39;, &amp;#39;calf&amp;#39;, &amp;#39;小牛&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (11, &amp;#39;10592499c65648b0a9519206688ef3f9&amp;#39;, &amp;#39;little lion&amp;#39;, &amp;#39;小狮子&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (12, &amp;#39;1bf095110b634a01bee5b31c5ee7ee0c&amp;#39;, &amp;#39;little cow&amp;#39;, &amp;#39;母牛&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (13, &amp;#39;4813e588cde54c30bd65bfdbb243ad1f&amp;#39;, &amp;#39;little calf&amp;#39;, &amp;#39;小小牛&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (14, &amp;#39;5e377e281ad344048b6938a638b78ccb&amp;#39;, &amp;#39;little bull&amp;#39;, &amp;#39;小公牛&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (15, &amp;#39;2855ad0da2964c7682c178eb8271f13d&amp;#39;, &amp;#39;little buffalo&amp;#39;, &amp;#39;小水牛&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (16, &amp;#39;72f24c9a77644d57a36f3bdf2b8116b0&amp;#39;, &amp;#39;little lamb&amp;#39;, &amp;#39;小羊羔&amp;#39;); INSERT INTO t_word (id, uid, word, translate) VALUES (17, &amp;#39;2d592499c65648b0a9519206688ef3f9&amp;#39;, &amp;#39;I&amp;#39;&amp;#39;m a big lion&amp;#39;, &amp;#39;我是一只大狮子&amp;#39;);</pre><div class="contentsignin">Copier après la connexion</div></div> </p>3. Supprimer l'index de texte intégral<p><code>create fulltext index 索引名称 on 表名(字段名)；

eg:

MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
    IN NATURAL LANGUAGE MODE
    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
    | IN BOOLEAN MODE
    | WITH QUERY EXPANSION
}
Copier après la connexion

3、删除全文索引

alter table 表名 drop index 索引名; modifier le nom de la table supprimer le nom de l'index ;

4.1. EN MODE LANGAGE NATUREL

Le mode langage naturel est MySQL

par défaut

mode de recherche en texte intégral. Le mode langage naturel ne peut pas utiliser d'opérateurs et ne peut pas spécifier de requêtes complexes telles que des mots-clés qui doivent apparaître ou ne doivent pas apparaître. 

// 默认是使用 in natural language mode
select * from t_word where match(word) against (&#39;lion&#39;);
// 或者 显示写
select * from t_word where match(word) against (&#39;lion&#39; in natural language mode);
Copier après la connexion
Copier après la connexion
Les résultats sont les suivants :

4.2 EN MODE BOOLÉEN

Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

Mode BOOLÉEN

Vous pouvez utiliser des opérateurs, qui peuvent prendre en charge des requêtes complexes telles que spécifier que les mots-clés doivent apparaître ou ne doivent pas apparaître ou si le le poids du mot-clé est élevé ou faible. Il est recommandé d'utiliser le mode booléen

Opérateur

Descriptionest videPar défaut, contient le mot +inclure, ce mot doit exister. -Exclusion, les mots ne doivent pas apparaître. >(supérieur au signe)Incluez et augmentez la valeur de classement, les résultats de la requête seront plus élevés<Incluez et réduisez la valeur de classement, les résultats de la requête seront inférieurs ()Regroupez les mots en sous-expressions (leur permettant d'être inclus, exclus, classés, etc. en tant que groupe). ~La valeur de classement des mots négatifs. *Le joker est à la fin du mot. "" Définissez une phrase (par opposition à une liste de mots individuels, où la phrase entière correspond pour inclure ou exclure).

示例：

// 默认是使用 in natural language mode
select * from t_word where match(word) against (&#39;lion&#39;);
// 或者 显示写
select * from t_word where match(word) against (&#39;lion&#39; in natural language mode);
Copier après la connexion
Copier après la connexion

Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

// 排除包含lion记录、查询出包含cow或者little的记录，提升包含calf单词的排名，降低包含cow记录的排名，查询出以go开头的记录
select * from t_word where match(word) against (&#39;-lion cow little >calf <cow  go*&#39; in boolean mode) ;
Copier après la connexion

Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

好像问题都解决了， 但是问题才刚开始

回到最开始的需求，我想模糊搜索

select * from t_word where  match(word) against(&#39;lio&#39; in boolean mode);
Copier après la connexion

预期值：把包含lion的都查询出来 实际结果：啥都没有。

Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

全匹配查询的时候能查询出来

select * from t_word where  match(translate) against(&#39;小水牛&#39; in boolean mode);
Copier après la connexion

Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

只查询部分查询不出来。如：下面只查询 "小水" 或者"水牛" 都没有数据

select * from t_word where  match(translate) against(&#39;小水&#39; in boolean mode);
Copier après la connexion

Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

奇怪了，这咋没出来呢？

全文索引默认是只按照空格进行分词的，所以当我完整的单个单词去查询的时候是能查出来的。但是使用部分单词去查询或者使用部分中文去查询时，是查询不出来数据的,像中文需要使用中文分词器进行分词。

中文分词与全文索引

InnoDB默认的全文索引parser非常合适于Latin，因为Latin是通过空格来分词的。但对于像中文，日文和韩文来说，没有这样的分隔符。一个词可以由多个字来组成，所以我们需要用不同的方式来处理。在MySQL 5.7.6中我们能使用一个新的全文索引插件来处理它们：N-gram parser。

什么是N-gram？

在全文索引中，n-gram就是一段文字里面连续的n个字的序列。例如，用n-gram来对“齿轮传动”来进行分词，得到的结果如下：

N=1 : &#39;齿&#39;, &#39;轮&#39;, &#39;传&#39;, &#39;动&#39;;
N=2 : &#39;齿轮&#39;, &#39;轮传&#39;, &#39;传动&#39;;
N=3 : &#39;齿轮传&#39;, &#39;轮传动&#39;;
N=4 : &#39;齿轮传动&#39;;
Copier après la connexion

这个上面这个N是怎么去配置的？

查一下目前的值

show variables like &#39;%token%&#39;;
Copier après la connexion

Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

参数解析:

innodb_ft_min_token_size
默认3，表示最小3个字符作为一个关键词，增大该值可减少全文索引的大小
innodb_ft_max_token_size
默认84，表示最大84个字符作为一个关键词，限制该值可减少全文索引的大小
ngram_token_size
默认2，表示2个字符作为内置分词解析器的一个关键词,合法取值范围是1-10，如对“abcd”建立全文索引，关键词为’ab’，‘bc’，‘cd’ 当使用ngram分词解析器时，innodb_ft_min_token_size和innodb_ft_max_token_size 无效

修改方式

方式1： 在my.cnf中修改/添加参数

[mysqld]ngram_token_size = 1
Copier après la connexion

方式2： 修改启动参数

mysqld --ngram_token_size=1复制代码
Copier après la connexion

参数均不可动态修改，修改后需重启MySQL服务，并重新建立全文索引

实际使用

初始化测试数据

这里只提供部分测试数据，我下面sql使用全量数据，数据对不上

create table t_chinese_phrase
(
    id     int unsigned auto_increment comment &#39;id&#39;
        primary key,
    phrase varchar(32) not null comment &#39;词组&#39;
)
    collate = utf8mb4_general_ci;

INSERT INTO t_chinese_phrase (id, phrase) VALUES (278911, &#39;阿昌族&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (279253, &#39;八一南昌起义&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (282316, &#39;昌明&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (282317, &#39;昌盛&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (282318, &#39;昌言&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (286534, &#39;东昌纸&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (291525, &#39;海昌蓝&#39;);
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (346682, &#39;繁荣昌盛&#39;);
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (282317, &#39;昌盛&#39;);
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (287738, &#39;繁盛&#39;);
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (287736, &#39;繁荣&#39;);
Copier après la connexion

添加索引

mysql 全文索引使用倒排索引为 full inverted index
结构：{单词，(单词所在文档的ID，单词在具体文件中的位置)}

添加索引：

alter  table t_chinese_phrase add fulltext ful_phrase (phrase) with parser ngram;
Copier après la connexion

建完索引，我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE和INFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档，没有如我们所期望的那样出现在查询结果中，那么这个词可能是因为某些原因不在全文索引里面。比如，它含有stopword，或者它的大小小于ngram_token_size等等。这个时候我们就可以通过查询这两个表来确认。下面是一个简单的例子：

# test: 库名  t_chinese_phrase： 表名字
SET GLOBAL innodb_ft_aux_table="test/t_chinese_phrase";
# 查询分词情况
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
# 查询分词情况
select * from information_schema.innodb_ft_index_table;
Copier après la connexion

查询结果如下：

Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

因为我们上面设置了分词数是1，所以，可以看到都是按照一个词进行分词的。

字段解析：
FIRST_DOC_ID ：word第一次出现的文档ID
LAST_DOC_ID : word最后一次出现的文档ID
DOC_COUNT ：含有word的文档个数
DOC_ID ：当前文档ID
POSITION : word 当在前文档ID的位置

查询

1、使用自然语言模式 NATURAL LANGUAGE MODE 查询

在自然语言模式（NATURAL LANGUAGE MODE)下，文本的查询被转换为n-gram分词查询的并集。

例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘繁 荣 昌 盛’)。下面一个例子：

SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST (&#39;繁荣昌盛&#39; in natural language mode) ;
Copier après la connexion

Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

2、使用布尔模式（BOOLEAN MODE）查询

布尔模式（BOOLEAN MODE）文本查询被转化为n-gram分词的短语查询

例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘”繁荣昌盛“’)。下面一个例子：

SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST (&#39;繁荣昌盛&#39; in boolean  mode) ;
Copier après la connexion

1Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

实际使用

回到我们最开始的查询需求，看看实际的效果

查询包含了“昌”的数据

SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST (&#39;昌&#39; IN boolean  MODE) ;
SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST (&#39;昌&#39; ) order by id asc;
Copier après la connexion

1Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

可以看到结果：目前“昌”在任意位置都能被查询到。

查询执行计划如下：

1Parlons de la façon dont lindex de texte intégral MySQL résout le problème des requêtes lentes, comme celles à correspondance floue.

耗时31ms（不走索引是90ms），耗时差不多是之前的1/3。

注意点

1、自然语言全文索引创建索引时的字段需与查询的字段保持一致，即MATCH里的字段必须和FULLTEXT里的一模一样；

2、自然语言检索时，检索的关键字在所有数据中不能超过50%（即常见词），则不会检索出结果。可以通过布尔检索查询；

3、在mysql的stopword中的单词检索不出结果。可通过

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD
Copier après la connexion

查询所有的stopword。遇到这种情况，有两种解决办法：

（1）stopword一般是mysql自建的，但可以通过设置ft_stopword_file变量为自定义文件，从而自己设置stopword，设置完成后需要重新创建索引。但不建议使用这种方法；

（2）使用布尔索引查询。

4、小于最短长度和大于最长长度的关键词无法查出结果。可以通过设置对应的变量来改变长度限制，修改后需要重新创建索引。

myisam引擎下对应的变量名为ft_min_word_len和ft_max_word_len

innodb引擎下对应的变量名为innodb_ft_min_token_size和innodb_ft_max_token_size

5、MySQL5.7.6之前的版本不支持中文，需使用第三方插件

6、全文索引只能在 InnoDB（MySQL 5.6以后） 或 MyISAM 的表上使用，并且只能用于创建 char,varchar,text 类型的列。

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

Étiquettes associées：
mysql
source：juejin.cn
Article précédent：Parlons de la façon dont GitHub rend MySQL hautement disponible Article suivant：Partage de cas de requête multi-tables de base MySQL
Déclaration de ce site Web
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
Derniers articles par auteur
Derniers numéros
Comment afficher uniquement la valeur la plus basse d'un jeu de résultats (MYSQL) J'ai la déclaration suivante : selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'f...
Depuis 2024-04-06 21:44:19
0
1
603
Calculer la somme des champs dans une autre table à l'aide d'une requête MySQL SQL J'ai un schéma comme celui-ci : Table utilisateur avec les attributs "user_id" e...
Depuis 2024-04-06 19:39:29
0
1
441
MySQL : "Sélectionnez le texte de... comme <variable ici ou sous-requête>" J'ai le tableau suivant avec les données suivantes : id texte langue 1 texte allemand alle...
Depuis 2024-04-06 19:25:54
0
2
529
Comment regrouper et compter dans MySQL ? J'essaie d'écrire une requête qui extrait le nombre total de messages non supprimés envoyé...
Depuis 2024-04-06 18:30:17
0
1
353
MySQL récupère les données de plusieurs tables J'ai une table eg_design qui contient les colonnes suivantes : et une table eg_domains qui...
Depuis 2024-04-06 18:42:44
0
2
479
Rubriques connexes
Plus>
Recommandations populaires
Tutoriels populaires
Plus>
Tutoriels associés
Recommandations populaires
Derniers cours
Derniers téléchargements
Plus>
effets Web
Code source du site Web
Matériel du site Web
Modèle frontal