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

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • SQL Server での判断文 (IF ELSE/CASE WHEN) の使用例
  • エラーが発生した場合のMybatisケースの問題を解決する
  • Oracleはデコード関数またはCASE-WHENを使用してカスタムソートを実装します。
  • MySQLの場合の使用例分析
  • この記事ではSQL CASE WHENの使い方を詳しく説明します

<<:  プロトタイプとプロトタイプチェーン プロトタイプとプロトタイプの詳細

>>:  10分で始めるCSS3アニメーション

推薦する

vue2.x の徹底研究 - h 関数の説明

目次解決、要約: vue プロジェクト。 .vue ファイルのテンプレート内に記述されたコードは、w...

MySQL 8.0.17 のインストールと使用方法のチュートリアル図

前面に書かれた過去および現在のプロジェクトで最も一般的に使用されているリレーショナル データベースは...

シェル スクリプトを使用してワンクリックで MySQL 5.7.29 をインストールする方法

この記事は51CTOブログの著者wjw555の作品を参照しています。スクリプトの内容: vim イン...

MySQL の隠し列の詳細表示

目次1. 主キーが存在する2. 主キーはないが、一意のインデックスが存在する3. 共同主キーまたは共...

WebpackはCSSファイルを読み込み、その設定方法

webpackはCSSファイルとその設定をロードします複数の CSS ファイルを作成した後、HTML...

MySQL インフラストラクチャ チュートリアル: クエリ ステートメント実行プロセスの詳細な説明

序文私は以前から、SQL 文がどのように実行され、どのような順序で実行されるのかを知りたいと思ってい...

CSS3 カスタムスクロールバースタイル::webkit-scrollbar サンプルコード詳細説明

Windows のデフォルトのスクロール バー スタイルは見苦しく、プロジェクト内でスクロール バー...

フローティングメニューを実装するjQueryプラグイン

毎日jQueryプラグインを学ぶ - フローティングメニュー、参考までに、具体的な内容は次のとおりで...

MYSQL スロークエリとログ設定とテスト

1. はじめにスロークエリログを有効にすると、MySQL は指定された時間を超えるクエリステートメン...

柔軟で安定した高品質の HTML および CSS コード標準を作成するためのガイド

黄金律常に同じコーディング標準セットに従ってください。同じプロジェクトに何人の人が関わっているかに関...

Centos7 構成 fastdfs および nginx 分散ファイル ストレージ システムの実装プロセス分析

1. libfastcommon-1.0.43 をインストールします。インストール パッケージは h...

Nginx 1つのドメイン名で複数のプロジェクトにアクセスする方法の例

背景最近、複数のプロジェクトを展開する際に、1 つのドメイン名で複数のプロジェクトにアクセスする方法...

Linuxプロセス通信におけるFIFOの実装

FIFO通信(先入れ先出し)関連のないプロセス間の通信を可能にする FIFO 名前付きパイプ。パイプ...

Linux オペレーティング システムで ssh/sftp を構成して権限を設定する方法

FTP と比較すると、SSH ベースの sftp サービスは、セキュリティが優れており (非プレーン...

MySQL における 8 つの一般的な SQL 使用例

序文MySQL は、2016 年もデータベースの人気において力強い成長傾向を維持し続けました。 My...