실무에서 DB테이블을 요청할 때 인덱스를 추가해 달라는 말을 많이 들었습니다.
인덱스를 추가할 때 어떤 기준으로 어떻게 추가하면 좋은지, 그리고 인덱스를 왜 걸어야 되는지에 대하여 설명하도록 하겠습니다.
보통 My-SQL 기준으로 인덱스는 B트리 기반으로 작동하고 있습니다.
이유는 아래와 같습니다.
인덱스로 B-Tree를 사용하는 이유
1. 트리의 높이가 낮다
B-tree는 한 노드에 여러 키와 자식 노드를 가질 수 있어서, 같은 양의 데이터를 저장할 때 이진 트리보다 트리의 높이가 낮습니다.
예를 들어, 100만 개의 데이터를 저장할 때:
•
이진 트리는 높이가 최대 20 정도 됩니다.
•
B-tree는 높이가 최대 3 정도밖에 되지 않습니다.
이렇게 높이가 낮으면 데이터를 찾거나 추가하고 삭제하는 데 시간이 덜 걸립니다.
책장에 책을 꽂을 때, 한 칸에 책을 하나만 놓는 게 아니라 여러 권을 놓을 수 있다면 어떨까요?
한 곳에 여러 정보를 저장할 수 있어서 전체 구조가 더 납작해집니다.
2. 디스크 접근 횟수 줄어듦
B-tree는 디스크 기반 시스템에 적합하게 설계되었습니다. 한 번에 여러 데이터를 저장할 수 있는 노드 구조로 디스크를 읽는 횟수가 줄어듭니다.
예를 들어, 100만 개의 데이터 중 하나를 찾을 때:
•
이진 트리는 최대 20번 디스크를 읽어야 합니다.
•
B-tree는 최대 3번만 읽으면 됩니다.
컴퓨터가 하드디스크에서 정보를 읽어오는 건 마치 우리가 책을 꺼내 읽는 것과 같습니다. B-tree는 한 번에 여러 정보를 읽을 수 있어서 책을 꺼내는 횟수가 줄어듭니다. 이렇게 하면 컴퓨터가 덜 힘들어하고 더 빨리 일할 수 있습니다.
3. 캐시 활용에 유리
B-tree는 한 노드에 여러 데이터를 저장하므로, 한 번에 많은 데이터를 메모리로 가져올 수 있습니다. 이렇게 하면 메모리에서 데이터를 빠르게 처리할 수 있어 성능이 좋아집니다.
우리가 공부할 때 한 번에 여러 내용을 외우면 좋은 것처럼, B-tree도 한 번에 여러 정보를 기억합니다. 이렇게 하면 컴퓨터가 정보를 더 빨리 처리할 수 있습니다.
4. 범위 검색에 적합
B-tree는 데이터를 항상 정렬된 상태로 유지하기 때문에, 예를 들어 특정 범위 내의 데이터를 찾을 때 매우 빠르게 처리할 수 있습니다.
예를 들어, 월급이 50,000에서 70,000 사이인 직원들을 찾는 쿼리를 빠르게 실행할 수 있습니다.
도서관에서 책을 찾을 때 번호순으로 정리되어 있으면 쉽게 찾을 수 있습니다. B-tree이와 같이 정보를 순서대로 정리해두기 때문에 특정 범위의 정보를 찾을 때 아주 빠릅니다.
5. 균형 유지 비용이 적다
이진 트리(특히 AVL 트리나 레드-블랙 트리)는 삽입이나 삭제를 할 때 트리의 균형을 맞추기 위해 많은 작업이 필요하지만, B-tree는 균형을 유지하는 데 더 적은 비용이 듭니다. 노드가 나뉘거나 합쳐지는 일이 자주 발생하지 않기 때문입니다.
새 책을 꽂거나 책을 빼도 책장이 쉽게 엉망이 되지 않는 것처럼, B-tree도 정보를 추가하거나 삭제할 때 전체 구조를 크게 바꾸지 않아도 됩니다.
6. 공간을 더 효율적으로 사용
B-tree는 한 노드에 여러 개의 키를 저장할 수 있어서, 트리 전체를 관리하는 데 필요한 추가 공간(포인터 등)이 줄어듭니다. 이로 인해 저장 공간을 더 효율적으로 사용할 수 있습니다.
7. 삽입/삭제 성능
B-tree는 많은 데이터를 삽입하거나 삭제할 때도 성능이 크게 떨어지지 않습니다. 노드가 분할되거나 병합되더라도 트리 전체 구조를 크게 변경하지 않기 때문입니다.
8. 동시 작업에 유리
B-tree는 데이터베이스에서 여러 사용자가 동시에 작업할 때도 효율적입니다. 한 번에 큰 단위의 노드를 처리할 수 있기 때문에, 여러 사용자가 데이터를 처리할 때 충돌이 적게 일어납니다.
B-Tree 설명
인덱스 동작방식 예제
아래의 표처럼 일반의 Member데이터가 쌓여있다고 가정합니다.
옆에 표를 인덱스로 생성하게 되면 아래와 같은 표가 생성됩니다.
Member
B-tree based Index
a | b | c |
2 | … | … |
7 | … | … |
1 | … | … |
2 | … | … |
9 | … | … |
7 | … | … |
13 | … | … |
5 | … | … |
7 | … | … |
2 | … | … |
a | ptr |
1 | … |
2 | … |
2 | … |
3 | … |
5 | … |
7 | … |
7 | … |
7 | … |
9 | … |
13 | … |
B-tree based Index를 보면 우측에 Pointer란 값이 존재하며, 이 값을 통해서 Member의 튜플의 값을 참조 할 수 있게 됩니다.
1.
select * from Member where a = 9;를 실행시키게 되면, B-tree Based Index를 바이너리 서치를 통해서 찾게 됩니다.
2.
B-tree구조에서 바이너리 서치를 통해서 일치하는 a의 값을 찾으면 ptr을 통해서 원본 데이터의 값을 추출합니다.
그렇다면 개발자는 무엇을 잘 해야 되는가?
1.
인덱스의 동작 방식을 알아야 됩니다.
a.
추후 DB 옵티마이저를 튜닝해야 될 때 각각의 장담점을 알고 있으면 적재적소에 상용할 수 있을 것입니다.
•
B-tree 인덱스
•
Hash 인덱스
•
Bitmap 인덱스
•
복합 인덱스
•
클러스터드 인덱스
•
커버링 인덱스
2.
인덱스를 어떻게 주면 효율적으로 최대한 자원의 낭비 없이 사용할 수 있을지 알아야 됩니다.
•
B-Tree 인덱스는 table을 write (insert, update, delete)할 때마다 index를 계속 수정해 줘야 합니다. 때문에 wirte를 할때마다 오버헤드가 발생합니다.
•
index마다 각각 저장 공간을 차지하기 때문에 추가적인 저장 공간이 사용됩니다.
•
그렇다면 인덱스를 선언할 때 어떻게 선언해야지 자원의 낭비가 최대한 없을 수 있을까?
id | name | code | backnumber |
1 | 콜라 | 0001 | 0001-1 |
2 | 사이다 | 0002 | 0001-2 |
3 | 환타 | 0003 | 0001-3 |
조회 쿼리가
select * from item where id =?
select * from item where name =?
select * from item where code = ? and backnumber =?
select * from item where code =?
SQL
복사
위와 같이 4개의 조회 쿼리가 존재한다면 우리는 3개의 index를 지정해 줄 수 있습니다.
•
id: 기본 키(primary key)로 사용되는 경우 자동으로 인덱스가 생성됩니다.
•
name : 단일 인덱스를 추가합니다.
•
code와 name : 복합 인덱스로 추가합니다
그렇다면? code 단일 index가 또 필요할까?
현재 위 4개의 컬럼에 굳이 인덱스를 전부 넣어줄 필요가 없을 것입니다. 이유는 복합 인덱스 첫 시작이 code로 시작이 되고 있으므로 (code)단일 인덱스 대신에 사용하면 되기 때문입니다.
제가 실무에서 가장 많이 만나본 예제입니다. where 조건에 있다면 무조건 인덱스를 단건 씩 추가하는 경우를 많이 접해보았고, 이러한 구조에 대해 알지 못하면 자원의 낭비가 심할 것입니다.
때문에 복합 인덱스를 사용할 때 최대한 많이 사용되는 컬럼을 앞에 배치해두고 같이 사용하는 방법으로 단일 인덱스의 자원의 낭비를 줄일 수 있다는것을 인지하는 게 중요하다고 생각합니다.
.png&blockId=11ddb2e0-c45e-80c3-999c-e725a14dec5b)
정리하자면
•
인덱스를 무조건 많이 만든다고 성능이 좋아지는 것이 아닙니다. 적절한 설계가 중요하며, 중복된 인덱스는 오히려 성능을 저하시킬 수 있습니다.
•
인덱스 설계 시, 쿼리에서 자주 사용되는 컬럼을 기준으로 복합 인덱스를 생성하고, 불필요한 단일 인덱스를 지양하는 것이 자원 낭비를 막는 최적의 방법입니다.
오늘은 B-tree와 인덱스에 대해 설명하였습니다. 실무에서 가장 많이 보았던 단일인덱스선언과 복합 인덱스 선언에 대해 작성하게 되었고, 저 또한 이러한 특성을 알지 못하고 작성했던 경험이 있어 작성하게 되었습니다.
감사합니다.