🥇PostgreSQL Antipatterns: Đánh giá tình trạng trong SQL

SQL không phải là C++, cũng không phải là JavaScript. Do đó, việc đánh giá các biểu thức logic là khác nhau và điều này hoàn toàn không giống nhau:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Trong khi tối ưu hóa kế hoạch thực hiện truy vấn PostgreSQL có thể tùy ý "sắp xếp lại" các điều kiện tương đương, không tính toán bất kỳ bản ghi nào trong số chúng cho các bản ghi riêng lẻ, tham khảo điều kiện của chỉ mục được áp dụng ... Tóm lại, cách dễ nhất là giả sử rằng bạn không thể quản lý thứ tự mà chúng sẽ xảy ra (và liệu chúng có được tính toán hay không) bình đẳng điều kiện.

Do đó, nếu bạn vẫn muốn quản lý mức độ ưu tiên, bạn cần cấu trúc làm cho những điều kiện này không bình đẳng có điều kiện biểu thức и các nhà khai thác.

Dữ liệu và làm việc với chúng là cơ sở của phức hợp VLSI của chúng tôi, vì vậy điều rất quan trọng đối với chúng tôi là các thao tác trên chúng không chỉ được thực hiện chính xác mà còn hiệu quả. Hãy xem xét các ví dụ cụ thể nơi có thể mắc lỗi trong đánh giá biểu thức và nơi đáng để cải thiện hiệu quả của chúng.

#0: RTFM

bắt đầu ví dụ từ tài liệu:

Khi thứ tự đánh giá là quan trọng, nó có thể được cố định bằng cấu trúc CASE. Ví dụ, cách này để tránh chia cho XNUMX trong một câu WHERE không đáng tin cậy:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Tùy chọn an toàn:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Công trình được sử dụng CASE bảo vệ biểu thức khỏi tối ưu hóa, vì vậy nó chỉ nên được sử dụng khi cần thiết.

#1: điều kiện kích hoạt

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

Mọi thứ có vẻ tốt đẹp, nhưng... Không ai hứa rằng khoản đầu tư SELECT sẽ không được thực hiện nếu điều kiện đầu tiên là sai. Sửa nó với lồng vào nhau IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

Bây giờ chúng ta hãy xem xét kỹ lưỡng - toàn bộ chức năng kích hoạt hóa ra được "bao bọc" trong IF. Và điều này có nghĩa là không có gì ngăn cản chúng tôi loại bỏ điều kiện này khỏi quy trình bằng cách sử dụng WHEN-điều kiện:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

Cách tiếp cận này cho phép bạn tiết kiệm tài nguyên máy chủ với sự đảm bảo nếu điều kiện sai.

#2: Chuỗi OR/AND

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

Mặt khác, có thể thu được rằng cả hai EXISTS sẽ đúng, nhưng cả hai sẽ được thực hiện.

Nhưng nếu chúng ta biết chắc chắn rằng một trong số chúng là "đúng" thường xuyên hơn (hoặc "sai" - vì AND-chains) - có thể bằng cách nào đó "tăng mức độ ưu tiên" để cái thứ hai không được thực thi lại không?

Hóa ra là có thể - cách tiếp cận thuật toán gần với chủ đề của bài viết PostgreSQL Antipatterns: Mục nhập hiếm đạt đến giữa THAM GIA.

Chúng ta hãy "xô theo TRƯỜNG HỢP" cả hai điều kiện sau:

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

Trong trường hợp này, chúng tôi đã không xác định ELSE-value, nghĩa là, nếu cả hai điều kiện đều sai CASE sẽ trở lại NULL, được hiểu là FALSE в WHERE- điều kiện.

Ví dụ này có thể được kết hợp theo một cách khác - hương vị và màu sắc:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: cách [không] viết điều kiện

Chúng tôi đã dành hai ngày để phân tích lý do kích hoạt “kỳ lạ” của trình kích hoạt này - hãy xem tại sao.

Nguồn:

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

Vấn đề #1: Bất đẳng thức không tính đến NULL

Hãy giả sử rằng mọi thứ OLD-các lĩnh vực quan trọng NULL. Chuyện gì sẽ xảy ra?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

Và từ quan điểm của việc tìm ra các điều kiện NULL tương đương FALSE, Như được đề cập ở trên.

phán quyết: sử dụng toán tử IS DISTINCT FROM từ ROW-operator, so sánh toàn bộ hồ sơ cùng một lúc:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

Vấn đề số 2: triển khai khác nhau của cùng một chức năng

Hãy so sánh:

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

Tại sao có đầu tư thêm SELECT? một chức năng to_regclass? Tại sao lại khác...

Hãy sửa chữa:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

Vấn đề #3: bool ưu tiên

Hãy định dạng nguồn:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

Rất tiếc ... Trên thực tế, hóa ra trong trường hợp sự thật của bất kỳ điều kiện nào trong hai điều kiện đầu tiên, thì toàn bộ điều kiện đó biến thành TRUE, bỏ qua sự bất bình đẳng. Và đây không phải là tất cả những gì chúng tôi muốn.

Hãy sửa chữa:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

Vấn đề #4 (nhỏ): điều kiện OR phức tạp cho một trường

Trên thực tế, chúng tôi gặp vấn đề ở số 3 chính xác vì có ba điều kiện. Nhưng thay vì chúng, bạn có thể sử dụng một cái, sử dụng cơ chế coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

chúng ta cũng vậy NULL "bắt", và phức tạp OR Bạn không cần phải loay hoay với dấu ngoặc đơn.

trong tổng số

Hãy sửa những gì chúng ta có:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

Và cho rằng chức năng kích hoạt này chỉ có thể được sử dụng trong UPDATEkích hoạt do sự hiện diện OLD/NEW trong điều kiện cấp trên, thì điều kiện này thường có thể được đưa ra trong WHEN-điều kiện như trong #1...

Nguồn: www.habr.com

PostgreSQL Antipatterns: Đánh giá điều kiện trong SQL