1. 개요
데이터 모델링을 할 때 빼놓을 수 없는 개념인 인덱스에 대한 개념을 보완해가면서, 각 데이터베이스 별로 물리적인 인덱스 구조 및 동작방식이 다르다는 것을 알 수 있었다.
사실 인덱스에 대해 검색해보면 대부분 거의 내용이 비슷하고, Clustered Index나 Secondary Index 등 원본데이터를 포함하거나 보조 장치로 PK를 활용하는 등의 개념을 알려주고 있는데, 문제는 이러한 구조와 동작방식이 마치 모든 데이터베이스에 해당하는 내용처럼 기술하고 있기에 개념을 이해하는데 약간의 오해가 발생할 수 있다.
예를 들어, Clustered Index(이하 클러스터 인덱스)는 key(PK)값을 기준으로 데이터를 저장하는 인덱스이며, 원본데이터와 함께 보관하는 테이블 그 자체이기도 하다. 더불어 Non-clustered Index(Secondary Index, 이하 보조인덱스)는 클러스터 인덱스의 내부적으로, PK와 함께 보관하여 원본 데이터 탐색이 가능하도록 포인터를 제공해주는 자료구조이다.
인덱스는 "조회 성능을 향상하기 위한" 장치로써의 본론적 개념만 DB전역적으로 적용이 가능하하다. B+ Tree 기반의 자료구조 및 Clustered/Non-Clustered 인덱스 등에 대한 내용은 MySQL에만 적용되는 부분이다. 이 외 PostgreSQL, Oracle 별로 물리적으로 구성되어있는 구조나 세부 동작방식은 데이터베이스 별로 약간 다르다.
즉, 데이터베이스 세부적으로 파악하고자 할 때, 인덱스 개념 전부가 모든 DB에 통용되는 개념은 아니다.
따라서 인덱스를 이해하기 위해서는 본론적인 개념을 먼저 확실하게 잡고, DB별로 그 사소한 차이점들을 알아가면서 정확히 이해하는 것이 중요할 것이라는 판단으로 이에 대해 파악한 내용들을 기록해보고자 한다.
특히, 인덱스의 자동생성대상은 왜 있고 인덱스를 어떻게 바라보는 것이 좋을지 세부적으로 살펴보고자 한다.
또한 나아가, DB들은 Kafka, Elastic Search와 같은 인프라적 비용이 들어가는 항목에 대해 제한적인 기능을 제공하여, 이를 활용한 부가적인 체계를 구축할 수도 있다.
통상적으로 알고있는 DB에 대한 본질적 개념, 그 뒤에 존재하는 몇가지 사소하지만 중요한 항목에 대해 고찰하여 정리해보고자 한다.
2. "인덱스가 빠른 이유"라기 보다는 "인덱스를 사용하는 것이 성능적으로 유리한 이유"의 관점
인덱스를 "적절하게" 사용하여 조회 성능을 더 향상할 수 있는 사실은 자명하다.
조회성능이 왜 향상하는가 살펴보기 전에, 이에 대해 올바른 접근 방향을 먼저 정립하는 것이 좋겠다.
인덱스가 빠른 이유가 아니다. 인덱스를 사용하는 것이 왜 성능적으로 유리한지에 대한 이유이다.
먼저, 인덱스는 기본적으로 데이터베이스의 페이지를 저장하는 장소와 동일한 Disk이다. 즉, Redis처럼 인메모리 저장소와 함께, 인메모리 저장소에서 쓰기 성능을 극대화하기 위한 자료구조 활용 등이 결합된 경우는 아니다.
디스크
├─ Data Page → Clustered Index (PK B+Tree Leaf에 실제 Row 저장)
└─ Index Page → Secondary Index (보조 인덱스 B+Tree)
여기서 의문이 발생한다.
엥? 데이터를 저장하는 구조가 Clustered Index 그 자체이기도 하다고? 원본데이터의 자료구조가 Clustered Index와 동일하다는 의미인가?
대답은 "YES"이다. 즉, MySQL 기준으로 InnoDB가 데이터를 디스크에 저장할때, 그 저장되는 테이블은 PK기반으로 B+ Tree 자료구조에 저장된 Clustered Index, 그 자체이기도 하다.
여기서 하나의 결론점이 발생한다. 데이터 원본이 저장되는 테이블 그 자체이자 Clustered Index에서, leaf Node에는 실제 Row 전체 데이터, 페이지가 저장이 된다.
따라서, Clustered Index에는 데이터 원본이 들어있는 페이지 데이터로써 저장이 된다는 의미이다.
반대로, 보조 인덱스는 원본 데이터가 아닌, 원본 데이터를 가르키는 나침반 역할을 하는데, leaf Node에 PK가 저장되어 있기에 해당 PK를 바탕으로 원본 데이터로 I/O가 추가 발생하는 구조로 구축이 되어있다는 의미이다.
2-1. 인덱스는 B+ Tree 구조 기반의 fanout 만큼의 탐색효율이 발생하는 것이 핵심이다.
인덱스라 하면 빼놓을 수 없는 구조가 바로 B+ Tree 구조이다.
B+Tree 구조란, 기존 B- Tree구조에서 Internal Node와 leaf Node가 key와 데이터를 모두 저장하였다면, 이를 개선하여 Internal Node에서는 key 범위 목록만 저장하고 leaf Node에만 key와 데이터를 모두 저장하는 방식으로 탐색의 효율성을 높인 자료구조이다.
자료를 디스크에 저장하는 모든 DBMS의 테이블 구조는 MySQL, PostgreSQL, Oracle 모두 B+ Tree의 내부 구조를 구축하고 있다.
[Root Page]
│
┌──────┴──────┐
[Internal] [Internal]
│ │
┌─────┴────┐ ┌─────┴────┐
[Leaf Page] [Leaf Page] [Leaf Page]만약 원본 테이블 그대로 조회 시 사용한다고 할때, Root를 시작점으로 Internal을 거쳐 Key의 범위를 제한하고, leaf Node에 접근하여 실제 데이터를 저장하는 인덱스 key가 존재하는 방식이다.
참고로, MySQL은 leaf node에 원본 데이터를 저장하는 방식이며(leaf = 테이블이라 간주하여도 무방), 이 외 PostgreSQL 및 Oracle은 leaf Node에 Heap table에 존재하는 원본 데이터의 TID(위치포인터)를 저장해둔다.
물리적인 구현 구조는 다를 수 있으나, DBMS가 통용하는 기본적인 자료구조가 바로 B+ Tree인 것이다.
이 자료구조 상에서 원하는 데이터를 찾으려면 어떻게 해야할까?
기본적으로 Leaf Node에 있는 데이터 페이지에 도달해야 한다.
Leaf Page
--------------------------------
PK(key) | 나머지 컬럼 데이터
--------------------------------
1 | name, age ...
2 | name, age ...
3 | name, age ...페이지에는 이처럼 PK 순서대로 정렬된 데이터들이 구성되어있다.
이 상태에서 인덱스가 없다면? Leaf Node의 첫번째 Page부터 시작하여 쭉 순차탐색이 이루어진다.
Page1 → Page2 → Page3 → Page4 ...모든 페이지를 읽어야 하고, 그렇기에 순차I/O가 발생한다는 것이 바로 여기서 발생이 된 것이다.
즉 Full Scan 발생 시 디스크에 저장된 모든 페이지를 Page1부터 읽어나가면서, 원하는 데이터를 찾을 때까지 읽어나간다.
인덱스를 사용한다면, Page1부터 읽는 순차I/O 대신 fan-out 구조에 의해 이분탐색에 의한 PK범위 및 이에 따른 I/O 횟수를 O(log_fan-out) 만큼 줄일 수 있다.
단 여기서의 조건은, 필터링하고자 하는 WHERE 조건 항목 및 그 정렬 상태가 인덱스의 정렬 조건 및 상태와 일치해야 의미가 있다는 점이다.
특히 MySQL은 테이블 구조 자체가 Clustered Index이므로 다소 헷갈릴 수 있는데, 인덱스의 유무는 사실 그렇게 중요하지 않다. 중요한 것은 인덱스를 통해 탐색하고자 하는 대상을 이분탐색으로 fan-out할 수 있느냐 없느냐이다.
이를 좀 더 깊게 기술하자면, 옵티마이저가 검색 공간을 얼마나 줄일 수 있느냐에 대한 내용과 일맥상통한다.
인덱스는 결국 선택도(카디널리티, 유일도), 포함여부가 아닌 일치여부에 대한 내용이다.
이분탐색을 진행하더라도 중요한 것은 이분탐색을 통해 범위를 줄이고, 그에 따른 읽어야 하는 leaf Node의 범위까지 줄일 수 있다는 것이다.
그 과정에 대해 한번 살펴보자.
SELECT * FROM member WHERE id = 532123;라는 쿼리가 존재한다고 하자.
만약, PK가 member id라면?
딱 보아도 Clustered Index를 사용하여 이분탐색을 통해 조회성능을 향상할 수 있을 것처럼 보인다.
예를 들어,
페이지 1개 크기 = 16KB이라 하고, 인덱스에 저장할 key의 크기를 8byte라 하고 포인터 혹은 원본데이터를 8byte라 하면,
보통 leaf node 1개와 Data Page 1개가 매핑이 되므로, leaf node 한개에 저장할 수 있는 데이터는 최대 16KB/16byte, 약 1000개나 된다.
이 경우 1개의 internal node가 100개의 leaf node를 가질 수 있다면, 1000*100(10만개)의 목록에 대해 관리가 가능하고, 이러한 interel node를 100개 가지고 있다면, Root node에서는 1000만개의 데이터 목록에 대해 관리가 가능하다.
"관리"가 가능하다는 의미는 이분탐색에 의한 범위를 node의 자식 수, 즉 fan-out만큼 줄여 그만큼의 I/O 횟수를 줄일 수 있다는 의미이다.
실제로도,
- Root Node 에서는
[ 1 ~ 100000 ]
[ 100001 ~ 300000 ]
[ 300001 ~ 700000 ]위와 같이 Internal node에 들어가기 전에, 각 internal 노드가 지닌 범위에 대해 관리하고 있기에 이분탐색을 통해, 순차 I/O 없이 단 한번의 I/O 만으로 id = 532123인 구간으로 들어간다.
- Internal Node 에서는
[ 300001 ~ 400000 ]
[ 400001 ~ 500000 ]
[ 500001 ~ 550000 ]
[ 550001 ~ 600000 ]또 해당 internal node가 가진 leaf node 목록 중, 해당 pk 범위에 해당하는 데이터 항목을 이분 탐색을 통해 탐색할 수 있게 되고, 이 역시 순차 I/O없이 단 한번의 I/O 만으로 id = 532123인 leaf node를 찾아 해당 데이터 페이지에 도달한다.
- leaf Node 에서는
500001
500002
...
532123
...
549999page에 정렬되어있는 1000개의 데이터 중 id = 532123인 목록을 찾고, 특히 이 경우 Clustered Index에 저장된 데이터 그 자체이기에 이후 추가 비용없이 데이터 조회를 완료한다.
참고로, 이 page가 검색이 자주 이루어진다면 이 page를 메모리 버퍼에 올려두어 캐싱을 통한 조회가 이루어지도록 한다.
순차 비용이라면 수십번의 I/O가 발생하였을 기존 상황에서, 이분탐색을 통한 조회범위 감소 및 이에 따른 I/O횟수 제거로 단 3 depth 기반의 3번의 I/O만으로 원하는 데이터를 찾게 된 것이다.
이 모든 것은 필터링하는 조건이 인덱스가 관리하는 대상과 동일하기에, 인덱스를 통한 검색과 더불어, 무엇보다 내부적으로 이분탐색을 통해 그 범위를 줄여나갈 수 있었기에 가능한 효과이다.
만약, PK가 member email로 member id가 아니라면?
이제 반대로 생각해보자.
SELECT * FROM member WHERE email = 'test@gamil.com';위 쿼리를 실행하면 어떻게 동작할까?
우리는 이제 MySQL InnoDB 기준으로 PK기반의 Clustered Index가 이미 기본적으로 존재하고, 이 구조를 테이블 그 자체로 사용하고 있다는 것을 알고 있다.
여기서 가져야할 의문점의 방향은, "인덱스를 있느냐, 타느냐"가 아니다. 인덱스를 사용할 수 있는 조건인지, 이분탐색을 통해 범위를 좁힐 수 있는 상황인지 판단을 해야하는 것이다.
옵티마이저는 위 쿼리를 보고, Clustered Index는 PK인 id기반으로 정렬이 되어있는데 뜬금없이 필터링 조건이 email로 걸려있는 것을 보고 인덱스 사용이 불가능할 것임을 판단하게 된다.
즉, PK 기반으로 정렬되어 있는데 생뚱맞은 email을 비교하면서 이분탐색이 가능한가? 아니다. 심지어, 내부 데이터에는 PK기반 정렬 데이터 페이지는 존재할 것이기에, 각 회원의 email은 알파벳 순서가 아닌 뒤죽박죽 섞여서 들어있을 것이다.
정리하면 이분탐색에 의한 범위 좁히기도 불가능하고, 애초에 정렬 대상과 필터링 조건이 맞지 않기에 효율적인 인덱스 활용이 불가능한 상황이기에 인덱스 스캔이 아닌 full table scan이 발생하여 순차 I/O를 진행하게 된다.
이해하였는가? MySQL은 테이블 그 자체가 Clusterd Index이다. Index가 존재하지 않은 것도 아니고, 엄밀히 말하면 타지 않은 것도 아니다.
다만, 인덱스를 효율적으로 사용하는 것이 불가능하고, 이분탐색으로 인한 범위 탐색도 불가능하기에 인덱스 탐색을 사용하지 않고 full table scan을 진행한 것이다.
2-2. 보조 인덱스의 사용
위 과정만 이해하여도, 인덱스를 어떻게 구성해야할지, PK는 어떻게 구성하는 것이 좋을지 전략이 마구마구 떠오를 것이다.
그 전에, 보조인덱스에 대해 먼저 살펴보자.
MySQL에서 PK를 인덱스 키로 가지는 Clustered Index라는 인덱스가 이미 존재하고 있기에, 더이상 우리는 인덱스의 유무나 인덱스 사용이라는 이분법적 전략에 휘말려선 안된다.
위 쿼리를 다시 가져와보자.
SELECT * FROM member WHERE email = 'test@gamil.com';이제 전략은 명확하다. 저 email 조건에 해당하는 데이터로 빨리 도달하기 위해서는, 해당 필터링 대상을 인덱스 내부에서 해당 조건을 기반으로 정렬이 되어있어야 하며, 잉 따라 이분탐색을 통한 범위도약이 가능한 상황이어야 한다.
즉, 인덱스에는 email이라는 조건으로 정렬되어있는 상태로 데이터가 정리가 되어있어야 하겠다.
따라서, email을 정렬대상으로 하는 보조인덱스를 생성하였다고 하자.
모든 탐색 과정은 위와 동일하게 발생한다.
다만, 보조인덱스의 leaf Node에는 해당 보조인덱스의 key값과 PK값이 같이 저장되어있다.
즉, 원본 데이터는 없는 대신 원본 데이터로 향하는 포인터 역할을 하는 PK값이 들어있는 것이다(참고로 Oracle, PostgreSQL은 Row ID값이 저장되어있다).
여기서 알아낸 PK값을 기반으로, PK기반 인덱스, 즉 Clustered Index의 탐색을 진행한다.
PK기반으로한 원본 데이터를 찾기 위한 look up이 다시 발생하는 것이며,
Secondary Index 탐색
+
Clustered Index 탐색이에 따라 I/O가 추가적으로 발생하게 되는 것이다.
여기서 Random I/O의 비용과 순차 I/O의 비용을 비교하는 과정이 수반된다.
Secondary Index (email 기준 정렬)
test → PK 3
test1 → PK 500000
test2 → PK 27
test3 → PK 90000보조인덱스를 통해 찾은 PK대상이 위와 같다고 하자.
결국 최종적으로 탐색해야 하는 원본 데이터, row가 PK를 기준으로 보았을때 흩어져있기 때문에 Random I/O로 인한 추가비용 소모를 고려해야 한다는 것이다.
이에 따라, 데이터가 원본 대비 20~25%를 상회한다면 보조인덱스를 사용한다 하더라도, 원본을 찾기 위한 Random I/O 기반의 PK Look up이 순차 I/O보다 오히려 더 많은 물리적 접근 비용을 발생할 것으로 판단하여 순차 I/O를 통한 데이터 탐색을 하게 되는 것이다.
여기까지 이해하였다면, Index만으로 모든 데이터를 찾게 되는 Covering Index의 개념까지 자연스럽게 이해할 수 있게 된다.
Covering Index는 Index 만으로 모든 데이터 효율 탐색 및 추출까지 한번에 가능한 상황을 의미한다.
Clustered Index를 사용한다면 이미 원본 데이터가 존재하는 상황이기에, Covering Index가 개념적으로는 가능하지만 실무적으로는 표현하지 않는다.
중요한 것은 보조인덱스만으로 Covering Index가 가능한 상황으로, 보조 인덱스에 정렬 대상과 함께 추출 대상까지 모두 포함되어 Clustered Index PK Look up 없이 즉시적인 데이터 추출이 가능한 상황이다.
이 상황을 실무에서 Covering Index라 표현한다.
2-3. PK는 순차성이 보장되어야 Clustered Index 구조 상 가장 최적에 가깝다.
Oracle, PostgreSQL은 인덱스와 실제 원본 데이터의 heap table이 완전히 분리된 구조이기에 논외로 하고, MySQL에서 PK 기반의 Clustered Index 자료구조에 최적화하는 것을 보장하기 위해 PK의 순차성을 보장해야 하는 이유에 대해 알아보자.
이 의미는, 예를 들어,
email과 같은 단순 자연적 속성이 아닌,
1,2,3,4,5,6,..이와 같이 인위적으로 PK의 Sequential를 보장하기 위해 구성된 일련번호이어야 한다는 것이다.
왜 그럴까?
마지막에 생성된 데이터는 마지막 leaf node의, 그 페이지의 마지막 row에 저장이 되는 것은 자명하다.
Leaf1 → Leaf2 → Leaf3 → [Leaf4 ← insert]이에 따라, PK를 또다시 정렬해야 하는 번거로움이 없어지며,
중요한 점은 정렬로 인해 발생하는 페이지 분할 및 페이지 단편화가 발생할 여지가 거의 없다는 것이다.
만약, 이메일을 PK로 하게 된다면 email을 알파벳 순서로 정렬하기 위한 정렬비용과 함께, 다른 페이지의 데이터 중간에 신규 PK 데이터를 삽입함으로써 페이지 분할 및 페이지 단편화가 발생할 가능성이 생긴다.
이러한 순차성이 보장된 PK를 구성함으로써, 인덱스의 정렬비용 및 공간낭비를 최소화할 수 있고 조회 시에도 그만큼 효용성이 증가하게 되는 효과를 볼 수 있다.
3. 1차 결론
인덱스가 성능적으로 유리한 이유에 대한 결론을 이제 명확히 내릴 수 있다.
디스크에 있음에도, B+ Tree 구조 및 인덱스 대상의 정렬 상태 등이 이분탐색을 통한 빠른 데이터 탐색과 추출이 가능하기에 빠른 것이다.
물론, 단순히 인덱스가 존재하는 것이 빠른 속도를 보장할 수는 없다.
반드시 이분탐색이 가능하도록 인덱싱 대상이 그에 맞게 정렬되어있어야 하고, 추가적으로 발생하는 look up 비용도 충분하게 제거가 되거나 효율성을 가지도록 확보해두어야 진정한 인덱스 활용이 가능해진다.
4. UQ, FK는 왜 자동 인덱스 생성 대상인가?
한가지 더, MySQL 기준으로 Unique 및 Foreign Key 제한조건이 걸려있는 속성은 인덱스의 자동 생성 대상이다.
왜 그럴까? 단순히 선택도(카디널리티)가 높아서, 성능을 보장할 수 있기에 자동으로 생성이 되는 것일까?
사실 선택도에 의한 성능 보장은 자동 생성의 이유이기 보다는, 자동 생성으로 생기는 성능적 이점, 부수적 효과에 가깝다.
자동 인덱스 생성 대상의 핵심은,
- 무결성 검증 비용을 이분탐색을 통해 O(logN)으로 줄일 수 있고,
- 참조 관계 유지 시 필수적인 탐색 경로이기 때문이다.
- UQ : 중복검증
데이터 삽입 시 UQ 컬럼의 경우 반드시 중복 유무를 검사해야 하는데, 이것을 빠르게 결과를 알 수 있는 토대가 바로 인덱스이다.
데이터베이스 측에서는 이러한 중복유무를 이분탐색을 통해 빠르게 찾기 위해 UQ속성에 대해 자동으로 인덱스를 생성한다.
당연히, 인덱스가 없다면 full table scan으로 일일이 모든 데이터에 대해 중복여부를 확인해야하지만 UQ에 대해서는 자동으로 인덱스를 생성하므로, 이분탐색을 통해 log fan out만큼 탐색 효율이 발생한다.
위에서 기술한 인덱스의 자료구조 및 동작원리를 이해하였다면, 금방 이해할 수 있는 내용이다.
- FK : 참조 관계 유지를 위한 가장 효율적이고 빠른 탐색 경로
FK의 경우, 자식 테이블에 삽입되는 데이터는 부모 데이터로 선행되어야 한다는 관계가 반드시 보장되어야 한다.
INSERT INTO order(member_id=10)insert, update 시 해당 데이터가 부모 테이블에 존재하는지 확인하기 위해서는 해당 PK가 부모 테이블에 존재하는지, 이와 동시에 자식 테이블에 FK로 온전하게 존재하는지 살펴보아야 한다.
부모테이블에 PK가 존재하는지 여부는 Clustered Index를 통해 진행할 수 있다.
따라서, 자식 테이블 측에서 쓰기 및 삭제 등의 연산 시 해당 값의 존재 여부를 빠르게 확인하기 위해 이 역시 자동으로 인덱스 생성 대상으로 취한 것이다.
이는 MySQL의 전략적인 선택으로, FK 컬럼은 조회, 쓰기 모든 연산에 많이 쓰이고 이에 기반한 탐색 역시 그만큼 많이 발생할 것으로 예상되어 자동으로 인덱스를 생성하여 참조 무결성 비용을 아끼도록 하기 위한 전략이다.
더불어, JOIN 성능에서 그 진가를 발휘하는데,
SELECT *
FROM order o
JOIN member m
ON o.member_id = m.id
WHERE o.member_id = 10;위와 같이 order(자식) 측에서 member(부모) 측으로 join하는 연산이 있다고 가정하자.
옵티마이저는 가장 첫번째로 자식 테이블을 먼저 스캔하고, 그 후 부모 테이블을 look up 하는 과정으로 계획을 구성한다.
PK기반으로 이루어진 보조 인덱스 -> Clustered Index의 pk look up 과정이 그대로 여기에서도 적용이 되는 것이다.
즉, order를 스캔하면서 해당 자식 테이블에 존재하는 order id(FK)를 확보하여, 그대로 order 테이블의 PK look up 대상으로 활용하고 PK 기반 탐색을 진행한다.
만약, FK 인덱스가 없었다면 order 테이블에서 해당 하는 데이터를 찾기 위해 full table scan을 하였을 것이다. 하지만 인덱스가 만들어져 있기에, 보조인덱스로 활용하여 member id도 빠르게 찾을 수 있고, 이를 기반으로 PK look up도 빠르게 진행할 수 있게 된다.
이 역시도, JOIN에서 발생하는 참조 동작을 원활히 하기 위한 전략적 구성이라 할 수 있겠다.
5. Oracle, PostgreSQL의 인덱스
서두에 기술하였듯이 MySQL의 인덱스 개념이 모든 DBMS에 전역적으로 적용이 되는 것은 아니다.
B+ Tree 구조 기반, 이에 따른 인덱스 동작 원리 등은 MySQL에만 적용되는 내용이며, 특히 MySQL의 경우 인덱스가 곧 테이블 구조 그 자체이기 때문에, 데이터의 저장 및 정렬 역시 B+ Tree 구조 기반으로 진행된다고 볼 수 있다.
하지만 이 외 Oracle 및 PostgreSQL에서는 인덱스만 B+ Tree 구조로 되어있으며, 실제 데이터는 Heap Table이라는 정렬되지 않은 상태의 구조로 저장이 이루어진다.
즉, 정리하면
MySQL - 인덱스/데이터 저장 B+ Tree 구조
Oracle, PostgreSQL - 인덱스 B+ Tree, 데이터 Heap Table 구조
와 같다.
인덱스와 데이터 저장 구조가 다른 DBMS에서는 마치 보조인덱스처럼, B+ Tree 기반의 인덱스에서 찾은 포인터를 기반으로 원본 테이블인 Heap Table에서 거의 즉각적으로 찾을 수 있다.
여기서 데이터베이스 간의 설계 사상이 나누어진다.
- MySQL은 인덱스와 데이터 저장 구조를 통합하여 조회 성능을 극대화
- PostgreSQL, Oracle은 인덱스와 데이터 저장구조를 철저히 분리하여 데이터 연산 비용을 낮추고, 동시성 운용 방안을 확보하였다(MVCC).
Heap table은 B+ Tree 구조기반도 아니고, MySQL처럼 PK기반 정렬된 상태도 아니다. 다만, 정렬되어있지 않은 채로 Page 기반의 데이터를 그대로 정렬한다.
Heap
├ Page
├ Page
├ Page
└ Page즉, key를 기준으로 한 배열도 아니고 page 순서도 의미 없이 단순 page 정렬로 되어있는 저장 구조일 뿐이다.
PostgreSQL, Oracle은 검색 성능을 다소 약화한 대신, INSERT 및 UPDATE 등의 처리 속도 및 MVCC 운용에 조금 더 집중을 한 것이다.
좀 더 살펴보자.
- PostgreSQL
index에서 Key(PK), TID를 저장하며 TID내부에 Page 번호 및 offset을 등록해놓는다.
(Page 번호, Page 내부 Offset)위와 같이, 마치 "Page 티켓"을 가지고 있는 것처럼, 이 내용을 바탕으로
Page가 나열된 Heap Table에서
Heap 파일에서
→ 1234번 Page로 이동
→ 7번째 Row 슬롯 접근Page 번호와 내부의 몇번째 offset에 원본 데이터가 있는지 확인하고, 그대로 Heap table을 탐색하여 데이터를 추출한다.
따라서, 사실상의 추가 비용없이 조회를 마칠 수 있다.
- Oracle
Oracle의 경우 index에서 Key(PK), Row Id를 등록하고, 이때 Row Id는
Data Object Number
File Number
Block Number
Row Number위와 같이, 어떤 파일에, 어느 블록, 어느 Row Number에 데이터를 기록하고 있는지 완전한 물리적 위치 주소를 보유하고 있다.
이 물리적 주소를 바탕으로,
Index → ROWID → 바로 Row 접근Oracle 역시 즉각적인 원본 데이터로 접근 및 추출이 가능하다.
언뜻보면 조회 성능이 InnoDB에 비해 더 안좋나 할 수 있지만, PK기반 조회일 경우 InnoDB는 이분탐색에 의한 I/O 횟수가 수회로 줄어들기 때문에 정렬이 되어있지 않은 Oracle(PostgreSQL도 마찬가지)에서는 PK기반 조회의 성능 향상을 기대할 수 없다.
또한 애초에 Covering Index라는 개념이 없기에, 인덱스를 통한 Covered 탐색이 존재하지 않고 추가 Random I/O 비용이 무조건 존재하며, 포인터 값을 저장하고 있기에 원본 테이블의 변경이 인덱스에도 영향을 준다.
각자 DB에 맞는 최적화, 성능 향상 방법이 존재하므로 그에 맞는 전략을 선택하는 것이 좋을 것이다.
참고로, InnoDB는 Clustered Index 구조를 사용하며, Secondary Index Leaf에는 물리 Row 포인터 대신 PK 값을 저장하는데, 이는 원본 데이터의 이동 및 삽입 등의변경점 발생 시 인덱스의 페이지 분할이나 이동 시 Secondary Index 전체를 수정해야 하는 비용을 줄이고, PK B+Tree만으로 Row 위치를 관리하기위해 보조 인덱스는 오로지 PK값만 저장한다(별도 정렬 비용없음).
또한 모든 데이터 접근 경로를 PK로 통일함으로써 엔진 구조를 단순화하고, 부가적으로 보조 인덱스 공간 효율에도 영향을 준다는 것을 기억하자.
6. 결론
위 내용을 통해, MySQL InnoDB를 기준으로 인덱스가 어떤 구조로 이루어져 있으며 어떠한 원리를 통해 조회 성능을 빠르게 확보할 수 있는지 단계적으로 살펴보았다.
더불어, 이러한 내용을 일반화하지 않기 위해 Oracle, PostgreSQL은 각각 어떠한 구조적 차이점이 존재하는지, 나아가 어떠한 설계적 사상이 차이를 두었는지 분석하여 DB 별로 어떠한 소모 비용이 발생하며, 이에 따른 최적화 방안을 어떻게 고려해야 하는지 알아보았다.
어떠한 것이든 절대적인 것은 존재하지 않는다. 항상 부작용, trade-off를 고려해가면서 과잉 최적화를 진행하지 않고, 그 상황과 상태에 적절한 방안이 어떠한 것인지 고민하고 생각할 수 있는 튼튼한 기본기를 제고하도록 하자.
그러한 튼튼한 기본기를 바탕으로, 최적의 방안을 설계하고 선택할 수 있도록 해야 할 것이다.
