LoginSignup
32
32

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@madilloar(madi lloar)

MySQLでWebサービスを止めずに、その裏のバッチ処理で大量にデータを洗い替える。

Last updated at Posted at 2015-09-23

Oracleでよく使っているやり方なのですが、mysqlでもできることが最近分かったので書くことにしました。

具体的には、テーブルのパーティション化に関することです。mysqlのバージョン5.5では、テーブルをパーティション化するまではできたのですが、EXCHANGE PARTITIONが使えませんでした。バージョン5.6から使えることを最近知ったのがきっかけです。

EXCHANGE PARTITION とかでググれば、要素技術は拾えるのですが、実際下記のような局面を想定した使い方を解説しているものが拾えなかったので、参考になれば幸いです。

よく使う局面

よく使うのが 「Webオンラインサービスを止めずに、その裏のバッチ処理で大量にデータを洗い替えたい。」 というような局面です。

もう少しイメージアップのための具体例をあげると、
(1).Webで在庫販売を行うシステムを構築する。
(2).Webオンラインサービス中に、全国の契約倉庫(例えば10ヶ所）から在庫情報が毎日AM１０時頃に集中して送信されてくる。
(3).各倉庫からの在庫情報は70%超のレコードが何らかの更新があり、データ更新というよりは、洗い替えに近い。
といった状況をイメージしてください。

このような、Webオンラインサービスを止めずに、その裏のバッチ処理で大量にデータを洗い替える時に、よく使うやり方です。

解決のアイデア

ダブルバッファリングを使います。
ダブルバッファリングとは、ゲーム等の動きのある画像描画で、画像更新に時間がかかり、画面がちらつくのを抑える考え方です。
これは、利用者の目に見える画面(オンスクリーン)の裏に、見えない画面(オフスクリーン)を用意し、このオフスクリーンに画像を描画して、オフスクリーンからオンスクリーンに一気に変えるものです。
image

これをWebオンラインサービスの裏の大量データバッチ更新に応用します。
Webオンラインサービスで表示するテーブルをオンスクリーンに見立て、バッチ処理で大量にデータを洗い替える側をオフスクリーンに見立てる。
バッチ処理で大量にデータを洗い替えたら、スクリーンを一気に変えるイメージです。
image

実装のアイデア

(1).利用しているデータベース基盤で、テーブルをパーティション化できるなら、パーティションを使います。
①Webオンラインサービスで利用するテーブルをパーティション化します。このパーティション化したテーブルをオンスクリーンに見立てます。
②バッチで利用するオフスクリーンのテーブルは、このパーティション化したテーブルと同じテーブル構造であるけれど、パーティション化していない通常のテーブルとして用意します。
③オフスクリーンのテーブルとオンスクリーンのテーブルを一気に変えるには、EXCHANGE PARTITIONを使います。

(2).利用しているデータベース基盤で、パーティションが使えないなら、ビューを利用します。
※今回はEXCHANGE PARTITIONの話を書きたかったのでビューの実装は割愛します。そんなに難しくはないので。
①同じ構造を持ったテーブルを2つ用意し、どちらのテーブルが現在オンスクリーンになっているか管理するテーブルを用意します。
②オフスクリーン側のテーブルをバッチで洗い替え、スクリーン管理テーブルでオフスクリーンをオンスクリーンに更新します。
③スクリーン管理テーブルでオンになっているテーブルを表示します。

パーティションを利用した実装例

まずはテーブルの定義から。

パーティション化した在庫テーブルを作ります。

パーティション化した在庫テーブル
DROP TABLE T_STOCK;
CREATE TABLE T_STOCK (
  STOCKHOUSE_ID INT,
  ITEM_ID CHAR(10),
  AMOUNT INT,
  VER_ID CHAR(1), 
  UPD_DTTM DATETIME
)
-- この例では倉庫は5倉庫
  PARTITION BY LIST(STOCKHOUSE_ID) (
  PARTITION P_S01 VALUES IN (1),
  PARTITION P_S02 VALUES IN (2),
  PARTITION P_S03 VALUES IN (3),
  PARTITION P_S04 VALUES IN (4),
  PARTITION P_S05 VALUES IN (5)
);
-- EXCHANGE PARTITIONする先の通常テーブルの構造はパーティションテーブルの
-- 構造と合わせる必要がある。というのは分かっていたが、インデックス名や
-- プライマリキー制約名の名前についても通常テーブルと同じ名前で作ること。
-- そうしないと、EXCHANGE PARTITIONをすると、次のエラーとなる。
-- ERROR 1736 (HY000) at line 1: Tables have different definitions
CREATE INDEX IDX_NO1 ON T_STOCK (STOCKHOUSE_ID, ITEM_ID);
ALTER TABLE T_STOCK ADD CONSTRAINT PK_NO1 PRIMARY KEY(STOCKHOUSE_ID, ITEM_ID);

通常テーブル倉庫ID=1の在庫情報ワークテーブルを作成します。

倉庫ID=1だけを例にしています。

通常テーブル倉庫ID=1の在庫情報ワークテーブル作成。
DROP TABLE T_STOCK_S01;    
CREATE TABLE T_STOCK_S01 (
  STOCKHOUSE_ID INT,
  ITEM_ID CHAR(10),
  AMOUNT INT,
  VER_ID CHAR(1), 
  UPD_DTTM DATETIME
);
CREATE INDEX IDX_NO1 ON T_STOCK_S01 (STOCKHOUSE_ID, ITEM_ID);
ALTER TABLE T_STOCK_S01 ADD CONSTRAINT PK_NO1 PRIMARY KEY(STOCKHOUSE_ID, ITEM_ID);

オンスクリーン側の在庫テーブルにデータを登録します。

在庫テーブルのCSV形式初期登録用データ

T_STOCK.csv
"1","1234567-01","313","1","2015/09/23 16:33:52"
"1","1234567-02","600","1","2015/09/23 16:33:52"
"1","1234567-03","702","1","2015/09/23 16:33:52"
"1","1234567-04","395","1","2015/09/23 16:33:52"
"1","1234567-05","903","1","2015/09/23 16:33:52"
"1","1234567-06","679","1","2015/09/23 16:33:52"
"1","1234567-07","2","1","2015/09/23 16:33:52"
"1","1234567-08","158","1","2015/09/23 16:33:52"
"1","1234567-09","518","1","2015/09/23 16:33:52"
"1","1234567-10","75","1","2015/09/23 16:33:52"
"2","1234567-01","802","1","2015/09/23 16:33:52"
"2","1234567-02","245","1","2015/09/23 16:33:52"
"2","1234567-03","295","1","2015/09/23 16:32:49"
"2","1234567-04","855","1","2015/09/23 16:32:49"
"2","1234567-05","960","1","2015/09/23 16:32:49"
"2","1234567-06","457","1","2015/09/23 16:32:49"
"2","1234567-07","398","1","2015/09/23 16:32:49"
"2","1234567-08","296","1","2015/09/23 16:32:49"
"2","1234567-09","672","1","2015/09/23 16:32:49"
"2","1234567-10","217","1","2015/09/23 16:32:49"
"3","1234567-01","82","1","2015/09/23 16:32:49"
"3","1234567-02","146","1","2015/09/23 16:32:49"
"3","1234567-03","593","1","2015/09/23 16:32:49"
"3","1234567-04","341","1","2015/09/23 16:32:49"
"3","1234567-05","788","1","2015/09/23 16:32:49"
"3","1234567-06","895","1","2015/09/23 16:32:49"
"3","1234567-07","133","1","2015/09/23 16:32:49"
"3","1234567-08","261","1","2015/09/23 16:32:49"
"3","1234567-09","427","1","2015/09/23 16:32:49"
"3","1234567-10","269","1","2015/09/23 16:32:49"
"4","1234567-01","842","1","2015/09/23 16:32:49"
"4","1234567-02","571","1","2015/09/23 16:32:49"
"4","1234567-03","144","1","2015/09/23 16:32:49"
"4","1234567-04","265","1","2015/09/23 16:32:49"
"4","1234567-05","569","1","2015/09/23 16:32:49"
"4","1234567-06","519","1","2015/09/23 16:32:49"
"4","1234567-07","68","1","2015/09/23 16:32:49"
"4","1234567-08","611","1","2015/09/23 16:32:49"
"4","1234567-09","458","1","2015/09/23 16:32:49"
"4","1234567-10","659","1","2015/09/23 16:32:49"
"5","1234567-01","312","1","2015/09/23 16:32:49"
"5","1234567-02","547","1","2015/09/23 16:32:49"
"5","1234567-03","372","1","2015/09/23 16:32:49"
"5","1234567-04","607","1","2015/09/23 16:32:49"
"5","1234567-05","401","1","2015/09/23 16:32:49"
"5","1234567-06","332","1","2015/09/23 16:32:49"
"5","1234567-07","63","1","2015/09/23 16:32:49"
"5","1234567-08","799","1","2015/09/23 16:32:49"
"5","1234567-09","627","1","2015/09/23 16:32:49"
"5","1234567-10","735","1","2015/09/23 16:32:49"

在庫テーブルに初期登録データをロード

在庫テーブルに初期登録データをロード
LOAD DATA LOCAL INFILE './T_STOCK.csv' INTO TABLE T_STOCK FIELDS TERMINATED BY ',' ENCLOSED BY '"' (STOCKHOUSE_ID, ITEM_ID, AMOUNT, VER_ID, UPD_DTTM);

在庫テーブルにデータを登録したので、統計情報を取得。

在庫テーブルの統計情報を取得
ANALYZE TABLE T_STOCK;

これで、オンスクリーンの在庫情報テーブルが整ったので、中身を確認します。

在庫テーブルの倉庫ID=1と3の中身
mysql> SELECT * FROM T_STOCK
    -> WHERE
    -> STOCKHOUSE_ID IN
    -> (1,3);
+---------------+------------+--------+--------+---------------------+
| STOCKHOUSE_ID | ITEM_ID    | AMOUNT | VER_ID | UPD_DTTM            |
+---------------+------------+--------+--------+---------------------+
|             1 | 1234567-01 |    313 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-02 |    600 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-03 |    702 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-04 |    395 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-05 |    903 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-06 |    679 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-07 |      2 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-08 |    158 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-09 |    518 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-10 |     75 | 1      | 2015-09-23 16:33:52 |
|             3 | 1234567-01 |     82 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-02 |    146 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-03 |    593 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-04 |    341 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-05 |    788 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-06 |    895 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-07 |    133 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-08 |    261 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-09 |    427 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-10 |    269 | 1      | 2015-09-23 16:32:49 |
+---------------+------------+--------+--------+---------------------+
20 rows in set (0.00 sec)

オフスクリーン側の洗い替えを行います。

倉庫ID=1の洗い替え用CSV形式データ

T_STOCK_S01.csv
"1","1234567-01","400","1","2015/09/23 16:45:12"
"1","1234567-02","600","1","2015/09/23 16:45:12"
"1","1234567-03","400","1","2015/09/23 16:45:12"
"1","1234567-04","400","1","2015/09/23 16:45:12"
"1","1234567-05","600","1","2015/09/23 16:45:12"
"1","1234567-06","400","1","2015/09/23 16:45:12"
"1","1234567-07","400","1","2015/09/23 16:45:12"
"1","1234567-08","600","1","2015/09/23 16:45:12"
"1","1234567-09","400","1","2015/09/23 16:45:12"
"1","1234567-10","400","1","2015/09/23 16:45:12"

倉庫ID=1用のワークテーブルを洗い替えるために、まずは空っぽにします。

倉庫ID=1用のワークテーブルを切り捨て
TRUNCATE TABLE T_STOCK_S01;

倉庫ID=1用のワークテーブルに洗い替え用のデータをロードします。

倉庫ID=1用のワークテーブルに洗い替え用のデータをロード
LOAD DATA LOCAL INFILE './T_STOCK_S01.csv' INTO TABLE T_STOCK_S01 FIELDS TERMINATED BY ',' ENCLOSED BY '"' (STOCKHOUSE_ID, ITEM_ID, AMOUNT, VER_ID, UPD_DTTM);

倉庫ID=1用のワークテーブルの統計情報取得。

倉庫ID=1用のワークテーブルの統計情報取得。
ANALYZE TABLE T_STOCK_S01;

これで、オフスクリーンの倉庫ID=1用のワークテーブルが整ったので、中身を確認します。

倉庫ID=1用のワークテーブルの全件
mysql> SELECT
    -> *
    -> FROM
    -> T_STOCK_S01;
+---------------+------------+--------+--------+---------------------+
| STOCKHOUSE_ID | ITEM_ID    | AMOUNT | VER_ID | UPD_DTTM            |
+---------------+------------+--------+--------+---------------------+
|             1 | 1234567-01 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-02 |    600 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-03 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-04 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-05 |    600 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-06 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-07 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-08 |    600 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-09 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-10 |    400 | 1      | 2015-09-23 16:45:12 |
+---------------+------------+--------+--------+---------------------+
10 rows in set (0.00 sec)

オンスクリーンとオフスクリーンを切り替えます。

倉庫ID=1用のワークテーブルと、在庫テーブルのパーティションS01(倉庫ID=1パーティション)を切り替える。

倉庫ID=1用のワークテーブルと、在庫テーブルのパーティションS01(倉庫ID=1パーティション)を切り替える
ALTER TABLE T_STOCK
EXCHANGE PARTITION P_S01
WITH TABLE T_STOCK_S01;

切り替え後のデータ確認。

倉庫ID=1の量と日付のカラムが切り替わっていることが確認できます。

切り替え後のオンスクリーン側(在庫テーブル)確認。
mysql> SELECT * FROM T_STOCK
    -> WHERE
    -> STOCKHOUSE_ID IN
    -> (1,3);
+---------------+------------+--------+--------+---------------------+
| STOCKHOUSE_ID | ITEM_ID    | AMOUNT | VER_ID | UPD_DTTM            |
+---------------+------------+--------+--------+---------------------+
|             1 | 1234567-01 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-02 |    600 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-03 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-04 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-05 |    600 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-06 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-07 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-08 |    600 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-09 |    400 | 1      | 2015-09-23 16:45:12 |
|             1 | 1234567-10 |    400 | 1      | 2015-09-23 16:45:12 |
|             3 | 1234567-01 |     82 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-02 |    146 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-03 |    593 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-04 |    341 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-05 |    788 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-06 |    895 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-07 |    133 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-08 |    261 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-09 |    427 | 1      | 2015-09-23 16:32:49 |
|             3 | 1234567-10 |    269 | 1      | 2015-09-23 16:32:49 |
+---------------+------------+--------+--------+---------------------+
20 rows in set (0.01 sec)

量と日付のカラムが切り替わっていることが確認できます。

切り替え後のオフスクリーン側(倉庫ID=1用のワークテーブル)確認。
mysql> SELECT
    -> *
    -> FROM
    -> T_STOCK_S01;
+---------------+------------+--------+--------+---------------------+
| STOCKHOUSE_ID | ITEM_ID    | AMOUNT | VER_ID | UPD_DTTM            |
+---------------+------------+--------+--------+---------------------+
|             1 | 1234567-01 |    313 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-02 |    600 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-03 |    702 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-04 |    395 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-05 |    903 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-06 |    679 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-07 |      2 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-08 |    158 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-09 |    518 | 1      | 2015-09-23 16:33:52 |
|             1 | 1234567-10 |     75 | 1      | 2015-09-23 16:33:52 |
+---------------+------------+--------+--------+---------------------+
10 rows in set (0.00 sec)
32
32
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
32
32

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?