MySQLをスキーマレスDBとして扱うための試行錯誤のログ1

怪文書 mysql メモ

MySQLのデフォルトのデータベースエンジンはビックデータの格納には向かない。特にALTERでカラムが頻繁に変更になる場合には、行数が多くなるほどALTERに時間が掛かる。なので、そのための用途として、DWHを使うのが一般的です。もしくはSpider+VPとかInfiniDBとか。

以下は、MySQL単体のデータ構造だけで、なんとかしようと足掻いた後のメモ書きです。

例えばこういったテーブルを

user_id repeat category ext_val ext_str ....
1455 4 apple
784 8 orange 5 option
3151 6 apple 7

で以下のような縦もちのデータで定義しておき
type=1がrepeat
type=2がcategory
type=3がext_val
type=4がext_str

user_id type value
1455 1 4
1455 2 apple
784 1 8
784 2 orange
784 3 5
784 4 option
3151 1 6
3151 2 apple
3151 3 7

viewで横もちのデータに戻そうという作戦を考えました。
こうすれば、カラムが追加されてもviewを変更するだけで作業は完了です。かつカラムにデータがない場合に無駄なデータを格納する必要がなくなります。

CREATE TABLE `schema_less` (
 `user_id`   int(11)      NOT NULL,
 `column_id` int(11)      NOT NULL,
 `value`     varchar(255) NOT NULL,
 PRIMARY KEY    (`user_id`,`column_id`),
 KEY `covering` (`user_id`,`column_id`,`value`)
) ENGINE=InnoDB DEFAULT

INSERT INTO schema_less(user_id,column_id,value) VALUES 
( 1455        ,1     ,4        ), 
( 1455        ,2     ,'apple'  ), 
( 784         ,1     ,8        ), 
( 784         ,2     ,'orange' ), 
( 784         ,3     ,5        ), 
( 784         ,4     ,'option' ), 
( 3151        ,1     ,6        ), 
( 3151        ,2     ,'apple'  ), 
( 3151        ,3     ,7        )

ただ単にデータが表示されるようにするだけなら

SELECT 
  user_id,
  sum(           (column_id=1)*value    )              `repeat`  ,
  group_concat(IF(column_id=2 ,value,'') SEPARATOR '') `category`,
  sum(           (column_id=3)*value    )              `ext_val` ,
  group_concat(IF(column_id=4 ,value,'') SEPARATOR '') `ext_str`  
FROM schema_less 
GROUP BY user_id

group byでちゃちゃっと縦横変換を行いこれを元にviewを作るだけ

CREATE VIEW v_user AS 
SELECT 
  user_id,
  sum(           (column_id=1)*value    )              `repeat`  ,
  group_concat(IF(column_id=2 ,value,'') SEPARATOR '') `category`,
  sum(           (column_id=3)*value    )              `ext_val` ,
  group_concat(IF(column_id=4 ,value,'') SEPARATOR '') `ext_str`  
FROM schema_less 
GROUP BY user_id

ところが、このままviewにするとインデックスが効かなくなるという問題が....。

  • ユーザーIDで条件指定した場合
EXPLAIN SELECT * 
FROM  `v_user` 
WHERE  `user_id` =784
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ALL NULL NULL NULL NULL 3 Using where
2 DERIVED schema_less index NULL PRIMARY 8 NULL 9 
explain SELECT 
  user_id,
  sum(           (column_id=1)*value    )              `repeat`  ,
  group_concat(IF(column_id=2 ,value,'') SEPARATOR '') `category`,
  sum(           (column_id=3)*value    )              `ext_val` ,
  group_concat(IF(column_id=4 ,value,'') SEPARATOR '') `ext_str`  
FROM schema_less 
WHERE `user_id` =784 
GROUP BY user_id
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE schema_less ref PRIMARY,covering covering 4 const 4 Using index

どうやらviewにした場合user_idの条件をhavingにしてしまったようです。
いずれにしても、他のカラムの検索はhavingになってしまうので、これではインデックスが効かず実用的ではないですね。

データ構造上は、インデックスを効かせた上で上記実装は可能な筈なので、次回は別の集計手段を考えて見ましょう。
え、GROUP BY以外に集計手段があるの？いや、実はあるんですよ。