Oct 05 2011

mysqldump: nezapomínejte na uložené procedury!

Tag: MySQLJens @ 23:45

Pakliže někdy zálohujete MySQL databázi pomocí utility mysqldump a přitom používáte ve vaší databázi uložené procedury nebo funkce, pak určitě nezapomeňte přidat parametr --routines. Tento parametr zajistí, že výsledkem exportu budou nejen definice struktury tabulek a data, ale zároveň právě i uložené procedury a funkce vytvořené pomocí CREATE PROCEDURE a CREATE FUNCTION.

Je trochu s podivem, že mysqldump uložené procedury nezahrnuje do výchozího nastavení pro export, neboť např. TRIGGERy ve výchozím nastavení do výsledku exportu zahrnuje.

Aktualizujte si tedy vaše zálohovací skripty v tomto duchu:

mysqldump --routines [ostatní parametry] > [název souboru sql]

Více viz dokumentace:


Mar 02 2010

Překlad utility SysBench na Debianu Lenny

Tag: Debian,English,HW Tunning,Linux,MySQLJens @ 23:00

Tento článke je k dispozici pouze v jazyce English.


Nov 23 2009

MySQL: Hromadná kontrola tabulek

Tag: Debian,MySQLJens @ 10:00

V aktuální old-stable (Etch) a vyšších verzí debianu se při spouštění MySQL 5.x automaticky provádí kontrola tabulek (CHECK TABLE) a případně i oprava (REPAIR TABLE) poškozených tabulek. Toto má nastarosti skript /etc/mysql/debian-start a funkce check_for_crashed_tables (definovaná v /usr/share/mysql/debian-start.inc.sh). Občas je ale potřeba explicitně prověřit všechny databáze a tabulky, to lze nejjednodušeji udělat z příkazové řádku pomocí utility mysqlcheck:

/usr/bin/mysqlcheck --defaults-file=/etc/mysql/debian.cnf --verbose --all-databases

Konfigurační soubor /etc/mysql/debian.cnf je přístupný pouze pro roota (tedy výše zmíněný příkaz lze spustit pouze pod rootem) a jsou v něm uloženy autentizační údaje pro systémový učet Debianu (debian-sys-maint) do MySQL.


Aug 06 2009

Upgrade databáze a použití LAST_INSERT_ID()

Tag: MySQL,PHP,Zend FrameworkJens @ 10:40

Při vytváření nových verzí/revizí nějaké aplikace (ať již pomocí subversion či jiného nástroje) je často vhodné a někdy i potřebné aktualizovat strukturu databáze (přidání nových tabulek, sloupců, atd.) případně aktualizovat data (číselníky, systémové hodnoty) v databázi tak, aby nová změna v kódu byla kompatibilní se změnou struktury či dat v databázi (pro úplnost pouze doplním, že článek je napsán pro databázi MySQL 5.x).

Otázka je, jak tuto změnu provést tak, aby jsme při aktualizaci projektu na příslušnou revizi měli k dispozici i aktuální verzi databáze. Jedním z jednodušších řešení je, že spolu s commitem upraveného kódu (například nějakého modelu) commitneme i skript, který po aktualizaci projektu na příslušnou revizi spustíme a tím zajistíme upgrade databáze.

Tento skript může mít různé podoby, buď ho můžeme napsat přímo v PHP a nebo v případě jednodušší aktualizace použijeme pouze několik SQL příkazů. Nedávno jsem zrovna řešil poměrně jednoduchý problém, bylo třeba přidat skript do databáze šablonu i s jednou jazykovou mutací — to znamená: přidat jeden řádek do tabulky sablona a jeden řádek do tabulky sablona_mutace. Jelikož je však při definici ID šablon i mutací použit extra typ AUTO_INCREMENT, tak vložení druhého řádku s mutací bude záviset na ID vloženého řádku šablony. V SQL skriptu nemůžu přímo ID definovat, protože v době kdy se bude skript (upgrade) provádět může mnou vybrané ID šablony už být použito, proto je potřeba nechat sloupec NULL a MySQL vloží vlastní ID dle hodnoty AUTO_INCREMENT tabulky.

Řešením tohoto problému je použití funkce LAST_INSERT_ID(), po vložení řádku šablony definujeme cizí klíč ID šablony v řádku mutace právě pomocí této funkce. Výsledek by pak mohl vypadat nějak takto:

INSERT INTO sablona (id, nazev, kategorie, datum)
    VALUES (NULL, 'Neuskutečněná schůzka', 'system', NOW());

INSERT INTO sablona_mutace (id, sablona_id, jazyk_id, telo)
   VALUES (NULL, LAST_INSERT_ID(), 1, 'Schůzka ne neuskutečnila ...');

