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

MyEgo.cz

home foto blogy mywindows.cz kontakt

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

PHP 22.01.09

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.


Komentáře

  1. 1 Daniel Tlach 22.01.09, 18:37:17
    FB

    Ono už toho jde spoustu dělat i v MySQL ale z nějakého důvodu je to tam (alespoň pro mně) krajně nesympaticky vymyšlené. V MySQL mi chybí Execution Plan (možná to tam někde je ale jsem líný hrabat se v dokumentaci), jedna z opravdu super věcí MSSQL.

    Jo, ta pohoda když první spuštění dotazu zabralo 16 vteřin a každé další spuštění už jen 0.5, protože databáze už věděla co dělat a kam sahat. Čert ví jestli to MySQL má taky takhle. Zatím jsem neměl potřebu (a odvahu - ještě bych se naštval) v ní psát procedury.

  2. 2 Hmm.. Trochu drze ziadat skutocne meno, nie? 22.01.09, 22:26:30
    FB

    Hm toto by som vyuzil iba v pripade, ze by som si vytvoril generator v Ruby..

  3. 3 Petr 23.01.09, 00:04:46
    FB

    Projedu-li seznam zde uvedených výhod, tak to stále v každé z nich o dost vítězí FireBird...

  4. 4 Franda Nový 23.01.09, 00:18:38
    FB

    Zajímalo by mě jak hodnotíte přechod z Apache na IIS ze zkušeností co mám je to dost katastrofální.

  5. 5 Sura Martin 23.01.09, 01:32:14
    FB

    Teda, když čtu kolik je s tím starostí a problémů se divím, že to nepřepíšeš do asp.net :)

  6. 6 Zdenek 23.01.09, 14:29:43
    FB

    dovolte mi takovou legrační otázku :) má vůbec smysl použít MSSQL?

    sám dělám s 2003 a neskonale mě irituje právě kódóvání - UTF 16 - proč ne, ale pak mohu zapomenout na editaci záznamů přes GUI nástroje...
    co ale považuji za nevýhodu - kolace jen pro jeden jazyk. samozřejmě lze obejít tak, že pro každý jazyk vyrobím vlastní tabulku :) a pak se z toho zblázním! opravdu báječně řešení.

    a stále u mě platí vyčkávání na prosazení mnohem kvalitnějších objektových databází. z relační bych dal přednost FireBirdu a nebo za těžké prachy Oracle...

  7. 7 Tomáš Kafka 23.01.09, 15:07:04
    FB

    Děkuju za reálné shrnutí, vypadá jinak než microsoftí letáčky... Jsem příznivce Microsoftu, ale tady je zjevně potřeba pro spoustu triviálních věcí (LIMIT ...) se drbat levou rukou za pravým uchem, a to je podle mě znak platformy v dětských letech.

    Škoda, Microsoft by měl zjistit nejčastěji používané konstrukce v dotazech (a patterny používání databáze), a poskytnout pro ně nějaký syntactic sugar, podobně jako to dělají (a velmi dobře) s každou novou verzí C#.

  8. 8 Radek Hulán 23.01.09, 16:38:37

    [7] proč si myslíš, že nemůže editovat přes GUI? SQL Server 2008 je právě geniální v tom, že se s ním dodávají (v ceně, aka zdarma) nejlepší vývojářské a GUI nástroje ze všech konkurečních DB. To nikdo jiný nenabízí.

    [8] "dětská léta"? Ale kdepak. Každá SQL databáze má svá specifika, zde jsem prostě je pouze popsal, při migraci z MySQL.

    Není možné použít identický kód na různé DB, tak to prostě nefunguje. Onen LIMIT mimochodem nemá ani Sybase, Oracle to dělá přes ROWNUM (tedy podobně jako MSSQL), atd.

    SQL Server 2008 je především výkonný, dobře programovatelný a naprosto spolehlivý DB engine (což neplatí na MySQL, Firebird a další), navíc s bezkonkurenčně nejlepšími nástroji pro vývoj (SQL Management Studio, SQL Server Profiler, Data Import, Analysis a Reporting Services, Database Engine Tuning Advisor).

    Udělat kvalitní databázovou aplikaci nad SQL Serverem je prostě vysloveně požitek, pracuje se s ním (jako vývojáři) mnohem lépe. Na zaostalé MySQL je to utrpení a čekání, kdy co zkolabuje. Přečti si toto.

  9. 9 Tomáš Kafka 23.01.09, 16:50:06
    FB

    [9] Já vím že SQL kód není přenosný mezi databázemi, ale zrovna třeba LIMIT je strašně často používaná funkce, a tak by měla být podporovaná příkazem co má dva tokeny (LIMIT 5), ne takovou dlouhou konstrukcí (a ještě mi přijde že dotaz s LIMITem jde krásně optimalizovat, kdežto na podobně rychlou optimalizaci té konstrukce u MSSQL je potřeba mnohem chytřejší logika (i když jí třeba mSSQL má...))

    Podobně s tím UTF-8, bez jeho podpory bych už stránky nedělal, a zase si to všade převádět, to si říká o průšvih - a nadto pak nejde DB upravovat standardními nástroji.

    Na druhou stranu vím jaká hrůza je současný vývoj, či spíše nevývoj MySQL :(.

    Takže pro MS: do příští verze chci UTF-8 a syntactic sugar pro konstrukce co mají v MSSQL víc příkazů než stejná funkčnost u konkurence :).

  10. 10 Radek Hulán 24.01.09, 02:24:52

    [10] a jak si myslíš, že MySQL bude dělat LIMIT x,y? Pomocí SUB-SELECTu, tedy identicky jako MSSQL či Oracle..

Nový komentář

Pro přidání komentáře se musíte nejdříve  registrovat Facebookem

Banan.cz