🥇ある SQL 調査の物語 | プロホスター

昨年の XNUMX 月に、VWO サポートチームから興味深いバグレポートを受け取りました。大企業顧客向けの分析レポートの XNUMX つでは、読み込み時間が法外に長いと思われました。そして、これは私の責任分野であるため、私はすぐに問題の解決に集中しました。

背景

私が何について話しているのかを明確にするために、VWO について少し説明します。これは、Web サイト上でターゲットを絞ったさまざまなキャンペーンを開始できるプラットフォームです。A/B 実験の実施、訪問者とコンバージョンの追跡、販売目標到達プロセスの分析、ヒートマップの表示、訪問記録の再生などを行います。

しかし、このプラットフォームで最も重要なことはレポートです。上記の機能はすべて相互に接続されています。また、企業顧客にとって、膨大な量の情報は、それを分析形式で表示する強力なプラットフォームがなければまったく役に立ちません。

プラットフォームを使用すると、大規模なデータセットに対してランダムなクエリを作成できます。簡単な例を次に示します。

Chrome を使用したユーザー、または (ヨーロッパに居住し、iPhone を使用したユーザー) の、<日付 d1> から <日付 d2> までのページ「abc.com」のすべてのクリックを表示します。

ブール演算子に注意してください。クライアントはクエリインターフェイスでこれらを使用して、任意の複雑なクエリを作成してサンプルを取得できます。

リクエストが遅い

問題のクライアントは、直感的にすぐに機能するはずの何かを行おうとしていました。

「/jobs」を含む URL を持つページにアクセスしたユーザーのすべてのセッション レコードを表示します

このサイトには大量のトラフィックがあり、このサイトだけで XNUMX 万を超える一意の URL が保存されていました。そして、彼らは自分たちのビジネスモデルに関連した非常にシンプルな URL テンプレートを見つけたいと考えていました。

予備調査

データベース内で何が起こっているのかを見てみましょう。以下は元の遅い SQL クエリです。

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

そして、タイミングは次のとおりです。

計画時間: 1.480 ミリ秒 実行時間: 1431924.650 ミリ秒

クエリは 150 万行をクロールしました。クエリプランナーはいくつかの興味深い詳細を示しましたが、明らかなボトルネックはありませんでした。

リクエストをさらに詳しく調べてみましょう。ご覧のとおり、彼はそうします JOIN XNUMX つのテーブル:

セッション: ブラウザ、ユーザーエージェント、国などのセッション情報を表示します。
録音データ: 記録された URL、ページ、訪問時間
URLは: 非常に大きな URL の重複を避けるため、URL は別のテーブルに保存されます。

また、すべてのテーブルがすでにパーティション化されていることに注意してください。 account_id。こうすることで、XNUMX つの特に大規模なアカウントが他のアカウントに問題を引き起こす状況が排除されます。

手がかりを探しています

詳しく調べると、特定のリクエストに何か問題があることがわかります。この行を詳しく見てみる価値があります。

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

最初に考えたのは、おそらく次のような理由からです。 ILIKE これらすべての長い URL (1,4 万以上の URL があります) 独特の このアカウント用に収集された URL) のパフォーマンスが低下する可能性があります。

しかし、いいえ、それが重要ではありません!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

テンプレート検索リクエスト自体には 5 秒しかかかりません。 XNUMX 万件の一意の URL からパターンを検索することは明らかに問題ありません。

リスト上の次の容疑者は数名です JOIN。おそらく、使いすぎが速度低下の原因になっているのではないでしょうか? いつもの JOINはパフォーマンスの問題の最も明白な候補ですが、私たちのケースが典型的であるとは信じていませんでした。

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

そして、これは私たちの場合でもありませんでした。 JOINはかなり速いことが判明しました。

容疑者の範囲を絞り込む

可能な限りパフォーマンスを向上させるために、クエリの変更を開始する準備ができていました。私のチームと私は 2 つの主なアイデアを開発しました。

サブクエリ URL には EXISTS を使用します: URL のサブクエリに問題がないかをもう一度確認したいと思いました。これを実現する XNUMX つの方法は、単に次のようにすることです。 EXISTS. EXISTS 缶条件に一致する唯一の文字列が見つかるとすぐに終了するため、パフォーマンスが大幅に向上します。

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

はい、そうです。ラップされた場合のサブクエリ EXISTS、すべてが超高速になります。次の論理的な疑問は、なぜ次のようなリクエストが行われるのかということです。 JOIN-ami とサブクエリ自体は個別には速いですが、一緒にすると非常に遅くなりますか?

サブクエリを CTE に移動する : クエリ自体が高速な場合は、最初に高速な結果を単純に計算してから、それをメインクエリに提供できます。

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

しかし、それでも非常に遅かったです。

犯人を見つける

その間ずっと、一つの小さなことが私の目の前にちらつきましたが、私はそれを常に払いのけていました。しかし、他に何も残っていないので、私も彼女を見ることにしました。私が話しているのは && オペレーター。さよなら EXISTS パフォーマンスが向上しただけ && これは、低速クエリのすべてのバージョンにわたって残っている唯一の共通因子でした。

見てドキュメンテーション、それがわかります && XNUMX つの配列間で共通の要素を見つける必要がある場合に使用されます。

元のリクエストでは次のようになります。

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

つまり、URL に対してパターン検索を実行し、共通の投稿を持つすべての URL との共通部分を見つけます。ここでの「urls」は、すべての URL を含むテーブルを指すのではなく、テーブル内の「urls」列を指すため、これは少し混乱します。 recording_data.

に対する疑惑が高まる中、 &&、生成されたクエリプランでそれらの確認を見つけようとしました EXPLAIN ANALYZE (プランはすでに保存されていますが、クエリプランナーの不透明性を理解しようとするよりも、SQL を試してみる方が気が楽です)。

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

からのみフィルターが数行ありました &&。つまり、この操作は費用がかかるだけでなく、複数回実行されることになります。

条件を分離してこれをテストしました

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

このクエリは遅かったです。なぜなら JOIN-s もサブクエリも高速です。残っているのは && オペレーター。

これは単なるキー操作です。パターンを検索するには、基になる URL テーブル全体を常に検索する必要があり、常に交差部分を見つける必要があります。これらは単なる ID を参照しているため、URL レコードで直接検索することはできません。 urls.

解決への道のり

&& どちらのセットも巨大なので遅いです。交換すると動作が比較的早くなります urls на { "http://google.com/", "http://wingify.com/" }.

を使用せずにPostgresで交差を設定する方法を探し始めました。 &&、しかしあまり成功していません。

結局、私たちは問題を単独で解決することに決めました。すべてを私にください urls URL がパターンに一致する行。追加の条件がなければ、次のようになります -

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

代わりに JOIN 構文はサブクエリを使用して展開しただけです recording_data.urls 配列に条件を直接適用できるようにする WHERE.

ここで最も重要なことは、 && 指定されたエントリに一致する URL が含まれているかどうかを確認するために使用されます。少し目を細めると、この操作が配列の要素 (またはテーブルの行) を移動し、条件 (一致) が満たされたときに停止することがわかります。何か思い出しませんか？うん、 EXISTS.

それ以来 recording_data.urls サブクエリコンテキストの外側から参照できるため、これが発生した場合は、古い友人に頼ることができます。 EXISTS そしてサブクエリをそれでラップします。

すべてをまとめると、最終的に最適化されたクエリが得られます。

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

そして最終的なリードタイムは Time: 1898.717 ms お祝いの時間ですか？

そんなに早くない！まず、正しさを確認する必要があります。非常に疑問に思っていたのですが、 EXISTS ロジックを変更してより早く完了するように最適化します。明らかではないエラーがリクエストに追加されていないことを確認する必要があります。

簡単なテストを実行することでした count(*) 多数の異なるデータセットに対する低速クエリと高速クエリの両方で。次に、データの小さなサブセットについて、すべての結果が正しいことを手動で検証しました。

すべてのテストで一貫して肯定的な結果が得られました。すべてを修正しました！

学んだ教訓

この話からは、次のような多くの教訓が得られます。

クエリプランはすべてを物語るわけではありませんが、手がかりを提供することはできます。
主な容疑者が真犯人とは限らない
遅いクエリを分解してボトルネックを特定できる
すべての最適化が本質的に還元的であるわけではありません
使用 EXIST可能であれば、生産性の劇的な向上につながる可能性があります

出力

クエリ時間は約 24 分から 2 秒になり、パフォーマンスが大幅に向上しました。この記事は大々的に掲載されましたが、私たちが行ったすべての実験は 1,5 日で行われ、最適化とテストには 2 ～ XNUMX 時間かかったと推定されています。

SQL は、恐れることがなければ素晴らしい言語ですが、学習して使用するように努めてください。 SQL クエリがどのように実行されるか、データベースがクエリプランを生成する方法、インデックスがどのように機能するか、そして単純に扱うデータのサイズをよく理解することで、クエリの最適化を成功させることができます。ただし、さまざまなアプローチを試し続け、ボトルネックを見つけて問題をゆっくりと解決することも同様に重要です。

このような結果を達成する最も優れた点は、目に見える速度の向上です。以前は読み込まれなかったレポートが、ほぼ瞬時に読み込まれるようになりました。

特に感謝します 私の同志たち アディティヤ・ミシュラの命令で, アディティヤガウル и ヴァルン・マルホトラ ブレーンストーミング用と ディンカール・パンディル 最終リクエストに別れを告げる前に、最後のリクエストで重要な間違いを見つけてくれてありがとう!

出所： habr.com

ある SQL 調査の物語

背景