SQL Große(160Mio. Datensätze) MySql Datenbank nach Wörter/ Werten durchsuchen und Sortier

775726

Cadet 2nd Year
Registriert
Jan. 2009
Beiträge
25
Hallo liebe Gemeinde und Gäste,
Ich habe folgendes Problem und zwar habe ich eine große MySql Datenbank die ich nach einem Wort/Wert oder mehreren Wörtern/Werten durchsuchen möchte. Was ich bisher gemacht habe aber nicht sehr gute Ergebnisse in Bezug Performance und Relevanz ist folgendes.

  • Like Suche
  • Like Suche mit %(Wildcards)(overkill)
  • Fulltextsuche
  • Fulltextsuche im Boolean Mode

Jetzt würde ich gerne wissen wie ich die Vorteile der verschiedenen Suchtypen am besten kombinieren sollte und die besten Ergebnisse zu bekommen.

Hier der Link bzw. die Webseite womit man einen kleinen Teil der Daten durchsucht kann.
unique-portal.de/

PS: Oder muss ich bei so einer Datenmenge die Datenbank Struktur umbauen.

DB Structure:
ID
Title char(250)
URL char(250)
description text
EXTRA text
Date datetime
Point’s bigint
Userdescription varchar(500)
Htmlcode text
 
Zuletzt bearbeitet:
hast du einen myisam volltext-index auf den abgefragten spalten? dann sollte fulltext relativ performant sein. ansonsten: replikation, mehr ram, ssd :)
 
Der myisam volltext-index ist vorhanden und wird auch benutzt. Das Problem dabei ist die Suche nach mehreren Wörtern die nicht genauso(reihenfolge) in der Datenbank vorhanden sind.
 
Bei der Datenmenge kann die Antwort bald nur lauten: Mach es nicht mit MySQL.
Das größte Problem bei Suchen ist, dass man ja meist nicht präzise sucht. Like mit Wildcard hat wenigstens etwas "fuzzy-search", aber das fetzt dir jeden Server sofort weg. Für Like ohne Wildcard sowie Fulltext gilt: Deine Suche ist verdammt präzise. Wenn du "Rot" in der Datenbank stehen hast, wird eine Suche nach "Rote Schuhe" kaum zum Erfolg führen, eine Suche nach "Burgunderrot" geht auf jeden Fall in den Sack.
Selbst wenn du das Problem von Singular/Plural, Fälle,... irgendwie in den Griff bekommst, du glaubst nicht, wie unglaublich dämlich Menschen sind. Wir hatten bei uns in einem T-Shirt - Shop schon Suchen nach "Speidermann" oder "Tracktor"... Hier brauchst du noch eine Rechtschreib-Erkennung, die den Suchenden freundlich darauf hin weist, was er da für ne SCheiße schreibt.
Und all das: ne anständige Fuzzy Search mit Flexion & Co sowie REchtschreibprüfung, müssen auch noch verdammt performant sein. Bei deinen Datenmassen scheitert selbst ein Cluster.

All das kann MySQL nicht bieten, genauso wenig PostgreSQL oder sonst irgend eine der üblichen SQL Datenbanken. Hier muss eine anständige Suchengine ran, z.B. Sphinx oder Lucene.
 
Wie schon gesagt: Jede SQL Datenbank ist da restlos überfordert. Bei 1 Mio Einträgen geht's noch gerade so mit nem MATCH AGAINST, aber alles darüber ist für den Server ein Schlag in's Gesicht. Und da MySQL auch nicht so wirklich horizontal skaliert, rate ich an dieser Stelle auch zu Lucene. Mit dem richtigen abstrahierenden Framework ist es sehr einfach zu bedienen (ohne Framework mag ich Lucene gar nicht anfassen ;) )
 
Ich würde mir Elastic Search oder Apache Solr anschauen. Machen Lucene bedienbar. Persönlich werde ich bei Bedarf Elastic Search verwenden.
 
Ich sehe schon ich muss mir was anderes einfallen als eine SQL Select Anweisung. Aber trotzdem danke erst mal für die antworten.
Des Weiteren gibt es kostenlose Online Angebote wo man mit den eigenen Daten Lucene, Elastic Search oder Sphinx benutzten/testen kann?
 
Test weise würde ich eine Teil meiner eigenen Test Daten einem Drittanbieter in den Rachen werfen um zu gucken was möglich ist bzw. was für mich die beste Lösung ist. Das sphinxsearch probiere ich jetzt mal aus.

