PostgreSQL Antipatterns: ზღაპარი სახელის მიხედვით ძიების განმეორებითი დახვეწის შესახებ, ან „წინ და უკან ოპტიმიზაცია“

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

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

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

0: რა უნდოდა მომხმარებელს?

[KDPV აქედან გამომდინარე,]

რას ნიშნავს მომხმარებელი ჩვეულებრივ, როდესაც საუბრობს სახელის მიხედვით „სწრაფ“ ძიებაზე? თითქმის არასოდეს აღმოჩნდება „პატიოსანი“ ძიება მსგავსი ქვესტრიქონისთვის ... LIKE '%роза%' - იმიტომ, რომ მაშინ შედეგი მოიცავს არა მარტო 'Розалия' и 'Магазин Роза'მაგრამ 'Гроза' და კიდევ 'Дом Деда Мороза'.

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

1: შეზღუდეთ დავალება

და მით უმეტეს, ადამიანი კონკრეტულად არ შევა 'роз магаз', ასე რომ თქვენ უნდა მოძებნოთ თითოეული სიტყვა პრეფიქსით. არა, მომხმარებლისთვის ბევრად უფრო ადვილია უპასუხოს სწრაფ მინიშნებას ბოლო სიტყვისთვის, ვიდრე მიზანმიმართულად „დაუზუსტოს“ წინა სიტყვები - ნახეთ, როგორ უმკლავდება ამას ნებისმიერი საძიებო სისტემა.

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

რას აკეთებს აბსტრაქტული დეველოპერი?

1.0: გარე საძიებო სისტემა

ოჰ, ძებნა რთულია, მე საერთოდ არ მინდა არაფრის გაკეთება - მოდით, ეს დევოპს მივცეთ! დაე მათ განათავსონ საძიებო სისტემა მონაცემთა ბაზის გარეთ: Sphinx, ElasticSearch,...

სამუშაო ვარიანტი, თუმცა შრომატევადი სინქრონიზაციისა და ცვლილებების სიჩქარის თვალსაზრისით. მაგრამ არა ჩვენს შემთხვევაში, რადგან ძებნა ხორციელდება თითოეული კლიენტისთვის მხოლოდ მისი ანგარიშის მონაცემების ფარგლებში. და მონაცემებს აქვს საკმაოდ მაღალი ცვალებადობა - და თუ მენეჯერი ახლა შევიდა ბარათში 'Магазин Роза', შემდეგ 5-10 წამის შემდეგ შეიძლება უკვე ახსოვდეს, რომ დაავიწყდა მისი ელ.ფოსტის მითითება იქ და სურდეს მისი პოვნა და გამოსწორება.

ამიტომ - მოდით მოძებნეთ „პირდაპირ მონაცემთა ბაზაში“. საბედნიეროდ, PostgreSQL ამის გაკეთების საშუალებას გვაძლევს და არა მხოლოდ ერთი ვარიანტი - ჩვენ მათ გადავხედავთ.

1.1: "პატიოსანი" ქვესტრიქონი

ჩვენ ვიჭერთ სიტყვას "ქვესტრიქონი". მაგრამ ინდექსის ძიებისთვის ქვესტრიქონებით (და თუნდაც რეგულარული გამონათქვამებით!) არის შესანიშნავი მოდული pg_trgm! მხოლოდ ამის შემდეგ იქნება საჭირო სწორი დახარისხება.

მოდელის გასამარტივებლად ავიღოთ შემდეგი ფირფიტა:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

ჩვენ იქ ავტვირთავთ რეალური ორგანიზაციების 7.8 მილიონ ჩანაწერს და მათ ინდექსირებას ვახდენთ:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

მოდი ვეძებოთ პირველი 10 ჩანაწერი ინტერხაზური ძიებისთვის:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[ნახეთ description.tensor.ru]