Takto jednouše by mohl vypada SQL skript, PHP skript kterým by jste udělali to samé například v Zend_Frameworku by mohl vypadat přibližně takto:

...
$sablona = array(
  'nazev' => 'Neuskutečněná schůzka',
  'kategorie' => 'system',
  'datum' => new Zend_Db_Expr('NOW()'),
);
$sablona_id = Sablona::getInstance()->insert($sablona);

$mutace = array(
  'sablona_id' => $sablona_id,
  'jazyk_id' => 1,
  'telo' => 'Schůzka ne neuskutečnila ...',
);
$mutace_id = SablonaMutace::getInstance()->insert($mutace);
...

Poznámka k PHP příkladu: modely jsou potomky třídy Zend_Db_Table a jsou vytvořeny jako singleton; hodnoty sloupce kategorie a jazyk_id v příkladu jsou magic konstanty což není úplně programátorsky čisté — je to pouze pro jednoduchost příkladu.


Apr 09 2009

Seřazení tabulky pomocí ALTER TABLE

Tag: MySQLJens @ 22:00

Kolega mě nedávno upozornil na zajímavé použít příkazu ALTER TABLE v MySQL. Jedná o přeřazení tabulky pomocí zvoleného sloupce (nebo sloupců). Nejdříve jsem si říkal, na co to asi může být užitečné, ale když už jsem to několikrát použil, tak jsem zjistil že takovou funkci může být velmi užitečná.

ALTER TABLE table_name ORDER BY col_name [, col_name] ...

V MySQL je řazení jeden ze zásadních bottlenecků. Často je samotný dotaz velmi rychlý, přidáte-li řazení, rychlost jde exponenciálně dolů. Částečně tomu lze pomoci vytvoření správného indexu. Ale ani to nemusí být vždy ideální řešení – indexy vám nepomůžou například v případě řazení podle více sloupců a kombinaci ASC a DESC (více viz nepoužití indexů při řazení). V některých případech pak lze s úspěchem použít ALTERT TABLE … ORDER BY ….

Konkrétní příklad použití

Mějme tabulku články, články se většinou na stránku vypisují podle datumu (dne uveřejnění) sestupně, pokud je datum shodný, tak články seřadíme podle primárního klíče (id) vzestupně. V tabulce články máme cca 118 tisíc řádků.

0) Dotaz na články bez řazení:

mysql> SELECT SQL_NO_CACHE id, datum FROM clanky LIMIT 10;
+------+---------------------+
| id   | datum               |
+------+---------------------+
| 8211 | 2007-10-14 15:00:00 |
| 8213 | 2007-10-14 15:00:00 |
| 8218 | 2007-10-14 15:00:00 |
| 8220 | 2007-10-14 15:00:00 |
| 8231 | 2007-10-14 15:00:00 |
| 8237 | 2007-10-14 19:16:22 |
| 8240 | 2007-10-14 19:40:28 |
| 8241 | 2007-10-14 19:45:01 |
| 8242 | 2007-10-14 19:48:34 |
| 8245 | 2007-10-14 18:57:17 |
+------+---------------------+
10 rows in set (0.09 sec)

Výchozí řazení tabulky je podle toho, jak řádky vkládáme, tedy vetšinou podle primárního klíče.

1) Řazení bez použít klíčů:

mysql> SELECT SQL_NO_CACHE id, datum FROM clanky ORDER BY datum DESC, id LIMIT 10;
+--------+---------------------+
| id     | datum               |
+--------+---------------------+
| 175364 | 2038-01-19 03:14:08 |
| 176782 | 2038-01-19 03:14:08 |
| 178837 | 2038-01-19 03:14:08 |
| 178838 | 2038-01-19 03:14:08 |
| 181779 | 2038-01-19 03:14:08 |
| 191940 | 2038-01-19 03:14:08 |
| 215340 | 2009-04-09 19:02:39 |
| 215339 | 2009-04-09 19:02:33 |
| 215338 | 2009-04-09 19:02:31 |
| 215337 | 2009-04-09 19:02:29 |
+--------+---------------------+
10 rows in set (0.21 sec)

2) Řazení s použitím klíče:

