🥇 Sagan af einni SQL rannsókn

Í desember síðastliðnum fékk ég áhugaverða villuskýrslu frá VWO stuðningsteyminu. Hleðslutími einnar greiningarskýrslna fyrir stóran fyrirtækjaviðskiptavin virtist óhóflegur. Og þar sem þetta er mitt ábyrgðarsvið einbeitti ég mér strax að því að leysa vandamálið.

Forsaga

Til að gera það skýrt um hvað ég er að tala ætla ég að segja þér aðeins frá VWO. Þetta er vettvangur sem þú getur sett af stað ýmsar markvissar herferðir á vefsíðum þínum: framkvæma A/B tilraunir, fylgjast með gestum og viðskipta, greina sölutrektina, birta hitakort og spila heimsóknarupptökur.

En það mikilvægasta við vettvanginn er skýrsla. Allar ofangreindar aðgerðir eru samtengdar. Og fyrir fyrirtækjaviðskiptavini væri mikið magn upplýsinga einfaldlega gagnslaus án öflugs vettvangs sem kynnir þær í greiningarformi.

Með því að nota pallinn geturðu gert handahófskenndar fyrirspurn um stórt gagnasett. Hér er einfalt dæmi:

Sýna alla smelli á síðunni "abc.com" FRÁ <date d1> TIL <date d2> fyrir fólk sem notaði Chrome OR (staðsett í Evrópu OG notaði iPhone)

Gefðu gaum að Boolean rekstraraðilum. Þær eru tiltækar viðskiptavinum í fyrirspurnarviðmótinu til að gera geðþótta flóknar fyrirspurnir til að fá sýnishorn.

Hæg beiðni

Viðkomandi viðskiptavinur var að reyna að gera eitthvað sem ætti að virka hratt:

Sýna allar lotuskrár fyrir notendur sem heimsóttu hvaða síðu sem er með vefslóð sem inniheldur "/jobs"

Þessi síða hafði tonn af umferð og við vorum að geyma yfir milljón einstaka vefslóðir bara fyrir hana. Og þeir vildu finna frekar einfalt vefslóð sniðmát sem tengdist viðskiptamódeli þeirra.

Forrannsókn

Við skulum skoða hvað er að gerast í gagnagrunninum. Hér að neðan er upprunalega hæga SQL fyrirspurnin:

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 ;

Og hér eru tímasetningarnar:

Áætlaður tími: 1.480 ms Framkvæmdatími: 1431924.650 ms

Fyrirspurnin skreið 150 þúsund línur. Fyrirspurnarskipuleggjandinn sýndi nokkrar áhugaverðar upplýsingar, en enga augljósa flöskuhálsa.

Við skulum rannsaka beiðnina frekar. Eins og þú sérð gerir hann það JOIN þrjú borð:

fundur: til að birta lotuupplýsingar: vafra, umboðsmann notenda, land og svo framvegis.
recording_data: skráðar vefslóðir, síður, lengd heimsókna
slóðir: Til að forðast að afrita mjög stórar vefslóðir geymum við þær í sérstakri töflu.

Athugaðu líka að öll borðin okkar eru þegar skipt af account_id. Þannig er ástand þar sem einn sérstaklega stór reikningur veldur öðrum vandamálum útilokað.

Er að leita að vísbendingum

Við nánari skoðun sjáum við að eitthvað er athugavert við tiltekna beiðni. Það er þess virði að skoða þessa línu nánar:

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

Fyrsta hugsunin var að ef til vill vegna þess ILIKE á öllum þessum löngu slóðum (við erum með yfir 1,4 milljónir einstakt Vefslóðum sem safnað er fyrir þennan reikning) gæti frammistaðan orðið fyrir skaða.

En nei, það er ekki málið!

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

Time: 5231.765 ms

Sniðmátsleitarbeiðnin sjálf tekur aðeins 5 sekúndur. Það er greinilega ekki vandamál að leita að mynstri í milljón einstökum vefslóðum.

Næsti grunaði á listanum eru nokkrir JOIN. Kannski hefur ofnotkun þeirra valdið hægaganginum? Venjulega JOIN's eru augljósustu frambjóðendurnir fyrir frammistöðuvandamál, en ég trúði því ekki að mál okkar væru dæmigerð.

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

Og þetta var heldur ekki okkar mál. JOIN's reyndist vera nokkuð hratt.

Að þrengja niður hring grunaðra

Ég var tilbúinn til að byrja að breyta fyrirspurninni til að ná fram mögulegum framförum. Ég og teymið mitt þróuðum 2 meginhugmyndir:

Notaðu EXISTS fyrir undirfyrirspurnarslóð: Við vildum athuga aftur hvort einhver vandamál væru með undirfyrirspurnina fyrir vefslóðirnar. Ein leið til að ná þessu er einfaldlega að nota EXISTS. EXISTS getur bæta árangur til muna þar sem því lýkur strax um leið og það finnur eina strenginn sem passar við skilyrðið.

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

Nú já. Undirfyrirspurn þegar henni er pakkað inn EXISTS, gerir allt frábær hratt. Næsta rökrétta spurningin er hvers vegna beiðnin með JOIN-ami og undirspurningin sjálf eru hröð hvert fyrir sig, en eru hræðilega hæg saman?

Færir undirfyrirspurnina í CTE : Ef fyrirspurnin er hröð ein og sér, getum við einfaldlega reiknað út hröðu niðurstöðuna fyrst og gefið hana síðan í aðalfyrirspurnina

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;

En það var samt mjög hægt.

Að finna sökudólginn

Allan þennan tíma blasti við mér einn lítill hlutur sem ég strauk stöðugt til hliðar. En þar sem ekkert annað var eftir ákvað ég að skoða hana líka. Ég er að tala um && rekstraraðili. Bless EXISTS bara bætt frammistöðu && var eini sameiginlegi þátturinn sem eftir var í öllum útgáfum hægu fyrirspurnarinnar.

Horfa á skjöl, við sjáum það && notað þegar þú þarft að finna sameiginlega þætti á milli tveggja fylkja.

Í upphaflegu beiðninni er þetta:

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

Sem þýðir að við gerum mynsturleit á vefslóðunum okkar, finnum síðan gatnamótin við allar vefslóðirnar með algengum færslum. Þetta er svolítið ruglingslegt vegna þess að "urls" hér vísar ekki til töflunnar sem inniheldur allar vefslóðirnar, heldur til "urls" dálksins í töflunni recording_data.

Með vaxandi grunsemdum varðandi &&, Ég reyndi að finna staðfestingu fyrir þá í fyrirspurnaráætluninni sem búið var til EXPLAIN ANALYZE (Ég var þegar með áætlun vistuð, en ég er yfirleitt öruggari með að gera tilraunir í SQL en að reyna að skilja ógagnsæi fyrirspurnaskipuleggjenda).

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

Það voru nokkrar línur af síum aðeins frá &&. Sem þýddi að þessi aðgerð var ekki bara dýr heldur einnig gerð nokkrum sinnum.

Ég prófaði þetta með því að einangra ástandið

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

Þessi fyrirspurn var hæg. Vegna þess að JOIN-s eru hröð og undirfyrirspurnir eru hraðar, það eina sem var eftir var && rekstraraðili.

Þetta er bara lykilaðgerð. Við þurfum alltaf að leita í allri undirliggjandi töflu yfir vefslóðir til að leita að mynstri og við þurfum alltaf að finna gatnamót. Við getum ekki leitað beint eftir vefslóðaskrám vegna þess að þetta eru bara auðkenni sem vísa til urls.

Á leiðinni að lausn

&& hægt vegna þess að bæði settin eru risastór. Aðgerðin verður tiltölulega fljótleg ef ég skipti út urls á { "http://google.com/", "http://wingify.com/" }.

Ég byrjaði að leita að leið til að gera sett gatnamót í Postgres án þess að nota &&, en án mikils árangurs.

Á endanum ákváðum við bara að leysa vandamálið í einangrun: gefa mér allt urls línur þar sem vefslóðin passar við mynstrið. Án viðbótarskilyrða verður það -

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

Í stað þess að JOIN setningafræði ég notaði bara undirfyrirspurn og stækkaði recording_data.urls fylki þannig að þú getur beint beitt skilyrðinu í WHERE.

Það mikilvægasta hér er það && notað til að athuga hvort tiltekin færsla inniheldur samsvarandi vefslóð. Ef þú kíkir aðeins, geturðu séð að þessi aðgerð færist í gegnum þætti fylkis (eða raða töflu) og hættir þegar skilyrði (samsvörun) er uppfyllt. Minnir þig ekki á neitt? Já, EXISTS.

Síðan á recording_data.urls hægt að vísa til utan undirspurningarsamhengisins, þegar þetta gerist getum við fallið aftur á gamla vin okkar EXISTS og vefja undirfyrirspurnina með henni.

Þegar allt er sett saman fáum við endanlega fínstilltu fyrirspurnina:

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

Og lokatíminn Time: 1898.717 ms Kominn tími á að fagna?!?

Ekki svona hratt! Fyrst þarftu að athuga réttmæti. Ég var mjög tortrygginn um EXISTS hagræðingu þar sem það breytir rökfræðinni til að ljúka fyrr. Við verðum að vera viss um að við höfum ekki bætt óaugljósri villu við beiðnina.

Einfalt próf var að keyra count(*) á bæði hægum og hröðum fyrirspurnum fyrir mikinn fjölda mismunandi gagnasetta. Síðan, fyrir lítið hlutmengi gagna, staðfesti ég handvirkt að allar niðurstöður væru réttar.

Allar prófanir gáfu stöðugt jákvæðar niðurstöður. Við redduðum öllu!

Lexía lærð

Það er margt sem má draga af þessari sögu:

Fyrirspurnaráætlanir segja ekki alla söguna, en þær geta gefið vísbendingar
Aðal grunaðir eru ekki alltaf raunverulegir sökudólgar
Hægt er að skipta niður hægum fyrirspurnum til að einangra flöskuhálsa
Ekki eru allar hagræðingar afoxandi í eðli sínu
Nota EXIST, þar sem hægt er, getur leitt til stórkostlegrar framleiðniaukningar

Output

Við fórum úr fyrirspurnartíma upp á ~24 mínútur í 2 sekúndur - töluverð frammistöðuaukning! Þrátt fyrir að þessi grein hafi verið stór, gerðust allar tilraunirnar sem við gerðum á einum degi og það var áætlað að þær hafi tekið á milli 1,5 og 2 klukkustundir í hagræðingu og prófun.

SQL er dásamlegt tungumál ef þú ert ekki hræddur við það, en reyndu að læra og nota það. Með því að hafa góðan skilning á því hvernig SQL fyrirspurnir eru framkvæmdar, hvernig gagnagrunnurinn býr til fyrirspurnaáætlanir, hvernig vísitölur virka og einfaldlega stærð gagna sem þú ert að fást við geturðu náð mjög góðum árangri í að fínstilla fyrirspurnir. Það er þó ekki síður mikilvægt að halda áfram að prófa mismunandi aðferðir og brjóta vandann hægt niður og finna flöskuhálsana.

Það besta við að ná árangri sem þessum er áberandi, sýnileg hraðabót - þar sem skýrsla sem áður var ekki einu sinni hlaðin inn hleðst nú nánast samstundis.

Sérstakar þakkir til félagar mínir undir stjórn Aditya Mishra, Aditya Gauru и Varun Malhotra fyrir hugarflug og Dinkar Pandi fyrir að finna mikilvæga villu í lokabeiðni okkar áður en við kvöddum hana loksins!

Heimild: www.habr.com

Sagan af einni SQL rannsókn