MySQL tags

We wpisie Chmura tagów w PHP, w którym został przedstawiony problem budowy chmury tagów zapisałem przykładowe zapytanie prezentujące przykładowe dane dla klasy, które dosłownie zabija bazę danych zliczając za każdym razem ilość występowań tagów. Dostając feedbacki, zauważyłem, że problem ten jest bagatelizowany przez wiele osób. Spróbujmy zbudować bardziej optymalne rozwiązanie zarządzania strukturą danych w taki sposób, aby dane wyciągać bardzo bezboleśnie.

Zbudujmy przykładową strukturę bazy danych tagów, do której będziemy przypinać różne rzeczy – newsy, artykuły, galerie zdjęć, zdjęcia, cokolwiek.

Najprostsza tabela db_tags o polach:

  • tag_id, UNSIGNED, aby zwiększyć zakres INT – wartości ujemne nie są nam porzebne. Oczywiście primary key oraz auto increment.
  • tag_name, chociażby varchar(255)
  • tag_count, UNSIGNED, INT, ponownie bez znaku, aby zwiększyć zakres, wartości ujemne są nam niepotrzebne. Tutaj będziemy przechowywać liczbę reprezentującą, ile razy użyto tagu do oznaczenia dowolnego zestawu informacji.
CREATE TABLE db_tags (
  tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  tag_name VARCHAR(255) NOT NULL ,
  tag_count INT UNSIGNED NOT NULL
) ENGINE = INNODB;

Zastanówmy się, po czym będziemy sortować tagi. Warto założyć klucz na pole tag_count, znacznie przyspieszy późniejsze sortowanie wyników po najpopularniejszych tagach. Jeżeli chcemy sortować po liczbie występowań tagu oraz nazwie (aby chmura była alfabetycznie), warto założyć wspólny klucz na tag_name oraz tag_count. Osobiście sortowanie alfabetyczne zostawiam implementacji klasie tagów dla ksort(), bowiem zapytanie wyciągające tagi jest obarczone limitem, zatem wspólny klucz w bazie danych nie jest mi potrzebny – mniej danych w indeksach.

ALTER TABLE db_tags ADD INDEX (tag_count);

Tworzymy dowolną strukturę danych, która będzie podpinała się do naszych tagów. Pamiętajmy, że do tagów może podpinać się (a przynajmniej powinno, zależy od założeń początkowych projektu) wiele struktur jednocześnie. Wybrałem najbardziej pospolite – newsy w tabeli db_news.

CREATE TABLE db_news (
  news_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  news_title TEXT NOT NULL,
  news_content TEXT NOT NULL
) ENGINE = INNODB;

Pozostało nam stworzyć tabelę wiążącą nasze newsy z tagami (nie tagi z newsami). Tabelę nazwałem db_news_tags. Zawierać ona będzie tylko dwa pola przechowujące identyfikator newsa oraz przypisanego do niego tagu, zachowując typ danych wiążących, czyli INT UNSIGNED. Zakładam wspólny primary key dla obu pól.

  • handler_item – klucz ID newsa,
  • handler_node – klucz ID tagu.
CREATE TABLE db_news_tags (
  handler_item INT UNSIGNED NOT NULL,
  handler_node INT UNSIGNED NOT NULL,
PRIMARY KEY (handler_item, handler_node)
) ENGINE = INNODB;

Buduję relacyjną bazę danych. Gdy jakiś tag zostanie usunięty, bądź gdy jakiś news zostanie usunięty, automatycznie powinien zniknąć wpis z tabeli db_news_tags, zatem używamy kluczy obcych:

ALTER TABLE db_news_tags ADD FOREIGN KEY (handler_item) REFERENCES db_news (news_id) ON DELETE CASCADE;
ALTER TABLE db_news_tags ADD FOREIGN KEY (handler_node) REFERENCES db_tags (tag_id) ON DELETE CASCADE;

Tak zaprojektowaną strukturę danych mogę spokojnie używać do przechowywania danych. Pozostaje kwestia obliczania ilości występowań tagów. Istnieją co najmniej dwie szkoły.

  1. Każda zmiana danych w db_news_handler wywołuje procedurę liczącą tagi. Trzeba mieć na uwadze, że tagi są przeliczane od początku mielenie bazy, ale de facto proces odbywa się po kluczach. Zaletą rozwiązania jest to, że przy bardzo rozbudowanych strukturach (np. liczymy tylko aktywne i widoczne tagi) procedura uwspólnia nam warunki podliczania, używając jej w wielu miejscach nie musimy się martwić o redefiniowanie triggerów.
  2. Dla przedstawionego przykładu w tym poście wystarczy inkrementacja licznika przy dodaniu i dekrementacja przy usunięciu tagu. W większości przypadków właśnie takiego rozwiązania powinno się używać.

