🥇Bir SQL araşdırmasının hekayəsi

Keçən dekabr ayında VWO dəstək qrupundan maraqlı bir səhv hesabatı aldım. Böyük bir korporativ müştəri üçün analitik hesabatlardan birinin yüklənmə vaxtı qadağanedici görünürdü. Və bu mənim məsuliyyətim olduğu üçün dərhal problemin həllinə fokuslandım.

Prehistorya

Nə danışdığımı aydınlaşdırmaq üçün sizə VWO haqqında bir az danışacağam. Bu, vebsaytlarınızda müxtəlif məqsədyönlü kampaniyalar başlada biləcəyiniz platformadır: A/B eksperimentləri keçirin, ziyarətçiləri və dönüşümləri izləyin, satış hunisini təhlil edin, istilik xəritələrini göstərin və ziyarət qeydlərini oynayın.

Ancaq platforma ilə bağlı ən vacib şey hesabat verməkdir. Yuxarıda göstərilən bütün funksiyalar bir-birinə bağlıdır. Korporativ müştərilər üçün isə böyük miqdarda məlumat onu analitik formada təqdim edən güclü platforma olmadan sadəcə faydasız olardı.

Platformadan istifadə edərək, böyük bir məlumat dəstində təsadüfi sorğu edə bilərsiniz. Budur sadə bir nümunə:

"abc.com" səhifəsində Chrome və ya (Avropada yerləşən və iPhone istifadə edən) istifadə edənlər üçün <date d1> DƏDƏN <date d2>-ə qədər bütün klikləri göstərin

Boolean operatorlarına diqqət yetirin. Onlar nümunələr əldə etmək üçün özbaşına mürəkkəb sorğular etmək üçün sorğu interfeysində müştərilər üçün əlçatandır.

Yavaş sorğu

Sözügedən müştəri intuitiv olaraq tez işləməli olan bir şey etməyə çalışırdı:

URL-də "/jobs" olan hər hansı bir səhifəni ziyarət edən istifadəçilər üçün bütün sessiya qeydlərini göstərin

Bu saytın bir ton trafiki var idi və biz bunun üçün bir milyondan çox unikal URL saxlayırdıq. Və biznes modelləri ilə əlaqəli olduqca sadə bir URL şablonu tapmaq istədilər.

İlkin istintaq

Gəlin verilənlər bazasında baş verənlərə nəzər salaq. Aşağıda orijinal yavaş SQL sorğusu verilmişdir:

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 ;

Və burada vaxtlar:

Planlaşdırılan vaxt: 1.480 ms İcra müddəti: 1431924.650 ms

Sorğu 150 min sıra tarandı. Sorğunun planlayıcısı bir neçə maraqlı təfərrüat göstərdi, lakin açıq-aydın maneələr yox idi.

Müraciəti daha ətraflı öyrənək. Gördüyünüz kimi, edir JOIN üç masa:

sessiyaları: sessiya məlumatlarını göstərmək üçün: brauzer, istifadəçi agenti, ölkə və s.
qeyd_data: qeydə alınmış URL-lər, səhifələr, ziyarətlərin müddəti
urls: Çox böyük URL-lərin təkrarlanmasının qarşısını almaq üçün biz onları ayrıca cədvəldə saxlayırıq.

Onu da qeyd edək ki, bütün cədvəllərimiz artıq bölmələrə bölünüb account_id. Beləliklə, bir böyük hesabın başqaları üçün problem yaratması halları istisna edilir.

İpuçları axtarır

Diqqətlə yoxladıqda, müəyyən bir sorğuda nəyinsə səhv olduğunu görürük. Bu xəttə daha yaxından nəzər salmağa dəyər:

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

İlk fikir, bəlkə də ona görə idi ILIKE bütün bu uzun URL-lərdə (bizdə 1,4 milyondan çox bənzərsiz Bu hesab üçün toplanmış URL-lər) performansı azala bilər.

Amma yox, məsələ bu deyil!

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

Time: 5231.765 ms

Şablon axtarış sorğusunun özü cəmi 5 saniyə çəkir. Bir milyon unikal URL-də nümunə axtarmaq aydın şəkildə problem deyil.

