אופטימיזציה בסיסית של MySQL

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

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

אופטימיזציה של אינדקסים

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

לא להשתמש ב-NULL

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

לא להגזים עם האינדקס

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

לא לאנדקס במקרה של שונות נמוכה

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

אינדקס של תחילית השדה במקום שדה שלם

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

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

CREATE TABLE t
(
    name CHAR(255),
    INDEX (name(15))
);

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

חישוב השונות באינדקס המלא נעשה באמצעות השאילתה הזו:
(name הוא שם הטור, t הוא שם הטבלה)

SELECT
    COUNT(*) AS 'Total Rows',
    COUNT(DISTINCT name) AS 'Distinct Values',
    COUNT(*) - COUNT(DISTINCT name) AS 'Duplicate Values'
FROM t;

חישוב השונות באינדקס החלקי נעשה כאן:
(name הוא שם הטור, t הוא שם הטבלה, n הוא אורך האינדקס)

SELECT
    COUNT(DISTINCT LEFT(name,n)) AS 'Distinct Prefix Values',
    COUNT(*) - COUNT(DISTINCT LEFT(name,n)) AS 'Duplicate Prefix Values'
FROM t;

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

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

אופטימיזציה של שאילתת SELECT

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

כאשר מחפשים מספר, השתמשו במספר

לא מעט פעמים אנו מחפשים id מסוים או מספר מסוים. אם המספר הזה נמצא (כמו שהוא צריך להיות) בטור שה-Data Type שלו הוא מסוג INT אז שימוש במספר בשאילתה יהיה יעיל הרבה יותר:

WHERE id = 18

WHERE id = '18'

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

שימוש בתבנית (LIKE) בזהירות

כאשר אנו משתמשים ב-LIKE אנחנו צריכים להיות מודעים לכך שבשאילתה כזו:

WHERE name LIKE '%de%'

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

WHERE name LIKE 'de%'

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

שימוש ב-LIMIT

שימוש ב-LIMIT בצירוף WHERE מאפשר ל-MySQL לחתוך תהליך שהביא את התוצאות שלו. השימוש ב-WHERE חשוב כיוון שאם אנו סתם נגביל את מספר התוצאות בשאילתה פשוטה, לא נביא לחסכון בביצועי השרת (אלא רק בתעבורה). אם אנו רוצים לחסוך בביצועי השרת ויש לנו בשאילתה WHERE, שימוש ב-LIMIT יחסוך גם תעבורה וגם ביצועים,
חסכון נוסף בתעבורה בלבד יכול להתבצע אם לא נשתמש ב-* ב-SELECT שלנו אלא בשם הטורים שאנו צריכים.

להמנע מפעילות שגורמות ל-MySQL לחשב כל ערך בנפרד

לעתים אנו משתמשים בשאילתות מורכבות שמחייבות את ה-MySQL לחשב כל ערך וערך בטבלה. נניח שאני רוצה לחפש את כל הרשימות שהערך שנוצרו משנת 1994 ומעלה. אני יכול להשתמש באופרטור YEAR:

SELECT * FROM t WHERE YEAR >= 1994;

מה הבעיה עם זה? ש-MySQL נדרש לחשב את ה-YEAR של כך ערך וערך. שאילתה נכונה יותר תהיה:

SELECT * FROM t WHERE d >= '1994-01-01';

כאו אין צורך לחשב את המידע והשאילתה תהיה יותר מהירה.

שימוש ב-EXPLAIN

פקודת EXPLAIN היא פקודה המסייעת לנו לדעת אם השאילתה שלנו יעילה או לא. משתמשים בה באופן הבא:

EXPLAIN SELECT * FROM t WHERE YEAR >= 1994\G

ואז יוצא הפלט הזה:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 867038
Extra: Using where

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

EXPLAIN SELECT * FROM t WHERE d >= '1994-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: d
key: d
key_len: 4
ref: NULL
rows: 70968
Extra: Using where

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

במאמר זה השתמשתי בדוגמאות והסברים מתוך MySQL 5 Certification Study Guide.

⚠️המדריכים כאן הם טעימה בלבד! יש ספר מקיף המלמד MySQL מרמת בסיס ועד רמה מתקדמת. הספר הדיגיטלי, שרלוונטי גם לכל מסדי הנתונים שעובדים עם SQL – זמין כאן כחלק מהפרויקט "ללמוד ג'אווהסקריפט בעברית" – זו הדרך הטובה ביותר ללמוד באופן מקיף MySQL.

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