Resipi untuk Pertanyaan SQL Sakit

Sebulan yang lalu kami umumkan explain.tensor.ru - awam perkhidmatan untuk menghuraikan dan menggambarkan rancangan pertanyaan kepada PostgreSQL.

Anda telah menggunakannya lebih 6000 kali sejak itu, tetapi salah satu ciri berguna yang mungkin tidak disedari ialah petunjuk struktur, yang kelihatan seperti ini:

Dengarkan mereka dan permintaan anda akan "menjadi selembut sutera". 🙂

Tetapi serius, banyak situasi yang membuat permintaan perlahan dan "rakus" dari segi sumber, adalah tipikal dan boleh dikenali oleh struktur dan data pelan.

Dalam kes ini, setiap pembangun individu tidak perlu mencari pilihan pengoptimuman sendiri, bergantung semata-mata pada pengalamannya sendiri - kita boleh memberitahu dia apa yang berlaku di sini, apakah sebabnya, dan bagaimana untuk mendapatkan penyelesaian. Itulah yang kami lakukan.

Mari kita lihat dengan lebih dekat kes ini - bagaimana ia ditakrifkan dan apakah pengesyoran yang dibawa olehnya.

Untuk rendaman yang lebih baik dalam topik, anda boleh mula-mula mendengar blok yang sepadan daripada laporan saya di PGConf.Russia 2020, dan hanya kemudian pergi ke analisis terperinci bagi setiap contoh:

#1: indeks "undersorting"

Apabila timbul

Tunjukkan invois terakhir untuk pelanggan "LLC Kolokolchik".

Bagaimana untuk mengenal pasti

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

Cadangan

Indeks digunakan kembangkan dengan medan isihan.

Contoh:

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;

[lihat explain.tensor.ru]

Anda boleh melihat dengan serta-merta bahawa lebih daripada 100 rekod telah ditolak oleh indeks, yang kemudiannya semua diisih, dan kemudian satu-satunya yang tinggal.

Kami membetulkan:

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

[lihat explain.tensor.ru]

Walaupun pada sampel primitif sedemikian - 8.5x lebih pantas dan 33x lebih sedikit bacaan. Kesannya akan menjadi lebih jelas, lebih banyak "fakta" yang anda ada untuk setiap nilai. fk.

Saya perhatikan bahawa indeks sedemikian akan berfungsi sebagai indeks "awalan" tidak lebih buruk daripada yang sebelumnya untuk pertanyaan lain dengan fk, di mana menyusun mengikut pk bukan dan bukan (anda boleh membaca lebih lanjut tentang ini dalam artikel saya tentang mencari indeks yang tidak cekap). Khususnya, ia akan memberikan normal sokongan utama asing yang jelas oleh bidang ini.

#2: persimpangan indeks (BitmapAnd)

Apabila timbul

Tunjukkan semua kontrak untuk pelanggan "LLC Kolokolchik" yang dibuat bagi pihak "NJSC Lyutik".

Bagaimana untuk mengenal pasti

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

Cadangan

mewujudkan indeks komposit mengikut medan daripada kedua-dua sumber atau mengembangkan salah satu medan sedia ada daripada medan kedua.

Contoh:

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); -- отбор по конкретной паре

[lihat explain.tensor.ru]

Kami membetulkan:

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

[lihat explain.tensor.ru]

Di sini keuntungan adalah lebih kecil, kerana Bitmap Heap Scan agak berkesan dengan sendirinya. Tetapi bagaimanapun 7x lebih pantas dan 2.5x lebih sedikit bacaan.

#3: Menggabungkan Indeks (BitmapOr)

Apabila timbul

Tunjukkan 20 permintaan "sendiri" tertua pertama atau belum ditetapkan untuk pemprosesan, dengan keutamaan sendiri.

Bagaimana untuk mengenal pasti

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

Cadangan

Guna KESATUAN [SEMUA] untuk menggabungkan subkueri bagi setiap blok syarat ATAU.