Aber noch eine andere Frage mit einem kleinen Beispiel.
Ist es sinnvoll die ganze Datenbank um zu strukturieren sodass jedes Wort nur einmal gespeichert ist mit einer weiteren Tabelle die besagt wo welches Wort in welche Datensatz an welche Position zu finden ist.
Würde das die suche Performance verbessern? Weil man ja dann mit Volltextsuche und Kombination mit den IDS schnell heraus finden sollte in welche Datensatz die gesuchten Suchbegriffe zu finden sind.
Beispiel:

Information Daten
DID Text
1 Ich liebe dich
2 Ich kann liebe empfinden

Wörterliste
WID Wort
1 Ich
2 liebe
3 dich
4 Kann
5 empfinden

Kreuztabellen
ID WID Position DID
1 1 1 1
2 2 2 1
3 3 3 1
4 1 1 2
5 4 2 2
6 2 3 2
7 5 4 2
 
Zuletzt bearbeitet:
Also deine jetzige Tabelle hat 160Mio Zeilen? Sagen wir mal jede Zeile hat 'nen Text mit 10 Wörtern, dann hat deine "Kreuztabelle" am Ende 1,6Mrd Zeilen. Deine Tabelle mit den einzelnen Wörtern hat wohl einige Tausend Einträge.

Was machst du mit Satzzeichen? Punkte, Kommata, Bindestriche, Fragezeichen, etc. pp.

Wenn du nun mittels dieser Tabellen den ursprünglichen Text eines Eintrags ausgeben willst, musst du 'nen Haufen zusätzlicher JOINs machen. Da sollte man dann das ganze denormalisiert speichern, also die Tabellen zum Suchen _zusätzlich_ zum vollständigen Text.

Und wie willst du dann Suchen gestalten, die mehrere hintereinander stehende Worte exakt matchen soll? Z.B. steht in einem Eintrag der Text "Immer mehr Spiele mit einem Preisschild von 60 Euro bieten die Möglichkeit, zusätzliches Geld für Ingame-Käufe auszugeben." und ich will nach "60 Euro" suchen, aber deine DB bildet "60" und "Euro" als 2 separate Einträge in der Kreuztabelle ab, dann hast du am Ende ein extrem hässliches Query (wenn es überhaupt ohne Subquery möglich ist).

Dann kommen eben so Sachen "WHERE text LIKE '%auszugeben.'". Wie willst du nun einfach alle Einträge finden, die auf "auszugeben." enden? Genau indem für alle Matches schaust, ob es der MAX(Postition) Eintrag ist ... viel Spaß, wenn da mal 10000 Matches auftreten.

Also noch mal:
Benutz eine Engine, die für Textsuche ausgelegt ist. Die sind schneller und effizienter als jedes SQL Gefrickel und bieten darüber hinaus noch Features, die man mit SQL gar nicht umsetzen könnte, wie z.B. die Suche nach ähnlichen Worten, um Schreibfehler im Suchstring auszubügeln.


---

EDIT:

Man schaue sich mal ein paar Beiträge im Netz an: http://stackoverflow.com/a/6826806/1321564
MySQL: 15 Sec, Lucene <50ms und mit Cache sogar <1ms

Sphinx vs. ElasticSerach: http://blog.wajam.com/2013/08/scalable-architecture-behind-wajam-social-search/

Und irgendwo hab ich noch gelesen, dass MySQL kurze Wörter erst gar nicht indiziert. Was vielleicht nicht unbedingt nötig ist, aber eben auch nicht gerade das beste Bild einer ordentlichen Full Text Search Engine abliefert.

Wenn man sich Vergleiche und Benchmarks anschaut ist das Bild recht deutlich:
1. Wenn du MySQL nutzt und eine einfache Lösung willst: Sphinx (kann direkt mit MySQL kommunizieren und Daten indizieren)
2. Wenn es um das letzte bisschen Performance geht: Lucene (bzw. Solr / ElasticSearch aus Gründen des Komforts)
3. Wenn der Full Text Index verteilt auf mehreren Servern gespeichert werden soll: Elasticsearch

Der "Nachteil" an Lucene ist, dass es durch die JVM einiges an zusätzlichem RAM frisst. Bei unseren Projekten stört das nicht, da wir immer irgendwas Java-basierendes laufen haben, sei es Tomcat oder Neo4j. Und allgemein stört es auf Servern nicht, da diese üblicherweise mehr als 2GB RAM haben ;)

Es gibt natürlich noch weitere Projekte wie "Xapian", das aber scheinbar niemand nutzt.
 
Zuletzt bearbeitet:
775726 schrieb:
Ist es sinnvoll die ganze Datenbank um zu strukturieren sodass jedes Wort nur einmal gespeichert ist mit einer weiteren Tabelle die besagt wo welches Wort in welche Datensatz an welche Position zu finden ist.
Würde das die suche Performance verbessern? Weil man ja dann mit Volltextsuche und Kombination mit den IDS schnell heraus finden sollte in welche Datensatz die gesuchten Suchbegriffe zu finden sind.

