Prohoster > Blog > quản lý > PostgreSQL Antipatterns: "Chỉ có một!"

PostgreSQL Antipatterns: "Chỉ có một!"

Trong SQL, bạn mô tả “những gì” bạn muốn đạt được, chứ không phải “làm thế nào” nó sẽ được thực thi. Do đó, vấn đề phát triển các truy vấn SQL theo kiểu “như người ta nghe nói là viết như thế nào” chiếm vị trí quan trọng, cùng với Tính năng tính điều kiện trong SQL.

Ngày nay, bằng cách sử dụng các ví dụ cực kỳ đơn giản, hãy xem điều này có thể dẫn đến điều gì trong bối cảnh sử dụng GROUP/DISTINCT и LIMIT với họ.

Bây giờ, nếu bạn viết trong yêu cầu “Đầu tiên hãy kết nối các dấu hiệu này, sau đó loại bỏ tất cả các dấu hiệu trùng lặp, lẽ ra chỉ còn lại một sao chép cho mỗi phím" - đây chính xác là cách nó sẽ hoạt động, ngay cả khi không cần kết nối.

Và đôi khi bạn may mắn và nó “chỉ hoạt động”, đôi khi nó gây ảnh hưởng khó chịu đến hiệu suất và đôi khi nó mang lại những hiệu ứng hoàn toàn bất ngờ theo quan điểm của nhà phát triển.

PostgreSQL Antipatterns: "Chỉ có một!"
Chà, có lẽ không ngoạn mục lắm, nhưng...

“Cặp đôi ngọt ngào”: JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Sẽ rõ ràng họ muốn gì chọn bản ghi X có bản ghi trong Y liên quan đến điều kiện được đáp ứng. Đã viết yêu cầu qua JOIN — đã nhận được một số giá trị pk nhiều lần (chính xác có bao nhiêu mục phù hợp xuất hiện trong Y). Làm thế nào để loại bỏ? Chắc chắn DISTINCT!

Điều đặc biệt “thật vui” khi mỗi bản ghi X có hàng trăm bản ghi Y liên quan, sau đó các bản sao trùng lặp sẽ bị loại bỏ một cách hào hùng…

PostgreSQL Antipatterns: "Chỉ có một!"

Làm thế nào để khắc phục? Để bắt đầu, hãy nhận ra rằng vấn đề có thể được sửa đổi thành “chọn bản ghi X mà trong Y có ÍT NHẤT MỘT bản ghi được liên kết với điều kiện được đáp ứng” - xét cho cùng, chúng tôi không cần bất cứ thứ gì từ bản ghi Y.

TỒN TẠI lồng nhau

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Một số phiên bản của PostgreSQL hiểu rằng trong EXISTS chỉ cần tìm mục đầu tiên xuất hiện là đủ, những mục cũ hơn thì không. Vì vậy tôi thích luôn luôn chỉ ra LIMIT 1 bên trong EXISTS.

THAM GIA BÊN

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Tùy chọn tương tự cho phép, nếu cần, trả về ngay một số dữ liệu từ bản ghi Y liên quan được tìm thấy. Một lựa chọn tương tự được thảo luận trong bài viết "Các mẫu phản kháng PostgreSQL: một bản ghi hiếm sẽ đạt đến giữa THAM GIA".

“Tại sao phải trả nhiều tiền hơn”: DISTINCT [ON] + GIỚI HẠN 1

Một lợi ích bổ sung của việc chuyển đổi truy vấn như vậy là khả năng dễ dàng giới hạn việc tìm kiếm các bản ghi nếu chỉ cần một hoặc một vài trong số chúng, như trong trường hợp sau:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Bây giờ chúng ta đọc yêu cầu và cố gắng hiểu DBMS được đề xuất làm gì:

  • kết nối các dấu hiệu
  • độc đáo bởi X.pk
  • từ các mục còn lại, chọn một

Vậy bạn đã nhận được gì? "Chỉ một mục thôi" từ những cái duy nhất - và nếu chúng ta lấy cái này trong những cái không phải duy nhất, liệu kết quả có thay đổi theo cách nào đó không?.. “Và nếu không có sự khác biệt, tại sao phải trả nhiều tiền hơn?”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Và chính xác cùng một chủ đề với GROUP BY + LIMIT 1.

“Tôi chỉ cần hỏi thôi”: ngầm định NHÓM + GIỚI HẠN

Những điều tương tự xảy ra ở những nơi khác nhau kiểm tra không trống dấu hiệu hoặc CTE khi yêu cầu tiến triển:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Chức năng tổng hợp (count/min/max/sum/...) được thực thi thành công trên toàn bộ tập hợp, ngay cả khi không có hướng dẫn rõ ràng GROUP BY. Chỉ với LIMIT họ không thân thiện lắm.

Nhà phát triển có thể nghĩ “nếu có hồ sơ ở đó thì tôi không cần nhiều hơn GIỚI HẠN”. Nhưng đừng làm thế! Bởi vì đối với cơ sở nó là:

  • đếm những gì họ muốn theo tất cả hồ sơ
  • đưa ra bao nhiêu dòng tùy thích

Tùy thuộc vào điều kiện mục tiêu, có thể thực hiện một trong các thay thế sau:

  • (count + LIMIT 1) = 0 trên NOT EXISTS(LIMIT 1)
  • (count + LIMIT 1) > 0 trên EXISTS(LIMIT 1)
  • count >= N trên (SELECT count(*) FROM (... LIMIT N))

“Treo bao nhiêu gam”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Một nhà phát triển ngây thơ có thể tin tưởng một cách chân thành rằng yêu cầu sẽ ngừng thực thi. ngay khi chúng tôi tìm thấy $1 trong số các giá trị khác nhau đầu tiên xuất hiện.

Đôi khi trong tương lai điều này có thể và sẽ hoạt động nhờ vào một nút mới Quét bỏ qua chỉ mục, việc triển khai hiện đang được thực hiện nhưng vẫn chưa.

Bây giờ đầu tiên tất cả hồ sơ sẽ được lấy, là duy nhất và chỉ từ họ số tiền được yêu cầu mới được trả lại. Thật là buồn nếu chúng ta muốn một cái gì đó như $ 1 = 4, và có hàng trăm ngàn bản ghi trong bảng...

Để không buồn vô ích chúng ta hãy sử dụng truy vấn đệ quy "DISTINCT dành cho người nghèo" từ PostgreSQL Wiki:

PostgreSQL Antipatterns: "Chỉ có một!"

Nguồn: www.habr.com

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