Indexes in SQL Server - ilDBA Portal

Indexes in SQL Server

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

בס"ד

אינדקסים ב – SQL SERVER

לפני שנתחיל לדבר על אינדקסים נתחיל בהסבר קצת על "איך SQL שומר את הנתונים שלו?"
התשובה היא פשוטה– בתוך טבלאות. לא?
אז לא. בעצם, כן ולא. האובייקט שאליו אני מתייחס נקרא "טבלה" אבל שם בפנים זה נראה קצת אחרת. בתוך ה SQL המידע נשמר בעמודים בגודל של 8k כל אחד. ומה קורה עם העמודים האלה ? מה הם בדיוק מכילים ? כמה עמודים צריך לכל טבלה וכו'… זה תלויי בסוג הטבלה שלנו.

ישנם שני סוגי טבלאות. טבלאות עם clustered index וטבלאות בלי clustered index שנקראות טבלאות Heap. (זה שיש לטבלה אינדקסים זה נחמד, אבל כל עוד אין לה clustered index היא מוגדרת כ Heap).
מה ההבדל הגדול בין שני סוג הטבלאות האלו?
בטבלאות עם clustered index:

  1. המידע בטבלה יושב פיזית בסדר של ה clustered index. האינדקס מכתיב לSQL כיצד לשמור ולכתוב את הנתונים בדיסק.
  2. ביחד עם המפתח של האינדקס שבחרנו נשמרים גם כל שאר נתוני השורה. בקיצור כל המידע נמצא באותו מקום ומסודר ע"פ סדר האינקדס.
  3. העמודים עצמם עם המידע הפיזי מחוברים ביניהם ויש מצביעים בין העמודים קדימה ואחורה ולא צריך לחזור רמה לבדוק מה הדף הבא.
  4. השליפה של המידע ע"פ הערך המאנדקס היא מהירה.

לעומת זאת בטבלאות Heap :

  1. אין שום סדר לדפים שמכילים את הנתונים הפיזיים. הם יכולים להיות בכל מקום בדיסק, וללא כל סדר הגיוני.
  2. אם ניצור אינדקסים על טבלת Heap אבל הם לא יהיו clustered index – אלא non-clustered indexes הם לא יעשו סדר בבלגן. הם פשוט יצרו רשימה (סוג של תוכניה), שתצביע ותאמר שלערך מסוים שאותו אינדקסנו יש את המידע בדף מסוים בטבלת ה Heap לפי  IAM- (Index Allocation Map).
  3. העמודים שמכילים את המידע הפיזי לא  מחוברים ביניהם ואין מצביעים בין העמודים קדימה ואחורה אלא צריך לחזור לרמה העליונה, לדף ה – IAM  ולבדוק מה הדף הבא.  (ואם נהיה קצת יותר מדויקים: דפי ה –  IAM מכילים הצבעות על Extents,שהם ה – extents  מכילים את עמודי הטבלה).
  4. שליפת הנתונים מהטבלה היא הרבה יותר יקרה במונחים של IO.

נמשיך בהסבר על טבלאות עם  clustered index:

כדי להמחיש את תהליך בניית ה "טבלה" והאינדקס נשתמש בדוגמא הבאה:

CREATE TABLE Employee
(
EmployeeID int NOT NULL Identity,
LastName nvarchar(30) NOT NULL,
FirstName nvarchar(29) NOT NULL,
SSN char(11)NOT NULL,
…other columns
)

גודל שורה  = 400 bytes

מספר שורות בטבלה = 80,000

שלב ראשון – הסדר הפיזי של הנתונים

לאור הנתונים שהגדרנו לעייל ובגלל שכל עמוד מכיל =  8096 bytes יוצא לנו ש:

  1. מספר שורות בעמוד —< 20 שורות
  2. סה"כ עמודים בטבלה –> 4000 עמודים עבור ה leaf level.

בגלל שאנחנו מדברים על טבלה שהיא clustered,
4000 העמודים של טבלת העובדים יכתבו פיזית ע"פ סדר האינדקס. וזוהי בעצם הטבלה שלנו.

שלב שני  – מוסיפים את מבנה העץ

מתחילים מה – 4000 העמודים שנמצאים ב leaf level ועולים למעלה עד ה Root שיהיה תמיד עמוד אחד.

בעמודים האלה, במקרה של clustered index  על EmployeeID כל שורה תהיה של 13 bytes.

4 bytes EmployeeID (int) + 6 bytes page pointer + 3 byte row overhead

