Jens.cz

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:

• ALTER TABLE … ORDER BY syntax – kapitola v manuálu MySQL.
• ORDER BY optimalizace – kapitola v manuálu MySQL,
• ORDER BY … LIMIT Performance Optimization – článek na MySQL Performance Blog.