클러스터형 및 비 클러스터형 인덱스는 실제로 무엇을 의미합니까?

클러스터형 및 비 클러스터형 인덱스는 실제로 무엇을 의미합니까?

---

나는 DB에 대한 노출이 제한적이고 응용 프로그래머로 DB 만 사용했습니다. Clustered및 에 대해 알고 싶습니다 Non clustered indexes. 나는 봤고 내가 찾은 것은 :

클러스터형 인덱스는 테이블의 레코드가 물리적으로 저장되는 방식을 재정렬하는 특수한 유형의 인덱스입니다. 따라서 테이블에는 클러스터형 인덱스가 하나만있을 수 있습니다. 클러스터형 인덱스의 리프 노드에는 데이터 페이지가 포함됩니다. 비 클러스터형 인덱스는 인덱스의 논리적 순서가 디스크에있는 행의 물리적 저장 순서와 일치하지 않는 특수한 유형의 인덱스입니다. 비 클러스터형 인덱스의 리프 노드는 데이터 페이지로 구성되지 않습니다. 대신 리프 노드에는 인덱스 행이 포함됩니다.

SO에서 찾은 것은 클러스터형 인덱스와 비 클러스터형 인덱스의 차이점 은 무엇입니까? .

누군가 이것을 평범한 영어로 설명 할 수 있습니까?

---

클러스터형 인덱스를 사용하면 행이 인덱스와 동일한 순서로 디스크에 물리적으로 저장됩니다. 따라서 클러스터형 인덱스는 하나만있을 수 있습니다.

클러스터되지 않은 인덱스에는 물리적 행에 대한 포인터가있는 두 번째 목록이 있습니다. 새 인덱스가 생성 될 때마다 새 레코드를 작성하는 데 걸리는 시간이 늘어나지 만 클러스터되지 않은 인덱스를 많이 가질 수 있습니다.

모든 열을 다시 가져 오려면 일반적으로 클러스터형 인덱스에서 읽는 것이 더 빠릅니다. 먼저 인덱스로 이동 한 다음 테이블로 이동할 필요가 없습니다.

데이터를 다시 정렬해야하는 경우 클러스터형 인덱스가있는 테이블에 쓰는 속도가 느려질 수 있습니다.

---

클러스터형 인덱스는 디스크에 실제로 가까운 값을 저장하도록 데이터베이스에 지시하는 것을 의미합니다. 이것은 클러스터 된 인덱스 값의 일부 범위에 속하는 레코드를 빠르게 스캔 / 검색 할 수있는 이점이 있습니다.

예를 들어 고객 및 주문이라는 두 개의 테이블이 있습니다.

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

특정 고객의 모든 주문을 신속하게 검색하려면 Order 테이블의 "CustomerID"열에 클러스터형 인덱스를 만들 수 있습니다. 이렇게하면 동일한 CustomerID를 가진 레코드가 디스크 (클러스터형)에 서로 가깝게 물리적으로 저장되어 검색 속도가 빨라집니다.

PS CustomerID의 인덱스는 분명히 고유하지 않으므로 인덱스를 "고유 화"하기 위해 두 번째 필드를 추가하거나 데이터베이스가이를 처리하도록해야하지만 이는 또 다른 이야기입니다.

여러 인덱스에 대해. 데이터가 물리적으로 배열되는 방식을 정의하므로 테이블 당 하나의 클러스터형 인덱스 만 가질 수 있습니다. 비유를 원한다면 많은 테이블이있는 큰 방을 상상해보십시오. 이 테이블을 배치하여 여러 행을 형성하거나 모두 함께 당겨 큰 회의 테이블을 만들 수 있지만 동시에 두 가지 방법을 사용할 수는 없습니다. 테이블은 다른 인덱스를 가질 수 있으며, 클러스터 된 인덱스의 항목을 가리키며 최종적으로 실제 데이터를 찾을 위치를 알려줍니다.

---

SQL Server 행 지향 저장소에서 클러스터형 및 비 클러스터형 인덱스는 모두 B 트리로 구성됩니다.

( 이미지 소스 )

클러스터 인덱스와 비 클러스터 인덱스의 주요 차이점은 클러스터 인덱스의 리프 레벨이 있다는 것입니다 이다 테이블. 이것은 두 가지 의미가 있습니다.

1. 클러스터 된 인덱스 리프 페이지의 행은 항상 포함 뭔가 테이블에있는 (비 스파 스)의 각 열 (값 또는 실제 값에 대한 포인터 중 하나를).
2. 클러스터형 인덱스는 테이블의 기본 복사본입니다.

비 클러스터형 인덱스는 INCLUDESQL Server 2005 이후 절 을 사용하여 모든 비키 열을 명시 적으로 포함 함으로써 포인트 1을 수행 할 수 있지만이 열은 보조 표현이며 항상 데이터의 다른 복사본 (테이블 자체)이 있습니다.

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A,B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A,B) INCLUDE (C,D)

위의 두 인덱스는 거의 동일합니다. 키 열에 대한 값을 포함하는 상위 레벨 인덱스 페이지 A,B와 다음을 포함하는 리프 레벨 페이지A,B,C,D

데이터 행 자체는 하나의 순서로만 정렬 할 수 있으므로 테이블 당 하나의 클러스터형 인덱스 만있을 수 있습니다.

SQL Server 온라인 설명서의 위 인용문은 많은 혼란을 야기합니다

제 생각에는 훨씬 더 나은 표현이 될 것입니다.

클러스터 된 인덱스의 리프 수준 행이 있기 때문에 테이블 당 하나의 클러스터 된 인덱스가있을 수 있습니다 테이블 행.

책 온라인 인용문은 정확하지 않지만 클러스터되지 않은 인덱스와 클러스터링 된 인덱스 모두의 "정렬"은 논리적이지 물리적 인 것이 아님을 분명히해야합니다. 연결 목록을 따라 리프 수준에서 페이지를 읽고 슬롯 배열 순서로 페이지의 행을 읽으면 인덱스 행을 정렬 된 순서로 읽지 만 물리적으로 페이지가 정렬되지 않을 수 있습니다. 클러스터형 인덱스를 사용하면 행이 항상 인덱스 키 와 동일한 순서로 디스크에 물리적으로 저장된다는 일반적 믿음 이 거짓입니다.

이것은 터무니없는 구현입니다. 예를 들어 4GB 테이블의 중간에 행이 삽입 된 경우 SQL Server는 새로 삽입 된 행을위한 공간을 만들기 위해 파일에 2GB의 데이터를 복사 할 필요 가 없습니다 .

대신 페이지 분할이 발생합니다. 클러스터형 및 비 클러스터형 인덱스 모두의 리프 수준에있는 각 페이지에는 File:Page논리적 키 순서로 다음 및 이전 페이지 의 주소 ( )가 있습니다. 이러한 페이지는 연속적이거나 키 순서 일 필요는 없습니다.

예를 들어 링크 된 페이지 체인은 1:2000 <-> 1:157 <-> 1:7053

페이지 분할이 발생하면 파일 그룹의 모든 위치에서 새 페이지가 할당됩니다 (혼합 익스텐트, 소형 테이블의 경우 또는 해당 객체에 속하는 비어 있지 않은 균일 익스텐트 또는 새로 할당 된 균일 익스텐트). 파일 그룹에 둘 이상이 포함 된 경우 동일한 파일에 있지 않을 수도 있습니다.

논리적 순서 및 연속성이 이상적인 물리적 버전과 다른 정도는 논리적 조각화 정도입니다.

단일 파일로 새로 생성 된 데이터베이스에서 다음을 실행했습니다.

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type = 'P'
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

그런 다음 페이지 레이아웃을

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

결과는 도처에있었습니다. 키 순서의 첫 번째 행 (값 1-아래 화살표로 강조 표시됨)은 거의 마지막 실제 페이지에있었습니다.

