20201207のMySQLに関する記事は4件です。

投稿日：2020-12-07T23:58:03+09:00

クエリを見直してチューニングしてみよう！【変なSQL見える化作戦】

本記事は、サムザップ #2 AdventCalendar 2020の12/16の記事です。

この記事では、開発中のフェーズで、リリース直前のスマートフォン向けゲームを開発しているプロジェクトで実際に行ったMySQLのパフォーマンスチューニングのとある取り組み（作戦）について紹介したいと思います。

はじめに

例えば、このような雑なゲームで考えてみます。
以下のように、ユーザが3枚のカードをデッキに所持しているとします。

▼ user_card :ユーザが所持するカードテーブル（card_idはm_cardテーブルのidを指す）

id user_id card_id

123 1 1

124 1 2

125 1 3

▼ m_card :カードのマスタデータテーブル

id name hp

1 赤たぬき 60

2 青たぬき 42

3 黄たぬき 84

このとき、ユーザが所持しているカードの一覧を表示させようとすると、

user_cardテーブルから所持しているカードを取得

m_cardテーブルから所持しているカードのマスタデータを取得

となるかと思います。

1. user_cardテーブルから所持しているカードを取得

まずは、user_id=1のカードを取得します。
select * from user_card where user_id=1;
これで、このユーザが所持しているカードのcard_idが1,2,3ということがわかります。

2. cardテーブルから所持しているカードのマスタデータを取得

次に、card_idが1,2,3のマスタデータを取得します。
select * from m_card where id = 1;
select * from m_card where id = 2;
select * from m_card where id = 3;
これで、所持しているカードの情報も取得できます。

気になった

さて、ここで気になりました。
select * from m_card where id = 1;
select * from m_card where id = 2;
select * from m_card where id = 3;
これって、IN句を用いれば1クエリで書けるんじゃないかと。
select * from m_card where id in (1,2,3);
ちょっとした工夫だけで、この例だけでもクエリ数が2つ減りました。
1クエリを投げて返ってくるまでのレイテンシも意外と大きく、場合によってはms単位で時間がかかってしまいます。10msだとしても、10クエリを投げるだけで100msもかかります。もし、ユーザが100枚のカードを所持していたら1秒もかかってしまう計算です。

このように、例えばN+1問題のような、アプリ全体を重くする原因になりそうなクエリは良くない！ということで、こういうクエリを変なSQLと呼ぶこととします。

変なSQL見える化作戦

この変なSQLを撲滅させたいと思い、その取り組みを変なSQL見える化作戦としました。もちろん見える化がゴールではなく、撲滅がゴールです。今思えば作戦名も変なSQL撲滅作戦の方が良かったかもしれないですね。

具体的なソースコード等は省略します。また、この記事で紹介するプログラムやログ等の例は実際に出力されたものではなく、この記事用に書いているので、おかしなところがあるかもしれませんが、気にせず良い感じに汲み取ってください。

変なSQLの見える化

撲滅するためにも、まずはこの変なSQLを把握しないといけません。
まず、上記のユーザの所持しているカードの例だと、この所持カード一覧取得APIを叩いたときに流れるSQLをチェックすれば良いと考えました。つまり、そのゲームで用意されている各APIを叩いて、その時のSQLを解析して、変なSQLの有無を確認すれば良いということになります。

前提条件

言語はPHP、データベースにMySQLを利用しています。

APIの叩き方

まずは、APIを叩くところについてです。

今回利用したのはphpunitです。また、このチームでは、API定義を書いていて、その定義ファイルからAPIを叩いてレスポンスが問題ないことのテストコードを自動で生成して書くようにしています。そこに対して、前処理を追加したり、異常系のテストを追記したりしています。もちろん何も書かなくてもAPIを叩くだけのテストは出来上がっています。
（APIの定義は、swaggerでもオリジナルの定義ファイルでも構いません）
例えば、HTTPテスト 8.x Laravel - JSONとの完全一致を検証に書いてあるサンプルコードで例えると、
リクエストパラメータがname、レスポンスがcreatedとする/userのAPIの定義を書いておけば、そこに書いてある（以下のコード）テストをコマンド一つで生成されるようになっています。
※チーム内でのAPIのテストの生成はこれとは違った形で生成していますが、json()を用いているのは同じです。
ExampleTest.php
<?php

class ExampleTest extends TestCase
{
    /**
     * 基本的な機能テストの例
     *
     * @return void
     */
    public function testBasicExample()
    {
        $response = $this->json('POST', '/user', ['name' => 'Sally']);

        $response
            ->assertStatus(201)
            ->assertExactJson([
                'created' => true,
            ]);
    }
}
そのため、全てのAPIの定義ファイルが存在するので、基本的には全てのAPIを叩くテストはそれぞれ最低1つは書かれていることになります。（ここではそのテストによるカバレッジは無視している）

APIを叩いたときに発行されるSQLの取得方法

APIを叩いたときに流れるSQLだけを取得方法についてです。
SQLログは、言語に依存しないように、MySQLのgeneral_logを使用することにしました。

「APIを叩いたときに流れるSQLだけ」を取得するために、先程紹介したテストコードの$this->json()に注目しました。このメソッドを叩く処理は各APIを叩いていることとほぼ同じかなと思います。（全く同じではないけど、APIが叩かれたときの処理が実行されるという意味ではほぼ同じという表現）
すなわち、json()の処理の間に出力されるgeneral.logだけを見れば良いということになります。

ここでは、複雑なことは考えません。

json()が呼ばれる直前にgeneral.logの中身を削除する

json()の処理をさせる

json()が終わった後にgeneral.logの中身を取得する

以上のようにすれば、そのAPIが叩かれるときに発行されるクエリを取得することができます。

元のテストに対して、なるべく手を加えずに変なSQL見える化作戦を実施したかったので、以下のようなTestCaseを継承したクラスを作成しました。
TestCaseForSqlCheck
<?php

abstract class TestCaseForSqlCheck extends TestCase
{
    public function json($method, $uri, array $data = [], array $headers = [])
    {
        // 1. general.logの中身を削除する
        // ~~~general.logを削除する処理を書く~~~

        // 2. APIを叩く
        parent::json($method, $uri, $data, $headers);

        // 3. general.logの中身を取得する
        //~~~general.logを取得してゴニョゴニョする処理を書く~~~

    }
}
後は、変なSQL見える化作戦でSQLをチェックするときだけ、各APIのテストの継承元をこのクラスに変更するだけです。
$ git diff ExampleTest.php
- class ExampleTest extends TestCase
+ class ExampleTest extends TestCaseForSqlCheck
全APIのテストを一括で変更する必要があると思いますが、sedコマンド等を利用すれば、コマンド一つで一括置換ができるかと思います。

変なSQLの抽出

ここまでで、各APIを叩いたときに発行されるクエリログを取得することができるようになりました。（APIを叩いたときに出力されるgeneral.logが取得できた）

さて、ここで変なSQLの定義を改めて考えてみましょう。

同じようなクエリが2回以上発行されるもの（複数回投げられていたら疑っても良さそう）

update文は1クエリにまとめるとめんどくさいので、ここでは無視する（insertは楽でしょ）

同じようなクエリとはどういうことなのか？一番最初の例で見てみましょう。
select * from m_card where id = 1;
select * from m_card where id = 2;
select * from m_card where id = 3;
idが1か2か3の違いですね。ということは、PREPARE構文を利用すれば良さそうに見えます。
上記の例だと、以下のようにクエリログが吐かれるかと思います。
（わかりやすくPrepareだけにフィルタリングしています。grep Prepareみたいなイメージ。また、以下は実際に出力されたクエリログではなく、手で書いたログなので、general.logの出力フォーマットとは少し異なります）
Prepare   select * from m_card where id = ?
Prepare   select * from m_card where id = ?
Prepare   select * from m_card where id = ?
全く同じクエリが発行されていることがわかりますね！これなら判別することができます。
MySQLで例えるなら、このログに対して、select sql,count(*) from general.log group by sqlみたいなことをすれば良さそうですね！

ということで、変なSQLの抽出についての処理をまとめると以下になるかと思います。

general.logのPrepareだけをフィルタリングする（grepするイメージ）

update文は除外する

同じクエリをグルーピングしてカウントする

カウントした結果が2回以上のものを変なSQL候補とする

また、全体のphpunitを実行して各APIに対しての変なSQLを集計した後処理として、ログを集計するために、phpunitのTestRunnerの拡張のexecuteAfterLastTest()を利用することで、集計を楽にすることができました。

アウトプットイメージ

上記のような手順を行い、各APIごとの変なSQLとその出現回数をログとして出力させます。
解析結果出力例
URL: post /user
2回: select * from user where id = ?

URL: get /user
2回: select * from user where id = ?
3回: select * from m_card where id = ?

URL: /card
2回: select * from user where id = ?
3回: select * from m_card where id = ?

項目数: 5個
変なSQLが見えましたね。これで見える化は完了です！
上記の解析結果出力例は変なSQLは2種類ですが、項目数を5個として数えています。
この項目数が、サービス全体に与える影響力に比例しそうだったので、この数値を追うことにしました。select * from user where id = ?このクエリがいろんなところに影響しているということですね。
この例に関しては、この2種類の変なSQLを撲滅させれば撲滅完了ということになります。

撲滅フェーズ

さて、ここまでの手順で見える化が完了しました。
撲滅のために、以下について紹介したいと思います。

自動実行させる

結果を見えるところに出力させる

撲滅順序の決定

チームメンバーを巻き込む

実行タイミング・周期

自動実行させる

定期的に上記の見える化の処理を実行し、解析するようにします。
そのためにも、人間が手動で実行していたら、例えばその人が一回忘れたらおそらく一生再実行されることはないと思ってます。また、その人が異動や退職したりすることでも、この作戦はそこで停止してしまう可能性があります。そのためにも、実行者は人間ではなくcron等を用いて自動実行をするようにしました。

そこで、今回はGithub Actionsを利用することにしました。サーバサイドだけで環境を用意でき、また、PHPソースをgit管理したときに、同じレポジトリでその実行フローも管理することができたりと、導入がとてもしやすい印象なので、Github Actionsを選択しました。

Github Actions上のフローをざっくりと説明すると、以下のような流れになるかと思います。

環境構築（composer install等。docker-composeを使ってローカル開発環境と同じにすることで、デバッグしやすくしている）

phpunitの設定（TestRunnerのエクステンションを利用するようにphpunit.xmlの編集をしたりする）

各TestCaseの継承先を上で紹介したTestCaseForSqlCheckに変更する（sedコマンドで一発）

phpunitを実行してAPIのテストを行う

最後に解析結果を出力する

そして、Github Actionsの実行結果は以下のようになります。（スクショは自動実行ではなく、手動実行の結果ですがほぼ表示は変わらないです。）

このようにして、Github Actions上で実行・確認ができるようになりました。

結果を見えるところに出力させる

解析結果を出力するのですが、それがGithub Actionsのコンソールやどこかのスプレッドシートやファイルに出力するだけではダメです。みんなが嫌でも目に入るところに出力する必要があります。
そこで、今回はSlackに投稿するようにしました。社内でのチャット・連絡手段はSlackを使用しているので、みんなが必ず毎日何度も目にします。見ないと仕事になりませんよね。
Slackでも、それ用のチャンネルを作成して出力するのも良くないと思います。ミュートにされたらおしまいです。そのため、例えばエンジニアが普段連絡をするようなチャンネルにあえて投稿してやります。
あとは投稿されたらできるだけ毎回違うリアクションを速攻でつけると、みんなはそのリアクションが気になって解析結果を見てしまうと思います。

そのようなちょっとした工夫を積み重ねてチームにちょっとずつ浸透させていきました。

撲滅順序の決定

時間には制限があります。もしかしたら全ての変なSQLを撲滅することができないかもしれないです。
そこで、それぞれの変なSQLに対して、優先度をつけます。

優先度は以下のように分けました。

優先度レベル内容対応方針

S 状況・条件によっては3回以上のクエリが投げられる or 固定だけど多い
（n+1問題みたいなもの）リリースまでに直す

A 3〜5回だけど、どんな状況・条件でも必ず固定の回数だけ投げられる or 不要なクエリが投げられているリリースまでに直したい

B 必ず2回だけいつかは直したい

D 仕様上仕方がない or 作り上仕方がない放置する。変なSQLではない！

優先度レベルがSには、マスタデータの状態、データベース、ユーザデータによってものすごく増える可能性があるものを設定しています。
一番最初の例のように、ユーザのカードの所持枚数が100枚とかになると100クエリ投げられてしまう、のように無限ほどではないけど、どんどん増えてしまうものを最優先で撲滅しました。
select * from m_card where id = 1;
select * from m_card where id = 2;
select * from m_card where id = 3;
また、例えばフレンドになるという処理を考えたときに、フレンド上限の判定をする場合、自分のフレンド数と相手のフレンド数をチェックする必要があると思います。その時のフレンド数を取得するクエリが2回になったとしたら、それは必ず2回以内しか投げられません。3人以上のフレンド数を取得する必要はありません。でも、きっと一回で取得することも可能だと思います。そういった場合は優先度レベルをBとしています。

実際にやってみて、優先度レベルがAになるのは、処理をざっくりみただけじゃわからないけど、でもヤバそう、みたいなざっくりとした感覚で設定していました。

また、優先度をBにしたけど、直そうと思ったら「システムを作り直す」「機能自体を作り変えなきゃいけない」みたいなことが発生した場合は優先度レベルはDに下がります。
この作戦に対しての工数はそこまでかけ過ぎないことが大事だと思っています。

チームメンバーを巻き込む

変なSQLの撲滅運動は、1人でやらず、みんなでやりましょう。
みんなで実施することで、チームメンバー全員が変なSQLに対する感度が上がり、これを通してよりパフォーマンスを意識したプログラムを書けるようになると思います。
また、1人だとなかなか終わらないので、みんなで協力しましょう。

実行タイミング・周期

さて、優先度が決まったからと言って、片っ端からどんどん片付けていくのはよくありません。最初から全速力で走ると、すぐに疲れてしまいます。
そこで、今回は「1週間に1種類の変なSQLだけ撲滅させる」としました。

「1週間に1つだけなら、なんとなくできそうな感じがしませんか？」

また、毎週木曜日にチーム内のサーバ定例MTGを実施しているので、そこでその週の振り返りをするようにしていました。
そのMTGの変なSQL見える化作戦で話すこととしては、以下になります。

現状の変なSQL一覧の確認

この一週間で増えた/減った項目の確認

次の一週間でどれを撲滅させるかの決定

現状の変なSQL一覧の確認

変なSQLを一覧で可視化します。今回はこれをスプレッドシート等にまとめて管理します。実際に使用したスプレッドシートを紹介したいと思います。
（ここだけ貼り付けがアナログですね。）

スプレッドシートにした大きな理由は特にありません。とりあえず変なSQLの一覧が見えてフィルタとか色つけたりできればいいなって思ったくらいなので、今回はスプレッドシートを使用しました。
こうやってみると、このチームのシステムは変なSQLがたくさんありますが、このスクショはこれでも一部なんです。

この一週間で増えた/減った項目の確認

この一週間でどれだけ減ったかを増えたかを確認します。もちろん開発をしていれば増える可能性もあります。また、直したと思ったらいつの間にか別の変なSQLが出現している場合もあります。そういったのを確認して、認識をあわせます。

確認するために、前回の解析結果と今回の解析結果を単純にdiffを取るだけです。Slackをちょっと遡れば前回の結果が投稿されているので、ファイルは作成する必要はありますが、コマンド一発で簡単に差分の確認ができます。
$ diff 今回.txt 前回.txt
また、減っていくと気持ちが良いですよね。「今週は○項目減って、S,A,Bレベルが残り○○項目です！」っていうのが意外と楽しいんですよね。これを発表するとメンバーから拍手が聞こえたりするのでちょっと盛り上がります。
しかも1種類だけの変なSQLを撲滅させたとしても、それが複数のAPIで発行されている可能性があるので、複数項目を撲滅させることができるのです。
なので、例えば変なSQLが項目数が50項目あったとしても、50回撲滅させる必要はなく、もしかしたら実際には20種類の変なSQLしかない可能性もあるのです。

次の一週間でどれを撲滅させるかの決定

そして、MTGの最後にはこの一週間で撲滅させる変なSQLをみんなに決めてもらいます。ここがポイントです。「自分はこれをやります」って言ってしまったら、翌週のMTGまでに片付けないといけなくなりますよね。
また、周りの人がちゃんと撲滅させているのに、1人だけ撲滅してなかったら焦りますよね。そういう意味でも、ちゃんと毎週1人1種類ずつではありますが、撲滅させていきます。

決定したら、あとはその一週間のうちで気が向いたときに対応してもらっています。もちろん、開発が遅れてはいけないので、そちらを優先しつつも、なるべく撲滅してもらうようにしていました。

バッチの実行タイミング

ということで、毎週実施のサーバ定例MTGまでには、解析してその実行結果をスプレッドシートにまとめておく必要があります。
そこで、木曜日の朝に、バッチの実行設定をしておきます。
出社した頃には解析結果が出てて、それを集計してサーバ定例に挑む。そのような感じで毎週実施してました。

そのため、Github Actionsのスケジュールで朝7時に設定していました。
code_check.yml
name: 変なSQL見える化

on:
  schedule:
    #毎週木曜日の7(-2+9)時(=水曜日22時)実行するように設定
    - cron:  '0 22 * * 3'
Github Actionsを実行していると、7時にcron実行の設定をしても7時ちょうどには実行されなく、遅いときは8時直前になることもあったりするのです。
そのため、ものすごい余裕をもって早めに実行するように設定しています。

そして、撲滅へ

このようにして、毎週撲滅していきます。
実際にやってみて、今回はおよそ2ヶ月半くらいでS,A,Bレベルの変なSQLの撲滅に成功しました。

もちろん最初の方に書きましたが、phpunitのAPIテストでカバーしている処理に限りますが、これで把握している変なSQLはなくなったはずです。

最後に

この作戦は、サービス全体のパフォーマンス・チューニングの役立つ情報の1つになると思います。
これにより、1リクエストあたりのクエリが減ることで、DBの負荷も下がり、最終的にコスト削減にも繋がり、結果利益の向上にも貢献できます。

また、この作戦を実施する目的はそれだけではなく、実はチームメンバーにもパフォーマンスを気にする癖をつけてもらいたいと思って実施しました。SQLを意識することで、よりパフォーマンスの高いコードを書けるようになったらいいなと思っていました。（これはまだチームメンバーに伝えておりませんwこの記事を通して伝えることになりそうです。。）

おまけ

ここからはおまけです。上で紹介しなかった工夫をここで軽く触れておこうと思います。

どのテストによる変なSQLかの出力

各API毎に変なSQLとその回数を出力させるだけではなく、どのテストを実行したときに発行されたSQLなのかも一緒に出力するようにしました。
これをやることで、どのテストで発行されたかを調査する時間がなくなり、業務効率がちょっぴり上がります。

雑実装ですが、以下のようにどこテストから呼ばれているかをjson()内から知ることができます。
json()内の処理
$backTrace = debug_backtrace();
$functionName = $backTrace[2]['function'];
$className = $backTrace[2]['class'];
json()の中身で実際にAPIを叩いちゃう

元のjson()は擬似的にリクエストを作成してPHPの処理をさせているだけですが、実際にcurlしてしまおうという考えです。
処理するプロセスを分けることで、例えばPHP側で保持しているクエリキャッシュやstatic変数に保存している値を利用しないようにして、よりリアルな変なSQLを知ることができるようになります。

雑ではありますが、だいたいこんな感じの処理でjsonメソッドを置き換えることができました。
※nginxのコンテナを利用しているので、リクエスト先がnginxになっています。
    public function json($method, $uri, array $data = [], array $headers = [])
    {
        $content = json_encode($data, JSON_UNESCAPED_UNICODE);
        $url = 'http://nginx' . $uri;
        $header = [
            "Accept': 'application/json",
            "Content-Type: Content-Type: application/json",
            "Content-Length: " . strlen($content),
            'RealTime: ' . $realTime,
        ];
        $options = ['http' => [
            'header' => implode("\r\n", $header),
            'method' => $method,
            'content' => $content,
            'ignore_errors' => true,
        ]];
        $response = file_get_contents($url, false, stream_context_create($options));
        preg_match('/HTTP\/1\.[0|1|x] ([0-9]{3})/', $http_response_header[0], $matches);
        $statusCode = $matches[1];
        $this->response = new JsonResponse();
        $this->response->setData(json_decode($response, true));
        $this->response->setStatusCode($statusCode);
    }