אם כל שורה היא של 13 bytes  אז כל עמוד ברמה הראשונה של ה Non-leaf level  יכול להכיל 622 שורות שיצביעו על ה  leaf level 8096/13=622 רשומות בעמוד.

אם יש לנו סה"כ 4000 עמודים ב – leaf level אז אנחנו צריכים עכשיו 7 עמודים ברמה השנייה. 4000/622=7.

שלב שלשי  – מסיימים את מבנה העץ המאוזן

ממשיכים לבנות עוד רמה של עץ עד שמגיעים לעמוד אחד בסוף. גם כאן כל שורה היא של 13 bytes  וכיוון שיש  לנו רק 7 עמודים ברמה השנייה – מספיק לנו עמוד אחד ל Root.

כמה נקודות ביניים לציון:

  1. אם טבלה משמשת לקריאה – אל תשאירו אותה Heap – צרו לה clustered index.
  2. ככל ששורה בטבלה ארוכה יותר (או הרבה שדות או ערכים ארוכים יותר) ה leaf level (במקרה של Clustered ) יכיל יותר דפים, וכתוצאה מכך כמות הדפים ב Non-leaf level יכולה לגדול ואתם העומק של העץ יגדל וכמות ה IO תגדל. (הפתרון Partition – על כך במאמר אחר).
  3. אבל לא רק שורות ארוכות משפיעות, גם בחירה לא נכונה של ה clustering key משפיעה, כמו שציינו כל שורה מדפי האינדקס בכל הרמות מכילה את ה clustering key. ככל שהוא יהיה ארוך יותר , יכיל יותר שדות , השורות יהיה גדולות יותר, כל דף יכיל פחות שורות ונצטרך יותר דפי אינדקס. (ויותר מזה –  לא רק ה clustered index  יושפע, כל שאר האינדקסים שמוגדרים על הטבלה יושפעו מזה כפי שנראה בהמשך). ולכן חשוב מאוד שהאינדקס יהיה קצר ככל שניתן.
  4. מספר הרמות בעץ האינדקס שלנו מושפעים משני הפרמטרים שציינו: גודל האינדקס ומספר הדפים ב leaf level.

מה אכפת לי כמה רמות יש לי בעץ? אכפת לי!

כל קריאה מטבלה מתחילה בחיפוש של הערך המאנדקס בדף ה – root וממנו בעזרת מצביעים מגיעים עד למידע שיושב ב leaf level, ככל שיהיו יותר רמות לעץ כמות ה IO תגדל.

ברשומה הבאה נמשיך על מבנה ה Non-clustered index וההשלכות של ה – clustered index עליו, ועוד כמה טיפים על בנייה נכונה של ה – clustered index.

The following two tabs change content below.

ערן שקד

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

2 תגובות ל- “Indexes in SQL Server”

[…] התחבר Home Cmd DBA SQL Server המגזין Indexes in SQL Server 2 […]

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

השאר תגובה:

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

*



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

איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א')

כחלק מהטמעות מוצרי IT תשתיתיים (כגון FWDB, מוצרי גיבוי, חוות Storage, מוצרי שו"ב ועוד…) בארגוני, אנו נדרשים המון פעמים לענות על שאלות לספקים כגון : מה גודל הכולל של ה –  Datacenter  ? מה חלוקת גודל ה [...]
מבוא

מבוא לבעיות ביצועים באורקל

The following two tabs change content below.BioLatest Posts עודד רז עודד רז, מנכ"ל חברת בריליקס ומייסד אתר זה. עודד הוא Oracle ACE Director ואחד מה-DBA-ים הבכירים ביותר בישראל, עם מעל 15 שנות ניסיון כ-DBA תשתיתי ואפליקטיבי. לעודד [...]

ה-SQL Performance Analyzer ב-Real Application Testing

בפוסט הקודם הזכרתי בקצרה את ה-SQL Performance Analyzer, בפוסט הזה אני אציג את ה-SPA, יתרונות וחסרונות של המוצר ואיך כדאי להשתמש [...]

מבוא ל- Real Application Testing

הפעם רציתי לסקור feature שלם שנקרא RAT (או בשמו המלא Real Application Testing). ה- feature הזה הוא database option של Oracle Enterprise Edition והוא לא חדש בכלל. הוא הוצג ב- 11gR1 וגם נמצא בשימוש לא מועט בעולם. משום מה, אצלנו בארץ לא יוצא [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: