SQL 최적화란?
- DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정
1. SQL 실행하기 전 최적화 과정
1-1 SQL 파싱
- 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
- Syntax 체크 : 문법적 오류가 없는지 확인
- Semantic 체크 : 의미상 오류가 없는지 확인
1-2 SQL 최적화
- SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성
- 다양한 실행경로 내에서 가장 효율적인 하나를 선택한다.
- 데이터베이스 성능을 결정하는 가장 핵심적인 엔진
1-3 로우 소스 생성
- SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 프로시저 형태로 포맷팅 하는 단계
- 로우 소스 생성기가 그 역할을 맡는다.
2. SQL 옵티마이저
2-1 옵티마이저 최적환 단계
- 사용자로부터 받은 쿼리를 수행하는데 후보군이 될 실행 계획을 탐색
- Data Dictionary에 미리 저장된 정보를 이용해 각 실행계획의 예상비용을 산정
- 최저 비용을 나타내는 실행계획을 선택
3. 옵티마이저 힌트
옵티마이저의 경로를 선택할 때 기준인 Cost는 어디까지나 예상치다.
대부분 옳은 선택을 하지만, SQL이 복잡할 수록 경로 선택에 실수를 할 가능성이 크다.
만약에 개발자가 옵티마이저보다 더 효율적인 경로를 찾을 수 있다면
옵티마이저 힌트를 이용해 액세스 경로를 바꿀 수 있다.
사용 예시 : 주석 기호에 '+'를 붙이면 된다.
SELECT /*+ INDEX(A 고객_PK) */
고객명, 연락처, 주소, 가입일시
FROM 고객 A
WHERE 고객ID = '0000008'
주의사항
- 힌트와 힌트 사이에 ','(콤마)를 사용할 수 없다.
- 테이블을 지정할 때 스키마명까지 명시하면 안 된다.
- FROM절에서 ALIAS를 사용했다면, 힌트에도 ALIAS를 사용해야한다.
4. 라이브러리 캐시
SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록
캐싱해두는 메모리 공간을 말한다.
아래 그림을 살펴보자.
소프트 파싱 : 라이브러리 캐시에서 찾아서 곧바로 실행단계로 넘어가는 것
하드 파싱 : 라이브러리 캐시에서 찾는 데 실패해 최적화, 로우 소스 생성 과정을 모두 거치는 것
옵티마이저가 SQL를 최적화 할 때 엄청나게 많은 연산을 진행한다.
이 무거운 연산을 쿼리문이 들어올 때 마다 하드 파싱을 하게 되면 CPU를 많이 소비하게 되는 원인이 된다.
따라서 이를 방지하게 하기 위해 재활용 할 수 있게 해주는 역할이 바로 라이브러리 캐시이다.
5. 바인드 변수
순간적으로 쇼핑몰에 이벤트로 고객이 몰린다고 가정을 해보자.
해당 시스템에 접속을 시도하게 되고, 코드는 다음과 같다.
public void login(String login_id) throws Exception {
String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(SQLStmt);
if(rs.next()) {
// do anything
}
rs.close();
st.close();
}
이렇게 될 경우 아까 말한 하드파싱이 발생하게 된다.
내부적으로 돌아가는 코드를 보게 되면 다음과 같다.
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'oraking'
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'javaking'
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'tommy'
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'karajan'
...
고객이 로그인할 때마다 위와 같이 각각의 DBMS 내부 프로시저를 만들게 된다.
하드파싱이 여러번 발생하는 원인이 된다.
이를 방지하기 위해
1. 로그인 ID를 파라미터로 받는 프로시저 하나를 공유하면서 재사용을 하도록 유도한다.
2. 파라미터 Driven 방식으로 SQL를 작성을 한다. 여기서 바인드 변수가 등장한다.
수정된 코드는 다음과 같다.
public void login(String login_id) throws Exception {
String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";
PreparedStatement st = con.prepareStatement(SQLStmt);
st.setString(1, login_id);
ResultSet rs = st.executeQuery();
if(rs.next()) {
// do anything
}
rs.close();
st.close();
}
이러면 하드파싱은 최초 1번만 발생하게 된다.
6. 데이터베이스 저장 구조
테이블 스페이스 : 세그먼트를 담는 컨테이너
세그먼트 : 데이터 저장공간이 필요한 오브젝트
익스텐트 : 공간을 확장하는 단위
블록 : 데이터를 읽고 쓰는 단위
테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면
해당 테이블스페이스로부터 익스텐트를 추가로 할당받는다.
익스텐트 단위로 공간을 확장하지만, 사용자가 입력한 레코드를 저장하는 공간은 데이터 블록이다.
한 데이터 블록에 저장된 레코드는 모두 같은 테이블 레코드이다.
7. DB 버퍼 캐시
디스크 I/O가 SQL 성능을 결정한다.
자주 읽는 블록을 매번 디스크에서 읽는 것 = 매우 비효율적
DBMS에 데이터 캐싱 메커니즘이 등장하게 된 이유
DB 버퍼 캐시 : 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 반복적인 I/O Call을 줄인다.
8. 논리적 I/O vs 물리적 I/O
논리적 블록 I/O = SQL를 처리하는 과정에서 발생한 총 블록 I/O
물리적 블록 I/O = 디스크에서 발생한 총 블록 I/O
디스크를 액세스 하는 경우 : SQL에서 읽어야 할 블록을 DB 버퍼 캐시에서 찾지 못했을 경우
9. 버퍼캐시 히트율
BCHR = ( 1 - (물리적 I/O) / (논리적 I/O)) x 100
BCHR은 읽은 전체 블록 중에서 물리적 디스크를 거치지 않고 메모리에서 찾은 비율을 나타낸다.
위의 식을 변형하면 다음으로 바꿀 수 있다.
물리적 I/O = 논리적 I/O x (100% - BCHR)
여기서 중요한 점은 BCHR은 시스템 상황에 따라 달라진다.
그러면 자연스럽게 BCHR의 성능에 따라 물리적 I/O도 달라진다.
즉 BCHR, 물리적 I/O는 통제 불가능한 외생변수
따라서 SQL 성능을 높이기 위해서는 논리적 I/O를 줄여야 한다.
논리적 I/O가 줄어들면 자연스럽게 물리적 I/O도 줄인다.
논리적 I/O를 줄일려면 SQL를 튜닝해서 읽는 총 블록 개수를 줄인다.
10. Single Block I/O vs Multiblock I/O
캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼캐시에 적재하고 읽는다.
이 과정에서 2가지 형태로 나뉜다.
Single Block I/O = 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
인덱스는 소량 데이터를 읽을 때 주로 사용하므로 이 방식이 효율적이다.
Multiblock I/O = 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식
테이블을 전체를 스캔할 때 이 방식을 사용한다. (Table Full scan)
Table Full Scan = 시퀀셜 액세스 + Multiblock I/O 방식으로 디스크 블록을 읽는다.
한 블록에 속한 모든 레코드를 읽고, 캐시에서 못 찾으면 I/O Call을 통해 인접한 수십~수백 개의 블록을 1번에 I/O
대량 Good
Index Range Scan = 랜덤 액세스 + Single Block I/O 방식으로 디스크 블록을 읽는다.
캐시에서 블록을 못 찾으면 레코드 1개를 읽기 위해 매번 잠을 자는 I/O 매커니즘
소량 Good
11. 래치
버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다.
문제는 하나의 버퍼블록에 2개 이상의 프로세스가 동시에 접근 할 때 발생한다.
동시에 접근하면 블록 정합성의 문제가 생길 수 있다.
이를 해결하기 위해 순차적으로 접근시키기 위한 직렬화 매커니즘 필요.
특정 순간에는 한 프로세스만 사용하고, 다른 프로세스는 기다리도록 만든다.
이런 줄서기가 가능하도록 지원하는 매커니즘 = 래치(Latch)
빠른 데이터베이스를 구현할려면 버퍼캐시 히트율을 높여야 하지만, 캐시 I/O도 빠르지 않을 수 있다.
버퍼캐시는 공유자원이기에, 히트율이 높을수록 프로세스들이 동시에 접근하면서 래치에 의한 경합 발생 가능.
DB의 두 단계의 직렬화 메커니즘
| 1단계 | Cache Buffer Chain Latch | “블록이 어디 있는지 찾기 위한” 잠금 |
| 2단계 | Buffer Lock | “찾은 블록의 실제 읽기/수정 시 필요한” 잠금 |
출처 : 친절한 SQL 튜닝 / 조시형 지음
