🥇 هڪ SQL تحقيق جي ڪهاڻي

گذريل ڊسمبر مون VWO سپورٽ ٽيم کان هڪ دلچسپ بگ رپورٽ حاصل ڪئي. هڪ وڏي ڪارپوريٽ ڪلائنٽ لاءِ تجزياتي رپورٽن مان هڪ لاءِ لوڊ ٿيڻ جو وقت ممنوع لڳي رهيو هو. ۽ جيئن ته هي منهنجي ذميواري جو علائقو آهي، مون فوري طور تي مسئلو حل ڪرڻ تي ڌيان ڏنو.

prehistory

اهو واضح ڪرڻ لاءِ ته مان ڪهڙي ڳالهه ڪري رهيو آهيان، مان توهان کي VWO بابت ٿورڙو ٻڌايان ٿو. هي هڪ پليٽ فارم آهي جنهن سان توهان پنهنجي ويب سائيٽن تي مختلف ٽارگيٽ ڪيل مهم شروع ڪري سگهو ٿا: A/B تجربا ڪريو، سياحن ۽ تبادلن کي ٽريڪ ڪريو، سيلز فينل جو تجزيو ڪريو، گرمي جا نقشا ڏيکاريو ۽ دورو رڪارڊنگ کيڏيو.

پر پليٽ فارم بابت سڀ کان اهم شيء رپورٽنگ آهي. مٿين سڀني ڪمن سان ڳنڍيل آهن. ۽ ڪارپوريٽ ڪلائنٽ لاءِ، معلومات جي وڏي مقدار هڪ طاقتور پليٽ فارم کان سواءِ بيڪار هوندي جيڪا ان کي تجزياتي فارم ۾ پيش ڪري ٿي.

پليٽ فارم استعمال ڪندي، توهان ڪري سگهو ٿا بي ترتيب سوال هڪ وڏي ڊيٽا سيٽ تي. هتي هڪ سادي مثال آهي:

<تاريخ d1> کان <تاريخ d2> تائين صفحي "abc.com" تي سڀ ڪلڪ ڏيکاريو انھن ماڻھن لاءِ جن ڪروم استعمال ڪيو يا (يورپ ۾ واقع ۽ آئي فون استعمال ڪيو)

Boolean آپريٽرز تي ڌيان ڏيو. اهي ڪلائنٽ لاءِ موجود آهن سوالن جي انٽرفيس ۾ نمونا حاصل ڪرڻ لاءِ پاڻمرادو پيچيده سوال ڪرڻ لاءِ.

سست درخواست

سوال ۾ گراهڪ ڪجھھ ڪم ڪرڻ جي ڪوشش ڪري رھيو ھو جيڪو غير معمولي طور تي جلدي ڪم ڪرڻ گھرجي:

ڏيکاريو سيشن جا سمورا رڪارڊ استعمال ڪندڙن لاءِ جيڪي URL سان گڏ ڪنهن به صفحي جو دورو ڪيو "/jobs"

هن سائيٽ تي ٽرئفڪ جي هڪ ٽين هئي ۽ اسان صرف ان لاءِ هڪ لک کان وڌيڪ منفرد URL محفوظ ڪري رهيا هئاسين. ۽ اهي ڳولڻ چاهيندا هئا هڪ بلڪل سادو URL ٽيمپليٽ جيڪو انهن جي ڪاروباري ماڊل سان لاڳاپيل آهي.

ابتدائي تحقيق

اچو ته هڪ نظر رکون ته ڊيٽابيس ۾ ڇا ٿي رهيو آهي. هيٺ ڏنل اصل سست SQL سوال آهي:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

۽ هتي وقت آهن:

منصوبه بندي جو وقت: 1.480 ايم ايس ايگزيڪيوشن جو وقت: 1431924.650 ايم ايس

پڇا ڳاڇا ڪئي وئي 150 هزار قطارون. سوال جي منصوبه بندي ڪندڙ ڪجهه دلچسپ تفصيل ڏيکاريا، پر ڪابه واضح رڪاوٽ نه.

اچو ته درخواست جو وڌيڪ مطالعو ڪريون. جيئن توهان ڏسي سگهو ٿا، هو ڪندو آهي JOIN ٽي ٽيبل:

سيشن: سيشن جي معلومات ڏيکارڻ لاءِ: برائوزر، يوزر ايجنٽ، ملڪ، وغيره.
رڪارڊنگ_ڊيٽا: رڪارڊ ٿيل URLs، صفحا، دورن جو عرصو
يوزائين: تمام وڏا URLs نقل ڪرڻ کان بچڻ لاءِ، اسين انھن کي الڳ جدول ۾ ذخيرو ڪريون ٿا.

اهو پڻ نوٽ ڪريو ته اسان جا سڀئي ٽيبل اڳ ۾ ئي ورهاڱي وارا آهن account_id. اهڙيء طرح، هڪ صورتحال جتي هڪ خاص طور تي وڏو اڪائونٽ ٻين لاء مسئلا پيدا ڪري ٿي خارج ٿيل آهي.

اشارا ڳولي رھيا آھن

ويجهي معائني تي، اسان ڏسون ٿا ته ڪجهه خاص درخواست سان غلط آهي. اهو هن لائن تي هڪ ويجهي نظر وٺڻ جي قابل آهي:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

پهريون خيال اهو هو ته شايد ان ڪري ILIKE انهن سڀني ڊگھي URLs تي (اسان وٽ 1,4 ملين کان مٿي آهن ڌار ڌار هن اڪائونٽ لاءِ گڏ ڪيل URLs) ڪارڪردگي متاثر ٿي سگھي ٿي.

پر نه، اها ڳالهه ناهي!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

ٽيمپليٽ جي ڳولا جي درخواست پاڻ کي صرف 5 سيڪنڊن ۾ وٺندي آهي. هڪ ملين منفرد URLs ۾ نموني ڳولڻ واضح طور تي ڪو مسئلو ناهي.

لسٽ تي ايندڙ مشڪوڪ ڪيترائي آهن JOIN. شايد انهن جو گهڻو استعمال سست رفتار جو سبب بڻيل آهي؟ عام طور تي JOINڪارڪردگي جي مسئلن لاء سڀ کان وڌيڪ واضح اميدوار آهن، پر مون کي يقين نه آيو ته اسان جو ڪيس عام هو.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

۽ اهو به اسان جو معاملو نه هو. JOINڪافي تيز ثابت ٿيو.

شڪ جي دائري کي تنگ ڪرڻ

مان ڪنهن به ممڪن ڪارڪردگي بهتري حاصل ڪرڻ لاءِ سوال کي تبديل ڪرڻ شروع ڪرڻ لاءِ تيار هوس. منهنجي ٽيم ۽ مون ترقي ڪئي 2 مکيه خيالن:

استعمال ڪريو EXISTS subquery URL لاءِ: اسان ٻيهر چيڪ ڪرڻ چاهيون ٿا ته ڇا URLs لاءِ ذيلي پڇاڙيءَ ۾ ڪو مسئلو آهي. هن کي حاصل ڪرڻ جو هڪ طريقو صرف استعمال ڪرڻ آهي EXISTS. EXISTS ڪري سگهي ٿو ڪارڪردگي کي تمام گهڻو بهتر بڻائي ٿو ڇاڪاڻ ته اهو فوري طور تي ختم ٿي ويندو آهي جيئن ئي اهو صرف هڪ تار ڳولي ٿو جيڪو شرط سان ملندو آهي.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

خير، ها. پڇا ڳاڇا جڏهن لپي وئي EXISTS، هر شي کي تمام تيز بڻائي ٿو. ايندڙ منطقي سوال آهي ڇو درخواست سان JOIN-امي ۽ ذيلي ذيلي خود انفرادي طور تي تيز آهن، پر گڏو گڏ تمام سست آهن؟

ذيلي سوال کي CTE ڏانهن منتقل ڪرڻ : جيڪڏهن سوال پنهنجو پاڻ تي تيز آهي، اسان آسانيءَ سان حساب ڪري سگھون ٿا تيزيءَ جو نتيجو پهريون ۽ پوءِ ان کي مهيا ڪري بنيادي سوال

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

پر اهو اڃا تائين تمام سست هو.

مجرم جي ڳولا

ان سڄي عرصي ۾، منهنجي اکين اڳيان هڪ ننڍڙي شيءِ چمڪي رهي هئي، جنهن کي مان مسلسل هڪ طرف ڇڪيندو رهيس. پر جيئن ته ٻيو ڪجهه به نه بچيو هو، تنهن ڪري مون به هن کي ڏسڻ جو فيصلو ڪيو. مان ڳالهائي رهيو آهيان && آپريٽر خدا حافظ EXISTS صرف بهتر ڪارڪردگي && سست سوال جي سڀني نسخن ۾ صرف باقي عام عنصر هو.

ڏسي رهيو آهي دستاويز، اسان اهو ڏسون ٿا && استعمال ڪيو ويندو آھي جڏھن توھان کي ٻن صفن جي وچ ۾ عام عنصر ڳولڻ جي ضرورت آھي.

اصل درخواست ۾ هي آهي:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

جنهن جو مطلب آهي ته اسان پنهنجي URLs تي نمونن جي ڳولا ڪندا آهيون، پوءِ عام پوسٽن سان گڏ سڀني URLs سان چونڪ ڳوليندا آهيون. اهو ٿورڙو مونجهارو آهي ڇو ته هتي "urls" سڀني URLs تي مشتمل ٽيبل ڏانهن اشارو نه آهي، پر ٽيبل ۾ "urls" ڪالمن ڏانهن. recording_data.

جي حوالي سان وڌندڙ شڪ سان &&، مون انهن لاءِ تصديق ڳولڻ جي ڪوشش ڪئي سوال جي منصوبي ۾ ٺاهيل EXPLAIN ANALYZE (مون وٽ اڳ ۾ ئي هڪ منصوبو محفوظ ڪيو ويو هو، پر آئون عام طور تي SQL ۾ تجربا ڪرڻ کان وڌيڪ آرامده آهيان سوالن جي رٿابندي ڪندڙن جي مبهميت کي سمجهڻ جي ڪوشش ڪرڻ کان).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

فلٽر جون ڪيتريون ئي لائينون هيون صرف مان &&. جنهن جو مطلب اهو ٿيو ته هي آپريشن نه رڳو مهانگو هو، پر ڪيترائي ڀيرا ڪيو ويو.

مون هن حالت کي الڳ ڪري آزمايو

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

اهو سوال سست هو. ڇاڪاڻ ته JOIN-s تيز آهن ۽ ذيلي سوال تيز آهن، صرف هڪ ئي شيء ڇڏي ويو && آپريٽر

اهو صرف هڪ اهم آپريشن آهي. اسان کي هميشه URLs جي پوري ھيٺئين جدول کي ڳولڻ جي ضرورت آھي ھڪڙو نمونو ڳولڻ لاءِ، ۽ اسان کي ھميشه چوڪن کي ڳولڻ جي ضرورت آھي. اسان URL رڪارڊ ذريعي سڌو سنئون نه ڳولي سگهون ٿا، ڇاڪاڻ ته اهي صرف IDs جو حوالو ڏئي رهيا آهن urls.

هڪ حل جي رستي تي

&& سست ڇاڪاڻ ته ٻئي سيٽ وڏا آهن. آپريشن نسبتا تيز ٿي ويندو جيڪڏهن آئون تبديل ڪريان urls تي { "http://google.com/", "http://wingify.com/" }.

مون استعمال ڪرڻ کان سواءِ پوسٽ گريس ۾ چونڪ سيٽ ڪرڻ جو رستو ڳولڻ شروع ڪيو &&، پر گهڻي ڪاميابي کان سواءِ.

آخر ۾، اسان فيصلو ڪيو ته صرف اڪيلائي ۾ مسئلو حل ڪريو: مون کي سڀ ڪجهه ڏيو urls سٽون جن لاءِ URL نموني سان ملندو آهي. بغير اضافي شرطن جي اهو ٿيندو -

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

بدران JOIN نحو مون صرف هڪ ذيلي سوال استعمال ڪيو ۽ وڌايو recording_data.urls array ته جيئن توھان سڌو سنئون شرط لاڳو ڪري سگھوٿا WHERE.

هتي سڀ کان اهم شيء اها آهي && چيڪ ڪرڻ لاءِ استعمال ڪيو ويو ته ڇا ڏنل داخلا هڪ ملندڙ URL تي مشتمل آهي. جيڪڏهن توهان ٿورو غور ڪيو ته توهان ڏسي سگهو ٿا ته هي آپريشن هڪ صف جي عناصر (يا ٽيبل جي قطار) جي ذريعي هلندو آهي ۽ بند ٿي ويندو آهي جڏهن هڪ شرط (ميچ) ملندي آهي. ڇا توهان کي ڪجهه به ياد نه ٿو اچي؟ ها، EXISTS.

کان وٺي recording_data.urls ذيلي سوال جي حوالي سان ٻاهران حوالو ڏئي سگهجي ٿو، جڏهن اهو ٿئي ٿو ته اسان پنهنجي پراڻي دوست تي واپس اچي سگهون ٿا EXISTS ۽ ان سان گڏ سبسڪرپشن لپي.

