PostgreSQL Antipatterns: "უნდა დარჩეს მხოლოდ ერთი!"

SQL-ში აღწერთ „რის“ მიღწევას გსურთ და არა „როგორ“ უნდა შესრულდეს. ამრიგად, SQL მოთხოვნების შემუშავების პრობლემა სტილში "როგორც ისმის, როგორ წერია" თავის საპატიო ადგილს იკავებს. SQL-ში პირობების გამოთვლის მახასიათებლები.

დღეს, უკიდურესად მარტივი მაგალითების გამოყენებით, ვნახოთ, რა შეიძლება გამოიწვიოს ამან გამოყენების კონტექსტში GROUP/DISTINCT и LIMIT მათთან ერთად.

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

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

შეიძლება არც ისე სანახაობრივი, მაგრამ...

„ტკბილი წყვილი“: JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

გასაგები იქნებოდა რა უნდოდათ აირჩიეთ ჩანაწერები X, რომლებისთვისაც არის ჩანაწერები Y-ში, რომლებიც დაკავშირებულია შესრულებულ პირობასთან. დაწერა მოთხოვნა მეშვეობით JOIN - რამდენჯერმე მიიღო pk მნიშვნელობა (ზუსტად რამდენი შესაფერისი ჩანაწერი გამოჩნდა Y-ში). როგორ ამოიღონ? Რა თქმა უნდა DISTINCT!

განსაკუთრებით "სასიხარულოა", როდესაც თითოეულ X-ჩანაწერზე არის რამდენიმე ასეული დაკავშირებული Y-ჩანაწერი, შემდეგ კი დუბლიკატები გმირულად იშლება...

როგორ გავასწორო? დასაწყისისთვის, გააცნობიერეთ, რომ პრობლემის შეცვლა შესაძლებელია "აირჩიეთ ჩანაწერი X, რომლისთვისაც Y-ში არის მინიმუმ ერთი დაკავშირებული შესრულებულ პირობასთან" - ბოლოს და ბოლოს, ჩვენ არაფერი გვჭირდება თავად Y- ჩანაწერიდან.

ჩადგმული EXISTS

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

PostgreSQL-ის ზოგიერთ ვერსიას ესმის, რომ EXISTS-ში საკმარისია იპოვოთ პირველი ჩანაწერი, რომელიც გამოჩნდება, უფროსები კი არა. ამიტომ მირჩევნია ყოველთვის მივუთითო LIMIT 1 ფარგლებში EXISTS.

გვერდითი შეერთება

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

იგივე ვარიანტი საშუალებას იძლევა, საჭიროების შემთხვევაში, დაუყოვნებლივ დააბრუნოს გარკვეული მონაცემები ნაპოვნი ასოცირებული Y- ჩანაწერიდან. მსგავსი ვარიანტი განიხილება სტატიაში "PostgreSQL Antipatterns: იშვიათი ჩანაწერი მიაღწევს JOIN-ის შუა რიცხვებს".

„რატომ გადაიხადოთ მეტი“: DISTINCT [ON] + LIMIT 1

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

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

ახლა ჩვენ ვკითხულობთ მოთხოვნას და ვცდილობთ გავიგოთ, რის გაკეთებას გვთავაზობენ DBMS:

• ნიშნების დამაკავშირებელი
• უნიკალური X.pk-ის მიერ
• დარჩენილი ჩანაწერებიდან აირჩიეთ ერთი

მერე რა მიიღე? "მხოლოდ ერთი შესვლა" უნიკალურიდან - და თუ ამ არაუნიკალურს ავიღებთ, შედეგი როგორმე შეიცვლება?.. „და თუ განსხვავება არ არის, რატომ გადაიხადოთ მეტი?“

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

და ზუსტად იგივე თემა GROUP BY + LIMIT 1.

„უბრალოდ უნდა ვიკითხო“: იმპლიციტური GROUP + LIMIT

მსგავსი რამ ხდება სხვადასხვა დროს სიცარიელე ჩეკები ნიშნები ან CTE მოთხოვნის პროგრესირებისას:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

აგრეგატული ფუნქციები (count/min/max/sum/...) წარმატებით შესრულებულია მთელ კომპლექტზე, თუნდაც მკაფიო ინსტრუქციების გარეშე GROUP BY. მხოლოდ ერთად LIMIT ისინი არ არიან ძალიან მეგობრულები.

დეველოპერს შეუძლია იფიქროს ”თუ იქ ჩანაწერებია, მაშინ მე არ მჭირდება LIMIT-ზე მეტი”. მაგრამ ნუ გააკეთებ ამას! რადგან ბაზისთვის ეს არის:

• დათვალეთ რა უნდათ ყველა ჩანაწერის მიხედვით
• მიეცით იმდენი ხაზი, რამდენსაც ითხოვენ

სამიზნე პირობებიდან გამომდინარე, მიზანშეწონილია განახორციელოთ ერთ-ერთი შემდეგი ჩანაცვლება:

• (count + LIMIT 1) = 0 on NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 on EXISTS(LIMIT 1)
• count >= N on (SELECT count(*) FROM (... LIMIT N))

„რამდენი დაკიდება გრამებში“: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

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

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

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

იმისათვის, რომ ტყუილად არ ვიწუწუნოთ, გამოვიყენოთ რეკურსიული შეკითხვა "DISTINCT არის ღარიბებისთვის" PostgreSQL ვიკიდან:

წყარო: www.habr.com