ٹھنڈی SQL آپٹیمائزیشنز جو لاگت کے ماڈل پر منحصر نہیں ہیں۔ حصہ 1

پانچ آسان اصلاحیں جنہیں صرف میٹا ڈیٹا (یعنی رکاوٹوں) اور خود سوال کی بنیاد پر لاگو کیا جا سکتا ہے، ہم آپ کو لوکاس ایڈر کے مضمون کی موافقت پیش کرتے ہیں، جو ان لوگوں کے لیے ڈیزائن کیا گیا ہے جو ڈیٹا بیس اور ایس کیو ایل کی عمومی سمجھ رکھتے ہیں، نیز DBMS کے ساتھ کچھ عملی تجربہ رکھتے ہیں۔ . لاگت کی اصلاح دراصل جدید ڈیٹا بیس میں ایس کیو ایل کے سوالات کو بہتر بنانے کا ایک معیاری طریقہ ہے۔ یہی وجہ ہے کہ 3GL (تیسری نسل کی پروگرامنگ زبانوں) میں دستی طور پر ایک پیچیدہ الگورتھم لکھنا بہت مشکل ہے جس کی کارکردگی ایک جدید اصلاح کار کے ذریعہ تیار کردہ متحرک طور پر کیلکولیشن کے نفاذ کے منصوبے سے زیادہ ہوگی۔ آج ہم لاگت کی اصلاح پر بات نہیں کریں گے، یعنی ڈیٹا بیس کے لاگت کے ماڈل پر مبنی اصلاح۔ ہم بہت آسان اصلاحوں کو دیکھیں گے۔ جن کو صرف میٹا ڈیٹا (یعنی پابندیوں) اور خود درخواست کی بنیاد پر لاگو کیا جا سکتا ہے۔ عام طور پر ڈیٹا بیس کے لیے ان کا نفاذ نیوٹن بائنومیئل نہیں ہوتا ہے، کیوں کہ اس صورت میں، کوئی بھی اصلاح ایک بہتر عمل درآمد کے منصوبے کی طرف لے جائے گی، قطع نظر کہ اشاریہ جات کی موجودگی، ڈیٹا کے حجم اور ڈیٹا کی تقسیم کی ترچھی۔ "Not a Newton binomial" اس معنی میں نہیں ہے کہ اصلاح کو لاگو کرنا کتنا آسان ہے، لیکن کیا اسے کیا جانا چاہیے۔ یہ اصلاح غیر ضروری، اضافی کام کو ختم کرتی ہے [ڈیٹا بیس کے لیے] ( غیر ضروری، مطلوبہ کام کے برخلاف، جس کے بارے میں میں پہلے ہی لکھ چکا ہوں )۔

یہ اصلاحات کس کے لیے استعمال ہوتی ہیں؟

ان میں سے زیادہ تر کے لئے استعمال کیا جاتا ہے:

• سوالات میں بگ کی اصلاحات؛
• ڈیٹا بیس کے اصل میں ویو لاجک کو عمل میں لائے بغیر آراء کو دوبارہ استعمال کرنے کی اجازت دینا۔

پہلی صورت میں، کوئی کہہ سکتا ہے: "تو کیا، بس آگے بڑھیں اور اس احمقانہ SQL استفسار کو ٹھیک کریں۔" لیکن جس نے کبھی غلطی نہیں کی وہ پہلے مجھ پر پتھر پھینکے۔ دوسرا معاملہ خاص طور پر دلچسپ ہے: یہ ہمیں ویوز اور ٹیبل فنکشنز کی پیچیدہ لائبریریاں بنانے کی صلاحیت فراہم کرتا ہے جنہیں متعدد پرتوں میں دوبارہ استعمال کیا جا سکتا ہے۔

استعمال شدہ ڈیٹا بیس

اس مضمون میں ہم پانچ سب سے زیادہ استعمال ہونے والے DBMSs میں 10 SQL آپٹیمائزیشن کا موازنہ کریں گے ( ڈیٹا بیس کی درجہ بندی کے مطابق ):

• اوریکل 12.2؛
• MySQL 8.0.2;
• ایس کیو ایل سرور 2014؛
• PostgreSQL 9.6;
• DB2 LUW 10.5.

ایک اور درجہ بندی تقریباً اس کی بازگشت کرتی ہے۔ ہمیشہ کی طرح، اس مضمون میں میں Sakila ڈیٹا بیس سے استفسار کروں گا ۔ یہاں ان دس اقسام کی اصلاح کی فہرست ہے:

1. عبوری بندش؛
2. ناممکن پیش گوئیاں اور غیر ضروری ٹیبل کالز؛
3. شمولیت کو ختم کرنا؛
4. "بے معنی" پیشین گوئیوں کا خاتمہ؛
5. موجود ذیلی سوالات میں تخمینے۔
6. پیش گوئیوں کا انضمام؛
7. ممکنہ طور پر خالی سیٹ؛
8. رکاوٹیں چیک کریں؛
9. غیر ضروری اضطراری کنکشن؛
10. پش ڈاؤن کی پیش گوئیاں

آج ہم پی پی پر بات کریں گے۔ 1-3، دوسرے حصے میں - 4 اور 5، اور حصہ 3 میں - 6-10۔

1. عبوری بندش

آئیے کچھ آسان کے ساتھ شروع کریں: عبوری بندش ۔ یہ ایک معمولی تصور ہے جس کا اطلاق بہت سے ریاضی کی کارروائیوں پر ہوتا ہے، جیسے مساوات آپریٹر۔ اسے اس صورت میں اس طرح بنایا جا سکتا ہے: اگر A = B اور B = C، تو A = C۔

مشکل نہیں، ٹھیک ہے؟ لیکن اس کے ایس کیو ایل آپٹیمائزرز کے لیے کچھ دلچسپ مضمرات ہیں۔ آئیے ایک مثال دیکھتے ہیں۔ آئیے 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
...

آئیے اب اوریکل ڈی بی ایم ایس کے معاملے میں اس استفسار کو انجام دینے کے منصوبے پر ایک نظر ڈالتے ہیں:

--------------------------------------------------------------
| 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 انتخاب کی طرف لے جا سکتی ہے۔ اس لیے جب بھی آپ کر سکتے ہیں، اپنے جوائنز اور سادہ پیشین گوئیاں تیار کریں تاکہ عارضی بندش کا فائدہ اٹھایا جا سکے۔ کون سے دوسرے ڈیٹا بیس اس خصوصیت کی حمایت کرتے ہیں؟

ڈی بی 2

جی ہاں!

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 کے عمل درآمد کے منصوبے اس قسم کے تجزیے کے لیے موزوں نہیں ہیں۔ پیشین گوئی خود آؤٹ پٹ معلومات سے غائب ہے:

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

لیکن حقیقت یہ ہے کہ REF کالم میں const کی دو بار وضاحت کی گئی ہے یہ ظاہر کرتا ہے کہ دونوں جدولیں ایک مستقل قدر کی تلاش کر رہی ہیں۔ ایک ہی وقت میں، 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 predicate کے کالم کا حوالہ دیتا ہے۔ کارڈینیلیٹی سکور تقریباً وہی ہے جو اوریکل میں ہے۔ تو ہاں، MySQL عارضی بندش کی بھی حمایت کرتا ہے۔

پوسٹگری ایس کیو ایل

جی ہاں!

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)

ایس کیو ایل سرور

جی ہاں!

|--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 ہمیشہ سچ ہوتا ہے، NULL = NULL حساب کا نتیجہ NULL ہے، جو کہ تین قدر والی منطق کے مطابق ، FALSE کے برابر ہے۔ یہ کافی حد تک خود وضاحتی ہے، تو آئیے براہ راست یہ معلوم کریں کہ کون سے ڈیٹا بیس اس اصلاح کو انجام دیتے ہیں۔

ڈی بی 2

جی ہاں!

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