Contoh:

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;

[lihat explain.tensor.ru]

Kami membetulkan:

(
  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, больше и не надо

[lihat explain.tensor.ru]

Kami mengambil kesempatan daripada fakta bahawa semua 20 rekod yang diperlukan telah diperoleh dengan segera di blok pertama, jadi yang kedua, dengan Imbasan Timbunan Bitmap yang lebih "mahal", tidak dilaksanakan - akibatnya 22x lebih pantas, 44x lebih sedikit bacaan!

Cerita yang lebih terperinci tentang kaedah pengoptimuman ini pada contoh konkrit boleh dibaca dalam artikel Antipattern PostgreSQL: JOIN dan OR Memudaratkan и Antipattern PostgreSQL: Kisah Penambahbaikan Berulang Carian mengikut Nama, atau "Mengoptimumkan Ke belakang dan ke belakang".

Versi umum pemilihan yang diperintahkan oleh beberapa kekunci (dan bukan hanya untuk pasangan const/NULL) dibincangkan dalam artikel SQL HowTo: tulis gelung sementara terus dalam pertanyaan, atau "Tiga hala asas".

#4: Kami membaca terlalu banyak

Apabila timbul

Sebagai peraturan, ia berlaku apabila anda ingin "melampirkan penapis lain" pada permintaan sedia ada.

"Dan anda tidak mempunyai perkara yang sama, tetapi dengan butang mutiara? " filem "Tangan Berlian"

Contohnya, mengubah suai tugasan di atas, tunjukkan 20 permintaan "kritikal" tertua pertama untuk diproses, tanpa mengira tujuannya.

Bagaimana untuk mengenal pasti

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

Cadangan

Buat [lebih] khusus indeks dengan klausa WHERE atau masukkan medan tambahan dalam indeks.

Jika keadaan penapisan adalah "statik" untuk tugasan anda - iaitu tidak termasuk pengembangan senarai nilai pada masa hadapan - lebih baik menggunakan indeks WHERE. Pelbagai status boolean/enum sesuai dengan kategori ini.

Jika keadaan penapisan boleh mengambil nilai yang berbeza, adalah lebih baik untuk mengembangkan indeks dengan medan ini - seperti dalam situasi dengan BitmapDan di atas.

Contoh:

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;

[lihat explain.tensor.ru]

Kami membetulkan:

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

[lihat explain.tensor.ru]

Seperti yang anda lihat, penapisan daripada pelan telah hilang sepenuhnya, dan permintaan telah menjadi 5 kali lebih cepat.

#5: jadual jarang

Apabila timbul

Pelbagai percubaan untuk membuat giliran pemprosesan tugas anda sendiri, apabila sejumlah besar kemas kini / pemadaman rekod di atas meja membawa kepada situasi sejumlah besar rekod "mati".

Bagaimana untuk mengenal pasti

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

Cadangan

Secara manual dijalankan dengan kerap VACUUM [PENUH] atau mencapai pemprosesan yang cukup kerap autovakum dengan memperhalusi parameternya, termasuk untuk jadual tertentu.

Dalam kebanyakan kes, masalah sedemikian disebabkan oleh reka letak pertanyaan yang lemah apabila dipanggil daripada logik perniagaan, seperti yang dibincangkan dalam Antipattern PostgreSQL: memerangi gerombolan "mati".

Tetapi kita mesti faham bahawa walaupun VACUUM FULL tidak selalu dapat membantu. Untuk kes sedemikian, anda harus membiasakan diri dengan algoritma dari artikel tersebut. DBA: apabila VACUUM berlalu, kami membersihkan meja secara manual.

#6: membaca dari "tengah" indeks

Apabila timbul

Nampaknya mereka membaca sedikit, dan semuanya telah diindeks, dan mereka tidak menapis sesiapa tambahan - tetapi tetap, lebih banyak halaman dibaca daripada yang kita mahukan.

Bagaimana untuk mengenal pasti

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

Cadangan

Lihat dengan teliti struktur indeks yang digunakan dan medan utama yang dinyatakan dalam pertanyaan - kemungkinan besar, bahagian indeks tidak ditetapkan. Anda berkemungkinan besar perlu mencipta indeks yang serupa, tetapi tanpa medan awalan, atau belajar untuk mengulangi nilai mereka.

Contoh:

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;

[lihat explain.tensor.ru]

Segala-galanya nampaknya baik-baik saja, walaupun dari segi indeks, tetapi entah bagaimana mencurigakan - untuk setiap satu daripada 20 rekod yang dibaca, 4 muka surat data terpaksa ditolak, 32KB setiap rekod - bukankah ia tebal? Ya dan nama indeks tbl_fk_org_fk_cli_idx membawa kepada pemikiran.

Kami membetulkan:

CREATE INDEX ON tbl(fk_cli);

[lihat explain.tensor.ru]

Tiba-tiba - 10 kali lebih cepat dan 4 kali lebih sedikit untuk membaca!

Untuk lebih banyak contoh penggunaan indeks yang tidak cekap, lihat artikel DBA: cari indeks yang tidak berguna.

#7: CTE × CTE

Apabila timbul

Dalam permintaan menjaringkan CTE "gemuk". daripada jadual yang berbeza, dan kemudian memutuskan untuk melakukan antara mereka JOIN.

Kes ini berkaitan untuk versi di bawah v12 atau permintaan dengan WITH MATERIALIZED.

Bagaimana untuk mengenal pasti

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

Cadangan

Analisis permintaan dengan teliti adakah CTE diperlukan di sini sama sekali? Jika ya, maka gunakan "kamus" dalam hstore/json mengikut model yang diterangkan dalam Antipattern PostgreSQL: Kamus Hit Heavy JOIN.

#8: tukar ke cakera (tempoh ditulis)

Apabila timbul

Pemprosesan sekali sahaja (isih atau keunikan) sejumlah besar rekod tidak sesuai dengan memori yang diperuntukkan untuk ini.

Bagaimana untuk mengenal pasti

-> *
   && temp written > 0

Cadangan

Jika jumlah memori yang digunakan oleh operasi tidak banyak melebihi nilai set parameter kerja_mem, ia harus diperbetulkan. Anda boleh serta-merta dalam konfigurasi untuk semua orang, atau anda boleh melalui SET [LOCAL] untuk permintaan/urus niaga tertentu.

Contoh:

SHOW work_mem;
-- "16MB"

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

[lihat explain.tensor.ru]

Kami membetulkan:

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

[lihat explain.tensor.ru]

Atas sebab yang jelas, jika hanya memori digunakan, dan bukan cakera, maka pertanyaan akan dilaksanakan dengan lebih cepat. Pada masa yang sama, sebahagian daripada beban juga dikeluarkan dari HDD.

Tetapi anda perlu memahami bahawa memperuntukkan banyak memori akan sentiasa tidak berfungsi sama ada - ia tidak akan mencukupi untuk semua orang.

#9: Statistik tidak relevan

Apabila timbul

Banyak yang dituangkan ke pangkalan sekaligus, tetapi mereka tidak mempunyai masa untuk menghalaunya ANALYZE.

Bagaimana untuk mengenal pasti

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

Cadangan

Belanja sama ANALYZE.

Keadaan ini diterangkan dengan lebih terperinci dalam PostgreSQL Antipatterns: statistik adalah ketua segala-galanya.

#10: "sesuatu telah berlaku"

Apabila timbul

Terdapat kunci menunggu permintaan bersaing, atau sumber perkakasan CPU/hypervisor tidak mencukupi.

Bagaimana untuk mengenal pasti

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

Cadangan

Gunakan alat luaran sistem pemantauan pelayan untuk menyekat atau penggunaan sumber yang tidak normal. Kami telah pun bercakap tentang versi kami mengatur proses ini untuk ratusan pelayan. di sini и di sini.

Sumber: www.habr.com