אינדקסים

אינדקסים הם כלי בעל חשיבות עצומה בכל מה שקשור לשליפות (ואולי גם עדכונים) של טבלאות, וככלל ניתן לומר כך:

• כשאין אינדקס אנחנו "מרוויחים" כשאנחנו מוסיפים נתונים או מעדכנים אותם, אבל משלמים וביוקר כששולפים נתונים מהטבלה. בערך כמו במונית: משלמים בסוף.

• כשיש אינדקס זה עולה לנו בעת הוספה או עדכון של נתונים (כי המערכת צריכה לעדכן גם את האינדקס), אבל אנחנו מרוויחים מביצועים משובחים כשמשתמשים בנתונים. בערך כמו באוטובוס: משלמים בהתחלה.

בטבלה נטולת אינדקסים, כל שליפה (כולל חיפוש, מיון, ביצוע Group By וכו') יחייב שליפה של כל הטבלה על כל העמודות, ואת הנתונים הרלוונטיים לפלטר, למיין וכו'.

בטבלאות קטנות זה לא נורא, אבל ככל שהטבלה גדלה וככל שהשימוש בה יעלה - כך המחיר של היעדר האינדקסים ילך ויגדל, ולכן כשעושים נסיונות ומתרגלים את הנושא - כדאי לעשות זאת על טבלאות בנות מאות אלפי שורות ויותר.

דוגמה- ניצור טבלה מטבלת הודעות השגיאה (בגרסאות On Premise יש בה כ-350,000 שורות), בצירוף עמודה הממספרת את השורות:

Drop Table If Exists #T_Messages;
Select	Row_Number() Over(Order By GetDate()) ID,
		*
Into	#T_Messages
From	sys.messages;

וננסה את השליפה הבאה (כרגע זה יחייב לשלוף את כל שורות ועמודות הטבלה ולמיינן):

Select	language_id,
		text
From	#T_Messages
Where	is_event_logged=0
		And message_id=21901
		And severity=16
Order By language_id;

האינדקס שהייתי בוחר במקרה זה הוא:

Create Index Idx_message_id_severity_is_event_logged_language_id
On #T_Messages(message_id,severity,is_event_logged,language_id)
Include(text);

• ארבע העמודות באינדקס הן ארבע העמודות של ה-Where (קודם כל) ושל ה-Order By או ה-Group By אחריהן, מכיוון שאני רוצה לקבל קודם כל את השורות שעונות על שלושת התנאים, ובהמשך - שהן יהיו ממויינות. זה לא יעבוד אם בתנאים יהיה לנו Or או אולי In (שזה סוג של Or), אבל בחרתי להתחיל בדוגמה פשוטה.

• מה הסדר של שלוש העמודות של ה-Where? לפי הסדר שלהן בקוד? לאו דווקא! לכאורה זה לא משנה, והאינדקס יהיה יעיל במקרה הזה באותה מידה ללא קשר לסדר של העמודות message_id,severity,is_event_logged; אלא שהאינדקס יכול לשרת גם שאילתות שזקוקות לשתי העמודות הראשונות, או אפילו לשלוש הראשונות; למשל במקרים הבאים (בהתייחס לאינדקס הנ"ל):

Select	text
From	#T_Messages
Where	message_id=12345;

Select	*
From	#T_Messages
Where	message_id=12345
Order By severity;

Select	message_id,
		Count(*)
From	#T_Messages
Group By message_id;

השאילתה הראשונה נעזרת רק באינדקס וכל המידע נמצא שם בצורה מסודרת. השאילתה השנייה מוצאת את השורות הרלוונטיות בעזרת האינדקס, אבל משלימה את העמודות החסרות בעזרת הטבלה. השאילתה השלישית מוצאת את כל המידע באינדקס, אך צריכה לבצע עליו Group By (כלומר- לא רק לשלוף אלא גם לקבץ).

שלוש השאילתות הבאות או שהאינדקס אינו עוזר להן, או שהעזרה שלו היא חלקית:

Select	text
From	#T_Messages
Where	ID=123;

Select	message_id
From	#T_Messages
Where	severity=20;

Select	text
From	#T_Messages
Where	message_id=8541
Order By language_id;

השאילתה הראשונה פונה לטבלה כי עמודה ID אינה כלולה באינדקס. השאילתה השנייה נעזרת באינדקס כי כל העמודות הרלוונטיות כלולות בו, אבל מכיוון שלא לפי הסדר המבוקש - עליה לסרוק את האינדקס מתחילתו עד סופו כדי למצוא את כל השורות שעונות על התנאי שב-Where. השאילתה השלישית גם נעזרת באינדקס והוא אפילו שימושי בפילטור, אבל יש למיין את מה שנשלף כי השורות נשלפו ממויינות לפי העמודה השניה באינדקס (severity) ולא לפי הרביעית. אם כך מה אמור להיות הסדר, אם כל סדר שנבחר יתמוך בשאילתות מסויימות ובאחרות לא או באופן חלקי? תשובה: יש לבחור כך שהעמודה הכי סלקטיבית תהיה ראשונה, והפחות סלקטיבית בסוף. בסדר יורד של סלקטיביות. מה הכוונה בסלקטיביות? נניח שיש לנו טבלה של כל תושבי מדינת ישראל ואנחנו מחפשים נשים, ילידות 15/02/1960 תושבות אשדוד. תאריך הלידה הוא הכי סלקטיבי כי לנשות מדינת ישראל יש כמה עשרות אלפי תאריכי לידה שונים. היישוב הוא פחות סלקטיבי כי יש כמה מאות ישובים במדינת ישראל. המין הוא הכי פחות סלקטיבי כי יש רק שתי אפשרויות.. אינדקס על עמודת "מין" לא יהיה יעיל כי יחזיר לנו הרבה מאוד תשובות, ולכן אם הוא יהיה הראשון באינדקס - תועלתו תהיה דלה בשאילתות על עמודה זו. לעומת זאת עמודת התאריך תהיה שימושית מאוד, כי אם נחפש רק לפיה - יחזרו לנו כמה מאות שנולדו באותו תאריך. בקיצור: עדיף שהעמודה הראשונה תהיה message_id (כמה רבבות ערכים אפשריים), השנייה severity (עשרות בודדות של ערכים), והשלישית is_event_logged (שני ערכים אפשריים).

• בעקבות הסעיף הקודם, אילו אינדקסים הייתי יוצר עבור השאילתות הבאות? קודם כל נבטל את האינדקס שיצרנו קודם:

Drop Index Idx_message_id_severity_is_event_logged_language_id

On #T_Messages

ולהלן השאילתות, שבשלב זה הרצתן תגרום ל-scan (סריקה) לא יעיל של כל הטבלה:

Select	text
From	#T_Messages
Where	severity=20;

Select	language_id
From	#T_Messages
Where	severity=14
Order By ID;

Select	ID
From	#T_Messages
Order By severity;

יתכן שב-Execution Plan יופיעו המלצות לאינדקס או כמה אינדקסים, ולהלן האינדקסים המותאמים לכל אחת מהשאילתות:

Create Index Idx_severity_1
On #T_Messages(severity)
Include(text);

Create Index Idx_severity_ID
On #T_Messages(severity,ID)
Include(language_id);

Create Index Idx_severity_2
On #T_Messages(severity)
Include(ID);

כאן כדאי לציין שההמלצות של ה-Execution Plan לגבי הוספת אינדקסים הן לרוב מדוייקות (כלומר- ההמלצה בהחלט משפרת את הביצועים ותפורה במדוייק לצרכי השאילתה) אבל צריך לבדוק כל מקרה לגופו: לעיתים ניתן להימנע מאינדקס חדש ובמקום "להרחיב" אינדקס קיים (כפי שיוסבר להלן), לפעמים מחיר האינדקס אינו מצדיק את התועלת ממנו (בעיקר כשהוא מאוד ספציפי), ולעיתים - ההמלצה עלולה להיות שגוייה.. ולענייננו: עמודות שמשמשות לפילטור ולמיון יופיעו לפי הסדר, ועמודות שמופיעות ב-Select ואינן משמשות למיון או לפילטור אלא רק לתצוגה - יופיעו ב-Include. רגע: לכל שאילתה צריך להתאים אינדקס? אז לא: לאינדקס יש מחיר, גם לבנייתו וגם לעדכונו השוטף, ולכן רק עבור שאילתות שרצות לעיתים קרובות נעשה זאת; וגם אז נשתדל ליצור אינדקס אחד טוב שיתן מענה לסוגים שונים של שאילתות, ובמקרה זה ניתן היה להסתפק באינדקס אחד במקום בשלושה:

Create Index Idx_severity_ID
On #T_Messages(severity,ID)
Include(language_id,text);

אינדקס על severity ועל ID נותן מענה גם לשאילתות רק על severity, ה-Include כולל את העמודות השונות שמופיעות ב-Include-ים שבאינדקסים הנ"ל, חוץ מ-ID שנכלל באינדקס עצמו ולכן אין לכלול אותו ב-Include. החלק הזה חשוב בשעה שאנו עומדים ליצור אינדקס חדש ויש כבר אינדקסים קיימים: יתכן שניתן לשפר את אחד האינדקסים הקיימים כך שיתן מענה לשאילתה החדשה מבלי לפגוע ביכולתו לתת מענה לשאילתו הקיימות.

• למשל (בהמשך לפסקה האחרונה) - השאילתה הבאה:

Select	is_event_logged
From	#T_Messages
Where	severity>=20;

לכאורה צריך ליצור אינדקס על Severity עם is_event_logged ב-Include, אלא שיש כבר אינדקס על severity ורק צריך להוסיף את is_event_logged ל-Include. מה עושים? נתחיל במה לא עושים: לא מוחקים (Drop) את האינדקס ויוצרים אותו מחדש, כי לאחר ה-Drop לא יהיה אינדקס מתאים עד שנגמור ליצור אותו שוב, שאילתות עלולות להיתקע, השרת לא יעמוד בעומס, ותוך כדי זה עוד נרצה ליצור אינדקס חדש.. מה כן? יוצרים מחדש את האינדקס עם השינוי כך:

Create Index Idx_severity_ID
On #T_Messages(severity,ID)
Include(language_id,text,is_event_logged)
With(Drop_Existing=On);

במקרה זה תיווצר נעילה על הטבלה והשאילתות ימתינו, אבל לא יווצר עומס. אם רוצים למנוע את הנעילה, ניתן לעשות זאת בגרסאות Enterprise כך:

Create Index Idx_severity_ID
On #T_Messages(severity,ID)
Include(language_id,text,is_event_logged)
With(Drop_Existing=On,OnLine=On);

• אילו עוד אופציות ראויות לציון ניתן להוסיף לפסוקית ה-With ביצירת האינדקס? אני בדרך כלל כולל את האופציות הבאות:

With(Drop_Existing=Off,OnLine=On,FillFactor=90,Data_Compression=Page);

אופציית Drop_Existing - ברירת המחדל היא Off ואני מוסיף אותה רק כשאני מחליף אינדקס קיים בחדש. OnLine - בגרסאות Standard אשתמש ב-Off כי On אינו נתמך (ברירת המחדל ולכן אין צורך להוסיף), וב-Enterprise - On. FillFactor מציין כמה מקום פנוי להשאיר באינדקס עבור ערכים חדשים. התשובה לשאלה "כמה" היא בדרך כלל It Depends (שזה עוזר למי שלא צריך עזרה ולא למי שכן צריך), אני מוסיף לרוב 80 או 90 (כלומר- 10% או 20% מקום פנוי). אם הטבלה סטאטית ואינה משתנית או הערכים מתווספים תמיד בסוף האינדקס (למשל- עמודת Identity) אפשר להתעלם מהפרמטר או לכתוב 100 (0% מקום פנוי כי אין צורך). Data_Compression דוחס את הנתונים ובכך חוסך בפעולות IO בקריאה ובכתיבה, אם כי יוצר עומס מסויים על ה-CPU שצריך לדחוס ולבטל דחיסה. ככלל ההמלצה היא להשתמש באופצייה הזו, אם כי יש גם דעות אחרות.

• איזה שם לתת לאינדקס? מומלץ לתת שם שירמז על תוכנו, אם כי עלי להודות שאם אני רוצה לדעת מה הוא כולל, אני מוציא את הסקריפט שלו ובודק בעצמי, ולא מסתמך על השם. בכל מקרה ההמלצה: השם יכול להיות משהו כמו Idx_MyCol1_MyCol2, כאשר Idx הוא הקידומת, ו- MyCol1 & MyCol2 הן שתי העמודות עליהן נבנה. אין צורך לדעתי לציין את שם הטבלה, מכיוון שכל אינדקס משוייך לטבלה (ואינו אובייקט עצמאי) ולכן ברור באיזו טבלה מדובר. יש כאלה שמציינים גם את העמודות שב-Include, אך לדעתי זה מסרבל את הכל.

אינדקס Unique - למשל:

Create Unique Index Idx_ID
On #T_Messages(ID);

אינדקס כזה מבטיח שכל ערך בעמודה ID יופיע פעם אחת (כולל Null). הוא שימושי גם לאבטחת איכות הנתונים (מניעה ברמת הטבלה של ערכים שגויים, דבר שמהווה הגנה טובה יותר מאשר מניעה בקוד באפליקציה), וגם מסייע לאופטימייזר בביצועים טובים יותר בשליפות (למשל- האופטימייזר יכול "לדעת" ש-Join מול עמודת ID לא יגרום למכפלת שורות אלא רק לפילטור שלהן).

אינדקס מפולטר (Filtered Index)- להלן דוגמה:

Create Index Idx_language_id_message_id
On #T_Messages(language_id,message_id)
Include(text)
Where is_event_logged=1;

כפי שאפשר לראות לאינדקס יש פסוקית Where שמגבילה אותו לחלק משורות הטבלה. לאיזו שאילתה, למשל, האינדקס הזה טוב? לשאילתה שכוללת את ה-Where הנ"ל, למשל:

Select	text
From	#T_Messages
Where	is_event_logged=1
		And language_id=1033
Order By message_id;

ומה קורה אם אני רוצה להריץ שאילתה עם התנאי is_event_logged=0 ? נסביר: התנאי על is_event_logged גורם לכך שזו העמודה הראשונה בסדר! כלומר- אם נרצה להרחיב את האינדקס תוך שמירת הפונקציונליות הקיימת, נצטרך למקם את is_event_logged לפני language_id & message_id. אם יהיו לנו שני אינדקסים דומים שכל ההבדל בינהם הוא שלאחד יש פסוקית Where (כלומר- זהו filtered index) והשני לא, לא ניתן לאחד אותם! אם כך למה הוא טוב? מתי יהיה בו צורך? מדוע לא למקם את העמודה המפולטרת ראשונה, וכך רק נרוויח פונקציונליות?

להלן מספר דוגמאות:

• אינדקס מפולטר על החודש האחרון בטבלה שכוללת לוג של שנים רבות, כאשר רוב השאילתות מתייחסות לתקופה אחרונה. במקרה זה נפסיד אולי פונקציונליות במקרה של שאילתות על תקופות קודמות, אבל ברוב השאילתות נרוויח סטטיסטיקות מדוייקות יותר על הנתונים הרלוונטיים (כמובן שנצטרך "לקדם" בכל יום את התנאי ביום אחד).

• אינדקס Unique על ערכים שאינם Null. נניח, עמודה שכוללת מספרים אישיים מהצבא בטבלת עובדים: לא לכל העובדים יש מספר אישי, אבל למי שכן - הוא יחודי.

• בארגון בו עבדתי היו תהליכים שרצים אחת ליום או לשבוע או לחודש, טבלת לוג של הריצות, וסטטוס שיכל להיות- "הצליח", "נכשל", "רץ". מכיוון שאסור להריץ תהליך במקביל לעצמו, בנינו אינדקס Unique על עמודת מספר תהליך, בתנאי (where) שהסטטוס הוא "רץ". אם מישהו (עובד או תהליך אוטומטי) היה מנסה להריץ את התהליך תוך כדי שהוא כבר רץ (דבר שיש למנוע ברמת האפליקציה) - הוא היה נכשל בגלל ה-unique-יות על עמודת הסטאטוס.

שילוב של תנאי <= (גדול או קטן מ..) ומיון.

נעיין בשאילתה הבאה:

Select  ...
From    MyTbl
Where   Col1=1234
        And Col2>=100
Order By Col3;      

ברור שהעמודה הראשונה באינדקס תהיה Col1.

מה תהיה העמודה השנייה? מה הבעייה בכלל? מדוע לא Col2 ואר כך Col3 כדי לקבל את הפלט ממויין?

אם ננהג כך, נקבל את כל הערכים הרלוונטיים מבחינת Col2, אבל המיון לפי Col3 יתבצע לכל ערך של Col2 בנפרד. כלומר- עבור Col2=100 נקבל את כל השורות ממויינות לפי Col3, עבור Col2=101 נקבל את כל השורות ממויינות לפי Col3 וכו'; ואזי המערכת תצטרך למיין את כל השורות. לחילופין, אם העמודה השנייה תהיה Col3 נקבל את כל השורות (המקיימות Col1=1234) ממויינות, והמערכת תצטרך לבצע scan על כולן ולחפש את אלו העונות לתנאי Col2>=100.

מתברר שאין דרך שתאפשר לנו גם לפלטר וגם למיין, ולכן עלינו לבחור בין:

• עמודה Col2 שנייה באינדקס ואז למיין (Col3 יכולה להיות ב-Include כי ממילא לא תביא תועלת בגוף האינדקס). אפשרות זו עדיפה כשמספר השורות קטן יחסית.

• עמודה Col3 שנייה באינדקס ואז לבצע scan כדי לפלטר את השורות העונות לתנאי תוך שמירה על המיון (Col2 יכולה להיות ב-Include כי ממילא לא תביא תועלת בגוף האינדקס). אפשרות זו עדיפה כשמספר השורות גדול: עדיף לבצע scan על מיליארד שורות מאשר למיין אותן. נכון שגם על עשר שורות זה נכון, אלא שהפער לטובת ה-scan הולך וגדל ככל שהסט גדל.

את העמודות שב-Select שאינן בגוף האינדקס - נוסיף ל-Include, אלא אם כן מעדיפים להימנע מכך ומעדיפים שהמערכת תבצע Look Up בטבלה עצמה.

שילוב של Join ו-Where

נעיין בשליפה הבאה:

Select    ...
From TblA A
Inner Join TblB B
        On A.ID=B.ID
Where   A.Col1=12
        And B.Col2=34;

כעקרון האינדקס בטבלה TblA יהיה על Col1 ועל ID,

ואילו בטבלה TblB הוא יהיה על Col2 ועל ID; ושאר העמודות שב-Select לשיקול דעתכם ב-Include.

ככל הנראה יתבצע כאן Nested Loop מטבלה אחת לשנייה, כלומר- מכל שורה בטבלה האחת, המערכת תחפש בעזרת האינדקס את השורות המתאימות בטבלה השנייה; ובמקרה זה ה-ID באינדקס של הטבלה האחת - מיותר, וניתן לשים אותו ב-Include במקום.

למה לא לכלול את כל העמודות בגוף האינדקס?

נתבונן בשאילתה הבאה:

Select    Col1,Col2
From      MyTbl
Where     Col3=5
Order By Col4;

ברור ששתי העמודות הראשונות באינדקס יהיו Col3 ו-Col4. מדוע לא להוסיף אחריהן גם את Col1 & Col2? מדוע לשים אותן (אם בכלל) ב-Include? הרי לא נפסיד דבר מבחינת הפונקציונליות, ואולי אף נרוויח אם במקרה יהיה בהן צורך.

שתי סיבות:

• גוף האינדקס מוגבל בגודלו, ולכן יש מגבלה על גודל העמודות שבו. ה-Include פחות מוגבל, ולכן מה שלא חייב להיות בגוף האינדקס שיהיה ב-Include.

• יום יבוא ומישהו יזדקק לאינדקס עם העמודות הבאות: Col3, Col4, Col5 (לפי הסדר משמאל לימין), הוא יראה שיש אינדקס עם העמודות Col3, Col4, Col1, Col2 שלא יתאים לצרכיו ולכן יצור אינדקס נוסף וחבל. לו יראה שיש אינדקס עם העמודות Col3, Col4 הוא יוכל להוסיף לו את Col5 מבלי לפגוע בפונקציונליות הקיימת.

שני אילוצי אי שיוויון

נסתכל בשליפה הבאה:

Select    ...
From      MyTbl
Where     Col1>=50
          And Col2<=15

אם העמודה הראשונה באינדקס תהיה Col1 המערכת תמצא בעזרתו את כל השורות עבורן הוא גדול או שווה ל-50, אך כדי למצוא את אלו שעונים על התנאי של Col2 לא יעזור לנו אם הוא יכלל באינדקס מכיוון שערכי Col2<=15 יהיו מפוזרי בין הערכים השונים של Col1>=50, ויהיה צורך לבצע Scan.

במקרה זה צריך לבחור בין אינדקס על Col1 עם Col2 ב-Include או להיפך; ועדיף שהוא יהיה על היותר סלקטיבי מבין השניים (ואז יתבצע scan קצר יותר עבטר הפילטר השני).