TL;DR
- auto_increment + JSON型（あるいはBLOB型やTEXT型でもいいけど）に生のJSONを突っ込む
- 後から
SELECT .. FOR UPDATE SKIP LOCKED でワーカーが取り出して、 INSERT .. SELECT JSON_TABLE(..) FROM .. で正規化したテーブルに突っ込みなおす
- 外部APIからの戻りのJSONを取り敢えずログテーブルに格納して…みたいな感じを想像している
PoC
こんなテーブルを用意した。
mysql80 13> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`seq` int(10) unsigned NOT NULL AUTO_INCREMENT,
`raw_json` json DEFAULT NULL,
PRIMARY KEY (`seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)
Twitterから1～10件のTweetを取ってきて、そのままテーブルに突っ込む。
my $count= int(rand(10) + 1);
my $json= to_json($twitter->search({q => "'MySQL'", lang => "ja", count => $count}));
$conn->do("INSERT INTO t2 (raw_json) VALUES (?)", undef, $json);
何回か叩いてテーブルの中身を確認するとこんな感じ。
mysql80 29> SELECT seq, JSON_LENGTH(raw_json->'$.statuses') FROM t2;
+-----+-------------------------------------+
| seq | JSON_LENGTH(raw_json->'$.statuses') |
+-----+-------------------------------------+
| 1 | 7 |
| 2 | 8 |
| 3 | 10 |
| 4 | 10 |
| 5 | 8 |
| 6 | 1 |
| 7 | 6 |
+-----+-------------------------------------+
7 rows in set (0.00 sec)
この状態で別々のクライアントから SELECT .. ORDER BY seq ASC LIMIT 1 FOR UPDATE SKIP LOCKED することで、「今他のクライアントがロックしていない行をseqの若い順に1件ロック」が表現できる。
mysql80 33> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql80 33> SELECT seq, raw_json FROM t2 ORDER BY seq ASC LIMIT 1 FOR UPDATE SKIP LOCKED INTO @seq, @raw_json;
Query OK, 1 row affected (0.00 sec)
mysql80 33> SELECT @seq; +------+
| @seq |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
------
mysql80 34> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql80 34> SELECT seq, raw_json FROM t2 ORDER BY seq ASC LIMIT 1 FOR UPDATE SKIP LOCKED INTO @seq, @raw_json;
Query OK, 1 row affected (0.00 sec)
mysql80 34> SELECT @seq;
+------+
| @seq |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
それぞれのクライアントで seq=1 と seq=2 の行をロックするついでに、 @raw_json 変数に raw_json カラムの中身を代入している。
JSON_TABLES関数は残念ながらカラムの値を直接参照できない（びっくりした）ので、一度変数の中に入れてやらないといけない。むむむ。
mysql80 34> SELECT tweet_id, text FROM JSON_TABLE(@raw_json, '$.statuses[*]' COLUMNS (tweet_id NUMERIC(32) PATH '$.id', text VARCHAR(300) PATH '$.text')) AS json;
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tweet_id | text |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 984620024198414338 | MySQL派でした |
| 984619948621230080 | MySQLくらいしか触ったことない |
| 984616323194896384 | なんか最近mysqlでやべえアプデとかあった？ |
| 984614613839888384 | この前調べてみたらレンタカーよりカーシェアリングの方が安いという事実を知った。そんなに進んでいたのね。。。 #PHP #MySQL |
| 984613344014290944 | 速効!図解プログラミングPHP MySQL―Windows/Linux PHP5対応 PHP5の基本から一歩ずつ学習。MySQLとの連携もマスター。力だめしの練習問題付き https://t.co/oT1wBBVkKV |
| 984608290205085696 | ISBB@東京の案件情報です。動画配信サイトシステム構築＠都内。PHP(LAMP環境における開発経験３年以上必須。MySql経験尚可、アジャイル経験尚可。詳細はこちらhttps://t.co/Mtw5EpBQNO |
| 984605840580591616 | ををを。。。MySQLのクエリの後に \G ってつけると、縦表示になるのか！初めて知った。
SELECT * FROM `table` LIMIT 10 \G; |
| 984603973939150850 | RT @yoku0825: デフォルトのままでもCPUを食い尽くすような正規表現はちゃんとタイムアウトするようになってた。えらい。
日々の覚書: 「危険な正規表現」 vs MySQL 8.0 https://t.co/hNpozpQnWz |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
JSON_TABLES は結構書式がややこしくて、
FROM (JSON_TABLE(..)) AS dummy のFROM句サブクエリーの形で
SELECT に受けるっぽい。
ちょっと思ったより使いにくくてびっくりしたけど、MySQLサーバー側の変数で完結できればJSONをパースするのにクライアントに転送しなくていいので通信量の削減にはなると思う。
ともあれ、 SELECT で受けられれば INSERT INTO .. SELECT .. の形に受けられるので、別のテーブルにINSERTして
mysql80 34> INSERT INTO t3 (tweet_id, text) SELECT tweet_id, text FROM JSON_TABLE(@raw_json, '$.statuses[*]' COLUMNS (tweet_id NUMERIC(32) PATH '$.id', text VARCHAR(300) PATH '$.text')) AS json;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql80 34> DELETE FROM t2 WHERE seq = @seq;
Query OK, 1 row affected (0.00 sec)
mysql80 34> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql80 34> SELECT seq, JSON_LENGTH(raw_json->'$.statuses') FROM t2;
+-----+-------------------------------------+
| seq | JSON_LENGTH(raw_json->'$.statuses') |
+-----+-------------------------------------+
| 1 | 7 |
| 3 | 10 |
| 4 | 10 |
| 5 | 8 |
| 6 | 1 |
| 7 | 6 |
+-----+-------------------------------------+
6 rows in set (0.00 sec)
JSON_TABLES関数がアレなことを除けば 割ときれいにジョブワーカーっぽいことができそうな予感。
ただこれ t2 （取り敢えずJSONで突っ込む）側のロックは分けられるけど、 t3 （正規化して突っ込む先）側はフツーにネクストキーロックとかデッドロックとかのアレを食らうので、 t3 側のロックはちゃんと設計しないと死ねそう。
