הקדמה

Indexed view הוא פיצ’ר מאד שימושי, אבל גם באופן יחסי פחות מוכר – לפחות לאוכלוסיית המפתחים. מדובר למעשה בשילוב בין Views לבין אינדקסים “רגילים” (non-clustered indexes) שקיימים על טבלאות. לפני שנבין מה הבעייה, ואיך אפשר להשתמש ב- Indexed Views כדי לפתור אותה, ניישר קו לגבי מס’ מונחים.

View – אפשר לחשוב על view בתור שאילתת SELECT ששמורה בתור אובייקט ב-DB שלנו. אם, למשל, יש לנו view בשם MyView הוא יכול לייצג שאילתה כלשהי (למשל, SELECT ColumnA, Column B FROM MyTable WHERE ColymnC=5) וכל שאילתה שנריץ על ה- view תהיה בפועל כאילו הרצנו אותה על ה- result set שחוזר מהשאילתה של ה- VIEW. למשל, אם בדוגמא הזאת נריץ SELECT * FROM MyView WHERE ColumnA=1 אז אנחנו למעשה מתשאלים את כל השורות מטבלת MyTable שבהם ColumnC הוא 5 (מהגדרת ה- view) ו- ColumnA הוא 1 (מהשאילתה שאנחנו עושים).

Clustered Index – כאשר לטבלה קיים Clustered Index, המשמעות של זה היא שהנתונים של הטבלה מסודרים בדיסק לפי ה- key של ה- clustered index. כלומר, בפועל בדיסק שמור מבנה נתונים (B-tree) שהשורות עצמן הן האיברים בו. כלומר, בפועל, הטבלה מסודרת לפי ה- clustered index בדיסק. למשל, אם ה- clustered index שלנו הוא על עמודת ID שמהווה מספר סדרתי עולה, אז זה אומר שהנתונים של הטבלה בדיסק מסודרים לפי המספר הזה.

Non-clustered index – מדובר למעשה במבנה נתונים (גם כן, B-tree) ששמור ובנוי לפי ה- key columns שהוגדרו לו. אולם, בניגוד ל- clustered index, השורה עצמה לא כלולה בעץ, אלא כלול הערך של ה- clustered index של השורה (במידה שקיים), או מצביע לשורה (הקרוי RID) במידה שאין clustered-index, כלומר שמדובר למעשה ב- “heap” (טבלה ללא clustered index). כלומר, בניגוד ל- clustered index שמהווה “אינדקס ראשי” על הטבלה, אפשר להתייחס ל- non-clustered index בתור “secondary index” שהתפקיד שלו לעזור במצבים מסויימים של גישה לנתונים (למשל, בפילטור לפי ה- key columns שמוגדרים ב- non-clustered index).

אם מישהו לא מכיר מה זה אינדקסים, אני ממליץ לו בחום שלא להסתפק בהסבר הבסיסי (מאד) שכתבתי בו ולקרוא על הנושא יותר לעומק.

הבעייה

Indexed View יכול לתת מענה למס’ בעיות שונות, ולהיות למעשה עוד כלי בארסנל הכלים לפיתרון בעיות ביצועים. אחת הבעיות לדוגמא ש- Indexed View יכול לפתור בקלות היא בעיית המידע הסיכומי. נניח לדוגמא שיש לנו טבלה שבה כל שורה מייצגת איזשהו “אירוע” כלשהו. למשל, ניקח טבלה שמתארת הדפסות שנעשות ע”י איזשהו בית דפוס שנראית ככה:

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

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

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

הפיתרון

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

נוכל גם לשים לב שמכיוון שיש הרבה חזרתיות, ולכן יש לנו יתרון לשמירת המידע הסיכומי בלבד ברוזולוציית זמן מסויימת. כלומר, אם נשמור עבור צירוף ייחודי של משתמש, מדפסת ותאריך את סה”כ העמודים שהוא הדפיס – נוכל לתת מענה לשאילתות שציינו קודם: נוכל לדעת בקלות שהיו הדפסות (אחרת לא יהיו שורות בטבלה הסיכומית), וגם לדעת על היקף הפעילות של המשתמש (סכום העמודים ללא תלות בכמות העמודים שהוא מדפיס), או היקף השימוש במדפסת (סכום העמודים, ללא תלות במשתמש שהדפיס).
מכיוון שהמידע שלנו מכיל הרבה חזרתיות, בזה שנשמור את המידע הסיכומי נוכל למעשה להפחית את כמות ה- data שעליו אנחנו מתשאלים (הטבלה הסיכומית תהיה קטנה משמעותית) ולמעשה לייעל מאד את השאילתות שלנו ששולפות: יש פחות data ולכן גם שליפה על אינדקס שהוא לא ספיציפי לשאילתה ולא covered index יכולה להיות מאד מהירה ולעזור.

