ერთი SQL გამოძიების ამბავი

გასულ დეკემბერში მივიღე საინტერესო ხარვეზის ანგარიში VWO მხარდაჭერის გუნდისგან. დიდი კორპორატიული კლიენტისთვის ერთ-ერთი ანალიტიკური ანგარიშის ჩატვირთვის დრო აკრძალული ჩანდა. და რადგან ეს არის ჩემი პასუხისმგებლობის სფერო, მაშინვე ყურადღება გავამახვილე პრობლემის გადაჭრაზე.

წინაისტორია

იმის გასაგებად, რაზეც ვსაუბრობ, ცოტას მოგიყვებით VWO-ზე. ეს არის პლატფორმა, რომლითაც შეგიძლიათ დაიწყოთ სხვადასხვა მიზნობრივი კამპანიები თქვენს ვებსაიტებზე: ჩაატაროთ A/B ექსპერიმენტები, თვალყური ადევნოთ ვიზიტორებს და კონვერტაციებს, გააანალიზოთ გაყიდვების ძაბრები, აჩვენოთ სითბოს რუქები და დაუკრათ ვიზიტების ჩანაწერები.

მაგრამ ყველაზე მნიშვნელოვანი რამ პლატფორმაზე არის მოხსენება. ყველა ზემოთ ჩამოთვლილი ფუნქცია ურთიერთდაკავშირებულია. კორპორატიული კლიენტებისთვის კი, უზარმაზარი ინფორმაცია უბრალოდ გამოუსადეგარი იქნებოდა ძლიერი პლატფორმის გარეშე, რომელიც მას ანალიტიკის სახით წარმოაჩენს.

პლატფორმის გამოყენებით, შეგიძლიათ გააკეთოთ შემთხვევითი მოთხოვნა მონაცემთა დიდ ნაკრებზე. აი მარტივი მაგალითი:

ყველა დაწკაპუნების ჩვენება გვერდზე "abc.com" FROM <date d1> to <date d2>> ადამიანებისთვის, ვინც იყენებდა Chrome OR-ს (მდებარეობს ევროპაში და იყენებდა iPhone-ს)

ყურადღება მიაქციეთ ბულის ოპერატორებს. ისინი ხელმისაწვდომია კლიენტებისთვის შეკითხვის ინტერფეისში, რათა განახორციელონ თვითნებურად რთული მოთხოვნები ნიმუშების მისაღებად.

ნელი მოთხოვნა

მოცემული კლიენტი ცდილობდა გაეკეთებინა ისეთი რამ, რაც ინტუიციურად სწრაფად უნდა მუშაობდეს:

სესიის ყველა ჩანაწერის ჩვენება მომხმარებლებისთვის, რომლებიც ეწვივნენ ნებისმიერ გვერდს URL-ით, რომელიც შეიცავს "/jobs"

ამ საიტს ჰქონდა ტონა ტრაფიკი და ჩვენ ვინახავდით მილიონზე მეტ უნიკალურ URL-ს მხოლოდ ამისთვის. და მათ სურდათ ეპოვათ საკმაოდ მარტივი URL-ის შაბლონი, რომელიც დაკავშირებულია მათ ბიზნეს მოდელთან.

წინასწარი გამოძიება

მოდით შევხედოთ რა ხდება მონაცემთა ბაზაში. ქვემოთ მოცემულია ორიგინალური ნელი SQL მოთხოვნა:

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 ;

და აქ არის დრო:

დაგეგმილი დრო: 1.480 ms შესრულების დრო: 1431924.650 ms

მოთხოვნამ მოიცვა 150 ათასი სტრიქონი. შეკითხვის დამგეგმავმა აჩვენა რამდენიმე საინტერესო დეტალი, მაგრამ აშკარა შეფერხებების გარეშე.

მოდით, უფრო დეტალურად შევისწავლოთ მოთხოვნა. როგორც ხედავთ, ის აკეთებს JOIN სამი მაგიდა:

1. სხდომები: სესიის ინფორმაციის ჩვენება: ბრაუზერი, მომხმარებლის აგენტი, ქვეყანა და ა.შ.
2. ჩანაწერის_მონაცემები: ჩაწერილი URL-ები, გვერდები, ვიზიტების ხანგრძლივობა
3. მისამართები: უკიდურესად დიდი URL-ების დუბლირების თავიდან ასაცილებლად, ჩვენ მათ ცალკე ცხრილში ვინახავთ.

