webentwicklung-frage-antwort-db.com.de

Wie beschleunigen Sie SELECT .. LIKE-Abfragen in MySQL für mehrere Spalten?

Ich habe eine MySQL-Tabelle, für die ich sehr häufig SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%'-Abfragen stelle. Würde irgendein Index helfen, die Dinge zu beschleunigen?

Die Tabelle enthält einige Millionen Datensätze. Wenn es etwas gibt, das die Suche beschleunigen würde, würde dies die Festplattennutzung durch die Datenbankdateien und die Geschwindigkeit von INSERT- und DELETE-Anweisungen ernsthaft beeinträchtigen? (es wird niemals eine UPDATE ausgeführt)

Aktualisieren: Schnell nach dem Posten habe ich viele Informationen und Diskussionen darüber gesehen, wie LIKE in der Abfrage verwendet wird. Ich möchte darauf hinweisen, dass die Lösung muss Verwenden Sie LIKE '%text%' (das heißt, der Text, nach dem Sie suchen, wird mit einem vorangestellten% Platzhalter vorangestellt). Die Datenbank muss aus verschiedenen Gründen, einschließlich der Sicherheit, auch lokal sein.

36
Tom

Ein Index würde die Abfrage nicht beschleunigen, da Indizes für Textspalten funktionieren, indem N-Zeichen beginnend mit links indiziert werden. Wenn Sie '% text%' LIKE tun, kann der Index nicht verwendet werden, da vor dem Text eine variable Anzahl von Zeichen stehen kann.

Was Sie tun sollten, ist, eine solche Abfrage überhaupt nicht zu verwenden. Verwenden Sie stattdessen so etwas wie FTS (Full Text Search), das MySQL für MyISAM-Tabellen unterstützt. Es ist auch ziemlich einfach, ein solches Indexierungssystem für Nicht-MyISAM-Tabellen selbst zu erstellen. Sie benötigen lediglich eine separate Indextabelle, in der Sie Wörter und ihre relevanten IDs in der eigentlichen Tabelle speichern.

59
reko_t

Ein Index hilft nicht, wenn Text mit einem führenden Platzhalter übereinstimmt. Ein Index kann für Folgendes verwendet werden:

LIKE 'text%'

Aber ich schätze, das wird es nicht schneiden. Bei dieser Art von Abfrage sollten Sie sich wirklich einen Volltextsuchanbieter ansehen, wenn Sie die Anzahl der Datensätze skalieren möchten, die Sie durchsuchen können. Mein bevorzugter Anbieter ist Sphinx , sehr gut ausgestattet/schnell usw. Lucene könnte ebenfalls einen Blick wert sein. Ein Volltextindex für eine MyISAM-Tabelle wird ebenfalls funktionieren, aber MyISAM für jede Datenbank zu verwenden, die eine beträchtliche Anzahl von Schreibvorgängen hat, ist keine gute Idee.

18
Michael

Ein Index kann nicht verwendet werden, um Abfragen zu beschleunigen, bei denen die Suchkriterien mit einem Platzhalter beginnen:

LIKE '%text%'

Ein Index kann (und kann, abhängig von der Selektivität) für Suchbegriffe des Formulars verwendet werden: 

LIKE 'text%'

12
Mitch Wheat

Ich würde hinzufügen, dass Sie in einigen Fällen die Abfrage mithilfe eines Index zusammen mit like/rlike beschleunigen können, wenn das Feld, das Sie betrachten, oft leer ist oder etwas Konstantes enthält.

In diesem Fall können Sie die über den Index besuchten Zeilen anscheinend einschränken, indem Sie eine "and" -Klausel mit dem festen Wert hinzufügen.

Ich habe dies versucht, um nach "Tags" in einer riesigen Tabelle zu suchen, die normalerweise nicht viele Tags enthält.

SELECT * FROM objects WHERE tags RLIKE("((^|,)tag(,|$))" AND tags!=''

Wenn Sie einen Index für Tags haben, werden Sie sehen, dass er verwendet wird, um die zu durchsuchenden Zeilen einzuschränken.

10
OderWat

Vielleicht können Sie versuchen, mysql5.1 auf mysql5.7 zu aktualisieren.

Ich habe ungefähr 70.000 Datensätze. Und führe folgendes SQL aus:

select * from comics where name like '%test%'; 

Es braucht 2000ms in mysql5.1 . Und es braucht 200ms in mysql5.7 oder mysql5.6.

7
lingceng

Ein anderer Weg:

Sie können berechnete Spalten mit diesen Zeichenketten REVERSEd beibehalten und verwenden

SELECT x, y, z FROM table WHERE x LIKE 'text%' OR y LIKE 'text%' OR z LIKE 'text%' OR xRev LIKE 'txet%' OR yRev LIKE 'txet%' OR zRev LIKE 'txet%' 

Beispiel für das Hinzufügen einer gespeicherten persistenten Spalte

ALTER TABLE table ADD COLUMN xRev VARCHAR(N) GENERATED ALWAYS AS REVERSE(x) stored;

und erstellen Sie dann einen Index für xRev, yRev usw.

Eine weitere Alternative, um vollständige Tabellenscans zu vermeiden, ist das Auswählen von Teilzeichenfolgen und das Einchecken in der having-Anweisung:

SELECT 
    al3.article_number,
    SUBSTR(al3.article_number, 2, 3) AS art_nr_substr,
    SUBSTR(al3.article_number, 1, 3) AS art_nr_substr2,
    al1.*
FROM
    t1 al1 
    INNER JOIN t2 al2 ON al2.t1_id = al1.id
    INNER JOIN t3 al3 ON al3.id = al2.t3_id
WHERE
    al1.created_at > '2018-05-29'
HAVING 
    (art_nr_substr = "FLA" OR art_nr_substr = 'VKV' OR art_nr_subst2 = 'PBR');
0
Juri Sinitson