สูตรสำหรับการสืบค้น SQL ที่ป่วย

หลายเดือนก่อน เราประกาศแล้ว exp.tensor.ru - สาธารณะ บริการสำหรับการแยกวิเคราะห์และการแสดงภาพแผนการสืบค้น สู่ PostgreSQL

คุณใช้งานไปแล้วมากกว่า 6000 ครั้ง แต่คุณสมบัติที่มีประโยชน์อย่างหนึ่งที่อาจไม่มีใครสังเกตเห็นคือ เบาะแสเชิงโครงสร้างซึ่งมีลักษณะดังนี้:

ฟังพวกเขาแล้วคำขอของคุณจะ “ราบรื่นและเนียน” 🙂

แต่จริงๆ แล้ว มีหลายสถานการณ์ที่ทำให้คำขอช้าและกินทรัพยากรมาก เป็นเรื่องปกติและสามารถรับรู้ได้จากโครงสร้างและข้อมูลของแผน.

ในกรณีนี้ นักพัฒนาแต่ละคนไม่จำเป็นต้องมองหาตัวเลือกการเพิ่มประสิทธิภาพด้วยตนเอง โดยอาศัยประสบการณ์ของเขาเพียงอย่างเดียว เราสามารถบอกเขาได้ว่าเกิดอะไรขึ้นที่นี่ อะไรอาจเป็นเหตุผล และ วิธีการแก้ไขปัญหา. นั่นคือสิ่งที่เราทำ

มาดูกรณีเหล่านี้ให้ละเอียดยิ่งขึ้น - มีวิธีกำหนดอย่างไรและให้คำแนะนำอะไรบ้าง

เพื่อให้คุณสามารถดื่มด่ำกับหัวข้อได้ดียิ่งขึ้นคุณสามารถฟังบล็อกที่เกี่ยวข้องก่อนได้ รายงานของฉันที่ PGConf.Russia 2020จากนั้นจึงไปยังการวิเคราะห์โดยละเอียดของแต่ละตัวอย่าง:

#1: ดัชนี “การจัดเรียงต่ำกว่า”

เมื่อไร

แสดงใบแจ้งหนี้ล่าสุดสำหรับลูกค้า "LLC Kolokolchik"

วิธีการระบุ

-> 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;

[ดูที่ expand.tensor.ru]

คุณจะสังเกตเห็นได้ทันทีว่ามีการลบบันทึกมากกว่า 100 รายการออกจากดัชนี ซึ่งจากนั้นจะถูกจัดเรียงทั้งหมด และเหลือเพียงรายการเดียวเท่านั้น

การแก้ไข:

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

[ดูที่ expand.tensor.ru]

แม้แต่ในกลุ่มตัวอย่างดั้งเดิม - เร็วขึ้น 8.5 เท่า และอ่านน้อยลง 33 เท่า. ยิ่งคุณมี "ข้อเท็จจริง" สำหรับแต่ละค่ามากเท่าใด ผลที่ได้ก็จะยิ่งชัดเจนมากขึ้นเท่านั้น fk.

ฉันทราบว่าดัชนีดังกล่าวจะทำงานเป็นดัชนี "คำนำหน้า" ไม่แย่ไปกว่าเมื่อก่อนสำหรับข้อความค้นหาอื่นด้วย fkโดยเรียงลำดับตาม pk ไม่มีและไม่มี (คุณสามารถอ่านเพิ่มเติมเกี่ยวกับเรื่องนี้ได้ ในบทความของฉันเกี่ยวกับการค้นหาดัชนีที่ไม่มีประสิทธิภาพ). รวมทั้งจะให้ตามปกติ การสนับสนุนคีย์ต่างประเทศที่ชัดเจน บนสนามนี้

#2: จุดตัดดัชนี (BitmapAnd)

เมื่อไร

แสดงข้อตกลงทั้งหมดสำหรับลูกค้า “LLC Kolokolchik” ซึ่งสรุปในนามของ “NAO Buttercup”

วิธีการระบุ

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

[ดูที่ expand.tensor.ru]

การแก้ไข:

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

[ดูที่ expand.tensor.ru]

ผลตอบแทนที่นี่น้อยกว่า เนื่องจาก Bitmap Heap Scan ค่อนข้างมีประสิทธิภาพในตัวเอง แต่อย่างไรก็ตาม เร็วขึ้น 7 เท่า และอ่านน้อยลง 2.5 เท่า.

