SQL(11) INDEX

인덱스란? 

: 데이터를 좀 더 빠르게 찾을 수 있도록 만든 데이터 구조.

- 데이터의 저장(INSERT, UPDATE, DELETE)의 성능을 희생하고,

대신에 데이터의 읽기 속도를 높여 테이블의 동작 속도를 높여준다.

- 인덱스가 없어도 데이터베이스를 작동하는데 있어서 문제는 없지만,

데이터베이스의 크기가 커지면 커질수록 데이터베이스의 성능을 위해 인덱스가 반드시 필요해진다.

---

인덱스의 특징

- 테이블에서 한 개 이상의 속성을 이용하여 인덱스를 생성한다.

- 인덱스를 사용하면 매우 빠른 응답 속도를 얻을 수 있고 쿼리의 부하가 줄어들어 시스템 전체 성능이 향상된다.

- 데이터베이스에 인덱스를 위한 전체 크기의 10%나 되는 추가적인 공간을 할당해줘야 하고,

  잘못 사용할 경우 성능이 오히려 크게 떨어질 수 있다.

  (ex. 변경 작업이 자주 일어나는 경우, 인덱스가 적절하지 않은 경우...) -> 효율적인 인덱스 설계 필요!

- SELECT 검색 속도를 크게 향상시킨다.

- INSERT, UPDATE, DELETE 같은 데이터 변경 쿼리가 잦은 경우 paging 및 인덱스의 재구성이 빈번해져 성능이 악화될 수 있다.

- 인덱스의 각 노드들은 키만 가지고 있기 때문에 테이블보다 작은 공간을 차지한다.

- 일반적으로 B- tree 형태의 구조를 갖는다. 

---

B- tree

: 데이터의 검색 시간을 단축하기 위한 자료구조

- B tree는 Balanced tree로, 모든 리프노드들이 같은 레벨을 갖도록 밸런스를 맞춰준다.

 

- 루트 노드: 트리의 가장 상위에 있는 노드

- 내부 노드: 중간에 있는 노드

- 리프 노드: 가장 말단에 있는 노드

* 노드: 트리구조에서 데이터가 존재하는 공간(= 페이지)

- 개념적으로는 노드라 하지만, MariaDB에서는 노드가 페이지가 된다.

- 16Kbyte 크기의 최소한의 저장 단위. 아무리 작은 데이터를 한 개만 저장하더라도 한 개의 페이지(16Kbyte)를 차지한다.

 

1.B-tree의 특징 

- 각 노드는 키(위 그림의 파란 부분)와 포인터(위 그림의 하얀 부분)를 가지며,

  키를 기준으로 오름차순으로 정렬되어 저장되어 있다. (왼쪽이 오른쪽 키값보다 작다.)

- 모든 검색은 루트 노드에서부터 시작해서 내부 노드를 지나 리프 노드까지 내려가면서 이루어진다.

- B-tree는 키 값이 새로 추가되거나 삭제될 경우 동적으로 노드를 분할하거나 통합해서 항상 균형 상태를 유지한다.

- B-tree는 데이터를 검색할 때 특유의 트리 구조를 이용하기 때문에

  한 번 검색할 때마다 검색 대상이 줄어 접근 시간이 적게 걸린다.

  -> 따라서 주요 DBMS에서는 인덱스의 기본 구조로 B-tree를 많이 사용한다.

- 하지만 데이터의 변경이나 추가가 잦을 경우 B-tree 모양을 유지하기 위해 노드의 분할 및 이동이 자주 발생하는 문제가 있다.

 

---

인덱스의 구분

- 클러스터 인덱스, 보조 인덱스로 구분할 수 있는데, 두 인덱스 모두 B-tree 인덱스를 기본으로 한다.

- 클러스터 인덱스는 영어 사전과 같이 처음부터 정렬이 되어 있는 개념이고, 보조 인덱스는 책 뒤의 찾아보기와 같은 개념이다.

1. 클러스터 인덱스

: 연속된 키값의 레코드들을 묶어서 같은 블록에 저장하는 방법

(1) 클러스터 인덱스의 특징

- 기본적으로 하나의 테이블 당 하나의 클러스터 인덱스만 생성 가능하다.

- 클러스터형 인덱스 생성 시에는 데이터 페이지 전체가 다시 정렬된다.

   -> 하지만 이런 특징으로 인해, 대용량 데이터가 입력된 상태라면 클러스터형 인덱스 생성은 심각한 시스템 부하를 줄 수 있다.