ასევე გაითვალისწინეთ, რომ ჩვენი ყველა ცხრილი უკვე დაყოფილია account_id. ამ გზით, გამორიცხულია სიტუაცია, როდესაც ერთი განსაკუთრებით დიდი ანგარიში სხვებს პრობლემებს უქმნის.

ეძებს მინიშნებებს

უფრო მჭიდრო შემოწმების შემდეგ, ჩვენ ვხედავთ, რომ რაღაც არასწორია კონკრეტულ მოთხოვნასთან დაკავშირებით. ღირს ამ სტრიქონის უფრო დეტალურად დათვალიერება:

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

პირველი აზრი იყო, რომ ალბათ იმიტომ ILIKE ყველა ამ გრძელ URL-ზე (ჩვენ გვაქვს 1,4 მილიონზე მეტი უნიკალური ამ ანგარიშისთვის შეგროვებული URL-ები) შეიძლება დაზარალდეს.

მაგრამ არა, ეს არ არის მთავარი!

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

Time: 5231.765 ms

შაბლონის ძიების მოთხოვნას მხოლოდ 5 წამი სჭირდება. შაბლონის ძიება მილიონ უნიკალურ URL-ში აშკარად არ არის პრობლემა.

სიაში შემდეგი ეჭვმიტანილი რამდენიმეა JOIN. იქნებ მათმა გადამეტებულმა გამოყენებამ გამოიწვია შენელება? ჩვეულებრივ JOINეს არის შესრულების პრობლემების ყველაზე აშკარა კანდიდატები, მაგრამ მე არ მჯეროდა, რომ ჩვენი შემთხვევა იყო ტიპიური.

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

და ეს ასევე არ იყო ჩვენი საქმე. JOINსაკმაოდ სწრაფი აღმოჩნდა.

ეჭვმიტანილთა წრის ვიწროვება

მზად ვიყავი დამეწყო მოთხოვნის შეცვლა, რათა მივაღწიო შესრულების შესაძლო გაუმჯობესებას. მე და ჩემმა გუნდმა განვავითარეთ 2 ძირითადი იდეა:

• გამოიყენეთ EXISTS ქვემოთხოვნის URL-ისთვის: ჩვენ გვინდოდა კიდევ ერთხელ გადაგვემოწმებინა, იყო თუ არა რაიმე პრობლემა URL-ების ქვემოთხოვნასთან დაკავშირებით. ამის მიღწევის ერთ-ერთი გზა უბრალოდ გამოყენებაა EXISTS. EXISTS შეუძლია მნიშვნელოვნად გააუმჯობესებს შესრულებას, რადგან ის მაშინვე მთავრდება, როგორც კი იპოვის ერთადერთ სტრიქონს, რომელიც ემთხვევა მდგომარეობას.

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

Კარგი, დიახ. ქვემოთხოვნა, როდესაც შეფუთულია EXISTS, ყველაფერს ძალიან სწრაფად აკეთებს. შემდეგი ლოგიკური კითხვაა, რატომ არის მოთხოვნა JOIN-ამი და თავად სუბკვერი ცალ-ცალკე სწრაფია, მაგრამ ერთად საშინლად ნელია?

• ქვემოთხოვნის გადატანა CTE-ზე : თუ მოთხოვნა თავისთავად სწრაფია, ჩვენ შეგვიძლია უბრალოდ გამოვთვალოთ სწრაფი შედეგი ჯერ და შემდეგ მივაწოდოთ ის მთავარ მოთხოვნას

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;

მაგრამ მაინც ძალიან ნელი იყო.

დამნაშავის პოვნა

მთელი ამ ხნის განმავლობაში ჩემს თვალწინ ერთი წვრილმანი მიტრიალებდა, რომელსაც გამუდმებით განზე ვივარცხნიდი. მაგრამ რადგან სხვა აღარაფერი დამრჩა, გადავწყვიტე მეც მიმეხედა. მე ვლაპარაკობ && ოპერატორი. Ნახვამდის EXISTS უბრალოდ გაუმჯობესებული შესრულება && იყო ერთადერთი დარჩენილი საერთო ფაქტორი ნელი შეკითხვის ყველა ვერსიაში.

