Teil 3: Indexformen für bestimmte Verwendungszwecke

SQL Server Indizes – Spezielle Indexformen
Kommentare

Nachdem in den ersten beiden Teilen der Serie die Grundlagen für Erstellung und Wartung der allgemeineren Indexformen behandelt wurden, widmet sich dieser Teil den speziellen Indexformen, die für bestimmte Verwendungszwecke entworfen wurden.

SQL Server bietet eine Reihe von Indexformen, die für bestimmte Verwendungszwecke (meist in Zusammenhang mit bestimmten Datentypen) ausgelegt sind und auch nur dort Sinn ergeben. Einige dieser Indexformen sind mittlerweile – genau wie die dazugehörenden Datentypen – schon recht alt, werden aber in der Praxis oft noch zu selten angewendet, da teilweise spezielle Zugriffsfunktionen nötig sind, um sie zu nutzen.

Artikelkasten
Teil 1: Index-Basics (klassische Indexvarianten)
Teil 2: Zusätzliche Optionen und Wartung von Indizes
Teil 3: Indexformen für spezielle Verwendungszwecke
Teil 4: Spaltenbasierte Indizes und Fazit

Volltextindizes

Eine Einschränkung der allgemeinen Indexformen liegt darin, dass sich damit keine Spalten der Datentypen varchar(max), nvarchar(max) (bzw. ebenso deren Vorgänger text und ntext) indizieren lassen. Das ist mit der Verwendung von Volltextindizes möglich. Doch Volltextindizes bieten noch eine Fülle an weiteren Möglichkeiten, die sich auch in Zusammenhang mit anderen alphanumerischen Datentypen einsetzen lassen, aber dazu später mehr [1].

Im Gegensatz zu den klassischen Indexformen werden Volltextindizes in separaten Volltextkatalogen angelegt und verwaltet, die in der Datenbank definiert werden. Bei kleinen Datenbanken reicht es aus, einen einzigen Katalog anzulegen, bei größeren Datenbanken ist es bewährte Praxis, pro Datenbankschema einen separaten Volltextkatalog zu definieren. Die Erstellung eines solchen Kataloges erfolgt mit der Anweisung:

CREATE FULLTEXT CATALOG AdventureWorks_FTC AS DEFAULT

Für die Benennung des Katalogs bietet sich entweder der Name der Datenbank an, oder – bei Verwendung von separaten Katalogen pro Datenbankschema – der Name des Schemas, jeweils gefolgt von dem Suffix _FTC.

Ist der Volltextkatalog einmal angelegt, lässt sich darin pro Tabelle ein Volltextindex anlegen. Das mag erst einmal nach einer Einschränkung klingen, stellt jedoch keine wirkliche Einschränkung dar, da man in diesen Index einfach alle zu indizierenden Spalten aufnimmt und die Reihenfolge der Spalten darin keine Rolle spielt. Somit besteht überhaupt keine Notwendigkeit, mehrere Indizes für eine Tabelle zu definieren. Aus demselben Grund erübrigt es sich auch, dem Volltextindex einen Namen zu geben, da auf diesen einfach über die Kombination aus Volltextkatalog und Tabellennamen verwiesen werden kann. Mit der folgenden Anweisung wird ein Volltextindex angelegt:

CREATE FULLTEXT INDEX ON Person.Person
KEY_INDEX PK_Person_BusinessEntityID ON (AdventureWorks_FTC)
WITH (CHANGE_TRACKING AUTO)

Dabei wird über das Schlüsselwort KEY_INDEX ein einspaltiger Index angegeben, den SQL Server unter anderem für die Rangfunktionen (siehe unten) benötigt. In den meisten Fällen wird man hier den Primärschlüssel nutzen. Sollte die Tabelle jedoch einen mehrspaltigen Primärschlüssel verwenden, kann man stattdessen auch einen anderen Index angeben, sofern dieser eindeutig, einspaltig und nicht Nullable ist. Nach dem Anlegen des Index kann man diesem Spalten hinzufügen, was in einer oder mehreren Anweisungen geschehen kann:

ALTER FULLTEXT INDEX ON Person.Person ADD (FirstName, LastName)
ALTER FULLTEXT INDEX ON Person.Person ADD (MiddleName)

Wie bei den bekannten klassischen Indexformen müssen auch Volltextindizes regelmäßig gewartet werden. Dafür spielt die CHANGE_TRACKING-Eigenschaft, die bei Erstellung des Index festgelegt wurde, eine entscheidende Rolle. Folgende Werte sind möglich:

  • AUTO: Nach jeder Änderung einer Zeile wird auch der Volltextindex aktualisiert. Das kann eine leichte Verzögerung beim Speichern von Datenänderungen zur Folge haben.
  • MANUAL: Nach einer Datenänderung merkt sich SQL Server, welche Zeile geändert wurde. Dadurch können die Datenänderungen mit folgender Anweisung zu einem späteren Zeitpunkt gebündelt im Volltextindex aktualisiert werden: ALTER FULLTEXT INDEX ON Person.Person START UPDATE POPULATION. Nachteilig ist dabei allerdings, dass der Index eigentlich nie hundertprozentig aktuell ist.
  • OFF: Es findet weder eine Aktualisierung des Volltextindex statt, noch werden die geänderten Zeilen markiert. Damit eignet sich diese Option vor allem für Tabellen, die sich nie oder nur sehr selten ändern. Um den Volltextindex zu aktualisieren, muss er mit folgender Anweisung komplett neu aufgebaut werden: ALTER FULLTEXT INDEX ON Person.Person START FULL POPULATION.

Während über die gerade beschriebenen Methoden jeweils ein Volltextindex einer einzelnen Tabelle aktualisiert wird, gibt es auch die Möglichkeit, einen gesamten Volltextkatalog zu aktualisieren. Dazu stellt SQL Server – analog zu den klassischen Indexformen – die Varianten REBUILD und REORGANIZE zur Verfügung:

CREATE FULLTEXT CATALOG AdventureWorks_FTC REBUILD
CREATE FULLTEXT CATALOG AdventureWorks_FTC REORGANIZE

Dabei arbeitet REBUILD effektiver, nimmt aber für die Dauer der Wartung den gesamten Volltextkatalog offline. REORGANIZE benötigt dagegen zwar länger, dafür bleibt der Volltextkatalog während der Wartung weiterhin verwendbar.

Um einen Volltextindex in Abfragen zu nutzen, sind spezielle Funktionen notwendig, die dafür auch eine erweiterte Funktionalität bieten. Im einfachsten Fall wird die CONTAINS-Funktion genutzt, um alle im Volltextindex enthaltenen Spalten nach einem Begriff zu durchsuchen:

SELECT * FROM Person.Person WHERE CONTAINS(*, 'Ross')

Dabei werden sowohl Zeilen gefunden, die den Namen „Ross“ im Vornamen enthalten, als auch solche, in denen dieser Wert als Nachname gespeichert ist. Hier zeigt sich also bereits ein großer Vorteil der Volltextindizes, wobei zu beachten ist, dass der Stern („*“) sich nicht auf alle Spalten der Tabelle, sondern nur auf die im Volltextindex zu dieser Tabelle enthaltenen Spalten bezieht. Alternativ lässt sich aber auch explizit angeben, in welcher Spalte nach dem angegebenen Wert gesucht werden soll:

SELECT * FROM Person.Person WHERE CONTAINS(FirstName, 'Ross')

Spannender wird es, sobald größere Spalten durchsucht werden, die nicht ein Wort, sondern ganze Texte enthalten, was sich gut an dem Volltextindex auf der Tabelle Production.ProductReview veranschaulichen lässt, der auch die Spalte Comments beinhaltet. So listet die folgende Abfrage alle Zeilen der Tabelle auf, deren Comments-Spalte das Wort „bike“ enthält:

SELECT * FROM Production.ProductReview WHERE CONTAINS(Comments, 'bike')

Mit einer anderen Syntax ist es sogar möglich, nach anderen Varianten desselben Wortstamms zu suchen. So listet die folgende Abfrage auch Einträge auf, die beispielsweise das Wort „biking“ enthalten:

SELECT * FROM Production.ProductReview
WHERE CONTAINS(Comments, 'FORMSOF(INFLECTIONAL, "bike")')

Selbst die Suche nach komplett anderen Wörtern mit derselben Bedeutung ist durch die Verwendung eines Thesaurus möglich. Diese liegen als vorbereitete XML-Dateien für verschiedene Sprachen im folgenden Ordner (je nach SQL-Server-Version variiert der Ordnername etwas) bereit und können daher mit einem beliebigen Editor erweitert werden: C:\Programme\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\FTData.

Neben der CONTAINS-Funktion gibt es noch die FREETEXT-Funktion, die als Parameter auch ganze Sätze entgegennimmt und daraus die für eine Suche relevanten Begriffe herausfiltert:

SELECT * FROM Production.ProductReview
WHERE FREETEXT(*, 'show me reviews about socks or bikes')

