Database

高速(?)なMySQL 8.0を検証してみた

2019年9月時点のMySQL最新バージョンは8.0.17(リリース日:2019/07/22)
業務ではMySQL 5.xを使うことが多いが、「mysql8」で検索をすると
『MySQL 8.0: MySQL 5.7よりも最大2倍高速』と大々的に出てくる。

実際にどれほど高速になっているのかは気にはなっていた(実際にアップデートをかけるのはいろいろと難しいorz)ので、
現行の最新であるMySQL8.0.17と5.7でどこまで差が出てくるのかをざっくり見てみたいと思う。

【検証環境】
Vagrant Ver 2.2.5
VirtualBox Ver 6.0.10
MySQL Ver 8.0.17
MySQL Ver 5.7.27

※デフォルト設定にて検証

■適応なDBとテーブル、200万程度のレコードを用意してみる


まずは適当に下ごしらえ

CREATE DATABASE log_db;
DROP TABLE IF EXISTS `log_db`.`user_l_action`;
CREATE TABLE `log_db`.user_l_action (
    `log_id`           bigint(20) unsigned not null auto_increment comment '履歴ID',
    `user_id`         int(11) unsigned not null comment 'ユーザID',
    `sex`               tinyint(1) unsigned not null comment '性別(0:未指定 1:男性 2:女性 3:それ以外)'
    `action_id`      int(11) unsigned not null comment '行動id',
    `create_date` date not null comment '作成日',
    `create_time` datetime not null comment '作成日時',
    PRIMARY KEY (log_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'ユーザ行動履歴';

insert into `log_db`.user_l_action (user_id,sex,action_id,create_date,create_time) values(228841,0,252,'2019-08-01','2019-08-01 00:00:00');
insert into `log_db`.user_l_action (user_id,sex,action_id,create_date,create_time) values(329246,2,699,'2019-08-01','2019-08-01 00:00:00');
insert into `log_db`.user_l_action (user_id,sex,action_id,create_date,create_time) values(456994,2,982,'2019-08-01','2019-08-01 00:00:00');
… and more…                                                    

こんな感じの適当なレコード用意

レッツ検証!白星(☆)黒星(★)で優劣をつけてみる

1. countしてみたら約3倍以上ものパフォーマンスの差が

★mysql Ver 5.7.27

mysql> select count(0) as record_count from log_db.user_l_action;
+--------------+
| record_count |
+--------------+
|        2077420 |
+--------------+
1 row in set (2.74 sec)

☆mysql Ver 8.0.17

mysql> select count(0) as record_count from log_db.user_l_action;
+--------------+
| record_count |
+--------------+
|        2077420 |
+--------------+
1 row in set (1.06 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 1.98 2.31 2.09 2.29 2.16
mysql 8.0 0.62 0.59 0.56 0.57 0.58

2. 特定のレコードをフルスキャンしてみた

8.0の方が優勢。

mysql> explain select * from log_db.user_l_action where action_id = 152;
+----+-------------+---------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | user_l_action | NULL | ALL | NULL | NULL | NULL | NULL | 2071994 | 10.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

★mysql Ver 5.7.27

2172 rows in set (3.44 sec)

☆mysql Ver 8.0.17

2172 rows in set (3.11 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 3.27 3.30 3.38 3.33 3.32
mysql 8.0 3.05 3.07 3.06 3.07 3.06

3. indexを追加してみた①

alterは、5.7の方が優勢

☆mysql Ver 5.7.27

mysql> alter table log_db.user_l_action add index idx_act(action_id);
Query OK, 0 rows affected (16.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

★mysql Ver 8.0.17

mysql> alter table log_db.user_l_action add index idx_act(action_id);
Query OK, 0 rows affected (19.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

あまり差はなさそう

mysql> explain select * from log_db.user_l_action where action_id = 152;
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user_l_action | NULL | ref | idx_act | idx_act | 4 | const | 2172 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

★mysql Ver 5.7.27

2172 rows in set (0.17 sec)

☆mysql Ver 8.0.17

2172 rows in set (0.16 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 0.03 0.03 0.05 0.04 0.037
mysql 8.0 0.03 0.03 0.03 0.04 0.032

4. indexを追加してみた②

こちらも、alterは5.7の方が優勢

☆mysql Ver 5.7.27

mysql> alter table log_db.user_l_action add index idx_scd(sex,create_date);
Query OK, 0 rows affected (18.80 sec)
Records: 0 Duplicates: 0 Warnings: 0

★mysql Ver 8.0.17

mysql> alter table log_db.user_l_action add index idx_scd(sex,create_date);
Query OK, 0 rows affected (22.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

group byでselectしてみた。8.0の方が優勢。

mysql> explain select create_date, sex, count(0) as create_cnt from log_db.user_l_action group by sex,create_date order by create_date asc, sex asc;
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+----------------------------------------------+
| 1 | SIMPLE | user_l_action | NULL | index | idx_scd | idx_scd | 4 | NULL | 2071994 | 100.00 | Using index; Using temporary; Using filesort |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

★mysql Ver 5.7.27

68 rows in set (2.94 sec)

☆mysql Ver 8.0.17

68 rows in set (2.50 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 2.84 2.80 2.83 2.87 2.83
mysql 8.0 2.37 2.29 2.41 2.35 2.35

5. 範囲検索用にindexを追加。

8.0の方が優勢。

mysql> alter table log_db.user_l_action add index idx_cdatetime(create_time);
mysql> explain select user_id,sex,action_id, create_time from log_db.user_l_action where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | user_l_action | NULL | range | idx_cdatetime | idx_cdatetime | 5 | NULL | 192764 | 100.00 | Using index condition |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)

★mysql Ver 5.7.27

103871 rows in set (0.80 sec)

☆mysql Ver 8.0.17

103871 rows in set (0.71 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 0.67 0.74 0.74 0.69 0.71
mysql 8.0 0.65 0.68 0.66 0.63 0.66

6. ついでにdistinctしてみた

8.0の方が優勢。

mysql> select distinct(user_id),sex,action_id, create_time from log_db.user_l_action where create_time between "
2019-09-01 00:00:00" and "2019-09-01 23:59:59";

★mysql Ver 5.7.27

103871 rows in set (1.90 sec)

☆mysql Ver 8.0.17

103871 rows in set (1.13 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 1.33 1.33 1.34 1.29 1.32
mysql 8.0 1.00 0.98 0.95 0.98 0.97

7. 一応logデータなのでupdateするケースはほぼないが、試しにやってみた

26,101レコードの更新で5.7優勢の結果に

☆mysql Ver 5.7.27

mysql> update log_db.user_l_action set sex = 0 where sex = 3 and create_date = '2019-08:30';
Query OK, 26101 rows affected (3.18 sec)
Rows matched: 26101 Changed: 26101 Warnings: 0

★mysql Ver 8.0.17

mysql> update log_db.user_l_action set sex = 0 where sex = 3 and create_date = '2019-08:30';
Query OK, 26101 rows affected (4.82 sec)
Rows matched: 26101 Changed: 26101 Warnings: 0

8. 試しにsumしてみた。

8.0の方が優勢。

★mysql Ver 5.7.27

mysql> select sum(sex) as sum_test_full from log_db.user_l_action;
+---------------+
| sum_test_full |
+---------------+
|         3198555 |
+---------------+
1 row in set (2.55 sec)

mysql> select sum(sex) as sum_test_idx from log_db.user_l_action where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
+--------------+
| sum_test_idx |
+--------------+
|          156200 |
+--------------+
1 row in set (0.63 sec)

☆mysql Ver 8.0.17

mysql> select sum(sex) as sum_test_full from log_db.user_l_action;
+---------------+
| sum_test_full |
+---------------+
|         3198555 |
+---------------+
1 row in set (2.13 sec)

mysql> select sum(sex) as sum_test_idx from log_db.user_l_action where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
+--------------+
| sum_test_idx |
+--------------+
|          156200 |
+--------------+
1 row in set (0.54 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7(full) 1.74 1.74 1.71 1.72 1.72
mysql 8.0(full) 1.72 1.69 1.70 1.68 1.69
mysql 5.7(idx) 0.50 0.51 0.50 0.50 0.50
mysql 8.0(idx) 0.44 0.46 0.44 0.44 0.44

9. 無理やりjoinもしてみた。

とりあえずのテスト用のテーブルを用意

DROP TABLE IF EXISTS mst_db.user_m_sex;
CREATE TABLE mst_db.user_m_sex (
  sex tinyint(1) unsigned not null comment '性別(0:未指定 1:男性 2:女性 3:それ以外)',
  sex_name varchar(50) not null default '' comment '性別名',
PRIMARY KEY (sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'ユーザ性別';

テスト用のテーブルとjoinしてみた。5.7の方が優勢。

☆mysql Ver 5.7.27

mysql> select log_id,sex, sex_name from log_db.user_l_action join mst_db.user_m_sex using(sex);
2077420 rows in set (5.07 sec)

mysql> select log_id,sex, sex_name from log_db.user_l_action join mst_db.user_m_sex using(sex) where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
103871 rows in set (2.04 sec)

★mysql Ver 8.0.17

mysql> select log_id,sex, sex_name from log_db.user_l_action join mst_db.user_m_sex using(sex);
2077420 rows in set (5.60 sec)

select log_id,sex, sex_name from log_db.user_l_action join mst_db.user_m_sex using(sex) where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
103871 rows in set (2.56 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7(full) 4.93 4.71 4.74 4.81 4.79
mysql 8.0(full) 5.21 5.14 5.22 5.12 5.17
mysql 5.7(idx) 1.69 1.68 1.75 1.78 1.72
mysql 8.0(idx) 2.42 2.34 2.39 2.39 2.38

10. mysqlslapを使ってみた。

read・key・write・updateを測定。結果5.7が優勢であった

特にwrite・updateの差は顕著。

【read】

☆mysql Ver 5.7.27

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=read --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.649 seconds
Minimum number of seconds to run all queries: 1.649 seconds
Maximum number of seconds to run all queries: 1.649 seconds
Number of clients running queries: 10
Average number of queries per client: 100

★mysql Ver 8.0.17

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=read --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 2.037 seconds
Minimum number of seconds to run all queries: 2.037 seconds
Maximum number of seconds to run all queries: 2.037 seconds
Number of clients running queries: 10
Average number of queries per client: 100


【key】

☆mysql Ver 5.7.27

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.113 seconds
Minimum number of seconds to run all queries: 0.113 seconds
Maximum number of seconds to run all queries: 0.113 seconds
Number of clients running queries: 10
Average number of queries per client: 100

★mysql Ver 8.0.17

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.173 seconds
Minimum number of seconds to run all queries: 0.173 seconds
Maximum number of seconds to run all queries: 0.173 seconds
Number of clients running queries: 10
Average number of queries per client: 100


【write】

☆mysql Ver 5.7.27

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.534 seconds
Minimum number of seconds to run all queries: 0.534 seconds
Maximum number of seconds to run all queries: 0.534 seconds
Number of clients running queries: 10
Average number of queries per client: 100

★mysql Ver 8.0.17

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.238 seconds
Minimum number of seconds to run all queries: 1.238 seconds
Maximum number of seconds to run all queries: 1.238 seconds
Number of clients running queries: 10
Average number of queries per client: 100


【update】

☆mysql Ver 5.7.27

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.538 seconds
Minimum number of seconds to run all queries: 0.538 seconds
Maximum number of seconds to run all queries: 0.538 seconds
Number of clients running queries: 10
Average number of queries per client: 100

★mysql Ver 8.0.17

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.280 seconds
Minimum number of seconds to run all queries: 1.280 seconds
Maximum number of seconds to run all queries: 1.280 seconds
Number of clients running queries: 10
Average number of queries per client: 100

書き込み系のパフォーマンス差が目立っていたので、パラメータ「auto-generate-sql-write-number」が足を引っ張ている可能性が?(read/keyもパフォーマンスが低下しているのはこれが原因なのでは??)

ということで、「auto-generate-sql-write-number」を使用せず、検証で作成したsqlで並列検索結果を見てみる。

★mysql Ver 5.7.27

$ mysqlslap --concurrency=10 --iterations=5 --create-schema=log_db --query='select * from log_db.user_l_action where action_id = 152' --number-of-queries=1000 --user=root --passwordBenchmark
Average number of seconds to run all queries: 10.284 seconds
Minimum number of seconds to run all queries: 9.263 seconds
Maximum number of seconds to run all queries: 12.670 seconds
Number of clients running queries: 10
Average number of queries per client: 100

☆mysql Ver 8.0.17

$ mysqlslap --concurrency=10 --iterations=5 --create-schema=log_db --query='select * from log_db.user_l_action where action_id = 152' --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Average number of seconds to run all queries: 9.483 seconds
Minimum number of seconds to run all queries: 9.378 seconds
Maximum number of seconds to run all queries: 9.634 seconds
Number of clients running queries: 10
Average number of queries per client: 100

Averageは8.0優勢の結果に。

少なくとも8.0のインサートは5.7より遅いのではと

※デフォルト設定の場合

■総評

ざっくりの検証だけだが、
1. countのパフォーマンス改善はかなり好印象。他についてはそこまで大きな差はなさそうであった。
ただし、updateなど書き込みは遅くなった印象を受ける(検証ケースが少ないので断言はできないが、結構気になる結果に)
新規開発等でこれから環境構築するのであれば、とりあえずは「mysql8」を選択しておけば良いのかな…といった印象を受けました。
mysql 5系からmysql 8系への移行の際は影響範囲等の兼ね合いもあるため、移行の「即決」はできないが価値はありそうかなと。
今回は触れないが、移行する際にどのような苦難が待ち受けているかなどは、機会があるようであれば今後紹介したいなと思います。

2016/09/19Database

metabaseが便利らしい

metabaseが便利らしい」と小耳に挟んだことをふと思い出しました。
テストや調査業務でExcel等の数値と睨めっこしていたことがきっかけです。
グラフとかで見ると変化や傾向が見えやすいのになぁと思い、ふと思い出しました。

ただ、便利「らしい」ことしかわからなかったので、実際に触れてみていきたいと思います。

今回は以前の投稿記事「2ファイルでLAMP開発環境構築」で構築した環境にmetabaseを導入していきます。

よって各バージョンは以下のようになります。

vagrant 2.2.3
virtualBox 6.0
centos 7.2
php(今回は不要) 7.3
mysql 8.0
java(OpenJDK) 8(1.8)
metabase 0.32.1

1. metabaseとは


metabaseはデータを可視化してくれるOSSです。
DBのデータから、Excelのようにグラフを作成できすることができるので、データ解析に(おそらく)役立ちます。
加えて、直感的に使用できるクエリビルダーが組み込まれており、sqlの知識がなくても調査や集計を簡易に行うことができるのではと…。
もちろんsqlを直接叩いて結果を描画することも可能です。
SQLite、MongoDb、Postgresなど複数対応しておりますが、私が業務で最も使用するMysqlを対象に紹介を行っていきます。


2. 下準備


metabaseを起動させるため、OpenJDKをインストールしましょう

$ sudo yum -y install java-1.8.0-openjdk java-1.8.0-openjdk-devel

公式ページから執筆時点で最新であるv0.32.1をダウンロードし、適当な場所に設置します

https://www.metabase.com/docs/latest/getting-started.html

https://www.metabase.com/start/jar.html


今回はサンプルとして、テスト用DB/テーブルを作成し、適当なレコードをセットしておきます。

mysql> CREATE DATABASE metabase DEFAULT CHARACTER SET utf8;

また、テスト用テーブルを作成し、データを放り込んでおきます。
metabase.item_m
metabase.boss_m
metabase.user_t
metabase.user_l_boss_drop_item

※解析対象のDBがある場合は不要です

なお、Mysql8.0.4以降の場合、デフォルトで認証方式が caching_sha2_password となっています。
metabaseでは非対応となっており、DBへの接続が失敗してしまいますので、以下のようにして接続用のユーザを作成しておくと良いでしょう。

mysql> create user metabase@localhost identified with mysql_native_password by 'Sr47f*dkdk3';
g
mysql> grant select on *.* to 'metabase'@'localhost';
mysql> flush privileges;

3. metabaseを起動させてみる


「下準備」でダウンロードしたjarを実行しましょう

$ sudo java -jar metabase.jar

こんな感じに起動が完了したら、
http://***.***.**.**:3000/setup/
にアクセスします。


こんな感じの画面が出ればOKです。指示に従って初期登録を行っていきましょう。

①②③と3ステップあります。

②では参照するDBを登録していきます。

本記事では、下準備で登録しておいた
db:metabase
user:metabase
pass:Sr47f*dkdk3
で登録します

ページ下部の「データ」項目に先程設定したものが表示されていればOKです。


もし追加されていない場合は、
設定>管理者>データベースを追加する
から再登録を行いましょう。
※参照先のDBの追加したい場合もこの手順で行います


4. metabaseをちょっと触ってみる


とりあえず、実験で
user_l_boss_drop_item
というテーブルの中身を見てみます。
(ゲームアプリのボスドロップログテーブルを適当に作ってみました。三か月分のデータをこれまた適当に突っ込んでおります)

初期表示時点でいい感じにグラフができており、前月比なども自動で出力してくれていますね。
縦軸がアイテムドロップ数、横軸が日付(Y-m-d)となっており、
累計(Total User L Boss Drop Item)の日ごとの数値が確認できます。

クエリビルダも触ってみます。

絞り込みなんかも直感的に操作できます。

とりあえず全件表示してみました。


idだけだとなんのアイテムがドロップしているのかよくわからんので、アイテム名を出してみます。
joinが必要なので、直接sqlを叩きます。

結果が表示されました。(アイテム名もこれまた適当です)

5. 実際にmetabaseに触れてみて

ちょっと触ってみましたが、なんだか色々できそうです。
実際に触ってみて「metabaseが便利らしい」ということは実感できました。
使い方次第でデータ解析を効率よく行えのではないかと。
まだまだ操作が不慣れなので、描画するグラフを変えてみたり、ダッシュボードをカスタマイズしてみたりと今後も色々試していけたらと思います。

以上、phpエンジニアのmetabase紹介でした。乱文長文、失礼しました。

2016/09/19Database

Firebase Realtime Databaseで遊んでみた件

ヒロシです。
FirebaseとはGoogleが提供しているmBaasです。mBaasって?という方はググってください。
今回、その機能の中に、「Realtime Database」なるものが存在するということをお客様よりお聞きし、
さっそく遊んでみました。というお話です。

Realtime Databaseとはなんぞや?

https://firebase.google.com/

「アプリデータを瞬時に保存および同期」

うん、なんとなくわかった。
サーバとクライアント間で同期できるNoSQLを使った仕組みで、チャットみたいなのも簡単に?作れるんだよね、きっと。
(という、ふんわりとした理解)

そんなこんなで、mBaasだもんで、UnityやらAndroidやら、そんなとこで使うもんなのかな?と
Firebaseのドキュメントを読み進めてたところ、
C#やAndroid、Unityというワードに、ごくごく当たり前のように潜んでいる
『ウェブ』なんていう、何とも抽象的な「そこにシビれる!あこがれるゥ!」な、
技術ドキュメントあるあるワードを発見。

サンプルコードもあったので見てみると、シンプルなjavascriptですね、これ簡単そう。

ということで、最近モノ忘れの激しい社内メンバーのために、リアルタイム共有TODOツールを作ってみたYO!

今回作りたいもの

  • TODOを登録できる
  • リアルタイムに共有できる
  • 完了したら削除できる

至ってシンプルですが、まずはこんなところでしょうか。

まずは初期設定

https://qiita.com/H_Crane/items/adf88cc01eabce2b9a5f

この辺りを参考にさせてもらって、まずは登録して、プロジェクト作って、
ウェブ用のconfigをコピー(下で使ってます)、と。

そして、今回作成するのは「todo.html」1枚だけ!(たぶん)

登録するよ

まずはこんな感じなのかね?
(Firebaseのプロジェクトの設定からコピーできるやつです)

<script src="https://www.gstatic.com/firebasejs/5.9.1/firebase.js"></script>
<script> // Initialize Firebase var config = { apiKey: hoge, authDomain: hoge, databaseURL: hoge, projectId: hoge, storageBucket: hoge, messagingSenderId: hoge }; firebase.initializeApp(config); </script>

でもって、データベースを参照する場合はこんな感じらしい

const database = firebase.database();

そんで、これでノードを指定するのね

const ref = database.ref('todo');

とりあえず、ここまでで準備は完了!まずは登録を。
今回、登録したい項目として、

  • タイトル
  • 内容
  • 期限
  • 担当者

が入力できる適当なフォームを作りました。
こんな。

<div>
    <div>
        <input type="text" id="title" placeholder="タイトル(必須)">
    </div>
    <div>
        <textarea id="content" placeholder="内容(任意)"></textarea>
    </div>
    <div>
        <input type="text" id="end_date" placeholder="タスクの期限を設定">
    </div>
    <div>
        <select id="person">
            <option value="未指定">担当者を選んでください</option>
            <option value="おれ">おれ</option>
            <option value="あいつ">あいつ</option>
        </select>
    </div>
    <div>
        <button id="post">登録する</button>
    </div>
</div>

divdivしちゃってるのは、あとで何かスタイルつけようかと思ってるだけ。
ブラウザでアクセスしてみましょう。

で、登録アクションを作ります。
内部ツールなのでタイトルだけ処理側で必須にして、弾かれてもエラーメッセージもなんもなし!
そう、これを漢(をとこ)ツールと勝手に呼ぶ。

// 登録処理
const postAction = () => {
    const title = $("#title").val();
    const content = $("#content").val();
    const end_date = $("#end_date").val();
    const person = $("#person").val();
    if(title && title !== "") {
        ref.push({
            title: title,
            content: content,
            person: person,
            end_date: end_date,
            date: new Date().getTime()
        });
    }
    // とりあえず登録終わったら空にしとく
    $("#title").val("");
    $("#content").val("");
    $("#end_date").val("");
    $("#person").val("未指定");
};

// 登録時のアクション
$('#post').click(() => postAction());

そう、気づいた人もいるだろう。
漢ツールはエスケープなんてものもしないのです。

NOエスケープ NOバリデーション!

これぞ漢(をとこ)の浪漫!
(お仕事ではちゃんとやりますので安心してくださいね)

で、指定したノードにpushってやるだけで子ノードが追加できるんだと。
ほんとかね? 実際に登録してみる。

こんな感じで、適当に入力して「ポチっ」と。

Firebaseの管理画面で確認すると、、
おおおお!!いけてますわー。(ちょっと感動)
どんどんいきましょ、次へ。

一覧表示してみる

// 初期表示と登録後のコールバック
ref.on("child_added", (snapshot) => {
    dispTodo({
        id: snapshot.key,
        value: snapshot.val()
    });
});

onの第一引数にリスナーっての指定してあげると、
リスナーに対応したイベントがトリガーとなって、この部分がコールバックされて動くみたい。
何言ってるかわからんって?
とりあえず、公式を以下に引用しておくので各自読み解いてくださいな。

value 特定のデータベース パスにあるコンテンツの静的なスナップショットを、読み取りイベントのときに存在していたとおりに読み取るために使用します。これは、初期データで 1 回トリガーされます。さらに、データが変更されると、そのたびに再びトリガーされます。イベントのコールバックには、その場所にあるすべてのデータ(子のデータも含む)を含んでいるスナップショットが渡されます。上記のコード例で、value はアプリ内のすべてのブログ投稿を返しています。新しいブログ投稿が追加されるたびに、コールバック関数がすべての投稿を返します。
child_added 通常、データベースからアイテムのリストを取得するために使用します。その場所にあるコンテンツ全体を返す value とは異なり、child_added は既存の子ごとに 1 回トリガーされます。さらに、指定されたパスに新しい子が追加されると、そのたびに再びトリガーされます。イベント コールバックには、新しい子のデータを含んでいるスナップショットが渡されます。並べ替え目的のため、前の子のキーを含んでいる 2 番目の引数も渡されます。
child_changed 子ノードが修正されると、そのたびに child_changed イベントがトリガーされます。この修正には、子ノードの子孫に対する修正も含まれます。これはアイテムのリストに対する変更に応答するために通常、child_added や child_removed と組み合わせて使用されます。イベント コールバックに渡されるスナップショットには、子の更新済みデータが含まれています。
child_removed 直接の子が削除されるとトリガーされます。これは通常、child_added や child_changed と組み合わせて使用されます。イベントのコールバックに渡されるスナップショットには、削除された子のデータが含まれています。

ひとまず、今回はchild_addedとchild_removedを使えばよさそう。
ってことでまずは初期表示と登録を行うためにchild_addedをチョイス。
dispTodoという実際にTODO表示を行う関数(後述)を呼び出す想定でこんな感じに。
さて、snapshotはなんぞや?という話になりますが、
これは上のコールバックが呼ばれた際に受信する静的スナップショットなんだそう。
なんかわからんけど、key:valueで来るのね。

じゃ、実際の表示処理を作ってみよう。

// TODOを表示する
const dispTodo = (todo) => {
    let end_date = "";
    // 期日があれば「xxまでに」という文字列を作る
    if(todo.value.end_date) {
        end_date = todo.value.end_date + "までに";
    }
    // TODO内容をリストの一番上に挿入
    const todo_html = todo.value.title + "<br />" + todo.value.content + "<br />" + todo.value.person + "が" + end_date  + "やる";
    $("#todo_list").prepend(`<div id="${todo.id}">${todo_html}</div>`);
}

とりあえず、下のような空のdivを作っておいて、そこに渡されたノードの情報をどんどん出していく作戦。

<div id="todo_list"></div>

リロード。でた。(ちょっと感動)

削除する

片付いたタスクは消すことができないと困りますね。
ということで、削除機能を作ります。
まずはボタン。『削除』ってのもなんかアレなんで『DONE』というボタンを作ってみました。

// TODOを表示する
const dispTodo = (todo) => {
    let end_date = "";
    // 期日があれば「xxまでに」という文字列を作る
    if(todo.value.end_date) {
        end_date = todo.value.end_date + "までに";
    }
    // TODO内容をリストの一番上に挿入
    const todo_html = todo.value.title + "<br />" + todo.value.content + "<br />" + todo.value.person + "が" + end_date  + "やる";
    $("#todo_list").prepend(`<div id="${todo.id}">${todo_html}<button class="done">DONE</button></div>`);
}

こんな感じで、しょーもないボタンを。

次に削除処理も作りましょ。
ボタンもどんどん動的に追加されていくので、documentのonでセレクタを指定してやる。
イベントの犯人を捕まえて、その親divのid(※)を取ってきて、削除リクエストを投げる、というプレイ。
※各TODOを挟んでいる<div id="${todo.id}">

// 削除処理
$(document).on('click', '.done', (event) => {
    const id = $(event.target).closest('div').attr('id');
    firebase.database().ref('todo/' + id).remove();
});

/keyで目的のノードを操れるようだ。
※登録時にkeyは指定していないが、Firebase側でユニークIDが勝手に付与される、というのはどこかで知りました。という前提ですw

次に削除時のコールバック

/
// 削除
ref.on("child_removed", (snapshot) => {
    $("#"+snapshot.key).remove();
});

出ました、リスナー。
迷わず「child_removed」でコールバックされることを期待しつつ、受け取ったkeyに対応するTODOのdivごと葬る。

よし、削除してみましょ!
ポチっとな!

できた。(あっさり)

さてさて、ほんとに同期とれてるの?
同期されてなければただのしょーもないHelloWorldに毛の生えたWebシステムもどきだし。

これ、複数ブラウザ開いて試してみてください。
どっちかで登録すると、もう一方に出現。
削除すると、もう一方からも消滅。
たいしたことしてないからこその、大感動がここにあるっ!

あとはちょいちょいとスタイルなんかつけちゃって、こんな感じの仕上がり♪

それにしてもHTML一枚、サーバレスでこんなことできる時代になったんですね。
おじさん、困っちゃう。

じゃ、またね!

2016/09/19Database

PostgreSQLで階層マスタを扱う

とある案件で階層マスタを扱うことがありました。

DBがOracleの場合だと、「START WITH 〜 CONNECT BY PRIOR 〜」という、階層問い合わせの構文があるのですが、PostgreSQLだと階層問い合わせが出来るのか知らなかったので調べてみました。
(なお、MySQLでは使えません)

1. データを準備する

以下の様な階層構造を考えます。

レベル0 レベル1 レベル2
勘定科目
資産科目
現金
預金
負債科目
借入金
預り金
資本科目
資本金
利益剰余金


テーブル定義を以下の通りとします。

列名 内容
id int 主キー
code varchar(20) 科目コード
name varchar(100) 科目名
parent_id int 親科目の主キー (外部キー)


DDLは以下の通りです。

CREATE TABLE kamoku (
  id int primary key,
  code varchar(20),
  name varchar(100),
  parent_id int references kamoku(id)
);

INSERT文は以下になります。

INSERT INTO kamoku (id, code, name, parent_id) VALUES (1, 'A', '勘定科目', NULL);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (2, 'A01', '資産科目', 1);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (3, 'A02', '負債科目', 1);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (4, 'A03', '資本科目', 1);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (5, 'A0101', '現金', 2);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (6, 'A0102', '預金', 2);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (7, 'A0201', '借入金', 3);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (8, 'A0202', '預り金', 3);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (9, 'A0301', '資本金', 4);
INSERT INTO kamoku (id, code, name, parent_id) VALUES (10, 'A0302', '利益剰余金', 4);

こうして作ったデータをSELECTすると、以下の結果になります。

hoge=# select * from kamoku;

id code name parent_id
1 A 勘定科目
2 A01 資産科目 1
3 A02 負債科目 1
4 A03 資本科目 1
5 A0101 現金 2
6 A0102 預金 2
7 A0201 借入金 3
8 A0202 預り金 3
9 A0301 資本金 4
10 A0302 利益剰余金 4

(10 rows)

2. WITH RECURSIVE句による階層問い合わせ

PostgreSQLにはWITH句というサブクエリを定義する句が使えます。
ここに、RECURSIVEを付けると、再帰的なサブクエリを書くことが出来ます。

具体的には、以下の様に書きます。

WITH RECURSIVE child (level, id, code, name, parent_id) AS (
  SELECT 0, kamoku.id, kamoku.code, kamoku.name, kamoku.parent_id FROM kamoku WHERE kamoku.id = 1
UNION ALL
  SELECT
    child.level + 1,
    kamoku.id,
    kamoku.code,
    kamoku.name,
    kamoku.parent_id
  FROM
    kamoku, child
  WHERE
    kamoku.parent_id = child.id)
SELECT level, id, code, name, parent_id FROM child ORDER BY code;

結果は以下の通りです。

level id code name parent_id
0 1 A 勘定科目
1 2 A01 資産科目 1
2 5 A0101 現金 2
2 6 A0102 預金 2
1 3 A02    負債科目 1
2 7 A0201 借入金 3
2 8 A0202 預り金 3
1 4 A03    資本科目 1
2 9 A0301 資本金 4
2 10 A0302 利益剰余金 4

(10 rows)

2行目のSELECT文のWHERE句の条件が、開始のidになります。
開始のidを指定すれば、途中から取得することも出来ます。

WITH RECURSIVE child (level, id, code, name, parent_id) AS (
  SELECT 0, kamoku.id, kamoku.code, kamoku.name, kamoku.parent_id FROM kamoku WHERE kamoku.id = 2
UNION ALL
  SELECT
    child.level + 1,
    kamoku.id,
    kamoku.code,
    kamoku.name,
    kamoku.parent_id
  FROM
    kamoku, child
  WHERE
    kamoku.parent_id = child.id)
SELECT level, id, code, name, parent_id FROM child ORDER BY code;

level id code name parent_id
0 2 A01 資産科目 1
1 5 A0101 現金 2
1 6 A0102 預金 2

(3 rows)

逆順(子→親)も出来ます。

WITH RECURSIVE child (level, id, code, name, parent_id) AS (
  SELECT 0, kamoku.id, kamoku.code, kamoku.name, kamoku.parent_id FROM kamoku WHERE kamoku.id = 10
UNION ALL
  SELECT
    child.level + 1,
    kamoku.id,
    kamoku.code,
    kamoku.name,
    kamoku.parent_id
  FROM
    kamoku, child
  WHERE
    child.parent_id = kamoku.id)
SELECT level, id, code, name, parent_id FROM child ORDER BY code DESC;

level id code name parent_id
0 10 A0302 利益剰余金 4
1 4 A03 資本科目 1
2 1 A 勘定科目

(3 rows)

3. まとめと落とし穴

今回の階層問い合わせを再帰的に行うクエリだと、1クエリで出来て速度も速いので良い方法だと思います。

ですが、各階層(level)毎に取得している為、最後に並び替えをしないと、ツリー状に並ぶ結果が返ってきません。
今回は簡単にするために、科目コード(code)にルールを作って並ぶようにしています。

実際に使う時は、もう一工夫したいところです。

2016/09/19Database
1