SQL WHERE ID(id1, id2, ..., idn)

SQL WHERE ID(id1, id2, ..., idn)

대량의 ID 목록을 검색하려면 쿼리를 작성해야 합니다.

많은 백엔드(MySQL, Firebird, SQL Server, Oracle, PostgreSQL...)를 지원하므로 표준 SQL을 작성해야 합니다.

ID 집합의 크기는 클 수 있으며 쿼리는 프로그래밍 방식으로 생성됩니다.그렇다면, 가장 좋은 접근법은 무엇일까요?

IN을 사용하여 조회 작성

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

여기서 제 질문은.n이 매우 크면 어떻게 됩니까?또한, 성능은 어떻습니까?

OR을 사용하여 조회 작성

SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn

이 접근법은 제한이 없다고 생각합니다만, 만약 n이 매우 크다면 성능은 어떨까요?

프로그래밍 방식 솔루션 작성:

  foreach (var id in myIdList)
  {
      var item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = " + id);
      myObjectList.Add(item);
  }

네트워크를 통해 데이터베이스 서버를 쿼리할 때 이 접근 방식에 몇 가지 문제가 발생했습니다.일반적으로 작은 쿼리를 많이 만드는 것보다 모든 결과를 검색하는 하나의 쿼리를 수행하는 것이 더 좋습니다.내가 틀릴 거 일 수도 있어.

이 문제에 대한 올바른 해결책은 무엇입니까?

옵션 1이 유일한 좋은 해결책입니다.

왜요?

• 옵션 2는 동일하지만 열 이름을 여러 번 반복합니다. 또한 SQL 엔진은 값이 고정 목록의 값 중 하나인지 여부를 즉시 확인하지 못합니다.그러나 좋은 SQL 엔진은 다음과 같은 동일한 성능을 갖도록 최적화할 수 있습니다.IN아직 가독성 문제가 있습니다만...

• 옵션 3은 성능 측면에서 매우 열악합니다.루프마다 쿼리를 전송하고 작은 쿼리로 데이터베이스를 해머합니다.또한 "값이 지정된 목록에 있는 값 중 하나"에 대한 최적화를 사용할 수 없습니다.

에드 귀네스가 제안한 것은 정말 성능 향상입니다, 저는 이런 질문이 있었습니다.

select * from table where id in (id1,id2.........long list)

내가 한 일:

DECLARE @temp table(
            ID  int
            )
insert into @temp 
select * from dbo.fnSplitter('#idlist#')

그런 다음 내부가 주 테이블과 함께 온도를 결합합니다.

select * from table inner join temp on temp.id = table.id

그리고 성능이 크게 향상되었습니다.

다른 방법은 ID 값을 포함하는 다른 테이블을 사용하는 것입니다.그러면 이 다른 테이블이 테이블에서 안쪽으로 결합되어 반환되는 행을 제한할 수 있습니다.이는 동적 SQL이 필요하지 않고(가장 좋은 경우 문제가 되는 경우) 무한히 긴 IN 절이 없다는 주요 이점을 제공합니다.

이 다른 테이블을 잘라내고 많은 행을 삽입한 다음 조인 성능을 지원하는 인덱스를 만들 수 있습니다.또한 이러한 행의 누적을 데이터 검색에서 분리하여 성능을 조정할 수 있는 더 많은 옵션을 제공할 수 있습니다.

업데이트: 임시 테이블을 사용할 수는 있지만, 꼭 사용해야 한다는 뜻은 아니었습니다.임시 데이터에 사용되는 영구 테이블은 여기서 설명하는 것 이상의 장점을 가진 일반적인 솔루션입니다.

첫 번째 옵션이 단연 최고의 옵션입니다.

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

그러나 ID 목록이 매우 방대한(예: 수백만 개) 점을 고려하면 아래와 같은 청크 크기를 고려해야 합니다.

• ID 목록을 고정된 숫자의 덩어리로 나눕니다. 예를 들어 100.
• 청크 크기는 서버의 메모리 크기에 따라 결정되어야 합니다.
• ID가 10000개라고 가정하면 10000/100 = 100개의 청크가 있습니다.
• 한 번에 청크 하나를 처리하여 선택할 데이터베이스 호출 100개를 생성합니다.

왜 덩어리로 나눠야 하나요?

당신은 당신과 같은 시나리오에서 매우 일반적인 메모리 오버플로 예외를 결코 얻지 못할 것입니다.데이터베이스 호출 수가 최적화되어 성능이 향상됩니다.

그것은 나에게 항상 매력적으로 작용했습니다.제 동료 개발자들에게도 효과가 있기를 바랍니다 :)

5억 개의 레코드가 있는 Azure SQL 테이블에서 SELECT * FROM MyTable where id in () 명령을 실행하면 대기 시간이 7분 이상이 됩니다!

대신 이렇게 하면 다음과 같은 결과가 즉시 반환됩니다.

select b.id, a.* from MyTable a
join (values (250000), (2500001), (2600000)) as b(id)
ON a.id = b.id

조인을 사용합니다.

대부분의 데이터베이스 시스템에서IN (val1, val2, …)그리고 일련의OR동일한 계획에 최적화됩니다.

세 번째 방법은 값 목록을 임시 테이블로 가져온 다음 값이 많은 경우 대부분의 시스템에서 더 효율적으로 결합하는 것입니다.

다음 기사를 읽어보시기 바랍니다.

• MySQL에서 매개 변수 전달: IN 목록 대 임시 테이블

SqlServer를 말하는 것 같은데 Oracle에서는 지정할 수 있는 IN 요소의 수가 1000개로 제한됩니다.

샘플 3은 명백한 이유 없이 데이터베이스를 수없이 업데이트하기 때문에 그 중에서 최악의 성능을 발휘합니다.

데이터를 임시 테이블에 로드한 다음 이 테이블에 참여하는 것이 가장 빠릅니다.그 후 IN은 OR 그룹보다 약간 더 빨리 작동해야 합니다.

1. 첫 번째 옵션의 경우
   임시 테이블에 ID를 추가하고 주 테이블과 내부 조인을 추가합니다.

CREATE TABLE #temp (column int)
INSERT INTO #temp (column) 
SELECT t.column1 FROM (VALUES (1),(2),(3),...(10000)) AS t(column1)

사용해 보세요.

SELECT Position_ID , Position_Name
FROM 
position
WHERE Position_ID IN (6 ,7 ,8)
ORDER BY Position_Name

언급URL : https://stackoverflow.com/questions/5803472/sql-where-id-in-id1-id2-idn