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?

[trueblue]

EXPLAIN to w MySQL jakiś żart (IMG:style_emoticons/default/sad.gif) 3 bezwartościowe informacje (wiersze)

Powodzenia w takim razie przy dalszej rozbudowie aplikacji i zaniechania używania:)

A byłem taki dumny ze swoich widoków i prostego kodu SQL w aplikacji (IMG:style_emoticons/default/tongue.gif) Serwer to dzielony hosting mydevil, MySQL 5.7.26, ale problem obserwuję też loklanie na MadiaDB 10.4.13.

Ale przekombinowałeś. To zapytanie, nawet z wyliczaniem sumy według stawek powinno wykonywać się w mgnieniu oka. Tak się składa, że pracuję z tabelami po kilkaset milionów rekordów i poprawnie założone indeksy pozwalają wykonywać zapytania poniżej dziesiątek sekundy.
Wydaje mi się, że i tak nadal będziesz kombinował, aby na zewnątrz mieć SELECT * FROM widok.

[Zajec]

Powodzenia w takim razie przy dalszej rozbudowie aplikacji i zaniechania używania:)

Uważam, że EXPLAIN jest bardzo przydatny i nikt (pracujący z większymi tabelami/zapytaniami) temu raczej nie zaprzeczy. Zaskoczyła mnie tylko jego uboga implementacja w MySQL, bo uczyłem się odczytywać wynik EXPLAIN w Oracle.

Ale przekombinowałeś. To zapytanie, nawet z wyliczaniem sumy według stawek powinno wykonywać się w mgnieniu oka. Tak się składa, że pracuję z tabelami po kilkaset milionów rekordów i poprawnie założone indeksy pozwalają wykonywać zapytania poniżej dziesiątek sekundy.
Wydaje mi się, że i tak nadal będziesz kombinował, aby na zewnątrz mieć SELECT * FROM widok.

Przyznaję, że nie chcę za każdym zapytaniem robić copy & paste np. całego:

[SQL] pobierz, plaintext 
IF(discount IS NULL, price, price * (100 - discount) / 100) * quantity * (100 + vat_rate) / 100 AS total
[SQL] pobierz, plaintext 

jakbym w C czy PHP nie zrobił z tego funkcji, każdy mi uznał to za pomyłkę. Może za bardzo przenoszę zwyczaje programistyczne do SQL.

Zrobię sobie jednak trigger z powyższym kodem, myslę, że będzie to optymalne rozwiązanie. Waham się jedynie między zapisywaniem wyniku do osobnej kolumny (oryginalnej tabeli) a zapisywaniem do osobnej tabeli (emulując widok zmaterializowany).