SQL集約関数による性能向上

この記事では、SQL集約関数がアプリケーションのパフォーマンスを大幅に向上させる簡単な方法を表す方法を学びます.主に、どのように彼らはゲーム産業のスタートアップのために開発されたデータ駆動アプリケーションに基づいて現実世界のシナリオでゲームチェンジャーだったかが表示されます.
​
今、このシナリオに深く掘り下げて、データサイエンスのSQL集約関数を無視できない理由を学びましょう.
​

シナリオ紹介

私が最近取り組んだアプリケーションは、ウェブを通してスポーツ界で先進的なデータ探査機能を提供することを目指します.特に、それは生の、そして、集められたデータの探査を許容する必要があります.データベースは不均質で非構造化されたデータのテラバイトを含むので、課題はほとんどバックエンドとデータベース側にありました.さあ、このシナリオに飛び込みましょう.
​

技術，サーバ仕様，アーキテクチャ

我々は、Kotlinでバックエンドを開発しました Spring Boot 2.5.3 フレームワークと Hibernate 5.4.32.Final ORMObject Relational Mapping ). 私たちは8 GBVPS Aを通じて Docker コンテナ管理Dokku . 最初のヒープサイズは2 GBに設定され、7 GBに制限されましたが、残りのGBのRAMをAに割り当てましたRedis -ベースのキャッシュシステム.我々は、パフォーマンスを念頭に置いてWebアプリケーションを構築しました.具体的には、それは説明された多層スプリングブートアーキテクチャに基づいているhere マルチスレッド処理が必要です.
​

データベース構造

データベースをAとして実装しましたMySQL 8 GBの2 CPU VPS上で動作するサーバ我々はバックエンドアプリケーションとデータベースを同じサーバーファームでホストしました、しかし、彼らは同じVPSを共有しません.スポーツデータは単純であるが、非常に不均一であるので、データベースは標準化されて、標準化を奨励しました.この構造は関係データベースを選んだ理由です.このように、データベースは何百ものテーブルを含みます、そして、私はそれのために完全にここに存在することができませんNDA .
幸いにも、最も問題が多いテーブルは多かれ少なかれ同じ構造を共有します.それで、ちょうど1つのテーブルを分析することは十分でなければなりません.特に、これはpositionaldataテーブルがどのように見えるかです.

CREATE TABLE `PositionalData` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `area1` double DEFAULT NULL,
    `area2` double DEFAULT NULL,
    `area3` double DEFAULT NULL,
    `area4` double DEFAULT NULL,
    `area5` double DEFAULT NULL,
...
    `area140` double DEFAULT NULL,
    `area141` double DEFAULT NULL,
    `area142` double DEFAULT NULL,
    `area143` double DEFAULT NULL,
    `area144` double DEFAULT NULL,
    `value` double DEFAULT NULL,
    `parameterId` int(11) NOT NULL,
    `gameId` int(11) NOT NULL,
    `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
    `createdBy` int(11) DEFAULT NULL,
    `updatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
    `updatedBy` int(11) DEFAULT NULL,
    `deletedAt` datetime DEFAULT NULL,
    `deletedBy` int(11) DEFAULT NULL,
    `active` tinyint(1) DEFAULT '1',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

見ることができるように、それは100以上のコラムを含みます、そして、それは4つ以上の外部IDを持ちます.平均で、これらのテーブルの各々は、少なくとも1500万の行を含んでいます.
​

性能問題

フロントエンドアプリケーションの重要な機能の一つは、ユーザーが1つ以上の季節のすべての選択されたゲームから来ているさまざまなスポーツパラメータ(例えば、パス、スロー、ブロック)の数百の集約値を分析できるようにすることです.我々は、データを取得する前に、前述の表のクエリを実行するバックエンドAPIを開発しました.このようなクエリは、10 Kから20 Kの行に戻る些細な選択ではありませんでした.それから、このデータはマルチスレッドプロセスで集められますRedis cache , そして最後にJSONでシリアル化してフロントエンドアプリケーションに戻りました.最初の瞬間から、APIがヒットを受信する(したがって、結果がREADISキャッシュで利用可能になる前に)が完了すると、ユーザーは2〜4秒の間待機する必要があります.
この遅れは受け入れられなかった.
​

パフォーマンス問題の解明

今、提案されたアプローチの欠点は何かを見ましょう.
​

ORMデータ変換ボトルネック

ほとんどの先進的なOMS抽象データベースのレベルでデータを表す方法.他の条件において、ormは問合せを実行して、データベースから所望のデータを検索して、それをそのアプリケーションレベル表現に変換することの世話をする.このデータ変換プロセスはシーンの後ろで起こりますが、それは間違いなくオーバーヘッドを表します.そのプロセスは通常性能に関しては無視できますが、それはすぐに何千もの行のためのボトルネックになることができます.
​
このスローダウンはOOを使用する場合に特に起こりますObject Oriented ) 言語.さらに、新しいクラスインスタンスを作成するには、時間とリソースがかかります.オブジェクトのサイズとヒープの使用量を制限する1つの方法は、厳密に必要な列の列だけを選択することです.このアプローチは、オブジェクト作成プロセスがメインオーバーヘッドを表すにもかかわらず、各オブジェクトを軽くします.したがって、この変換プロセスを実行するのに費やされた時間は、かなり変化しません.
​

ループ時間かかる

数千個の要素を含むオブジェクトの配列上でsumや平均のような単純な演算を行うことはパフォーマンスフリーではありません.これはデータを変換するためにORMに費やされる時間と比較しませんが、それは確かに追加のオーバーヘッドを表します.幸いにも、Javaは多くのスレッドセーフコレクションをサポートし、同時に操作を実行します.一方、オープンと管理スレッドは複雑で時間がかかるタスクです.
いくつかのSQL集約関数がパフォーマンス問題をどのように解決するのかを見てみましょう.
​

SQL集約関数とは

SQL集約関数を使用すると、いくつかの行を計算し、結果として1つの値を取得できます.それぞれのSQL言語には独自の集約関数がありますが、最も一般的な関数は以下の通りです:

count () :選択した行の数を返す

min () :最小値を抽出する

max () :最大値を抽出する

sum () : sum演算を実行する

avg () :平均演算を行う

グループBYステートメントに関連付けられている場合、それらは強力で有用なツールを表します.おかげで、最初のグループを希望のデータをグループ化することができますし、それらを活用して集計します.MySQL集約関数を調べたいなら、サポートされているすべてのものを見つけることができますhere . チェックアウトthis and this .
​

アプリケーションレベル操作のクエリへの置換

SQL集約機能が有望であるように見えたが、私たちは、彼らが行動しているのを見る前に、彼らが違いを生じることができるかどうか知りませんでした.具体的には、アプリケーション・レベル操作は、選択されたゲームの上に選ばれる各々のパラメータ上の値列および各々のareax(Xから1つの144までのカラム)の合計値を含んでいるデータ構造を生成した.簡単に次のクエリでこれを表すことができます.
​

SELECT SUM(`area1`) as `area1`,  
    SUM(`area2`) as `area2`, 
    SUM(`area3`) as `area3`,
...
    SUM(`area142`) as `area142`, 
    SUM(`area143`) as `area143`, 
    SUM(`area144`) as `area144`,
    AVG(`total`) as `total`, `parameterId`
FROM `PositionalData`
WHERE `parameterId` IN (:parameterIds) AND `gameId` IN (:gameIds)
GROUP BY `parameterId`

ご覧のように、このクエリは、データベース集約レベルで集約データを返すSQL集約関数を使用します.これは、IN文を使って目的のデータをフィルタリングしながらgameId and parameterId そしてそれに基づいてグループ化parameterId . 換言すれば、データは、季節の選択されたゲームおよび分析する所望のパラメータに基づいて、最初にフィルタリングされる.そして、結果として得られる情報をパラメータでグループ化し、SQL集約関数に集約します.
​

正しいインデックスの定義

このクエリには、Group、In、SQLの集約ステートメントが含まれますので、遅いかもしれません.この潜在的な遅さは、適切なインデックスを定義する理由はとても重要です.詳細には、最も重要でパフォーマンス効果的なインデックスは、以下のものを適用しました:
​

ALTER TABLE `PositionalData` ADD INDEX `PositionalData_parameterId_gameId` (`parameterId`, `gameId`) USING BTREE;

​
したがって、常に集約関数を使用する必要がありますか?いくつかのポジティブと否定このアプローチ.
長所

データベースレベルの集約は、アプリケーションのレベルで同じ集約ロジックを実行するよりもはるかに高速です.

group byステートメントを使用してSQL集約関数を含むクエリを使用すると、返される行の数を大幅に削減できます.詳細には、これは10 k行から解析されたパラメータの数に等しい行の数になります.その結果、ORMによって実行されるデータ変換プロセスは時間に関して無関係になります.そして、それがボトルネックであるのを防ぎます.

データベースレベルで集計すると、同じリクエストが実行されたときにデータベースキャッシュからパフォーマンスの利点を利用できます.このセットアップでは、アプリケーションレベルのキャッシュを持つことがより重要になります.

短所

SQL集約関数は、選択時に実行されます.強く型付けされたプログラミング言語を扱うとき、ORMは結果が持つタイプを知っている必要があります.そして、すべてのOrmsが簡単に定義することができない、時にはネイティブのクエリだけにSQL集約関数を制限することさえできます.この現実は、ORMによって導入された抽象化の利点を失って、彼らの使用を落胆させることを意味します.

SQL集約関数を含むデータを抽出するクエリは、常にSimple Where句を含むSELECTよりも遅くなります.それにもかかわらず、実行時間は、1秒のテンテンの順序で残っていなければならず、いずれの場合も、アプリケーションレベルで同じ操作を実行するよりもはるかに少ない.

利用可能なSQL集約演算子は通常1ダースに制限されます.

性能比較

キャッシュと同じパラメータを持たないデータ集約を含む同じAPIを呼び出すときの結果を比較しましょう.

アプリケーションレベルでの集約実行時の応答時間

データベースレベルでの集約を行うときの応答時間
​

SQL集約関数の最終的な考え方

SQL集約関数は間違いなく、データサイエンスを扱うときに次のレベルにパフォーマンスを取るための素晴らしいツールです.それらを使用して簡単かつ効果的ですが、すべてのormは完全にまたはネイティブにそれらをサポートすることはできません.いずれにせよ、どのようにそれらを利用するかを知ることはパフォーマンスを向上させるのに不可欠になるかもしれません、そして、現実世界のケーススタディを通してそれを説明することはこの記事を書いた理由です!