인덱스

데이터베이스

인덱스

growdeveloper 2021. 4. 23. 17:21

인덱스의 핵심

인덱스를 생성하면 검색의 속도가 무척 빨라질 수 있다.
인덱스에는 클러스터형 인덱스와 비 클러스터형 인덱스가 있다.
클러스터형 인덱스는 영어 사전으로, 비클러스터형 인덱스는 책 뒤의 찾아보기로 비유할 수 있다.
Primary key, Unique를 설정한 열에는 자동으로 인덱스가 생성된다.
인덱스는 B-Tree 구조를 갖는다.
클러스터형 인덱스는 테이블에 1개만 생성이 가능하며 비클러스터형 인덱스는 테이블에 여러 개를 생성할 수 있다.
클러스터를 생성하거나 삭제할 때는 CREATE INDEX/DROP INDEX 문을 사용할 수 있다.
클러스터형 인덱스가 보조 인덱스보다 검색 성능이 더 좋다.

▶ 인덱스 구조

루트 레벨/ 루트 페이지

인덱스 페이지의 최상위 수준을 루트 레벨(Root Level)이라 하고, 루트 레벨에 포함된 인덱스 페이지를 '루트 페이지(Root Page)' 라 한다. 루트 페이지는 당연히 하나다. 인덱스 검색은 이 루트 페이지에서 시작된다. 루트 페이지를 맨 먼저 찾아야 하므로 SQL Server는 각 인덱스의 루트 페이지 정보를 따로 관리한다.

리프 레벨 / 리프 페이지/ 퍼스트 페이지

인덱스 페이지의 최하위 수준을 '리프 레벨(Leaf Level) 이라이라하고'하고' 리프 레벨에 포함된 인덱스 페이지를 '리프 페이지(Leaf Page)'라 한다. 첫 번째 리프 페이지는 '퍼스트 페이지( First Page)' 라 한다.

인터미디어트 레벨

최상위 수준과 최하위 수준 사이를 '인터미디어트 레벨(Intermediate)이라 한다. 인터미디어트 레벨은 인덱스 크기에 따라 존재하거나 존재하지 않을 수 있다. 인터미디어트 레벨에 새로운 인터미디어트 레벨이 추가되는 것은 생각보다 쉽게 발생하지 않는다. 엄청난 양의 데이터가 추가되어야 인터미디어트 레벨이 하나 추가된다.'

▶ 테이블 존재 형태

테이블 존재 형태는 '힙(Heaps)[더미 무더기]' 형태와 클러스터형 인덱스(Clustered Indexes)' 형태 중 하나다.

힙 : 데이터 페이지와 페이지 안의 데이터가 순서 없이 존재하는 테이블을 '힙'이라 한다. 힙에 행을 추가하면, 중간의 여유 공간, 만일 여유 공간이 없으면 맨 뒤에 저장한다. 그래서 Insert 문은 힙이 좋다. 새로운 행을 아무 데나 저장하면 되니 Insert 문이 어찌 힙을 싫어할 수 있겠는가?
테이블 스캔 : SELECT 문은 힙이 정말 싫다. 존재함에 순서가 없으니, 테이블 전체를 뒤지는 것이다. 이를 테이블 스캔(Table Scan)이라 한다. 데이터가 적다면 몰라도 데이터가 많다면 테이블 스캔은 쿼리문의 성능을 떨어뜨린다.
힙과 인덱스 : 힙과 인덱스 힙에서 데이터를 찾을 때 테이블 스캔을 피하기 위한 제일 나은 방법은 무엇일까? 가장 빈번하게 되는 열을 기준으로 데이터를 정렬하면 된다. 예를 들어 영어 사진이 단어를 기준으로 정렬되어 찾기 쉽듯, 힙 데이터를 정렬하면 찾기 쉽다. 예를 들어 고객 테이블에서 이름으로 많이 찾으면 이름을 기준으로, 아이디로 많이 찾으면 아이디를 기준으로 정렬하면 된다. 클러스터형 인덱스를 만들면 데이터가 정렬된다. 이 과정으로 힙은 정렬되며, 정렬된 형태의 새로운 테이블이 된다. 그래서 클러스터형 인덱스가 테이블의 또 다른 존재 형태이다. 그럼 데이터를 정렬하지 않고 테이블 스캔을 피할 방법은 없을까 찾는 데이터가 어디 있는지 몰라 테이블을 스캔하니. 어디 있는지 알려주면 스캔하지 않아도 될 것이다. 찾는 데이터가 어디 있는지 알려주는 것, 이것이 또 다른 형태의 인덱스다, 클러스터형 인덱스와 구분하기 위해 '비클러스터형 인덱스' 라 한다.