جیسا کہ آپ دیکھ سکتے ہیں، ACTOR ٹیبل تک رسائی کو پلان سے مکمل طور پر خارج کر دیا گیا ہے۔ اس میں صرف GENROW آپریشن ہے، جو صفر قطاریں بناتا ہے۔ کامل

مائی ایس کیو ایل

جی ہاں!

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 ہے، لیکن Id=1 کے ساتھ فلٹرنگ آپریشن (FILTER) بھی ہے، جہاں کبھی TRUE نہیں ہوگا۔ معیاری SQL بولین ڈیٹا کی قسم کو اوریکل کی ناپسندیدگی کی وجہ سے ، اوریکل صرف FALSE کی بجائے NULL IS NOT NULL کو پلان میں دکھاتا ہے۔ اوہ اچھا... لیکن سنجیدگی سے، اس پیش گوئی کو دیکھیں۔ مجھے 1000 لائن ذیلی درختوں اور انتہائی اعلی قیمت والی اقدار کے ساتھ عملدرآمد کے منصوبوں کو ڈیبگ کرنے کا موقع ملا ہے، صرف اس حقیقت کے بعد دریافت کرنے کے لئے کہ NULL IS NOT NULL فلٹر کے ذریعہ پورے ذیلی درخت کو "کاٹ" جا رہا ہے۔ تھوڑا حوصلہ افزا، میں آپ کو بتاتا ہوں۔

پوسٹگری ایس کیو ایل

جی ہاں!

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

پہلے سے بہتر۔ کوئی پریشان کن ACTOR ٹیبل کالز اور ایک صاف چھوٹی سی غلط پیش گوئی نہیں ہے۔

ایس کیو ایل سرور؟

جی ہاں!

|--Constant Scan

ایس کیو ایل سرور اسے " مستقل اسکین" کہتا ہے، جو ایک ایسا اسکین ہے جہاں کچھ نہیں ہوتا ہے - DB2 کی طرح۔ ہمارے تمام ڈیٹا بیس ناممکن پیشین گوئیوں کو خارج کر سکتے ہیں:

3. شمولیت کو ختم کریں۔

پچھلے حصے میں، ہم نے سنگل ٹیبل کے سوالات میں غیر ضروری ٹیبل تک رسائی کا مشاہدہ کیا۔ لیکن کیا ہوتا ہے اگر JOIN کو متعدد ٹیبل تک رسائی کی ضرورت نہ ہو؟ میں نے پہلے ہی اپنے بلاگ سے پچھلی پوسٹ میں JOIN کو ختم کرنے کے بارے میں لکھا تھا ۔ ایس کیو ایل انجن استفسار کی قسم اور بنیادی اور غیر ملکی کلیدوں کی موجودگی کی بنیاد پر یہ تعین کرنے کے قابل ہے کہ آیا دی گئی استفسار میں کسی خاص JOIN کی درحقیقت ضرورت ہے، یا اسے ختم کرنے سے استفسار کی اصطلاحات پر کوئی اثر نہیں پڑے گا۔ اگلی تمام تین مثالوں میں، JOIN کی ضرورت نہیں ہے۔ ایک اندرونی ... سے ایک جوائن کو غیر غیر ملکی کلید رکھنے سے ختم کیا جا سکتا ہے۔ اس کے بجائے:

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

"...-to-one" قسم کی اندرونی شمولیت کو تبدیل کیا جا سکتا ہے اگر کوئی غیر ملکی کلید موجود ہو۔ مندرجہ بالا استفسار کام کرتا ہے اگر غیر ملکی کلید NOT NULL رکاوٹ کے تابع ہو۔ اگر نہیں، مثال کے طور پر، جیسا کہ اس درخواست میں ہے:

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

پھر JOIN کو ابھی بھی ختم کیا جا سکتا ہے، لیکن آپ کو NOT NULL predicate شامل کرنا پڑے گا، اس طرح:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

اگر کوئی منفرد کلید موجود ہو تو "...-to-one" قسم کا OUTER 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) ہٹایا جا سکتا ہے۔ اس کے بجائے:

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 یہاں کے غیر متنازعہ رہنما ہیں! جاری ہے