カバレッジが気になる

APIのテストを実施したときのテストのカバレッジを計測して可視化することで、どの処理が網羅できてそうかがわかります。

去年のアドベントカレンダーで書いた「テストコードのカバレッジの見える化をして、テストを書く文化を作ろう。」でも紹介しましたが、phpunitで簡単にカバレッジを可視化することができます。
また、APIのテスト(tests/Controller)とロジックのテスト(tests/Logic)のディレクトリを分けておくことで、APIだけのテストを実施することも可能となります。
$ phpunit tests/Controller/
手軽に実行できるようにしておく

GithubのレポジトリのActionsのタブから、簡単に解析をするようにしています。

これは、workflowのトリガーのところに、workflow_dispatchイベントを設定することで簡単に実現することができます。
参考：ワークフローの手動実行 - GitHub Docs
code_check.yml
name: 変なSQL見える化

on:
  schedule:
    #毎週木曜日の7(-2+9)時(=水曜日22時)実行するように設定
    - cron:  '0 22 * * 3'
  workflow_dispatch:
    inputs:
      post_slack:
        description: 'post slack'
        required: false
        default: 'false'
これで、Jenkinsのジョブを実行する感覚で、ブランチを指定して解析処理を開始できるようになりました。

また、定期実行時はslackに投稿されるようになっているので、手動実行時はSlack投稿されないように、入力パラメータを設けてdefault値をfalseで設定しています。

ワークフロー内では、例えば以下のような条件式を書くことで簡単にSlack投稿の処理をさせないようにすることも可能です。
ymlcode_check.yml
        if [ "${{ github.event.inputs.post_slack }}" != "false" ]; then
          # slack投稿処理
        fi
これで、変なSQLの撲滅作業時に、ちゃんと撲滅できたかな？というチェックをすることができるようになりますね。

おまけは増え続ける

おまけでいくつか紹介しましたが、この作戦を続けていくと、どんどん工夫は増えていくと思います。
実際に、毎週開催のサーバ定例MTGのときにもチームの皆さんからフィードバックをもらって、例えば「どのテストによる変なSQLかが知りたい！」ってことで、おまけに書いてある機能を追加したりしています。
もしかしたら、この記事を投稿した翌日にも新しい機能が追加されているかもしれません。

id	user_id	card_id
123	1	1
124	1	2
125	1	3

id	name	hp
1	赤たぬき	60
2	青たぬき	42
3	黄たぬき	84

優先度レベル	内容	対応方針
S	状況・条件によっては3回以上のクエリが投げられる or 固定だけど多い（n+1問題みたいなもの）	リリースまでに直す
A	3〜5回だけど、どんな状況・条件でも必ず固定の回数だけ投げられる or 不要なクエリが投げられている	リリースまでに直したい
B	必ず2回だけ	いつかは直したい
D	仕様上仕方がない or 作り上仕方がない	放置する。変なSQLではない！

　 Tweet
Qiitaで続きを読む

投稿日：2020-12-07T17:37:10+09:00

他部署へのデータ連携のためのデータダッシュボードなるものをつくっている話

はじめに

株式会社POLという会社でエンジニアをやっている @HHajimeW です。
この記事は「POL Advent Calendar 2020」の7日目の記事です。

昨日の @takahashik0422 さん（あだ名はまーさん）からバトンを頂きました。

最近、他部署向けにプロダクトのデータを整理しているため、具体的にどんなことを行っているのか紹介したいと思います。

背景

サービス概要

まず、私が所属する株式会社POLが提供するサービスであるLabBaseを手短に紹介させてください。

このリンクがわかりやすく説明されています。

LabBaseは、学生が自分のプロフィールや研究内容をサイトに載せて、企業がそのプロフィールを読んで興味を持った学生にスカウトをするという流れの就活サイトです。ダイレクトリクルーティングとか逆求人型と呼ばれています。同様なサービスはirootsさん、OfferBoxさん、などありますが、LabBaseは理系大学院生に特化しているところが他のサービスと異なるところです。

ダイレクトリクルーティングサービスの特徴として、自社にマッチした学生に個別にアプローチすることができるという点があります。ナビサイトなどはPVや申込数などで分析することが多いと思いますが、ダイレクトリクルーティングサービスは返信率や承諾率を見て、スカウトの内容や対象学生を変更することもあるため、データ活用が重要となります。

なぜ取り組んだのか

データ活用が大きな課題とはなっていなかったのですが、弊社では上記のような問い合わせがちょくちょくあったり、9月に入社したカスタマーサクセス（以下CS）の方が「データを探すの大変だよね」みたいなことがあったため、取り組み始めました。

想定していたデータの活用方法はたくさんありますが、
例えば、企業の人事の方にサービスをご利用いただく前だと、スカウト対象となりそうな学生数がどのくらい登録してくれているのかや、自社の業種や規模が近い企業がどのくらい返信率があるのかなどをご質問いただくことがあります。これらのデータを営業チームが常に把握できるようにする必要があります。

ほかにも、サービスをご利用中でも、時期によって学生の忙しさなどが変わるため、返信率が異なることがあります。返信率がさがったりすると、時期要因なのか、スカウト文が良くないかなどを分析するためのデータが必要になることがあります。これらのデータはカスタマーサクセスチームに連携します。