클러스터형 인덱스 구조

use test_db;
Create table clusterTbl
(
	userId char(8) not null,
	name nvarchar(10) not null
)
go
insert into clusterTbl values('lsg','이승기');
insert into clusterTbl values('kbs','김범수');
insert into clusterTbl values('kkh','김경호');
insert into clusterTbl values('jyp','조용필');
insert into clusterTbl values('ssk','성시경');
insert into clusterTbl values('ljb','임재범');
insert into clusterTbl values('yjs','윤종신');
insert into clusterTbl values('ejw','은지원');
insert into clusterTbl values('jkw','조관우');
insert into clusterTbl values('bbk','바비킴');

select * from dbo.clusterTbl

그림 1 힙 및 데이터 페이지 및 데이터 결과

가장 먼저 클러스터드 인덱스를 실행하지 않고 다음과 같이 실행했을 때 인설트 순서대로 데이터가 정렬된 것을 확인할 수 있다.
이때 데이터들은 힙 영역에 정렬되지 않은 형태로 저장되어 있다.

클러스터형 인덱스 만들기

create clustered index cl_userid
	on clustertbl (userid)
    
select * from dbo.clusterTbl

위의 그림과 같이 UserID를 기준으로 정렬된 기준으로 볼 수 있다. 즉 영어사전과 같이 A~Z 순서대로 정렬된 상태로 조회를 할 수 있다고 할 수 있다.
이렇듯 클러스터드 인덱스를 만들경우 정렬된 상태의 데이터를 조회한다고 볼 수 있다.

비클러스터형 인덱스

use test_db;
Create table nonclusterTbl
(
	userId char(8) not null,
	name nvarchar(10) not null
)
go
insert into nonclusterTbl values('lsg','이승기');
insert into nonclusterTbl values('kbs','김범수');
insert into nonclusterTbl values('kkh','김경호');
insert into nonclusterTbl values('jyp','조용필');
insert into nonclusterTbl values('ssk','성시경');
insert into nonclusterTbl values('ljb','임재범');
insert into nonclusterTbl values('yjs','윤종신');
insert into nonclusterTbl values('ejw','은지원');
insert into nonclusterTbl values('jkw','조관우');
insert into nonclusterTbl values('bbk','바비킴');

select * from dbo.nonclusterTbl


ALTER TABLE nonclusterTbl ADD Constraint UK_nonclusterTbl_userId Unique (userId);

ALTER 문을 통해 비클러스터형 인덱스를 생성.
그림 1 힙 및 데이터 페이지와 같은 상태의 데이터 페이지를 생성한다 및 결과를 얻을 수 있다.
왜 이렇게 결과가 나오는지 그림으로 확인해봐야 한다.

비클러스터형 인덱스는 데이터 페이지를 건드리지 않는다.
별도의 장소에 인덱스 페이지를 생성한다.
인덱스 페이지의 리프 페이지에 인덱스로 구성된 열을 정렬한 후 데이터 위치 포인터를 생성한다.
데이터의 위치 포인터는 클러스터형 인덱스와 달리 '페이지 번호 + 오프셋'이 기록 바로 데이터의 위치를 가리키게 된다.

데이터 위치 포인터를 RID(Row Id)라고 부른다. 또한, 실제로는 해당하는 행의 익스텐트 번호+페이지 번호+행의 순번(오프셋)을 합쳐져서 만들어진다. 예로 들든 비클러스터형 인덱스는 테이블에 클러스터형 인덱스가 존재하지 않고, 비클러스터형 인덱스만 존재한다는 가정하의 예다. 한 테이블에 클러스터형 인덱스와 비클러스터형 인덱스가 함께 존재한다면 얘기가 조금 달라진다.

페이지 분할

인덱스를 구성하면 데이터를 변경(INSERT, UPDATE, DELETE) 할 때 성능이 나빠지는 단점이 있다. 특히 INSERT 작업이 일어날 때 성능이 급격히 느려진 수 있다. 그 이유는 페이지 분할이라는 작업이 발생되기 때문이다.

클러스터 인덱스에 새로운 데이터를 입력해보자.

INSERT INTO CLUSTERTBL VALUES('fnt', '푸니타');
INSERT INTO CLUSTERTBL VALUES('kai', '카이');

첫 번째 리프 페이지(데이터 페이지)가 페이지 분할이 생김.
데이터를 공평하게 분배한 후에, 루트 페이지에 등록
루트 페이지의 순서가 약간 변경되기는 했지만 페이지 분할에 비해서 같은 페이지 내에서의 순서 변경은 시스템에 영향이 미미. (페이지 분할은 시스템에 많은 부하를 발생시킨다.)

비클러스터형 인덱스에 새로운 데이터를 입력해 보자.

INSERT INTO nonclusterTbl VALUES('fnt', '푸니타');
INSERT INTO nonclusterTbl VALUES('kai', '카이');

비클러스터형 인덱스는 데이터 페이지를 정렬하는 것이 아니다.
그냥 데이터 페이지의 뒤쪽 빈 부분에 삽입된다.
인덱스의 리프 페이지에도 약간의 위치가 조정된 것뿐 페이지 분할은 일어나지 않았다.
클러스터형 인덱스보다 데이터 입력에서는 성능에 주는 부하가 더 적다.

클러스터형 인덱스와 비클러스터형 인덱스가 혼합되어 있는 경우

현재까지는 테이블에 클러스터형 인덱스만 있거나, 비클러스터형 인덱스만 있는 경우를 살펴보았다. 하지만, 현실적으로 하나의 테이블에 클러스터형과 비클러스터형의 인덱스가 혼합된 경우가 더 많다.

use test_db;
Create table mixedTbl
(
	userId char(8) not null,
	name nvarchar(10) not null,
	addr nchar(2)
)
go
insert into mixedTbl values('lsg','이승기','서울');
insert into mixedTbl values('kbs','김범수','경남');
insert into mixedTbl values('kkh','김경호','전남');
insert into mixedTbl values('jyp','조용필','경기');
insert into mixedTbl values('ssk','성시경','서울');
insert into mixedTbl values('ljb','임재범','서울');
insert into mixedTbl values('yjs','윤종신','경남');
insert into mixedTbl values('ejw','은지원','경북');
insert into mixedTbl values('jkw','조관우','경기');
insert into mixedTbl values('bbk','바비킴','서울');

▶위의 쿼리를 실행했을 때 아래와 같은 힙 영역이 생성될 것이다.

ALTER TABLE mixedTbl add constraint pk_mixedtbl_userid primary key(userid);

▶위의 쿼리를 사용해 클러스터형 인덱스를 생성한 후

▶Unique 제약 조건으로 비클러스터형 인덱스를 추가. 및 프로시저를 사용하여 인덱스를 확인하자.

alter table mixedTbl add constraint uk_mixedtbl_name unique (name);
EXEC sp_helpindex mixedTbl;

클러스터형 인덱스인 경우에는 그대로 변함이 없다. 하지만 이외인 것이 비 클러스터형 인덱스다
비클러스터형 인덱스의 루프 페이지와 리프 페이지의 키 값(여기서는 name 열)이 이름으로 구성되었으므로 일단 이름으로 정렬되었다.
여기서 주목해야 할 점은 비클러스터형의 리프 페이지다.
클러스터형 인덱스 페이지가 없었다면 아마도 RID 값으로 구성되어 있었을 것이지만 지금은 클러스터형 인덱스의 키 값(여기서는 userId)을 가지게 된다.
한 가지 중요한 점은 비클러스터형 인덱스를 검색한 후에는 그림에 표현된 것처럼, 모두가 다시 클러스터형 인덱스의 루트 페이지부터 검색한다는 점이다.

SELECT ADDR FROM MIXEDTBL WHERE NAME='임재범';

다음과 같은 쿼리를 실행했을 때의 순서를 살펴보자.

페이지 번호 10번을 읽음, 비클러스터형 인덱스의 루트 페이지에서 은지원보다 큰 값이므로 200번 페이지에 있다는 것을 확인한다.
페이지 번호 200번을 읽음 임재범은 클러스터형 인덱스의 키 값 LJB임을 확인한 후, 무조건 클러스터 인덱스의 루트 페이지로 가서 찾는다.
페이지번호 20번 읽음 'LJB'은 'KBS'보다 크고 'SSK'보다 작으므로 1001번 페이지에 있는 것을 확인한다.
페이지번호 1001번 읽음 'LJB'값을 찾고, 그 주소인 '서울'을 찾아낸다.

★ 왜 이렇게 구성했을까??

비클러스터형 인덱스의 리프 페이지에 기존처럼 RID로 하면 검색이 더 빠를 텐데.....
클러스터형 인덱스와 비클러스터형 인덱스를 분리해서 서로 관련 없이 구성한다면 검색에서는 더 우수한 성능을 보일 것이다.
하지만!!!!!!!!!!
치명적인 단점이 여기서 발생한다.
여기서 userid = KHJ, name = 김현중, arr = 서울행이 추가된다고 생각해보자.
클러스터형 인덱스는 페이지 분할 등의 작업이 발생될 것이다.
그리고 비클러스터형 인덱스에도 100번 페이지에만 김현중 KHJ가 추가되면서 데이터의 순서가 약간 변경될 뿐 그렇게 큰 변화가 발생하지는 않을 것이다.
만약에 RID 값으로 되어 있다고 가정했을 때 데이터의 삽입으로 인해 클러스터형 인덱스의 리프 페이지(데이터 페이지)가 재구성이 되어서 '데이터 페이지 번호' 및 '#오프셋' 이 대폭 변경된다 그러면 단 한건의 행 삽입으로 데이터 페이지 (클러스터형 리프 페이지)의 페이지 번호 및 오프셋이 대폭 변경되므로 비클러스터형 인덱스 역시 많은 부분이 다시 구성되어야만 한다. 엄청난 시스템의 부하를 발생시킬 소지가 있다.
그래서 비클러스터형 인덱스와 클러스터형 인덱스가 하나의 테이블에 모두 존재하는 경우네는 비클러스터형을 검색한 후에 다시 클러스터형을 검색해야 하므로 약간의 손해를 볼 수도 있지만, 데이터의 삽입 때문에 비클러스터형 인덱스를 대폭 재구성하게 되는 큰 부하는 걸리지 않는다.

1.Table Scan: 테이블 검색, 데이터 페이지를 처음부터 끝까지 찾아본다는 의미로, 인덱스가 없을 경우 거나 인덱스가 있어도 그냥 테이블을 찾아보는 것이 빠른 경우다.

2.Index seek : 비클러스터형 인덱스에서 데이터를 찾아본다는 의미이다.

3.RID Lookup: 비클러스터형 인덱스에서 키를 검색한 후에, 실제 데이터 페이지(Heap)를 찾아본다는 의미이다.

4.Clustered Index Seek : 클러스터형 인덱스에서 데이터를 찾아본다는 의미이다.

5.Key Lookup: 비클러스터형 인덱스에서 키를 검색한 후에 클러스터형 인덱스에서 데이터를 찾아본다는 의미이다. 결국 Clustered Index seek와 작동 방식이 비슷하다.

6. Clustered index Scan : Table Scan과 비슷한 개념으로 전체를 찾아본다는 의미다. 클러스터형 인덱스의 리프 페이지는 결국 데이터 페이지이기 때문이다.

▶ 물리적인 정렬이 아니다

클러스터형 인덱스는 데이터 페이지가 정렬되어 있고, 데이터 페이지 안의 행이 정렬되어 있다고 했다. 이때 정렬이란 물리적인 정렬이 아닌. 단지 논리적인 정렬을 의미한다.

결론

인덱스는 열 단위에 생성된다
- 하나의 열에만 생성되는 것이 아니라 두 개 이상의 열을 조합해서 인덱스를 생성할 수 있다.
where 절에서 사용되는 열에 인덱스를 만들어야 한다.
- 테이블을 조회 시에 인덱스를 사용하는 경우는 WHERE 절의 조건에 해당 열이 나오는 경우에만 사용된다.
Where 절에 사용되더라도 자주 사용해야 가치가 있다.
- select 문은 아주 가끔만 사용되고 주로 INSERT 작업만이 일어나고 클러스터형 인덱스라면 인덱스로 인해서 성능이 데이터를 입력하는 성능이 무척 나빠질 수 있다.
데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효용이 없다.
- 데이터의 종류가 별로 없고 거의 같은 데이터가 있는 열은 비클러스터형 인덱스를 만들어도 SQL SERVER가 사용하지 않는다. 그러므로 만들지 않는 게 더 낫다. 경우에 따라 다르겠지만 중복도가 1~3% 이상이라면 인덱스를 만들지 않는 것이 낫다.
외래 키가 사용되는 열에는 인덱스를 되도록 생성해주는 것이 좋다.
- 외래 키 제약 조건이 열에는 자동으로 인덱스가 생성되지 않는다. 그러므로 인덱스를 직접 생성해야 한다.
Join에 자주 사용되는 열에는 인덱스를 생성해 주는 것이 좋다.
클러스터형 인덱스 는 테이블당 하나만 생성할 수 있다.
- 클러스터형 인덱스를 생성할 열은 범위(BEETWEEN, >, < 등의 조건)로 사용하거나, 집계 함수를 사용하는 경우네는 아주 적절하다. 클러스터형 인덱스는 데이터 페이지를 읽는 수가 최소화되어서 성능이 아주 우수하므로 가장 많은 조건에서 사용되는 열에 생성하는 것이 바람직하다, 또한, ORDER BY 절에 자주 나오는 열도 클러스터형 인덱스가 유리하다. 클러스터형 인덱스의 데이터 페이지(리프 페이지)는 이미 정렬되어 있기 때문이다.
- 범위로 자주 조회하는 열이 두 개 이상이라면 하나는 클러스터형 인덱스를 생성하고 다른 하나는 '포괄 열이 있는 인덱스'로 생성하면 된다.
클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있다.
- 클러스터형 인덱스로 구성되었으므로 데이터가 입력되는 즉시 정렬이 계속 수행되고 페이지 분할이 끊임없이 일어나게 될 수도 있어서, 시스템의 성능에 문제가 심각해질 수도 있다. 이런 경우네는 차라리 클러스터형 인덱스가 없는 편이 더 나을 수도 있다. 그러려면 테이블을 정의할 때 NONCLUSTERED로 지정하면 된다.
사용하지 않는 인덱스는 제거하자
- 운영되는 운영 프로그램의 쿼리들은 분석해서 WHERE 조건에서 사용되지 않는 열 들의 인덱스는 제거할 필요가 있다. 그러면, 공간을 확보할 뿐 아니라 데이터의 입력 시에 발생되는 부하도 많이 줄 일 수 있다.
계산 열에도 인덱스를 활용할 수 있다.
- 계산 열이란 계산된 열로 써 기본적으로는 데이터가 실제로 존재하지는 않지만 테이블 생성 시에 PERSISTED 키워드를 붙이면 실제 테이블에 데이터를 저장할 수 있다.
- CREATE TABLE COMPUTETBL (input1 INT, INPUT2 INT, HAP AS INPUT1+INPUT2 PERSISTED);