אופטימיזציות מגניבות של SQL שאינן תלויות במודל העלות. חלק 1

חמש אופטימיזציות פשוטות שניתן ליישם רק על סמך מטא נתונים (כלומר אילוצים) והשאילתה עצמה אנו מציעים לכם התאמה למאמר של לוקאס אדר, המיועד למי שיש לו הבנה כללית של מסדי נתונים ו-SQL, כמו גם קצת ניסיון מעשי עם DBMS . אופטימיזציית עלויות היא למעשה דרך סטנדרטית לייעל שאילתות SQL במסדי נתונים מודרניים. זו הסיבה שכל כך קשה לכתוב ידנית אלגוריתם מורכב ב -3GL (שפות תכנות מהדור השלישי) שהביצועים שלו יעלו על תוכנית הביצוע המחושבת באופן דינמי שנוצר על ידי מייעל מודרני. היום לא נדון באופטימיזציה של עלות, כלומר אופטימיזציה על בסיס מודל העלות של בסיס הנתונים. נבחן אופטימיזציות הרבה יותר פשוטות. אלה שניתן ליישם רק על סמך מטא נתונים (כלומר הגבלות) והבקשה עצמה. בדרך כלל היישום שלהם עבור מסד נתונים אינו בינומי של ניוטון, שכן, במקרה זה, כל אופטימיזציה תוביל לתוכנית ביצוע טובה יותר, ללא קשר לנוכחות של אינדקסים, נפחי נתונים והטיית הפצת הנתונים. "לא בינומיאל של ניוטון" זה לא במובן של כמה קל ליישם את האופטימיזציה, אלא האם יש לעשות זאת. אופטימיזציות אלו מונעות עבודה מיותרת, נוספת [עבור מסד הנתונים] ( בניגוד לעבודה מיותרת, נדרשת, שכבר כתבתי עליה ).

למה משמשות האופטימיזציות האלה?

רובם משמשים עבור:

• תיקוני באגים בשאילתות;
• המאפשר שימוש חוזר בתצוגות מבלי שבסיס הנתונים יבצע בפועל את לוגיקת התצוגה.

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

נעשה שימוש במאגרי מידע

במאמר זה נשווה 10 אופטימיזציות של SQL בחמשת ה-DBMSs הנפוצים ביותר ( לפי דירוג מסד הנתונים ):

• אורקל 12.2;
• MySQL 8.0.2;
• SQL Server 2014;
• PostgreSQL 9.6;
• DB2 LUW 10.5.

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

1. סגירה טרנזיטיבית;
2. פרדיקטים בלתי אפשריים וקריאות שולחן מיותרות;
3. ביטול JOIN;
4. ביטול פרדיקטים "חסרי משמעות";
5. תחזיות בשאילתות משנה EXISTS;
6. מיזוג פרדיקטים;
7. סטים ריקים בעליל;
8. אילוצים CHECK;
9. קשרים רפלקסיביים מיותרים;
10. פרדיקטים של Pushdown

היום נדון בעמוד. 1-3, בחלק השני - 4 ו-5, ובחלק 3 - 6-10.

1. סגירה טרנזיטיבית

נתחיל במשהו פשוט יותר: סגירה טרנזיטיבית . זהו מושג טריוויאלי שחל על פעולות מתמטיות רבות, כמו מפעיל השוויון. ניתן לנסח אותו במקרה זה באופן הבא: אם A = B ו- B = C, אז A = C.

לא קשה, נכון? אבל יש לזה כמה השלכות מעניינות על אופטימיזציית SQL. בואו נסתכל על דוגמה. בואו נחלץ את כל הסרטים עם ACTOR_ID = 1:

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.actor_id = 1;

התוצאה היא כדלקמן:

FIRST_NAME      LAST_NAME  FILM_ID
PENELOPE        GUINESS    1
PENELOPE        GUINESS    23
PENELOPE        GUINESS    25
PENELOPE        GUINESS    106
PENELOPE        GUINESS    140
PENELOPE        GUINESS    166
...

כעת נסתכל על התוכנית לביצוע שאילתה זו במקרה של Oracle DBMS:

--------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |
|   1 |  NESTED LOOPS                |               |    19 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACTOR         |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ACTOR      |     1 |
|*  4 |   INDEX RANGE SCAN           | PK_FILM_ACTOR |    19 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ACTOR_ID"=1)
   4 - access("FA"."ACTOR_ID"=1)

הקטע על הפרדיקטים מעניין כאן במיוחד. הפרדיקט ACTOR_ID = 1, עקב סגירה טרנזיטיבית, חל הן על טבלת ACTOR והן על טבלת FILM_ACTOR. אם:

