🥇Ühe SQL uurimise ajalugu

Eelmise aasta detsembris sain huvitava veaaruande VWO tugimeeskonnalt. Ühe analüüsiraporti laadimisaeg suurtele ettevõtte klientidele tundus olema ülemäära pikk. Kuna see on minu vastutusala, keskendusin kohe probleemi lahendamisele.

Eellugu

Et selgem oleks, millest jutt käib, räägin veidi VWO-st. See on platvorm, mille abil saab käivitada erinevaid sihitud kampaaniaid oma veebilehtedel: läbi viia A/B katseid, jälgida külastajaid ja konversioone, analüüsida müügivoolu, kuvada soojuskaarte ja esitada külastuste salvestusi.

Aga kõige tähtsam platvormi juures on aruannete koostamine. Kõik eelnevalt nimetatud funktsioonid on omavahel seotud. Ja ettevõtte klientide jaoks oleks tohutu informatsioonihulk lihtsalt kasutuks, kui see ei oleks korraliku analüüsiplatvormi esitlemine.

Platvormi kasutades saab teha vaba päringu suurel andmekogumil. Siin on lihtne näide:

Kuva kõik klõpsud lehel "abc.com"
KUNI <kuupäev d1> kuni <kuupäev d2>
inimeste jaoks, kes
kasutasid Chrome'i VÕI
(olid Euroopas JA kasutasid iPhone'i)

Pange tähele loogilisi operaatorite. Need on klientide jaoks päringute liideses, et luua keerulisemaid päringuid valikute saamiseks.

Aeglane päring

Client, mille kohta jutt käib, üritas teha midagi, mis peaks intuitiivselt toimuma kiiresti:

Kuva kõik sessioonide salvestused
kasutajate jaoks, kes külastasid mis tahes lehte,
kus on url, mis sisaldab "\/jobs"

Sellel veebisaidil oli tohutu liiklus ja me hoidsime üle miljoni ainulaadse URL-aadressi ainult selle jaoks. Ja nad soovisid leida üsna lihtsa url-malli, mis oleks seotud nende äri mudeliga.

Eeluurimine

Vaadakem, mis toimub andmebaasis. Allpool on algne aeglane SQL-päring:

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 ;

Siin on ajad:

Eeldatav aeg: 1.480 ms
Täideviimise aeg: 1431924.650 ms

Päring hõlmas 150 000 rida. Päringu planeerija näitas paar huvitavat detaili, kuid ei mingit ilmselget kitsaskohta.

Uurime päringut edasi. Nagu näha, see teeb JOIN kolm tabelit:

sessions: seansi teabe näitamiseks: brauser, kasutajaagent, riik jne.
recording_data: salvestatud URLid, lehed, külastuste kestus
urls: et vältida äärmiselt suurte URLide dubleerimist, hoiame neid eraldi tabelis.

Pange tähele, et kõik meie tabelid on juba jagatud account_id. Nii on välistatud olukord, kus ühe eriti suure konto tõttu tekivad probleemid ülejäänutel.

Tõendite otsing

Lähemal vaatlemisel näeme, et konkreetse päringuga on midagi valesti. Tasub tähelepanu pöörata sellele reale:

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

Esimene mõte oli, et võib-olla on selle tõttu, et ILIKE kõikide nende pikkade URLide puhul (meil on rohkem kui 1,4 miljonit unikaalset URLi, mis on selle konto jaoks kogutud) jõudlus võib olla problemaatiline.

Aga ei — asi pole selles!

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

Aeg: 5231.765 ms

Isegi mustriotsingu päring võtab vaid 5 sekundit. Mustriotsing miljonis unikaalses URLis pole kindlasti probleem.

Järgmine kahtlane isik nimekirjas — mõned JOIN. Võib-olla nende ülemäärane kasutamine viis aeglustumiseni? Tavalised JOINon kõige ilmsesemaid kahtlusaluseid jõudlusprobleemide osas, kuid ma ei uskunud, et meie juhtum oleks tüüpiline.

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 = 5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 rida)

Aeg: 147.851 ms

Ja see polnud samuti meie juhtum. JOINon osutunud üsna kiireks.

Kitsendame kahtlusaluste ringi

Olin valmis alustama päringu muutmist, et saavutada võimalikult palju jõudluse parandusi. Meie meeskond töötas välja kaks peamist ideed:

Kasutada EXISTS URL alampäringut: Soovisime veel kord kontrollida, kas URLide alampäringuga on probleeme. Üks viis selle saavutamiseks on lihtsalt kasutada EXISTS. EXISTS võib tõhusalt parandada jõudlust, kuna see lõppeb kohe, kui see leiab ainulaadse rea tingimuse alusel.

VALI
	count(*) 
KUST 
    acc_{account_id}.urls kui recordings_urls,
    acc_{account_id}.recording_data kui recording_data,
    acc_{account_id}.sessions kui sessions
KUS
    recording_data.usp_id = sessions.usp_id
    JA  (  1 = 1  )
    JA sessions.referrer_id = recordings_urls.id
    JA  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    JA r_time > to_timestamp(1547585600)
    JA r_time =5
    JA recording_data.num_of_pages > 0 ;
 count
 32519
(1 rida)
Aeg: 1636.637 ms

Jah, alamküsitlus, kui see on pakendatud EXISTS, muudab kõik super kiireks. Järgmine loogiline küsimus on, miks päringud koos JOIN-idega ja alamküsitlus on eraldi kiiresti, kuid koos aeglustavad need kohutavalt?

Viime alamküsitluse CTE-sse : kui päring on iseenesest kiire, saame kõigepealt lihtsalt kiiresti tulemuse arvutada ja seejärel anda selle põhipäringule

WITH matching_urls AS (
    vali id::text from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%'
)

VALI 
    count(*) FROM acc_{account_id}.urls kui recordings_urls, 
    acc_{account_id}.recording_data kui recording_data, 
    acc_{account_id}.sessions kui sessions,
    matching_urls
KUS 
    recording_data.usp_id = sessions.usp_id 
    JA  (  1 = 1  )  
    JA sessions.referrer_id = recordings_urls.id
    JA (urls && array(VALI id from matching_urls)::text[])
    JA r_time > to_timestamp(1542585600) 
    JA r_time =5 
    JA recording_data.num_of_pages > 0;

Kuid isegi see oli endiselt väga aeglane.

Leiame süüdlase

Kogu selle aja silme ees vilkus üks pisiasi, millest ma pidevalt kõrvale tõukasin. Kuid kuna ei jäänud enam midagi muud, otsustasin sellele pilku heita. Ma räägin && operaatorist. Seni EXISTS lihtsalt parandas jõudlust, && oli ainus allesjäänud ühine tegur kõigis aeglase päringu versioonides.

Vaadates dokumentatsioon, näeme, et && kasutatakse, kui on vaja leida ühiseid elemente kahe massiivi vahel.

Originaalses päringus see:

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

Mis tähendab, et me teeme mustri põhjal otsingu meie URLe, seejärel leiame ristmiku kõikide URL-idega, millel on ühised kirjed. See on natuke segane, kuna 'urls' siin ei viita tabelile, kus on kõik URL-id, vaid veerg 'urls' tabelis recording_data.

Seoses suureneva kahtlusega &&, üritasin leida kinnitust nendele küsimustele genereeritud päringu plaanis. EXPLAIN ANALYZE (mul on juba olnud salvestatud plaan, kuid tavaliselt on mul mugavam eksperimentida SQL-is kui proovida aru saada päringute optimeerijate läbipaistmatust).

Filter: ((urls && ($0)::text[]) JA (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) JA (r_time = '5'::double precision) JA (num_of_pages > 0))
                           Ridu eemaldatud filtri kaudu: 52710

Seal oli mitu filtririda ainult &&. Mis tähendas, et see operatsioon oli mitte ainult kulukas, vaid jõudis ka mitu korda toime.

Kontrollisin seda, isolerides tingimuse

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

See päring käidi aeglaselt. Kuna JOIN-id on kiired ja alam-päringud on kiired, jäi üle ainult && operaator.

Aga see on ainult see peamine operatsioon. Me peame alati otsima kogu põhilaudade URL-addresside kaudu, et otsida mallide järgi ja me peame alati leidma ristumisi. Me ei saa otsida URL-ide salvestiste kaudu otse, sest need on lihtsalt ID, mis viitavad urls.

Teel lahendusele

&& aeglane, kuna mõlemad kogumid on suured. Operatsioon oleks suhteliselt kiire, kui ma asendan urls . Tundub, et { "http://google.com/", "http://wingify.com/" }.

Hakkasin otsima viisi, kuidas teha Postgresis kogumite ristumist ilma &&, kuid ilma eriliste edusammudeta.

Lõpuks otsustasime lihtsalt lahendada probleemi isoleeritult: anna mulle kõik urls read, mille jaoks URL vastab mallile. Ilma täiendavate tingimusteta oleks see —

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 JA
	urls.url ILIKE '%jobs%'

Asenda JOIN süntaksis kasutasin lihtsalt alam-päringut ja avasin recording_data.urls massiiv, et saaks otse rakendada tingimust KUS.

Siin on kõige olulisem see, et && kasutatakse, et kontrollida, kas antud salvestis sisaldab vastavat URL- адреса. Veidi tõmmates võib näha, kuidas selles operatsioonis liikuda massiivi elementide (või tabeli ridade) vahel ja peatuda, kui toimub tingimus (vastavus). Kas midagi ei meenuta? Ah, EXISTS.

Kuna recording_data.urls võib viidata alt konteksti alam-päringu kontekstis, kui see juhtub, saame tagasi pöörduda meie vanade sõprade poole EXISTS ja ümbritseda seda alam-päringut.

Kokku sidudes saame me lõpuks optimeeritud päringu:

VALI 
    count(*) 
KUSTA 
    acc_{account_id}.urls kui recordings_urls, 
    acc_{account_id}.recording_data kui recording_data, 
    acc_{account_id}.sessions kui sessions 
KUS 
    recording_data.usp_id = sessions.usp_id 
    JA  (  1 = 1  )  
    JA sessions.referrer_id = recordings_urls.id 
    JA r_time > to_timestamp(1542585600) 
    JA r_time =5 
    JA recording_data.num_of_pages > 0
    JA EXITS(
        VALI urls.url
        KUSTA 
            acc_{account_id}.urls kui urls,
            (VALI unnest(urls) AS rec_url_id KUSTA acc_{account_id}.recording_data) 
            KUI unrolled_urls
        KUS
            urls.id = unrolled_urls.rec_url_id JA
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

Ja lõplik täitmise aeg Aeg: 1898.717 ms Kas on aeg tähistamiseks?!?

Ära nii kiiresti! Esiteks peame kontrollima õigsust. Ma olin äärmiselt kahtlev EXISTS optimeerimise osas, kuna see muudab loogikat varasemaks lõpetamiseks. Me peame olema kindlad, et me ei ole lisanud nähtamatut viga päringusse.

Lihtne kontroll seisnes count(*) ja aeglastes ja kiiretes päringutes erinevate andmekogumite jaoks. Seejärel kontrollisin väikese andmekogumi puhul kõiki tulemusi käsitsi.

Kõik kontrollid andsid stabiilselt positiivseid tulemusi. Me oleme kõik korda teinud!

Tõmmatud Õppetunnid

Sellest loost saab palju õppetunde:

Küsi plaanid ei räägi kogu lugu, kuid võivad anda vihjeid
Peamised kahtlusalused ei ole alati tõelised süüdlased
Aeglaseid päringuid saab jagada kitsaste kohtade isoleerimiseks
Kõik optimeerimised ei ole loomulikult reduktiivsed
Kasutamine EXIST, kus see on võimalik, võib viia drastilise jõudluse kasvuni

Kokkuvõte

Me liigutasime päringu aega ~24 minutist 2 sekundini - üsna märkimisväärne jõudluse kasv! Kuigi see artikkel on suur, toimusid kõik eksperimendid ühe päeva jooksul ja hinnanguliselt kulus optimeerimise ja testimise jaoks 1,5–2 tundi.

SQL on imeline keel, kui mitte karta seda, vaid püüda aru saada ja kasutada. Hea arusaam sellest, kuidas SQL-päringuid töötatakse, kuidas andmebaas genereerib plaanid, kuidas indeksid töötavad ja lihtsalt andmete suurusest, millega on tegemist, aitab teil päringute optimeerimisel väga hästi edasi jõuda. Samuti on oluline jätkata erinevate lähenemiste proovimist ja aeglaselt probleemi lahendamist kitsaste kohtade leidmiseks.

Parim osa sarnaste tulemuste saavutamisel on silmapaistev nähtav kiirusetõus — kui aruanne, mis varem isegi ei laadinud, laadib nüüd peaaegu koheselt.

Eriline tänu minu kolleegidele tiimist Aditya Mishra, Aditya Gaur ja Varun Malhotra ajurünnaku ja Dinkar Pandirile selle eest, et leidis meie lõpp-päringus olulise vea, enne kui me selle lõplikult lõpetasime!

Allikas: habr.com

Ühe SQL uurimise lugu