Wedle definicji, SQL jest językiem deklaratywnym. Oznacza to, że programista opisuje, co chce osiągnąć, natomiast sposób osiągnięcia celu pozostawia silnikowi bazy danych. Ta zacna reguła zazwyczaj się sprawdza, dzięki czemu pamiętając o zaledwie kilku regułach można tworzyć zgrabne i szybkie zapytania. Czasami jednak optymalizator podstawia nogę w zupełnie nieoczekiwanym momencie.

Ostatnio miałem następującą sytuację: w pewnej firmie, zajmującej się przekazywaniem dotacji dobroczynnych, trzeba utworzyć raport – listę organizacji, na rzecz których w zadanym okresie czasu dokonano co najmniej jednej donacji.

Zadanie nie wygląda na trudne. Do wykonania raportu wystarczy przeanalizować jedną tabelę myc_charity (listę organizacji – 750 rekordów) i jeden prosty widok  v_eventdonations (dane wykonanych donacji, ok 500 tysięcy rekordów). Widok powstał z normalnego złączenia 4 tabel, nie zawiera żadnych złożonych warunków, funkcji agregujących czy innych takich wynalazków. Defacto, jest tylko „skrótem”, stosowanym dla uniknięcia powtarzających się złączeń w kolejnych zapytaniach.

Pierwsze podejście do zadania wyglądało tak:

select
 CharityID, Name AS CharityName, OrgID
from
 myc_charity
where
CharityID in (
 select distinct ved.CharityID from v_eventdonations ved
 where
   ved.PaymentDate >= '2012-10-1'
   and ved.PaymentDate <= '2012-10-31'
   and (DEStatusID & 1) > 0
);

Rezultat: 235 rekordów. Czas wykonania: 619 sekund. Absurdalny! Tym bardziej absurdalny, że myc_charity.CharityID jest kluczem głównym, a samo podzapytanie wykonuje się w 0.81 sekundy.

Zwykle podejrzenie pada na skorelowanie podzapytania, ale w tym wypadku nie byłem w stanie znaleźć korelacji. Co prawda czas wykonania wskazywał na jej istnienie: 750 * 0.81 = 607 sekund, czyli niemal dokładnie tyle, ile zajmowało wygenerowanie raportu. Polecenie EXPLAIN również twierdziło, że zapytanie jest skorelowane:

mysql> explain select CharityID, Name AS CharityName, OrgID from myc_charity whe
re CharityID in (select distinct ved.CharityID from v_eventdonations ved where v
ed.PaymentDate >= '2012-10-1'
-> and ved.PaymentDate <= '2012-10-31'
-> and (DEStatusID & 1) > 0
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: myc_charity
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1228
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: utc
type: ref
possible_keys: PRIMARY,UserEventID_2,UserEventID,CharityID
key: CharityID
key_len: 4
ref: func
rows: 27
Extra: Using where; Using temporary
[.........]

Dopiero po wykonaniu EXPLAIN wykonanie SHOW WARNINGS pokazuje perfidię optymalizatora:

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select
  `mycharity_live`.`myc_charity`.`CharityID` AS `CharityID`,
  `mycharity_live`.`myc_charity`.`Name` AS `CharityName`,
  `mycharity_live`.`myc_charity`.`OrgID` AS `OrgID`
from
  `mycharity_live`.`myc_charity`
where <in_optimizer>(
  `mycharity_live`.`myc_charity`.`CharityID`,
  <exists>(
    select distinct 1
    from `mycharity_live`.`myc_userevents` `ev`
      join `mycharity_live`.`myc_userevent_to_charity` `utc`
      join `mycharity_live`.`myc_donationevent_to_charity` `dtc`
      join `mycharity_live`.`myc_donationevent` `de`
    where (
      (`mycharity_live`.`ev`.`UserEventID` = `mycharity_live`.`utc`.`UserEventID`)
      and (`mycharity_live`.`dtc`.`UserEventCharityID` = `mycharity_live`.`utc`.`UserEventCharityID`)
      and (`mycharity_live`.`de`.`DonationEventID` = `mycharity_live`.`dtc`.`DonationEventID`)
      and (`mycharity_live`.`de`.`PaymentDate` >= '2012-10-1')
      and (`mycharity_live`.`de`.`PaymentDate` <= '2012-10-31')
      and (
        (`mycharity_live`.`de`.`DEStatusID` & 1) > 0)
        and (<cache>(`mycharity_live`.`myc_charity`.`CharityID`) = `mycharity_live`.`utc`.`CharityID`
      )
)))
1 row in set (0.00 sec)

Cóż się tu wydarzyło? Ano, optymalizator „zoptymalizował” zapytanie. W tym niestety przypadku zamienił operator IN na operator EXISTS, a zapytanie wewnętrzne skorelował (ostatni warunek) z zapytaniem zewnętrznym. Efektem była fatalna wydajność tego, wydawałoby się, prostego zapytania.

Jak wybrnąć z tego problemu? Trzeba się odwołać do jednej z podstawowych wskazówek SQL-a: w miarę możliwości przesuwaj warunki z WHERE do złączeń.

select
    distinct c.CharityID, c.`Name` AS CharityName, c.OrgID
from
    myc_charity c
    left join v_eventdonations ved on ved.CharityID = c.CharityID
        and ved.PaymentDate >= '2012-10-1'
        and ved.PaymentDate <= '2012-10-31'
        and (ved.DEStatusID & 1) > 0
where ved.CharityID is not null;

Rezultaty: identyczne z pierwszym zapytaniem. Czas wykonania: 0.25 sekundy. Przyrost wydajności: 2500 razy

W tym roku w Irlandii zmienił się VAT. Zdarza się – ostatnio w całej Europie. Niestety, twórca systemu u mojego klienta nie przewidział takiej sytuacji i stawkę VAT zapisał na sztywno w ustawieniach systemu. 1 stycznia zmieniliśmy tą stawkę na nowe ustawienia i system działał bez problemu.

Problem pojawił się później: ceny usług klienta są zapisane jako wartości netto. Na zestawieniu faktur widnieją natomiast wartości brutto z faktur i (wyliczone) wartości brutto usług – wyliczone na podstawie wartości VAT pobranej z systemu. Oczywiście, po 1 stycznia obie wartości zaczęły się różnić, co zestawienie interpretuje jako rabat – którego w rzeczywistości nie było.

Pojawiła się pilna potrzeba przechowywania bieżącej i historycznych stawek VAT w bazie danych. Pierwszy pomysł wyglądał tak:

CREATE TABLE vat_history (
    vatHistoryId INT NOT NULL AUTO_INCREMENT,
    fromDate DATE,
    toDate DATE,
    rate DECIMAL(10,2),
    PRIMARY KEY(vatHistoryId),
    KEY(fromDate),
    KEY(toDate)
);

Struktura danych musiała być jednak tak zbudowana, żebym miał pewność, że znajdę wpis dla każdej daty, niezależnie jak odległej w czasie, i to najlepiej w obu kierunkach. Teoretycznie można by otworzyć przedziały „fromDate” i „toDate” (wpisując np. NULL), ale tak zdefiniowana struktura nie gwarantuje, że mam ciągłość pokrycia, oraz, że nie powstaną mi dziwne artefakty – przedziały zaczepiające jednym końcem o inne przedziały.

Lepszym pomysłem jest taka struktura:

CREATE TABLE vat_history (
    vatHistoryId INT NOT NULL AUTO_INCREMENT,
    toDate DATE NULL DEFAULT NULL,
    rate DECIMAL(10,2),
    PRIMARY KEY(vatHistoryId),
    KEY(toDate)
);

Wstawienie rekordu zawsze utworzy nowy przedział. Rekord z toDate = NULL oznacza aktualną stawkę. Struktura piękna, tylko jak to podłączać do innych tabel? Brak kolumny fromDate wymusza sprawdzanie dwóch rekordów, jeszcze do tego posortowanych… obrzydliwość.

Z pomocą przychodzi sprytny widok:

CREATE OR REPLACE VIEW v_vat_history AS
  SELECT
    MAX(t2.toDate) AS fromDate, t1.toDate, t1.rate
  FROM vat_history t1
    LEFT JOIN vat_history t2
      ON (t2.toDate < t1.toDate) OR (t1.toDate IS NULL)
  GROUP BY t1.vatId
;

Jak więc uzyskać stawkę obowiązującą w momencie wystawiania faktury? Na przykład tak:

SELECT
  i.issueDate, vat.rate
FROM invoices i
LEFT JOIN v_vat_history vat ON  
  (i.issueDate BETWEEN vat.fromDate AND vat.toDate)
  OR (i.issueDate < vat.toDate AND vat.fromDate IS NULL)
  OR (i.issueDate > vat.fromDate AND vat.toDate IS NULL)
;