抽出したデータの一部

学生登録実績

専攻別登録実績

卒業時期別スカウト実績

月次スカウト実績

専攻別返信率

業界別返信率

業界別×規模別返信率 etc

進め方

チーム体制（３人チーム）
* PdM：1人
* CS：１人
* エンジニア：１人（私）

役割
* PdM：過去に出したデータの引き継ぎとか俯瞰の視点
* エンジニア：必要なデータをRedashでとってきて、スプレッドシートに連携
* CS：CSチームやその他部署で必要とされるデータの吸い出しとスプレッドシートのデータの整理

今のところ、Redashでデータ取得して、それをスプレッドシートに連携という形で整理しています。他の企業さんがどんな感じでやっているのかも知りたいので、おすすめなどあれば、ぜひ教えて下さい！

実際のデータ

データは以下のような形で表やグラフでまとめています。実データの部分はカットして、カラム名だけにしています。理系就活の市況だったり、業界ごとの返信率は、サービス導入を考えていただいている企業さんであれば、提供することができると思いますので、ぜひお問い合わせください笑

学生登録実績

22卒スカウト実績

業界別×専攻別スカウト承諾率

他にも専攻別の登録数や、業界別のスカウト数などをすぐに確認できるようにしています。

改善された良い話

今回、データダッシュボードを作成してよかった点は大きく2点ありました。

1点目は想定通りだったのですが、問い合わせが減り、抽出したデータを解釈してCSの方々がお客様に提供できる情報が増えたことかなと思います。DBの登録状況や承諾率の現状を感覚ではなく、前よりも数値で語ることができるようになったのはプラスですし、お客様からの信頼獲得にもつながるのかなと思います。

2点目は感覚的なものでしかないのですが、CSの方々がデータの価値を実感し、活用していこうという意識が高まっている点です。これは一緒にデータダッシュボードを作成してくださったCSの方の影響が大きいと思いますが、誰かがデータを出しそれによる解釈を共有すると、自分も見てみようという動きは広まります。データ活用の最初の一歩として、ダッシュボード作成は良い施策でした。

データを共有する際に注意すべき点

実際にやってみて、以下の３点くらい課題があったので、参考にしていただけると幸いです。当然のことだとは思うのですが、意外とできてないなということもあり。

データの取得の定義を事前にすり合わせる

学生のデータを取得する時の学年の対象やスカウトデータを取得する時の対象企業に自社（POL）を含めるかどうかなどが、データを見る人の前提と異なることがあります。それを事前にすり合わせていくことは大切です。

その定義をきちんと残す

多くの人が見るダッシュボードとなるため、データの定義を全員に伝えることができるようにそれを残しておく必要があります。

メンテナンス性

登録データやスカウトデータは日々増えていくものであるため、コストが高いと更新しなくなり、更新されないデータは正しくないデータとなるため、使われなくなります。

おわりに

プロダクトチームでファネルを改善するためにデータをとっているチームもあるのですが、今回はそれとは別に、営業チームやCSチームなどのカウンタパート向けのデータ活用を行いました。今後の展望としてはメンテナンス性をあげていくことと、セールスやマーケにも使えるデータを増やしていきたいなと思います。

まだまだ始動したばかりで、これからなチームなので、ぜひご意見・アドバイスなどいただけると嬉しいです！

明日はPOLの顧問をしてくださっている松岡剛志さんが記事を書いてくださいます！

　 Tweet
Qiitaで続きを読む

投稿日：2020-12-07T14:36:21+09:00

SQLハック

SQLハックとは

たかがSQL　されどSQL 実務で通用することを狙ってのページです。

SQL

MySQLを想定しています。圧倒的に業務ではOracleだったのでOracleについての言及していることもあります。

モチベーション

喜びをもって道を切り開く。
SQL is an extremely in demand skill. Tons of jobs use SQL
NEXT STEP NEXT STEP
SQL獲得は素晴らしいスキルをあなたは手に入れるということです。

前提知識

・Progateの道場コース以外は完了しておくこと。

参考図書等

・［データベースの気持ちがわかる］SQLはじめの一歩 WEB+DB PRESS plus
https://www.amazon.co.jp/dp/B07JHJDTVX/ref=dp-kindle-redirect?_encoding=UTF8&btkr=1

・SQL 第2版ゼロからはじめるデータベース操作
https://www.amazon.co.jp/dp/B01HD5VWWO/ref=dp-kindle-redirect?_encoding=UTF8&btkr=1

・SQL for Data Analysis　Udacityで部分的に学ぶ。

コンテンツ

・用語
・シラバス
・TAKEAWAY

シラバス

select * from a_table;
SELECT id, name FROM a_table;
select * from a_table where a <> 0 AND b <> 0;
select * from a_table where no = 1 OR no = 2;
select a from b where ( or ) and (( or ) and )
　←　or より　andが強いため（）で括る必要がある。
limit
order by
name NOT LIKE 'A%' AND name LIKE '%d';
xxdate BETWEEN '2016-01-01' AND '2017-01-01'
select * from a_table where text like '%SQL';
select * from a_table where text like 'SQL%';
select * from a_table where text like '%SQL%';
select * from a_table where text like '%¥%%';
is NULL
is NOT NULL
Select a AS "コード" ,
CASE
when a = 1 then '男'
when a = 2 then '女'
else '未定義'
End as "性別" From a_table;
SELECT COUNT(*) FROM accounts;

