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?

[Zajec]

Dzięki wszystkim za odpowiedzi, wątpliwości co do problemów wydajnościowych (przy nieprzesadnie wielu danych) nakierowały mnie na dalsze podejrzenia i poszukiwania. Ostatecznie mieliście rację, do problemów (poza widokami) przyczynił się jeszcze jeden - nieświadomie "zatajony" - fakt.

Nadmieniłem, że operuję na cenach netto, brutto i stawkach VAT (nie wspomniałem jedynie o rabatach procentowych).

Otóz do wyliczania wartości brutto (dla "ułatwienia") stworzyłem sobie funkcje:

[SQL] pobierz, plaintext 
CREATE FUNCTION final_price (price INTEGER(11), discount SMALLINT(2))
RETURNS INTEGER(11) DETERMINISTIC
RETURN IF(discount IS NULL, price, price * (100 - discount) / 100);
 
CREATE FUNCTION price_to_total_gross (price INTEGER(11), discount SMALLINT(2), quantity INTEGER(11), vat_rate SMALLINT(2))
RETURNS INTEGER(11) DETERMINISTIC
RETURN final_price(price, discount) * quantity * (100 + vat_rate) / 100;
[SQL] pobierz, plaintext 

Dzięki temu mam ładnie wyglądające zapytanie SELECT price_to_total_gross(price, discount), quantity, vat_rate). Niektórzy może już widzą mój błąd. Okazuje się, że składowane funkcje są potwornie wolne w MySQL.

Przy kilkuset wierszach nie byłoby tego widać. Tu jednak na problem funkcji nakładają się nieoptymalne widoki, które powodują wyliczanie całej tymczasowej tabeli. To oznacza stosowanie wolnych funkcji dla kilkudziesięciu tysięcy wierszy.

Mam więc kilka rozwiązań, chociaż żadne nie jest idealne, ale przynajmniej zrozumiałem co się dzieje. Będę myślał o:

1. Użyciu triggerów do wyliczania wynikowych cen (kolumn)
2. Pozbyciu się funkcji
3. Rezygnacji z widoków

Ewentualnie zostaje użycie triggerów do imitowania zmaterializowanego widoku.

Na razie składanim się ku triggerowi wyliczającemu wynikowe kolumny. To od razu uprości kod SQL i pozbycie się widoków nie będzie takie bolesne.

Sprawdź jeszcze wynik EXPLAIN SELECT...

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

Albo zmień bazę na postgresa, stwórz sobie widok zmaterializowany i ogólna wydajność pewnie też będzie szybsza.

Super sprawa z tymi widokami zmaterializowanymi, zapomniałem, że takie coś istnieje (poza MySQL przynajmniej).

Wywal ten widok i zakoduj zapytanie w aplikacji a potem go reużywaj. Strasznie dziwne że przy takiej ilości danych to kuleje.

Jaką masz wersję mysql jeszcze, jakie zasoby serwera.

I na prawdę, wywal widok w cholerę (IMG:style_emoticons/default/smile.gif)

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.

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ść.

Również fajny pomysł z triggerem, zawsze to jakieś rozwiązanie przy braku zmaterializowanych widoków.

Ten post edytował Zajec 15.09.2020, 21:12:36