🥇L-istorja ta' investigazzjoni waħda SQL

F'Diċembru li għadda rċevejt rapport ta' bug interessanti mit-tim ta' appoġġ VWO. Il-ħin tat-tagħbija għal wieħed mir-rapporti analitiċi għal klijent korporattiv kbir deher projbittiv. U peress li dan huwa l-qasam tar-responsabbiltà tiegħi, immedjatament iffukajt fuq is-soluzzjoni tal-problema.

preistorja

Biex nagħmilha ċara dwar xiex qed nitkellem, ngħidilkom ftit dwar il-VWO. Din hija pjattaforma li biha tista’ tniedi diversi kampanji mmirati fuq il-websajts tiegħek: twettaq esperimenti A/B, issegwi viżitaturi u konverżjonijiet, tanalizza l-lembut tal-bejgħ, turi mapep tas-sħana u tilgħab reġistrazzjonijiet taż-żjarat.

Iżda l-iktar ħaġa importanti dwar il-pjattaforma hija r-rappurtar. Il-funzjonijiet kollha ta 'hawn fuq huma interkonnessi. U għall-klijenti korporattivi, ammont kbir ta 'informazzjoni tkun sempliċement inutli mingħajr pjattaforma b'saħħitha li tippreżentaha f'forma analitika.

Bl-użu tal-pjattaforma, tista 'tagħmel mistoqsija każwali fuq sett kbir ta' dejta. Hawn eżempju sempliċi:

Uri l-klikks kollha fuq il-paġna "abc.com"
MINN  SA 
għal nies li
użat Chrome JEW
(kienu fl-Ewropa U jużaw iPhone)

Oqgħod attent għall-operaturi Boolean. Huma disponibbli għall-klijenti fl-interface tal-mistoqsijiet biex jagħmlu mistoqsijiet kumplessi b'mod arbitrarju biex jiksbu kampjuni.

Talba bil-mod

Il-klijent inkwistjoni kien qed jipprova jagħmel xi ħaġa li intuwittivament għandha taħdem malajr:

Uri r-reġistrazzjonijiet kollha tas-sessjoni
għall-utenti li żaru kwalunkwe paġna
b'URL fejn hemm "/jobs"

Dan is-sit kellu ton ta 'traffiku u konna naħżnu aktar minn miljun URL uniċi biss għalih. U riedu jsibu mudell URL pjuttost sempliċi li kien relatat mal-mudell tan-negozju tagħhom.

Investigazzjoni preliminari

Ejja nagħtu ħarsa lejn dak li qed jiġri fid-database. Hawn taħt hija l-mistoqsija SQL bil-mod oriġinali:

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 ;

U hawn huma l-ħinijiet:

Ħin ippjanat: 1.480 ms
Ħin ta’ eżekuzzjoni: 1431924.650 ms

Il-mistoqsija crawled 150 elf ringiela. Il-pjanifikatur tal-mistoqsijiet wera ftit dettalji interessanti, iżda l-ebda ostakli ovvji.

Ejja nistudjaw it-talba aktar. Kif tistgħu taraw, hu jagħmel JOIN tliet tabelli:

sessjonijiet: biex turi l-informazzjoni tas-sessjoni: browser, aġent tal-utent, pajjiż, eċċ.
recording_data: URLs irreġistrati, paġni, tul taż-żjarat
URLs: Biex tevita li nidduplikaw URLs estremament kbar, aħna naħżnuhom f'tabella separata.

Innota wkoll li t-tabelli kollha tagħna diġà huma maqsuma minn account_id. B'dan il-mod, sitwazzjoni fejn kont wieħed partikolarment kbir jikkawża problemi għal oħrajn hija eskluża.

Tfittex ħjiel

Wara spezzjoni aktar mill-qrib, naraw li xi ħaġa ħażina ma 'talba partikolari. Ta' min jagħti ħarsa aktar mill-qrib lejn din il-linja:

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

L-ewwel ħsieb kien li forsi għax ILIKE fuq dawn l-URLs twal kollha (għandna aktar minn 1,4 miljun uniku URLs miġbura għal dan il-kont) il-prestazzjoni tista 'tbati.

Imma le, dak mhux il-punt!

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

Time: 5231.765 ms

It-talba għat-tfittxija tal-mudell innifsu tieħu biss 5 sekondi. It-tfittxija għal mudell f'miljun URL uniċi hija ċara li mhix problema.