Zu beiden Funktionen (CONTAINS und FREETEXT) gibt es auch eine entsprechende Tabellenwertfunktion (CONTAINSTABLE und FREETEXTTABLE), die als Ergebnis den Wert des bei der Indexdefinition definierten KEY_INDEX sowie einen Rang zurückgibt, anhand dessen bestimmt werden kann, wie der Suchtreffer zu bewerten ist. Dabei wird als RANK ein Wert zwischen 0 und 1000 zurückgegeben, wobei die höheren Werte eine bessere Übereinstimmung mit dem Suchbegriff bedeuten. Wenn man dieses Ergebnis über den KEY_INDEX mit der Tabelle selbst verbindet, lassen sich die Treffer leicht nach deren Wertigkeit sortieren (Abb. 1):

SELECT *
FROM FREETEXTTABLE(Production.ProductReview, *, 'bike') AS ft
INNER JOIN Production.ProductReview AS pr on ft.[KEY] = pr.ProductReviewID
ORDER BY [RANK] DESC
panther_sqlindizes_1

Abb. 1: Das Ergebnis der Volltextabfrage nach Treffergenauigkeit sortiert

Volltextindizes bieten noch eine ganze Reihe weiterer Varianten und Möglichkeiten, wie beispielsweise das Herausfiltern von Noise Words, die sich über so genannte Stop Lists definieren lassen. Einen guten Einstieg in die Thematik findet sich auch unter [2].

Neben den hier beschriebenen konventionellen alphanumerischen Datentypen lassen sich mithilfe so genannter iFilter auch binäre Spalten, die beispielsweise PDF- oder Word-Dateien beinhalten, sowie XML-Spalten indizieren. Bei der Verwendung von Volltextindizes auf XML-Spalten werden jedoch nur die Inhalte der jeweiligen Dokumente (also Werte von Attributen und Inhalte von Elementen), nicht aber deren Struktur im Index abgelegt. Die XML-Tags selbst werden also ignoriert. So kann man mit folgender Abfrage in der XML-Spalte Resume der Tabelle HumanResources.JobCandidate alle Einträge suchen, in denen das Wort „Bachelor“ auftaucht:

SELECT *
FROM HumanResources.JobCandidate
WHERE CONTAINS(Resume, 'Bachelor')

Die AdventureWorks-2014-Datenbank liefert hier fünf Zeilen zurück. Wenn man die jeweiligen XML-Lebensläufe öffnet, zeigt sich, dass das gesuchte Wort an verschiedenen Stellen auftauchen kann. Um gezielt auf einzelne Attribute oder Elemente einer XML-Struktur zuzugreifen, gibt es daher – ebenfalls seit SQL Server 2005 – spezielle XML-Zugriffsmethoden mit dazu passenden Indizes.

Schnell und überall: Datenzugriff mit Entity Framework Core 2.0

Dr. Holger Schwichtenberg (www.IT-Visions.de/5Minds IT-Solutions)

C# 7.0 – Neues im Detail

Christian Nagel (CN innovation)

XML-Indizes

Zur Abfrage von XML-Spalten unterstützt SQL Server die XML Query Language (oder kurz: XQuery). Diese verwendet verschiedene Methoden, um auf XML-Strukturen zuzugreifen [3].

  • query: Abfrage von XML-Inhalten über XQuery
  • value: Abfrage von Skalarwerten aus einem XML-Dokument
  • exist: Prüfen, ob ein Element/Knoten in einem XML-Dokument existiert
  • modify: Inhalte eines XML-Dokuments verändern
  • nodes: XML-Daten in relationale Daten umformen

Um beispielsweise in der Tabelle HumanResources.JobCandidate gezielt im Element /Education/Edu.Level nach dem Eintrag „Bachelor“ zu suchen, kann man die value-Funktion nutzen, wodurch sich das Suchergebnis der vorherigen Abfrage auf vier Zeilen reduziert (in der fünften Zeile kam zwar der Begriff „Bachelor“ vor, allerdings nicht im Element Edu.Level):

WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS "RES")
SELECT JobCandidateId, [Resume].query('//RES:Education') AS Education
FROM HumanResources.JobCandidate
WHERE [Resume].value('(//RES:Education/RES:Edu.Level)[1]',
  'nvarchar(200)') = 'Bachelor'

Das funktioniert sogar, ohne vorher einen XML-Index anzulegen. Allerdings ist diese Variante bei großen Tabellen nicht unbedingt schnell, was daran liegt, dass XML-Spalten bei der Abfrage intern in ein relationales Format konvertiert werden. Ohne XML-Index geschieht dies zur Laufzeit; mit einem primären XML-Index, der alle Tags, Werte und Pfade indiziert, kann dies persistiert werden:

