1. 인덱스란?
책의 목차와 같이 원하는 데이터를 빠르게 찾을 수 있게 해주는 객체(Object) 이다.
목차가 없다면 원하는 정보를 찾기 위해 책의 첫 페이지부터 전체를 뒤져야 하지만 목차가 있다면 원하는 정보가 몇 페이지에 있는지 금방 찾을 수 있다.
이처럼 인덱스도 데이터를 찾을 때 전체를 다 뒤지기보다 목차를 통해 해당 데이터를 바로 찾을 수 있게끔 도와준다.
하지만, 인덱스를 사용한다고 해서 무조건 좋은 것은 아니다.
목차를 만들기 위해 책의 페이지가 필요하고, 책의 정보를 추가,수정,삭제하면 목차를 수정해야하는 리소스가 필요하듯 인덱스를 저장하기 위한 공간이 필요하고 데이터를 변경할때의 리소스가 필요하다. 또, 무분별하게 생성된 인덱스는 용량을 차지하고, 옵티마이저의 최적화를 낮추는 결과를 얻게 된다.
인덱스 장/단점
1) 장점
ㆍ 검색 속도가 향상된다.
ㆍ 쿼리 수행의 부하가 줄어 시스템 전체적인 성능이 향상된다.
2) 단점
ㆍ 인덱스 저장을 위한 추가 공간이 필요하다.
ㆍ 데이터의 변경 작업이 자주 일어날 경우, 성능 저하가 발생할 수 있다.
인덱스 컬럼에 데이터 변경 작업이 자주 일어나면 안되는 이유
1) 클러스터드 인덱스
-- TblUserInfo 테이블 생성
CREATE TABLE TblUserInfo (
ID int,
Name nvarchar(10),
Birth char(8)
)
GO
-- ID 컬럼에 Clustered Index 생성
CREATE CLUSTERED INDEX CIDX_TblUserInfo_ID ON dbo.TblUserInfo(ID)
GO| ID (KEY) | Name | Birth |
| 1 | 고길동 | 18991003 |
| 3 | 김둘리 | 19920524 |
| 4 | 양희동 | 19911115 |
| 5 | 한또치 | 20080706 |
위와 같은 테이블이 있다고 하자.
이 상태에서 아래와 같은 데이터를 삽입한다고 가정하자.
INSERT INTO TblUserInfo (ID, Name, Birth)
VALUES (2, "마이콜", "19891228")
그렇다면 해당 데이터는 ID 기준으로 정렬이 되어야 하므로, ID 가 1인 데이터 밑으로 들어가게 되며,
ID 가 3,4,5 인 데이터는 한 칸씩 아래로 이동시키게 될 것이다.
결국, INSERT 1건을 하기 위해 3건의 데이터를 이동시켜야하며 비용이 그만큼 더 크게 들게 된다. (UPDATE 도 동일)
이처럼 클러스터드 인덱스는 중간에 데이터가 들어오면 테이블에 재정렬이 발생하기 때문에 성능에 매우 큰 악영향을 끼치게 되므로,
클러스터드 인덱스 컬럼은 반드시 재정렬이 일어나지 않고 순차적으로 쌓이는 컬럼에 설정해야만 한다.
2) 넌클러스터드 인덱스
-- TblUserInfo 테이블 생성
CREATE TABLE TblUserInfo (
ID int,
Name nvarchar(10),
Birth char(8)
)
GO
-- ID 컬럼에 NonClustered Index 생성
CREATE NONCLUSTERED INDEX CIDX_TblUserInfo_ID ON dbo.TblUserInfo(ID)
GO
ID 컬럼을 넌클러스터드 인덱스로 갖는 테이블이 있다고 가정하자.
넌클러스터드는 아래와 같이 인덱스 테이블이 따로 존재하며, 각 데이터와 연결되어 있는 구조를 가질 것이다.
이 때 아래와 같은 데이터를 INSERT 한다고하면 어떻게 될까?
INSERT INTO TblUserInfo (ID, Name, Birth)
VALUES (2, "마이콜", "19891228")
데이터 테이블에는 정렬 상관없이 마지막 데이터 아래에 쌓이게 될 것이다.
인덱스는 정렬이 유지되어야 하기 때문에 ID 값이 1 밑에 데이터가 들어가게 되며, ID 가 3,4,5 인 데이터는 아래로 이동시키게 될 것이다.
클러스터드 인덱스와 동일하게 INSERT 1건을 하기 위해 3건의 데이터를 이동시켜야하며 비용이 그만큼 더 크게 들게 된다.
클러스터드 인덱스에 비해서 넌클러스터드 인덱스가 인덱스 페이지를 정렬하는데 부하는 적겠지만 (클러스터드 인덱스는 전체 데이터를 재정렬해야함)
인덱스로 선정될 컬럼이 자주 변경되는 값이라면, 인덱스 컬럼으로 적합하지 않다.
인덱스를 사용하면 좋은 경우
1) 데이터가 많은 테이블
2) DML(INSERT, UPDATE, DELETE) 이 자주 발생하지 않는 컬럼
3) JOIN, WHERE, ORDER BY 절에 자주 사용되는 컬럼
4) 데이터의 중복도가 낮은 컬럼
5) 조회 결과가 전체 데이터 수의 3~5% 이상
인덱스를 사용하지 않는 게 좋은 경우
1) 데이터가 적은 (수천건 미만) 테이블
2) DML(INSERT, UPDATE, DELETE) 처리가 많은 테이블
3) 조회 결과가 전체 행의 15% 이상 읽어들일 것으로 예상 되는 경우
2. 인덱스 구조
B*Tree (Balanced Tree) 기반 인덱스
최상단 페이지를 Root 라고 하며, 최하단 페이지를 Leaf 라고 하는 BTree 구조를 가진다.
2. 인덱스 종류
클러스터드 인덱스 (Clustered Index)
CREATE CLUSTERED INDEX CIDX_UserInfo_No ON dbo.TblUserInfo(No); -- No 컬럼에 클러스터 인덱스 설정
테이블 전체가 인덱스 컬럼에 대해 정렬이 되어 있는 인덱스이다.
즉, 리프 페이지에 실제 데이터 페이지를 가지게 된다.
찾으려는 No 를 먼저 Root Page 를 찾은 후, 해당하는 Leaf Page(Data Page) 의 주소값에 가서 일치하는 데이터를 바로 가져올 수 있다.
범위 검색에 유리하다.
또, 테이블 자체가 인덱스 컬럼에 대해 정렬이 되어 있기 때문에 한 테이블당 1개만 생성 가능하며,
마치 책 자체가 정렬이 되어 있는 백과 사전과 비슷하다.
행 데이터를 클러스터드 인덱스로 지정하면 자동 정렬이 된다.
Primary Key 설정 시 자동으로 생성되며, 데이터 변경 시 항상 정렬을 유지한다.
하지만, DML(INSERT,UPDATE,DELETE) 발생 시 데이터 재정렬이 필요하여 리소스가 발생한다.
넌클러스터드 인덱스 (Non-Clustered Index)
CREATE NONCLUSTERED INDEX IDX_UserInfo_No ON dbo.TblUserInfo(No); -- No 컬럼에 넌클러스터 인덱스 설정
원본 테이블과 매핑된 인덱스 객체를 별도로 생성하여 관리한다. (인덱스 컬럼이 정렬된 상태로 유지된다.)
Root Page / Leaf Page / Data Page 라는 세 가지 영역이 존재하며, Leaf Page 에는 실제 데이터가 아닌 데이터의 키 값을 저장한다. (RID)
Root 페이지에서 검색을 원하는 Leaf 페이지에 접근해서 원하는 주소값의 RID 값으로 데이터 페이지에서 데이터를 찾는다. (RID LookUp)
인덱스 영역과 데이터 영역이 분리되어 있다.
넌클러스터드 인덱스에는 RID LookUp 이라는 부가적인 연산이 필요하다. 이 과정으로 인해 클러스터드 인덱스보다 부가적인 연산이 필요하므로 성능이 떨어진다.
또, 별도의 인덱스 객체를 생성하기 때문에 한 테이블에 여러개 생성이 가능하다.
넌클러스터드 인덱스는 책의 목차/색인 과 비슷하다. 하나의 책에 대해 페이지 순으로 혹은 가나다 순으로 정렬을 할 수 있다.
클러스터드 인덱스와 달리 테이블의 페이지를 정렬하지 않고 새로운 공간(인덱스 객체) 를 할당하므로 클러스터 인덱스보다 더 많은 공간을 차지한다.
클러스터 + 비클러스터형 복합형 인덱스
비클러스터형 인덱스의 RID 값 대신 Clustered Index 키 값을 가지게 된다.
또, 탐색은 Clustered Index 의 Root Page 부터 탐색한다.
힙 (Heap)
인덱스가 없는 일반적인 테이블이다. 힙 테이블은 데이터를 저장할 때 인덱스를 사용하지 안흔다.
새 데이터가 삽입될 때마다 마지막 페이지 뒤에 데이터가 추가된다.
이러한 특성으로 힙 테이블은 삽입 작업은 빠르나, 데이터를 검색할 때는 느릴 수 있다.
또, 힙 테이블에서 데이털르 검색할 때 데이터를 찾기 위해 모든 페이지를 스캔해야할 수 있으므로, 이는 디스크I/O 를 유발할 수 있다.
고로, 힙 테이블은 작은 규모의 데이터 저장에 적합하며, 대규 데이터 저장 및 검색 작업에는 적합하지 않다.
3. 인덱스 생성
단일 인덱스
데이터가 많지 않고 조건에 걸리는 컬럼이 적은 경우에 주로 사용한다.
CREATE INDEX IDX_SINGLE (C1);
복합 인덱스
CREATE INDEX IDX_MULTI (C1,C2);
2개 이상의 컬럼을 인덱스 컬럼으로 지정한 인덱스이다. 선행 컬럼(C1) 정렬 후, 후행 컬럼(C2) 정렬이 된다.
복합 인덱스는 선행 컬럼 선정이 매우 중요한데 데이터의 분포도가 높고, 다양성이 많은 컬럼을 앞쪽에 배치하는 것이 좋다.
(성별/혈액형/혼인 여부는 다양성이 적고, 이름/주민등록번호/전화번호는 다양성이 높은 항목이다.)
다양성이 많은 컬럼을 앞쪽에 배치해야하는 이유는 선행 컬럼이 다양하지 않다면, 해당 하는 컬럼을 스캔해야할 횟수가 많아지기 때문이다.
아래와 같이 다양성이 낮은 -> 높은 순서로 인덱스를 구성했다고 가정해보자.
CREATE CLUSTERED INDEX CIDX_TblUserInfo ON dbo.TblUserInfo (성별,혈액형,이름)
| 성별 | 혈액형 | 이름 |
| 남 | A | 고길동 |
| 남 | A | 김둘리 |
| 남 | AB | 도우너 |
| 남 | O | 마이콜 |
| 남 | O | 사오정 |
| 여 | AB | 치타 |
| 여 | AB | 하니 |
| 여 | B | 희동이 |
이 상태에서 성별은 "남자", 혈액형은 "O형", 이름은 "사오정" 이라는 이름을 가진 데이터를 찾아본다고 하자.
이 때 성별에서부터 남자를 스캔하는데만 5번을 스캔하게된다.
이제 다양성이 높은 -> 낮은 순으로 인덱스를 구성했다고 하자.
CREATE CLUSTERED INDEX CIDX_TblUserInfo ON dbo.TblUserInfo (이름, 혈액형, 성별)
| 이름 | 혈액형 | 이름 |
| 고길동 | A | 남 |
| 김둘리 | A | 남 |
| 도우너 | AB | 남 |
| 마이콜 | O | 남 |
| 사오정 | O | 남 |
| 치타 | AB | 여 |
| 하니 | AB | 여 |
| 희동이 | B | 여 |
이름은 "사오정", 혈액형은 "O형", 성별은 "남자" 를 찾는다고 해보자.
이 때, 이름에서 "사오정" 을 먼저 찾으므로써, 스캔 횟수가 현저히 주는 것을 확인 할 수 있다.
고유 인덱스 (Unique Index)
CREATE UNIQUE INDEX UIDX_TblUserInfo_ID ON TblUserInfo (ID);
특정 컬럼에 고유 인덱스를 생성하면, 해당 컬럼에 중복 데이터를 허용하지 않도록 한다.
Unique 인덱스는 기본적으로 Non-Clustered Index 이다.
Clustered Index 로 생성하고 싶을 경우, Clustered 를 명시해주면 된다.
ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] UNIQUE CLUSTERED (컬럼명)
Unique 인덱스와 비슷한 개념으로 Unique 제약 조건이 있는데 이 둘의 차이는 아래와 같다.
ㆍ Unique 인덱스는 각 Row 마다 하나씩 유효성 검증하며, 중복 발생 그 즉시 Rollback 한다.
ㆍ Unique 제약조건은 DML 맨 마지막에 한꺼번에 검증하며, 모든 DML 처리 후, 중복 발생 시 전체를 Rollback 한다.
이 때 데이터가 적을 경우 중복 데이터를 한 번에 검증하는데 문제가 없다.
하지만,데이터가 많을 경우 중복이 발생하면 전체 롤백이 실행되기 때문에 성능 상 문제가 크다.
고로, 데이터의 양이 많을 경우 Unique 제약조건보다 Unique 인덱스를 사용하면 성능 상 효율이 좋다.
프라이머리 키(Primary Key)
ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] PRIMARY KEY ([컬럼명])
PK 는 기본적으로 Clustered Index 이다.
만약 Non-Clustered Index 로 생성하고 싶을 경우, 아래와 같이 명시해주면 된다.
ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] PRIMARY KEY NONCLUSTERED (컬럼명)
컬럼스토어 인덱스 (Columnstore Index)
Columnstore 인덱스는 컬럼 기반의 인덱스로서, 대량의 데이터 분석 쿼리의 성능을 향상시킬 수 있다.
Columnstore 인덱스는 매우 높은 수준의 데이터 압축(Rowstore 기반보다 10배) 을 제공하여 대량의 데이터 및 읽기 전용 쿼리를 수행하는 데이터 웨어하우스 작업에 효과적이다.
데이터 압축 비율이 높아 메모리 내 사용 공간이 감소되어 쿼리 성능이 향상된다.
일괄 처리 실행이 가능하여, 쿼리 성능을 일반적으로 2~4회 향상시킨다.
1) 클러스터형 Columnstore 인덱스
클러스터형 Columnstore 인덱스는 테이블의 모든 열을 컬럼 기반으로 저장하며, 이를 압축하여 저장한다.
이러한 방식으로 데이터를 저장하면 쿼리의 속도가 향상되는 효과가 있다.
클러스터형 Columnstore 인덱스는 기본적으로 읽기 전용(Read-Only) 로 설계되었으며,
새로운 데이터의 INSERT 및 UPDATE 는 일괄 처리 방식으로 처리된다.
이로 인해, 쓰기 작업에 대해서는 일반적인 Rowstore 인덱스보다 성능이 떨어진다.
고로, 클러스터형 Columnstore 인덱스는 대량의 데이터 조회 및 분석 작업에 대해서 효과적이다.
2) 비클러스터형 Columnstore 인덱스
클러스터형 Columnstore 인덱스와 다르게 전체 컬럼을 저장하는 것이 아니라, Rowstore 테이블에 데이터를 나누어 저장한다.
또, 비클러스터형 Columnstore 인덱스는 기존의 Rowstore 인덱스와 함께 사용할 수 있다.
데이터 저장 시 Columnstore 인덱스로 빠르게 쿼리하고, 데이터의 INSERT 및 UPDATE 는 Rowsotre 인덱스를 통해 처리가 가능하다.
이를 통해, OLTP 워크로드에서 Rowstore 인덱스를 사용하는 동시에 비클러스터형 Columnstore 인덱스로 데이터 분석을 동시에 사용할 수 있다.
필터링된 인덱스 (Filtered Index)
필터링된 인덱스는 특정 조건을 만족하는 데이터에만 인덱스를 생성하는 인덱스이다.
원하는 데이터에만 인덱스를 걸 수 있어서 인덱스 생성 시간을 단축하고 인덱스 크기를 줄이는 효과가 있다.
CREATE NONCLUSTERED INDEX [Index_Name]
ON [Table_Name]([Column1], [Column2], ...)
WHERE [Filter_Condition]
4. 인덱스 사용 시 주의할 점
LIKE 절 사용
LIKE 절에서 와일드카드(%,_) 가 검색어 앞에 사용하는 경우, 인덱스 검색 시작점과 끝점을 알 수 없기 때문에 인덱스를 타지 않는다.
(검색어 뒤에 % ,_가 오는 것은 인덱스 사용이 가능하다.)
NULL 사용
IS NULL 혹은 IS NOT NULL 과 같은 경우를 조건으로 검색하면 인덱스를 타지 않는다.
부정 연산자 사용
!=, <>, NOT IN, NOT EXISTS 와 같은 부정연산자를 사용할 경우, 검색 시작점과 끝점을 알 수 없기 때문에 인덱스를 타지 않는다.
인덱스 컬럼 가공
조건절에 있는 인덱스 컬럼에 함수를 씌우면 인덱스를 타지 않는다. (CONVERT, SUBSTR, ...)
묵시적 형 변환 사용
데이터 형식이 맞지 않아 발생하는 묵시적 형변환이 발생하면 인덱스를 타지 않는다.
OR 구문 사용
WHERE 절에서 인덱스가 있는 컬럼과 OR 구문으로 연동된 다른 컬럼을 조회할 경우, 인덱스를 타지 않게 된다.
OR 조건 대신 UNION ALL 로 SELECT 결과를 이어 붙이는 게 좋다.
JOIN 좌측 절
JOIN 의 조건을 걸때 좌측의 테이블에만 인덱스를 탑니다.
예를 들어 ON A.NAMES = B.NAMES 의 경우, A 테이블의 NAMES 컬럼에 인덱스가 있으면 인덱스를 타게 되고 A 테이블에 인덱스가 없을 경우 인덱스를 타지 않게된다.
'Database' 카테고리의 다른 글
| [Database] JOIN 기법의 종류 (Nested Loops Join, Merge Join, Hash Join) (0) | 2024.02.22 |
|---|---|
| [Database] 트랜잭션 격리 수준 (Transaction Isolation Level) (0) | 2023.10.23 |
| [Database] MVCC - 다중 버전 동시성 제어 (Multi Version Concurrency Control) (2) | 2023.10.18 |