אופטימיזציה של שאילתות SQL באמצעות ניתוח EXPLAIN עבור 1C-Bitrix

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

לדוגמה, עמוד רשימת מוצרים נטען 8–10 שניות כי MySQL סורק מיליוני שורות ב-b_iblock_element, בעוד שמפתחים לעולם לא בודקים את יומן השאילתות האיטיות. לקוחות מאבדים המרות, ומשאבי שרת מתבזבזים. המשימה שלנו היא למצוא צווארי בקבוק באמצעות ניתוח EXPLAIN תוך 2–5 ימים ולתקן אותם.

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

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

כיצד לקרוא EXPLAIN

EXPLAIN SELECT * FROM b_iblock_element WHERE IBLOCK_ID = 5 AND ACTIVE = 'Y' ORDER BY SORT;

עמודות פלט עיקריות:

לאבחון מדויק יותר, השתמש ב-EXPLAIN ANALYZE (MySQL 8.0+, MariaDB 10.9+), אשר מריץ את השאילתה ומציג זמן בפועל:

EXPLAIN ANALYZE SELECT ...;

כיצד EXPLAIN חושף שאילתות איטיות

ביומן השאילתות האיטיות, אנו מחפשים שאילתות עם זמן ביצוע > 1 שנייה. עבור כל אחת, אנו מריצים EXPLAIN. אם אנו רואים type=ALL או rows > 10,000 עבור שאילתת נקודה, שאילתה זו היא מועמדת לאופטימיזציה. לדוגמה, בפרויקט אחד מצאנו שאילתה מול b_iblock_element_property עם rows=1,200,000. לאחר הוספת אינדקס, rows ירד ל-1,200, וזמן הביצוע ירד מ-3 שניות ל-0.01 שניות.

בעיות נפוצות ב-Bitrix

שימוש ב-filesort על b_iblock_element הוא ממצא שכיח. השאילתה ממיינת לפי SORT, אך האינדקס אינו מכסה את הצירוף (IBLOCK_ID, ACTIVE, SORT). הפתרון: אינדקס מורכב:

ALTER TABLE b_iblock_element ADD INDEX ix_iblock_active_sort (IBLOCK_ID, ACTIVE, SORT);

לאחר הוספת האינדקס, EXPLAIN מציג type=ref ו-Extra ללא Using filesort. זמן הביצוע יורד משניות למילישניות.

rows = 500,000 בשאילתה אל b_iblock_element_property מתרחש בעת סינון לפי ערך מאפיין ללא אינדקס על (IBLOCK_PROPERTY_ID, VALUE). עבור שדה VARCHAR בשם VALUE, יש להשתמש באינדקס קידומת VALUE(64). הדבר מפחית את מספר השורות הנסרקות למאות.

Using temporary עם GROUP BY מופיע בשאילתות סינון פאסטות. פאסטות של Bitrix בונות טבלאות מותאמות b_iblock_find_* — אם הן אינן נבנות מחדש לאחר הוספת מאפיינים, השאילתות עוקפות אותן.

אבחון סריקות ALL

type=ALL הוא המקרה הגרוע ביותר: MySQL סורק את כל הטבלה. ב-Bitrix, זה קורה לעתים קרובות בשאילתות ללא תנאים או עם תנאים על שדות שאינם אינדקסים. אנו מוסיפים מיד אינדקסים חסרים.

לדוגמה, ל-b_iblock_element צריך להיות אינדקס על (IBLOCK_ID, ACTIVE), ול-b_iblock_element_property על (IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID). אם הבעיה היא בקוד הרכיב, אנו כותבים מחדש את השאילתה באמצעות CIBlockElement::GetList עם פרמטרים נכונים.

מדוע ניתוח EXPLAIN עדיף על גישה אינטואיטיבית

ניסיון מלמד: מפתחים לעתים קרובות מוסיפים אינדקסים 'לפי העין' מבלי לבדוק את תוכנית השאילתה. EXPLAIN נותן תמונה אובייקטיבית. השוו בעצמכם: ללא אינדקס, השאילתה סורקת 500,000 שורות ב-2 שניות; עם אינדקס, 500 שורות ב-0.002 שניות. הבדל של פי 250. רק כך מבטיחים תוצאות.

מה כוללת העבודה שלנו

1. ביקורת יומן שאילתות איטיות — איסוף כל השאילתות האיטיות במשך שבוע. אנו משתמשים ב-pt-query-digest או בכלים המובנים של MySQL.
2. ניתוח EXPLAIN של כל שאילתה חשודה — זיהוי type=ALL, Using filesort, Using temporary.
3. תכנון אינדקסים — תוך התחשבות בעומס ובמבנה הנתונים. לעיתים אינדקס עלול לפגוע בביצועי ההוספה, ולכן אנו מעריכים את האיזון בין היתרונות לחסרונות.
4. הוספת אינדקסים ושכתוב שאילתות — אם אינדקס אינו עוזר, אנו מתקנים את קוד הרכיב או את השאילתה (למשל, שינוי ORDER BY או הוספת FORCE INDEX).
5. הרצת EXPLAIN מחדש — אימות ש-type השתנה וש-rows ירד.
6. בדיקה על נתוני ייצור — מדידת זמן הביצוע לפני ואחרי. שימוש בפרופיילינג באמצעות EXPLAIN ANALYZE.
7. תיעוד — רישום כל השינויים וההמלצות לניטור שוטף.

השוואה לפני ואחרי

לוחות זמנים ועלויות

אופטימיזציה של שאילתה טיפוסית אחת (ניתוח + תיקון) אורכת בין 2 ל-5 שעות. ביקורת מלאה של פרויקט אורכת בין 2 ל-5 ימי עבודה. העלות עבור אופטימיזציה של שאילתה אחת נקבעת לאחר ביקורת. צרו קשר לקבלת ביקורת מקדימה של יומן השאילתות האיטיות שלכם. הזמינו אופטימיזציה של SQL באמצעות ניתוח EXPLAIN ואנחנו נתחיל בניתוח של שאילתה אחת.

מידע נוסף: Wikipedia: EXPLAIN