はじめに
Oracleで連番を生成したい場合に使用される SEQUENCE オブジェクトは、MySQLには実装されていません。そのため、MySQLで同様の機能を実現したい場合、IDカラムにauto_increment属性を付与したり、自動採番テーブルを独自に作るなどの対応が必要となります。
そんな中で、MariaDB 10.3からOracleと互換性のあるSEQUENCEオブジェクトが実装されました。そこで本記事ではこの機能について色々検証してみたいと思います。
MariaDB と SEQUENCE
元々、MariaDBではバージョン10.0から「Sequence Storage Engine」が追加プラグインとして実装され、10.1からはデフォルトで有効になりました。
これはOracleのSEQUENCEオブジェクトをMySQLのストレージエンジンという形式で再実装したものであり、自動採番専用テーブルとして使うことが想定されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
MariaDB [(none)]> SHOW ENGINES\G ... （中略） ... *************************** 8. row *************************** Engine: SEQUENCE Support: YES Comment: Generated tables filled with sequential values Transactions: YES XA: NO Savepoints: YES 8 rows in set (0.001 sec) MariaDB [(none)]> CREATE DATABASE test; MariaDB [(none)]> SELECT * FROM test.seq_1_to_5; +-----+ | seq | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | +-----+ 5 rows in set (0.002 sec) |
ただし、SEQUENCEストレージエンジンはMariaDB内部に組み込まれたエンジンなので、以下のように任意のテーブルを作成することはできませんでした。
|
1 2 |
MariaDB [(none)]> CREATE TABLE test.seq_engine (seq_id smallint) ENGINE=SEQUENCE; ERROR 1005 (HY000): Can't create table `test`.`seq_engine` (errno: 131 "Command not supported by the engine") |
しかし、今回MariaDB10.3で実装されたSEQUENCEオブジェクトでは下記のようにOracle互換を意識されており、使い勝手が大幅に向上しています。
MariaDB 10.3 supports both ANSI SQL and Oracle syntax for sequences.
SEQUENCEオブジェクトの使い方
それでは MariaDB10.3 で早速SEQUENCEオブジェクトを使ってみましょう。
まずはシンプルに「100」からスタートして、「10」ずつ増えていく seq_1 オブジェクトを作成します。
|
1 2 3 4 |
MariaDB [(none)]> use test Database changed MariaDB [test]> CREATE SEQUENCE seq_1 START WITH 100 INCREMENT BY 10; Query OK, 0 rows affected (0.013 sec) |
オブジェクトを使用する場合は、「NEXTVAL(オブジェクト名)」を指定します。「NEXT VALUE FOR (オブジェクト名)」でも構いません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
MariaDB [test]> SELECT NEXTVAL(seq_1); +----------------+ | NEXTVAL(seq_1) | +----------------+ | 100 | +----------------+ 1 row in set (0.006 sec) MariaDB [test]> SELECT NEXTVAL(seq_1); +----------------+ | NEXTVAL(seq_1) | +----------------+ | 110 | +----------------+ 1 row in set (0.001 sec) |
sql_mode=ORACLE を設定している場合、Oracleと同じ文法も使用できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
MariaDB [test]> SET GLOBAL sql_mode = "ORACLE"; Query OK, 0 rows affected (0.000 sec) ... （MariaDBに再接続）... MariaDB [test]> SELECT seq_1.nextval; +---------------+ | seq_1.nextval | +---------------+ | 120 | +---------------+ 1 row in set (0.000 sec) |
オブジェクトが記録している値を変更（リセット）したい場合は、ALTER文を実行します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
MariaDB [test]> ALTER SEQUENCE seq_1 RESTART 50; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> SELECT NEXTVAL(seq_1); +----------------+ | NEXTVAL(seq_1) | +----------------+ | 50 | +----------------+ 1 row in set (0.001 sec) MariaDB [test]> SELECT NEXTVAL(seq_1); +----------------+ | NEXTVAL(seq_1) | +----------------+ | 60 | +----------------+ 1 row in set (0.001 sec) |
上記のように、オブジェクトの詳細確認や、名前の変更、オブジェクトの削除などは通常のテーブルと同じようなクエリを使うことができます。
|
1 2 3 4 5 6 7 8 9 10 11 |
MariaDB [test]> SHOW CREATE SEQUENCE seq_1\G *************************** 1. row *************************** Table: seq_1 Create Table: CREATE SEQUENCE "seq_1" start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 10 cache 1000 nocycle 1 row in set (0.000 sec) MariaDB [test]> RENAME TABLE seq_1 TO new_seq_1; Query OK, 0 rows affected (0.011 sec) MariaDB [test]> DROP TABLE new_seq_1; Query OK, 0 rows affected (0.006 sec) |
※ ただし、SHOW CREATE TABLE 文については以下のようにオブジェクトの設定値を入れるためのテーブル定義が出力されてしまいます
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
MariaDB [test]> SHOW CREATE TABLE seq_1\G *************************** 1. row *************************** Table: seq_1 Create Table: CREATE TABLE "seq_1" ( "next_not_cached_value" bigint(21) NOT NULL, "minimum_value" bigint(21) NOT NULL, "maximum_value" bigint(21) NOT NULL, "start_value" bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', "increment" bigint(21) NOT NULL COMMENT 'increment value', "cache_size" bigint(21) unsigned NOT NULL, "cycle_option" tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', "cycle_count" bigint(21) NOT NULL COMMENT 'How many cycles have been done' ) 1 row in set (0.000 sec) |
また、SEQUENCEオブジェクトに対して付与できる設定値は以下のようなものがあります。用途に合わせて適宜変更しましょう。
| オプション名 | デフォルト値 | 説明 |
|---|---|---|
| INCREMENT | 1 | 数字が増えていく間隔。デフォルトでは「1」ずつインクリメントされていく |
| MINVALUE | INCREMENT > 0 の時 1 INCREMENT < 0 の時 -(2^63-1) |
SEQUENCEオブジェクトが取りうる最小値 |
| MAXVALUE | INCREMENT > 0 の時 2^63-1 INCREMENT < 0 の時 -1 |
SEQUENCEオブジェクトが取りうる最大値 |
| START | INCREMENT > 0 の時 MINVALUEの値 INCREMENT < 0 の時 MAXVALUEの値 |
SEQUENCEオブジェクトの開始値 |
| CACHE | 1000 | キャッシュする件数（「0」を設定するとキャッシュ機能が無効になる） SEQUENCEのキャッシュとは、次に割り当てる値を予め生成しメモリに格納する機能を指す |
SEQUENCEオブジェクトのメリット
次にSEQUENCEオブジェクトを従来のauto_increment属性と比較した時に、どのようなメリットがあるのか考えてみます。
まずは auto_incrementはテーブル単位の属性ですが、SEQUENCEオブジェクトはテーブルとは独立した存在である点が挙げられます。分かりやすい例では、前者の場合は1テーブルに対し別々の挙動をするincrementカラムを設定することは困難でした（片方は1ずつ増え、もう片方は10ずつ増える等）。しかし、後者の場合は1カラムにつき1つのSEQUENCEオブジェクトを割り当てることができるので、挙動がかなり柔軟に制御できます。また、SEQUENCE用のキャッシュも用意されており、性能も向上する可能性があります。
また、インクリメント値のリセットについてもSEQUENCEオブジェクトは、コマンド1回で簡単に実施でき、負荷も低いです。一方、 auto_increment の場合はALTER TABLEを実行する必要があるため、テーブルのサイズによっては大きな負荷がかかってしまう可能性があります。
さらに、Oracleとの互換性という面もSEQUENCEオブジェクトの強みです。例えばOracle→MySQLの移行では、SEQUENCEオブジェクトからauto_incrementの書き換え・動作検証にかかる工数（コスト）が重荷になるケースが多くあります。一方、MariaDB 10.3であれば現行のまま移行できる可能性もあり、その場合移行がかなりスムーズに進みます。加えて、SEQUENCEオブジェクトは性質として「テーブル」（DROP TABLEなどが実行可能）と同じなので、従来のMariaDB向けの管理・運用ツールでも変わらず動作することが期待されます（ただしUPDATE/DELETEなどは実行不可）。
まとめると、SEQUENCEオブジェクトのメリットは以下の通りです。
- カラム単位でSEQUENCEオブジェクトを割り当てることができるため、柔軟な制御が可能
- インクリメント値のリセットや変更などの運用も簡単になる
- Oracleからの移行時の工数が節約できる可能性が高い
SEQUENCEオブジェクト利用時の注意点
MariaDB10.3でSEQUENCEオブジェクトを使う場合、以下のような点に注意する必要があります。
- Galeraによるマルチマスタ構成を使っている場合は、CREATE SEQUENCE実行時に「INCREMENT BY 0」を指定してください。これにより、SEQUENCEは auto_increment_increment 変数と auto_increment_offset 変数を使用して連番を生成するようになります（ノード間の値重複を回避）。
- SEQUENCEはテーブルと同じ性質を持つため、LOCK TABLEでロックが取得できます。これは他のRDBMSにはない挙動です。また、FLUSH TABLESを実行するとSEQUENCEのキャッシュが全て破棄されます。
- SEQUENCEに対してSELECTを実行すると、キャッシュに予約されている値を除き、シーケンスの保持する現在の値が表示されます。next_valueカラムは、キャッシュによって予約されていない次の値を示します。
まとめ
現時点で、SEQUENCEオブジェクトはMySQLフォークの中でもMariaDBにのみ実装されている機能です。この独自性から、OracleからOSSDBへの移行を検討している人にとってはより良い選択肢の一つとなり得るのではないでしょうか。
