"תסביר לי" - גרסת ה SQL

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

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

יש מי שאמר:

"EXPLAIN היא הפקודה החשובה ביותר ב SQL, אחרי הפקודה SELECT"


בבסיס נתונים אנחנו רוצים:

• לשמור נתונים (לאורך זמן, בצורה אמינה)
• לשלוף נתונים / לבצע שאילתות.

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

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

אפשר להחליט שזהו. "בואי שרהל'ה, אנחנו אורזים ועוברים - ל Cassandra".
מי אמר שעל קסנדרה יהיה טוב יותר?
כבר נתקלתי במקרים בהם גילוי שאילתה אטית לווה מיד בקריאות "ה DB איטי - בואו נעבור ל <השלם את החסר>!" - עוד לפני שבוצע ניתוח בסיסי. זו כנראה הגרסה המודרנית לקריאה "!A Witch! - burn her".

נתקלתי פעם בפרויקט שביצע מעבר ל Cassandra לאורך לשנה - ורק אז הסיק ש Cassandra היא no-go לצרכים שלו (הם היו זקוקים Graph Database... זו הייתה טעות בסיסית בזיהוי).

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

דוגמה בסיסית

אני מריץ את השאילתה הבאה, ואינני מרוצה מזמני הביצוע שלה.

SELECT `value` 
FROM `quote_job_execution_internals` 
WHERE `quote_job_id` = ( 
  SELECT `quote_job_id` 
  FROM `quote_job_execution_internals` 
  WHERE `key` = 'some key' AND `value` = '1290268' 
) AND `key` = 'other key' 
;

מה אני יכול לעשות?

• "שמעתי שאינטל הוציאו איזה SSD חדש מצוין לבסיסי נתונים"
• "לאמזון יש עכשיו מכונות X1e עם איזה 100 cores וטרות של זיכרון"

שניה!
בואו נתחיל בבקשה מבסיס הנתונים להסביר כיצד הוא מתכנן להריץ את השאילתה (להלן ה query plan):

