Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

 
Reply to this topicStart new topic
> Projekt i wybór bazy - normalizacja oraz wydajność widoków
Zajec
post 12.09.2020, 11:49:17
Post #1





Grupa: Zarejestrowani
Postów: 1 085
Pomógł: 8
Dołączył: 10.12.2003

Ostrzeżenie: (0%)
-----


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?
Go to the top of the page
+Quote Post
trueblue
post 12.09.2020, 12:12:23
Post #2





Grupa: Zarejestrowani
Postów: 5 934
Pomógł: 1619
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


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ę?


--------------------
Go to the top of the page
+Quote Post
Zajec
post 12.09.2020, 12:31:01
Post #3





Grupa: Zarejestrowani
Postów: 1 085
Pomógł: 8
Dołączył: 10.12.2003

Ostrzeżenie: (0%)
-----


Cytat(trueblue @ 12.09.2020, 13:12:23 ) *
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
Go to the top of the page
+Quote Post
trueblue
post 12.09.2020, 12:37:14
Post #4





Grupa: Zarejestrowani
Postów: 5 934
Pomógł: 1619
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


1. To tyle co nic. Założone są indeksy na kolumny złączenia i ewentualne warunki?
2. W takim razie zastąp LEFT JOIN, INNER JOIN.


--------------------
Go to the top of the page
+Quote Post
Zajec
post 12.09.2020, 12:47:40
Post #5





Grupa: Zarejestrowani
Postów: 1 085
Pomógł: 8
Dołączył: 10.12.2003

Ostrzeżenie: (0%)
-----


Cytat(trueblue @ 12.09.2020, 13:37:14 ) *
1. To tyle co nic. Założone są indeksy na kolumny złączenia i ewentualne warunki?
2. W takim razie zastąp LEFT JOIN, INNER JOIN.

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.
Go to the top of the page
+Quote Post
trueblue
post 12.09.2020, 13:26:05
Post #6





Grupa: Zarejestrowani
Postów: 5 934
Pomógł: 1619
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


Sprawdź jeszcze wynik EXPLAIN SELECT...


--------------------
Go to the top of the page
+Quote Post
viking
post 12.09.2020, 13:30:49
Post #7





Grupa: Zarejestrowani
Postów: 5 759
Pomógł: 980
Dołączył: 30.08.2006

Ostrzeżenie: (0%)
-----


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


--------------------
Go to the top of the page
+Quote Post
Pyton_000
post 12.09.2020, 16:17:47
Post #8





Grupa: Zarejestrowani
Postów: 7 962
Pomógł: 1396
Dołączył: 26.10.2005

Ostrzeżenie: (0%)
-----


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ę smile.gif
Go to the top of the page
+Quote Post
phpion
post 13.09.2020, 08:51:46
Post #9





Grupa: Moderatorzy
Postów: 6 066
Pomógł: 860
Dołączył: 10.12.2003
Skąd: Dąbrowa Górnicza




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ść.
Go to the top of the page
+Quote Post
Zajec
post 15.09.2020, 21:10:43
Post #10





Grupa: Zarejestrowani
Postów: 1 085
Pomógł: 8
Dołączył: 10.12.2003

Ostrzeżenie: (0%)
-----


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:

  1. CREATE FUNCTION final_price (price INTEGER(11), discount SMALLINT(2))
  2. RETURNS INTEGER(11) DETERMINISTIC
  3. RETURN IF(discount IS NULL, price, price * (100 - discount) / 100);
  4.  
  5. CREATE FUNCTION price_to_total_gross (price INTEGER(11), discount SMALLINT(2), quantity INTEGER(11), vat_rate SMALLINT(2))
  6. RETURNS INTEGER(11) DETERMINISTIC
  7. RETURN final_price(price, discount) * quantity * (100 + vat_rate) / 100;


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.


Cytat(trueblue @ 12.09.2020, 14:26:05 ) *
Sprawdź jeszcze wynik EXPLAIN SELECT...
EXPLAIN to w MySQL jakiś żart sad.gif 3 bezwartościowe informacje (wiersze)

Cytat(viking @ 12.09.2020, 14:30:49 ) *
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).

Cytat(Pyton_000 @ 12.09.2020, 17:17:47 ) *
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ę smile.gif
A byłem taki dumny ze swoich widoków i prostego kodu SQL w aplikacji tongue.gif Serwer to dzielony hosting mydevil, MySQL 5.7.26, ale problem obserwuję też loklanie na MadiaDB 10.4.13.

Cytat(phpion @ 13.09.2020, 09:51:46 ) *
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
Go to the top of the page
+Quote Post
trueblue
post 16.09.2020, 06:04:45
Post #11





Grupa: Zarejestrowani
Postów: 5 934
Pomógł: 1619
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


Cytat(Zajec @ 15.09.2020, 22:10:43 ) *
EXPLAIN to w MySQL jakiś żart sad.gif 3 bezwartościowe informacje (wiersze)

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

Cytat(Zajec @ 15.09.2020, 22:10:43 ) *
A byłem taki dumny ze swoich widoków i prostego kodu SQL w aplikacji 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.


--------------------
Go to the top of the page
+Quote Post
Zajec
post 16.09.2020, 06:21:51
Post #12





Grupa: Zarejestrowani
Postów: 1 085
Pomógł: 8
Dołączył: 10.12.2003

Ostrzeżenie: (0%)
-----


Cytat(trueblue @ 16.09.2020, 07:04:45 ) *
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.

Cytat(trueblue @ 16.09.2020, 07:04:45 ) *
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:
  1. IF(discount IS NULL, price, price * (100 - discount) / 100) * quantity * (100 + vat_rate) / 100 AS total

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).
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 Użytkowników czyta ten temat (1 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 



RSS Wersja Lo-Fi Aktualny czas: 19.09.2020 - 19:16