Luźny komentarz techniczny (problems, tips & tricks): Aby ominąć problemy wynikłe z założenia w punkcie pierwszym, równie dobrze możemy napisać procedury, które inkrementują/dekrementują liczbę tagów w zależności od warunków (np. tylko wtedy, kiedy tag jest aktywny i widoczny w serwisie). Nikt nie powiedział, że procedury muszą liczyć wszystko od początku możemy się na takie rozwiązanie zgodzić, rezygnujemy natomiast z synchronizacji licznika podczas zmiany warunków, wówczas podczas każdej zmiany warunków, trzeba przekręcić licznik od początku, zliczając wszystkie rekordy wg. ustalonych warunków ręcznie. Triggera należałoby również umieścić w UPDATE (zmiana stanu tagu, np. z niewidocznego na widoczny, z aktywnego na nieaktywny). I to jest najrozsądniejsze rozwiązanie.

W naszym przypadku ograniczymy się do dwóch triggerów, które będą trzymały rękę na pulsie w momencie przypisania tagu do struktury INSERT oraz zerwaniu przypisania DELETE. Zatem:

CREATE TRIGGER NewsTagsCountInsert AFTER INSERT ON db_news_tags
  FOR EACH ROW BEGIN
    UPDATE db_tags SET tag_count = tag_count + 1 WHERE tag_id = NEW.handler_node;
  END

CREATE TRIGGER NewsTagsCountDelete AFTER DELETE ON db_news_tags
  FOR EACH ROW BEGIN
    UPDATE db_tags SET tag_count = tag_count - 1 WHERE tag_id = OLD.handler_node;
  END

Komentarz: bardziej eleganckim w większej strukturze danych byłoby wywołanie procedur inkrementujących i dekrementujących licznik – wówczas wykonywalibyśmy procedury (nie zapytania) w wielu strukturach wiązanych (nie tylko newsy, a video, ankiety, etc). Zmiana implementacji liczenia tagów byłaby wówczas wiele prostsza – zmienialibyśmy tylko procedurę, a nie każdy TRIGGER z osobna, zatem:

CREATE PROCEDURE TagsCountIncrement(IN iTagID INT)
BEGIN
  UPDATE db_tags SET tag_count = tag_count + 1 WHERE tag_id = iTagID;
END

CREATE PROCEDURE TagsCountDecrement(IN iTagID INT)
BEGIN
  UPDATE db_tags SET tag_count = tag_count - 1 WHERE tag_id = iTagID;
END

CREATE TRIGGER NewsTagsCountInsert AFTER INSERT ON db_news_tags
  FOR EACH ROW BEGIN
    CALL TagsCountIncrement(NEW.handler_node);
  END

CREATE TRIGGER NewsTagsCountDelete AFTER DELETE ON db_news_tags
  FOR EACH ROW BEGIN
    CALL TagsCountDecrement(OLD.handler_node);
  END

Finalnie, z czystym sumieniem:

SELECT tag_name, tag_count FROM db_tags ORDER BY tag_count LIMIT 0, 50
 

MySQL DELETE JOIN

Składowanie danych w kilku tabelach połączonych relacyjnie to bardzo dobry pomysł. Chyba najprostszym przykładem jest forum dyskusyjne: struktura oraz content postów mogą spokojnie być trzymane w osobnych tabelach. To samo tyczy się danych użytkowników. Rekordy rozbite na kilka tabel stają się mniej rozbudowane, o ile w ogóle występują – istnienie zawartości pola nie jest wtedy wymagane (użytkownik nie podał danych = nie ma rekordu).

Zaznaczając JOIN’ujemy tabele w zależności od potrzeb, co zdarza się bardzo często. O UPDATE JOIN już wspominałem, też bardzo wygodna operacja, natomiast, co w przypadku, gdy musimy usunąć rekord uzależniony od wartości pola w innej tabeli? Sprawa jest banalnie prosta.

Na początek kilka technicznych uwag, na które łatwo można się nadziać:

  • Najczęściej będziemy mieli przypadek, w którym wartość pola musi być znana = rekord w tabeli obok musi istnieć. Nie zapomnijmy o pełnym złączeniu tabel INNER JOIN.
  • Gdy czyścimy śmieci w bazie danych, chcemy, aby wartość pola była konkretna lub niezdefiniowana, tabele możemy złączyć lewostronnie LEFT JOIN.
  • Składnia DELETE FROM jest bardzo podobna do SELECT. Zaznaczamy alias_tabeli.* jako wybór rekordu do usunięcia. Możemy usuwać rekordy z kilku tabel oddzielając zaznaczenia przecinkami. Przy JOIN’ach wymagane jest zdefiniowanie aliasów i sprecyzowanie, co chcemy usunąć alias_tabeli.* (edit: nie jest wymagane definiowanie aliasów, przykłady niżej)

Przykłady.

Dane userów mam składowane w dwóch tabelach – w jednej podstawowe dane (id, name, pass, pass_salt, mail, status_active), w kolejnej dane (data [jako handler user -> user_data], data_* [* – jakieśdane]). Chcę usunąć wszystkich użytkowników, którzy zarejestrowali się przed 48-godzinami i nie aktywowali swoich kont, aby zwolnić unikalne nazwy użytkowników i adresy email. Jednym kryterium jest user_status_active z tabeli users, kolejnym jest data user_data_join z tabeli users_data. Jako, że mam założony kaskadowy foregin key na pole user_data w tabeli users_data, przy usunięciu rekordu z tabeli users pozbędę się również jego danych, o co dbać nie muszę przy wypisywaniu alias_tabeli.*. W przypadku, kiedy nie miałbym założonego foregin key, musiałbym obsłużyć usunięcie rekordu z users_data wypisując po przecinku tabelę. Zatem:

DELETE item.* FROM `cms_members` AS `item`
INNER JOIN `cms_members_data` AS `item_data` ON (item.user_id = item_data.user_data)
WHERE item.user_state_active = 0 AND item_data.user_data_join < NOW()

~Tiraeth przesłał rozwiązanie beż użycia aliasów i słowa kluczowego JOIN, odwołujemy się po nazwie tabeli:

DELETE cms_members.* FROM `cms_members`, `cms_members_data`
WHERE cms_members.user_id = cms_members_data.user_data AND user_state_active = 0 AND user_data_join < NOW()

Podzapytanie oraz INNER JOIN generuje nam iloczyn kartezjański:

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

Mam nadzieję, że komuś się przyda.

 

MySQL DATE() dla pola DATETIME

Oblicza MySQL nie są do końca znane przy tworzeniu aplikacji, a problemy optymalizacyjne stają się nie lada problemem przy funkcjonowaniu wersji produkcyjnej projektu. Nie sposób przewidzieć wszystkich możliwości użycia pól, założenia zarówno wspólnych, jak i pojedynczych indeksów posiadających zakładaną przez nas moc i zajętą pamięć na dysku.

Ostatnimi czasy budowałem dość skomplikowany projekt, jeżeli chodzi o złożoność zapytań i wykonywanych przez nie operacje matematyczne. Pomimo tego, że aplikacja była doskonale przemyślana, a struktury bazy danych perfekcyjnie jej podporządkowane, gdzieś tkwił problem, bowiem jedno z zapytań generowało pozornie prosty (wizualnie) rezultat, baza reagowała na zapytanie dopiero po 2.5 sekundy dla 30k+ rekordów. Patrząc na strukturę kluczy i zapytania, zwłaszcza, że pola, na których operowałem były różnego rodzaju liczbami i datami zacząłem się poważnie martwić i rozkładać zapytanie na czynniki pierwsze, kończąc na warunkach. Wyobraźcie sobie moje zdziwienie, gdy doszedłem do tego, że całe obciążenie (ponad 2.3 sekundy) generował warunek:

WHERE DATE(ticket_date) >= " ... "

Gdzie ticket_date to pole typu DATETIME. Od razu doszedłem do wniosku, że w parze idzie złe przygotowanie danych przez PHP, a angażowana jest w to wszystko baza, na której forsuje się użycie funkcji DATE(). Przynajmniej dla 30k+ rekordów zindeksowanego pola. Prosty zabieg zamiany jednej linijki kodu na drugą przyniósł porządane efekty.

$aTerms[] = 'DATE(ticket_date) >= "' . $sDate . '"'
$aTerms[] = 'ticket_date >= "' . date('Y-m-d H:i:s', strtotime($sDate)) . '"'

