بهینه سازی های جالب 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 قابل تهی است و اگر هر یک از سطرها دارای 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

همانطور که می بینید، دو پرس و جو ما در مقادیر ستون های POSSIBLE_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]))

خلاصه

به نظر می رسد یک بهینه سازی ساده است، اما در همه DBMS ها استفاده نمی شود؛ به ویژه، به اندازه کافی عجیب، در 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 جالب بحث خواهیم کرد.