რეცეპტები ავადმყოფი SQL შეკითხვებისთვის

რამდენიმე თვის წინ გამოვაცხადეთ განმარტეთ.tensor.ru - საჯარო სერვისი შეკითხვის გეგმების ანალიზისა და ვიზუალიზაციისთვის PostgreSQL-ზე.

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

მოუსმინეთ მათ და თქვენი მოთხოვნები "აბრეშუმისებრი გლუვი გახდება". 🙂

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

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

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

თემის უკეთ ჩაძირვისთვის, ჯერ შეგიძლიათ მოუსმინოთ შესაბამის ბლოკს ჩემი მოხსენება PGConf.Russia 2020-ზედა მხოლოდ ამის შემდეგ გადადით თითოეული მაგალითის დეტალურ ანალიზზე:

#1: ინდექსი "დახარისხება"

Როდის

აჩვენეთ კლიენტის ბოლო ინვოისი "შპს კოლოკოლჩიკი".

როგორ ამოვიცნოთ

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

რეკომენდაციები

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

მაგალითი:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

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

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

ვასწორებთ:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

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

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

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

#2: ინდექსის კვეთა (BitmapAnd)

Როდის

აჩვენეთ ყველა კონტრაქტი კლიენტისთვის "შპს კოლოკოლჩიკი" დადებული "NJSC Lyutik"-ის სახელით.

როგორ ამოვიცნოთ

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

რეკომენდაციები

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

მაგალითი:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

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

ვასწორებთ:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

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

აქ მოგება უფრო მცირეა, რადგან Bitmap Heap Scan თავისთავად საკმაოდ ეფექტურია. Მაგრამ მაინც 7-ჯერ უფრო სწრაფი და 2.5-ჯერ ნაკლები კითხვა.

#3: ინდექსების გაერთიანება (BitmapOr)

Როდის

აჩვენეთ პირველი 20 უძველესი "საკუთარი" ან მიუწერელი მოთხოვნა დამუშავებისთვის, პრიორიტეტულად საკუთარი.

როგორ ამოვიცნოთ

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

რეკომენდაციები

გამოიყენეთ გაერთიანება [ყველა] დააკავშიროთ ქვემოთხოვნები თითოეული პირობის ან ბლოკისთვის.

მაგალითი:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

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

ვასწორებთ:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

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

ჩვენ ვისარგებლეთ იმით, რომ 20-ვე აუცილებელი ჩანაწერი დაუყოვნებლივ იქნა მიღებული პირველ ბლოკში, ასე რომ, მეორე, უფრო "ძვირი" Bitmap Heap Scan-ით, არც კი შესრულებულა - შედეგად. 22-ჯერ უფრო სწრაფი, 44-ჯერ ნაკლები კითხვა!

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

განზოგადებული ვერსია შეუკვეთა შერჩევა რამდენიმე გასაღებით (და არა მხოლოდ წყვილი const / NULL) განხილულია სტატიაში SQL HowTo: ჩაწერეთ while-ციკლი პირდაპირ მოთხოვნაში, ან „დაწყებითი სამმხრივი“.

#4: ჩვენ ძალიან ბევრს ვკითხულობთ

Როდის

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

”და თქვენ არ გაქვთ იგივე, მაგრამ მარგალიტის ღილებით? " ფილმი "ბრილიანტის ხელი"

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

როგორ ამოვიცნოთ

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

რეკომენდაციები

შექმენით [მეტი] სპეციალიზებული ინდექსი WHERE პუნქტით ან შეიტანეთ დამატებითი ველები ინდექსში.

თუ ფილტრაციის მდგომარეობა "სტატიკურია" თქვენი ამოცანებისთვის - ეს არის არ მოიცავს გაფართოებას მნიშვნელობების სია მომავალში - უმჯობესია გამოიყენოთ WHERE ინდექსი. სხვადასხვა ლოგიკური/ენუმ სტატუსი კარგად ჯდება ამ კატეგორიაში.

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

მაგალითი:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

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

ვასწორებთ:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

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

როგორც ხედავთ, გეგმიდან გაფილტვრა მთლიანად გაქრა და მოთხოვნა გახდა 5-ჯერ უფრო სწრაფად.

#5: იშვიათი მაგიდა

Როდის

საკუთარი დავალების დამუშავების რიგის შექმნის სხვადასხვა მცდელობა, როდესაც მაგიდაზე ჩანაწერების განახლებების/წაშლის დიდი რაოდენობა იწვევს „მკვდარი“ ჩანაწერების დიდი რაოდენობის მდგომარეობას.

როგორ ამოვიცნოთ

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

რეკომენდაციები

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

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

მაგრამ ჩვენ უნდა გვესმოდეს, რომ VACUUM FULL-იც კი ყოველთვის ვერ დაგვეხმარება. ასეთი შემთხვევებისთვის, თქვენ უნდა გაეცნოთ სტატიის ალგორითმს. DBA: როდესაც VACUUM გადის, ჩვენ ვასუფთავებთ მაგიდას ხელით.

#6: კითხვა ინდექსის "შუადან".

Როდის

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

როგორ ამოვიცნოთ

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

რეკომენდაციები

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

მაგალითი:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

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

როგორც ჩანს, ყველაფერი კარგადაა, თუნდაც ინდექსის მხრივ, მაგრამ რაღაცნაირად საეჭვოა - წაკითხული 20 ჩანაწერიდან თითოეულს 4 გვერდის მონაცემი უნდა გამოეკლებინა, თითო ჩანაწერზე 32 კბ - თამამი არ არის? დიახ და ინდექსის სახელი tbl_fk_org_fk_cli_idx აზროვნებამდე მივყავართ.

ვასწორებთ:

CREATE INDEX ON tbl(fk_cli);

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

უცებ - 10-ჯერ უფრო სწრაფად და 4-ჯერ ნაკლებად წასაკითხად!

ინდექსების არაეფექტური გამოყენების მეტი მაგალითებისთვის იხილეთ სტატია DBA: იპოვნეთ უსარგებლო ინდექსები.

#7: CTE × CTE

Როდის

მოთხოვნით გაიტანა "მსუქანი" CTE სხვადასხვა მაგიდებიდან და შემდეგ გადავწყვიტე მათ შორის გაკეთება JOIN.

საქმე ეხება v12 ქვემოთ მოყვანილ ვერსიებს ან მოთხოვნებს WITH MATERIALIZED.

როგორ ამოვიცნოთ

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

რეკომენდაციები

ყურადღებით გაანალიზეთ მოთხოვნა CTE-ები აქ საერთოდ საჭიროა? თუ კი, მაშინ გამოიყენეთ "ლექსიკონი" hstore/json-ში აღწერილი მოდელის მიხედვით PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN.

#8: დისკზე შეცვლა (ტემპერატურა დაწერილია)

Როდის

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

როგორ ამოვიცნოთ

-> *
   && temp written > 0

რეკომენდაციები

თუ ოპერაციის მიერ გამოყენებული მეხსიერების რაოდენობა მნიშვნელოვნად არ აღემატება პარამეტრის დადგენილ მნიშვნელობას work_mem, უნდა გამოსწორდეს. თქვენ შეგიძლიათ დაუყოვნებლივ შეიყვანოთ კონფიგურაცია ყველასთვის, ან შეგიძლიათ SET [LOCAL] კონკრეტული მოთხოვნის/ტრანზაქციისთვის.

მაგალითი:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

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

ვასწორებთ:

SET work_mem = '128MB'; -- перед выполнением запроса

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

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

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

#9: შეუსაბამო სტატისტიკა

Როდის

ბაზაში ერთდროულად ბევრი ჩაასხეს, მაგრამ გაძევების დრო არ ჰქონდათ ANALYZE.

როგორ ამოვიცნოთ

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

რეკომენდაციები

დახარჯე იგივე ANALYZE.

ეს სიტუაცია უფრო დეტალურად არის აღწერილი PostgreSQL Antipatterns: სტატისტიკა ყველაფრის სათავეა.

#10: "რაღაც არასწორედ წარიმართა"

Როდის

იყო ჩაკეტვა, რომელიც ელოდა კონკურენტულ მოთხოვნას, ან არ იყო საკმარისი CPU/hypervisor ტექნიკის რესურსი.

როგორ ამოვიცნოთ

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

რეკომენდაციები

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

წყარო: www.habr.com