EXPLAIN

מה זה Optimizer?

נחשוב עליו כמו על WAZE (או Google Maps, כל אחד והבחירה שלו בניווט 🙂). כשנכתוב ל DB שאילתה כמו: "תביא לי את כל הלקוחות שקנו בחודש האחרון", הוא יחזיר לנו את רשימת הלקוחות, אבל אנחנו לא יודעים איך הוא הגיע אליהם מאחורי הקלעים.

כאן נכנס ה Optimizer לתמונה. הוא מקבל את הבקשה שלך, ומבצע תהליך שנקרא Query Optimization המורכב מ 4 חלקים עיקריים:

1. ניתוח: הוא בודק את השאילתה שלך ומפרק אותה לחלקים לוגיים.
2. חיפוש נתיבים: הוא בוחן את כל הדרכים האפשריות לשלוף את המידע. למשל: האם לסרוק את כל הטבלה? (זה כמו לנסוע דרך כבישים צדדיים בלי תכנון), האם להשתמש באינדקס מסוים כדי לקפוץ ישר לשורות הרלוונטיות? (כמו לעלות על כביש מהיר), ובאיזה סדר לבצע את ה-Joins בין הטבלאות.
3. חישוב עלות (Cost): ה-Optimizer מעריך את "העלות" של כל נתיב, כלומר, כמה משאבים (CPU, זיכרון, I/O מהדיסק) כל דרך תצרוך.
4. בחירה: הוא בוחר את המסלול שנראה לו הכי מהיר ויעיל, וזוהי "תוכנית הביצוע" (Execution Plan) שהוא מוציא לפועל.

ניתן להשוות את ה-EXPLAIN ל-Debugger ב-IDE. כמו שדיבאגר נותן לך לראות מה הState של המשתנים ואיזה שורות קוד רצות, EXPLAIN הוא הדיבאגר של הדאטה שלך.

דוגמת הרצה: איך מגיעים לתוצאה?

נניח ואנחנו מריצים את השאילתה הבאה עם הקידומת EXPLAIN:

EXPLAIN SELECT o.order_id, p.product_name, o.order_date FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.customer_id = 12345 AND o.order_date > '2026-01-01';

זו התוצאה (החלקית והחשובה) שיכולה להתקבל בבסיס הנתונים:

+-------------+-------+--------+---------------+-------------+---------+------+-----------------------+
| select_type | table | type   | possible_keys | key         | key_len | rows | Extra                 |
+-------------+-------+--------+---------------+-------------+---------+------+-----------------------+
| SIMPLE      | o     | ref    | customer_id   | customer_id | 4       |   15 | Using index condition |
| SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY     | 4       |    1 |                       |
+-------------+-------+--------+---------------+-------------+---------+------+-----------------------+

איך לקרוא את התוצאה?

🚩 דגלים אדומים - מה מעורר חשד? תוצאות בעמודת TYPE

1. ALL (Full Table Scan)
בסיס הנתונים עובר שורה - שורה מתחילת הטבלה ועד סופה כדי למצוא את מה שביקשת. האם זה תמיד גרוע? לא. אם הטבלה קטנה ומכילה 100 שורות, אין שום בעיה. אבל אם הטבלה מכילה 10 מיליון שורות? אנחנו בבעיה קשה והשאילתה תזחל.

2. מלכודת האינדקס המורכב (The Leftmost Prefix Rule)
הרבה מפתחים חושבים שאם הם יצרו אינדקס אחד שמורכב משתי עמודות, למשל (First_Name, Last_Name), הם מכוסים. בפועל, אינדקס מורכב עובד בהקפדה משמאל לימין (כמו ספר טלפונים ישן או רשימת אנשי הקשר בנייד). אם נחפש "כהן, משה", קל מאוד למצוא. אבל נחפש את כל מי שקוראים לו "משה" (בלי לדעת את שם המשפחה),נאלץ לסרוק את כל הספר מתחילתו ועד סופו! סדר העמודות באינדקס הוא קריטי.

3. Using filesort או Using temporary
ה-DB לא הצליח להשתמש באינדקס כדי לשלוף את הנתונים בסדר המבוקש, אז הוא נאלץ ליצור טבלה זמנית בזיכרון (או גרוע מכך, על הדיסק) כדי למיין את התוצאות באופן עצמאי. זהו תהליך יקר ומיותר. במצב כזה נבדוק: האם השאילתה רשומה בצורה תקינה? לעיתים קרובות מדובר בשימוש לא נכון ב-ORDER BY או ב-GROUP BY, או שאין אינדקס שתומך בדרך שבה מיינו את הנתונים.

4. key: NULL
ה-Optimizer החליט שאף אינדקס בטבלה לא רלוונטי לשאילתה שלך (אולי כי יש יותר מדי ערכים זהים, או כי הוא לא בנוי נכון). יש לבדוק את האינדקסים הקיימים או לשקול להוסיף חדשים.

ריכוז הדגלים האדומים ב-EXPLAIN:

אינדקס מכסה (Covering Index / Index Only Scan)

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

ב-EXPLAIN זה יופיע תחת העמודה Extra בתור Using index (שימו לב!, זה שונה מ-Using index condition). זה המצב האידיאלי, השאילתה שלך פשוט תטוס.