#3: รวมดัชนี (BitmapOr)

เมื่อไร

แสดงคำขอ “พวกเรา” ที่เก่าที่สุดหรือคำขอที่ยังไม่ได้มอบหมายสำหรับการประมวลผล 20 คำขอแรก โดยให้ความสำคัญกับคำขอของคุณเป็นหลัก

วิธีการระบุ

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

แนะนำ

ที่จะใช้ ยูเนี่ยน [ทั้งหมด] เพื่อรวมแบบสอบถามย่อยสำหรับแต่ละเงื่อนไข OR

ตัวอย่าง:

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;

[ดูที่ expand.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, больше и не надо

[ดูที่ expand.tensor.ru]

เราใช้ประโยชน์จากความจริงที่ว่าได้รับบันทึกที่จำเป็นทั้งหมด 20 รายการทันทีในบล็อกแรก ดังนั้นบล็อกที่สองซึ่งมี Bitmap Heap Scan ที่ "แพงกว่า" จึงไม่ได้ดำเนินการด้วยซ้ำ - ท้ายที่สุด เร็วขึ้น 22 เท่า อ่านน้อยลง 44 เท่า!

เรื่องราวโดยละเอียดเพิ่มเติมเกี่ยวกับวิธีการเพิ่มประสิทธิภาพนี้ โดยใช้ตัวอย่างเฉพาะ สามารถอ่านได้ในบทความ PostgreSQL Antipatterns: JOIN และ OR ที่เป็นอันตราย и PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา".

รุ่นทั่วไป เรียงลำดับการเลือกตามหลายคีย์ (และไม่ใช่แค่คู่ const/NULL) ที่ถูกกล่าวถึงในบทความ SQL HowTo: เขียน while-loop โดยตรงในแบบสอบถาม หรือ "Elementary three-way".

#4: เราอ่านสิ่งที่ไม่จำเป็นมากมาย

เมื่อไร

ตามกฎแล้ว มันเกิดขึ้นเมื่อคุณต้องการ "แนบตัวกรองอื่น" กับคำขอที่มีอยู่แล้ว

“และคุณไม่มีอันเดียวกันแต่ ด้วยกระดุมมุก? " ภาพยนตร์เรื่อง "แขนเพชร"

ตัวอย่างเช่น การแก้ไขงานด้านบน แสดงคำขอ "สำคัญ" ที่เก่าที่สุด 20 รายการแรกสำหรับการประมวลผล โดยไม่คำนึงถึงวัตถุประสงค์

วิธีการระบุ

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

แนะนำ

สร้างความเชี่ยวชาญ [เพิ่มเติม] ดัชนีที่มีเงื่อนไข WHERE หรือรวมฟิลด์เพิ่มเติมในดัชนี

หากเงื่อนไขตัวกรองเป็น "คงที่" สำหรับวัตถุประสงค์ของคุณ นั่นก็คือ ไม่ได้หมายความถึงการขยายตัว รายการค่าในอนาคต - ควรใช้ดัชนี WHERE ดีกว่า สถานะบูลีน/แจงนับต่างๆ เหมาะสมกับหมวดหมู่นี้

หากสภาพการกรอง สามารถสื่อความหมายได้หลากหลายดังนั้นจึงเป็นการดีกว่าที่จะขยายดัชนีด้วยฟิลด์เหล่านี้ - เช่นเดียวกับในสถานการณ์ที่มีบิตแมปและด้านบน

ตัวอย่าง:

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;

[ดูที่ expand.tensor.ru]

การแก้ไข:

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

[ดูที่ expand.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 Antipatterns: ต่อสู้กับฝูง "คนตาย".

แต่คุณต้องเข้าใจว่าแม้แต่ 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;

[ดูที่ expand.tensor.ru]

ดูเหมือนทุกอย่างจะเรียบร้อยดีแม้จะดูตามดัชนีก็ตาม แต่ก็น่าสงสัยเช่นกัน - สำหรับแต่ละบันทึกจาก 20 บันทึกที่อ่าน เราต้องลบข้อมูล 4 หน้า จำนวน 32KB ต่อบันทึก - ไม่ใช่ตัวหนาใช่ไหม และชื่อดัชนี tbl_fk_org_fk_cli_idx กระตุ้นความคิด

การแก้ไข:

CREATE INDEX ON tbl(fk_cli);

[ดูที่ expand.tensor.ru]

กะทันหัน - เร็วขึ้น 10 เท่า และอ่านน้อยลง 4 เท่า!

ตัวอย่างอื่น ๆ ของสถานการณ์การใช้ดัชนีอย่างไม่มีประสิทธิภาพสามารถดูได้ในบทความ DBA: ค้นหาดัชนีที่ไร้ประโยชน์.

#7: ซีทีอี × ซีทีอี

เมื่อไร

ตามคำขอ ได้คะแนน CTE “อ้วน” จากโต๊ะต่างๆ แล้วจึงตัดสินใจทำระหว่างกัน JOIN.

กรณีนี้เกี่ยวข้องกับเวอร์ชันที่ต่ำกว่า v12 หรือคำขอด้วย WITH MATERIALIZED.

วิธีการระบุ

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

แนะนำ

วิเคราะห์คำขออย่างรอบคอบ - และ CTE จำเป็นที่นี่เลยหรือเปล่า?? ถ้าใช่ก็แล้วกัน ใช้ "พจนานุกรม" ใน hstore/json ตามแบบที่อธิบายไว้ใน PostgreSQL Antipatterns: มาเข้าร่วมกับพจนานุกรมกัน.

#8: สลับไปยังดิสก์ (เขียนชั่วคราว)

เมื่อไร

การประมวลผลครั้งเดียว (การเรียงลำดับหรือการระบุเฉพาะ) ของบันทึกจำนวนมากไม่พอดีกับหน่วยความจำที่จัดสรรไว้สำหรับสิ่งนี้

วิธีการระบุ

-> *
   && temp written > 0

แนะนำ

หากจำนวนหน่วยความจำที่ใช้โดยการดำเนินการไม่เกินค่าที่ระบุของพารามิเตอร์อย่างมาก work_memมันคุ้มค่าที่จะแก้ไข คุณสามารถกำหนดค่าสำหรับทุกคนได้ทันทีหรือผ่านก็ได้ SET [LOCAL] สำหรับคำขอ/ธุรกรรมเฉพาะ

ตัวอย่าง:

SHOW work_mem;
-- "16MB"

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

[ดูที่ expand.tensor.ru]

การแก้ไข:

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

[ดูที่ expand.tensor.ru]

ด้วยเหตุผลที่ชัดเจน หากใช้เพียงหน่วยความจำเท่านั้น ไม่ใช่ดิสก์ การสืบค้นจะดำเนินการเร็วขึ้นมาก ในขณะเดียวกัน โหลดบางส่วนจาก HDD ก็จะถูกลบออกด้วย

แต่คุณต้องเข้าใจว่าคุณไม่สามารถจัดสรรหน่วยความจำจำนวนมากได้เสมอไป เพราะหน่วยความจำนั้นไม่เพียงพอสำหรับทุกคน

#9: สถิติที่ไม่เกี่ยวข้อง

เมื่อไร

พวกเขาหลั่งไหลเข้าสู่ฐานข้อมูลจำนวนมากในคราวเดียว แต่ไม่มีเวลาที่จะขับไล่มันออกไป ANALYZE.

วิธีการระบุ

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

แนะนำ

ดำเนินการออก ANALYZE.

สถานการณ์นี้อธิบายรายละเอียดเพิ่มเติมใน PostgreSQL Antipatterns: สถิติคือทุกสิ่ง.

#10: “มีบางอย่างผิดพลาด”

เมื่อไร

มีการรอการล็อคที่กำหนดโดยคำขอที่แข่งขันกัน หรือมีทรัพยากรฮาร์ดแวร์ CPU/ไฮเปอร์ไวเซอร์ไม่เพียงพอ

วิธีการระบุ

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

แนะนำ

ใช้ภายนอก ระบบการตรวจสอบ เซิร์ฟเวอร์สำหรับการบล็อกหรือการใช้ทรัพยากรที่ผิดปกติ เราได้พูดคุยเกี่ยวกับเวอร์ชันของเราในการจัดการกระบวนการนี้สำหรับเซิร์ฟเวอร์หลายร้อยเครื่องแล้ว ที่นี่ и ที่นี่.

ที่มา: will.com