ისე, ეს არის ... 26 ms, 31 MB წაკითხული მონაცემები და 1.7K-ზე მეტი გაფილტრული ჩანაწერი - 10 მოძიებული ჩანაწერისთვის. ზედმეტად მაღალი ხარჯებია, რამე უფრო ეფექტური არ არის?

1.2: ძიება ტექსტით? ეს არის FTS!

მართლაც, PostgreSQL უზრუნველყოფს ძალიან ძლიერ სრული ტექსტის საძიებო სისტემა (სრული ტექსტის ძიება), ძიების პრეფიქსის ჩათვლით. შესანიშნავი ვარიანტი, თქვენ არც კი გჭირდებათ გაფართოებების დაყენება! Მოდი ვცადოთ:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[ნახეთ description.tensor.ru]

აქ შეკითხვის შესრულების პარალელიზება ცოტა დაგვეხმარა, დრო განახევრდა 11 ms. და 1.5-ჯერ ნაკლები მოგვიწია წაკითხვა - ჯამში 20MB. მაგრამ აქ, რაც ნაკლებია, მით უკეთესი, რადგან რაც უფრო დიდია ჩვენ მიერ წაკითხული მოცულობა, მით უფრო მაღალია ქეშის გამოტოვების შანსი და დისკიდან წაკითხული მონაცემების ყოველი დამატებითი გვერდი პოტენციური „მუხრუჭებია“ მოთხოვნისთვის.

1.3: ისევ მოგწონს?

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

გავიხსენოთ რისი ნახვა სურს მომხმარებელს პირველი "რომელიც იწყება ...". ასე რომ, ეს არის მისი სუფთა სახით პრეფიქსის ძებნა via text_pattern_ops! და მხოლოდ იმ შემთხვევაში, თუ ჩვენ "არ გვაქვს საკმარისი" 10-მდე ჩანაწერი, რომელსაც ვეძებთ, მაშინ ჩვენ უნდა დავასრულოთ მათი კითხვა FTS ძიების გამოყენებით:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[ნახეთ description.tensor.ru]

შესანიშნავი შესრულება - სულ 0.05ms და 100KB-ზე ცოტა მეტი წაიკითხე! მხოლოდ ჩვენ დაგვავიწყდა სახელის მიხედვით დალაგებარათა მომხმარებელი არ დაიკარგოს შედეგებში:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[ნახეთ description.tensor.ru]

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

1.4: "დასრულება ფაილით"

მაგრამ არის ინდექსი, რომელიც საშუალებას გაძლევთ მოძებნოთ დიაპაზონის მიხედვით და კვლავ გამოიყენოთ დახარისხება ნორმალურად - რეგულარული ბხე!

CREATE INDEX ON firms(lower(name));

მხოლოდ მასზე მოთხოვნა უნდა იყოს „ხელით შეგროვებული“:

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[ნახეთ description.tensor.ru]

შესანიშნავი - დახარისხება მუშაობს და რესურსების მოხმარება რჩება "მიკროსკოპული", ათასობითჯერ უფრო ეფექტური ვიდრე "სუფთა" FTS! რჩება მხოლოდ მისი გაერთიანება ერთ მოთხოვნად:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

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

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

1.5*: ჩვენ შეგვიძლია გავაკეთოთ ფაილის გარეშე

ზემოთ LIKE ჩვენ ხელი შეგვიშალეს არასწორი დახარისხების გამოყენებაში. მაგრამ მისი დაყენება შესაძლებელია "სწორ გზაზე" USING ოპერატორის მითითებით:

ნაგულისხმევად ვარაუდობენ ASC. გარდა ამისა, თქვენ შეგიძლიათ მიუთითოთ კონკრეტული დახარისხების ოპერატორის სახელი პუნქტში USING. დალაგების ოპერატორი უნდა იყოს B-ხის ოპერატორების ზოგიერთი ოჯახის წევრზე ნაკლები ან მეტი. ASC ჩვეულებრივ ექვივალენტი USING < и DESC ჩვეულებრივ ექვივალენტი USING >.

