인덱스 (Index) / PK와 Index

이 글의 목차

1. Index 정의
2. 특성
3. 생성시 주의할 점
4. 생성전략
5. PK와 Index
6. 클러스터형 / 비클러스터형 인덱스

 

 

[ Index 정의 ]

데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조

인덱스를 저장하는 방식(또는 알고리즘)에 따라 B-Tree 인덱스, Hash 인덱스, Fractal 인덱스 등으로 나눌 수 있으며, 일반적으로 B-Tree 구조가 사용되기 때문에 B-Tree 인덱스를 통해 인덱스의 동작 방식에 대해 살펴보겠습니다.

* B-Tree(Balanced Tree, 균형 트리)  : 이진트리에서 발전되어 모든 리프노드들이 같은 레벨을 가질 수 있도록 자동으로 균형을 맞추는 트리

 

특정 컬럼에 인덱스를 생성하면

해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장됩니다. (DB의 약 10%)

Index 와 테이블의 관계. Index테이블이 원본 데이터 테이블을 찾아가는 방식은 C의 포인터와 유사하다.

 

인덱스를 설명할 때, 가장 자주 드는 예시가 책의 목차입니다.

 

인덱스는 페이지(Page) 단위로 저장되기 때문에 책의 목차와 유사합니다. 특정 데이터를 조회 시 어떤 페이지에 있는지를 찾은 후 해당 페이지를 스캔해 나가는 것과 모든 페이지를 스캔하는 것에는 검색속도의 차이가 있기 때문입니다.

 

데이터를 조회시 설정된 인덱스가 없다면 테이블의 첫번째 행부터 ~ 마지막 까지 찾습니다. = Full Scan

데이터 건수가 많아질수록 속도는 느려집니다.

 

이럴때 Index를 설정하여 검색 속도를 향상시킬 수 있고, 부가적으로는 데이터의 고유성을 지킬 수 있습니다.

(index의 중복 값 불가능 특성)

 

- Index가 레코드를 찾는 방법 

Ex) 테이블에서 '홍길동' 이라는 회원을 찾고자 회원명으로 조회한다고 했을 때, 인덱스를 통해 리프 노드에 도달하고, 인덱스 키에 저장되어 있는 레코드의 값(PK(RowID)) 을 찾아와 이 값을 통해 최종적으로 데이터를 조회하게 된다. 즉, 인덱스를 통해 데이터를 조회 할 때에는 아래의 2가지 작업이 수행되는 것이다.

1. 인덱스를 통해 PK 를 찾음 
2. PK 를 통해 레코드를 찾음

= 디스크 읽기 작업이 줄어듦.

 

[상세 설명 - Index를 사용하는 이유]

이렇게 두번에 걸쳐 찾는 작업을 하기 때문에 옵티마이저는 인덱스를 통해 레코드 1건을 읽는 것이 테이블을 통해 직접 읽는 것 보다 4~5배 정도 비용이 더 많이 드는 것으로 예측한다. 하지만 DBMS는 우리가 원하는 레코드가 어디있는지 모르므로, 모든 테이블을 뒤져서 레코드를 찾아야한다. 이는 엄청난 디스크 읽기 작업이 필요하므로 상당히 느리다.

하지만 인덱스를 사용한다면 ⓐ인덱스를 통해 PK를 찾고, ⓑPK를 통해 레코드를 저장된 위치에서 바로 가져올 수 있으므로 디스크 읽기가 줄어들게 된다. 그렇기 때문에 레코드를 찾는 속도가 훨씬 빠르며, 이것이 인덱스를 사용하는 이유이다.

"반면에 인덱스를 타지 않는 것이 효율적일 수도 있다. " 인덱스를 통해 레코드 1건을 읽는 것이 4~5배 정도 비싸기 때문에, 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않는 것이 효율적이다. 이런 경우 옵티마이저는 인덱스를 이용하지 않고 테이블 전체를 읽어서 처리한다.

[Index 활용시 주의]

B-Tree는 인덱스 키 값을 변형하지 않고 정렬된 상태로 저장한다. (물론 VARCHAR 255를 넘어가면 앞 부분만 잘라서 인덱스를 생성하긴 한다.) 그래서 동등 조건 연산(=)이나 부등호 검색 등 대부분의 경우에 인덱스를 활용할 수 있다. 하지만 키 값의 뒷부분만 검색(LIKE “%name%”)하거나 변형된 값으로 쿼리하는 등의 경우라면 인덱스를 활용할 수 없다.

출처: https://mangkyu.tistory.com/286 [MangKyu's Diary:티스토리]

 

 

이렇게 속도 향상을 위해 인덱스를 사용하지만, 인덱스를 설정할 때 주의해야 하는 점이 있습니다.

그것은 Index의 특성을 알면 기억하기 쉽습니다.

 

[ Index의 특성 ] 

• 인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요. (테이블과 별도의 공간에 저장됨)
• 인덱스 테이블은 인덱스 키를 바탕으로 데이터들이 정렬되어 저장되어 있다.
• 인덱스는 페이지 단위로 저장된다.  페이지 =  SQL Server의 기본 저장(I/O) 단위
• 정렬된 인덱스 키를 따라서 리프 노드에 도달하면 (인덱스 키, PK) 쌍으로 저장되어 있다. 

 

=> 인덱스 생성시 고려해야 하는 이유는 인덱스 = 정렬된 상태를 계속 유지 시켜줘야 한다는 점.

정렬을 유지해줘야 하기 때문에, 데이터의 값이 바뀐 경우 Index도 함께 연산작업이 필요해진다.

 

INSERT : 인덱스를 추가함

DELETE : 삭제하는 데이터의 인덱스를 '사용하지 않음' 처리 함

