עד עכשיו עשיתי בעיקר benchmark של PHP.
עכשיו אני רוצה להתחיל עם SQL בסביבת MYSQL, כיוון שזה תופס את רוב המשאבים באפליקציה.
כיוון שאין לי הרבה רעיונות ל-benchmark של SQL, אשמח אם תביאו לי כמה ...

הרצתי 2 שאילתות שאמורות לבצע פעולה דומה בדיוק אותו הדבר.

האחת באמצעות MAX, והאחרת באמצעות ORDER BY col LIMIT 1.
את הבדיקות עשיתי כאשר הטור, שעליו בוצעה הבדיקה, היה טור INDEX ופעם אחרת כאשר הוא לא היה INDEX.
הטבלה היא MyISAM.
את השאילתות הרצתי דרך mysql driver של PHP, גירסא 5.0.51a.
MySQL גירסא 5.1.25.

השאילתות:

קוד:

# The MAX() query:
SELECT MAX(`col_name`) AS max_value FROM `tbl_name`

# The ORDER BY...LIMIT query:
SELECT `col_name` FROM `tbl_name` ORDER BY `col_name` DESC LIMIT 1

התוצאות הסופיות (לאחר חישוב ממוצע):

קוד:

When the columm IS an index
-----------------------------

MAX():			0.00049078464508057
ORDER BY & LIMIT 1:	0.00054395198822022
_______
MAX() is 111% faster


When the columm is NOT an index
-----------------------------

MAX():			0.016632278760274
ORDER BY & LIMIT 1:	0.021290977795919
_______
MAX() is 128% faster

מסקנה - MAX תמיד עדיף.
הבעיה היא שזה לא SQL portable...

קובץ התוצאות המפורט:
MAX()_VS_ORDER_BY_LIMIT_results.txt

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

ציטוט:

יש אין אינדקסים? CLUSTERED?

צריך לפרט על אינדקסים שלא כלולים בבדיקה?
יש רק אינדקס נוסף שהוא PRIMARY KEY, טור בשם `id`.

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

ציטוט:

כמה שורות יש בטבלה וכמה בלוקים היא והאינדקסים תופסים?

סה"כ בטבלה יש 4 טורים.
סה"כ מספר השורות 26,921.

למה הכוונה בבלוקים?

ציטוט:

מה הפיזור של הערכים בטור הנבחר? מה הEXECUTION PLAN לכל אחת מהשאילתות והאם הוא שונה? אם כן, למה?

אין לי שמץ, אם תוכל לפרט קצת אשמח

ה EXECUTION PLAN מראה מה מסד הנתונים עושה מאחורי הקלעים על מנת לשלוף את הנתונים שביקשת.
למשל, באילו אינדקסים הוא משתמש ובאיזה אופן הוא קורא את הנתונים.

מספר הבלוקים חשוב על מנת להבין עד כמה כדאי לקרוא את הטבלה כולה לזיכרון
(אם הוא נמוך מספיק, אזי שבעלות I/O קטנה אפשר למיין את הנתונים בזיכרון ביעילות)

ואם הוא גדול מספיק, גם האינדקס יתפרש על מספר בלוקים ויאלץ את מסד הנתונים לבצע I/O רב יותר.


פיזור הערכים משפיע על יעילות האינדקס
אם קיימים רק שני ערכים בלבד לכל הרשומות שלך (לדוגמא עמודה של "זכר"/"נקבה")
כמות הנתונים שתקרא מהאינדקס תהיה רבה יותר מאשר באינדקס של ערכים ייחודיים.

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

ולגבי המסקנה - היא לא מפתיעה כמובן - במיוחד כשמדובר בעמודה עם אידקס
פונקצית ה MAX ( וגם MIN) צריכה רק לקרוא את הבלוק הראשון של האינדקס (או האחרון)

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

_________________________________________________

אזהרה: משרד הבריאות קובע כי העישון מזיק לבריאות !
תראו, אפילו החייזר נהיה ירוק מזה

מנהל פורום מדע בדיוני ופנטסיה ופורום מדע טכנולוגיה וטבע בפרש

תודה על ההסבר

עשיתי קצת קריאות בנוסף...

לגבי ה-EXECUTION PLAN הבנתי, אבל תוכל להמליץ לי על איזה דרך טובה שאתה מכיר כדי שאוכל לדעת מהי ה-execution plan? אני משתמש ב-MySQL כפי שכתבתי. כיצד אדע את ה-query plan?

ציטוט:

מספר הבלוקים חשוב על מנת להבין עד כמה כדאי לקרוא את הטבלה כולה לזיכרון (אם הוא נמוך מספיק, אזי שבעלות I/O קטנה אפשר למיין את הנתונים בזיכרון ביעילות) ואם הוא גדול מספיק, גם האינדקס יתפרש על מספר בלוקים ויאלץ את מסד הנתונים לבצע I/O רב יותר.

ניסיתי לחפש מידע על ה-I/O, אבל אני לא בטוח כיצד זה קשור..?
האם הכוונה היא לקריאות (read) של ה-DBMS מהכונן הקשיח?
עד היום, יצא לי לשמוע על הביטוי I/O בהקשר של שתי מערכות שמתקשרות (מקלדת ומחשב ; או 2 נקודות קצה).
בנוגע לבלוקים, הבנתי שהם סה"כ חלקים קטנים יותר של כל המידע שבטבלה. איך אני יודע כמה בלוקים יש? האם אפשר לראות זאת ב-PMA ? (ר"ת phpMyAdmin)

ציטוט:

פיזור הערכים משפיע על יעילות האינדקס אם קיימים רק שני ערכים בלבד לכל הרשומות שלך (לדוגמא עמודה של "זכר"/"נקבה") כמות הנתונים שתקרא מהאינדקס תהיה רבה יותר מאשר באינדקס של ערכים ייחודיים.

מצאתי את זה בתור cardinality ב-PMA. בבדיקה שעשיתי היה לי cardinality של 640 באינדקס counter. כדי לדעת אם זה גבוה\נמוך\בינוני - האם אני צריך לייחס את זה למספר הרשומות, או הבלוקים, או משהו אחר?

ציטוט:

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

25K שורות זה לא מספיק? :/

גודל הטבלה סה"כ, כולל אינדקס והמידע, הוא KiB. 1,590.8

ציטוט:

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

אז איך מהירות הביצוע היה יחסית קרוב...?
אני מניח שלשיטה ORDER BY...LIMIT יש אופטימיזציה ספציפית כאשר זה דומה ל-MAX...

תודה על ההסבר

עשיתי קצת קריאות בנוסף...

לגבי ה-EXECUTION PLAN הבנתי, אבל תוכל להמליץ לי על איזה דרך טובה שאתה מכיר כדי שאוכל לדעת מהי ה-execution plan? אני משתמש ב-MySQL כפי שכתבתי. כיצד אדע את ה-query plan?

ב MySQL הפקודה היא EXPLAIN
קרא כאן: http://dev.mysql.com/doc/refman/5.1...ng-explain.html



ניסיתי לחפש מידע על ה-I/O, אבל אני לא בטוח כיצד זה קשור..?
האם הכוונה היא לקריאות (read) של ה-DBMS מהכונן הקשיח?
עד היום, יצא לי לשמוע על הביטוי I/O בהקשר של שתי מערכות שמתקשרות (מקלדת ומחשב ; או 2 נקודות קצה).
בנוגע לבלוקים, הבנתי שהם סה"כ חלקים קטנים יותר של כל המידע שבטבלה. איך אני יודע כמה בלוקים יש? האם אפשר לראות זאת ב-PMA ? (ר"ת phpMyAdmin)

I/O זו קריאה וכתיבה מקבצים. גם המסך והמקלדת הם קבצים (STDIN ו STDOUT בדר"כ)
והכוונה היא אכן לכמות פעולות הקריאה והכתיבה מהכונן הקשיח - בדרך כלל זוהי הפעולה האיטית ביותר במסד נתונים.

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


מצאתי את זה בתור cardinality ב-PMA. בבדיקה שעשיתי היה לי cardinality של 640 באינדקס counter. כדי לדעת אם זה גבוה\נמוך\בינוני - האם אני צריך לייחס את זה למספר הרשומות, או הבלוקים, או משהו אחר?

המספר מייצג את היחס בין הערכים הייחודיים לרשומות. ככל שהוא גבוה יותר- יותר טוב.
שים לב שעליך לבצע ANALYZE כדי שמסד הנתונים יעריך נכון את מספר הרשומות.
זה מה שמצאתי בתיעוד: http://dev.mysql.com/doc/refman/5.0/en/show-index.html

ציטוט:

Cardinality An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

25K שורות זה לא מספיק? :/

גודל הטבלה סה"כ, כולל אינדקס והמידע, הוא KiB. 1,590.8


אז איך מהירות הביצוע היה יחסית קרוב...?
אני מניח שלשיטה ORDER BY...LIMIT יש אופטימיזציה ספציפית כאשר זה דומה ל-MAX...


אתה הראת עכשיו בדיוק למה 25K שורות זה מעט.
מדובר כאן על 1.5 MiB בערך,
זאת אומרת שהכל נכנס בקלות לזיכרון וכל הפעולות מבוצעות שם בלי בעיה.
אבל אם נגדיל את זה ל 1.5 GiB מסד הנתונים כבר יתקשה לנהל הכל בזיכרון
ואולי תראה הבדל משמעותי בביצועים בין השיטות.

_________________________________________________

אזהרה: משרד הבריאות קובע כי העישון מזיק לבריאות !
תראו, אפילו החייזר נהיה ירוק מזה

מנהל פורום מדע בדיוני ופנטסיה ופורום מדע טכנולוגיה וטבע בפרש

ציטוט:

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

תודה, מצאתי את המידע למי שיצטרך:

ציטוט:

במקור נכתב על ידי http://dev.mysql.com/doc/mysql-security-excerpt/5.0/en/table-info.html Blocksize The size of each index block. By default this is 1024, but the value may be changed at compile time when MySQL is built from source.

(מקור: http://dev.mysql.com/doc/mysql-secu...table-info.html )

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

ציטוט:

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

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

קוד:

SELECT COUNT(DISTINCT `index_name`) FROM tbl

אבל האם אפשר לדעת מראש (ע"י הערכה) האם MySQL עשוי להשתמש באינדקס בהרצת השאילתא בהינתן cardinality מסוים?
הדרך הכי הגיונית לפי דעתי להערכה של האם cardinality מסוים הוא גבוה\בינוני\נמוך, זה השוואתו ביחס למספר הרשומות. בעצם מצד שני, הדרך החלופה של אי-שימוש באינדקס היא לבצע full table scan שאז חשובה גודל הטבלה... יצאתי קצת מבולבל :s

ציטוט:

אתה הראת עכשיו בדיוק למה 25K שורות זה מעט. מדובר כאן על 1.5 MiB בערך, זאת אומרת שהכל נכנס בקלות לזיכרון וכל הפעולות מבוצעות שם בלי בעיה. אבל אם נגדיל את זה ל 1.5 GiB מסד הנתונים כבר יתקשה לנהל הכל בזיכרון

איך אפשר להעריך איזה גודל של טבלה מסד הנתונים מסוגל לנהל בזיכרון?



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

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

ציטוט:

במקור נכתב על ידי borsood זה נראה פשוט, אבל במערכות אמיתיות זה לא תמיד ככה.

ודאי במיוחד כשמצרפים Join'ים...

יש איזה מקור מומלץ שאוכל ללמוד את הנושא של index'ים, db/query tuning ודברים דומים?