EXPLAIN SELECT `value` 
FROM `quote...

התוצאה תראה משהו כזה:

אמנם זהו Query אחד - אך תוצאת ה Explain מציגה שורה עבור כל Select (בדוגמה שלנו: שניים).

ה Quickwin בטיפול ב Explain נמצא בשתי עמודות: key ו type

• key - באיזה אינדקס נעשה שימוש בכדי להגיע לנתונים. לרוב נרצה שיהיה שימוש באינדקס.
• נשאל: האם האינדקס מתאים?
• אולי חסר אינדקס?
• אולי תנאי ה WHERE מכיל 2 עמודות - אך האינדקס מכסה רק אחת מהן? (ואז כדאי לשנות אינדקס / להוסיף עמודות לאינדקס).
• לפעמים יש מקרים "לא צפויים" בהם האומפטימייזר בוחר לא להשתמש באינדקס. 
למשל: התנאי WHERE ref_number = 1023 לא גרם לשימוש באינדקס כי העמודה ref_number היא מסוג varchar. השאילתה תחזיר תשובה נכונה - אבל האופטימייזר מבצע השוואת טיפוסים ומפספס שיש אינדקס מתאים. שינוי התנאי ל 'WHERE ref_number = '1023 - יחולל שינוי דרמטי בזמני הריצה.
• דוגמה נוספת:  'WHERE Lower(some_column) = 'some_value...
  - לא ישתמש באינדקס על some_column (כי המידע לא מאונדקס ב lower_case) בעוד:('WHERE  some_column = Upper('some_value
  - דווקא כן. 
• type - כיצד טענו את הנתונים מהטבלה? גם כשיש שימוש באינדקס, יש הבדל אם סורקים את כל האינדקס, רשומה אחר רשומה, או מצליחים להגיע לערכים הנכונים באינדקס מהר יותר.
• בקירוב, אלו הערכים העיקריים שנראה, ממוינים מה"טוב" ל"רע":
• system / const - יש עמודה אחת רלוונטית לשליפה (למשל: טבלה עם רשומה בודדת).
• eq_ref - יש תנאי ב WHERE המבטיח לנו איבר יחיד באינדקס שהוא רלוונטי - אנו ניגשים לאיבר יחיד באינדקס. למשל: כאשר העמודה היא UNIQUE + NOT NULL.
• ref - אנו הולכים לסרוק איברים באינדקס כמספר הרשומות שנשלוף. הגישה לאינדקס היא "מדויקת".
• range - עלינו לסרוק טווח (או מספר טווחים) באינדקס - שכנראה יש בהם יותר איברים ממספר הרשומות שאנו עומדים לשלוף.
• index - יש צורך לסרוק את כל האינדקס. 
• ALL - יש צורך לסרוק את כל הרשומות בטבלה. 😱
• ייתכנו מקרי-קצה בהם index ו ALL - יהיו עדיפים על range או ref. אפרט אותם בהמשך.

רק להזכיר: האינדקס הוא "טבלה" רזה יותר המכילה רק עמודה בודדת (או מספר עמודות בודדות) מהטבלה - וממוינת ע"פ הסדר הזה. לא נדיר שאינדקס הוא רזה פי 10 ויותר מהטבלה (במיוחד אם בטבלה יש שדות מטיפוס varchar) - ואז גם סריקה מלאה של האינדקס הוא טעינה של (נניח) 10MB מדיסק - מול 100MB או יותר של נתונים שיש לטעון עבור סריקה מלאה של הטבלה.

מצד שני, גישה לאינדקס היא רק הקדמה לגישה לטבלה.

למשל: 20% מהרשומות עלולה בקלות להתמפות ל 50% מהבלוקים בדיסק בהם מאוחסנת הטבלה (במיוחד כאשר הרשומות קטנות). רזולוציית השליפה היא בלוקים - ולא רשומות בודדות.

יתרה מכך: שליפת כל הבלוקים של הטבלה כקובץ רציף תהיה לרוב מהירה יותר משליפת חצי מהבלוקים - כקבצים "רנדומליים".

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

זהו. אם רציתם לקבל 50% מה value בקריאת 20% מהפוסט - אתם יכולים לעצור כאן, ולהמשיך בעיסוקכם.

תזכורת

מידע נוסף שניתן לשאוב מתוך פקודת ה Explain

בואו נחזור לרגע למבנה ה output של הפקודה, ונעבור עליו סעיף אחר סעיף.

קבוצה #1: "איך הנתונים נשלפים"

שלושת העמודות הראשונות עוזרות לנו לקשר בין השורה ב output לחלק המתאים בשאילתה.
בד"כ תוצאה של explain תהיה שורה או שתיים - אך גם נתקלתי גם ב 6 או 7 שורות בשאילתות מורכבות במיוחד.

• Select Type הוא שדה זה נועד לאתר סוג ה SELECT שאליו מתייחסת השורה: SIMPLE - כאשר אין קינון שאילתות או איחוד שלהן, PRIMARY - השאילתה החיצונית ביותר (כאשר יש קינון) או הראשונה (ב UNION), יש גם SUBQUERY, UNION וכו'.
• מעבר למה שציינתי / מה שאינטואטיבי - חבל להשקיע בהבנת העמודה הזו. בהמשך אראה לכם מה עושים אם יש שאילתה מורכבת במיוחד.
• table - הטבלה שעליה המתבצע ה Select. זה יכול להיות שם של טבלה, או ביטוי כגון <Union <table1, table2.
• partition ה partition של הטבלה, במידה ויש כזה.
• partition היא היכולת להגדיר שחלקים שונים של הטבלה יאוחסנו על הדיסק בנפרד זה מזה (על מנת לשפר ביצועים בגישה לחלק המסוים). לדוגמה: כל הרשומות של שנת 2018 נשמרות בנפרד מהרשומות של שנת 2017 - למרות שלוגית זו טבלה אחת. התוצאה: שאילתות בתוך שנה בודדת יהיו מהירות יותר - על חשבון שאילתות הדורשות נתונים מכמה שנים.

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

סריקת index יכולה להיות יעילה יותר מ range או ref

שימוש באינדקס נעשה לרוב ב-2 שלבים:

1. האינדקס נסרק / נקרא, וממנו נשלפו מזהיי (primary key) הרשומות שיש לקרוא מהטבלה.
2. נעשית גישה נוספת לטבלה על מנת לטעון את ערכי הרשומות המתאימות.

אם בעמודה "Extra" (האחרונה) מופיע הערך "Using Index" משמע שלא היה צורך בשלב 2 - כי ערכי העמודות שביקשנו - נמצאו כבר באינדקס.

למשל: ביקשנו SELECT x FROM table1 WHERE y = 4

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


סריקת ALL עשויה להיות יעילה יותר מ index, range או ref

בהמשך להסבר של שני שלבי השליפה:

1. כאשר הטבלאות מאוד קטנות (KBs מעטים) - טעינת האינדקס ואז טעינת הטבלה - דורשת לפחות שתי גישות לדיסק.
2. במקרים כאלו עדיף כבר לטעון את תוכן הטבלה ("ALL") ולסרוק אותה. העלות העיקרית היא מספר הגישות לדיסק - ולא הסריקה בזיכרון.

קבוצה #2: "אינדקסים"

• possible_key - היא רשימת האינדקסים שמכילים את העמודות בתנאי ה WHERE / JOIN.
  האופטימייזר בוחר אינדקס ספציפי (זה שהופיע בעמודה key) ע"פ יוריסטיקות מסוימות - והוא עשוי גם לפספס.
• אם אתם משוכנעים שהוא לא בחר נכון (הוא טועה פחות ממה שנדמה לנו) אתם יכולים להנחות אותו באיזה אינדקס להשתמש בעזרת ההנחיה (USE INDEX (idx המגדילה את ציון האינדקס באלגוריתם הבחירה.
• אתם יכולים להשתמש גם ב  FORCE INDEX - אבל התוצאה עשויה להכאיב: מגיעה שאילתה (או אלפי מופעמים של שאילתה) שאין טעם באינדקס - אך האופטימייזר ישתמש באינדקס, כי אתם אמרתם.
• הנחיה אחרונה שימושית היא IGNORE INDEX - אתם יכולים לקרוא עוד בנושא כאן.
• key_len - נשמעת כמו עמודה משעממת עד חרפה, אך זה בכלל לא המצב!
• key_len עוזרת לאתר בזריזות אינדקסים "שמנים". 
• למשל: בתמונה למעלה יש key באורך 3003 בתים. כיצד זה ייתכן?
  
• כל Character ב Unicode היא 3 בתים (בייצוג של MySQL) אז 3*1000 = 3000 בתים. עוד שלושה בתים, כך נראה לי, מגיעים מכך שהשדה הוא Nullable (בד"כ Nullable מוסיף 1+ לגודל).
  כלומר: גודל האינדקס הוא כ 90% מגודל הטבלה.
• אם יש לי שאילתות הסורקות את כל האינדקס (type=index) - הרי שהאינדקס גורם לפי 2 נתונים להיטען מהדיסק, מאשר לו היינו פשוט סורקים את הטבלה.
• כשאינדקס הוא כ"כ גדול (3000 בתים!), ובמידה ונעשה שימוש תדיר באינדקס - שווה לחשוב על הוספת עמודה נוספת, רזה יותר, המכילה subset של תוכן העמודה המקורית (למשל: 50 תווים ראשונים) - ואת העמודה הזו נאנדקס. הפתרון הספציפי מאוד תלוי בסוג הנתונים והגישה אליהם.
• תובנה חשובה נוספת, שעמודת ה key_len יכולה לספק לי היא בכמה עמודות מתוך אינדקס-מרובה-עמודות נעשה שימוש. אם יש לי אינדקס של שני שדות מטיפוס INT (כלומר: ביחד 8 בתים), אך העמודה key_len מחזירה ערך 4 - סימן שנעשה שימוש רק בעמודה הראשונה שבאינדקס (שימוש באינדקס יכול להיעשות רק ע"פ סדר האינדקסים "שמאל לימין").
• לא קל להבחין במקרים הללו (צריך לחשב גדלים של שדות) - אך הם יכולים להעיד על בעיה בלתי-צפויה. למשל: תנאי שעוטף את ערך המספר במרכאות - וכך נמנע שימוש בשדה המשני של האינדקס. לכאורה: היה שימוש באינדקס - אבל שיפור שכזה עשוי לשפר את זמן הריצה פי כמה.
• נ.ב. שדה Int תמיד יהיה 4 בתים. ה "length" משפיע על מספר הספרות בתצוגה.
• אם אתם לא בטוחים מה מבנה הטבלה, תוכלו להציג אותו בזריזות בעזרת
    SHOW CREATE TABLE tbl_name 
• ref - איזה סוג של ערכים מושווים כנגד האינדקס. לא כזה חשוב.
• const הוא הערך הנפוץ, המתאר שההשוואה היא מול ערך "קבוע", למשל: ערך מתוך טבלה אחרת.
• NULL הכוונה שלא נעשתה השוואה. זה מתרחש כאשר יש Join ומדובר בטבלה המובילה את ה join.
• func משמע שנעשה שימוש בפונקציה. 

אם קיבלתם ערך ref=func, ואתם רוצים לדעת איזה פונקציה הופעלה, הפעילו את הפקודה SHOW WARNINGS מיד לאחר ה Explain. היא תספק לכם מידע נוסף (מה שנקרא בעבר "extended explain").

זוכרים שאמרתי לכם שאם יש שאילתה מורכבת מאוד, חבל לנסות ולשבור את הראש איזו שורה ב Explain מתאימה לאיזה חלק בשאילה? 

כאשר אתם קוראים ל Show Warnings אתם גם מקבלים את הגרסה ה "expanded" של השאילתה.

/* select#1 */ SELECT `quote_job_execution_internals`.`value` AS `value` 
FROM  `quote_job_execution_internals` 
WHERE  ( ( `quote_job_execution_internals`.`quote_job_id` 
           = ( /* select#2 */ SELECT            `quote_job_execution_internals`.`quote_job_id` 
           FROM   `quote_job_execution_internals` 
           WHERE  ( (            `quote_job_execution_internals`.`key` = 'nimiGlPolicyId' ) 
           AND (`quote_job_execution_internals`.`value` = '1290268' ) )) ) 
           AND ( `quote_job_execution_internals`.`key` = 'storageId' ) ) 

הסימון בהערות ("select#1" ו "select#2") הוא הדרך הקלה לזהות אלו חלקים בשאילתה מתייחסים לאלו שורות ב Explain. 

קבוצה #3: כמות הרשומות

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

• עמודת ה rows מספקת הערכה כמה נתונים ייסרקו. השאילתה עדיין לא רצה - אז לא ניתן לדעת.
• במקרה שלנו, כאשר ה subquery עתיד לסרוק 2 שורות, וה primary select עתיד לסרוק 6 שורות - אנו מצפים לסריקה של 6 * 2 = 12 שורות, וזה מספר קטן. 
• אם יש לנו 3 שאילתות מקוננות, וכל אחת סורקת 50 שורות - אזי נגיע סה"כ לסריקה של עד 125,000 שורות - וזה כבר הרבה!
• עמודת ה filtered מספקת הערכה (לא מדויקת) באיזה אחוז מהרשומות שנסרקו באינדקס - ייעשה שימוש. 
• בדוגמה למעלה קיבלנו הערכה שנטען 6 או 2 רשומות, וב "10%" מהן יעשה שימוש, כלומר: באחת.
• כאשר הערך הוא נמוך מאוד (פחות מאחוזים בודדים, או שבריר האחוז), ובמיוחד כאשר מספר הרשומות הנסרק (rows) הוא גבוה - זהו סימן שהשימוש באינדקס הוא לא יעיל. ייתכן ואינדקס אחר יכול להתאים יותר?
• השאיפה שלנו היא להגיע ל rows = 1 ו filtered = 100% - זהו אינדקס המנוצל בצורה מיטבית!

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

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

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

הפקודה OPTIMIZE TABLE your_table גורמת ל MySQL לבצע דחיסה (סוג של defrag) על הקבצים של הטבלה - ואז להריץ Analyze Table. זה ייקח יותר זמן - ולכן מומלץ להריץ את הפקודה הזו רק "בשעות המתות".

קבוצה #4: Extra


עמודת האקסטרא בד"כ תאמר לכם "Using Where" (דאא?), אבל יש כמה ערכים שהיא יכולה לקבל שדווקא מעניינים:

• Using Index - ציינו את המקרה הזה למעלה. נעשה שימוש באינדקס בלבד על מנת לספק את הערכים (מצוין!)
• Using filesort - מכיוון שלא הצליח לבצע מיון (ORDER BY) על בסיס אינדקס ו/או כמות הנתונים גדולה מדי - בסיס הנתונים משתמש באלגוריתם בשם filesort בכדי לבצע את המיון. בסיס הנתונים ינסה להקצות שטח גדול בזיכרון ולבצע אותו שם - לפני שהוא באמת משתמש בקבצים. 
• פעמים רבות אפשר להימנע מ filesort ע״י אינדקס על העמודה לפיה עושים מיון. 
• [עודכן] כמובן שבאינדקס הכולל כמה שדות - רק השדה הראשון (ה״שמאלי״) שימושי למיון, או לחלופין אם סיננו ע"פ האינדקס (WHERE) - השדה השמאלי לשדה/שדות על פיהם נעשה הסינון (תודה לאנונימי על ההערה).
• אם יש ORDER BY על יותר מעמודה אחת - שימוש באינדקס יהיה יעיל רק אם האינדקס מכיל את השדות לפיהם ממיינים בסדר הנכון.
• Using temporary - מקרה דומה: יש פעולה גדולה (בד״כ GROUP BY) שלא ניתן לבצע בזיכרון - ולכן משתמשים בטבלה זמנית (בזיכרון). גם זה מצב שכדאי לנסות להימנע ממנו.
• בד"כ אין פתרונות קלים למצב הזה: לנסות ולהסתדר ללא GROUP BY? לבצע את פעולת הקיבוץ אפליקטיבית בקוד? (כמעט תמיד custom code שנכתב בחכמה ינצח את בסיס הנתונים בביצועים - אבל יהיה יקר יותר לפיתוח ותחזוקה)

הנה הרפרנס לערכים האחרים האפשריים לעמודה Extra

אחרית דבר

לאחר הפוסט הזה אתם אמורים להבין את תוצאות הפקודה EXPLAIN בצורה דיי טובה!

חשוב להזכיר, ש Explain מבצע הערכה לגבי אופן ביצוע עתידי - ולא מידע על הביצוע בפועל.
ניתוח ההערכה הוא הכלי השימושי ביותר לשיפור ביצועי שאילתות - אך לא היחיד.

שווה להזכיר את ה Slow Log וה Performance Schema שעוזרים לאתר בכלל את השאילתות הבעייתיות.
את Profile ו Trace (+ מידע שניתן לשלוף מה Performance Schema) - שבעזרתם ניתן לבחון כיצד השאילתה רצה בפועל.

הפרמטר  format=json (כלומר EXPLAIN format=json SELECT something) גורם ל Explain לספק פירוט עשיר יותר (ובפורמט json).

יש גם עוד הנחיות שונות שניתן להוסיף בשאילתה על מנת להנחות את האופטימייזר לפעול באופן מסוים (למשל SQL_BIG_RESULT או STRAIGHT_JOIN), או אפילו הנחיות הנובעות מהחומרה שבשימוש (להלן טבלת ה mysql.server_cost , וה optimizer_switch)
אבל אני חושב שאת תחום זה מוטב להשאיר ל DBAs...




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