ProHoster > Blog > Rianachd > Eachdraidh aon sgrùdadh SQL

Eachdraidh aon sgrùdadh SQL

Anns an Dùbhlachd an-uiridh fhuair mi aithisg bug inntinneach bho sgioba taic VWO. Bha coltas gun robh an ùine luchdachadh airson aon de na h-aithisgean anailis airson neach-dèiligidh corporra mòr toirmisgte. Agus leis gur e seo an raon dleastanais agam, chuir mi fòcas sa bhad air fuasgladh fhaighinn air an duilgheadas.

ro-eachdraidheil

Gus a dhèanamh soilleir cò mu dheidhinn a tha mi a’ bruidhinn, innsidh mi beagan dhut mu VWO. Is e àrd-ùrlar a tha seo leis an urrainn dhut grunn iomairtean cuimsichte a chuir air bhog air na làraich-lìn agad: dèan deuchainnean A/B, sùil a chumail air luchd-tadhail agus atharrachaidhean, mion-sgrùdadh a dhèanamh air an funail reic, mapaichean teas a thaisbeanadh agus clàran tadhal a chluich.

Ach is e an rud as cudromaiche mun àrd-ùrlar aithris. Tha na gnìomhan gu h-àrd uile eadar-cheangailte. Agus airson teachdaichean corporra, bhiodh tòrr fiosrachaidh dìreach gun fheum às aonais àrd-ùrlar cumhachdach a tha ga thaisbeanadh ann an cruth anailis.

A’ cleachdadh an àrd-ùrlair, faodaidh tu ceist air thuaiream a dhèanamh air seata dàta mòr. Seo eisimpleir shìmplidh:

Seall a h-uile cliog air duilleag "abc.com" BHO <date d1> GU <date d2> airson daoine a chleachd Chrome OR (suidhichte san Roinn Eòrpa AGUS a chleachd iPhone)

Thoir aire do ghnìomhaichean Boolean. Tha iad rim faighinn le teachdaichean anns an eadar-aghaidh ceist gus ceistean iom-fhillte a dhèanamh gu neo-riaghailteach gus sampallan fhaighinn.

Iarrtas slaodach

Bha an neach-dèiligidh sin a’ feuchainn ri rudeigin a dhèanamh a bu chòir a bhith ag obair gu sgiobalta:

Seall a h-uile clàr seisean airson luchd-cleachdaidh a thadhail air duilleag sam bith le URL anns a bheil "/jobs"

Bha tunna de thrafaig air an làrach seo agus bha sinn a’ stòradh còrr air millean URL sònraichte dìreach air a shon. Agus bha iad airson teamplaid URL gu math sìmplidh a lorg a bha co-cheangailte ris a’ mhodail gnìomhachais aca.

Rannsachadh tòiseachaidh

Bheir sinn sùil air na tha dol air adhart san stòr-dàta. Gu h-ìosal tha a’ cheist SQL slaodach tùsail:

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 na h-amannan:

Ùine dealbhaichte: 1.480 ms Ùine cur gu bàs: 1431924.650 ms

Shreap a’ cheist 150 mìle sreath. Sheall dealbhaiche na ceiste mion-fhiosrachadh inntinneach no dhà, ach cha robh botail follaiseach ann.

Nì sinn sgrùdadh nas fhaide air an iarrtas. Mar a chì thu, tha e a 'dèanamh JOIN trì bùird:

  1. seiseanan: gus fiosrachadh seisean a thaisbeanadh: brabhsair, neach-cleachdaidh, dùthaich, agus mar sin air adhart.
  2. clàradh_data: URLan clàraichte, duilleagan, fad tursan
  3. urls: Gus dùblachadh URLan fìor mhòr a sheachnadh, bidh sinn gan stòradh ann an clàr air leth.

Thoir an aire cuideachd gu bheil na bùird againn uile air an sgaradh mu thràth account_id. San dòigh seo, tha suidheachadh far a bheil aon chunntas sònraichte mòr ag adhbhrachadh dhuilgheadasan dha feadhainn eile air an dùnadh a-mach.

A’ coimhead airson sanasan

Às deidh sgrùdadh nas dlùithe, chì sinn gu bheil rudeigin ceàrr air iarrtas sònraichte. Is fhiach sùil nas mionaidiche a thoirt air an loidhne seo:

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

B 'e a' chiad bheachd gur dòcha air sgàth ILIKE air na URLan fada sin (tha còrr air 1,4 millean againn gun samhail URLan a chaidh a chruinneachadh airson a’ chunntais seo) dh’ fhaodadh coileanadh fulang.

Ach chan e, chan e sin a’ phuing!

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

Time: 5231.765 ms

Cha toir an t-iarrtas sgrùdaidh teamplaid fhèin ach 5 diogan. Tha e soilleir nach e duilgheadas a th’ ann a bhith a’ lorg pàtran ann am millean URL sònraichte.

