0. Index
DataBase의 Index는 어떤 데이터가 어디에 있는지 정보를 제공하는 주소록과 유사하다. Index는 Table의 모든 행을 스캔하지 않고도 필요한 데이터를 빠르게 찾을 수 있도록 도와준다.
1. DataBase 관점에서 Table의 Index
1-1. Index의 작동 방식:
- DataBase 버퍼 캐시: **DataBase는 하드 디스크의 데이터에 액세스하기 전에 메모리의 버퍼 캐시를 확인한다. 자주 사용하는 Table은 캐시에 저장되며, 해당 Table에서 데이터를 찾으면 빠르게 검색된다.
- 하드 디스크 검색:** 데이터가 캐시에 없는 경우 DataBase는 하드 디스크의 데이터 파일을 찾기 시작한다.
- Index 사용:** Index를 사용하면 버퍼 캐시와 하드 디스크 검색을 건너뛰고 데이터가 있는 주소로 바로 이동하여 전국을 조사하지 않고 특정 주소로 택배를 배달하는 것과 같이 데이터가 있는 주소로 바로 이동한다(Full Scan).
1-2. Index 생성 원리
Index를 생성할 때 중간에 데이터가 변경되면 문제가 발생할 수 있다. 따라서 데이터 변경을 방지하기 위해 예방 조치를 취하고 메모리에서 데이터를 정렬한다(PGA의 정렬 영역). 공간이 충분하지 않으면 임시 Table 공간이 사용된다. 전체 Table을 스캔한 후 메모리에서 정렬하고 블록을 쓰는 과정이 포함된다.
PGA는 Program Global Area의 약자로, 단독 사용하는 공간이다. 따라서 Private Global Area라고 불리기도 한다. PGA는 프로세스에 대한 데이터와 제어정보가 포함된 비 공유 메모리 영역으로 서버 프로세스가 시작될 때 생성되며, DataBase에 접속하는 모든 사용자에게 할당된 각각의 서버 프로세스가 독자적으로 사용하는 오라클 DataBase의 메모리 공간이다.
PGA는 서버 프로세스가 시작될 때 오라클 DataBase에 의해 생성되며 프로세스가 종료될 때 해제된다. PGA는 SQL의 작업공간이기 때문에 정렬 작업을 할 때 주로 사용된다. 따라서 SQL 정렬 작업을 위해 PGA 메모리 영역을 사용할 경우, PGA 크기 내에서 정렬이 일어나면 메모리 내에서 정렬되는 것이므로 수행속도가 빠르고 PGA 크기를 초과하면 디스크에서 정렬이 일어나므로 수행속도가 느려지게 된다.
1-3. Index 구조 및 작동 원리(B-TREE Index 기준)
Index는 Key(Indexing을 위해 지정된 컬럼)와 ROWID의 두 개의 컬럼으로 구성된다.
예시:
EMP Table에서 EMPNO = 7902에 대한 쿼리를 고려하고, 데이터 파일에 100,000개의 블록이 있다고 가정한다:
- Index가 없는 경우:** 100,000개의 데이터 조각이 모두 DB 버퍼 캐시에 복사되고 전체 스캔을 통해 데이터가 검색된다.
- Index가 있는 경우:** WHERE 절 열이 Index의 키로 생성되었는지 확인한 후, 서버가 먼저 Index로 이동하여 7902 정보가 있는 ROWID를 확인한다. 해당 ROWID에서 블록을 찾아 DB Buffer 캐시로 가져와서 정보를 표시한다.
1-4. Index의 종류
- B-TREE Index : 실시간 데이터 입력 및 수정이 필요한 OLTP(온라인 트랜잭션 처리) 환경에서 사용된다.
- 구조:** 루트 블록(루트 블록에 대한 정보), 브랜치 블록(브랜치 블록에 대한 정보), 리프 블록(실제 데이터의 주소)을 포함한다.
- 비트맵 Index : 분석이나 통계 정보 출력을 위한 OLAP(온라인 분석 처리)에서 사용된다. 데이터 값의 종류가 적고 동일한 데이터가 많을 때 자주 사용된다.
1-5. Index 주의 사항
- SELECT 작업 :** Index는 일반적으로 Indexing된 열이 쿼리 조건에서 사용되는 한 선택 쿼리 성능을 향상시킨다.
- UPDATE 작업 :** 기존 데이터를 삭제하고 새로운 값을 삽입하는 이중 프로세스가 필요하여 부하가 증가한다.
- INSERT 작업 :** Index가 많을수록 Index 분할을 유발하여 성능 문제를 일으킬 수 있다.
- DELETE 작업 :** Index에서 삭제된 데이터는 제거되지 않고 미사용으로만 표시되어 성능 문제가 발생할 수 있다.
1-6. Index 생성 시 고려 사항
- 데이터 검색을 위한 Index는 Table 전체 행 수의 15% 이하로 생성한다.
- 작은 Table이나 수정이 잦은 Table은 Indexing 하지 않는다.
- 고유성이 높은 열을 지정하고 NULL 값이 많은 열은 피한다.
- 열 순서와 SQL 쿼리 구조를 고려한다.
- 너무 많은 Index를 만들면 성능이 저하될 수 있으므로 주의한다.
- Index 사용과 빠른 검색 및 수정, 삭제, 삽입과 같은 작업의 필요성 간에 균형을 맞추는 것이 좋다.
DataBase이러한 측면을 이해하면 블록체인 데이터 엔지니어로서의 요구사항에 맞춰 DataBase 성능을 최적화하여 대량의 데이터를 효율적으로 처리하고, 메트릭을 빠르게 계산하며, DataBase에 과부하를 주지 않으면서 쿼리 속도를 줄일 수 있다.
2. Hardware 관점에서 Table의 Index
2-1. Index 유형
DataBase는 종종 B-트리, 해시 Index, 비트맵 Index와 같은 다양한 유형의 Index를 제공한다. 선택은 데이터의 특성과 실행하려는 쿼리 유형에 따라 달라진다. B-트리는 가장 일반적인 Index 구조 중 하나이다.
2-2. Index 구조 작업
B-트리 Index
- 트리 구조:** B-트리 Index는 검색, 삽입, 삭제가 효율적으로 수행될 수 있도록 정렬된 데이터를 유지하는 균형 잡힌 트리 구조이다.
- 노드:** B-트리의 각 노드에는 키와 포인터가 포함되어 있다. 키는 마커 역할을 하고 포인터는 검색을 올바른 데이터 하위 집합으로 안내한다.
- 리프:** 트리의 리프 노드는 DataBase의 실제 레코드에 대한 포인터(또는 때로는 레코드로 연결되는 클러스터링 키에 대한 포인터)를 보유한다.
해시 색인
- 해시 함수:** 해시 Index는 해시 함수를 사용하여 값을 배열의 특정 위치에 매핑한다. 동일한 값으로 해시된 모든 값은 해당 위치의 링크된 목록에 저장된다.
- 버킷:** 배열을 해시 Table이라고도 하고 각 배열 요소를 버킷이라고도 한다.
2-3. Index로 쿼리
Indexing된 열을 사용할 수 있는 쿼리가 실행되는 경우이다:
- B-트리:** DataBase 엔진은 B-트리의 루트에서 시작하여 각 노드의 키를 사용하여 트리를 따라 내려가면서 따라야 할 올바른 경로를 결정한다. 리프 노드에 도달하면 올바른 데이터 하위 집합을 찾은 것이다.
- 해시 Index: 쿼리의 값이 해시되고 해시 Table의 해당 버킷에 직접 액세스한다. 이 방식은 조회 속도가 매우 빠르지만 B-트리에 비해 유연성이 떨어진다.
2-4. Index 업데이트
- 삽입 및 삭제:** 데이터가 삽입되거나 삭제되면 그에 따라 Index를 업데이트해야 한다. B-트리에서는 트리의 균형을 유지하기 위해 노드를 분할하거나 병합해야 할 수 있다. 해시 Index에서는 해당 버킷의 링크된 목록을 업데이트해야 할 수 있다.
- 유지 관리: 시간이 지남에 따라, 특히 데이터 수정이 많은 경우 Index를 재구성하거나 재구축해야 할 수 있다. 이렇게 해야 Index가 계속 효율적으로 작동할 수 있다.
2-5. 스토리지 고려 사항
Index는 추가 디스크 공간을 소비하며, 그 양은 Index 유형과 Indexing된 열의 수와 크기에 따라 달라진다.
3. Table에서 Index 추가
DataBase의 기존 데이터에 Index를 추가할 수 있으며, 이는 특정 열에 대한 쿼리 성능을 최적화하기 위해 종종 수행된다. Index를 만드는 정확한 방법은 사용 중인 DataBase 관리 시스템(DBMS)에 따라 다르다. 또한, DataBase에서 Index를 만드는 것은 특히 지금 작업 중인 것과 같은 대규모 데이터 집합을 다룰 때 쿼리의 효율성을 개선하는 강력한 도구가 될 수 있다. 아래에서는 기존 열에 Index를 생성하는 방법, Index인 새 열을 생성하는 방법, Indexing된 열이 있는 새 테이블을 생성하는 방법을 SQL과 Python에서 모두 SQLAlchemy 또는 Pandas와 같은 라이브러리를 사용하여 설명하겠다.
열 식별하기: 색인할 열을 결정한다. 일반적으로 쿼리 조건에서 자주 사용되는 열이어야 한다.
Index 유형 선택하기: 사용 중인 DBMS에 따라 다양한 유형의 Index(예: B-tree, 해시 등)에 대한 옵션이 있을 수 있다. 쿼리 패턴과 데이터 구조에 가장 적합한 것을 선택한다.
**Index 생성: 대부분의 SQL 기반 DataBase에서는 다음과 같은 SQL 명령을 사용하여 Index를 생성할 수 있다:
3-1. 기존 컬럼에 Index 생성
SQL:
CREATE INDEX index_name ON table_name (column_name);파이썬(SQLAlchemy 사용):
from sqlalchemy import Index
index_name = Index('index_name', table_name.c.column_name)
index_name.create(bind=engine)3-2. Index인 새 컬럼 생성
SQL:
ALTER TABLE table_name ADD column_name data_type;
CREATE INDEX index_name ON table_name (column_name);파이썬 (SQLAlchemy 사용):
from sqlalchemy import Column, Index
# 새 칼럼 추가
table_name.addColumn(Column('column_name', data_type))
# Index 생성
index_name = Index('index_name', table_name.c.column_name)
index_name.create(bind=engine)3-3. Indexing된 열이 있는 새 테이블 생성
SQL:
CREATE TABLE table_name (
COLUMN_NAME1 DATA_TYPE1,
COLUMN_NAME2 DATA_TYPE2,
...
INDEX index_name (indexed_column_name)
);파이썬(SQLAlchemy 사용):
from sqlalchemy import Table, Column, Index
table_name = Table('table_name', metadata,
Column('column_name1', data_type1),
Column('column_name2', data_type2),
...
Index('index_name', 'indexed_column_name')
)
table_name.create(bind=engine)3-4. 열 Indexing 조건
- 쿼리에서 자주 사용됨: 칼럼이 WHERE 절에 자주 나타나거나 쿼리에서 자주 비교되는 경우 Indexing에 적합한 후보이다.
- 데이터 고유성: 고유성이 높고 값의 범위가 넓은 열은 일반적으로 Indexing에 적합하다. 반복되는 값이 많은 열을 Indexing하는 것은 큰 이점을 제공하지 못할 수 있다.
- 데이터 유형: 일부 데이터 유형은 Indexing하는 것이 더 효율적일 수 있다. 숫자 또는 날짜/시간 유형은 텍스트 또는 BLOB 유형에 비해 더 나은 성능을 제공할 수 있다.
- 읽기 작업과 쓰기 작업의 균형: 테이블을 자주 업데이트, 삭제 또는 삽입하는 경우 Indexing을 사용하면 이러한 작업 속도가 느려질 수 있다. 읽기 작업이 많은 테이블의 열에 대해서는 Indexing을 고려해야 한다.
Indexing은 쿼리 속도를 크게 높일 수 있지만, 저장 공간을 추가로 소모하고 쓰기 작업의 속도를 저하시킬 수 있다는 점에 유의한다. 블록체인 데이터 엔지니어는 이러한 측면을 고려하면 데이터를 처리할 때 높은 정확도와 효율성이라는 목표를 달성하는 데 도움이 될 수 있다.
4. 결론
Indexing은 복잡한 트리 또는 해시 구조의 균형을 맞춰 빠른 검색 기능을 구현하는 DataBase의 강력한 도구이다. 내부 작업에는 데이터에 대한 효율적인 액세스를 제공하는 동시에 데이터 변경 시 Index의 무결성을 유지하도록 설계된 데이터 구조와 알고리즘이 포함된다. 이는 물론 방대한 양의 데이터를 효율적으로 처리하고, 메트릭을 빠르게 계산하며, DataBase에 과부하를 주지 않으면서 쿼리 속도를 줄여야 하는 데이터 엔지니어의 요구사항과도 일치하는 부분이다.
