[Database] Index

[DB] Index

 

작업과 공간을 활용하여 테이블의 검색 속도를 향상하기 위한 자료구조

---

 

특정 컬럼에 인덱스를 생성하면, 해당 칼럼 데이터들을 정렬해서 별도의 메모리 공간에 물리적 주소와 함께 저장

생성되었다면, where 조건절 등을 통해서 옵티마이저에서 판단해, 생성된 인덱스를 타고 검색할 수 있다.

검색 속도 향상을 위한 작업이라고 볼 수 있음.

 

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

DB시스템에 따라 상이하지만, 현대에는 비용을 계산해서 효율적인 계획을 선택하는 비용 기반 옵티마이저가 거의 채택되는 듯함.

 

 

 

쉽게 말하자면 책의 목차 같은 것. 개념적으로는 쉽지만 실무에서 DB관련 작업을 할 때 대부분의 속도 저하는

Select 문, 즉 조건 검색 where 절 같은 곳에서 가장 많이 발생하는데 첫 번째로 고려해 볼만한 것이 index다.

SQL 튜닝에서도 index 관련된 문제가 많기 때문에 알아야 할 개념임.

 

 **SQL 튜닝에 대해서도 추가로 공부해야 할 필요성 있음

 

---

 인덱스를 왜 써야 해요?

• 조건 검색 Where 절의 효율성

 

테이블을 만들고 안에 데이터가 쌓이게 되면 레코드는 내부적으로 뒤죽박죽으로 저장됨. 이렇게 되면

Where절 특정 조건에 맞는 데이터들을 찾아낼 때도 처음부터 끝까지 (fullscan)하게 됨. 하지만 인덱스를 사용하면

인덱스 테이블은 데이터들이 정렬되어 저장되기 때문에 해당 조건에 맞는 (where) 데이터들을 빠르게 찾아낼 수 있음.

이것이 가장 큰 이유임.

 

• 정렬 Order by 절의 효율성

 

인덱스를 사용하면 Order by에 의한 정렬 과정(Sort)을 피할 수 있다. Order by는 굉장히 부하가 많이 걸리는 작업이다.

정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고, 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 

이루어지기 때문이다.

하지만 인덱스를 사용하면 이럴 필요가 없음. 왜? 이미 정렬돼 있는 데이터를 가져오기만 하면 되기 때문.

그래서 실무에서도 Order by보다는 인덱스를 통한 정렬을 많이 사용하는듯함. (확실하진 않음)

 

 **디스크 I/O : 데이터를 작성하고 변경할 때 HID에 저장되는 것을 말함

 

• MIN, MAX 효율적 처리 가능

 

이것 또한 데이터가 정렬돼 있기 때문에 얻을 수 있는 장점. 최댓값과 최솟값을 찾아야 할 일이 많은데

레코드의 양 끝만 가져오면 되기 때문.

 

---

 그러면 인덱스가 무조건 좋으냐?? 

그렇진 않음. 인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지해줘야 한다는 거임.  그렇기 때문에 레코드의 값이 바뀐다면 인덱스 테이블의 값들을 수정해 주고 다시 정렬해야 함. 그리고 인덱스 테이블, 원본 테이블 이렇게 두 군데에 모두 수정작업을 해줘야 함.

 

또한 검색 시에도 인덱스가 무조건 좋은 것은 아님. 인덱스는 테이블의 전체 데이터의 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고, 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 효율적이다.

 

이 부분은 블로그에서 발췌한 내용인데, 사실이 아닌듯함. 인덱스는 데이터베이스의 데이터 크기와 상관없이 무조건 효율적인 방법이고, 대신 단점이라 하면은 인덱스를 추가로 생성한다는 것 자체가 DB 검색의 효율성을 높이기 위한 작업이지만, 인덱스를 저장하는 것 또한 추가 저장공간을 사용하는 것이기 때문에, 어떻게 DB를 구성해야 효율적인가에 대한 

고민이 필요한 부분임.

 

예를 들어, 데이터의 크기가 작다면 인덱스를 생성해 추가적인 저장 공간을 할당하는 것보다

