Prohoster > Blog > quản lý > Antipatterns của PostgreSQL: Điều hướng sổ đăng ký

Antipatterns của PostgreSQL: Điều hướng sổ đăng ký

Ngày nay sẽ không có trường hợp phức tạp và thuật toán phức tạp nào trong SQL. Mọi thứ sẽ rất đơn giản, ở cấp độ Captain Obvious - hãy làm đi xem sổ đăng ký sự kiện sắp xếp theo thời gian.

Tức là có một dấu hiệu trong cơ sở dữ liệu events, và cô ấy có một cánh đồng ts - chính xác thời điểm mà chúng ta muốn hiển thị các bản ghi này một cách có trật tự:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Rõ ràng là chúng ta sẽ không có hàng tá bản ghi ở đó, vì vậy chúng ta sẽ cần một số dạng điều hướng trang.

#0. “Tôi là kẻ tàn ác của mẹ tôi”

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Nó gần như không phải chuyện đùa - nó rất hiếm nhưng được tìm thấy trong tự nhiên. Đôi khi, sau khi làm việc với ORM, có thể khó chuyển sang làm việc “trực tiếp” với SQL.

Nhưng hãy chuyển sang những vấn đề phổ biến hơn và ít rõ ràng hơn.

#1. BÙ LẠI

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

Con số 26 đến từ đâu? Đây là số lượng mục nhập gần đúng để lấp đầy một màn hình. Chính xác hơn, 25 bản ghi được hiển thị, cộng với 1, báo hiệu rằng có ít nhất một thứ gì đó khác trong mẫu và việc tiếp tục là hợp lý.

Tất nhiên, giá trị này không thể được “khâu” vào nội dung của yêu cầu mà được truyền qua một tham số. Nhưng trong trường hợp này, bộ lập lịch PostgreSQL sẽ không thể dựa vào kiến ​​thức rằng sẽ có tương đối ít bản ghi - và sẽ dễ dàng chọn một phương án không hiệu quả.

Và trong khi ở giao diện ứng dụng, việc xem sổ đăng ký được thực hiện dưới dạng chuyển đổi giữa các “trang” trực quan, không ai nhận thấy điều gì đáng ngờ trong một thời gian dài. Chính xác cho đến thời điểm, trong cuộc đấu tranh vì sự thuận tiện, UI/UX quyết định làm lại giao diện thành “cuộn vô tận” - nghĩa là tất cả các mục đăng ký được rút ra trong một danh sách duy nhất mà người dùng có thể cuộn lên xuống.

Và vì vậy, trong lần thử nghiệm tiếp theo, bạn bị bắt sao chép hồ sơ trong sổ đăng ký. Tại sao, vì bảng có chỉ số bình thường (ts), truy vấn của bạn dựa vào điều gì?

Chính xác là vì bạn đã không tính đến điều đó ts không phải là khóa duy nhất trong bảng này. Trên thực tế, và giá trị của nó không phải là duy nhất, giống như bất kỳ “thời gian” nào trong điều kiện thực tế - do đó, cùng một bản ghi trong hai truy vấn liền kề dễ dàng “nhảy” từ trang này sang trang khác do thứ tự cuối cùng khác nhau trong khuôn khổ sắp xếp cùng một giá trị khóa.

Trên thực tế, ở đây còn ẩn giấu một vấn đề thứ hai, khó nhận ra hơn nhiều - một số mục sẽ không được hiển thị ở tất cả! Rốt cuộc, những bản ghi “trùng lặp” đã chiếm chỗ của người khác. Có thể tìm thấy lời giải thích chi tiết với hình ảnh đẹp đọc ở đây.

Mở rộng chỉ mục

Một nhà phát triển khôn ngoan hiểu rằng khóa chỉ mục cần phải được tạo thành duy nhất và cách dễ nhất là mở rộng nó bằng một trường rõ ràng là duy nhất, PK này hoàn hảo cho:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

Và yêu cầu thay đổi:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. Chuyển sang “con trỏ”

Một thời gian sau, một DBA đến gặp bạn và “hài lòng” vì yêu cầu của bạn họ tải máy chủ như điên với các quy tắc OFFSET của họvà nói chung, đã đến lúc chuyển sang điều hướng từ giá trị cuối cùng được hiển thị. Truy vấn của bạn lại thay đổi:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Bạn thở phào nhẹ nhõm cho đến khi...

#3. Chỉ số làm sạch

Bởi vì một ngày DBA của bạn đọc bài viết về tìm chỉ mục không hiệu quả và nhận ra rằng Dấu thời gian “không phải mới nhất” không tốt. Và tôi lại đến với bạn - bây giờ với suy nghĩ rằng chỉ số đó vẫn sẽ quay trở lại thành (ts DESC).

Nhưng phải làm gì với vấn đề ban đầu là “nhảy” bản ghi giữa các trang?.. Và mọi thứ đều đơn giản - bạn cần chọn các khối có số lượng bản ghi không cố định!

Nói chung, ai cấm chúng ta đọc không phải “chính xác 26”, mà là “không dưới 26”? Ví dụ, để trong khối tiếp theo có hồ sơ với ý nghĩa rõ ràng khác nhau ts - sau đó sẽ không có vấn đề gì với việc “nhảy” bản ghi giữa các khối!

Đây là cách để đạt được điều này:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Những gì đang xảy ra ở đây?

  1. Chúng ta bước 25 bản ghi “xuống” và nhận giá trị “ranh giới” ts.
  2. Nếu chưa có gì ở đó thì thay thế giá trị NULL bằng -infinity.
  3. Chúng tôi trừ toàn bộ phân đoạn giá trị giữa giá trị nhận được ts và tham số $1 được truyền từ giao diện (giá trị được hiển thị “cuối cùng” trước đó).
  4. Nếu một khối được trả về có ít hơn 26 bản ghi thì đó là khối cuối cùng.

Hoặc cùng một hình ảnh:
Antipatterns của PostgreSQL: Điều hướng sổ đăng ký

Bởi vì bây giờ chúng ta có mẫu không có bất kỳ “sự khởi đầu” cụ thể nào, thì không có gì ngăn cản chúng tôi “mở rộng” yêu cầu này theo hướng ngược lại và thực hiện tải động các khối dữ liệu từ “điểm tham chiếu” theo cả hai hướng - cả xuống và lên.

Ghi chú:

  1. Có, trong trường hợp này, chúng tôi truy cập chỉ mục hai lần, nhưng mọi thứ đều “hoàn toàn theo chỉ mục”. Vì vậy, một truy vấn con sẽ chỉ dẫn đến vào một lần Quét chỉ chỉ mục bổ sung.
  2. Rõ ràng là kỹ thuật này chỉ có thể được sử dụng khi bạn có các giá trị ts chỉ có thể vượt qua một cách tình cờ, và không có nhiều người trong số họ. Nếu trường hợp điển hình của bạn là “một triệu bản ghi lúc 00:00:00.000”, thì bạn không nên làm điều này. Ý tôi là, bạn không nên để trường hợp như vậy xảy ra. Nhưng nếu điều này xảy ra, hãy sử dụng tùy chọn có chỉ mục mở rộng.

Nguồn: www.habr.com

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