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

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

---

인덱스 선언의 시작 부분에서 가장 선택적인 열을 넣어야한다고 들었습니다. 예:

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

우선, 내가 말하고있는 것이 맞습니까? 그렇다면 인덱스의 열 순서를 다시 정렬하여 성능에 큰 차이가 있습니까? 그렇지 않으면 "행하기 좋은"방법입니까?

내가 묻는 이유는 DTA를 통해 쿼리를 넣은 후 기존 인덱스와 거의 동일한 열이 거의 동일한 인덱스를 다른 순서로 생성하는 것이 좋습니다. 누락 된 열을 기존 색인에 추가하고 잘 호출하는 것을 고려하고있었습니다. 생각?

---

다음과 같은 색인을보십시오.

Cols
  1   2   3
-------------
|   | 1 |   |
| A |---|   |
|   | 2 |   |
|---|---|   |
|   |   |   |
|   | 1 | 9 |
| B |   |   |
|   |---|   |
|   | 2 |   |
|   |---|   |
|   | 3 |   |
|---|---|   |

첫 번째 열이 두 번째 열을 먼저 제한하는 것보다 더 많은 결과를 제거하므로 A를 첫 번째로 제한하는 방법을 참조하십시오. 인덱스 통과 방법, 열 1, 열 2 등을 파악하면 주먹 패스에서 대부분의 결과를 제거하면 2 단계가 훨씬 빨라집니다.

또 다른 경우, 열 3에서 쿼리 한 경우 결과 집합을 좁히는 데 전혀 도움이되지 않기 때문에 옵티마이 저는 인덱스를 사용하지 않습니다. 쿼리 할 때마다 다음 단계 전에 처리 할 결과 수를 좁 히면 성능이 향상됩니다.

인덱스도 이런 식으로 저장되므로 인덱스를 쿼리 할 때 첫 번째 열을 찾기 위해 인덱스를 역 추적하지 않습니다.

한마디로 : 아니오, 그것은 보여주기위한 것이 아니며 실제 성능상의 이점이 있습니다.

---

열 순서가 중요합니다. 이제 올바른 순서는 쿼리 방법에 따라 다릅니다. 정확한 검색 또는 범위 스캔을 수행하기 위해 인덱스를 사용할 수 있습니다. 정확한 검색은 인덱스의 모든 열에 대한 값이 지정되고 쿼리가 정확히 행에 도달하는 경우입니다. 검색의 경우 열의 순서는 관련이 없습니다. 범위 스캔은 일부 열만 지정된 경우이며이 경우 순서가 중요합니다. SQL Server는 가장 왼쪽 열이 지정된 경우에만 다음으로 가장 왼쪽 열이 지정된 경우에만 범위 검색에 인덱스를 사용할 수 있습니다. 당신은 (A, B, C)에 대한 인덱스가있는 경우이를위한 범위 스캔에 사용할 수 A=@a에 대한, A=@a AND B=@b하지만 하지 에 대한 B=@b위해, C=@c도 B=@b AND C=@c. 케이스 A=@a AND C=@c는A=@a부분은 인덱스를 사용하지만 C=@cnot 은 사용합니다 (쿼리는 모든 B 값을 스캔하고로 A=@a건너 뛰지 않습니다 C=@c). 다른 데이터베이스 시스템에는 소위 '건너 뛰기 스캔'연산자가있어 외부 열이 지정되지 않은 경우 인덱스의 내부 열을 활용할 수 있습니다.

그 지식을 가지고 인덱스 정의를 다시 볼 수 있습니다. 인덱스 (MostSelective, SecondMost, Least)는 MostSelective컬럼이 지정된 경우에만 유효 합니다. 그러나 이것이 가장 선택 적이기 때문에 내부 컬럼의 관련성이 빠르게 저하됩니다. 더 나은 색인이 켜져 (MostSelective) include (SecondMost, Least)있거나 켜져 있는 경우가 종종 있습니다 (MostSelective, SecondMost) include (Least). 내부 열은 관련성이 적기 때문에 인덱스의 올바른 위치에 낮은 선택도 열을 배치하면 탐색에 노이즈가 발생하지 않으므로 중간 페이지 밖으로 이동하여 리프 페이지에만 유지하는 것이 좋습니다. 쿼리 적용 범위 목적. 즉, INCLUDE로 옮깁니다. Least열 크기가 커질수록 더 중요해 집니다. 이 인덱스는 다음을 지정하는 쿼리에만 혜택을 줄 수 있습니다.MostSelective 정확한 값 또는 범위로, 그리고 가장 선택적인 컬럼은 이미 후보 행을 상당히 제한합니다.

