Volltextsuchen in RDBMS

Vergleich von Volltextsuchen in MySQL, PostgreSQL und MS SQL Server 2000 (Stand 2004)

#cc-by #from-slideshare
https://www.slideshare.net/gerritbeine/volltextsuchen-in-rdbms-2004

1. Volltextsuchen in RDBMS Realisierung und Verwendung von Volltextsuchen in den Relationalen DBMS M$ SQL Server 2000 PostgreSQL MySQL
2. Übersicht • Vergleich der DBMS • Was ist eine Volltextsuche? • Realisierung in den DBMS • Zusammenfassung
3. Vergleichsmöglichkeiten • Werbung durch Hersteller • Aufstellung von Feature Listen • Benchmarks • Erfahrungswerte sammeln • Propaganda von Anwendern
4. Was vergleicht man? • Einsatzgebiet • Einsatzzweck • Kosten • Support • Geschwindigkeit • Funktionsumfang
5. Vergleichskriterien • Unixoides Serverbetriebssystem • Datenbanken für Community-Portal • Möglichst geringe Kosten • Schneller Support bei Fragen • Gute Skalierung auch bei hoher Last • Unterstützung für Volltextsuchen
6. MS SQL Server 2000 • Läuft nur auf MS Windows • Verhältnismäßig preiswert (ab 1.500 US-$) • Proprietäres System • Keine Unterstützung für implizite FTI • Skaliert gar schauerlich unter hoher Last • Support... *hust*
7. MySQL AB's MySQL 4.x • Läuft auf allen Unixoiden OS • Verfügbar unter GPL • Kommerzielle Lizenz ab 220 Euro • Offenes System • Hervorragender Support durch Community • Kommerzieller Support durch Hersteller
8. MySQL AB's MySQL 4.x • Skaliert hervorragend bei SELECT • Schlechte Serialisierung bei hoher Last • Leistungseinbruch ab Lesen:Schreiben ~ 7:3 • Unterstützung für impliziten FTI
9. PostgreSQL 7.4 • Läuft auf allen Unixoiden OS (inkl. Sony Playstation) • Software kostenlos – BSD Lizenz • Offenes System • Support durch Community und Firmen • Skaliert hervorragend durch genetischen Query Optimizer
10. PostgreSQL 7.4 • Keine Unterstützung für FTI • Unterstützung für nutzerdefinierte Datentypen • Unterstützung von eingebetteten Sprachen (Perl, C, Python, TCL) • Unterstützung von GiST (Verallgemeinerter balancierter Suchbaum) • Addon für implizite FTI
11. Benchmarks • Traue keiner Statistik... • Benchmarks sind einseitig • Ergebnisse durch Know-How beeinflußbar • eWeek Server Database Clash 2002 • AS3AP Benchmark 2000
12. eWeek-Benchmark 2002 • 5 RDBMS getestet • Oracle 9i 9.0.1.1.1 • MS SQL Server 2000 Service Pack 2 • Sybase ASE 12.5.0.1 • IBM DB2 7.2 FixPack 5 • MySQL-Max 4.0.1-alpha
13. Ergebnis • Oracle und MySQL schaffen Dauertest (8 Stunden) fehlerfrei • Oracle und MySQL klare Testsieger
14. AS3AP Benchmark - 2000
15. Codequalität • Microsoft zufolge haben Microsoft Produkte die höchste Codequalität • Reasoning zufolge hat MySQL die höchste Codequalität aller verfügbaren DBMS • Meiner Erfahrung nach sind 90% der Probleme an Servern durch Microsoft Produkte verursacht
16. Was ist eine Volltextsuche? • Unscharfe Abfragen • Keine eindeutigen Ergebnisse • Suchergebnisse werden bewertet • Suche nach Worten
17. LIKE sucks SELECT ... COL LIKE „Begriff“ SELECT ... COL LIKE „Begriff%“ SELECT ... COL LIKE „%Begriff%“ • LIKE sucht nach Zeichenketten • nicht optimierbar • „teurer“ Full Table Scan
18. FTI-Techniken • Zeitpunkt der Erstellung • Automatisch (implizit) • auf Anforderung • Art der Erstellung • Textuelle Indizierung • Konzeptionelle Indizierung • Typ des FTI
19. Automatische Erstellung • Indizierung wird beim Einfügen des Datensatzes vorgenommen • Overhead durch permanentes traversieren und sortieren des Index • Verlangsamt Schreiboperationen • Änderungen On-the-Fly verfügbar
20. Erstellung auf Anforderung • Index muß manuell erstellt werden • Index liegt außerhalb der Datenbank • Kein Overhead beim Schreiboperationen • Änderungen erst nach Neuerstellung verfügbar • Redundante Datenhaltung
21. Textuelle Indizierung • Statistische Methoden • häufige Worte sind „wertlos“ • häufigste Worte werden gesucht und gefiltert • andere Worte werden indiziert • Stoppwortlisten • Worte in der Stoppwortliste werden ausgefiltert • andere Worte werden indiziert
22. Konzeptuelle Indizierung • beruht auf KI-Verfahren • Linguistische Analyse • Stemming • Präfix und Suffixe erkennen • Wortstamm finden • Begriffshierarchien • Thesaurus • Wörterbücher
23. FTI-Typen • Herkömmliche Bäume nicht geeignet (B-Bäume, B+-Bäume, B*-Bäume) • Andere Verfahren • Tries • Patricia-Bäume • Präfix-Bäume • GiST • Hash-Verfahren
24. Volltextsuche in MS SQL Server • Unterstützung für FTI vorhanden • ein FTI pro Tabelle • Generierung des FTI auf Anforderung • keine implizite Aktualisierung • keine Stoppworte • kein konzeptueller Index • Index außerhalb der Datenbank
25. MS SQL Server - Anlegen
26. MS SQL Server - Anlegen
27. MS SQL Server - Anlegen
28. MS SQL Server - Anlegen
29. MS SQL Server - Anlegen
30. MS SQL Server - Abfragen SELECT * FROM TABLE WHERE FTI_COL LIKE „%Begriff%“ -- wird zu SELECT * FROM TABLE WHERE CONTAINS (FTI_COL, 'Begriff')
31. Volltextsuche in MySQL • Unterstützung für FTI vorhanden • beliebig viele FTI pro Tabelle • Generierung zu jedem Zeitpunkt möglich • implizite Aktualisierung • Stoppwortlisten als Umgebungsvariable • BOOLE'sche Suche möglich • qualitativ gute Bewertungsfunktion
32. Volltextsuche in MySQL • INSERT und UPDATE bis zu 10 mal langsamer • SELECT kann zum TABLE LOCK führen • Index ist 30% größer als Datenbestand • Kein konzeptueller Index
33. MySQL - Anlegen -- beim Anlegen einer Tabelle CREATE TABLE fttest ( number int NOT NULL default '0', text text NOT NULL, FULLTEXT (text) );
34. MySQL - Anlegen -- bei einer existierenden Tabelle CREATE TABLE fttest ( number int NOT NULL default '0', text text NOT NULL ); ALTER TABLE fttest ADD FULLTEXT (text);
35. MySQL - Abfragen -- einfache Suche SELECT * FROM fttest WHERE MATCH (text) AGAINST ('datenbank');
36. MySQL - Abfragen -- einfache Suche mit Bewertung SELECT text, MATCH text AGAINST ('datenbank') AS SCORE FROM fttest WHERE MATCH (text) AGAINST ('datenbank');
37. MySQL - Abfragen -- Suche im Boolean Mode SELECT * FROM fttest WHERE MATCH (text) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
38. MySQL - Operatoren '+' - Wort muß im Datensatz vorkommen '-' - Wort darf nicht im Datensatz vorkommen '>', '<' - Berücksichtigt das Wort stärker oder schwächer '(', ')' - Gruppiert Worte, Nesting möglich '~' - Negiert ein Matching '“' - Phrasierung mehrerer Worte '*' - Wortanfang kennzeichnen
39. MySQL - Operatoren -- Suche mit Operatoren 'datenbank microsoft' '+datenbank +microsoft' '+datenbank -microsoft' '+datenbank microsoft' '“datenbank microsoft“' 'daten*' '+datenbank +(„microsoft sql“)'
40. Volltextsuche in PostgreSQL • Keine Unterstützung für FTI • aber dafür tsearch2 • neuer Datentyp tsvector • Unterstützung für Stemming • Verwendet GiST-Index • implizite Aktualisierung des FTI • Generierung zu jedem Zeitpunkt möglich
41. Installation von tsearch2 $ tar -zxvf tsearch-v2.tar.gz $ mv tsearch2 $PGSQL_SRC/contrib/ $ cd $PGSQL_SRC/contrib/tsearch2 $ gmake $ gmake install $ createdb fttest $ psql fttest < tsearch2.sql
42. Anlegen der Tabelle CREATE TABLE fttest ( number int NOT NULL default '0', text text NOT NULL, fti tsvector ); CREATE INDEX idxFTI ON fttest USING gist(fti);
43. Erstellen des Trigger CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON fttest FOR EACH ROW EXECUTE PROCEDURE tsearch2(fti, text);
44. PostgreSQL - Abfrage SELECT * FROM fttest WHERE fti @@ to_tsquery('default', 'datenbank');
45. PostgreSQL - Stopwords -- Konfiguration erstellen INSERT INTO pg_ts_cfg (ts_name, prs_name, locale) VALUES ('default_german', 'default', 'de_DE');
46. PostgreSQL - Stopwords INSERT INTO pg_ts_dict (SELECT 'de_stem', dict_init, 'postgresql/contrib/german.stop', dict_lexize FROM pg_ts_dict WHERE dict_name = 'en_stem');
47. PostgreSQL - Stopword-Abfrage -- ohne Stopwords SELECT to_tsvector('das ist das haus vom nikolaus'); to_tsvector ------------------------------------- 'das':1,3 'ist':2 'haus':4 'vom':5 'nikolaus':6 (1 row)
48. PostgreSQL - Stopword-Abfrage -- mit Stopwords SELECT to_tsvector('default_german', 'das ist das haus vom nikolaus'); to_tsvector ------------------------------------- 'haus':4 'nikolaus':6 (1 row)
49. PostgreSQL - Dictionaries INSERT INTO pg_ts_dict (SELECT 'de_ispell', dict_init, 'DictFile="ispell/deutsch.med",' 'AffFile="ispell/deutsch.aff",' 'StopFile="postgresql/contrib/german.stop"', dict_lexize FROM pg_ts_dict WHERE dict_name = 'ispell_template');
50. Dictionary-Abfrage -- ohne Dictionary SELECT to_tsvector('das sind die häuser vom nikolaus'); to_tsvector ------------------------------------- 'häuser':4 'nikolaus':6 (1 row)
51. Dictionary-Abfrage -- mit Dictionary SELECT to_tsvector('default_german', 'das sind die häuser vom nikolaus'); to_tsvector ------------------------------------- 'haus':4 'nikolaus':6 (1 row)
52. Zusammenfassung • Volltextsuchen sind rechenintensive Operationen • Aktualität vs. Geschwindigkeit • Integration von tsearch in PostgreSQL ermöglicht große Flexibilität
53. May the Source be with you! Noch Fragen?