OLTP, OLAP

 

OLTP (On-Line Transaction Processing)

실시간으로 서버가 자료를 처리하는 과정을 의미하며, 여러 과정의 연산이 하나의 단위 프로세스(= 트랜잭션) 으로 실행하는 프로세스입니다.

 

예를 들면

A가 B에게 10,000원을 이체한다면 아래의 단계들이 하나의 프로세스로 이루어져야 하며, 중간에 오류가 발생하는 경우 모든 단계를 되돌려야합니다.

1. A 의 계좌에서 10,000원이 인출되어
2. B 의 계좌에 10,000원이 입급됩니다. 

 

ROW단으로 저장하는 MySQL, MSSQL 등 RDBMS에서 사용하는 프로세스입니다.

해당 DB들은 물리적으로 데이터 저장 시 아래와 같이 하나의 레코드안에 파일로서 저장을 하기 때문에 검색시 모든 데이터를 풀스캔 하여야 합니다. 

FILE 1 : “001;Cho;Terry;Seoul;30,002;Lee;Simon;Suwon;40,003;Kim;Carl;Busan;22”

 

이러한 I/O 효율성을 높이기 위해  Index를 사용하는 것이 해당 프로세스의 특징입니다. 

 

Table 구조 

DB 설계 시 OLTP 처리 프로세스의 영향이 있습니다.

 

모든 웹 사이트 서비스를 이용 시 회원가입을 진행합니다. 가입자 수가 많아지면 가입된 회원 ID를 가져오는 것이 느려지는데, 

이 문제를 해결하는 방법은 Index(=Primary Key)를 생성해 해결할 수 있습니다. 

 

Index 는 보통 테이블의 자료를 빠르게 검색하기 위해 생성되며, 1개의 테이블에 N개의 Index를 생성할 수 있습니다.

Index 생성 시 속성(UNIQUE / NON UNIQUE)를 설정해 중복 허용 여부를 지정할 수 있습니다. 

 

가장 많이 쓰이는  BTree Index 는 이진검색에 기반을 둔 것으로

자료가 정렬되어있는 상태에서 절반 위치를 찾아가는 구조로 전체를 읽을 때보다 빠르게 원하는 값을 찾을 수 있고 이 값이 위치한 테이블의 레코드를 바로 접근하여 원하는 값을 가져올 수 있습니다. 

 

인덱스와 테이블 관계를 표시한 예시는 다음과 같습니다. 

회원_ID_IndexID(KEY)Table 위치 값

강감찬	XXX
김좌진	XXX
이순신	XXX
홍길동	XXX

→ 

회원입력순서ID암호

1	홍길동	1234
2	강감찬	5678
3	이순신	abcd
4	김좌진	efgh

 

클러스터 

디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장하는 것으로

OLTP 특성을 가진 DB의 테이블에 인덱스로 많이 사용되는 형식입니다. 

 

클러스터 하기 좋은 테이블은 

• 주로 조회가 발생하고 수정이 거의 발생하지 않는 테이블
• 컬럼안의 많은 중복 데이터 가지고 있는 테이블
• 자주 JOIN 되는 테이블 

이고 ,  클러스터 KEY로는 

• 데이터 값의 범위가 큰 컬럼
• 테이블 간의 조인에 사용되는 칼럼

을 많이 사용하고 있습니다. 

 

 

클러스터형 인덱스는 두 가지 종류로 나눠질 수 있는데 특징은 다음과 같습니다. 

• 클러스터 인덱스 
  

  - 인덱스를 생성할 때는 데이터 페이지 전체를 다시 정렬한다.
  - 대용량의 데이터를 강제로 다시 클러스터 인덱스를 생성하는 건 조심
  - 인덱스 자체가 데이터 페이지이다. 인덱스 자체에 데이터가 포함
  - 비클러스형 인덱스 보다 검색 속도는 더 빠르다. 하지만 데이터의 입력/수정/삭제는 느리다.
  - 테이블에 한 개만 생성할 수 있다.

  
  
  
  
• 넌클러스터 인덱스
  

  - 별도의 페이지에 인덱스를 구성한다.
  - 검색 속도는 느리지만, 데이터의 입력,수정,삭제가 더 빠르다.
  - 남용할 경우에는 시스템 성능을 떨어뜨리는 결과를 가져온다.

 

OLAP ( On-Line Analytical Processing )

대용량 데이터를 고속으로 처리하며 다양한 관점에서 추출, 분석할 수 있도록 지원하는 데이터 분석 기술입니다. 

최종 사용자가 대규모 데이터(DW) 에 직접 접근하여 대화식으로 정보를 분석하고 의사결정에 활용하는 과정을 의미하며 DW에 저장된 데이터를 쉽게 보고서로 출력할 수있도록 도와줍니다. 

 

데이터베이스 내용을 다차원적으로 분석하고자 스냅샷형식으로 데이터를 가지고와서 정제와 가공 과정을 거쳐 Data Mart에 담아놓고 분석 View를 제공하는 프로세스라고 할 수 있습니다. 

 

 

BigQuery 

빅쿼리는 OLAP 기반으로 컬럼 단위로 저장하는 데이터베이스입니다. 

컬럼 기반 저장소의 경우 각 컬럼을 아래와 같이 다른 파일에 나눠서 저장합니다. 

FILE 1: 001:Cho,002:Lee,003:Kim

FILE 2: 001:Terry,002:Simon,003:Carl

FILE 3: 001:Seoul;002:Suwon:003:Busan

 

컬럼 단위로 저장 시 해당 컬럼 기준으로 검색하면 다른 컬럼 데이터를 스캔하지 않아도 되기 때문에 데이타 효율성이 높습니다. 

 

SQL 연산 수행 순서를 보면 해당 프로세스를 이해하기 쉽습니다. 

 

natality 테이블에서 1980 ~ 1990 년 대 태어난 아이들의 수를 state 별로 그룹핑하여 내림차순 정렬 후 상위 10개 데이터만 출력하는 쿼리 예시입니다. 

SELECT state, COUNT(*) count_babies
FROM [publicdata:samples.natality]
WHERE year >= 1980 AND year < 1990
GROUP BY state
ORDER BY count_babies DESC
LIMIT 10

 

디스크에서 state, year 컬럼 읽어와서 → 1980 ~ 1990 년 데이터를 주 단위로 태어난 아이들 수 카운트

 → mixer 에서 state 별 아이들 수 합치고 → mixer0 에서 mixer1의 모든 값을 합쳐 sorting → sorting 끝나고 맨 위 10개만 리턴 

 

RDBMS와 다르게 BigQuery 가 가지고 있는 특성은 다음과 같습니다.

• 키나 인덱스의 개념이 없어서 무조건 풀 스캔한다. 
• 성능 위해서 update 대신 append만 지원한다. 한번 입력되면 수정되기 어렵기 때문에 테이블을 지우고 다시 생성해야한다 
• 3개의 데이터센터에 걸쳐 복제하기 때문에 데이터 입력 후 데이터가 바로 조회되지 않을 수 있다. 

 

이러한 특성을 잘 알고 있어야 빅쿼리 조회 시 비용이 어마어마하게 청구되는 일을 막을 수 있을 것 같습니다 ㅎㅎ 

1. 파티션 기준으로 조회 
2. ( * ) 는 절대 금지 

 

 

기능적 비교 

구분	OLTP	OLAP
개념	- Transaction 중심으로 거래 데이터의 정확한   처리 기능 제공이 중요	- 요청된 조건에 맞는 대용량 데이터를   검색하여 다양한 View 제공이 중요
기능원리	- 트랜재션 ACID 준수 (Atomic, Consistency, Isolation, Durability)	- 중복과 집계, 소트, 차원별 그룹
사용목적	- 단일 Transaction 데이터 저장 - 데이터의 무결성 유지	- 대용량 데이터 조회 - 각 차원별 다양한 View 제공 - 시계열 분석
주요기능	- Insert, Upate, Commit, Rollback	- Drill Down/Up, Pivot, Slice, Dice
구현기술	-TP-Monitor , CS , EJB, ODBC	- MOLAP, ROLAP, DOLAP

 

데이터 측면 비교 

구분	OLTP	OLAP
데이터 구조	- 정규화를 통한 데이터 중복성을 최소화	중복 및 집계성 컬럼을 생성 데이터 마트
데이터 모델	ER모델	스타스키마, 스노우 플랙, ER모델
데이터 목적	데이터의 무결성 유지	시계열분석, 그룹분석, 실적집계, 의사결정
데이터 보호	Repeatable Read 레벨 Locking, 병행처리	Read Only
데이터 접근	응용프로그램을 통한 접근	사용자가 직접 접근

참고 : https://bcho.tistory.com/1117