ჩვენს შემთხვევაში, "ნაკლები" არის ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[ნახეთ description.tensor.ru]

2: როგორ ხდება მოთხოვნები მჟავე

ახლა ჩვენ ვტოვებთ ჩვენს თხოვნას ექვსი თვის ან ერთი წლის განმავლობაში "ადუღებას" და გაკვირვებულები აღმოვაჩინეთ ის კვლავ "ზედაზე" მეხსიერების მთლიანი ყოველდღიური "გამოტუმბვის" მაჩვენებლებით (ბუფერების გაზიარებული დარტყმა) 5.5TB - ანუ იმაზე მეტიც, ვიდრე თავდაპირველად იყო.

არა, რა თქმა უნდა, ჩვენი ბიზნესი გაიზარდა და ჩვენი დატვირთვა გაიზარდა, მაგრამ არა ამდენით! ეს ნიშნავს, რომ აქ რაღაც სევდიანია - მოდით გავარკვიოთ.

2.1: პეიჯინგის დაბადება

რაღაც მომენტში, სხვა განვითარების გუნდს სურდა, რომ შესაძლებელი ყოფილიყო „გადახტომა“ სწრაფი ხელმოწერის ძიებიდან რეესტრში იგივე, მაგრამ გაფართოებული შედეგებით. რა არის რეესტრი გვერდის ნავიგაციის გარეშე? მოდი გავაფუჭოთ!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

ახლა შესაძლებელი იყო ძიების შედეგების რეესტრის ჩვენება „გვერდი-გვერდის“ დატვირთვით, დეველოპერისთვის ყოველგვარი სტრესის გარეშე.

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

2.2: რაღაც ეგზოტიკური მინდა

რაღაც მომენტში დეველოპერს სურდა მიღებული ნიმუშის დივერსიფიკაცია მონაცემებით სხვა ცხრილიდან, რისთვისაც მთელი წინა მოთხოვნა გაეგზავნა CTE-ს:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

და ასეც რომ იყოს, ეს არ არის ცუდი, რადგან ქვემოთხოვნა ფასდება მხოლოდ 10 დაბრუნებული ჩანაწერისთვის, თუ არა ...

2.3: DISTINCT უაზრო და დაუნდობელია

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

რას აკეთებს დეველოპერი მიზეზის ძებნის ნაცვლად?.. კითხვა არაა!

• გაორმაგება ზომა ორიგინალური ნიმუშები
• გამოიყენეთ DISTINCTთითოეული ხაზის მხოლოდ ცალკეული მაგალითების მისაღებად

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

ანუ, გასაგებია, რომ შედეგი, საბოლოო ჯამში, ზუსტად იგივეა, მაგრამ მე -2 CTE ქვემოძიებაში "გაფრენის" შანსი გაცილებით მაღალი გახდა და ამის გარეშეც კი, აშკარად უფრო იკითხება.

მაგრამ ეს არ არის ყველაზე სამწუხარო. ვინაიდან დეველოპერმა სთხოვა არჩევა DISTINCT არა კონკრეტული, არამედ ყველა სფეროსთვის ერთდროულად ჩანაწერები, შემდეგ sub_query ველი - ქვემოთხოვნის შედეგი - ავტომატურად ჩართული იყო იქ. ახლა, შესასრულებლად DISTINCT, მონაცემთა ბაზა უკვე უნდა შესრულებულიყო არა 10 ქვემოთხოვნა, არამედ ყველა + 2!

2.4: უპირველეს ყოვლისა თანამშრომლობა!

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

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

ზოგადად, დაჭერილ ნიმუშში N მიაღწია თითქმის 17K მნიშვნელობებს, და მხოლოდ ერთ დღეში, სულ მცირე, 4K ასეთი მოთხოვნა შესრულდა "ჯაჭვის გასწვრივ". ბოლო მათგანი თამამად დაასკანირა 1 გბ მეხსიერება თითო გამეორებაზე...

საერთო ჯამში

წყარო: www.habr.com