שיפור חישוב השאילתות

במסמך הזה מפורטות השיטות המומלצות לאופטימיזציה של ביצועי השאילתות.

כשמריצים שאילתה, אפשר לצפות בתוכנית לביצוע השאילתה במסוף Cloud de Confiance . אפשר גם לבקש את פרטי ההרצה באמצעות התצוגות של INFORMATION_SCHEMA.JOBS* או ה-method של ה-API בארכיטקטורת REST‏ jobs.get.

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

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

צמצום כמות הנתונים שעוברים עיבוד

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

הימנעות מSELECT *

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

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

  • משתמשים באפשרויות של התצוגה המקדימה של הנתונים. אם אתם מתנסים עם נתונים או חוקרים נתונים, השתמשו באחת מאפשרויות התצוגה המקדימה של הנתונים במקום ב-SELECT *.
  • שאילתות על עמודות ספציפיות. הוספת סעיף LIMIT לשאילתת SELECT * לא משפיעה על כמות הנתונים שנקראים. תחויבו על קריאת כל הבייטים בטבלה כולה, והשאילתה תיספר במכסה של התוכנית בחינם. במקום זאת, כדאי לשלוח שאילתות רק לעמודות שאתם צריכים. לדוגמה, אפשר להשתמש ב-SELECT * EXCEPT כדי להחריג עמודה אחת או יותר מהתוצאות.
  • שימוש בטבלאות מחולקות למחיצות. אם אתם צריכים להריץ שאילתות על כל עמודה בטבלה, אבל רק על קבוצת משנה של נתונים, כדאי לשקול:

  • שימוש ב-SELECT * EXCEPT. שליחת שאילתות לקבוצת משנה של נתונים או שימוש ב-SELECT * EXCEPT יכולים להפחית באופן משמעותי את כמות הנתונים שנקראים על ידי שאילתה. בנוסף לחיסכון בעלויות, הביצועים משתפרים כי כמות הקלט/פלט של הנתונים וכמות החומריות שנדרשת לתוצאות השאילתה מצטמצמות.

    SELECT * EXCEPT (col1, col2, col5)
FROM mydataset.newtable

הימנעו משימוש מוגזם בטבלאות של תווים כלליים לחיפוש

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

אפשר להשתמש בתווים כלליים לחיפוש כדי להריץ שאילתות על כמה טבלאות באמצעות הצהרות SQL תמציתיות. טבלאות עם תווים כלליים לחיפוש הן איחוד של טבלאות שתואמות לביטוי של התו הכללי לחיפוש. טבלאות תווים כלליים לחיפוש שימושיות אם מערך הנתונים מכיל את המשאבים הבאים:

  • כמה טבלאות עם שמות דומים וסכימות תואמות
  • טבלאות מפוצלות למחיצות Shard

כששולחים שאילתה לטבלת תווים כלליים לחיפוש, צריך לציין תו כללי לחיפוש (*) אחרי הקידומת הנפוצה של הטבלה. לדוגמה, FROM bigquery-public-data.noaa_gsod.gsod194* שולחת שאילתה לכל הטבלאות משנות ה-40.

קידומות מפורטות יותר מניבות ביצועים טובים יותר מקידומות קצרות יותר. לדוגמה, הביצועים של FROM bigquery-public-data.noaa_gsod.gsod194* טובים יותר מאלה של FROM bigquery-public-data.noaa_gsod.* כי פחות טבלאות תואמות לתו הכללי לחיפוש.

הימנעו מטבלאות שמחולקות לפי תאריך

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

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

הימנעו מפיצול יתר של טבלאות

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

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

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

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

כמות הנתונים שנקראים על ידי שאילתה והמקור שלהם יכולים להשפיע על הביצועים והעלות של השאילתה.

צמצום שאילתות מחולקות למחיצות

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

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

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

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

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

צמצום הנתונים לפני השימוש בJOIN

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

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

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

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

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

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

שימוש בתנאי WHERE

