Index란

책의 목차와 비슷하게 데이터의 색인을 기록하여 검색 속도를 증가 시킬 수 있는 구조입니다.

  • 클러스터형
    • 클러스터형 인덱스는 해당 키 값을 기반으로 테이블이나 뷰의 데이터 행을 정렬하고 저장합니다. 인덱스 정의에 여러 열이 포함됩니다. 데이터 행 자체는 한 가지 순서로만 저장될 수 있으므로 테이블당 클러스터형 인덱스는 하나만 있을 수 있습니다.
    • 테이블의 데이터 행이 정렬된 순서로 저장될 때만 테이블에 클러스터형 인덱스가 포함됩니다. 클러스터형 인덱스가 포함된 테이블을 클러스터형 테이블이라고 합니다. 테이블에 클러스터형 인덱스가 없으면 해당 데이터 행은 힙이라는 정렬되지 않은 _구조로 저장됩니다.
  • 비클러스터형 인덱스
    • 비클러스터형 인덱스의 구조는 데이터 행으로부터 독립적입니다. 비클러스터형 인덱스에는 비클러스터형 인덱스 키 값이 있으며 각 키 값 항목에는 해당 키 값이 포함된 데이터 행에 대한 포인터가 있습니다.
    • 비클러스터형 인덱스의 인덱스 행에서 데이터 행으로의 포인터를 행 로케이터라고 합니다. 행 로케이터의 구조는 데이터 페이지가 힙에 저장되는지 아니면 클러스터형 테이블에 저장되는지에 따라 다릅니다. 힙의 경우 행 로케이터는 행에 대한 포인터입니다. 클러스터형 테이블의 경우 행 로케이터는 클러스터형 인덱스 키입니다.

인덱스 및 제약 조건

테이블 열에 PRIMARY KEY 및 UNIQUE 제약 조건을 정의하면 인덱스가 자동으로 생성됩니다. 예를 들어 사용자가 UNIQUE 제약 조건이 있는 테이블을 만들면 데이터베이스 엔진에서는 자동으로 비클러스터형 인덱스를 만듭니다. PRIMARY KEY를 구성하면 데이터베이스 엔진에서는 자동으로 클러스터형 인덱스를 만듭니다(클러스터형 인덱스가 없는 경우). 기존 테이블에 PRIMARY KEY 제약 조건을 적용하려 하거나 해당 테이블에 클러스터형 인덱스가 이미 있으면 SQL Server는 비클러스터형 인덱스를 사용하여 기본 키를 적용합니다.

쿼리 최적화 프로그램의 인덱스 사용 방법

인덱스를 잘 디자인하면 디스크 I/O 작업과 시스템 리소스 사용을 줄일 수 있으므로 쿼리 성능이 향상됩니다. 인덱스는 SELECT, UPDATE, DELETE 또는 MERGE 문을 포함하는 다양한 쿼리에 유용합니다. SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250 데이터베이스의 AdventureWorks2012 을 예로 들어 보겠습니다. 이 쿼리가 실행될 때 쿼리 최적화 프로그램은 데이터 검색에 사용할 수 있는 각 방법을 평가하고 가장 효율적인 방법을 선택합니다. 이때 테이블 검색이 선택될 수 있습니다. 가능한 경우 하나 이상의 인덱스 검색이 선택될 수도 있습니다.

테이블 검색을 수행할 때 쿼리 최적화 프로그램은 테이블의 모든 행을 읽고 쿼리 조건에 맞는 행을 추출합니다. 테이블 검색은 많은 디스크 I/O 작업을 생성하고 리소스를 많이 사용할 수 있습니다. 그러나 예를 들어 쿼리 결과 집합의 행이 테이블에서 높은 비율을 차지할 경우 테이블 검색이 가장 효율적인 방법일 수 있습니다.

일반 인덱스 디자인 지침

