[DB] DB Index 📌

DB 인덱스(Index)란?

• 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조
• 예를 들어 우리가 책을 볼 때 원하는 내용을 찾고 싶으면 책은 전부 찾는 것이 아니라 책의 색인을 통해 페이지를 찾는 것과 동일하다
• 인덱스 생성 시 데이터를 오름차순으로 정렬하기 때문에 정렬된 주소 체계라고 할 수 있다

 

 

 

 

인덱스 사용 예시

1. 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다
2. 인덱스를 생성한 후, 쿼리문에 인덱스 생성 컬럼을 WHERE 조건으로 거는 등의 작업을 하면 *옵티마이저에서 판단하여 생성된 인덱스를 탈 수 있다
3. 만약 인덱스를 타면, 위의 그림처럼 먼저 인덱스에 저장된 데이터의 물리적 주소로 가서 데이터를 가져오는 방식으로 동작하기 때문에 검색 속도가 향상될 수 있다

 

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

 

 

[ 예시 코드 ]

-- CREATE
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); -- 컬럼 중복 X

-- SELECT
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'CUSTOMERS';

-- DELETE
DROP INDEX [인덱스 명]
DROP INDEX EX_INDEX;

-- UPDATE
DROP INDEX [기존 인덱스 명] TO [바뀔 인덱스 명]
ALTER INDEX EX_INDEX RENAME TO EX_INDEX_NEW

-- REBUILD
ALTER INDEX [인덱스명] REBUILD;
ALTER INDEX EX_INDEX REBUILD;

 

 

 

 

 

인덱스 장점

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

이 특징으로 인해 조건 검색이라는 영역에서 굉장히 유리하다

또한 전반적인 시스템의 부하를 줄이고, 테이블의 조회 속도와 그에 따른 성능이 향상된다

 

 

1. 조건 검색 WHERE 절의 효율성

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

 

2. 정렬 ORDER BY 절의 효율성

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

❓ 디스크 I/O : 간단하게 말해 우리가 데이터를 작성하고 변경할 적에 디스크, 즉 HDD에 저장되는 것을 의미한다

 

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

• 인덱스를 사용하면 이미 데이터가 정렬되어 있기 때문에 MIN 값과 MAX 값을 레코드의 시작 값과 끝 값 한 건 씩만 가져오면 된다
• 따라서 Full Table Scan으로 모든 데이터를 확인할 필요 없이 효율적으로 찾을 수 있게 된다

 

 

 

 

인덱스 단점

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

따라서 레코드 내에 데이터 값이 바뀌는 부분이라면 악영향을 미친다

 

 

1. 인덱스는 DML에 취약하다

• INSERT, UPDATE, DELETE 를 통해 데이터가 추가되거나 값이 바뀌면, 인덱스 테이블 내에 있는 값들을 다시 정렬해야 한다
• 또한 인덱스 테이블과 원본 테이블 이렇게 두 군데의 데이터 수정 작업을 해줘야 한다
• 따라서 DML이 빈번한 테이블보다 검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋다

 

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

• 검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋지만, 검색 시에 인덱스가 무조건 좋은 것은 아니다
• 인덱스는 테이블의 전체 데이터 중 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고, 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 좋다
  • 예를 들어 A 테이블에는 1개의 데이터가 있고, B 테이블에는 100만 개의 데이터가 들어있다고 가정하자
  • 당연히 B 테이블은 Full Scan보다 Index Scan이 유리하지만, A 테이블은 굳이 Index Scan이 필요하지 않을 것이다

 

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

• 인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요하다
• 따라서 인덱스를 많이 만들면 그만큼 메모리를 많이 차지하기 때문에 바람직하지 않다
• 즉, 속도 향상과 단점들의 COST를 비교해서 인덱스를 생성해야 한다

 

 

💡 인덱스 남용이 좋지 않은 이유
인덱스가 쌓일 수록 하나의 쿼리문을 빠르게는 할 수 있다
하지만 전체적인 데이터베이스의 성능 부하를 초래한다

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

따라서 인덱스를 생성하는 것보다 SQL문을 효율적으로 짜는 것이 효과적이다
인덱스 생성은 마지막 수단으로 강구해야 할 문제이다!

 

 

 

 

인덱스의 관리

인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다

따라서 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행되면 계속 정렬을 해줘야 하는데, 그러면 그에 따른 부하가 발생한다

