אופטימיזציית שאילתות MySQL (EXPLAIN, Slow Query Log, Indexes)

אופטימיזציית שאילתות MySQL (EXPLAIN, Slow Query Log, אינדקסים)

אנו מתמודדים עם מסדי נתונים שבהם 80% מהשאילתות האיטיות נגרמות משלוש סיבות: אינדקסים חסרים, שימוש לא נכון באינדקסים, או JOIN שאינו אופטימלי. Slow Query Log הוא הכלי הראשון למציאת בעיות אלו. ניתן להפעיל אותו עם שתי שורות בקובץ my.cnf, אך צריך לדעת איך לקרוא אותו—ומה לעשות עם מה שמוצאים. במשך 5+ שנים, ניתחנו מאות פרויקטים ומבטיחים האצת שאילתות עד פי 10. לדוגמה, בפרויקט אחד, SELECT עם JOIN לקח 12 שניות—לאחר הוספת אינדקס מכסה, הזמן ירד ל-0.03 שניות, שיפור של פי 400. הפחתה זו בזמן התגובה הורידה את עומס השרת וחסכה עלויות תשתית משמעותיות. לצוות שלנו יש 5+ שנות ניסיון באופטימיזציית MySQL והוא ערך ביקורת על למעלה מ-200 מסדי נתונים.

הגדרת Slow Query Log וניתוחו

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10
min_examined_row_limit = 1000

הלוג הגולמי מסורבל. לצורך צבירה, השתמשו ב-pt-query-digest מ-Percona Toolkit—הוא מקבץ שאילתות זהות ומציג סטטיסטיקות: מספר ביצועים, זמן כולל וממוצע, יחס של rows_examined ל-rows_sent. rows_examined גבוה עם rows_sent נמוך מצביע על אינדקס גרוע או חסר.

לניטור מקוון ללא לוג מבוסס קבצים, שאילתו את performance_schema.events_statements_summary_by_digest:

SELECT ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_sec,
       COUNT_STAR AS calls,
       ROUND(AVG_TIMER_WAIT/1e12, 4) AS avg_sec,
       ROUND(SUM_ROWS_EXAMINED/COUNT_STAR) AS avg_rows_examined,
       LEFT(DIGEST_TEXT, 80) AS query
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'mydb'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

איך לקרוא EXPLAIN: תוכנית ביצוע שאילתה

EXPLAIN FORMAT=JSON
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id;

my.cnf נותן תוכנית מלאה עם הערכות עלות. לקריאה מהירה, השתמשו ב-EXPLAIN הרגיל. מה לבדוק קודם: עמודת type — שיטת הגישה לטבלה. היררכיה מהטוב ביותר לגרוע ביותר:

ALL על טבלה גדולה באמצע JOIN הוא דגל אדום מיידי. אינדקס מכסה יעיל פי 100 מסריקה מלאה.

rows — מספר השורות המשוער ש-MySQL מצפה לבדוק. אם לתוכנית יש 3 טבלאות עם rows=500, 5000, 1000—פוטנציאלית 2.5 מיליארד צירופים בלולאה מקוננת ללא אינדקסים.

Extra — פעולות נוספות:

• Using filesort — מיון מחוץ לאינדקס, בזיכרון או בדיסק
• Using temporary — טבלה זמנית (GROUP BY, DISTINCT ללא אינדקס)
• Using index — אינדקס מכסה (טוב)
• Using index condition — דחיפת תנאי אינדקס (טוב)

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

יצירת אינדקסים לשאילתות ספציפיות

כאשר אנו רואים type=ALL על הזמנות עבור JOIN על user_id:

CREATE INDEX idx_orders_user_id ON orders(user_id); 

סדר העמודות באינדקסים מורכבים הוא קריטי. כלל הקידומת השמאלית: אינדקס (status, created_at) עובד עבור WHERE status = ?, עבור WHERE status = ? AND created_at > ?, אך לא עבור WHERE created_at > ? ללא status.

אינדקס מכסה—השאילתה מקבלת נתונים ישירות מהאינדקס ללא גישה לשורות הטבלה:

CREATE INDEX idx_orders_covering ON orders(user_id, status, total); 

EXPLAIN יציג Extra: Using index—נתונים מהאינדקס, ללא חיפוש בערימה.

דפוסי שאילתות איטיות נפוצים

LIKE עם תו כללי בתחילת התבנית. WHERE name LIKE '%pattern%' — אינדקס לא בשימוש, תמיד סריקה מלאה. פתרון: אינדקס FULLTEXT או Elasticsearch לחיפוש טקסט מלא.

פונקציה על עמודה עם אינדקס. WHERE YEAR(created_at) = 2021 — האינדקס על created_at מתעלם. כתבו מחדש כ-WHERE created_at >= '2021-01-01' AND created_at < '2022-01-01'.

OR על עמודות שונות. WHERE status = 'active' OR priority = 'high' — MySQL עשוי להשתמש במיזוג אינדקסים, אך לעיתים קרובות גרוע יותר משתי שאילתות נפרדות עם UNION ALL.

OFFSET גדול בעמודים. LIMIT 20 OFFSET 50000 — MySQL קורא 50020 שורות ומשליך 50000. פתרון: keyset pagination: WHERE id > :last_id ORDER BY id LIMIT 20.

איך להפוך ניטור שאילתות איטיות לאוטומטי

אנו ממליצים להגדיר איסוף לוג איטי דרך Prometheus exporter או להשתמש בתוסף ניתוח בזמן אמת. בניסיון שלנו, סקריפטים ב-Python מנתחים את performance_schema ושולחים התראות כאשר חריגות ספים (לדוגמה, avg_sec > 0.1). זה מאפשר להגיב לפני תלונות משתמשים.

תהליך האופטימיזציה ומה כלול

1. איסוף השאילתות האיטיות המובילות דרך pt-query-digest או performance_schema.
2. EXPLAIN לכל שאילתה, חיפוש type=ALL, Using filesort, Using temporary.
3. יצירת אינדקסים עם CREATE INDEX (לא חוסם ב-MySQL 5.6+ עבור InnoDB).
4. בדיקה חוזרת של תוכניות עם EXPLAIN ובדיקה על נתוני ייצור.
5. מתן דוח עם תוצאות והמלצות ניטור.

ציר זמן לאופטימיזציה נקודתית של 5-10 שאילתות: 1-2 ימים. ביקורת מערכת עם ניתוח כל השאילתות האיטיות, שיפוץ אינדקסים ואופטימיזציית סכמה: 2-3 ימים. העלות נקבעת לאחר ניתוח מקדים.

למה לבחור בנו?

עם 5+ שנות ניסיון, ערכנו ביקורת על למעלה מ-200 מסדי נתונים, והאצנו שאילתות קריטיות בממוצע פי 15. אנו משתמשים בכלים מוכחים: Percona Toolkit ותיעוד EXPLAIN הרשמי של MySQL.

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