השיטה המומלצת: השתמשו במשפט WHERE כדי להגביל את כמות הנתונים שהשאילתה מחזירה. כשזה אפשרי, השתמשו בעמודות BOOL,‏ INT64,‏ FLOAT64 או DATE בסעיף WHERE.

בדרך כלל, פעולות בעמודות BOOL, ‏ INT64, ‏ FLOAT64 ו-DATE מהירות יותר מפעולות בעמודות STRING או BYTE. כשאפשר, השתמשו בעמודה שכוללת אחד מסוגי הנתונים האלה בסעיף WHERE כדי להפחית את כמות הנתונים שמוחזרים על ידי השאילתה.

שימוש בתצוגות מהותיות

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

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

שימוש ב-BI Engine

שיטה מומלצת: השתמשו ב-BigQuery BI Engine כדי להאיץ שאילתות על ידי שמירה במטמון של הנתונים שבהם אתם משתמשים בתדירות הגבוהה ביותר.

כדאי להוסיף הזמנה של BI Engine לפרויקט שבו מחושבות השאילתות. ‫BigQuery BI Engine משתמש במנוע שאילתות וקטורי כדי לשפר את ביצועי השאילתות ב-SELECT.

שימוש באינדקס החיפוש

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

אינדקס חיפוש הוא מבנה נתונים שנועד לאפשר חיפוש יעיל מאוד באמצעות הפונקציה SEARCH, אבל הוא יכול גם להאיץ שאילתות באמצעות אופרטורים ופונקציות אחרים, כמו האופרטורים שווה (=), IN או LIKE ופונקציות מסוימות של מחרוזות ו-JSON.

אופטימיזציה של פעולות השאילתה

כדי לייעל את פעולות השאילתות, אתם יכולים להשתמש באפשרויות שמתוארות בקטעים הבאים.

הימנעות מטרנספורמציה חוזרת של נתונים

שיטה מומלצת: אם אתם משתמשים ב-SQL כדי לבצע פעולות ETL, הימנעו ממצבים שבהם אתם מבצעים טרנספורמציה של אותם נתונים שוב ושוב.

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

הימנעו מהערכות מרובות של אותם ביטויי CTE

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

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

אתם יכולים לאחסן את התוצאה של CTE במשתנה סקלרי או בטבלה זמנית, בהתאם לנתונים שה-CTE מחזיר.

הימנעו משאילתות משנה ומפעולות JOIN חוזרות

שיטה מומלצת: הימנעו מצירוף חוזר של אותן טבלאות ומשימוש חוזר באותן שאילתות משנה.

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

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

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

אופטימיזציה של דפוסי הצירוף

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

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

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

כדי לראות את גודל הטבלאות ב-JOIN, קראו את המאמר קבלת מידע על טבלאות.

ציון אילוצים של מפתח ראשי ומפתח זר

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

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

אופטימיזציה של הסעיף ORDER BY