So ähnlich löst es die Suche von Contao, und ganz ehrlich: Die ist zwar um Welten besser (performanter, flexibler) als die Standard-Suche von Magento (die ist unterste Schublade, ein No-Go für ein Shopsystem), aber trotzdem findet das Ding nur Grütze.

Für kleine Seiten ist so etwas durchaus noch akzeptabel, aber für sehr viele Inhalte oder komplexe Suchen scheitert der Kram gnadenlos.

Beispiel bei einem unserer Kunden, hier läuft ein selbst geschriebenes Immobilien-Modul auf Contao-Basis, in dem die verschiedenen zum Verkauf stehenden Objekte des Kunden aufgelistet sind: Sucht man jetzt nach "Münchner Platz" oder "Berliner Straße", was ja eine recht übliche Adresse ist, so findet das Ding natürlich alles, was "Münchner" oder "Platz" enthält. Es findet aber nciht präzise den Term "Münchner Platz", bzw. das Highlighting scheitert. Jedes Auftreten von "Platz" und "Münchner" erhält <span class="highlight">, also natürlich auch ein Objekt an der "Münchner Straße" oder dem "Straßburger Platz".

Für komplexe Volltextsuche führt kein Weg an echten FT-Suchen vorbei, SQL ist da das falsche Werkzeug.

Der wichtigste Punkt FÜR echte FT-Suchen ist "Stemming". Ohne Stemming finden deine Kunden/Besucher eher zufällig das, was sie eigentlich suchen.

Und irgendwo hab ich noch gelesen, dass MySQL kurze Wörter erst gar nicht indiziert. Was vielleicht nicht unbedingt nötig ist, aber eben auch nicht gerade das beste Bild einer ordentlichen Full Text Search Engine abliefert.
Korrekt.
1.) Ein FT-Index muss mindestens 3 Zeichen lang sein, außer man ändert das GLOBAL für die gesamte Maschine. Senkt man den Wert auf 2 oder gar 1 Zeichen, bricht die Geschwindigkeit noch mehr weg
2.) MySQL verwendet Stop Words, was auch sinnig ist. Blöderweise sind diese Stop Words englisch und in die Binary kompiliert und können ohne Neukompilieren nicht geändert werden.
 
@benneque: Danke für die ausführliche Antwort mit Vergleich lucene / Sphinx. Das spart mir gleich die Folgefrage, die ich hier im Thread stellen wollte. :D
 
benneque schrieb:
Also noch mal:
Benutz eine Engine, die für Textsuche ausgelegt ist. Die sind schneller und effizienter als jedes SQL Gefrickel und bieten darüber hinaus noch Features, die man mit SQL gar nicht umsetzen könnte, wie z.B. die Suche nach ähnlichen Worten, um Schreibfehler im Suchstring auszubügeln.

Ähnliche Wörter können mit der Funktion SOUNDEX ermittelt werden und wird von allen bekannteren Datenbanken unterstützt.
 
Verglichen mit der Stemming - Funktion von anständigen FT-Suchmaschinen ist so etwas doch der blanke Hohn.
Außerdem scheitert MySQL (bzw. jedes RDBMS) bei 160Mio Einträgen im Fulltext so oder so gnadenlos. Sphinx & Co sind hier um den Faktor 10 oder noch mehr performanter. Wenn du mit nem RDBMS - Cluster die Suchdauer auf leidlich erträgliche 2-300ms drücken kannst, schaffst du mit einem Sphinx/SOLR/...-Server allein 100ms und weniger.
 
SOUNDEX ist für bestimmte Schreibfehler ausgelegt und funktioniert bekanntermaßen nur für kurze Wörter (oder benutzen die SQL Engines hier eine abweichende Impl.?). Aber es gibt ja auch so genug Fälle, in denen SOUNDEX an seine Grenzen stößt:
- Falscher Anfangsbuchstabe, aber den Rest des Wortes richtig geschrieben
- Einen der ersten drei Konsonanten beim Schnelltippen vergessen
- Buchstabendreher (wenn Konsonanten paarweise betroffen) vergibt der Algorithmus auch nicht

Und da gibt's bestimmt noch weitere Dinge ;)

Also natürlich kann man auch mit ner realtionalen Datenbank auf die Performance einer ordentlichen Text Search Engine kommen, wenn man nur genug Rechner zur Verfügung stellt, aber wozu 10 Server mieten, wenn es auch ein einziger tut?
 
Ich würde sagen, wende mal die relationale Algebra auf die Abfrage und berechne somit die I/O.
 
Zuletzt bearbeitet:
Zurück
Oben