אחרי שהבנו שיש יתרון ברור לשמירת המידע הסיכומי, נשאלת השאלה מה עושים איתו ואיך מייצרים אותו: נוכל למשל לעשות job שמייצר ומעדכן איזושהי טבלה סיכומית שאנחנו שומרים ב-DB שלנו (למשל, עושה MERGE למס’ השורות). נוכל לעשות job שטוען delta של המידע ל- SQL Server Analysis Services ולעשות את התשאול מולו. נוכל גם לתזמן יצירת דו”ח בכלי BI אחר שבו אנחנו עושים שימוש (למשל Tableau).  בדרכים האלה נוכל להשיג למעשה שמירה של המידע בצורה סיכומית, אבל לא זמינה מיידית (אלא מתעדכנת כשרץ ה- job המתאים).

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

דרך נוספת, אלגנטית יותר, שאפשר להשתמש בה לעיתים (אבל לא תמיד, נדבר על החסרונות בהמשך), היא לעשות שימוש ב- indexed views.

בעוד ש- view רגיל הוא למעשה metadada בלבד (כלומר, לא מבנה נתונים שנשמר בדיסק), indexed view הוא view שיש לו clustered index, כלומר ששמור עבורו מבנה נתונים בדיסק שמכיל את נתוני ה- view. ה-clustered index מופיע בתור index על ה- view, אבל בפועל אפשר להתייחס אליו גם בתור עוד אינדקס על טבלת המקור, כך שכל פעולה על טבלת המקור (או טבלאות המקור, כי נתמך גם join עם מגבלות מסויימות) מעדכן למעשה את מבנה הנתונים הזה ושומר על ה- indexed view מעודכן באופן מיידי.

דוגמא ליצירת Indexed View

תחילה, יש לייצר את ה-View שאנחנו נרצה לייצר עליו את ה- clustered index:

   1: CREATE VIEW PrintJobsAggregated WITH SCHEMABINDING 

   2: AS

   3: (

   4:     SELECT    DateStarted = CAST(JobSentEventTime as date),

   5:             UserID,

   6:             PrinterID,

   7:             TotalPages = SUM(NumPages),

   8:             PrintCounts = COUNT_BIG(*)

   9:     FROM dbo.PrintJobs

  10:     GROUP BY CAST(JobSentEventTime as date), UserID, PrinterID

  11: )

נשים לב שבשורה הראשונה אנחנו מגדירים את ה- view עם SCHEMABINDING. המשמעות של ההגדרה הזאת היא שלא ניתן יהיה לעשות שינויים בעמודות הטבלה המקורית, בצורה שתשבור את הגדרת ה- view (בניגוד ל- view שלא מכיל את ההגדרה הזאת, שלא מונע שינויים ל- base table בצורה כזאת ששוברת את ה- view). שימו לב שכאשר עושים שימוש ב SCHEMABINDING, כל התייחסות לאובייקט ב-DB צריכה להיות מורכבת מ <schema_name>.<object_name> (כלומר, לא רק שם הטבלה, אלא שם הסכימה לפניה).

דרישה נוספת שבאה לידי ביטוי היא ש- indexed view שמכיל GROUP BY חייב להכיל גם COUNT_BIG של מס’ השורות, כפי שניתן לראות בדוגמא שלנו (במקרה של שמירה סיכומית של נתונים, ממילא מדובר במשהו שבד”כ נרצה).

בשלב הזה אנחנו מחזיקים ביד סתם View רגיל – חסר ייחוד בינתיים. כעת, נרצה לייצר את ה- clustered index עליו. אם אנחנו משתמשים ב- SSMS, נוכל לעשות זאת גם דרך ה-GUI (כמו שיוצרים אינדקס על טבלה, פשוט על ה- view). ה-CREATE statement ייראה בסוף כך:

   1: CREATE UNIQUE CLUSTERED INDEX [CI_PrintJobsAggregated] ON [dbo].[PrintJobsAggregated]

   2: (

   3:     [DateStarted] ASC,

   4:     [UserID] ASC,

   5:     [PrinterID] ASC

   6: )

נשים לב לכך ה- clustered index על ה- view חייב להיות unique (בניגוד ל- clustered index על טבלה), ובאמת אנחנו עושים אותו על שילוב עמודות שמובטח לנו שהוא ייחודי (בגלל ה-GROUP BY שלנו).

שימוש ב- Indexed View

