UPSERT wielu wierszy w MySQL

Dzisiaj zacznę odwrotnie niż zwykle czyli od prezentacji gotowego rozwiązania:

INSERT INTO user_counters (user_id, counter_type, counter_value, updated_at)
VALUES
    (:user_id1, :counter_type1, :counter_value1, :updated_at1),
    (:user_id2, :counter_type2, :counter_value2, :updated_at2),
    # ...
    (:user_idN, :counter_typeN, :counter_valueN, :updated_atN)
ON DUPLICATE KEY UPDATE
    counter_value = counter_value + VALUES(counter_value),
    updated_at = VALUES(updated_at);

To tak zwany UPSERT czyli zapytanie, które wstawia rekord(y) do bazy, a jeśli taki już istnieje (tzn. istnieje już taki klucz główny lub unikalny) to go zaktualizuje (wartość w kolumnie counter_value zostanie powiększona o nową wartość, a w kolumnie updated_at zastąpiona). To zapytanie jest jednak trochę lepsze od tych, które zazwyczaj podaje się jako przykład UPSERTA, ponieważ pozwala zapisać wiele (dziesięć, sto, tysiąc) wierszy za jednym zamachem. Sprawia to użyta w części UPDATE funkcja VALUES(), która pobiera wartości ze wskazanej kolumny w części INSERT.

Po co się w ogóle tak babrać w SQL-u jak zwierzę skoro Doctrine mógłby to zrobić za nas? Akurat tego nie zrobi. Przy użyciu Doctrine należałoby:

  1. Pobrać wszystkie pasujące rekordy (jeden SELECT).
  2. Przeiterować przez nie i:
    • zaktualizować wartości,
    • zidentyfikować brakujące rekordy.
  3. Utworzyć encje brakujących rekordów.
  4. Opróżnić bufor managera encji (flush, który zapewne wykona jeden INSERT i jeden UPDATE).

Jak widać zarówno po stronie aplikacji (PHP) jak i bazy to wyraźnie więcej pracy, która i tak pewnie poszłaby na marne, bo przy kilku pracujących jednocześnie instancjach aplikacji jest spora szansa na wyjątek UniqueConstraintViolationException, który zamknie managera encji.

UPSERT z wieloma wartościami rozwiązuje problem wydajności i problem naruszania ograniczeń, ale tworzy klikna innych:

  • Przy jednoczesnych instancjach aplikacji łatwo o deadlocki na bazie. Trzeba zastosować jakiś mechanizm blokowania.
  • Jeśli w ten sposób zapisujemy dane z encji Doctrine’a to ich stan może być nieaktualny.

INSERT … ON DUPLICATE KEY UPDATE nie jest częścią standardu SQL, to rozszerzenie dostępne w MySQL. W PostgrSQL jest podobny mechanizm, ale jego działanie jest nieco inne, więc może opiszę je przy innej okazji.

WHERE warunek vs LEFT JOIN warunek

Być może nie powinienem publicznie przyznawać się, że popełniłem tak podstawowy błąd w konstruowaniu zapytania SQL, ale po jego odkryciu szczerze się uśmiałem, a skoro śmiech to zdrowie to się nim podzielę.

W uproszczeniu: mam tabelkę z danymi klientów (client) i tabelkę produktów (product), pomiędzy którymi istnieje relacja jeden do wielu. Wyciąganie danych do pokazania jest zrealizowane w Doctrine w następujący sposób:

$qb
    ->select('customer, product')
    ->from($this->getEntityName(), 'customer')
    ->leftJoin('customer.products', 'product')
    ->where('customer.identifier = :identifier')
    ->setParameter('identifier', $identifier);

Dostałem za zadanie zmienić wyświetlanie danych w ten sposób by pokazywać jedynie aktywne produkty. Zrobiłem to tak:

$qb
    ->select('customer, product')
    ->from($this->getEntityName(), 'customer')
    ->leftJoin('customer.products', 'product')
    ->where('customer.identifier = :identifier')
    ->andWhere('product.isActive = 1')
    ->setParameter('identifier', $identifier);

Ku memu zdziwieniu w wypadku klienta, który ma tylko jeden nieaktywny produkt wynik zapytania był pusty. No jakże to?! Przecież klient jak najbardziej istnieje, a ponieważ nie ma produktów spełniających kryteria wyszukiwania (isActive = 1) to w miejscu danych produktu powinny być NULLe.

Swój błąd zrozumiałem dopiero, kiedy spojrzałem na generowane przez Doctrine zapytanie SQL. W uproszczeniu wygląda ono tak:

SELECT 
  c.name,
  p.product_type
FROM 
  customer c 
  LEFT JOIN product p ON c.id = p.customer_id 
WHERE 
  c.identifier = '65092900214'
  AND p.is_active = 1

Ponieważ warunek p.is_active = 1 sprawdzamy w części WHERE zapytania to eliminujemy z zapytania wszystkie wiersze, w których on nie jest spełniony. Zatem jeśli klient ma tylko jeden nieaktywny produkt to wynik zapytania będzie pusty.

Oczekiwany efekt (wiersz z NULLami w miejscu danych produktu) uzyskamy przenosząc warunek do części LEFT JOIN zapytania:

SELECT 
  c.name,
  p.product_type
FROM customer c
  LEFT JOIN product p ON c.id = p.customer_id 
  AND p.is_active = 1
WHERE 
  c.identifier = '65092900214'

Ponieważ warunek dotyczy złączenia to jeśli nie zostanie on spełniony to złączenie nie zostanie wykonane, ale dane klienta zostaną zwrócone. Dokładnie tak jak chciałem.

W Doctrine poprawne zapytanie należy zbudować tak:

$qb
    ->select('customer, product')
    ->from($this->getEntityName(), 'customer')
    ->leftJoin('customer.products', 'product', 'WITH', 'product.isActive = 1')
    ->where('customer.identifier = :identifier')
    ->setParameter('identifier', $identifier);

Klucz kluczem do sukcesu

Mechanizm planowania zapytań w MySQL jest mniej sprytny niż sądziłem. Chciałem wykonać z pozoru łatwe zapytanie: z bazy użytkowników liczącej ok 130 tys. wierszy chciałem wyłuskać osoby mieszkające pod określonymi kodami pocztowymi (ok. 20 tys.). Dodatkowym warunkiem było podanie numeru telefonu, który znajduje się w innej tabelce oraz wyrażenie zgody na marketing, ale to nie jest istotne dla głównego problemu. Napisałem takie zapytanie:

SELECT
	k.imie,
	k.nazwisko,
	k.kod_pocztowy,
	u.numer_telefonu
FROM klient k
INNER JOIN ustawienia u ON k.id = u.klient_id
INNER JOIN tmp_kody t ON k.kod_pocztowy = t.kod_pocztowy
WHERE k.zgoda_na_marketing = 1
ORDER BY k.id

Szukane kody pocztowe wrzuciłem sobie do tabelki, bo przy próbie umieszczenia ich w treści zapytania phpMyAdmin odmówił współpracy, a wklejanie zapytania na zdalnej konsoli trwało ponad 10 minut.

Pierwszą próbę wykonania zapytania przerwałem po kilku minutach. EXPLAIN pokazał, że MySQL nie bardzo chce korzystać z indeksów i najchętniej przeskanowałby całą tabelkę tmp_kody i ustawienia. Próbowałem przepisać zapytanie na kilka sposobów by skłonić MySQL do ułożenia innego planu, ale bezskutecznie. Skutecznym rozwiązaniem okazało się tymczasowe dodanie indeksu na kolumnę kod_pocztowy. Oryginalne zapytanie wykonało się w mgnieniu oka, a EXPLAIN pokazał, że wszystkie etapy planu wykonania zaczęły korzystać z indeksów.

Wniosek na przyszłość: jeśli MySQL sobie nie radzi to można mu pomóc dodając tymczasowy indeks. To może okazać się szybsze niż czekanie na wynik nieoptymalnego zapytania.

Serwer na Debianie: nginx, PHP i bazy danych

Przedstawiam mój przepis szybkie postawienie serwera, na którym działa nginx, PHP i bazy danych. Ja z powodzeniem używam takiej konfiguracji na maszynie deweloperskiej, ale można jej też użyć jako punktu wyjścia do konfiguracji serwera produkcyjnego, jednak w tym wypadku radzę uważnie przeczytać zastrzeżenia na końcu.

W przepisie używam Debiana Squeeze, ale po dodaniu sudo przed większością poleceń będzie go można zastosować do Ubuntu. ;-)

Opisywana konfiguracja spełnia następujące założenia:

  • Na serwerze mają działać nginx, PHP, MySQL, PostgreSQL, MongoDB i memcached.
  • Oprogramowanie powinno być w możliwie aktualnych stabilnych wersjach.
  • Skrypty PHP mają działać z prawami użytkownika.
  • Serwer powinien mieć możliwość uruchamiania skryptów PHP zabezpieczonych ionCube’em.

Czytaj dalej Serwer na Debianie: nginx, PHP i bazy danych