mysql יצירת עמודה עבור ID שניתן להסתדר בלעדיו

חוקר

@חוקר אמר בmysql יצירת עמודה עבור ID שניתן להסתדר בלעדיו:

EXPLAIN ANALYZE SELECT COUNT(*) FROM LogPlaybackPlayStop WHERE ProjectID = 'tifertMoshe'

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

EXPLAIN ANALYZE
SELECT COUNT(*) FROM `LogPlaybackPlayStop` WHERE `ProjectID` = 'tifertMoshe'

תוצאה

-> Aggregate: count(0)  (actual time=562.230..562.231 rows=1 loops=1)
   -> Index lookup on LogPlaybackPlayStop using ProjectID (ProjectID='tifertMoshe')  (cost=78749.48 rows=698936) (actual time=0.048..336.836 rows=354415 loops=1)

השאילתה צרכה 53 אחוז CPU.

שאילתה

EXPLAIN ANALYZE
SELECT * FROM `LogPlaybackPlayStop` WHERE `ProjectID` = 'tifertMoshe' ORDER BY `EnterDate` DESC, `EnterTime` DESC LIMIT 2000

התוצאה

-> Limit: 2000 row(s)  (actual time=2140.300..2144.361 rows=2000 loops=1)
   -> Sort: LogPlaybackPlayStop.EnterDate DESC, LogPlaybackPlayStop.EnterTime DESC, limit input to 2000 row(s) per chunk  (cost=428549.39 rows=698936) (actual time=2140.295..2141.907 rows=2000 loops=1)
       -> Index lookup on LogPlaybackPlayStop using ProjectID (ProjectID='tifertMoshe')  (actual time=0.964..1718.924 rows=354415 loops=1)

צרך 97.2 אחוז CPU.
@yossiz
כעת נותר לפנח לי את התוצאות מה הן אומרות בהשוואה לטבלה בה יש מפתח ראשי ID

yossiz

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

לגבי ה-COUNT: אני לא יודע כרגע איך אפשר לשפר המהירות של השאילתה. למרות שיש לך אינדקס, אבל יש לך המון שורות על כל ערך באינדקס, אז תכלס צריך לעבור על כל ה-354415 שורות ולספור אותם שזה אומר המון קריאות דיסק.
אני הייתי עושה טבלה נפרדת ששומר את המספר, ומעדכן את זה על ידי trigger או משהו דומה. דוגמה פה: https://stackoverflow.com/a/34513196

לגבי ה-SELECT אני רואה מקום גדול לשיפור.
שים לב, אתה מבקש סינון על עמודת ProjectID ומיון לפי EnterDate ועוד.
גם הסינון וגם המיון יכולים להנות מהאינדקסים הקיימים. (שורות באינדקס תמיד ממויינים לפי העמודה שאתה מאנדקס). אבל לא שניהם ביחד.
כרגע mysql בחר להשתמש באינדקס של ה-ProjectID ולא באינדקס של ה-EnterDate. יש לזה הגיון מסויים, כי איך המנוע אמור לדעת שיש לך 354415 שורות שמתאימים לפילטר...
אבל! יש לזה משמעות עצומה במקרה שלך, כי כתוצאה מכך צריך לשלוף את ככללל ה-354415 שורות כדי למיין ולמצוא את ה-2000 הראשונים (כי LIMIT מתבצעת - לוגית - אחרי מיון).
אם השליפה היתה מתבצעת מאינדקס של ה-EnterDate, יש סיכוי שיהיה צריך לעבור על פחות שורות כדי לקבל את ה-2000 הראשונים של ה-ProjectID המבוקש. ויש גם סיכוי שלא... זה תלוי על צורת פיזור הפקרוייקטים בזמן (אם כל השורות מפוזרות בצורה שווה בזמן אז יהיה צריך לעבור על "רק" כ-140,000 שורות כדי לקבל את 2000 הראשונים של פרוייקט מסויים - כ-70 פרוייקטים * 2000 שורות).

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

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

CREATE INDEX `ProjectID_ValName` ON `LogPlaybackPlayStop`(`ProjectID`, `ValName`);
CREATE INDEX `ProjectID_EnterId` ON `LogPlaybackPlayStop`(`ProjectID`, `EnterId`);
CREATE INDEX `ProjectID_Folder` ON `LogPlaybackPlayStop`(`ProjectID`, `Folder`);
CREATE INDEX `ProjectID_EnterDate` ON `LogPlaybackPlayStop`(`ProjectID`, `EnterDate`);
CREATE INDEX `ProjectID_Current` ON `LogPlaybackPlayStop`(`ProjectID`, `Current`);

