MariaDB 10.3 の SEQUENCE オブジェクトを使ってみる

この記事は最終更新から6年以上経過しています。内容が古くなっている可能性があります。

はじめに

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のストレージエンジンという形式で再実装したものであり、自動採番専用テーブルとして使うことが想定されています。

ただし、SEQUENCEストレージエンジンはMariaDB内部に組み込まれたエンジンなので、以下のように任意のテーブルを作成することはできませんでした。

しかし、今回MariaDB10.3で実装されたSEQUENCEオブジェクトでは下記のようにOracle互換を意識されており、使い勝手が大幅に向上しています。

Standards Compliance

MariaDB 10.3 supports both ANSI SQL and Oracle syntax for sequences.

SEQUENCEオブジェクトの使い方

それでは MariaDB10.3 で早速SEQUENCEオブジェクトを使ってみましょう。

まずはシンプルに「100」からスタートして、「10」ずつ増えていく seq_1 オブジェクトを作成します。

オブジェクトを使用する場合は、「NEXTVAL(オブジェクト名)」を指定します。「NEXT VALUE FOR (オブジェクト名)」でも構いません。

sql_mode=ORACLE を設定している場合、Oracleと同じ文法も使用できます。

オブジェクトが記録している値を変更(リセット)したい場合は、ALTER文を実行します。

上記のように、オブジェクトの詳細確認や、名前の変更、オブジェクトの削除などは通常のテーブルと同じようなクエリを使うことができます。

※ ただし、SHOW CREATE TABLE 文については以下のようにオブジェクトの設定値を入れるためのテーブル定義が出力されてしまいます

また、SEQUENCEオブジェクトに対して付与できる設定値は以下のようなものがあります。用途に合わせて適宜変更しましょう。

CREATE 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への移行を検討している人にとってはより良い選択肢の一つとなり得るのではないでしょうか。


MariaDB

 

 

スマートスタイルTECHブログについて

スマートスタイルTECHブログでは、日頃オープンソースデータベースのサポート業務に従事している有資格者で構成された技術サポートチームがMariaDBに関する技術情報を発信しています。データベースのお困りごとはお気軽にご相談下さい。

よかったらシェアしてね!
  • URLをコピーしました!
目次