Có những tình huống khi đến một bảng không có khóa chính hoặc một số chỉ mục duy nhất khác, do có sự giám sát, các bản sao hoàn chỉnh của các hồ sơ hiện có sẽ được đưa vào.
Ví dụ: các giá trị của số liệu theo trình tự thời gian được ghi vào PostgreSQL bằng luồng SAO CHÉP, sau đó xảy ra lỗi đột ngột và một phần dữ liệu hoàn toàn giống hệt sẽ xuất hiện trở lại.
Làm thế nào để loại bỏ cơ sở dữ liệu của các bản sao không cần thiết?
Khi PK không phải là người giúp đỡ
Cách dễ nhất là ngăn chặn tình huống như vậy xảy ra ngay từ đầu. Ví dụ: cuộn PRIMARY KEY. Nhưng điều này không phải lúc nào cũng có thể thực hiện được nếu không tăng khối lượng dữ liệu được lưu trữ.
Ví dụ: nếu độ chính xác của hệ thống nguồn cao hơn độ chính xác của trường trong cơ sở dữ liệu:
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}
Bạn có để ý không? Việc đếm ngược thay vì 00:00:02 đã được ghi lại trong cơ sở dữ liệu với ts một giây trước đó, nhưng vẫn khá hợp lệ theo quan điểm ứng dụng (xét cho cùng, các giá trị dữ liệu là khác nhau!).
Tất nhiên bạn có thể làm điều đó PK(số liệu, ts) - nhưng sau đó chúng ta sẽ gặp phải xung đột khi chèn dữ liệu hợp lệ.
Можно СЃРґРμР »Р ° С, Њ PK(số liệu, ts, dữ liệu) - nhưng điều này sẽ làm tăng đáng kể khối lượng của nó, thứ mà chúng tôi sẽ không sử dụng.
Do đó, lựa chọn đúng đắn nhất là tạo một chỉ mục không duy nhất thông thường (số liệu, ts) và giải quyết các vấn đề sau này nếu chúng phát sinh.
"Cuộc chiến vô tính đã bắt đầu"
Một tai nạn nào đó đã xảy ra và bây giờ chúng tôi phải hủy các bản sao bản sao khỏi bảng.
Hãy mô hình hóa dữ liệu gốc:
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)
;Ở đây tay chúng tôi run ba lần, Ctrl+V bị kẹt và bây giờ...
Đầu tiên, hãy hiểu rằng bảng của chúng ta có thể rất lớn, vì vậy sau khi tìm thấy tất cả các bản sao, chúng ta nên “chọc ngón tay” theo đúng nghĩa đen để xóa hồ sơ cụ thể mà không cần tìm kiếm lại chúng.
Và có một cách như vậy - đây , mã định danh vật lý của một bản ghi cụ thể.
Tức là trước hết chúng ta cần thu thập ctid của các bản ghi trong bối cảnh nội dung đầy đủ của hàng trong bảng. Tùy chọn đơn giản nhất là chuyển toàn bộ dòng thành văn bản:
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)"}
Có thể không casting được không?Về nguyên tắc, có thể thực hiện được trong hầu hết các trường hợp. Cho đến khi bạn bắt đầu sử dụng các trường trong bảng này các loại không có toán tử đẳng thức:
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
Vâng, chúng tôi thấy ngay rằng nếu có nhiều hơn một mục trong mảng thì tất cả đều là bản sao. Hãy để chúng lại:
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)Dành cho ai thích viết ngắnBạn cũng có thể viết nó như thế này:
SELECT
unnest((array_agg(ctid))[2:])
FROM
tbl T
GROUP BY
T::text;Vì bản thân giá trị của chuỗi được xê-ri hóa không được chúng tôi quan tâm nên chúng tôi chỉ cần loại nó ra khỏi các cột được trả về của truy vấn con.
Chỉ còn một chút việc phải làm - thực hiện DELETE bằng bộ chúng tôi nhận được:
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[]);Chúng ta hãy tự kiểm tra:
Có, mọi thứ đều chính xác: 3 bản ghi của chúng tôi đã được chọn cho lần Quét Seq duy nhất của toàn bộ bảng và nút Xóa được sử dụng để tìm kiếm dữ liệu một lần với Tid Scan:
-> Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1)
TID Cond: (ctid = ANY ($0))Nếu bạn xóa nhiều hồ sơ, .
Hãy kiểm tra một bảng lớn hơn và có số lượng bản sao lớn hơn:
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;
Vì vậy, phương pháp này hoạt động thành công nhưng phải thận trọng khi sử dụng. Bởi vì mỗi bản ghi bị xóa sẽ có một trang dữ liệu được đọc trong Tid Scan và một trang trong Xóa.
Nguồn: www.habr.com