מפתחות זרים

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

סקירה כללית של מפתחות זרים ב-Spanner

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

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

המבנה הבסיסי של מסד הנתונים לעיבוד הזמנות.

איור 1. תרשים של מסד נתונים לעיבוד הזמנות

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

באיור 1 מוצגים גם קישורים בין הטבלאות, שממפים את הקשרים הבאים בעולם האמיתי:

  • לקוח מבצע הזמנה.

  • מבצעים הזמנה של מוצר.

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

  • אי אפשר ליצור הזמנה ללקוח שלא קיים.

  • לקוח לא יכול להזמין מוצר שאתם לא מוכרים.

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

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

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

סכימת מסד נתונים עם מפתחות זרים

איור 2. תרשים של סכמת מסד נתונים עם מפתחות זרים

העיצוב מציג עכשיו את שמות העמודות והסוגים בכל טבלה. בOrders הטבלה מוגדרים גם שני קשרי גומלין של מפתח זר. הפרמטר FK_CustomerOrder מצפה שכל השורות ב-Orders יכללו ערך תקין של CustomerId. המפתח הזר FK_ProductOrder מצפה שכל הערכים של ProductId בטבלה Orders יהיו תקינים. בטבלה הבאה מפורטות המגבלות האלה בהתאם לכללים בעולם האמיתי שרוצים לאכוף.

שם המפתח הזר מגבלה תיאור מהעולם האמיתי
FK_CustomerOrder הפונקציה מצפה שכל השורות ב-Orders יכללו ערך תקין של CustomerId לקוח תקין מבצע הזמנה
FK_ProductOrder הפונקציה מצפה שכל השורות ב-Orders יכללו ערך תקין של ProductId הוזמן מוצר תקין

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

בניגוד למפתחות זרים עם אכיפה, Spanner לא מאמת אילוצים על מפתחות זרים אינפורמטיביים. המשמעות היא שאם משתמשים במפתח זר אינפורמטיבי בתרחיש הזה, טרנזקציה שמנסה להוסיף או לעדכן שורה בטבלה Orders עם CustomerId או ProductId שלא נמצאים בטבלאות Customers ו-Products לא מאומתת והטרנזקציה לא נכשלת. בנוסף, בניגוד למפתחות זרים שנאכפים, מפתחות זרים אינפורמטיביים נתמכים רק על ידי GoogleSQL ולא על ידי PostgreSQL.

מאפיינים של מפתח זר

בהמשך מפורטים מאפיינים של מפתחות זרים ב-Spanner.

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

  • המפתח הזר מפנה לעמודות המופנות של הטבלה המופנית.

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

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

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

  • אי אפשר ליצור מפתחות זרים בעמודות עם האפשרות allow_commit_timestamp=true.

  • אין תמיכה בעמודות מסוג מערך.

  • אין תמיכה בעמודות JSON.

  • מפתח זר יכול להפנות לעמודות באותה טבלה (מפתח זר עם הפניה עצמית). דוגמה: טבלה Employee עם עמודה ManagerId שמפנה לעמודה EmployeeId בטבלה.

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

  • המפתחות שאליהם יש הפניה צריכים להיות ייחודיים. ‫Spanner משתמש ב-PRIMARY KEY של הטבלה המופנית אם העמודות המופנות של מפתח זר תואמות לעמודות של המפתח הראשי של הטבלה המופנית. אם Spanner לא יכול להשתמש במפתח הראשי של הטבלה שאליה מתייחסים, הוא יוצר UNIQUE NULL_FILTERED INDEX בעמודות שאליהן מתייחסים.

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

סוגים של מפתחות זרים

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

מפתחות זרים נאכפים

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

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

  • מחיקת שורה מטבלה שהיא טבלת המקור, שאליה מפנות שורות בטבלה המפנה, נכשלת.

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

מפתחות זרים שמכילים מידע

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

משתמשים במילת המפתח NOT ENFORCED כדי לציין שמפתח זר ב-GoogleSQL הוא לצורכי מידע. ‫PostgreSQL לא תומך במפתחות זרים אינפורמטיביים.

השוואה בין סוגים של מפתחות זרים

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

הבדלים ברמת מפתח זר

באופן כללי, אלה כמה מההבדלים בין מפתחות זרים עם אכיפה לבין מפתחות זרים למידע בלבד:

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

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

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

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

טבלת ההבדלים בין מפתחות זרים

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

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

בחירת סוג המפתח הזר שבו רוצים להשתמש

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

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

מומלץ להשתמש במפתחות זרים אינפורמטיביים אם כל התנאים הבאים מתקיימים:

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

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

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

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

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

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

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

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

אתם יוצרים ומסירים מפתחות זרים אינפורמטיביים ממסד הנתונים של Spanner באמצעות הצהרות DDL. מוסיפים מפתחות זרים לטבלה חדשה באמצעות ההצהרה CREATE TABLE. באופן דומה, אפשר להוסיף או להסיר מפתחות זרים מטבלה קיימת באמצעות ההצהרה ALTER TABLE.

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

GoogleSQL 

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE Orders (
  OrderId INT64 NOT NULL,
  CustomerId INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  ProductId INT64 NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
   REFERENCES Customers (CustomerId) NOT ENFORCED
 ) PRIMARY KEY (OrderId);

PostgreSQL 

Not Supported

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

שימוש במפתחות זרים אינפורמטיביים לאופטימיזציה של שאילתות

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

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

כברירת מחדל, אופטימיזציית השאילתות משתמשת בNOT ENFORCED מפתחות זרים. כדי לשנות את זה, צריך להגדיר את האפשרות use_unenforced_foreign_key_for_query_optimization במסד הנתונים לערך false. הדוגמה הבאה של GoogleSQL ממחישה את זה (מפתחות זרים אינפורמטיביים לא זמינים ב-PostgreSQL):

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

ההערה @{use_unenforced_foreign_key} של הצהרת השאילתה הבוליאנית מבטלת את האפשרות של מסד הנתונים על בסיס כל שאילתה, שקובעת אם האופטימיזציה משתמשת במפתחות זרים של NOT ENFORCED. השבתה של הרמז הזה או של אפשרות מסד הנתונים יכולה להיות שימושית כשמנסים לפתור בעיות שקשורות לתוצאות לא צפויות של שאילתות. בדוגמה הבאה מוסבר איך להשתמש ב-@{use_unenforced_foreign_key}:

@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
    FROM Orders
    INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;

שימוש במפתחות זרים שנאכפים

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

יצירת טבלה חדשה עם מפתח זר מחייב

אפשר ליצור ולהסיר מפתחות זרים מונפקים ממסד הנתונים של Spanner באמצעות DDL. מוסיפים מפתחות זרים לטבלה חדשה באמצעות ההצהרה CREATE TABLE. באופן דומה, אפשר להוסיף מפתח זר לטבלה קיימת או להסיר ממנה מפתח זר באמצעות ההצהרה ALTER TABLE.

אתם יוצרים ומסירים מפתחות זרים ממסד הנתונים שלכם ב-Spanner באמצעות DDL. מוסיפים מפתחות זרים לטבלה חדשה באמצעות ההצהרה CREATE TABLE. באופן דומה, אפשר להוסיף מפתח זר לטבלה קיימת או להסיר ממנה מפתח זר באמצעות הצהרת ALTER TABLE.

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

GoogleSQL 

CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE Orders (
OrderId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductId INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
  REFERENCES Customers (CustomerId) ENFORCED
) PRIMARY KEY (OrderId);

PostgreSQL 

CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);

CREATE TABLE Orders (
OrderId BIGINT NOT NULL,
CustomerId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
  REFERENCES Customers (CustomerId),
PRIMARY KEY (OrderId)
);

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

פעולות של מפתח זר

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

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

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

GoogleSQL 

CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL 

CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

בהמשך מפורטים מאפיינים של פעולות במפתח זר ב-Spanner.

  • הפעולות של המפתח הזר הן ON DELETE CASCADE או ON DELETE NO ACTION.

  • אפשר לשלוח שאילתה אל INFORMATION_SCHEMA כדי למצוא אילוצים של מפתח זר שיש להם פעולה.

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

אימות מגבלות

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

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

ערך שמוזן בעמודה המפנה מושווה לערכים בטבלה המופנית ובעמודות המופנות. השורות עם NULL שמפנות לערכים <0x0A>לא נבדקות, מה שאומר שאפשר להוסיף אותן לטבלת ההפניות.

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

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

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

פעולת מחיקה ממושכת של נתונים קשורים

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

חריגה ממגבלת השינויים (Mutation) למחיקה מדורגת של מפתח זר

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

