Ühe SQL-uuringu lugu

Eelmise aasta detsembris sain VWO tugimeeskonnalt huvitava veateate. Ühe analüüsiaruande laadimisaeg suure ettevõtte kliendi jaoks näis olevat ülemäära pikk. Kuna see kuulub minu vastutusalasse, keskendusin viivitamatult probleemi lahendamisele.

Eelalugu

Et oleks selge, millest jutt, räägin pisut VWO-st. See on platvorm, millega saab oma saitidel käivitada erinevaid sihitud kampaaniaid: teostada A/B katseid, jälgida külastajaid ja konversioone, teha müügivihje analüüse, kuvada soojuskaarte ja esitada külastuse salvestusi.

Kuid kõige olulisem platvormi aspekt on aruannete koostamine. Kõik eelnevalt mainitud funktsioonid on omavahel seotud. Suur hulk teavet oleks ettevõtte klientidele lihtsalt kasutuks, kui see ei oleks esitatud analüüsimiseks mõeldud platvormi kaudu.

Platvormi abil saab teha vabalt valitud päringu suure hulga andmete pealt. Siin on lihtne näide:

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

Pange tähele loogika operaatorite olemust. Need on saadaval klientide päringu liideses, et luua piisavalt keerulisi päringuid andmestike saamiseks.

Aeglane päring

Kliendil, millest me räägime, oli soov teha midagi, mis intuitiivselt peaks töötama kiiresti:

Kuva kõik seansi salvestused 
külastajate jaoks, kes on käinud mis tahes lehel 
URL-iga, mis sisaldab "/jobs".

Saidil oli tohutult liiklust, ja me säilitasime üle miljoni unikaalse URL-aadressi vaid selle jaoks. Nad soovisid leida üsnagi lihtsa URL-malli, mis seondub nende ärimudeliga.

Eeluurimine

Vaatame, 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äitmise aeg: 1431924.650 ms

Päring läbis 150 tuhat rida. Päringu planeerija näitas mõningaid huvitavaid detaile, kuid mingeid ilmseid kitsaskohti ei olnud.

Vaatame päringut põhjalikumalt. Nagu näha, tegeleb see JOIN kolme tabeliga:

1. sessions: seansi teabe kuvamiseks: brauser, kasutajaagent, riik jne.
2. recording_data: salvestatud URL-id, leheküljed, visiitide kestus.
3. urls: et vältida äärmiselt suurte URL-ide dubleerimist, säilitame need eraldi tabelis.

Pange tähele, et kõik meie tabelid on juba jagatud account_id. Seega elimineeritakse olukord, kus ühe ülemäära suure konto tõttu tekivad probleemid ülejäänutele.

Tõendite otsing

Süvenedes, märkame, et konkreetse päringu korral 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 äkki, kuna ILIKE kõikide nende pikkade URL-ide korral (meil on üle 1,4 miljoni unikaalse URL-aadressi, mis on selle konto jaoks kogutud) võib jõudlus langetada.

Aga ei — asi pole selles!

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

Aeg: 5231.765 ms

Otsingupäring mallide järgi võtab ainult 5 sekundit. Mallide põhjal otsimine üle miljoni unikaalse URL-i osas pole kindlasti probleem.

Järgmine kahtlane kahtlane — mitu JOIN. Võib-olla nende ülemäärane kasutamine viis aeglustumise juurde? Tavaliselt JOIN‘id on kõige ilmsemad kandidaadid jõudlusprobleemide osas, aga ma ei uskunud, et meie juhtum oleks tavaline.

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 rida)

Aeg: 147.851 ms

Ja see ei olnud ka meie juhtum. JOIN‘id osutusid olema üsna kiired.

Kitsendame kahtlusi

Olin valmis hakkama päringut muutma, et saavutada võimalikult suur jõudluse paranemine. Meie meeskond välja töötas kaks peamist ideed:

• Kasuta EXISTS-i URLi alampäringu suhtes: Soovisime veel kord kontrollida, kas URL-i alampäringuga on probleeme. Üks võimalus selleks on lihtsalt kasutada EXISTS. EXISTS suuteline tõhususe suurendamiseks, kuna see lõppeb kohe, kui leiab üksiku rea vastavalt tingimusele.

VALI
	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 =5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

Jah, alampäring, kui see on ümber. EXISTS, muudab kõik ülikiireks. Järgmine loogiline küsimus on, miks päringud koos JOIN-dega ja alampäringud töötavad eraldi kiiresti, aga koos on nad hirmsasti aeglased?

• Viime alampäringu CTE-sse : kui päring töötab kiiresti iseenesest, saame kõigepealt kiire tulemuse arvutada ja seejärel esitada selle põhiküsimusele.

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 =5 
    AND recording_data.num_of_pages > 0;

Aga isegi see oli endiselt väga aeglane.

Leidke süüdlane

Kogu selle aja silme ees oli üks detail, millest ma pidevalt eemale tõukasin. Aga kuna rohkem ei olnud jäänud, otsustasin sellele siiski pilgu heita. Ma räägin && operaator. Seni EXISTS lihtsalt parandas jõudlust, && oli ainus veel alles jäänud ühine tegur kõigis aeglaste päringute versioonides.

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

Originaalses päringus on see:

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

See tähendab, et me teeme mustriotsingut meie URL-ide üle ja seejärel leiame lõike kõigist URL-idest, millel on ühised salvestused. See on veidi segane, kuna 'urls' ei viita tabelile, mis sisaldab kõiki URL-e, vaid veerule 'urls' tabelis recording_data.

Kasvatades kahtlusi &&, üritasin leida neile tõestust päringu plaani kaudu, mille genereeris EXPLAIN ANALYZE (mul oli juba salvestatud plaan, kuid mulle meeldib tavaliselt SQL-is eksperimenteerida pigem, kui püüda mõista päringute planeerijate läbipaistmatust).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time = '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

Seal oli mitmeid filtreerimisridasid ainult &&. Mis tähendas, et see operatsioon polnud mitte ainult kulukas, vaid toimus mitu korda.

Kontrollisin seda, isoleerides 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 töötas aeglaselt. Kuna JOIN-d on kiire ja alampäringud kiired, jäi alles ainult && operaator.

Kuid see on võtmeoperatsioon. Me peame alati otsima pealt suures URL-de tabelis, et mustri põhjal otsida, ja me peame alati leidma lõike. Me ei saa otsida URL-i salvestusi otse, kuna need on lihtsalt ID-d, mis viitavad urls.

Lahenduse poole liikudes

&& aeglaseks, kuna mõlemad komplektid on tohutud. Operatsioon on suhteliselt kiire, kui asendan urls järgnevaga { "http://google.com/", "http://wingify.com/" }.

Alustasin viisi otsimist, kuidas teha Postgresis kogumite lõikeid ilma &&, kuid ei olnud erilisi edusamme.

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

Asetame JOIN Süntaksit kasutasin lihtsalt alampäringut ja lahti keritud recording_data.urls massiiv, et saaksime otse rakendada tingimust WHERE.

Kõige olulisem on see, et && kasutatakse selle kontrollimiseks, kas antud salvestusel on vastav URL-aadress. Veidi pilku keerates võib näha, et see operatsioon liigub massiivi (või tabeli ridade) elementide kaudu ja peatub, kui tingimust (vastavust) täidetakse. Kas see ei tuleta meelde midagi? Ah, EXISTS.

Kuna recording_data.urls võib viidata konteksti väljastpoolt alampäringut, kui see juhtub, saame naasta tagasi meie vanale sõbrale EXISTS ja ümbritseda selle alampäringuga.

Kokkuvõttes saame lõpliku optimeeritud päringu:

VALI 
    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 = 5 
    AND recording_data.num_of_pages > 0
    AND EXIST(
        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 lõplik täitmise aeg Aeg: 1898.717 ms Kas on pidu pidada?!?

Aga mitte nii kiiresti! Esiteks peame kontrollima korrektsust. Olin väga ettevaatlik EXISTS optimeerimise suhtes, kuna see muudab loogikat varasemaks lõpetamiseks. Me peame olema kindlad, et me ei ole lisanud pettumust valmistavat viga päringusse.

Lihtne kontroll seisnes selles, et tegin count(*) ja aeglaste ja kiirete päringute kohta suure hulga erinevate andme komplektide jaoks. Seejärel, väikese andmekogumi jaoks kontrollisin kõigi tulemuste õigsust käsitsi.

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

Tõukude Õppetunnid

Selle looga saab võtta palju õppetunde:

1. Päringu plaanid ei räägi kogu lugu, kuid võivad anda vihjeid
2. Peamised kahtlusalused ei ole alati tegelikud süüdlased
3. Aeglaseid päringuid saab jagada, et kitsaskohti isoleerida
4. Kaugel ei ole kõik optimeerimised oma olemuselt reduktiivsed
5. Kasutamine EXIST, kus see on võimalik, võib viia oluliseks jõudluse kasvuks

Kokkuvõte

Oleme liikunud päringu ajast ~24 minutist 2 sekundini — väga tõsine jõudluse kasv! Kuigi see artikkel on suur, toimusid kõik meie eksperimendid ühe päeva jooksul ja nende teostamiseks ja testimiseks kulus umbes 1,5 kuni 2 tundi.

SQL on imeline keel, kui mitte karta seda, vaid proovida mõista ja kasutada. Häid teadmisi, kuidas SQL päringud toimuvad, kuidas andmebaasid genereerivad päringuplaanid, kuidas indeksid töötavad ja lihtsalt andmekoguse kohta, millega tegelete, saate päringute optimeerimises väga hästi hakkama. Samuti on oluline jätkata erinevate lähenemiste testimist ja aeglaselt probleeme lahendada, leides kitsaskohad.

Parim osa selliste tulemuste saavutamisel on märgatav kiirusetõus — kui raport, mis varem ei launud, launeb nüüd peaaegu koheselt.

Eriline tänu minu kolleegidele meeskonnast Adithya Mishrale, Adithya Gaurile ja Varun Malhotrale ajurünnakute eest ja Dinkar Pandirile selle eest, et leidis meie lõpppäringus olulise vea, enne kui me sellega lõplikult hüvasti jätsime!

Allikas: habr.com