🥇Istwa a nan yon sèl envestigasyon SQL

Desanm pase a mwen te resevwa yon rapò ensèk enteresan nan men ekip sipò VWO la. Tan an chaj pou youn nan rapò analiz yo pou yon kliyan gwo antrepriz te sanble entèdi. Epi kòm sa a se zòn responsablite mwen an, mwen imedyatman konsantre sou rezoud pwoblèm nan.

pre-istwa

Pou fè konnen sa m ap pale a, m ap di nou yon ti kras sou VWO. Sa a se yon platfòm ak ki ou ka lanse divès kanpay vize sou sit entènèt ou yo: fè eksperyans A/B, swiv vizitè yo ak konvèsyon, analize antonwa lavant yo, montre kat chalè ak jwe anrejistreman vizit yo.

Men, bagay ki pi enpòtan sou platfòm la se rapò. Tout fonksyon ki anwo yo konekte. Ak pou kliyan antrepriz, yon gwo kantite enfòmasyon ta dwe tou senpleman initil san yon platfòm pwisan ki prezante li nan fòm analytics.

Sèvi ak platfòm la, ou ka fè yon rechèch o aza sou yon seri done gwo. Men yon egzanp senp:

Montre tout klik sou paj "abc.com" SOTI <dat d1> POU <dat d2> pou moun ki te itilize Chrome OSWA (ki sitiye nan Ewòp AK te itilize yon iPhone)

Peye atansyon sou operatè Boolean. Yo disponib pou kliyan nan koòdone rechèch la pou fè demann abitrèman konplèks pou jwenn echantiyon.

Demann dousman

Kliyan an nan kesyon an t ap eseye fè yon bagay ki entwitif ta dwe travay byen vit:

Montre tout dosye sesyon pou itilizatè ki te vizite nenpòt paj ak yon URL ki gen "/jobs"

Sit sa a te gen yon tòn trafik epi nou te estoke plis pase yon milyon URL inik jis pou li. Apre sa, yo te vle jwenn yon modèl URL jistis senp ki gen rapò ak modèl biznis yo.

Ankèt preliminè

Ann pran yon gade nan sa k ap pase nan baz done a. Anba a se rekèt orijinal SQL dousman an:

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 ;

Ak isit la se tan yo:

Tan planifikasyon: 1.480 ms Tan ekzekisyon: 1431924.650 ms

Rekèt la ranpe 150 mil ranje. Planifikatè rechèch la te montre yon koup nan detay enteresan, men pa gen okenn blokaj evidan.

Ann etidye demann lan pi lwen. Kòm ou ka wè, li fè sa JOIN twa tab:

sesyon: pou montre enfòmasyon sou sesyon yo: navigatè, ajan itilizatè, peyi, elatriye.
done_anrejistreman: URL anrejistre, paj, dire vizit yo
adrès: Pou evite kopi URL ekstrèmman gwo, nou estoke yo nan yon tablo separe.

Epitou sonje ke tout tab nou yo deja divize pa account_id. Nan fason sa a, yon sitiyasyon kote yon kont patikilyèman gwo lakòz pwoblèm pou lòt moun yo eskli.

Kap chèche endikasyon

Lè nou enspeksyon pi pre, nou wè ke gen yon bagay ki mal ak yon demann patikilye. Li vo pran yon gade pi pre nan liy sa a:

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

Premye panse a te ke petèt paske ILIKE sou tout URL long sa yo (nou gen plis pase 1,4 milyon dola inik URL yo kolekte pou kont sa a) pèfòmans ka soufri.

Men, non, se pa pwen an!

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

Time: 5231.765 ms

Demann rechèch modèl la li menm pran sèlman 5 segonn. Chèche yon modèl nan yon milyon URL inik klèman pa yon pwoblèm.

Sispè kap vini an sou lis la se plizyè JOIN. Petèt twòp itilizasyon yo te lakòz ralentissement la? Anjeneral JOIN's yo se kandida ki pi evidan pou pwoblèm pèfòmans, men mwen pa t 'kwè nou an se te yon ka tipik.

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

Ak sa a tou pa te ka nou an. JOINa te tounen byen vit.

Redwi sèk sispèk yo

Mwen te pare yo kòmanse chanje rechèch la reyalize nenpòt amelyorasyon pèfòmans posib. Ekip mwen an ak mwen devlope 2 lide prensipal:

Sèvi ak EXISTS pou URL subquery: Nou te vle tcheke ankò si te gen nenpòt pwoblèm ak subrequest pou URL yo. Youn nan fason yo reyalize sa a se tou senpleman itilize EXISTS. EXISTS kapab anpil amelyore pèfòmans depi li fini imedyatman le pli vit ke li jwenn fisèl la sèlman ki matche ak kondisyon an.

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

Oke, wi. Subquery lè vlope nan EXISTS, fè tout bagay super vit. Pwochen kesyon ki lojik se poukisa demann lan ak JOIN-ami ak subquery nan tèt li yo vit endividyèlman, men yo fò anpil dousman ansanm?

Deplase subquery a nan CTE la : Si rechèch la vit poukont li, nou ka tou senpleman kalkile rezilta rapid la an premye epi bay li nan rechèch prensipal la.

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;

Men, li te toujou trè dousman.

Jwenn koupab la

Pandan tout tan sa a, yon ti bagay te klere devan je m ', ke mwen toujou ap bwose sou kote. Men, piske pa t gen anyen ankò, mwen te deside gade l tou. m ap pale de && operatè. Bye EXISTS jis amelyore pèfòmans && te sèl faktè komen ki rete nan tout vèsyon rechèch la dousman.

Gade dokimantasyon, nou wè sa && itilize lè ou bezwen jwenn eleman komen ant de etalaj.

Nan demann orijinal la sa a se:

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

Ki vle di nou fè yon rechèch modèl sou URL nou yo, Lè sa a, jwenn entèseksyon an ak tout URL yo ak pòs komen. Sa a se yon ti jan konfizyon paske "urls" isit la pa fè referans a tablo ki genyen tout URL yo, men nan kolòn "urls" nan tablo a. recording_data.

Ak grandi sispèk konsènan &&, Mwen te eseye jwenn konfimasyon pou yo nan plan rechèch la te pwodwi EXPLAIN ANALYZE (Mwen te deja gen yon plan sove, men mwen anjeneral pi konfòtab fè eksperyans nan SQL pase ap eseye konprann opakite nan planifikatè rechèch).

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

Te gen plizyè liy filtè sèlman soti nan &&. Ki vle di ke operasyon sa a pa te sèlman chè, men tou, fè plizyè fwa.

Mwen teste sa a pa izole kondisyon an

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

Rekèt sa a te ralanti. Paske la JOIN-s yo vit ak subqueries yo vit, sèl bagay ki rete te && operatè.

Sa a se jis yon operasyon kle. Nou toujou bezwen chèche tout tablo URL yo pou chèche yon modèl, epi nou toujou bezwen jwenn entèseksyon. Nou pa ka fè rechèch sou dosye URL dirèkteman, paske sa yo se jis idantite ki refere a urls.

Sou wout la nan yon solisyon

&& ralanti paske tou de seri yo gwo. Operasyon an pral relativman rapid si mwen ranplase urls sou { "http://google.com/", "http://wingify.com/" }.

Mwen te kòmanse chèche yon fason pou fè mete entèseksyon nan Postgres san yo pa itilize &&, men san anpil siksè.

Nan fen a, nou deside jis rezoud pwoblèm nan nan izolasyon: ban m 'tout bagay urls liy pou ki URL la matche ak modèl la. San kondisyon adisyonèl li pral -

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

Olye pou yo JOIN sentaks mwen jis itilize yon subquery ak elaji recording_data.urls etalaj pou ou ka aplike dirèkteman kondisyon an nan WHERE.

Bagay ki pi enpòtan isit la se sa && itilize pou tcheke si yon antre bay gen yon URL matche. Si ou strabiye yon ti kras, ou ka wè operasyon sa a deplase nan eleman yo nan yon etalaj (oswa ranje yon tab) epi li sispann lè yon kondisyon (matche ak) satisfè. Pa fè w sonje anyen? wi, EXISTS.

Depi sou recording_data.urls ka referans soti deyò kontèks la subquery, lè sa rive nou ka tonbe tounen sou ansyen zanmi nou an EXISTS epi vlope subquery a avèk li.

Mete tout bagay ansanm, nou jwenn final la rechèch optimize:

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

Ak tan final la Time: 1898.717 ms Tan pou fete?!?

Pa tèlman vit! Premye ou bezwen tcheke kòrèkteman an. Mwen te trè sispèk sou EXISTS optimize kòm li chanje lojik la fini pi bonè. Nou bezwen asire w ke nou pa te ajoute yon erè ki pa evidan nan demann lan.

Yon tès senp te kouri count(*) sou demann tou de dousman ak rapid pou yon gwo kantite seri done diferan. Lè sa a, pou yon ti sous-ensemble nan done yo, mwen manyèlman verifye ke tout rezilta yo te kòrèk.

Tout chèk yo te bay rezilta toujou pozitif. Nou ranje tout bagay!

Leson Aprann

Gen anpil leson yo dwe aprann nan istwa sa a:

Plan rechèch pa rakonte istwa a tout antye, men yo ka bay endikasyon
Sispèk prensipal yo pa toujou koupab yo reyèl
Rekèt ralanti yo ka kraze pou izole kou boutèy
Se pa tout optimize yo rediksyon nan lanati
Sèvi ak EXIST, kote sa posib, ka mennen nan ogmantasyon dramatik nan pwodiktivite

Sòti

Nou te ale soti nan yon tan rechèch nan ~ 24 minit a 2 segonn - byen yon ogmantasyon pèfòmans enpòtan! Malgre ke atik sa a te soti gwo, tout eksperyans nou te fè yo te rive nan yon sèl jou, epi li te estime ke yo te pran ant 1,5 ak 2 èdtan pou optimize ak tès.

SQL se yon lang bèl bagay si ou pa bezwen pè li, men eseye aprann epi sèvi ak li. Lè w gen yon bon konpreyansyon sou ki jan demann SQL yo egzekite, ki jan baz done a jenere plan rechèch, ki jan endèks travay, epi tou senpleman gwosè a nan done w ap fè fas ak, ou ka gen anpil siksè nan optimize demann. Li enpòtan egalman, sepandan, kontinye eseye diferan apwòch ak tou dousman kraze pwoblèm nan, jwenn bouch yo.

Pati ki pi bon sou reyalize rezilta tankou sa yo se amelyorasyon nan vitès aparan, vizib - kote yon rapò ki te deja pa ta menm chaje kounye a chaje prèske imedyatman.

Mèsi espesyal pou kanmarad mwen yo sou lòd Aditya Mishra, Aditya Gauru и Varun Malhotra pou brase lide ak Dinkar Pandir pou jwenn yon erè enpòtan nan demann final nou an anvan nou finalman te di orevwa nan li!

Sous: www.habr.com

Istwa a nan yon sèl envestigasyon SQL