זהו, יצרנו indexed view. בואו נראה עכשיו קצת את ההשפעה של זה בפועל. לטובת זה, מילאתי את הטבלה ב- 2.1 מיליון שורות פיקטיביות שמתארות הדפסות בטווח תאריכים של 10 ימים. כאמור, מדובר בטבלה שבאופן שלה מתארת אירועים בעלי חזרתיות גבוהה של ערכים בעמודות UserID ו- PrinterID באותו היום (כלומר, יש הרבה אירועים באותו יום שנעשים ע”י אותו בן אדם מול אותה המדפסת), ולכן ב- indexed view שלנו יש בפועל קצת יותר מ-100,000 שורות. משמעותית פחות מאשר בטבלה המקורית.

אם נעשה שליפה של כמה שורות מה- indexed view נראה שהוא נראה כך:

כבר בשליפה הפשוטה של SELECT TOP … מה- indexed view ניתן לשים לב להבדל. נשווה את ה- execution plan בין השליפה מה- indexed view כמו שהוא מוגדר עכשיו, לבין שליפה מ- view זהה (פשוט לפני היצירה של ה- clustered index):

בלי ה-clustered index:

עם ה- clustered index:

נשים לב שבעוד בתמונה הראשונה, ללא ה-clustered index על ה- view, אנחנו מבצעים בפועל full scan של ה- clustered index של הטבלה עצמה, כאשר כן מוגדר ה-clustered index על ה- view, אז השליפה מתבצעת עליו והיא כפי שניתן לראות זולה משמעותית מבחינת ה- cost.

חשוב לציין שה- indexed view מתנהג למעשה כמו כל index על הטבלה. זה בא לידי ביטוי גם בשליפות, וגם בהכנסות. הצורה שבה ניתן לראות את זה בא לידי ביטוי בשליפות, היא מה יקרה אם נריץ את השאילתה הבאה:

   1: SELECT    DateStarted = CAST(JobSentEventTime as date),

   2:         UserID,

   3:         TotalPages = SUM(NumPages)

   4: FROM dbo.PrintJobs

   5: GROUP BY CAST(JobSentEventTime as date), UserID

מדובר למעשה בשאילתה שמזכירה מאד את השאילתה של ה- indexed view עצמו, פשוט כאשר ה- grouping לא כולל את ה- PrinterID והשליפה לא כוללת את ה- COUNT_BIG. אם נסתכל על ה- execution planהוא נראה כך:

כלומר נבחר execution plan שעושה שימוש באינדקס של ה- indexed view. למה? מסיבה פשוטה. הוא עוזר לשליפה. בעוד שלעשות את השליפה מול הטבלה המלאה הייתה מייצרת למעשה מעבר על 2.1 מיליון שורות, ב-indexed view יש משמעותית פחות שורות, בגלל החזרתיות הגבוהה בטבלת המקור – ולכן השליפה מולו משתלמת יותר.

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

NOEXPAND

בואו נניח שאני רוצה לעשות שליפה ולהביא את כל הדפסים שעבדו בתאריכים 15.10.2016-19.10.2016 ולא עבדו בתאריכים 21.10.2016-23.10.2016. לכאורה, מדובר בשליפה שאין שום יתרון ל- indexed view שלנו בביצוע שלה: היא לא כוללת אגרגציה משום סוג, לא משתמשת בעמודות האגרגטיביות של ה- view – בקיצור, לכאורה אין יתרון. עם זאת, ל- indexed view יש יתרון חשוב: הגודל שלו הוא כ-6% מגודל הטבלה המקורית. כלומר,  גם אם מדובר בסריקה של כל הנתונים, עדיין מדובר בסריקה של פחות נתונים.

ככה תראה למשל השאילתה שהגדרנו:

   1: SELECT DISTINCT UserID

   2:   FROM [check1].[dbo].[PrintJobsAggregated]

   3:   WHERE DateStarted BETWEEN '2016-10-15' AND '2016-10-19' 

   4:         AND NOT EXISTS (SELECT NULL

   5:                         FROM PrintJobsAggregated sec

   6:                         WHERE sec.UserID = PrintJobsAggregated.UserID AND sec.DateStarted BETWEEN '2016-10-21' AND '2016-10-23')

וכך נראה ה- execution plan שלה:

נשים לב לכך שהשליפה מתבצעת בפועל מול PrintJobs (ה- clustered index scan הוא על ה- PK של PrintJobs, שהוא ה- clustered index שלנו), ואנחנו מקבלים המלצה לאינדקס שייעל את השליפה: אם היה לנו nonclustered index על JobSentEventTime (או בעדיפות, אם הוא היה ה- clustered index) השליפה הייתה מהירה יותר (הזמן הנוכחי שלה הוא בממוצע 833ms על המכונה שלי, והיא מבצעת 74,163 logical reads).