CREATE PRIMARY XML INDEX PXML_JobCandidate_Resume
ON HumanResources.Jobcandidate([Resume])

Auf Basis eines primären XML-Index können weitere, so genannte sekundäre XML-Indizes definiert werden, die sich im Prinzip wie relationale Indizes verhalten, aber sich statt auf einen gruppierten Index immer auf den primären XML-Index beziehen. Sekundäre XML-Indizes gibt es in drei verschiedenen Varianten:

  • PATH: Indiziert die Pfade der XML-Dokumente in der angegebenen Spalte, wodurch Abfragen von Werten beschleunigt werden, deren XML-Pfad innerhalb des Dokuments bekannt ist.
  • VALUE: Indiziert die Werte von XML-Elementen und beschleunigt Abfragen von bestimmten Werten, deren XML-Pfad nicht – oder nur teilweise – bekannt ist.
  • PROPERTY: Indiziert Attributwerte von einzelnen XML-Dokumenten, womit Abfragen dieser Werte innerhalb eines XML-Dokuments beschleunigt werden.

Mit der folgenden Anweisung kann beispielsweise ein sekundärer PATH-Index erzeugt werden:

CREATE XML INDEX IXML_JobCandidate_Resume_Path
ON HumanResources.Jobcandidate([Resume])
USINGX XML INDEX PXML_JobCandidate_Resume FOR PATH

XML-Indizes bringen allerdings auch eine Reihe von Einschränkungen mit sich. Dass diese nur auf XML-Spalten erzeugt werden können, ist offensichtlich. Wenn eine Tabelle mehrere XML-Spalten enthält, kann kein kombinierter Index erstellt werden. Stattdessen muss für jede Spalte ein separater XML-Index erstellt werden. Dabei liegt die Obergrenze bei 249 XML-Indizes pro Tabelle, was für die meisten Tabellen ausreichen sollte. Generell zu beachten ist auch der recht hohe Speicherbedarf von XML-Indizes.

Die Wartung von XML-Indizes erfolgt wie bei den klassischen Indexvarianten über die ALTER INDEX-Anweisung mit dem Zusatz REBUILD (Neuaufbau des Index) oder alternativ REORGANIZE (Neustrukturierung des Index). Weitere Informationen zu XML-Indizes sind hier zu finden.

Räumliche Indizes

Mit SQL Server 2008 kam die neue Möglichkeit hinzu, .NET-basierte Datentypen selbst zu implementieren, wobei SQL Server ein paar auch direkt mitgeliefert hat. Dazu gehören neben den gerade behandelten XML-Spalten auch die Geodatentypen GEOMETRY und GEOGRAPHY, die sich dadurch unterscheiden, dass sich mit der erstgenannten Variante zweidimensionale Punkte oder komplexere geometrische Objekte abbilden lassen, während GEOGRAPHY – wie der Name schon vermuten lässt – zur Speicherung von geografischen Daten verwendet wird.

Dies wird beispielsweise in Adresstabellen häufig genutzt, um die geografische Lage der Adresse als Kombination aus Breiten- und Längengrad zu speichern. Zwar wäre das auch mithilfe von zwei numerischen Feldern realisierbar, allerdings bieten beide Geodatentypen über spezielle Zugriffsmethoden zusätzliche Möglichkeiten, mit denen sich beispielsweise feststellen lässt, ob sich zwei geometrische Figuren überschneiden (STIntersects) oder auch der Abstand zwischen zwei geografischen Punkten berechnen lässt (STDistance).

Damit das auch performant umgesetzt wird, wurden speziell für diese Datentypen auch entsprechende Indexvarianten eingeführt. In der ursprünglichen Form wird dazu der indizierte Raum in ein Raster mit vier Ebenen geteilt, wobei zu jeder Ebene ein Detaillierungsgrad in der Form LOW (4 x 4 Zellen), MEDIUM (8 x 8 Zellen) oder HIGH (16 x 16 Zellen) anzugeben ist. Mit der folgenden Anweisung wird beispielsweise ein Index für die GEOGRAPHY-Spalte SpatialLocation der Tabelle Person.Address erstellt:

CREATE SPATIAL INDEX SX_Address_SpatialLocation
ON Person.[Address] (SpatialLocation)
USING GEOGRAPHY_GRID
WITH (GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM,
LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16)