Ყურება დოკუმენტაცია, ჩვენ ამას ვხედავთ && გამოიყენება მაშინ, როდესაც გჭირდებათ საერთო ელემენტების პოვნა ორ მასივს შორის.

თავდაპირველ მოთხოვნაში ეს არის:

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

რაც ნიშნავს, რომ ჩვენ ვაკეთებთ შაბლონის ძიებას ჩვენს URL-ებზე, შემდეგ ვპოულობთ კვეთას ყველა URL-თან საერთო პოსტებით. ეს ცოტა დამაბნეველია, რადგან "urls" აქ არ ეხება ცხრილს, რომელიც შეიცავს ყველა URL-ს, არამედ ცხრილის "urls" სვეტს. recording_data.

მზარდი ეჭვებით &&, შევეცადე მეპოვა დადასტურება მათთვის შეკითხვის გენერირებულ გეგმაში EXPLAIN ANALYZE (მე უკვე მქონდა შენახული გეგმა, მაგრამ, როგორც წესი, უფრო კომფორტულად ვატარებ ექსპერიმენტებს SQL-ში, ვიდრე ვცდილობ გავიგო შეკითხვის დამგეგმავების გამჭვირვალობა).

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

მხოლოდ ფილტრების რამდენიმე ხაზი იყო &&. რაც იმას ნიშნავდა, რომ ეს ოპერაცია არა მხოლოდ ძვირი ღირდა, არამედ რამდენჯერმე გაკეთდა.

მე გამოვცადე ეს მდგომარეობის იზოლირებით

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

ეს შეკითხვა ნელი იყო. Იმიტომ რომ JOIN-s არის სწრაფი და subqueries სწრაფი, ერთადერთი რაც დარჩა იყო && ოპერატორი.

ეს მხოლოდ საკვანძო ოპერაციაა. ჩვენ ყოველთვის გვჭირდება მოძებნოთ URL-ების მთელი ცხრილი, რათა მოძებნოთ ნიმუში და ყოველთვის გვჭირდება კვეთების პოვნა. ჩვენ არ შეგვიძლია უშუალოდ URL ჩანაწერების მიხედვით მოძიება, რადგან ეს მხოლოდ ID-ებია urls.

გამოსავლის გზაზე

&& ნელი, რადგან ორივე ნაკრები უზარმაზარია. ოპერაცია შედარებით სწრაფი იქნება თუ შევცვლი urls on { "http://google.com/", "http://wingify.com/" }.

დავიწყე პოსტგრესში მითითებული კვეთის გაკეთების გზების ძებნა გამოყენების გარეშე &&, მაგრამ დიდი წარმატების გარეშე.

საბოლოო ჯამში, ჩვენ გადავწყვიტეთ პრობლემის მოგვარება იზოლირებულად: მომეცი ყველაფერი urls ხაზები, რომელთა URL ემთხვევა ნიმუშს. დამატებითი პირობების გარეშე იქნება - 

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

ნაცვლად JOIN სინტაქსი უბრალოდ გამოვიყენე ქვემოთხოვნა და გავაფართოვე recording_data.urls მასივი ისე, რომ თქვენ შეგიძლიათ პირდაპირ გამოიყენოთ პირობა WHERE.

აქ ყველაზე მთავარი ის არის && გამოიყენება იმის შესამოწმებლად, შეიცავს თუ არა მოცემული ჩანაწერი შესაბამის URL-ს. თუ ცოტას თვალი ჩახუჭავთ, ხედავთ, რომ ეს ოპერაცია მოძრაობს მასივის (ან ცხრილის რიგების) ელემენტებში და ჩერდება, როდესაც პირობა (შესაბამისი) დაკმაყოფილებულია. არაფერს არ გახსენებს? ჰო, EXISTS.

მას შემდეგ recording_data.urls შეიძლება მითითება ქვემოთხოვნის კონტექსტის გარედან, როდესაც ეს მოხდება, ჩვენ შეგვიძლია დავუბრუნდეთ ჩვენს ძველ მეგობარს EXISTS და შეფუთეთ ქვემოძიება მასთან.

