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

חוקר

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

CREATE TABLE `LogPlaybackPlayStop` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ProjectID` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `Folder` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `Current` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `Phone` varchar(14) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `IdType` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `ValName` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `EnterId` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `EnterHebrewDate` varchar(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `EnterDate` date DEFAULT NULL,
 `EnterTime` time DEFAULT NULL,
 `PositionPlay` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `PositionStop` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `ExitTime` time DEFAULT NULL,
 `TimeTotal` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `ProjectID` (`ProjectID`),
 KEY `ValName` (`ValName`),
 KEY `EnterId` (`EnterId`),
 KEY `Folder` (`Folder`),
 KEY `EnterDate` (`EnterDate`),
 KEY `Current` (`Current`)
) ENGINE=InnoDB AUTO_INCREMENT=10065876 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

יש כאן כמה אינקסים, החשוב שבהם הוא ProjectID שזה עבור הסינון לאיזה לקוח/פרוייקט השורה מיועדת (ישנם כ70 ערכים נכון לעכשיו).
ובנוסף תאריך, טלפון, מזהה ועוד, שגם עליהם יצרתי אינדקס כיון שהלקוחות יכולים למיין ולחפש על פי העמודות (עשיתי רק על אלו שיש עליהם יותר הסתברות למיין/לחפש על פיהם.
למעשה אני יכול ליצור מזהה ע"י שילוב של פרוייקט + טלפון + תאריך ושעה, או שילוב אחר שייצור לי מזהה יחודי.
בממשק שלי, כל לקוח שנכנס לטבלה מוצגים לו מיד רק השורות שלו, על ידי סינון לפי ProjectID, ובמקביל הוא יכול לחפש בתוך השורות שלו.
כל שניתן לומר שתמיד מבוצע גם חיפוש על פי ProjectID.
אבל שמתי לב שזה צורך הרבה CPU, כל כניסה של לקוח לטבלה יוצרת שאילתות אלו:

SELECT COUNT(*) FROM `LogPlaybackPlayStop` WHERE `ProjectID` = 'tifertMoshe'
SELECT * FROM `LogPlaybackPlayStop` WHERE `ProjectID` = 'tifertMoshe' ORDER BY `EnterDate` DESC, `EnterTime` DESC LIMIT 2000

וזה צורך לחצי שניה כמעט 100 אחוז (מתוך 200) של הCPU.
השאלה היא באם אני ימחוק את עמודה ID, ואשנה את המזהה כנ"ל, כמה זה משמעותי?
האם יש למישהו נסיון בזה?
אני כותב את הנ"ל על פי מה שראיתי כאן:
https://stackoverflow.com/a/51010325

OdedDvir

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

yossiz

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

וזה צורך לחצי שניה כמעט 100 אחוז (מתוך 200) של הCPU

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

שים את זה לפני השאילתה.
EXPLAIN ANALYZE

חוקר

@yossiz
אני מצוטט:

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

ה CPU קפץ ל30 אחוז (לעומת 1-3 לפי ואחרי).
התוצאה:

-> Aggregate: count(0)  (actual time=347.026..347.027 rows=1 loops=1)
   -> Index lookup on LogPlaybackPlayStop using ProjectID (ProjectID='tifertMoshe')  (cost=72277.48 rows=641706) (actual time=0.080..303.072 rows=354415 loops=1)

שליפת השורות:

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

ה CPU קפץ ל 100.
וזה התוצאה:

-> Limit: 2000 row(s)  (actual time=2294.114..2297.032 rows=2000 loops=1)
   -> Sort: LogPlaybackPlayStop.EnterDate DESC, LogPlaybackPlayStop.EnterTime DESC, limit input to 2000 row(s) per chunk  (cost=392030.59 rows=641706) (actual time=2294.113..2296.869 rows=2000 loops=1)
       -> Index lookup on LogPlaybackPlayStop using ProjectID (ProjectID='tifertMoshe')  (actual time=0.533..1787.849 rows=354415 loops=1)

חוקר

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

שים את זה לפני השאילתה.
EXPLAIN ANALYZE

אני רואה שזה נתמך רק בגרסה 8.0 ולא בקודמים.
שם נתמך רק EXPLAIN ולא EXPLAIN ANALYZE.
כי רציתי לבדוק על שרת אחר ששם עשיתי ניסוי להוריד את העמודה ID , ושם עוד לא שדרגתי ל 8.0.
ואגב אציין ששדרגתי מ5.7 ל 8.0 ללא בעיות ב"ה.
עשיתי זאת על פי המדריך כאן
https://askubuntu.com/a/1163033
רק שבסוף ההתקנה זה שאל שהוא מזהה שקובץ ההגדרות שונה מהמקורי/החדש, ומה ברצוני לעשות, זה נתן מספר אפשרויות, ואחת מהם זה ליצור מחדש והשני להשאר העם הישן.
עשיתי נסיון להישאר עם הישן וזה נתקע לגמרי. (עשיתי ראשית נסיונות על שרת טסטים שהעליתי מתוך תמונת מערכת של השרת בפועל).
מחקתי את המופע ויצרתי שוב מופע ובחרתי באפשרות ליצור את הקובץ החדש, ואז השדרוג הסתיים ללא בעיות ב"ה.
@שואף - היה נידון על כך..

חוקר

@חוקר אמר ב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 שניתן להסתדר בלעדיו