[DB] DB 성능 개선 방법

---

데이터베이스 성능 개선을 위한 기법

1. 데이터베이스 인덱스(Index)

2. 옵티마이저(Optimizer)

3. 클러스터링(Clustering)

4. 레플리케이션(Replication)

5. 샤딩(Sharding)

 

+ 성능 개선 팁

---

 

1. 데이터베이스 인덱스(Index)

저장한 컬럼을 기준으로 메모리 영역에 일종의 색인을 생성하는 것.
검색 속도는 빨라지고 삽입, 삭제, 갱신 속도는 느려진다.

 

추가적인 오버헤드

- INSERT 시 : 새로운 데이터에 대한 인덱스를 추가함

- DELETE 시 : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함

- UPDATE 시 : 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터의 인덱스를 추가함

장점 : 테이블 조회 속도 및 성능 향상, 전반적인 시스템 부하 감소

단점 : 인덱스를 관리하기 위한 별도의 저장공간 필요, 추가 작업 필요, 잘못 사용할 경우 오히려 성능이 저하됨

 

→ 인덱스를 사용하면 좋은 경우 : 규모가 큰 테이블, 삽입·삭제·갱신이 자주 발생하지 않는 컬럼, Join, where, order by에 자주 사용되는 컬럼, 데이터의 중복도가 낮은 컬럼 등

 

인덱스 자료구조

- 해시 테이블(Hash Table)

- B-Tree

 

2. 옵티마이저(Optimizer)

가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진 - DBMS의 CPU

 

- 규칙 기반 옵티마이저

: 실행 속도가 빠른 순으로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 채택한다
오라클8 이하의 버전에서 기본으로 설정된 옵티마이저

 

우선순위 ↓

1	ROWID를 사용한 단일 행인 경우
2	클러스터 조인에 의한 단일 행인 경우
3	유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인 경우
4	유일하거나 기본키(Primary Key)에 의한 단일 행인 경우
5	클러스터 조인인 경우
6	해시 클러스터 조인인 경우
7	인덱스 클러스터 키인 경우
8	복합 칼럼 인덱스인 경우
9	단일 칼럼 인덱스인 경우
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
12	정렬-병합(Sort-Merge) 조인인 경우
13	인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
15	전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

 

 

- 비용 기반 옵티마이저

: 옵티마이저에서 실행 계획을 세운 뒤, 비용이 최소한으로 나온 실행 계획을 수행한다
최근에 가장 많이 사용하고 있는 방식, 오라클10 이후 버전

 

옵티마이저 모드

- CHOOSE

- FIRST_ROWS

- FIRST_ROWS_n

- ALL_ROWS

 

 

 

 

3. 클러스터링(Clustering)

여러 개의 DB를 수평적인 구조로 구축하는 방식, DB 들 간의 데이터 무결성 검사를 하는 동기 방식으로 데이터를 동기화한다.

장점 : 동기화를 통해 항상 일관성 있는 데이터를 얻을 수 있다, 시스템을 장애 없이 운영할 수 있다

단점 : 동기화하는 시간이 필요하여 쓰기 성능이 떨어진다, 장애가 전파된 경우 처리가 까다롭다

 

▷ Active - Active Clustering : 클러스터를 항상 가동하여 사용 가능한 상태로 두는 방식

▷ Active - Standby Clustering : 일부는 가동, 일부는 대기상태로 구성하는 방식

 

처리 방식

노드에 쓰기 트랜잭션이 수행되고, Commit 실행

→ 실제 디스크에 쓰기 전에, 다른 노드로 데이터 복제를 요청

→ 다른 노드에서 복제 요청을 수락한다는 신호(OK)를 보내고, 디스크에 쓰기 시작

→ 다른 노드로부터 신호(OK)를 받으면 실제 디스크에 데이터를 저장

기본 DB 구조

 

4. 레플리케이션(Replication)

여러 개의 DB를 권한에 따라 수직적인(Master-Slave)로 구축하는 방식

Master Node는 쓰기 작업만을 처리하고, Slave Node는 읽기 작업만을 처리한다.

Master와 Slave 간의 데이터 무결성 검사를 하지 않는 비동기 방식으로 노드들 간의 데이터를 동기화한다.

장점 : DB 요청은 대부분 읽기 작업이기 때문에, 충분히 성능을 높일 수 있다. 비동기 방식으로 지연시간이 거의 없다.

단점 : 노드들 간의 데이터 동기화가 보장되지 않아 일관성 있는 데이터를 얻지 못할 수 있다, Master Node 다운 시 복구 및 대처가 까다롭다.

 

처리 방식

Master Node에 쓰기 트랜잭션 수행

→ Master Node는 데이터를 저장하고 트랜잭션에 대한 로그를 파일에 기록 (BIN LOG)

→ Slave Node의 IO Thread는 Master Node의 로그 파일(BIN  LOG)을 파일(Replay Log)에 복사

→ Slave Node의 SQL Thread는 파일(Replay Log)을 한 줄씩 읽으면서 데이터를 저장

 

5. 샤딩(Sharding)

각 DB서버에서 데이터를 분할하여 저장하는 방식. DB서버의 부하를 분산할 수 있다.

테이블을 특정 기준으로 나누어 저장 및 검색함으로써 시간 효율을 높일 수 있다.

데이터를 어떻게 분산시킬지, 어떻게 읽을지에 대한 것이 중요한데, 데이터 분산의 기준이 되는 것이 Shard Key다.

대표적인 Shard Key 방식

• Hash Sharding
• Dynamic Sharding
• Entity Group

 

---

 

성능 개선 팁!

• 데이터 타입 정의 시
  컬럼에 필요한 데이터를 저장할 수 있는 데이터 타입 중, 가장 작은 데이터 타입을 선택한다
  
  
• select 시
  필요 컬럼의 레코드만 반환한다. (select * 지양)
  
  
• 테이블 전체 Row 수를 알고 싶을 때
  count(컬럼명)은 해당 컬럼 값이 null인 row를 제외하고 세기 때문에 count(*)을 쓸 것
  더 큰 테이블에서는 sysindexes 시스템 테이블 사용
  
  
• 단순 Select 시
  WITH NOLOCK 옵션을 사용한다
  
  
• Where 조건문
  꼭 필요한 결과만 반환하도록 Where문을 사용한다
  좌항은 변형하지 않고 우항에만 조건을 선언한다
  
  
• Having 절
  가능한 사용을 피하고 GROUP BY와 WHERE절을 사용한다
  
  
• 특정 레코드의 존재 유무를 파악할 때
  COUNT 보다는 EXISTS를 사용한다
  count는 모든 레코드 중 관련된 것을 필터링한 후, count함수를 실행하지만 
  exists는 필터링 시 하나라도 레코드가 있음을 인지했을 때 반환한다
  
  
• JOIN 시
  되도록 INNER JOIN을 사용한다
  
• VIEW 사용 시
  VIEW는 편리한 개념이지만, 무분별한 사용은 자제한다

---

참고자료

Tistory, kwomy - 데이터베이스 성능 향상을 위한 규칙/팁

Tistory, 태리는당근을좋아해 - [데이터베이스] 데이터베이스 성능 개선

Tistory, 망나니개발자 - [Database] 인덱스(index)란?

Tistory, 코딩팩토리 - [DB] 데이터베이스 옵티마이저(Optimizer)에 대하여

Tistory, Jordy-torvalds - Clustering vs Replication vs Sharding

Tistory, Jae Honey - Database - 샤딩이란 무엇인가?!(+ 샤딩의 다양한 기법, 각 기법 비교)