Was ist ein MySQL-Volltextindex?

In MySQL ist die Volltextindizierung eine Technologie zum Auffinden aller in der Datenbank gespeicherten Informationen im gesamten Buch oder im gesamten Artikel. Die meisten der von uns benötigten Abfragen können durch numerischen Vergleich, Bereichsfilterung usw. abgeschlossen werden. Wenn Sie die Abfrage jedoch durch Schlüsselwortübereinstimmung filtern möchten, benötigen Sie eine Abfrage, die auf Ähnlichkeit basiert, und nicht auf dem ursprünglichen präzisen numerischen Vergleich und Volltext Die Indizierung ist für dieses Szenario konzipiert.

Die Betriebsumgebung dieses Tutorials: Windows7-System, MySQL8-Version, Dell G3-Computer.

Einführung in das Konzept von

---

Mit der Volltextsuche werden alle Informationen im gesamten Buch oder Artikel gefunden, die in der Datenbanktechnologie gespeichert sind. Es kann bei Bedarf Informationen zu Kapiteln, Abschnitten, Absätzen, Sätzen, Wörtern usw. im Volltext abrufen und auch verschiedene Statistiken und Analysen durchführen. Die Volltextindizierung wird im Allgemeinen durch invertierte Indizes implementiert.

Die meisten der von uns benötigten Abfragen können durch numerischen Vergleich, Bereichsfilterung usw. abgeschlossen werden. Wenn Sie die Abfrage jedoch durch Schlüsselwortübereinstimmung filtern möchten, benötigen Sie eine Abfrage, die auf Ähnlichkeit basiert, und nicht auf dem ursprünglichen präzisen numerischen Vergleich. Für dieses Szenario ist die Volltextindizierung konzipiert.

Sie sagen vielleicht, Sie können wie + % verwenden, um einen Fuzzy-Matching zu erreichen. Warum benötigen Sie eine Volltextindizierung? like + % ist geeignet, wenn der Text relativ klein ist, für den Abruf einer großen Menge an Textdaten ist dies jedoch unvorstellbar. Angesichts einer großen Datenmenge kann die Volltextindizierung N-mal schneller sein als etwa + %. Die Geschwindigkeit ist keine Größenordnung, aber die Volltextindizierung kann Probleme mit der Genauigkeit haben.

Vielleicht haben Sie sich noch nicht mit der Volltextindizierung beschäftigt, aber Sie sollten mit mindestens einer Volltextindizierungstechnologie vertraut sein: verschiedenen Suchmaschinen. Obwohl es sich bei den Indexobjekten von Suchmaschinen um extrem große Datenmengen handelt und in der Regel keine relationale Datenbank dahinter steckt, sind die Grundprinzipien der Volltextindizierung dieselben.

Versionsunterstützung

Bevor wir beginnen, sprechen wir über die Volltextindexversion, die Speicher-Engine und die Datentypunterstützung

1. In früheren Versionen von MySQL 5.6 unterstützte nur die MyISAM-Speicher-Engine Volltext index;
2. MySQL 5.6 und spätere Versionen, sowohl MyISAM- als auch InnoDB-Speicher-Engines unterstützen Volltextindizes;
3. Volltextindizes können nur erstellt werden, wenn der Datentyp des Felds char, varchar, text und ihre Serien ist.

Beim Testen oder Verwenden des Volltextindex müssen Sie zunächst prüfen, ob Ihre MySQL-Version, Speicher-Engine und Ihr Datentyp den Volltextindex unterstützen.

Bedienung des Volltextindex

---

Der Indexierungsvorgang kann durch einfaches Suchen durchgeführt werden, aber ich werde ihn hier wiederholen.

Erstellen

1. Erstellen Sie beim Erstellen einer Tabelle einen Volltextindex.

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. Erstellen Sie einen Volltextindex für eine vorhandene Tabelle.

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

1. Erstellen Sie einen Volltextindex über SQL Anweisung ALTER TABLE

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

Ändern

Ändern Sie ein O, löschen Sie es und erstellen Sie es direkt neu.

Löschen

1. Verwenden Sie DROP INDEX direkt, um den Volltextindex zu löschen

1. und häufig verwendet Für Fuzzy-Matching verwenden Sie z. B. + % Anders, der Volltextindex verfügt über ein eigenes Syntaxformat. Verwenden Sie die Match- und Gegenschlüsselwörter wie

drop index content_tag_fulltext    on fulltext_test;

Hinweis: Die im ​​angegebenen Spalten passen( )-Funktion muss genau mit den im Volltextindex angegebenen Spalten übereinstimmen, andernfalls wird ein Fehler gemeldet. Der Volltextindex kann nicht verwendet werden, da der Volltextindex nicht aufzeichnet, aus welcher Spalte das Schlüsselwort stammt. Wenn Sie einen Volltextindex für eine Spalte verwenden möchten, erstellen Sie einen separaten Volltextindex für diese Spalte.

---

Testen Sie den Volltextindex.

Testdaten hinzufügen.

---

Mit den oben genannten Kenntnissen können Sie den Volltextindex testen.

Erstellen Sie zuerst die Testtabelle und fügen Sie die Testdaten ein.

alter table fulltext_test    drop index content_tag_fulltext;

Führen Sie die folgende Abfrage gemäß der Verwendungssyntax des Volltextindex aus Es gibt nicht einmal einen Datensatz, nur wenn die folgende Abfrage ausgeführt wird

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

wird der Datensatz

aaaa

gefunden.

Warum? Es gibt viele Gründe für dieses Problem, der häufigste davon wird durch die „minimale Suchlänge“ verursacht. Darüber hinaus müssen bei Verwendung des Volltextindex mindestens 4 Datensätze in der Testtabelle vorhanden sein, da sonst unerwartete Ergebnisse auftreten.

Der Volltextindex in MySQL verfügt über zwei Variablen, die minimale Suchlänge und die maximale Suchlänge. Wörter, deren Länge kleiner als die minimale Suchlänge und größer als die maximale Suchlänge ist, werden nicht indiziert. Für Laien ausgedrückt: Wenn Sie die Volltextindexsuche für ein Wort verwenden möchten, muss die Länge des Worts im Bereich der beiden oben genannten Variablen liegen.

Die Standardwerte dieser beiden können mit dem folgenden Befehl angezeigt werden

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视频教程】

Das obige ist der detaillierte Inhalt vonWas ist ein MySQL-Volltextindex?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!