Prohoster > Blog > quản lý > Antipatterns của PostgreSQL: Chuyển các bộ và lựa chọn sang SQL

Antipatterns của PostgreSQL: Chuyển các bộ và lựa chọn sang SQL

Đôi khi, nhà phát triển cần chuyển một tập hợp các tham số hoặc thậm chí toàn bộ lựa chọn cho yêu cầu "ở lối vào". Đôi khi có những giải pháp rất lạ cho vấn đề này.
Antipatterns của PostgreSQL: Chuyển các bộ và lựa chọn sang SQL
Hãy đi "từ phía đối diện" và xem làm thế nào để không làm điều đó, tại sao và làm thế nào bạn có thể làm điều đó tốt hơn.

"Chèn" trực tiếp các giá trị vào phần thân yêu cầu

Nó thường trông giống như thế này:

query = "SELECT * FROM tbl WHERE id = " + value

... hoặc như thế này:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

Về phương pháp này người ta đã nói, đã viết và thậm chí còn được vẽ đủ:

Antipatterns của PostgreSQL: Chuyển các bộ và lựa chọn sang SQL

Hầu như luôn luôn là vậy đường dẫn trực tiếp tới SQL SQL và tải thêm logic nghiệp vụ, buộc phải “kết dính” chuỗi truy vấn của bạn.

Cách tiếp cận này chỉ có thể được biện minh một phần nếu cần thiết. sử dụng phân vùng trong PostgreSQL phiên bản 10 trở xuống để có kế hoạch hiệu quả hơn. Trong các phiên bản này, danh sách các phần được quét được xác định mà không tính đến các tham số được truyền đi, chỉ dựa trên nội dung yêu cầu.

$n đối số

Sử dụng phần giữ chỗ thông số tốt, nó cho phép bạn sử dụng BÁO CÁO CHUẨN BỊ, giảm tải cả logic nghiệp vụ (chuỗi truy vấn chỉ được hình thành và truyền một lần) và trên máy chủ cơ sở dữ liệu (không cần phân tích lại và lập kế hoạch cho từng phiên bản của yêu cầu).

Số lượng đối số thay đổi

Các vấn đề sẽ chờ đợi chúng ta khi chúng ta muốn chuyển trước một số lượng đối số không xác định:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

Nếu bạn để lại yêu cầu ở dạng này, thì mặc dù nó sẽ cứu chúng ta khỏi khả năng bị tiêm, nhưng nó vẫn dẫn đến việc phải dán / phân tích yêu cầu cho mỗi tùy chọn từ số lượng đối số. Tốt hơn là làm điều đó mọi lúc, nhưng bạn có thể làm mà không cần nó.

Chỉ cần truyền một tham số chứa biểu diễn tuần tự của một mảng:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

Sự khác biệt duy nhất là nhu cầu chuyển đổi rõ ràng đối số thành kiểu mảng mong muốn. Nhưng điều này không gây ra vấn đề gì vì chúng ta đã biết trước mình đang giải quyết vấn đề ở đâu.

Chuyển mẫu (ma trận)

Thông thường, đây là tất cả các loại tùy chọn để chuyển các tập dữ liệu để chèn vào cơ sở dữ liệu “trong một yêu cầu”:

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

Ngoài các vấn đề được mô tả ở trên với việc "dán lại" yêu cầu, điều này cũng có thể dẫn chúng ta đến hết trí nhớ và sự cố máy chủ. Lý do rất đơn giản - PG dành thêm bộ nhớ cho các đối số và số lượng bản ghi trong bộ chỉ bị giới hạn bởi Danh sách mong muốn của ứng dụng logic nghiệp vụ. Đặc biệt trong các trường hợp lâm sàng cần phải xem đối số "được đánh số" lớn hơn $9000 - đừng làm theo cách này.

Hãy viết lại truy vấn, áp dụng đã tuần tự hóa "hai cấp độ":

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

Có, trong trường hợp các giá trị "phức tạp" bên trong một mảng, chúng cần được đóng khung bằng dấu ngoặc kép.
Rõ ràng là bằng cách này, bạn có thể "mở rộng" lựa chọn với số lượng trường tùy ý.

bất bình, bất bình,…

Đôi khi, có các tùy chọn để chuyển thay vì một "mảng mảng" một số "mảng cột" mà tôi đã đề cập trong bài báo cuối cùng:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

