אופטימיזציה בסיסית של MySQL – חלק ב'

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

בניית טבלאות זמניות

בואו ונניח מצב היפותטי. נניח ויש לי טבלה שאני משתמש בה בהרבה שאילתות כאשר אני מבצע לה JOIN. כלומר, אני לא מחפש בטבלה עצמה אלא פשוט משתמש בה כאשר אני מבצע JOIN. בואו ונניח שיש לי טבלה של יבשות (Continents) עם כל מיני נתונים על היבשות וטבלה של מדינות (Countries) עם כל מיני נתונים על המדינות.

נניח שאני רוצה להוציא נתוני מדינות שהתל"ג שלהן הוא פחות מאחוז מהתל"ג של היבשת שלהן. אם יש לי טבלת מדינות (country) עם נתון של היבשת שבה הן נמצאות (continent) והתל"ג שלהן (gnp) ואני ארצה למצוא את המדיניות העניות האלו, אני אצטרך להריץ שאילתה כזו:

SELECT
Country.Continent, Country.Name,
Country.GNP AS CountryGNP,
 FROM Country
INNER JOIN Continent
ON Continent.Continent=Country.Continent
 WHERE
Country.GNP < AVG(Continent.GNP) * .01
 ORDER BY Country.Continent, Country.Name;

ואם באותו עמוד יש גם דרישה לבדיקת המדינות העשירות – אלו שהתל"ג שלהן גבוה יותר מעשר אחוזים מהתל"ג של כלל היבשת שלהן? אני אצטרך להריץ שאילתה כזו:

SELECT
Country.Continent, Country.Name,
Country.GNP AS CountryGNP,
 FROM Country
INNER JOIN Continent
ON Continent.Continent=Country.Continent
 WHERE
Country.GNP < AVG(Continent.GNP) * .01
 ORDER BY Country.Continent, Country.Name;

אם לא נבהל מהשאילתות ונבחן אותן בעיון, אנו נראה שבכל אחת מהן יש JOIN של טבלה בשם Country לContinent. כלומר בכל שאילתא אני מבצע את אותו SELECT (שזה ה-JOIN) לאותה טבלה. בנוסף, בכל פעם אני מבצע חישוב של ממוצע באמצעות האופרטור AVG.

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

CREATE TEMPORARY TABLE ContinentGNP
SELECT Continent, AVG(GNP) AS AvgGNP
FROM Country GROUP BY Continent;

טבלה זו תשמר עד שנתנתק מהחיבור (בנוגע להתנתקות מהחיבור – ר' את MySQL ו-PHP). אם הטבלה לא גדולה (כמו במקרה שלנו, רק 5 יבשות) אפשר ליצור אותה גם עם מנוע Memory ואז בכלל מגיעים לתוצאות טובות מאד.

השאילתות במקרה כזה יראו כך:

SELECT
Country.Continent, Country.Name,
Country.GNP AS CountryGNP,
ContinentGNP.AvgGNP AS ContinentAvgGNP
 FROM Country, ContinentGNP
 WHERE
Country.Continent = ContinentGNP.Continent
AND Country.GNP < ContinentGNP.AvgGNP * .01
 ORDER BY Country.Continent, Country.Name;

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

אופטימיזציה של הכנסת/עדכון נתונים

כאשר אנו משתמשים ב-UPDATE או DELETE כדאי לזכור שכל הכללים שחלים על SELECT חלים גם על UPDATE – עדיף להשתמש באינדקסים כאשר אני מכניס את התנאי ב-WHERE, עדיף להשתמש במספר כאשר ה-Data Type הוא מספר וכו'. למרות שאין לנו EXPLAIN בשאילתת UPDATE, אפשר לכתוב את ה-UPDATE או את ה-DELETE כ-SELECT ואז לבצע EXPLAIN.

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

 INSERT INTO t (id, name) VALUES(1,'Bea');
 INSERT INTO t (id, name) VALUES(2,'Belle');
 INSERT INTO t (id, name) VALUES(3,'Bernice');

אפשר להשתמש ב:

INSERT INTO t (id, name) VALUES(1,'Bea'),(2,'Belle'),(3,'Bernice');

במנוע טבלה מסוג InnoDB אפשר לקבץ את כל פקודות ה-INSERT בטרנזקציה אחת:

  START TRANSACTION;
  INSERT INTO t (id, name) VALUES(1,'Bea');
  INSERT INTO t (id, name) VALUES(2,'Belle');
  INSERT INTO t (id, name) VALUES(3,'Bernice');
  COMMIT;

כאשר אנו מחליפים שורה, אפשר להשתמש בסינטקס REPLACE במקום DELETE ואז INSERT.

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

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

במאמר הבא אנו נדבר על דיבאגינג של MySQL.