سڀڪنھن شيء کي گڏ ڪرڻ، اسان کي حتمي اصلاحي سوال حاصل ڪريون ٿا:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

۽ آخري ليڊ ٽائيم Time: 1898.717 ms جشن ملهائڻ جو وقت؟!؟

ايترو جلدي نه! پهرين توهان کي درست ڪرڻ جي جانچ ڪرڻ جي ضرورت آهي. مون کي ڏاڍي شڪ هئي EXISTS اصلاح جيئن اڳ ختم ڪرڻ جي منطق کي تبديل ڪري ٿي. اسان کي پڪ ڪرڻ جي ضرورت آهي ته اسان درخواست ۾ غير واضح غلطي شامل نه ڪئي آهي.

هڪ سادي آزمائش ڊوڙندو هو count(*) مختلف ڊيٽا سيٽ جي وڏي تعداد لاء سست ۽ تيز سوالن تي. ان کان پوء، ڊيٽا جي ننڍڙي ذيلي سيٽ لاء، مون دستي طور تي تصديق ڪئي ته سڀئي نتيجا صحيح هئا.

سڀئي ٽيسٽ مسلسل مثبت نتيجا ڏنائين. اسان سڀ ڪجھ ٺيڪ ڪيو!

سکيا جا سبق

هن ڪهاڻي مان ڪيترائي سبق سکڻ وارا آهن:

سوالن جا منصوبا پوري ڪهاڻي نه ٻڌائيندا آهن، پر اهي اشارا مهيا ڪري سگھن ٿا
مکيه ملزم هميشه حقيقي مجرم نه هوندا آهن
سست سوالن کي ٽوڙي سگھجي ٿو الڳ ڪرڻ لاءِ رڪاوٽون
نه سڀئي اصلاحون فطرت ۾ گھٽائيندڙ آھن
استعمال ڪريو EXIST، جتي ممڪن هجي، پيداوار ۾ ڊرامائي اضافو ٿي سگهي ٿو

ٿڪل

اسان ~ 24 منٽ کان 2 سيڪنڊن جي سوال جي وقت کان وياسون - ڪافي ڪارڪردگي واڌارو! جيتوڻيڪ هي مضمون وڏو نڪتو، سڀ تجربا اسان هڪ ڏينهن ۾ ڪيا، ۽ اندازو لڳايو ويو ته انهن کي بهتر ڪرڻ ۽ جانچ ڪرڻ لاءِ 1,5 ۽ 2 ڪلاڪ لڳا.

SQL هڪ شاندار ٻولي آهي جيڪڏهن توهان ان کان نه ڊڄو، پر ان کي سکڻ ۽ استعمال ڪرڻ جي ڪوشش ڪريو. چڱيءَ ريت سمجھڻ سان ته SQL سوالن تي عمل ڪيئن ڪيو وڃي ٿو، ڊيٽابيس سوالن جا منصوبا ڪيئن ٺاھي ٿو، انڊيڪس ڪيئن ڪم ڪن ٿا، ۽ صرف ڊيٽا جي سائيز جنھن سان توھان ڊيل ڪري رھيا آھيو، توھان تمام ڪامياب ٿي سگھوٿا سوالن کي بهتر ڪرڻ ۾. اهو ساڳيو ضروري آهي، تنهن هوندي، مختلف طريقن جي ڪوشش جاري رکڻ ۽ دير سان مسئلو کي ٽوڙڻ، رڪاوٽون ڳولڻ.

اهڙن نتيجن کي حاصل ڪرڻ جي باري ۾ بهترين حصو قابل ذڪر، ڏسڻ واري رفتار جي بهتري آهي - جتي هڪ رپورٽ جيڪا اڳ ۾ به لوڊ نه ٿيندي هئي هاڻي لڳ ڀڳ فوري طور تي لوڊ ٿئي ٿي.

جي خاص مهرباني منهنجا ساٿي آدتيه مشرا جي حڪم تي, آديت گورو и ورون ملهوترا ذهن سازي لاءِ ۽ دنڪر پندر اسان جي آخري درخواست ۾ هڪ اهم غلطي ڳولڻ کان اڳ اسان آخرڪار ان کي الوداع چيو!

جو ذريعو: www.habr.com

هڪ SQL تحقيق جي ڪهاڻي