MySQL-Volltextindex, um eine einfache Version des Suchmaschinen-Beispielcodes zu erreichen

Vorwort

• Nur Innodb- und MyISAM-Speicher-Engines können Volltextindizes verwenden (Innodb unterstützt Volltextindizes ab MySQL 5.6).
• Felder vom Typ char, varchar und text können Volltextindizes erstellen (Volltextindextyp)
• Die Volltextindizierung basiert auf Schlüsselwörtern. Um verschiedene Schlüsselwörter zu unterscheiden, müssen wir Stoppwörter verwenden.
• Englische Wörter werden durch Leerzeichen und Kommas segmentiert; die chinesische Wortsegmentierung ist unpraktisch (ich weiß nicht, wie ich verschiedene Schlüsselwörter in einem Satz unterscheiden soll)
• Integrierter Wortsegmentierungsparser ngram unterstützt Chinesisch, Japanisch, Koreanisch (Sätze in eine feste Anzahl von Phrasen aufteilen)
• Beim Schreiben großer Datenmengen in eine Tabelle ist es schneller, nach dem Schreiben der Daten einen Volltextindex zu erstellen (wodurch der Aufwand für die Indexpflege reduziert wird).
• Der invertierte Index (eine Datenstruktur) des Volltextindizierungsprinzips verwendet im Allgemeinen ein assoziatives Array, um die Zuordnung zwischen Wörtern und ihren Positionen im Dokument in einer Hilfstabelle zu speichern.

verwenden

Suche mit MATCH() ... AGAINST

match() gibt an, dass die Spalte durchsucht wird, against gibt an, dass die Zeichenfolge durchsucht wird

Sehen Sie sich die standardmäßige Wortsegmentierung an (verwenden Sie diese Wörter, um verschiedene Schlüsselwörter zu unterscheiden). Sie können die Wortsegmentierung auch anpassen, um verschiedene Schlüsselwörter zu unterscheiden.

AUSWÄHLEN * AUS information_schema.INNODB_FT_DEFAULT_STOPWORD;

wie

+----------+ 
| Wert | 
+----------+ 
| ein | 
| über | 
| an | 
| sind | 
| als | 
| bei | 
| sein | 
| von | 
| com | 
| de | 
| de | 
| für | 
| von |

Drei Arten von Volltextsuchmethoden

Suche in natürlicher Sprache

Übergeben Sie eine bestimmte Zeichenfolge, die mit MATCH AGAINST überprüft werden soll. Die Standardmethode

Boolesche Suche

Fügen Sie der Suchzeichenfolge Operatoren hinzu, z. B. bedeutet "+" muss enthalten, "-" bedeutet nicht enthalten, "*" bedeutet Platzhalter. Auch wenn die übergebene Zeichenfolge klein ist oder im Stoppwort erscheint, wird sie nicht herausgefiltert

Abfrageerweiterung Suche

Die Suchzeichenfolge wird verwendet, um eine Suche in natürlicher Sprache durchzuführen. Anschließend werden die Wörter aus den relevantesten Zeilen, die von der Suche zurückgegeben wurden, der Suchzeichenfolge hinzugefügt. Die Suche wird erneut ausgeführt. Die Abfrage gibt die Zeilen aus der zweiten Suche zurück.

Verwandte Parameter

Zugehörige Parameter konfigurieren

innodb_ft_min_token_size

Der Standardwert ist 3, was bedeutet, dass mindestens 3 Zeichen als Schlüsselwort verwendet werden können. Durch Erhöhen dieses Wertes kann die Größe des Volltextindexes verringert werden.

innodb_ft_max_token_size

Der Standardwert ist 84, was bedeutet, dass ein Schlüsselwort maximal 84 Zeichen enthalten kann. Durch die Begrenzung dieses Wertes kann die Größe des Volltextindexes reduziert werden.

ngram_token_größe

Der Standardwert ist 2, was bedeutet, dass zwei Zeichen als Schlüsselwort für den integrierten Wortsegmentierungsparser verwendet werden. Um beispielsweise einen Volltextindex für „abcd“ zu erstellen, lauten die Schlüsselwörter „ab“, „bc“, „cd“.
innodb_ft_min_token_size und innodb_ft_max_token_size haben keinen Effekt bei Verwendung des Ngram-Parsers

Beachten Sie, dass diese drei Parameter nicht dynamisch geändert werden können. Wenn Sie diese Parameter ändern, müssen Sie den MySQL-Dienst neu starten und den Volltextindex neu erstellen.

Testen Sie die InnoDB-Engine mithilfe des Volltextindex

Vorbereiten

1. Ziel

• Prüfen Sie, ob ein Artikel ein bestimmtes Schlüsselwort enthält; wie oft ein bestimmtes Schlüsselwort in einer Artikelserie vorkommt
• Überprüfen Sie, ob der Titel des Artikels ein bestimmtes Schlüsselwort enthält

2. Stellen Sie die folgenden Parameter ein, um den Festplatten-IO-Druck zu reduzieren

Setzen Sie GLOBAL sync_binlog=100;
GLOBAL festlegen innodb_flush_log_at_trx_commit=2;

3. Importieren Sie 1 kW-Daten, um die Volltextindizierung zu testen

Die Daten stammen aus der Online-Suche

Extraktionscode: iyip

4. Der Aufbau einer Artikeltabelle

CREATE TABLE `Artikel` (
 `id` bigint(10) NICHT NULL,
 `url` varchar(1024) ZEICHENSATZ latin1 NICHT NULL STANDARD '',
 `title` varchar(256) NICHT NULL STANDARD '',
 `Quelle` varchar(32) DEFAULT '' KOMMENTAR 'Wahre Quelle',
 `Schlüsselwörter` varchar(32) DEFAULT NULL,
 `publish_time` Zeitstempel NULL DEFAULT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `title_idx` (`Titel`)
) ENGINE=InnoDB

Verwenden Sie myloader, um Testdaten in mehreren Threads zu importieren

Dekomprimieren Sie zunächst die Testdaten tar -zxf mydumper_dump_article.tar.gz
Zeit myloader -u $user -p $passwd -S $socket -t 32 -d /datas/dump_article -v 3

5. Gesamtdatenvolumen und Datendatei- und Indexdateigröße nach dem Datenimport

SELECT COUNT(*) FROM `Artikel`;
+----------+
| ANZAHL(*) |
+----------+
| 10000000 |
+----------+
1 Reihe im Satz (7,85 Sek.)

SELECT Tabellenname, CONCAT(FORMAT(SUM(Datenlänge) / 1024 / 1024,2),'M') AS Datenbankdatengröße, CONCAT(FORMAT(SUM(Indexlänge) / 1024 / 1024,2),'M') AS Datenbankindexgröße, CONCAT(FORMAT(SUM(Datenlänge + Indexlänge) / 1024 / 1024 / 1024,2),'G') AS `Datenbankgröße(G)`, AVG_ROW_LENGTH, Tabellenzeilen, Aktualisierungszeit FROM information_schema.tables WHERE Tabellenschema = DATENBANK() und Tabellenname='Artikel';
+------------+----------+--------------+------------+----------------+------------+---------------------+
| Tabellenname | Datenbankdatengröße | Datenbankindexgröße | Datenbankgröße(G) | DURCHSCHNITTLICHE REIHENLÄNGE | Tabellenzeilen | Aktualisierungszeit |
+------------+----------+--------------+------------+----------------+------------+---------------------+
| Artikel | 3.710,00 M | 1.003,00 M | 4,60 G | 414 | 9388739 | 05.07.2019 15:31:37 |
+------------+----------+--------------+------------+----------------+------------+---------------------+

Erstellen eines Volltextindexes mit der Standardmethode

1. Die Tabelle verfügt bereits über ein Schlüsselwortfeld (eine kurze Beschreibung des Artikelinhalts) und "," wird als Worttrennzeichen verwendet

Wählen Sie Schlüsselwörter aus dem Artikellimit von 10 aus.
+-------------------------------------------------+
| Schlüsselwörter |
+-------------------------------------------------+
| NULL |
| NULL |
|,Ehe,Liebe|
| Frisur, Seitenscheitel, Make-up, Mode |
| Xiao A, |
| , passende Kleidung, Frauen, Mode |
| schön,weiblich|
| Valentinstag, Dongguan, Frauen |
| Haut, Hautpflege, Hautpflege, Nahrungsmittelernährung, Schönheit, Gesundheitspflege |
| Sanlitun, Peking, Mode |
+-------------------------------------------------+