mysql> CREATE INDEX razeni ON clanky (datum DESC, id);
Query OK, 118970 rows affected (1.24 sec)
Records: 118970  Duplicates: 0  Warnings: 0
mysql> OPTIMIZE TABLE clanky;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| bench.clanky | optimize | status   | OK       |
+--------------+----------+----------+----------+
1 row in set (0.05 sec)
mysql> SELECT SQL_NO_CACHE id, datum FROM clanky ORDER BY datum DESC, id LIMIT 10;
+--------+---------------------+
| id     | datum               |
+--------+---------------------+
| 175364 | 2038-01-19 03:14:08 |
| 176782 | 2038-01-19 03:14:08 |
| 178837 | 2038-01-19 03:14:08 |
| 178838 | 2038-01-19 03:14:08 |
| 181779 | 2038-01-19 03:14:08 |
| 191940 | 2038-01-19 03:14:08 |
| 215340 | 2009-04-09 19:02:39 |
| 215339 | 2009-04-09 19:02:33 |
| 215338 | 2009-04-09 19:02:31 |
| 215337 | 2009-04-09 19:02:29 |
+--------+---------------------+
10 rows in set (0.20 sec)

Výsledek při přidání klíče v tomto případě nemá téměř žádný vliv, při kombinace ASC a DESC řazení MySQL klíče nezohledňuje.

3) Použít řazení pomocí seřazené tabulky:

mysql> DROP INDEX razeni ON clanky;
Query OK, 118970 rows affected (1.03 sec)
Records: 118970  Duplicates: 0  Warnings: 0

mysql> OPTIMIZE TABLE clanky;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| bench.clanky | optimize | status   | OK       |
+--------------+----------+----------+----------+
1 row in set (0.02 sec)

mysql> ALTER TABLE clanky ORDER BY datum DESC, id;
Query OK, 118970 rows affected (1.45 sec)
Records: 118970  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_NO_CACHE id, datum FROM clanky LIMIT 10;
+--------+---------------------+
| id     | datum               |
+--------+---------------------+
| 175364 | 2038-01-19 03:14:08 |
| 176782 | 2038-01-19 03:14:08 |
| 178837 | 2038-01-19 03:14:08 |
| 178838 | 2038-01-19 03:14:08 |
| 181779 | 2038-01-19 03:14:08 |
| 191940 | 2038-01-19 03:14:08 |
| 215340 | 2009-04-09 19:02:39 |
| 215339 | 2009-04-09 19:02:33 |
| 215338 | 2009-04-09 19:02:31 |
| 215337 | 2009-04-09 19:02:29 |
+--------+---------------------+
10 rows in set (0.10 sec)

Zde je vidět dvojnásobný nárůst rychlosti. Navíc, mám experimentálně ověřeno, že pokud vybíráte více sloupců (né tedy jen id a datum), tak se rychlost řazeného výsledku dramaticky propadá. To proto, že řazení probíhá v sort bufferu v paměti, velikost tohoto bufferu definuje proměnná sort_buffer_size v nastavení MySQL. V momentě kdy řadíte velké výsledky, buffer „přeteče“ a řazení se začne odehrávat na disku — viz algoritmus filesort v MySQL — což celý proces dále zpomaluje.

Jediný vážný problém tohoto způsobu využití seřazení tabulky je ten, že v případě vložení nebo aktualizace řádků se „seřazenost“ tabulky nezachová. Dále pak tento princip nefunguje na InnoDB typy tabulek. I přes tyto nevýhody lze tuto vlastnost MySQL často s úspěchem využít a zefektivnit tak některé dotazy.

Zajímavé odkazy k tématu:


Jun 02 2008

Nulový počet řádků v MySQL

Tag: MySQLJens @ 19:01

Jak udělat co nejjednodušší dotaz v MySQL tak, aby vrátil vždy nulový počet řádků? Je to blbost, ale někdy se to může hodit — spoléháte-li např. v PHP na výsledek dotazu: if ( $res->fetchRow() ) nebo počet řádků výsledků: $res->rowCount()

Můžete použít něco takového:

mysql> SELECT 0 LIMIT 0;
Empty set (0.00 sec)

jednoduché (nepotřebujete žádnou tabulku ani funkci), rychlé** a v určitých případech užitečné :) Možná to jde i jednodušeji, pokud ano, pište jak na to do komentářů, rád se přiučím.

**Benchmark (IBM ThinkPad T41 – Intel(R) Pentium(R) M processor 1400MHz)

mysql> SELECT BENCHMARK(1000000,(SELECT 0 LIMIT 0));
+---------------------------------------+
| BENCHMARK(1000000,(SELECT 0 LIMIT 0)) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.04 sec)

Feb 06 2008

Změna pořadí sloupců v MySQL

Tag: MySQLJens @ 11:46

Při upravě tabulek pomocí jinak výborného nástroje phpMyAdmin jsem zjistil, že nemá funkci pro změnu pořadí sloupců tabulky. Proto jsem musel hledat syntaxi ALTER TABLE v manuálu MySQL.

Pokračování článku…