מפתחות זרים

במאמר הזה מוסבר על מפתחות זרים ב-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 נאכף הוא אופציונלי.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    • מקור הנתונים במעלה הזרם פועל לפי מודל של עקביות בסופו של דבר. במקרה כזה, יכול להיות שהעדכונים שבוצעו במערכת המקור לא יופיעו מיד ב-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 SET NULL.

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

כשמשתמשים בפעולה ON DELETE SET NULL, כשמוחקים שורה שמפנה למפתח זר, העמודות שמפנות בכל השורות שמפנות למפתח הזה מוגדרות כ-NULL באותה עסקה.

אתם יכולים להוסיף מפתח זר עם פעולה כשאתם יוצרים את מסד הנתונים באמצעות 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 SET NULL או ON DELETE NO ACTION.

  • אי אפשר להשתמש ב-ON DELETE SET NULL בעמודות שמפנות לחלק מהמפתח הראשי של הטבלה המפנה, שמסומן ב-NOT NULL, או בעמודות שנוצרו.

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

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

אימות אילוצים

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

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

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

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

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

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

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

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

פעולת מחיקה ממושכת של הגדרת ערך null

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

חריגה ממגבלת המוטציות במחיקה מדורגת של מפתח זר

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

חרגת ממגבלת המוטציות למחיקת מפתח זר והגדרת ערך null

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

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

ל-ON DELETE CASCADE ול-ON DELETE SET NULL יש עלויות שונות של משאבים ומוטציות, בהתאם לסכימה:

  • מוטציות של מסד נתונים טבלאי: ON DELETE SET NULL מעדכן עמודות ספציפיות, כך שכל שורה מעודכנת שמפנה נספרת כמוטציות מרובות (למפתח השורה ולכל עמודה שמוגדרת ל-NULL). ON DELETE CASCADE נספר כמוטציה אחת לכל שורה שנמחקה, או כמוטציה אחת לכל טווח מפתחות שנמחק כשהעמודות שמפנות הן קידומת של המפתח הראשי של הטבלה שמפנה.
  • מוטציות של אינדקס: שתי הפעולות משתמשות במוטציית מחיקה אחת לכל שורה באינדקס לגיבוי של המפתח הזר. באינדקסים משניים אחרים בטבלה המפנה, ON DELETE SET NULL משנה רק אינדקסים שכוללים את העמודות המפנות, ואילו ON DELETE CASCADE מוחק רשומות מכל אינדקס משני בטבלה.
  • קריאות: ON DELETE SET NULL תמיד קורא את האינדקס לגיבוי של המפתח הזר כדי למצוא שורות תואמות. ‫ON DELETE CASCADE יכול לדלג על הקריאה הזו ולמחוק טווח מפתחות ישירות כשהעמודות המפנות הן קידומת של המפתח הראשי של הטבלה המפנה.
  • Cascading: הערך ON DELETE SET NULL אף פעם לא מועבר בשרשור לטבלאות צאצא משולבות, כי אי אפשר להגדיר את עמודות המפתח הראשי כ-NULL. היא מפעילה פעולות נוספות רק אם מפתח זר אחר מפנה לעמודה המעודכנת. ON DELETE CASCADE מוחק את כל השורה המפנה, כך שהוא גם מוחק את כל טבלאות הצאצא המשולבות עם ON DELETE CASCADE ואת המפתחות הזרים במורד הזרם.

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

התכונה "טבלאות משולבות" ב-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 בכל הזמנה, והרשומות של OrderItems לכל הזמנה משולבות, ולכן הן ממוקמות יחד עם ההזמנות. שילוב פיזי של Orders ו-OrderItems בדרך הזו יכול לשפר את הביצועים, לאחד מראש את הטבלאות ביעילות ולאפשר לכם לגשת לשורות קשורות יחד, תוך צמצום הגישה לדיסק. לדוגמה, Spanner יכול לבצע צירופים לפי מפתח ראשי באופן מקומי, וכך לצמצם את הגישה לדיסק ואת התנועה ברשת.

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

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

טבלת השוואה

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

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

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

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

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

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

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

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

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

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

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

שינויים בסכימה לאורך זמן

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

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

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

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

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

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

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

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

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

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

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