Budując aplikację zwracam szczególną uwagę na strukturę bazy, indeksowanie pól, rysuję diagramy przewidujące wykorzystanie danych pod różne zapytania, ale… tak banalny błąd przy przeanalizowanej aplikacji rozłożył mnie na łopatki. Z drugiej strony, zapomniałem o jednej bardzo ważnej rzeczy: maksymalnym odciążeniu bazy danych przy preparowaniu argumentów warunków, skoro warunki te mogą być w odpowiedni i przede wszystkim szybki sposób spreparowane na poziomie modelu (abstrakcyjnie rzecz ujmując, pozbywam się pojęcia PHP), który przygotują zapytanie tylko do wykonania operacji na surowych danych, bez konieczności ich ewentualnego przeliczania. Oczywiście nie zawsze taki efekt da się uzyskać, ale należy to maksymalnie optymalizować.

Jedno jest wiadome: przeliczanie DATE() dla rekordów w warunku jest nieoptymalne dla pola DATETIME.

 

MySQL: remove duplicate entries/rows

Usuwając coś permanentnie z bazy danych musimy być bardzo ostrożni, bowiem przywrócenie danych jest bardzo trudne, czasem niemożliwe. Podstawową strukturę bazy danych powinno się budować na samym początku tworzenia aplikacji, z biegiem czasu rozbudowywać ją, ale unikać przebudowywania. Niestety są przypadki, gdzie trzeba przebudować jedną rzecz, co powoduje zmianę w wielu warstwach nie tyle aplikacji, co strukturze bazodanowej.

Dziś postaram się opisać, jakie kroki trzeba wykonać, aby bezpiecznie usunąć zdublowane rekordy z bazy danych nie tracąc żadnych danych:

  1. Tworzymy dwie kopie bazy danych lub tabel, na których będziemy pracowali. Najlepiej, aby pracować na drugiej kopii, nigdy na oryginale, a potem wdrożyć zmiany z drugiej kopii na oryginał. Przezorny zawsze ubezpieczony.
  2. Analiza danych w tabeli. Musimy dokładnie wiedzieć jakie są relacje między tabelami, kiedy występują JOIN’y itp. Jeżeli rekordy są zdublowane, a posiadają ustalony ID, do których odwołuje się inny rekord z sąsiedniej tabeli, trzeba będzie w niej zmienić ID rekord zdublowanego na ID „substytuta”, bądź takiego rekordu, który nie spowoduje zmian w serwisie.
  3. Wykonanie operacji usunięcia zdublowanych rekordów.

Po wykonaniu kroku pierwszego zabieramy się za kolejny. Jest to najważniejszy moment naszych operacji. Aby ułatwić zrozumienie problemu, podam przykład z życia. Aplikacja posiadała poważny błąd, który umożliwiał zdublowanie użytkowników, ściślej: można było zdublować username. Za każdym razem, gdy użytkownik się logował i pisał komentarze, był ich właścicielem, ale comment_author posiadały różne ID tego samego użytkownika. Zaraz po skopiowaniu bazy danych spróbowałem przepisać ID autorów komentarzy na pierwszy rekord identyfikujący użytkownika, jaki istnieje w tabeli użytkowników. Skonstruowałem zapytanie:

UPDATE cms_comments
JOIN cms_members AS user_original ON(user_original.user_id = comment_author)
SET comment_author = (
  SELECT user_first.user_id FROM cms_members AS user_first
  WHERE user_first.user_name = user_original.user_name
  ORDER BY user_first.user_id ASC LIMIT 0, 1)

Usunięcie zdublowanych użytkowników było już tylko formalnością. Teraz się okaże, dlaczego zależało mi na wyciągnięciu dokładnie pierwszego rekordu reprezentującego „unikalnego” użytkownika: poniższe zapytanie (ALTER IGNORE TABLE ADD UNIQUE) usunie wszystkie kolejne rekordy oznaczone jako duplicated:

ALTER IGNORE TABLE cms_members ADD UNIQUE INDEX(user_name);

Krótki komentarz z manuala do ALTER TABLE:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

Pisząc ostatnie posty związane z bazami danych, mam nadzieję, że komuś się przydadzą.

 

MySQL: how to convert NULL to 0 number/int

Im więcej nietypowych rzeczy programuję, tym więcej nietypowych problemów musze pokonać. Co powiecie na sumę 2 liczb, z których jedna jest wartością NULL powstałą w wyniku działania SUM() lub pochodnych, gdzie nie odnaleziono żadnego rekordu.

Badamy:

SELECT 1+2+3
>> 6

SELECT 1+2+NULL
>> NULL

SELECT COALESCE( NULL, 0 )
>> 0

Zatem analogicznie do powyższego przykładu:

UPDATE users SET user_points = user_points + COALESCE((SELECT SUM( ... ) WHERE ...), 0)

Punkty użytkownika już zawsze będą się sumowały poprawnie 🙂

 

MySQL UPDATE JOIN

Ostatnimi czasy potrzebowałem danych z sąsiedniej tabeli przy UPDATE jedngo z pól w bazie danych. Danych do przetworzenia było sporo, więc zwracałem uwagę na wydajność zapytania. Aby zebrać potrzebne informacje, można użyć jednego ze sposobów:

  1. Zebrać potrzebne dane za pomocą SELECT’a, co sprawiłoby, że zajęta zostanie niepotrzebna pamięć w środowisku PHP podczas przypisania rezultatu do zmiennej.
  2. Wykonać SET z podzapytaniem, ale potrzebnych mi było kilka kolumn z sąsiedniej tabeli, podzapytanie może zwrócić tylko jedną określoną wartość.
  3. Wykonać JOIN przy update, czego niestety wówczas nie potrafiłem zrobić.

Kartkując manual nie natrafiłem się w standardowej dokumentacji na nic konkretnego, aż nie spojrzałem na bardzo przydatne komentarze użytkowników. Okazało się, że przy UPDATE można wykonywać dowolne JOIN’y, schemat jest następujący:

UPDATE table JOIN another_table SET ...

W tym momencie mamy do dyspozycji wszystkie pola z dołączonej tabeli. Bardzo przydatne.

Przykład z życia.

Miałem za zadanie odznaczyć typy bukmacherskie na trafione, nietrafione, odwołane z przyczyn odwołania całego meczu piłkarskiego oraz te, które jeszcze nie mogą zostać oznaczone jako trafione lub nie, gdyż mecz się jeszcze nie odbył.

UPDATE typer_tickets_items
LEFT JOIN typer_events ON(event_id = item_event)
SET item_status = (
CASE
WHEN event_status IS NULL THEN NULL # mecz nie zostal rozegrany
WHEN event_status = -1 THEN -1 # mecz anulowany
WHEN event_status = item_bet THEN 1 # typ trafiony
ELSE 0 END # typ nietrafiony
) WHERE item_status IS NULL

Mam nadzieję, że komuś się przyda…

 

Przypadki w MySQL – CASE WHEN THEN ELSE END

Podobnie jak w PHP, baza danych MySQL ma odpowiednik if, czyli przypadków (inaczej serii warunków, instrukcji warunkowych). Różnicą między implementacją CASE’a w MySQL i ifa PHP jest to, że baza danych zwraca konkretną wartość z case’a, a nie wykonuje dowolnej ilości dowolnych akcji.

CASE Syntax:

Najprostsza struktura CASE’aprzedstawia się nastepująco:

CASE WHEN [conditions] THEN ... ELSE ... END

Składnia powinna rozpocząć się słowem kluczowym CASE, a zakończyć END. Pomiędzy znajdują się warunki WHEN oraz operacja zwrócenia odpowiedniej wartości, która po nich następuje THEN (mamy możliwość uwzględnić nieskończenie wiele warunków). Jeżeli żaden warunek nie zostanie spełniony możemy użyć opcjonalnie ELSE.

Przykłady z życia.

Wyobraźmy sobie, że mamy posortować listę aukcji przedmiotów na Allegro od najtańszych, do najdroższych. Należy założyć, że są 2 typy aukcji: kup teraz i licytacja. Pole licytacji w bazie danych zawiera największą zaproponowaną kwotę przez użytkowników w procesie licytacji, a cena kup teraz ustalana jest przez sprzedającego. Są to dwa różne pola w bazie danych, a jedno kryterium sortowania, dlatego trzeba scalić cenę w jedną, wybierając odpowiednią. Musimy przewidzieć sytuację, w której aukcja jest typu kup teraz oraz licytacji, wówczas jeżeli najwyższa oferta jest większa od ceny kup teraz, wówczas wybieramy pole z największa propozycją:

SELECT (
  CASE
    WHEN (auction_type = 'bidding' OR auction_price_bid > auction_price_buynow)
      THEN auction_price_bid
    ELSE auction_price_buynow
  END) AS auction_price

Stworzyliśmy pole auction_price, po którym można sortować aukcje od najtańszej do najdroższej i na odwrót.

Mam nadzieję, że krótki wpis przyda się początkującym. Nic więcej nie trzeba opisywać, temat wydaje się co najmniej trywialny.