Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQLをKVSのように使う

RDBは、使いやすい!速い!なのですが、カラムの拡張には、どの製品であろうと弱い。。24時間365日稼働しているサービスでカラム追加なんてなんてしようものなら、もうとんでもなくめんどくさいことになる。。 というわけで、カラム拡張が容易なテーブル構成について考えてみました。

例えばこんなテーブルがあったとします。

CREATE TABLE `m_users` (
  `id` bigint(20) unsigned NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(50) NOT NULL DEFAULT '',
  `login_id` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

なんのことはない、login_idとnameがあるテーブルです。実際こんな質素なユーザーテーブルはないとは思いますが、シナリオ的には、このテーブルで、数年運用して、1億レコードがすでに格納されたとことで改修が はいったなんてことにしましょうか?

改修内容

メールアドレス、性別、プロフィールのカラムを新規で追加したい・・ なんていう要件にしましょうか?

この程度のテーブルであれば、カラムを追加、もしくは別テーブルを生成することで対応できるのですが、ここでは、既存のテーブル(m_users)の定義に一切変更をあたえず、上記の要件を満たすように考えてみたいと思います

カラム名と値だけの構成のテーブルを新設する

あ、そうだ、keyとvalueだけもつテーブルを作ってみようということで、そんなテーブルを作ってみました。テーブルの名前は、既存のm_usersをひっかけて、m_user_propsという名前にしてみました

CREATE TABLE `m_user_props` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `mkey` varchar(20) NOT NULL COMMENT 'column_name',
  `mval` varchar(150) NOT NULL COMMENT 'value',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix01_user_props` (`mkey`,`mval`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
;

どう使うか?

直接上記テーブルにINSERTしてもいいのですが、なんかちょっとかっこよくしたかったのでこんなストアドプロシージャを作ってみました

DROP PROCEDURE IF EXISTS sp_put_m_user_props ;

DELIMITER $$

CREATE PROCEDURE `sp_put_m_user_props`(
IN _user_id bigint(20) unsigned,
IN _mkey varchar(20) ,
IN _mval varchar(150) ,
OUT _result int
)
BEGIN
  SET _result = 0;

    BEGIN
      DECLARE _not_found TINYINT UNSIGNED DEFAULT 0;
      DECLARE _studio_prop_id BIGINT ;

      DECLARE EXIT HANDLER FOR SQLEXCEPTION 
      BEGIN
        SET _result = -99;
      END;

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;

      BEGIN  

        SELECT id INTO _studio_prop_id FROM m_user_props 
        WHERE
          user_id = _user_id AND
          mkey = _mkey
        ;

        IF _not_found = 1 THEN 
          INSERT INTO
            m_user_props
          (
            user_id,
            mkey,
            mval
          ) VALUES (
            _user_id,
            _mkey,
            _mval
          ) ;
        ELSE
          UPDATE
            m_user_props
          SET
            mval = _mval
          WHERE
            user_id = _user_id AND
            mkey = _mkey
          ;
        END IF ;

     END ;
  END ;
END $$


DELIMITER ;

極力エラーは出したくなかった為、キーがあれば、更新する。キーが無かればインサートするといった構成にしてみました。

試してみる

では実際に試してみます。

m_users

+----+---------------------+---------------------+------+----------+
| id | created_at          | updated_at          | name | login_id |
+----+---------------------+---------------------+------+----------+
|  1 | 2016-03-02 17:40:45 | 2016-03-02 17:40:45 | hit  | hitabc   |
+----+---------------------+---------------------+------+----------+

m_user_props

今回依頼のあった、メールアドレス、性別、プロフィールを上記のストアドプロシージャを利用して追加してみます。

SET @user_id=1 ;
SET @mkey='mail' ;
SET @mval='hit@abcdefg.com' ;
CALL sp_put_m_user_props (@user_id,@mkey,@mval,@result) ;

SET @mkey='sex' ;
SET @mval='男' ;
CALL sp_put_m_user_props (@user_id,@mkey,@mval,@result) ;

SET @mkey='profile' ;
SET @mval='SQLの検証中です' ;
CALL sp_put_m_user_props (@user_id,@mkey,@mval,@result) ;

MySQL > SELECT * FROM m_user_props ;
+----+---------+---------+-----------------------+
| id | user_id | mkey    | mval                  |
+----+---------+---------+-----------------------+
|  1 |       1 | mail    | hit@abcdefg.com       |
|  2 |       1 | sex     | 男                    |
|  3 |       1 | profile | SQLの検証中です       |
+----+---------+---------+-----------------------+

感の良い皆様ならもうおきづきかもしませんが、key/value の構成でデータを持つことに成功しました

投稿した値を取得する

あとはもうSQLの話しなのでおわかりかと思いますが、こんな感じで今回追加したカラムが表示できるかと思います

  • SQL、その1
SELECT
  u.id AS user_id,
  u.name AS user_name,
  p.mval AS mail,
  p2.mval AS sex,
  p3.mval AS profile
FROM
  m_users u
LEFT JOIN
  m_user_props p
ON
  u.id = p.user_id AND
  p.mkey = 'mail'
LEFT JOIN
  m_user_props p2
ON
  u.id = p2.user_id AND
  p2.mkey = 'sex'
LEFT JOIN
  m_user_props p3
ON
  u.id = p3.user_id AND
  p3.mkey = 'profile'
;
+---------+-----------+-----------------+------+-----------------------+
| user_id | user_name | mail            | sex  | profile               |
+---------+-----------+-----------------+------+-----------------------+
|       1 | hit       | hit@abcdefg.com | 男   | SQLの検証中です       |
+---------+-----------+-----------------+------+-----------------------+

なんかかっこ悪いですね

  • SQL、その2
SELECT
  X.user_id,
  X.user_name,
  MAX(X.mail) AS mail ,
  MAX(X.sex) AS sex ,
  MAX(X.profile) AS profile
FROM
(
SELECT
  u.id AS user_id,
  u.name AS user_name,
  CASE p.mkey WHEN 'mail' THEN p.mval END AS mail,
  CASE p.mkey WHEN 'sex' THEN p.mval END AS sex,
  CASE p.mkey WHEN 'profile' THEN p.mval END AS profile
FROM
  m_users u
LEFT JOIN
  m_user_props p
ON
  u.id = p.user_id
) X
GROUP BY 
  X.user_id
;
+---------+-----------+-----------------+------+-----------------------+
| user_id | user_name | mail            | sex  | profile               |
+---------+-----------+-----------------+------+-----------------------+
|       1 | hit       | hit@abcdefg.com | 男   | SQLの検証中です       |
+---------+-----------+-----------------+------+-----------------------+

JOINの数は減ったけど、なんか複雑だなあ。。

ということで。。。

値を取得するストアドファンクションを作成する

値を取得するだけのストアドファンクションを作成してみました

DROP FUNCTION IF EXISTS sf_get_props ;

DELIMITER $$

DROP FUNCTION IF EXISTS sf_get_props ;

DELIMITER $$

CREATE FUNCTION `sf_get_props`(
_user_id bigint(20),
_mkey  varchar(20)
) RETURNS varchar(150) 
BEGIN   

   DECLARE _cur_val varchar(150)  ;
   DECLARE _not_found TINYINT UNSIGNED DEFAULT 0;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;

   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
     SET _cur_val = '-9999-';
     return _cur_val ;
   END;

   SELECT
     mval INTO _cur_val
   FROM
     m_user_props
   WHERE
     user_id = _user_id AND 
     mkey = _mkey ;

   RETURN _cur_val;

END $$

DELIMITER ;

さっそく使ってみる

こんな感じのSQLになります

SELECT
  m.id AS user_id,
  m.name AS user_name,
  sf_get_props(m.id,'mail') AS mail ,
  sf_get_props(m.id,'sex') AS sex,
  sf_get_props(m.id,'profile') AS profile
FROM
  m_users m
;
+---------+-----------+-----------------+------+-----------------------+
| user_id | user_name | mail            | sex  | profile               |
+---------+-----------+-----------------+------+-----------------------+
|       1 | hit       | hit@abcdefg.com | 男   | SQLの検証中です       |
+---------+-----------+-----------------+------+-----------------------+
1 row in set (0.01 sec)

SQLもシンプルでかっこ良くないですか?(中身はゴリゴリですが。。 結果的にカラムもテーブルの値としてもっておけば、増減は楽勝ですよねって話しでしたw パフォーマンスは、よくわかんないけどね。