🥇SQL ikerketa baten istorioa

Joan den abenduan VWO laguntza-taldearen akatsen txosten interesgarri bat jaso nuen. Bezero korporatibo handi baten txosten analitikoetako bat kargatzeko denbora debekua zirudien. Eta hori nire ardura-eremua denez, berehala arazoa konpontzera bideratu nintzen.

historiaurrea

Zertaz ari naizen argi uzteko, VWOri buruz pixka bat kontatuko dizuet. Plataforma bat da, zeinaren bidez zuzendutako hainbat kanpaina abiarazi ditzakezu zure webguneetan: A/B esperimentuak egin, bisitarien eta bihurketen jarraipena egin, salmenta-inbutua aztertu, bero-mapak bistaratu eta bisiten grabazioak erreproduzitu.

Baina plataformaren gauzarik garrantzitsuena berri ematea da. Aurreko funtzio guztiak elkarri lotuta daude. Eta bezero korporatiboentzat, informazio kopuru handi bat alferrikakoa izango litzateke analitika moduan aurkezten duen plataforma indartsurik gabe.

Plataforma erabiliz, ausazko kontsulta bat egin dezakezu datu multzo handi batean. Hona hemen adibide sinple bat:

Erakutsi klik guztiak "abc.com" orrialdean <data d1> ARABERA <data d2> Chrome EDO erabiltzen duten pertsonentzat (Europan kokatuta ETA iPhone bat erabilita)

Erreparatu operadore boolearrei. Kontsulten interfazean bezeroen eskura daude kontsulta konplexuak arbitrarioki egiteko laginak lortzeko.

Eskaera motela

Aipatutako bezeroa intuitiboki azkar funtzionatu beharko lukeen zerbait egiten saiatzen ari zen:

Erakutsi "/jobs" duen URL batekin edozein orrialde bisitatu duten erabiltzaileentzako saio-erregistro guztiak

Gune honek trafiko asko zuen eta milioi bat URL esklusibo baino gehiago gordetzen genituen horretarako. Eta euren negozio-ereduarekin erlazionatutako URL txantiloi sinple samarra aurkitu nahi zuten.

Aurretiazko ikerketa

Ikus dezagun zer gertatzen den datu-basean. Jarraian, jatorrizko SQL kontsulta motela dago:

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 ;

Eta hona hemen ordutegiak:

Aurreikusitako denbora: 1.480 ms Exekuzio denbora: 1431924.650 ms

Kontsultak 150 mila errenkada arakatu ditu. Kontsulten planifikatzaileak xehetasun interesgarri pare bat erakutsi zituen, baina ez dago botilanik nabarmenik.

Azter dezagun eskaera gehiago. Ikusten duzuenez, egiten du JOIN hiru taula:

saioak: saioaren informazioa bistaratzeko: arakatzailea, erabiltzaile-agentea, herrialdea, eta abar.
grabaketa_datuak: erregistratutako URLak, orriak, bisiten iraupena
urls: URL oso handiak bikoiztea ekiditeko, aparteko taula batean gordetzen ditugu.

Kontuan izan, gainera, gure taula guztiak dagoeneko zatituta daudela account_id. Horrela, kontu handi batek besteei arazoak eragiten dizkien egoera baztertzen da.

Arrastoen bila

Gertuago aztertuta, eskaera jakin batean zerbait gaizki dagoela ikusten dugu. Merezi du lerro hau gertutik aztertzea:

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

Lehen pentsamendua izan zen agian horregatik ILIKE URL luze hauetan guztietan (1,4 milioitik gora ditugu berezia Kontu honetarako bildutako URLak) errendimenduak kaltetu ditzake.

Baina ez, hori ez da kontua!

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

Time: 5231.765 ms

Txantiloiaren bilaketa-eskaerak 5 segundo baino ez ditu behar. Milioi bat URL esklusibotan eredu bat bilatzea, argi eta garbi, ez da arazo bat.

Zerrendako hurrengo susmagarria hainbat da JOIN. Haien gehiegizko erabilerak moteltzea eragin du agian? Normalean JOINErrendimendu-arazoetarako hautagairik nabarmenenak dira, baina ez nuen uste gure kasua ohikoa zenik.

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

Eta hau ere ez zen gure kasua. JOINnahiko azkarra izan da.

Susmagarrien zirkulua murriztea

Kontsulta aldatzen hasteko prest nengoen errendimendu-hobekuntza posibleak lortzeko. Nire taldeak eta biok 2 ideia nagusi garatu ditugu:

Erabili EXISTS azpikontsultaren URLrako: URLen azpikontsultarekin arazorik izan ote den berriro egiaztatu nahi izan dugu. Hori lortzeko modu bat erabiltzea besterik ez da EXISTS. EXISTS ahal asko hobetu errendimendua egoerarekin bat datorren kate bakarra aurkitu bezain laster berehala amaitzen baita.

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

Bueno, bai. Azpikontsulta bilduta dagoenean EXISTS, dena oso azkar egiten du. Hurrengo galdera logikoa da zergatik eskaerarekin JOIN-ami eta azpikontsulta bera azkarrak dira banaka, baina izugarri motelak al dira elkarrekin?

Azpikontsulta CTEra eramatea : Kontsulta bere kabuz azkarra bada, lehenik eta behin emaitza azkarra kalkula dezakegu eta ondoren kontsulta nagusiari eman.

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;

Baina oraindik oso motela zen.

Erruduna aurkitzea

Denbora honetan guztian, gauza txiki bat agertu zitzaidan begien aurrean, etengabe baztertzen nuena. Baina beste ezer geratzen ez zenez, berari ere begiratzea erabaki nuen. buruz ari naiz && operadorea. Agur EXISTS errendimendua hobetu besterik ez dago && kontsulta geldoaren bertsio guztietan geratzen zen faktore komun bakarra zen.

Begira dokumentazioa, hori ikusten dugu && bi arrayren artean elementu komunak aurkitu behar dituzunean erabiltzen da.

Jatorrizko eskaeran hau da:

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

Horrek esan nahi du ereduen bilaketa bat egiten dugula gure URLetan, eta gero mezu arruntak dituzten URL guztiekin elkargunea aurkitzen dugu. Pixka bat nahasia da, hemen "urls" ez delako URL guztiak dituen taula aipatzen, taulako "urls" zutabeari baizik recording_data.

Gero eta susmo handiagoarekin &&, sortutako kontsulta-planean haien baieztapena bilatzen saiatu nintzen EXPLAIN ANALYZE (Dagoeneko plan bat gordeta nuen, baina normalean erosoago nago SQLn esperimentatzen kontsulta-planifikatzaileen opakutasuna ulertzen saiatzen baino).

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

Hainbat iragazki-lerro zeuden soilik &&. Horrek esan nahi zuen operazio hau garestia ez ezik, hainbat aldiz ere egin zela.

Egoera isolatuz probatu nuen

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

Kontsulta hau motela izan zen. Zeren JOIN-s azkarrak dira eta azpikontsultak azkarrak dira, geratzen zen bakarra zen && operadorea.

Hau funtsezko eragiketa bat besterik ez da. Eredu bat bilatzeko azpiko URLen taula osoa bilatu behar dugu beti, eta elkarguneak bilatu behar ditugu beti. Ezin dugu zuzenean URL-erregistroen arabera bilatu, horiek aipatzen dituzten IDak besterik ez direlako urls.

Konponbiderako bidean

&& motela, bi multzoak erraldoiak direlako. Eragiketa nahiko azkarra izango da ordezkatzen badut urls on { "http://google.com/", "http://wingify.com/" }.

Postgres-en elkarguneak erabili gabe egiteko modu bat bilatzen hasi nintzen &&, baina arrakasta handirik gabe.

