מדריך לייעול שאילתות בשפת SQL

שימוש באינדקסים לייעול שאילתות

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

דוגמה – בטבלת לקוחות שחלק גדול מהשליפות עליה מתבסס על שדה שם הלקוח, כדאי יהיה ליצור אינדקס על השדה הזה. לאחר יצירת האינדקס, כאשר נרצה לשלוף מהטבלה את כל הלקוחות המתחילים באות ע' המנוע של הדאטה-בייס לא יסרוק את כל השורות בטבלה כדי למצוא את השמות המתחילים באות ע', אלא המנוע ישתמש בטבלה קטנה שהוא הכין מבעוד מועד (Hash table) אשר תראה לו לאיזה שורה עליו ללכת כדי למצוא את הלקוחות המתחילם באות ע'.

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

מבחינה טכנית יצירת אינדקס היא משימה פשוטה. אפשר ליצור אותו דרך הממשק של ניהול הדאטה-בייס או באמצעות משפט SQL.

CREATE INDEX AAA_idx ON BBB (AAA);

כאשר :

AAA – שם השדה שעליו ניצור את האינדקס.

BBB – שם הטבלה שבה נמצא השדה.

כמובן, שאם רוצים ליצור אינדקסים מורכבים יותר, אז גם הפקודה תהיה מורכבת יותר.

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

שימו לב שישנם דאטה-בייס אשר להם אין אינדקסים (למשל snowflake). בדאטה בייסים אלא יש ברוב המקרים אלמנטים מקבילים כגון clusters.

איך נייעל שאילתות מורכבות בעזרת טבלה עם אינדקס?

נחזור למשימה שלנו מהסעיף הקודם:

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

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

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

ביצוע פעולות על שדות שיש להם מפתח – שמירה על המפתח

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

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

לדוגמה – במקום לסנן את הרכישות שבוצעו בשנת 2021 בעזרת פונקציית year על שדה purch_date נעדיף להשתמש בפונקציות between שפועלת על תאריך מלא ללא מניפולציות:

year(purch_date) = 2021

purch_date between ‘2021-01-01’ and ‘2021-21-31’

ייעול שאילתות על ידי שימוש בטבלאות אגרגטיביות במקום גרנולריות

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

שליפה לא יעילה – לצורך השליפה נחבר בין הטבלה של שמות המחלקה לטבלת המכירות (לפי השדה של המחלקות) ונקבץ את המכירות לפי שם מחלקה ושם מנהל.

שליפה יעילה – נכתוב תת שאילתה היוצרת טבלה אגרגטיבית של המחלקות והמכירות של כל מחלקה, ולטבלה זאת נחבר את שמות המנהלים.

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

כתיבת שאילתות לסינון שורות בתוך הוראת IN

בכתיבת שאילתות ישנה פרקטיקה של שימוש בתת שאילתות בתוך בפונקציית In או Exists.

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

הבעיה בשיטה היא שהשימוש בשאילתה בתוך פונקציית in גורמת לאיבוד האינדקס (עליו הרחבנו בסעיף הקודם). במקום זאת נעדיף לחבר את טבלת הלקוחות בעזרת join Inner ולסנן את הלקוחות שגרים בעפולה. בדרך זאת לא נאבד את האינדקס בעת החיבור בין טבלת ההזמנות לטבלת הלקוחות.

תרגול שפת SQL

באתר strata scratch תמצאו תרגילים רבים עם שאלות עסקיות מורכבות, וממשק המאפשר כתיבה והרצה של שאילתות כך שתוכלו לבדוק האם הפתרון שלכם נכון. באתר יש גם אפשרות לתרגל תרגילים ב- Python.

התרגול באתר הוא ללא עלות, אך אפשר להירשם כדי לקבל גישה לתרגילים חדשים ופתרונות לכל התרגילים (גילוי נאות – רישום לאתר דרך הקישור מתגמל אותי בעמלה קטנה, וגולשים המגיעים דרך הקישור יכולים להשתמש בקוד קופון yuval20 על מנת לקבל 20% הנחה).

נכתב על ידי יובל מרנין,
לשרותי ניתוח נתונים ויצירת דוחות BI ניתן לפנות אליי ב- לינקדאין, פייסבוק או במייל: [email protected]

*** לפודקאסט של הבלוג לחצו כאן ***