UPDATE : 기존 인덱스를 '사용하지 않음' 처리하고, update된 데이터에 대한 인덱스를 추가함

 

 

 

[ Index 생성시 주의할 점 ]

위와 같은 이유로, 값이 자주 바뀌는 데이터를 Index로 생성하면 비효율적이다. * (DML : Insert / Delete / Update)

원본 테이블 뿐만 아니라 인덱스 테이블까지 두군데의 작업이 일어나기 때문이며, delete, update 시 삭제처리하는 것이 아닌 사용하지 않음 처리를 하고 인덱스가 추가되기 때문에 실제 데이터보다 인덱스가 비대해지는 경우가 생길 수 있다.

---

=> 즉, 쿼리를 통해 어떤 레코드를 읽고자 할 때, 결국 하나의 페이지(블럭) 를 읽어야 한다.

레코드를 찾는데 1개의 페이지만으로 처리가 안된다면 다른 페이지를 읽어야 하는데, 추가 페이지를 읽는 디스크 I/O 때문에 성능이 떨어지게 된다.

---

 

 

[ Index 생성 전략 ]

 

1. 규모가 큰 테이블 - (규모가 작은 경우 테이블 스캔(full 스캔) 비용이 적을 수 있음)
2. WHERE / JOIN / ORDER BY 절에 자주 쓰이는 컬럼
3. INSERT / DELETE / UPDATE 가 '빈번하지 않은' 컬럼
4. GROUP BY : 행을 그룹화하는 데 사용되는 값의 수가 그룹화되는 행의 수에 비해 적을 경우
5. PK

 

 

[ PK 와 Index ]

PK는 레코드의 물리적인 저장 위치를 결정하며, 인덱스는 PK에 의존한다.

인덱스를 통해 테이블에서 데이터를 가져오려면 인덱스 키를 통해서 PK를 찾아야 하고, 그 다음 PK를 통해서 테이블로부터 레코드를 읽어오기 때문이다.

그렇기에 인덱스를 통해 데이터를 읽어오는 것 보다는 PK로 읽어오는게 빠르다고 볼 수 있다.

 

- 하지만 PK는 신중히 선택 해야 한다.

만약 조회하는 레코드 건수가 많아서 1개의 페이지만으로 처리가 안된다면 다른 페이지를 읽어야 한다. 이는 추가 디스크 읽기 작업을 의미하며, 읽어야 하는 페이지의 수 만큼 성능이 떨어지게 된다. 데이터베이스 성능 개선 혹은 쿼리 튜닝은 디스크 I/O 자체를 줄이는 것이 핵심인 경우가 많은데, 그 만큼 디스크 I/O는 느리다. 또한 메모리의 효율을 위해서도 중요하다. 디스크 I/O를 통해 페이지를 읽어오면 버퍼풀이라는 메모리에 캐싱해둔다. 그런데 개별 데이터의 크기가 커지면 페이지 자체의 크기가 커지면서, 메모리에 캐싱해둘 수 있는 페이지 수도 줄어들게 되고 메모리 효율이 떨어지게 된다.

5개의 인덱스를 갖는 테이블의 PK 크기에 따라 인덱스의 크기를 비교해 본다면, 레코드 한 건을 생각하면 50바이트 쯤이야 대수롭지 않지만 레코드 건수가 100만건 만 돼도 인덱스의 크기가 190MB나 증가했다. = 비대해진 인덱스 

1000만건이 되면 1.9GB 증가한다. 또한 인덱스가 커질수록 같은 성능을 내기 위해 그 만큼의 메모리가 더 필요해지므로 PK는 신중히 선택해야 한다.

 

출처: https://mangkyu.tistory.com/285 [MangKyu's Diary:티스토리]

 

 

[ 클러스터형 / 비클러스터형 Index]

• 클러스터형
  • 클러스터형 인덱스는 해당 키 값을 기반으로 테이블이나 뷰의 데이터 행을 정렬하고 저장한다.
  • 데이터 행 자체는 한 가지 순서로만 저장될 수 있으므로 테이블당 클러스터형 인덱스는 하나만 있을 수 있다.  
  • 테이블의 데이터 행이 정렬된 순서로 저장될 때만 테이블에 클러스터형 인덱스가 된다. 테이블에 클러스터형 인덱스가 있는 경우 테이블을 클러스터형 테이블이라고 합니다. 테이블에 클러스터형 인덱스가 없는 경우 해당 데이터 행은 힙이라는 순서가 지정되지 않은 구조에 저장된다.

* 힙 은 클러스터형 인덱스가 없는 테이블 (웬만해선 인덱스를 등록해라..)

• 비클러스터형 인덱스
  • 비클러스터형 인덱스에는 비클러스터형 인덱스 키 값이 포함되며 각 키 값 항목에는 해당 키 값이 포함된 데이터 행에 대한 포인터가 있다.
  • 비클러스터형 인덱스에서 데이터 행에 대한 인덱스 행의 포인터 = 행 로케이터
  • 행 로케이터의 구조는 데이터 페이지가 힙 또는 클러스터형 테이블에 저장되는지 여부에 따라 달라진다.
    • 힙의 경우 행 로케이터는 행에 대한 포인터입니다.
    • 클러스터형 테이블의 경우 행 로케이터는 클러스터형 인덱스 키입니다.
  • 키가 아닌 열을 비클러스터형 인덱스의 리프 수준에 추가하여 기존 인덱스 키 제한을 바이패스하고 완전히 적용된 인덱싱된 쿼리를 실행할 수 있다.

 

 

 

 

도움이 된 사이트

https://www.ibm.com/docs/ko/db2/11.5?topic=objects-indexes

 

https://mangkyu.tistory.com/96