2014年7月2日水曜日

MariaDB 10.0のSEQUENCEストレージエンジンを試してみる

というか、MariaDB 10.1.0がAlphaリリースされたのでそれを試したついでなので、実際にはMariaDB 10.1のSEQUENCEストレージエンジンを使ってみました。


MariaDB [d1]> SELECT @@version;
+--------------------+
| @@version          |
+--------------------+
| 10.1.0-MariaDB-log |
+--------------------+
1 row in set (0.00 sec)

MariaDB [d1]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| TEST_SQL_DISCOVERY | YES     | Minimal engine to test table discovery via sql statements                  | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| CONNECT            | YES     | Management of External Data (SQL/MED), including many file formats         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| SPHINX             | YES     | Sphinx storage engine 2.1.5-release                                        | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                             | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
14 rows in set (0.00 sec)

うぇーい(^ω^)


MariaDB [d1]> CREATE TABLE t1 (num int) Engine= SEQUENCE;
ERROR 1005 (HY000): Can't create table `d1`.`t1` (errno: 131 "Command not supported by database")

あれ、なんか違うらしい。
確かに前(10.0に入るって時に)なんかちょっと見た気がするけど、Oracleのシーケンステーブル(?)とかとは根本的に違う何からしいんだよね確か。

おとなしくマニュアル読む。 https://mariadb.com/kb/en/mariadb/mariadb-documentation/mariadb-storage-engines/sequence/


使い方はこうらしい。


MariaDB [d1]> SELECT * FROM seq_1_to_12;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  12 |
+-----+
12 rows in set (0.00 sec)

MariaDB [d1]> SELECT seq FROM seq_10_to_20_step_2;
+-----+
| seq |
+-----+
|  10 |
|  12 |
|  14 |
|  16 |
|  18 |
|  20 |
+-----+
6 rows in set (0.00 sec)

お、おう。連番が欲しい時に便利そうだねってか、偶数だけ欲しい時にWHERE MOD(c1, 2) = 0とかしなくてもJOINすれば良いように…とかそんなかんじ?(思い付かない


MariaDB [d1]> SELECT seq FROM seq_1_to_99999999999999999999999999999999 ORDER BY seq DESC LIMIT 3;
+----------------------+
| seq                  |
+----------------------+
| 18446744073709551615 |
| 18446744073709551614 |
| 18446744073709551613 |
+----------------------+
3 rows in set (0.00 sec)

2 ^ 64 - 1までいけるっぽい。負値はダメ。


MariaDB [d1]> SELECT * FROM seq_10_to_1;
+-----+
| seq |
+-----+
|  10 |
|   9 |
|   8 |
|   7 |
|   6 |
|   5 |
|   4 |
|   3 |
|   2 |
|   1 |
+-----+
10 rows in set (0.00 sec)

MariaDB [d1]> SELECT * FROM seq_10_to_1_step_2;
+-----+
| seq |
+-----+
|   9 |
|   7 |
|   5 |
|   3 |
|   1 |
+-----+
5 rows in set (0.00 sec)

頭の方が数字が大きければ降順っぽく。
ただし、seq_1_to_10_step_2 は 「先の値」の1から2ずつインクリメントするのに対して、seq_10_to_1_step_2は「先の値」の10からデクリメントしてるわけではないので、たぶん単にseq_1_to_10_step_2をひっくり返してるだけ。


MariaDB [d1]> explain SELECT * FROM seq_1_to_10_step_2;
+------+-------------+--------------------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table              | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+--------------------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | seq_1_to_10_step_2 | index | NULL          | PRIMARY | 8       | NULL |    5 | Using index |
+------+-------------+--------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [d1]> explain SELECT * FROM seq_10_to_1_step_2;
+------+-------------+--------------------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table              | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+--------------------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | seq_10_to_1_step_2 | ALL  | NULL          | NULL | NULL    | NULL |    5 |       |
+------+-------------+--------------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)


ちゃんとEXPLAIN取れるところは芸が細かいというかなんと言うか。


MariaDB [d1]> SELECT seq, CONCAT(IF(fizz.seq IS NULL, '', 'fizz'), IF(buzz.seq IS NULL, '', 'buzz')) AS str FROM seq_1_to_100 AS main LEFT JOIN seq_0_to_100_step_3 AS fizz USING(seq) LEFT JOIN seq_0_to_100_step_5 AS buzz USING(seq);
+-----+----------+
| seq | str      |
+-----+----------+
|   1 |          |
|   2 |          |
|   3 | fizz     |
|   4 |          |
|   5 | buzz     |
|   6 | fizz     |
|   7 |          |
|   8 |          |
|   9 | fizz     |
|  10 | buzz     |
|  11 |          |
|  12 | fizz     |
|  13 |          |
|  14 |          |
|  15 | fizzbuzz |
..
| 100 | buzz     |
+-----+----------+
100 rows in set (0.00 sec)

FIZZBUZZしてみたけど、IF演算子使うならなんでもいいやね。。

0 件のコメント :

コメントを投稿