• A.ACTOR_ID = 1 (из предиката WHERE) и…
• A.ACTOR_ID = FA.ACTOR_ID (из предиката ON)
  То:
• FA.ACTOR_ID = 1

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

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = 'PENELOPE'
AND last_name = 'GUINESS'

התוכנית שלו:

----------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |
|   1 |  NESTED LOOPS                        |                     |     2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR               |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME |     3 |
|*  4 |   INDEX RANGE SCAN                   | PK_FILM_ACTOR       |    27 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."FIRST_NAME"='PENELOPE')
   3 - access("A"."LAST_NAME"='GUINESS')
   4 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")

כפי שניתן לראות, מספר השורות בטבלת FILM_ACTOR מוערך יתר על המידה, בעוד שה-NESTED LOOP מוערך בחסר. הנה כמה ערכים מעניינים:

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
  SELECT count(*) c FROM film_actor GROUP BY actor_id
);

תוֹצָאָה:

19
27.315

מכאן מגיעות ההערכות. אם מסד הנתונים יודע שאנחנו מדברים על ACTOR_ID = 1, אז הוא יכול לאסוף נתונים סטטיסטיים על מספר הסרטים עבור השחקן המסוים הזה . אם לא (מכיוון שמנגנון איסוף הנתונים הסטטיסטיים הסטנדרטי אינו מתאם בין FIRST_NAME/LAST_NAME לבין ACTOR_ID), נקבל את המספר הממוצע של סרטים עבור כל השחקנים . טעות פשוטה וחסרת חשיבות במקרה הספציפי הזה, אבל בשאילתה מורכבת היא יכולה להתפשט הלאה, להצטבר ולהוביל הלאה לתוך השאילתה (במעלה למעלה בתוכנית) לבחירת JOIN שגויה. אז בכל פעם שאתה יכול, עצב את החיבורים והפרדיקטים הפשוטים שלך כדי לנצל את היתרונות של סגירה טרנזיטיבית. אילו מסדי נתונים אחרים תומכים בתכונה זו?

DB2

כן!

Explain Plan
-----------------------------------------------------------
ID | Operation              |                 Rows | Cost
 1 | RETURN                 |                      |   13
 2 |  NLJOIN                |              27 of 1 |   13
 3 |   FETCH ACTOR          |     1 of 1 (100.00%) |    6
 4 |    IXSCAN PK_ACTOR     |   1 of 200 (   .50%) |    0
 5 |   IXSCAN PK_FILM_ACTOR | 27 of 5462 (   .49%) |    6
Predicate Information
 4 - START (Q2.ACTOR_ID = 1)
      STOP (Q2.ACTOR_ID = 1)
 5 - START (1 = Q1.ACTOR_ID)
      STOP (1 = Q1.ACTOR_ID)

אגב, אם אתה אוהב תוכניות ביצוע מגניבות כמו זה, בדוק את התסריט של מרקוס וויננד .

MySQL

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

ID  SELECT TYPE  TABLE  TYPE   REF    ROWS
------------------------------------------
1   SIMPLE       a      const  const  1
1   SIMPLE       fa     ref    const  19

אבל העובדה ש-const מצוין פעמיים בעמודה REF מראה ששתי הטבלאות מחפשות ערך קבוע. במקביל, תוכנית השאילתות עם FIRST_NAME/LAST_NAME נראית כך:

ID  SELECT TYPE  TABLE  TYPE   REF         ROWS
-----------------------------------------------
1   SIMPLE       a      ref    const       3
1   SIMPLE       fa     ref    a.actor_id  27

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

PostgreSQL

כן!

QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop  (cost=4.49..40.24 rows=27 width=15)
  ->  Seq Scan on actor a  (cost=0.00..4.50 rows=1 width=17)
        Filter: (actor_id = 1)
  ->  Bitmap Heap Scan on film_actor fa  (cost=4.49..35.47 rows=27 width=4)
        Recheck Cond: (actor_id = 1)
        ->  Bitmap Index Scan on film_actor_pkey  (cost=0.00..4.48 rows=27 width=0)
              Index Cond: (actor_id = 1)

SQL Server

כן!

|--Nested Loops(Inner Join)
     |--Nested Loops(Inner Join)
     |    |--Index Seek (SEEK:([a].[actor_id]=(1)))
     |    |--RID Lookup
     |--Index Seek (SEEK:([fa].[actor_id]=(1)))

סיכום

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

2. פרדיקטים בלתי אפשריים וקריאות שולחן מיותרות

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

-- "Очевидный"
SELECT * FROM actor WHERE 1 = 0
-- "Хитрый"
SELECT * FROM actor WHERE NULL = NULL