이런 부하를 최소화하기 위해 인덱스는 '데이터 삭제' 라는 개념에서 '인덱스를 사용하지 않는다'라는 작업으로 이를 대신한다

 

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

 

 

 

 

인덱스 생성 전략

인덱스를 효율적으로 사용하려면 데이터의 분포도는 최대한으로, 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성해야 한다

인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 인덱스 테이블이 생성된다

이 때 기준 컬럼은 최대한 중복되지 않는 값이 좋기 때문에 가장 최선은 PK로 인덱스를 거는 것이다!

 

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

 

 

 

 

인덱스 구조

인덱스에는 주로 해시 테이블이나 B TREE(Balanced Tree) 구조가 많이 사용된다

특히 B TREE 인덱스 중에서는 B * Tree와 B + TREE 구조가 가장 많이 사용된다

 

 

1. 해시 테이블 (Hash Table)

• Key-Value 형태로 데이터를 저장하는 자료 구조이다
• 내부적으로 배열(버킷)을 이용하여 데이터를 저장하기 때문에 검색 속도가 빠르다
• B-Tree 만큼 범용적이진 않지만 고유의 특성과 용도를 지닌 인덱스이다
• 일반적인 DBMS에서 메모리 기반의 테이블에 주로 구현되며, 디스크 대용량 테이블 용으로는 거의 사용되지 않는다
• 데이터 탐색 시, 해시 함수(Hash Function)을 이용하여 Key를 통해 index를 계산하는 식으로 적절한 index를 찾기 때문에 Key 충돌을 줄여 빠른 속도를 유지할 수 있다
• 만약 Key 충돌이 있는 경우에는 부가적인 처리가 필요하다
• Index를 이용하여 배열에 저장된 value에 접근하기 때문에 해시 테이블의 평균 시간 복잡도는 O(1)이다

 

2. B-Tree (Balanced Tree)

• 트리를 구성하는 아이템을 노드라고 하는데 B-Tree는 자식 노드의 개수가 2개 이상인 트리를 말한다
• 가장 상단을 구성하는 루트 노드(root node), 중간에 위치한 브랜치 노드 (branch node), 마지막에 위치한 리프 노드(leaf node)로 구성되어 있다
• 트리의 차수에 따라 노드 내 최대 Key-Value 수가 달라지낟
  • Key-Value 수 2개는 2차 B-Tree
  • Key-Value 수 3개는 3차 B-Tree ...

 

3. B+Tree

• B-Tree의 확장 개념으로, B-Tree와 다르게 브랜치 노드는 Value에 대한 정보가 존재하지 않고, 단순히 Key 값만 존재한다
• 리프 노드(leaf node)에서만 Value를 관리한다
• 리프 노드들은 LinkedList 구조로 서로를 참조하고 있기 때문에 B-Tree보다 노드 순회가 쉽다
• 리프 노드가 아닌 자료는 인덱스 노드(포인터 주소 존재)라고 부르고 리프 노드 자료는 데이터 노드(데이터 존재)라고 부른다
• 모든 리프노드는 같은 레벨에 존재한다
• 브랜치 노드에 Value가 없어서 B-Tree보다 차지하는 메모리가 적지만 Value를 찾기위해 리프 노드 까지 이동해아 한다
• 브랜치 노드와 리프 노드에 모두 Key가 존재하므로 Key 중복이 발생하게 된다

 

4. B*Tree

• B*Tree 인덱스는 대부분의 DBMS (특히 오라클)에서 중점적으로 사용하는 보편적인 인덱스이다
• 구조는 위처럼 root, branch, leaf node로 구성되며 계층적 구조를 갖는다
• 특정 컬럼에 인덱스를 생성하는 순간 컬럼의 값들을 정렬하는데, 오라클 서버에서 Full Scan보다 Index Scan이 더 유리하다고 판단되면 생성된 인덱스의 정렬한 순서가 중간쯤 되는 데이터를 뿌리에 해당하는 ROOT 블록으로 지정한다
• ROOT 블록을 기준으로 가지가 되는 BRANCH 블록을 정의하며 마지막으로 앞에 해당하는 LEAF 블록에 인덱스의 키가 되는 데이터와 데이터의 물리적인 주소 정보인 ROWID를 저장한다

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

 

 

 

 

---

참고 자료)

https://github.com/InJun2/TIL/blob/main/CS-topic/DB/DB_Index.md

https://choicode.tistory.com/27

https://brunch.co.kr/@skeks463/25