SQL Server : Index Leitfaden Teil 2 – Vergabe von Indizes

Im Teil 1 Index Leitfaden Teil 1- Index für Rookies haben wir die den gruppierten und nicht gruppierten Index vorgestellt. Nun wollen wir mal sehen, auf was es bei der Vergabe der Indizes ankommt.

Wir wissen bereits, dass Nicht gruppierte Indizes gut für Abfragen bei relativ geringen Ergebnismengen ist. Der Gruppierte Index dagegen spielt seine Vorteile bei Abfragen aus, die relativ große Ergebnismengen bringen bzw. bei Abfragen, die sich auf Bereiche beziehen.

Man kann also folgendes feststellen:

Where Bedingung

Die where Bedingung ist das wichtigste Element in einer Abfrage. Diese entschiedet, ob und welcher Index am besten geeignet ist.

Der gruppierte Index ist vor allem bei folgenden Abfrage Mustern zu überlegen:

--Gruppierter Index auf sp5
select sp1, sp2 from t1
    where sp5 between 10000 and 20000

select sp2, sp4 from t1
    where sp5 > 'H'

select sp2, sp4 from t1
    where sp5 < 20099
    
select sp2, sp8 from t1
    where sp5 > '1.1.1998' and sp5 < '31.12.1999'

select sp2, sp3 from t1 
    order by sp5

Der nicht gruppierte Index ist für folgendes Statements empfehlenswert:

--nicht gruppierter Index auf SP1
select sp6, sp7 from t1
    where sp1 = 10

select sp7,sp3 from t1
    where ID = 1243

AND und OR

Bei AND Bedingungen im where spielt es keine Rolle, welches Spalte zuerst abgefragt wird,Hauptsache wir haben einen Index, der mindestens eine der Spalten indiziert. Anders beim OR. Hier müssen alle Spalten einen eigenen Index besitzen, da die Ergebnisse einzelne gesucht werden und danach zusammengesetzt werden.

Zusammengesetzte Indizes

Um die Leistung von Indizes zu verbessern, kann ein Index durchaus mehrere Spalten beinhalten. Man spricht dann von einem zusammengesetzten Index.

Die führenden Spalten ist die Entscheidende! Suchen wir nach der OrderID wird eine sogenannter Seek ausgeführt. Das gleicht der Suche nach dem Familiennamen im Telefonbuch. Würde man nur nach der CustomerID suchen, wäre das ein Scan…die Siche nach dem Vornamen im Telefonbuch. Also die Suche von A bis Z.

Bei genauer Überlegung wird man feststellen, dass ein zusammengesetzter Index oft nur maximal 4 Spalten braucht um exakte Ergebnisse zu liefern. Selbst die Suche nach Maier im Telefonbuch, liefert spätestens nach Vorname und Straße ein oder zwei Treffer. Durch eine größere Anzahl von Spalten im Index werden die Nicht-Blattseiten (Suchebenen) stärker belastet.

Die Bedeutung des SELECT

Ist für die Planung des Index der SELECT wichtig? Sehen wir uns folgende Abfrage an:

Obwohl wir auf die Spalte PostalCode einen passenden Index haben, wird dennoch 98% der Leistung für einen Lookup verwendet. Der Grund liegt im *. Der Index kann zwar die Postalcode liefern oder bei zusammengesetzten Indizes evtl auch mehr Werte ohne in der Tabelle genauer nachsehen zu müssen, aber der Stern zwingt den Optimierer auf den Heap zuzugreifen um alle Spaltenwerte liefern zu können.

Covered Indizes

Liefert eine Index alle benötigten Daten, so spricht man von einem abdeckenden Index bzw covered Index. Insofern ist ein covered Index eher ein gewünschtes Ergebnis, als ein tatsächlicher Index. Ein Select * verhindert einen Covered Index!

Fügen wir dem Index die Spalten City und AddressID hinzu, so liefert die folgende Abfrage einen lupenreinen Covered Index:

Wer denkt, dass nun die beste Taktik ein Index mit allen Spalten wäre, den muss ich enttäuschen. Erstens gibt es ein Limit auf 16 Spalten im Index und einen maximale Länge von 900 bytes für den Indexschlüssel. Bspw. wird die Länge von 900 bytes überschritten, sofern man die Spalten: Sp1 nchar(5), Sp2 nvarchar(400) und Sp3 nvarchar(50) in einen Index mit aufnehmen möchte. (Zur Erinnerung: nchar bzw nvarchar belegen auf Grund des Unicodes die doppelte Platzmenge als angegeben). In diesem Fall ergäbe es eine Größe von 910 bytes.

Allerdings gibt es einen Ausweg: Indizes mit eingeschlossenen Spalten

Index mit eingeschlossenen Spalten

Diese Option bei nicht gruppierten Indizes erlaubt, bis zu 1023 Spalten in einen Index mit aufzunehmen. Eingeschlossene Spalten werden nur der Blattebene eines Index hinzugefügt und werden nicht zur Berechnung des Indexschlüssels herangezogen. Die Suchbaum (Nicht Blattebene) wird nicht belastet und bleibt daher klein.

Fazit

Was also die Planung eines Nicht gruppierten Index betrifft, könnte man dies in folgende optische Form bringen:

• Bei der Vergabe ist nicht nur das WHERE zu betrachten, sondern auch des SELECT!
• Nur wenn alle Ergebnisse einer Abfrage aus einem Index beantwortet werden können, erreicht man den Index Himmel -  einen covered Index!
• Zusammengesetzte Indizes brauchen in der Regel kaum mehr als 3 bis 4 Spalten für eine gute Trefferquote.
• Die erste Spalte des Index ist entscheidend für den Erfolg eines Index. (Suche nach Nachname oder Vorname).
• Ein SELECT * ist verboten! Dieser führt fast unweigerlich zu teuren Lookup Vorgängen.

Im Teil 3 werden wir Besonderheiten bei der Wahl des Index herauskehren und weitere Indexvarianten vorstellen..