Với phương pháp này, nếu mắc lỗi khi tạo danh sách giá trị cho các cột khác nhau thì rất dễ bị lỗi hoàn toàn. kết quả bất ngờ, điều này cũng phụ thuộc vào phiên bản máy chủ:

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

Bắt đầu từ phiên bản 9.3, PostgreSQL có đầy đủ các chức năng để làm việc với kiểu json. Do đó, nếu các tham số đầu vào của bạn được xác định trong trình duyệt, bạn có thể ngay tại đó và tạo đối tượng json cho truy vấn SQL:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

Đối với các phiên bản trước, phương pháp tương tự có thể được sử dụng cho mỗi(hstore), nhưng việc "gấp" chính xác bằng cách thoát các đối tượng phức tạp trong hstore có thể gây ra sự cố.

json_populate_recordset

Nếu bạn biết trước rằng dữ liệu từ mảng json “đầu vào” sẽ điền vào một số bảng, bạn có thể lưu rất nhiều vào các trường “hội thảo” và chuyển sang các loại mong muốn bằng cách sử dụng hàm json_populate_recordset:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

Và hàm này sẽ chỉ đơn giản là “mở rộng” mảng đối tượng đã truyền vào một vùng chọn mà không cần dựa vào định dạng bảng:

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

BẢNG TẠM THỜI

Nhưng nếu lượng dữ liệu trong mẫu được truyền rất lớn thì việc đưa nó vào một tham số được tuần tự hóa là rất khó và đôi khi là không thể, vì nó yêu cầu thực hiện một lần. cấp phát bộ nhớ lớn. Ví dụ: bạn cần thu thập một lượng lớn dữ liệu sự kiện từ hệ thống bên ngoài trong một thời gian dài và sau đó bạn muốn xử lý dữ liệu đó một lần ở phía cơ sở dữ liệu.

Trong trường hợp này giải pháp tốt nhất là sử dụng bảng tạm thời:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

Phương pháp này tốt để truyền không thường xuyên khối lượng lớn dữ liệu.
Từ quan điểm mô tả cấu trúc dữ liệu của nó, bảng tạm thời khác với bảng “thông thường” chỉ ở một tính năng. trong bảng hệ thống pg_classvà trong pg_type, pg_depend, pg_attribute, pg_attrdef, ... - và không có gì cả.

Do đó, trong các hệ thống web có số lượng lớn các kết nối tồn tại trong thời gian ngắn cho mỗi hệ thống, một bảng như vậy sẽ tạo ra các bản ghi hệ thống mới mỗi lần, các bản ghi này sẽ bị xóa khi đóng kết nối với cơ sở dữ liệu. Sau cùng, việc sử dụng TEMP TABLE không được kiểm soát dẫn đến "sưng" các bảng trong pg_catalog và làm chậm nhiều hoạt động sử dụng chúng.
Tất nhiên, điều này có thể được giải quyết bằng vượt qua định kỳ VACUUM ĐẦY ĐỦ theo bảng danh mục hệ thống.

Biến phiên

Giả sử việc xử lý dữ liệu từ trường hợp trước khá phức tạp đối với một truy vấn SQL, nhưng bạn muốn thực hiện việc đó khá thường xuyên. Đó là, chúng tôi muốn sử dụng xử lý thủ tục trong khối DO, nhưng việc sử dụng truyền dữ liệu qua các bảng tạm thời sẽ quá tốn kém.

Chúng tôi cũng không thể sử dụng thông số $n để chuyển tới một khối ẩn danh. Các biến phiên và hàm sẽ giúp chúng ta thoát khỏi tình trạng này. thiết lập hiện tại.

Trước phiên bản 9.2, bạn phải cấu hình trước không gian tên đặc biệt custom_variable_classes cho các biến phiên "của họ". Trên các phiên bản hiện tại, bạn có thể viết một cái gì đó như thế này:

SET my.val = '{1,2,3}';
DO $$
DECLARE
  id integer;
BEGIN
  FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
    RAISE NOTICE 'id : %', id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

Có các giải pháp khác có sẵn bằng các ngôn ngữ thủ tục được hỗ trợ khác.

Biết nhiều cách hơn? Chia sẻ trong các ý kiến!

Nguồn: www.habr.com

Thêm một lời nhận xét