친절한 SQL 튜닝_3장.인덱스 튜닝_2

인덱스 튜닝

 

3.2 부분범위 처리 활용

 

3.2.1 부분범위 처리

 

DBMS가 클라이언트에게 데이터를 전송할 때도 일정량씩 나누어 전송한다. 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어도 서버 프로세스는 클라이언트로부터 추가 Fetch Call을 받기 전까지 그대로 멈춰 서서 기다린다.

 

OLTP 환경에서 대용량 데이터를 빠르게 핸들링할 수 있는 아주 중요한 원리가 바로 여기에 숨어있다. 예를 들어, 마우스로 클릭하면 아래 JAVA 메소드를 호출하는 실행 버튼이 있다고 하자. SQL문에 사용한 BIG_TABLE이 1억 건에 이르는 대용량 테이블이어도 실행 결과는 버튼을 클릭하자마자 곧바로 화면에 출력된다.

 

private void execute(Connection con) throws Exception{
 
    Statement stmt = con.createStatement();
    
    Result rs = stmt.executeQuery(“select name from big_table”);
    
    for(int i=0; i<100; i++){
        if(rs.next() System.out.println(rs.getString(1));
    }
 
    rs.close();
 
    stmt.close();
}

 

1억 건짜리 테이블인데도 결과를 빨리 출력할 수 있는 이유는, DBMS가 데이터를 모두 읽어 한 번에 전송하지 않고 먼저 읽는 데이터부터 일정량(Array Size)을 전송하고 멈추기 때문이다. 데이터를 전송하고 나면 서버 프로세스는 CPU를 OS에 반환하고 대기 큐에서 잠을 잔다. 다음 Fetch Call을 받으면 대기 큐에서 나와 그다음 데이터부터 일정량을 읽어서 전송하고 또다시 잠을 잔다.

 

이처럼 전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것을 이른바 ‘부분범위 처리’라고 한다.

 

정렬 조건이 있을 때 부분범위 처리

 

Statement stmt = con.createStatement();
 
Result rs = stmt.executeQuery(“select name from big_table order by created”);

 

DB 서버는 ‘모든’ 데이터를 다 읽어 created 순으로 정렬을 마치고서야 클라이언트에게 데이터 전송을 시작할 수 있다. 전체범위처리다. Sort Area와 Temp 테이블스페이스까지 이용해 데이터 정렬을 마치고 나면 그때부터 일정량씩 나눠 클라이언트에게 데이터를 전송한다.

 

다행히 created 컬럼이 선두인 인덱스가 있으면, 부분범위 처리가 가능하다. 인덱스는 항상 정렬된 상태를 유지하므로 전체 데이터를 정렬하지 않고도 정렬된 상태의 결과집합을 바로 전송할 수 있기 때문이다.

 

Array Size 조정을 통한 Fetch Call 최소화

 

대량 데이터를 파일로 내려받는다면 어차피 데이터를 모두 전송해야 하므로 가급적 그 값을 크게 설정해야 한다. Array Size를 조정한다고 해서 전송해야 할 총량이 변하진 않지만, Fetch Call 횟수를 그만큼 줄일 수 있다.

 

반대로, 앞쪽 일부 데이터만 Fetch하다가 멈추는 프로그램이라면 Array Size를 작게 설정하는 것이 유리하다. 불필요하게 많은 데이터를 전송하고 버리는 비효율을 줄일 수 있기 때문이다.

 

 

3.2.2 부분범위 처리 구현

 

출력 레코드 수가 Array Size에 도달하면 멈추었다가 사용자 요청이 있을 때 다시 데이터를 Fetch하는 부분이 핵심이다.

 

3.2.3 OLTP 환경에서 부분범위 처리에 의한 성능개선 원리

 

OLTP는 ‘Online Transaction Processing’의 줄임말이다. OTLP 시스템은 말 그대로 온라인 트랜잭션을 처리하는 시스템을 말한다. 온라인 트랜잭션은 일반적으로 소량 데이터를 읽고 갱신한다.

 

그런데 OLTP 시스템이라고 항상 소량 데이터만 조회하는 것은 아니다. 수천수만 건을 조회하는 경우도 있다. 인덱스를 이용해 수천수만 건을 조회하려면 만족할만한 성능을 내기 어려울 수 있다. 많은 테이블 랜덤 액세스가 발생하기 때문이다.

 

인덱스와 부분범위 처리 원리를 잘 활용하면 OLTP 환경에서 극적인 성능개선 효과를 얻을 수 있는 원리가 바로 여기에 숨어 있다. 아래 쿼리를 예로 들어보자.

 

SELECT 게시글ID, 작성자, 등록일시
  FROM 게시판
 WHERE 게시판구분코드 = ‘A’
 ORDER BY 등록일시 DESC

 

인덱스 선두 컬럼을 『게시판구분코드 + 등록일시』 순으로 구성하지 않으면(게시판구분코드 단일 컬럼으로 구성하거나, 게시판구분코드 바로 뒤에 등록일시가 위치하지 않으면), 소트 연산을 생략할 수 없다. 게시판구분코드 = ‘A’ 조건을 만족하는 모든 레코드를 인덱스에서 읽어야 하고, 그 만큼 많은 테이블 랜덤 액세스가 발생한다. 모든 데이터를 다 읽어 등록일시 역순으로 정렬을 마치고서야 출력을 시작하므로 OLTP 환경에서 요구되는 빠른 응답 속도를 내기 어렵다.

 

출처 : 친절한 SQL 튜닝 - 조시형 지음