Indexes in SQL Server 2 - ilDBA Portal

Indexes in SQL Server 2

13/06/2012 | פורסם על ידי

תזכורת למאמר הקודם – חלק א

במאמר הקודם עסקנו בתפקיד המוכר והידוע של ה-Clustered index שהיה נכון עד לגרסה 7, תפקיד זה כלל את:

  • ה-clusterd index הוא זה שקובע את הסדר הפיזי של הנתונים.
  • ה-clusterd index הוא זה שיכול לגרום לנו לפרגמנטציה. ככל שהבסיס נתונים שלנו יהיה גדול יותר – זה יהיה משמעותי יותר.

מגרסה 7 ל Clustered index נוסף לו עוד תפקיד. הוא משמש כ Lookup עבור Nonclustered indexes.

מה בדיוק התפקיד החדש הזה אומר?

בעבר (עד גרסה 7) שרת ה SQL Server היה משתמש במה שנקרא volatile RID structure – ובתרגום פשוט לעברית "מזהה עמוד זמני". זאת אומרת ששורה שמכילה את השם שלי "ערן שקד" שהיום מאוכלסת בדף מסויים עם RID מסויים, מחר בגלל שינויים כלשהם השורה תעבור לדף אחר שיש לו RID אחר.
הבעיה עם מבנה כזה היא, שכל ה Nonclustered indexes צריכים להיות מעודכנים בהתאם כי הם מתבססים על ה RID. התןצאה של מבנה זה היא: כל שינוי של מיקום שורה חייב שינוי של כל האינדקסים!

ניקח לדוגמא מציאות שכיחה:

page splits – במקרה הזה  50% מהשורות עוברות לעמוד חדש.

