MySQLでマテリアライズドビュー3　実装編即時反映(on commit)

まずは簡単な履歴系のテーブルから実体化してみましょう例としてユーザのテーブルと紐づいているコメント機能を提示します。

まずは例によってテーブル定義とデータ作成から

ユーザテーブル

CREATE TABLE `user` (
`user_id`   BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY 
                                    COMMENT  'ユーザID',
`user_name` VARCHAR( 255 ) NOT NULL COMMENT  'ユーザ名'
) ENGINE = MYISAM COMMENT =  'ユーザ';

コメントテーブル

CREATE TABLE `comment` (
`comment_id`   BIGINT         NOT NULL
            AUTO_INCREMENT PRIMARY KEY COMMENT 'コメントID'    ,
`from_user_id` INT            NOT NULL COMMENT '送信元ユーザID',
`to_user_id`   INT            NOT NULL COMMENT '送信先ユーザID',
`comment`      VARCHAR( 255 ) NOT NULL COMMENT 'コメント'      ,
INDEX ( `from_user_id` ),INDEX (   `to_user_id` )  
) ENGINE = MYISAM  COMMENT =  'コメント';

ユーザのデータを10万件用意します

call insert_generate_series('user(user_name)','md5(?)',1,100000,1);

コメントのデータを100万件用意します

call insert_generate_series('comment(from_user_id,to_user_id,comment)','ceil(rand()*100000),ceil(rand()*100000),md5(?)',1,1000000,1);

まずは作成したデータを結合してデータを表示してみます。

SELECT SQL_NO_CACHE 
  comment_id , 
  fu.user_name form_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   
LIMIT 900000,30

30 rows in set (16.19 sec)

この手のテーブル構造は、ユーザが10万件、コメントが100万件を超えたあたりから性能が劣化してきます。Open Social系でウインク履歴、ガチャ履歴、あしあと履歴、twiter系のサービスで、つぶやき等膨大なレコードを扱う場合、さすがのMySQLでもJOINが厳しくなってきます。逆に中規模程度のサービスであれば対策は必要ではないです。また、マテリアライズ化する以外にJOINしない手法はいくつか存在します。

ビューを作成

CREATE OR REPLACE 
ALGORITHM = UNDEFINED
VIEW  `v_comment` AS 
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

SELECT SQL_NO_CACHE 
  comment_id , 
  from_user_name , 
    to_user_name , 
  comment 
 FROM `v_comment` 
LIMIT 900000,30;

30 rows in set (12.07 sec)

当然のことながら、ビューにしたところで速度は早くなりませんね

擬似マテリアライズドビューのテーブルを確保します。

CREATE TABLE mv_comment (
  PRIMARY KEY( `comment_id` ), INDEX ( `from_user_id` ), INDEX (   `to_user_id` )
)  
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

v_commentをmv_commentにしてアクセス

SELECT SQL_NO_CACHE 
  comment_id , 
  from_user_name , 
    to_user_name , 
  comment 
FROM  `mv_comment`
LIMIT 900000,30

30 rows in set (0.73 sec)

めちゃめちゃ早くなってます。JOINしてないんだもの、そりゃそうだ。

トリガーを張ります。

次にcommentに行が追加された場合にmv_commentにも追加されるようにトリガーを張ります。

DROP TRIGGER IF EXISTS comment_materialized_insert
DELIMITER //
CREATE TRIGGER comment_materialized_insert
AFTER INSERT ON comment FOR EACH ROW
BEGIN
  INSERT INTO mv_comment (
    comment_id , 
    from_user_id   , 
    to_user_id   ,   
    from_user_name , 
    to_user_name , 
    comment 
  )
  SELECT 
    NEW.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 , 
    NEW.comment 
   FROM user fu,user tu 
  WHERE fu.user_id=NEW.from_user_id 
    AND tu.user_id=NEW.to_user_id;   
END;
//
DELIMITER ;

以上で基本的なところは完了です。mv_commentで高速にアクセスできるようになりました。update、deleteトリガーも必要であれば適時張っておきます。