Scéal imscrúdaithe SQL amháin

Mí na Nollag seo caite fuair mé tuairisc fhabht spéisiúil ó fhoireann tacaíochta VWO. Bhí an chuma ar an scéal go raibh an t-am luchtaithe do cheann de na tuarascálacha anailíse do chliant corparáideach mór toirmeascach. Agus ós rud é gurb é seo mo réimse freagrachta, dhírigh mé láithreach ar an bhfadhb a réiteach.

réamhstair

Chun é a dhéanamh soiléir cad faoi a bhfuil mé ag caint, inseoidh mé beagán duit faoi VWO. Is ardán é seo inar féidir leat feachtais éagsúla spriocdhírithe a sheoladh ar do shuímh Ghréasáin: turgnaimh A/B a dhéanamh, cuairteoirí a rianú agus tiontaithe, anailís a dhéanamh ar an tonnadóir díolacháin, léarscáileanna teasa a thaispeáint agus taifeadtaí cuairte a imirt.

Ach is é an rud is tábhachtaí faoin ardán ná tuairisciú. Tá na feidhmeanna thuas go léir idirnasctha. Agus do chliaint chorparáideacha, bheadh ​​méid ollmhór faisnéise gan úsáid gan ardán cumhachtach a chuireann i láthair é i bhfoirm anailíse.

Trí úsáid a bhaint as an ardán, is féidir leat ceist randamach a dhéanamh ar thacar mór sonraí. Seo sampla simplí:

Taispeáin gach cliceáil ar an leathanach "abc.com" Ó <dáta d1> GO <dáta d2> do dhaoine a d'úsáid Chrome OR (lonnaithe san Eoraip AGUS a d'úsáid iPhone)

Tabhair aird ar oibreoirí Boole. Tá siad ar fáil do chliaint sa chomhéadan fiosrúcháin chun fiosrúcháin casta treallach a dhéanamh chun samplaí a fháil.

Iarratas mall

Bhí an cliant i gceist ag iarraidh rud éigin a dhéanamh ar cheart go n-oibreodh sé go hintuigthe go tapa:

Taispeáin gach taifead seisiúin d'úsáideoirí a thug cuairt ar aon leathanach le URL ina bhfuil "/jobs"

Bhí tonna tráchta ar an suíomh seo agus bhíomar ag stóráil breis is milliún URL uathúla díreach dó. Agus bhí siad ag iarraidh teimpléad URL simplí go leor a aimsiú a bhain lena múnla gnó.

Réamh-imscrúdú

Breathnaímis ar a bhfuil ar siúl sa bhunachar sonraí. Seo thíos an bhuncheist mall 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 ;

Agus seo iad na hamanna:

Am beartaithe: 1.480 ms Am forghníomhaithe: 1431924.650 ms

Shroich an cheist 150 míle sraith. Léirigh an pleanálaí fiosrúcháin cúpla sonraí suimiúla, ach ní raibh aon scrogall soiléir ann.

Déanaimis tuilleadh staidéir ar an iarratas. Mar a fheiceann tú, déanann sé JOIN trí tábla:

1. seisiúin: chun faisnéis seisiúin a thaispeáint: brabhsálaí, gníomhaire úsáideora, tír, agus mar sin de.
2. taifeadta_sonraí: URLanna taifeadta, leathanaigh, fad na gcuairteanna
3. urls: Chun URLanna an-mhór a dhúbailt a sheachaint, déanaimid iad a stóráil i dtábla ar leith.

Tabhair faoi deara freisin go bhfuil ár táblaí go léir deighilte cheana féin ag account_id. Ar an mbealach seo, fágtar as an áireamh cás ina mbíonn cuntas an-mhór amháin ina chúis le fadhbanna do dhaoine eile.

Ag lorg leideanna

Tar éis iniúchadh níos dlúithe a dhéanamh, feicimid go bhfuil rud éigin cearr le hiarratas ar leith. Is fiú breathnú níos géire ar an líne seo:

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

An chéad smaoineamh a bhí b'fhéidir mar gheall ar ILIKE ar na URLanna fada seo go léir (tá os cionn 1,4 milliún againn uathúil URLanna a bhailítear don chuntas seo) d’fhéadfadh go mbeadh feidhmíocht thíos leis.

Ach ní hea, ní hé sin an pointe!

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

Time: 5231.765 ms

Ní thógann an t-iarratas cuardaigh teimpléid féin ach 5 soicind. Is léir nach fadhb é patrún a chuardach i milliún URL uathúla.

Is é an chéad amhrastach eile ar an liosta ná roinnt JOIN. B'fhéidir gurb é a ró-úsáid ba chúis leis an moilliú? De ghnáth JOINIs iad na hiarrthóirí is soiléire maidir le fadhbanna feidhmíochta, ach níor chreid mé go raibh ár gcás tipiciúil.

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

Agus ní raibh sé seo ar ár gcás freisin. JOIN's iompaigh amach a bheith sách tapa.

Ciorcal na ndaoine faoi dhrochamhras a laghdú

Bhí mé réidh le tosú ar an gceist a athrú chun aon fheabhsuithe feidhmíochta a d'fhéadfadh a bheith ann a bhaint amach. D’fhorbair m’fhoireann agus mé féin 2 phríomhsmaoineamh:

• Bain úsáid as EXISTS le haghaidh URL fo-cheiste: Theastaigh uainn a sheiceáil arís an raibh aon fhadhb ann leis an bhfocheist do na URLanna. Bealach amháin chun é seo a bhaint amach ná úsáid a bhaint as EXISTS. EXISTS Is féidir feabhas a chur ar fheidhmíocht go mór ós rud é go gcríochnaíonn sé láithreach chomh luath agus a aimsíonn sé an t-aon teaghrán a oireann don riocht.

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

Bhuel, tá. Subquery nuair fillte i EXISTS, a dhéanann gach rud super tapa. Is í an chéad cheist eile loighciúil cén fáth an t-iarratas le JOIN-ami agus an subquery féin go tapa ina n-aonar, ach tá siad thar a bheith mall le chéile?

• An subquery á aistriú chuig an CTE : Má tá an cheist go tapa leis féin, is féidir linn an toradh tapa a ríomh ar dtús agus ansin é a sholáthar don phríomhcheist

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;

Ach bhí sé fós an-mhall.

Lorg an culprit

An t-am seo ar fad, tháinig rud beag amháin os comhair mo shúile, rud a scuab mé i leataobh i gcónaí. Ach ós rud é nach raibh aon rud eile fágtha, chinn mé breathnú uirthi freisin. Tá mé ag caint faoi && oibreoir. Slán EXISTS feidhmíocht feabhsaithe díreach && an t-aon fhachtóir coitianta a bhí fágtha i ngach leagan den cheist mhall.

Ag féachaint ar doiciméadú, feicimid é sin && a úsáidtear nuair is gá duit teacht ar eilimintí coitianta idir dhá eagar.

Sa bhuniarratas is é seo:

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

Rud a chiallaíonn go ndéanaimid cuardach patrún ar ár URLanna, ansin faighimid an crosbhealach leis na URLanna go léir le poist choitianta. Tá sé seo beagán mearbhall mar ní thagraíonn "urls" anseo don tábla ina bhfuil na URLanna go léir, ach don cholún "urls" sa tábla recording_data.

Le hamhrais ag méadú maidir le &&, Rinne mé iarracht deimhniú a fháil dóibh sa phlean fiosrúcháin a gineadh EXPLAIN ANALYZE (Bhí plean agam cheana féin a shábháil, ach is iondúil go mbíonn mé níos compordaí ag triail a bhaint as SQL ná ag iarraidh teimhneacht na bpleanálaithe fiosrúcháin a thuiscint).

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

Bhí roinnt línte de scagairí amháin ó &&. Rud a chiallaigh go raibh an oibríocht seo ní hamháin costasach, ach freisin a dhéantar arís agus arís eile.

Thástáil mé é seo tríd an riocht a leithlisiú

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

Bhí an cheist seo mall. Toisc go bhfuil an JOIN-s atá tapa agus subqueries go tapa, bhí an rud amháin fágtha && oibreoir.

Níl anseo ach príomhoibríocht. Ní mór dúinn i gcónaí buntábla iomlán na URLanna a chuardach chun patrún a chuardach, agus ní mór dúinn crosbhealaí a aimsiú i gcónaí. Ní féidir linn cuardach a dhéanamh de réir taifid URL go díreach, mar níl iontu seo ach IDanna a bhfuil tagairt dóibh urls.

Ar an mbealach chun réiteach

&& mall mar go bhfuil an dá shraith ollmhór. Beidh an oibríocht sách tapa má athraítear mé urls ar { "http://google.com/", "http://wingify.com/" }.

Thosaigh mé ag lorg bealach chun trasnú socraithe a dhéanamh i Postgres gan úsáid a bhaint as &&, ach gan mórán ratha.

