בעיות בביצועי SQL הנובעות מ"עבודה מיותרת אך נדרשת"

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

מְיוּתָר

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

SELECT title, rating
FROM film

עם זאת, היישום שלנו (או ה-ORM שלנו) מבצע את השאילתה הזו:

SELECT *
FROM film

מה אנחנו מקבלים כתוצאה מכך? לְנַחֵשׁ. אנחנו מקבלים הרבה מידע חסר תועלת: בצד ימין אתה אפילו יכול לראות כמה JSON מורכב נטען:

• מהדיסק
• למטמון
• בחוט
• לזכר הלקוח
• ולבסוף נזרק [כמיותר]

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

חובה

ועכשיו - החלק הגרוע ביותר. למרות שמייעלים יכולים כעת לעשות הרבה, פעולות אלו הן חובה עבור מסד הנתונים. למסד הנתונים אין דרך לדעת שיישום הלקוח אינו זקוק ל-95% מהנתונים הללו. וזו רק הדוגמה הפשוטה ביותר. תאר לעצמך לחבר כמה טבלאות... אז מה, אתה אומר, אבל מסדי נתונים הם מהירים? תן לי להאיר לך כמה דברים שכנראה לא חשבת עליהם. מובן שזמן הביצוע של בקשה פרטנית לא ממש משפיע על כלום. אוקיי, זה רץ פי אחד וחצי יותר לאט, אבל אנחנו נעבור את זה, נכון? לנוחות? לפעמים זה נכון. אבל אם אתה תמיד מקריב את הביצועים מטעמי נוחות , הדברים הקטנים האלה יתחילו להצטבר. לא נדבר יותר על ביצועים (מהירות הביצוע של בקשות בודדות), אלא על תפוקה (זמן תגובה של המערכת), ואז יתחילו בעיות רציניות, שלא כל כך קל לפתור. זה הזמן שבו אתה מאבד יכולת הרחבה. בואו נסתכל על תוכניות הביצוע, במקרה זה, ה-Oracle DBMS:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   166K|
|   1 |  TABLE ACCESS FULL| FILM |  1000 |   166K|
--------------------------------------------------

לעומת:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 20000 |
|   1 |  TABLE ACCESS FULL| FILM |  1000 | 20000 |
--------------------------------------------------

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

שימוש באינדקסים

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

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

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

-------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR               |     8 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACTOR_LAST_NAME |     8 |
-------------------------------------------------------------------

תוכנית טובה, לא? ובכן, אם באמת היינו צריכים את זה, אז לא: ברור שאנו מבזבזים זיכרון וכו'. הבה נשקול את השאילתה הזו כחלופה:

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'

התוכנית שלו היא כזו:

----------------------------------------------------
| Id  | Operation        | Name            | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT |                 |     8 |
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 |
----------------------------------------------------

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

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 100000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Улучшенный request, но все равно с доступом к таблице
      SELECT /*+INDEX(actor(last_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Оптимальный request: покрывающий индекс
      SELECT /*+INDEX(actor(last_name, first_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

שימו לב שלטבלת השחקנים יש רק 4 עמודות, כך שההבדל בביצועים בין הצהרות 1 ו-2 לא כל כך גדול, אבל הוא עדיין משמעותי. אציין גם שהשתמשתי ברמזים של Oracle Optimizer כדי שהאופטימיזר יבחר באינדקס ספציפי כזה או אחר עבור השאילתה. מפעיל 3 הוא המנצח הבלתי מעורער במירוץ שלנו. הביצועים שלו הרבה יותר טובים, ואנחנו מדברים על שאילתה פשוטה ביותר. שוב, כאשר אנו כותבים SELECT *, אנו יוצרים עבודה מיותרת אך חובה עבור בסיס הנתונים שהוא לא יכול לבצע אופטימיזציה. היא לא תבחר באינדקס המכסה כי יש לו תקורה מעט גבוהה יותר מהמדד LAST_NAME שבחרה, ובין היתר היא עדיין צריכה לגשת לטבלה כדי לאחזר עמודת LAST_UPDATE חסרת תועלת, למשל. אבל ככל שננתח את SELECT * לעומק, כך יתברר שהדברים גרועים יותר. בוא נדבר על...

המרות SQL

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

CREATE VIEW v_customer AS
SELECT
  c.first_name, c.last_name,
  a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)

תצוגה זו פשוט מבצעת את כל ההצטרפות "...-to-one" בין טבלת הלקוחות של CUSTOMER לטבלאות שונות עבור חלקים מהכתובת שלהם. תודה, נורמליזציה. תארו לעצמכם שאחרי שעבדנו קצת עם הנוף הזה, התרגלנו אליו ושכחנו מהטבלאות הבסיסיות. ועכשיו אנחנו מבצעים את השאילתה הבאה:

SELECT *
FROM v_customer

כתוצאה מכך, אנו מקבלים תוכנית מרשימה מאוד:

----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   599 | 47920 |   14 |
|*  1 |  HASH JOIN           |          |   599 | 47920 |   14 |
|   2 |   TABLE ACCESS FULL  | COUNTRY  |   109 |  1526 |    2 |
|*  3 |   HASH JOIN          |          |   599 | 39534 |   11 |
|   4 |    TABLE ACCESS FULL | CITY     |   600 | 10800 |    3 |
|*  5 |    HASH JOIN         |          |   599 | 28752 |    8 |
|   6 |     TABLE ACCESS FULL| CUSTOMER |   599 | 11381 |    4 |
|   7 |     TABLE ACCESS FULL| ADDRESS  |   603 | 17487 |    3 |
----------------------------------------------------------------

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

SELECT first_name, last_name
FROM v_customer

עכשיו בואו נבדוק מה קרה!

------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   599 | 16173 |     4 |
|   1 |  NESTED LOOPS      |             |   599 | 16173 |     4 |
|   2 |   TABLE ACCESS FULL| CUSTOMER    |   599 | 11381 |     4 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C007120 |     1 |     8 |     0 |
------------------------------------------------------------------

שינויים דרסטיים לטובה מבחינת הביצוע. ההצטרפות בוטלו מכיוון שהאופטימיזר יכול לראות שהם חסרי תועלת , ואם הוא יכול לראות את זה (ולא הפכת את העבודה הזו לחובה על ידי בחירת *), אז הוא פשוט לא יכול לעשות את כל העבודה הזו. מדוע זה כך במקרה הזה? המפתח הזר CUSTOMER.ADDRESS_ID למפתח הראשי ADDRESS.ADDRESS_ID מבטיח בדיוק ערך אחד של האחרון, כלומר פעולת JOIN תהיה צירוף "...-to-one" שאינו מגדיל או מקטין את מספר השורות . ומכיוון שאנחנו לא בוחרים או מבקשים שום שורות בכלל, אז אין טעם לטעון אותן בכלל. הסרת ה-JOIN כנראה לא תשפיע כלל על תוצאת השאילתה. מסדי נתונים עושים זאת כל הזמן. אתה יכול להריץ את השאילתה הבאה כמעט בכל מסד נתונים:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)

במקרה זה, אתה עשוי לצפות שיוצא חריג אריתמטי, כמו בעת ביצוע השאילתה הבאה:

SELECT 1 / 0 FROM dual

אבל זה לא קורה. האופטימיזר (או אפילו ה-parser) יכול להבטיח ששום רכיבי רשימה של בחירה בפרדיקט EXISTS (SELECT ..) לא ישנו את תוצאת השאילתה, כך שאין צורך לבצע אותה. ככה!

בינתיים...

אחת הבעיות המעצבנות ביותר עם ORMs היא שקל כל כך לכתוב שאילתות SELECT *. למעשה, למשל, ב-HQL / JPQL הם משמשים בדרך כלל כברירת מחדל. אנחנו יכולים להשמיט את פסקת ה-SELECT לגמרי, כי אנחנו הולכים לאחזר את כל הישות, נכון? לדוגמה:

FROM v_customer

לדוגמה, Vlad Mihalcea, מומחה ותומך בפיתוח עם Hibernate , ממליץ להשתמש בשאילתות [מוסמך] כמעט תמיד כאשר אתה בטוח שאינך רוצה לשמור שינויים כלשהם לאחר התשלום. ORMs מקלים מאוד על פתרון הבעיה של התמדה של גרפי אובייקטים. הערה: התמדה. המשימות של שינוי גרפי אובייקטים בפועל ושמירת שינויים קשורות קשר בל יינתק. אבל אם אתה לא מתכוון לעשות את זה, אז למה לטרוח לחלץ את המהות? למה לא לכתוב בקשה [מעודנת]? בואו נהיה ברורים: מנקודת מבט של ביצועים, כתיבת שאילתה המותאמת במיוחד למקרה השימוש הספציפי שלכם היא ללא ספק טובה יותר מכל אפשרות אחרת. אולי לא אכפת לך מכיוון שמערך הנתונים שלך קטן וזה לא משנה. גדול. אבל כאשר אתה בסופו של דבר צריך מדרגיות, עיצוב מחדש של האפליקציות שלך לשימוש בשאילתות במקום חצייה הכרחית של גרף הישות יהיה די מאתגר. ויהיה לך מה לעשות בלעדיו.

סופר שורות כדי לגלות אם יש משהו

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

SELECT count(*)
FROM orders
WHERE user_id = :user_id

יְסוֹדִי. אם COUNT = 0, אז אין פקודות. אחרת, כן. הביצועים לא יהיו כל כך גרועים מכיוון שכנראה יש לנו אינדקס בעמודה ORDERS.USER_ID. אבל מה לדעתך יושוו הביצועים של השאילתה לעיל לאפשרות הבאה:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
)

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

סיכום

מאמר זה מציין את המובן מאליו. אל תכריח את מסד הנתונים לבצע עבודה מיותרת אך נדרשת . זה מיותר כי בהתחשב בדרישות, אתה יודע שאין צורך לבצע עבודה ספציפית כלשהי. עם זאת, אתה אומר למסד הנתונים לעשות זאת. זה נדרש מכיוון שאין דרך עבור מסד הנתונים להבטיח שעבודה זו מיותרת . מידע זה זמין רק ללקוח ואינו זמין לשרת. אז מסד הנתונים צריך להפעיל את זה. המאמר התמקד ב-SELECT *, בעיקר בגלל שהוא אובייקט כל כך נוח להסתכל עליו. אבל זה חל לא רק על מסדי נתונים. זה חל על כל האלגוריתמים המבוזרים שבהם הלקוח אומר לשרת לעשות עבודה מיותרת אך נדרשת . כמה משימות N+1 יש באפליקציית AngularJS הממוצעת שלך שבה ממשק המשתמש עובר בלולאה בתוצאה של שירות A, מתקשר לשירות B מספר פעמים, במקום לארוז את כל השיחות ל-B לשיחה אחת? זוהי תופעה נפוצה מאוד. הפתרון תמיד זהה. ככל שתספק יותר מידע לישות שמבצעת את הפקודות שלך, כך היא תבצע את הפקודות הללו מהר יותר (תיאורטית). כתוב שאילתות אופטימליות. תמיד. כל המערכת שלך תודה לך על כך. מאמר מקורי