Projekt i wybór bazy - normalizacja oraz wydajność widoków Opcje

[Zajec]

Zaprojektowałem bazę danych do przechowywania dokumentów sprzedażowych z ich pozycjami. W uproszczeniu do istotnych kolumn wygląda ona tak:

Kod

┌────────────┐        ┌────────────┐
│ invoices   │        │ items      │
├────────────┤        ├────────────┤
│ id         │ 1    n │ id         │
│ date       ├────────┤ invoice_id │
│ number     │        │ position   │
│ contractor │        │ name       │
└────────────┘        │ quantity   │
                      │ price      │
                      └────────────┘

Jak widać, w tabeli "invoices" nie przechowuję sumy pozycji. Dla wygody stworzyłem więc widok:

Kod

CREATE VIEW invoices_summary AS
SELECT invoices.*, SUM(items.quantity * items.price) AS total
FROM invoices
LEFT JOIN items ON (items.invoice_id = invoices.id)
GROUP BY invoices.id

Powyższe działało bardzo fajnie na MySQL aż do rozrośnięcia się danych. Obecnie wyciągnięcie choćby jednego wiersza z "invoices_summary" trwa ok. 2 sekund.

Przyczyną kulejącej wydajności jest ograniczenie algorytmu MERGE w MySQL: https://dev.mysql.com/doc/refman/8.0/en/view-algorithms.html
MySQL nie potrafi użyć MERGE gdy widok korzysta z funkcji grupujących. Stąd każde zapytanie do "invoices_summary" to generowanie tymczasowej tabeli na podstawie setek tysięcy. Gdyby MySQL "przeniosło" moje WHERE do wnętrza widoku, wystarczyłoby kilkadziesiąt wierszy.

Szukam pomysłu jak wybrnąc z tej sytuacji i potrzebuję pomocy. Rozważałem:

1. Przechowywanie sumy w tabeli "invoices".
   Spowoduje to duplikowanie informacji oraz będzie wymagało dodatkowej logiki w aplikacji (ręczne sumowanie i aktualizowanie "total").
2. Rezygnacja z widoku
   W rzeczywistości tabele są bardziej skomplikowane (dochodzą kwoty netto, brutto, stawki VAT). Suma brutto jest liczona na podstawie sum netto z podziałem na stawki VAT. Widok naprawdę upraszcza całą sprawę, nie chciałbym duplikować tak skomplikowanego zapytania SQL w wielu miejscach aplikacji.
3. Zmiana bazy danych
   Musiałbym poszukać bazy danych z lepszą implementacją widoków. MySQL jest jednak dużo powszechniejsza. Boję się potencjalnych problemów w innych bazach danych, które w MySQL mogą akurat nie występować.

Czy ktoś ma jakieś inne pomysły albo ogólnie sugestie?

[phpion]

Nie ma co na siłę trzymać się normalizacji, czasem denormalizacja nie jest zła, a wręcz wskazana (np. przez względy wydajnościowe). Dodaj do tabeli z fakturami kolumnę z sumą i aktualizuj jej wartość triggerem (w zasadzie na każde zdarzenie :/). Od strony aplikacji nie dotykaj tej kolumny, nie ingeruj w jej wartość.