MySQLをKVSのように使う

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

f:id:hit10231023:20180309104332j:plain

Acer ディスプレイモニター KA270Hbid 27インチ/フルHD/4 ms/HDMI端子付

出版社/メーカー: 日本エイサー
発売日: 2015/07/03
メディア: Personal Computers
この商品を含むブログ (2件) を見る

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

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、その１

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、その２

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もシンプルでかっこ良くないですか？（中身はゴリゴリですが。。結果的にカラムもテーブルの値としてもっておけば、増減は楽勝ですよねって話しでしたｗパフォーマンスは、よくわかんないけどね。

Database JUNKY

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