Tips på hur du optimerar SQL Serverns kombinerade Index
Förord
Ett kombinerat Index är ett Index som byggs upp av fler än en kolumn. I vissa fall så kan ett kombinerat Index också vara ett täckande Index. (Se den relaterade artikeln ” Tips på hur du optimerar täckande SQL Server Index” längst upp på sidan). Generellt sett så bör du undvika kombinerade Index (med undantag av täckande Index). Det beror på att kombinerade Index tenderar att bli väldigt breda, vilket innebär att Indexet kommer att bli väldigt stort. Det kräver då mer disk I/O för att kunna läsa Indexet, vilket skadar prestandan.
Huvudanledningen till att man använder kombinerade Index (inte täckande kombinerade Index) är för att göra ett Index mer selektivt. Men det finns ett bättre sätt att försäkra sig om selektivitet, och det är genom en bra databasdesign – inte genom att skapa kombinerade Index. [SQL Server 6.5, 7.0, 2000] Inlagd 02-08-09.
Innehåll
Relaterade artiklar
» Alla SQL Server Index skapas inte lika» Tips på hur du optimerar täckande SQL Server Index
Tips på hur du optimerar SQL Serverns kombinerade Index
Håll ”bredden” på dina Index så smala som möjligt, speciellt då du skapar kombinerade Index (med flera kolumner). Det reducerar storleken på Indexen samtidigt som det reducerar antalet disk I/O läsningar som krävs för att läsa Indexet, och det höjer prestandan. [SQL Server 6.5, 7.0, 2000]
*****
Ett kombinerat Index är generellt sett bara användbart i SQL-satser om WHERE klausulen i SQL-satsen matchar kolumnen (eller kolumnerna) som finns till vänster i Indexet. Så om du skapar ett kombinerat Index med t ex ”City, State”, så kommer en SQL-sats som t ex ”WHERE City = ’Springfield’” att använda Indexet medan SQL-satsen ”WHERE State = ’MO’” inte kommer att göra det. [SQL Server 6.5, 7.0, 2000]
*****
Även om WHERE klausulen i en SQL-sats inte specificerar den första kolumnen i ett tillgängligt Index (vilket normalt sett diskvalificerar Indexet från att användas), och om Indexet är ett kombinerat Index som innehåller alla kolumner som det refereras till i en SQL-sats, så kan din Query Optimizer fortfarande använda sig av Indexet eftersom det är ett täckande Index. [SQL Server 6.5, 7.0, 2000]
*****
När du skapar ett Index med en kombinerad nyckel så är ordningen av kolumnerna i nyckeln viktigt. Försök att sortera kolumnerna i nyckeln för att förstärka selektiviteten, genom att sätta den mest selektiva kolumnen längs till vänster i nyckeln. Om du inte gör det utan istället sätter en icke-selektiv kolumn längs till vänster i nyckeln, så riskerar du att inte Query Optimizer använder sig utav Indexet överhuvudtaget. Generellt sett så bör en kolumn vara minst 95 % unikt för att kunna räknas som selektiv. [SQL Server 6.5, 7.0, 2000] Uppdaterad 01-03-06. Se mer information om selektivitet i artikeln längst upp, ”Alla Server Server Index skapas inte lika”.
*****
Ibland kan det vara en bra idé att dela upp ett kombinerat Index till flera singel-kolumn Index. Det beror på att det bara lagras statistik om den första kolumnen i ett kombinerat Index. Och om den första kolumnen inte är särskilt selektiv så kanske den inte används av Query Optimizer. Om du däremot skulle dela upp ett kombinerat Index till flera singelkolumn Index så skulle det lagras statistik om varje kolumn, och Query Optimizer skulle då ha bättre information för att göra bättre val om hur den ska använda Indexen. SQL Server har förmågan att JOINa flera Index och sedan skära över dem, precis som om du skulle använda ett kombinerat Index, vilket ger dig bäst fördelar med singel- och kombinerade Index.
Med det här vill jag dock inte säga att flera singelkolumn Index alltid är bättre än ett enda kombinerat Index. Du kan endast genom noggranna tester se vilken strategi som passar i just din situation bäst. [SQL Server 7.0, 2000] Inlagd 02-03-27.
*****
Som du kanske vet så skapas det automatiskt ett Index på kolumnen (eller kolumnerna) i din tabell som du har specificerat som PRIMARY KEY eller som UNIQUE. Om det skulle vara två eller flera kolumner involverade och det skulle skapas ett kombinerat Index, så bör du välja att sortera kolumnerna i det kombinerade Indexet själv och inte bara acceptera de standardval som erbjuds av SQL Server. Det beror på att du alltid bör ha den kolumnen som är mest selektiv längst till vänster i nyckeln, för att försäkra dig om att det kombinerade Indexet är selektivt nog för att användas av Query Optimizer. Om du inte gör det så kan det ske så att den standardsorteringen av kolumner i det kombinerade Indexet inte blir särskilt selektivt, och då kanske inte Indexet används av Query Optimizern. Och medan sorteringen av kolumner i Indexet är viktigt för Query Optimizer, så är sorteringen inte särskilt viktig för de begränsningar som ges PRIMARY KEY eller UNIQUE. [SQL Server 6.5, 7.0, 2000] Inlagd 01-03-06.
*****
Det finns en bugg i SQL Server 7.0 och 2000 som ännu måste korrigeras, vilken kan påverka vissa SQL-satser prestanda negativt. Det kan hända att SQL-satser innehållande många OR klausuler som baseras på ett Clustrat kombinerat Index, ignorerar Indexet och utför en tabellscan istället. Den här buggen visar sig bara om SQL-satsen finns i en lagrad procedur eller om den exekveras genom en ODBC baserad applikation såsom t ex VB, ASP eller Microsoft Access.
Det bästa sättet för att se om du har råkat ut för den här buggen är att se över din Query Plan för SQL-satser som presterar långsamt, eller som möter kriterierna här ovan. I Query Planen så kan du se om det är en tabellscan som utförs eller om Indexet används.
Den finns fem olika möjliga lösningar för det här problemet, beroende på hur din situation ser ut:
- Använd en lämplig Indexlösning för att tvinga fram användandet av det kombinerade Indexet. Det här är förmodligen den enklaste metoden att komma runt det här problemet med, och det är den här metoden som jag rekommenderar.
- Att byta från ett Clustrat kombinerat Index till ett icke-Clustrat kombinerat Index kan hjälpa, men det är inte garanterat. Du måste prova dig fram.
- Skriv om din SQL-sats och med hjälp UNION klausulen för att kombinera resultaten du skulle ha fått från OR klausulerna. Men här kan själva användandet av UNION klausulen själv degradera prestandan. Du måste testa det här alternativet för att se om det går snabbare eller inte.
- Om SQL-satsen exekveras från en ODBC applikation genom SQLPrepare funktionen med SQL Server ODBC Driver version 3.6 eller tidigare, så kan du stänga av alternativet ”Generate Stored Procedures for Prepared Statements” för att komma runt buggen.
- Om SQL-satsen exekveras från en ODBC applikation genom antingen SQLPrepare eller SQLExecDirect funktionerna med en parameter SQL-sats med SQL Server ODBC Driver version 3.7, så kan du komma runt buggen genom att använda verktyget ”odbccmpt” för att aktivera SQL Server 6.5 ODBC kompatibilitetsalternativ och samtidigt stänga av alternativet ”Generate Stored Procedures for Prepared Statements”.
[SQL Server 7.0, 2000] Inlagd 01-07-02.
0 Kommentarer