הימנע משימוש ב-OFFSET וב-LIMIT בשאילתות מעומדות

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

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

SELECT * FROM table_name LIMIT 10 OFFSET 40

ככה זה?

אבל אם כך עשית את העימוד שלך, אני מצטער לומר שלא עשית זאת בצורה היעילה ביותר.

אתה רוצה להתנגד לי? אתה יכול לא לבלות זמן. רפוי, Shopify и מיקסמקס הם כבר משתמשים בטכניקות שאני רוצה לדבר עליהן היום.

תן שם לפחות מפתח אחורי אחד שמעולם לא השתמש OFFSET и LIMIT כדי לבצע שאילתות מעומדות. ב-MVP (Minimum Viable Product) ובפרויקטים שבהם נעשה שימוש בכמויות קטנות של נתונים, גישה זו ישימה למדי. זה "פשוט עובד", כביכול.

אך אם אתם צריכים ליצור מערכות אמינות ויעילות מאפס, כדאי לדאוג מראש ליעילות השאילתה למאגרי המידע המשמשים במערכות כאלו.

היום נדבר על הבעיות בהטמעות נפוצות (חבל) של מנועי שאילתות מעומדים, וכיצד להשיג ביצועים גבוהים בעת ביצוע שאילתות כאלה.

מה רע ב-OFFSET ו-LIMIT?

כאמור, OFFSET и LIMIT הם מתפקדים היטב בפרויקטים שאינם צריכים לעבוד עם כמויות גדולות של נתונים.

הבעיה מתעוררת כאשר מסד הנתונים גדל לגודל כזה שהוא כבר לא נכנס לזיכרון של השרת. עם זאת, בעת עבודה עם מסד נתונים זה, עליך להשתמש בשאילתות מעומדות.

כדי שבעיה זו תתבטא, חייב להיות מצב שבו ה-DBMS יפנה לפעולה לא יעילה של סריקת טבלה מלאה בכל שאילתה מעומדת (בעוד שפעולות הכנסה ומחיקה עשויות להתרחש, ואיננו זקוקים לנתונים מיושנים!).

מהי "סריקת טבלה מלאה" (או "סריקת טבלה רציפה", סריקה רציפה)? זוהי פעולה שבמהלכה ה-DBMS קורא ברצף כל שורה בטבלה, כלומר את הנתונים הכלולים בה, ובודק את תאימותם לתנאי נתון. סוג זה של סריקת טבלה ידוע כאיטי ביותר. העובדה היא שכאשר הוא מבוצע, מבוצעות פעולות קלט/פלט רבות הכוללות את תת-מערכת הדיסק של השרת. המצב מחמיר בגלל השהיה הקשורה לעבודה עם נתונים המאוחסנים בדיסקים, והעובדה שהעברת נתונים מדיסק לזיכרון היא פעולה עתירת משאבים.

לדוגמה, יש לך רשומות של 100000000 משתמשים ואתה מפעיל שאילתה עם המבנה OFFSET 50000000. זה אומר שה-DBMS יצטרך לטעון את כל הרשומות האלה (ואנחנו אפילו לא צריכים אותן!), לשים אותן בזיכרון, ואחרי זה לקחת, למשל, 20 תוצאות שדווחו ב- LIMIT.

נניח שזה עשוי להיראות כך: "בחר שורות מ-50000 עד 50020 מ-100000". כלומר, המערכת תצטרך קודם כל לטעון 50000 שורות כדי להשלים את השאילתה. אתה רואה כמה עבודה מיותרת היא תצטרך לעשות?

אם אתה לא מאמין לי, תסתכל על הדוגמה שיצרתי באמצעות התכונות db-fiddle.com. 

דוגמה ב-db-fiddle.com

שם, משמאל, בשטח Schema SQL, יש קוד שמכניס 100000 שורות למסד הנתונים, ומימין, בשדה Query SQL, מוצגות שתי שאילתות. הראשון, האיטי, נראה כך:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

והשני, שהוא פתרון יעיל לאותה בעיה, הוא כזה:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

על מנת למלא את הבקשות הללו, פשוט לחץ על הכפתור Run בתחילת הדף. לאחר שעשינו זאת, אנו משווים מידע על זמן ביצוע השאילתה. מסתבר שביצוע שאילתה לא אפקטיבית לוקח לפחות פי 30 יותר מביצוע השאילתה השנייה (זמן זה משתנה מהפעלה להרצה; לדוגמה, המערכת עשויה לדווח שהשאילתה הראשונה לקחה 37 אלפיות השנייה כדי להשלים, אך ביצוע השאילתה שנייה - 1 אלפיות השנייה).

ואם יש יותר נתונים, אז הכל ייראה אפילו יותר גרוע (כדי להשתכנע בזה, תסתכל על שלי דוגמה עם 10 מיליון שורות).

מה שדיברנו זה עתה אמור לתת לך קצת תובנות לגבי האופן שבו שאילתות מסד נתונים מעובדות בפועל.

שימו לב שככל שהערך גבוה יותר OFFSET - ככל שהבקשה ייקח זמן רב יותר להשלמה.

במה עלי להשתמש במקום השילוב של OFFSET ו-LIMIT?

במקום שילוב OFFSET и LIMIT כדאי להשתמש במבנה שנבנה לפי הסכמה הבאה:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

זהו ביצוע שאילתה עם עימוד מבוסס סמן.

במקום לאחסן את הנוכחים באופן מקומי OFFSET и LIMIT ולשדר אותם עם כל בקשה, עליך לאחסן את המפתח הראשי האחרון שהתקבל (בדרך כלל זהו ID) ו LIMIT, כתוצאה מכך, יתקבלו שאילתות דומות לאמור לעיל.

למה? הנקודה היא שעל ידי ציון מפורש של המזהה של השורה האחרונה שנקראה, אתה אומר ל-DBMS שלך היכן הוא צריך להתחיל לחפש את הנתונים הדרושים. יתר על כן, החיפוש, הודות לשימוש במפתח, יתבצע ביעילות; המערכת לא תצטרך להיות מוסחת על ידי קווים מחוץ לטווח שצוין.

בואו נסתכל על השוואת הביצועים הבאה של שאילתות שונות. הנה שאילתה לא יעילה.

בקשה איטית

והנה גרסה אופטימלית של הבקשה הזו.

בקשה מהירה

שתי השאילתות מחזירות בדיוק את אותה כמות נתונים. אבל לראשון לוקח 12,80 שניות להשלים, ולשני לוקח 0,01 שניות. האם אתה מרגיש את ההבדל?

בעיות אפשריות

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

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

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

אם אתה מעוניין בנושא זה - כאן, כאן и כאן - מספר חומרים שימושיים.

תוצאות של

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

איך מנתחים ומבצעים אופטימיזציה של שאילתות מסד נתונים?

מקור: www.habr.com