Sa deireadh, shocraigh muid an fhadhb a réiteach ina n-aonar: tabhair dom gach rud urls línte a bhfuil an URL ag teacht leis an bpatrún dóibh. Gan coinníollacha breise beidh sé - 

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

In ionad JOIN chomhréir úsáid mé díreach tar éis subquery agus leathnaithe recording_data.urls eagar ionas gur féidir leat an coinníoll a chur i bhfeidhm go díreach i WHERE.

Is é an rud is tábhachtaí anseo && a úsáidtear chun seiceáil an bhfuil URL meaitseála in iontráil tugtha. Má fhéachann tú beagán, is féidir leat a fheiceáil go ngluaiseann an oibríocht seo trí eilimintí eagar (nó sraitheanna tábla) agus stopann sí nuair a chomhlíontar coinníoll (meaitseáil). Nach gcuireann sé rud ar bith i gcuimhne duit? Sea, EXISTS.

Ó shin i leith recording_data.urls Is féidir tagairt a dhéanamh ó lasmuigh den chomhthéacs subquery, nuair a tharlaíonn sé seo is féidir linn titim ar ais ar ár seanchara EXISTS agus wrap an subquery leis.

Ag cur gach rud le chéile, faigheann muid an cheist optamaithe deiridh:

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

Agus an t-am luaidhe deiridh Time: 1898.717 ms Am le ceiliúradh?!?

Níl sé chomh tapaidh! An Chéad is gá duit a sheiceáil le cruinneas. Bhí mé thar a bheith amhrasach faoi EXISTS leas iomlán a bhaint mar a athraíonn sé an loighic a chur i gcrích níos luaithe. Ní mór dúinn a bheith cinnte nach bhfuil earráid neamhshoiléir curtha againn leis an iarratas.

Bhí tástáil shimplí le rith count(*) ar cheisteanna malla agus meara araon do líon mór tacair sonraí éagsúla. Ansin, le haghaidh fothacar beag de na sonraí, d'fhíoraigh mé de láimh go raibh na torthaí go léir i gceart.

Thug na tástálacha go léir torthaí dearfacha go seasta. Shocraigh muid gach rud!

Ceachtanna a foghlaimíodh

Tá go leor ceachtanna le foghlaim ón scéal seo:

1. Ní insíonn pleananna fiosrúcháin an scéal iomlán, ach is féidir leo leideanna a sholáthar
2. Ní hiad na príomh-amhrastaigh i gcónaí na culprits fíor
3. Is féidir fiosruithe malla a bhriseadh síos chun scrogaill a leithlisiú
4. Níl gach leas iomlán a bhaint as nádúr
5. Úsáid EXIST, nuair is féidir, go dtiocfaidh méaduithe móra ar tháirgiúlacht

Aschur

Chuaigh muid ó am fiosrúcháin ~24 nóiméad go 2 shoicind - méadú suntasach ar fheidhmíocht! Cé gur tháinig an t-alt seo amach go mór, tharla na turgnaimh go léir a rinne muid in aon lá amháin, agus measadh gur thóg siad idir 1,5 agus 2 uair an chloig le haghaidh optimizations agus tástála.

Is teanga iontach é SQL mura bhfuil eagla ort roimhe, ach déan iarracht í a fhoghlaim agus a úsáid. Trí thuiscint mhaith a bheith agat ar conas a chuirtear fiosruithe SQL i gcrích, conas a ghineann an bunachar sonraí pleananna fiosrúcháin, conas a oibríonn innéacsanna, agus go simplí ar mhéid na sonraí a bhfuil tú ag déileáil leo, is féidir leat a bheith an-rathúil ar cheisteanna a bharrfheabhsú. Tá sé chomh tábhachtach céanna, áfach, leanúint ar aghaidh ag triail cineálacha cur chuige éagsúla agus an fhadhb a bhriseadh síos go mall, ag teacht ar na baic.

Is é an chuid is fearr maidir le torthaí mar seo a bhaint amach ná feabhas suntasach, luais infheicthe - áit a lódálann tuairisc nach fiú a luchtú roimhe seo beagnach láithreach.

Buíochas ar leith le mo chomrádaithe faoi ​​cheannas Aditya Mishra, Aditya Gauru и Varun Malhotra le haghaidh tobsmaointeoireachta agus Dincar Pandir as earráid thábhachtach a aimsiú inár n-iarratas deiridh sular éirigh linn slán a fhágáil leis ar deireadh!

Foinse: will.com