תגית: mysql

MySQL Transactions – חלק א’

פוסטים בסדרה:
“תסביר לי” – גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א’ – עבודה עם JSON
נושאים מתקדמים ב MySQL: חלק ב’ – json_each  ו Generated Columns

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

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

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

איך משתמשים בטרנזקציה? בבסיס, באופן הבא:

START TRANSACTION;
— do something 
COMMIT;
— or: ROLLBACK;

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


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

כיום, טרנזקציות הוא דבר “לא-קולי” (“not cool”).

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

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

עדיין, הקמפיין להשמצת הטרנזקציות היה יעיל יותר – והטרנזקציות נותרו חבוטות ודחויות על רצפת חדר-התכנון של ארגונים רבים.
ובכן:
  • טרנזקציות הן אויב ל Scalability (ברוב המקרים), וכאשר צריך הרבה Scalability – עלינו להימנע מהן.
    • גם במערכות המתמודדות עם Scalability, ישנם flows ותסריטים שעובדים ב volume נמוך יותר – ויש להם בעיות שטרנזקציות יכולות לפתור.
  • חטאנו (גם) בשנות האלפיים, ועשינו שימוש-יתר, ביכולות שונות של בסיסי-הנתונים הרלציוניים: כמו טרנזקציות, Foreign Keys, או Triggers. עדיין, בשימוש מידתי – אלו כלים שימושים שיכולים לפתור בעיות רבות.
    • הסיפור של Overselling של כלים וטכניקות, הוא לא מקרה יחידני. הוא קורה שוב ושוב, ויקרה שוב ושוב. תתרגלו.
  • טרנזקציות הן פעמים רבות, הכלי הנכון והטוב ביותר לפתור בעיות.
    • אם אתם מסוגלים להתגבר על הקושי שלא המציאו את הטרנזקציות בשנת 2018 בגוגל, והן לא מככבות במצעד ה”טכנולוגיות היפות והנכונות של 2019 [א]” – אז יש לכם סיכוי טוב להעשיר את סט הכלים שלכם בצורה מועילה.
נחזור לשאלה המקורית, שמסתבר שהיא לא טריוויאלית: “מדוע / מתי להשתמש בטרנזקציות”?
הנה שימושים מרכזיים לדוגמה:
  • אנו רוצים לעדכן שתיים (או יותר) טבלאות בבסיס הנתונים בצורה עקבית: שלא יוותר מצב שאחת מעודכנת, אבל של תקלה – השנייה לא עודכנה, ויש לנו אי-עקביות בנתונים / נתונים חסרים.
  • אנו רוצים לעדכן ערך בטבלה, בצורה עקבית ותחת racing condition אפשרי: שני threads (או מנגנון מקבילי אחר) רוצים לבצע שינוי שתלוי במצב הקיים – אבל ללא הגנה התוצאה יכולה להיות שגויה.
  • אנו רוצים להשתמש בבסיס-הנתונים כמגנון סנכרון פשוט בין כמה שרתים.
    • אין לנו מנגנון אחר, ובסיס-הנתונים הוא מספיק טוב למשימה.
  • שיפור ביצועים – זה נשמע לא אינטואטיבי, אבל במקרים מסוימים טרנזקציות דווקא יכולות לעזור לשפר ביצועים.
  • ביצוע שינוי בנתונים בצורה זהירה ומפוקחת – (בסביבת פרודקשיין, למשל). נבצע את השינוי, נבחן את השלכותיו, ורק אז נעשה commit.
לכאורה אפשר לחשוב (בתמימות) שלכל המקרים יש פתרון זהה. שבשימוש בפקודות START TRANSACTION … COMMIT – נפתרו בעיותינו. מובטח לנו שמה שציפינו, אכן יקרה, וללא תופעות לוואי מיותרות – כמובן.
הבעיה היא שבכדי להבטיח ACID, בסיס הנתונים משתמש בנעילות שפוגעות מאוד ביכולת לרוץ במקביל על אותם הנתונים.

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

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

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

את ה tradeoff בין בטיחות למקביליות – בסיס הנתונים לא יודע לקחת עבורנו. הוא מספק לנו כמה נקודות בסיסיות על ציר ה tradeoff (להלן Isolation Levels), ועוד כמה כלים נוספים בכדי לדייק את המקום בו אנו רוצים להיות.

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

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

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

זו הזדמנות טובה להזכיר את Amdahl’s law המראה את הקשר בין החלק בפעולה שאינו מקבילי – לחסמים על מקביליות, לא משנה בכמה threads נשמש….
כדי להשלים את התמונה, שווה להכיר גם את ה Universal Scalability Law (בקיצור USL) שמפרמל מהי מקביליות, וממנו ניתן לראות שניסיון לדחוף יותר עבודה מקיבולת מסויימת – דווקא תאט את המערכת עוד יותר.

מודל המקביליות של InnoDB

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

  • נעילה פסימיסטית – לקיחת מנעול על משאב (טבלה, רשומה, וכו’) בצורה שתגביל פעולות מקביליות אחרות – אך תמנע בעיות של עקביות-נתונים.
    • מחלקים את הנעילות ל-2 סוגים:
      • נעילה משותפת (Shared lock) עבור פעולות קריאה. אני קורא ערך ואוסר על שינוי הערך – אבל לא יפריע לי שטרנזקציות נוספות יקראו את הערך גם.
      • נעילה בלעדית (Exclusion lock) – לצורך שינוי הערך. אני תופס את המנעול, ולא אתן לשום טרנזקציה אחרת אפילו לקרוא את הערך (כי הוא עומד להשתנות)
  • נעילה אופטימיסטית – מאפשרת לתת לפעולות לרוץ במקביל, לגלות “התנגשויות” ואז להתמודד איתן.
    • לפעמים נרצה להכשיל את אחת הטרנזקציות (או יותר – אם יש כמה). לפעמים נסכים לקבל חוסר אחידות בקוד.
    • למרות שיש עבודה משמעותית נוספת בגילוי וטיפול ב”התנגשויות”, אנו מאפשרים מקביליות בין הפעולות – שזה יתרון שלא יסולא בפז (הציצו שוב בתרשים שלמעלה – כמה צמצום החלקים ה”בלעדיים” הוא חשוב).

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

  • 2PL (קיצור של Two-Phase Locking) עבור נעילה פסימיסטית. הרעיון הוא שנחלק את הפעולה לשני שלבים:
    • שלב ראשון – בו ניתן רק לתפוס מנעולים.
    • שלב שני – בו ניתן רק לשחרר מנעולים.
    • לרוב נרצה לתפוס מנעולים ע”פ סדר מסוים (“קודם אובייקט מסוג X ורק אז אובייקט מסוג Y”) – על מנת להימנע מ deadlocks.
      במקרים אחרים, אנו יכולים להחליט לתפוס בסדר לא-קבוע על מנת לצמצם זמני-נעילה ולהגביר מקביליות, במחיר שטרנזקציות יבוטלו לנו. המשמעות: נצטרך לפעמים לנסות להריץ אותן כמה ניסיונות – עד שנצליח לתפוס את המנעולים הרצויים.
  • MVCC (קיצור של Multi-version concurrency control) הרעיון שבו אני “מעתיק” הנתונים שטרנקזציה צריכה הצידה, ואז היא חיה ב״סביבה וירטואלית משלה״, ללא התעסקות ב racing conditions או צורך בנעילות.
    • במימוש של InnoDB, לא באמת מעתיקים נתונים, אלא משתמשים בעמודות טכנית של המערכת לכל טבלה, המנהלת איזה עותק של הנתונים שייך לכל טרנזציה, ואלו ערכים נמחקו.
    • כמובן שיש מחיר שנוסף בניהול “העותק הוירטואלי”. למשל: כאשר טרנזקציה מבקשת ערך שבטיפול של טרנזקציה אחרת – על בסיס הנתונים לבצע הדמיה של rollback של הטרנזקציה האחרת על מנת לדעת אלו ערכים צריכים להיות לטרנזקציה הנוכחית.
      • למרות המחיר הנוסף בפעולות הללו – הוא אינו דורש בלעדיות ולכן scales well.

בעיות חוסר-עקביות

כל טרנזקציה ב MySQL היא, כברירת מחדל, ברמת הפרדה (Isolation Level) שנקראת Repeatable Read.
ישנן 4 רמות הפרדה שהוגדרו ע”י התקן ANSI-SQL 92 ומקובלות בכל בסיסי-הנתונים הרלציוניים המוכרים.

למרות שרמות ההפרדה והתופעות האפשריות[ב] הוגדרו בתקן ה ANSI-SQL – ההתנהגויות בין בסיסי-הנתונים עדיין מעט שונות.
למשל: SQL Server לא מגן בפני Phantom Reads ברמת הפרדה של Repeatable Read, אבל כן מגן בפני Write Skew או Lost Update. אורקל משתמש רק ב MVCC ולא ב 2PL, מה שתורם למקביליות – אבל גם אומר שרמת הפרדה של Serializable לא מגנה בפני Write Skew….

בקיצור: Tradeoffs. Tradeoffs everywhere.

