Cost-Based Optimizer (CBO)
ה-Cost-Based Optimizer (מייעל מבוסס עלויות) הוא ה"מוח" המרכזי של כל מסד נתונים רלציוני מודרני (כמו PostgreSQL, Oracle, SQL Server, MySQL). התפקיד שלו הוא לקחת את שאילתת ה-SQL שכתבת, לחשוב על כמה דרכים שונות לבצע אותה (Execution Plans), לחשב את העלות (Cost) של כל דרך, ולבחור את המסלול הזול והמהיר ביותר לביצוע.
איך ה-CBO עובד מאחורי הקלעים?
כשנריץ שאילתת SQL, יש אולי 5 או 10 דרכים שונות לשלוף את הנתונים (למשל: האם לעשות Table Scan? איזה אינדקס לבחור? באיזה סדר לחבר בין הטבלאות Nested Loop או Hash Join?). ה-CBO עובר במסע קבלת החלטות הבא:
- יצירת תוכניות חלופיות (Candidate Plans): המנוע מייצר את כל האסטרטגיות החוקיות האפשריות שיביאו את התוצאה הנכונה.
-
הערכת עלויות (Cost Estimation): עבור כל תוכנית, ה-CBO מעריך כמה משאבים היא תצרוך. המדד המרכזי מבוסס לרוב על:
- I/O (קריאות דיסק): כמה בלוקים של נתונים צריך לקרוא מהדיסק אל הזיכרון.
- CPU (עיבוד מעבד): כמה פעולות חישוב או השוואה המעבד יצטרך לבצע (למשל במיון או סינון).
- בחירת התוכנית ה"זולה" ביותר: ה-CBO בוחר את התוכנית עם העלות הנמוכה ביותר ומעביר אותה למנוע הביצוע (Execution Engine).
על בסיס מה ה-CBO מקבל החלטות?
ה-CBO מקבל החלטות על סמך סטטיסטיקות ששמורות במסד הנתונים על הטבלאות והאינדקסים שלהן.
הסטטיסטיקות שמורות בתוך מסד הנתונים עצמו, בתוך טבלאות מערכת פנימיות (System Catalogs / Metadata / System Tables) שמנוהלות אוטומטית על ידי הדאטה בייס. משתמשים רגילים לרוב לא יכולים (ואסור להם) לגעת בטבלאות האלו ישירות, אבל מסד הנתונים קורא אותן בכל פעם כששאילתא מורצת כדי שה-CBO יוכל לבנות את תוכנית הביצוע האופטימלית.
סטטיסטיקות אלו כוללות:
- כמה שורות יש בסך הכל בטבלה (Row Count).
- מהו גודל הטבלה בדיסק.
- כמה ערכים ייחודיים יש בכל עמודה (Cardinality).
- מהם הערכים המינימליים והמקסימליים בעמודה (והיסטוגרמות של פיזור הנתונים).
אם הסטטיסטיקות בטבלה שלנו לא מעודכנות (למשל כי נוספו מיליון שורות חדשות ומסד הנתונים עוד לא עשה ANALYZE או UPDATE STATISTICS), ה-CBO יעשה חישובים שגויים לחלוטין, יבחר תוכנית ביצוע גרועה (Execution Plan לא אופטימלי), והשאילתא עלולה לקרוס או לרוץ שעות.
מעניין אותך לראות את הסטטיסטיקות האחרונות שיש לדאטה בייס? ישנה פקודה שנותנת לנו הצצה פנימה ב-SQL:
DBCC SHOW_STATISTICS ('users', 'IX_Users_Email');איך הסטטיסטיקות האלו מתעדכנות? (The Update Process)
הסטטיסטיקות אינן מתעדכנות באופן אוטומטי ושקוף, על כל שורה ושורה שמוסיפים או מוחקים; כי זה היה זולל משאבי מעבד ודיסק עצומים בזמן אמת. במקום זאת, הן מתעדכנות בכמה דרכים:
- תהליך אוטומטי ברקע (Auto-Analyze / Auto-Update Statistics): למסדי נתונים מודרניים יש מנגנון ברקע שמזהה מתי חל שינוי משמעותי בטבלה (למשל, אם נוספו או שונו יותר מ-%X מהשורות). כשזה קורה, המסד מריץ מחדש סקריפט סטטיסטיקות שקט.
-
עדכון ידני (Manual Refresh): לפעמים, אחרי פעולות מסיביות כמו טעינה לילית של מיליוני שורות, הסטטיסטיקות עדיין ישנות וה-CBO ייכנס "להלם". במצב כזה, המפתח או ה-DBA חייבים להריץ ידנית פקודת רענון:
SQL Server:
UPDATE STATISTICS my_table;נרצה לראות מה ה-CBO מצא לנו כתוכנית האולטימטיבית עבור השאילתא שלנו? נשתמש ב-EXPLAIN. ניתן לראות הרחבה על הנושא כאן:explain