MySQLで疑似的なマテリアライズド・ビューを作成
MySQLにはOracleで利用可能な、マテリアライズド・ビューは実装されてません。
その為、トリガーで対応したり、ワークテーブルを作成しておいて、定期的にデータを入れ替えるような処理で対応する必要があります。
過去の経験では、デイリー、ウイークリーランキングを作成する為に、以下のようにMySQLのEVENT SCHEDULE機能とREPLACE 構文を利用して変更されたデータを
定期的に入れ替える処理を行ないマテリアライズド・ビューの代わりに利用していました。
イベントは、イベントを作成してスケジュールします。但し、イベントスケジューラが有効になっていないかぎり実行されません。
メモ:EVENTの代わりに、LinuxのcronやWindows Task Schedulerでも良いかと思います。
■ 検証用にテーブルの作成
ここでは、City_MasterがオリジナルでCity_SnapShotはSNAPコピーとしています。
オリジナルのデータはMySQLのオフィシャルサイトからダウンロードしたWorldデータベースのCityテーブルのデータを利用しています。
root@localhost [world2]> CREATE TABLE `City_Master` ( -> `ID` int(11) NOT NULL AUTO_INCREMENT, -> `Name` char(35) NOT NULL DEFAULT '', -> `CountryCode` char(3) NOT NULL DEFAULT '', -> `District` char(20) NOT NULL DEFAULT '', -> `Population` int(11) NOT NULL DEFAULT '0', -> `test` varchar(255) DEFAULT NULL, -> PRIMARY KEY (`ID`), -> KEY `idx_CountryCode` (`CountryCode`), -> KEY `idx_City2_Covering` (`ID`,`Name`) -> ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.08 sec) root@localhost [world2]> insert into City_Master select * from City; Query OK, 4079 rows affected (0.15 sec) Records: 4079 Duplicates: 0 Warnings: 0 root@localhost [world2]> CREATE TABLE `City_SnapShot` ( -> `ID` int(11) NOT NULL AUTO_INCREMENT, -> `Name` char(35) NOT NULL DEFAULT '', -> `CountryCode` char(3) NOT NULL DEFAULT '', -> `District` char(20) NOT NULL DEFAULT '', -> `Population` int(11) NOT NULL DEFAULT '0', -> `test` varchar(255) DEFAULT NULL, -> PRIMARY KEY (`ID`), -> KEY `idx_CountryCode` (`CountryCode`), -> KEY `idx_City2_Covering` (`ID`,`Name`) -> ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.02 sec) root@localhost [world2]> root@localhost [world2]> REPLACE INTO City_SnapShot select ID,Name,CountryCode,District,Population,test from City_Master; Query OK, 4079 rows affected (0.19 sec) Records: 4079 Duplicates: 0 Warnings: 0 root@localhost [world2]> select * from City_Master where ID = 100; +-----+---------+-------------+------------+------------+------+ | ID | Name | CountryCode | District | Population | test | +-----+---------+-------------+------------+------------+------+ | 100 | Paraná | ARG | Entre Rios | 207041 | NULL | +-----+---------+-------------+------------+------------+------+ 1 row in set (0.00 sec) root@localhost [world2]> select * from City_SnapShot where ID = 100; +-----+---------+-------------+------------+------------+------+ | ID | Name | CountryCode | District | Population | test | +-----+---------+-------------+------------+------------+------+ | 100 | Paraná | ARG | Entre Rios | 207041 | NULL | +-----+---------+-------------+------------+------------+------+ 1 row in set (0.00 sec)
■ マスターテーブルでデータを変更してみます。(207041->207045)した後に、
REPLACEコマンドでSnapShotテーブルへ変更を反映してみます。
メモ:PKが同じであれば、データを問題無くリプレースするかどうかの動作確認。
root@localhost [world2]> update City_Master set Population = 207045 where ID = 100; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@localhost [world2]> select * from City_Master where ID = 100; +-----+---------+-------------+------------+------------+------+ | ID | Name | CountryCode | District | Population | test | +-----+---------+-------------+------------+------------+------+ | 100 | Paraná | ARG | Entre Rios | 207045 | NULL | +-----+---------+-------------+------------+------------+------+ 1 row in set (0.00 sec) root@localhost [world2]> select * from City_SnapShot where ID = 100; +-----+---------+-------------+------------+------------+------+ | ID | Name | CountryCode | District | Population | test | +-----+---------+-------------+------------+------------+------+ | 100 | Paraná | ARG | Entre Rios | 207041 | NULL | +-----+---------+-------------+------------+------------+------+ 1 row in set (0.00 sec) root@localhost [world2]> REPLACE INTO City_SnapShot select ID,Name,CountryCode,District,Population,test from City_Master; Query OK, 4080 rows affected (0.10 sec) Records: 4079 Duplicates: 1 Warnings: 0 root@localhost [world2]> select * from City_Master where ID = 100; +-----+---------+-------------+------------+------------+------+ | ID | Name | CountryCode | District | Population | test | +-----+---------+-------------+------------+------------+------+ | 100 | Paraná | ARG | Entre Rios | 207045 | NULL | +-----+---------+-------------+------------+------------+------+ 1 row in set (0.01 sec) root@localhost [world2]> select * from City_SnapShot where ID = 100; +-----+---------+-------------+------------+------------+------+ | ID | Name | CountryCode | District | Population | test | +-----+---------+-------------+------------+------------+------+ | 100 | Paraná | ARG | Entre Rios | 207045 | NULL | +-----+---------+-------------+------------+------------+------+ 1 row in set (0.00 sec) root@localhost [world2]>
■ ここから、EVENTを有効にして定期的にデータが自動反映されるスケジュールを設定します。
オプションファイルへ追加
event-scheduler=ON
■ オプションファイルへの設定変更が反映されているかどうか確認。
メモ:SETコマンドで、動的にも設定可能。
event_schedulerが実行されている事を確認したら、EVENTを作成しスケジュールを設定します。
root@localhost [world2]> show variables like 'event%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set (0.00 sec) root@localhost [world2]> show processlist; +----+-----------------+-----------+--------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+--------+---------+------+------------------------+------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 53 | Waiting on empty queue | NULL | | 4 | root | localhost | world2 | Query | 0 | starting | show processlist | +----+-----------------+-----------+--------+---------+------+------------------------+------------------+ 2 rows in set (0.01 sec) root@localhost [world2]> root@localhost [world2]> CREATE EVENT SNAP_SHOT_VIEW_City_Master_City_SnapShot -> ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR -> DO -> REPLACE INTO City_SnapShot select ID,Name,CountryCode,District,Population,test from City_Master; Query OK, 0 rows affected (0.00 sec) root@localhost [world2]> show events; +--------+------------------------------------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +--------+------------------------------------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+ | world2 | SNAP_SHOT_VIEW_City_Master_City_SnapShot | root@localhost | SYSTEM | ONE TIME | 2016-06-10 15:23:01 | NULL | NULL | NULL | NULL | ENABLED | 1 | utf8 | utf8_general_ci | utf8mb4_general_ci | +--------+------------------------------------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) root@localhost [world2]> SELECT * FROM information_schema.EVENTS\G *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: world2 EVENT_NAME: SNAP_SHOT_VIEW_City_Master_City_SnapShot DEFINER: root@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: REPLACE INTO City_SnapShot select ID,Name,CountryCode,District,Population,test from City_Master EVENT_TYPE: ONE TIME EXECUTE_AT: 2016-06-10 15:23:01 INTERVAL_VALUE: NULL INTERVAL_FIELD: NULL SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION STARTS: NULL ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2016-06-10 14:23:01 LAST_ALTERED: 2016-06-10 14:23:01 LAST_EXECUTED: NULL EVENT_COMMENT: ORIGINATOR: 1 CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8mb4_general_ci 1 row in set (0.00 sec) root@localhost [world2]>
■ イベントを設定し終わったので、データを変更すれば、
次回のスケジュール(2016-06-10 15:23:01)にはデータが変更される事になります。
データを一件変更してみます。
root@localhost [world2]> select * from City_Master where ID = 1; +----+-------+-------------+----------+------------+------+ | ID | Name | CountryCode | District | Population | test | +----+-------+-------------+----------+------------+------+ | 1 | Kabul | AFG | Kabol | 1780000 | NULL | +----+-------+-------------+----------+------------+------+ 1 row in set (0.00 sec) root@localhost [world2]> select * from City_SnapShot where ID = 1; +----+-------+-------------+----------+------------+------+ | ID | Name | CountryCode | District | Population | test | +----+-------+-------------+----------+------------+------+ | 1 | Kabul | AFG | Kabol | 1780000 | NULL | +----+-------+-------------+----------+------------+------+ 1 row in set (0.00 sec) root@localhost [world2]> update City_Master set Population = 1780001 where ID = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
■ データ反映の確認
2016-06-10 15:23:01にEVENTが実行されたので、
SnapShot側のテーブルのデータも変更されている事が確認出来ます。
root@localhost [world2]> select *,now() from City_Master where ID = 1; +----+-------+-------------+----------+------------+------+---------------------+ | ID | Name | CountryCode | District | Population | test | now() | +----+-------+-------------+----------+------------+------+---------------------+ | 1 | Kabul | AFG | Kabol | 1780001 | NULL | 2016-06-10 14:25:30 | +----+-------+-------------+----------+------------+------+---------------------+ 1 row in set (0.00 sec) root@localhost [world2]> select *,now() from City_SnapShot where ID = 1; +----+-------+-------------+----------+------------+------+---------------------+ | ID | Name | CountryCode | District | Population | test | now() | +----+-------+-------------+----------+------------+------+---------------------+ | 1 | Kabul | AFG | Kabol | 1780000 | NULL | 2016-06-10 14:25:36 | +----+-------+-------------+----------+------------+------+---------------------+ 1 row in set (0.00 sec) root@localhost [world2]> root@localhost [world2]> select *,now() from City_SnapShot where ID = 1; +----+-------+-------------+----------+------------+------+---------------------+ | ID | Name | CountryCode | District | Population | test | now() | +----+-------+-------------+----------+------------+------+---------------------+ | 1 | Kabul | AFG | Kabol | 1780001 | NULL | 2016-06-10 15:25:16 | +----+-------+-------------+----------+------------+------+---------------------+ 1 row in set (0.00 sec) root@localhost [world2]>
■ 集計表の場合
まずは、JPNの人口を合計したテーブルを作成してデータを入力しておく。
メモ:テーブルにはPKが必要です。
メモ:実際の人口は1億2000万を超えています。
root@localhost [world2]> select CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode; +-------------+-----------------+ | CountryCode | SUM(Population) | +-------------+-----------------+ | JPN | 77965107 | +-------------+-----------------+ root@localhost [world2]> create table City_Summary ( -> ID int(11) NOT NULL, -> CountryCode char(3) NOT NULL DEFAULT '', -> Population int(11) NOT NULL DEFAULT '0', -> PRIMARY KEY (`ID`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.02 sec) root@localhost [world2]> REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 root@localhost [world2]> select * from City_Summary; +----+-------------+------------+ | ID | CountryCode | Population | +----+-------------+------------+ | 1 | JPN | 77965107 | +----+-------------+------------+ 1 row in set (0.00 sec) root@localhost [world2]>
■ 集計表へのデータ反映
東京の人口を10名増やして、EVENTを作成してスケジュールを設定しデータが反映されるか確認してみます。
root@localhost [world2]> update City_Master set Population = 7980240 where Name = 'tokyo'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@localhost [world2]> select * from City_Summary; +----+-------------+------------+ | ID | CountryCode | Population | +----+-------------+------------+ | 1 | JPN | 77965107 | +----+-------------+------------+ 1 row in set (0.00 sec) root@localhost [world2]> CREATE EVENT SNAP_SHOT_VIEW_City_Master_City_Summary -> ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE -> DO -> REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode; Query OK, 0 rows affected (0.00 sec) root@localhost [world2]> root@localhost [world2]> SELECT * FROM information_schema.EVENTS -> where EVENT_NAME = 'SNAP_SHOT_VIEW_City_Master_City_Summary'\G *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: world2 EVENT_NAME: SNAP_SHOT_VIEW_City_Master_City_Summary DEFINER: root@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode EVENT_TYPE: ONE TIME EXECUTE_AT: 2016-06-10 15:07:09 INTERVAL_VALUE: NULL INTERVAL_FIELD: NULL SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION STARTS: NULL ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2016-06-10 14:57:09 LAST_ALTERED: 2016-06-10 14:57:09 LAST_EXECUTED: NULL EVENT_COMMENT: ORIGINATOR: 1 CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8mb4_general_ci 1 row in set (0.01 sec) root@localhost [world2]>
■ 集計データ結果確認
設定時間に確認してみると、集計データが反映されている事が確認出来ました。
root@localhost [world2]> select *,now() from City_Summary; +----+-------------+------------+---------------------+ | ID | CountryCode | Population | now() | +----+-------------+------------+---------------------+ | 1 | JPN | 77965107 | 2016-06-10 14:59:26 | +----+-------------+------------+---------------------+ 1 row in set (0.00 sec) root@localhost [world2]> select *,now() from City_Summary; +----+-------------+------------+---------------------+ | ID | CountryCode | Population | now() | +----+-------------+------------+---------------------+ | 1 | JPN | 77965117 | 2016-06-10 15:07:31 | +----+-------------+------------+---------------------+ 1 row in set (0.00 sec) root@localhost [world2]>
■ 継続的、且つ定期的にEVENTを実行したい場合
上記の例は、1回のみ実行する方法でしたが、
一定間隔で,継続して定期的に実行するには、以下の様にEVENTを作ると良いです。
root@localhost [world2]> CREATE EVENT SNAP_SHOT_VIEW_City_Master_City_Summary -> ON SCHEDULE -> EVERY 10 MINUTE -> COMMENT 'サマリーテーブルを10分毎に更新します' -> DO -> REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode; Query OK, 0 rows affected (0.00 sec) root@localhost [world2]> SELECT * FROM information_schema.EVENTS\G *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: world2 EVENT_NAME: SNAP_SHOT_VIEW_City_Master_City_Summary DEFINER: root@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 10 INTERVAL_FIELD: MINUTE SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION STARTS: 2016-06-10 15:46:29 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2016-06-10 15:46:29 LAST_ALTERED: 2016-06-10 15:46:29 LAST_EXECUTED: 2016-06-10 15:46:29 EVENT_COMMENT: サマリーテーブルを10分毎に更新します ORIGINATOR: 1 CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8mb4_general_ci 1 row in set (0.00 sec) root@localhost [world2]>
Oracleのマテリアライズド・ビュー程の柔軟性は無いですが、
こんな対応方法もあるかと思いますので、使えそうでしたら是非試してみて下さい。
メモ: イベントの削除
———————-
DROP EVENT イベント名;
Triggerを利用する場合の参考)
http://www.fromdual.ch/mysql-materialized-views
http://d.hatena.ne.jp/IT7C/20100730/1280501734
参考)
13.2.8 REPLACE 構文
https://dev.mysql.com/doc/refman/5.6/ja/replace.html
13.2.5.3 INSERT … ON DUPLICATE KEY UPDATE 構文
https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html
13.1.11 CREATE EVENT 構文
https://dev.mysql.com/doc/refman/5.6/ja/create-event.html