ყველაფრის ერთად შეკრებით, მივიღებთ საბოლოო ოპტიმიზებულ შეკითხვას:

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

და საბოლოო ტყვიის დრო Time: 1898.717 ms აღნიშვნის დროა?!?

არც ისე სწრაფად! ჯერ უნდა შეამოწმოთ სისწორე. ძალიან ეჭვი მეპარებოდა EXISTS ოპტიმიზაცია, რადგან ის ცვლის ლოგიკას ადრე შეწყვეტისთვის. ჩვენ უნდა დავრწმუნდეთ, რომ მოთხოვნას არ დავამატეთ არა აშკარა შეცდომა.

მარტივი გამოცდა იყო გასაშვები count(*) როგორც ნელ, ასევე სწრაფ შეკითხვებზე სხვადასხვა მონაცემთა ნაკრების დიდი რაოდენობით. შემდეგ, მონაცემთა მცირე ქვეჯგუფისთვის, მე ხელით დავადასტურე, რომ ყველა შედეგი იყო სწორი.

ყველა შემოწმებამ თანმიმდევრულად დადებითი შედეგი გამოიღო. ყველაფერი მოვაგვარეთ!

ნასწავლი გაკვეთილები

ამ ისტორიიდან ბევრი გაკვეთილი შეიძლება ვისწავლოთ:

1. შეკითხვის გეგმები არ მოგვითხრობს მთელ ამბავს, მაგრამ მათ შეუძლიათ მიაწოდონ ინფორმაცია
2. მთავარი ეჭვმიტანილები ყოველთვის არ არიან ნამდვილი დამნაშავეები
3. ნელი მოთხოვნები შეიძლება დაიშალოს, რათა გამოვყოთ ბოსტნეულობა
4. ყველა ოპტიმიზაცია არ არის რედუქციური ხასიათის
5. გამოიყენეთ EXIST, სადაც ეს შესაძლებელია, შეიძლება გამოიწვიოს პროდუქტიულობის მკვეთრი ზრდა

გამოყვანის

ჩვენ გადავედით შეკითხვის დროიდან ~24 წუთიდან 2 წამამდე - შესრულების საკმაოდ მნიშვნელოვანი ზრდა! მიუხედავად იმისა, რომ ეს სტატია დიდი გამოვიდა, ყველა ექსპერიმენტი, რომელიც ჩვენ გავაკეთეთ, ჩატარდა ერთ დღეში და შეფასდა, რომ ოპტიმიზაციისა და ტესტირებისთვის მათ 1,5-დან 2 საათამდე დასჭირდათ.

SQL მშვენიერი ენაა, თუ მისი არ გეშინიათ, მაგრამ შეეცადეთ ისწავლოთ და გამოიყენოთ იგი. თუ კარგად გესმით, თუ როგორ სრულდება SQL მოთხოვნები, როგორ აგენერირებს მონაცემთა ბაზა შეკითხვის გეგმებს, როგორ მუშაობს ინდექსები და უბრალოდ იმ მონაცემების ზომა, რომელთანაც თქვენ გაქვთ საქმე, შეგიძლიათ იყოთ ძალიან წარმატებული მოთხოვნების ოპტიმიზაციაში. ამასთან, თანაბრად მნიშვნელოვანია, რომ გავაგრძელოთ სხვადასხვა მიდგომების ცდა და ნელ-ნელა დაშალოთ პრობლემა, აღმოვაჩინოთ ბოსტნეულობა.

ასეთი შედეგების მიღწევის საუკეთესო ნაწილი არის შესამჩნევი, თვალსაჩინო სიჩქარის გაუმჯობესება - სადაც ანგარიში, რომელიც ადრე არც კი იტვირთებოდა, ახლა იტვირთება თითქმის მყისიერად.

Განსაკუთრებული მადლობა ჩემო ამხანაგებო ადიტია მიშრას მეთაურობით, ადიტია გაურუ и ვარუნ მალჰოტრა ტვინის შტორმისთვის და დინკარ პანდირი ჩვენს საბოლოო მოთხოვნაში მნიშვნელოვანი შეცდომის აღმოჩენისთვის, სანამ საბოლოოდ დავემშვიდობებოდით მას!

წყარო: www.habr.com