Forum PHP.pl

Zaprojektowałem bazę danych do przechowywania dokumentów sprzedażowych z ich pozycjami. W uproszczeniu do istotnych kolumn wygląda ona tak:
Jak widać, w tabeli "invoices" nie przechowuję sumy pozycji. Dla wygody stworzyłem więc widok:

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?

1. Ile obecnie jest tych danych (faktur i pozycji)?
2. System umożliwia tworzenie pustych faktur (bez pozycji), a jeśli tak, to czy na potrzeby obliczania sumy muszą być one brane pod uwagę?

1. Tabela "invoices": 1619 pozycji, tabela "items": 24038 pozycji
2. Pustych faktur nie ma

Tyle wierszy przy tak prostym VIEW jeszcze nie byłoby tragedią, ale zapytanie - jak wspomniałem - jest jeszcze bardziej skomplikowane.
Tabela "items" ma jeszcze stawkę VAT. Najpierw w podzapytaniu grupuję wiersze "items" po kolumnie "vat_rate" i wyciągam sumę netto i brutto każdej stawki. Dopiero potem grupuję podsumowanie stawek i sumuję netto i brutto każdej stawki VAT.

Ten post edytował Zajec 12.09.2020, 12:31:45

1. Korzystam z CONSTRAINTS więc od początku miałem INDEX (BTREE) na "invoice_id'. Na kolumny z WHERE mogę jeszcze dodać indeksy, ale samo zapytanie z LIMIT (bez WHERE) jest wolne.
2. Spróbuję, faktycznie zapomniałem, że LEFT jest wolniejszy od INNER, ale nie spodziewam się drastycznej różnicy.

Sprawdź jeszcze wynik EXPLAIN SELECT...

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.


EXPLAIN to w MySQL jakiś żart 3 bezwartościowe informacje (wiersze)

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

A byłem taki dumny ze swoich widoków i prostego kodu SQL w aplikacji Serwer to dzielony hosting mydevil, MySQL 5.7.26, ale problem obserwuję też loklanie na MadiaDB 10.4.13.

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

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


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.

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.


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).