SELECT COUNT(id) FROM accounts;
←　NULLは数えない。

TAKEAWAY

日付の型

Oracle
https://qiita.com/ponsuke0531/items/57bc035f129922b1b0de
注　日付の型がきちんとしていないとINSERTできない。できても問題が・・・

0割回避

A few accounts have 0 for total, so I divided by (total + 0.01)

GROUP BY

Any column in the SELECT statement that is not within an aggregator must be in the GROUP BY clause.

The GROUP BY always goes between WHERE and ORDER BY.

　 Tweet
Qiitaで続きを読む

投稿日：2020-12-07T10:40:25+09:00

データベースの正規化をシェアハウスを題材にして学ぶ

はじめに

みなさん、データベースをさわることって多いですか？
僕は全くです。

僕の普段の業務ではネイティブアプリの開発が主で、データベースとふれ合うことは滅多にありません。。
それでもこの記事を書こうと思ったのには理由があります。

僕は新卒2年目なのですが、弊社では新卒2年目向けの研修があります。
そこでは普段触れることが少ない技術や苦手な技術を担当してチームでサービス開発をします。

その研修を通して、データベースについてなかなか学べなかったなという反省がありました。
特に正規化~~（大学で学んだはずなのに抜けている...）~~について学ぶことを目標にしていましたが、他の作業で手をつけられずに不完全燃焼だったので何かしらの形でアウトプットして知見を深めようと心に決めました。

これはその行動の記録です。
ちなみに題材を「シェアハウス」にしたのは僕がたまたまシェアハウスに住んでいたのでイメージしやすいかなと思った、ただそれだけです。

正規化とは

リレーショナルデータベース(RDB)において、表の関連性を失わないように項目を整理して表を分離するとのこと。
正規化することで、データの更新や削除が容易に行えるようになります。

ほうほう。
じゃあ、実際にテーブルを正規化していってみよう

この記事で扱うデータ

どこぞのシェアハウス運営会社がハウス・住民・ハウスを管理するマネージャーの情報を集約したデータベースを作ってシステムを構築したい。と仮定してテーブルの設計をします。

※住民やハウス等の情報は適当なものです。

正規形の種類

正規形には
- 非正規形
- 第1正規形
- 第2正規形
- 第3正規形
- ボイスコッド正規形
- 第4正規形
- 第5正規形
があるそうです。

本記事では、一般的と言われている太字になっている正規形について見ていきます。

非正規形

早速作ってみました。

非正規形はエンジニアじゃなくても一般的にExcelとかでよく見かけるような形です。
重複する場所は結合されていてまとまって見やすいですね。

ただし非正規形はあくまで人の目で見てわかりやすい形になっているだけで、一行ずつのデータの操作を行うRDBでは扱うことができません。
そこで、正規化してRDBでも扱えるような形にしてあげます。

第1正規形

第1正規形では、繰り返し部分を除外することで「単一の値」としてRDBでも扱えるようにします。
要するに、下記の表のように結合されていた箇所を一行一行ちゃんと登録されてる状態にしましょうってやつですね。

これでDBの操作ができるようになりました。

ただ、これだけでは少し問題があります。

例えば、たろうさんがハウスBに引っ越したとします。
そうすると、ハウスIDは2（表の赤字）になります。
それにつれて、「ハウス」や「所在地」、「マネージャー」等の情報も変更してあげなければいけませんね（表の太字部分）。

また、ハウスCのオーナーさんが「うちのコンセプトを新しくしたい！新しいコンセプトは世界のゲームにする！」って言ったとします。
この場合、ハウスCのコンセプトの列を修正しないといけません。
作った表には3つのデータしかありませんが、このハウスが100人規模のシェアハウスだったら100箇所のデータを更新してあげなければいけません。
めんどくさいですよね...

じゃあ正規化してこのめんどくささを解消しましょう。

第2正規形

第1から第2正規形にするには、まず「関数従属」について知っておかなければなりません。
関数従属というのはxが決まればyがわかるといいったものです。

そして第２正規形にする条件は、部分関数従属を排除されているというものです。
はい？？

わかりにくいので、具体例で見てみましょう。
sharehousesテーブルのうち、ハウスIDがわかれば、ハウスよりも右の列の情報がわかりますが住民名や性別まではわかりません。
これがハウスよりも右の列の情報が部分的に関数従属しているということなんですね。
これを排除してあげるのが、第2正規形にするということです。

やってみたのがこちら。
residents(住民)テーブルとhouses(ハウス)テーブルに分割しました。
また、residents(住民)テーブルではハウスIDという列を追加してハウスの情報を参照できるようにしました。

これによって、冗長性がなくなりました。
さっきの第１正規形の例であった「ハウスCのコンセプトを世界のゲームにしたい！」という話が上がっても、housesテーブルの該当箇所を1箇所直すだけでよくなり、residentsテーブルではハウスIDを参照しておけば良いという状態になります。

ハウスを引っ越す場合もresidentsテーブルのハウスIDを更新すればそれで終わりです。

とても変化に柔軟になりました！

第3正規形

