느낀점:
이번에는 테이블 액세스 최소화에 대해 학습했다. 학습하는 과정에서 이전에 배웠던 인덱스, I/O 등 여러 개념들이 자연스럽게 연결되며 활용되었다.
가장 인상 깊었던 부분은 MySQL과 오라클의 트리 구조 비교(이 부분은 공부하다가 따로 찾아봤다) 그리고 멀티블록 접근과 싱글블록 접근을 다시 깊이 있게 이해하게 된 점이다. 이전에 배운 개념이지만, 이번에 다시 등장했을 때 최근 사례가 떠오르면서 더 명확하게 이해할 수 있었다.
랜덤 I/O를 줄이기 위한 MySQL과 오라클의 접근 방식
랜덤 I/O를 개선하는 방법으로 오라클은 IOT(Index-Organized Table)을 옵션으로 제공하는 반면,
MySQL(InnoDB)은 기본적으로 클러스터형 인덱스를 사용한다고 한다.
MySQL과 오라클이 트리 구조가 다르다는 것은 알고 있었지만, 이번 기회에 다시 학습하면서 그 차이를 설계 철학적으로 이해할 수 있어 좋았다.
"왜 오라클과 MySQL은 이렇게 다르게 설계했을까?"라는 고민이 들었고, 이를 통해 각 DBMS의 철학적 차이를 알게 되었다.
- 오라클: 범용성과 유연성을 고려한 설계를 지향
- MySQL(InnoDB): OLTP(트랜잭션)에 최적화된 구조를 지향
이 차이가 발생하는 이유를 보면,
- 오라클은 ROWID 기반 인덱스를 사용하며, 테이블과 인덱스를 분리하여 관리하는 방식
- MySQL(InnoDB)는 리프 노드에 테이블 데이터를 직접 저장하는 방식
- 관리의 어려움이 있지만, 기본 키(Primary Key) 조회 속도가 빠르게 동작하는 장점이 있음
시퀀셜 액세스가 근본적으로 왜 빠른가?
이번 학습 중 인덱스 손익분기점(Break-even Point of Index)을 공부하다가 랜덤 액세스와 시퀀셜 액세스의 차이가 다시 등장했다.
이 과정에서 시퀀셜 액세스가 근본적으로 왜 빠른지에 대해 명확하게 이해할 수 있었다.
최근 대시설2 스터디에서 "순차적으로 접근하는 것이 왜 빠를까?"라는 질문이 나왔지만,
단순히 "디스크 이동이 없어서 빠르다"는 설명만으로는 충분히 납득하기 어려웠다.
그러나 이번에 학습하면서 시퀀셜 액세스가 빠른 이유를 테이블 액세스 방식과 연결해서 이해할 수 있었다.
- 테이블 풀 스캔은 멀티블록 I/O(Multiblock I/O)를 사용 → 한 번에 여러 개의 블록을 가져올 수 있음.
- 인덱스를 통한 ROWID 기반 테이블 액세스는 싱글블록 I/O(Single Block I/O)를 사용 → 한 번에 하나의 블록만 읽음.
결국 한 번에 여러 개의 블록을 가져오는 것과 하나씩 가져오는 것의 차이가 성능 차이를 만들며, 이 방식 자체가 근본적으로 다르다는 것을 이해했다.
최근 스터디에서 명확한 해법을 찾지 못했었는데, 개인 공부를 하다가 스스로 답을 찾아내서 기뻤다.
학습 :
3장 인덱스 튜닝
3. 1 테이블 엑세스 최소화
본 절에서는 테이블 랜덤 엑세스를 최소화하는 구체적인 방법들을 소개하는데, 그에 앞서 지금까지 학습한 내용을 토대로 테이블 랜덤 엑세스가 성능에 미치는 영향을 정리해 보자.
3. 1. 1 테이블 랜덤 액세스
인덱스에 대한 맹신 또는 섣부른 자신감
SQL 튜닝, 특히 인덱스 튜닝을 공부하면서 누구나 두 번 놀란다. 첫 번째는 아무리 데이터가 많아도 인덱스를 사용하니까 데이터가 금방 조회된다는 사실에 놀란다. 두 번째는 대량 데이터를 조회할 때 인덱스를 사용하니 테이블 전체를 스캔할 때보다 훨씬 느리다는 사실에 놀란다.
인덱스 ROWID는 물리적 주소? 논리적 주소?
인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스엣 ㅓ빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값, 즉 ROWID를 얻으려는 데 있다.
그렇다면 인덱스 ROWID는 물리적 주소일까, 논리적 주소일까? 인덱스 ROWID를 물리적 주소라고 생각한다면, 그것이 데이터파일 번호, 오브젝트 번호, 블록 번호 같은 물리적 요소로 구성돼 있어서다. 그런 의미에서 ROWID 를 물리적 주소라고 설명한다면 틀리다고 말할 수 없다. 하지만 인덱스 ROWID는 물리적 주소보다 논리적 주소에 가깝다. 물리적으로 직접 연결되지 않고 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있기 때문이다.
데이터베이스 인덱스를 설명할 때 항상 도서 색인에 비유한다. 색인데 기록된 페이지 번호가 ROWID에 해당한다. 프로그래밍 언어에서 포인터pointer를 설명할 때도 흔히 도서 색인이 기록된 페이지 번호에 비유한다. 그래서인지 인덱스 ROWID를 포인터라고 생각하는 분이 아주 많다.
프로그래밍 언어에서 포인터는 메모리 주소값을 담는 변수를 말한다. 메모리에 있는 데이터를 포인터로 액세스할 때 얼마나 빠른지, 프로그래밍 해 본 독자라면 잘 알 것이다. 메모리상에서 데이터를 찾아가는 데 있어 포인터만큼 빠른 방법은 없으며, 그 비용은 0에 가깝다. 물리적으로 직접 연결된 구조와 다름없다.
메모리 상에서의 위치 정보인포인터를 생각하며 인덱스 ROWID를 물리적 주소로 이해했다면 잘못 이해한 것이다. 인덱스 ROWID는 포인터가 아니다. 지시봉처럼 어딘가를 가리킨다는 의미에서는 포인터가 맞다.
정리하면, 인덱스 ROWID는 논리적 주소다. 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다. 테이블 레코드와 물리적으로 직접 연결된 구조는 더더욱 아니다.
메인 메모리 DB와 비교
메인 메모리 DBMMDB에 대해 들어 본 적이 있는가? 말 그대로 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB라고 할 수 있다. 그런데 잘 튜닝된 OLTP성 데이터베이스 시스템이라면 버퍼캐시 히트율이 99% 이상이다. 디스크를 경유하지 않고 대부분 데이터를 메모리에서 읽는다는 뜻이다. 그런데도 메인 메모리 DB만큼 빠르지는 않다. 특히 대량 데이터를 인덱스로 엑세스할 때는 엄청난 차이가 난다. 왜 그럴까?
메인 메모리 DB 아키텍처를 소개함으로써 방금 던진 질문에 대한 답을 찾고자 한다. 벤더에 따라 내부 아키텍처가 모두 다르겠지만, 어떤 메인 메모리 DB의 경우 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 생서한다. 이때 인덱스는 오라클처럼 디스크 상의 주소정보를 갖는 게 아니라 메모리상의 주소정보, 즉 포인터를 갖는다. 따라서 인덱스를 경유해 테이블을 엑세스하는 비용이 오라클과 비교할 수 없을 정도로 낮다.
질문에 대한 답을 이해했으리라 믿는다. 오라클은 테이블 블록이 수시로 버커캐시에서 밀려났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 연결할 수 없는 구조다. 메모리 주소 정보(포인터)가 아닌 디스크 주소 정보DBA, Data Block Address를 이용해 해시 알고리즘 버퍼 블록을 찾아간다.
메인 메모리 DB의 우월성을 강조하려는 게 아니다. 일반 DBMS에서 인덱스 ROWID를 이용한 테이블 엑세스가 생각만큼 빠르지 않은 이유를 설명하려는 것이다.
I/O 메커니즘 복습
DBA(=데이터파일번호 + 블록번호)는 디스크 상에서 블록을 찾기 위한 정보다. 그렇다고 매번 디스크에서 블록을 읽을 수는 없다. I/O 성능을 높이려면 버퍼캐시를 활용해야 한다. 그래서 블록을 읽을 때는 디스크로 가기전에 버퍼캐시부터 찾아본다. 일고자 하는 DBA를 해시 함수에 입력해서 해시 체인을 찾고 거기서 버퍼 헤더를 찾는다.
캐시에 적재할 때와 읽을 때 같은 해시 함수를 사용하므로 버퍼 헤더는 항상 같은 해시 체인에 연결된다. 반면, 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱되는데, 그 메모리 주소값을 버퍼 헤더가 가지고 있다. 정리하면, 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록은 찾아간다.
인덱스로 테이블 블록을 엑세스할 때는 리프 블록에서 읽은 ROWID를 분해해서 DBA 정보를 얻고, 테이블을 Full Scan할 때는 인스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다.
다시 말하지만, 인덱스 ROWID는 포인터가 아니다. 디스크 상에서테이블 레코드를 찾아가기 위한 논리적인 주소 정보다. ROWID가 가리키는 테이블 블록을 버퍼캐시에서 먼저 찾아보고, 못 찾을 때만 디스크에서 블록을 읽는다. 물론 버퍼 캐시에 적재한 후에 읽는다.
설령 모든 데이터가 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 획득 과정을 반복해야 한다. 동시 엑세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다. 이처럼 인덱스 ROWID를 이용한 테이블 엑세스는 생각보다 고비용 구조다.
인덱스 ROWID는 우편주소
디스크 DB(오라클, SQL Server 같은 일반 DBMS)가 사용하는 ROWID를 우편주소에, 메인 메모리 DB가 사용하는 포인터를 전화번호에 비유할 수 있다. 전화통신은 물리적으로 연결된 통신망을 이용하므로 전화번호를 누르면 곧바로 상대방과 통화할 수 있다. 하지만, 우편통신은 봉투에 적힌 대로 우체부 아저씨가 일일이 찾아다니는 구조이므로 전화와는 비교할 수 없이 느리다.
오라클에서 하나의 레코드를 찾아가는 데 있어 가장 빠르다고 알려진 'ROWID'에 의한 테이블 엑세스'가 얼마나 고비용 연산인지 여기서 정확히 이해하기 바란다.
3. 1. 2 인덱스 클러스터링 팩터
클러스터링 팩터Clustering Factor, 이하 CF 는 '군집성 계수' 쯤으로 번역할 수 있는 용어로서, 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다. CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다.
인덱스 클러스터링 팩터 효과
CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋다고 했는데, 이는 테이블 엑세스량에 비해 블록 I/O가 적게 발생함을 의미한다. 여기서 의문이 생긴다. 인덱스 레코드마다 테이블 레코드를 건건이 블록 단위로 I/O 한다면, CF가 달라도 블록 I/O 발생량에 차이가 없어야 하지 않나?
인덱스 ROWID로 테이블을 액세스할 때, 오라클은 래치 획득과 해시 체인 스캔 과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터를 바로 해제하지 않고 일단 유지한다. 이를 '버퍼 Pinning'이라고 부른다.
이 상태에서 다음 인덱스 레코드를 읽었는데, 마침 '직전과 같은' 테이블 블록을 가리ㅣㄴ다. 그러면 래치 획득과 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있다. 논리적인 블록 I/O 과정을 생략할 수 있는 것이다.
3. 1. 3 인덱스 손익분기점
인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다. 따라서 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다. Index Range Scan에 의한 테이블 엑세스가 Table Full Scan보다 느려지는 지점을 흔히 '인덱스 손익분기점'이라고 부른다.
인덱스를 이용한 테이블 액세스가 Table Full Scan 보다 더 느려지게 만드는 가장 핵심적인 두 가지 요인은 다음과 같다.
- Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이다.
- Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O 방식이다.
인덱스가 항상 좋을 수 없음을 설명하려고 손익분기점이란 개념을 사용했을 뿐, 이를 높이기 위해 어떤 조치를 해야 한다는 뜻으로 오해하지 말기 바란다. 즉, 테이블 스캔이 항상 나쁜 것은 아니며, 바꿔 말해 인덱스 스캔이 항상 좋은 것도 아니라는 사실을 설명하는 데 목적이 있다.
온라인 프로그램 튜닝 vs 배치 프로그램 튜닝
온라인 프로그램은 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 무엇보다 중요하다. 조인도 대부분 NL 방식을 사용한다. NL 조인은 인덱스를 이용하는 조인 방식이다. 인덱스를 이용해 소트 연산을 생략함으로써 부분범위 처리 방식으로 구현할 수 있다면, 온라인 환경에서 대량 데이터를 조회할 때도 아주 빠른 응답 속도를 낼 수 있다.
반면, 대량 데이터를 읽고 갱신하는 배치Batch 프로그램은 항상 전체범위 처리 기준으로 튜닝해야 한다. 즉, 처리대상 집합 중 일부를 빠르게 처리하는 것이 아니라 전체를 빠르게 처리하는 것을 목표로 삼아야 한다. 대량 데이터를 빠르게 처리하려면, 인덱스와 NL 조인보다 Full Scan과 해시 조인이 유리하다.
대량 배치 프로그램에선 인덱스보다 Full Scan이 효과적이지만, 초대량 테이블을 Full Scan 하면 상당히 오래 기다려야 하고 시스템에 주는 부담도 적지 않다. 따라서 배치 프로그램에서는 파티션 활용 전략이 매우 중요한 튜닝 요소이고, 병렬 처리까지 더할 수 있으면 금상첨화다.
파티션 테이블에서도 인덱스를 사용할 수 있지만, 월 단위로 파티션한 테이블에서 특정 월 또는 몇 개월 치 데이터를 조회할 때 인덱스는 좋은 선택이 아니다. 보름 또는 일주일 치 데이터를 조회하더라도 인덱스보다 Full Scan이 유리하며, 심지어 2~3일 데이터를 조회할 때도 Full Scan이 유리할 수 있다. (관리적 측면을 배제하고 성능 측면에서만 보면) 테이블을 파티셔닝하는 이유는 결국 Full Scan을 빠르게 처리하기 위해서다.
3. 1. 4 인덱스 컬럼 추가
테이블 엑세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는 것이다.
3. 1. 5 인덱스만 읽고 처리
테이블 액세스 단계 필터 조건에 의해 버려지는 레코드가 많을 때, 인덱스에 컬럼을 추가함으로써 얻는 성능 효과를 살펴봤다. 그런데 테이블 랜덤 액세스가 아무리 많아도 필터 조건에 의해 버려지는 레코드가 거의 없다면 거기에 비효율은 없다. 들인 노력만큼 결과를 얻었기 때문이다. 이때는 어떻게 튜닝해야 할까?
비효율이 없더라도 인덱스 스캔 과정에서 얻은 데이터가 많다면 그만큼 랜덤 액세스가 많이 발생하므로 성능이 느릴 수 밖에 없다. 쿼리나 인덱스에 문제가 있어서가 아니라 절대 일량이 많아서 느린 거니 어쩔 수 없다. 느린대로 사용하는 수밖에!
반드시 성능을 개선해야 한다면, 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게 하는 방법을 고려해 볼 수 있다. 참고로, 인덱스만 읽어서 처리하는 쿼리를 Covered 쿼리라고 부르며, 그 쿼리에 사용한 인덱스를 Covered 인덱스라고 부른다.
3. 1. 6 인덱스 구조 테이블
인덱스를 이용한 테이블 액세스가 고비용 구조라고 하니, 랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성하면 어떨까? 실제 그런 방법이 제공되는데, 오라클은 이를 IOTIndex-Organized Table 라고 부른다. 참고로, MS-SQL Server는 '클러스터형Clustered 인덱스 ' 라고 부른다.
테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스와 달리 IOT는 그 자리에 테이블 데이터를 갖는다. 즉, 테이블 블록에 있어야 할 데이터를 리프 블록에 모두 저장하고 있다. IOT에서는 '인덱스 리프 블록이 곧 데이터 블록'이다.
IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나다. 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있으므로 랜덤 액세스가 아닌 시퀀셜 방식으로 데이터를 액세스한다. 이 때문에 BETWEEN이나 부등호 조건으로 넓은 범위를 읽을 때 유리하다.
3. 1. 7 클러스터 테이블
클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 두 가지가 있다. 인덱스 클러스터부터 살펴보자.
인덱스 클러스터 테이블
인덱스 클러스터 테이블은 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조다. 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결한다.
심지어 여러 테이블 레코드를 같은 블록에 저장할 수 도 있는데, 이를 '다중 테이블 클러스터'라고 부른다. 일반 테이블은 하나의 데이터 블록을 여러 테이블이 공유할 수 없음을 상기하기 바란다.
이름 때문에 SQL 서버나 Sybase에서 말하는 '클러스터형 인덱스Clustered Index'와 같다고 생각할지 모르지만 클러스터형 인덱스는 오히려 IOT에 가깝다. 오라클 클러스터는 키 값이 같은 데이터를 같은 공간에 저장해 둘 뿐, IOT나 SQL Server의 클러스터형 인덱스처럼 정렬하지는 않는다.
클러스터에 테이블을 담기 전에 클러스터 인덱스를 반드시 정의해야 한다. 왜냐하면, 클러스터 인덱스는 데이터 검색 용도로 사용할 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용하기 때문이다.
클러스터 인덱스도 일반 B * Tree 인덱스 구조를 사용하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가리킨다는 점이 다르다. 즉, 일반 테이블에 생성한 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다. 따라서 클러스터 인덱스의 키 값은 항상 Unique하다(=중복 값이 없다.)
이런 구조적 특성 때문에 클러스터 인덱스를 스캔하면서 값을 찾을 때는 랜덤 액세스가( 클러스터 체인을스캔하면서 발생하는 랜덤 액세스는 제외하고) 값 하나당 한 번씩 밖에 발생하지 않는다. 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 대문에 넓은 범위를 읽더라도 비효율이 없다는 게 핵심 원리다.
해시 클러스터 테이블
해시 클러스터는 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르다.
