UPDATE z SELECT w MySQL oraz PostgreSQL
Często dostaje pytanie, jak zrobić UPDATE na całej tabeli z wykorzystaniem polecenia SELECT.
To pytanie powstaje przez błędne założenie, że trzeba znać konkretne ID danego wiersza w bazie danych.
Wiele osób próbuje zatem zrobić pętlę w np. w PHP, która robi ręcznie update na każdym wierszu z osobna.
Wygląda to mniej więcej tak:
Niepoprawne rozwiązanie
<? $subjectArray = $db->sql('SELECT id FROM subject'); foreach ($subjectArray as $key => $value) { $iloscPostowArray = $db->sql('SELECT count(id) AS posts_count FROM posts WHERE subject_id='.$value['id']); $iloscPostow = $iloscPostowArray[0]['posts_count']; $db->sql("UPDATE subject SET posts_count=$iloscPostow WHERE id=".$value['id']); } ?>
Przykładowy schemat naszej bazy
Mamy dwie tabele: subject i posts
Tabela "subject":
- id - id danego tematu
- posts_count - liczba postów w danym temacie
Tabela "posts":
- id - id danego postu
- subject_id - id tematu do którego należy post
Dlaczego powyższe rozwiązanie jest niepoprawne
Z prostego powodu: przy dużej bazie skrypt może nie wyrobić z pamięcią lub nie zmieścić się w czasie wykonywania (nie zawsze mamy możliwość zmodyfikowania odpowiednio plików konfiguracyjnych). Już nie wspomnę nawet o tym, że takie rozwiązanie powoduje u nas ogromną liczbę zapytań SQL.
Poprawne rozwiązanie
UPDATE subject SET posts_count=(SELECT COUNT(id) FROM posts WHERE subject_id = subject.id)
Tak jak widzimy cały kod został sprowadzony do jednego polecenia SQL. Jest to o wiele lepsze rozwiązanie tego problemu.
UWAGA
Przy tworzeniu kolumny działającej na zasadzie naszego posts_count z tabeli subject, pamiętajmy o aktualizowaniu liczby postów najlepiej przy użyciu triggerów
Zapraszam do dyskusji, spisywania innych rozwiązań z użyciem UPDATE i SELECT. Jeżeli znajdziecie jakieś błędy w tym co napisałem, to też proszę mi to zgłosić ;)
±
Komentarze do wpisu "UPDATE z SELECT w MySQL oraz PostgreSQL":
1.
09 września 2010, 15:45:35
Widzę, że kolorowanie składni "trochę" zmniejszyło się. Zaraz to poprawię.
2.
09 września 2010, 15:54:06
Programowanie w oparciu o triggery to przy większych rzeczach zło w czystej postaci. Nie panuje się nad kodem, oraz to co powinno/wykonało się w danej chwili. Lepszym pomysłem jest taki kod, gdzie jawnie wykonujemy to co trzeba, tam gdzie trzeba.
3.
09 września 2010, 16:13:19
@m.: Kto co lubi :)
Przy np. dodawaniu postów do danego tematu, automatycznie nam się zwiększa liczba postów przy odpowiednim zaprogramowaniu triggera, dzięki czemu nie musimy tego programować za każdym razem w PHP (np. cztery różne miejsca w kodzie pozwalają na dodanie postu do tematu, przez co w każdym tym miejscu musielibyśmy w PHP napisać zwiększanie ilości postów w danym temacie, a tak to mamy trigger ;))
4.
09 września 2010, 17:28:56
To wołasz w tych czterech miejscach procedurę dodaj_komentarz(args), która to obsługuje, przecież chyba nie klepiesz w tych czterech miejscach tego samego SQLa? Za przedmówcą - triggerów w czymkolwiek, nad czym chcesz panować, się nie używa.
5.
09 września 2010, 18:50:41
@VeD, m: nie zgodzę się. Są sytuacje kiedy triggery wyjątkowo się sprawdzają. Jeśli są dobrze napisane to nie ma z nimi żadnego problemu (poparte na serwisach w których je wdrożyliśmy). Poza tym nie zawsze jest możliwość aby z poziomu kodu zapewnić to co dają nam triggery (rzadkie sytuacje, ale czasami się zdarzają).
Z resztą trigerry wykonują to "gdzie trzeba i kiedy trzeba", także tym bardziej nie rozumiem sytuacji. Sytuacja wygląda nieco inaczej jeśli chodzi o wydajność, wtedy trzeba się zastanowić nad tym czy wprowadzone triggery nie zawalą działania aplikacji.
6.
09 września 2010, 19:27:44
Lanie wody. Jakieś statystyki masz co potwierdzają lepsiejszość jednego rozwiązania nad drugim? Że np. dla miliona updejtów wykonało się 2% szybciej?
No i w zasadzie to tych zapytań będzie i tak tyle samo. Poza "ładniejszym" kodem chyba nie ma żadnej korzyści.
Zresztą oba rozwiązania są złe, bo takie pierdoły się robi na triggerach czy innych kolejkach ;)
7.
09 września 2010, 20:46:56
@Romek: To był przykład z tymi postami i tematami. Pamiętaj o tym, że czasem ktoś dopiero po jakimś czasie może chcieć dodać kolumnę działającą na zasadzie posts_counts i jeżeli wcześniej nie miał zaprogramowanych triggerów to musi jakoś uzupełnić te dane. Tak jak mówiłem z postami i tematami to był tylko przykład.
Kolejna kwestia o której pisałem: przy dużej ilości danych pierwsze rozwiązanie jest złe. Sam musiałem wgrać ostatnio do bazy kilka milionów wierszy danych. PHP siadł bo miał za mało pamięci na wczytanie tych danych. Tak samo by było w przypadku pobrania danych selectem do tablicy. Przy zbyt dużej ilości danych obciążymy cały serwer lub skrypt nie zadziała bo zabraknie mu pamięci. Możemy to robić seriami, ale wtedy dojdą nam kolejne zapytania SQL.
Co do zapytania SQL tego "poprawnego":
Tam działa to na zasadzie pętli ze zwalnianiem z pamięci wykonanego wiersza.
8.
09 września 2010, 23:58:37
> Często dostaje pytanie
co to za ktosie ?
9.
10 września 2010, 12:02:09
@Radmen: a co konkretnie masz na myśli pisząc o rzeczach, które są dostępne z poziomu triggera, a kodu nie? Zawsze to z chęcią się czegoś nowego dowiem. :) Może trochę przesadziłem z tym całkowitym nieużywaniem - generalnie chodziło mi o duże bazy, gdzie tak jak piszesz, triggery psują wydajność. Jak dla mnie używa się tego zwykle do jakiejś walidacji, co moim zdaniem lepiej zrobić po stronie klienta/serwera, albo do zmieniania rekordów powiązanych, co też można wrzucić do jakiejś procedury na bazie.
@Romek: na oko obstawiałbym różnicę kilkudziesięciu procent w wydajności. I nie, zapytań nie będzie tyle samo. Jeśli nie wyczułem ukrytej ironii to z góry przepraszam. :)
10.
10 września 2010, 20:34:32
VeD: co do pierwszego: przykład z pracy: do tabeli z syncami musimy wrzucać wszystkie nowe rekordy. Nie można tego zrobić z poziomu aplikacji, bo jest w niej tak namieszane, że jej twórcy sami nie mają pewności gdzie co dodać.
co do drugiego: w dużych bazach triggery też są dobre, trzeba tylko uważać na ich budowę. Zakładaliśmy triggery na bazę która w swoich tabelach posiada kilkaset tysięcy (i więcej) rekordów, póki co nie ma z tym problemów.
11.
11 września 2010, 11:23:37
@radmen:
Kilkaset tysięcy rekordów to wg Ciebie duża baza? No to gratuluję ;-)
Jeśli chodzi o "zuo" jakim są triggery, to wszystko zależy od tego czy umiejętnie je stosujesz. Jeśli trigger na jednej tabeli robi update/insert na innej, co powoduje odpalenie kolejnych 3 triggerów, które powodują update/insert na kolejnej tabeli, na której są kolejne triggery itd itp, to robi Ci się absolutna rozpierducha.
Jeśli po czymś takim stwierdzasz nagle, że coś się gdzieś rozjechało, to jesteś w głębokiej, murzyńskiej dupie, bo zdebugowanie tego to istny koszmar.
12.
11 września 2010, 13:01:44
@ellem: dlatego mówię, że trzeba uważać z triggerami. Kilkaset tysięcy rekordów to już sporo jak na serwis internetowy, który posiada tylko jedną centralną bazę - już wtedy trzeba zwracać uwagę na to co się robi, bo małe błędy mogą gwarantować spore problemy wydajnościowe.
13.
11 września 2010, 13:24:18
Radmen, czyli to nie kwestia, że trigger daje ci jakieś magiczne możliwości, tylko aplikacja jest mocno zryta. Co do drugiej części, to ja miałem na myśli duże bazy. Jeśli masz te kilkaset tysięcy rekordów, to właściwie nie zauważysz różnicy w czasie działania, bo pewnie operacje na bazie dokonują się stosunkowo rzadko. Takie rzeczy to by prawdopodobnie mój desktop udźwignął, a wy pewnie macie dodatkowo jakiś porządny sprzęt pod to postawiony. Nakład triggera widać, jak te kilkaset tysięcy rekordów nie tyle leży, ile ląduje w bazie w ciągu, dajmy na to, godziny czy doby. Co do reszty: ellem +1.
I jeszcze mi do głowy przychodzi klasyka gatunku pt. "wyłączę tego triggera tylko na chwilę". ;-)
14.
11 września 2010, 13:30:41
@VeD: zgodzę się z Tobą. Natomiast spotkaliśmy się z tym, że po prostu trigger był jedynym wyjściem (bo jak wspomniałeś aplikacja jest zryta) - jedyną magiczną właściwością było to, że nie musieliśmy się zastanawiać gdzie go podpiąć.
Dodaj komentarz: