ProHoster > Blog > Administrasi > Carita hiji panalungtikan SQL

Carita hiji panalungtikan SQL

Désémber kamari kuring nampi laporan bug anu pikaresepeun ti tim dukungan VWO. Waktu loading pikeun salah sahiji laporan analytics pikeun klien perusahaan badag seemed prohibitive. Sareng kusabab ieu mangrupikeun tanggung jawab kuring, kuring langsung difokuskeun pikeun ngarengsekeun masalah.

prasajarah

Pikeun ngajelaskeun naon anu kuring nyarioskeun, kuring bakal nyarios sakedik ngeunaan VWO. Ieu mangrupikeun platform anu anjeun tiasa ngaluncurkeun sababaraha kampanye anu ditargetkeun dina situs wéb anjeun: ngalaksanakeun percobaan A / B, ngalacak sémah sareng konversi, nganalisis corong penjualan, ningalikeun peta panas sareng ngarékam kunjungan.

Tapi anu paling penting ngeunaan platformna nyaéta ngalaporkeun. Sakabéh fungsi di luhur téh interconnected. Sareng pikeun klien perusahaan, sajumlah ageung inpormasi bakal ngan saukur henteu aya gunana tanpa platform anu kuat anu nampilkeun dina bentuk analitik.

Ngagunakeun platform, anjeun tiasa ngadamel query acak dina set data badag. Ieu conto saderhana:

Tampilkeun sadaya klik dina halaman "abc.com" TI <tanggal d1> dugi ka <tanggal d2> pikeun jalma anu nganggo Chrome OR (aya di Éropa sareng nganggo iPhone)

Nengetan operator Boolean. Aranjeunna sadia pikeun klien dina panganteur query nyieun queries wenang kompléks pikeun ménta sampel.

Paménta lalaunan

Klién anu ditaroskeun nyobian ngalakukeun hiji hal anu sacara intuitif kedah dianggo gancang:

Tampilkeun sadaya rékaman sési pikeun pangguna anu ngadatangan halaman mana waé kalayan URL anu ngandung "/jobs"

Situs ieu ngagaduhan ton lalu lintas sareng kami nyimpen langkung ti sajuta URL unik kanggo éta. Jeung maranéhna hayang manggihan template URL cukup basajan nu patali jeung modél bisnis maranéhanana.

Panilitian awal

Hayu urang tingali naon anu lumangsung dina database. Di handap ieu query SQL slow aslina:

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 ;

Sareng ieu waktosna:

Waktos anu direncanakeun: 1.480 ms waktos palaksanaan: 1431924.650 ms

Paménta ngarayap 150 rébu jajar. Nu Ngarencana query némbongkeun sababaraha rinci metot, tapi euweuh bottlenecks atra.

Hayu urang nalungtik pamundut nu salajengna. Sakumaha anjeun tiasa tingali, anjeunna ngalakukeun JOIN tilu tabel:

  1. sesi: pikeun mintonkeun informasi sési: browser, agén pamaké, nagara, jeung saterusna.
  2. recording_data: dirékam URL, kaca, lilana nganjang
  3. url: Pikeun ngahindarkeun duplikat URL anu kacida gedéna, urang nyimpen éta dina tabel anu misah.

Ogé dicatet yén sakabéh tabel kami geus partitioned ku account_id. Ku kituna, kaayaan dimana hiji akun utamana badag ngabalukarkeun masalah pikeun batur teu kaasup.

Néangan pitunjuk

Kana pamariksaan anu langkung caket, urang ningali yén aya anu salah dina pamundut anu khusus. Éta patut nyandak katingal ngadeukeutan dina garis ieu:

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

Pikiran kahiji éta meureun sabab ILIKE dina sadaya URL panjang ieu (urang gaduh langkung ti 1,4 juta unik URL dikumpulkeun pikeun akun ieu) kinerja bisa sangsara.

Tapi henteu, éta sanés titik!

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

Time: 5231.765 ms

Paménta milarian template nyalira ngan ukur 5 detik. Milarian pola dina sajuta URL unik jelas henteu janten masalah.

Tersangka salajengna dina daptar nyaéta sababaraha JOIN. Sugan overuse maranéhanana geus ngabalukarkeun slowdown nu? Biasana JOIN'S mangrupikeun calon anu paling atra pikeun masalah kinerja, tapi kuring henteu percanten yén kasus urang biasa.

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

Sareng ieu ogé sanés kasus urang. JOIN's tétéla rada gancang.

Narrowing handap bunderan tersangka

Kuring siap ngamimitian ngarobah pamundut pikeun ngahontal sagala mungkin perbaikan kinerja. Abdi sareng tim ngembangkeun 2 ide utama:

  • Paké EXISTS pikeun URL subquery: Urang hayang mariksa deui lamun aya wae masalah jeung subquery pikeun URL. Hiji cara pikeun ngahontal ieu ngan saukur ngagunakeun EXISTS. EXISTS bisa greatly ngaronjatkeun kinerja saprak éta ends geuwat pas manggih hijina string nu cocog kondisi.

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

Muhun, enya. Subquery nalika dibungkus EXISTS, ngajadikeun sagalana super gancang. Patarosan logis salajengna naha pamundut kalawan JOIN-ami jeung subquery sorangan gancang individual, tapi pisan slow babarengan?

  • Mindahkeun subquery ka CTE : Upami pamundutna gancang nyalira, urang ngan saukur tiasa ngitung hasil gancang heula teras nyayogikeun kana pamundut utama

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;

Tapi éta kénéh pisan slow.

Manggihan palaku

Sadaya waktos ieu, hiji hal saeutik flashed saméméh panon kuring, nu kuring terus brushed kumisan. Tapi kumargi teu aya anu nyésakeun, kuring mutuskeun pikeun ningali anjeunna ogé. Kuring ngawangkong ngeunaan && operator. aduh EXISTS ngan ningkat kinerja && Ieu hiji-hijina faktor umum sésana dina sakabéh versi query slow.

Ningali dokuméntasi, urang tingali éta && dipaké nalika anjeun kudu manggihan elemen umum antara dua arrays.

Dina pamundut aslina ieu:

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

Anu hartosna urang milarian pola dina URL kami, teras milarian simpang sareng sadaya URL sareng tulisan umum. Ieu rada ngabingungkeun sabab "url" di dieu henteu ngarujuk kana tabel anu ngandung sadaya URL, tapi kana kolom "url" dina tabél. recording_data.

Kalawan tumuwuh kacurigaan ngeunaan &&, Kuring diusahakeun neangan konfirmasi pikeun aranjeunna dina rencana query dihasilkeun EXPLAIN ANALYZE (Kuring geus kungsi rencana disimpen, tapi Abdi biasana leuwih nyaman experimenting di SQL ti nyobian ngartos opacity of planners query).

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

Aya sababaraha garis saringan ngan ti &&. Nu hartina operasi ieu teu ngan mahal, tapi ogé dipigawé sababaraha kali.

Kuring nguji ieu ku ngasingkeun kaayaan

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

Patarosan ieu lambat. Kusabab éta JOIN-s anu gancang sarta subqueries anu gancang, hijina hal ditinggalkeun éta && operator.

Ieu ngan hiji operasi konci. Urang salawasna kudu neangan sakabéh tabel kaayaan URL pikeun milarian pola, sarta kami salawasna kudu manggihan intersections. Urang teu bisa neangan ku rékaman URL langsung, sabab ieu ngan ID ngarujuk kana urls.

Dina jalan ka solusi

&& slow sabab duanana susunan badag. Operasi bakal rélatif gancang mun kuring ngaganti urls dina { "http://google.com/", "http://wingify.com/" }.

Kuring mimiti néangan cara pikeun ngalakukeun set simpang di Postgres tanpa ngagunakeun &&, Tapi tanpa loba sukses.

Tungtungna, urang mutuskeun pikeun ngabéréskeun masalahna nyalira: pasihan abdi sadayana urls garis nu URL cocog pola. Tanpa syarat tambahan, éta bakal - 

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

Malahan JOIN sintaksis I ngan dipaké subquery sarta dimekarkeun recording_data.urls array supados anjeun tiasa langsung nerapkeun kaayaan di WHERE.

Hal anu paling penting di dieu nyaéta éta && dipaké pikeun mariksa naha entri dibikeun ngandung URL cocog. Lamun squint saeutik, Anjeun bisa nempo operasi ieu ngalir ngaliwatan unsur hiji Asép Sunandar Sunarya (atawa jajar méja) jeung eureun lamun kaayaan hiji (cocok) geus patepung. Teu ngingetan nanaon? Leres, EXISTS.

Ti saprak recording_data.urls bisa referenced ti luar konteks subquery, lamun ieu kajadian urang bisa ragrag deui ka sobat heubeul urang EXISTS sareng bungkus subquery sareng éta.

Ngahijikeun sadayana, urang nampi pamundut anu dioptimalkeun akhir:

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

Jeung waktu kalungguhan ahir Time: 1898.717 ms Waktos ngagungkeun?!?

Teu jadi gancang! Kahiji maneh kudu pariksa correctness. Kuring curiga pisan EXISTS optimasi sakumaha eta ngarobah logika pikeun ngalengkepan saméméhna. Urang kedah pastikeun yén kami henteu nambihan kasalahan anu teu jelas kana pamundut éta.

Hiji tés basajan éta ngajalankeun count(*) on duanana queries slow sarta gancang pikeun angka nu gede ngarupakeun susunan data béda. Lajeng, pikeun sawaréh leutik data, kuring sacara manual diverifikasi yén sakabéh hasil bener.

Sadaya tés masihan hasil anu positip sacara konsisten. Urang ngalereskeun sagalana!

Pangajaran Diajar

Aya seueur pelajaran anu tiasa dicandak tina carita ieu:

  1. Rencana kueri henteu nyaritakeun sadayana, tapi tiasa masihan petunjuk
  2. The tersangka utama henteu salawasna culprits nyata
  3. Patarosan slow bisa direcah pikeun ngasingkeun bottlenecks
  4. Henteu sadayana optimasi sifatna réduksi
  5. pamakean EXIST, mana mungkin, bisa ngakibatkeun kanaékan dramatis dina produktivitas

kacindekan

Urang indit ti waktu query tina ~ 24 menit ka 2 detik - rada ngaronjat kinerja signifikan! Sanajan artikel ieu kaluar badag, sagala percobaan kami lumangsung dina hiji poé, sarta diperkirakeun yén maranéhna butuh antara 1,5 jeung 2 jam pikeun optimizations sarta nguji.

SQL mangrupikeun basa anu saé upami anjeun henteu sieun, tapi coba diajar sareng dianggo. Ku gaduh pamahaman anu hadé ngeunaan kumaha queries SQL dieksekusi, kumaha database ngahasilkeun rencana query, kumaha indéks dianggo, sareng ngan ukur ukuran data anu anjeun urus, anjeun tiasa suksés pisan dina ngaoptimalkeun patarosan. Sarua pentingna, kumaha ogé, pikeun teras-terasan nyobian pendekatan anu béda-béda sareng lalaunan ngarecah masalah, mendakan bottlenecks.

Bagian anu pangsaéna pikeun ngahontal hasil ieu nyaéta paningkatan kacepetan anu katingali - dimana laporan anu sateuacana henteu tiasa dimuat ayeuna dimuat ampir langsung.

hatur nuhun husus ka babaturan kuring ku paréntah Aditya Mishra, Aditya Gauru и Varun Malhotra pikeun brainstorming jeung Dinkar Pandir pikeun manggihan kasalahan penting dina pamundut final urang saméméh urang tungtungna ceuk wilujeung ka dinya!

sumber: www.habr.com

Tambahkeun komentar