- 보조 인덱스보다 검색 속도는 빠르지만, 입력/수정/삭제는 느리다.

- MySQL에서는 primary key가 있다면 primary key를 클러스터 인덱스로,

  없으면 unique하면서 not null인 컬럼을,

  이것도 없으면 임의로 보이지 않는 컬럼을 만들어 클러스터 인덱스로 지정한다.

- 테이블 생성 시 기본키를 생성하면 자동으로 클러스터 인덱스가 생성된다.

  데이터를 넣고 나중에 기본키를 생성하면 데이터를 다시 키값을 기준으로 정렬해서 저장해야 하기 때문에 시간이 더 오래걸린다.

- 리프 노드에서 페이지에 데이터의 위치를 저장하는 것이 아니라 데이터 그 자체를 저장한다.

 

(2) 클러스터 인덱스 예시

- 아래는 클러스터 인덱스를 생성한 예시이다.

인덱싱을 하면 루트 페이지가 만들어지는데, 각 리프 페이지의 첫번째 데이터만 모아 매핑시킨다.

루트 페이지는 리프 페이지에 연결되며, 리프 페이지는 자동 정렬이 된다.

 

1) 클러스터 인덱스를 이용한 단일 데이터 조회

- 조관우를 찾고 싶다면, 두 페이지만 거치면 조회가 가능하다.

 

2) 클러스터 인덱스를 이용한 범위 데이터 조회

- 아이디가 A~J인 사용자를 모두 조회하려고 해도 두 페이지만 거치면 조회가 가능하다.

 

3) 클러스터 인덱스를 이용한 데이터 삽입

- 데이터를 삽입하려는데 1000번 페이지에 공간이 부족해 페이지 분할이 일어나 2000번 페이지가 생겨난다.

- 정렬으로 인해 삽입, 삭제 등을 할 때는 페이지 분할이나 추가적인 정렬이 필요하여 성능이 오히려 나빠지기도 한다.

INSERT INTO clustertbl VALUES('FNT', '푸니타');
INSERT INTO clustertbl VALUES('KAI', '카아이');

 

2. 보조 인덱스 (= 논 클러스터 인덱스)

(1) 보조 인덱스의 특징

- 각 데이터에 대해 고유값(unique)들이 있는 목록에 생성할 수 있는 인덱스이다.

- 보조 인덱스 생성 시에는 데이터 페이지는 그대로 둔 상태에서 별도의 페이지에 인덱스를 구성한다.

  -> 별도의 페이지에서 인덱스를 구성하니, 클러스터와 달리 자동 정렬이 필요 없다.

- 클러스터형 인덱스보다 검색 속도는더 느리지만 데이터의 입력/수정/삭제는 더 빠르다.

- 여러 개의 보조 인덱스를 생성할 수 있지만, 남용할 경우에는 오히려 성능을 떨어뜨릴 수 있다.

 

(2) 보조 인덱스 예시

- 아래는 보조 인덱스를 생성한 예시다.

보조 인덱스 역시 루트 페이지가 만들어지지만,

데이터 페이지에 바로 연결시키는 것이 아니라 리프 페이지(RID)를 따로 만들어서 매핑하고 정렬시킨다.

이와 같이 추가 공간이 필요하므로 인덱스를 남용하면 공간 낭비로 이어질 수 있다.

데이터 페이지에는 변화를 주지 않으므로 클러스터 인덱스와 달리 여러개 생성 가능하다.

 

1) 보조 인덱스를 이용한 단일 데이터 조회

- 조관우만 찾고 싶어도 3페이지를 거쳐야 조회할 수 있다.

- 만약 데이터가 아주 클 경우, 클러스터 인덱스와의 페이지 조회수 차이가 훨씬 커질 것이며 그만큼 성능도 저하될 것이다.

 

2) 보조 인덱스를 이용한 범위 데이터 조회

- 아이디가 A~J인 사용자를 검색하려면 5 페이지나 거쳐야 조회가 가능하다.

3) 보조 인덱스를 이용한 데이터 삽입

- 데이터 페이지의 빈 곳에 데이터를 넣고 리프 페이지에서 매핑만 하면 되기 때문에 페이지 분할이 일어나지 않는다.

  -> 삽입 측면에서 클러스터 인덱스보다 성능 이점을 갖는다.

