Was ist schneller: MySQL-Volltextindex, gemeinsamer Index, Like-Abfrage und JSON-Abfrage?

Abfragehintergrund

Es gibt eine Tabelle tmp_test_course mit etwa 100.000 Datensätzen und dann gibt es ein JSON-Feld namens Outline, das eine Eins-zu-viele-Beziehung speichert (speichert mehrere Codes, wie z. B. jy1577683381775).

Wir müssen bestimmte Datentypen aus diesen 100.000 Datensätzen abrufen. Das gesamte Zieldatenvolumen beträgt: 2931 Datensätze

Wählen Sie COUNT(*) aus tmp_test_course, wobei `Typ`=5 und del=2 und is_leaf=1 ist.

Während wir die oben genannten Typen einschränken, müssen wir auch einen der folgenden Codes einschließen (d. h. ODER-Abfrage)

jy1577683381775
jy1577683380808
jy1577683379178
jy1577683378676
jy1577683377617
jy1577683376672
jy1577683375903
jy1578385720787
jy1499916986208
jy1499917112460
jy1499917093400
jy1499917335579
jy1499917334770
jy1499917333339
jy1499917331557
jy1499917330833
jy1499917329615
jy1499917328496
jy1576922006950
jy1499916993558
jy1499916992308
jy1499917003454
jy1499917002952

Nachfolgend sind vier Möglichkeiten zum Abfragen des Gliederungsfelds aufgeführt, wobei die entsprechende Abfragezeit und die Anzahl der gescannten Zeilen angegeben werden.

1. Like-Abfrage

Es dauerte 248 Millisekunden

Wählen Sie * aus tmp_test_course 
WO `Typ`=5 UND del=2 UND is_leaf=1 
UND (
Umriss wie „%jy1577683381775%“
ODER Gliederung wie „%jy1577683380808%“
ODER Gliederung wie „%jy1577683379178%“
ODER Gliederung wie „%jy1577683378676%“
ODER Gliederung wie „%jy1577683377617%“
ODER Gliederung wie „%jy1577683376672%“
ODER Gliederung wie „%jy1577683375903%“
ODER Gliederung wie „%jy1578385720787%“
ODER Gliederung wie „%jy1499916986208%“
ODER Gliederung wie „%jy1499917112460%“
ODER Gliederung wie „%jy1499917093400%“
ODER Gliederung wie „%jy1499917335579%“
ODER Gliederung wie „%jy1499917334770%“
ODER Gliederung wie „%jy1499917333339%“
ODER Gliederung wie „%jy1499917331557%“
ODER Gliederung wie „%jy1499917330833%“
ODER Gliederung wie „%jy1499917329615%“
ODER Gliederung wie „%jy1499917328496%“
ODER Gliederung wie „%jy1576922006950%“
ODER Gliederung wie „%jy1499916993558%“
ODER Gliederung wie „%jy1499916992308%“
ODER Gliederung wie „%jy1499917003454%“
ODER Gliederung wie „%jy1499917002952%“
)

Die Ergebnisse der EXPLAIN-Analyse lauten wie folgt: Vollständiger Tabellenscan

2. JSON-Funktionsabfrage

Offizielle JSON-Funktion

Es dauerte 196 Millisekunden, was etwas schneller ist.

Wählen Sie * aus tmp_test_course 
WO `Typ`=5 UND del=2 UND is_leaf=1
UND 
(
JSON_SEARCH(outline, 'one', 'jy1577683381775') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1577683380808') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1577683379178') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1577683378676') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1577683377617') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1577683376672') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1577683375903') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1578385720787') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499916986208') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917112460') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917093400') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917335579') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917334770') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917333339') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917331557') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917330833') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917329615') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917328496') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1576922006950') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499916993558') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499916992308') IST NICHT NULL ODER
JSON_SEARCH(outline, 'one', 'jy1499917003454') IST NICHT NULL ODER
JSON_SEARCH(Umriss, 'eins', 'jy1499917002952') IST NICHT NULL  
)