논리적 순서와 물리적 순서 간의 상관 관계를 높이기 위해 인덱스를 다시 작성하거나 재구성하여 조각화를 줄이거 나 제거 할 수 있습니다.

실행 후

ALTER INDEX ix ON T REBUILD;

나는 다음을 얻었다

테이블에 클러스터형 인덱스가없는 경우이를 힙이라고합니다.

Non clustered indexes can be built on either a heap or a clustered index. They always contain a row locator back to the base table. In the case of a heap this is a physical row identifier (rid) and consists of three components (File:Page:Slot). In the case of a Clustered index the row locator is logical (the clustered index key).

For the latter case if the non clustered index already naturally includes the CI key column(s) either as NCI key columns or INCLUDE-d columns then nothing is added. Otherwise the missing CI key column(s) silently get added in to the NCI.

SQL Server always ensures that the key columns are unique for both types of index. The mechanism in which this is enforced for indexes not declared as unique differs between the two index types however.

Clustered indexes get a uniquifier added for any rows with key values that duplicate an existing row. This is just an ascending integer.

For non clustered indexes not declared as unique SQL Server silently adds the row locator in to the non clustered index key. This applies to all rows, not just those that are actually duplicates.

The clustered vs non clustered nomenclature is also used for column store indexes. The paper Enhancements to SQL Server Column Stores states

Although column store data is not really "clustered" on any key, we decided to retain the traditional SQL Server convention of referring to the primary index as a clustered index.

---

I realize this is a very old question, but I thought I would offer an analogy to help illustrate the fine answers above.

CLUSTERED INDEX

If you walk into a public library, you will find that the books are all arranged in a particular order (most likely the Dewey Decimal System, or DDS). This corresponds to the "clustered index" of the books. If the DDS# for the book you want was 005.7565 F736s, you would start by locating the row of bookshelves that is labeled 001-099 or something like that. (This endcap sign at the end of the stack corresponds to an "intermediate node" in the index.) Eventually you would drill down to the specific shelf labelled 005.7450 - 005.7600, then you would scan until you found the book with the specified DDS#, and at that point you have found your book.

NON-CLUSTERED INDEX

