44 . DB index

 인덱스(Index)란? 

인덱스는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조라고 한다. 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다. 이렇게 인덱스가 생성하였다면 앞으로 쿼리문에 "인덱스 생성 컬럼을 WHERE 조건으로 거는 등"의 작업을 하면 *옵티마이저에서 판단하여 생성된 인덱스를 탈 수가 있다. 만약 인덱스를 타게 되면 아래의 그림과 같이 인덱스를 타게 되고 먼저 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 데이터를 가져오는 식으로 동작을 하여 검색 속도의 향상을 가져올 수 있다.

또한 인덱스 생성 시 데이터를 오름차순으로 정렬하기 때문에 정렬된 주소체계라고 표현할 수 있다. (아래 그림을 보면 특정 컬럼에 인덱스가 생성됐을 때 컬럼의 데이터들을 오름차순으로 정렬한 모습을 볼 수 있다.)

 

*옵티마이저 : 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다. 컴퓨터의 두뇌가 CPU인 것처럼 DBMS의 두뇌는 옵티마이저라고 할 수 있다.

---

 인덱스를 사용하는 이유 (장점) 

인덱스의 가장 큰 특징은 데이터들이 정렬이 되어있다는 점이다.

이 특징으로 인해 조건 검색이라는 영역에서 굉장한 장점이 된다.

아래 세 가지 이유 모두 데이터들이 정렬되었다는 장점을 관통하는 내용들이다.

① 조건 검색 WHERE 절의 효율성

테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드(row : 행)는 내부적으로 순서가 없이 뒤죽박죽으로 저장이 된다. 이렇게 되면 WHERE절에 특정 조건에 맞는 데이터들을 찾아낼 때도 레코드의 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 한다. 이것을 풀 테이블 스캔 (Full Table Scan), 줄여서 풀 스캔(Full Scan)이라고 한다. 하지만 인덱스 테이블 스캔(Index Table Scan) 시 인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건(WHERE)에 맞는 데이터들을 빠르게 찾아낼 수 있는 것이다. 이것이 인덱스를 사용하는 가장 큰 이유이다.

② 정렬 ORDER BY 절의 효율성

인덱스를 사용하면 ORDER BY에 의한 정렬(Sort) 과정을 피할 수가 있다. ORDER BY는 굉장히 부하가 많이 걸리는 작업이다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 *디스크 I/O도 추가적으로 발생되기 때문이다. 하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 된다. 왜? 이미 정렬이 되어 있기 때문에 가져오기만 하면 되기 때문이다.

*디스크 I/O : 간단하게 말해 우리가 데이터를 작성하고 변경할 적에 디스크 즉 HDD에 저장되는 것을 말한다. (디스크 I/O에 대해서는 나중에 다시 공부해볼 필요가 있을 것 같다.)

③ MIN, MAX의 효율적인 처리가 가능하다

이것 또한 데이터가 정렬되어 있기에 얻을 수 있는 장점이다. MIN값과 MAX값을 레코드의 시작 값과 끝 값 한 건씩만 가져오면 되기 때문에 Full Table Scan으로 테이블을 모두 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있다.

---

 인덱스를 사용하면 무조건 효율이 좋을까? (단점) 

인덱스가 주는 혜택이 있으면 그에 따른 부작용도 있다. 

인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지시켜줘야 한다는 점이다.

그렇기에 레코드 내에 데이터 값이 바뀌는 부분이라면 악영향을 미친다.

① 인덱스는 DML에 취약

INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 인덱스 테이블 내에 있는 값들을 다시 정렬을 해야 한다. 그리고 위에 사진처럼 인덱스 테이블, 원본 테이블 이렇게 두 군데의 데이터 수정 작업을 해줘야 한다는 단점도 발생한다. 그렇기 때문에 DML이 빈번한 테이블보다 검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋다.

② 무조건 인덱스 스캔이 좋은 것은 아니다

검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋지만 무조건 검색 시에도 인덱스가 좋은 것은 아니다. 인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫다. 직관적인 예시를 들자면 1개의 데이터가 있는 테이블과 100만 개의 데이터가 들어 있는 테이블이 있다고 하자. 100만 개의 데이터가 들어있는 테이블이라면 풀 스캔보다는 인덱스 스캔이 유리하겠지만, 1개의 데이터가 들어있는 테이블은 굳이 인덱스 스캔 없이 풀 스캔이 빠를 것이다. 

