🥇Nkhani ya kafukufuku wina wa SQL | Pulogalamu ya ProHoster

December watha ndinalandira lipoti losangalatsa la cholakwika kuchokera ku gulu lothandizira la VWO. Nthawi yotsegula ya lipoti limodzi la analytics kwa kasitomala wamkulu wamakampani inkawoneka ngati yolepheretsa. Ndipo popeza ili ndi gawo langa laudindo, nthawi yomweyo ndinayang'ana kwambiri kuthetsa vutoli.

prehistory

Kuti ndifotokoze momveka bwino zomwe ndikunena, ndikuwuzani pang'ono za VWO. Iyi ndi nsanja yomwe mutha kuyambitsa makampeni osiyanasiyana omwe mukufuna patsamba lanu: gwiritsani ntchito zoyeserera za A/B, tsatirani alendo ndi otembenuka, pendani zogulitsa, wonetsani mamapu otentha ndi kusewera makanema ojambula.

Koma chofunika kwambiri pa nsanja ndi kupereka lipoti. Ntchito zonse zomwe zili pamwambapa ndizolumikizana. Ndipo kwamakasitomala amakampani, zidziwitso zambiri zitha kukhala zopanda ntchito popanda nsanja yamphamvu yomwe imaziwonetsa mu mawonekedwe a analytics.

Pogwiritsa ntchito nsanja, mutha kufunsa mwachisawawa pagulu lalikulu la data. Nachi chitsanzo chosavuta:

Onetsani kudina konse patsamba "abc.com" KUYAMBIRA <date d1> MPAKA <date d2> kwa anthu omwe amagwiritsa ntchito Chrome KAPENA (omwe ali ku Europe NDIPO amagwiritsa ntchito iPhone)

Samalani kwa ogwiritsa ntchito Boolean. Zilipo kwa makasitomala pamawonekedwe amafunso kuti apange mafunso ovuta kuti apeze zitsanzo.

Pempho lapang'onopang'ono

Wofuna chithandizo amayesa kuchita chinachake chomwe chiyenera kugwira ntchito mofulumira:

Onetsani zolemba zonse zagawo za ogwiritsa ntchito omwe adayendera tsamba lililonse lomwe lili ndi ulalo womwe uli ndi "/jobs"

Tsambali linali ndi anthu ambiri ndipo tinali kusunga ma URL apadera oposa miliyoni chifukwa cha izo. Ndipo amafuna kupeza template yosavuta ya URL yomwe ikugwirizana ndi bizinesi yawo.

Kufufuza koyambirira

Tiyeni tiwone zomwe zikuchitika mu database. Pansipa pali funso loyambira pang'onopang'ono la 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 ;

Ndipo apa pali nthawi:

Nthawi yokonzekera: 1.480 ms Nthawi yokonzekera: 1431924.650 ms

Funsolo linakwawa mizere 150 zikwi. Wokonza mafunso adawonetsa zambiri zosangalatsa, koma palibe zolepheretsa zowonekera.

Tiyeni tiphunzire zambiri za pempholi. Monga mukuonera, amatero JOIN matebulo atatu:

magawo: kuwonetsa zambiri za gawo: msakatuli, wogwiritsa ntchito, dziko, ndi zina zotero.
recording_data: ma URL ojambulidwa, masamba, nthawi yochezera
mavlo: Kupewa kubwereza ma URL akulu kwambiri, timawasunga mu tebulo lapadera.

Dziwaninso kuti matebulo athu onse adagawidwa kale account_id. Mwanjira iyi, nthawi yomwe akaunti imodzi yayikulu imayambitsa mavuto kwa ena imachotsedwa.

Kuyang'ana zizindikiro

Tikayang'anitsitsa, timaona kuti pali chinachake cholakwika ndi pempho linalake. Ndikoyenera kuyang'anitsitsa mzerewu:

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

Lingaliro loyamba linali lakuti mwina chifukwa ILIKE pa ma URL onse aatali awa (tili ndi oposa 1,4 miliyoni wapadera Ma URL osonkhanitsidwa a akauntiyi) magwiridwe antchito atha kukhala ovuta.

Koma ayi, sichoncho!

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

Time: 5231.765 ms

Kufunsira kwa template komwe kumangotenga masekondi 5 okha. Kusaka mawonekedwe mu ma URL apadera miliyoni sivuto.

Wokayikira wotsatira pamndandandawu ndi angapo JOIN. Mwina kugwiritsa ntchito kwawo mopitirira muyeso kwachititsa kuti kuchepeko? Kawirikawiri JOIN's ndi omwe ali odziwika kwambiri pazovuta zamachitidwe, koma sindinkakhulupirira kuti nkhani yathu inali yofanana.

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

Ndipo izi sizinalinso vuto lathu. JOINZakhala zothamanga kwambiri.

Kuchepetsa kuzungulira kwa okayikira

Ndinali wokonzeka kuyamba kusintha funso kuti ndikwaniritse kusintha kulikonse kotheka. Ine ndi gulu langa tinapanga mfundo zazikulu ziwiri:

Gwiritsani ntchito EXISTS pazambiri za URL: Tinkafuna kuwonanso ngati panali vuto lililonse ndi subquery ya ma URL. Njira imodzi yokwaniritsira izi ndikungogwiritsa ntchito EXISTS. EXISTS mungathe kuwongolera kwambiri magwiridwe antchito popeza imatha nthawi yomweyo ikapeza chingwe chokhacho chomwe chikugwirizana ndi chikhalidwecho.

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

Chabwino, inde. Subquery pamene atakulungidwa EXISTS, imapangitsa chilichonse kukhala chofulumira kwambiri. Chotsatira chomveka funso ndi chifukwa chake pempho ndi JOIN-ami ndi subquery yokha imathamanga payekhapayekha, koma imachedwa kwambiri palimodzi?

Kusunthira subquery kupita ku CTE : Ngati funso liri lofulumira palokha, tikhoza kungowerengera zotsatira zofulumira kenako ndikuzipereka ku funso lalikulu.

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;

Koma kunali kochedwa kwambiri.

Kupeza wolakwa

Nthaŵi yonseyi, kanthu kakang’ono kanang’anima pamaso panga, kamene ndinkakankhira pambali. Koma popeza panalibenso china chilichonse, ndinaganiza zomuyang’ananso. Ine ndikukamba za && woyendetsa. Bye EXISTS tangochita bwino && chinali chinthu chokhacho chomwe chinatsala pamitundu yonse yamafunso ochedwa.

Kuyang'ana zolemba, tikuwona kuti && amagwiritsidwa ntchito pamene mukufuna kupeza zinthu zofanana pakati pa magulu awiri.

Pempho loyambirira ndi ili:

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

Zomwe zikutanthauza kuti timafufuza pa ma URL athu, kenako tikupeza mphambano ndi ma URL onse okhala ndi zolemba wamba. Izi ndi zosokoneza pang'ono chifukwa "ma url" pano sakunena za tebulo lomwe lili ndi ma URL onse, koma ndi gawo la "urls" patebulo. recording_data.

Ndi kukayikira komwe kukukulirakulira &&, Ndinayesa kupeza chitsimikiziro kwa iwo mu dongosolo lamafunso opangidwa EXPLAIN ANALYZE (Ndinali ndi pulani yosungidwa kale, koma nthawi zambiri ndimakhala womasuka kuyesa SQL kuposa kuyesa kumvetsetsa kuwonekera kwa okonza mafunso).

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

Panali mizere ingapo ya zosefera kuchokera &&. Zomwe zikutanthauza kuti opaleshoniyi sinali yokwera mtengo, komanso imachitika kangapo.

Ndinayesa izi podzipatula

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[]

Funsoli linali lochedwa. Chifukwa ndi JOIN-s ndi achangu ndipo ma subqueries ndi achangu, chinthu chokha chomwe chatsala chinali && woyendetsa.

Ichi ndi ntchito yofunika kwambiri. Nthawi zonse tiyenera kusaka mndandanda wonse wa ma URL kuti tifufuze mawonekedwe, ndipo nthawi zonse timafunika kupeza mphambano. Sitingathe kusaka ndi ma URL mwachindunji, chifukwa awa ndi ma ID okhawo akulozerako urls.

Panjira yothetsera

&& pang'onopang'ono chifukwa ma seti onse ndi akulu. Opaleshoni ikhala yachangu ngati ndisintha urls pa { "http://google.com/", "http://wingify.com/" }.

Ndinayamba kufunafuna njira yopangira mphambano mu Postgres osagwiritsa ntchito &&, koma sizinaphule kanthu.

Pamapeto pake, tinaganiza zongothetsa vutoli payekhapayekha: ndipatseni chilichonse urls mizere yomwe URL ikufanana ndi ndondomeko. Popanda zina zowonjezera zidzakhala -

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%'

M'malo mwake JOIN syntax Ndangogwiritsa ntchito subquery ndikukulitsa recording_data.urls array kuti mutha kugwiritsa ntchito mwachindunji chikhalidwecho WHERE.

Chofunika kwambiri apa ndi chakuti && amagwiritsidwa ntchito kuwona ngati cholowacho chili ndi ulalo wofananira. Ngati muyang'ana pang'ono, mutha kuwona kuti ntchitoyi ikuyenda m'magulu angapo (kapena mizere ya tebulo) ndikuyima pomwe chikhalidwe (machesi) chakwaniritsidwa. Sikukukumbutsani kalikonse? Inde, EXISTS.

Kuyambira pamenepo recording_data.urls zitha kutchulidwa kuchokera kunja kwa subquery, izi zikachitika, titha kubwereranso kwa mnzathu wakale EXISTS ndi kukulunga subquery nayo.

Kuyika zonse pamodzi, timapeza funso lomaliza:

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%'
    );

Ndipo nthawi yomaliza yotsogolera Time: 1898.717 ms Nthawi yokondwerera?!?

Osati mofulumira kwambiri! Choyamba muyenera kufufuza kulondola. Ndinkakayikira kwambiri EXISTS kukhathamiritsa pamene ikusintha malingaliro kuti athetse kale. Tiyenera kutsimikiza kuti sitinawonjezere cholakwika chosadziwika pa pempholo.

Mayeso osavuta anali kuthamanga count(*) pamafunso ang'onoang'ono komanso achangu pamitundu yambiri yama data osiyanasiyana. Kenaka, pa kagawo kakang'ono ka deta, ndinatsimikizira pamanja kuti zotsatira zonse zinali zolondola.

Mayeso onse adapereka zotsatira zabwino nthawi zonse. Tinakonza zonse!

Maphunziro

Pali zinthu zambiri zomwe tingaphunzire m'nkhaniyi:

Zolinga zamafunso sizifotokoza nkhani yonse, koma zimatha kupereka zidziwitso
Sikuti nthawi zonse okayikira kwambiri amakhala olakwa
Mafunso apang'onopang'ono atha kugawidwa kuti muchepetse zopinga
Sikuti kukhathamiritsa konse kumakhala kocheperako
Gwiritsani ntchito EXIST, ngati kuli kotheka, kungayambitse kuwonjezereka kwakukulu kwa zokolola

Pomaliza

Tidachoka pafunso la ~ mphindi 24 mpaka masekondi awiri - chiwonjezeko chachikulu! Ngakhale nkhaniyi idatuluka yayikulu, zoyeserera zonse zomwe tidachita zidachitika tsiku limodzi, ndipo akuti zidatenga pakati pa 2 ndi 1,5 maola kuti zitheke ndikuyesa.

SQL ndi chilankhulo chodabwitsa ngati simuchichita mantha, koma yesani kuphunzira ndikuchigwiritsa ntchito. Pomvetsetsa bwino momwe mafunso a SQL amagwiritsidwira ntchito, momwe nkhokwe imapangira mapulani amafunso, momwe ma index amagwirira ntchito, komanso kukula kwa zomwe mukukumana nazo, mutha kuchita bwino kwambiri pakuwongolera mafunso. Ndikofunikiranso, komabe, kupitiliza kuyesa njira zosiyanasiyana ndikuthetsa vutoli pang'onopang'ono, kupeza zolepheretsa.

Gawo labwino kwambiri pakupeza zotsatira ngati izi ndikuwoneka bwino, kuwongolera liwiro - pomwe lipoti lomwe m'mbuyomu silinatsegulidwe tsopano likudzaza nthawi yomweyo.

Mwapadera zikomo kwa anzanga molamulidwa ndi Aditya Mishra, Aditya Gauru и Varun Malhotra kwa kukambirana ndi Dinkar Pandir chifukwa chopeza cholakwika chofunikira pa pempho lathu lomaliza tisanatsanzike!

Source: www.habr.com

Nkhani ya kafukufuku wina wa SQL