경험이 많은 데이터베이스 관리자는 인덱스를 잘 디자인할 수 있습니다. 그러나 데이터베이스와 작업이 조금만 복잡해져도 이 태스크는 복잡하고 시간이 많이 걸리며 오류가 쉽게 발생할 수 있습니다. 데이터베이스, 쿼리 및 데이터 열의 특성을 이해하면 최적의 인덱스를 디자인할 수 있습니다.

데이터베이스 고려 사항

인덱스를 디자인할 때 다음과 같은 데이터 베이스 지침을 고려합니다.

  • 테이블에 대한 인덱스를 많이 만들면 테이블의 데이터가 변경될 경우 인덱스도 모두 적절하게 조정되어야 하므로 INSERT, UPDATE, DELETE 및 MERGE 문의 성능이 저하될 수 있습니다. 예를 들어 열이 여러 인덱스에서 사용되고 열 데이터를 수정하는 UPDATE 문을 실행하는 경우 열을 포함하는 각 인덱스뿐만 아니라 기본 테이블(힙 또는 클러스터형 인덱스)에 있는 열도 업데이트되어야 합니다.

    • 과도하게 업데이트되는 테이블에 대한 인덱스를 너무 많이 만들지 말고 가능한 열 수가 적은 좁은 인덱스를 만듭니다.
    • 많은 인덱스를 사용하면 업데이트 요구는 적지만 데이터 양이 많은 테이블의 쿼리 성능이 향상될 수 있습니다. 인덱스 수가 많으면 SELECT 문과 같이 데이터를 수정하지 않는 쿼리의 성능이 향상될 수 있습니다. 이는 쿼리 최적화 프로그램이 가장 빠른 액세스 방법을 결정할 때 선택할 수 있는 인덱스가 더 많기 때문입니다.

  • 작은 테이블에 대한 인덱스를 만들면 비효율적일 수 있습니다. 이는 쿼리 최적화 프로그램이 간단한 테이블 스캔을 수행하는 시간보다 데이터를 검색하기 위해 인덱스를 통과하는 시간이 더 길 수 있기 때문입니다. 따라서 작은 테이블에 대한 인덱스는 전혀 사용되지 않을 수도 있지만 테이블의 데이터가 변경될 때마다 유지 관리되어야 합니다.

클러스터형 인덱스 디자인 지침

클러스터형 인덱스는 그 키 값에 기반하여 테이블에 데이터 행을 정렬하고 저장합니다. 데이터 행은 자체적으로 하나의 순서로만 정렬될 수 있으므로 테이블당 클러스터형 인덱스가 하나만 있을 수 있습니다. 일부 예외를 제외하고는 각 테이블에서 클러스터형 인덱스가 정의된 열은 다음과 같은 특성을 가져야 합니다.

  • 자주 사용하는 쿼리에 대해 사용할 수 있습니다.

  • 높은 수준의 고유성을 제공합니다.

    참고

    PRIMARY KEY 제약 조건을 만들 때는 열에 고유 인덱스가 자동으로 생성됩니다. 기본적으로 이 인덱스는 클러스터링되지만 제약 조건을 만들 때 비클러스터형 인덱스로 지정할 수 있습니다.

  • 범위 쿼리에서 사용할 수 있습니다.

클러스터형 인덱스가 UNIQUE 속성으로 생성되지 않는 경우 데이터베이스 엔진은 4바이트 고유 식별자 열을 테이블에 자동으로 추가합니다. 필요한 경우 데이터베이스 엔진 은 고유 식별자 값을 자동으로 행에 추가하여 각 키를 고유하게 만듭니다. 이 열과 해당 값은 내부적으로 사용되며 사용자는 보거나 액세스할 수 없습니다.

비클러스터형 인덱스 디자인 지침

비클러스터형 인덱스에는 테이블 데이터의 스토리지 위치를 가리키는 행 로케이터와 인덱스 키 값이 있습니다. 테이블 또는 인덱싱된 뷰에 비클러스터형 인덱스를 여러 개 만들 수 있습니다. 일반적으로 클러스터형 인덱스를 적용할 수 없고 자주 사용되는 쿼리의 성능을 개선하도록 비클러스터형 인덱스를 디자인해야 합니다.

책에서 색인을 사용하는 것처럼 쿼리 최적화 프로그램은 비클러스터형 인덱스를 검색하여 테이블에서 데이터 값의 위치를 찾고 해당 위치에서 데이터를 직접 검색하는 방식으로 데이터 값을 검색합니다. 비클러스터형 인덱스에는 쿼리에서 검색하는 데이터 값에 대한 테이블의 정확한 위치를 설명하는 항목이 있기 때문에 정확히 일치하는 값을 찾는 쿼리에는 비클러스터형 인덱스가 가장 적합합니다. 예를 들어 HumanResources. Employee 테이블에서 특정 관리자에게 보고하는 모든 직원을 쿼리하면 쿼리 최적화 프로그램은 IX_Employee_ManagerID가 키 열인 비클러스터형 인덱스 ManagerID 를 사용할 수 있습니다. 쿼리 최적화 프로그램은 인덱스에서 지정된 ManagerID와 일치하는 모든 항목을 빠르게 찾을 수 있습니다. 각 인덱스 항목은 해당 데이터를 찾을 수 있는 클러스터형 인덱스나 테이블의 정확한 페이지와 행을 가리킵니다. 쿼리 최적화 프로그램은 인덱스에서 모든 항목을 찾은 후 정확한 페이지와 행으로 직접 이동하여 데이터를 검색할 수 있습니다.

데이터베이스 고려 사항

비클러스터형 인덱스를 디자인할 때 데이터베이스의 특징을 고려해야 합니다.

  • 자주 업데이트하지는 않지만 데이터가 많은 데이터베이스나 테이블의 경우 비클러스터형 인덱스가 많으면 쿼리 성능이 향상될 수 있습니다. 전체 테이블 비클러스터형 인덱스에 비해 인덱스 유지 관리 비용을 줄이고, 인덱스 스토리지 비용을 줄이고, 쿼리 성능을 향상시킬 수 있도록 데이터의 잘 정의된 하위 집합에 대한 필터링된 인덱스를 만듭니다.

    읽기 전용 데이터를 주로 포함하는 의사 결정 지원 시스템 애플리케이션 및 데이터베이스의 경우에도 비클러스터형 인덱스가 많으면 유용할 수 있습니다. 쿼리 최적화 프로그램에서 가장 빠른 액세스 방법을 결정할 때 선택할 수 있는 인덱스가 늘어나며 데이터베이스가 자주 업데이트되지 않으므로 인덱스 유지 관리로 인해 성능이 저하되지 않습니다.

  • 자주 업데이트되는 테이블을 포함하는 OLTP(온라인 트랜잭션 처리) 애플리케이션 및 데이터베이스의 경우에는 너무 많이 인덱싱하지 않아야 합니다. 또한 인덱스는 가능한 적은 수의 열을 포함하는 좁은 인덱스여야 합니다.

    테이블에 대한 인덱스를 많이 만들면 테이블의 데이터가 변경될 경우 인덱스도 모두 적절하게 조정되어야 하므로 INSERT, UPDATE, DELETE 및 MERGE 문의 성능이 저하될 수 있습니다.

쿼리 고려 사항

비클러스터형 인덱스를 만들기 전에 데이터가 액세스되는 방법을 이해해야 합니다. 다음과 같은 특성이 있는 쿼리의 경우 비클러스터형 인덱스 사용을 고려하십시오.

  • JOIN 또는 GROUP BY 절을 사용합니다.

    조인 및 그룹화 작업과 관련된 열에는 비클러스터형 인덱스를 여러 개 만들고 외래 키 열에는 클러스터형 인덱스를 만듭니다.

  • 큰 결과 집합을 반환하지 않는 쿼리

    대형 테이블에서 행의 잘 정의된 하위 집합을 반환하는 쿼리를 처리하는 필터링된 인덱스를 만듭니다.