[MSSQL] 인덱스란? / 인덱스 생성 기준

# 인덱스 개요

데이터베이스 애플리케이션 병목 상태는 주로 잘못 디자인된 인덱스와 인덱스의 부족으로 인해 나타난다. 최적의 데이터베이스와 최상의 애플리케이션 성능을 위해서는 효율적인 인덱슬르 디자인하는 것이 가장 중요하다. 인덱스 유형에는 아래와 같이 여러가지가 존재한다.

• 클러스터형
• 비클러스터형 인덱스
• 고유한
• Filtered
• columnstore
• Hash
• 메모리 최적화 비클러스터형

# 인덱스 디자인 기본 사항

일반적으로 책의 끝에는 책의 정보를 빠르게 찾을 수 있는 인덱스가 존재한다. 인덱스는 키워드의 정렬된 목록이며, 각 키워드 옆에는 각 키워드를 찾을 수 있는 페이지를 가리키는 페이지 번호 세트가 있습니다. SQL Server 인덱스도 다르지 않습니다. 

 

SQL Server 인덱스는 테이블이나 뷰와 연결된 디스크상 또는 메모리 내 구조로, 테이블이나 뷰의 행 검색 속도를 높입니다. 인덱스에는 테이블이나 뷰에 있는 하나 이상의 열로 작성되는 키가 포함됩니다. 디스크상 인덱스에서 이러한 키는 SQL Server가 키 값과 연결된 행을 빠르고 효율적으로 찾을 수 있는 트리 구조(B-트리)에 저장됩니다.

(특정 쿼리에 대해 쿼리 최적화 프로그램이 사용하는 인덱스를 확인하려면 SQL Server Management Studio의 쿼리 메뉴에서 실제 실행 계획 포함을 선택합니다.)

 

인덱스 사용이 항상 좋은 성능을 의미하지 않으며 마찬가지로 좋은 성능이 항상 효율적인 인덱스 사용을 나타내는 것은 아닙니다. 인덱스 사용이 최상의 성능을 내는 데 항상 도움이 된다면 쿼리 최적화 프로그램의 작업은 간단할 것입니다. 실제로 인덱스를 잘못 선택하면 최상의 성능을 얻지 못할 수 있습니다. 따라서 쿼리 최적화 프로그램에서는 성능을 향상시킬 경우에만 인덱스나 인덱스 조합을 선택하고 성능을 저하시킬 경우에는 인덱싱된 검색을 피해야 합니다.

 

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

 

 

# 인덱스의 선정 기준

1. WHERE 절에 많이 사용되는 컬럼

2. 동일한 데이터가 적은 컬럼

3. JOIN 할 때 사용되는 컬럼

4. 정렬 기준으로 자주 사용되는 컬럼

5. NOT NULL

 

 

OR

 

1. 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도 향상(10~15%)

2. 자주 조합되어 사용되는 컬럼의 경우에는 결합인덱스 생성 고려

3. 인덱스간의 역할 정의

(즉, 가능한 한 모든 Access Path를 만족시키도록 함 -> 범용성)

4. 수정이 빈번하게 일어나지 않는 컬럼을 인덱스로 사용

5. 외부 키로 사용된 컬럼에 대하여 인덱스 생성

6. 정렬기준으로 자주 사용되는 컬럼에 대한 인덱스 생성

 

 

# 인덱스의 생성기준

 

1. 사용자의 SQL문에서 where 조건절에 자주 사용되는 컬럼이 대상이 된다.

2. 개수는 사용 형태에 따라 적절하게 생성한다.

3. 너무 많은 인덱스는 오히려 성능이 감소된다.

4. 빈번하게 변경되지 않는 테이블에 적용

5. 컬럼의 분포도가 2~4%인 경우 (10%기준)

6. 작은 테이블보단 큰 테이블에 인덱스를 생성한다.

 

 

 

출처 : docs.microsoft.com/ko-kr/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15

SQL Server 인덱스 아키텍처 및 디자인 가이드 - SQL Server

 

# 인덱스 설계 절차

