Bir neçə ay əvvəl - ictimai PostgreSQL-ə.
Siz artıq 6000 dəfədən çox istifadə etmisiniz, lakin diqqətdən kənarda qalan bir faydalı xüsusiyyətdir. struktur ipuçları, bu kimi bir şey görünür:
Onlara qulaq asın və istəkləriniz "hamar və ipək kimi olacaq". 🙂
Ancaq ciddi olaraq, sorğunu yavaş və resurs tələb edən bir çox vəziyyət tipikdir və planın strukturu və məlumatları ilə tanınır.
Bu vəziyyətdə, hər bir fərdi inkişaf etdirici yalnız öz təcrübəsinə güvənərək, tək başına optimallaşdırma variantını axtarmaq məcburiyyətində deyil - ona burada nə baş verdiyini, səbəbi nə ola biləcəyini söyləyə bilərik və həllə necə yanaşmaq olar. Biz bunu etdik.
Gəlin bu hallara daha yaxından nəzər salaq - onlar necə müəyyən edilir və hansı tövsiyələrə gətirib çıxarır.
Mövzuya daha yaxşı daxil olmaq üçün əvvəlcə müvafiq bloku dinləyə bilərsiniz , və yalnız bundan sonra hər bir nümunənin ətraflı təhlilinə keçin:
№1: indeks "aşağı sıralama"
Nə vaxt yaranır
"MMC Kolokolchik" müştəri üçün ən son fakturanı göstərin.
Necə müəyyən etmək olar
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
tövsiyələr
İstifadə olunan indeks çeşidləmə sahələri ilə genişləndirin.
Misal:
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;
Dərhal görə bilərsiniz ki, indeksdən 100-dən çox qeyd çıxarıldı, sonra hamısı sıralandı və sonra yalnız biri qaldı.
Düzəliş:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Hətta belə bir primitiv nümunədə - 8.5 dəfə sürətli və 33 dəfə az oxuma. Hər bir dəyər üçün nə qədər çox “fakt” varsa, təsir bir o qədər aydın olur fk.
Qeyd edim ki, belə bir indeks "prefiks" indeksi kimi işləyəcək, digər sorğular üçün əvvəlkindən daha pis olmayacaq. fk, harada sırala pk yox idi və yoxdur (bu barədə daha çox oxuya bilərsiniz ). O cümlədən, normal təmin edəcək açıq xarici açar dəstəyi bu sahədə.
#2: indeks kəsişməsi (BimapVə)
Nə vaxt yaranır
"NAO Buttercup" adından bağlanmış müştəri "MMC Kolokolchik" üçün bütün müqavilələri göstərin.
Necə müəyyən etmək olar
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scantövsiyələr
yaratmaq kompozit indeks hər iki orijinaldan sahələrə görə və ya mövcud olanlardan birini ikincinin sahələri ilə genişləndirin.
Misal:
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); -- отбор по конкретной паре
Düzəliş:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Bitmap Heap Scan öz başına olduqca təsirli olduğundan burada qazanc daha kiçikdir. Lakin hər halda 7 dəfə sürətli və 2.5 dəfə az oxuma.
№3: İndeksləri birləşdirin (BitmapOr)
Nə vaxt yaranır
İlk 20 ən köhnə "biz"i və ya emal üçün təyin edilməmiş sorğuları, sizinki prioritet olaraq göstərin.
Necə müəyyən etmək olar
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scantövsiyələr
Istifadə BİRLİK [hamısı] şərtlərin OR-bloklarının hər biri üçün alt sorğuları birləşdirmək.
Misal:
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;
Düzəliş:
(
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, больше и не надо
Tələb olunan 20 qeydin hamısının birinci blokda dərhal qəbul edilməsindən istifadə etdik, buna görə də daha "bahalı" Bitmap Heap Scan ilə ikincisi hətta icra edilmədi - sonda 22x daha sürətli, 44x daha az oxuyur!
Bu optimallaşdırma üsulu haqqında daha ətraflı hekayə konkret misallardan istifadə etməklə məqalələrdə oxumaq olar и .
Ümumiləşdirilmiş versiya bir neçə açar əsasında sifarişli seçim (və yalnız const/NULL cütü deyil) məqalədə müzakirə olunur .
#4: Biz çoxlu lazımsız şeylər oxuyuruq
Nə vaxt yaranır
Bir qayda olaraq, artıq mövcud sorğuya "başqa filtr əlavə etmək" istədiyiniz zaman yaranır.
“Və sizdə eynisi yoxdur, amma sədəf düymələri ilə? " "Almaz qol" filmi
Məsələn, yuxarıdakı tapşırığı dəyişdirərək, məqsədindən asılı olmayaraq emal üçün ilk 20 ən köhnə "kritik" sorğunu göstərin.
Necə müəyyən etmək olar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
tövsiyələr
İxtisaslaşmış [daha] yaradın WHERE şərti ilə indeks və ya indeksə əlavə sahələri daxil edin.
Filtr vəziyyəti sizin məqsədləriniz üçün "statik" olarsa - yəni genişlənməsini nəzərdə tutmur Gələcəkdə dəyərlərin siyahısı - WHERE indeksindən istifadə etmək daha yaxşıdır. Müxtəlif boolean/enum statusları bu kateqoriyaya yaxşı uyğun gəlir.
Əgər süzgəc vəziyyəti müxtəlif mənalar ala bilər, onda bu sahələrlə indeksi genişləndirmək daha yaxşıdır - BitmapVə yuxarıdakı vəziyyətdə olduğu kimi.
Misal:
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;
Düzəliş:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Gördüyünüz kimi, filtrləmə plandan tamamilə itdi və sorğu oldu 5 dəfə sürətli.
# 5: seyrək masa
Nə vaxt yaranır
Cədvəldəki çoxlu sayda yeniləmələr/silinmələr çoxlu sayda "ölü" qeydlərin vəziyyətinə gətirib çıxardıqda, öz tapşırıqların işlənməsi növbənizi yaratmaq üçün müxtəlif cəhdlər.
Necə müəyyən etmək olar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
tövsiyələr
Müntəzəm olaraq əl ilə həyata keçirin VAKUUM [TAM] və ya kifayət qədər tez-tez məşq edin daxil olmaqla, onun parametrlərini dəqiq tənzimləməklə .
Əksər hallarda, bu cür problemlər, müzakirə olunanlar kimi iş məntiqindən zəng edərkən sorğunun zəif tərkibi səbəbindən yaranır .
Ancaq başa düşməlisiniz ki, hətta VACUUM FULL həmişə kömək edə bilməz. Belə hallar üçün məqalədəki alqoritmlə tanış olmağa dəyər .
# 6: İndeksin “ortasından” oxumaq
Nə vaxt yaranır
Deyəsən, bir az oxuduq və hər şey indeksləşdirildi və heç kimi həddindən artıq filtrləmədik - amma yenə də istədiyimizdən xeyli çox səhifə oxuyuruq.
Necə müəyyən etmək olar
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
tövsiyələr
İstifadə olunan indeksin strukturuna və sorğuda göstərilən əsas sahələrə yaxından baxın - çox güman ki indeksin bir hissəsi göstərilmir. Çox güman ki, oxşar indeks yaratmalı olacaqsınız, lakin prefiks sahələri olmadan və ya .
Misal:
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;
İndeksə görə də hər şey yaxşı görünür, amma bu, nədənsə şübhəlidir - oxunan 20 qeydin hər biri üçün 4 səhifə məlumat, hər qeyd üçün 32 KB çıxarmalı olduq - bu qalın deyilmi? Və indeks adı tbl_fk_org_fk_cli_idx düşündürücü.
Düzəliş:
CREATE INDEX ON tbl(fk_cli);
Birdən - 10 dəfə sürətli, oxumaq üçün isə 4 dəfə az!
İndekslərin səmərəsiz istifadəsi hallarının digər nümunələri məqalədə göstərilə bilər .
№7: CTE × CTE
Nə vaxt yaranır
Xahişdə "yağlı" CTE qazandı müxtəlif masalardan, və sonra onların arasında bunu etmək qərarına gəlib JOIN.
İş v12-dən aşağı versiyalar və ya sorğular üçün uyğundur WITH MATERIALIZED.
Necə müəyyən etmək olar
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
tövsiyələr
Sorğunu diqqətlə təhlil edin - və ? Əgər belədirsə, onda hstore/json-da "lüğət" tətbiq edin -də təsvir olunan modelə görə .
# 8: diskə dəyişdirin (müvəqqəti yazılır)
Nə vaxt yaranır
Çoxlu sayda qeydlərin birdəfəlik işlənməsi (çeşidlənməsi və ya unikallaşdırılması) bunun üçün ayrılmış yaddaşa uyğun gəlmir.
Necə müəyyən etmək olar
-> *
&& temp written > 0tövsiyələr
Əməliyyat tərəfindən istifadə olunan yaddaşın miqdarı parametrin göstərilən dəyərini çox aşmazsa , onu düzəltməyə dəyər. Siz dərhal hər kəs üçün konfiqurasiya edə bilərsiniz, ya da keçə bilərsiniz SET [LOCAL] xüsusi sorğu/əməliyyat üçün.
Misal:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Düzəliş:
SET work_mem = '128MB'; -- перед выполнением запроса
Aydın səbəblərə görə, disk deyil, yalnız yaddaş istifadə edilərsə, sorğu daha sürətli yerinə yetiriləcəkdir. Eyni zamanda, HDD-dən yükün bir hissəsi də çıxarılır.
Ancaq başa düşməlisiniz ki, siz həmişə çoxlu və çoxlu yaddaş ayıra bilməyəcəksiniz - sadəcə hər kəs üçün kifayət etməyəcəksiniz.
# 9: əlaqəsiz statistika
Nə vaxt yaranır
Bir anda verilənlər bazasına çox şey tökdülər, lakin onu qovmağa vaxtları yox idi ANALYZE.
Necə müəyyən etmək olar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10tövsiyələr
Onu həyata keçirin ANALYZE.
Bu vəziyyət daha ətraflı təsvir edilmişdir .
# 10: "bir şey səhv getdi"
Nə vaxt yaranır
Rəqabətli sorğunun tətbiq etdiyi kilid üçün gözləmə var idi və ya kifayət qədər CPU/hipervisor aparat resursları yox idi.
Necə müəyyən etmək olar
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
tövsiyələr
Xarici istifadə edin monitorinq sistemi bloklanması və ya anormal resurs istehlakı üçün server. Artıq yüzlərlə server üçün bu prosesi təşkil etmək versiyamız haqqında danışdıq и .
Mənbə: www.habr.com