SQL: חלוקה לדפים ביעילות

20 בדצמבר 2009

תגובה אחת

English: SQL Server and MySQL optimization for paging large tables. skip the Hebrew explanations and look at the SQL. the first is slow, the second is better and the third's cost is 1% of the original.
The four is MySQL specific "limit" feture and the last using ROW_NUMBER() function introduction in SQL Server 2005.

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

ניקח למשל טבלה של 1,000,000 רשומות. נקרא לה  forum_messages, ונצרף עוד טבלה, של 100,000 רשומות, נקרא לה forum_topics

נמלא אותן בנתונים של פורום ממוצע, סה"כ בין 5000 ל-70,000 הודעות פר פורום וכ-20 הודעות לשרשור. נשים אינדקס על מספר השרשור ואינדקס נוסף על מספר הפורום (יש כמובן עוד פרמטרים רבים, אבל ננסה לפשט אותם):

SELECT * FROM forum_messages, forum_topics WHERE m_forum=50 and t_id=m_topic ORDER BY m_date desc

יחזיר (נניח) 20,000 רשומות.

אבל לנו מספיק רק 20, אז נעשה משהו כזה:

SELECT top 20 * FROM forum_messages, forum_topics WHERE m_forum=50 and t_id=m_topic ORDER BY m_date desc

יקח כ-5% מהזמן בערך, עם השפעה נוספת בתחום של עיבוד בקוד ואם צריך גם רוחב פס ברשת.

 

אבל אפשר לחסוך עוד קצת:

SELECT m_id, m_title,t_id,t_status from (SELECT top 20 * FROM forum_topics ORDER BY t_id desc) as x, forum_messages WHERE t_id=m_topic

בשיטה הזו, המכונה "Join מאוחר", במקום לבצע שליפה על 1,000,000*100,000 ואז נצמצם אותה, נשלוף קודם מטבלה אחת 20 רשומות ורק אז נצרף מהטבלה השניה את שאר המידע. השליפה הזו תיקח כ-25% מהשליפה שלפניה (כ-1% מהשליפה המקורית). אינדקס מתאים למיון יאפשר גם לחסוך עוד כמה אחוזים על ידי ייעול גם השלב הזה של השליפה.

ב-MySQL אגב, העבודה שלנו פשוטה בהרבה. שם אפשר פשוט לבצע חלוקה לדפים עם LIMIT הפשוט והיעיל.

SELECT m_id, m_title,t_id,t_status from (SELECT  * FROM forum_topics ORDER BY t_id desc limit 20,40) as x, forum_messages WHERE t_id=m_topic ORDER BY m_date 

יחזיר לנו את העמוד השני של הפורום מטבלת forum_topics, ואז יצרף אליה את הטבלה הכבדה יותר עם ההודעות לפי הצורך. הנה דוגמא למימוש כזה עבור SQL Server וחבריו תוך שימוש בפונקציה Row_Number שממומשת החל מ-SQL Server 2005

SELECT * FROM   (SELECT top 40 ROW_NUMBER() OVER(ORDER BY t_id,m_id) AS rownum, 
  
       * FROM (SELECT top 20 * FROM forum_topics ORDER BY t_id desc) as x, forum_messages WHERE t_id=m_topic) AS t1
WHERE  rownum > 20 AND rownum <= 40

כשהרעיון הוא לשלוף את מספר הרשומות המינימלי האפשרי ואז לסנן אותו לפי rownum

 

אם יש צורך לדעת גם את מספר העמודים, בשביל זה הומצאה פונקציית count(*), שתעשה את העבודה בצורה נפלאה. את התוצאות שלה הייתי שומר באיזה Cache ומעדכן רק כשמשהו באמת משתנה.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

תגובה אחת

  1. יוסי (דוגמה)15 בפברואר 2010 ב 23:41

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

    ובכל זאת, בעיקר תודה 🙂

    הגב