Die Ergebnisse der EXPLAIN-Analyse sind wie folgt oder ein vollständiger Tabellenscan

3. Gemeinsame Indexabfrage

Als nächstes erstellen Sie einen gemeinsamen Index für die Tabelle (ursprünglich wollte ich einen Index vom Typ „del-is_leaf-outline“ erstellen, aber das Outline-Feld ist zu lang, also habe ich nur einen gemeinsamen Index vom Typ „del-is_leaf“ hinzugefügt.

ALTER TABLE tmp_test_course ADD KEY `type-del-is_leaf` (`type`,`del`,`is_leaf`)

Nach dem Hinzufügen des Index werden die Like- und JSON-Abfragen deutlich beschleunigt.
Die Ausführung dauerte 136 Millisekunden und die JSON-Abfrage 82,6 Millisekunden. Dies zeigt, dass die Verwendung der JSON-Funktionsabfrage für den JSON-Typ schneller ist als

Die Ergebnisse der EXPLAIN-Analyse lauten wie folgt. Die Anzahl der von beiden Abfragen gescannten Zeilen ist auf 2931 Zeilen begrenzt.

4. Volltextindexabfrage

Da Volltextindizes nur CHAR, VARCHAR und TEXT unterstützen, müssen wir die JSON-Felddefinition ändern.

ALTER TABLE tmp_test_course MODIFY `outline` VARCHAR(1024) NOT NULL DEFAULT '[]'

Volltextindex hinzufügen

ALTER TABLE tmp_test_course ADD FULLTEXT INDEX Gliederung (Gliederung);

Nun verwenden wir den Volltextindex für die Suche

Wählen Sie * aus tmp_test_course 
WO `Typ`=5 UND del=2 UND is_leaf=1
UND 
MATCH(outline) AGAINST ('jy1577683381775 jy1577683380808 jy1577683379178 jy1577683378676 jy1577683377617 jy1577683376672 jy1577683375903 jy1578385720787 jy1499916986208 jy1499917112460 jy1499917093400 jy1499917335579 jy1499917334770 jy1499917333339 jy1499917331557 jy1499917330833 jy1499917329615 jy1499917328496 jy1576922006950 jy1499916993558 jy1499916992308 jy1499917003454 jy1499917002952')

Es dauert 11,6 Millisekunden und die Geschwindigkeit ist deutlich verbessert, was die Leistungsstärke der Volltextindizierung zeigt.

Die Ergebnisse der EXPLAIN-Analyse lauten wie folgt und zeigen, dass nur eine Zeile gescannt wurde

abschließend

Nachfolgend sind die Ausführungsergebnisse von 4 Fällen aufgeführt

Volltextindex: 11,6 ms
Gemeinsamer Index: 82,6 ms (JSON), 136 ms (Like)
JSON-Funktionsabfrage: 196 ms
Wie Abfrage: 248 ms

Fazit: Volltextindex > Gemeinsamer Index > JSON-Funktionsabfrage > Like-Abfrage

Je größer die Datenmenge, desto schneller ist die Volltextindizierung. Bei einer Tabelle mit 100.000 Datenpunkten ist die Abfragegeschwindigkeit etwa 20-mal schneller als bei einer direkten Abfrage. Wenn die Tabelle Millionen oder Zehnmillionen Datenpunkte enthält, ist die Verbesserung sogar noch größer. Verwenden Sie daher nach Möglichkeit die Volltextindizierung.

Dies ist das Ende dieses Artikels darüber, was schneller ist: MySQL-Volltextindex, kombinierter Index, Like-Abfrage oder JSON-Abfrage. Weitere Informationen zu MySQL-Volltextindex, kombiniertem Index, Like-Abfrage und JSON-Abfrage finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder lesen Sie weiter in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!