Siyahıdakı növbəti şübhəli bir neçə nəfərdir JOIN. Bəlkə onların həddindən artıq istifadəsi yavaşlamağa səbəb oldu? Adətən JOIN's performans problemləri üçün ən bariz namizədlərdir, lakin bizim vəziyyətimizin tipik olduğuna inanmadım.

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

Və bu da bizdə deyildi. JOINnin olduqca sürətli olduğu ortaya çıxdı.

Şübhəlilərin dairəsini daraltmaq

İstənilən mümkün performans təkmilləşdirmələrinə nail olmaq üçün sorğunu dəyişməyə hazır idim. Komandam və mən 2 əsas ideya hazırladıq:

Alt sorğu URL üçün EXISTS istifadə edin: URL-lər üçün alt sorğu ilə bağlı hər hansı problem olub-olmadığını yenidən yoxlamaq istədik. Buna nail olmağın bir yolu sadəcə istifadə etməkdir EXISTS. EXISTS can performansını xeyli yaxşılaşdırır, çünki o, şərtə uyğun gələn yeganə sətri tapan kimi dərhal bitir.

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

Yaxşı, bəli. Büküldükdə alt sorğu EXISTS, hər şeyi super sürətli edir. Növbəti məntiqi sual niyə sorğu ilə bağlıdır JOIN-ami və alt sorğunun özü fərdi olaraq sürətlidir, lakin birlikdə olduqca yavaşdır?

Alt sorğunun CTE-yə köçürülməsi : Əgər sorğu öz-özünə sürətlidirsə, biz sadəcə olaraq sürətli nəticəni hesablaya və sonra onu əsas sorğuya verə bilərik.

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;

Amma yenə də çox yavaş idi.

Günahkarın tapılması

Bütün bu müddət ərzində gözümün önündə daim bir kənara atdığım kiçik bir şey keçdi. Amma başqa heç nə qalmadığından mən də ona baxmaq qərarına gəldim. haqqında danışıram && operator. sağol EXISTS sadəcə təkmilləşdirilmiş performans && yavaş sorğunun bütün versiyalarında qalan yeganə ümumi amil idi.

Baxmaq sənədlər, bunu görürük && iki massiv arasında ümumi elementləri tapmaq lazım olduqda istifadə olunur.

Orijinal sorğuda bu belədir:

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

Bu o deməkdir ki, biz URL-lərimizdə nümunə axtarışı edirik, sonra ümumi yazıları olan bütün URL-lərlə kəsişməni tapırıq. Bu bir az qarışıqdır, çünki burada "url" bütün URL-ləri ehtiva edən cədvələ deyil, cədvəldəki "url" sütununa istinad edir. recording_data.

ilə bağlı artan şübhələrlə &&, yaradılan sorğu planında onlar üçün təsdiq tapmağa çalışdım EXPLAIN ANALYZE (Artıq yadda saxladığım planım var idi, lakin sorğu planlayıcılarının qeyri-şəffaflığını anlamağa çalışmaqdansa, mən adətən SQL-də təcrübə aparmaqdan daha rahatam).

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

Yalnız bir neçə filtr xətti var idi &&. Hansı ki, bu əməliyyat nəinki baha başa gəlir, hətta bir neçə dəfə həyata keçirilirdi.

Mən bunu vəziyyəti təcrid etməklə sınaqdan keçirdim

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

Bu sorğu yavaş idi. Çünki JOIN-s sürətli və alt sorğular sürətli, qalan tək şey idi && operator.

Bu sadəcə əsas əməliyyatdır. Biz həmişə nümunə axtarmaq üçün bütün əsas URL cədvəlini axtarmalıyıq və biz həmişə kəsişmələri tapmalıyıq. Biz birbaşa URL qeydləri ilə axtarış edə bilmərik, çünki bunlar sadəcə olaraq istinad edilən ID-lərdir urls.

Həll yolunda

&& yavaş, çünki hər iki dəst böyükdür. Əvəz etsəm əməliyyat nisbətən tez olacaq urls haqqında { "http://google.com/", "http://wingify.com/" }.

Postgres-də istifadə etmədən dəst kəsişməsi etmək üçün bir yol axtarmağa başladım &&, lakin çox uğur qazanmadı.

Sonda problemi yalnız təcrid olunmuş şəkildə həll etmək qərarına gəldik: mənə hər şeyi verin urls URL-nin nümunəyə uyğun olduğu sətirlər. Əlavə şərtlər olmadan -

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