2. Suche nach einem Schlüsselwort ohne Volltextindizierung

Ein vollständiger Tabellenscan ist erforderlich

Wählen Sie die Anzahl (*) aus dem Artikel aus, in dem Schlüsselwörter wie „%Fashion%“ enthalten sind.
+----------+
| Anzahl(*) |
+----------+
| 163 |
+----------+
1 Reihe im Satz (7,56 Sek.)

3. Erstellen Sie einen Volltextindex für das Schlüsselwortfeld (mit als Wortsegment)

Legen Sie innodb_ft_min_token_size in der Konfigurationsdatei my.cnf fest und starten Sie den MySQL-Dienst neu (die Mindestanzahl der als Schlüsselwort verwendeten Zeichen beträgt zwei, die Standardanzahl der als Schlüsselwort verwendeten Zeichen beträgt drei).

[mysqld]
innodb_ft_min_token_size=2

3.1 Festlegen benutzerdefinierter Stoppwörter (d. h. Wortsegmentierung)

VERWENDEN Sie MySQL;
Tabelle erstellen my_stopwords (Wert VARCHAR (30)) Engine = INNODB;
EINFÜGEN IN my_stopwords(WERT) WERT (',');
SETZEN SIE GLOBAL innodb_ft_server_stopword_table = "mysql/my_stopwords";

GLOBALE VARIABLEN ANZEIGEN, WO Variablenname IN('innodb_ft_min_token_size','innodb_ft_server_stopword_table');
+---------------------------------+--------------------+
| Variablenname | Wert |
+---------------------------------+--------------------+
| innodb_ft_min_token_size | 2 |
| innodb_ft_server_stopword_table | mysql/meine_Stoppwörter |
+---------------------------------+--------------------+

3.2 Volltextindex erstellen

Tabelle ändern, Artikel Volltextindex hinzufügen, idx_full_keyword(keywords);
* [ ] Abfrage OK, 0 Zeilen betroffen, 1 Warnung (1 Min. 27,92 Sek.)
* [ ] Datensätze: 0 Duplikate: 0 Warnungen: 1

3.3 Der verbleibende Speicherplatz muss ausreichend sein. Die ursprüngliche Tabelle ist 4,6 G groß und der verbleibende Speicherplatz beträgt 5,7 G. Das Hinzufügen eines Volltextindex schlägt ebenfalls fehl.

df -h
Verwendete Dateisystemgröße Verfügbare Nutzung% Eingebunden auf
/dev/vda1 7,8 G 6,3 G 1,2 G 85 % /
tmpfs 1,9 G 0 1,9 G 0 % /Entwickler/shm
/dev/mapper/vg_opt-lvol0
            19G 12G 5,7G 68 % /Daten

Es wird eine temporäre Datei mit der ursprünglichen Tabellengröße erstellt. 8,6 KB, 5. Juli, 16:19 Uhr #sql-5250_3533.frm
 4,4 G, 5. Juli, 16:20 #sql-ib117-1768830977.ibd


Tabelle ändern, Artikel Volltextindex hinzufügen, idx_full_keyword(keywords);
FEHLER 1114 (HY000): Die Tabelle 'Artikel' ist voll

3.4 Verwenden Sie den erstellten Volltextindex, um die Häufigkeit des Auftretens eines Schlüsselworts abzufragen

Die Antwortzeit für Abfragen wurde erheblich verbessert und beträgt nur noch 0,05 s. Die Verwendung von Where-Schlüsselwörtern wie „%时尚%“ dauert 7,56 s.

Wählen Sie die Anzahl (*) aus dem Artikel aus, in dem die Übereinstimmung (Schlüsselwörter) mit ('%-Begriff%') übereinstimmt.
+----------+
| Anzahl(*) |
+----------+
| 163 |
+----------+
1 Zeile im Satz (0,05 Sek.)

3.5 Wenn Sie mehrere Schlüsselwörter gleichzeitig vollständig abgleichen müssen, verwenden Sie die boolesche Volltextsuche

Gibt die Anzahl der Datensätze an, die vollständig mit „Sanlitun, Beijing“ übereinstimmen. select count(*) from article where match(keywords) against('+Sanlitun, Beijing' in boolean mode);
+----------+
| Anzahl(*) |
+----------+
| 1 |
+----------+
1 Zeile im Satz (0,06 Sek.)

Gibt die Anzahl der Datensätze an, die mit „Sanlitun“ oder „Beijing“ übereinstimmen. select count(*) from article where match(keywords) against('Sanlitun,Beijing');
+----------+
| Anzahl(*) |
+----------+
| 8 |
+----------+
1 Zeile im Satz (0,06 Sek.)

3.6 Nach dem Erstellen eines Volltextindex werden einige andere Dateien erstellt

96K 5. Juli 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_1.ibd
96K 5. Juli 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_2.ibd
96K 5. Juli 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_3.ibd
96K 5. Juli 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_4.ibd
128K 5. Juli 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_5.ibd
256K 5. Juli 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_6.ibd
96K 5. Juli 16:29 FTS_00000000000000a7_BEING_DELETED_CACHE.ibd
96K 5. Juli 16:29 FTS_00000000000000a7_BEING_DELETED.ibd
96K 5. Juli 16:30 FTS_00000000000000a7_CONFIG.ibd
96K 5. Juli 16:29 FTS_00000000000000a7_DELETED_CACHE.ibd
96K 5. Juli 16:29 FTS_00000000000000a7_DELETED.ibd
- Die ersten 6 stellen den invertierten Index dar (Hilfsindextabelle)
- Die 7. und 8. Spalte stellen die Dokument-ID (DOC_ID) dar, die das gelöschte Dokument enthält, dessen Daten derzeit aus dem Volltextindex gelöscht werden.
- Die 9. gibt den internen Status des FULLTEXT-Index an
- Die 10. und 11. Spalte enthalten Dokumente, die gelöscht wurden, deren Daten aber noch nicht aus dem Volltextindex entfernt wurden

Erstellen eines Volltextindexes mit dem Ngram-Parser

1. Erstellen Sie einen Volltextindex für das Titelfeld (dieses Feld hat keine festen Stoppwörter und verwendet den Ngram-Parser).

Sie müssen ngram_token_size in der Konfigurationsdatei my.cnf festlegen (der Standardwert ist 2, 2 Zeichen als Schlüsselwörter von ngram) und den MySQL-Dienst neu starten. Hier verwenden wir den Standardwert von 2.

Wählen Sie einen Titel aus dem Artikellimit 10;
+-------------------------------------------------------------------+
| Titel |
+-------------------------------------------------------------------+
| es ist es wert |
|Kleine Show der Launchpad Jiangnan Leather Factory |
|Seltener Moment hinter den Kulissen von Raw: „Mad Man“ wird hinter die Bühne getragen|
|Raw: Der Sohn schrie seinen Vater an: „Du bist ein grüner Tee, Sohn.“ Er kämpfte allein gegen vier Leute.|
|Vier Gruppen mit je 30 Quadratmetern liebevoll eingerichteten Kleinwohnungen, zahlreichen Bildern und Grundrissen |
|Sexy rauchiges Katzenaugen-Make-up von der Nachtclub-Queen|
|Big Show kritisiert The Rock Johnson |
|Kleidungswissen von Girls' Generation Choi Sooyoung: Lin Yoona, Huang Miying, Kim Taeyeon und Jung Soojung|                       
|Ausflug in die Natur in Deyang, Grillen in den Blumenfeldern|
+-------------------------------------------------------------------+

2. Erstellen Sie einen Volltextindex für das Titelfeld

