10 bodů jak migrovat z MySQL na SQL Server 2008 + IIS7 + PHP

Pro několik nových velkých portálových projektů (desítky tisíc registrovaných členů, milióny komentářů pod desítkami tisíc článků, milióny příspěvků v diskusním chatu) budeme používat namísto kombinace Linux + PHP + MySQL + Apache nově tři čtvrtiny Microsoftích technologií, tedy Windows 2008 Server, IIS7, SQL Server 2008 Web/Standard Edition a z open-source tak zůstane jen PHP.

Základem je samozřejmě SQL Server 2008 databáze, která výborně škáluje, je stabilní a je možné ji programovat podstatně lépe než zaostalou MySQL. Výborné jsou i standardně dodávané nástroje pro ladění výkonu - SQL Server Profiler a Database Engine Tuning Advisor.

S prvotními testy s reálnými daty na nové platformě jsem osobně velice spokojen, po optimalizaci SQL dotazů, přepsání části kódu z PHP do T-SQL (na triggery a uložené procedury) je výkon ohromující.

Tam, kde MySQL + Apache zabral podobný (byť relativně neoptimalizovaný) soubor dotazů nad menším datovým objemem cca 0.5 sekundy pro vytvoření jedné stránky, tam kombinace IIS7 + SQL Server 2008 potřebuje jen desítky milisekund (30-50ms) dle stránky. Toto je skutečné živé generování stránky, která se dále ukládá jako statická a server je tedy ještě rychlejší.

Pro připojení PHP na SQL Server používáme SQL Server Driver for PHP 1.0. Ukázal se jako nejstabilnější a nejrychlejší, ve srovnání na občas padající MSSQL či PDO_MSSQL.

Uvedený vývoj a přesun z MySQL na SQL Server není samozřejmě bez problémů, zde je tedy pár poznatků, které se vám budou při migraci aplikace a dat hodit.

1. Neexistuje LIMIT start,offset

SQL Server 2008 nepodporuje v SQL SELECT dotazech stránkování, tedy LIMIT start, offset příkaz. Podporuje jen ekvivalent LIMIT 0, offset a to ve formě TOP x. Nicméně není problém si napsat stránkování vlastní, a to za pomocí funkce ROW_COUNT(), která vrací číslo aktuálního řádku podle určeného řazení v OVER (ORDER BY ...).

V praxi je pro migraci z MySQL na SQL Server 2008 nejjednodušší napsat si funkci, která upraví původní dotaz do vnořeného sub-SELECTU, v této formě:

SELECT queryresult.* FROM (
   SELECT fieldsX, 
   ROW_NUMBER() OVER (ORDER BY fieldsY) as rnumber
   FROM table
) as queryresult
WHERE queryresult.rnumber BETWEEN ($offset+1) AND ($offset+$limit)

S takovou funkcí můžete používat syntax MySQL dotazů, a pro SQL Server budou správně překládány.

2. Neexistuje buffered query a tím ani row count

MySQL databáze implicitně používá buffered query, tedy přenese veškeré výsledky na klienta, a poté se dá použít funkce mysql_num_rows() pro jejich spočítání.

PHP driver pro SQL Server 2008 naproti tomu používá defaultně unbuffered query, takže podobná funkce nemůže ani existovat. Pokud v kódu potřebujete vědět předem počet řádků, doporučuji jej přepsat, většinou to totiž není nezbytně nutné, a často postačí jedna IF podmínka navíc, popřípadě můžete počítat počet řádků v průchodu WHILE cyklem pro FETCH, počet znáte na konci cyklu.

Počítat dopředu počet položek bývá z 99% programátorská chyba. Většinou navíc ani nepotřebujete počet, spíše něco jako "has_rows()", tedy IF row count>0.

V databázovém layeru přesto funkci sql_num_rows() a sql_has_rows() máme, udělají v podstatě to, že vezmou připravený SELECT, namísto položek do něj dosadí COUNT(*) a vrátí výsledek, popřípadě udělají SELECT s TOP(1).

3. Data dostanete v UTF16LE

Datové typy nvarchar, ntext a další používají nativně v SQL Serveru UCS2LE kódování. PHP Driver pro SQL Server takováto data poskytne v UTF16LE. Oboje je problematické v tom, že s tím neumí řada prohlížečů pracovat, takže musíte vstup a výstup překládat pomocí iconv či mb_string do UTF-8.

S tímto se nedá zatím nic dělat, buď používat jen ANSI (CP1250), pokud vám to stačí, anebo databázový layer upravit pro tuto konverzi.

V budoucnu doufám, že SQL Server team zapracuje na tom, aby PHP driver podporoval alespoň UTF-8, ušetřilo by to hodně práce.

Teoreticky můžete udělat i to, že stránku pomocí ob_start() funkce odchytnete, a převedete ji z UTF16LE na UTF8 až těsně před jejím výstupem, takže budete muset ošetřit jen vstup dat.

4. Číslo posledního vloženého záznamu s autoincrementem

Pole s automaticky se zvyšujícím sekvenčním číslem se nenazývá v SQL Serveru AUTOINCREMENT, ale IDENTITY.  Jeho hodnotu změníte snadno pomocí dbcc checkident.

Na poslední hodnotu IDENTITY, kterou vygeneroval INSERT, neexistuje speciální funkce jako v MySQL - mysql_insert_id(), ale získáte ji jednoduchým SQL dotazem:

SELECT SCOPE_IDENTITY() AS 'Identity'

5. Ladění při vývoji

Při vývoji je dobré si zapnout maximální úroveň logu, a to příkazem:

sqlsrv_configure(LogSeverity, SQLSRV_LOG_SEVERITY_ALL);

6. Rychlost dotazů

Plánování dotazů funguje v SQL Serveru 2008 o hodně jinak než v MySQL. Jeden složitý (dříve rychlý) dotaz, který měl použít hlavní tabulku a k ní 4x INNER JOIN na primární klíče jiných tabulek plus WHERE podmínka mě překvapil v tom, že běžel asi 6 sekund. Po záměně INNER JOIN za LEFT JOIN zabral ovšem jen 0.1 sekundy, tedy 60x méně, a to byl teprve začátek optimalizace uvedeného dotazu.

Obecně, doporučuji mít při vývoji neustále zapnutý SQL Server Profiler, a případně se po nějaké době podívat i na doporučení od aplikace Database Engine Tuning Advisor.

Není ale příliš dobré aplikovat hromadně doporučení Database Engine Tuning Advisor,  jako možný hint to můžete brát. Často je lepší dotaz přepsat a případně k němu vytvořit ručně indexy pro tabulky a podívat se na jejich Execution Plan v Microsoft SQL Server Management Studio.

7. Triggery používají "inserted" a "delete" temporary tabulky

SQL Server 2008 nemá BEFORE INSERT triggery jako třeba Oracle. Řeší se to AFTER INSERT triggerem, který poté pracuje s temporary tabulkami inserted (či deleted), které mají stejnou strukturu jako tabulka, nad níž se trigger aktivoval, a obsahují vložené či smazané záznamy.

V triggeru můžete aktualizovat i samotnou tabulku, která trigger vyvolala, což při jiném přístupu nebývá pravidlem. Kurzory v T-SQL zde fungují podobně jako na Oracle v PL-SQL.

Malý příklad (zjednodušený oproti realitě):

CREATE TRIGGER [dbo].[TR_comment_insert] 
   ON  [dbo].[comment] 
   AFTER INSERT
AS 
BEGIN
 SET NOCOUNT ON;
 DECLARE @citem as int, 
      @ctime as datetime, 
      @crel as int, 
      @cnumber as int;
 DECLARE item_cursor CURSOR FOR 
   SELECT citem, ctime, cnumber 
   FROM inserted ORDER BY ctime ASC, cnumber ASC;
 OPEN item_cursor;
 FETCH NEXT FROM item_cursor INTO @citem, @ctime, @cnumber;
 WHILE @@FETCH_STATUS = 0
  BEGIN
   UPDATE dbo.item SET 
     ilatestcomment = @ctime, 
     inumbercomments = inumbercomments+1 
   WHERE inumber=@citem;
   SELECT @crel = inumbercomments FROM dbo.item WHERE inumber=@citem;
   UPDATE dbo.comment SET crel=@crel WHERE cnumber=@cnumber;
   FETCH NEXT FROM item_cursor INTO @citem, @ctime, @cnumber;
 END
 CLOSE item_cursor;
 DEALLOCATE item_cursor;
END

8. CLUSTERED indexy na primární klíč nemusí být vždy vhodné

Dobře si rozmyslete, zda chcete PRIMARY indexy dělat jako CLUSTERED (řazení v indexu je poté ovlivněno fyzických pořadím záznamů na disku), pokud jsou v tabulce milióny položek a vy drobně modifikujete jeden komentář, bude SQL Server provádět zbytečně mnoho IO operací.

Je dobré si otestovat, jak se chová CLUSTERED a jak NONCLUSTERED i pro primární klíč.

9. Datetime typ nevrací varchar, ale DateTime objekt

Pokud máte v databázi datetime položku, v MySQL tato vrací při SELECTu datový typ varchar (a podobně se chová i SQLite), nicméně v SQL Serveru vám vrátí objekt typu DateTime, s nímž se dále dá pracovat v PHP třeba pomocí ->format() metody.

V každém případě je to docela opruz, předělávat kvůli tomu podstatné množství kódu (pokud si to tedy neošetříte přímo na úrovni DB layeru, což doporučuji).

Často používaná v MySQL je i funkce UNIX_TIMESTAMP(), která převede datetime položku na počet sekund od 1.1. 1970. V SQL Serveru tato funkce neexistuje, ale můžete si ji snadno vytvořit:

CREATE FUNCTION [dbo].[UNIX_TIMESTAMP]
( 
    @dt DATETIME 
) 
RETURNS BIGINT 
AS 
BEGIN 
    DECLARE @diff BIGINT 
    IF @dt >= '20380119' 
    BEGIN 
        SET @diff = CONVERT(BIGINT, 
          DATEDIFF(S, '19700101', '20380119')) 
          + CONVERT(BIGINT, DATEDIFF(S, '20380119', @dt)) 
    END 
    ELSE 
        SET @diff = DATEDIFF(S, '19700101', @dt) 
    RETURN @diff 
END

Pro práci s datem existuje v SQL Serveru velice komfortní funkce DATEADD, s prvním parametrem hour, day, month, atd., čímž můžete snadno nahradit funkce DAY(), MONTH() a další z MySQL.

10. Programujte, používejte relace

Použít SQL Server znamená hlavně programovat.

Díky silnému T-SQL (obdoba PL-SQL v Oracle, kde jsem v roce 1996 databázově začínal), je obrovská příležitost jak z logiky, která je "naprasena" v PHP, dostat toky dat a jejich integritu tam, kam patří - tedy do databáze.

Používejte FOREIGN KEYS, ON DELETE CASCADE, skalární funkce, prostě s trochou snahy se daný projekt dá učinit mnohem stabilnějším a robustnějším, než by tomu bylo možné u MySQL.