Minimalizowanie indeksowanych odczytów za pomocą złożonych kryteriów

Optymalizuję bazę zgłoszeń pracy Firebird 2.5. Są one przechowywane w tabeli zadeklarowanej jako taka:

CREATE TABLE TICKETS (
  TICKET_ID id PRIMARY KEY,
  JOB_ID id,
  ACTION_ID id,
  STATUS str256 DEFAULT 'Pending'
);

Zasadniczo chcę znaleźć pierwszy bilet, który nie został przetworzony i ma Pendingstatus.

Moja pętla przetwarzania to:

1. Odzyskaj 1. bilet gdzie Pending
2. Pracuj z Ticket.
3. Zaktualizuj status biletu => Complete
4. Powtarzać.

Nic nadzwyczajnego. Jeśli oglądam bazę danych podczas działania tej pętli, widzę liczbę indeksowanych odczytów wzlotów dla każdej iteracji. Wydajność nie wydaje się strasznie obniżać, co mogę powiedzieć, ale maszyna, na której testuję, jest dość szybka. Jednak otrzymałem raporty o spadku wydajności z czasem od niektórych moich użytkowników.

Mam włączony indeks Status, ale nadal wygląda na to, że skanuje Ticket_Idkolumnę po każdej iteracji. Wygląda na to, że coś przeoczyłem, ale nie jestem pewien, co. Czy oczekiwana jest rosnąca liczba indeksowanych odczytów dla czegoś takiego, czy też indeks jest w jakiś sposób niewłaściwy?

- Edycja komentarzy -

W Firebird ograniczasz pobieranie wierszy, takie jak:

Select First 1
  Job_ID, Ticket_Id
From
  Tickets
Where
  Status = 'Pending'

Więc kiedy mówię „pierwszy”, proszę tylko o ograniczony zestaw rekordów gdzie Status = 'Pending'.

Degradacja w czasie występuje z powodu zwiększonej liczby elementów, które mają status „Ukończony”. Pomyśl o tym przez chwilę - nie pogorszysz wydajności podczas testowania, ponieważ prawdopodobnie masz niewielką liczbę wierszy ze statusem „Complete”. Ale w produkcji mogą mieć miliony wierszy ze statusem „Ukończone”, a liczba ta z czasem wzrośnie. Zasadniczo sprawia to, że Twój indeks statusu staje się z czasem coraz mniej przydatny. Jako taka, baza danych prawdopodobnie po prostu decyduje, że ponieważ Status prawie zawsze ma wartość „Kompletny”, po prostu skanuje tabelę zamiast używać indeksu.

W SQL Server (a może w innych RDBMS?) Można to obejść za pomocą Filtrowanych Indeksów. W SQL Server należy dodać warunek WHERE na końcu definicji indeksu, aby powiedzieć „zastosuj ten indeks tylko do rekordów ze statusem <>„ Complete ””. Wtedy każde zapytanie korzystające z tego predykatu najprawdopodobniej użyje indeksu na małej liczbie rekordów, które nie są ustawione na „Complete”. Jednak w oparciu o dokumentację tutaj: http://www.firebirdsql.org/refdocs/langrefupd25-ddl-index.html nie wygląda na to, aby Firebird obsługiwał filtrowane indeksy.

Obejściem tego problemu jest umieszczenie rekordów „Complete” w tabeli ArchiveTickets. Utwórz tabelę z dokładnie taką samą definicją (choć bez żadnego automatycznie generowanego identyfikatora), jak tabela biletów i zachowaj wiersze między nimi, przesuwając rekordy „Complete” do tabeli ArchiveTickets. Indeks w tabeli biletów będzie wtedy obejmował znacznie mniejszą liczbę rekordów i będzie miał znacznie wyższą wydajność. Będzie to prawdopodobnie oznaczać, że będziesz musiał zmienić wszelkie raporty itp., Które odnoszą się do biletów „Complete”, aby wskazać tabelę Archive lub wykonać UNION w obu biletach i ArchiveTickets. Będzie to miało tę zaletę, że będzie nie tylko szybkie, ale będzie również oznaczać, że możesz utworzyć określone indeksy dla tabeli ArchiveTickets, aby zwiększyć jej wydajność w przypadku innych zapytań (na przykład:

Powinieneś się tym przejmować, jeśli twoja produkcja pójdzie w tysiące rzędów. Z czasem wydajność ulegnie zmniejszeniu i negatywnie wpłynie na wrażenia użytkownika.

To, czy wpłynie to na wydajność, zależy od objętości danych i wydajności maszyny. Biorąc pod uwagę pojemność nowoczesnego sprzętu, trudno sobie wyobrazić wielkość sprzedaży biletów, której nie da się obsłużyć opisanym projektem. Są jednak zmiany, które poleciłbym ze względu na poprawność i mogą poprawić wydajność jako dodatkową korzyść.

Twoje pierwsze oczekujące zapytanie jest niedeterministyczne. Najpierw według jakiej kolejności? Tabela SQL nie ma wewnętrznej kolejności; First 1Hack jest po prostu daje trochę arbitralne pierwszy. Aby uczynić go deterministycznym, dlaczego nie przetwarzać oczekujących zadań w kolejności Job_ID?

Jeśli masz dwa indeksy {Job_ID} i {Status, Job_ID}, to zapytanie zwróci jeden wiersz w przewidywalny i wydajny sposób:

Select Job_ID, Ticket_Id
From   Tickets
Where Job_ID = ( 
  select min(Job_ID) from Tickets 
  where Status = 'Pending'
);

Nie jestem użytkownikiem Firebird, więc musisz sprawdzić plan zapytań, ale powinien on być wydajny, ponieważ podzapytanie odwołuje się tylko do drugiego indeksu, generuje wartość dla pierwszego. (Mogą być dostępne inne sztuczki wydajności. Możesz zorganizować fizyczny stół jako drzewo B + lub mieć na przykład dostęp do ukrytego id_wiersza).

Inną zmianą, którą chciałbym wprowadzić dla poprawności, jest utworzenie Statuspojedynczego, ograniczonego bajtu i zezwolenie aplikacji na dostarczenie ciągu „Oczekujące”. To uchroni przed błędnymi Statuswartościami i prawdopodobnie zmniejszy indeks w okazyjnej cenie. Coś jak:

CREATE TABLE TICKETS (
  TICKET_ID id PRIMARY KEY,
  JOB_ID id,
  ACTION_ID id,
  STATUS char(1) not NULL 
     DEFAULT 'P'
     CHECK( STATUS in ('P', 'C', 'X') ) -- whatever the domain is
);

Oczywiście możesz użyć widoku (lub może kolumny pochodnej), aby podać ciągi kanoniczne dla statusu.