Veröffentlicht: 24. Apr 2003 | Aktualisiert: 22. Jun 2004
Von Andrew B. Cencini
Auf dieser Seite
Einführung
Grundlegendes zur Volltextsuche
Konfigurieren der Volltextsuche
Volltextabfragen
Rangordnung und Optimierung
Weitere Leistungstricks
Schlussfolgerung
Anhang A: Implementieren von Best Bets bei Volltextsuchen
Anhang B: Eine Beispielanwendung unter Verwendung von Best Bets, Ergebnispaging und effizienter Volltextabfragelogik
Anhang C: Ressourcen
Einführung
Mithilfe der Volltextsuche von Microsoft® SQLT Server 2000 können Sie schnell und flexibel Abfragen an Indizes, die aus unstrukturierten Textdaten erstellt wurden, durchführen. Ein häufiger Verwendungszweck für eine Volltextsuche sind Suchmodule für Websites. Um die Volltextsuche bestmöglich einzusetzen, sollten Sie sich mit einigen grundlegenden Konzepten und Abstrahierungen auskennen. Es gibt auch verschiedene Tipps und Tricks, mit denen Sie die Volltextindizes und -abfragen für einen maximalen Durchsatz und maximale Leistung optimieren können.
Grundlegendes zur Volltextsuche
Die Volltextsuche wurde als Feature in SQL Server 7.0 eingeführt. Das Kernmodul der Volltextsuche wird mithilfe der Microsoft Search-Technologie (MSSearch) bereitgestellt, die auch in Produkten wie Microsoft Exchange und Microsoft SharePointT Portal Server eingesetzt wird.
Die von der SQL Server 7.0-Volltextsuche bereitgestellte Funktionalität beinhaltet grundlegende Textsuchfähigkeiten und verwendet eine frühere Version von MSSearch. Die Implementierung der Volltextsuche von SQL Server 2000 stellt einen robusten Satz von Index- und Abfragefunktionen sowie verschiedene Erweiterungen zusätzlich zu den im Lieferumfang von SQL Server 7.0 enthaltenen Erweiterungen bereit. Die Erweiterungen umfassen: volle Unterstützung für Clusterbildung über den Microsoft Clusterdienst, die Möglichkeit, in IMAGE-Spalten gespeicherte Dokumente zu filtern und zu indizieren, verbesserte Sprachunterstützung sowie Verbesserungen in Bezug auf Leistung, Skalierbarkeit und Zuverlässigkeit.
MSSearch dient der Erstellung, Verwaltung und Abfrage von Volltextindizes, die im Dateisystem (und nicht innerhalb von SQL Server) gespeichert sind. Die logische und physische Speichereinheit, die für Volltextindizes von MSSearch verwendet wird, ist ein Katalog. Ein Volltextkatalog enthält einen oder mehrere Volltextindizes pro Datenbank - pro Tabelle kann ein Volltextindex in SQL Server erstellt werden, und Sie können eine oder mehrere Spalten dieser Tabelle in den Index aufnehmen. Jede Tabelle kann nur zu einem Katalog gehören, und für jede Tabelle kann nur ein Index erstellt werden. Wir werden etwas später auf die optimalen Methoden zum Organisieren der Volltextkataloge und -indizes eingehen. Vorerst jedoch möchte ich näher erläutern, wie eine Volltextsuche funktioniert.
Konfigurieren der Volltextsuche
Vor dem Erstellen eines Volltextindex der in SQL Server gespeicherten Textdaten sollten Sie einige Schritte ausführen. Sie sollten zunächst die Datenbank, die die zu indizierenden Textdaten enthält, volltextfähig machen (falls Sie dies nicht bereits getan haben).
Achtung:
Das Ausführen der folgenden Anweisung löscht alle Volltextkataloge, die zu der Datenbank gehören, für die die Volltextsuche aktiviert werden soll, und erstellt sie neu. Stellen Sie sicher, dass Sie nicht bereits Volltextkataloge in der Datenbank erstellt haben, die aktiviert werden soll, es sei denn, diese Kataloge sollen neu erstellt werden.
Falls Sie ein Mitglied der Rolle sysadmin oder der db_owner für diese bestimmte Datenbank sind, können Sie folgende Anweisung ausführen:
use Northwind
exec sp_fulltext_database 'enable'
Als Nächstes erstellen Sie einen Volltextkatalog, um die Volltextindizes zu speichern. Wie vorhin erwähnt, werden die Daten in diesem Katalog in Ihrem Dateisystem und nicht in SQL Server gespeichert, sodass Sie den Speicherort des Volltextkatalogs sorgfältig auswählen sollten. Falls nicht anders angegeben, wird der Volltextkatalog in einem Unterverzeichnis des Verzeichnisses FTDATA gespeichert, das sich am Speicherort von Microsoft SQL Server\MSSQL befindet. So erstellen Sie einen Volltextkatalog an einem anderen als dem Standardspeicherort:
exec sp_fulltext_catalog 'Cat_Desc', 'create', 'f:ft'
In diesem Fall wird der Volltextkatalog als ein Unterverzeichnis von f:\ft erstellt. Wenn Sie sich diesen Teil des Dateisystems ansehen, wird er dort als eigenes Verzeichnis angezeigt. Die von MSSearch verwendete Namenskonvention für Volltextkataloge lautet folgendermaßen:
SQL+dbid+catalogID
Katalog-IDs beginnen bei 00005 und werden für jeden neu erstellten Katalog um eins erhöht.
Als optimale Methode wird empfohlen, Volltextkataloge nach Möglichkeit in einem eigenen physischen Laufwerk (bzw. Laufwerken) zu erstellen. Da die Erstellung eines Volltextindex relativ E/A-intensiv ist (auf einer hohen Ebene gehören dazu das Lesen der Daten von SQL Server und das anschließende Schreiben des Index in das Dateisystem), sollten Sie vermeiden, dass das E/A-Subsystem zu einem Engpass wird.
Wie groß sind also die Volltextkataloge? Im Allgemeinen fügen Volltextkataloge rund 30% Overhead für die in SQL Server gespeicherte Datenmenge hinzu, für die ein Volltextindex erstellt wird. Diese grobe Schätzung hängt jedoch von der Verteilung eindeutiger Wörter (oder Schlüssel) in den Daten ab, sowie von den Wörtern, die als Füllwörter erachtet werden. Unter Füll- bzw. Stoppwörtern versteht man jene Wörter, die von Volltextindizes ausgeschlossen werden, da sie keine "interessanten" Suchbegriffe mit hoher Vorkommensrate darstellen und den Index nur aufblähen. Weiter unten werden wir darauf eingehen, nach welchen Kriterien Füllwörter ausgewählt werden sollen und wie sie so angepasst werden können, dass die Abfrageleistung verbessert wird.
Erstellen Sie für jede Tabelle, für die ein Volltextindex erstellt werden soll, einen eindeutigen, einspaltigen Index, der keine NULL-Werte zulässt. Dieser eindeutige Index wird dazu verwendet, jede Zeile der Tabelle einem eindeutigen, komprimierbaren Schlüssel zuzuordnen, der intern von MSSearch verwendet wird. Weisen Sie im Anschluss daran MSSearch an, einen Volltextindex für Ihre Tabellen zu erstellen. Beim Ausgeben der folgenden Anweisung für Ihre Tabelle wird diese zu einem Volltextkatalog Ihrer Wahl hinzugefügt (in diesem Fall zu dem weiter oben erstellten Katalog Cat_Desc):
exec sp_fulltext_table 'Categories', 'create', 'Cat_Desc',
'PK_Categories'
Der nächste Schritt besteht im Hinzufügen von Spalten zu diesem Volltextindex. Sie können für jede Spalte optional eine Sprache wählen. Wenn die Spalte den Typ IMAGE aufweist, müssen Sie eine andere Spalte festlegen, mit der angegeben werden kann, welcher Dokumenttyp in jeder Zeile der IMAGE-Spalte gespeichert ist.
Die Wahl der Spaltensprache richtet sich nach einigen wichtigen (und nicht gut dokumentierten) Kriterien. Diese Kriterien beziehen sich darauf, wie die Tokenisierung für den Text durchgeführt und der Text dann von MSSearch indiziert wird.
Der zu indizierende Text wird von einem so genannten Worttrennmodul verarbeitet, das die Tokenisierung anhand von Wortgrenzen durchführt. Diese Wortgrenzen sind in Englisch in der Regel ein Leerzeichen oder eine andere Form der Interpunktion. In anderen Sprachen, z.B. Deutsch, können Wörter oder Zeichen kombiniert werden. Bei der Wahl einer Sprache auf Spaltenebene sollten Sie deshalb jene Sprache angeben, die aller Voraussicht nach in den Zeilen dieser Spalte gespeichert wird.
Wenn Sie sich nicht sicher sind, empfiehlt sich in der Regel die Verwendung des neutralen Worttrennmoduls, bei dem die Tokenisierung ausschließlich anhand von Leerzeichen und Interpunktionen erfolgt. Ein weiterer Vorteil der Sprachauswahl auf Spaltenebene ist die Stammformbildung. Bei Volltextabfragen versteht man darunter den Vorgang der Suche nach allen Stammformen (flektierten Formen) eines Wortes in einer bestimmten Sprache.
Ein weiterer wichtiger Punkt bei der Sprachauswahl betrifft die Darstellung der Daten. Daten aus Nicht-IMAGE-Spalten werden nicht speziell gefiltert. Stattdessen wird der Text in der Regel in der vorliegenden Form an das Worttrennmodul übergeben. Worttrennmodule sind vor allem für die Verarbeitung von geschriebenem Text konzipiert. Wenn Ihr Text also eine Art von Markup aufweist (z.B. HTML), weist die Indizierung und Suche unter Umständen keine hohe linguistische Genauigkeit auf. In diesem Fall stehen Ihnen zwei Optionen zur Verfügung.
Die bevorzugte Methode besteht darin, die Textdaten in einer IMAGE-Spalte zu speichern und den Dokumenttyp anzugeben, damit sie gefiltert werden. Wenn dies nicht möglich ist, können Sie versuchen, das neutrale Worttrennmodul zu verwenden und nach Möglichkeit Markupdaten (z.B. "br" in HTML) zu den Füllwörterlisten hinzuzufügen. Dies hat den Nachteil, dass bei einer Spalte mit neutraler Sprache keine sprachenbasierte Stammformbildung durchgeführt wird. Unter bestimmten Bedingungen ist dies jedoch die einzig mögliche Option.
Da Sie nun die Optionen auf Spaltenebene kennen, können Sie eine oder zwei Spalten zum Volltextindex hinzufügen, indem Sie folgende Anweisung ausgeben:
exec sp_fulltext_column 'Categories', 'Description', 'add'
Es wird Ihnen auffallen, dass ich hier keine Sprache angegeben habe. In diesem Fall wird die Standard-Volltextsprache verwendet. Sie können die Standard-Volltextsprache für den Server mithilfe der gespeicherten Systemprozedur sp_configure festlegen.
Da nun alle Spalten zum Volltextindex hinzugefügt sind, können Sie mit der Auffüllung beginnen. Es ließe sich einiges Wissenswerte zu den verschiedenen Optionen im Zusammenhang mit Auffüllungsmethoden sagen. Ich möchte hier jedoch nicht zu sehr ins Detail gehen. Für dieses Beispiel sollten Sie einfach eine vollständige Auffüllung für die Tabelle durchführen und warten, bis der Vorgang abgeschlossen ist:
exec sp_fulltext_table 'Categories', 'start_full'
Sie können den Auffüllungsstatus mithilfe der FULLTEXTCATALOGPROPERTY- oder OBJECTPROPERTY-Funktion überwachen. Sie können den Katalogauffüllungsstatus durch Ausführen folgender Anweisung abrufen:
select FULLTEXTCATALOGPROPERTY('Cat_Desc', 'Populatestatus')
Wenn eine vollständige Auffüllung durchgeführt wird, wird als Ergebnis 1 zurückgegeben. Weitere Informationen zur Verwendung von FULLTEXTCATALOGPROPERTY und OBJECTPROPERTY finden Sie in der SQL Server-Onlinedokumentation.
Volltextabfragen
Das Abfragen von Volltextindizes unterscheidet sich in gewisser Hinsicht vom Ausführen von standardmäßigen relationalen Abfragen in SQL Server. Da die Indizes außerhalb von SQL Server gespeichert und verwaltet werden, wird die Verarbeitung von Volltextabfragen größtenteils von MSSearch durchgeführt. Abfragen, die ihrem Wesen nach teilweise relational und teilweise volltextbasiert sind, werden separat verarbeitet. Dies kann sich unter Umständen negativ auf die Leistung auswirken.
Beim Ausführen einer Volltextabfrage werden die Abfragebegriffe an MSSearch übergeben.
MSSearch durchläuft dann die internen Datenstrukturen (die Indizes) und gibt einen Schlüssel- und einen Rangwert an SQL Server zurück. Sie sehen die Schlüssel- und Rangwerte beim Ausführen einer CONTAINS- oder FREETEXT-Abfrage nicht. Wenn Sie jedoch eine CONTAINSTABLE oder FREETEXTTABLE-Abfrage ausführen, werden diese Werte angegeben. Sie werden dann in der Regel mit der Basistabelle verknüpft. Das Verknüpfen von Schlüsseln mit der Basistabelle kann sehr kostspielig sein. Ich werde weiter unten darauf eingehen, wie sich diese Verknüpfung reduzieren oder vollständig umgehen lässt.
Wenn Sie schon weiter vorausdenken und wissen, wie Volltextabfragen Daten zurückgeben, liegt die Vermutung nahe, dass CONTAINS/FREETEXT-Abfragen einfach eine CONTAINSTABLE/FREETEXTTABLE-Abfrage ausführen und eine Verknüpfung mit der Basistabelle erstellen. Diese Einsicht sollte Sie dazu bewegen, diese Abfragetypen zu vermeiden, außer die Kosten wären andernfalls noch höher.
Im Fall von Websuchanwendungen ist die Verwendung von CONTAINSTABLE und FREETEXTTABLE der Verwendung der TABLE-freien Entsprechungen vorzuziehen.
Sie wissen nun, dass Volltextabfragen eine besondere Art sind, auf Daten von MSSearch-Indizes zuzugreifen, die außerhalb von SQL Server gespeichert sind. Auch ist Ihnen klar, dass das unüberlegte Erstellen von Verknüpfungen mit der Basistabelle einige Probleme verursachen kann. Sie müssen auch den tatsächlichen Unterschied zwischen CONTAINS-basierten und FREETEXT-basierten Abfragen kennen.
CONTAINS-Abfragen führen eine genaue Übereinstimmung aller von Ihnen gesuchten Begriffe durch. Ob Sie nur nach einem einzelnen Wort oder nach allen Wörtern suchen, die mit "orange" beginnen, Sie erhalten nur Ergebnisse zurück, die alle Suchbegriffe enthalten. CONTAINS-Abfragen sind relativ schnell, da sie in der Regel weniger Ergebnisse zurückgeben und im Allgemeinen nicht viel zusätzlicher Verarbeitungsaufwand erforderlich ist.
Zu den Nachteilen von CONTAINS-Abfragen zählen die lästigen Probleme mit dem Filtern von Füllwörtern. Erfahrene Entwickler und DB-Administratoren, die in der Vergangenheit mit Volltextsuchen gearbeitet haben, kennen die gefürchtete Fehlermeldung, die sie darauf hinweist, dass die Abfrage nur Füllwörter enthält, wenn sie versuchen, Wörter oder Ausdrücke übereinzustimmen, die auch nur ein einziges Füllwort enthalten. Eine Möglichkeit, diesen Fehler zu vermeiden, besteht darin, Füllwörter vor dem Ausführen von Volltextabfragen herauszufiltern.
Auf eine CONTAINS-Abfrage, die Füllwörter enthält, können keine Ergebnisse zurückgegeben werden, da derartige Abfragen genaue Übereinstimmungen mit der gesamten Abfragezeichenfolge zurückgeben müssen. Da Füllwörter nicht volltextindiziert sind, werden bei einer CONTAINS-Abfrage, die Füllwörter enthält, möglicherweise keine Zeilen zurückgegeben.
FREETEXT-Abfragen meistern alle Probleme, die gelegentlich bei CONTAINS-Abfragen auftreten. Beim Durchführen einer FREETEXT-Abfrage führen Sie im Wesentlichen eine stammformbasierte Abfrage nach beliebigen Wörtern aus. Wenn Sie also z.B. nach "viel geben" suchen, werden die Begriffe "viel" und "geben" in allen Stammformen gebildet (die Stammformbildung ist sprachspezifisch; die verwendete Sprache wird von der zum Zeitpunkt der Indexerstellung angegebenen Volltextspaltensprache bestimmt und muss in allen abgefragten Spalten dieselbe sein), und es wird jede Zeile zurückgegeben, die mit mindestens einem dieser Begriffe übereinstimmt.
Der Nachteil von FREETEXT-Abfragen ist, dass die Prozessorauslastung oft höher ist als bei CONTAINS-Abfragen. Verantwortlich dafür sind die Stammformbildung und eine größere Menge an zurückgegebenen Ergebnissen, zusammen mit einer komplizierteren Berechnung des Rangs. Dennoch sind FREETEXT-basierte Abfragen erstaunlich flexibel, dabei extrem schnell und oft die beste Methode bei webbasierten Suchanwendungen.
Rangordnung und Optimierung
Ich werde oft von Benutzern von Volltextsuchen gefragt, was denn die Rangordnungsnummern bedeuten und wie sich diese in Werte übersetzen lassen, die leichter verständlich sind. Darauf gibt es eine kurze und eine lange Antwort, und ich werde mich der Einfachheit halber hier auf die kurze Variante beschränken. Diese Rangordnungszahlen sind bei Weitem nicht so wichtig wie die Reihenfolge, in der die Ergebnisse zurückgegeben werden.
Dies bedeutet einfach, dass beim Anordnen der Ergebnisse nach Rang die relevantesten Ergebnisse immer zuerst zurückgegeben werden. Die Rangwerte selbst können sich oft ändern - die Volltextsuche verwendet einen probabilistischen Rangordnungsalgorithmus, was bedeutet, dass die Wichtigkeit eines jeden zurückgegebenen Dokuments direkt von allen anderen Dokumenten im Volltextindex bestimmt wird.
Manchmal wird die Ansicht vertreten, dass durch das Wiederholen von häufig verwendeten Suchschlüsselwörtern in den volltextindizierten Spalten bestimmter Zeilen der Rang dieser Zeilen verbessert werden kann. Obwohl dieser Trick in beschränktem Maße die Chancen erhöhen kann, dass diese Zeilen bei bestimmten Schlüsselwörtern zuerst zurückgegeben werden, kann er in anderen Fällen ins Auge gehen und unter Umständen sogar die Abfrageleistung bei diesen Begriffen verschlechtern. Eine bessere Lösung ist die Implementierung eines "Best Bets"-Systems für die Suchanwendung (siehe das Beispiel weiter unten), sodass bestimmte Dokumente garantiert zuerst zurückgegeben werden.
Das Problem mit der umfassenden Duplizierung von Schlüsselwörtern liegt darin, dass die Volltextindizes für diese bestimmten Schlüsselwörter zu sehr aufgebläht werden und MSSearch mehr Zeit als nötig damit zubringen muss, die richtigen Zeilen zu finden und den Rang zu berechnen. Wenn Sie diese Methode bei einer Riesenmenge von volltextindizierten Daten ausprobiert haben, werden Sie vermutlich festgestellt haben, dass einige Volltextabfragen relativ lange dauern können. Wenn Sie ein schlankeres (und wahrscheinlich genaueres) "Best Bets"-System implementieren können, werden Sie vermutlich feststellen, dass sich dies enorm auf die Abfrageleistung auswirkt.
Ein weiteres Problem im Zusammenhang mit der umfassenden Duplizierung von Daten betrifft einen häufig verwendeten Trick zum Kombinieren von relationalen und Volltextabfragen. Dieses Problem ist unter Nutzern der Volltextsuche weit verbreitet und tritt dann auf, wenn versucht wird, auf Ergebnisse, die von einer Volltextabfrage zurückgegeben werden, eine Art Filter anzuwenden. Wie schon gesagt, geben Volltextabfragen einen Schlüssel und einen Rang für jede übereinstimmende Zeile zurück.
Um zusätzliche Informationen zu diesen Zeilen zu erhalten, muss eine Verknüpfung mit der Basistabelle durchgeführt werden. Da möglicherweise eine beliebige Anzahl von Ergebnissen von einer uneingeschränkten Volltextabfrage zurückgegeben werden kann, kann eine solche Verknüpfung unter Umständen recht kostspielig sein. Eine smarte Methode, diese Verknüpfung zu umgehen, besteht darin, die zu filternden Daten (falls möglich) einfach zum Volltextindex hinzuzufügen. Anders ausgedrückt: Wenn jemand das Schlüsselwort "Ichiro" im Text aller Artikel einer Zeitung finden möchte, jedoch nur Artikel aus dem Sportteil der Zeitung zurückgegeben werden sollen, werden Abfragen in der Regel wie folgt ausgedrückt:
METHODE 1
SELECT ARTICLES_TBL.Author, ARTICLES_TBL.Body, ARTICLES_TBL.Dateline,
FT_TBL [rank]
FROM FREETEXTTABLE(Articles, Body, 'Ichiro') AS FT_TBL
INNER JOIN Articles AS ARTICLES_TBL
ON FT_TBL [key] = ARTICLES_TBL.ArticleID
WHERE ARTICLES_TBL.Category = 'Sports'
METHODE 2
SELECT [key], [rank]
FROM CONTAINSTABLE(Articles, *, 'FORMSOF(INFLECTIONAL('Ichiro')
AND "sports"')
Das Problem bei diesen Abfragen liegt darin, dass sie entweder unnötig teuer sind oder dass falsche Ergebnisse zurückgegeben werden (bei der zweiten Abfrage taucht der Begriff "sports" höchstwahrscheinlich in Artikeln aller Kategorien auf). Es gibt andere Variationen dieser Methoden, aber dies sind zwei sehr einfache Varianten.
Ich schlage oft eine horizontale Partitionierung von Daten vor, wenn dies realisierbar ist. Anders ausgedrückt: Jeder mögliche Wert für die categories-Spalte wird zu einer eigenen Spalte (oder Tabelle), und die Schlüsselwörter, die sich auf diesen Artikel beziehen und nach denen gesucht werden kann, werden nur in dieser Spalte gespeichert. Anstatt eine einzige Body-Spalte und eine Category-Spalte zu verwenden, können Sie bei dieser Methode die Category-Spalte entfernen und nur eine Body_<category>-Spalte verwenden, in der die Schlüsselwörter gespeichert sind, nach denen gesucht werden kann.
Nachfolgend ein Beispiel dazu:
Wenn das Schema angepasst werden kann, funktioniert dies problemlos - jede Kategorie wird zu einer eigenen Spalte (oder Tabelle), und es wird nur dieser kleinere Volltextindex verwendet. Es gibt natürlich einige Problembereiche ...
SELECT [key], [rank]
FROM FREETEXTTABLE(Articles, Body_Sports, 'Ichiro')
Bei Systemen mit großen Datenmengen, bei denen diese (unter Umständen beträchtliche) Schemaänderung problemlos durchgeführt werden kann, sollte sich sofort eine beträchtliche Leistungsverbesserung abzeichnen. Es gibt natürlich Einschränkungen beim Anwenden von mehreren oder keinen Filtern. Diese Probleme lassen sich zweifelsohne auf andere Weise umgehen. Das obige Beispiel zeigt eine Methode auf, wie einige der Suchbedingungen in das Schema abstrahiert werden können. Im Wesentlichen wird dabei der Optimierer "getäuscht" (oder besser gesagt, "vorgetäuscht"), da derzeit nur eine geringe oder gar keine systemeigene Optimierung für Volltextabfragen innerhalb von SQL Server selbst möglich ist.
Weitere Leistungstricks
Ein anderes Anliegen, das oft an mich herangetragen wird, betrifft die Möglichkeit, Volltextabfrageergebnisse seitenweise zu durchlaufen. Anders ausgedrückt: Wenn ich eine Abfrage nach "Bratkartoffeln" ausgebe und jeweils 40 Ergebnisse gleichzeitig auf einer Webseite angezeigt werden sollen, sollen nur die 40 Ergebnisse für diese bestimmte Seite zurückgegeben werden (auf Seite 3 z.B. sollten nur die Ergebnisse 81-120 zurückgegeben werden).
Ich kenne verschiedene Methoden, um sich seitenweise durch Ergebnisse zu bewegen, aber keine ist hundertprozentig effizient. Bei der von mir vorgeschlagenen Methode können Sie die Anzahl der ausgeführten Volltextabfragen minimieren (um genau zu sein, nur eine Abfrage pro Resultset, das seitenweise durchlaufen werden soll) und den Webserver als einfachen Cache verwenden.
Auf einer hohen Ebene sollten Sie ein vollständiges Rowset von Schlüsseln und Rangwerten für die Volltextabfrage abrufen (Sie können nach Wunsch auch die Best Bets einbeziehen und allgemeine Filter in das Schema abstrahieren) und im Arbeitsspeicher auf dem Webserver speichern. (Je nach Anwendung und Last muss mit einer typischen Schlüsselgröße von < 32 Byte plus einer Ranggröße von < 4 Byte, also = < 36 Byte gerechnet werden. Multipliziert mit einem typischen zurückgegebenen Resultset von < 1000 Zeilen also < 35 KB. Unter der Annahme eines aktiv zwischengespeicherten Satzes von < 1000 aktiven Abfrageresultsets zu einem beliebigen Zeitpunkt ergibt das weniger als 35 MB RAM auf dem Webserver - kein schlechtes Ergebnis.)
Damit Sie sich seitenweise durch die Ergebnisse bewegen können, muss einfach ein auf dem Webserver im Arbeitsspeicher gespeichertes Array durchlaufen und ein SELECT für SQL Server für nur jene Zeilen und Spalten ausgegeben werden, die angezeigt werden sollen. Damit wird auch das Konzept verfolgt, nur Schlüssel und Ränge für Volltextabfragen zurückzugeben - ein SELECT (oder selbst viele) ist um vieles schneller als eine Volltextabfrage.
Die Verwendung von SELECT anstelle der Verknüpfung vieler Zeilen mit der Basistabelle in Verbindung mit verschiedenen anderen Methoden ermöglicht es Ihnen, mehr CPU-Zyklen auf den Computern mit SQL Server zu reservieren und Ihre (weniger kostspielige) Webfarm gezielter zu nutzen.
Eine Alternative zum webserverseitigen Zwischenspeichern besteht darin, die Resultsets in SQL Server selbst zwischenzuspeichern und verschiedene Methoden zum Navigieren durch diese Ergebnisse zu definieren. Obwohl sich dieser Artikel hauptsächlich auf das Anwendungsdesign auf Webserverebene (ASP) konzentriert, bieten die Programmierbarkeitsfeatures von SQL Server ein umfassendes Framework zum Erstellen von leistungsstarken Suchanwendungen für das Web.
Schlussfolgerung
Die Volltextsuchfunktionen von Microsoft SQL Server 2000 bieten eine robuste, schnelle und flexible Möglichkeit zum Indizieren und Abfragen von unstrukturierten, in einer Datenbank gespeicherten Daten. Da schnelle, genaue Suchfunktionen bei zahlreichen Anwendungen immer beliebter und wichtiger werden, muss beim Implementieren der Volltextsuchlösung darauf geachtet werden, dass die Vorteile der Geschwindigkeit und Präzision voll zum Tragen kommen. Durch das Verteilen der Rechenlast und das wohldurchdachte Organisieren der Daten können Sie unnötige Ausgaben für zusätzliche Hard- und Software und die durch unnötig langsame Abfragen bedingte Frustration vermeiden. Obwohl es immer zahlreiche Faktoren und Überlegungen gibt, die beim Entwickeln von leistungsfähigen Suchanwendungen eine Rolle spielen, hoffe ich doch, dass die in diesem Artikel bereitgestellten Informationen und Beispiele Ihnen bei der Erstellung einer optimalen Suchanwendung für das Web mit SQL Server 2000 helfen.
Anhang A: Implementieren von Best Bets bei Volltextsuchen
Eine mögliche Methode zum Verbessern der Volltextabfrageleistung und -effizienz ist die Implementierung eines "Best Bets"-Systems. Dieses System ist eine einfache Methode, um sicherzustellen, dass bestimmte Zeilen, die mit einem bestimmten Abfrageausdruck übereinstimmen, vor anderen zurückgegeben werden. In Ermangelung einer ausgefeilten vorprogrammierten Logik (z.B. bei SharePoint Portal Server) werden die Best Bets in der Regel manuell bestimmt.
Bei dem nachfolgenden Beispiel werden die Best Bets manuell bestimmt, und der eindeutige Schlüssel und eine Reihe von Schlüsselwörtern werden in einer separaten Tabelle gespeichert. Eine FREETEXTTABLE-Abfrage wird für die (sehr viel kleinere) Best Bets-Tabelle ausgeführt, und die von dieser Abfrage zurückgegebenen Ergebnisse werden mit den Ergebnissen der FREETEXTTABLE-Abfrage für die Basistabelle zurückgegeben. Aufgrund dieser Heuristiken werden alle Zeilen, die "Best Bets" sind, zuerst zurückgegeben, gefolgt in absteigender Reihenfolge von den Zeilen, die von MSSearch als am relevantesten erachtet werden.
Nachfolgend finden Sie ein sehr einfaches Beispielskript, mit dem ein "Best Bets"-System erstellt wird.
use myDb
create table documentTable(ftkey int not null, document ntext)
create unique index DTftkey_idx on documentTable(ftKey)
/*
Dokumente hier einfügen
(alle Dokumente müssen volltextindiziert sein)
*/
-- Volltextkatalog & Indizes für All Documents-Tabelle erstellen
exec sp_fulltext_catalog 'documents_cat', 'create', 'javascript:void(null);'
exec sp_fulltext_table 'documentTable', 'create', 'documents_cat',
'DTftkey_idx'
exec sp_fulltext_column 'documentTable', 'document', 'add'
exec sp_fulltext_table 'documentTable', 'start_change_tracking'
exec sp_fulltext_table 'documentTable', 'start_background_updateindex'
/*
Nun Best Bets-Tabelle und Indizes erstellen
(Dokumente hinzufügen, die immer zuerst zurückgegeben werden sollen)
*/
create table bestBets(ftKey int not null, keywords ntext)
create unique index BBftkey_idx on bestBets(ftKey)
/*
Best Bets hier einfügen
*/
-- Volltextkatalog & Indizes für Best Bets-Tabelle erstellen
exec sp_fulltext_catalog 'bestBets_cat', 'create', 'javascript:void(null);'
exec sp_fulltext_table 'bestBets', 'create', 'bestBets_cat', 'BBftkey_idx'
exec sp_fulltext_column 'bestBets', 'keywords', 'add'
exec sp_fulltext_table 'bestBets', 'start_change_tracking'
exec sp_fulltext_table 'bestBets', 'start_background_updateindex'
Zuerst wird eine generische All Documents-Tabelle erstellt, um alle Dokumente zu speichern, die volltextindiziert werden sollen. In der Regel sind in der Documents-Tabelle auch andere Spalten vorhanden, aber für den Zweck dieses Artikels verwenden wir nur zwei Spalten: den Schlüsselindex und das Dokument selbst. Für die Documents-Tabelle werden ein Volltextkatalog und -index erstellt.
Anschließend wird eine Best Bets-Tabelle erstellt, um spezielle Dokumente zu speichern, die bei allen Volltextabfragen zuerst zurückgegeben werden sollen. Diese Tabelle benötigt nur eine Full-Text key-Spalte und das Dokument selbst (Die Strategie zum Auswählen bestimmter Dokumente bei bestimmten Abfragen ließe sich dadurch verbessern, dass zusätzliche Schlüsselwörter zum Dokument hinzugefügt werden, die nicht im Dokument selbst enthalten sind.) Für die Best Bets-Tabelle werden ein Volltextkatalog und -index erstellt.
Die Best Bets-Tabelle und die Documents-Tabelle können entweder Dokumente gemeinsam nutzen (ein Dokument, das ein Best Bet ist, wird auch in der regulären Documents-Tabelle gespeichert, und es wird derselbe Schlüsselwert verwendet) oder sie können sich gegenseitig ausschließen (Best Bets-Dokumente werden nur in der Best Bets-Tabelle gespeichert). Zum leichteren Abrufen ist es unter Umständen einfacher, die Best Bets-Tabelle und die Documents-Tabelle getrennt zu halten. Dadurch müssen gemeinsame Treffer nicht aus den zurückgegebenen Best Bets- und regulären Suchergebnis-Rowsets entfernt werden. Andererseits ist es unter Umständen nicht praktisch, Dokumente auf diese Weise zu verwalten. In diesem Fall müsste Logik zu Abfragen hinzugefügt werden, um gemeinsame Dokumente aus den zurückgegebenen Rowsets zu entfernen.
Bei den obigen Tabellen können zwei gespeicherte Prozeduren erstellt werden, um die Best Bets- und Documents-Tabellen zu durchsuchen. Logik auf der Webserverebene oder eine zusätzliche gespeicherte Prozedur kann zum Zwischenspeichern und Präsentieren der gewünschten Ergebnisse verwendet werden. (Im nächsten Abschnitt finden Sie ein vollständiges, effizientes Beispiel für das Zwischenspeichern, die Präsentation und das Paging bei der Verwendung mit Best Bets.)
Hier zunächst eine gespeicherte Prozedur zum Abrufen von Best Bets-Zeilen, falls vorhanden:
create procedure BBSearch @searchTerm varchar(1024) as
select [key], [rank] from freetexttable(bestBets, keywords, @searchTerm) order by [rank] desc
Stellen Sie sicher, dass die eingehende Suchzeichenfolge bereinigt wurde, um das willkürliche Ausführen von T-SQL auf dem Server zu vermeiden, und stellen Sie sicher, dass die Zeichenfolge in einfache Anführungszeichen gesetzt ist. Die Verwendung von FREETEXTTABLE ist in diesem Fall der Verwendung von CONTAINSTABLE vorzuziehen, da FREETEXTTABLE die Stammformbildung verwendet und Best Bets findet, die mit einem beliebigen Suchbegriff übereinstimmen. Im Anschluss daran ruft eine zweite gespeicherte Prozedur Dokumente ab, die mit den regulären Suchkriterien übereinstimmen (falls vorhanden):
create procedure FTSearch @searchTerm varchar(1024) as
select [key], [rank] from freetexttable(documentTable, keywords, @searchTerm) order by [rank] desc
Stellen Sie wiederum sicher, dass die eingehende Suchzeichenfolge bereinigt wurde und in einfache Anführungszeichen gesetzt ist.
Beim Ausführen dieser gespeicherten Prozeduren sollte derselbe Suchbegriff an beide übergeben werden, wobei die Best Bets-Suche zuerst ausgeführt wird, gefolgt von der regulären Volltextsuche. Der nächste Abschnitt enthält einen breiteren Überblick darüber, wie Best Bets zusammen mit anderen Volltextsuchtechniken beim Erstellen von Websuchanwendungen verwendet werden können.
Anhang B: Eine Beispielanwendung unter Verwendung von Best Bets, Ergebnispaging und effizienter Volltextabfragelogik
In diesem Beispiel implementieren wir eine Websuchanwendung, die beinahe alle der in diesem Artikel vorgestellten Optimierungen verwendet. Wir verwenden das einfache Szenario eines Suchmoduls für den Katalog eines Onlinehändlers und setzen ein hohes Verkehrsaufkommen voraus, wobei die Kunden die Ergebnisse in sehr kurzen Antwortzeiten erwarten. Die Best Bets-Tabellen und gespeicherten Prozeduren aus dem vorherigen Abschnitt werden in diesem Beispiel verwendet.
Diese Anwendung stellt ein einfaches Beispiel einiger Taktiken auf höherer Ebene dar, die eingesetzt werden können, um die bestmögliche Volltextsuchleistung zu erzielen. Dieses Beispiel verwendet ASP, es können jedoch auch ISAPI, ASP.NET oder andere Plattformen verwendet werden, um ähnliche Lösungen mit ihren jeweiligen Stärken und Schwächen zu implementieren. Die Verwendung des Sitzungsobjekts wird nicht immer für alle Anwendungen empfohlen und kann bei falscher Verwendung negative Folgen haben. In diesem Fall verwenden wir das Sitzungsobjekt, um einen schnellen Zwischenspeicherungsmechanismus zu implementieren. Diese Funktion kann auf verschiedenen Ebenen auf zahlreiche andere Arten implementiert werden.
Nachfolgend der generische Code für die ASP-Seite:
<% @Language = "VBScript" %>
<% Response.buffer = true %>
<html>
<head>
<title>VT-Test</title></head>
<body>
<pre<
----------------- Begin Test ------------------
<%
Dim firstRow ' beim Paging der Zeilen das Element, mit dem begonnen wird
Dim lastRow ' letzte Zeile beim Paging
Dim pageSize ' Größe der Seite (wie viele Zeilen gleichzeitig)
Dim cn ' Verbindungsobjekt
Dim rs ' Resultset für Volltextschlüssel/Rang (wieder verwendet)
Dim useCache ' Cache verwenden oder Volltextzugriff (0, nicht verwenden; 1, verwenden)
Dim alldata ' zwischenzuspeicherndes Ergebnisrowset
Dim bbdata ' zwischenzuspeicherndes Best Bets-Rowset
Dim connectionString ' SQL-Verbindungszeichenfolge
' festlegen, ob vom Cache abgerufen wird oder nicht
' Standard FALSE, andernfalls eingehende verwenden
if (request.Form("useCache") <> "") then
useCache = request.Form("useCache")
elseif (request.QueryString("useCache") <> "") then
useCache = request.QueryString("useCache")
else
useCache = 0
end if
' Konstanten festlegen
pageSize = 24
firstRow = 0
lastRow = 23
connectionString = <Ihre Verbindungszeichenfolge hier>
'----------------------------------------------------------------'
' zeigt ein einfaches Schlüssel/Rang-Paar an, das mit Best Bets/Suchbegriff übereinstimmt'
'----------------------------------------------------------------'
Private Sub SearchNPage()
Dim p ' Zähler für Durchlaufen der Zeilen
Dim numRows ' Anzahl der Zeilen, gesamt, in Cache/Resultset
if (useCache <> "1") then ' Best Bets/Ergebnisse abrufen und zwischenspeichern
Dim queryArg ' eingehender Abfragebegriff
if (request.Form("searchTerm") <> "") then
queryArg = request.Form("searchTerm")
elseif (request.QueryString("searchTerm") <> "") then
queryArg = request.QueryString("searchTerm")
else
response.Write("Kein Suchbegriff angegeben" & VbCrLF)
exit sub
end if
' im Idealfall sollte Abfragebegriff hier bereinigt werden
' benutzerdefinierte Reinigungslogik hinzufügen, um willkürliche
' SQL-Ausführung zu verhindern
' CleanString(queryArg) aufrufen
' Verbindung mit SQL herstellen
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open connectionString
' Best Bets-Übereinstimmungen durch Übergeben der bereinigten Zeichenfolge abrufen
set rs = cn.Execute("exec BBSearch '" & queryArg & "'")
' Best Bets abrufen, falls vorhanden
if not(rs.EOF) then
bbData = rs.GetRows
end if
' nun reguläre Übereinstimmungen durch Übergeben der bereinigten Zeichenfolge abrufen
set rs = cn.Execute("exec FTSearch '" & queryArg & "'")
' falls nichts zurückgegeben wird, beenden
if (rs.EOF and IsEmpty(bbdata)) then
response.Write("Keine übereinstimmenden Zeilen gefunden" & VbCrLF)
call ConnClose
exit sub
end if
' andernfalls Zeilen abrufen, falls vorhanden
if not(rs.EOF) then
alldata = rs.GetRows
Session("results") = alldata
end if
call ConnClose
else ' von Cache laden (usecache=1)
alldata = Session("results")
' auch Zeilenbereich für die Verwendung hier abrufen
if (request.Form("firstRow") <> "") then
firstRow = request.Form("firstRow")
lastRow = firstRow+pageSize
elseif (request.QueryString("firstRow") <> "") then
firstRow = request.QueryString("firstRow")
lastRow = firstRow+pageSize
end if
end if ' useCache<>TRUE
' für diese Anwendung einfach alle Best Bets ausdrucken
' (eventuell größer als Seitengröße), dann Paging der regulären Ergebnisse
' Annahme, dass bei Verwendung des Caches zuvor
' Best Bets angezeigt wurden - falls keine Best Bets vorhanden, fortfahren
if not(IsEmpty(bbdata)) then
response.Write("BEST BETS:" & VbCrLf)
for p = 0 to ubound(bbdata, 2)
response.Write(bbData(0,p) & " " & bbData(1,p) & VbCrLf)
next
response.Write(VbCrLf)
end if
' Suchergebnisse zurückgeben, falls vorhanden (möglicherweise nur Best Bets vorhanden)
if not(IsEmpty(alldata)) then
if uBound(alldata, 2) < lastRow then
lastRow = uBound(allData, 2)
end if
response.Write("SUCHERGEBNISSE:" & VbCrLf)
for p = firstRow to lastRow
response.Write(allData(0,p) & " " & allData(1,p) & VbCrLf)
next
end if ' not(IsEmpty(alldata))
End Sub
'----------------------------------------------------------------'
' Verbindungsobjekte schließen und bereinigen'
'----------------------------------------------------------------'
Private Sub ConnClose
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
call SearchNPage
%>
---------------- Test Complete ----------------
<form action="<Diese Seite>" method="post">
<input type=submit value="next <%=pageSize%> rows" NAME="Submit1">
<input type=hidden name="useCache" value="1">
<input type=hidden name="firstRow" value=<%=lastrow+1%>>
</form>
</pre>
</body>
</html>
Eine einfache HTML-Formularseite kann obiges Skript ausführen:
<html>
<head><title>Suchbegriffe eingeben</title>
</head>
<body>
<form action="<ASP-Seite durchsuchen>" method="post">
Suchbegriff: <input name="searchTerm">
<p>
<input type="submit" value="Search">
</form>
</body>
</html>
Wie sich aus den zwei obigen Codebeispielen erkennen lässt, ist es nicht sehr aufwändig, eine Webanwendung zu erstellen, die eine effiziente Volltextabfrage (mit Best Bets) ausführen kann, und die Ergebnisse zwischenzuspeichern und seitenweise zu durchlaufen. Logik kann mit minimalem Overhead hinzugefügt werden, um zusätzliche Daten bereitzustellen, die Darstellung der Best Bets zu verbessern und durch die Suchergebnisse zu navigieren. (Es wird auch nachdrücklich empfohlen, zusätzliche wohldurchdachte Logik für die Fehlerbehandlung, Sicherheit und das Bereinigen von eingehenden Daten zu implementieren).
Anhand der weiter oben angeführten Empfehlungen und Beispiele auf hoher Ebene sollte das Design und die Implementierung einer schnellen und skalierbaren Websuchanwendung mithilfe der SQL Server 2000-Volltextsuche durchaus durchführbar sein.
Anhang C: Ressourcen
Full-Text Search Deployment (in Englisch)
Ein gutes Referenzdokument für all jene, die sich gerade erst mit der Volltextsuche vertraut machen. Dieser Artikel behandelt Auffüllungsmethoden, Hard- und Softwareanforderungen und enthält Tipps und Tricks sowie weitere Dokumentation zum Arbeiten mit der SQL Server 2000-Volltextsuche.
Full-Text Search Public Newsgroup (microsoft.public.sqlserver.fulltext, in Englisch)
Eine hervorragende Newsgroup für Antworten auf Fragen zur Volltextsuche und für hilfreiche Tipps und Tricks. Die Full-Text Search Newsgroup wird von Mitgliedern des SQL Server-Entwicklungsteams und sehr erfahrenen Microsoft MVPs (Most Valuable Professionals) besucht.