Azkenean, arazoa bakarka konpontzea erabaki genuen: dena eman urls URLak ereduarekin bat datorren lerroak. Baldintza gehigarririk gabe izango da -

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

Horren ordez JOIN sintaxia azpikontsulta bat erabili eta zabaldu dut recording_data.urls array, baldintza zuzenean aplikatu ahal izateko WHERE.

Hemen garrantzitsuena hori da && Sarrera jakin batek bat datorren URLrik duen egiaztatzeko erabiltzen da. Begiak apur bat estutzen badituzu, ikus dezakezu eragiketa hau array baten (edo taula baten errenkadetan) elementuetan zehar mugitzen dela eta baldintza bat betetzen denean gelditzen dela. Ez al dizu ezer gogoratzen? Bai, EXISTS.

Geroztik recording_data.urls azpikontsulta testuingurutik kanpo erreferentzia egin daiteke, hau gertatzen denean gure lagun zaharrarengan erori gaitezke EXISTS eta bildu azpikontsulta horrekin.

Dena elkartuta, azken kontsulta optimizatua lortzen dugu:

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

Eta azken denbora Time: 1898.717 ms Ospatzeko garaia?!?

Ez hain azkar! Lehenik eta behin, zuzentasuna egiaztatu behar duzu. Oso susmagarria nintzen EXISTS optimizazioa logika aldatzen duelako lehenago osatzeko. Ziurtatu behar dugu ez dugula ageriko errorerik gehitu eskaerari.

Proba sinple bat exekutatzen zen count(*) datu-multzo ezberdin askotarako kontsulta motel nahiz azkarrean. Ondoren, datuen azpimultzo txiki baterako, eskuz egiaztatu nuen emaitza guztiak zuzenak zirela.

Proba guztiek emaitza positiboak eman zituzten etengabe. Dena konpondu dugu!

Ikasgaiak

Istorio honetatik ikasgai asko atera daitezke:

Kontsulta planek ez dute istorio osoa kontatzen, baina pistak eman ditzakete
Susmagarri nagusiak ez dira beti benetako errudunak
Kontsulta motelak hautsi daitezke botila-lepoak isolatzeko
Optimizazio guztiak ez dira izaera murriztaileak
Erabili EXIST, ahal den neurrian, produktibitatearen igoera izugarria ekar dezake

Irteera

~24 minutuko kontsulta-denboratik 2 segundora igaro ginen - errendimenduaren igoera nabarmena da! Artikulu hau handia atera zen arren, egin genituen esperimentu guztiak egun bakarrean gertatu ziren, eta optimizazio eta probak egiteko 1,5 eta 2 ordu artean behar zirela kalkulatu zen.

SQL hizkuntza zoragarria da beldurrik ez baduzu, baina saiatu ikasten eta erabiltzen. SQL kontsultak nola exekutatzen diren, datu-baseak kontsulta-planak nola sortzen dituen, indizeek nola funtzionatzen duten eta, besterik gabe, tratatzen ari zaren datuen tamaina ondo ulertuta, arrakasta handia izan dezakezu kontsultak optimizatzen. Garrantzi berdina da, hala ere, planteamendu desberdinak probatzen jarraitzea eta poliki-poliki arazoa apurtzen jarraitzea, botilak aurkituz.

Horrelako emaitzak lortzeko alderdirik onena abiaduraren hobekuntza nabaria da; lehen kargatu ere egiten ez zen txosten bat orain ia berehala kargatzen da.

Esker bereziak nire lagunak Aditya Mishraren aginduetara, Aditya Gauru и Varun Malhotra ideia-jasa egiteko eta Dinkar Pandir Gure azken eskaeran azkenean agur esan baino lehen akats garrantzitsu bat aurkitzeagatik!

Iturria: www.habr.com

SQL ikerketa baten istorioa