שיטה מומלצת: כשמשתמשים בסעיף ORDER BY, חשוב לפעול לפי השיטות המומלצות הבאות:

  • משתמשים ב-ORDER BY בשאילתה החיצונית ביותר או בתוך סעיפי חלון. דוחפים פעולות מורכבות לסוף השאילתה. הוספת סעיף ORDER BY באמצע שאילתה משפיעה מאוד על הביצועים, אלא אם משתמשים בו בפונקציה אנליטית (window function).

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

  • משתמשים בסעיף LIMIT. אם אתם מסדרים מספר גדול מאוד של ערכים אבל לא צריכים שכולם יוחזרו, השתמשו בסעיף LIMIT. לדוגמה, השאילתה הבאה מסדרת קבוצת תוצאות גדולה מאוד ומחזירה שגיאת Resources exceeded. השאילתה ממיינת לפי העמודה title ב-mytable. העמודה title מכילה מיליוני ערכים.

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title;

    כדי להסיר את השגיאה, משתמשים בשאילתה כמו זו:

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title DESC
LIMIT
1000;

  • שימוש בפונקציה אנליטית (window function). אם אתם מסדרים מספר גדול מאוד של ערכים, השתמשו בפונקציה אנליטית (window function) והגבילו את הנתונים לפני הקריאה לפונקציה. לדוגמה, השאילתה הבאה מציגה את עשרת המשתמשים הוותיקים ביותר ב-Stack Overflow ואת הדירוג שלהם, כשהחשבון הוותיק ביותר מדורג במקום הנמוך ביותר:

    SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

    הרצת השאילתה הזו נמשכת כ-15 שניות. השאילתה הזו משתמשת ב-LIMIT בסוף השאילתה, אבל לא בפונקציה האנליטית (window function) DENSE_RANK() OVER. לכן, השאילתה דורשת מיון של כל הנתונים בצומת עובד אחד.

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

    WITH users AS (
SELECT
id,
reputation,
creation_date,
FROM bigquery-public-data.stackoverflow.users
ORDER BY creation_date ASC
LIMIT 10)
SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM users
ORDER BY user_rank;

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

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

פיצול שאילתות מורכבות לשאילתות קטנות יותר

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

שאילתות מורכבות, פונקציות REGEX ושאילתות משנה או צירופים בשכבות עלולים להיות איטיים ולצרוך הרבה משאבים. לפעמים, הניסיון להכניס את כל החישובים להצהרת SELECT אחת גדולה, למשל כדי להפוך אותה לתצוגה, הוא דפוס אנטי-תבניתי (antipattern) שעלול לגרום לשאילתה איטית שצורכת הרבה משאבים. במקרים קיצוניים, התוכנית הפנימית לביצוע שאילתה הופכת למורכבת כל כך, עד ש-BigQuery לא יכול לבצע אותה.

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

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

שימוש בשדות בתוך שדות ובשדות חוזרים

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

שימוש בסוגי נתונים של INT64 בשאילתות איחוד (join)

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

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

צמצום הפלט של השאילתה

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

התממשקות של קבוצות תוצאות גדולות

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

ב-BigQuery, המגבלה על תוצאות שנשמרות במטמון היא בערך 10GB של נתונים דחוסים. שאילתות שמחזירות תוצאות גדולות יותר חורגות מהמגבלה הזו, ולעיתים קרובות גורמות לשגיאה הבאה: Response too large.

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

כדי להתגבר על המגבלה על גודל התוצאות שנשמרות במטמון, אפשר להשתמש באפשרויות הבאות:

  • שימוש במסננים כדי להגביל את קבוצת התוצאות
  • כדאי להשתמש בסעיף LIMIT כדי לצמצם את קבוצת התוצאות, במיוחד אם משתמשים בסעיף ORDER BY.
  • כתיבת נתוני הפלט בטבלת יעד

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

הימנעות מתבניות אנטי-SQL

השיטות המומלצות הבאות מספקות הנחיות להימנעות מתבניות אנטי-תבניות של שאילתות שמשפיעות על הביצועים ב-BigQuery.

הימנעות מ-self-join

שיטה מומלצת: במקום להשתמש ב-self-join, השתמשו בפונקציית חלון (ניתוח) או באופרטור PIVOT.

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

הימנעות משאילתות איחוד (cross join)

שיטה מומלצת: הימנעו מצירופים שיוצרים יותר פלטים מאשר קלטים. כשנדרש CROSS JOIN, צריך לצבור מראש את הנתונים.

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

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

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

הימנעו מפקודות DML שמעדכנות או מוסיפות שורות בודדות

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

שימוש בהצהרות DML ספציפיות לנקודה הוא ניסיון להתייחס ל-BigQuery כמו למערכת עיבוד עסקאות אונליין (OLTP). ‫BigQuery מתמקד בעיבוד אנליטי אונליין (OLAP) באמצעות סריקות של טבלאות ולא חיפושים נקודתיים. אם אתם צריכים התנהגות שדומה ל-OLTP (עדכונים או הוספות של שורה אחת), כדאי לשקול מסד נתונים שנועד לתמוך בתרחישים לדוגמה של OLTP, כמו Cloud SQL.

פקודות DML ב-BigQuery מיועדות לעדכונים בכמות גדולה. פקודות DML מסוג UPDATE ו-DELETE ב-BigQuery מיועדות לשכתובים תקופתיים של הנתונים, ולא למוטציות של שורה אחת. השימוש בפקודת ה-DML‏ INSERT אמור להיות מוגבל. הוספות צורכות את אותן מכסות שינוי כמו משימות טעינה. אם אתם צריכים להוסיף שורות בודדות לעיתים קרובות, כדאי לשקול להעביר בסטרימינג את הנתונים.

אם תקבצו את הצהרות ה-UPDATE שלכם ותקבלו הרבה קבוצות של ערכים בשאילתות ארוכות מאוד, יכול להיות שתתקרבו למגבלת האורך של השאילתה (256KB). כדי לעקוף את מגבלת האורך של השאילתה, כדאי לבדוק אם אפשר לטפל בעדכונים על סמך קריטריונים לוגיים במקום סדרה של החלפות ישירות של קבוצות נתונים.

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

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

הימנעות מהתנגשויות בנעילת טרנזקציות

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

פקודות DML לשינוי בתוך טרנזקציה עם כמה פקודות מקבלות נעילה ברמת הטבלה, ויש להן מגבלת בו-זמניות של 1 לכל טבלה. לעומת זאת, ב-BigQuery אפשר להוסיף לתור באופן אוטומטי עד 20 פקודות DML עצמאיות לשינוי נתונים בכל טבלה. טרנזקציות עם כמה הצהרות שמתרחשות בו-זמנית ומנסות לשנות טבלה נעולה לא נכנסות לתור, ונכשלות מיד עם הודעת השגיאה: Transaction is aborted due to concurrent update against table.

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

  • אל תעטפו פקודת DML אחת בטרנזקציה: פקודת DML אחת, כמו פקודת MERGE בפרוצדורה מאוחסנת, היא כבר אטומית. אם מריצים את ההצהרה בלי ההצהרות BEGIN TRANSACTION ו-COMMIT TRANSACTION, אפשר להשתמש ב-BigQuery בתור DML.
  • צמצום משך הטרנזקציה בסקריפטים ובסשנים: כשמריצים פקודת DML לשינוי נתונים בתוך טרנזקציה בשאילתה עם כמה פקודות או בסשן פעיל, BigQuery נועל את טבלת היעד ברמת הטבלה. ‫BigQuery מחזיק את נעילת הכתיבה הזו עד שהטרנזקציה מתבצעת או מתבטלת, או עד שהסקריפט או הסשן הראשיים (כמו חיבור ODBC או JDBC) מסתיימים. כדי שהטבלה לא תישאר נעולה, כדאי להריץ חישובים ארוכים או שאילתות של אזור ביניים לפני שמתחילים את הטרנזקציה.
  • שימוש בהשהיה מעריכית לפני ניסיון חוזר (exponential backoff): כשטרנזקציה נכשלת בגלל עדכון בו-זמני, יכול להיות שיעברו כמה שניות עד ש-BigQuery ישחרר את נעילת הטרנזקציה בשכבת המטא-נתונים. כשמנסים שוב לבצע עסקאות שנכשלו, צריך להשתמש באסטרטגיית השהיה מעריכית לפני ניסיון חוזר (exponential backoff) ולהמתין לפחות 60 שניות לפני הניסיון החוזר הראשון. כדי לזהות סשנים או סקריפטים חופפים של הורה שנועלים טבלה, קראו את המאמר איתור משימות חופפות לטרנזקציה שנכשלה.

שימוש בשמות חלופיים לעמודות עם שמות דומים

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

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

המאמרים הבאים