12회차 데이터베이스 | DB 인덱스(B-Tree, B+Tree, 해쉬테이블), DB 튜닝, DB 다중화, 내용 정리

728x90

DB 인덱스
인덱스 거는이유
인덱스에 왜 해쉬 보다 B Tree를 쓰는지?

DB 튜닝
DB 다중화 (클러스터링, 리플리케이션)

DB 인덱스

⬛ DB 인덱스

1) DB 인덱스 개념

원하는 데이터를 빨리 찾기 위해 튜플(행)의 키 값에 대한 물리적 위치 기록해둔 자료구조
주로 테이블의 컬럼을 인덱스로 설정한다.

cf. 인덱스가 없는 경우, 테이블의 모든 행을 FUll-Scan하게 된다. (FTS) : Full Table Scan

특정 값 탐색을 위해 모든 데이터, 테이블을 순차 탐색하며 대상을 검색한다면 시간이 오래 걸린다. O(N)

→ DB 데이터 조회 성능 향상

→ ‘인덱스’는 데이터와 데이터 위치를 포함한 자료구조를 생성하여 데이터 탐색 시 빠른 조회를 돕는다.

2) DB 인덱스 관리

(DB 인덱스 관리 : 항상 최신의 데이터를 정렬된 상태로 유지시키려고 관리함. 그에 따른 오버헤드 O)

인덱스는 데이터 (저장, 수정, 삭제)에 대한 성능을 희생시키고 (탐색)에 대한 성능을 대폭 상승시킨 방식이라 볼 수 있다.
인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 검색하는데 빠르지만, 새로운 값을 추가하거나 삭제, 수정하는 경우에는 쿼리문 실행 속도가 느려진다.

INSERT : 새로운 데이터에 대한 인덱스 추가한다.

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

UPDATE : DELETE 후 INSERT를 하므로 큰 성능 저하 ( =기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가한다는 소리)

DBMS는 원하는 값을 빠르게 탐색하기 위해 인덱스를 항상 최신의 정렬 상태로 유지해두는데, 인덱스가 적용된 컬럼에 대한 INSERT, UPDATE, DELETE가 수행된다면 ‘균형과 정렬’을 위한 추가 작업이 별도로 필요하기 때문에 그에 따른 오버헤드가 발생하기 때문이다.
또한, Delete, Update는 기존 인덱스를 삭제하는 게 아니라 '사용X'처리만 할 뿐이다. 따라서 INSERT, UPDATE, DELETE를 빈번히 사용할 경우 실제 DB인덱스 구조 상에서는 (사용O + 사용X) 인덱스가 훨씬 많이 존재해서 성능이 떨어진다.

3) 인덱스 사용의 장단점

(1) 장점

테이블 조회 속도 향상, 그에 따른 성능 향상
전반적 시스템 부하 줄임

(2) 단점

인덱스 관리 목적으로 DB의 약 10%되는 별도의 저장 공간 필요 (메모리 공간 차지)
인덱스 관리를 위한 추가 작업 필요
인덱스 잘못 사용 시 오히려 성능 저하되는 역효과

RDBMS에서 인덱스 사용은 필수다. 일반적인 OLTB(온라인 트랜잭션 처리) 시스템의 경우 데이터 조회 업무가 90% 이상이기 떄문이다.

4) 인덱스 사용하면 좋은 케이스

(1) 테이블 선정 기준

규모 작지 않은 테이블 (-규모 너무 작은 테이블의 경우, 인덱스 사용 비용이 더 듬)
랜덤 액세스 빈번, 특정 범위 특정 순서로 데이터 조회 필요한 테이블
다른 테이블과 순차적 조인 발생하는 테이블

(2) 컬럼 선정 기준

INSERT, UPDATE, DELTE 발생 적은 컬럼 ( : 빈번한 속성에 인덱스 걸 경우 되려 성능 저하됨)
JOIN, WHERE, ORDER BY, GROUP BY, UNION 빈번한 컬럼 ( :이런 연산 빈번한 속성 인덱싱 해두면 추후에 해당 열 빨리 찾을 수 있어서 좋음)
데이터 중복도 낮은 컬럼 (=카디널리티 높은 컬럼)

* cardinality (행 튜플 개수)

- 카디널리티 높다 = 중복도가 낮다.

-카디널리티 낮다 = 중복도가 높다.

DB 인덱스의 자료 구조

(B-Tree, B+Tree, Hash Table, RedBlack-Tree등)

→ 일반적인 RDBMS의 인덱스는 대부분 B-Tree 구조로 되어 있다.