Seit SQL Server 2012 gibt es eine alternative Syntax, über die der indizierte Raum wahlweise auch in ein Raster mit acht Ebenen geteilt werden kann, was zwar mehr Speicher erfordert, aber deutlich selektiver und damit effizienter ist. Dazu kann der Detaillierungsgrad über die Option GEOGRAPHY_AUTO_GRID auch automatisch bestimmt werden, was die Syntax der Anweisung erheblich vereinfacht:

CREATE SPATIAL INDEX SX_Address_SpatialLocation
ON Person.[Address] (SpatialLocation)
USING GEOGRAPHY_AUTO_GRID
WITH (CELLS_PER_OBJECT = 12)

Die alte Syntax (mit den alten Einschränkungen) wird zwar weiterhin unterstützt, arbeitet aber in vielen Fällen weniger effektiv, sodass man ab SQL Server 2012 die neue Variante nutzen sollte. Der Autor dieses Artikels hat in der Praxis Fälle erlebt, in denen lediglich durch die Umstellung der Syntax bei der Indexerstellung die Dauer einer Abfrageausführung von 20 Sekunden auf weniger als 1 Sekunde reduziert werden konnte.

Dazu sind räumliche Indizes ab SQL Server 2012 (Enterprise Edition) auch komprimierbar, wodurch etwa 40 bis 50 Prozent Speicherplatz gespart werden kann.

Listing 1 zeigt, wie der angelegte räumliche Index genutzt werden kann, um anhand der Geokoordinaten einer Adresse, die zuvor in der Variablen @Location gespeichert werden, über die STDistance-Methode die zehn am nächsten liegenden Adressen aus derselben Tabelle zu lesen.

Listing 1: Verwendung eines räumlichen Index

DECLARE @Location AS GEOGRAPHY

SELECT TOP 1 @Location=SpatialLocation
FROM Person.[Address]

SELECT TOP 10 SpatialLocation.STDistance(@Location), *
FROM Person.[Address]
ORDER BY SpatialLocation.STDistance(@Location) ASC

Als wesentliche Einschränkung ist bei den räumlichen Indizes zu beachten, dass die Tabelle auch über einen gruppierten Primärschlüssel verfügen muss, auf den der räumliche Index verweisen kann. Zur Wartung dieser Indexform finden wieder einmal die Variante REORGANIZE und REBUILD Anwendung.

Fazit

Die beschriebenen speziellen Indexformen sind zwar nur für bestimmte Datentypen nutzbar, bieten dort aber verschiedene Zusatzmöglichkeiten, die sonst nur schwer zu realisieren wären. Dafür ist auch jeweils eine spezielle Syntax für den Zugriff erforderlich. Natürlich war es aus Platzgründen hier nicht möglich, alle Aspekte der beschriebenen Indexvarianten zu behandeln. Die wesentlichen Einsatzmöglichkeiten sowie Vor- und Nachteile sollten jedoch nun bekannt sein.

In der nächsten Folge der Serie wird auf die noch relativ neuen spaltenbasierten Indizes eingegangen, mit denen sich teilweise beachtliche Performancesteigerungen erzielen lassen. Da dies auch die letzte Folge dieser Artikelserie sein wird, ist dort auch ein abschließendes Fazit zur gesamten Serie zu finden, das die verschiedenen Indexformen noch einmal vergleichend gegenüberstellt.

Links & Literatur

[1] Panther, Robert: „SQL Server Performance Ratgeber“, Kapitel 4.5 – Volltextindizes, entwickler.press, 2010, ISBN 978-3-86802-030-4
[2] Nielsen, Paul; Delaney, Kalen; Low, Greg; Machanic, Adam; Randal, Paul S.; Tripp, Kimberly L.: „SQL Server MVP Deep Dives“. Cain, Robert C.: Chapter 13 – „Full-text searching“, Manning Publications, 2010, ISBN 978-1-93518-204-7
[3] Nielsen, Paul; Delaney, Kalen; Low, Greg; Machanic, Adam; Randal, Paul S.; Tripp, Kimberly L.: „SQL Server MVP Deep Dives“. Coles, Michael: Chapter 10 – „Introduction to XQuery on SQL Server“, Manning Publications, 2010, ISBN 978-1-93518-204-7

Windows Developer

Windows DeveloperDieser Artikel ist im Windows Developer erschienen. Windows Developer informiert umfassend und herstellerneutral über neue Trends und Möglichkeiten der Software- und Systementwicklung rund um Microsoft-Technologien.

Natürlich können Sie den Windows Developer über den entwickler.kiosk auch digital im Browser oder auf Ihren Android- und iOS-Devices lesen. In unserem Shop ist der Windows Developer ferner im Abonnement oder als Einzelheft erhältlich.

Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -