PostgreSQL의 다른 테이블에서 누락된 IP 주소를 한 테이블에서 찾는 방법은 무엇입니까?

이 문서에서는 PostgreSQL 데이터베이스 내의 login_log 테이블에 있지만 ip_location 테이블에는 누락된 IP 주소를 효율적으로 식별하는 방법을 보여줍니다. 여러 가지 접근 방식이 제시되며 각각 고유한 장점과 단점이 있습니다.

누락된 IP 주소 확인

핵심 문제는 login_log(IP 주소를 사용한 로그인 시도 포함) 및 ip_location(알려진 IP 주소 집합 포함)이라는 두 테이블 간의 불일치를 찾는 것입니다. 목표는 login_log에 기록되지 않은ip_location로그인된 IP 주소를 추출하는 것입니다.

최적의 쿼리 방법

여러 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_location의 각 IP에 대해 login_log에 해당 IP가 있는지 효율적으로 확인합니다. 하위 쿼리 내의 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_location에서 누락된 IP 주소는 NULL 열에 i.ip이 있으며, 이는 WHERE 절

으로 필터링됩니다.

3. EXCEPT ALL: 간단한 방법이지만 복잡한 쿼리에는 효율성이 떨어질 수 있습니다.

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

이 설정 작업은 두 IP 주소 집합 간의 차이점을 직접 찾습니다. EXCEPT ALL 속도가 더 빠르고 중복 IP를 처리하므로 바람직합니다.

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 하위 쿼리에서 읽기 쉬운 것은 무엇입니까?
• join이 존재하지 않는 레코드 찾기
• 두 번째 열의 값이 첫 번째 열에 없는 행 선택

위 내용은 PostgreSQL의 다른 테이블에서 누락된 IP 주소를 한 테이블에서 찾는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!