Əvəzinə JOIN sintaksis Mən sadəcə bir alt sorğudan istifadə etdim və genişləndirdim recording_data.urls serialda şərti birbaşa tətbiq edə biləsiniz WHERE.

Burada ən əsası budur && verilmiş girişdə uyğun URL olub-olmadığını yoxlamaq üçün istifadə olunur. Bir az gözünüzü qıysanız, bu əməliyyatın massivin elementləri (və ya cədvəlin sətirləri) arasında hərəkət etdiyini və şərt (uyğunluq) yerinə yetirildikdə dayandığını görə bilərsiniz. Sizə heç nəyi xatırlatmır? Bəli, EXISTS.

O vaxtdan bəri recording_data.urls alt sorğu kontekstindən kənarda istinad edilə bilər, bu baş verəndə köhnə dostumuza qayıda bilərik EXISTS və alt sorğunu onunla sarın.

Hər şeyi bir araya gətirərək, optimallaşdırılmış son sorğunu alırıq:

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

Və son çıxış vaxtı Time: 1898.717 ms Qeyd etmək vaxtıdır?!?

O qədər də sürətli deyil! Əvvəlcə düzgünlüyünü yoxlamaq lazımdır. Mən çox şübhəli idim EXISTS daha əvvəl tamamlamaq üçün məntiqi dəyişdirdiyi üçün optimallaşdırma. Sorğuya aşkar olmayan xəta əlavə etmədiyimizə əmin olmalıyıq.

Sadə bir sınaq qaçmaq idi count(*) çoxlu sayda müxtəlif məlumat dəstləri üçün həm yavaş, həm də sürətli sorğularda. Sonra məlumatların kiçik bir hissəsi üçün bütün nəticələrin düzgün olduğunu əl ilə təsdiqlədim.

Bütün testlər ardıcıl olaraq müsbət nəticələr verdi. Hər şeyi düzəltdik!

Öyrənilmiş dərslər

Bu hekayədən öyrənilməli çoxlu dərslər var:

Sorğu planları bütün hekayəni izah etmir, lakin onlar ipucu verə bilər
Əsas şübhəlilər həmişə əsl günahkar deyillər
Yavaş sorğular darboğazları təcrid etmək üçün parçalana bilər
Bütün optimallaşdırmalar reduktiv xarakter daşımır
Istifadə EXIST, mümkün olduqda, məhsuldarlığın kəskin artmasına səbəb ola bilər

Buraxılış

Sorğu müddətindən ~24 dəqiqədən 2 saniyəyə qədər getdik - olduqca əhəmiyyətli performans artımı! Bu məqalə böyük olsa da, etdiyimiz bütün təcrübələr bir gündə baş verdi və optimallaşdırma və sınaq üçün 1,5 ilə 2 saat arasında vaxt apardıqları təxmin edildi.

SQL çox gözəl bir dildir, əgər ondan qorxmursunuzsa, onu öyrənib istifadə etməyə çalışın. SQL sorğularının necə yerinə yetirildiyini, verilənlər bazasının sorğu planlarını necə yaratdığını, indekslərin necə işlədiyini və sadəcə olaraq məşğul olduğunuz məlumatların ölçüsünü yaxşı başa düşməklə, siz sorğuları optimallaşdırmaqda çox uğurlu ola bilərsiniz. Bununla belə, fərqli yanaşmaları sınamağa davam etmək və darboğazları tapmaqla problemi yavaş-yavaş aradan qaldırmaq eyni dərəcədə vacibdir.

Bu kimi nəticələrə nail olmağın ən yaxşı tərəfi nəzərəçarpacaq, görünən sürət artımıdır - burada əvvəllər hətta yüklənməyən hesabat indi demək olar ki, dərhal yüklənir.

-a xüsusi təşəkkürlər yoldaşlarım Aditya Mişranın əmri ilə, Aditya Gauru и Varun Malhotra beyin fırtınası üçün və Dinkar Pandir Nəhayət onunla vidalaşmamışdan əvvəl son sorğumuzda vacib bir səhv tapdığımız üçün!

Mənbə: www.habr.com

Bir SQL araşdırmasının hekayəsi