🥇 Ühe SQL-i uurimise lugu

Eelmise aasta detsembris sain VWO tugimeeskonnalt huvitava veateate. Ühe suure ärikliendi analüütilise aruande laadimisaeg tundus liiga pikk. Ja kuna see on minu vastutusala, keskendusin kohe probleemi lahendamisele.

eelajalugu

Et oleks selge, millest ma räägin, räägin veidi VWOst. See on platvorm, mille abil saate oma veebisaitidel käivitada erinevaid sihitud kampaaniaid: viia läbi A/B katseid, jälgida külastajaid ja konversioone, analüüsida müügilehtrit, kuvada soojuskaarte ja mängida külastuste salvestisi.

Kuid platvormi juures on kõige olulisem aruandlus. Kõik ülaltoodud funktsioonid on omavahel seotud. Ja äriklientide jaoks oleks tohutul hulgal teavet lihtsalt kasutu ilma võimsa platvormita, mis seda analüütilisel kujul esitaks.

Platvormi kasutades saate teha suure andmekogumi kohta juhusliku päringu. Siin on lihtne näide:

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

Pöörake tähelepanu Boole'i operaatoritele. Need on klientidele päringuliideses kättesaadavad, et teha näidiste saamiseks meelevaldselt keerulisi päringuid.

Aeglane taotlus

Kõnealune klient püüdis teha midagi, mis intuitiivselt peaks kiiresti toimima:

Kuva kõik seansikirjed kasutajatele, kes külastasid mis tahes lehte, mille URL sisaldab "/jobs"

Sellel saidil oli palju liiklust ja me talletasime ainult selle jaoks üle miljoni unikaalse URL-i. Ja nad tahtsid leida üsna lihtsa URL-i malli, mis oleks seotud nende ärimudeliga.

Eeluurimine

Vaatame, mis andmebaasis toimub. 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 ;

Ja siin on ajad:

Planeeritud aeg: 1.480 ms Täitmise aeg: 1431924.650 ms

Päring läbis 150 tuhat rida. Päringuplaneerija näitas paar huvitavat detaili, kuid ei mingeid ilmseid kitsaskohti.

Uurime taotlust edasi. Nagu näete, teeb ta seda JOIN kolm tabelit:

istungid: seansi teabe kuvamiseks: brauser, kasutajaagent, riik ja nii edasi.
salvestus_andmed: salvestatud URL-id, lehed, külastuste kestus
internetiaadressid: väga suurte URL-ide dubleerimise vältimiseks salvestame need eraldi tabelisse.

Pange tähele ka seda, et kõik meie tabelid on juba jaotatud account_id. Nii on välistatud olukord, kus üks eriti suur konto tekitab teistele probleeme.

Otsib vihjeid

Lähemal uurimisel näeme, et konkreetse taotlusega on midagi valesti. Tasub seda rida lähemalt uurida:

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 sellepärast ILIKE kõigil neil pikkadel URL-idel (meil on üle 1,4 miljoni unikaalne Selle konto jaoks kogutud URL-id) toimivus võib kannatada.

Aga ei, see pole asja mõte!

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

Time: 5231.765 ms

Malli otsingupäring ise võtab aega vaid 5 sekundit. Mustri otsimine miljonist ainulaadsest URL-ist pole ilmselgelt probleem.

Järgmine kahtlusalune nimekirjas on mitu JOIN. Võib-olla on aeglustumise põhjustanud nende liigne kasutamine? Tavaliselt JOIN's on jõudlusprobleemide kõige ilmsemad kandidaadid, kuid ma ei uskunud, et meie juhtum on 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 < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

Ja see ei olnud ka meie juhtum. JOINosutus üsna kiireks.

Kahtlustatavate ringi kitsendamine

Olin valmis päringut muutma, et jõudlust parandada. Minu meeskond ja mina töötasime välja 2 peamist ideed:

Kasutage alampäringu URL-i jaoks EXISTS: tahtsime uuesti kontrollida, kas URL-ide alampäringuga ei esinenud probleeme. Üks viis selle saavutamiseks on lihtsalt kasutamine EXISTS. EXISTS võib parandab oluliselt jõudlust, kuna see lõpeb kohe, kui leiab ainsa tingimusele vastava stringi.

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

Nojah. Allpäring, kui see on sisse pakitud EXISTS, teeb kõik ülikiireks. Järgmine loogiline küsimus on, miks taotlus koos JOIN-ami ja alampäring ise on eraldi kiired, aga koos kohutavalt aeglased?

Alampäringu teisaldamine CTE-sse : kui päring on iseenesest kiire, saame esmalt lihtsalt kiire tulemuse välja arvutada ja seejärel põhipäringule esitada

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;

Aga see oli ikka väga aeglane.

Süüdlase leidmine

Kogu selle aja välgatas mu silme ees üks pisiasi, mida ma pidevalt kõrvale ajasin. Aga kuna muud üle ei jäänud, otsustasin ka teda vaadata. ma räägin sellest && operaator. Hüvasti EXISTS lihtsalt paranenud jõudlus && oli ainus järelejäänud ühine tegur kõigis aeglase päringu versioonides.

Vaatan dokumentatsioon, me näeme seda && kasutatakse siis, kui on vaja leida kahe massiivi vahel ühiseid elemente.

