🥇 د یوې SQL تفتیش کیسه

تیر دسمبر ما د VWO ملاتړ ټیم څخه په زړه پورې بګ راپور ترلاسه کړ. د یوې لویې تصدۍ پیرودونکي لپاره د تحلیلي راپورونو څخه د بارولو وخت ممنوع ښکاري. او څرنګه چې دا زما د مسؤلیت ساحه ده، ما سمدلاسه د ستونزې حل کولو تمرکز وکړ.

له تاریخ څخه دمخه

د دې لپاره چې دا روښانه کړم چې زه د څه په اړه خبرې کوم، زه به تاسو ته د VWO په اړه لږ څه ووایم. دا یو پلیټ فارم دی چې ورسره تاسو کولی شئ په خپلو ویب پاڼو کې مختلف هدف شوي کمپاینونه پیل کړئ: د A/B تجربې ترسره کړئ، لیدونکي او تبادلې تعقیب کړئ، د پلور فینل تحلیل کړئ، د تودوخې نقشې ښکاره کړئ او د لیدنې ریکارډونه پلی کړئ.

مګر د پلیټ فارم په اړه ترټولو مهم شی راپور ورکول دي. پورتنۍ ټولې دندې له یو بل سره تړلي دي. او د کارپوریټ پیرودونکو لپاره ، د معلوماتو لوی مقدار به د ځواکمن پلیټ فارم پرته په ساده ډول بې ګټې وي چې دا د تحلیلي شکل کې وړاندې کوي.

د پلیټ فارم په کارولو سره ، تاسو کولی شئ په لوی ډیټا سیټ کې تصادفي پوښتنه وکړئ. دلته یو ساده مثال دی:

د هغو کسانو لپاره چې کروم یې کارولی یا (په اروپا کې موقعیت لري او آی فون یې کارولی دی) د <date d1> TO <date d2> پاڼې "abc.com" کې ټول کلیکونه وښایاست

د بولین آپریټرانو ته پام وکړئ. دوی د پوښتنو انٹرفیس کې پیرودونکو ته شتون لري ترڅو د نمونو ترلاسه کولو لپاره په خپله خوښه پیچلې پوښتنې وکړي.

ورو غوښتنه

په پوښتنه کې پیرودونکي هڅه کوله چې یو څه وکړي چې په شعوري ډول باید ګړندي کار وکړي:

د هغو کاروونکو لپاره د ناستې ټول ریکارډونه وښایاست چې د یو آر ایل سره هر هغه پاڼې لیدلي چې "/ دندې" لري

دا سایټ یو ټن ټرافیک درلود او موږ یوازې د دې لپاره له یو ملیون څخه ډیر ځانګړي URLs ذخیره کوو. او دوی غوښتل چې یو ساده ساده 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 ms د اجرا وخت: 1431924.650 ms

پوښتنې 150 زره قطارونه تیر کړل. د پوښتنې پلان جوړونکي یو څو په زړه پوري توضیحات وښودل ، مګر کوم څرګند خنډونه ندي.

راځئ چې غوښتنه نوره مطالعه کړو. لکه څنګه چې تاسو لیدلی شئ، هغه کوي JOIN درې میزونه:

غونډې: د سیشن معلوماتو ښودلو لپاره: براوزر، د کارونکي اجنټ، هیواد، او داسې نور.
ثبت_ډاټا: ثبت شوي URLs، پاڼې، د لیدنو موده
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دا خورا ګړندی ثابت شو.

د شکمنو کسانو دایره کمول

زه چمتو وم چې د هرې ممکنه فعالیت ښه والي ترلاسه کولو لپاره د پوښتنې بدلولو پیل وکړم. زما ټیم او ما دوه اصلي نظرونه رامینځته کړل:

د فرعي پوښتنو URL لپاره EXISTS وکاروئ: موږ غوښتل بیا وګورو چې ایا د 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" کالم ته اشاره کوي 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 د دې لپاره چې تاسو کولی شئ په مستقیم ډول شرایط پلي کړئ 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 پوښتنې څنګه اجرا کیږي ، ډیټابیس څنګه د پوښتنو پلانونه رامینځته کوي ، څنګه شاخصونه کار کوي ، او په ساده ډول د هغه ډیټا اندازه چې تاسو ورسره معامله کوئ ، تاسو کولی شئ د پوښتنو اصلاح کولو کې خورا بریالي اوسئ. په هرصورت، دا مساوي اهمیت لري، د مختلفو طریقو هڅه کولو ته دوام ورکړئ او ورو ورو ستونزه مات کړئ، د خنډونو په موندلو سره.

د دې په څیر پایلو ترلاسه کولو په اړه غوره برخه د پام وړ ، د لید سرعت ښه والی دی - چیرې چې یو راپور چې دمخه به یې حتی بار نه کاوه اوس نږدې سمدستي باریږي.

ځانګړې مننه زما ملګري د ادیتیا مشرا په امر, ادیتیا ګورو и ورون ملهوترا د فکر کولو لپاره او دنکر پنډیر زموږ په وروستۍ غوښتنه کې د یوې مهمې تېروتنې موندلو لپاره مخکې لدې چې موږ په پای کې دې ته الوداع وویل!

سرچینه: www.habr.com

د یوې SQL تفتیش کیسه