Använd Index Intersection
Förord
Index Intersection, som först introducerades i SQL Server 7.0 (finns självklart också att tillgå i SQL Server 2000), ger dig nya möjligheter till att skapa Index på tabeller för att höja Serverns prestanda.
För att demonstrera hur Index Intersection fungerar så kommer jag först att använda mig utav ”authors” tabellen i PUBS databas, och visa på hur de redan satta Indexen på den tabellen fungerar. Sedan ska vi se vilka möjligheter Index Intersection ger oss för att kunna höja prestandan på dem SQL-satser som körs mot ”authors” tabellen.
Höj SQL Serverns prestanda med Index Intersection
av Neil Boyle
På ett sätt så är ”authors” tabellen både ett bra och dåligt exempel för den här artikeln. Den är perfekt för demonstrationssyften eftersom PUBS databasen gjordes innan SQL Server 7.0, och på så sätt kan vi se hur Indexen valdes utan att kunna dra fördel av Index Intersection. PUBS är också välkänt och åtkomligt för alla. Å andra sidan så är den prestanda man tjänar när man använder Index Intersection relativt obetydlig (om man tjänar någon alls) för en så här liten tabell med dess design.
Utan Index Intersection
Tänk dig att du har en tabell med två kolumner, och att du regelbundet söker i kolumnerna som ett par, t ex:
USE PUBS
GO
SELECT *
FROM authors
WHERE au_fname = 'Akiko' AND au_lname = 'Yokomoto'
PUBS databasen använder sig i den här tabellen av ett icke-clustrat multipelt Index (alltså ett Index med fler än en kolumn), vilket passar SQL-satsen perfekt eftersom Indexet är satt på kolumnerna au_fname och au_lname – i den ordningen. SQL Server kommer att använda sig utav Indexet för att returnera resultaten från SQL-satsen.
Hur du sorterar Indexkolumnerna är viktigt, därför att då du använder ett multipelt Index så kommer kolumnen längst till vänster i Indexet att övervägas i WHERE klausulen (eller JOIN klausulen om du använder en sats som behandlar flera tabeller). På grund av det här så kommer SQL Server att behandla följande två satser på två skilda sätt:
SELECT *
FROM authors
WHERE au_lname = 'Yokomoto'
SELECT *
FROM authors
WHERE au_fname = 'Akiko'
Den första av dessa två satser kommer att, precis som i första exemplet, använda sig utav Indexet för att leta i tabellen, eftersom au_lname är den första kolumnen i Indexet. SQL Server kan däremot inte använda samma Index till den andra SQL-satsen, eftersom au_fname inte är kolumnen längst till vänster i Indexet. Så det som sker är att Optimizern väljer en helt annan exekveringsplan, eller så genomförs en tabellsökning (au_fname är normalt sett inte Indexerad). Du kan själv bevisa det här genom att köra dessa två satser genom Query Analyzer, och titta på de exekveringsplaner som genereras.
Hur du använder Index Intersection
Index Intersection är en teknik inbyggd i SQL Server systemet, som gör det möjligt att använda fler än ett Index på en tabell, för att tillfredsställa en given SQL-sats. För att demonstrera det här så måste vi göra vissa ändringar i PUBS databasen, så det bör nu vara ett ypperligt tillfälle till att köra en backup på databasen. Backupen säkrad? Ok, då kör vi vidare…
Först ska vi korta ner ”authors” tabellen genom att köra följande skript:
CREATE TABLE authors_names (
id INT IDENTITY,
au_lname VARCHAR(40),
au_fname VARCHAR(40),
filler CHAR(7000)
)
GO
INSERT authors_names (au_lname, au_fname, filler)
SELECT l.au_lname, f.au_fname, 'filler'
FROM authors l CROSS JOIN authors f
GO
ALTER TABLE authors_names ADD CONSTRAINT PK_authors_names PRIMARY KEY
LUSTERED (id)
GO
CREATE NONCLUSTERED INDEX i__au_fname ON authors_names (au_fname)
GO
CREATE NONCLUSTERED INDEX i__au_lname ON authors_names (au_lname)
GO
Notera att jag har tagit till ett par genvägar för att generera passande testdata. Till skillnad från att ignorera onödiga kolumner till testet, så har jag använt mig av en CROSS JOIN för att istället få ihop så mycket tillgänglig testdata som möjligt. Jag har också lagt till en ”filler” kolumn för att ta upp så mycket plats som möjligt, eftersom inte SQL Server använder Index vid små tabeller.
Om du nu kör de tre SQL-satser som vi tidigare körde, mot ”authors_name” tabellen och med användning av ”Display Estimated Execution Plan” alternativet i Query Analyzer, så kan du se att alla tre SQL-satserna nu använder sig utav en eller fler Index (kom ihåg att SQL-satsen som kördes mot au_fname tidigare använde sig utav en tabellsökning).
Du kan därmed också se att den SQL-satsen som har angivit både för- och efternamnen i WHERE klausulen, använder sig av Indexet både på au_fname och av Indexet på au_lname. Det är det som är fördelen med att använda Index Intersection. Det låter SQL Server att skanna över fler än ett Index för att få ut de data du behöver, vilket minimerar mängden data som returneras samt höjer prestandan. Innan SQL 7.0 så fanns inte den här funktionen att tillgå i SQL Server.
Saker att lägga märke till
I det här exemplet har jag en bra anledning till att använda mig utav icke-clustrade Index. Generellt sett så föredrar Optimizern att använda clustrade Index istället för Index Intersection, så det är mindre troligt att demonstrationen skulle ha fungerat om jag hade använt clustrade Index i SQL-satserna.Huruvida en kombination av singelkolumn Index är bättre än ett multipelt Index eller inte, beror på dina data samt SQL-satserna som du kör mot datan. Du borde testa båda fallen i din databasdesign, för att finna det som är effektivast under dina omständigheter.
Som med vilken annan Indexerad tabell som helst, så är det viktigt att regelbundet övervaka (eller använda auto-stats) Indexens struktur och statistik, för att på så sätt hjälpa din SQL Server till att finna det effektivaste Indexet för varje SQL-sats.
0 Kommentarer