Is e an ath neach a tha fo amharas air an liosta grunn JOIN. Is dòcha gur e an cus cleachdaidh aca a dh’ adhbhraich an slaodachadh? Mar as trice JOIN's iad na tagraichean as follaisiche airson duilgheadasan coileanaidh, ach cha robh mi a' creidsinn gu robh a 'chùis againn àbhaisteach.

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 cha b’ e seo a’ chùis againn cuideachd. JOINbha e gu math luath.

A’ lughdachadh cearcall nan daoine a tha fo amharas

Bha mi deiseil airson tòiseachadh air a’ cheist atharrachadh gus leasachaidhean coileanaidh sam bith a choileanadh. Leasaich mo sgioba agus mise 2 phrìomh bheachd:

  • Cleachd EXISTS airson URL subquery: Bha sinn airson dearbhadh a-rithist an robh duilgheadas sam bith ann leis an fho-iarrtas airson na URLan. Is e aon dòigh air seo a choileanadh dìreach a chleachdadh EXISTS. EXISTS urrainn leasachadh gu mòr air coileanadh oir thig e gu crìch sa bhad cho luath ‘s a lorgas e an aon sreang a tha a rèir an t-suidheachaidh.

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

Uill, tha. Subquery nuair a thèid a phasgadh a-steach EXISTS, a’ dèanamh a h-uile càil gu math luath. Is e an ath cheist loidsigeach carson a tha an t-iarrtas le JOIN-ami agus an subquery fhèin luath leotha fhèin, ach tha iad uamhasach slaodach còmhla?

  • A’ gluasad an fho-iarrtas chun an CTE : Ma tha a’ cheist luath leis fhèin, is urrainn dhuinn dìreach an toradh luath obrachadh a-mach an toiseach agus an uairsin a thoirt don phrìomh cheist

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 bha e gu math slaodach fhathast.

A 'lorg an neach a tha ciontach

Fad na h-ùine seo, bha aon rud beag a’ lasadh ro mo shùilean, a bha mi an-còmhnaidh a’ bruiseadh gu aon taobh. Ach leis nach robh dad eile air fhàgail, chuir mi romham coimhead oirre cuideachd. Tha mi a 'bruidhinn mu dheidhinn && gnìomhaiche. Beannachd leat EXISTS dìreach coileanadh nas fheàrr && b' e seo an aon fheart cumanta a bha air fhàgail anns a h-uile tionndadh den cheist mhall.

A ’toirt sùil sgrìobhainnean, chì sinn sin && air a chleachdadh nuair a dh’ fheumas tu eileamaidean cumanta a lorg eadar dà shreath.

Anns an iarrtas tùsail tha seo:

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

Tha sin a’ ciallachadh gun dèan sinn sgrùdadh pàtrain air na URLan againn, agus an uairsin lorg an eadar-ghearradh leis na URLan gu lèir le puist cumanta. Tha seo beagan troimh-chèile oir chan eil “urls” an seo a’ toirt iomradh air a’ chlàr anns a bheil na URLan gu lèir, ach air a’ cholbh “urls” sa chlàr recording_data.

Le amharas a’ sìor fhàs a thaobh &&, dh'fheuch mi ri dearbhadh a lorg dhaibh anns a' phlana ceist a chaidh a chruthachadh EXPLAIN ANALYZE (Bha plana agam mu thràth air a shàbhaladh, ach mar as trice bidh mi nas comhfhurtail a bhith a’ feuchainn ann an SQL na bhith a’ feuchainn ri neo-sheasmhachd luchd-dealbhaidh cheistean a thuigsinn).

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

Bha grunn loidhnichean de shìoltachain a-mhàin bho &&. A bha a 'ciallachadh nach robh an obair seo a-mhàin daor, ach cuideachd air a dhèanamh grunn thursan.

Rinn mi deuchainn air seo le bhith a’ dealachadh an t-suidheachaidh

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

Bha a’ cheist seo slaodach. Air sgàth gu bheil an JOIN-s tapaidh agus tha subqueries luath, cha robh air fhàgail ach && gnìomhaiche.

Is e seo dìreach prìomh ghnìomhachd. Feumaidh sinn an-còmhnaidh an clàr bunaiteach de URLan a sgrùdadh gus pàtran a lorg, agus feumaidh sinn an-còmhnaidh croisean-rathaid a lorg. Chan urrainn dhuinn sgrùdadh dìreach le clàran URL, oir chan eil annta seo ach IDan air a bheilear a’ toirt iomradh urls.

Air an t-slighe gu fuasgladh

&& slaodach oir tha an dà sheata gu math mòr. Bidh an obair gu math luath ma thèid mi na àite urls air { "http://google.com/", "http://wingify.com/" }.

Thòisich mi a’ coimhead airson dòigh air eadar-ghearradh suidhichte ann am Postgres gun a bhith a’ cleachdadh &&, ach gun mòran soirbheachaidh.

