きっかけ

私たちの会社のBizメンバーはとても頼もしく、SQLを駆使してRedashで自らデータを分析し、日々の意思決定に活かしています。（これ、入社して非常に驚いたことの一つなのですが、すごくないですか？！) エンジニアとしては、データがビジネスを動かしている実感を持てる、とても嬉しい環境です。

先日、

「Redashで今まで見えていた数字が見えない。なんとかしてほしい……！」

という依頼をBizメンバーの方からいただきました。依頼のメッセージに書いてくださった経緯をみると、分析環境のアップデートに伴い、Redashのタイムアウト設定が以前より短く見直されることになったことが原因となったようです。実際に該当のクエリを確認すると、クエリ発行後2分後に「Timeout error」という無慈悲なメッセージとともに停止してしまいました。（タイムアウト値はその時点で2分でした）

問題のクエリ

そのクエリは、「エキスパートへのアンケート送信数から、回答率、さらにはその先の成約数まで」を一度に集計しようとするものでした。

-- 修正前のイメージ：巨大な一塊のSELECT
SELECT 
    sender_id AS _id,
    (SELECT name FROM staff_accounts WHERE id = sender_id) AS `送信者`,
    COUNT(DISTINCT questionnaire_id) AS `アンケート数`,
    COUNT(DISTINCT CASE WHEN status = 'sent' THEN request_id END) AS `送信数`,
    -- ...（中略）... 10個近いCOUNT(DISTINCT CASE WHEN...)
FROM questionnaire_detail
LEFT JOIN questionnaire ...
LEFT JOIN project_details ...
WHERE created_at BETWEEN '{{start}}' AND '{{end}}'
GROUP BY _id;

この「一度に全部JOINして、巨大な表を作ってから DISTINCT で数える」手法により、2分=120秒経っても終わらないクエリになってしまっていました。

解決策

初歩的ですが、単純に「一度に全部JOINして、巨大な表を作って」いるところを直し、「項目ごとにあらかじめ小さな集計結果を作っておき、最後にそれらを結合する」アプローチで書き換えてみました。

-- 修正後のイメージ：サブクエリで「専門家」を分けてから結合
SELECT 
    base.sender_id AS _id,
    U.name AS `送信者`,
    base.survey_count AS `アンケート数`,
    COALESCE(sent.count, 0) AS `送信数`,
    COALESCE(ans.count, 0) AS `回答数`
FROM (
    -- 1. まずは母数となるアンケート数だけを、最速で集計
    SELECT sender_id, COUNT(DISTINCT questionnaire_id) AS questionnaire_count
    FROM questionnaire_detail 
    WHERE created_at BETWEEN ... GROUP BY sender_id
) AS base
-- 2. 指標ごとに独立したサブクエリで、インデックスを効かせて集計
LEFT JOIN (
    SELECT sender_id, COUNT(id) AS count FROM questionnaire_detail WHERE ... GROUP BY sender_id
) AS sent ON base.sender_id = sent.sender_id
-- 3. 最後にユーザー情報を添えるだけ
LEFT JOIN staff_accounts U ON base.sender_id = U.id;

これをするだけで、実行時間は120秒オーバーから、4秒に改善しました。タイムアウトの壁を軽々と飛び越えてくれてホッとしました。

参考にしたドキュメント

基本を抑えつつ、こういうことでもAIに聞けるようになったのは大変良いなと感じました、、！

MySQL公式: Optimization and Indexes
- クエリ実行計画（EXPLAIN）: Using temporary（一時テーブル）が発生している箇所をサブクエリで分離し、メモリ効率を高めるヒントを得ました。
ビザスクのValueの一つである最速で解決を目指すにはAIにも聞いてみましょう、と言うことで聞いてみたところ、今回の解決策と同じ方法を提案してもらいました。

おわりに

久しぶりに純粋なSQLだけのパフォーマンス改善を業務で行って新鮮な気持ちになったのと、改めてパフォーマンスの大切さを感じられたので記事にしてみました！開発チームではありますが、運用やビジネスの現場に関わりながらお仕事できるのもありがたいなと思える依頼でした。

ビザスクではエンジニアの仲間を募集しています！少しでもビザスク開発組織にご興味を持たれた方は、ぜひ一度カジュアルにお話ししましょう！ recruit.visasq.co.jp

VISASQ Dev Blog

ビザスク開発ブログ

SQLチューニングを業務で対応して新鮮な気持ちになった話

きっかけ

問題のクエリ

解決策

参考にしたドキュメント

おわりに