Tabelle ändern, Artikel Volltextindex hinzufügen, ft_index_title(Titel) mit Parser ngram;
Abfrage OK, 0 Zeilen betroffen (3 Min. 29,22 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

3. Es wird ein invertierter Index erstellt (je länger das Titelfeld ist, desto größer ist der erstellte invertierte Index).

112M 5. Juli 21:46 FTS_000000000000000a7_00000000000000cd_INDEX_1.ibd
28M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_2.ibd
20M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_3.ibd
140M 5. Juli 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_4.ibd
128M 5. Juli 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_5.ibd
668M 5. Juli 21:46 FTS_000000000000000a7_00000000000000cd_INDEX_6.ibd

4. Suche nach einem Schlüsselwort im Titel, ohne einen Volltextindex zu erstellen

Wählen Sie die Anzahl (*) aus dem Artikel mit dem Titel „%Outdoor%“ aus.
+----------+
| Anzahl(*) |
+----------+
| 22058 |
+----------+
1 Reihe im Satz (8,60 Sek.)

Wählen Sie die Anzahl (*) aus dem Artikel aus, in dem der Titel „%Background%“ lautet.
+----------+
| Anzahl(*) |
+----------+
| 1142 |
+----------+

5. Verwenden Sie die Volltextindizierung, um nach einem Schlüsselwort zu suchen

Die Reaktionszeit wurde erheblich verbessert

Wählen Sie die Anzahl (*) aus dem Artikel aus, in dem „Titel“ mit „Outdoor“ übereinstimmt.
+----------+
| Anzahl(*) |
+----------+
| 22058 |
+----------+
1 Zeile im Satz (0,07 Sek.)

Wählen Sie die Anzahl (*) aus dem Artikel aus, in dem der Titel „%Background%“ lautet.
+----------+
| Anzahl(*) |
+----------+
| 1142 |
+----------+
1 Reihe im Satz (8,31 Sek.)

6. Beachten Sie, dass Inkonsistenzen auftreten, wenn die Anzahl der Zeichen im Suchbegriff größer als 2 ist (ngram_token_size definiert die Größe)

Bei einer normalen Suche beträgt die Anzahl der Datensätze, die dieses Schlüsselwort tatsächlich enthalten, 6
Wählen Sie die Anzahl (*) aus dem Artikel mit dem Titel „%公子大%“ aus.
+----------+
| Anzahl(*) |
+----------+
| 6 |
+----------+
1 Reihe im Satz (8,40 Sek.)

Volltextsuche, die Anzahl der Datensätze mit dem Schlüsselwort beträgt 9443
Wählen Sie die Anzahl (*) aus dem Artikel aus, in dem Übereinstimmung (Titel) mit ('Titel') übereinstimmt.
+----------+
| Anzahl(*) |
+----------+
|9443|
+----------+
1 Zeile im Satz (0,06 Sek.)

Die tatsächliche Anzahl der Datensätze, die dieses Schlüsselwort enthalten, beträgt 1
Wählen Sie die Anzahl (*) aus dem Artikel aus, dessen Titel wie „%花田自服%“ lautet.
+----------+
| Anzahl(*) |
+----------+
| 1 |
+----------+
1 Reihe im Satz (8,33 Sek.)

Die Anzahl der Datensätze, die dieses Schlüsselwort in der Volltextsuche enthalten, beträgt 3202
Wählen Sie die Anzahl (*) aus dem Artikel aus, in dem Übereinstimmung (Titel) mit ('花田自服') übereinstimmt.
+----------+
| Anzahl(*) |
+----------+
| 3202 |
+----------+
1 Zeile im Satz (0,06 Sek.)

abschließend

• Wenn ein MySQL-Feld ein festes Stoppwort hat (Leerzeichen im Englischen, „-“ im Chinesischen usw.), wird ein Volltextindex für das Feld erstellt, der schnell nach relevanten Datensätzen suchen kann, die ein bestimmtes Schlüsselwort enthalten, und so den Effekt einer einfachen Suchmaschine erzielt.
• Wenn ein MySQL-Feld kein festes Stoppwort hat, kann der integrierte Parser ngram verwendet werden, um den Feldwert für eine schnelle Suche in eine feste Anzahl von Schlüsselwörtern (ngram_token_size) aufzuteilen. Wenn die Anzahl der Zeichen im gesuchten Schlüsselwort nicht der durch ngram_token_size definierten Größe entspricht, tritt ein Problem auf, das nicht mit der tatsächlichen Situation übereinstimmt
• Die Volltextindizierung ermöglicht schnelle Suchvorgänge, verursacht jedoch auch Mehraufwand bei der Indexpflege. Je größer die Feldlänge, desto größer ist der erstellte Volltextindex, was sich auf den Durchsatz von DML-Anweisungen auswirkt. Sie können hierfür eine dedizierte Volltextsuchmaschine (ES) verwenden.

siehe

InnoDB FULLTEXT-Indizes

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.