Aig a 'cheann thall, chuir sinn romhainn dìreach an duilgheadas fhuasgladh leotha fhèin: thoir dhomh a h-uile dad urls loidhnichean airson a bheil an URL a 'freagairt ris a' phàtran. Às aonais cumhachan a bharrachd bidh e - 

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

An àite sin JOIN syntax Cha chleachd mi ach subquery agus leudaich mi recording_data.urls sreath gus an urrainn dhut an suidheachadh a chuir an sàs gu dìreach WHERE.

Is e an rud as cudromaiche an seo && air a chleachdadh gus dèanamh cinnteach a bheil URL co-ionnan ann an inntrigeadh sònraichte. Ma nì thu spìonadh beagan, chì thu an obrachadh seo a’ gluasad tro na h-eileamaidean ann an sreath (no sreathan de bhòrd) agus a’ stad nuair a thèid suidheachadh (geama) a choileanadh. Nach eil dad a’ cur nar cuimhne? Seadh, EXISTS.

Bhon uair sin air adhart recording_data.urls faodar iomradh a thoirt air bho thaobh a-muigh co-theacs an fho-cheist, nuair a thachras seo faodaidh sinn tuiteam air ais air ar seann charaid EXISTS agus paisg an subquery leis.

A’ cur a h-uile càil ri chèile, gheibh sinn a’ cheist leasaichte mu dheireadh:

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 ùine stiùiridh mu dheireadh Time: 1898.717 ms An t-àm airson comharrachadh?!?

Chan eil cho luath! An toiseach feumaidh tu sgrùdadh a dhèanamh air ceartachd. Bha mi uabhasach amharasach mu dheidhinn EXISTS optimization oir bidh e ag atharrachadh an loidsig gus a chrìochnachadh na bu thràithe. Feumaidh sinn a bhith cinnteach nach do chuir sinn mearachd neo-fhollaiseach ris an iarrtas.

Bha deuchainn sìmplidh ri ruith count(*) air ceistean slaodach agus luath airson àireamh mhòr de sheataichean dàta eadar-dhealaichte. An uairsin, airson fo-sheata bheag den dàta, dhearbh mi le làimh gu robh a h-uile toradh ceart.

Thug a h-uile deuchainn toraidhean adhartach gu cunbhalach. Chàirich sinn a h-uile càil!

Leasanan a chaidh ionnsachadh

Tha mòran leasanan ri ionnsachadh bhon sgeulachd seo:

  1. Chan eil planaichean ceist ag innse na sgeòil gu lèir, ach faodaidh iad sanasan a thoirt seachad
  2. Chan e na prìomh dhaoine a tha fo amharas an-còmhnaidh na fìor eucoirich
  3. Faodar ceistean slaodach a bhriseadh sìos gus botail a lorg
  4. Chan eil a h-uile optimizations lùghdaichte ann an nàdar
  5. Cleachd EXIST, far a bheil sin comasach, a’ leantainn gu àrdachadh mòr ann an cinneasachd

co-dhùnadh

Chaidh sinn bho àm ceiste de ~24 mionaid gu 2 dhiog - àrdachadh coileanaidh gu math mòr! Ged a thàinig an artaigil seo a-mach mòr, thachair a h-uile deuchainn a rinn sinn ann an aon latha, agus bhathas a 'meas gun tug iad eadar 1,5 agus 2 uair airson optimizations agus deuchainn.

Tha SQL na chànan mìorbhaileach mura h-eil eagal ort roimhe, ach feuch ri ionnsachadh agus a chleachdadh. Le bhith a’ faighinn deagh thuigse air mar a tha ceistean SQL air an cur an gnìomh, mar a bhios an stòr-dàta a’ gineadh planaichean ceist, mar a bhios clàran-amais ag obair, agus dìreach meud an dàta ris a bheil thu a’ dèiligeadh, faodaidh tu a bhith air leth soirbheachail ann a bhith a’ dèanamh an fheum as fheàrr de cheistean. Tha e a cheart cho cudromach, ge-tà, a bhith a 'leantainn air adhart a' feuchainn diofar dhòighean-obrach agus a 'briseadh sìos an duilgheadas gu slaodach, a' lorg nam botail.

Is e am pàirt as fheàrr mu bhith a’ coileanadh thoraidhean mar seo leasachadh luaths follaiseach, faicsinneach - far a bheil aithisg nach biodh eadhon a’ luchdachadh a-nis a’ luchdachadh cha mhòr sa bhad.

Taing sònraichte do mo chompanaich aig ceannas Aditya Mishra, Aditya Gauru и Varun Malhotra airson cnuasachadh inntinn agus Dincar Pandir airson mearachd chudromach a lorg anns an iarrtas mu dheireadh againn mus do leig sinn beannachd leis mu dheireadh!

Source: www.habr.com

Cuir beachd ann