Index
미리 보는 인덱스 튜닝
데이터를 찾는 두 가지 방법
- 테이블 전체 스캔
- 인덱스 이용
- 테이블 전체 스캔에 관련해서는 튜닝 요소가 많지 않지만 인덱스는 튜닝이 필요하고 기법도 다양함
인덱스 튜닝의 두 가지 핵심 요소
- 인덱스는 큰 테이블에서 소량의 데이터를 검색할 때 사용하기에 인덱스 튜닝 중요
인덱스 스캔 효율화 튜닝: 인덱스 스캔 과정에서 발생하는 비효율 제거랜덤 액세스 최소화 튜닝: 테이블 액세스 회수를 줄이는 것- 둘 다 중요하지만 특히 랜덤 액세스 최소화 튜닝이 미치는 영향이 큼
SQL 튜닝은 랜덤 I/O와의 전쟁
- 데이터베이스 성능은 디스크 I/O에서 결정
인덱스 구조
- 인덱스: 대용량 데이터 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용되는 오브젝트
- 인덱스를 사용하면 데이터 전체가 아닌 일부만 읽고 멈추는
범위 스캔이 가능
- DBMS는 기본적으로
B Tree Index사용- 나무를 거꾸로 뒤집은 모양으로 Root가 위에 있고 Leaf가 밑에 있음
- LMC: Leftmost Child
- LMC는 키값을 가지지 않는 특별한 레코드
- LMC가 가리키는 주소로 가면 다음 블록의 첫 번째 레코드 저장
- 리프 블록에 저장된 각 레코드는 키값 순으로 정렬돼 있으며 테이블 레코드를 가리키는 주소값 ROWID를 가짐
- 인덱스 키값이 같으면 ROWID 순으로 정렬
- 인덱스를 스캔하는 이유는 검색 조건을 만족시키는 소량의 데이터만 스캔하기 위함
- ROWID는 다음과 같이 구성
- ROWID = 데이터 블록 주소 + 로우 번호
- 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
- 블록 번호: 데이터 파일 내에서 부여한 상대적 순번
- 로우 번호: 블록 순번
- 인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 구성
- 수직적 탐색: 인덱스 스캔 시작 지점을 찾는 과정
- 수평적 탐색: 데이터를 찾는 과정
인덱스를 사용한다는 것
- 인덱스 컬럼을 가공하지 않고 사용하는 것
- 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것
- 리프 블록 일부만 스캔하는 Index Range Scan
더 중요한 인덱스 사용 조건
- 인덱스를 [소속팀 + 사원명 + 연령] 순으로 구성한다고 가정
- 사원명 = ‘홍길동’ 조건을 만족하는 데이터는 리프 블록 전 구간에 흩어짐
- 인덱스 스캔 시작 지점과 스캔 종료 지점 모두 찾을 수 없어 전부 스캔해야 함
- 인덱스를 Range Scan하기 위한 첫 번째 조건은
인덱스 선두 컬럼이 조건절에 있어야 함 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능
인덱스를 이용한 소트 연산 생략
- 인덱스를 Range Scan할 수 있는 이유는 데이터가 정렬되어 있기 때문
- 이미 인덱스로 정렬되어 있는 데이터에 대해서는 ORDER BY를 사용해도 실행하지 않음
- 인덱스로 소트 연산 생략
ORDER BY 절에서 컬럼 가공
| 장비번호 | 변경일자 | 변경순번 |
|---|---|---|
| … | … | … |
| B | 20180505 | 031583 |
| … | … | … |
- 조건절이 아닌 ORDER BY 혹은 SELECT-LIST 에서 컬럼을 가공함으로 인해 인덱스가 정상적으로 작동되지 않는 경우가 있음
- PK 인덱스를 [장비번호 + 변경일자 + 변경순번] 순으로 구성했다고 가정
SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자 || 변경순번- 위와 같은 코드는 정렬 연산을 생략할 수 없음
- 인덱스에는 가공되지 않은 상태로 값을 저장했는데 가공한 값 기준의 정렬을 요구하기 때문
SELECT-LIST 절에서 컬럼 가공
- MIN, MAX에서도 인덱스 값을 따라 제일 첫 번째 값, 마지막 값만 읽으면 되기에 정렬 연산이 생략되는 경우가 있음
SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
FROM 상태변경이력
WHERE 장비번호 = 'C' AND 변경일자 = '20180316'- 위와 같은 코드는 정렬 연산을 생략할 수 없음
- 인덱스는 문자열 기준으로 정렬되어 있는데 이를 숫자값으로 바꾼 순번을 요구했기 때문
RDB Index vs NoSQL Index
RDB Index
- 인덱스를 주로 B-Tree나 B+ Tree 구조로 구현
NoSQL Index
- B-Tree 기반의 인덱스를 사용하지만 다양한 인덱스 형태 지원
- 단일 필드 인덱스, 복합 인덱스, 다중 키 인덱스, 텍스트 인덱스, 해시 인덱C스 …
Normalization
Notmalization
- 테이블 간의 중복된 데이터를 허용하지 않기 위한 분리 정책
- 중복된 데이터를 허용하지 않음으로 인해 무결성을 유지하고 DB의 저장 용량을 줄이려는 것이 목적
정규화 단계
- 제 1정규화: 도메인은 원자값을 가짐
- 제 2정규화: 부분적 종속 제거
- 제 3정규화: 이행적 종속 제거
- BCNF 정규화: 모든 결정자가 후보키가 되도록
- 제 5정규화: 다치 종속 제거
- 제 5정규화: 조인 종속 제거
DeNormalization
- 조회할 때 Join이 너무 많아져서 조인 쿼리를 처리하는 시간이 너무 많아지는 경우 역정규화 실행
- 자주 사용하는 데이터들을 굳이 따로 테이블에 접근해서 하기 귀찮을 때
DB Locking
Lock
- 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 하나의 커넥션만 변경할 수 있게 해 주는 기능
- 동시성을 제어하기 위한 기능
Optimistic Lock
- 낙관적 락은 충돌하지 않을 것이라고 가정하고, 충돌이 발생한 경우에 대비하는 방식
- 낙관적 락은 DB에서 내가 먼저 이 값을 수정했다고 명시하여 다른 사람이 동일한 조건으로 값을 수정할 수 없게 하는 것
- 리소스 경쟁이 적고 락으로 인한 성능 저하가 적음
- 충돌 발생 시 처리해야 할 외부 요인이 존재
Pessimistic Lock
- 충돌이 발생할 확률이 높다고 가정하여 실제로 데이터에 액세스하기 전에 먼저 락을 걸어 충돌을 예방하는 방식
- 충돌 발생을 미연에 방지하고 데이터의 일관성을 유지할 수 있음
- 동시 처리 성능 저하 및 교착 상태 발생 De가능성
Dead Lock
- 교착 상태: 여러 개의 트랜잭션들이 실행을 하지 못하고 서로 무한정 기다리는 상태
- 예방 1 모두 로킹
- 각 트랜잭션이 실행되기 전에 필요한 데이터를 모두 Locking해 주는 것
- 트랜잭션의 병행성을 보장하지는 못함
- 예방 2 회피 기법 Wait-Die
- 나중에 들어온 트랜잭션은 포기하고 나중에 다시 요청
- 다른 트랜잭션이 데이터를 점유하고 있을 때 기다리거나 포기하는 방식
- 예방 2 회피 기법 Wound-Wait
- 나중에 들어온 트랜잭션이라면 기다림
- 다른 트랜잭션이 데이터를 점유하고 있을 때 빼앗거나 기다리는 방식
Connection Pool
Connection Pool
- DB에 접속하기 위한 Connection들의 집합
- Connection을 지속적으로 점유하게 되면 리소스 누수가 발생할 수 있기 때문에, 사용 후 리소스 반납이 필요
- 이 과정을 진행하기까지의 비용이 상당함
- 따라서 사용을 종료한 Connection을 완전히 반납하는 것이 아닌 Pool에 다시 반환하여 사용할 수 있도록
동작 방식
| 속성 | 설명 |
|---|---|
| MAX_POOL_SIZE | 최대로 유지할 수 있는 Connection의 개수 |
| MIN_IDLE | 실제 사용되지는 않더라고 최소한으로 유지할 Connection의 개수 |
| IDLE_TIMEOUT | Connection이 얼마나 사용되지 않으면 완전 종료할 것인지 |
| CONNECTION_TIMEOUT | Connection을 얻을 때까지 기다릴 수 있는 시간으로 이 시간을 넘으면 SQLException 발생 |
- 일반적으로 MIN_IDLE 수만큼 Connection 생성
- MAX_POOL_SIZE를 넘지 못함
Table Full Scan vs Index Range Scan
Table Full Scan vs Index Range Scan
- Table Full Scan: 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾는 방식
- Index Range Scan: 인덱스를 이용한 테이블 스캔으로 ROWID를 통해 테이블 레코드를 찾아 가는 방식
- Full Scan을 피하는 것도 중요하지만 인덱스가 SQL의 성능을 떨어뜨리는 경우도 잦음
- Table: 시퀀셜 액세스와 Multi Block I/O로 읽어 한번에 읽어들이고 캐시에 없는 경우 한 번의 프로세스 중단 발생
- Index: 랜덤 액세스와 Single Block I/O로 읽어 레코드 하나를 읽기 위해 매번 프로세스 중단 발생
Index Range Scan
- B-Tree 인덱스의 가장 일반적인 방식
- 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 뒤 필요한 범위까지 수평적으로 탐색
- 선두 컬럼을 가공하지 않은 상태로 조건절에 사용
