MySQLでマテリアライズドビュー4 実装編 手動更新(on demand)

mysql

さて、前回のMySQLでマテリアライズドビュー3 on commitのエントリーにてマテリアライズド・ビューの一般的な形は紹介しました。先の例は高速化という意味では成功でした。ですが、世の中何事も等価交換(トレードオフ)です。参照の高速化の見返りとして、ディスクの消耗という面で、オリジナルテーブル以上のディスク領域を消耗しています。また、commentテーブル追加時に都度トリガーを使ってマテリアライズ化したテーブルへも追加を行っているため、MySQLでのトリガーの低いパフォーマンスと相まって、更新時の負荷が重くなってしまいました。その差は以下のように無視出来ないレベルです。

  • comment_materialized_insertトリガーがある場合
call insert_generate_series('comment(from_user_id,to_user_id,comment)','ceil(rand()*100000),ceil(rand()*100000),md5(?)',1,1000000,1);

Query OK, 0 rows affected (1 hour 5 min 11.74 sec)

  • comment_materialized_insertトリガーがない場合
call insert_generate_series('comment(from_user_id,to_user_id,comment)','ceil(rand()*100000),ceil(rand()*100000),md5(?)',1,1000000,1);

Query OK, 0 rows affected (24 min 30.17 sec)

  • トリガーを除去
DROP TRIGGER IF EXISTS  `comment_materialized_insert`
  • 差分を更新するinsert into select文を書く
INSERT INTO mv_comment 
SELECT 
  comment_id , 
  fu.user_id   from_user_id   , 
  tu.user_id     to_user_id   ,   
  fu.user_name from_user_name , 
  tu.user_name   to_user_name , 
  comment 
FROM  `comment` cm,user fu,user tu 
WHERE fu.user_id=cm.from_user_id 
  AND tu.user_id=cm.to_user_id   
  AND comment_id > (SELECT MAX(comment_id) FROM mv_comment)

これを適時実行する。まとめて更新する分高速に動作する。例えば、参照前に上記SQLを実行するといった方法がon demandの使い方となる。

  • ストアドプロシージャ化しておく
DROP   PROCEDURE comment_materialized_insert;
delimiter //
CREATE PROCEDURE comment_materialized_insert()
begin
  INSERT INTO mv_comment 
  SELECT 
    comment_id , 
    fu.user_id   from_user_id   , 
    tu.user_id     to_user_id   ,   
    fu.user_name from_user_name , 
    tu.user_name   to_user_name , 
    comment 
  FROM  `comment` cm,user fu,user tu 
  WHERE fu.user_id=cm.from_user_id 
    AND tu.user_id=cm.to_user_id   
    AND comment_id > (SELECT MAX(comment_id) FROM mv_comment) ;
end;
//
delimiter ;

call comment_materialized_insert();