הגדרת מחיצות טבלאות ב-MySQL: RANGE, LIST, HASH

מדוע partitionning של MySQL מפסיק לעזור

partitionning של MySQL לפי RANGE לפי תאריך — סכמה עובדת בדיוק עד לרגע שבו השאילתות מתחילות לעבור על כל הטבלה. כל עוד תנאי WHERE מכיל את מפתח ה-partitionning, האופטימיזר חותך את המקטעים המיותרים וקורא מגזע אחד או שניים.

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

נקודת הכשל הטיפוסית השנייה — ניקוי נתונים היסטוריים. DELETE FROM ... WHERE created_at < ... על טבלה גדולה מתבצע במשך שעות, מחזיק נעילות, מנפח את ה-undo log ואת הפיגור של הרפליקות.

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

הכשל השלישי — אסטרטגיה שגויה. HASH ו-KEY נותנים פיזור אחיד, אבל לא נותנים חיתוך לפי טווח תאריכים: ניקוי תקופות ישנות הופך לאותה פעולה המונית. LIST נוח לערכים בדידים, אולם הוספת ערך חדש מחייבת בנייה מחדש של המקטע.

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

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

מה נותנת סכימת חלוקה נכונה של טבלאות

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

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

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

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

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

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

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

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

RANGE, LIST, HASH ומפתחות מורכבים: מה לבחור

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

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

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

מצפן מעשי: אם 90% מהשאילתות מסננות לפי תאריך — לוקחים RANGE, אם לפי קטגוריה — LIST, אם אין סינון וחשוב הפיזור האחיד — HASH. מפתח מורכב מוסיפים מעל כל מצב, כאשר יש צורך לשמור על ייחודיות לפי כמה שדות.

כיצד אנו מיישמים מחיצות: שלבי העבודה

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

1. ביקורת סכימה ושאילתות. בוחנים את מבנה הטבלאות, האינדקסים, המפתחות הזרים, הטריגרים והתצוגות התלויות בה. אוספים לוג איטי וסטטיסטיקת ביצוע: אילו שאילתות קוראות מהטבלה, עם אילו תנאים ב-WHERE, והאם קיימות אגרגציות על כל הטבלה.
2. פרופיל עומס ובחירת מפתח. קובעים את התפלגות הנתונים לפי מועמד למפתח מיחוּל — בדרך כלל תאריך או טווח מזהים. בודקים שרוב השאילתות הכבדות מסננות בדיוק לפי שדה זה, אחרת אין טעם בחלוקה.
3. תכנון סכימת המקטעים. בוחרים סוג: RANGE לפי תאריך עבור יומנים והיסטוריה, LIST עבור קבוצת ערכים קבועה, HASH עבור התפלגות אחידה ללא גבול טבעי. מחשבים את מספר המקטעים והגבולות כך שכל אחד יחזיק נפח דומה.
4. הגירה. מעבירים את הטבלה למבנה החדש עם חלון אי-זמינות מינימלי: מכינים מקטעים מראש, מעבירים נתונים במנות, מסנכרנים שינויים שנצברו בזמן ההעברה, ורק אז מעבירים את האפליקציה לטבלה החדשה.
5. בדיקה על עותק. מריצים את ההגירה על עותק מלא של הפרודקשן: משווים סכומי ביקורת, תוכניות ביצוע לפני ואחרי, וזמן תגובה של שאילתות מפתח. מוודאים שניפוי המקטעים אכן עובד ולא נשאר על הנייר.
6. הגדרת תחזוקה. קובעים הוספה סדירה של מקטעים חדשים ומחיקה של מיושנים — באמצעות אירוע מתזמן או משימה חיצונית. הישן נמחק בניפוי מקטע, ולא ב-DELETE המוני שמחזיק נעילות ומנפח את היומן.
7. מסירת נהלים. מוסרים תיעוד: מפת מקטעים, כללי הוספה ומחיקה, סדר rollback, נקודות בקרה. צוות הלקוח ממשיך לנהל את הטבלה בעצמו, בלעדינו.

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

מה הלקוח מקבל לידיו לאחר העבודה

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

• קונפיגורציות partition — סקריפטי DDL מוכנים ליצירה ולבנייה מחדש של מחיצות לפי האסטרטגיה הנבחרת (RANGE לפי תאריכים, LIST לפי טבלאות עזר, HASH/KEY לפיזור אחיד), עם הערות מדוע נבחר דווקא מפתח partition זה.
• סכמת partition ומפת נתונים — מסמך שבו רואים איזו טבלה כיצד מחולקת, לפי איזה שדה מתבצע חיתוך מחיצות, ואילו אינדקסים חייבים לכלול את מפתח ה-partition.
• מדיניות שמירה — כללי מחזור חיים: כמה מחיצות מחזיקים "חמות", מתי מיושנות עוברות לארכיון, ומה עושים עם נתונים היסטוריים במקום מחיקה.
• סקריפטי תחזוקה — יצירה אוטומטית מראש של מחיצות עתידיות, ניתוק וארכוב של ישנות, בדיקת שלמות, וכן עטיפות להרצה לפי לוח זמנים.
• ניטור והתראות — מטריקות לפי מספר מחיצות, גודל, והאם שאילתות נכנסות ל-partition pruning, עם התראות בהתקרבות לגבולות.
• נוהל תפעול — הוראות שלב-אחר-שלב: כיצד להוסיף מחיצה ידנית, כיצד לבצע rollback לבנייה מחדש שכשלה, ומה לעשות כשהטבלה גדלה מהר מהתחזית.
• דוח בדיקה — מדידות לפני ואחרי על שאילתות אמיתיות: זמן הריצה של השאילתות, תוכנית הביצוע, ונפח הנתונים הנסרקים.

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

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

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

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

מה שינינו

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

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

התוצאה במספרים

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

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

במה שונה מחיצה (partitioning) מפיצול (sharding)?

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

ה-DBMS עצמו חותך את המקטעים המיותרים לפי המפתח (partition pruning), ושאילתה לפי טווח תאריכים קוראת רק את המקטע שלה במקום את כל הטבלה. פיצול הוא הפצת הנתונים על פני כמה שרתים בלתי תלויים, כאשר כל צומת מאחסן את חלקו בשורות ומשרת את זרם השאילתות שלו.

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

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

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

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

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

ספקות טכניים נפוצים בעת הטמעת מחיצות

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

כיצד זה ישפיע על הרפליקציה?

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

האם יהיה צורך לשכתב את האפליקציה?

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

האם ניתן לבטל את השינויים?

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

אילו סיכונים נותרו?

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

בואו נדון במשימת Partitioning של MySQL שלכם

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

מה כדאי להכין לפני השיחה:

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

לפי נתונים אלה אנחנו קובעים את סוג ה-partitioning המתאים — RANGE לפי תאריך, LIST לפי קטגוריה או HASH לפי מפתח הפצה — ובודקים אם החלוקה מתנגשת עם אינדקסים ייחודיים קיימים. לאחר מכן מכינים תוכנית מיגרציה: בנייה מחדש ללא נעילה ממושכת, סדר המעבר, נקודות rollback.

תארו את המשימה — נחזור עם הערכה, תוכנית עבודה ומועדי התחלה.