반면에 인덱스 (Least, SecondMost, MostSelective)는 실수로 보일 수 있지만 실제로는 매우 강력한 인덱스입니다. Least가장 바깥 쪽 쿼리로 열 이 있기 때문에 선택도가 낮은 열에 대한 결과를 집계해야하는 쿼리에 사용할 수 있습니다. 이러한 쿼리는 OLAP 및 분석 데이터웨어 하우스에서 널리 사용되며, 이러한 인덱스가 매우 적합한 경우입니다. 이러한 인덱스는 실제로 관련 클러스터의 큰 청크 ( Least일반적으로 일종의 범주 또는 유형을 나타내는 동일한 값) 에 물리적 레이아웃을 구성하고 분석 쿼리를 용이하게하기 때문에 우수한 클러스터형 인덱스를 만듭니다 .

불행히도 '정확한'순서는 없습니다. 쿠키 커터 레시피를 따르지 말고 대신 해당 테이블에 대해 사용할 쿼리 패턴을 분석하고 올바른 인덱스 열 순서를 결정하십시오.

---

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 = '~'; 

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

The ASCII art in the accepted answer is not in fact how indexes are structured. The index pages for Table1 are represented below (click the image to open in full size).

The index pages contain rows containing the whole key (in this case there is actually an additional key column appended for the row identifier as the index was not declared as unique but that can be disregarded further information about this can be found here).

For the query above SQL Server doesn't care about the selectivity of the columns. It does a binary search of the root page and discovers that the Key (PPP...,3,~ ) is >=(JJJ...,1,~ ) and < (SSS...,3,~ ) so it should read page 1:118. It then does a binary search of the key entries on that page and locates the leaf page to travel down to.

Altering the index in order of selectivity doesn't affect either the expected number of key comparisons from the binary search or the number of pages that need to be navigated to do an index seek. At best it might marginally speed up the key comparison itself.

Sometimes ordering the most selective index first will make sense for other queries in your workload though.

E.g if the workload contains queries of both the following forms.

SELECT * ... WHERE  MostSelective = 'P'

SELECT * ...WHERE Least = '~'

The indexes above aren't covering for either of them. MostSelective is selective enough to make a plan with a seek and lookups worthwhile but the query against Least isn't.

However this scenario (non covering index seek on subset of leading column(s) of a composite index) is only one possible class of query that can be helped by an index. If you never actually search by MostSelective on its own or a combination of MostSelective, SecondMost and always search by a combination of all three columns then this theoretical advantage is useless to you.

Conversely queries such as

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

Would be helped by having the reverse order of the commonly prescribed one - as it covers the query, can support a seek and returns rows in the desired order to boot.

So this is an often repeated piece of advice but at most it's a heuristic about the potential benefit to other queries - and it is no substitute for actually looking at your workload.

---

you should put columns that will be the most selective at the beginning of the index declaration.

Correct. Indexes can be composites - composed of multiple columns - and the order is important because of the leftmost principle. Reason is, that the database checks the list from left to right, and has to find a corresponding column reference matching the order defined. For example, having an index on an address table with columns:

• Address
• City
• State

Any query using the address column can utilize the index, but if the query only has either city and/or state references - the index can not be used. This is because the leftmost column isn't referenced. Query performance should tell you which is optimal - individual indexes, or multiple composites with different orders. Good read: The Tipping Point, by Kimberley Tripp

---

All the answers are wrong.

Selectivity of the individual columns in a composite index does not matter when picking the order.

Here is the simple thought process: Effectively, an index is the concatenation of the columns involved.

Giving that rationale, the only difference is comparing two 'strings' that differ earlier versus later in the string. This is a tiny part of the total cost. There is no "first pass / second pass", as mentioned in one Answer.

So, what order should be used?

1. Start with column(s) tested with =, in any order.
2. Then tack on one range column.

For example, the very-low selectivity column must come first in this:

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

Swapping the order in the index would have it totally ignore deleted.

(There are a lot more rules for ordering the columns.)

참고URL : https://stackoverflow.com/questions/2292662/how-important-is-the-order-of-columns-in-indexes