第２正規形で部分関数従属を排除できてスッキリできました。
しかし、まだ第２正規形のテーブルの中にはまだ冗長なものがあります。

第３正規形にするには推移的関数従属を排除することを行います。
推移的関数従属とはxが決まればyが決まり、yが決まればzが決まるというものです。

これを第２正規形のテーブルで見ると、以下のようなイメージです。
idがわかればハウスidがわかり、ハウスIDがわかればマネージャーIDがわかるという関係ですね。

これを排除してあげれば良いので、housesテーブルからマネージャーの情報を分割させます。
できた表は以下になりました。

たまたま作成したテーブルにマネージャーの情報が少ないのでしなくても良いじゃんと思ったりするかもしれませんが、マネージャーの情報が増えてくると恩恵を感じられるようになるかと思います。

修正

作成したテーブルをチームの人たちにみてもらい上司から「この表の中にデータとしてあまり持たせない方が良いものがあるよ」と言われました。

さあ、どのテーブルのどの列で、どうするべきでしょうか？

正解は以下、年齢を生年月日にするでした。

なぜ年齢にしない方が良いかというと、データベースが1年に1回誕生日を迎えた人の年齢の値を都度都度更新しなければならず、コストがかかるからです。
データベースの操作ができるとはいえ、なるべくデータを操作しない設計にするのが望ましいのですね。

また、このテーブルについて議論しているうちに自分でもこの列はおかしいと思うものを見つけました。
それは、家賃です。
確かにresidentsが支払うものであるからここに持たせるのが自然かと思っていましたが、家賃というのはハウスや部屋ごとに決まるもののため、住民テーブルに持たせるのは不適切かもしれないと考えました。

そこで住民テーブルから家賃を切り離して家賃テーブル(rents)を持たせる設計にしてみました。

これで家賃と住民、ハウスの関連性を持たせることができてスッキリしたかなと思います。

SQLの影響

作成した各テーブルに対して、SQLを作成/実行したときにどのように変化もあるのかを考えてみました。

今回は、マネージャーがまさしさんであるハウスの情報と住んでいる人の名前を知りたいといったケースでのSQLについて考えてみました。

SQL文

作成したSQL文は以下になります。※あくまで一例です。

第1正規形
SELECT id, housename, address, concept, residentname FROM sharehouse WHERE managername = 'まさし';
第2正規形
SELECT h.id, h.housename, h.address, h.concept, r.residentname FROM houses h
    LEFT JOIN residents r ON h.id = r.house_id 
    WHERE managername = 'まさし';
第3正規形
SELECT id, housename, address, concept, residentname FROM 
    (SELECT h.id, h.housename, h.address, h.concept, r.residentname, m.managername FROM houses h
        LEFT JOIN managers m ON h.manager_id = m.id
        LEFT JOIN residents r ON h.id = r.house_id) sharehouses
    WHERE managername = 'まさし';
正規化してテーブルが増えていくにつれて参照しなければいけないテーブルも増えるので、SQLが複雑になっていきます。

パフォーマンス

ローカル環境にそれぞれの正規形のデータベースとテーブルを作成し、約12万件のサンプルデータを用意して実行速度を確認してみました。

正規形速度

第1正規形 0.09 sec

第2正規形 0.38 sec

第3正規形 0.10 sec

数百件とかではまだ違いはみられませんでしたが、膨大なデータを扱うようになるとパフォーマンスに影響が現れました。

予想ではテーブルの数が多く、その分テーブル間の参照回数が多そうな第3正規形が一番遅くなるのかと思いきや第２正規形が最も遅くなる結果となりました。

ちなみに、実行計画は以下のようになりました。
第1正規形

第2正規形

第3正規形

疑問点

さすがにSQLを見るに、第２正規形のものよりも複雑なSQLになっているのにここまで早くなるのはおかしいと思い、第３正規形のサブクエリ部分のみを実行して実行時間を確認してみました。
SELECT h.id, h.housename, h.address, h.concept, r.residentname, m.managername FROM houses h
        LEFT JOIN managers m ON h.manager_id = m.id
        LEFT JOIN residents r ON h.id = r.house_id
結果は0.40secでした。
と、いうことは0.4sec + 0.10secの0.50secくらい処理に時間がかかっている？

SQLの実行時に表示される時間が実行時間のどの部分を表しているのかまで今回突き止めることができなかったので、ここについて詳しい方がいたらご教授願いたいです(;_;)

おわりに

正規化することで、テーブルの冗長性がなくなり管理しやすくなる一方でSQLのパフォーマンスにも影響が出るので、どの設計が適切かを考えるのが重要だということがわかりました。

SQLのパフォーマンス向上にはテーブル設計を見直す以外にも下記等の方法があるようです。

そもそものSQLを見直す

INDEXを作成する

キャッシュ機構を用意する

この辺りの知識も別の機会にインプットしていけたらなと思います。

今回は自分なりにテーブルを作り、職場の先輩方のレビューをもとにテーブルの設計やパフォーマンスについてを考えられて、正規形についての理解が深まったと思います。

もっとこういう設計にした方が良いとかアドバイス等いただければ幸いです。

参照

正規化とは - コトバンク

DBを正規化するということ

【関係データベースの正規化】第1正規形・第2正規形・第3正規形について紹介

データベースの基礎【正規化(非正規形を第３正規形にする)】