클러스터형 vs 비 클러스터형

클러스터형 vs 비 클러스터형

---

저의 SQL (Server 2008)에 대한 저수준 지식은 제한적이며 이제 DBA가 문제를 해결하고 있습니다. 시나리오를 설명해 드리겠습니다 (내가 옳다는 희망으로 명백한 진술을 언급했지만 잘못된 것을 발견하면 알려주세요).

사람들을위한 '법원 명령'을 보관하는 테이블이 있습니다. (이름 : CourtOrder) 테이블을 만들 때 다음과 같이 만들었습니다.

CREATE TABLE dbo.CourtOrder
(
  CourtOrderID INT NOT NULL IDENTITY(1,1), (Primary Key)
  PersonId INT NOT NULL,
  + around 20 other fields of different types.
)

그런 다음 효율성을 위해 클러스터되지 않은 인덱스를 기본 키에 적용했습니다. 내 이유는 이것이 고유 한 필드 (기본 키)이고 주로 선택 목적으로 인덱싱되어야하기 때문입니다.Select from table where primary key = ...

그런 다음 PersonId에 CLUSTERED 인덱스를 적용했습니다. 그 이유는 대부분의 작업이 한 사람에 대한 주문을 받고 있기 때문에 특정 사람에 대한 주문을 물리적으로 그룹화하기 때문입니다. 그래서,select from mytable where personId = ...

나는 지금 이것에 대해 뽑혔다. 클러스터형 인덱스는 기본 키에, 일반 인덱스는 personId에 배치해야한다고 들었습니다. 그것은 나에게 매우 이상하게 보입니다. 우선, 고유 한 열에 클러스터형 인덱스를 배치하는 이유는 무엇입니까? 클러스터링이란 무엇입니까? 확실히 클러스터형 인덱스의 낭비입니까? 고유 한 열에 일반 인덱스가 사용된다고 믿었을 것입니다. 또한 인덱스 클러스터링은 다른 열을 클러스터링 할 수 없음을 의미합니다 (테이블 당 하나, 맞습니까?).

내가 실수했다는 이유는 PersonId에 클러스터형 인덱스를 넣으면 삽입 속도가 느려질 것이라고 믿기 때문입니다. 선택 속도가 5 % 증가하면 삽입 및 업데이트 속도가 95 % 저하됩니다. 정확하고 유효합니까?

personId를 클러스터링하기 때문에 SQL Server는 PersonId를 삽입하거나 변경할 때마다 데이터를 재 배열해야한다고 말합니다.

그래서 나는 왜 그렇게 느리다면 SQL이 CLUSTERED INDEX의 개념을 가질까요? 그들이 말하는 것만 큼 느린가요? 최적의 성능을 얻으려면 인덱스를 어떻게 설정해야합니까? SELECT가 INSERT보다 더 많이 사용된다고 생각했지만 INSERTS에 잠금 문제가 있다고 말합니다.

누군가 나를 도울 수 있기를 바랍니다.

---

클러스터형 인덱스와 비 클러스터형 인덱스의 차이점은 클러스터형 인덱스가 데이터베이스 행의 물리적 순서를 결정한다는 것 입니다. 즉, 클러스터형 인덱스를에 적용하면 PersonId행이 PersonId테이블에서 물리적으로 정렬 되어 이에 대한 인덱스 검색이 행으로 바로 이동할 수 있습니다 (비 클러스터형 인덱스가 아닌 행의 위치, 추가 단계 추가).

즉, 기본 키가 클러스터 된 인덱스가 아니라 전례가없는 경우는 드뭅니다 . 시나리오의 문제는 실제로 가정하는 것과 반대입니다 . 중복이 아닌 클러스터형 인덱스에서 고유 한 값 을 원합니다 . 클러스터형 인덱스가 행의 물리적 순서를 결정하기 때문에 인덱스가 고유하지 않은 열에있는 경우 서버는 중복 키 값이있는 행 (귀하의 경우 동일한 PersonId) 결합 된 값 (키 + 배경 값)이 고유하도록합니다.

내가되는 제안 유일한 것은 하지 대리 키 (사용자의 사용 CourtOrderId대신 기본 키 등) 열,하지만의 복합 기본 키를 사용하여 PersonId열 및 일부 다른 고유 식별 열 또는 세트를. 그래도 가능하지 않거나 실용적이지 않은 경우 클러스터형 인덱스를 CourtOrderId.

---

나는 결코 SQL 전문가가 아니므로 DBA 관점이 아닌 개발자 관점으로 생각하십시오 ..

순차적 순서가 아닌 클러스터 된 (물리적으로 정렬 된) 인덱스에 대한 삽입은 삽입 / 업데이트에 대한 추가 작업을 유발합니다. 또한 한 번에 많은 삽입이 발생하고 모두 동일한 위치에서 발생하는 경우 경합이 발생합니다. 특정 성능은 데이터 및 액세스 방법에 따라 다릅니다. 일반적인 경험 규칙은 테이블에서 가장 고유 한 좁은 값 (일반적으로 PK)에 클러스터형 인덱스를 구축하는 것입니다.

PersonId가 변경되지 않을 것이라고 가정하므로 여기에서 업데이트가 작동하지 않습니다. 그러나 PersonId가 1 2 3 3 4 5 6 7 8 8 인 몇 행의 스냅 샷을 고려하십시오.

이제 3의 PersonId에 대해 20 개의 새 행을 삽입합니다. 먼저 이것은 고유 키가 아니기 때문에 서버는 값에 약간의 추가 바이트를 추가하여 (장면 뒤에서) 고유하게 만들고 (추가 공간도 추가 함) 위치를 지정합니다. 이것들은 상주 할 것입니다. 삽입이 끝에 발생하는 자동 증가 PK 삽입과 비교하십시오. 비 기술적 인 설명은 다음과 같을 것입니다. 테이블 끝에서 더 높은 값이 자연스럽게 진행되고 항목을 삽입하는 동안 해당 위치에서 기존 항목의 위치를 ​​재 작업하는 경우 수행 할 '리프 셔플 링'작업이 적습니다.

이제 삽입에 문제가있는 경우 동일한 (또는 유사한) PersonId 값을 한 번에 삽입하여 테이블 전체의 여러 위치에서이 추가 작업을 수행하고 조각화로 인해 죽을 가능성이 있습니다. 귀하의 경우에 클러스터링되는 PK로 전환하는 단점은 테이블 전체에 분산 된 값이 다른 PersonIds에 삽입 문제가있는 경우, 클러스터형 인덱스를 PK로 전환하고 모든 삽입이 이제 하나에서 발생하는 경우입니다. 경합 집중 증가로 인해 문제가 실제로 악화 될 수 있습니다. (반대로, 오늘날 삽입물이 전체적으로 퍼져 있지 않지만 일반적으로 모두 유사한 영역에 묶여 있다면 클러스터형 인덱스를 PersonId에서 PK로 전환하여 문제를 완화 할 수 있습니다. 분열.)

성능 문제는 고유 한 상황에 따라 분석되어야하며 이러한 유형의 답변은 일반적인 지침으로 만 사용하십시오. 최선의 방법은 문제가 어디에 있는지 정확히 확인할 수있는 DBA에 의존하는 것입니다. 단순한 인덱스 조정 이상의 리소스 경합 문제가있는 것 같습니다. 이것은 훨씬 더 큰 문제의 증상 일 수 있습니다. (아마도 디자인 문제 ... 그렇지 않으면 리소스 제한이 있습니다.)

어쨌든 행운을 빕니다!

---

일부 작성자는 범위 쿼리에 도움이되는 대안이있는 경우 열 CI에서 "을 낭비"하지 말 것을 제안 identity합니다.

MSDN Clustered Index Design Guidelines 에서 다음 기준에 따라 키를 선택해야합니다.

1. 자주 사용하는 쿼리에 사용할 수 있습니다.
2. 높은 수준의 고유성을 제공합니다.
3. 범위 쿼리에 사용할 수 있습니다.

Your CourtOrderID column meets 2. Your PersonId meets 1 and 3. As most rows will end up with the uniqueifier added anyway you might as well just declare it as unique and use PersonId,CourtOrderID as this will be the same width but be more useful as the clustered index key is added to all NCIs as the row locator and this will allow them to cover more queries.

The main issue with using PersonId,CourtOrderID as the CI is that logical fragmentation will likely ensue (and this particularly affects the range queries you are trying to help) so you would need to monitor fill factor, and fragmentation levels and perform index maintenance more often.

---

It's explained in the following link: https://msdn.microsoft.com/en-us/ms190457.aspx

Clustered

• Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

• The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered

• Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

• The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

• You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.

---

Some db with some nasty selects, joins in a stored procedure - only diffrence is the index

INDEXES - clustered vs nonclustered

  891 rows
  10 sec
  NONCLUSTERED 

  OR

  891 rows
  14 sec
  CLUSTERED

참고URL : https://stackoverflow.com/questions/7605707/clustered-vs-non-clustered