PostgreSQL の別のテーブルに存在しない、あるテーブルの IP アドレスを見つける方法

この記事では、PostgreSQL データベース内の login_log テーブルには存在するが、ip_location テーブルには存在しない IP アドレスを効率的に識別する方法を示します。 いくつかのアプローチが示されており、それぞれに独自の長所と短所があります。

欠落している IP アドレスの特定

中心的な問題は、login_log (IP アドレスによるログイン試行を含む) と ip_location (既知の IP アドレスのセットを含む) という 2 つのテーブル間の不一致を見つけることです。目的は、login_log にログインしている、 に記録されていない の IP アドレスを抽出することです。ip_location

最適なクエリ方法

いくつかの SQL テクニックでこれを実現できます。最も効率的で一般的に使用されるものは次のとおりです:

1. サブクエリ:NOT EXISTS 通常、PostgreSQL で最も速いメソッドです。

SELECT ip
FROM   login_log l
WHERE  NOT EXISTS (
    SELECT 1 -- SELECT list is irrelevant, 1 is efficient
    FROM   ip_location i
    WHERE  i.ip = l.ip
);

このクエリは、

内の各 IP に対して、対応する IP が ip_location に存在するかどうかを効率的にチェックします。 サブクエリ内の login_log はパフォーマンスを最適化します。SELECT 1

2. と LEFT JOIN:IS NULL 簡潔で、多くの場合高速な代替手段。

SELECT l.ip
FROM   login_log l
LEFT   JOIN ip_location i USING (ip) -- Equivalent to ON i.ip = l.ip
WHERE  i.ip IS NULL;

これは左結合を実行し、

からのすべての行を保持します。 login_log にない IP アドレスは ip_location 列に NULL を持ち、i.ip 句によってフィルターされます。WHERE

3. :EXCEPT ALL コンパクトなメソッドですが、複雑なクエリでは効率が低くなる可能性があります。

SELECT ip
FROM   login_log
EXCEPT ALL -- "ALL" preserves duplicates, potentially faster
SELECT ip
FROM   ip_location;

この集合演算は、2 つの IP アドレスのセット間の差異を直接検出します。

は高速で重複 IP を処理できるため、推奨されます。EXCEPT ALL

4. (注意して使用してください):NOT IN は機能しますが、特に大規模なデータセットの場合は遅くなる可能性があります。 一般に、以前の方法よりも効率が低くなります。NOT IN

SELECT ip
FROM   login_log
WHERE  ip NOT IN (
    SELECT DISTINCT ip -- DISTINCT is optional but can improve performance slightly
    FROM   ip_location
);

このアプローチでは、IP が

の IP リストに 存在しないかどうかを直接チェックします。ただし、データセットが大きくなると、パフォーマンスが大幅に低下する可能性があります。ip_location

さらに読む:

これらの手法と関連する PostgreSQL 最適化戦略の詳細については、次のリソースを参照してください。

• EXISTS サブクエリで読みやすいものは何ですか?
• 結合が存在しないレコードを検索
• 2 番目の列の値が最初の列に存在しない行を選択します

以上がPostgreSQL の別のテーブルに存在しない、あるテーブルの IP アドレスを見つける方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。