השוואה בין אילוץ של מפתחות זרים לבין שילוב טבלאות

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

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

נניח שיש לכם טבלה Orders שמוגדרת כך:

סכימת מסד נתונים עם מפתחות זרים

איור 3. תרשים של סכימת מסד הנתונים עם מפתחות זרים שנאכפים

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

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

כך מגדירים את הטבלה OrderItems, שמשולבת עם Orders.

GoogleSQL 

CREATE TABLE Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);

CREATE TABLE OrderItems (
OrderId INT64 NOT NULL,
ProductId INT64 NOT NULL,
Quantity INT64 NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId)
) PRIMARY KEY (OrderId, ProductId),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

PostgreSQL 

CREATE TABLE Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);

CREATE TABLE OrderItems (
OrderId BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId),
PRIMARY KEY (OrderId, ProductId)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

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

סכימת מסד נתונים שמציגה קשר של אחד לרבים בין טבלת Orders לבין טבלת OrderItems החדשה, שמשולבת בטבלה הקודמת

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

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

אם מספר השינויים בעסקה חורג מ-80,000, העסקה נכשלת. מחיקות גדולות כאלה של נתונים שקשורים זה לזה מתאימות לטבלאות עם קשר מסוג interleaved in parent, אבל לא לטבלאות עם קשר של מפתח זר. אם יש לכם קשר של מפתח זר ואתם צריכים למחוק מספר גדול של שורות, כדאי למחוק את השורות מטבלאות הצאצא קודם.

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

טבלת השוואה

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

סוג הקשר הורה-צאצא שילוב טבלאות מפתחות זרים נאכפים
אפשר להשתמש במקשים הראשיים כן כן
אפשר להשתמש בעמודות שהן לא עמודות של מפתח ראשי לא כן
מספר ההורים הנתמכים ‫0 .. 1 ‫0 .. N
אחסון נתונים של נכסים ראשיים ומשניים יחד כן לא
תמיכה במחיקה מדורגת כן כן
מצב התאמה של Null הבדיקה עוברת אם כל הערכים שמפנים לערכים אחרים לא שונים מהערכים שאליהם הם מפנים.
ערכים מסוג Null לא שונים מערכים מסוג Null, אבל הם שונים מערכים שאינם מסוג Null.		 הבדיקה עוברת אם יש ערכים ריקים בהפניות.
הבדיקה עוברת אם כל הערכים בהפניות לא ריקים, ולטבלה שאליה מתבצעת ההפניה יש שורה עם ערכים ששווים לערכים בהפניות.
הבדיקה נכשלת אם לא נמצאה שורה תואמת.
תזמון האכיפה לכל פעולה כשמשתמשים ב-API של מוטציות.
לכל הצהרה כשמשתמשים ב-DML.		 לכל עסקה כשמשתמשים ב-Mutation API.
לכל הצהרה כשמשתמשים ב-DML.
ניתן להסרה לא. אי אפשר להסיר שילוב של טבלאות אחרי שהוא נוצר, אלא אם מוחקים את כל טבלת הצאצא. כן

אינדקסים לגיבוי

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

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

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

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

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

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

ב-Spanner נעשה שימוש בסכימת המידע של כל מסד נתונים כדי לאחסן מטא-נתונים על אינדקסים תומכים. שורות ב-INFORMATION_SCHEMA.INDEXES עם ערך SPANNER_IS_MANAGED של true מתארות אינדקסים תומכים.

בנוסף לשאילתות SQL שמפעילות ישירות את סכימת המידע,Google Cloud המסוף לא מציג מידע על אינדקסים של גיבוי מסד נתונים.

שינויים ממושכים בסכימה

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

בטבלה הבאה מוצג מה קורה ב-Spanner כשמגדירים מפתח זר חדש או קיים בטבלה חדשה או קיימת:

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

אין תמיכה בתכונות הבאות:

  • הוספת פעולה של מפתח זר לאילוץ קיים של מפתח זר שנאכף.
  • שינוי האכיפה של מפתח זר קיים.

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

  1. מוסיפים אילוץ חדש עם הפעולה או האכיפה הנדרשות.
  2. משמיטים את האילוץ הישן.

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

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

אפשר לשלוח שאילתה אל INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE כדי לבדוק את מצב היצירה של המפתח הזר.

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