MySQLは、統計クエリを最適化するために、sum、case、whenを巧みに使用します。

私は最近、会社で統計レポートの開発に関わるプロジェクトに取り組んでいました。データの量が比較的多かったため、以前書いたクエリステートメントでは 500,000 個のデータをクエリするのに約 10 秒かかりました。その後、上司の指導を受けて、sum、case...when... を使用して SQL を書き直すと、パフォーマンスがすぐに 1 秒に改善されました。問題と解決策を明確かつ簡潔に説明するために、ここでは需要モデルを簡略化します。

データベースには、次の構造を持つ注文テーブル (簡略化された中間テーブル) が作成されました。

テーブル `statistic_order` を作成します (
 `oid` bigint(20) NOT NULL、
 `o_source` varchar(25) デフォルト NULL コメント 'ソース番号',
 `o_actno` varchar(30) デフォルト NULL コメント 'アクティビティ番号',
 `o_actname` varchar(100) DEFAULT NULL COMMENT '参加アクティビティ名',
 `o_n_channel` int(2) デフォルト NULL コメント 'ショッピングモール',
 `o_clue` varchar(25) DEFAULT NULL COMMENT '手がかりカテゴリ',
 `o_star_level` varchar(25) DEFAULT NULL COMMENT '星評価の注文',
 `o_saledep` varchar(30) デフォルト NULL コメント 'マーケティング部門',
 `o_style` varchar(30) デフォルト NULL コメント '車種',
 `o_status` int(2) デフォルト NULL コメント '注文ステータス',
 `syctime_day` varchar(15) DEFAULT NULL COMMENT '日付を日ごとにフォーマットする',
 主キー (`oid`)
) エンジン=InnoDB デフォルト文字セット=utf8

プロジェクトの要件は次のとおりです。

一定期間内の各日のソース番号の数をカウントします。ソース番号はデータテーブルの o_source フィールドに対応し、フィールド値は CDE、SDE、PDE、CSE、SSE のいずれかになります。

ソース分類のフローは時間とともに変化します

最初は、次の SQL を書きました。

S.syctime_dayを選択し、
 (SS.syctime_day = S.syctime_day かつ SS.o_source = 'CDE' の場合、statistic_order SS から count(*) を 'CDE' として選択します。
 (SS.syctime_day = S.syctime_day かつ SS.o_source = 'CDE' の場合、statistic_order SS から count(*) を 'SDE' として選択します。
 (SS.syctime_day = S.syctime_day かつ SS.o_source = 'CDE' の場合、statistic_order SS から count(*) を 'PDE' として選択します。
 (SS.syctime_day = S.syctime_day かつ SS.o_source = 'CDE' の場合、statistic_order SS から count(*) を 'CSE' として選択します。
 (SS.syctime_day = S.syctime_day かつ SS.o_source = 'CDE' の場合、statistic_order SS から count(*) を選択) を 'SSE' として選択します。
 statistic_order S から、S.syctime_day > '2016-05-01' かつ S.syctime_day < '2016-08-01' 
 GROUP BY S.syctime_day は S.syctime_day の昇順で順序付けされます。

この書き込み方法はサブクエリを使用します。インデックスを追加せずに、この SQL 文を 550,000 のデータに対して実行しました。ワークベンチで待機するのに 10 分近くかかり、最終的に接続中断が報告されました。Explain インタープリタを通じて、SQL 実行プランが次のようになっていることがわかります。

各クエリは完全なテーブルスキャンを実行します。5 つのサブクエリ DEPENDENT SUBQUERY は、外部クエリに依存していることを示します。このクエリメカニズムは、最初に外部クエリを実行して、グループ化後の日付結果を取得し、次にサブクエリが対応する日付の CDE、SDE などの数をクエリします。その効率は想像に難くありません。

o_source と syctime_day にインデックスを追加すると、効率が大幅に向上し、クエリ結果が約 5 秒で取得されます。

実行プランを見ると、スキャンされる行数が大幅に削減され、テーブル全体のスキャンが実行されなくなったことがわかります。

これは明らかに十分な速度ではありません。データの量が数百万に達すると、クエリ速度は間違いなく耐えられないものになります。 Java のリストコレクションをトラバースし、特定の条件に遭遇したときに 1 回カウントするのと同様に、1 回のトラバースですべての結果を直接クエリする方法があるかどうか疑問に思っていました。これにより、完全なテーブルスキャンを実行して結果セット、結果インデックスをクエリすることができ、効率が非常に高くなるはずです。上司の指導の下、sum 集計関数と case...when...then... の「奇妙な」使用法を使用して、この問題を効果的に解決しました。
具体的なSQLは次のとおりです。

 S.syctime_dayを選択し、
 sum(case when S.o_source = 'CDE' then 1 else 0 end) を 'CDE' として計算します。
 sum(case when S.o_source = 'SDE' then 1 else 0 end) を 'SDE' として計算します。
 sum(case when S.o_source = 'PDE' then 1 else 0 end) を 'PDE' として計算します。
 sum(case when S.o_source = 'CSE' then 1 else 0 end) を 'CSE' として計算します。
 sum(case when S.o_source = 'SSE' then 1 else 0 end) を 'SSE' として計算します。
 statistic_order S から、S.syctime_day > '2015-05-01' かつ S.syctime_day < '2016-08-01' 
 GROUP BY S.syctime_day は S.syctime_day の昇順で順序付けされます。

MySQL での case...when...then の使い方についてはあまり説明しません。この SQL は簡単に理解できます。まず、レコードを 1 つずつ走査し、group by で日付を分類し、sum 集計関数で特定の日付の値を合計します。重要な点は、case...when...then が合計に条件を巧みに追加していることです。o_source = 'CDE' の場合、カウントは 1、それ以外の場合は 0 です。o_source = 'SDE' の場合...

このステートメントの実行には 1 秒強しかかかりませんでした。これは、500,000 を超えるデータポイントに対してこのディメンションの統計を実行するのに最適です。

実行プランを見ると、スキャンされる行数は増えているものの、フルテーブルスキャンは 1 回のみ実行され、SIMPLE クエリであるため、実行効率は当然高いことがわかります。

この問題に対するより良い解決策やアイデアがあれば、メッセージを残してください。

要約する

これで、MySQL で sum、case、when を使用して統計クエリを最適化する方法についての説明は終わりです。MySQL での統計クエリの最適化の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: