[MSSQL/SQL SERVER] 인덱스

1. 인덱스가 뭘까?

 

 인덱스란 목차를 뜻하며 그 기능은 효율적인 조회에 이바지하는 것이다.

 보통 책에서 자신이 원하는 키워드나 파트를 찾을 때 책의 가장 앞에있는 목차 또는 가장 뒤쪽에 있는 범례를 보는데   SQL에서는 이 기능을 하는 것을 인덱스라 한다. 여기서 목차는 클러스터형 인덱스이고 범례는 비클러스터형 인덱스라   고 생각하면 쉽다.

---

2. 클러스터드와 비클러스터드 인덱스

 

클러스터형 인덱스는 각 테이블당 하나만 생성이 가능하다. 그 이유는 클러스터형 인덱스를 기준으로 테이블이 정렬이 되기 때문. SQL SERVER에서는 테이블 생성 시 PRIMARY KEY를 지정하면 클러스터형 인덱스로 자동 생성한다. 

 

비클러스터드 인덱스는 정렬이 되지 않는 인덱스이다. 그렇기 때문에 테이블당 다수의 비클러스터형 인덱스가 존재할 수 있다. 

 

정렬 유무의 차이 뿐 아니라 구조적으로도 두가지의 인덱스는 차이점을 가지고있다.

인덱스의 구조에서 클러스터드 인덱스는 끝 부분에는 테이블의 데이터 페이지 값을 가지고 있다. 그렇기 때문에 클러스터형 인덱스를 통한 검색은 인덱스의 컬럼을 제외한 내용을 출력해도 LOOKUP 이라는 절차가 따로 필요하지 않다. 하지만 비클러스터드 인덱스는 인덱스의 끝 부분에 클러스터드 인덱스의 키값 또는 RID 값을 가지고있다. 그렇기 때문에 비클러스터형 인덱스의 컬럼이 아니면서 클러스터형 인덱스의 컬럼이 아닌 값을 출력하고자 하면 옵티마이저는 LOOKUP을 통해 해당 값들을 가져와야한다.

 

EX) 테이블 구조 : COL1, COL2 COL3 

클러스터형인덱스(IDX1) : COL1

비클러스터형인덱스(IDX2) : COL2

조회 조건이 인덱스를 충분히 사용 가능하다는 전제하에

SELECT COL1 FROM TABLE WHERE COL1 > 0                    IDX1 SEEK

SELECT COL2 FROM TABLE WHERE COL2 > 0                    IDX2 SEEK

SELECT COL2 FROM TABLE WHERE COL1 > 0                    IDX1 SEEK

SELECT COL1, COL2 FROM TABLE WHERE CO2  > 0            IDX2 SEEK

SELECT * FROM TABLE WHERE COL2 > 0  ->                     IDX2 SEEK + KEY LOOK UP

SELECT * FROM TABLE WHERE COL3 > 0                          CLUSTERED SCAN

---

3. 인덱스 옵션들

 

3-1 복합인덱스

인덱스에 2개 이상의 컬럼을 설정한 경우. 복합인덱스를 사용할 때는 최소한 첫번째 인덱스 조건을 사용해야 인덱스를 탈 수있다.

 

3-2 포괄인덱스

CREATE NONCLUSTERED INDEX 인덱스명
      ON 테이블명(컬럼명1)
      INCLUDE (컬럼명2, 컬럼명3) --포괄열 지정
포괄 열이 있는 인덱스는 비클러스터형 인덱스의 리프 페이지에 데이터를 포함하고 있는 열을 의미한다. 

include 옵션으로 조회 조건이 아닌 출력조건에 사용되는 컬럼을 지정 -> 룩업을 방지할 수 있다.

 

* 포괄 열이 있는 인덱스는 비클러스터형 인덱스에만 생성할 수 있다.
* 포괄 열이 있는 인덱스는 인덱스의 크기가 커지는 단점이 있다.
* 포괄 열이 있는 인덱스 생성 후에, 일부 SELECT문의 성능이 급격히 향상될 수 있다.
* 포괄 열이 있는 인덱스가 있더라도 SELECT의 열이 그 포괄 열에 포함되지 않으면 인덱스는 사용되지 않는다.

 

3-3 필터드인덱스

where 조건으로 인덱스 조회의 조건을 지정 가능 ex) 컬럼의 대부분이 null인 경우 where col is not null 을 지정하여 null을 제외한 인덱스 사용 가능

 

3-4 유니크인덱스

유니크 = 중복데이터가 없다, null이 가능(하지만 중복은 안됨), 비클러스터드 클러스터드에 둘다 적용 가능

---

인덱스의 랜덤 액세스
랜덤 엑세스는 적을수록 좋다.
OR 조건 -> 조건이 추가될 때마다 처리 대상이 계속적으로 확대되기때문에 옵티마이저는 FULL SCAN이 유리하다고 판단한다.
AND 조건 -> 조건이 추가될 때마다 처리 대상의 집합은 감소한다. 이 경우 옵티마이저는 인덱스를 활용하여 SQL을 수행한다

 

(OR조건을 많이 사용해야 할 경우 AND 조건과 UNION을 사용하는 것도 방법 중 하나)