רשימת התופעות הבעייתיות האפשריות:

  • Dirty Write – כאשר שתי טרנזקציות יכולות לשנות את אותו השדה, כך שטרנזקציה אחת משנה את הערך לשנייה.
    • בגלל השימוש ב MVCC (או גישה יותר מחמירה: 2PL) – זה לא יקרה אף פעם בטרנזקציה של MySQL.
  • Dirty Read – הטרנזקציה יכולה לקרוא שינוי של טרנזקציה אחרת שהוא עדיין לא committed. הערך הזה יכול להתבטל (rollback) מאוחר יותר – בעוד הטרנזקציה שלנו משתמשת בו. התוצאה עשויה להיות שנשתמש בערך שאין לו ייצוג תקין בשאר המערכת (למשל: id לרשומה שלא קיימת). לא משהו…
  • Not Repeatable Read (בקיצור: NRR) – הטרנזקציה קוראת ערך משדה x בנקודת זמן t1, וכאשר היא קוראת שוב את השדה הזה בנקודת זמן מאוחרת יותר, t2 – ערך השדה הוא שונה. כלומר: טרנזקציה אחרת עשתה commit (אולי autocommit) – והערך שאנו רואים איננו עקבי. ההתנהגות ה NRR שוברת את תמונת “העולם המבודד” שרצינו ליצור לטרנזקציה שלנו – ובמקרים רבים היא יכולה להיות בעייתית.
  • Phantom Read – הטרנזקציה ביצעה קריאה של תנאי מסוים (נניח: year between 2016 and 2018) וקיבלה סדרה של רשומות, אך בינתיים טרנזקציה אחרת עשתה commit והוסיפה רשומות חדשות לטווח. כלומר: אם ניגש שוב לטווח – התוצאה תהיה שונה.
    • זו וריאציה מורכבת יותר של NRR. בעוד NRR ניתן לפתור בעזרת נעילה המאפשרת קריאות-בלבד מרשומה שאליה ניגשה הטרנזקציה, נעילה של טווח שנובע מפרדיקט היא דבר מסובך למדי – גם עבור בסיס נתונים משוכלל.
  • Read Skew – וריאציה נוספת של NRR: ישנן 2 טבלאות עם קשר לוגי ביניהן. שדה x באחת משפיע או מושפע על שדה y בטבלה השנייה. הטרנזקציה קוראת ערך x מטבלה אחת, אך בינתיים טרנזקציה אחרת משנה את x וגם את y (בצורה עקבית). בסיס הנתונים לא יודע בוודאות על הקשר, וכאשר אנו קוראים את y – עדיין עלולים לקבל את הערך הישן – לפני העדכון של הטרנזקציה השנייה. התסריט הזה מעט מבלבל – הנה דוגמה מפורטת.
  • Write Skew – וריאציה דומה, בה שתי טרנזקציות קוראות את שני הערכים x ו y, ואז אחת מעדכנת את x – בעוד השנייה מעדכנת את y. התוצאה – עקביות הנתונים נשברה. הלינק מלמעלה מספק גם דוגמה מפורטת ל Write skew.
  • Lost update – שתי טרנזקציות קוראות ערך ומעדכנות אותו. אחד העדכונים יאבד – מבלי שנדע שכך אכן קרה. במקרה של counter, למשל – הנזק מוחשי מאוד. גם בשדות המכילים ריבוי פריטים (כמו json) – הנזק הוא ברור. ישנם גם מקרים נוספים בהם התוצאה היא בעייתית.
למרות ההגנה הרבה שהן מספקות, חשוב לנסות ולהימנע מרמת הפרדה של Serializable – היכולה לפגוע משמעותית במקביליות, במיוחד כאשר הטרנזקציות אינן קצרות.

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

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

אם רק מעניינת אותנו היכולת לבצע מספר פעולות ביחד, או שאף אחת לא תתרחש (rollback) – ואנו מוכנים לקבל חוסר-עקביות כאלו ואחרים הנובעים ממקביליות (כמו עבודה רגילה בבסיס נתונים, ללא טרנזקציות), אזי רמת הפרדה של Read Uncommitted מספיקה לנו – ותספק מקביליות טובה.

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

אם מעוניינים ב Atomicity, היכולת של טרנזקציה להתבטל מבלי להשאיר “שאריות” אחריה בפעולת Rollback, אזי רמת ההפרדה של Read Committed עשויה להספיק – ולאפשר מקביליות רבה.

כאשר אנו רוצים להתגונן בפני Racing condition אפשרי (למשל: מתעסקים בדברים רגישים, כמו כסף או פעולות שיש לדייק בהן) – אזי לרוב נרצה להשתמש ברמת הפרדה של Repeatable Read.

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

בשתי הדוגמאות האחרונות, אם אנו יודעים בוודאות שהגישה הקריטית היא רק לרשומה בודדת (למשל: קריאת ערך – ואז עדכון) – אזי Read committed היא רמה מספיק טובה. כל הרמות הגבוהות יותר מגינות מפני תסריטים של ריבוי רשומות / טבלאות.
עם אופטימיזציות כאלו כדאי להיות זהירים: משהו עשוי להשתנות בתוכן הטרנזצקיה, מבלי שמבצע השינוי יזכור / יבין שעליו להעלות את רמת ההפרדה.
מצד שני, יש כמה יתרונות משמעותיים לשימוש ב Read Committed (בקיצור RC) על פני Repeatable Read (בקיצור RR) מבחינת ביצועים:

  • ב RR, מנוע InnoDB ינעל כל רשומת אינדקס ששימשה את הטרנזקציה. אם חיפוש הרשימה מתבצע באינדקס לא יעיל (סריקה של הרבה רשומות) – אזי חסמנו הרבה מקביליות.
  • ב RR, המנוע מחזיק את כל הנעילות עד סוף הטרנזקציה. ככל שהטרנזקציה ארוכה יותר – כך זה בעייתי יותר.
  • ב RR, המנוע יוצר gap lock, על רשומות באינדקס שעלו בטווח של השאילתה (גם אם לא נסרקו). שוב – נעילה שעשויה להיות משמעותית למקביליות. נדבר על gap lock בחלק השני של הפוסט.

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

עוד שני תסריטים שציינתי ולא כיסינו הם אלו:

שיפור ביצועים – זה מקרה ייחודי – אך שימושי.
InnoDB מחזיק binary log, על כל שינוי שבוצע בינתיים עבור התאוששות מהתרסקות ועבור replications. כדי לשמור על הלוג ככשיר להתאוששות, עליו לבצע flush ללוג (כלומר: לדיסק) על כל טרנזקציה שמתבצעת.
אם אנו מבצעים עשרות או מאות שינויים (למשל: הכנסה של רשומות חדשות), בשל ה autocommit – אנו נחייב את המנוע לבצע עשרות או מאות flushes לדיסק (פעולה יקרה).
שימוש בטרנזקציה (הכי פשוטה) – יאפשר לבסיס הנתונים לבצע flush יחיד.

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

בד”כ נשתמש ברמת Repeatable Read לכאלו שינויים. כבני-אדם, אנו נחזיק את הטרנזקציה פתוחה לנצח (דקות? יותר?) – ולכן כדאי להימנע מ Serialization – במידת האפשר.

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

כיצד עושים “התערבות ידנית”?
אלו מנגנוני-נעילה נוספים, הרצים מ”אחורי-הקלעים”, קיימים ב InnoDB? (למשל: הזכרנו את ה gap lock)
כיצד מאתרים בעיות של נעילות בעייתיות / עודפות?

על כל זה – נדבר בפוסט ההמשך.

שיהיה בהצלחה!

——

[א] החל מחודש ספטמבר, נהוג כבר להתמקד רק בטכנולוגיות השנה הבאה. #שנה_נוכחית_זה_פח

[ב] התקן המקורי של ANSI-SQL 92 זיהה רק 3 תופעות אפשריות של חוסר עקביות בנתונים, אך מאמר שהגיע שלוש שנים אחריו, הציף עוד 4 מקרים בעייתיים נוספים.

[ג] בעצם, בסיסי-נתונים לא רלציוניים היו תמיד: היררכיים (קדמו, והתחרו עם בסיסי-נתונים רלציוניים – תקופה מסוימת), Time series, מבוססי-אובייקטים, מבוססי-XML, גיאוגרפיים, ועוד.
הם פשוט לרוב נשארו נישה, ולא הצליחו לייצר רעש, כמו הגל האחרון.

נושאים מתקדמים ב MySQL: חלק ג׳ – מנועי אחסון, ומבנה האינדקסים

פוסטים בסדרה:
“תסביר לי” – גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א’ – עבודה עם JSON
נושאים מתקדמים ב MySQL: חלק ב’ – json_each  ו Generated Columns

נושאים מתקדמים ב MySQL: חלק ג׳ – מנועי אחסון, ומבנה האינדקסים

—-

אלמנט חשוב של הארכיטקטורה של MySql היא ההפרדה של ה Storage Engines כרכיב מודולרי בבסיס הנתונים.
זהו בעצם יישום של דפוס Adapter בו ניהול האחסון לדיסק (או מקור אחר) מבוצע ע”י מודול שניתן להחליף.

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