-- אם אתה לא צריך את האינדקסים הקודמים
DROP INDEX `ValName` ON `LogPlaybackPlayStop`;
DROP INDEX `EnterId` ON `LogPlaybackPlayStop`;
DROP INDEX `Folder` ON `LogPlaybackPlayStop`;
DROP INDEX `EnterDate` ON `LogPlaybackPlayStop`;
DROP INDEX `Current` ON `LogPlaybackPlayStop`;

חוקר

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

yossiz

@חוקר אמר בmysql יצירת עמודה עבור ID שניתן להסתדר בלעדיו:

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

בדיוק.

@חוקר אמר בmysql יצירת עמודה עבור ID שניתן להסתדר בלעדיו:

בנוסף אציין שבשאילתה לעיל הסינון של עמודת התאריך ועמודת השעה הינה רק בשביל המיון לפי תאריך.
זה אומר משהו?

לא הבנתי מה אתה מנסה לומר

חוקר

@yossiz אמר בmysql יצירת עמודה עבור ID שניתן להסתדר בלעדיו:

לא הבנתי מה אתה מנסה לומר

האם משנה לגבי האינדקס שעמודה זו משמשת למיון ולא לסינון

yossiz

@חוקר אמר בmysql יצירת עמודה עבור ID שניתן להסתדר בלעדיו:

האם משנה לגבי האינדקס שעמודה זו משמשת למיון ולא לסינון

עד כמה שאני יודע, לא.

חוקר

@yossiz אמר בmysql יצירת עמודה עבור ID שניתן להסתדר בלעדיו:

בדיוק.

מה הסדר המומלץ ולמה
ProjectID,EnterDate
או
EnterDate,ProjectID

yossiz

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

חוקר

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

EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM `Pupils` WHERE `ProjectID` LIKE 'tifertMoshe') AS Pupils LEFT JOIN (SELECT  `EnterId`
, MAX(IF(`EnterDate` = CURRENT_DATE ,co,0)) AS 'day_co', MAX(IF(`EnterDate` = CURRENT_DATE,SEC_TO_TIME(CONVERT(su, SIGNED)),0)) AS 'day_su'
,MAX(IF(`EnterDate` = DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY) ,co,0)) AS 'Yesterday_co', MAX(IF(`EnterDate` = DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY),SEC_TO_TIME(CONVERT(su, SIGNED)),0)) AS 'Yesterday_su'
, MAX(IF(YEARWEEK(`EnterDate`, 1) = YEARWEEK(CURRENT_DATE, 1) ,co,0)) AS 'week_co', MAX(IF(YEARWEEK(`EnterDate`, 1) = YEARWEEK(CURRENT_DATE, 1),SEC_TO_TIME(CONVERT(su, SIGNED)),0)) AS 'week_su'
, MAX(IF(YEARWEEK(`EnterDate`, 1) = YEARWEEK(DATE_SUB(CURRENT_DATE,INTERVAL 7 DAY), 1) ,co,0)) AS 'lest_week_co', MAX(IF(YEARWEEK(`EnterDate`, 1) = YEARWEEK(DATE_SUB(CURRENT_DATE,INTERVAL 7 DAY), 1),SEC_TO_TIME(CONVERT(su, SIGNED)),0)) AS 'lest_week_su'
, MAX(IF(MONTH(`EnterDate`) = MONTH(CURRENT_DATE) ,co,0)) AS 'month_co', MAX(IF(MONTH(`EnterDate`) = MONTH(CURRENT_DATE),SEC_TO_TIME(CONVERT(su, SIGNED)),0)) AS 'month_su'
, MAX(IF(MONTH(`EnterDate`) = MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 1 MONTH)) ,co,0)) AS 'lest_month_co', MAX(IF(MONTH(`EnterDate`) = MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 1 MONTH)),SEC_TO_TIME(CONVERT(su, SIGNED)),0)) AS 'lest_month_su'
, MAX(co) AS all_co
, SEC_TO_TIME(CONVERT(SUM(su), SIGNED)) AS all_su
FROM (SELECT `EnterId`, COUNT(`id`) AS co, SUM(`TimeTotal`) AS su, `EnterDate` FROM `LogPlaybackPlayStop` WHERE `ProjectID` LIKE 'tifertMoshe'  GROUP BY `EnterId`, `EnterDate`) AS t1 GROUP BY EnterId) AS t2 ON `Pupils`.`user_id` = `t2`.EnterId 
ORDER BY `class`, `Family`, `name`

בטבלה המקורית לקח הביצוע 2.651 שניות
וזה התוצאה

-> Nested loop left join  (actual time=2565.828..2568.774 rows=716 loops=1)
    -> Sort: Pupils.class, Pupils.Family, Pupils.`name`  (cost=812.76 rows=716) (actual time=9.546..9.867 rows=716 loops=1)
        -> Index range scan on Pupils using ProjectID, with index condition: (Pupils.ProjectID like 'tifertMoshe')  (actual time=0.423..4.044 rows=716 loops=1)
    -> Filter: (Pupils.user_id = t2.EnterId)  (actual time=3.573..3.574 rows=1 loops=716)
        -> Index lookup on t2 using <auto_key0> (EnterId=Pupils.user_id)  (actual time=0.002..0.002 rows=1 loops=716)
            -> Materialize  (actual time=3.573..3.573 rows=1 loops=716)
                -> Table scan on <temporary>  (actual time=0.001..0.513 rows=666 loops=1)
                    -> Aggregate using temporary table  (actual time=2553.656..2554.225 rows=666 loops=1)
                        -> Table scan on t1  (actual time=0.002..3.429 rows=16831 loops=1)
                            -> Materialize  (actual time=2454.324..2459.523 rows=16831 loops=1)
                                -> Table scan on <temporary>  (actual time=0.002..5.742 rows=16831 loops=1)
                                    -> Aggregate using temporary table  (actual time=2437.997..2445.624 rows=16831 loops=1)
                                        -> Index range scan on LogPlaybackPlayStop using ProjectID, with index condition: (LogPlaybackPlayStop.ProjectID like 'tifertMoshe')  (cost=717075.83 rows=641706) (actual time=138.827..1616.996 rows=354415 loops=1)

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

-> Nested loop left join  (actual time=1946.354..1956.694 rows=716 loops=1)
    -> Sort: Pupils.class, Pupils.Family, Pupils.`name`  (cost=812.76 rows=716) (actual time=5.865..6.479 rows=716 loops=1)
        -> Index range scan on Pupils using ProjectID, with index condition: (Pupils.ProjectID like 'tifertMoshe')  (actual time=0.252..1.879 rows=716 loops=1)
    -> Filter: (Pupils.user_id = t2.EnterId)  (actual time=2.718..2.721 rows=1 loops=716)
        -> Index lookup on t2 using <auto_key0> (EnterId=Pupils.user_id)  (actual time=0.003..0.003 rows=1 loops=716)
            -> Materialize  (actual time=2.715..2.717 rows=1 loops=716)
                -> Table scan on <temporary>  (actual time=0.002..0.549 rows=666 loops=1)
                    -> Aggregate using temporary table  (actual time=1937.403..1938.763 rows=666 loops=1)
                        -> Table scan on t1  (actual time=0.002..11.759 rows=16831 loops=1)
                            -> Materialize  (actual time=1842.499..1875.116 rows=16831 loops=1)
                                -> Table scan on <temporary>  (actual time=0.002..13.555 rows=16831 loops=1)
                                    -> Aggregate using temporary table  (actual time=1793.238..1827.118 rows=16831 loops=1)
                                        -> Index range scan on LogPlaybackPlayStop using ProjectID, with index condition: (LogPlaybackPlayStop.ProjectID like 'tifertMoshe')  (cost=838658.37 rows=733932) (actual time=79.989..1189.331 rows=354415 loops=1)

ולמען האמת מהשאילתה עם EXPLAIN ANALYZE אני עדיין לא כ"כ מבין את הלוגים, אבל בהרצת השאילתה עצמה ושליפת התוצאות אני רואה:
בטבלה המקורית 2.686
בטבלה לאחר השינוי 1.510

yossiz

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

תחומים - פורום חרדי מקצועי

mysql יצירת עמודה עבור ID שניתן להסתדר בלעדיו