Algses taotluses on see järgmine:

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

Mis tähendab, et teeme oma URL-idelt mustriotsingu, seejärel leiame ristumiskoha kõigi levinud postitustega URL-idega. See on veidi segane, sest "urls" ei viita siin mitte kõiki URL-e sisaldavale tabelile, vaid tabeli veerule "urls" recording_data.

Kasvavate kahtlustega seoses &&, püüdsin genereeritud päringuplaanist neile kinnitust leida EXPLAIN ANALYZE (Mul oli plaan juba salvestatud, kuid tavaliselt on mul mugavam SQL-is katsetada kui päringuplaneerijate läbipaistmatusest aru saada).

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

Seal oli mitu rida filtreid ainult &&. Mis tähendas, et see operatsioon ei olnud mitte ainult kallis, vaid seda tehti ka mitu korda.

Testisin seda seisundi isoleerimisega

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 oli aeglane. Kuna JOIN-s on kiire ja alampäringud on kiired, jäi ainult && operaator.

See on vaid võtmeoperatsioon. Mustri otsimiseks peame alati otsima kogu URL-ide aluseks olevast tabelist ja peame alati leidma ristmikud. Me ei saa otse URL-i kirjete järgi otsida, kuna need on vaid ID-d, millele viitavad urls.

Teel lahenduse poole

&& aeglane, sest mõlemad komplektid on suured. Kui ma välja vahetan, on operatsioon suhteliselt kiire urls edasi { "http://google.com/", "http://wingify.com/" }.

Hakkasin otsima viisi, kuidas määrata Postgresis ristmikku ilma kasutamata &&, kuid ilma suurema eduta.

Lõpuks otsustasime probleemi lihtsalt eraldiseisvalt lahendada: anna mulle kõik urls read, mille URL vastab mustrile. Ilma lisatingimusteta on 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 AND
	urls.url  ILIKE  '%jobs%'

Selle asemel JOIN süntaks Kasutasin just alampäringut ja laiendasin recording_data.urls massiivi, et saaksite tingimust otse rakendada WHERE.

Kõige tähtsam on siin see && kasutatakse kontrollimaks, kas antud kirje sisaldab sobivat URL-i. Kui vaatate veidi silmi, näete, et see toiming liigub läbi massiivi elementide (või tabeli ridade) ja peatub, kui tingimus (sobivus) on täidetud. Ei tuleta sulle midagi meelde? jah, EXISTS.

Sest edasi recording_data.urls võib viidata väljastpoolt alampäringu konteksti, kui see juhtub, võime tagasi pöörduda oma vana sõbra poole EXISTS ja pakkige alampäring sellega kokku.

Kui kõik kokku panna, saame lõpliku optimeeritud päringu:

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

Ja viimane aeg Time: 1898.717 ms Aeg tähistada?!?

Mitte nii kiiresti! Kõigepealt peate kontrollima õigsust. Ma olin äärmiselt kahtlustav EXISTS optimeerimine, kuna see muudab loogikat varem lõpuleviimiseks. Peame olema kindlad, et me pole taotlusele lisanud ilmselget viga.

Lihtne test oli joosta count(*) nii aeglaste kui ka kiirete päringute puhul suure hulga erinevate andmekogumite jaoks. Seejärel kontrollisin väikese andmete alamhulga puhul käsitsi, et kõik tulemused olid õiged.

Kõik testid andsid püsivalt positiivseid tulemusi. Panime kõik korda!

Õppetunnid

Sellest loost on palju õppetunde:

Päringuplaanid ei räägi kogu lugu, kuid võivad anda vihjeid
Peamised kahtlusalused ei ole alati tegelikud süüdlased
Aeglased päringud saab kitsaskohtade eraldamiseks jaotada
Kõik optimeerimised ei ole oma olemuselt redutseerivad
Kasutama EXIST, kui võimalik, võib see tuua kaasa tootlikkuse järsu tõusu

Väljund

Päringuajalt läks ~24 minutilt 2 sekundile – päris märkimisväärne jõudluse kasv! Kuigi see artikkel tuli välja suur, toimusid kõik meie tehtud katsed ühe päevaga ning nende optimeerimiseks ja testimiseks kulus hinnanguliselt 1,5–2 tundi.

SQL on suurepärane keel, kui te seda ei karda, vaid proovite seda õppida ja kasutada. Kui mõistate hästi, kuidas SQL-päringuid täidetakse, kuidas andmebaas päringuplaane genereerib, indeksite toimimist ja lihtsalt käsitletavate andmete suurust, saate päringuid optimeerida väga edukalt. Sama oluline on aga jätkata erinevate lähenemisviiside proovimist ja probleemi aeglaselt murda, leides kitsaskohad.

Parim osa selliste tulemuste saavutamisel on märgatav ja nähtav kiiruse paranemine – aruanne, mida varem isegi ei laaditud, laaditakse nüüd peaaegu koheselt.

Eriline tänu mu seltsimehed Aditya Mishra käsul, Aditya Gauru и Varun Malhotra ajurünnakuks ja Dinkar Pandir selle eest, et leidsime oma viimases taotluses olulise vea, enne kui sellega lõpuks hüvasti jätsime!

Allikas: www.habr.com

Ühe SQL-i uurimise lugu