But if you didn't come into the library with the DDS# of your book memorized, then you would need a second index to assist you. In the olden days you would find at the front of the library a wonderful bureau of drawers known as the "Card Catalog". In it were thousands of 3x5 cards -- one for each book, sorted in alphabetical order (by title, perhaps). This corresponds to the "non-clustered index". These card catalogs were organized in a hierarchical structure, so that each drawer would be labeled with the range of cards it contained (Ka - Kl, for example; i.e., the "intermediate node"). Once again, you would drill in until you found your book, but in this case, once you have found it (i.e, the "leaf node"), you don't have the book itself, but just a card with an index number (the DDS#) with which you could find the actual book in the clustered index.

Of course, nothing would stop the librarian from photocopying all the cards and sorting them in a different order in a separate card catalog. (Typically there were at least two such catalogs: one sorted by author name, and one by title.) In principle, you could have as many of these "non-clustered" indexes as you want.

---

Find below some characteristics of clustered and non-clustered indexes:

Clustered Indexes

1. Clustered indexes are indexes that uniquely identify the rows in an SQL table.
2. Every table can have exactly one clustered index.
3. You can create a clustered index that covers more than one column. For example: create Index index_name(col1, col2, col.....).
4. By default, a column with a primary key already has a clustered index.

Non-clustered Indexes

1. Non-clustered indexes are like simple indexes. They are just used for fast retrieval of data. Not sure to have unique data.

---

A very simple, non-technical rule-of-thumb would be that clustered indexes are usually used for your primary key (or, at least, a unique column) and that non-clustered are used for other situations (maybe a foreign key). Indeed, SQL Server will by default create a clustered index on your primary key column(s). As you will have learnt, the clustered index relates to the way data is physically sorted on disk, which means it's a good all-round choice for most situations.

---

Clustered Index

A clustered index determine the physical order of DATA in a table.For this reason a table have only 1 clustered index.

• "dictionary" No need of any other Index, its already Index according to words

Nonclustered Index

A non clustered index is analogous to an index in a Book.The data is store in one place. the index is store in another place and the index have pointers to the storage location of the data.For this reason a table have more than 1 Nonclustered index.

• "Chemistry book" at staring there is a separate index to point Chapter location and At the "END" there is another Index pointing the common WORDS location

---

Clustered Index

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, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns. For details about index key limits see Maximum Capacity Specifications for SQL Server.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described

---

Let me offer a textbook definition on "clustering index", which is taken from 15.6.1 from Database Systems: The Complete Book:

We may also speak of clustering indexes, which are indexes on an attribute or attributes such that all of tuples with a fixed value for the search key of this index appear on roughly as few blocks as can hold them.

To understand the definition, let's take a look at Example 15.10 provided by the textbook:

A relation R(a,b) that is sorted on attribute a and stored in that order, packed into blocks, is surely clusterd. An index on a is a clustering index, since for a given a-value a1, all the tuples with that value for a are consecutive. They thus appear packed into blocks, execept possibly for the first and last blocks that contain a-value a1, as suggested in Fig.15.14. However, an index on b is unlikely to be clustering, since the tuples with a fixed b-value will be spread all over the file unless the values of a and b are very closely correlated.

Note that the definition does not enforce the data blocks have to be contiguous on the disk; it only says tuples with the search key are packed into as few data blocks as possible.

A related concept is clustered relation. A relation is "clustered" if its tuples are packed into roughly as few blocks as can possibly hold those tuples. In other words, from a disk block perspective, if it contains tuples from different relations, then those relations cannot be clustered (i.e., there is a more packed way to store such relation by swapping the tuples of that relation from other disk blocks with the tuples the doesn't belong to the relation in the current disk block). Clearly, R(a,b) in example above is clustered.

To connect two concepts together, a clustered relation can have a clustering index and nonclustering index. However, for non-clustered relation, clustering index is not possible unless the index is built on top of the primary key of the relation.

"Cluster" as a word is spammed across all abstraction levels of database storage side (three levels of abstraction: tuples, blocks, file). A concept called "clustered file", which describes whether a file (an abstraction for a group of blocks (one or more disk blocks)) contains tuples from one relation or different relations. It doesn't relate to the clustering index concept as it is on file level.

However, some teaching material likes to define clustering index based on the clustered file definition. Those two types of definitions are the same on clustered relation level, no matter whether they define clustered relation in terms of data disk block or file. From the link in this paragraph,

An index on attribute(s) A on a file is a clustering index when: All tuples with attribute value A = a are stored sequentially (= consecutively) in the data file

Storing tuples consecutively is the same as saying "tuples are packed into roughly as few blocks as can possibly hold those tuples" (with minor difference on one talking about file, the other talking about disk). It's because storing tuple consecutively is the way to achieve "packed into roughly as few blocks as can possibly hold those tuples".

---

Clustered Index: Primary Key constraint creates clustered Index automatically if no clustered Index already exists on the table. Actual data of clustered index can be stored at leaf level of Index.

비 클러스터형 인덱스 : 비 클러스터형 인덱스의 실제 데이터는 리프 노드에서 직접 찾을 수 없습니다. 대신 실제 데이터를 가리키는 행 로케이터 값만 있기 때문에 추가 단계를 거쳐야합니다. 비 클러스터형 인덱스는 클러스터형 인덱스로 정렬 할 수 없습니다. 테이블 당 클러스터되지 않은 인덱스가 여러 개있을 수 있으며 실제로 사용중인 SQL Server 버전에 따라 다릅니다. 기본적으로 SQL Server 2005는 249 개의 비 클러스터형 인덱스를 허용하고 2008, 2016과 같은 위 버전의 경우 테이블 당 999 개의 비 클러스터형 인덱스를 허용합니다.

참고 URL : https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean