שחרור SQL: 17 דרכים להאיץ את שאילתות ה- SQL שלך

מפתחי SQL בכל פלטפורמה מתקשים, לכאורה תקועים DO WHILEבלופ שגורם להם לחזור על אותן טעויות שוב ושוב. הסיבה לכך היא ששדה מסד הנתונים עדיין לא בוגר. בטח, ספקים עושים כמה צעדים, אבל הם ממשיכים להתמודד עם הנושאים הגדולים יותר. מקבילות, ניהול משאבים, ניהול שטח ומהירות עדיין מציקים למפתחי SQL בין אם הם מקודדים ב- SQL Server, Oracle, DB2, Sybase, MySQL או בכל פלטפורמה יחסית אחרת.

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

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

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

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

1. אל תשתמש UPDATEבמקוםCASE

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

קח למשל את התרחיש הזה: אתה מכניס נתונים לטבלת זמני ואתה זקוק להם כדי להציג ערך מסוים אם קיים ערך אחר. אולי אתה שולף משולחן הלקוח ואתה רוצה שמישהו עם יותר מ 100,000 $ הזמנות יתויג בתור "מועדף". לפיכך, אתה מכניס את הנתונים לטבלה ומריץ UPDATEהצהרה כדי להגדיר את העמודה CustomerRank ל"מועדפת "עבור כל מי שיש לו יותר מ 100,000 $ הזמנות. הבעיה היא UPDATEשההצהרה נרשמת, מה שאומר שהיא צריכה לכתוב פעמיים עבור כל כתיבה אחת לשולחן. הדרך לעקוף זאת, כמובן, היא להשתמש CASEבהצהרה מוטבעת בשאילתת SQL עצמה. זה בודק בכל שורה את מצב סכום ההזמנה וקובע את התווית "מועדף" לפני שהיא נכתבת לטבלה. עליית הביצועים יכולה להיות מדהימה.

2. אל תשתמש בקוד באופן עיוור

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

3. האם משוך רק את מספר העמודות הדרוש לך

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

4. לא לטבול פעמיים

הנה עוד אחת שראיתי פעמים רבות מכפי שהייתי צריכה: נוהל מאוחסן נכתב כדי לשלוף נתונים מטבלה עם מאות מיליוני שורות. היזם זקוק ללקוחות המתגוררים בקליפורניה ולהכנסות של יותר מ -40,000 דולר. אז הוא פונה ללקוחות שגרים בקליפורניה ומכניס את התוצאות לטבלת זמני; ואז הוא פונה ללקוחות עם הכנסות מעל 40,000 $ ומכניס את התוצאות לטבלת זמני אחרת. לבסוף, הוא מצטרף לשני השולחנות כדי להשיג את המוצר הסופי.

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

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

6. עשו נתונים לפני הבמה

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

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

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

7. האם למחוק ולעדכן בקבוצות

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

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

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

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

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

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

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

9. אל תקנן נופים

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

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

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

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