بهینه سازی های جالب SQL که به مدل هزینه بستگی ندارند. قسمت 1

پنج بهینه سازی ساده که فقط بر اساس ابرداده (یعنی محدودیت ها) و خود پرس و جو قابل پیاده سازی هستند، ما اقتباسی از مقاله Lukas Eder را به شما پیشنهاد می کنیم، که برای کسانی طراحی شده است که درک کلی از پایگاه های داده و SQL و همچنین تجربه عملی با DBMS دارند. . بهینه سازی هزینه در واقع یک روش استاندارد برای بهینه سازی پرس و جوهای SQL در پایگاه داده های مدرن است. به همین دلیل است که نوشتن یک الگوریتم پیچیده به صورت دستی در 3GL (زبان های برنامه نویسی نسل سوم) که عملکرد آن از برنامه اجرایی محاسبه شده پویا توسط یک بهینه ساز مدرن فراتر رود، بسیار دشوار است. امروز ما در مورد بهینه سازی هزینه، یعنی بهینه سازی بر اساس مدل هزینه پایگاه داده بحث نمی کنیم. ما بهینه سازی های بسیار ساده تر را بررسی خواهیم کرد. مواردی که فقط بر اساس فراداده (یعنی محدودیت ها) و خود درخواست قابل اجرا هستند. معمولاً پیاده‌سازی آنها برای پایگاه داده یک دو جمله‌ای نیوتنی نیست، زیرا در این حالت، هر بهینه‌سازی بدون توجه به وجود شاخص‌ها، حجم داده‌ها و چولگی توزیع داده‌ها، منجر به اجرای بهتر طرح می‌شود. "دوجمله ای نیوتن نیست" به این معنا نیست که اجرای بهینه سازی چقدر آسان است، بلکه به این معناست که آیا باید انجام شود یا خیر. این بهینه‌سازی‌ها کار اضافی و غیرضروری [برای پایگاه داده] را حذف می‌کنند ( برخلاف کارهای غیر ضروری و ضروری که قبلاً در مورد آن نوشتم ).

این بهینه سازی ها برای چه مواردی استفاده می شود؟

بیشتر آنها برای موارد زیر استفاده می شوند:

• رفع اشکال در پرس و جو؛
• امکان استفاده مجدد از نماها بدون اینکه پایگاه داده در واقع منطق view را اجرا کند.

در مورد اول، می‌توان گفت: «پس چه، فقط پیش بروید و این پرس و جو احمقانه SQL را برطرف کنید.» اما کسی که هرگز اشتباه نکرده است، بگذار اول به من سنگ بزند. مورد دوم به ویژه جالب است: به ما توانایی ایجاد کتابخانه های پیچیده ای از نماها و توابع جدول را می دهد که می توانند در چندین لایه دوباره استفاده شوند.

پایگاه های داده استفاده شده

در این مقاله ما 10 بهینه سازی SQL را در پنج DBMS پرکاربرد ( بر اساس رتبه بندی پایگاه داده ) مقایسه خواهیم کرد:

• Oracle 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. محدودیت ها چک.
9. اتصالات انعکاسی غیر ضروری؛
10. محمولات فشاری

امروز ما در مورد pp. 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)

به هر حال، اگر برنامه های اجرایی جالبی مانند این را دوست دارید، اسکریپت Markus Winand را بررسی کنید .

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 وجود نخواهد داشت. به دلیل عدم علاقه Oracle از نوع داده استاندارد SQL Boolean ، اوراکل به جای FALSE، NULL IS NOT NULL را در طرح نمایش می دهد. اوه خوب... اما جدی، آن محمول را تماشا کنید. من فرصتی برای اشکال زدایی برنامه های اجرایی با زیردرخت های 1000 خطی و مقادیر بسیار بالا داشته ام، اما بعد از این واقعیت کشف کردم که کل زیردرخت توسط فیلتر NULL IS NOT NULL "قطع" شده است. کمی دلسرد کننده به شما می گویم.

PostgreSQL

آره!

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

در حال حاضر بهتر است. بدون تماس جدول ACTOR آزار دهنده و یک محمول نادرست کوچک.

SQL Server؟

آره!

|--Constant Scan

SQL Server این را " اسکن ثابت " می نامد، که اسکنی است که در آن هیچ اتفاقی نمی افتد - مشابه DB2. همه پایگاه های داده ما می توانند محمولات غیرممکن را حذف کنند:

3. JOIN ها را حذف کنید

در بخش قبل، دسترسی های غیر ضروری جدول را در پرس و جوهای تک جدولی مشاهده کردیم. اما اگر JOIN به یکی از چندین دسترسی جدول نیاز نداشته باشد چه اتفاقی می افتد؟ من قبلاً در مورد حذف JOIN در پست قبلی وبلاگم نوشتم . موتور SQL قادر است بر اساس نوع پرس و جو و وجود کلیدهای اصلی و خارجی، تعیین کند که آیا یک JOIN خاص واقعاً در یک پرس و جو مورد نیاز است یا اینکه حذف آن بر معنای پرس و جو تأثیر نمی گذارد. در هر سه مثال بعدی، JOIN مورد نیاز نیست. اتصال داخلی ... به یک را می توان با داشتن یک کلید خارجی 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

در صورت وجود یک کلید خارجی قابل تهی، یک NJOIN داخلی از نوع "...-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

اگر یک کلید منحصر به فرد وجود داشته باشد، می‌توان یک JOIN خارجی از نوع "...-به-یک" را حذف کرد. به جای این:

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) از نوع "...-to-many" قابل حذف است. به جای این:

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 رهبران بلامنازع اینجا هستند! ادامه دارد