زبردست SQL اصلاحون جيڪي قيمت ماڊل تي منحصر نه آھن. حصو 1

پنج سادي اصلاحون جيڪي صرف ميٽاڊيٽا (يعني رڪاوٽون) ۽ خود سوال جي بنياد تي لاڳو ڪري سگهجن ٿيون، اسان توهان کي لوڪاس ايڊر جي مضمون جي موافقت پيش ڪريون ٿا، جيڪي انهن لاءِ ٺهيل آهن جن کي ڊيٽابيس ۽ SQL جي عام فهم آهي، انهي سان گڏ DBMS سان گڏ ڪجهه عملي تجربو . قيمت جي اصلاح اصل ۾ جديد ڊيٽابيس ۾ SQL سوالن کي بهتر ڪرڻ جو هڪ معياري طريقو آهي. اهو ئي سبب آهي ته 3GL (ٽين نسل جي پروگرامنگ ٻولين) ۾ دستي طور تي هڪ پيچيده الگورٿم لکڻ ڏاڍو ڏکيو آهي ، جنهن جي ڪارڪردگي هڪ جديد اصلاح ڪندڙ طرفان ٺاهيل متحرڪ طور تي ڳڻپيوڪر عملدرآمد منصوبي کان وڌيڪ هوندي. اڄ اسان قيمت جي اصلاح تي بحث نه ڪنداسين، اهو آهي، ڊيٽابيس جي قيمت ماڊل جي بنياد تي. اسان ڏسنداسين وڌيڪ آسان اصلاحن کي. جيڪي صرف ميٽاداٽا (يعني پابنديون) ۽ درخواست جي بنياد تي لاڳو ڪري سگھجن ٿيون. عام طور تي ڊيٽابيس لاءِ انهن جو عمل نيوٽن بائنوميل نه هوندو آهي، ڇاڪاڻ ته، هن صورت ۾، ڪا به اصلاح هڪ بهتر عمل جي منصوبي جي اڳواڻي ڪندي، بغير انڊيڪس جي موجودگي، ڊيٽا جي مقدار ۽ ڊيٽا جي ورڇ جي خرابي جي. "Not a Newton binomial" ان معنيٰ ۾ نه آهي ته اصلاح کي لاڳو ڪرڻ ڪيترو آسان آهي، پر ڇا اهو ٿيڻ گهرجي. اهي اصلاحون غير ضروري، اضافي ڪم کي ختم ڪن ٿيون [ڊيٽابيس لاءِ] ( جيئن غير ضروري، گهربل ڪم جي مخالفت، جنهن بابت مون اڳ ۾ ئي لکيو آهي ).

اهي اصلاحون ڇا لاءِ استعمال ڪيون ويون آهن؟

انهن مان گهڻا استعمال ڪيا ويا آهن:

• سوالن ۾ بگ فيڪس؛
• نظارن کي ٻيهر استعمال ڪرڻ جي اجازت ڏئي ٿو ڊيٽابيس کان سواءِ اصل ۾ ڏسڻ جي منطق تي عمل ڪندي.

پهرين صورت ۾، هڪ چئي سگهي ٿو: "پوء ڇا، بس اڳتي وڌو ۽ هن بيوقوف SQL سوال کي درست ڪريو." پر جنهن ڪڏهن به غلطي نه ڪئي آهي ان کي پهرين مون تي پٿر اڇلائڻ ڏيو. ٻيو ڪيس خاص طور تي دلچسپ آهي: اهو اسان کي ڏسڻ ۽ ٽيبل جي ڪم جي پيچيده لائبريري ٺاهڻ جي صلاحيت ڏئي ٿو جيڪا ڪيترن ئي تہن ۾ ٻيهر استعمال ڪري سگهجي ٿي.

استعمال ٿيل ڊيٽابيس

هن آرٽيڪل ۾ اسين 10 SQL اصلاحن جو مقابلو ڪنداسين پنجن وڏي پيماني تي استعمال ٿيندڙ ڊي بي ايم ايس ۾ ( ڊيٽابيس جي درجه بندي جي مطابق ):

• Oracle 12.2؛
• MySQL 8.0.2؛
• SQL سرور 2014؛
• PostgreSQL 9.6؛
• DB2 LUW 10.5.

هڪ ٻي درجه بندي تقريبن ان جي گونج ڪري ٿي. هميشه وانگر، هن آرٽيڪل ۾ آئون سوال ڪندس سکيلا ڊيٽابيس . هتي انهن ڏهن قسمن جي اصلاحن جي هڪ فهرست آهي:

1. عارضي بندش؛
2. ناممڪن اڳڪٿيون ۽ غير ضروري ٽيبل ڪالون؛
3. شامل ٿيڻ کي ختم ڪرڻ؛
4. "بي معنيٰ" اڳڪٿين جو خاتمو؛
5. EXISTS ذيلي سوالن ۾ تخمينو؛
6. predicates جو ضم ڪرڻ؛
7. واضح طور تي خالي سيٽ؛
8. پابنديون چيڪ ڪريو؛
9. غير ضروري reflexive ڪنيڪشن؛
10. Pushdown predicates

اڄ اسان بحث ڪنداسين 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 پسند ڏانهن. تنهن ڪري جڏهن به توهان ڪري سگهو ٿا، پنهنجي جوائن ۽ سادي اڳڪٿين کي ترتيب ڏيو ته جيئن منتقلي بندش جو فائدو وٺن. ٻيو ڪهڙو ڊيٽابيس هن خصوصيت جي حمايت ڪن ٿا؟

ڊي بي 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)

رستي جي ذريعي، جيڪڏهن توهان پسند ڪريو ٿا شاندار عملدرآمد منصوبا جهڙوڪ، چيڪ ڪريو مارڪس ونند جي اسڪرپٽ .

MySQL

بدقسمتي سان، 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. Cardinality سکور لڳ ڀڳ ساڳيو آهي Oracle ۾. سو ها، 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 سرور

ها!

|--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 آپريشن تي مشتمل آهي، جيڪو صفر قطار ٺاهي ٿو. ڪامل.

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 سان آهي، جتي ڪڏهن به صحيح نه ٿيندو. Oracle جي معياري SQL Boolean ڊيٽا جي قسم جي ناپسنديدگي جي ڪري ، Oracle ڏيکاري ٿو 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 ٽيبل ڪال ۽ هڪ صاف ٿورو غلط اڳڪٿي ناهي.

SQL سرور؟

ها!

|--Constant Scan

SQL سرور ان کي سڏي ٿو " مسلسل اسڪين"، جيڪو هڪ اسڪين آهي جتي ڪجھ به نه ٿيندو آهي - DB2 وانگر. اسان جا سڀئي ڊيٽابيس ناممڪن اڳڪٿين کي خارج ڪري سگھن ٿا:

3. شامل ٿيڻ کي ختم ڪريو

پوئين حصي ۾، اسان غير ضروري ٽيبل جي رسائي کي سنگل ٽيبل سوالن ۾ ڏٺو. پر ڇا ٿيندو جيڪڏهن 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

"...-to-one" قسم جي هڪ اندروني شموليت کي تبديل ڪري سگهجي ٿو جيڪڏهن ڪو غير ملڪي ڪيجي آهي. مٿي ڄاڻايل سوال ڪم ڪري ٿو جيڪڏهن غير ملڪي ڪيئي غير 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" قسم جي هڪ ٻاهرئين شموليت کي ختم ڪري سگهجي ٿو جيڪڏهن اتي هڪ منفرد ڪنجي آهي. ان جي بدران:

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 سرور هتي آهن اڻڄاتل اڳواڻ! جاري رکڻ گهرجي