כדי לסבר את האוזן, הנה רשימה של כמה מנועי-אחסון בהם נעשה שימוש ב MySQL:

  • InnoDB – ברירת המחדל של MySQL מאז גרסה 5.5, וכיום גם ברירת המחדל של MariaDB. נדבר עליו בהמשך.
  • MyISAM – ברירת המחדל של MySQL לפני גרסה 5.5. נדבר עליו בהמשך.
  • Memory (או Heap) – אחסון של הנתונים בזיכרון.  הגישה מהירה, אך באתחול בסיס הנתונים – הסכמה נשמרת, בעוד המידע בטבלאות מתאפס.
  • CSV – אכסון וניהול המידע בקבצי CSV.
  • BlackHole – כמו dev/null/ – המנוע מקבל שאילתות עדכון – אך לא מאחסן מידע בכלל. השימוש הנפוץ במנוע הזה הוא בתצורה מבוזרת בה כל המידע שנשמר ל node משוכפל ל replica מרוחקת, ואין צורך לשמור אותה מקומית.
  • Archive – מנוע ש optimized לגישות נדירות לכמות גדולה של נתונים בכל פעם. למשל: Audit.
  • XtraDB – מנוע בסיס נתונים משופר שנבנה ע”י חברת Percona (חברת ייעוץ / מומחים ל MySQL). תקופה מסוימת נחשב עדיף על InnoDB בביצועים והיה מנוע ברירת המחדל של MariaDB (החליף את Aria), אך לאחרונה הפערים נסגרו – ומנוע ברירת המחדל של MariaDB כיום גם הוא InnoDB.
  • MyRocks – מנוע שפותח ע”י פייסבוק המאפשר להשתמש בנתונים של RocksDB (שהוא בעצם Fork של LevelDB שמתוחזק ע”י פייסבוק). המנוע נכלל בהתקנה הגרסאות החדשות של MariaDB, וגם בהתקנה של Percona Server (ה distro של חברת Percona ל MySQL).
  • TukoDB – עוד מנוע שנוצר ע”י חברת Percona וזמין כברירת מחדל ב MariaDB וב Percona Server, המכוון לטיפול במידע שהוא Steaming או שיש לטפל בו ב Near-Realtime. המנוע משתמש באינדקסים המבוססים על מבנה-נתונים בשם Fractal Tree במקום ה B-Tree המסורתי.

מנועי האחסון הם Pluggable וניתן להתקין אותם על גבי התקנה קיימת של MySQL.
הבחירה הארכיטקטונית של MySQL במנוע אחסון שהוא Pluggble פותחת אופציה להוסיף יכולות, בקלות יחסית, לבסיס הנתונים וגם לבצע שינויים הדרגתיים בארכיטקטורה (נבנה את InnoDB לאורך שנים – עד שיהיה בשל להיות ה Default). מצד שני – הגישה הזו מקשה על אופטימיזציות קצה-אל-קצה ברמת בסיס-הנתונים כולו, כי כל מנוע אחסון מתנהג קצת אחרת.
כמו כל שיקול ארכיטקטוני – יש פה Trade-off.

מקור. הבהרה: Keys Cache היא יכולת ש MyISAM משתמש בה – ולא יכולת של השרת המתבססת על MyISAM.

המנועים המרכזיים: InnoDB מול MyISAM

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

כיום, InnoDB עולה בכמעט כל פרמטר על MyISAM.
בעבר עוד היו ל MyISAM יתרונות יחסיים, כגון דחיסה, Full-Text Index, או אינדקס Geospatial.
הפערים הללו נסגרו, ו MyISAM נראה היום מיושן למדי (אין Transactions! הנעילה היא ברמת הטבלה!).
עד שנת 2009, בערך, MySQL פיגר ביכולות בסיסיות אחרי שאר התעשייה. הוא היה חינמי ופשוט – וכך הצליח לחדור ולתפוס נתח שוק משמעותי.

למנוע ה Memory, כמובן, אין תחליף. בסיס הנתונים משתמש בו לכל מיני טבלאות מערכת (לדוגמה: ה Performance Schema – כך שה overhead שלה יהיה זניח למדי), ואכסון נתונים בזיכרון היא יכולת שימושית במגוון מקרים.

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

InnoDB

המבנה המדובר ביותר לאינדקסים של בסיס נתונים הוא מבנה הנתונים בשם B-Tree. אני מניח שהמבנה הזה מוכר לכם מהתואר האקדמי או מקור אחר. זהו מבנה של עץ שבו כל node הוא בגודל של Page בדיסק, כך שמצמצמים את מספר הקריאות לדיסק. הרעיון תקף גם לבלוקים של זיכרון (הרי הזיכרון הוא לא באמת “Random Access”. גם שם ניגשים לבלוקים).

מבנה הנתונים ש InnoDB משתמש בו הוא גרסה מעט “משופרת” הנקראת B+Tree (בחירה נפוצה בקרב בבסיסי-נתונים):

  • כל Leaf node מכיל מצביע לזה שאחריו (על מנת ליעל סריקות של טווחים).
  • כל הערכים נשמרים רק ב Leaf Nodes מה שאומר שב nodes הביניים יש רק מפתחות ולא ערכים. זה טוב כי אז ניתן לשמור יותר מצביעים בכל node ביניים. מצד שני, מאבדים את היכולת לשים ב node ביניים ערכים (key+value) שבשימוש נפוץ, ואז להגיע אליהם בפחות גישות. כרגיל: a trade-off.
מקור: Stack Overflow
MySQL מנהל לכל טבלה שני סוגי אינדקסים:
  • Clustered Index, או Primary Index – בו מאחסנים גם מפתחות (keys) וגם את שאר ערכי הטבלה (row) ביחד, כאשר הערכים בעלי ערך אינדקס דומה/עוקב – מאוחסנים זה ליד זה פיסית על הדיסק.
    • לכל טבלה יש רק Primary Index אחד.
    • אם לא תגדירו Primary Index לטבלה, אזי InnoDB ייצור אחד לבד, על בסיס auto-increment, אבל שלא חשוף לכם. יש בזה כמה בעיות – וההמלצה הגורפת היא תמיד להגדיר Primary Index בעצמכם.
  • Non-Clustered Index או Secondary Index – בו יושבים keys, אך הערכים הם לא ה Rows עצמם, אלא מצביעים ל primary Index.
    • האינדקס ממוין ע”פ ה keys, ולא ע”פ ה primary Index.
    • אפשר להגדיר כמה secondary Indexes שרוצים לכל טבלה.

בואו נראה תרשים שיסביר זאת בצורה יותר ברורה:

מקור: סיני כלשהו. אהבתי את התרשים.
  • ה Primary Index הוא B+Tree, כאשר בכל Leaf Node מאוחסנים <Pair<Key, Row. הרשומה שלנו במקרה הזה היא מס’ חברה ושם (אם מתעלמים מה key).
  • על הדיסק נשמרים הערכים בצורה ממוינת. InnoDB ינסה לשמור את ה Pages של ה LeafNodes העוקבים קרובים זה לזה על הדיסק (כדי שיהיה ניתן לקרוא אותם בגישה רציפה אחת).
  • המחיר של Clustered Index הוא בהכנסת רשומות (או בטווח הפחות מיידי – מחיקות). הפעולות הללו יהיו יקרות יחסית ל non-clustered index.
    • פעולות של פיצול / איחוד דפים על הדיסק – הן יקרות.
    • כש InnoDB יוצר Page חדש, הוא מותיר בו 7% שטח פנוי, עבור עדכונים של רשומות (נניח ערך varchar שגדל) או הכנסה של רשומות חדשות.

עכשיו נתבונן על ה secondary index:

  • ה Secondary Index הוא גם B+Tree, כאשר בכל Lead Node מאוחסנים <Pair<Key, Primary Key.

מה שחשוב להבין מזה:

  • הרשומות בטבלה אשכרה נשמרות בדיסק ממוינות ע”פ ה Primary Index. יש לזה מחיר – אבל גישות ע”פ ה Primary Index יהיו יעילות ביותר.
    • לדייק: הרשומות בטבלה נשמרות על ה Primary Index.
    • זה לא בהכרח המצב בבסיסי נתונים רבים אחרים. אין clustered index ב Postgres או MyISAM Engine, ובאורקל זהו פיצ’ר אופציונלי (Index-Organized Tables).
  • אינדקסים משניים הם רק הצבעות ל Primary Index.
    • ככל שה Primary Key הוא ארוך יותר (בבתים) – אזי כל ה secondary index המצביעים אליו יהיו קטנים יותר, ויוכלו להכיל פחות רשומות בכל Leaf Node. הסבר: גודל ה Leaf Node הוא קבוע. למשל 16KB או 64KB.

עוד אינדקסים שכדאי להכיר:

  • Full Text Index – סוג של אינדקס הפוך המכיל את כל ההצבעות לרשומות המכילות מילות מפתח מסוימות. זהו אינדקס גדול מאוד – אך יכול לשפר מאוד חיפושים ע”פ מילות מפתח.
    • בכדי להשתמש בו, יש להשתמש בפקודת MATCH AGAINST במקום ב WHERE.
  • Geospatial Index – לחיפוש בשטחים (למשל: פוליגונים) על גבי מרחב דו-מימדי (מרחב גאוגרפי). המימוש של InnoDB הוא של R-Tree, אם כי גם KD-Tree הוא מבנה מקובל לאינדקסים מסוג זה.
    • שווה לציין שהמימוש של InnoDB ל Geospatial Index הוא מוגבל יחסית למימושים של Oracle, PostgreSQL או MongoDB.
האינדקסים הטובים ביותר ע”פ גוגל
שניה! …. מה?!?!

איך להגדיר את ה Primary Index האולטימטיבי?

סיכמנו שלתת ל InnoDB לקבוע לבד את ה Primary Index הוא פרקטיקה לא טובה.
בואו נראה מהן האופציות המקובלות / ה Best-Practices:
  • אפשר להשתמש ב Auto-Increment (גודל ברירת מחדל = 4 בתים).
    • יתרון: Primary Index מספרי הוא קטן (= מעט בתים בזיכרון) ויאפשר להכניס:
      • יותר מצביעים ב Primary Index intermediate nodes = פחות גישות לדיסק.
      • יותר רשומות ב Secondary Index Leaf Nodes = אינדקסים משניים יותר קטנים ויעילים.
      • יתפוס פחות מקום כ Foreign Key המצביע על הטבלה.
    • יתרון נוסף: ניתן למיין בזריזות את הטבלה ע”פ סדר עולה / יורד של הכנסת הרשומות. זה נחמד בעיקר בעבודה עם כלי Queries נוסח SQL Pro.
    • יתרון נוסף: מפתח קטן יקל על פעולות Join (החלק שמתבצע בזיכרון).
  • אפשר להשתמש ב GUID (כלומר = 128 ביט).
    • למרות ש 128, באופן תאורטי, הם 16 בתים, לרוב נייצג את ה GUID בייצוג הקסדצימלי (0-9A-F) מה שאומר מחרוזת באורך 32 בתים, ואז נגדיר אותו כ (varchar(32 – מה שבמצבים מסוימים עשוי להתפרש ע”י MySQL כ 3 תווים לכל אות + תו delimiter (בגלל ה Varchar) = אורך של 97 בתים.
      • מתוך רצון להטיב, אפשר להגדיר את השדה כ (char(32 – מה שיגרום לבסיס הנתונים להקצות לו 96 תווים (utf8mb3) או 128 בתים (utf8mb4) – כמעט תמיד.
      • יש ב MySQL 8 אופטימיזציות חדשות שיכולות להטיב את המצב. בלתי אפשרי באמת להעריך מה תהיה התוצאה המדויקת של כלל האופטימיזציות שעובדות בשילוב.
    • ההבדל בין 4 בתים ל 97 בתים, או אפילו “רק” 32 בתים – הוא כבר משמעותי למדי!
    • יתרון: ה ID הוא ייחודי בכל המערכת (או כל מערכת). באגים בהם משתמשים ב key הלא נכון – ייחשפו במהרה. ב auto-increment, אם השתמשנו במפתח לא נכון – יש סיכוי טוב שנקבל רשומה לא נכונה ויהיה קשה יותר לגלות זאת.
      • הייחודיות הזו מאפשרת לאחד נתונים מגרסאות שונות של בסיס הנתונים. למשל: תסריט של recovery, תסריט של Multi-region או כמה עותקים של בסיס הנתונים.
      • Id ייחודי ובעל פיזור אחיד יחסית, מאפשר Sharding (תסריט פחות נפוץ).
    • יתרון: אבטחה. מישהו שנחשף למפתח אחד – לא יכול להסיק ממנו ולנחש מפתחות אחרים. ב Auto-increment אפשר בקלות להבין שיש מפתחות דומים במספרים עוקבים.
    • חיסרון: האקראיות של המספרים הופכת את המיון של ה Clustered index לחסר משמעות.
      למשל: ב MS-SQL יש פונקציה בשם ()newsequentialid, המייצרת GUID בעל אלמנטים סדרתיים – כך שעדיין ה clustered index בא לידי ביטוי.
    • שווה לציין גם ש GUID עצמו לא כולו אקראי. חלק ממנו מבוסס על הפרטים של המכונה המקומית (למשל IP address), כך שאם כל ה GUID נוצרים על אותה המכונה (בסיס הנתונים) – יש כאן חוסר יעילות מסוים. זה עדיין משני לכל הנ”ל.
  • אפשר להשתמש במפתח עסקי טהור. למשל: כתובת אימייל. שם חברה + קוד מדינה (אם ייחודי), וכו’
    • המפתח הזה עלול להיות הגדול ביותר = ההשפעה החמורה ביותר על הביצועים מהיבט גודל האינדקסים.
    • מצד שני, אם יש לנו intensive read workload שניתן לאפיין בצורה ברורה (נניח: קריאת כל הרשומות של אותו ה email בצורה תדירה) – אזי מפתח שירכז את כל הרשומות הללו במספר קטן של דפים בדיסק עשוי לשפר מאוד את הביצועים.
    • קריאה של 10 דפים בכדי לטעון 1000 רשומות תהיה מהירה בסדרי גודל מקריאה של 300 דפים בכדי לטעון את אותן 1000 רשומות. זה כבר לא עניין של אינדקס – אלא גישה לנתונים בדיסק.
טוב. לא נפתור כאן את הדילמה הזו, דילמה רבת שנים. בכל זאת, כמה תובנות מצדי:
  • GUID הוא בחירה טובה, כאשר מדובר בטבלאות לא גדולות במיוחד ו/או אינן מעורבות בעבודה אינטנסיבית (הרבה חיפושים מורכבים, הרבה joins, וכו’).
  • כאשר הביצועים מתחילים לשחק תפקיד – קרנו של ה Auto-Increment עולה.
    • פשרה אפשרית היא להחזיק שני מפתחות לרשומה:
      גם Id כ auto-Increment (שזה יהיה ה Primary Key) וגם GUID כשדה נוסף בטבלה (שיוחזק כ Secondary Key).

      • כל חשיפה לעולם החיצון (למשל: Clients או בסיס נתונים אחר) – תתבצע על בסיס ה GUID.
      • כל העבודה הפנימית – תתבצע על בסיס Auto-increment.
    • בכל מקרה, דאגו להחזיק את ה GUID כ 16 בתים, ולא כ 97. ההבדל בביצועים עשוי להיות דרמטי.
      • הקצרנים, יוכלו לקצץ חלקים לא אקראיים, ולקצר את המפתח ל 12 או 14 בתים. זה כבר לא כ”כ משמעותי. דווקא להוספת כמה בתים בתחילת ה GUID המאפשרים סדרתיות (למשל – מספר build או מספר טעינה של השרת) – תהיה השפעה טובה יותר.
  • מפתח עסקי טהור, הוא לא בהכרח רע.
    • יש פוטנציאל טוב לשיפור אמיתי, אם יודעים מה עושים ומהם דפוסי השאילתות.
    • דיי נפוץ שדפוסי השימוש משתנים עם הזמן, ובצוות יש אנשים בעלי הבנה פחות עמוקה של ה tradeoffs וההשלכה שלהם, כך שסטטיסטית מפתחות טהורים עסקים – נוטים להיות פחות טובים.
      • לא הזכרנו את המקרה שבו מטעמים עסקיים, אנחנו נדרשים לעקוף את האילוץ של המפתח (למשל: התמיכה רוצה להזין למערכת משתמש שאין להם כתובת אימייל שלו עדיין).
    • ולכן, ככלל, הייתי ממליץ להימנע ממפתחות עסקיים טהורים, מלבד מקרים חריגים. הם בעייתיים ברמת ה Scalability של הצוות.

הערה על Prefix Indexes:

ב MySQL יש יכולת להגדיר אינדקסים על חלק מהשדה, למשל: 10 תווים ראשונים. למשל:

CREATE INDEX part_of_name ON customer (name(10));

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

אליה וקוץ בה: prefix Index הוא אינו Covering, כלומר – באינדקס לא מאוחסן הערך השלם של העמודה.
בד”כ במפתח משני ה key הוא ערך השדה. בשאילות מסוימות – מספיק לקרוא את האינדקס המשני מבלי לקרוא בכלל את ה primary index (או “הטבלה”).
ב Prefix Index – תמיד MySQL ילך לקרוא את ה Primary index. גם אם ה significant part נראה נכון. היה ניתן לבצע אופטימיזציות ובמקרים מסוימים לא ללכת. למשל: שאילתה המבוססת על התנאי ‘%name LIKE ‘Lio לא צריכה באמת ללכת לאינדקס הראשי, כי יש לה את כל המידע הנדרש ב Prefix Index.
כיום – אין כזו אופטימיזציה ו MySQL תמיד יקרא גם את ה Primary Index.

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

הארכיטקטורה של InnoDB

תחזוקה של אינדקסים: Analyze Table

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

ל MySQL (ובכלל בסיסי נתונים) כן יש כמה מנגנונים לתחזוקת אינדקסים – אבל התערבות ידנית עשויה להיות שימושית למדי.

תפקיד ה Query Optimizer של MySQL הוא לבנות את תוכנית הפעולה (להלן Query Plan) היעילה ביותר על מנת לספק שאילתה נתונה. נקודת מפתח בתוכנית הזו היא האם להשתמש באינדקסים – ואלו אינדקסים.

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

  • כמה רשומות יש בטבלה, ומה הפיזור שלהן (להלן Cardinality).
  • כמה רשומות צפויות להיות בטווח מסוים של מפתחות (להלן: ()records_in_range)

ספציפית, InnoDB שומר את הסטטיסטיקות הנ”ל בטבלה שלו המבוססת על ה Memory Storage Engine.
פעולת איסוף הסטטיסטיקות מתבצע ע”י דגימה של מספר קטן של דפים (ברירת מחדל = 8 דפים, המספר שיבחר בסוף מושפע גם מגודל הטבלה) שנבחרו באופן אקראי מתוך הטבלה, ומתוך הנחה שהדגימה הזו מייצגת.

הדגימה הזו תתבצע במצבים הבאים:

  • גישה ראשונה לטבלה.
  • מספר הרשומות בטבלה גדל ב 10% מאז הדגימה האחרונה או 2 מיליארד רשומות נוספו מאז הדגימה האחרונה (לטבלאות גדולות במיוחד).
תובנה חשובה היא שהסטטיסטיקות אינן מדויקות, ואינן בהכרח עקביות. כל ניתוח מחדש של הטבלה עשוי להציג תמונה מעט שונה.
ניתן להורות ל DB לבצע את הדגימה מחדש ע”י הפקודה:
ANALYZE TABLE table_name;
הפעולה הזו קצרה מאוד [א], מכיוון שהיא ניגשת למדגם קטן למדי של נתונים בטבלה.
ניתן לקבוע את גודל המדגם ע”י הפרמטר בשם innodb_stats_transient_sample_pages. ערך גבוה יותר יספק סטטיסטיקות מדויקות יותר, במחיר פעולת ניתוח יקרה יותר. נראה, למשל, שב AWS RDS הערך נקבע ל 20.
מתי חשוב לבצע Analyze Table בצורה יזומה?
אחר שינוי אינדקס, או הוספה של כמות גדולה מאוד של נתונים לטבלה – כדאי לבצע פעולת Analyze Table יזומה. כבר נתקלתי במצבים בהם ביצענו שינויים שאמורים היו להיות משמעותיים לביצועים – אך לא ראינו את ההשפעה שלהם עד הפעלה של פעולת ה Analyze Table
החל מגרסה 5.6.2 ברירת המחדל היא לשמור את הסטטיסטיקות לדיסק בעת אתחול (1 = innodb_stats_persistent). המוטיבציה המוצהרת לשינוי היא שמירה על עקביות של זמני הריצה של השאילתות לאחר אתחול של השרת. ייתכן וזה היה בשל משתמשים שפתחו באגים בנוסח “שאילתה X יותר אטית לאחר עדכון ו/או Restart” ולא אופטימיזציה נכונה לכל מצב.
החל מגרסה 8.0.3 נוסף הפרמטר sysvar_information_schema_stats_expiry המוחק את הסטטיסטיקות כל זמן נתון. ערך ברירת המחדל הוא 24 שעות. זו גישה הפוכה, והרבה יותר הגיונית, לדעתי.
אני לא מכיר דרך פשוטה לבצע Analyze Table תקופתי בגרסה 5.7 (ועדיף: בזמן ה off hours של המערכת). זו פעולה הגיונית מאוד, ובד”כ נעשית ע”י custom scripts.
ניתן לבדוק מתי התעדכנו הסטטיסטיקות לאחרונה ע”י בדיקת עמודת ה last_update בטבלת mysql.innodb_table_stats ובפירוט של אינדקס בטבלת ה mysql.innodb_index_stats.

תחזוקת אינדקסים: Optimize Table

פעולת התחזוקה החשובה השנייה היא פעולה של Defragmentation של מבנה הטבלה (האינדקס הראשי) והאינדקסים המשניים.
השפעה שלילית גדולה של fragmentation היא כאשר עושים סריקה סדרתית של אינדקס / נתוני הטבלה. יש הבדל מהותי אם זו קריאה רציפה מהדיסק (ועדיף: של דפים עם תפוסה גבוהה), או קריאה של דפים המפוזרים במקומות שונים על הדיסק. גם בעידן ה SSD – ההבדל הוא מהותי מאוד.

הפקודה Optimize Table שקולה ל:

  • איחוי ה primary index.
    • כלומר: דחיסת הדפים ל 93% תפוסה, וכתיבה סדרתית שלהם על הדיסק.
  • איחוי ה secondary indexes
    • כנ”ל.
  • ביצוע ANALYZE TABLE על הטבלה

ב InnoDB, הפקודה OPTIMIZE TABLE ממופה ל ALTER TABLE … FORCE – המבצעת פעולה דומה.
מתחת לקלעים InnoDB ייצור עותק נוסף של הטבלה אשר יכתב לדיסק בצורה רציפה (זהו האיחוי), יעדכן בו שינויים שנעשים בטבלה תוך כדי העבודה, ואז יחליף את העותק הישן בחדש.

  • הפעולה תנעל את הטבלה לזמן קצר (שלב ה preparation) ואז עלולה לארוך זמן לא-מבוטל (מספר שעות הוא לא זמן נדיר לטבלה גדולה / פעילה. במקרים קיצוניים זה גם יכול לארוך ימים).
  • אין אינדיקציה על התקדמות, וביטול הפעולה גם יכול לארוך זמן.
  • בזמן הזה, כל העבודה עם הטבלה תהיה אטית יותר. אם זו טבלה מרכזית – ההשפעה על כלל בסיס הנתונים עשויה להיות ניכרת. חשוב מאוד לבצע פעולות OPTIMIZE TABLE ב Off Hours של המערכת שלכם.
  • כשאפשר – InnoDB ישתמש ביכולת בשם online DDL על מנת לבצע את העדכונים הללו במקביל, ובצורה שתעמיס פחות על הטבלה המקורית.

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

בזמנו היו הצעות לבצע Drop Index לפני ה OPTIMIZE TABLE ואז Create Index לאחריו, בכדי להמהיר את זמני הביצוע. יש כאן סיכון ברור לרגרסיה משמעותית בביצועים בזמן ה Optimize, ואני לא יודע לומר עד כמה העצה הזו רלוונטית גם היום.

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

  • טבלה שבה יש הרבה Inserts אבל ה Primary Key שלה תלוי בהוספה (למשל: מפתח ראשי מסוג Auto-increment) – לא צפויה ליהנות הרבה מ OPTIMIZE TABLE.
  • טבלה שבה יש עדכונים אינטנסיביים (נניח: מאות updates בשנייה) עשויה ליהנות מ OPTIMIZE TABLE – אבל חשוב מאוד לתכנן את הפעולה בזמן של מינימום עבודה על הטבלה. הסכנה ל downtime היא ממשית.
אתם יכולים לבדוק את הצורך ב OPTIMIZE TABLE בעזרת כמה שאילתות:

SELECT *
FROM   sys.schema_index_statistics
WHERE  table_name = ‘tbl_name’;
לבדוק כמה הכנסות / מחיקות / עדכונים היו בטבלה. הטבלה הזו מבוססת ככל הנראה על ה performance_schema.

השאילתה הבאה היא שאילתת מערכת:

SELECT table_name,
index_length,
data_length,
data_free,
data_length + index_length                           AS total_ו,
( data_free * 100 ) / ( data_length + index_length ) AS free_ratio
FROM   information_schema.tables
WHERE  table_schema = ‘schema_name’;

המציגה את ה free_ratio – היחס בין השטח שמוקצה ואינו בשימוש – לשטח שבשימוש.
זכרו ש InnoDB מכוון ל 7%. הכל תלוי במקרה, אבל אחוזים גבוהים (נאמר 40-50% ומעלה) הם מועמדים ל OPTIMIZE.

BLOBs

בכל הדיון עכשיו, לא דיברנו על שדות BLOB/CLOB.
ב InnoDB, עמודות באורך משתנה (כמו BLOB, JSON, TEXT, Varchar, וכו׳) עשויות להישמר בתוך ה Pages של ה Primary Index או עשויות להישמר בקובץ אחר נלווה.

השיקול נעשה ברמת הרשומה והוא עובד כך:

  • אם גודל השדה הוא 40 בתים או פחות (משתנה בשם BTR_EXTERN_FIELD_REF_SIZE, כפול 2) – אזי השדה יאוחסן בתוך המפתח הראשי.
    • בתצורות שונות (למשל: Table’s ROW_FORMAT = COMPACT) המספר הזה עשוי לעלות ל 768 או 1024 בתים.
  • מעבר לכך InnoDB ישתדל להשאיר את השדות באורך משתנה בתוך האינדקס הראשי. אם הוא אינו מצליח להכניס ב Page (להזכיר: ברירת המחדל היא 16KB) לפחות 2 רשומות (rows) – אזי הוא יתחיל להוציא את השדות באורך משתנה מתוך ה Primary Index.
    • הוא יתחיל להוציא את השדות מהגדול – לקטן.
בקיצור: שדות בגודל המתקרב ל 8KB, בתצורת ברירת-המחדל של MySQL – הם המועמדים העיקריים לצאת מתוך ה Primary Index לקובץ נפרד.
לקחים:
  • מכיוון שהשימוש בקובץ BLOB חיצוני איננו אחיד לאורך הרשומות בטבלה, הרעיון לבצע SELECT ללא עמודות מסוימות ולהימנע כך מהמחיר של שדות מסוג BLOB על הטבלה – אינו ממש נכון.
  • תאורטית, ב InnoDB אין ממש הבדל בין (Varchar(65,536 ל Text.
    • בפועל, למרות ש InnoDB תומך בערכי Varchar גדולים, MySQL עצמו לא יאפשר להגדיר סכמה בה סך גודל השדות ברשומה (row) גדול מ 65K – ולכן לא ניתן יהיה להגדיר (varchar(65K
  • כדאי להיזהר מביצוע OPTIMIZE TABLE של טבלה המכילה BLOBs גדולים (למשל: קבצי PDF). חלק מהמדדים (כמו free_ratio) עשויים להצביע על צורך ב defrag לטבלה – בעוד שבפועל ה Primary Index (מה שחשוב) הוא במצב מצוין. השאילתה שבודקת מספר עדכונים ומחיקות – מתאימה יותר למשימה במקרים של BLOBs.

שיהיה בהצלחה!

——

[א] פעם, פעולת Analyze Table הייתה נועלת את הטבלה – בעיה ממשית. ההתנהגות הזו תוקנה בגרסה 5.6.38 – אך עדיין ניתן למצוא אזהרות באינטרנט לא לבצע פעולת Analyze Table בצורה תכופה. #לא_אקטואלי.

—–

לינקים רלוונטיים:

נושאים מתקדמים ב MySQL: חלק ב’ – Generated columns ו json_each

פוסטים בסדרה:
“תסביר לי” – גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א’ – עבודה עם JSON
נושאים מתקדמים ב MySQL: חלק ב’ – json_each  ו Generated Columns

בפוסט הקודם דיברנו על שימוש ב json column ב MySQL.

אחת המגבלות הגדולות של שדות json הם מהירות החיפוש בהם:

  • אם השדות שמורים כ json – יש לקרוא את ה json כולו מהדיסק לזיכרון – על מנת לשלוף ערך בודד.
  • אם השדות שמורים כ text column – אזי יש עוד עבודת CPU של פענוח מבנה ה json – זמן לא זניח ב jsons גדולים. להזכיר: בעמודה מטיפוס json הם שמורים כ key-value עם גישה מהירה יותר לפי מפתח.
הפתרון האפליקטיבי המתבקש שבו מפתחים שונים נוקטים הוא לאחסן “עותק” של מספר מצומצם של שדות עליהם יש חיפוש – ב columns מקבילים לזה של ה json. ההשפעה על הביצועים – תהיה לרוב דרמטית.

הנה סכמה כזו לדוגמה:

[ Id (guid/auto-inc), json_data (json), field_x (varchar(100)), field_y (int) ]

את השדות x ו y – נרצה לרוב להחזיק גם בתוך ה josn וגם בצד לצורך גישה מהירה / שימוש באינדקסים.
למה שכפול נתונים?
אם נאחסן אותם רק מחוץ ל json – העבודה עם אובייקט ה json תהיה מסורבלת יותר.
לפעמים השדה שאנו מוציאים הוא גם ערך וגם מיקום באובייקט – למשל: המחיר של ההזמנה המאוחרת ביותר (כאשר יש לנו מערך של גרסאות של הזמנות).

את התהליך הנ”ל – ניתן לבצע בצורה פשוטה יותר בעזרת יכולת של MySQL שנקראת generated column, או בעברית “עמודות מחוללות” (מלשון לחולל, ולא מלשון חילול).

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

ALTER TABLE policies
ADD COLUMN state VARCHAR(30) GENERATED ALWAYS
AS (json_unquote(json_extract(`json_data`,‘$.address.state’))) STORED
;

אני מוסיף שערכו יהיה הביטוי (expression) שבסוגריים לאחר המילה השמורה AS.

  • חשוב לי להשתמש ב json_unquote על מנת שהעמודה לא תכיל מירכאות – וכך אוכל לבצע חיפוש יעיל מול האינדקס.
  • הביטוי GENERATED ALWAYS הוא רשות – ועוזר להבליט את העובדה שמדובר ב generated column – עבור קוראים עתידיים.

תזכורת חשובה: אם השאילתה עושה פעולה כלשהי על שדה מאונדקס (נאמר: state) – לא יהיה שימוש באינדקס. למשל:

…
WHERE my_state = json_unquote(state)

לא טוב!

השינוי הבא יאפשר שימוש באינדקס (כי את הערך “כפי שהוא” ניתן להשוות בינרית לאינדקס):
…
WHERE json_quote(my_state) = state
אבל הכי טוב זה להכין את האינדקס מראש בצורה הכי “טבעית” לשאילתה שאפשר – וכך לחסוך טעויות מרגיזות.

בגדול יש שני סוגים של generated columns:

  • Virtual – המחושב בזמן הגישה לרשומה.
    • בסיס הנתונים ישמור את הביטוי החישובי (expression) כ metadata על הסכמה, ובכל גישה לרשומה / הפעלת trigger – יתבצע החישוב מחדש. קונספט דומה מאוד ל “calculated field” במערכות BI.
    • אנו חוסכים מקום בדיסק – אבל מכבידים על ה CPU בכל גישה.
    • זהו ערך ברירת המחדל – אם לא ציינתם כלום.
  • Stored – הערכת הביטוי תבוצע בזמן יצירה / עדכון של הרשומה, וישמר לדיסק כמו עמודות אחרות.
    • יש מחיר בנפח אכסון, אך לרוב – הרבה פחות עבודת CPU.
    • זו הגישה הטבעית בעבודה עם json.

לצורך העניין generated column יכול לשמש לצרכים נוספים מלבד json.

למשל:

CREATE TABLE periods
id VARCHAR(32),
start_date_millis INT, # WARN: https://en.wikipedia.org/wiki/Year_2038_problem 
end_date_millis INT,   # WARN: https://en.wikipedia.org/wiki/Year_2038_problem 
start_date_sec AS (state_date_millis * 1000) VIRTUAL
;

יש כמה מגבלות על שימוש ב generated columns שכדאי להכיר. בביטוי של עמודה מחוללת לא ניתן להשתמש ב:

  • פונקציות לא דטרמיניסטיות – כאלו שיציגו ערכים שונים אם הופעלו פעם שניה. למשל: ()now או ()current_user.
  • sub-queries
  • Parameters / Variables או User Defined Functions.
  • עמודת AUTO_GENERATED או תכונת AUTO_INCREMENT.
  • שדה generated יכול להיות מבוסס רק על שדות generated המופיעים לפניו.
  • לא ניתן להשתמש על ה stored generated column באילוצים של FK מסוגי ON_UPDATE / ON_DELETE

עוד פרט מעניין: ניתן להשתמש באינדקס (משני) גם על virtual generated column מעל innoDB. הוא יהיה מסוג BTree בלבד (כלומר: לא FULLTEXT או GIS).

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

קצת יותר על השימוש בפונקציות ה JSON של MySQL

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

רקע: אני רוצה לבצע מיגרציה לשם של אלמנט בתוך מבנה json שאנו עושים בו שימוש. שם האלמנט היום הוא breakdown אבל השם לא כ”כ נכון – ואני רוצה לשנות אותו ל amounts. הנה דוגמה פשוטה למבנה ה json הקיים לפני השינוי:

[{“policyId”:”policy_id”,”lob”:”GL”,”breakdown”:{“PREMIUM”:{“amount”:423.17}}}]

וכך אני רוצה שמבנה יראה לאחר השינוי:

[{“policyId”:”policy_id”,”lob”:”GL”,”amounts“:{“PREMIUM”:{“amount”:423.17}}}]
איך לעזאזל ניגשים למיגרציה “עמוקה” בתוך שדה json? האם זה חייב להיות פרויקט מורכב?
ברור שלא.
אז הנה אני אעשה את השינוי בכמה שלבים פשוטים:
ראשית, אני רוצה ליצור שדות כפולים: גם breakdown וגם amounts – כי באופן טבעי בזמן deployment ירוצו 2 גרסאות של הקוד (גרסה חדשה שמחפשת אחר amounts וגרסה ישנה שמחפשת אחר breakdown). אני צריך להתכונן לכפילות הזו.
אני דואג לכך שבזמן ה deploy (כמה דקות) – לא יווצרו רשומות חדשות (במקרים אחרים ניתן למצוא אותן בעזרת זמן יצירה, ולתקן ידנית אח״כ).
בניית שאילתה של SQL היא עניין של ניסוי וטעיה, והדבר הזה נכון גם בעבודה עם  json.

אני מתחיל בשאילתה פשוטה של SELECT על מנת לראות אם קלעתי נכון ב query.
בחרתי לי רשומה שהמפתח שלה הוא ‘009277a371b8c3def40996a754085030’ על מנת לבצע את הניסויים ב scale קטן.

SELECT Json_insert(financial_attribution, ‘$[0].foo’, 2)
FROM   `payments`
WHERE  id = ‘009277a371b8c3def40996a754085030’;
במבט ראשון זה קצת מבלבל שאני משתמש ב ()JSON_INSERT בפקודת SELECT.
מה קורה כאן?
אני שולף שדה json בשם `financial_attribution` ואז מבצע עליו מניפולציה בזיכרון. המניפולציה שבחרתי היא הכנסה של ערך. מפתח מוזר בשם foo עם ערך של 2. רק לצורך הבדיקה.
הנה התוצאה:
[{“foo”: 2, “lob”: “GL”, “policyId”: “policy_id”, “breakdown”: {“PREMIUM”: {“amount”: 423.17}}}]
השדה נוסף בהצלחה (לי לקח ניסיון או שניים עד שזה עבד) – אך שום מידע לא השתנה בבסיס הנתונים.
עכשיו ננסה משהו יותר אמיתי:
SELECT Json_insert(financial_attribution, ‘$[0].amounts’,                    financial_attribution -> ‘$[0].breakdown’)
FROM   `payments`
WHERE  id = ‘009277a371b8c3def40996a754085030’;
לקחתי מתוך ה json את אובייקט ה breakdown – והכנסתי אותו בחזרה כ amounts:
[{“lob”: “GL”, “amounts”: {“PREMIUM”: {“amount”: 423.17}}, “policyId”: “policy_id”, “breakdown”: {“PREMIUM”: {“amount”: 423.17}}}]
אחרי שאני רואה שזה מצליח אני יכול להסיר את תנאי ה where ולראות את התוצאה על מגוון ערכים. מוודא שהכל תקין.
אנסה בצד דוגמה שגם שאילתת ה update תקינה:
UPDATE `payments`
SET    `financial_attribution` = Json_insert(`financial_attribution`, ‘$[0].amounts’,
financial_attribution -> ‘$[0].breakdown’)
;
ואז הוסיף אותה ל db migrations של השינוי.
אחרי שכל המיגרציה הסתיימה (בהצלחה), אני יכול למחוק את אובייקט ה breakdown מתוך ה json בעזרת פקודת  ()JSON_REMOVE. 
אפשרות אחרת היא פשוט להשאיר את הנתונים העודפים – אם הם לא מפריעים לנו.

עוד דרך מהירה (ולעתים יותר יעילה) לבצע תיקונים ב jsons היא בעזרת ()REPLACE פשוט על טקסט – אבל חשוב מאוד להיזהר מהחלפות לא-צפויות.

השלמות ליכולות ה json של MySQL 5.7

אם אתם זוכרים את הפוסט הקודם – הבטחתי לכם workarounds למחסור של MySQL 5.7 ב-2 פונקציות שימושיות בעבודה עם json.

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

קריאת הערך האחרון במערך שבתוך json

אני רוצה לקבל את השדה האחרון במערך שבתוך json:

SELECT JSON_EXTRACT(`from`,CONCAT(“$[“,JSON_LENGTH(`from`)-1,”]”)) FROM `table`;

זה קצת תרגיל: אני מוצא את אורך המערך (במקרה שלנו, תחת תכונה בשם from) ואז מרכיב שאילתה בעזרת ()CONCAT – ומריץ אתה. הנה קלט לדוגמה שעליו תעבוד השאילתה:

{ from: [“a”,”b”,”c”], “to”: [ “d”, “e” ] }

התוצאה תהיה ״c״.

הנה דוגמה ב DB Fiddle שאפשר קצת ״לשחק״ איתה:

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

גרסה מאולתרת ל json_each – טיפול בכל איבר במערך שבתוך json

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

הנה דוגמה לשאילתה כזו:

select  n,
JSON_EXTRACT(`data`, concat(‘$.items[‘, n – 1, ‘].price’)) as price
from `my_table`
join numbers on (n <= JSON_LENGTH(`data`->’$.items’) and n > 0)
;

כאשר התוצאה נראית כך:

n הוא מספר האיבר ברשימה (אינדקס על בסיס 1), ו price הוא… המחיר.

והנתונים נראים כך:

עשינו join עם טבלה של מספרים (0-255 לרוב מספיקה) ואז, עבור כל מספר באורך המערך של ה items – ביצענו פעולת שליפה מתוך ה json על המקום הזה, בהתבסס על ״תרגיל״ ה CONCAT שהשתמשנו בו קודם לכן.

הכי-אלגנטי-בעולם? – לא.
עובד ושימושי – כן!

הנה אתם יכולים לשחק ב fiddle שיצרתי לקוד הזה: https://www.db-fiddle.com/f/dmA8af4CHJ3xkx4fzV99Zw/0

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

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL
SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15
;
CREATE OR REPLACE VIEW numbers
AS SELECT ( hi.n * 16 + lo.n ) AS n
FROM generator_16 lo, generator_16 hi
;

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

אפשר למצוא את Fiddle ממקוד של ה generator על מנת ״לשחק״ איתו: https://www.db-fiddle.com/f/jCRetSiTaKqz5SUiQQG8Py/0

סיכום

טוב, נראה לי שהכנסו דיי מידע לפוסט יחיד.
מבחינתי הנושאים הללו מכסים יפה את העבודה ב MySQL (״הוסמכת – סרג׳יו״), ומכאן ניתן להמשיך הלאה, לנושאים מתקדמים אחרים ב MySQL.

שיהיה בהצלחה!

נושאים מתקדמים ב MySQL: חלק א’ – עבודה עם JSON

פוסטים בסדרה:
“תסביר לי” – גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א’ – עבודה עם JSON
נושאים מתקדמים ב MySQL: חלק ב’ – json_each  ו Generated Columns

MySQL הוא בסיס-נתונים פשוט.

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

גרסה 8 שיצאה לא מכבר הייתה עשויה להיקרא גרסה 5.8 – היא הוסיפה כמות נאה של תוספות, אבל בוודאי לא מהפיכה. (במיוחד לאחר שכמה שינויים זכו ל down-port לגרסה 5.7). לא ניתן להשוות אותה לחידושים של גרסה 5.

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

מקור: DB-engines.com

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

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

  • “בסיסי-נתונים רלציונים הם העבר”
  • “ל PostgresSQL יש יותר שיטות לבצע join – אז הביצועים שלו טובים יותר”
  • “בכנס חשוב-כלשהו היו 3 הרצאות על PostgreSQL ורק אחת על MySQL”
  • “ל MySQL אין רפליקציה טובה (כמו ל Mongo, לכאורה)”. “הוא לא בנוי ל Scale”.
  • “ל Postgres יש פי 3 יכולות מאשר ל MySQL”.
אלו דוגמאות לטיעונים לא לא עקרוניים. יש הרבה רצון לחדש ולעבוד עם “בסיס נתונים חדש יותר” – אבל גם המוכר והלא buzzy יכול להיות מוצלח מאוד.
נוכחתי לאורך הקריירה בכמה יוזמות אימוץ של “בסיס נתונים מתקדם יותר” – שנגמרו במפח-נפש.
שלא תבינו לא נכון: PostgreSQL ו MongoDB (ועוד אחרים) הם Databases מרשימים וטובים – אבל גם להם יש חסרונות, ואם אתם עושים מעבר – חשוב מאוד שתהינה לכן סיבות עמוקות ומבוססות. חבל להשקיע חודשים במעבר ואז לגלות שחיסרון חדש מעיב על כל המאמץ שהושקע. מעבר של בסיס נתונים במערכת “חיה” הוא שינוי לא-קל. הכלל הזה נכון גם לגבי מעבר ל MySQL – כמובן.
דיאגרמה (לא בהכרח מאלה) של Databases בתחום הרלציוני. מקור: 451 Research.

בכמה פוסטים הקרובים הייתי רוצה להתעמק קצת ביכולות של MySQL. יחסית לפופולריות השימוש שבו, MySQL לא “מסופר” בצורה טובה , כבר כמה שנים טובות. הרבה יכולות טובות נוספו בכמה שנים הללו – אך נראה שחלק נכבד מקהל השמתמשים לא מכיר אותן.
אמנם עכשיו עם השחרור של MySQL גרסה 8, הולכים ויוצאים ספרים חדשים – אך חלק גדול מהפוקוס שלהם הוא על מה שחדש בגרסה החדשה, ופחות במה שאפשר לעשות עם גרסה 5.7 – שהיא כנראה תישאר הגרסה הדומיננטית בשנתיים הקרובות (בסיס נתונים לא משדרגים כ”כ מהר – וטוב שכך).

הנה רשימת של נושאים שנראים לי מעניינים:

    • עבודה עם JSON וה Document Store.
    • Generated columns
    • פיצוי על יכולות חסרות ב MySQL כמו json_each או fist_value/last_value – איך אפשר להסתדר בלעדיהם.
    • מנועי Storage וההבדלים ביניהם: InnoDB ל MyISAM, וכו’ (לא חדש בכלל – אך ידע חסר, ממה שנתקלתי).
    • סטטיסטיקות של אינדקסים וטבלאות – ואיך זה משפיע עלינו (גם לא חדש).
    • Full Text indexes
  • Partitioning
  • Large Hadron Migrator- https://github.com/soundcloud/lhm, ביצוע migrations גדולים ללא downtime.
כל הנושאים הנ”ל הם נושאים שלי יצא באופן אישי, או לאנשים מסביבי להשתמש בהם. הם נושאים ישימים ופרקטיים – עד כמה שאני יכול לומר.

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

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

JSON וה MySql Document Store

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

  • בסיסי-נתונים רלציוניים
  • בסיסי-נתונים מבוססי-מסמכים (Document-Based)
* נכון, יש גם K/V DB שנמצאים בשימוש נרחב, וגם columnar, wide-column, וגם graph ו time series – אך עדיין בסיסי נתונים רלציוניים ו document-based אחראים למגוון הגדול יותר של הנתונים והשימושים.
ההרכב הנפוץ הוא שארגון מחזיק את רוב האובייקטים בבסיס נתונים רלציוני / מסמכים, והיכן שצריך scale גבוה יותר – פונה לפתרונות יותר ממוקדים לצורך הספציפי.

בסיסי הנתונים מבוססי המסמכים (כמו CouchDb, MongoDB) הציגו חזון מסעיר וחדשני, יחסית לבסיסי-הנתונים הרלציוניים – והיה נדמה שהם עומדים לכבוש את עולם בסיסי-הנתונים בסערה. הנה פוסט עתיק שלי על MongoDB (שלום, מונגו!)
המהפכה הזו לא קרתה כפי שחזו – אך היא בהחלט השפיעה על עולם בסיס הנתונים.
אנשי תוכנה רבים, החלו ליישם עקרונות של בסיסי-נתונים מבוססי-מסמכים על בסיסי-נתונים רלציוניים (הנה פוסט ישן נוסף, המתאר כיצד עושים זאת: עשה זאת בעצמך: NoSQL).
בסיסי הנתונים הרלציוניים, החלו לספק גם יכולות של ניהול מסמכים (או JSON snippets, לפחות. ה”מסמכים” הכי שימושיים), והיום יש יכולות Document כאלו ואחרות ברוב בסיסי-הנתונים הרלציוניים המוכרים. השילוב הזה – מאוד מוצלח, לטעמי.
מאז MySQL גרסה 5.7.12 (אמצע 2016, בעזרת plugin) יש ל MySQL ממשק עבודה שדומה מאוד לעבודה מול בסיס-נתונים מבוסס מסמכים, מה שנקרא The MySQL Document Store:
  • “מסמכים” (כלומר: JSON), מאוחסנים ב Collections.
    • מאחורי הקלעים, לאכסון collection של מסמכים, נוצרות טבלאות בנות שתי-עמודות id_ ו doc (כמו שהיינו עושים פעם, בעצמנו…)
  • בעזרת API (או ה shell החדש, mysqlsh) ניתן לגשת ל”מסמכים” ב API המוכר מבסיסי-נתונים מבוססי-מסמכים. למשל:
    • (“db.product.find(‘_id = “iPhone XI
    • (…)db.createCollection
    • (…)add(…), sort(…), modify, וכו’
  • את המסמכים ניתן לאנקס
    • מאחורי הקלעים MySQL יוצר generated columns – נושא שארצה לכסות בהרחבה.
  • ל API יש clients זמינים ל JavaScript ול Python – אם כי כמה פעולות, תחזוקה וטיפול בעיקר, עדיין יש לעשות ב SQL.
אף פעם לא “נפלתי” מהממשק של ה Documents Stores ולכן מעולם משך אותי לנסות ולהשתמש ב MySQL Document Store.
אני אישית מעדיף בהרבה לעבוד בסגנון מעורב (רלציוני-Document).

הייתי שמח מאוד להיות מסוגל לעשות מניפולציות על json ב js או פייטון המקונן בתוך שאילתת SQL – אך לצערי לא נראה שהשוק הולך לשם…
עדכון: תודה לנדב נווה שעדכון אותי שכן יש plugin ל User Defined Functions ב js עבור MySQL. מעניין!

עדיין, לא נראה שה plugin הזה נתמך ע”י AWS RDS. חבל…

JSON ב MySQL דרך SQL

column מטיפוס JSON הוסף ב MySQL גרסה 5.7.8 (אוג’ 2015), אם כי ניתן להשתמש ביכולות ה JSON שנוספו לבסיס הנתונים גם על גבי עמודות מסוג text, varchar, BLOB וכו’. עמודות טקסטואליות.

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

יתרון נוסף בשימוש ב”מסמך” JSON הוא האטומיות: הכל משתנה ביחד – הכל, או לא כלום.
בקבוצה של סכמות יהיה עליכם להתעסק עם טרנזקציות בכדי לקבל הגנה בפני 2 תהליכים שמשנים באופן לא-עקבי שני חלקים של האובייקט. טרנזקציות על עדכון של שורה של טבלאות (פעם ראיתי אובייקט שמופה ל 35 טבלאות שונות בבסיס הנתונים) – הוא לא דבר פשוט.

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

  • יש ב MySQL תחביר המאפשר לאמוד אם שדה x בתוך אובייקט o שבתוך ה JSON כחלק מפקודת WHERE.
    כמו שנראה בהמשך, כשמבנה ה json הוא מורכב יותר – זה הולך והופך להיות קשה יותר.
  • ביצועים: כאשר אנו רוצים להשוות שדה אחד מתוך אובייקט עם 50 שדות – עלינו לטעון לזיכרון את כל 50 השדות בכל פעם, שזה הרבה I/O מיותר (מדד חשוב מאוד בביצועים של בסיסי-נתונים).
    הגישה המקובלת להתמודד עם בעיית הביצועים היא להוציא לעמודות מקבילות לעמודת ה JSON “שכפול” של שדות אותן נרצה לתשאל בצורה תדירה (ולכן גם לאנדקס).
    בהמשך נראה כיצד MySQL יכול לסייע להפוך לעשות את התהליך הזה לפשוט יותר בעזרת Generated Columns.
טיפוס ה json ב MySQL שונה מ text בשתי תכונות עיקריות:

  1. בהכנסת ערך לעמודה מסוג json – בסיס הנתונים יוודא את תקינות ה format של ה json.
  2. בעמודה מסוג json ולא text – בסיס הנתונים ידחוס את ה json לפורמט בינארי דחוס יותר, בו המפתחות ממוינים (בדומה לפורמט bson שנעשה בו שימוש ב MongoDB).

json תקין הוא כמובן אובייקט ({}), מערך ([]), מחרוזת (“”), או null.

שווה לציין שמחרוזת ה json שהוכנסה לעמודה מסוג json לא תחזור כמחרוזת זהה: במעבר לפורמט בינארי ינקו את ה whitespace וסדר המפתחות ישתנה.
כמו כן, אם יש אובייקטים עם מפתחות “כפולים” – אזי המפתח הראשון הוא זה שישמר, עד לגרסה 8.0.3 ממנה המפתח האחרון הוא זה שישמר (מה שיותר עקבי עם רוב המימושים של javaScript).
כל עוד אנחנו עובדים עם json בצורה תקינה – זה פרט שלא נשים אליו לב.

טיפוס ה json של mySQL הוא optimized לקריאות, כך שאם אנחנו הולכים לכתוב יותר (למשל: audit) – יכול להיות שיהיה עדיף, מבחינת ביצועים, להשתמש בעמודה מסוג text.

הפקודה הבסיסית בעבודה עם json ב MySQL היא JSON_EXTRACT:

SELECT c, JSON_EXTRACT(c, “$.id”), g
FROM some_table
WHERE JSON_EXTRACT(c, “$.id”) > 1
ORDER BY JSON_EXTRACT(c, “$.name”);
+–––––––––––––––––––––––––––––––+–––––––––––+––––––+| c | c–>”$.id”| g |+–––––––––––––––––––––––––––––––+–––––––––––+––––––+| {“id”: “3”, “name”: “Barney”} |“3”| 3 || {“id”: “4”, “name”: “Betty”} |“4”| 4 || {“id”: “2”, “name”: “Wilma”} |“2”| 2 |+–––––––––––––––––––––––––––––––+–––––––––––+––––––+

יש גם תחביר מקוצר:

SELECT c, c->>’$.id’, g
FROM some_table
WHERE c->”$.id” > 1
ORDER BY c->’$.name’;
+–––––––––––––––––––––––––––––––+–––––––––––+––––––+| c | c–>”$.id”| g |+–––––––––––––––––––––––––––––––+–––––––––––+––––––+| {“id”: “3”, “name”: “Barney”} | 3 | 3 || {“id”: “4”, “name”: “Betty”} | 4 | 4 || {“id”: “2”, “name”: “Wilma”} | 2 | 2 |+–––––––––––––––––––––––––––––––+–––––––––––+––––––+

כאשר <<- הוא תחליף ל  ((JSON_UNQUOTE( JSON_EXTRACT(column, path. הפונקציה JSON_UNQUOTE מסירה את ה quotes – אם קיימים.

ניתן להשתמש בביטויים מורכבים יותר כמו ‘column->’$[2].person.pets[1].name
  • את כל הביטוי יש לעטוף במירכאות בודדות או כפולות – כי זו מחרוזת ב SQL.
  • יש לציין את ה $ – המתאר את ה root של ה json (לפי תקן ה json path – ה $ נקרא “context”).
  • כאשר יש שמות של keys המשתמשים בסימנים מסוימים – יש לעטוף אותם ב quotes, למשל:
    ‘column->’$[2].person.”my-pets”[1].name
  • ניתן להשתמש ב * בכמה מצבים:
    • [*]$ – יחזיר את כל האיברים במערך (או null אם הפעלתם על אובייקט או מחרוזת)
    • price.*.$ יחזיר מערך של כל שדות ה price בכל האובייקטים שבתוך העמודה.
    • price.**.$ יחזיר מערך של כל שדות ה price בכל האובייקטים, או תתי-האובייקטים, שבתוך העמודה.
  • יש פונקציות כגון ()JSON_KEYS ו ()JSON_SEARCH – שיחזירו בהתאמה את רשימת ה keys באובייקט, או רשימת האובייקטים המכילים ערכים מסוימים.
יש פעולות שלא ניתן להשיג בעזרת ה path כפי שמתאפשר היום ב MySQL 5.7.x. דוגמה נפוצה בשימוש: בחירת האיבר האחרון מתוך רשימה, או פעולות מיון / סינון על מפתחות מסוימים.
תמיד ניתן לעשות את הניתוח ברמה האפליקטיבית, שם ה json הוא “כחומר ביד היוצר” אם כי עבור שאילתות ניתוח /ואו כלי BI – עדיין יהיה שימושי מאוד להיות מסוגלים לעשות את כל הניתוחים בשפת SQL.
יכולות ה JSON של PostgreSQL הן מתקדמות יותר משל MySQL – אך נראה ש PostgreSQL הוא פשוט פחות סטנדרטי. מקור/2016.
ישנן עוד סדרה של פונקציות המאפשרות פעולות על json ב MySQL – אני רק אזכיר אותן בקצרה, בידיעה שתמיד אפשר לפנות לתיעוד (שהוא ברמה טובה):
  • יצירה של אובייקטי json כחלק משאילתה, בעזרת הפונקציות ()JSON_ARRAY(), JSON_OBJECT ו ()JSON_MERGE_PRESERVE.
  • שינוי של ה json מתוך SQL בעזרת הפונקציות:
    JSON_APPEND(), JSON_ARRAY_APPEND() JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET.
  • פונקציות עזר שימושיות הן:
    ()JSON_UNQUOTE(), JSON_QUOTE(), JSON_DEPTH(), JSON_PRETTY(), JSON_LENGTH(), JSON_TYPE ו ()JSON_VALID
פונקציה שלי מאוד חסרה ב MySQL אך קיימת ב PostgreSQL היא json_each ההופכת מערך או זוגות מתוך עמודת json לרשומות רלציוניות עליהן ניתן לבצע פעולות ב SQL שונות.
בפוסט המשך אני אראה “תרגיל” ב SQL בו אני משתמש בכדי לעשות זאת גם על MySQL.

הערה: יש פתרון לשליפת האיבר האחרון במערך ב MySQL 8 בצורת:

JSON_EXTRACT(JsonData, ‘$[last]’)
או שליפת האיבר לפני האחרון בעזרת last-1.
אני אראה גם “תרגיל” איך ניתן לעשות זאת גם בגרסה 5.7, וללא התמיכה של operator ה last.

סיכום

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

שיהיה בהצלחה!