נושאים מתקדמים ב 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 בצורה תכופה. #לא_אקטואלי.

-----

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

• סקירה מקיפה של יתרונות חסרונות ויתרונות של GUID מול AutoIncrement.
• ניהול BLOBs ב InnoDB
• סדרת מאמרים על ה InnoDB Internals
• Geographic Indexes
• Pattern Matching vs. Full Text Search