חפיפה בין אינדקסים

16/02/2014

אין תגובות

נתקלתי פעם במקרה הבא- טבלה שהוגדרו לה שני אינדקסים:

  • Primary Key & Clusterd Index על עמודות Col1, Col2, Col3.
  • Unique Index על עמודות Col1, Col2.

כבר במבט ראשון אפשר לראות בעייה בסיסית – אם כל צירוף של Col1, Col2 הוא יחודי, מדוע המפתח כולל גם את עמודה Col3?
זה לא נכון, מי שינסה להכניס לטבלה את הצירופים 1,1,2ו-1,1,3 יקבל הודעת שגיאה בגלל האינדקס השני (למרות שלפי הגדרת המפתח שני הצירופים נראים תקינים), ומי שיצור Foreign Key לטבלה – יאלץ לכלול בה עמודה מיותרת ללא צורך.

יתכן ש"כוונת המשורר" הייתה שהמפתח הוא Col1,Col2 אבל הוא רצה ליצור Clustered Index שיכלול בנוסף למיון הראשי לפי Col1 ולמיון המשני לפי Col2, גם מיון תת-משני לפי Col3; אלא שזה מופרך כי כל צירוף של Col1,Col2 הוא יחודי ולכן לא יכול להיות לו מיון תת-משני.

יחד עם זאת, יתכן שבלי משים – פלוני כיוון לדעת גדולים. למשל, הוא מעוניין לשפר את ביצועי השליפות מ-OurTbl במקרים כדוגמת:

Select Col1,Col2,Col3

From   OurTbl

(Where ...)

---------------------

Select Col1,

       Col2,

       Max(Col3)

From   OurTbl

...

Group By Col1,

       Col2

...

לכאורה, די ב-Primary Key & Clusterd Index על עמודות Col1,Col2 כדי להגיע ביעילות לשורות המבוקשות, ולבצע מיונים (Order By) & סינונים (Where) & קיבוצים (Group BY) לפי עמודות המפתח; אלא שנצטרך בכל פעם לקרוא את השורות המבוקשות במלואן בשעה שדי לנו בשלוש העמודות Col1,Col2,Col3; ואו אז יש מקום לשקול אינדקס נוסף ולכאורה מיותר רק על שלושתן, ובמקרה זה ראוי שהוא יהיה Unique על Col1,Col2 ועמודה Col3 תיכלל ב-Include.
ניצור שתי טבלאות – T1 & T2, הראשונה רק עם Primary Key, והשניה גם עם אינדקס נוסף:

Select * Into T1 From sys.messages;

Alter Table T1 Add Constraint PK_T1 Primary Key Clustered (language_id, message_id);

 

Select * Into T2 From sys.messages;

Alter Table T2 Add Constraint PK_T2 Primary Key Clustered (language_id, message_id);

Create Unique Index Idx_T2 On T2 (language_id, message_id) Include (severity);

וכעת נבצע שתי שליפות לדוגמה ונשווה בינהן מבחינת הביצועים:

Select    language_id, message_id,severity

From    T1

Where    severity=20;

 

Select    language_id, message_id,severity

From    T2

Where    severity=20;

image

Select    language_id, 

        message_id,

        Max(severity)

From    T1

Group By language_id, 

        message_id;

 

Select    language_id, 

        message_id,

        Max(severity)

From    T2

Group By language_id, 

        message_id;

image

בשתי הדוגמאות – השליפה הראשונה הייתה פי 10 יותר יקרה מהשניה כי היא חייבה שליפה של השורות במלואן מה-Clustered Index ולא של שלוש העמודות הרלוונטיות מהאינדקס הלכאורה מיותר (למעשה- מועיל מאוד במקרה זה).

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *