ProHoster > blog > Gweinyddiaeth > Hanes un ymchwiliad SQL

Hanes un ymchwiliad SQL

Fis Rhagfyr diwethaf derbyniais adroddiad byg diddorol gan dîm cymorth VWO. Roedd yr amser llwytho ar gyfer un o'r adroddiadau dadansoddol ar gyfer cleient menter fawr yn ymddangos yn afresymol. A chan mai dyma fy maes cyfrifoldeb, canolbwyntiais ar unwaith ar ddatrys y broblem.

cynhanes

Er mwyn ei gwneud yn glir am beth rwy'n siarad, fe ddywedaf ychydig wrthych am VWO. Mae hwn yn blatfform y gallwch chi lansio amryw o ymgyrchoedd wedi'u targedu ar eich gwefannau: cynnal arbrofion A/B, olrhain ymwelwyr ac addasiadau, dadansoddi'r twndis gwerthu, arddangos mapiau gwres a chwarae recordiadau ymweliadau.

Ond y peth pwysicaf am y platfform yw adrodd. Mae'r holl swyddogaethau uchod yn gydgysylltiedig. Ac i gleientiaid corfforaethol, byddai llawer iawn o wybodaeth yn ddiwerth heb blatfform pwerus sy'n ei chyflwyno ar ffurf dadansoddeg.

Gan ddefnyddio'r platfform, gallwch wneud ymholiad ar hap ar set ddata fawr. Dyma enghraifft syml:

Dangos pob clic ar dudalen "abc.com" O <dyddiad d1> I <dyddiad d2> ar gyfer pobl a ddefnyddiodd Chrome OR (wedi'u lleoli yn Ewrop AC wedi defnyddio iPhone)

Rhowch sylw i weithredwyr Boole. Maent ar gael i gleientiaid yn y rhyngwyneb ymholiad i wneud ymholiadau mympwyol gymhleth i gael samplau.

Cais araf

Roedd y cleient dan sylw yn ceisio gwneud rhywbeth a ddylai weithio'n gyflym yn reddfol:

Dangos pob cofnod sesiwn ar gyfer defnyddwyr a ymwelodd ag unrhyw dudalen ag URL yn cynnwys "/jobs"

Roedd gan y wefan hon dunnell o draffig ac roeddem yn storio dros filiwn o URLs unigryw ar ei gyfer yn unig. Ac roeddent am ddod o hyd i dempled URL eithaf syml a oedd yn ymwneud â'u model busnes.

Ymchwiliad rhagarweiniol

Gadewch i ni edrych ar yr hyn sy'n digwydd yn y gronfa ddata. Isod mae'r ymholiad SQL araf gwreiddiol:

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 ;

A dyma'r amseroedd:

Amser a gynlluniwyd: 1.480 ms Amser gweithredu: 1431924.650 ms

Ymlusgodd yr ymholiad 150 mil o resi. Dangosodd y cynlluniwr ymholiad ychydig o fanylion diddorol, ond dim tagfeydd amlwg.

Gadewch i ni astudio'r cais ymhellach. Fel y gwelwch, mae'n ei wneud JOIN tri bwrdd:

  1. sesiynau: i arddangos gwybodaeth sesiwn: browser, user agent, country, ac ati.
  2. cofnodi_data: URLau wedi'u recordio, tudalennau, hyd ymweliadau
  3. urls: Er mwyn osgoi dyblygu URLs hynod o fawr, rydym yn eu storio mewn tabl ar wahân.

Sylwch hefyd fod pob un o'n byrddau eisoes wedi'u rhannu gan account_id. Fel hyn, ni chynhwysir sefyllfa lle mae un cyfrif arbennig o fawr yn achosi problemau i eraill.

Chwilio am gliwiau

O edrych yn agosach, gwelwn fod rhywbeth o'i le ar gais penodol. Mae'n werth edrych yn agosach ar y llinell hon:

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

Y meddwl cyntaf oedd efallai oherwydd ILIKE ar yr holl URLau hir hyn (mae gennym ni dros 1,4 miliwn unigryw URLs a gasglwyd ar gyfer y cyfrif hwn) gall perfformiad ddioddef.

Ond na, nid dyna'r pwynt!

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

Time: 5231.765 ms

Dim ond 5 eiliad y mae'r cais chwilio templed ei hun yn ei gymryd. Mae'n amlwg nad yw chwilio am batrwm mewn miliwn o URLau unigryw yn broblem.

Y sawl sydd dan amheuaeth nesaf ar y rhestr yw sawl un JOIN. Efallai mai eu gorddefnydd sydd wedi achosi'r arafu? Fel arfer JOIN's yw'r ymgeiswyr amlycaf ar gyfer problemau perfformiad, ond doeddwn i ddim yn credu bod ein hachos yn nodweddiadol.

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

Ac nid dyna oedd ein hachos ni chwaith. JOIN' wedi troi allan i fod yn eithaf cyflym.

Culhau'r cylch o bobl dan amheuaeth

Roeddwn yn barod i ddechrau newid yr ymholiad i gyflawni unrhyw welliannau perfformiad posibl. Datblygodd fy nhîm a minnau 2 brif syniad:

  • Defnyddiwch EXISTS ar gyfer URL subquery: Roeddem am wirio eto a oedd unrhyw broblemau gyda'r subquery ar gyfer yr URLs. Un ffordd o gyflawni hyn yw defnyddio yn syml EXISTS. EXISTS Gall gwella perfformiad yn fawr gan ei fod yn dod i ben ar unwaith cyn gynted ag y bydd yn dod o hyd i'r unig llinyn sy'n cyfateb i'r cyflwr.

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

Wel, ie. Subquery pan lapio mewn EXISTS, yn gwneud popeth yn gyflym iawn. Y cwestiwn rhesymegol nesaf yw pam y cais gyda JOIN-ami a'r subquery ei hun yn gyflym yn unigol, ond yn ofnadwy o araf gyda'i gilydd?

  • Symud y subquery i'r CTE : Os yw'r ymholiad yn gyflym ar ei ben ei hun, gallwn gyfrifo'r canlyniad cyflym yn gyntaf ac yna ei ddarparu i'r prif ymholiad

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;

Ond roedd yn dal yn araf iawn.

Dod o hyd i'r troseddwr

Yr holl amser hwn, fflachiodd un peth bach o flaen fy llygaid, yr oeddwn yn ei frwsio'n gyson o'r neilltu. Ond gan nad oedd dim arall ar ôl, penderfynais edrych arni hi hefyd. Rwy'n siarad am && gweithredydd. Hwyl EXISTS perfformiad gwell yn unig && oedd yr unig ffactor cyffredin a oedd yn weddill ar draws pob fersiwn o'r ymholiad araf.

Edrych ar dogfennaeth, gwelwn hynny && a ddefnyddir pan fydd angen i chi ddod o hyd i elfennau cyffredin rhwng dwy arae.

Yn y cais gwreiddiol dyma:

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

Sy'n golygu ein bod yn gwneud chwiliad patrwm ar ein URLs, yna dod o hyd i'r groesffordd gyda'r holl URLs gyda swyddi cyffredin. Mae hyn ychydig yn ddryslyd oherwydd nid yw "urls" yma yn cyfeirio at y tabl sy'n cynnwys yr holl URLs, ond at y golofn "urls" yn y tabl recording_data.

Gydag amheuon cynyddol ynghylch &&, Ceisiais ddod o hyd i gadarnhad ar eu cyfer yn y cynllun ymholiad a gynhyrchwyd EXPLAIN ANALYZE (Roedd gen i gynllun wedi'i arbed yn barod, ond rydw i fel arfer yn fwy cyfforddus yn arbrofi yn SQL na cheisio deall didreiddedd cynllunwyr ymholiadau).

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

Roedd sawl llinell o hidlwyr yn unig o &&. Sy'n golygu bod y llawdriniaeth hon nid yn unig yn ddrud, ond hefyd yn perfformio sawl gwaith.

Profais hyn trwy ynysu'r cyflwr

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

Roedd yr ymholiad hwn yn araf. Gan fod y JOIN-s yn gyflym ac subqueries yn gyflym, yr unig beth ar ôl oedd && gweithredydd.

Dim ond gweithrediad allweddol yw hwn. Mae angen i ni bob amser chwilio'r tabl gwaelod cyfan o URLs i chwilio am batrwm, ac mae angen i ni bob amser ddod o hyd i groestoriadau. Ni allwn chwilio yn ôl cofnodion URL yn uniongyrchol, oherwydd dim ond IDau y mae'r rhain yn cyfeirio atynt urls.

Ar y ffordd i ateb

&& araf oherwydd bod y ddwy set yn enfawr. Bydd y llawdriniaeth yn gymharol gyflym os byddaf yn disodli urls ar { "http://google.com/", "http://wingify.com/" }.

Dechreuais chwilio am ffordd i wneud croestoriad gosodedig yn Postgres heb ei ddefnyddio &&, ond heb fawr o lwyddiant.

Yn y diwedd, fe wnaethom benderfynu datrys y broblem ar wahân: rhowch bopeth i mi urls llinellau y mae'r URL yn cyd-fynd â'r patrwm ar eu cyfer. Heb amodau ychwanegol fe fydd - 

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

Yn lle JOIN cystrawen Fi jyst yn defnyddio subquery ac ehangu recording_data.urls arae fel y gallwch chi gymhwyso'r amod yn uniongyrchol i mewn WHERE.

Y peth pwysicaf yma yw hynny && a ddefnyddir i wirio a yw cofnod penodol yn cynnwys URL cyfatebol. Os byddwch yn llygad croes ychydig, gallwch weld y llawdriniaeth hon yn symud trwy elfennau arae (neu resi o fwrdd) ac yn dod i ben pan fodlonir amod (match). Nid yw'n eich atgoffa o unrhyw beth? Ydw, EXISTS.

Achos ymlaen recording_data.urls gellir cyfeirio ato o'r tu allan i'r cyd-destun subquery, pan fydd hyn yn digwydd gallwn ddisgyn yn ôl ar ein hen ffrind EXISTS a lapio y subquery ag ef.

Gan roi popeth at ei gilydd, rydyn ni'n cael yr ymholiad terfynol wedi'i optimeiddio:

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

A'r amser arweiniol terfynol Time: 1898.717 ms Amser i ddathlu?!?

Ddim mor gyflym! Yn gyntaf mae angen i chi wirio cywirdeb. Roeddwn yn hynod o amheus EXISTS optimeiddio gan ei fod yn newid y rhesymeg i derfynu yn gynharach. Mae angen i ni fod yn sicr nad ydym wedi ychwanegu gwall nad yw'n amlwg at y cais.

Prawf syml oedd rhedeg count(*) ar ymholiadau araf a chyflym ar gyfer nifer fawr o setiau data gwahanol. Yna, ar gyfer is-set fach o'r data, fe wnes i wirio â llaw bod yr holl ganlyniadau'n gywir.

Rhoddodd pob prawf ganlyniadau cadarnhaol cyson. Rydym yn trwsio popeth!

Gwersi a Ddysgwyd

Mae llawer o wersi i’w dysgu o’r stori hon:

  1. Nid yw cynlluniau ymholiad yn dweud y stori gyfan, ond gallant roi cliwiau
  2. Nid y prif ddrwgdybwyr bob amser yw'r tramgwyddwyr go iawn
  3. Gellir dadansoddi ymholiadau araf i ynysu tagfeydd
  4. Nid yw pob optimizations yn gostyngol eu natur
  5. Defnyddio EXIST, lle bo modd, yn gallu arwain at gynnydd dramatig mewn cynhyrchiant

Allbwn

Aethom o amser ymholi o ~24 munud i 2 eiliad - cynnydd sylweddol mewn perfformiad! Er bod yr erthygl hon wedi dod allan yn fawr, digwyddodd yr holl arbrofion a wnaethom mewn un diwrnod, ac amcangyfrifwyd eu bod wedi cymryd rhwng 1,5 a 2 awr ar gyfer optimeiddio a phrofi.

Mae SQL yn iaith wych os nad ydych chi'n ei ofni, ond ceisiwch ei dysgu a'i defnyddio. Trwy gael dealltwriaeth dda o sut mae ymholiadau SQL yn cael eu gweithredu, sut mae'r gronfa ddata yn cynhyrchu cynlluniau ymholiad, sut mae mynegeion yn gweithio, a maint y data rydych chi'n delio ag ef yn syml, gallwch chi fod yn llwyddiannus iawn wrth optimeiddio ymholiadau. Mae'r un mor bwysig, fodd bynnag, i barhau i roi cynnig ar wahanol ddulliau gweithredu a chwalu'r broblem yn araf, gan ddod o hyd i'r tagfeydd.

Y peth gorau am gyflawni canlyniadau fel y rhain yw'r gwelliant cyflymder amlwg, gweladwy - lle mae adroddiad na fyddai hyd yn oed yn llwytho o'r blaen bellach yn llwytho bron yn syth.

Diolch yn arbennig i fy nghymrodyr ar orchymyn Aditya Mishra, Aditya Gauru и Varun Malhotra ar gyfer taflu syniadau a Dincar Pandir am ganfod gwall pwysig yn ein cais terfynol cyn i ni o'r diwedd ffarwelio ag ef!

Ffynhonnell: hab.com

Ychwanegu sylw