Jogg.About::$Me->RaVbaker

MySQL vs. RaVbaker - 0:2

Ten wpis będzie nieco inny niż poprzednie. Zajmę się w nim dwoma zagadnieniami związanymi z bazą danych MySQL, na które niedawno natknąłem się w pracy.

Wykluczanie wyników z zapytania

Możliwe, że dla większości z was będzie to oczywiste rozwiązanie, dla mnie na pierwszy rzut oka nie było i sam do niego doszedłem.

Mamy dwie tabele:

Users

Tabela z danymi o naszych klientach, zawierająca takie pola jak: id, name, email itd.

Mailings

Tabela wysłanych maili, zawiera pola id, subject, content, user_id, recipient_email i inne nieistotne dla zadania.

Załóżmy, że w pracy dostalismy zadanie, aby w raporcie przedstawić menadżerowi, którzy z naszych klientów nie otrzymali jeszcze wielkanocnego mailingu o święcie knedliczek. Pierwsze moje podejście do tematu wyglądało następująco:

 
SELECT 
  u.* 
FROM users AS u
WHERE 
  id NOT IN (
    SELECT user_id
    FROM mailings
    WHERE 
      subject = 'Knedliczki 2009'
    GROUP BY user_id
  )                 

Niby proste i logiczne rozwiązanie. Każdy przecież rozumie, że wyciąga userów których nie ma w mailing. Ale, czy aby na pewno optymalne? Zauważmy, że explain zwraca:

id	select_type	    table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	            u	        ALL	NULL	        NULL	NULL	NULL	331061	Using where
2	DEPENDENT SUBQUERY  mailings	index	NULL	        user_id	4	NULL	1711784	Using where; Using filesort

Nie wygląda to zbyt interesująco. Widzimy, że MySQL musi użyć dwa razy wyszukiwania po where, używa tylko jednego indeksu (user_id), sortuje filesort i przejrzy bardzo dużo rekordów... 1711784*331061.

Optymalizacja? Oczywiście! Mój pomysł to użycie JOINa. W tym wypadku niezbędne dla nas będzie, aby pokazał nam wiersze, które są w jednej tabeli a nie mają powiązania z drugą, więc nasz LEFT JOIN będzie następujący:

SELECT 
  u.* 
FROM users AS u
LEFT JOIN mailings AS m
  ON m.user_id = u.id AND subject = 'Knedliczki 2009'
WHERE 
  m.id IS NULL

W tym przypadku wyszukujemy te rekordy których nie udało się bazie dopasować do maila i mają określony temat. Explain?

id	select_type	table	type	possible_keys	key	key_len	ref	          rows	Extra
1	SIMPLE	        m	const	PRIMARY,user_id	PRIMARY	4	const	          1	Using where
1	SIMPLE	        u	eq_ref	PRIMARY	        PRIMARY	4	nokaut.m.user_id  1	Using where

Rekordów do przeczesania zdecydowanie mniej, a także zmusiliśmy bazę do wykorzystania indeksów. Wydaje mi się, że lepiej usprawnić tego już się nie da.

Konkluzja? Porównywanie identyfikatora tabeli z którą łączymy LEFT JOINem do NULLa, czyli braku dopoasowania. Zdecydowanie usprawnia zapytania. Co o nim myślicie?

Tabele tymczasowe

Po dzisiejszych walkach z tworzeniem tabel tymczasowych do obróbki danych, myślę że powinienem się z wami podzielić moimi spostrzeżeniami.

Tabele tymczasowe mogą przykrywać już istniejące tabele!

Nie wiem, czy rozumiecie to zdanie, ale spróbuję wyjaśnić. Załózmy, że mamy tabelę z jakimiś danymi dziennymi. Np. z logiem odwiedzin i każdego dnia tworzymy tabelę dzienną, a następnie statystyki z ostatnich 30 dni przechowujemy w tabeli obok, typu MERGE:

• logs_2009_05_28 - logi z 28 maja 2009
• logs_2009_05_29 - logi z 29 maja 2009, itd...
• logs_last_30_days - tabela o engine=MERGE z logami z ostatnich 30 dni