Is-suspettat li jmiss fuq il-lista huwa diversi JOIN. Forsi l-użu żejjed tagħhom ikkawża t-tnaqqis? Normalment JOIN's huma l-aktar kandidati ovvji għal problemi ta' prestazzjoni, imma ma kontx nemmen li l-każ tagħna kien tipiku.

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

U dan ma kienx il-każ tagħna wkoll. JOIN‘s irriżultaw li kienu pjuttost veloċi.

Tidjiq taċ-ċirku tas-suspettati

Kont lest li nibda nbiddel il-mistoqsija biex nikseb kwalunkwe titjib possibbli fil-prestazzjoni. Jien u t-tim tiegħi żviluppajna 2 ideat ewlenin:

Uża EŻISTI għall-URL tas-subquery: Ridna nerġgħu niċċekkjaw jekk kienx hemm xi problemi bis-subquery għall-URLs. Mod wieħed biex jinkiseb dan huwa li sempliċiment tuża EXISTS. EXISTS jista ittejjeb bil-kbir il-prestazzjoni peress li tispiċċa immedjatament malli ssib l-unika spag li taqbel mal-kundizzjoni.

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

Ukoll, iva. Subquery meta mgeżwra EXISTS, jagħmel kollox super mgħaġġel. Il-mistoqsija loġika li jmiss hija għaliex it-talba ma JOIN-ami u s-subquery innifsu huma veloċi individwalment, iżda huma terriblement bil-mod flimkien?

Nimxu s-subquery lejn is-CTE : Jekk il-mistoqsija hija veloċi waħedha, nistgħu sempliċement nikkalkulaw ir-riżultat mgħaġġel l-ewwel u mbagħad nipprovduh lill-mistoqsija prinċipali

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;

Iżda kien għadu bil-mod ħafna.

Sib il-ħati

Dan il-ħin kollu, ħaġa waħda żgħira tefgħet quddiem għajnejja, li kontinwament warrabt. Imma peress li ma kien fadal xejn ieħor, iddeċidejt li nħares lejha wkoll. Qed nitkellem dwar && operatur. Bye EXISTS prestazzjoni mtejba biss && kien l-uniku fattur komuni li kien fadal fil-verżjonijiet kollha tal-mistoqsija bil-mod.

Ħarsa lejn dokumentazzjoni, naraw li && użat meta jkollok bżonn issib elementi komuni bejn żewġ arrays.

Fit-talba oriġinali din hija:

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

Li jfisser li nagħmlu tfittxija tal-mudell fuq l-URLs tagħna, imbagħad insibu l-intersezzjoni mal-URLs kollha b'postijiet komuni. Dan huwa daqsxejn konfuż għaliex "urls" hawnhekk ma tirreferix għat-tabella li fiha l-URLs kollha, iżda għall-kolonna "urls" fit-tabella recording_data.

B’suspetti dejjem jikber dwar &&, Ippruvajt insib konferma għalihom fil-pjan ta 'mistoqsija ġġenerat EXPLAIN ANALYZE (Kelli diġà pjan salvat, imma ġeneralment inkun aktar komdu nesperimenta fl-SQL milli nipprova nifhem l-opaċità tal-pjanifikaturi tal-mistoqsijiet).

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

Kien hemm diversi linji ta 'filtri biss minn &&. Li jfisser li din l-operazzjoni mhux biss kienet għalja, iżda wkoll imwettqa diversi drabi.

Ittestjajt dan billi iżolajt il-kundizzjoni

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

Din il-mistoqsija kienet bil-mod. Minħabba li l- JOIN-s huma mgħaġġla u subqueries huma mgħaġġla, l-unika ħaġa li fadal kienet && operatur.

Din hija biss operazzjoni ewlenija. Dejjem għandna bżonn infittxu t-tabella sottostanti kollha tal-URLs biex infittxu mudell, u dejjem għandna bżonn insibu intersezzjonijiet. Ma nistgħux infittxu direttament bir-rekords tal-URL, għaliex dawn huma biss IDs li jirreferu għalihom urls.

Fit-triq lejn soluzzjoni

&& bil-mod għax iż-żewġ settijiet huma enormi. L-operazzjoni tkun relattivament malajr jekk nissostitwixxi urls fuq { "http://google.com/", "http://wingify.com/" }.

Bdejt infittex mod kif tagħmel issettjat intersezzjoni f'Postgres mingħajr ma nuża &&, iżda mingħajr ħafna suċċess.

