ProHoster > Blog > administracja > Antywzorce PostgreSQL: poruszanie się po rejestrze

Antywzorce PostgreSQL: poruszanie się po rejestrze

Dziś w SQL nie będzie skomplikowanych przypadków i wyrafinowanych algorytmów. Wszystko będzie bardzo proste, na poziomie Kapitana Oczywistego – zróbmy to przeglądanie rejestru zdarzeń posortowane według czasu.

Oznacza to, że w bazie danych znajduje się znak eventsi ma pole ts - dokładnie czas, w którym chcemy wyświetlić te zapisy w sposób uporządkowany:

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

CREATE INDEX ON events(ts DESC);

Jasne jest, że nie będziemy mieli tam kilkunastu płyt, więc będziemy potrzebować jakiejś formy nawigacja strony.

#0. „Jestem pogromistą mojej matki”

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

To prawie nie żart – jest rzadki, ale spotykany na wolności. Czasami po pracy z ORM-em może być trudno przejść na „bezpośrednią” pracę z SQL.

Przejdźmy jednak do problemów bardziej powszechnych i mniej oczywistych.

#1. ZRÓWNOWAŻYĆ

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

Skąd wzięła się liczba 26? Jest to przybliżona liczba wpisów do wypełnienia jednego ekranu. Dokładniej, 25 wyświetlonych rekordów plus 1, sygnalizujące, że w próbce jest jeszcze co najmniej coś więcej i warto iść dalej.

Oczywiście wartości tej nie można „wszyć” w treść żądania, lecz przekazać ją poprzez parametr. Ale w tym przypadku planista PostgreSQL nie będzie mógł polegać na wiedzy, że rekordów powinno być stosunkowo mało - i łatwo wybierze nieefektywny plan.

I choć w interfejsie aplikacji przeglądanie rejestru realizowane jest w formie przełączania pomiędzy wizualnymi „stronami”, to przez długi czas nikt nie zauważa niczego podejrzanego. Dokładnie do momentu, gdy w walce o wygodę UI/UX postanowi przerobić interfejs na „niekończące się przewijanie” – czyli wszystkie wpisy rejestru rysowane są na jednej liście, którą użytkownik może przewijać w górę i w dół.

I tak podczas kolejnych testów zostajesz złapany powielanie zapisów w rejestrze. Dlaczego, ponieważ tabela ma normalny indeks (ts), na którym opiera się Twoje zapytanie?

Właśnie dlatego, że tego nie wziąłeś pod uwagę ts nie jest kluczem unikalnym w tej tabeli. Właściwie, i jego wartości nie są wyjątkowe, jak każdy „czas” w warunkach rzeczywistych – dlatego ten sam rekord w dwóch sąsiednich zapytaniach łatwo „przeskakuje” ze strony na stronę ze względu na inną kolejność końcową w ramach sortowania tej samej wartości klucza.

Tak naprawdę kryje się tu także drugi problem, który jest dużo trudniejszy do zauważenia - niektóre wpisy nie zostaną pokazane w ogóle! W końcu „zduplikowane” zapisy zajęły miejsce kogoś innego. Można znaleźć szczegółowe wyjaśnienie z pięknymi zdjęciami przeczytaj tutaj.

Rozszerzanie indeksu

Sprytny programista rozumie, że klucz indeksu trzeba uczynić unikalnym, a najłatwiej jest go rozszerzyć o oczywiście unikalne pole, do czego PK jest idealny:

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

A żądanie ulega zmianie:

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

#2. Przełącz na „kursory”

Jakiś czas później przychodzi do Ciebie administrator danych i jest „zadowolony” z Twoich próśb ładują serwer jak cholera swoimi regułami OFFSETi ogólnie czas na zmianę nawigacja od ostatniej wyświetlanej wartości. Twoje zapytanie ponownie ulega zmianie:

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

Odetchnęłaś z ulgą, aż nadeszło...

#3. Indeksy czyszczenia

Ponieważ pewnego dnia Twój DBA przeczytał artykuł o znajdowaniu nieefektywnych indeksów i zdałem sobie z tego sprawę „nie najnowszy” znacznik czasu nie jest dobry. I znów przyszedłem do Was - teraz z myślą, że ten indeks powinien jeszcze wrócić (ts DESC).

Ale co zrobić z początkowym problemem „przeskakiwania” rekordów pomiędzy stronami?.. I wszystko jest proste - trzeba wybierać bloki z nieustaloną liczbą rekordów!

W ogóle, kto zabrania nam czytać nie „dokładnie 26”, ale „nie mniej niż 26”? Na przykład, żeby w kolejnym bloku były zapisy o wyraźnie odmiennym znaczeniu ts - wtedy nie będzie problemu z „przeskakiwaniem” rekordów pomiędzy blokami!

Oto jak to osiągnąć:

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;

Co tu się dzieje?

  1. Przesuwamy 25 rekordów „w dół” i uzyskujemy wartość „graniczną”. ts.
  2. Jeśli już nic tam nie ma, zamień wartość NULL na -infinity.
  3. Od otrzymanej wartości odejmujemy cały segment wartości ts oraz parametr $1 przekazany z interfejsu (poprzednia „ostatnia” wyrenderowana wartość).
  4. Jeżeli zwrócony zostanie blok zawierający mniej niż 26 rekordów, jest on ostatnim.

Lub ten sam obrazek:
Antywzorce PostgreSQL: poruszanie się po rejestrze

Bo teraz mamy próbka nie ma żadnego konkretnego „początku”, to nic nie stoi na przeszkodzie, abyśmy „rozwinęli” to żądanie w przeciwnym kierunku i zaimplementowali dynamiczne ładowanie bloków danych od „punktu odniesienia” w obu kierunkach – zarówno w dół, jak i w górę.

Uwaga:

  1. Tak, w tym przypadku uzyskujemy dostęp do indeksu dwukrotnie, ale wszystko odbywa się „czysto według indeksu”. Dlatego podzapytanie spowoduje jedynie do jednego dodatkowego skanowania zawierającego tylko indeks.
  2. Jest całkiem oczywiste, że tej techniki można użyć tylko wtedy, gdy masz wartości ts może przejść tylko przez przypadek, i kilka z nich. Jeśli typowym przypadkiem jest „milion rekordów o godzinie 00:00:00.000”, nie powinieneś tego robić. To znaczy, nie powinieneś pozwalać na taki przypadek. Ale jeśli tak się stanie, użyj opcji z rozszerzonym indeksem.

Źródło: www.habr.com

Dodaj komentarz