③ 속도 향상을 위해 인덱스를 많이 만드는 것은 좋지 않다.

인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요하다. 무턱대고 인덱스를 만들어서는 결코 안 된다는 것이다. 즉, 속도 향상에 비해 단점들의 COST를 비교해서 인덱스를 만들지 말지를 정해야 한다.

💡 여기서 잠깐! Index를 남발하지 말아야 하는 이유

데이터베이스 서버에 성능 문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성이다.
문제가 발생할 때마다 인덱스를 생성하면서 인덱스가 쌓여가는 것은 하나의 쿼리문을 빠르게는 만들 수 있지만,
전체적인 데이터베이스의 성능 부하를 초래한다.

조회 성능을 극대화하려 만든 객체인데 많은 인덱스가 쌓여서 INSERT, UPDATE, DELETE 시에
부하가 발생해 전체적인 데이터베이스 성능을 저하한다.

그렇기에 인덱스를 생성하는 것보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야 한다.
인덱스 생성은 마지막 수단으로 강구해야 할 문제이다.

 

---

 인덱스의 관리 

앞서 설명했듯이 인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 계속 정렬을 해주어야 하고 그에 따른 부하가 발생한다. 이런 부하를 최소화하기 위해 인덱스는 '데이터 삭제'라는 개념에서 '인덱스를 사용하지 않는다'라는 작업으로 이를 대신한다.

• INSERT : 새로운 데이터에 대한 인덱스를 추가한다.
• DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행한다.
• UPDATE : 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가한다.

---

 인덱스 생성 전략 

생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋다. 인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 인덱스 테이블이 생성된다. 이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋다. 가장 최선은 PK로 인덱스를 거는 것이라고 할 수 있다. 중복된 값이 없는 인덱스 테이블이 최적의 효율을 발생시키겠고, 반대로 모든 값이 같은 컬럼의 인덱스 컬럼이 된다면 인덱스로써의 가치가 없다고 봐야 할 것이다.

1. 조건절에 자주 등장하는 컬럼
2. 항상 = 으로 비교되는 컬럼
3. 중복되는 데이터가 최소한인 컬럼 (분포도가 좋은 컬럼)
4. ORDER BY 절에서 자주 사용되는 컬럼
5. JOIN 조건으로 자주 사용되는 컬럼

---

 인덱스 구조 

인덱스에는 여러 가지 유형이 있지만 그중에서도 가장 많이 사용하는 인덱스의 구조는 밸런스드 트리 인덱스 구조다. 그리고 B TREE 인덱스 중에서도 가장 많이 사용하는 것은 B * TREE와 B + TREE 구조가 가장 많이 사용되는 인덱스의 구조다.

B * Tree 인덱스 구조

B * Tree 인덱스는 대부분의 DBMS 그리고 오라클에서 특히 중점적으로 사용하고 있는 가장 보편적인 인덱스이다. 구조는 위와 같이 Root(기준) / Branch(중간) / Leaf(말단) Node로 구성되며 계층적 구조를 갖고 있다. 특정 컬럼에 인덱스를 생성하는 순간 컬럼의 값들을 정렬하는데, 오라클 서버에서 풀 스캔보다 인덱스 스캔이 유리하다고 판단되었을 때 생성된 인덱스의 정렬한 순서가 중간쯤 되는 데이터를 뿌리에 해당하는 ROOT 블록으로 지정하고 ROOT 블록을 기준으로 가지가 되는 BRANCH블록을 정의하며 마지막으로 잎에 해당하는 LEAF 블록에 인덱스의 키가 되는 데이터와 데이터의 물리적 주소 정보인 ROWID를 저장합니다.

* 참고) ROOT에는 BRANCH 블럭의 시작점에 대한 정보를 갖고 있어 찾고자 하는 데이터의 위치가 어느 BRANCH에 위치하는지 알 수 있다. BRANCH 블럭에서도 마찬가지로 LEAF 블럭에 대한 시작점 정보를 갖고 있어 어느 LEAF에 포함되어 있는지 알 수 있다.

 

 

 

 

 

 

출처-

https://choicode.tistory.com/27

https://mangkyu.tistory.com/96

https://azderica.github.io/00-db-index/