그냥 Full Scan 때리는 게 더 효율적일 수 있다는 말임.

 

💡 인덱스를 남발하면 안 되는 이유

 

DB서버 성능에 문제가 생기면 첫 번째로 인덱스를 추가로 생성하는 방법을 떠올리기 쉬운데 

문제가 생길 때마다 인덱스를 생성하면서 인덱스가 쌓여가면 쿼리문 짜기엔 용이할 수 있지만,

전체적인 DB서버의 과부하를 초래함.

그렇기에 문제가 생겼을 때 인덱스를 계속 생성하기보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나아가는 것이

올바름직함.

 

---

 인덱스 생성 전략

 

생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로, 그리고 조건절에 호출 빈도가 잦은 칼럼을 인덱스로 생성하는 게 좋다. 인덱스는 특정 칼럼을 기준으로 하고 기준이 된 칼럼으로 정렬된 인덱스 테이블을 생성하게 됨.

이 기준 칼럼은 최대한 중복되지 않게 하는 편이 좋다. 가장 일반적인 방법은 PK로 인덱스를 거는 것.

중복된 값이 없는 인덱스 테이블이 최적의 효율을 내고, 반대로 모든 값이 같은 칼럼이 인덱스 칼럼이 된다면 가치가 없음.

 

1. 조건절에 자주 등장하는 칼럼
2. 항상 = 으로 비교되는 칼럼 (ex "where user_id = 12345" 같은 케이스)
3. 중복되는 데이터가 최소한인 칼럼 (분포도가 좋은) 칼럼
4. Ordey by 절에서 자주 사용되는 칼럼
5. 조인 조건으로 자주 사용되는 칼럼

 

---

인덱스 구조

 

인덱스 구조엔 여러 가지가 있지만 밸런스드 트리 인덱스 구조만 간단하게 알아보자.

그중에서도 B*TREE와 B+TREE 구조가 가장 많이 사용된다.

 

 

B*Tree 인덱스 구조

 

B*TREE 인덱스는 대부분의 DBMS 그리고 오라클에서 특히 중점적으로 사용하고 있는 가장 보편적인 인덱스이다.

구조는 위와 같이 Root(기준) / Branch(중간) / Leaf(말단) 노드로 구성된 계층적 구조를 갖고 있다. 특정 칼럼에 인덱스를 생성하는 순간 칼럼들을 정렬하는데, 정렬한 순서가 중간쯤 되는 데이터를 뿌리에 해당하는 ROOT 블록으로 지정하고,

ROOT 블록을 기준으로 가지가 되는 BRANCH 블록을 정의하며, 마지막으로 잎에 해당하는 LEAF 블록에 인덱스의 키가 되는 데이터와 데이터의 물리적 주소 정보인 ROWID를 저장함.

 

B+트리와 B*트리 모두 B-트리에서 파생된 인덱스 구조고, 비슷하지만 약간의 차이가 있다.

 

1. 노드 분배 규칙

• B*트리는 노드가 2/3 이상 차야 분할을 수행함. 즉 노드가 최소 2/3 이상 데이터를 포함해야 하고, 새로운 데이터가 삽입되면 분할됨.
• B+트리는 반 이상 차야 분할을 수행.

2. 리프 노드 구성

• B*트리는 리프 노드에 데이터와 해당 데이터의 주소를 함께 저장. B*트리의 리프 노드는 데이터의 저장과 검색 모두 사용.
• B+트리의 리프 노드는 데이터만 저장하고, 해당 데이터의 주소는 따로 인덱스 블록에서 관리. 따라서 B+트리의 리프 노드는 데이터의 저장에만 사용하며, 인덱스 블록을 통해 데이터를 검색.

 

 

더 많은 차이가 있지만 이 정도만 알아도 충분할 듯하다. 

혹시 자료나 설명이 더 필요하다면 아래의 게시글을 추후에 참고하자

 

(https://velog.io/@seanlion/btree)

B트리,B+트리, B*트리 개념 정리

 

 

 

참조 : https://velog.io/@seanlion/btree, https://choicode.tistory.com/27