[sql] 인덱스의 열 순서는 얼마나 중요합니까?

Remus에 따르면 워크로드에 따라 다릅니다.

그래도 수용 된 답변의 오도 된 측면을 다루고 싶습니다.

인덱스의 모든 열에서 동등 검색을 수행하는 쿼리의 경우 큰 차이가 없습니다.

아래는 두 개의 테이블을 만들고 동일한 데이터로 채 웁니다. 유일한 차이점은 하나의 키는 가장 선택적인 순서에서 가장 덜 선택적인 순서이고 다른 하나는 반대 순서입니다.

CREATE TABLE Table1(MostSelective char(800), SecondMost TINYINT, Least  CHAR(1), Filler CHAR(4000) null);
CREATE TABLE Table2(MostSelective char(800), SecondMost TINYINT, Least  CHAR(1), Filler CHAR(4000) null);

CREATE NONCLUSTERED INDEX MyINDX on Table1(MostSelective,SecondMost,Least);
CREATE NONCLUSTERED INDEX MyINDX2 on Table2(Least,SecondMost,MostSelective);

INSERT INTO Table1 (MostSelective, SecondMost, Least)
output inserted.* into Table2
SELECT TOP 26 REPLICATE(CHAR(number + 65),800), number/5, '~'
FROM master..spt_values
WHERE type = 'P' AND number >= 0
ORDER BY number;

이제 두 테이블 모두에 대해 쿼리를 수행합니다 …

SELECT *
FROM   Table1
WHERE  MostSelective = REPLICATE('P', 800)
       AND SecondMost = 3
       AND Least = '~';

SELECT *
FROM   Table2
WHERE  MostSelective = REPLICATE('P', 800)
       AND SecondMost = 3
       AND Least = '~'; 

… 둘 다 지수 벌금을 사용하며 모두 동일한 비용이 부과됩니다.

허용 된 답변의 ASCII 기술은 실제로 색인이 구성되는 방식이 아닙니다. Table1의 인덱스 페이지는 아래와 같습니다 (이미지를 클릭하면 전체 크기로 열림).

인덱스 페이지에는 전체 키를 포함하는 행이 포함되어 있습니다 (이 경우 인덱스가 고유 한 것으로 선언되지 않았지만 이에 대한 자세한 정보는 여기서 무시할 수 있으므로 행 식별자에 추가 된 추가 키 열 이 있습니다 ).

위의 쿼리에서 SQL Server는 열의 선택성에 신경 쓰지 않습니다. 이 루트 페이지와 것을 발견의 이진 검색 수행 키 (PPP...,3,~ ) 입니다 >=(JJJ...,1,~ )및 < (SSS...,3,~ )그래서 페이지를 읽어야합니다 1:118. 그런 다음 해당 페이지에서 주요 항목을 이진 검색하고 아래로 이동할 리프 페이지를 찾습니다.

선택성 순서로 색인을 변경해도 이진 검색에서 예상되는 키 비교 수 또는 색인 검색을 수행하기 위해 탐색해야하는 페이지 수에는 영향을 미치지 않습니다. 기껏해야 키 비교 속도 가 약간 빨라질 수 있습니다.

때로는 가장 선택적인 인덱스를 먼저 주문하면 작업 부하의 다른 쿼리에 적합합니다.

예를 들어 작업 부하에 다음 두 가지 형식의 쿼리가 모두 포함되어있는 경우

SELECT * ... WHERE  MostSelective = 'P'

SELECT * ...WHERE Least = '~'

위의 색인 중 하나를 다루지 않습니다. MostSelective검색 및 조회 기능을 사용하여 계획을 세우기에 충분히 선택적이지만 쿼리에 Least대해서는 그렇지 않습니다.

그러나이 시나리오 (복합 인덱스의 선행 열 서브 세트에서 인덱스 탐색을 다루지 않음)는 인덱스가 도움이 될 수있는 쿼리 클래스 중 하나 일뿐입니다. 실제로 MostSelective단독으로 검색하지 않고 MostSelective, SecondMost항상 세 열 모두의 조합으로 검색하는 경우이 이론적 인 장점은 쓸모가 없습니다.

반대로 다음과 같은 쿼리

SELECT MostSelective,
       SecondMost,
       Least
FROM   Table2
WHERE  Least = '~'
ORDER  BY SecondMost,
          MostSelective 

쿼리를 다루고 탐색을 지원하고 원하는 순서로 행을 반환하여 부팅 할 수 있도록 일반적으로 처방 된 순서의 역순을 가하면 도움이됩니다.

이 조언의 자주 반복되는 부분이다 그러나 대부분에 그것의 잠재적 인 혜택에 대한 경험적 그래서 다른 쿼리 – 그리고 실제로보고를 대신 할 수 없습니다 당신의 작업.

답변

WHERE deleted = 0  AND  the_datetime > NOW() - INTERVAL 7 DAY
INDEX(deleted, the_datetime)