🥇Historia e një hetimi SQL

Dhjetorin e kaluar mora një raport interesant defekti nga ekipi mbështetës i VWO. Koha e ngarkimit për një nga raportet analitike për një klient të madh të korporatës dukej penguese. Dhe duke qenë se kjo është fusha ime e përgjegjësisë, u fokusova menjëherë në zgjidhjen e problemit.

parahistorinë

Për ta bërë të qartë se për çfarë po flas, do t'ju tregoj pak për VWO. Kjo është një platformë me të cilën mund të nisni fushata të ndryshme të synuara në faqet tuaja të internetit: të kryeni eksperimente A/B, të gjurmoni vizitorët dhe konvertimet, të analizoni gypin e shitjeve, të shfaqni hartat e nxehtësisë dhe të luani regjistrimet e vizitave.

Por gjëja më e rëndësishme në lidhje me platformën është raportimi. Të gjitha funksionet e mësipërme janë të ndërlidhura. Dhe për klientët e korporatave, një sasi e madhe informacioni do të ishte thjesht e padobishme pa një platformë të fuqishme që e paraqet atë në formë analitike.

Duke përdorur platformën, mund të bëni një pyetje të rastësishme në një grup të madh të dhënash. Ja një shembull i thjeshtë:

Shfaq të gjitha klikimet në faqen "abc.com" NGA <data d1> NË <data d2> për njerëzit që kanë përdorur Chrome OR (ndodhet në Evropë DHE kanë përdorur një iPhone)

Kushtojini vëmendje operatorëve Boolean. Ato janë të disponueshme për klientët në ndërfaqen e pyetjeve për të bërë pyetje arbitrare komplekse për të marrë mostra.

Kërkesë e ngadaltë

Klienti në fjalë po përpiqej të bënte diçka që në mënyrë intuitive duhet të funksiononte shpejt:

Shfaq të gjitha të dhënat e sesioneve për përdoruesit që kanë vizituar çdo faqe me një URL që përmban "/jobs"

Kjo faqe kishte një ton trafiku dhe ne ruanim mbi një milion URL unike vetëm për të. Dhe ata donin të gjenin një shabllon mjaft të thjeshtë URL që lidhet me modelin e tyre të biznesit.

Hetimi paraprak

Le të hedhim një vështrim se çfarë po ndodh në bazën e të dhënave. Më poshtë është pyetja origjinale e ngadaltë SQL:

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 ;

Dhe këtu janë oraret:

Koha e planifikuar: 1.480 ms Koha e ekzekutimit: 1431924.650 ms

Kërkesa zvarriti 150 mijë rreshta. Planifikuesi i pyetjeve tregoi disa detaje interesante, por pa pengesa të dukshme.

Le ta studiojmë kërkesën më tej. Siç mund ta shihni, ai bën JOIN tre tabela:

seanca: për të shfaqur informacionin e sesionit: shfletuesin, agjentin e përdoruesit, vendin, etj.
regjistrimi_të dhënat: URL-të e regjistruara, faqet, kohëzgjatja e vizitave
urls: Për të shmangur dublikimin e URL-ve jashtëzakonisht të mëdha, ne i ruajmë ato në një tabelë të veçantë.

Gjithashtu vini re se të gjitha tabelat tona tashmë janë ndarë nga account_id. Në këtë mënyrë, një situatë ku një llogari veçanërisht e madhe shkakton probleme për të tjerët përjashtohet.

Duke kërkuar për të dhëna

Pas një inspektimi më të afërt, ne shohim se diçka nuk është në rregull me një kërkesë të veçantë. Vlen të hedhim një vështrim më të afërt në këtë rresht:

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

Mendimi i parë ishte se ndoshta sepse ILIKE në të gjitha këto URL të gjata (ne kemi mbi 1,4 milion unik URL-të e mbledhura për këtë llogari) performanca mund të vuajë.

Por jo, nuk është kjo gjëja!

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

Time: 5231.765 ms

Vetë kërkesa e kërkimit të shabllonit zgjat vetëm 5 sekonda. Kërkimi i një modeli në një milion URL unike nuk është qartësisht problem.

Të dyshuarit e radhës në listë janë disa JOIN. Ndoshta përdorimi i tepërt i tyre ka shkaktuar ngadalësim? Zakonisht JOIN's janë kandidatët më të dukshëm për problemet e performancës, por nuk besoja se rasti ynë ishte 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

Dhe ky nuk ishte gjithashtu rasti ynë. JOINdoli të ishte mjaft i shpejtë.

Duke ngushtuar rrethin e të dyshuarve

Isha gati të filloja të ndryshoja pyetjen për të arritur çdo përmirësim të mundshëm të performancës. Ekipi im dhe unë zhvilluam 2 ide kryesore:

Përdorni EXISTS për URL-në e nënpyetjes: Ne donim të kontrollonim përsëri nëse kishte ndonjë problem me nënpyetjen për URL-të. Një mënyrë për ta arritur këtë është thjesht përdorimi EXISTS. EXISTS mund përmirëson shumë performancën pasi përfundon menjëherë sapo gjen vargun e vetëm që përputhet me kushtin.

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

Epo, po. Nënpyetje kur mbështillet EXISTS, e bën gjithçka super të shpejtë. Pyetja tjetër logjike është pse kërkesa me JOIN-ami dhe vetë subquery janë të shpejtë individualisht, por janë tmerrësisht të ngadalshëm së bashku?

Zhvendosja e nënpyetjes në CTE : Nëse pyetja është e shpejtë më vete, ne thjesht mund të llogarisim rezultatin e shpejtë fillimisht dhe më pas t'ia ofrojmë atë pyetjes kryesore

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;

Por ishte ende shumë i ngadalshëm.

Gjetja e fajtorit

Gjatë gjithë kësaj kohe, një gjë e vogël shkëlqeu para syve të mi, të cilën vazhdimisht e laja mënjanë. Por duke qenë se nuk kishte mbetur asgjë tjetër, vendosa ta shikoja edhe atë. Unë jam duke folur për && operatori. Mirupafshim EXISTS vetëm performanca e përmirësuar && ishte i vetmi faktor i përbashkët i mbetur në të gjitha versionet e pyetjes së ngadaltë.

Duke shikuar dokumentacionin, ne e shohim atë && përdoret kur ju duhet të gjeni elemente të përbashkëta midis dy vargjeve.

Në kërkesën origjinale kjo është:

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

Që do të thotë që ne bëjmë një kërkim modeli në URL-të tona, më pas gjejmë kryqëzimin me të gjitha URL-të me postimet e zakonshme. Kjo është pak konfuze sepse "urls" këtu nuk i referohet tabelës që përmban të gjitha URL-të, por kolonës "urls" në tabelë. recording_data.

Me dyshime në rritje në lidhje me &&, u përpoqa të gjeja konfirmimin për ta në planin e pyetjeve të krijuara EXPLAIN ANALYZE (Unë tashmë kisha një plan të ruajtur, por zakonisht jam më rehat duke eksperimentuar në SQL sesa duke u përpjekur të kuptoj paqartësinë e planifikuesve të pyetjeve).

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

Kishte disa rreshta filtrash vetëm nga &&. Që do të thoshte se ky operacion jo vetëm ishte i shtrenjtë, por edhe i kryer disa herë.

Unë e testova këtë duke izoluar gjendjen

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

Kjo pyetje ishte e ngadaltë. Sepse JOIN-at janë të shpejta dhe pyetjet e nënshtruara janë të shpejta, e vetmja gjë që mbeti ishte && operatori.

Ky është vetëm një operacion kyç. Gjithmonë duhet të kërkojmë të gjithë tabelën themelore të URL-ve për të kërkuar një model dhe gjithmonë duhet të gjejmë kryqëzime. Ne nuk mund të kërkojmë drejtpërdrejt sipas regjistrimeve të URL-së, sepse këto janë vetëm ID-të që u referohen urls.

Në rrugën drejt zgjidhjes

&& i ngadalshëm sepse të dy grupet janë të mëdha. Operacioni do të jetë relativisht i shpejtë nëse e zëvendësoj urls mbi { "http://google.com/", "http://wingify.com/" }.

Fillova të kërkoja një mënyrë për të bërë kryqëzimin e vendosur në Postgres pa përdorur &&, por pa shumë sukses.

Në fund, vendosëm ta zgjidhim problemin në izolim: më jep gjithçka urls linjat për të cilat URL-ja përputhet me modelin. Pa kushte shtesë do të jetë -

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

Në vend të JOIN sintaksë Sapo përdora një nënpyetje dhe e zgjerova recording_data.urls grup në mënyrë që të mund të aplikoni drejtpërdrejt kushtin në WHERE.

Gjëja më e rëndësishme këtu është se && përdoret për të kontrolluar nëse një hyrje e caktuar përmban një URL që përputhet. Nëse shikoni pak, mund të shihni se ky operacion lëviz nëpër elementët e një grupi (ose rreshtash të një tabele) dhe ndalon kur plotësohet një kusht (përputhje). Nuk ju kujton asgjë? Po, EXISTS.

Që në vazhdim recording_data.urls mund të referohet nga jashtë kontekstit të nënpyetjes, kur kjo ndodh, ne mund të kthehemi në mikun tonë të vjetër EXISTS dhe mbështillni pyetjen me të.

Duke bashkuar gjithçka, marrim pyetjen përfundimtare të optimizuar:

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

Dhe koha e fundit e çojë Time: 1898.717 ms Koha për të festuar?!?

Jo kaq shpejt! Së pari ju duhet të kontrolloni korrektësinë. Unë isha jashtëzakonisht dyshues për EXISTS optimizimi pasi ndryshon logjikën për të përfunduar më herët. Duhet të jemi të sigurt se nuk kemi shtuar një gabim jo të dukshëm në kërkesë.

Një provë e thjeshtë ishte për të drejtuar count(*) në pyetjet e ngadalta dhe të shpejta për një numër të madh grupesh të ndryshme të dhënash. Më pas, për një nëngrup të vogël të të dhënave, verifikova manualisht që të gjitha rezultatet ishin të sakta.

Të gjitha testet dhanë rezultate të vazhdueshme pozitive. Ne rregulluam gjithçka!

Mesimet e mesuara

Ka shumë mësime për të mësuar nga kjo histori:

Planet e pyetjeve nuk tregojnë të gjithë historinë, por ato mund të japin të dhëna
Të dyshuarit kryesorë nuk janë gjithmonë fajtorët e vërtetë
Pyetjet e ngadalta mund të zbërthehen për të izoluar pengesat
Jo të gjitha optimizimet janë reduktuese në natyrë
Përdorim EXIST, ku është e mundur, mund të çojë në rritje dramatike të produktivitetit

Prodhim

Kemi kaluar nga koha e pyetjes prej ~ 24 minutash në 2 sekonda - një rritje mjaft domethënëse e performancës! Megjithëse ky artikull doli i madh, të gjitha eksperimentet që bëmë ndodhën brenda një dite dhe u vlerësua se atyre u deshën nga 1,5 deri në 2 orë për optimizime dhe testime.

SQL është një gjuhë e mrekullueshme nëse nuk keni frikë prej saj, por përpiquni ta mësoni dhe ta përdorni. Duke pasur një kuptim të mirë se si ekzekutohen pyetjet SQL, si baza e të dhënave gjeneron planet e pyetjeve, si funksionojnë indekset dhe thjesht madhësinë e të dhënave me të cilat po trajtoni, mund të jeni shumë të suksesshëm në optimizimin e pyetjeve. Është po aq e rëndësishme, megjithatë, të vazhdosh të provosh qasje të ndryshme dhe të zgjidhësh ngadalë problemin, duke gjetur pengesat.

Pjesa më e mirë për arritjen e rezultateve si këto është përmirësimi i dukshëm dhe i dukshëm i shpejtësisë - ku një raport që më parë as nuk do të ngarkohej tani ngarkohet pothuajse menjëherë.

Falenderim i veçantë për shokët e mi nën komandën e Aditya Mishra, Aditya Gauru и Varun Malhotra për stuhi mendimesh dhe Dinkar Pandir për gjetjen e një gabimi të rëndësishëm në kërkesën tonë përfundimtare përpara se t'i themi lamtumirë!

Burimi: www.habr.com

Historia e një hetimi SQL