مضمون کي سمجھڻ لاءِ علم جي سطح گھربل آھي: ڊيٽابيس ۽ SQL جي عام سمجھ، DBMS سان ڪجھ عملي تجربو.
غالباً سڀ کان اهم شيءِ جيڪا توهان سکي سگهو ٿا موثر SQL سوالن کي لکڻ لاءِ انڊيڪسنگ. بهرحال، ٻئي جاءِ تي، تمام گهڻو پوئتي، اها ڄاڻ آهي ته ڪيترن ئي SQL ڪلائنٽ کي ڊيٽابيس جي ضرورت آهي تمام گهڻو "غير ضروري پر ضروري ڪم" ڪرڻ لاءِ . مون کان پوء ورجايو:
”غير ضروري پر واجبي ڪم“ ڇا آهي؟ جيئن ته ڪئپٽن واضح اسان کي ٻڌائي ٿو، هوء:
ڪجھ به غير معمولي ناهي. اسان هڪ فلم ڊيٽابيس سان ڪم ڪري رهيا آهيون (جهڙوڪ Sakila ڊيٽابيس ) ۽ سڀني فلمن جو عنوان ۽ درجه بندي صارفين کي ڏيکارڻ چاهيون ٿا. هيٺ ڏنل سوال اهو نتيجو ڏئي سگهي ٿو جنهن جي اسان کي ضرورت آهي:
ساڄي پاسي توهان ڏسي سگهو ٿا ته ڪجهه پيچيده JSON لوڊ ٿي رهيو آهي:
ظاهر آهي، اسان يادگيري وغيره کي ضايع ڪري رهيا آهيون. اچو ته هن سوال کي متبادل طور تي غور ڪريون:
نوٽ ڪريو ته اداڪار جي ٽيبل ۾ صرف 4 ڪالمن آھن، تنھنڪري بيانن 1 ۽ 2 جي وچ ۾ ڪارڪردگي فرق ايترو وڏو نه آھي، پر اھو اڃا به اھم آھي. مان اهو به نوٽ ڪندس ته مون استعمال ڪيو Oracle آپٽمائزر اشارن لاءِ optimizer کي سوال لاءِ هڪ يا ٻيو مخصوص انڊيڪس چونڊيو. آپريٽر 3 اسان جي نسل جو اڻڄاتل فاتح آهي. ان جي ڪارڪردگي تمام بهتر آهي، ۽ اسان هڪ انتهائي سادي سوال بابت ڳالهائي رهيا آهيون. ٻيهر، جڏهن اسان لکندا آهيون SELECT *، اسان ڊيٽابيس لاءِ غير ضروري پر لازمي ڪم ٺاهيندا آهيون جيڪو اهو بهتر نٿو ڪري سگهي. هوءَ ڍڪڻ واري انڊيڪس کي نه چونڊيندي ڇو ته ان ۾ هن جي چونڊيل LAST_NAME انڊيڪس کان ٿورو مٿي اوور هيڊ آهي، ۽، ٻين شين سان گڏ، هن کي اڃا تائين ٽيبل تائين پهچڻو آهي هڪ بيڪار LAST_UPDATE ڪالمن کي ٻيهر حاصل ڪرڻ لاءِ، مثال طور. پر جيتري قدر اسان SELECT * جو تجزيو ڪيو، اوترو ئي خراب شيون نڪرنديون. اچو ته ڳالهايون...
ڇا، سنجيده، صحيح؟ ھاڻي توھان سمجھڻ شروع ڪيو آھي ته مان ڇا ڳالھائي رھيو آھيان. پر تصور ڪريو ته اسان ماضي جي غلطين مان ڪجھ سکيو آھي، ۽ ھن تي عمل ڪريو، وڌيڪ بھترين سوال:
پر ائين نٿو ٿئي. اصلاح ڪندڙ (يا حتي parser) پڪ ڪري سگھي ٿو ته EXISTS predicate (SELECT ..) ۾ ڪو به چونڊ فهرست عنصر سوال جو نتيجو تبديل نه ڪندو، تنھنڪري ان کي عمل ڪرڻ جي ڪا ضرورت ناھي. هن وانگر!
غير ضروري پر گهربل ڪم |
غير ضروري
اچو ته اسان جي ڪلائنٽ ايپليڪيشن کي هيٺين ڊيٽا جي ضرورت آهي:SELECT title, rating
FROM film
بهرحال، اسان جي ايپليڪيشن (يا اسان جي ORM) بدران هن سوال تي عمل ڪري ٿو:
SELECT *
FROM film
نتيجي طور اسان کي ڇا حاصل آهي؟ گمان. اسان کي تمام گهڻو بيڪار معلومات ملي ٿي:
- ڊسڪ کان
- ڪيش ڪرڻ
- تار ذريعي
- ڪسٽمر جي ياد ۾
- ۽ آخرڪار اڇلايو ويو [غير ضروري طور تي]
لازمي
۽ هاڻي - بدترين حصو. جيتوڻيڪ اصلاح ڪندڙ هاڻي تمام گهڻو ڪري سگهن ٿا، اهي عمل ڊيٽابيس لاءِ لازمي آهن. ڊيٽابيس کي ڄاڻڻ جو ڪو طريقو ناهي ته ڪلائنٽ ايپليڪيشن کي هن ڊيٽا جي 95٪ جي ضرورت ناهي. ۽ اهو صرف آسان مثال آهي. تصور ڪريو ڪيترن ئي جدولن کي ڳنڍڻ... پوءِ ڇا، توهان چئو ٿا، پر ڊيٽابيس تيز آهن؟ اچو ته مان توهان کي ڪجهه شين تي روشني ڏيان جن بابت توهان شايد نه سوچيو هجي. يقينا، هڪ فرد جي درخواست جي عمل جو وقت واقعي ڪنهن به شيء تي اثر انداز نٿو ڪري. ٺيڪ، اهو هڪ اڌ ڀيرا سست ٿي ويو، پر اسان ان جي ذريعي حاصل ڪنداسين، صحيح؟ سهولت لاءِ؟ ڪڏهن ڪڏهن اهو سچ آهي. پر جيڪڏهن توهان هميشه سهولت لاءِ ڪارڪردگي قربان ڪريو ٿا ، اهي ننڍيون شيون شامل ٿيڻ شروع ٿينديون. اسان هاڻي ڪارڪردگي بابت (انفرادي درخواستن تي عملدرآمد جي رفتار) جي باري ۾ نه ڳالهائينداسين، پر throughput بابت (سسٽم جي جواب جو وقت)، ۽ پوء سنجيده مسئلا شروع ٿي ويندا، جن کي حل ڪرڻ ايترو آسان ناهي. اهو آهي جڏهن توهان اسڪاليبلٽي وڃائي ڇڏيو. اچو ته عمل جي منصوبن تي هڪ نظر رکون، هن صورت ۾، Oracle DBMS:--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 166K|
| 1 | TABLE ACCESS FULL| FILM | 1000 | 166K|
--------------------------------------------------
جي مقابلي ۾:
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 20000 |
| 1 | TABLE ACCESS FULL| FILM | 1000 | 20000 |
--------------------------------------------------
SELECT عنوان جي بدران SELECT * سوال هلائڻ، درجه بندي ڊيٽابيس ۾ 8 ڀيرا وڌيڪ ميموري استعمال ڪري ٿي. ڪجھ به غير متوقع نه، صحيح؟ اسان کي خبر هئي ته اهو ٿيندو. پر اسان اڃا تائين ان سان متفق آهيون اسان جي ڪيترن ئي درخواستن لاءِ جنهن ۾ اسان کي صرف هن ڊيٽا جي ضرورت ناهي. اسان ڊيٽابيس لاءِ غير ضروري پر لازمي ڪم ٺاهيندا آهيون ، جيڪو جمع ٿيندو رهندو آهي. اسان استعمال ڪريون ٿا 8 ڀيرا وڌيڪ ياداشت جي ضرورت کان وڌيڪ (ضرب بدلائي ويندي). ان کان علاوه، ٻين سڀني مرحلن تي (ڊسڪ I/O، نيٽ ورڪ تي ڊيٽا جي منتقلي، ڪلائنٽ طرفان ميموري واپرائڻ) مسئلا بلڪل ساڳيا آهن، پر آئون انهن کي ڇڏي ڏيندس ۽ ان جي بدران ڏسو ...
Indexes استعمال ڪندي
اڄ اڪثر ڊيٽابيس اڳ ۾ ئي انڊيڪس کي ڍڪڻ جي تصور کي ساراهيو آهي . هڪ ڍڪڻ واري انڊيڪس بذات خود هڪ خاص قسم جي انڊيڪس ناهي. پر اهو ٿي سگهي ٿو "خاص انڊيڪس" ڪنهن خاص سوال لاءِ، يا ته "حادثي سان" يا ڇاڪاڻ ته اهو ٿيڻ جو ارادو هو. هيٺ ڏنل سوال تي غور ڪريو:SELECT *
FROM actor
WHERE last_name LIKE 'A%'
ان جي عمل جي لحاظ کان غير متوقع ڪجھ به نه آھي. هي هڪ سادي درخواست آهي. انڊيڪس ذريعي حد ڏسو، ٽيبل تائين رسائي ڪريو - ۽ توھان ڪيو آھي:
-------------------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 |
| 1 | TABLE ACCESS BY INDEX ROWID| ACTOR | 8 |
|* 2 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 8 |
-------------------------------------------------------------------
سٺو منصوبو، اهو ناهي؟ خير، جيڪڏهن اسان کي واقعي هن جي ضرورت آهي، پوء نه:
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
هن جو منصوبو هي آهي:
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 8 |
|* 1 | INDEX RANGE SCAN| IDX_ACTOR_NAMES | 8 |
----------------------------------------------------
اسان ٽيبل تائين رسائي کي مڪمل طور تي ختم ڪرڻ جي قابل ٿي ويا هئاسين، هڪ انڊيڪس جي موجودگي جي مهرباني جيڪا اسان جي سوال جي سڀني ضرورتن کي پورو ڪري ٿي... هڪ ڍڪڻ واري انڊيڪس. ڇا اهو ضروري آهي؟ ۽ ڪيئن! اهو طريقو توهان کي ڪجهه سوالن کي تيز ڪرڻ جي اجازت ڏئي ٿو شدت جي ترتيب سان (يا انهن کي شدت جي ترتيب سان سست ڪيو جڏهن انڊيڪس ڪجهه تبديلين کان پوء ڍڪيل نه آهي). ڍڪڻ واري انڊيڪس هميشه استعمال نه ٿي ڪري سگھجي. توهان کي انڊيڪس لاءِ ادا ڪرڻو پوندو ۽ توهان کي انهن مان تمام گهڻو شامل نه ڪرڻ گهرجي. پر هن معاملي ۾، سڀڪنھن شيء کي پڌرو آهي. اچو ته ڪارڪردگي جو جائزو وٺون:
SET SERVEROUTPUT ON
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT NUMBER := 100000;
BEGIN
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Улучшенный request, но все равно с доступом к таблице
SELECT /*+INDEX(actor(last_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Оптимальный request: покрывающий индекс
SELECT /*+INDEX(actor(last_name, first_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/
نتيجي طور، اسان حاصل ڪريون ٿا: آپريٽر 1: +000000000 00:00:02.479000000 آپريٽر 2: +000000000 00:00:02.261000000 آپريٽر 3: +000000000 00:00:01.857000000 |
SQL تبديليون
Optimizers تمام سٺو ڪم ڪن ٿا ڇاڪاڻ ته اهي SQL سوالن کي تبديل ڪن ٿا ( مون ٻڌايو ته اهو ڪيئن ڪم ڪري ٿو منهنجي تازي ڳالهه ٻولهه ۾ Voxxed Days in Zurich ). مثال طور، اتي هڪ انتهائي طاقتور "استثنا شامل ڪريو" تبديلي آهي. هيٺ ڏنل مددگار ڏسڻ تي غور ڪريو جيڪو اسان کي ٺاهڻو پوندو انهن سڀني جدولن ۾ شامل ٿيڻ کان بچڻ لاءِ هر وقت دستي طور تي:CREATE VIEW v_customer AS
SELECT
c.first_name, c.last_name,
a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)
اهو نظارو صرف سڀني "...-to-one" کي CUSTOMER ڪسٽمر ٽيبل ۽ انهن جي ايڊريس جي حصن لاءِ مختلف جدولن جي وچ ۾ شامل ڪري ٿو. مهرباني، عام ڪرڻ. تصور ڪريو ته هن نظر سان ٿورو ڪم ڪرڻ کان پوء، اسان ان جي عادت پئجي وئي ۽ هيٺ ڏنل جدولن کي وساري ڇڏيو. ۽ ھاڻي اسان ھيٺ ڏنل سوال تي عمل ڪريون ٿا:
SELECT *
FROM v_customer
نتيجي طور، اسان هڪ تمام شاندار منصوبو حاصل ڪندا آهيون:
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 599 | 47920 | 14 |
|* 1 | HASH JOIN | | 599 | 47920 | 14 |
| 2 | TABLE ACCESS FULL | COUNTRY | 109 | 1526 | 2 |
|* 3 | HASH JOIN | | 599 | 39534 | 11 |
| 4 | TABLE ACCESS FULL | CITY | 600 | 10800 | 3 |
|* 5 | HASH JOIN | | 599 | 28752 | 8 |
| 6 | TABLE ACCESS FULL| CUSTOMER | 599 | 11381 | 4 |
| 7 | TABLE ACCESS FULL| ADDRESS | 603 | 17487 | 3 |
----------------------------------------------------------------
خير، يقينا. ڊيٽابيس انهن سڀني جوائنز ۽ مڪمل ٽيبل اسڪين ڪري رهيو آهي ڇاڪاڻ ته اهو ئي آهي جيڪو اسان ان کي ڪرڻ لاءِ چيو آهي - هي سڀ ڊيٽا حاصل ڪريو. هاڻي، ٻيهر، تصور ڪريو ته اسان سڀني کي واقعي جي ضرورت هئي:
SELECT first_name, last_name
FROM v_customer
هاڻي اچو ته ڏسو ته ڇا ٿيو!
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 599 | 16173 | 4 |
| 1 | NESTED LOOPS | | 599 | 16173 | 4 |
| 2 | TABLE ACCESS FULL| CUSTOMER | 599 | 11381 | 4 |
|* 3 | INDEX UNIQUE SCAN| SYS_C007120 | 1 | 8 | 0 |
------------------------------------------------------------------
عمل جي لحاظ کان بھترين تبديليون . شامل ٿيڻ کي ختم ڪيو ويو آهي ڇاڪاڻ ته اصلاح ڪندڙ هاڻي ڏسي سگهي ٿو ته اهي بيڪار آهن ، ۽ جيڪڏهن اهو ڏسي سگهي ٿو ته (۽ توهان ان ڪم کي لازمي نه ڪيو آهي * چونڊيو)، ته پوء اهو صرف اهو سڀ ڪم نٿو ڪري سگهي. هن معاملي ۾ ائين ڇو آهي؟ غير ملڪي چيڪ CUSTOMER.ADDRESS_ID کي پرائمري ڪيئي ADDRESS.ADDRESS_ID جي ضمانت ڏئي ٿي بلڪل ھڪڙي قدر جي آخري، جنھن جو مطلب آھي ته JOIN آپريشن "...-to-one" شامل ٿيندو جيڪو قطارن جو تعداد وڌائي يا گھٽ نٿو ڪري. . ۽ جيئن ته اسان ڪنهن به قطار کي چونڊيو يا درخواست نه ڪندا آهيون، پوء انهن کي لوڊ ڪرڻ ۾ ڪو به مقصد ناهي. JOIN کي هٽائڻ سان شايد سوال جي نتيجي تي ڪو به اثر نه پوندو. ڊيٽابيس اهو هر وقت ڪندا آهن. توھان ھيٺ ڏنل سوال ھلائي سگھوٿا تقريباً ڪنھن ڊيٽابيس تي:
-- Oracle
SELECT CASE WHEN EXISTS (
SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
انهي صورت ۾، توهان کي اميد آهي ته هڪ رياضياتي استثنا اڇلايو وڃي، جيئن هيٺ ڏنل سوال تي عمل ڪندي:
SELECT 1 / 0 FROM dual
ٿيو: ORA-01476: تقسيم صفر جي برابر آهي |
ان دوران...
ORMs سان گڏ سڀ کان وڌيڪ پريشان ڪندڙ مسئلن مان هڪ اهو آهي ته اهي SELECT * سوالن کي لکڻ لاء تمام آسان آهن. حقيقت ۾، مثال طور، HQL / JPQL ۾ اهي عام طور تي ڊفالٽ طور استعمال ڪيا ويا آهن. اسان SELECT شق کي مڪمل طور تي ختم ڪري سگهون ٿا، ڇو ته اسان سڄي اداري کي ٻيهر حاصل ڪرڻ وارا آهيون، صحيح؟ مثال طور:FROM v_customer
مثال طور، Vlad Mihalcea، Hibernate سان ترقي ڪرڻ جو هڪ ماهر ۽ وڪيل ، استعمال ڪرڻ جي صلاح ڏئي ٿو [qualified] سوال تقريباً هميشه جڏهن توهان کي پڪ آهي ته توهان چيڪ آئوٽ کان پوءِ ڪا به تبديلي بچائڻ نٿا چاهيو. ORMs وڏي پئماني تي اعتراض جي گراف جي تسلسل جي مسئلي جي حل کي آسان بڻائي ٿو. نوٽ: تسلسل. اصل ۾ آبجیکٹ گرافس کي تبديل ڪرڻ ۽ تبديلين کي بچائڻ جا ڪم هڪ ٻئي سان ڳنڍيل آهن. پر جيڪڏهن توهان ائين ڪرڻ وارا نه آهيو، ته پوء جوهر ڪڍڻ جي تڪليف ڇو؟ ڇو نه هڪ [سڌريل] درخواست لکي؟ اچو ته صاف رهون: ڪارڪردگي جي نقطي نظر کان، هڪ سوال لکڻ خاص طور تي توهان جي مخصوص استعمال جي صورت ۾ ترتيب ڏنل ڪنهن ٻئي اختيار کان واضح طور تي بهتر آهي. توهان شايد پرواه نه ڪندا آهيو ڇو ته توهان جو ڊيٽا سيٽ ننڍڙو آهي ۽ اهو مسئلو ناهي. زبردست. پر جڏهن توهان کي آخرڪار اسپيبليبلٽي جي ضرورت پوندي، توهان جي ايپليڪيشنن کي ٻيهر ڊزائين ڪرڻ بدران سوالن کي استعمال ڪرڻ لاءِ استعمال ڪرڻ جي بدران اداري گراف جي لازمي ٽرورسل ڪافي مشڪل هوندي. ۽ توهان کي ان کان سواء ڪجهه ڪرڻو پوندو.
ڳولهڻ لاءِ قطارون ڳڻڻ ته ڇا ڪا شيءِ موجود آهي
وسيلن جي بدترين بربادي مان هڪ آهي COUNT(*) سوالن کي صرف اهو ڏسڻ لاءِ ته ڇا ڪا شيءِ ڊيٽابيس ۾ آهي. مثال طور، اسان کي اهو معلوم ڪرڻ جي ضرورت آهي ته ڇا ڏنو ويو صارف وٽ آرڊر آهن. ۽ اسان درخواست تي عمل ڪريون ٿا:SELECT count(*)
FROM orders
WHERE user_id = :user_id
ابتدائي. جيڪڏهن COUNT = 0، پوء ڪو به حڪم نه آهي. ٻي صورت ۾، ها. ڪارڪردگي ايتري خراب نه هوندي ڇو ته اسان وٽ شايد ORDERS.USER_ID ڪالمن تي هڪ انڊيڪس آهي. پر توهان ڇا ٿا سمجهو ته مٿين سوال جي ڪارڪردگي هيٺ ڏنل آپشن جي مقابلي ۾ هوندي.
-- Oracle
SELECT CASE WHEN EXISTS (
SELECT *
FROM orders
WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
SELECT *
FROM orders
WHERE user_id = :user_id
)
اهو معلوم ڪرڻ لاءِ ڪنهن راڪيٽ سائنسدان جي ضرورت نه آهي ته هڪ حقيقي وجود جو اڳڪٿي اضافي تارن جي ڳولا بند ڪري ڇڏيندو جيئن ئي اهو پهريون ڀيرو ملندو . تنهن ڪري جيڪڏهن نتيجو نڪرندو آهي ”نه آرڊر“، پوءِ رفتار برابر ٿي ويندي. جيڪڏهن، تنهن هوندي، نتيجو آهي "ها، آرڊر آهن،" پوء ان صورت ۾ جتي صحيح مقدار کي ڳڻڻ جي ضرورت ناهي، جواب گهڻو تيزيء سان ملي ويندو. آخرڪار، اسان کي صحيح نمبر ۾ دلچسپي نه آهي. بهرحال، اسان ڊيٽابيس کي ٻڌايو ته ان کي ڳڻيو ( غير ضروري ڪم ) ۽ ڊيٽابيس کي خبر ناهي ته اسان سڀني نتيجن کي نظر انداز ڪري رهيا آهيون 1 کان وڌيڪ ( گهربل ڪم ). يقينا، اهو تمام گهڻو خراب ٿيندو جيڪڏهن اسان هڪ JPA جي پٺڀرائي واري مجموعي تي list.size() کي سڏيندا آهيون ساڳيا نتيجا حاصل ڪرڻ لاءِ. مون اڳ ۾ ئي هن بابت پنهنجي بلاگ تي اڳ ۾ ئي لکيو آهي، ۽ ٻنهي اختيارن جي تقابلي جاچ ڪئي ...
GO TO FULL VERSION