השפעת האינדקסים על חיפוש פשוט בטבלה
כיצד המערכת מחפשת ערכים בטבלה ומה השפעת האינדקסים על כך?
מדובר בנושא די בסיסי בתחום, והפוסט הזה מוקדש למי שעדיין אינו מכיר.
טבלת המערכת הגדולה ביותר היא sys.messages הכוללת את הודעות השגיאה של המערכת (קרוב ל-100,000 שורות בגרסת 2008 ועוד היד נטוייה בגרסאות הבאות):
Select *
From sys.messages;
ניצור מספר עותקים ממנה ונאנדקס כל אחד בדרך אחרת,
ונבדוק כיצד מתבצע החיפוש בכל פעם.
נתחיל עם עותק ללא אינדקסים כלל:
--1. No Indexes
Select *
Into messages
From sys.messages;
Go
--2. Clustered Primary Key on message_id,language_id
Select *
Into messages_ClstInd
From sys.messages;
Go
Alter Table messages_ClstInd
Add Constraint PK_messages_ClstInd Primary Key Clustered (message_id,language_id);
Go
--3. Unique Index on message_id,language_id (if a Primary Key already exists)
Select *
Into messages_Ind
From sys.messages;
Go
Create Unique Index Idx_messages_Ind On messages_Ind(message_id,language_id);
Go
--4. Unique Covered Index- Exists from SQL 2008 on
Select *
Into messages_CvdtInd
From sys.messages;
Go
Create Unique Index Idx_messages_CvdtInd On messages_CvdtInd(message_id,language_id) Include (severity, is_event_logged, [text]);
Go
וכעת נפעיל את הסטטיסטיקה ואת ה-Execution Plan ונבצע שליפה זהה בארבעתן:
Set Statistics IO On;
Go
Select *
From messages
Where message_id=2786;
Select *
From messages_Ind
Where message_id=2786;
Select *
From messages_ClstInd
Where message_id=2786;
Select *
From messages_CvdtInd
Where message_id=2786;
(11 row(s) affected)
Table 'messages'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(11 row(s) affected)
Table 'messages_ClstInd'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(11 row(s) affected)
Table 'messages_Ind'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(11 row(s) affected)
Table 'messages_CvdtInd'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
שליפה 1- ללא אינדקס. במקרה זה המערכת נאלצת לבצע Scan Table מלא על כל הטבלה, ולא פלא שמבחינת העלות זה 98% ן- logical reads 2719.
שליפה 2- Primary Key שהוא גם Clustered Index. זהו החיפוש הכי יעיל במקרה זה- מתבצע Index Seek ישר לשורות הרלוונטיות ללא בזבוז משאבים על חיפושים מיותרים. העלות אפסית 0% ו- logical reads 4.
שליפה 3- Unique Index. גם כאן מגיעים לערכים המבוקשים ביעילות על ידי Index seek, אך אם רוצים לשלוף גם את העמודות שאינן באינדקס (כלומר- גם את is_evrnt_logged & text) יש לפנות מהאינדקס לטבלה עצמה על ידי Look Up שמייקר את השליפה ל-2% ו- logical reads 13.
אם היינו שולפים רק את העמודות המאנדקסות לא היה צורך ב-Look Up.
העובדה שהאינדקס הוא Unique מונעת הכנסת ערכים כפולים, אך אינה משפיעה על החיפוש במקרה זה.
אם עמודה message_id הייתה פחות סלקטיבית והחיפוש היה מחזיר הרבה יותר ערכים- יתכן שלמערכת לא היה כדאי לבצע הרבה Look Ups והייתה מעדיפה לבצע Table Scan ולהתעלם מהאינדקס.
שליפה 4- Unique Covered Index. החל מגרסת 2008 ניתן לכלול באינדקס עמודות נוספות מבלי לאנדקס אותן, כלומר- האינדקס לא עוזר לבצע בהן חיפוש, אך הוא מונע את הצורך ב-Look Up כמו קודם. במקרה זה הביצועים הם כמו עם Clustered Index מתבצע Index Seek הישר לשורות הרלוונטיות, ואין צורך ב-Look Up. המסקנה היא ש-Covered Index דומה ל-Clustered Index מבחינת הביצועים אך ללא מגבלה על הכמות (Clustered Index – כמו אמא – יש רק אחד..), אבל יש לזה מחיר של הכפלת נפח האחסון ופעולות ה-DML (הוספה, עדכון, מחיקה).
מה יקרה אם החיפוש לא יהיה ממוקד (הנ"ל החזיר 11 שורות)? ננסה לפנות לטווח גדול יותר:
Select *
From messages
Where message_id Between 15000 And 16000;
Select *
From messages_ClstInd
Where message_id Between 15000 And 16000;
Select *
From messages_Ind
Where message_id Between 15000 And 16000;
Select *
From messages_CvdtInd
Where message_id Between 15000 And 16000;
(5577 row(s) affected)
Table 'messages'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(5577 row(s) affected)
Table 'messages_ClstInd'. Scan count 1, logical reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(5577 row(s) affected)
Table 'messages_Ind'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(5577 row(s) affected)
Table 'messages_CvdtInd'. Scan count 1, logical reads 120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
שליפה 1 (ללא אינדקס) ביצעה כצפוי Table Scan, אבל גם שליפה 3 (Non Covered Index) בחרה בדרך זו כדי להימנע מביצוע 5577 Look Ups. אינדקס שאינו Covered מועיל כשנשלפות מעט שורות, או כשה-Select הוא על עמודות הנכללות בו. אם נבצע את שליפה 3 האחרונה אבל נפנה רק לשתי העמודות שבאינדקס- הוא יבצע Index Scan יעיל כמו בשליפה 2 ושליפה 3.
Select message_id,
language_id
From messages_Ind
Where message_id Between 15000 And 16000;
(5577 row(s) affected)
Table 'messages_Ind'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
שליפה 2 (Clustered Index ) ושליפה 3 (Covered Index) מבצעות גם כאן Index Seek כמו קודם.
מה יקרה אם החיפוש יהיה על עמודה שאינה מאונדקסת או על עמודה משנית באינדקס?
Select *
From messages
Where language_id=1040;
Select *
From messages_ClstInd
Where language_id=1040;
Select *
From messages_Ind
Where language_id=1040;
Select *
From messages_CvdtInd
Where language_id=1040;
(8866 row(s) affected)
Table 'messages'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(8866 row(s) affected)
Table 'messages_ClstInd'. Scan count 1, logical reads 2726, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(8866 row(s) affected)
Table 'messages_Ind'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(8866 row(s) affected)
Table 'messages_CvdtInd'. Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
במקרה זה המערכת בחרה בכל ארבעת המקרים לבצע Scan מלא. כדאי לשים לב ש-Table Scan ו-Clustered Index Scan הם היינו הך במקרה זה מכיוון ש-Clustered Index אינו אינדקס הנבנה על הטבלה, אלא הטבלה עצמה; ולכן Clustered Index Scan אינו נחשב ל-"שימוש באינדקס", בניגוד ל-Clustered Index Seek.
האם המערכת ויתרה על השימוש באינדקסים בגלל ש-language_id הוא עמודה משנית באינדקס או בגלל שהשליפה מחזירה מספר רב של שורות (8866)? נעשה נסיון פשוט: ניצור טבלה נוספת, נבנה עליה אינדקס פשוט כמו זה של שליפה 2 אבל עם סדר עמודות הפוך, ונבצע עליו את השליפה הראשונה שמחזירה מספר מועט של שורות אבל פונה כעת לעמודה המשנית בטבלה:
Select *
Into messages_Ind2
From sys.messages;
Create Unique Index Idx_messages_Ind2 On messages_Ind2(language_id,message_id);
Go
Select *
From messages_Ind2
Where message_id=2786;
(11 row(s) affected)
Table 'messages_Ind2'. Scan count 1, logical reads 218, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
הפעם המערכת כן מבצעת Index Scan וניגשת ל-message_id הרלוונטי בכל language_id בנפרד, ואת המידע החסר על שתי העמודות החסרות היא משלימה על ידי Look Up. כלומר- גם בשליפה עם תנאי על עמודה שאינה ראשית באינדקס- יתכן והמערכת תחליט להשתמש בכל זאת באינדקס, בעיקר עם הסלקטיביות גבוהה (צפויים לחזור מעט נתונים יחסית).
דבר נוסף שכדאי לשים אליו לב הוא שהמערכת ממליצה להוסיף אינדקס על message_id ומבטיחה שיפור נאה בביצועים..
את קבצי ה-Execution Plan ניתן להוריד דרך צילומי המסך שבפוסט.