ProHoster > Blog > Bestjoer > It ferhaal fan ien SQL-ûndersyk

It ferhaal fan ien SQL-ûndersyk

Ferline desimber krige ik in nijsgjirrich bugrapport fan it VWO-stipeteam. De laadtiid foar ien fan 'e analytyske rapporten foar in grutte bedriuwskliïnt like ferbean. En om't dit myn ferantwurdlikensgebiet is, rjochte ik my fuortendaliks op it oplossen fan it probleem.

prehistoarje

Om dúdlik te meitsjen wêr't ik it oer ha, sil ik jo in bytsje fertelle oer VWO. Dit is in platfoarm wêrmei jo ferskate rjochte kampanjes op jo websiden kinne lansearje: A/B-eksperiminten útfiere, besikers en konversaasjes folgje, de ferkeaptrechter analysearje, waarmtekaarten werjaan en besite-opnames spielje.

Mar it wichtichste ding oer it platfoarm is rapportaazje. Alle boppesteande funksjes binne mei-inoar ferbûn. En foar bedriuwskliïnten soe in enoarme hoemannichte ynformaasje gewoan nutteloos wêze sûnder in krêftich platfoarm dat it yn analytyske foarm presintearret.

Mei it brûken fan it platfoarm kinne jo in willekeurige query meitsje op in grutte dataset. Hjir is in ienfâldich foarbyld:

Lit alle klikken sjen op side "abc.com" FAN <date d1> TO <date d2> foar minsken dy't Chrome OR brûkten (yn Jeropa leit EN in iPhone brûkten)

Jou omtinken oan Boolean operators. Se binne beskikber foar kliïnten yn 'e query-ynterface om willekeurich komplekse fragen te meitsjen om samples te krijen.

Stadich fersyk

De klant yn kwestje besocht wat te dwaan dat yntuïtyf fluch soe moatte wurkje:

Lit alle sesje-records sjen foar brûkers dy't elke side besocht hawwe mei in URL mei "/ jobs"

Dizze side hie in ton ferkear en wy bewarje mear dan in miljoen unike URL's krekt foar. En se woenen in frij ienfâldige URL-sjabloan fine dy't relatearre oan har bedriuwsmodel.

Foarriedich ûndersyk

Litte wy ris sjen wat der bart yn 'e databank. Hjirûnder is de orizjinele stadige SQL-query:

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 ;

En hjir binne de timings:

Planne tiid: 1.480 ms Utfiertiid: 1431924.650 ms

De query krûpte 150 tûzen rigen. De queryplanner liet in pear nijsgjirrige details sjen, mar gjin dúdlike knelpunten.

Litte wy it fersyk fierder ûndersykje. Sa't jo sjen kinne, hy docht JOIN trije tabellen:

  1. sesjes: om sesje-ynformaasje wer te jaan: browser, brûkersagint, lân, ensfh.
  2. opname_data: opnommen URL's, siden, doer fan besites
  3. urls: Om it duplikearjen fan ekstreem grutte URL's foar te kommen, bewarje wy se yn in aparte tabel.

Tink derom dat al ús tabellen al ferdield binne troch account_id. Op dizze manier is in situaasje dêr't ien bysûnder grut akkount foar problemen foar oaren soarget útsletten.

Op syk nei oanwizings

By neier ynsjoch sjogge wy dat der wat mis is mei in bepaald fersyk. It is it wurdich in tichterby te besjen op dizze line:

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

De earste gedachte wie dat miskien wol ILIKE op al dizze lange URL's (wy hawwe mear dan 1,4 miljoen unyk URL's sammele foar dit akkount) prestaasjes kinne lije.

Mar nee, dat is net it punt!

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

Time: 5231.765 ms

It sjabloansykfersyk sels nimt mar 5 sekonden. Sykje nei in patroan yn in miljoen unike URL's is dúdlik gjin probleem.

De folgjende fertochte op 'e list is ferskate JOIN. Miskien hat har oerbrûk de fertraging feroarsake? Gewoanwei JOIN's binne de meast foar de hân lizzende kandidaten foar prestaasjesproblemen, mar ik leaude net dat ús saak typysk wie.

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

En dit wie ek net ús gefal. JOIN's bliek frij fluch te wêzen.

De kring fan fertochten beheine

Ik wie ree om te begjinnen mei it feroarjen fan de query om alle mooglike prestaasjesferbetteringen te berikken. Myn team en ik ûntwikkele 2 haadideeën:

  • Brûk EXISTS foar subquery-URL: Wy woenen nochris kontrolearje oft der problemen wiene mei de subquery foar de URL's. Ien manier om dit te berikken is gewoan te brûken EXISTS. EXISTS kin gâns ferbetterje prestaasjes sûnt it einiget fuortendaliks sa gau as it fynt de ienige tekenrige dy't oerienkomt mei de betingst.

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

No ja. Subquery as ynpakt EXISTS, makket alles super fluch. De folgjende logyske fraach is wêrom it fersyk mei JOIN-ami en de subquery sels binne fluch yndividueel, mar binne ferskriklik stadich tegearre?

  • De subquery ferpleatse nei de CTE : As de query op himsels rap is, kinne wy ​​​​earst it rappe resultaat gewoan berekkenje en it dan leverje oan 'e haadfraach

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;

Mar it wie noch hiel stadich.

It finen fan de dieder

Al dy tiid flitse my ien lyts ding foar de eagen, dat ik hieltyd oan 'e kant skuorde. Mar om't der neat mear oer wie, besleat ik har ek te sjen. Ik haw it oer && operator. Doei EXISTS krekt ferbettere prestaasjes && wie de ienige oerbleaune mienskiplike faktor yn alle ferzjes fan 'e trage query.

Sjen nei dokumintaasje, wy sjogge dat && brûkt as jo moatte fine mienskiplike eleminten tusken twa arrays.

Yn it orizjinele fersyk is dit:

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

Wat betsjut dat wy in patroan sykje op ús URL's, fine dan de krusing mei alle URL's mei mienskiplike berjochten. Dit is in bytsje betiizjend omdat "urls" hjir net ferwiist nei de tabel mei alle URL's, mar nei de kolom "urls" yn 'e tabel recording_data.

Mei groeiende fertochten oangeande &&, Ik besocht te finen befêstiging foar harren yn de query plan oanmakke EXPLAIN ANALYZE (Ik hie al in plan bewarre, mar ik bin meastentiids nofliker te eksperimintearjen yn SQL dan besykje de dekking fan queryplanners te begripen).

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

Der wiene ferskate rigels fan filters allinnich út &&. Wat betsjutte dat dizze operaasje net allinnich djoer wie, mar ek ferskate kearen útfierd.

Ik testte dit troch de betingst te isolearjen

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

Dizze fraach wie traach. Fanwege de JOIN-s binne fluch en subqueries binne fluch, it ienige ding oerbleaun wie && operator.

Dit is gewoan in kaai operaasje. Wy moatte altyd de hiele ûnderlizzende tabel fan URL's sykje om in patroan te sykjen, en wy moatte altyd krusingen fine. Wy kinne net direkt sykje op URL-records, om't dit gewoan ID's binne dy't ferwize nei urls.

Op wei nei in oplossing

&& stadich om't beide sets enoarm binne. De operaasje sil relatyf fluch wêze as ik ferfange urls op { "http://google.com/", "http://wingify.com/" }.

Ik begon te sykjen nei in manier om ynstelde krusing te dwaan yn Postgres sûnder te brûken &&, mar sûnder folle súkses.

Op it lêst hawwe wy besletten om it probleem gewoan yn isolemint op te lossen: jou my alles urls rigels wêrfoar de URL oerienkomt mei it patroan. Sûnder ekstra betingsten sil it wêze - 

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

Ynstee JOIN syntaksis Ik haw gewoan in subquery brûkt en útwreide recording_data.urls array sadat jo de betingst direkt kinne tapasse yn WHERE.

It wichtichste ding hjir is dat && brûkt om te kontrolearjen oft in opjûne yngong in oerienkommende URL befettet. As jo ​​squint in bytsje, kinne jo sjen dat dizze operaasje beweecht troch de eleminten fan in array (of rigen fan in tabel) en stoppet as in betingst (oerienkomst) wurdt foldien. Doet dy neat oan tinken? Ja, EXISTS.

Sûnt oan recording_data.urls kin wurde ferwiisd fan bûten de subquery kontekst, as dit bart kinne wy ​​weromfalle op ús âlde freon EXISTS en wrap de subquery dermei.

Alles byinoar sette, krije wy de lêste optimalisearre query:

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

En de lêste leadtiid Time: 1898.717 ms Tiid om te fieren?!?

Net sa fluch! Earst moatte jo de krektens kontrolearje. Ik wie ekstreem fertocht oer EXISTS optimisaasje as it feroaret de logika om earder te beëinigjen. Wy moatte der wis fan wêze dat wy gjin net-foar de hân lizzende flater hawwe tafoege oan it fersyk.

In ienfâldige test wie om te rinnen count(*) op sawol stadige as flugge queries foar in grut oantal ferskillende datasets. Dan, foar in lytse subset fan 'e gegevens, haw ik manuell ferifiearre dat alle resultaten korrekt wiene.

Alle testen joegen konsekwint positive resultaten. Wy hawwe alles reparearre!

Lessen leard

D'r binne in protte lessen te learen út dit ferhaal:

  1. Queryplannen fertelle net it heule ferhaal, mar se kinne oanwizings leverje
  2. De haadfertochten binne net altyd de echte dieders
  3. Stadige queries kinne wurde ôfbrutsen om knelpunten te isolearjen
  4. Net alle optimalisaasjes binne reduktyf fan aard
  5. Gebrûk EXIST, wêr mooglik, kin liede ta dramatyske ferheging fan produktiviteit

konklúzje

Wy gongen fan in fraachtiid fan ~24 minuten nei 2 sekonden - nochal in signifikante prestaasjesferheging! Hoewol't dit artikel kaam út grut, barde alle eksperiminten wy diene op ien dei, en it waard rûsd dat se namen tusken 1,5 en 2 oeren foar optimizations en testen.

SQL is in prachtige taal as jo der net bang foar binne, mar besykje it te learen en te brûken. Troch in goed begryp te hawwen fan hoe't SQL-query's wurde útfierd, hoe't de databank queryplannen generearret, hoe't yndeksen wurkje, en gewoan de grutte fan 'e gegevens wêrmei jo te krijen hawwe, kinne jo heul suksesfol wêze by it optimalisearjen fan queries. It is lykwols like wichtich om troch te gean mei it besykjen fan ferskate oanpakken en it probleem stadichoan ôf te brekken, de knyppunten te finen.

It bêste diel oer it berikken fan resultaten lykas dizze is de merkbere, sichtbere snelheidferbettering - wêr't in rapport dat earder net iens soe laden no hast direkt laadt.

Spesjaal tank oan myn kameraden op befel fan Aditya Mishra, Aditya Gauru и Varun Malhotra foar brainstorming en Dinkar Pandir foar it finen fan in wichtige flater yn ús lêste fersyk foardat wy lang om let seine ôfskied fan it!

Boarne: www.habr.com

Add a comment