אם האפליקציה שלך מרגישה איטית ואתה כבר שיפרת את התמונות, הפעלת caching וכיוונת את השרת, יש סיכוי טוב שהבעיה האמיתית מסתתרת בשקט בבסיס הנתונים שלך. ובמדויק, באינדקסים, או בהיעדרם.
הוספת אינדקס נכון לעמודה הנכונה היא אחד מאותם תיקונים נדירים שיכולים להוריד שאילתה מ-800 מילישניות ל-8 מילישניות. זו לא הגזמה. זה קורה כל הזמן, וכשתבינו למה, תתחילו לזהות הזדמנויות כאלה בכל מקום.
מה אינדקס עושה בעצם
תארו לעצמכם טבלה בבסיס נתונים בלי אינדקס, כמו ספר טלפונים בלי סדר אלפביתי. כדי למצוא "כהן", הייתם צריכים לקרוא כל רשומה מהעמוד הראשון ועד האחרון. זה נקרא סקירה מלאה של הטבלה (full table scan), וזה בדיוק מה שבסיס הנתונים עושה כשהוא לא יכול להשתמש באינדקס.
אינדקס הוא מבנה נתונים נפרד, בדרך כלל עץ B, ששומר הפניה ממוינת לערכים בעמודה (או קבוצת עמודות) לצד מצביעים למקום שבו השורות בפועל נמצאות. במקום לסקור מיליון שורות, בסיס הנתונים "מדלג" ישר לאלה שהוא צריך.
הנה ההבדל בפועל. נניח שיש לכם טבלת `users` עם מיליון שורות ואתם מריצים:
SELECT * FROM users WHERE email = 'jane@example.com';
בלי אינדקס על `email`, בסיס הנתונים בודק כל שורה. עם אינדקס, הוא מבצע חיפוש שקרוב יותר ל-O(log n) במקום O(n). בטבלה עם מיליון שורות, זה ההבדל בין לבדוק מיליון שורות לבין לבדוק בערך 20.
איך מוצאים את העמודות שבאמת צריכות אינדקס
לא כל עמודה צריכה אינדקס. אינדקס לכל דבר בעצם מאט פעולות כתיבה, כי כל הוספה או עדכון צריך לעדכן גם כל אינדקס בטבלה. המטרה היא ליצור אינדקסים בצורה מושכלת, כחלק מתהליך אופטימיזציית בסיס נתונים נכון.
תבדקו את סעיפי WHERE, JOIN ו-ORDER BY
אלה שלושה המקומות שבהם אינדקסים חשובים ביותר:
- עמודות שמשמשות בפילטרים של WHERE, בעיקר בטבלאות גדולות
- עמודות שמשמשות לחיבור טבלאות עם JOIN, כמו מפתחות זרים
- עמודות שמשמשות ב-ORDER BY או GROUP BY, כי מיון נתונים שכבר ממוינים מראש הרבה יותר זול
תשתמשו ב-EXPLAIN לפני שאתם מנחשים
אל תוסיפו אינדקסים בלי לבדוק. הריצו EXPLAIN (או EXPLAIN ANALYZE ב-PostgreSQL) על השאילתות האיטיות שלכם קודם. ב-MySQL, תוכנית שאילתה שמציגה type: ALL אומרת שאתם רואים סקירה מלאה של הטבלה. זה הסימן שלכם.
לדוגמה:
EXPLAIN SELECT * FROM orders WHERE customer_id = 4821 AND status = 'pending';
אם זה מחזיר סקירה מלאה על טבלת `orders` עם 500,000 שורות, זה מועמד חזק לאינדקס מורכב על `(customer_id, status)`.
אינדקסים מורכבים וסדר העמודות חשוב
טעות נפוצה היא ליצור אינדקסים לעמודה בודדת כשאינדקס מורכב היה משרת את השאילתה הרבה יותר טוב. אם אתם מסננים לעיתים קרובות לפי שתי עמודות יחד, תיצרו אינדקס משותף לשתיהן, בסדר שבו הן משמשות.
סדר העמודות באינדקס מורכב אינו שרירותי. אינדקס על `(customer_id, status)` עוזר לשאילתות שמסננות רק לפי `customer_id` או לפי שתי העמודות יחד. הוא לא יעזור לשאילתה שמסננת רק לפי `status`. תחשבו על זה כמו ספר טלפונים ממוין לפי שם משפחה ואז שם פרטי. אפשר למצוא בקלות את "כהן", ואת "כהן, יוסי" אפילו מהר יותר, אבל לא ניתן למצוא בקלות את כל מי שנקרא "יוסי" בכל שמות המשפחה.
דוגמה אמיתית: הצניחה של 90 אחוז
הנה תבנית שאנחנו רואים כל הזמן. לאתר מסחר אלקטרוני יש טבלת `orders` עם 2 מיליון שורות. שאילתת דשבורד מסננת הזמנות אחרונות לפי מזהה חנות וטווח תאריכים:
SELECT * FROM orders WHERE store_id = 55 AND created_at > '2024-01-01' ORDER BY created_at DESC;
בלי אינדקס, השאילתה הזו לוקחת בערך 1.2 שניות בעומס. היא סוקרת את כל הטבלה, מסננת בזיכרון, ואז ממיינת. אחרי הוספת אינדקס מורכב על `(store_id, created_at)`, אותה שאילתה יורדת לבערך 90 מילישניות. זה ירידה של 92 אחוז, ונדרשה בשביל זה שורת SQL אחת בלבד:
CREATE INDEX idx_store_created ON orders (store_id, created_at);
בלי שינויי קוד, בלי חומרה חדשה, בלי שכבת caching. רק האינדקס הנכון על העמודות הנכונות.
שימו לב לחסרונות
אינדקסים לא ניתנים בחינם. כל אחד מהם מוסיף עומס על פעולות כתיבה וצורך שטח דיסק. טבלה עם עשרה אינדקסים תכתוב ותעדכן הרבה יותר לאט מטבלה עם שני אינדקסים שנבחרו בקפידה. כמה כללי אצבע מעשיים:
- אל תיצרו אינדקס לעמודות עם קרדינליות נמוכה כמו דגל בוליאני `is_active` בפני עצמו, כי בסיס הנתונים לרוב לא יכול לצמצם הרבה את התוצאות
- תבדקו מדי פעם אינדקסים שלא בשימוש. רוב בסיסי הנתונים יכולים לדווח אילו אינדקסים לא נגעו בהם שבועות
- הימנעו מאינדקס לעמודות שמשתנות כל הזמן אם היתרון בקריאה לא שווה את עלות הכתיבה
איפה אחסון נכנס לתמונה
אינדקס נכון מביא אתכם לרוב הדרך, אבל השרת שמתחת עדיין חשוב. קלט/פלט דיסק איטי, עומס על הזיכרון, או בסיס נתונים שמתחרה עם דיירים אחרים על משאבים בשרת שיתופי עמוס יכול לבטל הרבה מהעבודה שהשקעתם באינדקסים. על שרת VPS עם משאבים נכונים, חיפושי אינדקס נהנים באמת מקלט/פלט מהיר וייעודי במקום להיתקע בתור מאחורי תעבורה של מישהו אחר.
גם כדאי לעקוב אחרי ביצועי השאילתות שלכם עם הזמן, כי הנתונים גדלים ודפוסי השאילתות משתנים. מעקב שרתים שעוקב אחר העומס על בסיס הנתונים לצד CPU וזיכרון יכול לתפוס שאילתה שמתדרדרת בשקט לפני שהיא הופכת לבעיה של ממש.
כתבנו יותר על התמונה הרחבה של שאילתות איטיות בלמה שאילתות איטיות הן הצוואר בקבוק הנסתר ברוב אפליקציות הווב, ואם אתם מריצים WordPress בפרט, ניקוי בסיס נתונים ל-WordPress מכסה זווית קשורה שכדאי לקרוא.
מה לזכור
אחסון עם אופטימיזציית בסיס נתונים לא עוסק רק בבחירת שרת מהיר. זה עניין של שילוב תשתית טובה עם החלטות חכמות בסכמה, ואינדקס הוא הצעד עם ההשפעה הגדולה ביותר שאפשר לעשות. לפני שאתם פונים לשרת גדול יותר או לשכבת caching חדשה, הריצו EXPLAIN על השאילתות האיטיות ביותר שלכם ובדקו אם אינדקס בודד ומדויק יכול לעשות את העבודה במקום. לרוב, זה יעבוד יותר ממה שחשבתם.