אבל גם עכשיו, בלי לעשות שום שינוי במבנה הטבלה, אנחנו יכולים לייעל את השליפה הזאת תוך הסתמכות על ה- indexed view. נרצה למעשה להוסיף hint שרומז ל- query optimizer שלא לפתוח את הגדרת ה- view אלא לעבוד מול ה- indexed view בכל אופן. נעשה את זה באמצעות הוספת ה- hint שנקרא NOEXPAND – כך תראה השאילתה לאחר השינוי:

   1: SELECT DISTINCT UserID

   2:   FROM [check1].[dbo].[PrintJobsAggregated] WITH(NOEXPAND)

   3:   WHERE DateStarted BETWEEN '2016-10-15' AND '2016-10-19' 

   4:         AND NOT EXISTS (SELECT NULL

   5:                         FROM PrintJobsAggregated sec WITH(NOEXPAND)

   6:                         WHERE sec.UserID = PrintJobsAggregated.UserID AND sec.DateStarted BETWEEN '2016-10-21' AND '2016-10-23')

וכך נראה ה- execution plan:

זמן הריצה ירד ל- 22ms וכמות ה- logical reads ירדה ל-285. שיפור דרמטי לעומת ההרצה הקודמת.

למה זה קרה? משתי סיבות עיקריות. הראשונה, ה-clustered index של ה- indexed view קטן משמעותית מה- clustered index של הטבלה עצמה, ולכן שליפות מולו כוללות פחות קריאות. השנייה, העמודה הראשונה ב- clustered index של ה- indexed view היא למעשה DateStarted, כלומר ניתן לעשות seek עליה ולקבל את אותו המענה שהיינו מקבלים אם היינו יוצרים את האינדקס שהומלץ ע”י ה- query optimizer.

המסקנה פשוטה: כאשר יש indexed view אגרגטיבי, שמקטין לנו את מס’ השורות, נוכל לספק איתו מענה גם לשאילתות שלא עושות שימוש באגרגציות השונות ששמרנו, אלא רק בעובדה שפשוט יש פחות מידע. לעיתים, נצטרך להשתמש ב- hint של NOEXPAND כדי לקבל את ה- execution plan הרצוי.

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

יצירת non-clustered indexes על ה- Indexed View

עוד פיצ’ר שכדאי להכיר, הוא שברגע שיש לנו clustered index על איזשהו view, אנחנו יכולים לייצר עליו non clustered indexes נוספים. למשל, נוכל לייצר את האינדקס הבא על ה- view:

   1:  

   2: CREATE NONCLUSTERED INDEX [IX_PrintJobsAggregated_PrinterID] ON [dbo].[PrintJobsAggregated]

   3: (

   4:     [PrinterID] ASC

   5: )WITH (SORT_IN_TEMPDB=ON)

ולהשתמש בו כדי לשפר שליפות על מדפסות מסויימות שנעשות מול ה- indexed view.

כמובן, שצריך לזכור שלכל אינדקס יש עלות (מעבר לעלות ה- storage) בפעולות CRUD (הכנסה, עדכון ומחיקה).

מתי אי אפשר לייצר Indexed View?

מי שיסתכל על התיעוד של Indexed Views ב- msdn, יראה שיש רשימה ארוכה של statements שאם נעשה בהם שימוש ב- view אז לא ניתן להפוך אותו ל- indexed view. למשל, אם היינו רוצים לעשות view שעבור כל שילוב של משתמש ומדפסת מציג את התאריך והשעה הראשונים שהם הופיעו, האחרונים שהם הופיעו, ובכמה ימים שונים הופיע הצימוד – היינו נכשלים, כי הוא מכיל MIN ו-MAX.

יש לציין שעבור חלק מהמקרים האלה ניתן לעשות מעקפים פשוטים. למשל, אם היינו רוצים לעשות את ה- view שתואר קודם, היינו יכולים למעשה לעשות את אותו ה- view שהגדרנו עכשיו,  להגדיר אותו כ- indexed view, ולעשות view נוסף מעליו שעושה את מה שאנחנו רוצים:

   1: CREATE VIEW PrintJobsMinMax 

   2: AS

   3: (

   4:     SELECT    UserID,

   5:             PrinterID,

   6:             FirstSeen = MIN(DateStarted),

   7:             LastSeen = MAX(DateStarted),

   8:             NumDistinctDays = COUNT(DISTINCT(DateStarted))

   9:     FROM PrintJobsAggregated

  10:     GROUP BY UserID, PrinterID

  11: )

כאשר בפועל ה- view הזה לא יכול להיות indexed view בפני עצמו, אבל הוא נעזר ב- indexed view שעושה עבורו “חצי מהעבודה” של האגרגציה לרוזולוציה של אחת ליום.

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

סיכום

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