[DB 인덱스로 B-Tree 사용하는 이유]

항상 정렬된 상태로 특정 값보다 크고 작은 부등호 범위 연산에 문제 없다. (- Hash Table 저격)
참조 포인터가 적어 방대한 데이터 양에도 빠른 메모리 접근 가능하다. (- RedBlack Tree 저격)
데이터 탐색 뿐 아니라, 저장, 수정, 삭제에도 항상 O(logN) 갖는다.

⬛ B-Tree 구조 (Balanced Tree)

이진탐색트리와 유사하지만 자식 노드로 둘 이상 가질 수 있고 균형 트리라는 특징 있다.
탐색 시 O(logN) 시간 복잡도 갖는다.
모든 노드들에 대해 값을 저장하고 있으며 포인터 역할 동반한다. (각 노드 : 키값들 + 포인터들)

1) 구성 : 루트노드/ 브랜치노드 / 단말 노드

2) 균형 노드 (단말 노드가 모두 같은 레벨 높이에 존재)

3) 모든 검색은 루트 노드에서 시작하여 내부 노드 지나 리프 노드까지 내려가며 이루어진다.

4) B-Tree 새 값 추가, 삭제, 수정될 경우, 동적으로 노드 분할하거나 통합하여 항상 균형 상태 유지한다.

🎈 DB 인덱스 종류 (2)

인덱스를 가진 테이블에 DML 작업을 할 경우 더 많은 비용과 시간이 필요하다. 따라서 인덱스를 생성 시 해당 테이블의 용도를 정확하게 파악한 후에 상황에 맞게 적절한 칼럼으로 Clustered Index와 Non-Clustered Index를 구성해야 한다.

클러스터형 인덱스(Clustered Index) : 책의 해당 페이지 바로 펴는 것
넌 클러스터형 인덱스(Non-clustered Index) : 책의 목차로 찾고자 하는 내용의 페이지 찾고, 그 페이지로 이동하는

B-Tree 구조로 각각의 인덱스 종류를 파헤쳐보자.
(1) 클러스터드 인덱스 (Clustered Index) : 30% 이내에서 사용

- 테이블당 한개만 생성 가능
- PK 설정 시 default로 PK가 클러스터 인덱스가 만들어진다.
- 인덱스 자제에 실제 데이터가 저장되어 있음
- 실제 물리적 정렬 순서와 인덱스 정렬 순서 같음 ==
-- 클러스터 인덱스는 인덱스 페이지 자체에 데이터가 직접 저장되므로
(인덱스 자체도 B-Tree 구조에 맞춰 정렬되므로 데이터를 재배열하게 된다)
-- 따라서 데이터의 물리적 정렬순서와 인덱스 정렬순서는 같다.

(2) 넌클러스터드 인덱스 (Non-Clustered Index) : 3% 이내에서 사용
- 하나의 테이블에 여러 개 인덱스 생성 가능
- 인덱스 자체에 실제 데이터의 위치가 저장되어 있음
- 실제 물리적 정렬 순서와 인덱스 정렬 순서는 다름 ≠

→ 인덱스 페이지에는 데이터의 주소가 저장되므로 해당 주소값을 가지고 해당 주소로 접근하여 실제 데이터를 가져온다. (실제 데이터 정렬순서 변경없이 인덱스 페이지에 데이터 주소를 입력하므로)
→ 인덱스 정렬순서와 데이터 정렬순서가 다르다.

⬛ B+Tree 자료구조

B-Tree 개선한 형태의 자료구조
값을 리프노드에만 저장
리프노드 제외한 노드들은 오직 포인터 역할만 수행
리프노드끼리 LinkedList로 연결되어 순차 탐색 용이하게 한다. (→ 이 때문에 부등호문 연산에 효과적)

그런데, 인덱스 사용 자료구조가 왜 하필 B-Tree지? 탐색 시간이 O(logN)인 자료구조나 알고리즘은 다른 것들도 많지 않나? 해시 테이블은 O(1)인데, 그러면 차라리 해시 테이블을 쓰는 게 더 빠르지 않나? 그러면 밸런스 트리 중 B-Tree 말고도 RedBlack-Tree로도 사용할 수 있는 것 아닌가? → 에 대한 질문이 가장 많다.

Q. HashTable 보다 B-Tree가 적합한 이유 (질문 多)

⬛ Hash Table 자료구조

해시 테이블 (Key, Value)로 데이터 저장하는 자료구조
Key 값을 이용해 고유한 index 생성하여, 그 인덱스에 저장된 값을 꺼내오는 구조라 빠른 탐색 가능 (물론 해시 충돌 등 최악의 경우에는 O(N) 이지만, 평균적으로 O(1) )

→ 사실 탐색 시간이 가장 빠른 것으로 인덱스의 자료구조를 선택하고자 했다면, 시간복잡도가 O(1)인 해시 테이블을 사용하는 게 더 나을 수 있다. 하지만 DB 인덱스 자료구조로 부적합한 면이 있다. (그렇다고 안쓰는 건 아니다. 동등 연산에 특화되어있어서 사용하는 경우는 있는데, 보편적으로 잘 사용하지 않는다고 봐야 맞다.)

DB 인덱스에서 해시 테이블이 사용되는 경우는 탐색이 제한적이다. (동등 연산 O, 범위 연산 X)
이유 : 해시가 동등 (=) 연산에만 특화되었기 때문에, 범위 연산( <, > 부등호 연산) 성능은 좋지 않다.

[추가] 해시 테이블 내의 데이터는 정렬된 상태가 아니다. 해시 함수는 기본적으로 각각의 key에 대해 완전히 다른 해시 값을 생성하므로 그렇다. 따라서 범위 연산이 필요한 DB 인덱스에는 부적합하다.

→ 인덱스는 데이터를 정렬된 구조로 관리하는데, 해시 함수의 (값 변화 시 완전히 다른 해시 값 생성하는 특성) 으로 인해 연속된 값의 검색이 어려워질 수 있다.

→ 따라서 범위 연산이 필요한 경우에는 해시 함수보다는 정렬된 데이터 구조를 사용하는 것이 효과적

Q. RedBlack- Tree 대신 B-Tree 사용하는 이유 (질문 多)

⬛ RedBlack-Tree | 레드-블랙 트리

레드-블랙 트리 역시, Balance Tree 중 하나 (특정 규칙 따르는 균형 이진 탐색 트리)
각 노드는 하나의 값만 가진 상태. 좌, 우 노드 개수의 밸런스를 맞춘다. → 이 점 때문에 B-Tree를 채택한다.

(B-Tree는 하나의 노드에 여러 개의 데이터 요소 저장이 가능하다. 반면, RedBlack-Tree는 무조건 하나의 노드에 하나의 데이터 요소만 저장 가능하다.)

(하나의 노드에 하나의 데이터만 있기 때문에, 자식 노드로 넘어가는 횟수가 더 많을 것이다.)

즉, B-Tree의 경우, 참조 포인터가 RedBlack Tree에 비해 상대적으로 적어서 방대한 데이터 양에도 빠른 메모리 접근이 가능하다.

💡 사실 논리적인 시간복잡도로 생각하면 둘 다 O(log N)의 성능을 낸다.
하지만 하드웨어적인 측면에서 봤을 때 성능은 크게 차이난다. 그 이유는 바로 참조 포인터의 사용 정도에 따름

참조 포인터를 사용하는 Linked-List와 인덱스를 사용하는 Array의 차이를 떠올려보자.
배열 같이 상수 인덱스로 접근하는 경우, 정말로 정렬된 실제 메모리에서 단순 덧셈으로 한번에 접근하는거라 탐색 속도가 굉장히 빠르다. 하지만 참조 포인터의 경우 계속해서 해당 메모리 주소를 타고 들어가서 원하는 값을 가져오기에 탐색 속도가 느리다.

RedBlack-Tree의 경우, (하나의 노드 당 하나의 데이터만 있기 때문에) 각각의 노드들을 오직 참조 포인터만 사용해서 접근한다.
반면 B-Tree의 경우, (하나의 노드 당 여러 개의 데이터 저장 가능해서) 같은 노드 안에 있는 데이터에 대해서는 배열처럼 상수 인덱스 덧셈으로 빠르게 데이터에 접근 할 수 있다.

즉 B-Tree가 특정 값을 탐색하거나 부등호(<, >) 등의 연산에서 순서대로 값을 가져올 때 훨씬 좋은 효율을 낸다.

DB 튜닝 | DB Tuning (= DB 성능 최적화)

⬛ DB 튜닝

최적의 자원으로 최적의 성능(응답속도) 얻기 위해 개선하는 작업

DB 튜닝 : DB의 구조나, DB 자체, 운영체제 등을 조정하여 DB 시스템의 전체적인 성능을 개선하는 작업

⬛ DB 튜닝 3단계 | (DB 설계 튜닝 → DBMS 튜닝 → SQL 튜닝)
1단계 : DB 설계 튜닝 (모델링 관점) : DB 설계 단계에서 성능 고려하여 설계
- 데이터 모델링, 인덱스 설계
- 데이터 파일, 테이블 스페이스 설계
- 데이터베이스 용량 산정
- 즉, DB의 물리적 구조인 테이블과 인덱스 설계를 최적화
→ 튜닝 사례 : 반정규화, 분산파일 배치

2단계: DBMS 튜닝 (환경 관점) : 성능을 고려하여 메모리나 블록 크기 지정
- CPU, 메모리 I/O에 관한 관점
- 메모리 할당, 캐시 사이즈 조정, 동시 연결 세션 수 조정, 디스크 I/O 최적화
→ 튜닝 사례 : Buffer 크기, Cache 크기 조절하여 시스템 리소스 활용도 높이기

3단계 : SQL 튜닝 (App 관점) : SQL 작성 시 성능 고려
- Join, Indexing, SQL Execution Plan
- 인덱스 활용하여 검색 성능 향상시킬 수 있는지를 먼저 고려하고, 이후 SQL문을 성능 고려하여 변경함
- (*) 사용보다 필요 컬럼만 명시하여 Select 하여 검색 속도 향상시키기
→ 튜닝 사례 : Hash/Join

Q. SQL 튜닝 시 주의해야 할 점은 무엇인가요?

SQL 튜닝 시 주의해야 할 점은 단순히 쿼리 실행 시간만 줄이는 것에 집중하는 것이 아니라, 전체 시스템 성능에 미치는 영향을 고려해야 한다는 것입니다.

또한, 인덱스는 DB 성능 향상 수단으로 사용되는 가장 일반적인 방법입니다.
응답 시간 낮은 SQL이 실행되면 (SQL문 튜닝보다 우선하여) 인덱스로 성능 향상할 수 없는지 검사하는 게 튜닝의 제 1선택입니다.
인덱스의 가장 큰 장점은 SQL문 변경없이도 성능을 개선할 수 있다는 점과 인덱싱 자체가 테이블 자체나 데이터에 영향을 주지 않는다는 점이기 때문입니다.

DB 서버 다중화 | (클러스터링, 리플리케이션)

1. 클러스터링 (Clustering)

DB 서버만 다중화하는 것
여러 개의 DB 서버를 수평 구조로 구축하는 방식

→ 하나의 서버에 가해지던 부하가 여러 개로 나뉘므로 CPU, Memory 부하도 줄어든다.

2. 리플리케이션 (Replication)

DB 서버와 저장소를 같이 다중화하는 것 (저장소의 복제)
여러 개의 DB를 수직적 구조(Master-Slave)로 구축하는 방식
메인으로 사용할 Master 서버, 이를 복제한 Slave 서버로 구조

아래 그림을 살펴 보면, Insert, Update, Delete 작업은 Master 서버에 전달되고, Select는 Slave 서버에 전달되고 있다.

즉, Slave 서버는 복제된 데이터이기 때문에 select 작업만 수행하고,

데이터 조작 발생할 수 있는 (insert, update, delete)는 Master 서버에 전달되는 것이다.

[클러스터링 vs 리플리케이션 비교]

클러스터링(Clustering)

여러 개의 DB 서버를 수평적인 구조로 구축하여 Fail Over(장애 복구)한 시스템을 구축하는 방식
동기 방식으로 서버들 간의 데이터를 동기화

→ 동기 방식 : 매번 한 서버 변경 사항을 즉시 다른 서버에 즉시 동기화 (데이터 일관성 높고, 시간은 느림)

장점 : 1개의 서버 죽어도 다른 서버가 살아 있어 시스템을 장애없이 운영 O
단점 : 여러 서버들 간의 데이터를 동기화하는 시간이 필요하므로 리플리케이션에 비해 쓰기 성능이 떨어진다.

리플리케이션(Replciation)

여러 개의 DB를 권한에 따라 수직적인 구조(Master-Slave)로 구축하는 방식
비동기 방식으로 노드들 간의 데이터를 동기화

→ 비동기 방식 : 매번 변경 사항을 즉시 동기화X. 나중에 비동기적으로 동기화하는 방식

장점 : 비동기 방식으로 데이터가 동기화되어 지연 시간이 거의 X (데이터 일관성 낮고, 시간은 빠름)
단점 : 노드들 간의 데이터가 동기화되지 않아 일관성있는 데이터를 얻지 못할 수 있습니다.

파티셔닝과 샤딩

[추가하기]