ברור שהשאילתה הראשונה לעולם לא תחזיר תוצאות, אבל אותה הצהרה נכונה לגבי השנייה. אחרי הכל, למרות ש-NULL IS NULL הוא תמיד TRUE, תוצאת החישוב NULL = NULL היא NULL, שלפי לוגיקה תלת-ערכית , שווה ערך ל-FALSE. זה די מובן מאליו, אז בואו נקפוץ ישר לגלות אילו מסדי נתונים מבצעים את האופטימיזציה הזו.

DB2

כן!

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

כפי שאתה יכול לראות, הגישה לטבלת ACTOR אינה נכללת לחלוטין מהתוכנית. הוא מכיל רק את פעולת GENROW, שיוצרת אפס שורות. מושלם.

MySQL

כן!

ID  SELECT TYPE  TABLE   EXTRAS
-----------------------------------------
1   SIMPLE         Impossible WHERE

הפעם, MySQL היה אדיב מספיק כדי להודיע ​​לנו על סעיף WHERE הבלתי אפשרי. תודה! זה הופך את הניתוח להרבה יותר קל, במיוחד בהשוואה למאגרי מידע אחרים.

נבואה

כן!

---------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |
|*  1 |  FILTER            |       |      1 |        |      0 |
|   2 |   TABLE ACCESS FULL| ACTOR |      0 |    200 |      0 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

אנו רואים שהתוכנית עדיין מזכירה גישה לטבלת ACTOR, ומספר השורות הצפוי הוא עדיין 200, אבל יש גם פעולת סינון (FILTER) עם Id=1, שבה לעולם לא יהיה TRUE. בשל חוסר החיבה של אורקל מסוג הנתונים הבוליאניים של SQL , אורקל מציגה NULL IS NOT NULL בתוכנית, במקום רק FALSE. נו טוב... אבל ברצינות, תראה את הפרדיקט הזה. היה לי הזדמנות לנפות באגים בתוכניות ביצוע עם תת-עצים של 1000 שורות וערכי עלות גבוהים במיוחד, רק כדי לגלות לאחר העובדה שכל תת-העץ "נתקע" על ידי מסנן NULL IS NOT NULL. קצת מייאש, אני אומר לך.

PostgreSQL

כן!

QUERY PLAN
-------------------------------------------
Result  (cost=0.00..0.00 rows=0 width=228)
  One-Time Filter: false

כבר יותר טוב. אין קריאות מעצבנות בטבלה של ACTOR ופרדיקט FALSE קטן ומסודר.

SQL Server?

כן!

|--Constant Scan

SQL Server קורא לזה " סריקה מתמדת ", שהיא סריקה שבה לא קורה כלום - בדומה ל-DB2. כל מסדי הנתונים שלנו יכולים לא לכלול פרדיקטים בלתי אפשריים:

3. בטל JOIN

בסעיף הקודם, ראינו גישה מיותרת לטבלה בשאילתות של טבלה אחת. אבל מה קורה אם ה-JOIN לא דורש אחת מכמה גישה לטבלה? כבר כתבתי על ביטול JOIN בפוסט קודם מהבלוג שלי . מנוע ה-SQL מסוגל לקבוע, בהתבסס על סוג השאילתה ונוכחות מפתחות ראשיים וזרים, האם אכן יש צורך ב-JOIN מסוים בשאילתה נתונה, או שביטולו לא ישפיע על הסמנטיקה של השאילתה. בכל שלוש הדוגמאות הבאות, אין צורך ב-JOIN. ניתן לבטל צירוף פנימי ...-to-one על ידי שימוש במפתח זר NOT NULL. במקום זאת:

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

מסד הנתונים יכול לעשות את הפעולות הבאות:

SELECT first_name, last_name
FROM customer c

ניתן להחליף INNER JOIN מסוג "...-to-one" אם יש מפתח זר בר-ריק. השאילתה שלמעלה פועלת אם המפתח הזר כפוף לאילוץ NOT NULL. אם לא, למשל, כמו בבקשה זו:

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

אז עדיין ניתן לבטל את JOIN, אבל תצטרך להוסיף את הפרדיקט NOT NULL, כך:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

ניתן להסיר OUTTER JOIN מסוג "...-to-one" אם יש מפתח ייחודי. במקום זה:

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

מסד הנתונים, שוב, יכול לעשות את הפעולות הבאות:

SELECT first_name, last_name
FROM customer c

... גם אם אין מפתח זר עבור CUSTOMER.ADDRESS_ID. ניתן להסיר את החיבור החיצוני הייחודי (DISTINCT OUTER JOIN) מסוג "...-לרבים". במקום זה:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

מסד הנתונים יכול לעשות את הפעולות הבאות:

SELECT DISTINCT first_name, last_name
FROM actor a

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