אופטימיזציה בסיסית של 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.

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

אהבתם? לא אהבתם? דרגו!

לא אהבתי בכלללא אהבתיבסדראהבתיאהבתי מאוד (1 הצבעות, ממוצע: 5.00 מתוך 5)

תגיות: פורסם בקטגוריה: לימוד MySQL

אל תשארו מאחור! יש עוד מה ללמוד!

הצטרפו לעדכונים!

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

רן בר-זיק

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