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

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

4. ביטול פרדיקטים "חסרי משמעות".

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

SELECT * FROM actor WHERE 1 = 1;

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

SELECT * FROM film WHERE release_year = release_year;

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

SELECT * FROM film WHERE release_year IS NOT NULL;

אילו מאגרי מידע עושים זאת?

DB2

כן!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

MySQL

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

CREATE INDEX i_release_year ON film (release_year);

וקבל תכניות לבקשות הבאות בתמורה:

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

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

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

כפי שאתה יכול לראות, שתי השאילתות שלנו שונות באופן משמעותי בערכי העמודות POSIBLE_KEYS ו- FILTERED . אז אני אעז בניחוש סביר ש-MySQL לא מייעל את זה.

נבואה

כן!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

למרבה הצער לא!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

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

SELECT * FROM film WHERE release_year IS NOT NULL;

נותן תוצאות הרבה יותר טובות:

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

באסה!

SQL Server

באופן מוזר, נראה שגם SQL Server לא עושה את זה:

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

עם זאת, בהתבסס על הופעת התוכנית, הערכת הקרדינליות נכונה, וכך גם העלות. אבל מניסיוני עם SQL Server, הייתי אומר שבמקרה זה, לא מתרחשת אופטימיזציה, מכיוון ש-SQL Server יציג את הפרדיקט המבוצע בפועל בתוכנית (כדי לראות מדוע, עיין בדוגמאות של אילוצי CHECK למטה). מה לגבי פרדיקטים "חסרי משמעות" בעמודות NOT NULL ? ההמרה שלעיל הייתה הכרחית רק כי RELEASE_YEAR יכול להיות בלתי מוגדר. מה קורה אם אתה מפעיל את אותה שאילתה חסרת משמעות על, למשל, העמודה FILM_ID ?

SELECT * FROM film WHERE film_id = film_id

האם כעת זה לא תואם לשום פרדיקט בכלל? או לפחות ככה זה צריך להיות. אבל האם זה?

DB2

כן!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

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

MySQL

כן! (שוב, ניחוש מושכל)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

שימו לב שהעמודה EXTRA ריקה כעת, כאילו אין לנו משפט WHERE כלל!

נבואה

כן!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

שוב, לא חלות פרדיקטים.

PostgreSQL

וואו, שוב לא!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

הפילטר מוחל וציון הקרדינליות הוא עדיין 5. באמר!

SQL Server

והנה שוב לא!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

סיכום

זה נראה כמו אופטימיזציה פשוטה, אבל זה לא בשימוש בכל DBMSs; במיוחד, באופן מוזר, זה לא בשימוש ב-SQL Server!

5. תחזיות בתתי שאילתות EXISTS

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

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

והתשובה היא... כן. פחית. הכוכבית אינה משפיעה על הבקשה. איך אתה יכול להיות בטוח בזה? שקול את השאילתה הבאה:

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

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

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

כעת אף אחד ממסדי הנתונים לא מחזיר שגיאה. כולם מחזירים TRUE או 1 . המשמעות היא שאף אחד מבסיסי הנתונים שלנו לא מעריך בפועל את ההשלכה (כלומר, סעיף ה-SELECT ) של שאילתת המשנה EXISTS . SQL Server, למשל, מציג את התוכנית הבאה:

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

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

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

תוכנית השאילתות לעיל נראית כך:

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

אנו רואים מידע על ההקרנה ב- Id=3 . למעשה, אנחנו אפילו לא ניגשים לטבלת FILM_ACTOR כי אנחנו לא צריכים. ניתן לבצע את הפרדיקט EXISTS באמצעות אינדקס מפתח זר בעמודה יחידה של ACTOR_ID - כל מה שדרוש עבור שאילתה זו - למרות שכתבנו SELECT * .

סיכום

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