出典について
この記事はPercona Data Performance Blog内のStephane Combaudon氏によるVirtual columns in MySQL and MariaDB(2016/3/4)を翻訳したものです。
このブログ投稿では、MySQLとMariaDBの仮想列について比較しようと思います。
仮想列は私がMySQL 5.7で最も気に入っている機能の1つです。同じテーブルの1つあるいは複数の他のフィールド由来の値を新しいフィールドに保存できる機能です。関数インデックスを作成するには非常に良い方法です。この機能がMariaDBで利用できるようになってからしばらく経過しましたので、両者が同等のものなのか比較してみましょう。いくつかの観点でみていきましょう。
ドキュメント
MariaDBのドキュメントはとても見つけやすいです。
MySQL 5.7の仮想列のドキュメントを探すのは少し難しいです。ここが私が見つけたリンクのうち一番良いと思ったものです。
MariaDBのドキュメントは仮想的な列ではなく永続化された列を扱おうとした場合が不明確です。注意をはらって読めば、インデックスが永続化列(persistent column)でのみサポートされることがわかりますが、両オプションの長所短所をもう少しよく表現するとよかったと思います。
MySQLはストアド列(stored column)と仮想列を使うユースケースをリストした興味深い項があります。この項はとても分かりやすいというわけではありませんが、その要点は「即席で見積もって非常に手間がかかるわけでない場合を除き常に仮想列を使う」というものです。MySQL 5.7ではインデックスを利用するためにストアド列を使う必要はないことに注意してください。
構文
仮想列を作成するコマンドは両者で非常に似ています。
ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;
NOT NULLに関してMySQL 5.7で利用可能な一方で、MariaDBではサポートされていないことにご注意ください。
# MariaDB 10.0
MariaDB [db1]> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL' at line 1
# MySQL 5.7
mysql> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
マテリアライズされた仮想列を作成するときは、残念なことに構文が異なります。
MariaDBはPERSISTENT列で、MySQL 5.7はSTORED列です。これは大きな問題ではないように思いますが、移行する際のチェックリストに加える必要があるでしょう。
仮想列の追加
# MySQL 5.7
ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL;
Query OK, 0 rows affected (0.03 sec)
すばらしい！列の作成はメタデータの変更のみで完了し、テーブルサイズに関わらずほぼ即時に実行されます。
MariaDBでは全く異なります
# MariaDB 10.0
ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;
Query OK, 0 rows affected (7 min 8.50 sec)
そうです。テーブルの再構成が必要でした。そしてsysbenchの挿入負荷を走らせれば、これがオンラインの再構成でないことがわかります。スキーマ変更のおよそ1/3位の間、書込みが停止しました。
インデックス
これがおそらく最も大きな違いで、MariaDBではインデックスを作成するためには列はPERSISTENTでなければなりません。MySQL 5.7ではその必要はありません。MySQL 5.7でインデックス列がSTOREDでなければならない唯一のケースは、それが主キーであった場合です。
インデックスを複数列の追加する場合、そのうちのいくつかは通常の列で、いくつかは仮想列となりえますが、両者ともにこの操作は可能となります。
# MySQL 5.7
mysql> ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad);
Query OK, 0 rows affected (2 min 38.14 sec)
# MariaDB 10.0
MariaDB [db1]> ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad);
Query OK, 10187085 rows affected (4 min 43.76 sec)
インデックス追加は、MariaDB 10.0ではブロッキングな操作であるのに対して、MySQL 5.7ではオンラインで実行されるとう大きな違いがあります。
まとめ
一見するとMariaDB 10とMySQL 5.7は仮想列に関して似た機能を提供しているように見えますが、実際は全く異なっていました。仮想列に関しては、MySQLとMariaDBの構文が完全に同一でなく、仮想列の追加は同じように実施されず、インデックス追加に関する制約条件が異なります。MySQL 5.7の実装は、テーブルサイズが大きくトラフィック量が多いプロダクションでの利用に対してより洗練されているようです。