INSERT INTO clustertbl VALUES('FNT', '푸니타');
INSERT INTO clustertbl VALUES('KAI', '카아이');

 

 

3. 클러스터 인덱스 + 보조 인덱스

(1) 클러스터 인덱스 생성

ALTER TABLE mixedtbl
ADD CONSTRAINT PK_mixedtbl_userID PRIMARY KEY (userID);

 

(2)  클러스터 인덱스에 보조 인덱스 생성                                                           

                                                                                                                                       

(3) 클러스터 + 보조 인덱스 조회

- 임재범을 조회한다고 해보자.

SELECT addr FROM mixedtbl WHERE name = '임재범';

- 총 4페이지를 읽게 된다.

위와 같은 형태를 이용하는 이유는,

데이터 삽입/삭제에 있어 보조 인덱스 페이지의 부담을 많이 줄이기 위해서이다.

'클러스터 인덱스 + 보조 인덱스' 구조에서는 보조 인덱스의 리프 페이지에 데이터의 주소(RID)가 아닌 PK를 저장한다.

 

RID를 저장하면 INDEX를 끝까지 탐색하고 바로 데이터로 가니까 3페이지만 거치면 데이터를 찾을 수 있는데, PK를 저장하면 총 4페이지를 거쳐야 한다. 이 말은 PK를 저장하면 성능이 저하된다는 뜻인데, 그럼에도 불구하고 PK를 저장하는 이유는, RID를 저장하게 되면 삽입/삭제를 할 때 엄청나게 많은 데이터들의 RID가 바뀌게 될 것이다. 하지만 PK를 저장해놓는다면 약간의 정렬만 해주면 된다. 즉, 검색으로 얻는 이득보다 삽입/삭제 시 잃는 성능이 더 크기 때문에 PK를 저장하도록 구성된 것이다.

 

---

인덱스 설계 및 사용의 핵심

1. 효율적인 인덱스 설계 

-  WHERE절에 자주 사용되는 열

-  JOIN절에 자주 사용되는 열

- SELECT 절에 자주 등장하는 컬럼들을 잘 조합해서  INDEX로 만들어 두면 INDEX 조회 후 다시 데이터에서 조회할 필요가 없으므로 빠른 검색이 가능하다.

- ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬됐기 때문에 클러스터 인덱스가 유리하다.

 

2. 주의해야 할 인덱스 설계 및 사용

- 대용량 데이터가 자주 입력될 때, 클러스터 인덱스의 경우 페이징이 빈번하게 일어나기 때문에 부하가 생긴다. 따라서 인덱스가 필요한 경우 클러스터 인덱스보다 보조 인덱스가 좋을 수 있다.

- 데이터 중복도가 높은 열은 인덱스 효과가 없다. 예를 들어 성별에 M, F만 있을 경우 인덱스를 쓰지 않는 것이 낫다.

- 자주 사용되지 않으면 성능 저하를 초래할 수 있다.

- 데이터 변경(삽입/수정/삭제) 작업이 얼마나 자주 일어나는 지를 고려해야 한다.

- 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다. (테이블 당 4~5개 권장)

- 테이블이 갖고 있는 전체 데이터 양의 10~15% 이내의 데이터가 출력될 때만 INDEX를 이용하는 것이 효율적이고,

  그 이상일 경우에는 오히려 전체 스캔이 빠르다.

  -> 검색할 데이터가 전체 데이터의 20% 이상이면 MySQL에서 인덱스를 사용하지 않으며,

      강제로 사용할 경우 성능 저하를 초래할 수 있다.

- 사용하지 않는 인덱스는 제거하는 것이 바람직하다.

 

3. ORDER BY와 GROUP BY에 대한 인덱스

INDEX는 ORDER BY와 GROUP BY에도 영향을 끼치는데, 아래와 같은 경우에는 INDEX를 타지 않는다.

- ORDER BY 인덱스컬럼1, 컬럼2 : 복수의 키에 대해 ORDER BY를 사용한 경우

- WHERE 컬럼1 = '값' ORDER BY 인덱스 컬럼: 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우

- ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC: DESC와 ASC를 혼합해서 사용한 경우

- GROUP BY 컬럼1 ORDER BY 컬럼2: GROUP BY와 ORDER BY의 컬럼이 다른 경우

- ORDER BY ABS(컬럼): ORDER BY 절에 다른 표현을 사용한 경우

 

 

 

 

 

 

 

 <출처>

[도서] 이것이 MySQL이다