שימוש במקשים ראשיים ובמקשים זרים

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

‫BigQuery תומך באילוצים הבאים של מפתחות:

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

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

ניהול אילוצים

אפשר ליצור ולנהל קשרים של מפתח ראשי ומפתח זר באמצעות הצהרות DDL הבאות:

יוצרים אילוצים של מפתח ראשי ומפתח זר כשיוצרים טבלה באמצעות הפקודה CREATE TABLE.
כדי להוסיף אילוץ של מפתח ראשי לטבלה קיימת, משתמשים בהצהרת ALTER TABLE ADD PRIMARY KEY.
כדי להוסיף אילוץ של מפתח זר לטבלה קיימת, משתמשים בפקודה ALTER TABLE ADD FOREIGN KEY.
כדי להסיר אילוץ של מפתח ראשי מטבלה, משתמשים בהצהרת ALTER TABLE DROP PRIMARY KEY.
כדי להסיר אילוץ של מפתח זר מטבלה, משתמשים בהצהרת ALTER TABLE DROP CONSTRAINT.

אפשר גם לנהל אילוצים של טבלאות דרך BigQuery API על ידי עדכון האובייקט TableConstraints.

צפייה באילוצים

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

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

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

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

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

בדוגמאות שבקטעים הבאים יש הפניה לטבלאות store_sales ו-customer עם אילוצים:

CREATE TABLE mydataset.customer (customer_name STRING PRIMARY KEY NOT ENFORCED);

CREATE TABLE mydataset.store_sales (
    item STRING PRIMARY KEY NOT ENFORCED,
    sales_customer STRING REFERENCES mydataset.customer(customer_name) NOT ENFORCED,
    category STRING);

ביטול של שאילתות איחוד פנימיות

נניח שיש לכם את השאילתה הבאה שכוללת INNER JOIN:

SELECT ss.*
FROM mydataset.store_sales AS ss
    INNER JOIN mydataset.customer AS c
    ON ss.sales_customer = c.customer_name;

העמודה customer_name היא מפתח ראשי בטבלה customer, ולכן לכל שורה בטבלה store_sales יש התאמה אחת או ללא התאמה אם sales_customer הוא NULL. מכיוון שהשאילתה בוחרת רק עמודות מהטבלה store_sales, אופטימיזציית השאילתה יכולה לבטל את הצירוף ולשכתב את השאילתה באופן הבא:

SELECT *
FROM mydataset.store_sales
WHERE sales_customer IS NOT NULL;

ביטול שאילתות איחוד חיצוניות

כדי להסיר LEFT OUTER JOIN, מפתחות הצירוף בצד שמאל צריכים להיות ייחודיים, וצריך לבחור רק עמודות מהצד הימני. נניח שיש לכם את השאילתה הבאה:

SELECT ss.*
FROM mydataset.store_sales ss
    LEFT OUTER JOIN mydataset.customer c
    ON ss.category = c.customer_name;

בדוגמה הזו, אין קשר בין category לבין customer_name. העמודות שנבחרו מגיעות רק מהטבלה store_sales ומפתח הצירוף customer_name הוא מפתח ראשי בטבלה customer, כך שכל ערך הוא ייחודי. כלומר, לכל שורה בטבלה store_sales יש התאמה אחת בדיוק (יכול להיות NULL) בטבלה customer, ואפשר לבטל את LEFT OUTER JOIN:

SELECT ss.*
FROM mydataset.store_sales;

שינוי הסדר של שאילתות איחוד

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

מגבלות

המגבלות הבאות חלות על מפתחות ראשיים ומפתחות זרים:

אילוצים של מפתח לא נאכפים ב-BigQuery. באחריותכם לשמור על האילוצים האלה בכל שלב. שאילתות על טבלאות עם אילוצים שהופרו עשויות להחזיר תוצאות שגויות.
מפתחות ראשיים לא יכולים לכלול יותר מ-16 עמודות.
למפתחות זרים צריכים להיות ערכים שקיימים בעמודה של הטבלה שאליה מתבצעת ההפניה. הערכים האלה יכולים להיות NULL.
מפתחות ראשיים ומפתחות זרים חייבים להיות מאחד מהסוגים הבאים: BIGNUMERIC, ‏BOOLEAN, ‏DATE, ‏DATETIME, ‏INT64, ‏NUMERIC, ‏STRING או TIMESTAMP.
אפשר להגדיר מפתחות ראשיים ומפתחות זרים רק בעמודות ברמה העליונה.
אי אפשר לתת שמות למפתחות ראשיים.
אי אפשר לשנות את השם של טבלאות עם אילוצים של מפתח ראשי.
בטבלה יכולים להיות עד 64 מפתחות זרים.
מפתח זר לא יכול להתייחס לעמודה באותה טבלה.
אי אפשר לשנות את השם או את הסוג של שדות שמהווים חלק מאילוצים של מפתח ראשי או של מפתח זר.
אם מעתיקים, משכפלים, משחזרים או מצלמים טבלה בלי האפשרות -a או --append_table, האילוצים של טבלת המקור מועתקים ומוחלפים בטבלת היעד. אם משתמשים באפשרות -a או --append_table, רק הרשומות של טבלת המקור מתווספות לטבלת היעד ללא אילוצי הטבלה.

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

מידע נוסף על שיפור חישוב השאילתות