🥇การล้างบันทึกโคลนจากตารางโดยไม่มี PK

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

ตัวอย่างเช่น ค่าของเมตริกตามลำดับเวลาจะถูกเขียนลงใน PostgreSQL โดยใช้สตรีม COPY จากนั้นเกิดความล้มเหลวอย่างกะทันหัน และส่วนหนึ่งของข้อมูลที่เหมือนกันทั้งหมดก็มาถึงอีกครั้ง

จะกำจัดฐานข้อมูลของโคลนที่ไม่จำเป็นได้อย่างไร?

เมื่อพีเคไม่ใช่ตัวช่วย

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

ตัวอย่างเช่น หากความแม่นยำของระบบต้นทางสูงกว่าความแม่นยำของฟิลด์ในฐานข้อมูล:

metric   | ts                  | data
--------------------------------------------------
cpu.busy | 2019-12-20 00:00:00 | {"value" : 12.34}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 10}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 11.2}
cpu.busy | 2019-12-20 00:00:03 | {"value" : 15.7}

คุณสังเกตเห็นไหม? การนับถอยหลังแทนที่จะเป็น 00:00:02 ถูกบันทึกในฐานข้อมูลโดยใช้เวลา ts วินาทีก่อนหน้านี้ แต่ยังคงค่อนข้างใช้ได้จากมุมมองของแอปพลิเคชัน (ท้ายที่สุดแล้วค่าข้อมูลจะแตกต่างกัน!)

แน่นอนคุณสามารถทำได้ PK(เมตริก, ts) - แต่จากนั้นเราจะได้รับข้อขัดแย้งในการแทรกข้อมูลที่ถูกต้อง

สามารถทำได้ PK(เมตริก, ts, ข้อมูล) - แต่สิ่งนี้จะเพิ่มระดับเสียงอย่างมากซึ่งเราจะไม่ใช้

ดังนั้นตัวเลือกที่ถูกต้องที่สุดคือการสร้างดัชนีที่ไม่ซ้ำกันเป็นประจำ (เมตริก, ts) และจัดการกับปัญหาที่เกิดขึ้นในภายหลังหากเกิดขึ้น

"สงครามโคลนได้เริ่มต้นขึ้นแล้ว"

มีอุบัติเหตุบางอย่างเกิดขึ้น และตอนนี้เราต้องทำลายบันทึกโคลนจากตาราง

มาสร้างแบบจำลองข้อมูลต้นฉบับกันเถอะ:

CREATE TABLE tbl(k text, v integer);

INSERT INTO tbl
VALUES
  ('a', 1)
, ('a', 3)
, ('b', 2)
, ('b', 2) -- oops!
, ('c', 3)
, ('c', 3) -- oops!!
, ('c', 3) -- oops!!
, ('d', 4)
, ('e', 5)
;

มือของเราสั่นสามครั้ง Ctrl+V ค้าง และตอนนี้...

ก่อนอื่น เรามาทำความเข้าใจว่าตารางของเราอาจมีขนาดใหญ่มาก ดังนั้นหลังจากที่เราพบโคลนทั้งหมดแล้ว ขอแนะนำให้เรา "สะกิดนิ้ว" อย่างแท้จริงเพื่อลบ บันทึกเฉพาะโดยไม่ต้องค้นหาซ้ำ.

และมีวิธีเช่นนี้ - สิ่งนี้ ที่อยู่โดย ctidตัวระบุทางกายภาพของบันทึกเฉพาะ

นั่นคือก่อนอื่น เราต้องรวบรวม ctid ของบันทึกในบริบทของเนื้อหาทั้งหมดของแถวตาราง ตัวเลือกที่ง่ายที่สุดคือแปลงทั้งบรรทัดเป็นข้อความ:

SELECT
  T::text
, array_agg(ctid) ctids
FROM
  tbl T
GROUP BY
  1;

t     | ctids
---------------------------------
(e,5) | {"(0,9)"}
(d,4) | {"(0,8)"}
(c,3) | {"(0,5)","(0,6)","(0,7)"}
(b,2) | {"(0,3)","(0,4)"}
(a,3) | {"(0,2)"}
(a,1) | {"(0,1)"}

เป็นไปได้ไหมที่จะไม่หล่อ?โดยหลักการแล้วมันเป็นไปได้ในกรณีส่วนใหญ่ จนกว่าคุณจะเริ่มใช้เขตข้อมูลในตารางนี้ ประเภทที่ไม่มีตัวดำเนินการความเท่าเทียมกัน:

CREATE TABLE tbl(k text, v integer, x point);
SELECT
  array_agg(ctid) ctids
FROM
  tbl T
GROUP BY
  T;
-- ERROR:  could not identify an equality operator for type tbl

ใช่ เราจะเห็นได้ทันทีว่าหากมีมากกว่าหนึ่งรายการในอาร์เรย์ รายการเหล่านี้ล้วนเป็นโคลน ปล่อยพวกเขาไปเถอะ:

SELECT
  unnest(ctids[2:])
FROM
  (
    SELECT
      array_agg(ctid) ctids
    FROM
      tbl T
    GROUP BY
      T::text
  ) T;

unnest
------
(0,6)
(0,7)
(0,4)

สำหรับผู้ที่ชอบเขียนให้สั้นลงคุณยังสามารถเขียนได้ดังนี้:

SELECT
  unnest((array_agg(ctid))[2:])
FROM
  tbl T
GROUP BY
  T::text;

เนื่องจากค่าของสตริงที่ทำให้ซีเรียลไลซ์นั้นไม่น่าสนใจสำหรับเรา เราจึงโยนมันออกจากคอลัมน์ที่ส่งคืนของแบบสอบถามย่อย

เหลืออีกเพียงเล็กน้อยที่ต้องทำ - ให้ DELETE ใช้ชุดที่เราได้รับ:

DELETE FROM
  tbl
WHERE
  ctid = ANY(ARRAY(
    SELECT
      unnest(ctids[2:])
    FROM
      (
        SELECT
          array_agg(ctid) ctids
        FROM
          tbl T
        GROUP BY
          T::text
      ) T
  )::tid[]);

มาตรวจสอบตัวเราเอง:

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

ใช่ ทุกอย่างถูกต้อง: ระเบียน 3 รายการของเราถูกเลือกสำหรับการสแกน Seq เพียงอย่างเดียวของทั้งตาราง และใช้โหนด Delete เพื่อค้นหาข้อมูล ผ่านครั้งเดียวด้วย Tid Scan:

->  Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1)
      TID Cond: (ctid = ANY ($0))

หากคุณล้างบันทึกจำนวนมาก อย่าลืมเรียกใช้ VACUUM ANALYZE.

เรามาตรวจสอบตารางที่ใหญ่กว่าและมีจำนวนซ้ำกันมากขึ้น:

TRUNCATE TABLE tbl;

INSERT INTO tbl
SELECT
  chr(ascii('a'::text) + (random() * 26)::integer) k -- a..z
, (random() * 100)::integer v -- 0..99
FROM
  generate_series(1, 10000) i;

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

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

ที่มา: will.com

การล้างบันทึกโคลนจากตารางที่ไม่มี PK

เมื่อพีเคไม่ใช่ตัวช่วย

"สงครามโคลนได้เริ่มต้นขึ้นแล้ว"

เพิ่มความคิดเห็น ยกเลิกการตอบ