인덱스는 특정 응용 프로그램을 위해서 생성되는 것이 아니다. 최소의 인덱스 구성으로 모든 접근 경로를 제공할 수 있어야 전략적인 인덱스 설계가 된다. 따라서 인덱스 선정은 테이블에 접근하는 모 든 경로를 수집하고 수집된 결과를 분석하여 종합적인 판단에 의해서 결정되는 것이 바람직하다.인덱스는 특정 애플리케이션을 위해서 생성되는 것이 아니다. 최소의 인덱스 구성으로 모든 접근 경로를 제공할 수 있어야 전략적인 인덱스 설계가 된다. 따라서 인덱스 선정은 테이블에 접근하는 모 든 경로를 수집하고 수집된 결과를 분석하여 종합적인 판단에 의해 결정하는 것이 바람직하다.

 

- 접근 경로 수집

접근 경로는 테이블에서 데이터를 검색하는 방법으로, 테이블 스캔과 인덱스 스캔 등이 있다. 접근 경로를 수집한다는 의미는 SQL이 최적화되었을 때 인덱스 스캔을 해야 하는 검색 조건들을 수집하 는 것이므로 데이터베이스 설계 시 혹은 완성되지 않은 프로그램에서 사용될 모든 접근 경로를 예측 하기는 불가능하다. 따라서 프로그램 설계서, 화면 설계 자료, 프로그램 처리 조건 등을 고려하여 예 상되는 접근 경로를 수집하여야 한다. 수집은 테이블 단위로 진행하고, 다음과 같은 점을 고려하여 접근 유형을 목록화한다.

 

- 반복 수행되는 접근 경로

대표적인 것이 조인 칼럼이다. 조인 칼럼은 FK 제약 대상이기도 하다. 주문 1건당 평균 50개의 주 문 내역을 갖는다면 주문 테이블과 주문 내역 테이블을 이용하여 주문서를 작성하는 SQL은 조인 을 위해 평균 50번의 주문 내역 테이블을 반복 액세스한다.

 

- 분포도가 양호한 칼럼

주문번호, 청구번호, 주민번호 등은 단일 칼럼 인덱스로도 충분한 수행 속도를 보장 받을 수 있는 후보를 수집한다.

 

- 조회 조건에 사용되는 칼럼

성명, 상품명, 고객명 등 명칭이나 주문일자, 판매일, 입고일 등 일자와 같은 칼럼은 조회 조건으 로 많이 이용되는 칼럼이다.

 

- 자주 결합되어 사용되는 칼럼

판매일 + 판매부서, 급여일+급여부서와 같이 조합에 의해서 사용되는 칼럼 유형을 조사한다.

 

- 데이터 정렬 순서와 그룹핑 칼럼

조건뿐만 아니라 순방향, 역방향 등의 정렬 순서를 병행하여 찾는다. 인덱스는 구성 칼럼 값들이 정렬되어 있어 인덱스를 이용하면 별도의 ORDER BY에 의한 정렬작업이 필요 없다. 동일한 원리 로 그룹핑 단위(GROUP BY)로 사용된 칼럼도 조사한다.

 

- 일련번호를 부여한 칼럼

이력을 관리하기 위해서 일련번호를 부여한 칼럼에 대해서도 조사를 실시한다. 마지막 일련번호를 찾는 경우가 빈번히 발생하므로 효과적인 액세스를 위해서 필요하다.

 

- 통계 자료 추출 조건

통계 자료는 결과를 추출하기 위해서 넓은 범위의 데이터가 필요하다. 따라서 다양한 추출 조건을 사전에 확보하여 인덱스 생성에 반영하여야 한다.

 

- 조회 조건이나 조인 조건 연산자

위에 제시되는 유형의 칼럼과 함께 적용된 =, between, like 등의 비교 연산자를 병행 조사한다. 이는 인덱스 결합 순서를 결정할 때 중요한 정보로 사용된다.

 

위에서 제시되는 칼럼들은 인덱스 생성이 필요한 칼럼들이다. 운영 중인 시스템을 대상으로 인덱 스를 설계할 때는 사용하고 있는 애플리케이션 내에 SQL 문장을 수집하거나 트레이스(Trace)를 사 용해 SQL들을 추출하여 접근 경로를 수집할 수 있다. 사후 작업은 많은 공수가 필요하지만 정확한 접근 경로를 추출할 수 있다. 따라서 설계 단계에서 위와 같은 칼럼으로 접근 경로로 추출해 인덱스 설계에 이용하고, 개발이 완료된 후 시스템 운영 초기나 성능에 문제가 발생되었을 때 인덱스 설계를 보완하는 것이 일반적인 형태이다.

 

출처 : www.dbguide.net/db.db?cmd=view&boardUid=13856&boardConfigUid=9&categoryUid=216&boardIdx=80&boardStep=1