MySQLで疑似的なマテリアライズド・ビュー作成

by

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

Posted in: DB, MYSQL, ORACLE, 設定, 運用 ⋅ Tagged: , , ,

Comments are closed.

Post Navigation