Jednak chcielibyście wyciągnąć dane z ostatniego pół roku i dowiedzieć się, które strony są u nas najpopularniejsze. Co robicie? Piszecie prosty skrypcik w PHP, który stworzy odpowiednią tabelę mergującą w PHP i cieszycie się z prostego odpytania o konkretne informacje:

CREATE TEMPORARY TABLE logs_last_180_days_tmp (
  id int,
  url varchar(255),
  ip_address inet_addr
  http_referer varchar(255)
)  ENGINE=MERGE INSERT_METHOD=LAST UNION=(logs_2009_01_29,logs_2009_01_30,
 logs_2009_01_31, ..., logs_2009_05_29 );

I przydarzył się nam fail. Okazało się, że niektórych z tych 180 tabel z ostatniego pół roku nie ma. Myśl jest oczywista, założyć je tymczasowo gdy nie istnieją i pozwolić zapytaniu działać:

CREATE TEMPORARY TABLE IF NOT EXISTS {NAZWA_TABELI_DZIENNEJ_KTORA_NIE_ISTNIEJE} LIKE logs_structure

Niby wszystko zadziałało. Tabele się wygenerowały, łącznie z tą mergującą. Git! No to teraz odpytujemy:

SELECT 
  url, count(*) AS number_of_visits
FROM logs_last_180_days_tmp
GROUP BY url
ORDER BY count(*) DESC
LIMIT 10

... i usiłujemy się cieszyć z listy 10 najpopularniejszych naszych podstron. Niestety: 0 wyników

Gdzie popełniliśmy błąd?

Zaufaliśmy MySQLowi, że gdy użyjemi klauzuli: IF NOT EXISTS, to nie stworzy on żądanej tabeli jeśli ta już istnieje. Zasadniczo tak to działa, ale nie z tabelami tymczasowymi. Gdyż umieszcza on je w innej przestrzeni i poprzez CREATE TEMPORARY TABLE pozwala nam nawet przykryć już istniejące tabele nie zgłaszając żadnego komunikatu ani ostrzeżenia (tak było w mojej wersji 5.0.67). I właśnie w taki sposób nasze wszystkie tabele zostały nadpisane. Dobrze, że tylko na czas bieżącego połączenia z bazą.

Zalety?

Można wykorzystać do bezpiecznego uruchomienia np. testów jednostkowych na maszynach produkcyjnych, bez lęku o utratę danych, gdyż tabel tymczasowe "przykryją" te faktycznie posiadające dane i to tylko dla aktualnego połączenia. Bardzo wygodne, jeśli się już wie i nie debugowało się problemu przez pół godziny. :/

Tabele tymczasowe nie zapisują nigdzie jawnie informacji, że zostały utworzone

Problem, który kolejny chciałem rozwiązać, to testy jednostkowe na wyżej wymieniony problem. Mianowicie, chciałem prosta asercją wykryć, że żądana tabela tymczasowa została już utworzona. Nie da się! A przynajmniej grzebiąc w google'u i dokumentacji nie udało mi się znaleźć żadngo sensownego rozwiązania.

Jeśli chodzi o zwykłe tabele, to z nimi jest prosto:

SELECT *
FROM information_schema.TABLES
WHERE 
  TABLE_NAME = 'nazwa_tabeli'
  AND TABLE_SCHEMA = 'nazwa_bazy'

... i mamy szczegółowe informacje o konkretnej tabeli. Jednak, tabel tymczasowych nie można w ten sposób odpytywać.

Jedyny sposób na rozwiązanie testowania jej utworzenia było sprawdzenie, czy po odpytaniu jej o któryś z rekordów zwróciła go poprawnie. Chamskie, ale jedyne co działa.

To w zasadzie tyle, jeśli chodzi o moje ostatnie zmagania z MySQLem. I jak wam się podobało? Dajcie znać w komentarzach.

EDITED: Knedliczki na życzenie @dstranz-a i @madsheep-a się pojawiły.