(מתי מתרחש page split? כאשר עמוד מתמלא עד הסוף, ועכשיו נכנסת שורה נוספת שצריכה ע"פ ה-Clustered index להכנס למקום מסויים באותו עמוד – כיוון שבפועל אין לשורה מקום להכנס כי העמוד מלא ב-100 אחוז, שרת ה-SQL מבצע page split הוא פותח עמוד חדש, וחצי מכמות הרשומות מהעמוד המלא הוא מעביר לעמוד חדש – וכך בכל עמוד יש לו חמישים אחוז פנוי להכנסת שורות חדשות).

עבור כל השורות האלה ה-RID של העמוד משתנה למספר אחר בגלל שהם עברו לעמוד חדש.
עכשיו עבור כל השורות האלה אנחנו צריכים לשנות את כל ה-Nonclustered indexes שידעו שהן יושבות ב-RID אחר. ככל שיהיו לנו יותר Nonclustered indexes, יהיה לנו הרבה יותר עבודה.

החל מגרסה 7  – כל הנושא של record relocation עבר שינוי.

בטבלאות HEAP, התחילו להשתמש במצביעים forwarding pointers.
בזמן ה-INSERT של השורה נקבע לה RID אחד וקבוע עבור השורה.
אם אח"כ בגלל page splits לדוגמא, היא צריכה לעבור עמוד, ה-RID לא משתנה.
במקום זאת יש לנו מצביע לעמוד החדש שנוצר וככה מגיעים ישירות ל-DATA עם תוספת של מקסימום תוספת של IO אחד בלבד.

בטבלאות Clustered לא צריך לזכור את ה-RID שבו יושב ה-DATA, ולא צריך לייצר pointers ל-RID אם העמוד משתנה. שרת ה-SQL משתמש בClustering key כדי למצוא את הנתונים.

השינוי הזה של הוספת ה-clustering key כ-Lookup לכל ה-Nonclustered indexes יוצרת חשיבות רבה ונוספת להגדרה נכונה של ה-Clustering key.

איך בונים Clustered index נכון?

  1. מומלץ, אך לא חובה לבנות את האינדקס כUNIQUE.
    במידה והוא לא יהיה כזה:

א. בכל פעם שנחפש לפי ה-Nonclustered indexes הוא לא ידע עם איזה שורה יש לבצע את ה-Lookup  כי יש לו כמה ערכים שמתאימים לאותו מפתח.

ב. כדי להתגבר על הבעיה לעיל, ה-SQL יוסיף 4-byte uniquifier לכל ערך כפול ב-clustering key –> חבל על המקום ועל הזמן.

  1. מומלץ לבנותו כ-STATIC. הכוונה כאן היא לבנות את האינדקס על שדה שלא מתבצע עליו עידכונים בד"כ, כמו תאריך יצירה, ID, וכד'.
    במידה והוא לא יהיה כזה (לדוגמא, בחירת ה-Clustered index key בעמודת מספר הטלפון של לקוח – שאמנם היא מזהה חד חד ערכי, אך יכולה להשתנות)– כל עדכון שלו יגרום שינוי של כל ה-Nonclustered indexes האחרים בטבלה.
3. תכונה נוספת המומלצת בעת בניית Clustered index היא NARROW (זר וקטן ככל שניתן)– אחרת אנחנו "נבזבז" מקום על כל המופעים שלו בכל Nonclustered indexes.
(הרי אמרנו שבכל nonclusterd index מלבד השדות שבחרנו להיות בתוך האינדקס, שרת ה-SQL משרשר גם את ה-Clustered index column/s).

לסיכום, להלן דוגמאות של שימוש נכון ב-clustering keys :

  1. יש לבנות Clustered index על שדה identity.

  2. אם השדות שמרכיבים את האינקדס הם שילוב שלdate  ושל identity  – הסדר שלהם חשוב: קודם יש להגדיר את עמודת הdate  ואח"כ את עמודת הidentity .

<

p style="direction: rtl; text-align: right;">3. במידה ובונים את האינדקס על עמודה מסוג GUID  את מספר ה-GUID נייצר עם NEWSEQUENTIALID() ולא עם newid() – זה גם ימנע מאיתנו פרגמנטציה גם יצרוך פחות IO וגם יצרוך פחות מקום.
הסבר על הפונקציה החדשה תוכלו למצוא: http://www.fotia.co.uk/fotia/DY.19.NewSequentialId.aspx

נקודה למחשבה

בדוגמא השנייה לשימוש נכון של Clustered index, ציינו שילוב של עמודת date  ועם עמודת identity .

למה אנחנו צריכים את השילוב הזה? הרי אם יש לנו עמודת identity , היא מהווה מזהה חד-חד ערכי לרשומה – ואין שום משמעות לעוד מפתח נוסף?

ההסבר לצורך של שילוב בין שתי העמודות הוא:
דוגמא למקרה בו נרצה ליצור אינדקס משולב, היא אם אנחנו נירצה ליצור PARTITION על שדה שהוא מסוג תאריך.
עמודת התאריך תהיה חייבת להיות חלק מהאינדקס.
מכיוון שישנה עדיפות ליצירת Clustered index ייחודי, הוספת עמודת ה-Identity מאפשרת לנו את ה-Uniqueness.

שאלה שניה: למה הסדר שבו נגדיר את העמודות האינדקס הוא קודם שדה ה-date ואח"כ שדה ה-identity?

תשובה לשאלה – נסו לענות עליה בתגובות.
נענה על שאלה זו במאמר הבא בע"ה.

נתראה ברשומה הבאה !

ערן שקד – Eran@brillix.co.il
יועץ SQL Server בחברת Brillix

The following two tabs change content below.

ערן שקד

Latest posts by ערן שקד (see all)

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

הפונקציה COALESCE

כולם מכירים את הפונקציה NVL, ומשתמשים בה לא מעט. לעומת זאת, הפונקציה COALESCE, השייכת לאותה משפחה של פונקציות, פחות מוכרת, למרות שהיא קיימת כבר מגרסה 9i ועולה ביכולותיה על NVL הישנה והטובה. מצד שני, אף [...]

טיפ: איך לזכור ב-SQLPLUS שאתה בסביבת היצור

כמה פעמים קרה לנו שעבדנו עם sqlplus  מול סביבת יצור ביחד עם עוד כמה חלונות פתוחים ועם קצת לחץ? בקיצור כרגיל… מכוון שהכול כל כך לחוץ, הרצנו איזה פקודה הרסנית, ומיד הצטערנו עליה מאוד: אוי! הרצתי [...]
עבודה

עבודה תחת סביבת FireWall

אורי לרנר בטיפ קצר ושימושי על עבודה בסביבת אורקל [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: