이것은 비용 기반 옵티마이 저의 결정입니다.

이 선택에 사용 된 추정 비용은 다른 열의 값 사이의 통계적 독립성을 가정하므로 올바르지 않습니다.

이는 짝수와 홀수가 음의 상관 관계를 갖는 행 목표 Gone Rogue에 설명 된 문제와 유사합니다 .

재현하기 쉽습니다.

CREATE TABLE dbo.animal(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    colour varchar(50) NOT NULL,
    species varchar(50) NOT NULL,
    Filler char(10) NULL
);

/*Insert 20 million rows with 1% black and 1% swan but no black swans*/
WITH T
     AS (SELECT TOP 20000000 ROW_NUMBER() OVER (ORDER BY @@SPID) AS RN
         FROM   master..spt_values v1,
                master..spt_values v2,
                master..spt_values v3)
INSERT INTO dbo.animal
            (colour,
             species)
SELECT CASE
         WHEN RN % 100 = 1 THEN 'black'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END,
       CASE
         WHEN RN % 100 = 2 THEN 'swan'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END
FROM   T

/*Create some indexes*/
CREATE NONCLUSTERED INDEX ix_species ON  dbo.animal(species);
CREATE NONCLUSTERED INDEX ix_colour ON  dbo.animal(colour);

이제 시도

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black'
       AND species LIKE 'swan' 

이것은 아래 계획에 비용이 듭니다 0.0563167.

계획은 id열의 두 인덱스 결과간에 병합 조인을 수행 할 수 있습니다. ( 병합 결합 알고리즘에 대한 자세한 내용은 여기를 참조 하십시오 ).

병합 병합에서는 두 키를 모두 입력해야합니다.

클러스터되지 않은 인덱스에 의해 정렬 (species, id)하고 (colour, id)각각 (고유하지 않은 클러스터되지 않은 인덱스는 항상 행 로케이터는 암시 적으로 키의 끝에 추가가 명시 적으로 추가하지 않은 경우). 와일드 카드가없는 쿼리는 species = 'swan'and에 대한 등호 탐색을 수행합니다 colour ='black'. 각 검색은 선행 열에서 하나의 정확한 값만 검색하므로 일치하는 행은 순서대로 정렬되므로이 id계획이 가능합니다.

쿼리 계획 연산자 는 왼쪽에서 오른쪽으로 실행됩니다 . 왼쪽 연산자가 자식에서 행을 요청 하면 자식 에서 행을 요청 합니다 (리프 노드에 도달 할 때까지). TOP(10)는 수신 한 후 반복자는 자식에서 더 이상 행을 요청 중지됩니다.

SQL Server에는 인덱스에 대한 통계가있어서 행의 1 %가 각 술어와 일치 함을 알려줍니다. 이 통계는 독립적으로 (즉, 긍정적 또는 부정적으로 상관되지 않음) 가정하여 평균적으로 첫 번째 술어와 일치하는 1,000 개의 행을 처리 한 후 두 번째 일치하는 행을 찾은 후 종료 할 수 있습니다. (위의 계획은 실제로 1,000이 아니라 987을 보여 주지만 충분히 가깝습니다).

실제로 술어가 음의 상관 관계를 갖기 때문에 실제 계획은 각 인덱스에서 200,000 개의 일치하는 행을 모두 처리해야한다는 것을 보여 주지만 조인 된 행이 0 인 조회도 실제로 필요하다는 것을 의미하기 때문에 어느 정도 완화됩니다.

와 비교

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

아래 계획에 따라 비용이 청구됩니다. 0.567943

후행 와일드 카드를 추가하면 색인 스캔이 발생했습니다. 2 천만 행 테이블을 스캔하는 경우 계획 비용은 여전히 ​​매우 낮습니다.

추가하면 querytraceon 9130더 많은 정보가 표시됩니다

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 9130)  

SQL Server가 술어와 일치하는 10 개를 찾고 TOP행 요청을 중지 할 수 있기 전에 약 100,000 개의 행만 스캔하면된다는 것을 알 수 있습니다.

다시 이것은 독립성 가정과 같이 의미가 있습니다. 10 * 100 * 100 = 100,000

마지막으로 인덱스 교차 계획을 시도하고 강제 적용하십시오.

SELECT TOP 10 *
FROM   animal WITH (INDEX(ix_species), INDEX(ix_colour))
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

이것은 3.4625의 추정 비용으로 나에게 평행 계획을 제공합니다.

여기서 주요 차이점은 colour like 'black%'술어가 이제 여러 다른 색상과 일치 할 수 있다는 것 입니다. 이는 해당 술어에 대해 일치하는 인덱스 행이 더 이상 순서대로 정렬되지 않음을 의미합니다 id.

예를 들어, 인덱스 검색 like 'black%'은 다음 행을 리턴 할 수 있습니다.

+------------+----+
|   Colour   | id |
+------------+----+
| black      | 12 |
| black      | 20 |
| black      | 23 |
| black      | 25 |
| blackberry |  1 |
| blackberry | 50 |
+------------+----+

각 색상 내에서 ID가 정렬되어 있지만 다른 색상의 ID는 그렇지 않을 수 있습니다.

결과적으로 SQL Server는 더 이상 블로킹 정렬 연산자를 추가하지 않고 병합 조인 인덱스 교차를 수행 할 수 없으며 대신 해시 조인을 수행하도록 선택합니다. 해시 조인이 빌드 입력을 차단하므로 이제 비용은 일치하는 모든 행이 첫 번째 계획에서와 같이 1,000 만 스캔해야한다고 가정하지 않고 빌드 입력에서 처리해야한다는 사실을 반영합니다.

그러나 프로브 입력은 블로킹되지 않으며 여전히 987 개의 행을 처리 한 후 프로빙을 중지 할 수 있다고 잘못 추정합니다.

(비 차단 및 반복자 차단에 대한 자세한 정보는 여기)

추가로 추정되는 행과 해시 조인의 비용이 증가하면 부분 클러스터 된 인덱스 스캔이 더 저렴 해 보입니다.

실제로 “부분”클러스터 된 인덱스 스캔은 전혀 부분적이지 않으며 계획을 비교할 때 1 억 개가 아닌 2 천만 개의 행 전체를 통과해야합니다.

TOPCI 값을 늘리면 (또는 완전히 제거 할 때) CI 스캔에서 처리해야하는 행 수에 따라 계획이 더 비싸게 보이고 인덱스 교차 계획으로 되돌아가는 팁 포인트가 발생합니다. 나를 위해 두 계획 사이의 컷오프 지점은 TOP (89)vs TOP (90)입니다.

클러스터 인덱스의 너비에 따라 달라 지므로 상당히 다를 수 있습니다.

TOPCI 스캔 제거 및 강제

SELECT *
FROM   animal WITH (INDEX = 1)
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

88.0586예제 테이블에 대해 내 컴퓨터 에서 비용이 청구됩니다 .

SQL Server에 동물원에 검은 백조가없고 100,000 행을 읽지 않고 전체 스캔을 수행해야한다는 사실을 알고 있다면이 계획은 선택되지 않았습니다.

나는에 멀티 컬럼 통계를 시도했습니다 animal(species,colour)과 animal(colour,species)과에 대한 통계를 필터링 animal (colour) where species = 'swan'그러나이 중 어느 것도 검은 백조가 존재하지 않으며 있음을 확신 도울 TOP 10스캔이 10 만 개 이상의 행을 처리해야합니다.

이는 SQL Server가 본질적으로 무언가를 검색하는 경우 존재한다고 가정하는 “포함 가정”때문입니다.

2008+에는 행 목표를 해제 하는 문서화 된 추적 플래그 4138 이 있습니다. 그 결과 TOP자식 연산자가 일치하는 모든 행을 읽지 않고 조기에 종료 할 수 있다는 가정없이 계획에 비용이 발생합니다 . 이 추적 플래그를 사용하면 자연스럽게 더 최적의 인덱스 교차 계획을 얻습니다.

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 4138)       

이 계획은 이제 두 인덱스 탐색에서 2 억 행 전체를 읽는 데 비용이 올바르게 들지만 키 조회에 비용이 많이 듭니다 (2 천 대 실제 0으로 추정 TOP 10됨).이를 최대 10으로 제한하지만 추적 플래그는이를 고려하지 않습니다 . 그래도 계획은 전체 CI 스캔보다 훨씬 저렴하므로 선택됩니다.

물론이 계획 은 일반적인 조합 에는 적합하지 않을 수 있습니다 . 백조와 같은.

복합 인덱스 animal (colour, species)또는 이상적으로 animal (species, colour)는 두 시나리오 모두에서 쿼리가 훨씬 효율적입니다.

복합 인덱스를 가장 효율적으로 사용하려면 LIKE 'swan'로 변경해야 = 'swan'합니다.

아래 표는 네 가지 순열 모두에 대한 실행 계획에 표시된 탐색 술어 및 잔존 술어를 보여줍니다.

+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
|                 WHERE clause                 |       Index       |                         Seek Predicate                         |              Residual Predicate              |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
| colour LIKE 'black%' AND species LIKE 'swan' | ix_colour_species | colour >= 'black' AND colour < 'blacL'                         | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species LIKE 'swan' | ix_species_colour | species >= 'swan' AND species <= 'swan'                        | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species = 'swan'    | ix_colour_species | (colour,species) >= ('black', 'swan')) AND colour < 'blacL'    | colour LIKE 'black%' AND species = 'swan'    |
| colour LIKE 'black%' AND species = 'swan'    | ix_species_colour | species = 'swan' AND (colour >= 'black' and colour <  'blacL') | colour like 'black%'                         |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+

답변

DECLARE @topn INT = 10
SELECT TOP (@topn) *
FROM    animal
WHERE   colour LIKE 'black%'
AND species LIKE 'swan'