Fl-aħħar, iddeċidejna li nsolvu l-problema biss f'iżolament: agħtini kollox urls linji li għalihom il-URL jaqbel mal-mudell. Mingħajr kundizzjonijiet addizzjonali se jkun -

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

Minflok JOIN sintassi I biss użajt subquery u estiża recording_data.urls array sabiex tkun tista' tapplika direttament il-kundizzjoni fi WHERE.

L-iktar ħaġa importanti hawnhekk hija li && użat biex jiċċekkja jekk dħul partikolari fihx URL li jaqbel. Jekk tgħawweġ ftit, tista' tara li din l-operazzjoni timxi fl-elementi ta' firxa (jew ringieli ta' tabella) u tieqaf meta tiġi sodisfatta kundizzjoni (taqbil). Ma jfakkarkom f’xejn? Iva, EXISTS.

Għax fuq recording_data.urls jista 'jiġi referenzjat minn barra l-kuntest tas-subquery, meta jiġri dan nistgħu naqgħu lura fuq ħabib antik tagħna EXISTS u wrap is-subquery magħha.

Meta npoġġu kollox flimkien, niksbu l-mistoqsija finali ottimizzata:

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

U l-ħin finali finali Time: 1898.717 ms Ħin biex niċċelebraw?!?

Mhux daqshekk malajr! L-ewwel trid tiċċekkja l-korrettezza. Kont estremament suspettuż dwar EXISTS ottimizzazzjoni peress li tibdel il-loġika biex titlesta qabel. Irridu nkunu ċerti li ma żidnax żball mhux ovvju mat-talba.

Test sempliċi kellu jaħdem count(*) kemm fuq mistoqsijiet bil-mod kif ukoll veloċi għal numru kbir ta’ settijiet ta’ data differenti. Imbagħad, għal subsett żgħir tad-dejta, ivverifikajt manwalment li r-riżultati kollha kienu korretti.

It-testijiet kollha taw riżultati pożittivi b'mod konsistenti. Irranġajna kollox!

Lezzjonijiet Mgħallma

Hemm ħafna lezzjonijiet li wieħed jitgħallem minn din l-istorja:

Pjanijiet ta' mistoqsijiet ma jgħidux l-istorja kollha, iżda jistgħu jipprovdu ħjiel
Is-suspettati ewlenin mhux dejjem huma l-ħatja reali
Mistoqsijiet bil-mod jistgħu jinqasmu biex jiġu iżolati l-konġestjonijiet
Mhux l-ottimizzazzjonijiet kollha huma ta’ natura riduttiva
Użu EXIST, fejn possibbli, jistgħu jwasslu għal żidiet drammatiċi fil-produttività

Output

Morna minn ħin ta' mistoqsija ta' ~24 minuta għal 2 sekondi - żieda fil-prestazzjoni pjuttost sinifikanti! Għalkemm dan l-artikolu ħareġ kbir, l-esperimenti kollha li għamilna ġraw f'ġurnata waħda, u kien stmat li ħadu bejn 1,5 u 2 sigħat għall-ottimizzazzjonijiet u l-ittestjar.

L-SQL hija lingwa mill-isbaħ jekk ma tibżax minnha, imma tipprova titgħallem u tużaha. Billi tifhem tajjeb kif jiġu esegwiti l-mistoqsijiet SQL, kif id-database tiġġenera pjanijiet ta’ mistoqsijiet, kif jaħdmu l-indiċi, u sempliċement id-daqs tad-dejta li qed tittratta magħha, tista’ tkun suċċess kbir fl-ottimizzazzjoni tal-mistoqsijiet. Huwa ugwalment importanti, madankollu, li tkompli tipprova approċċi differenti u bil-mod tkisser il-problema, u nsibu l-konġestjonijiet.

L-aħjar parti dwar il-kisba ta 'riżultati bħal dawn hija t-titjib notevoli u viżibbli tal-veloċità - fejn rapport li qabel lanqas biss kien jgħabbi issa jgħabbi kważi istantanjament.

Grazzi speċjali lil sħabi fil-kmand ta’ Aditya Mishra, Aditya Gauru и Varun Malhotra għall-brainstorming u Dinkar Pandir talli sibna żball importanti fl-aħħar talba tagħna qabel fl-aħħar għidna addio għaliha!

Sors: www.habr.com

L-istorja ta 'investigazzjoni waħda SQL