Vor einigen Monaten - öffentlich zu PostgreSQL.
Sie haben es bereits mehr als 6000 Mal verwendet, aber eine praktische Funktion ist möglicherweise unbemerkt geblieben strukturelle Hinweise, die etwa so aussehen:
Hören Sie ihnen zu und Ihre Wünsche werden „glatt und seidig“ werden. 🙂
Aber im Ernst, es gibt viele Situationen, die eine Anfrage langsam und ressourcenintensiv machen sind typisch und anhand der Struktur und Daten des Plans erkennbar.
In diesem Fall muss nicht jeder einzelne Entwickler alleine nach einer Optimierungsmöglichkeit suchen und sich ausschließlich auf seine Erfahrung verlassen – wir können ihm sagen, was hier passiert, was der Grund sein könnte und wie man an eine Lösung herangeht. Das haben wir getan.
Schauen wir uns diese Fälle genauer an – wie sie definiert sind und zu welchen Empfehlungen sie führen.
Um besser in die Thematik einzutauchen, können Sie sich zunächst den entsprechenden Block von anhören , und erst dann mit einer detaillierten Analyse jedes Beispiels fortfahren:
#1: Index „Untersortierung“
Wann entsteht
Zeigen Sie die neueste Rechnung für den Kunden „LLC Kolokolchik“ an.
So identifizieren Sie sich
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Empfehlungen
Verwendeter Index mit Sortierfeldern erweitern.
Beispiel:
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;
Man merkt sofort, dass mehr als 100 Datensätze aus dem Index abgezogen wurden, die dann alle sortiert wurden und dann nur noch einer übrig blieb.
Korrigieren:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Selbst bei einer so primitiven Probe - 8.5-mal schneller und 33-mal weniger Lesevorgänge. Je mehr „Fakten“ Sie für jeden Wert haben, desto offensichtlicher ist der Effekt fk.
Ich stelle fest, dass ein solcher Index als „Präfix“-Index für andere Abfragen mit nicht schlechter funktioniert als zuvor fk, wo sortieren nach pk Das gab es nicht und das gibt es auch nicht (Sie können mehr darüber lesen ). Einschließlich wird es normal zur Verfügung gestellt explizite Fremdschlüsselunterstützung auf diesem Gebiet.
#2: Indexschnittpunkt (BitmapAnd)
Wann entsteht
Alle Verträge für den Kunden „LLC Kolokolchik“ anzeigen, die im Auftrag von „NAO Buttercup“ abgeschlossen wurden.
So identifizieren Sie sich
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanEmpfehlungen
Schaffen Composite-Index um Felder aus beiden Originalen erweitern oder eines der vorhandenen mit Feldern aus dem zweiten erweitern.
Beispiel:
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); -- отбор по конкретной паре
Korrigieren:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Der Nutzen ist hier geringer, da der Bitmap-Heap-Scan allein recht effektiv ist. Aber wie auch immer 7-mal schneller und 2.5-mal weniger Lesevorgänge.
#3: Indizes zusammenführen (BitmapOr)
Wann entsteht
Zeigen Sie die ersten 20 ältesten „uns“ oder nicht zugewiesenen Anfragen zur Bearbeitung an, wobei Ihre Anfragen Vorrang haben.
So identifizieren Sie sich
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanEmpfehlungen
Zu verwenden UNION [ALLE] um Unterabfragen für jeden der ODER-Blöcke von Bedingungen zu kombinieren.
Beispiel:
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;
Korrigieren:
(
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, больше и не надо
Wir machten uns die Tatsache zunutze, dass alle 20 benötigten Datensätze sofort im ersten Block empfangen wurden, sodass der zweite, mit dem „teureren“ Bitmap Heap Scan, am Ende gar nicht erst ausgeführt wurde 22x schneller, 44x weniger Lesevorgänge!
Eine detailliertere Geschichte über diese Optimierungsmethode an konkreten Beispielen kann in Artikeln nachgelesen werden и .
Verallgemeinerte Version geordnete Auswahl anhand mehrerer Schlüssel (und nicht nur das const/NULL-Paar) wird im Artikel besprochen .
#4: Wir lesen viele unnötige Dinge
Wann entsteht
In der Regel entsteht es, wenn man einer bereits bestehenden Anfrage „einen weiteren Filter anhängen“ möchte.
„Und du hast nicht das Gleiche, aber mit Perlmuttknöpfen? » Film „Der Diamantarm“
Wenn Sie beispielsweise die obige Aufgabe ändern, werden die ersten 20 ältesten „kritischen“ Anfragen zur Verarbeitung angezeigt, unabhängig von ihrem Zweck.
So identifizieren Sie sich
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Empfehlungen
Erstellen Sie [mehr] spezialisierter Index mit WHERE-Bedingung oder zusätzliche Felder in den Index aufnehmen.
Das heißt, wenn die Filterbedingung für Ihre Zwecke „statisch“ ist bedeutet keine Erweiterung Liste der Werte in der Zukunft - es ist besser, einen WHERE-Index zu verwenden. Verschiedene boolesche/Enum-Status passen gut in diese Kategorie.
Wenn die Filterbedingung kann unterschiedliche Bedeutungen annehmen, dann ist es besser, den Index um diese Felder zu erweitern – wie in der Situation mit BitmapAnd oben.
Beispiel:
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;
Korrigieren:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Wie Sie sehen, ist die Filterung vollständig aus dem Plan verschwunden und die Anfrage ist geworden 5 Mal schneller.
#5: spärlicher Tisch
Wann entsteht
Verschiedene Versuche, eine eigene Aufgabenverarbeitungswarteschlange zu erstellen, wenn eine große Anzahl von Aktualisierungen/Löschungen von Datensätzen in der Tabelle zu einer Situation mit einer großen Anzahl „toter“ Datensätze führt.
So identifizieren Sie sich
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Empfehlungen
Regelmäßig manuell durchführen VAKUUM [VOLL] oder ausreichend häufiges Training erreichen durch Feinabstimmung seiner Parameter, einschließlich .
In den meisten Fällen werden solche Probleme durch eine schlechte Abfragezusammensetzung beim Aufruf aus der Geschäftslogik verursacht, wie in beschrieben .
Aber Sie müssen verstehen, dass selbst VAKUUM VOLL möglicherweise nicht immer hilft. In solchen Fällen lohnt es sich, sich mit dem Algorithmus aus dem Artikel vertraut zu machen .
#6: Lesen Sie von der „Mitte“ des Index aus
Wann entsteht
Es scheint, dass wir ein wenig gelesen haben, alles indiziert war und wir niemanden übermäßig herausgefiltert haben – aber trotzdem haben wir deutlich mehr Seiten gelesen, als uns lieb war.
So identifizieren Sie sich
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Empfehlungen
Schauen Sie sich die Struktur des verwendeten Index und die in der Abfrage angegebenen Schlüsselfelder genau an – höchstwahrscheinlich Ein Teil des Index ist nicht festgelegt. Höchstwahrscheinlich müssen Sie einen ähnlichen Index erstellen, jedoch ohne die Präfixfelder oder .
Beispiel:
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;
Alles scheint in Ordnung zu sein, auch laut Index, aber es ist irgendwie verdächtig – für jeden der 20 gelesenen Datensätze mussten wir 4 Seiten Daten, 32 KB pro Datensatz, abziehen – ist das nicht dreist? Und der Indexname tbl_fk_org_fk_cli_idx regt zum Nachdenken an.
Korrigieren:
CREATE INDEX ON tbl(fk_cli);
Plötzlich - 10-mal schneller und 4-mal weniger Leseaufwand!
Weitere Beispiele für Situationen ineffizienter Verwendung von Indizes finden Sie im Artikel .
#7: CTE × CTE
Wann entsteht
Auf Anfrage erzielte einen „fetten“ CTE von verschiedenen Tischen und beschloss dann, es zwischen ihnen zu tun JOIN.
Der Fall ist für Versionen unter v12 oder Anfragen mit relevant WITH MATERIALIZED.
So identifizieren Sie sich
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Empfehlungen
Analysieren Sie die Anfrage sorgfältig – und ? Wenn ja, dann Wenden Sie „Wörterbuch“ in hstore/json an nach dem in beschriebenen Modell .
#8: Auf Festplatte auslagern (temporär geschrieben)
Wann entsteht
Die einmalige Verarbeitung (Sortierung oder Eindeutigkeit) einer großen Anzahl von Datensätzen passt nicht in den dafür vorgesehenen Speicher.
So identifizieren Sie sich
-> *
&& temp written > 0Empfehlungen
Wenn die von der Operation verwendete Speichermenge den angegebenen Wert des Parameters nicht wesentlich überschreitet , es lohnt sich, es zu korrigieren. Sie können dies sofort in der Konfiguration für alle tun, oder Sie können durch SET [LOCAL] für eine bestimmte Anfrage/Transaktion.
Beispiel:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Korrigieren:
SET work_mem = '128MB'; -- перед выполнением запроса
Aus offensichtlichen Gründen wird die Abfrage viel schneller ausgeführt, wenn nur Speicher und keine Festplatte verwendet wird. Gleichzeitig wird auch ein Teil der Last von der Festplatte entfernt.
Sie müssen sich jedoch darüber im Klaren sein, dass Sie nicht immer sehr viel Speicher zuweisen können – es wird einfach nicht genug für alle da sein.
#9: irrelevante Statistiken
Wann entsteht
Sie haben viel auf einmal in die Datenbank geschüttet, hatten aber keine Zeit, es zu vertreiben ANALYZE.
So identifizieren Sie sich
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Empfehlungen
Führen Sie es aus ANALYZE.
Diese Situation wird ausführlicher beschrieben in .
#10: „Etwas ist schiefgelaufen“
Wann entsteht
Es wurde auf eine durch eine konkurrierende Anforderung verhängte Sperre gewartet, oder es waren nicht genügend CPU-/Hypervisor-Hardwareressourcen vorhanden.
So identifizieren Sie sich
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Empfehlungen
Externe verwenden Überwachungssystem Server wegen Blockierung oder abnormalem Ressourcenverbrauch. Wir haben bereits über unsere Version der Organisation dieses Prozesses für Hunderte von Servern gesprochen и .
Source: habr.com