はじめに
マテリアライズド・ビューと言えば、Oracle Databaseに存在するスキーマ・オブジェクトですが、残念ながらMySQLには同等の機能は存在しません。
MySQLの入門書で、マテリアライズド・ビューの代替手段として「Flexviews」というサードパーティツールが紹介されていたのを見かけました。
高速リフレッシュを頑張るとなると、本家の高速リフレッシュは「ベーステーブルに対する更新の差分を別領域に保管しておき、その部分だけを再計算して適用」ですので、それに近づけるならバイナリログをパースして適用することになるでしょう（事実、遠い遠い昔からそのコンセプトで開発されたFlexviews注3.35というサードパーティーツールがあります。日本で使われているのは見たことがありませんが）。
注3.35 https://github.com/greenlion/swanhart-tools/tree/master/flexviews引用：yoku0825、北川健太郎、tom__bo、坂井恵『MySQL運用・管理［実践］入門 〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ』技術評論社、2024年5月22日、p.67
今回はFlexviewsをインストールから実際に動作させるまでのデモンストレーションを行いたいと思います。
ただし、FlexviewsはMySQL 5.1の頃に開発されたツールであり、4年前から更新が止まっている状態となっています。GitリポジトリのREADMEにも、プロジェクト廃止の旨が書かれています。製作者は、AWS上で使えるMySQLディストリビューションを作成し、提供する方向にシフトしたそうです。
動作の保証はないため、使用は自己責任でお願いします。
最新のLTSリリースであるMySQL 8.4 LTSで動作させるためには、何点かスクリプトを修正する必要がありましたので、その経緯も含めて説明していきます。（例：MASTER/SLAVE語句廃止など）
検証環境
|
1 2 3 4 5 6 |
$ cat /etc/oracle-release Oracle Linux Server release 9.4 $ mysqld --version /usr/sbin/mysqld Ver 8.4.3 for Linux on x86_64 (MySQL Community Server - GPL) $ cat ~/swanhart-tools/flexviews/VERSION 1.8.0RC1 |
インストール
インストール手順は、以下のブログを参考に進めていきます。
Using Flexviews – part two, change data capture
Flexviewsの動作要件は以下です。
- MySQL 5.1以降
- 行ベースのログ記録 (binlog_format=1)
- my.cnf 内の一意の server_id
- log_slave_updates=1（MySQLスレーブの場合）
- transaction_isolation=READ-COMMITTED
設定ファイルの変更
要件に適合するようにmy.cnfを設定します。
|
1 |
$ vi /etc/my.cnf |
|
1 2 3 4 |
[mysqld] binlog_format=ROW server_id=10 transaction_isolation=READ-COMMITTED |
Gitリポジトリからクローン
FlexviewsをGitリポジトリから取得します。
|
1 2 3 4 5 |
$ dnf install -y git $ git clone --no-checkout https://github.com/greenlion/swanhart-tools.git $ cd swanhart-tools/ $ git sparse-checkout set flexviews $ git checkout master |
Flexviews設定ファイルの変更
mysqlbinlogバイナリの場所を確認しておきます。
|
1 2 |
$ whereis mysqlbinlog mysqlbinlog: /usr/bin/mysqlbinlog /usr/share/man/man1/mysqlbinlog.1.gz |
設定ファイルを編集します。
|
1 2 3 |
$ cd flexviews/consumer/ $ cp consumer.ini.example consumer.ini $ vi consumer.ini |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
3 mysqlbinlog=/usr/bin/mysqlbinlog ... 98 [source] 99 user=root 100 host=127.0.0.1 101 port=3306 102 password='password' ... 107 [dest] 108 user=root 109 host=127.0.0.1 110 port=3306 111 password='password' |
セットアップスクリプト実行
セットアップスクリプトを実行します。
|
1 2 |
$ php ./setup_flexcdc.php --ini consumer.ini bash: php: command not found |
エラー対策1-1
phpパッケージが不足していたので、インストールします。
|
1 |
$ dnf install -y php php-pear php-mysqlnd |
エラー対策1-2
SHOW MASTER STATUSでエラーになりました。
|
1 |
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MASTER STATUS' at line 1 |
MASTER語句はMySQL 8.4 LTS以降で削除されました。
Features Removed in MySQL 8.4
…
SHOW MASTER STATUS: Use SHOW BINARY LOG STATUS.引用：MySQL :: MySQL 8.4 Reference Manual :: 1.4 What Is New in MySQL 8.4 since MySQL 8.0
代わりに、SHOW BINARY LOG STATUSを使用します。
該当ファイルの文字列を置換します。
|
1 |
$ sed -i 's/SHOW MASTER STATUS/SHOW BINARY LOG STATUS/g' include/flexcdc.php |
セットアップスクリプトを実行し、正常終了しました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
$ php ./setup_flexcdc.php --ini consumer.ini 6 mysqlbinlog=/usr/bin/mysqlbinlog database=flexviews error_log=flexcdc.err binlog_consumer_status=binlog_consumer_status mvlogs=mvlogs mview_uow=mview_uow log_retention_interval=10 DAY skip_before_update= mark_updates= auto_changelog= raise_warnings= bulk_insert=1 sleep_increment=.25 sleep_maximum=5 failure_sleep_time=30 failure_log_file=flex_cdc_log.log failure_email_address=root@localhost setup starting setup completed |
バックグラウンド処理の実行
変更ログ作成処理のバックグラウンドプロセスを実行します。
|
1 2 |
$ chmod +x consumer_safe.sh $ ./consumer_safe.sh --ini=consumer.ini & |
インストールスクリプトの実行
次に、INSTALLファイルの記述を参考にインストールスクリプトを実行します。
エラー対策2-1
local_infileを有効化する必要があります。
クライアントとサーバー両方で有効化します。
サーバーの設定を変更します。
|
1 |
$ vi /etc/my.cnf |
|
1 2 3 |
[mysqld] local_infile=1 secure_file_priv="" |
クライアントは--local-infile=1を指定して接続するようにします。
|
1 2 3 4 |
$ systemctl restart mysqld $ cd .. $ mysql --local-infile=1 mysql> \. install.sql |
エラー対策2-2
ユーザ作成でエラーとなりました。
|
1 |
ERROR 1064 (42000) at line 24 in file: 'install_schema.inc': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'flexdemo'' at line 4 |
GRANTでユーザ作成と同時に権限付与する方法はMySQL 8.0以降で削除された機能です。
MySQL 8.0 で削除された機能
…
GRANT を使用したユーザーの作成。 かわりに、CREATE USER を使用してください。
CREATE USERと、GRANTを2文に分けて実行します。
インストールスクリプトを修正します。
|
1 |
$ vi install_schema.inc |
|
1 2 3 4 5 6 7 8 9 10 |
-- 修正前 24 GRANT ALL 25 ON flexviews.* 26 TO flexviews@localhost 27 IDENTIFIED BY 'flexdemo'; -- 修正後 24 CREATE USER IF NOT EXISTS flexviews@localhost IDENTIFIED BY 'flexdemo'; 25 GRANT ALL 26 ON flexviews.* 27 TO flexviews@localhost; |
エラー対策2-3
storage_engineというシステム変数がないためエラーとなりました。
|
1 |
ERROR 1193 (HY000) at line 21 in file: 'schema/schema.sql': Unknown system variable 'storage_engine' |
storage_engineシステム変数はMySQL 5.7以降で削除されました。
代わりにdefault_storage_engineシステム変数を使用します。
Features Removed in MySQL 5.7
…
The storage_engine system variable. Use default_storage_engine instead.引用：MySQL :: MySQL 5.7 Reference Manual :: 1.3 What Is New in MySQL 5.7
スクリプトを修正します。
|
1 |
$ vi schema/schema.sql |
|
1 2 3 4 |
-- 修正前 21 set storage_engine = -- 修正後 21 set default_storage_engine = |
エラー対策2-4
ブロックコメント/* */の最後にセミコロンが無かったため、エラーとなりました。
|
1 |
ERROR 1064 (42000) at line 2 in file: './procs/uow.sql': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 20 |
他のsqlファイルの書式に倣って、DELIMITER ;;の位置を20行目から1行目に移動します。
|
1 |
$ vi ./procs/uow.sql |
|
1 2 3 4 5 6 |
1 DELIMITER ;; 2 /* Flexviews for MySQL ... 19 */ 20 21 DROP FUNCTION IF EXISTS flexviews.uow_from_dtime;; |
エラー対策2-5
Warningが出ています。
|
1 |
PHP Warning: Uninitialized string offset 0 in /root/swanhart-tools/flexviews/consumer/include/flexcdc.php on line 1269 |
mysqlbinlogから読み取ったバイナリログに空行が含まれていたため、エラーになっていました。
空行が来ても良いように、null避けを追加します。
|
1 |
$ vi consumer/include/flexcdc.php |
|
1 |
1255 if($line == '') { continue; } |
インストールが正常終了しました。
|
1 2 3 4 5 6 7 8 |
mysql> \. install.sql ... +---------------------------------------------------------+ | message | +---------------------------------------------------------+ | If you see no errors, then installation was successful. | +---------------------------------------------------------+ 1 row in set (0.00 sec) |
マテリアライズド・ビュー作成とリフレッシュ
flexviews_demo/INSTRUNCTIONSファイルの記述に従い、デモを行っていきます。
マテリアライズド・ビューを作成し、マスター表のデータを更新し、リフレッシュでデータが最新化されることを確認したいと思います。
Step 1: マスター表の作成
マスター表を作成し、デモ用のデータを投入します。
|
1 2 3 |
$ cd flexviews_demo $ mysql mysql> \. demo.sql |
Step 2: マテリアライズド・ビューログの作成
マテリアライズド・ビューログを作成します。
|
1 2 3 4 |
mysql> CALL flexviews.create_mvlog('demo','orders'); mysql> CALL flexviews.create_mvlog('demo','customers'); mysql> CALL flexviews.create_mvlog('demo','order_details'); mysql> CALL flexviews.create_mvlog('demo','products'); |
作成したテーブルの名前はディクショナリテーブルから確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT * FROM flexviews.mvlogs; +--------------+---------------+----------------------------------------+-------------+ | table_schema | table_name | mvlog_name | active_flag | +--------------+---------------+----------------------------------------+-------------+ | demo | customers | mvlog_d3057313a3361eb07ef05076ed982969 | 1 | | demo | orders | mvlog_c496f57e511287e48f593ade60d42649 | 1 | | demo | order_details | mvlog_19c14fc9808cd414aeda4f446d73598e | 1 | | demo | products | mvlog_97d59ac57e1f5791917836f843758fbd | 1 | | flexviews | mview_signal | mvlog_3b0cef8fb9788ab03163cf02b19918d1 | 1 | +--------------+---------------+----------------------------------------+-------------+ 5 rows in set (0.00 sec) |
試しに一つSELECTしてみますが、中身は空です。
|
1 2 |
mysql> SELECT * FROM flexviews.mvlog_19c14fc9808cd414aeda4f446d73598e; Empty set (0.00 sec) |
マテリアライズド・ビューがDMLで変更されるたびに、ここに変更ログが記録されていきます。
Step 3: マテリアライズド・ビューの作成
マテリアライズド・ビューを作成します。
|
1 |
mysql> \. views.sql |
エラー対策3-1
作成エラーになりました。
|
1 |
ERROR 1364 (HY000): Field 'last_step_at' doesn't have a default value |
refresh_step_info.last_step_at列がNOT NULL制約に違反しているので、テーブル定義を変更します。
|
1 2 3 4 5 6 7 8 |
mysql> DROP TABLE flexviews.refresh_step_info; mysql> CREATE TABLE IF NOT EXISTS flexviews.refresh_step_info( mview_id int(11) not null PRIMARY KEY, last_step varchar(255) not null, last_step_at TIMESTAMP DEFAULT NULL ) DEFAULT CHARSET=UTF8 ENGINE=MYISAM; |
重複作成でエラーになるため、一旦削除して仕切り直します。
（作成途中のゴミが残って削除し切れないので、ディクショナリテーブルをTRUNCATEしてしまいます。）
|
1 2 |
mysql> CALL flexviews.drop(flexviews.get_id('demo','mv_company_sales_items_monthly')); mysql> TRUNCATE flexviews.mview; |
views.sqlをインポートし、正常終了しました。
|
1 |
mysql> \. views.sql |
Step 4: データを参照
マテリアライズド・ビューからサンプルデータをSELECTします。
|
1 2 3 4 5 6 7 8 |
mysql> SELECT * FROM demo.mv_company_sales_total -> WHERE CompanyName LIKE 'Ranch%'; +----------+---------------+-------------------+-----+ | mview$pk | CompanyName | TotalSalesDollars | cnt | +----------+---------------+-------------------+-----+ | 74 | Rancho grande | 44202.14 | 12 | +----------+---------------+-------------------+-----+ 1 row in set (0.01 sec) |
マテリアライズド・ビューの定義を取得するにはget_sqlファンクションを使用します。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SELECT flexviews.get_sql( flexviews.get_id('demo','mv_company_sales_total')) as 'SQL'\G *************************** 1. row *************************** SQL: SELECT NULL as mview$pk, (c.CompanyName) as 'CompanyName', SUM(od.UnitPrice * od.Quantity) as 'TotalSalesDollars', COUNT(*) as 'cnt' FROM demo.customers as 'c' JOIN demo.orders as 'o' USING(CustomerId) JOIN demo.order_details as 'od' USING(OrderId) JOIN demo.products as 'p' USING(ProductId) GROUP BY (c.CompanyName) 1 row in set (0.01 sec) |
Step 5: マスター表のデータを更新
マスター表のデータを1行更新します。
|
1 2 3 4 |
mysql> UPDATE demo.order_details SET Quantity = Quantity + 100 -> WHERE odID = 532; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
変更ログがマテリアライズド・ビューログに記録されます。
dml_typeはINSERTで1、DELETEで-1、UPDATEは-1の後に1で記録されます。- UPDATEの結果通りにQuantityが+100加算されていることがわかります。
|
1 2 3 4 5 6 7 8 |
mysql> SELECT * FROM flexviews.mvlog_19c14fc9808cd414aeda4f446d73598e; +----------+--------+--------------+--------+------+---------+-----------+-----------+----------+----------+ | dml_type | uow_id | fv$server_id | fv$gsn | odID | OrderID | ProductID | UnitPrice | Quantity | Discount | +----------+--------+--------------+--------+------+---------+-----------+-----------+----------+----------+ | 1 | 67 | 10 | 15 | 532 | 10448 | 26 | 19.33 | 106 | 0 | | -1 | 67 | 10 | 14 | 532 | 10448 | 26 | 19.33 | 6 | 0 | +----------+--------+--------------+--------+------+---------+-----------+-----------+----------+----------+ 2 rows in set (0.00 sec) |
マテリアライズド・ビューはまだリフレッシュしていないので、値に変化はありません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> SELECT * FROM demo.mv_company_sales_total -> WHERE CompanyName LIKE 'Ranch%'; +----------+---------------+-------------------+-----+ | mview$pk | CompanyName | TotalSalesDollars | cnt | +----------+---------------+-------------------+-----+ | 74 | Rancho grande | 44202.14 | 12 | +----------+---------------+-------------------+-----+ 1 row in set (0.00 sec) mysql> SELECT NULL as mview$pk, (c.CompanyName) as 'CompanyName', SUM(od.UnitPrice * od.Quantity) as 'TotalSalesDollars', COUNT(*) as 'cnt' -> FROM demo.customers as 'c' -> JOIN demo.orders as 'o' USING(CustomerId) -> JOIN demo.order_details as 'od' USING(OrderId) -> JOIN demo.products as 'p' USING(ProductId) -> GROUP BY (c.CompanyName) -> HAVING c.CompanyName LIKE 'Ranch%'; +----------+---------------+-------------------+-----+ | mview$pk | CompanyName | TotalSalesDollars | cnt | +----------+---------------+-------------------+-----+ | NULL | Rancho grande | 46135.14 | 12 | +----------+---------------+-------------------+-----+ 1 row in set (0.12 sec) |
Step 6: マテリアライズド・ビューのリフレッシュ
マテリアライズド・ビューをリフレッシュします。
|
1 2 |
mysql> CALL flexviews.refresh(flexviews.get_id('demo','mv_company_sales_total'), 'BOTH', NULL); Query OK, 2 rows affected, 3 warnings (20.45 sec) |
Step 7: リフレッシュ結果の確認
マテリアライズド・ビューのデータが最新化されていることを確認します。
|
1 2 3 4 5 6 7 8 |
mysql> SELECT * FROM demo.mv_company_sales_total -> WHERE CompanyName LIKE 'Ranch%'; +----------+---------------+-------------------+-----+ | mview$pk | CompanyName | TotalSalesDollars | cnt | +----------+---------------+-------------------+-----+ | 74 | Rancho grande | 46135.14 | 12 | +----------+---------------+-------------------+-----+ 1 row in set (0.00 sec) |
おわりに
本記事で修正したスクリプトを盛り込んでアップロードし直したGitHubのForkを公開しています。お気軽に動作検証をお試しください。
MySQLでマテリアライズド・ビューを実現するツールとしてFlexviewsを紹介しました。本記事では紹介しきれませんでしたが、差分リフレッシュ・完全リフレッシュどちらも扱えたり、ほとんどの集合関数にも対応しているようです。ただ開発停止なので今後MySQLに大きなアップデートが来た場合に動作しなくなることも考えられます。導入は慎重にご検討くださいませ。
