OracleDB IOT로 MariaDB/MySQL 인덱스 이해하기

Random Access

 

SQL튜닝의 핵심은 랜덤액세스의 최소화입니다.
랜덤액세스는 인덱스 스캔을 통해 테이블을 찾아갈 때 발생하는 IO입니다. 인덱스를 사용하더라도 테이블에 접근하는 횟수가 많으면 성능이 저하되며, 어느정도 수치를 넘어서면 인덱스를 사용하는것이 오히려 더 느리다. 이 지점을 인덱스의 손익분기점이라고 할 수 있습니다.

DBMS에서도 랜덤IO를 최소화 시키기 위한 몇가지 기능들이 있습니다. 서비스 구조를 파악하여 적절히 사용한다면 충분히 좋은 대안이 될 수 있습니다. 문제는, 이런 기능들을 사용 할 때에는 정확히 알아야 한다는 것입니다.
Oracle 기본설정이 Heap 테이블(비IOT)이며, MS-SQL은 테이블 생성 시 기본설정으로 클러스터 테이블로 생성이 됩니다.
테이블 생성 시 이런 기능들을 정확히 모른 채 테이블을 Default 옵션으로만 생성하여 사용하는 경우가 많습니다.

IOT, Cluster Table과 Heap, Non-Cluster Table은 상당히 다른 성격을 가지고 있으며, 이 두 DBMS비교를 통해 MariaDB의 인덱스 탐색방법까지 이해할 수 있습니다.

---

1. 조회
  IOT는 데이터가 항상 정렬된 상태로 저장되기 때문에 PK인덱스 값으로 조회할 경우 버퍼피닝 효과가 극대화 된다. 대량데이터를 조회하여도 버퍼피닝 효과에 의해 IO가 상당히 감소하며 이는 분명한 장점이다.

2. 입력
  ORACLE의 IOT, MS-SQL의 클러스터 테이블은 테이블에 데이터를 Insert할 때부터 테이블과 동일한 정렬상태를 유지한다. 즉 인덱스의 Leaf블록에 테이블에 해당  row전체가 담기게 된다.
이때 중요한것은 항상 정렬상태가 보장되어 있는 채로 저장이 되어야 한다는 것이다. 즉, 리프블록에 PCTFREE설정에 의해 IndexSplit현상이 발생할 수 있다는 것이다. 이것은 Secondary Index탐색에 아주 중요한 부분이다.

2. Secondary Index
 IOT의 정렬기준으로 지정한 Primary Key외에 다른 인덱스를 생성하게 되면 조금은 복잡한 원리로 작동하게 되며, 이를 통해 MariaDB/MySQL, MS-SQL까지 이해할수 있다. 

▶ MS-SQL , MariaDB/MySQL

MS-SQL , MariaDB/MySQL

MS-SQL의 이전 버전에서는  Non Cluster Index는 Cluster Index의 Rowid값이 저장되어 있어, Non Cluster Index의 b-tree구조를 탐색 후 Clustered 인덱스를 찾아갔다. Clustered Index의 Split이 발생할 경우 Non Clustered 인덱스의 rowid값도 같이 갱신해야 한다는 부담이 있었다. 
7.0부터는 row를 저장하지 않고, Clustered Index의 KEY값을 저장하게 되었다. Clustered Index의 Split이 발생하여도 ROWID를 갱신하는 부담은 없어졌지만, 인덱스를 두번 탐색(Non Clustered Index -> Clustered Index)하는 부담이 있다.

또, Secondary Index에는 PK컬럼의 key값을 전부 저장하기 때문에, PK컬럼의 구성 전략도 중요하게 되었다.
innodb engine을 사용하는 MySQL, MariaDB는 시퀀스와 같이 순차 증가하는 컬럼 하나만을 PK로 지정하여 사용하는것이 성능면에서 유리하다.

---

▶ Oracle

IOT Secndary Index

Oracle은 상황에 따라 두가지 방식으로 IOT레코드에 엑세스 한다.
Secondary Index의 리프블록에는 IOT의 rowid와 PK키값을 같이 가지고 있으며, 이것을 Logical Rowid(Primary Key + Physical Guess) 라고 한다

 ① 의 방식으로 Secondary Index의 수직적 탐색 이후 ROWID를 찾아 IOT를 찾아가는데, Index Split등의 사유로 인해 그 위치에 찾아야 할 값이 없다면, 그 이후에  ②번의 방식으로 Primary Key값을 가지고 IOT의 Primary Key 인덱스를 수직적탐색하여 값을 찾는다.

Primary Key 인덱스를 수직적 탐색 하기 이전에 먼저 rowid를 통해 데이터가 있는지 먼저 확인하는 방식으로 인덱스의 무의미한 수직적 탐색을 반복하는것을 최소화 시켰다. 

(PCT_DIRECT_ACESS 등은 추후 포스팅 예정)

---

Heap Table과 IOT는 서로 좋다, 나쁘다의 개념은 아니다. 상황에 따라 IOT가 일반 Heap Table보다 유리한 경우가 분명히 존재하며, 전략적으로 사용할 경우 충분한 이점이 있다.

IOT가 유리한 경우
 1) 크기가 작고 NL조인으로 반복 Lookup하는 테이블
 2) 폭이 좁고 긴(=로우 수가 많은 테이블)
 3) Between, Like와 같은 조건으로 넓은 범위를 주로 검색하는 테이블 : 통계성 테이블 등
 4) 데이터의 입력과 조회 패턴이 서로 다른 테이블

 

하지만, MariaDB /MySQL처럼 Cluster Table 한가지 방식으로만 사용 가능한 경우에는 제한적인 부분이 많다.
OracleDB만 사용하였던 설계자 혹은 개발자가 MariaDB/MySQL환경에 구축을 하게 되면 이를 정확히 모르고, Oracle처럼 설계 한다면 용량과 성능 면에서 비효율이 있다.

때문에 MariaDB/MySQL은 Secondary Index에 Primary Key 컬럼의 Key를 모두 저장한다. Primary Key가 여러개일 경우 전부 저장을 하게 된다. 이렇기 때문에 Primary Key는 최소화해야 하며 Auto increment와 같은 순차적으로 증가하는 SEQ컬럼 하나로 지정하는것이 좋다.

논리설계 단계에서는 업무요건에 맞게 Primary Key들을 지정할 수도 있다. 하지만 DB 테이블을 직접 생성하는 단계에서는 이를 명확히 알고 생성하는것이 중요하다.