Projekt i wybór bazy - normalizacja oraz wydajność widoków |
Projekt i wybór bazy - normalizacja oraz wydajność widoków |
12.09.2020, 11:49:17
Post
#1
|
|
Grupa: Zarejestrowani Postów: 1 086 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:
Czy ktoś ma jakieś inne pomysły albo ogólnie sugestie? |
|
|
12.09.2020, 12:12:23
Post
#2
|
|
Grupa: Zarejestrowani Postów: 6 765 Pomógł: 1822 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ę? -------------------- |
|
|
12.09.2020, 12:31:01
Post
#3
|
|
Grupa: Zarejestrowani Postów: 1 086 Pomógł: 8 Dołączył: 10.12.2003 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ę? 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 |
|
|
12.09.2020, 12:37:14
Post
#4
|
|
Grupa: Zarejestrowani Postów: 6 765 Pomógł: 1822 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. -------------------- |
|
|
12.09.2020, 12:47:40
Post
#5
|
|
Grupa: Zarejestrowani Postów: 1 086 Pomógł: 8 Dołączył: 10.12.2003 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. 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. |
|
|
12.09.2020, 13:26:05
Post
#6
|
|
Grupa: Zarejestrowani Postów: 6 765 Pomógł: 1822 Dołączył: 11.03.2014 Ostrzeżenie: (0%) |
Sprawdź jeszcze wynik EXPLAIN SELECT...
-------------------- |
|
|
12.09.2020, 13:30:49
Post
#7
|
|
Grupa: Zarejestrowani Postów: 6 366 Pomógł: 1115 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.
-------------------- |
|
|
12.09.2020, 16:17:47
Post
#8
|
|
Grupa: Zarejestrowani Postów: 8 068 Pomógł: 1414 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ę |
|
|
13.09.2020, 08:51:46
Post
#9
|
|
Grupa: Moderatorzy Postów: 6 070 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ść.
|
|
|
15.09.2020, 21:10:43
Post
#10
|
|
Grupa: Zarejestrowani Postów: 1 086 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:
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:
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 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. 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.Jaką masz wersję mysql jeszcze, jakie zasoby serwera. I na prawdę, wywal widok w cholerę 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 |
|
|
16.09.2020, 06:04:45
Post
#11
|
|
Grupa: Zarejestrowani Postów: 6 765 Pomógł: 1822 Dołączył: 11.03.2014 Ostrzeżenie: (0%) |
EXPLAIN to w MySQL jakiś żart 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 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. -------------------- |
|
|
16.09.2020, 06:21:51
Post
#12
|
|
Grupa: Zarejestrowani Postów: 1 086 Pomógł: 8 Dołączył: 10.12.2003 Ostrzeżenie: (0%) |
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:
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). |
|
|
Wersja Lo-Fi | Aktualny czas: 28.05.2024 - 15:04 |