1. 테이블 액세스 최소화
인덱스 튜닝을 하면서 깨닫는 점
- 아무리 데이터가 많아도 인덱스를 사용하면 데이터가 금방 조회된다.
- 대량의 데이터를 조회할 때 인덱스를 사용하면 테이블 전체 스캔할 때보다 훨씬 느리다.
인덱스로 검색하는데 왜 느릴까?
인덱스 ROWID를 이용한 테이블 랜덤 액세스는 고비용 구조
따라서 인덱스를 이용해 대량 데이터를 조회하면, 디스크 I/O 발생량도 함께 증가하므로 성능이 급격히 나빠진다.
인덱스 클러스터링 팩터
클러스터링 팩터(Clusterin Factor, 이하 CF)는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미
CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다. 이는 테이블 액세스량에 비해 블록 I/O가 적게 발생하기 때문이다.
인덱스 손익분기점
읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다. Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 ‘인덱스 손익분기점’이라고 부른다.
인덱스 스캔 VS 테이블 전체 스캔
| 구분 | 테이블 전체 스캔 | 인덱스 스캔 |
|---|---|---|
| 액세스 방식 | 시퀀셜 액세스 | 랜덤 액세스 방식 |
| I/O 방식 | Multiblock I/O | Single Block I/O |
2. 부분범위 처리 활용
1억 건짜리 테이블인데도 결과를 빨리 출력할 수 있는 이유는 DBMS가 데이터를 모두 읽어 한 번에 전송하지 않고 먼저 읽는 데이터부터 일정량을 전송하고 멈추기 때문이다. 이처럼 전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것을 ‘부분범위 처리’라고 한다.
// 전체범위
public class AllRange {
public static void execute(Connection con) throws Exception {
int arraySize = 10;
String SQLstmt = "select object_id, object_name from all_objects";
Statement stmt = con.createStatement();
stmt.setFetchSize(arraySize);
ResultSet rs = stmt.executeQuery(SQLstmt);
while(rs.next()) {
System.out.println(rs.getLong(1) + " : " + rs.get(String(2));
}
rs.close();
stmt.close();
}
public static void main(String[] args) throws Exception {
Connection con = getConnection();
execute();
releaseConnection(con);
}
}// 부분범위
public class PartialRange {
public static int fetch(ResultSet rs, int arraySize) throws Exception {
int i = 0;
while(rs.next()) {
System.out.println(rs.getLong(1) + " : " + rs.get(String(2));
if(++i >= arraySize) {
return i;
}
}
return i;
}
public static void execute(Connection con) throws Exception {
int arraySize = 10;
String SQLstmt = "select object_id, object_name from all_objects";
Statement stmt = con.createStatement();
stmt.setFetchSize(arraySize);
ResultSet rs = stmt.executeQuery(SQLstmt);
while(true) {
int r = fetch(rs, arraySize);
if (r < arraySize) {
break;
}
System.out.println("Enter to Continue .... (Q)uit? ");
BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
String input = in.readLine();
if (input.equals("Q")) {
break;
}
}
rs.close();
stmt.close();
}
public static void main(String[] args) throws Exception {
Connection con = getConnection();
execute();
releaseConnection(con);
}
}3. 인덱스 스캔 효율화
운영 환경에서 가능한 일반적인 튜닝 기법은 인덱스 컬럼 추가다.
인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 등치(=) 조건으로 사용할 때가 가장 좋다.
리프 블록을 스캔하면서 읽은 레코드는 하나도 걸러지지 않고 모두 테이블 액세스로 이어지므로 인덱스 스캔 단계에서의 비효율은 전혀 없다.
BETWEEN과 LIKE 스캔범위 비교
select * from 월별고객별판매집계
where 판매월 like '2021%';
select * from 월별고객별판매집계
where 판매월 between '202101' and '202112'LIKE보다는 BETWEEN을 사용하는게 낫다. 더 스캔을 적게한다.
OR 조건 활용
인덱스 선두 컬럼에 대한 옵션 조건으로 OR를 사용해선 안 된다. 인덱스에 포함되지 않은 컬럼에 대한 옵션 조건은 어차피 테이블에서 필터링할 수 밖에 없으므로 그럴 때는 OR 조건을 사용해도 무방하다.
- 인덱스 액세스 조건으로 사용 불가
- 인덱스 필터 조건으로도 사용 불가
- 테이블 필터 조건으로만 사용 가능
LIKE/BETWEEN 조건 활용
LIKE/BETWEEN 패턴을 사용하고자 할 때는 다음 네 가지 사항을 점검해야 한다.
- 인덱스 선두 컬럼
- NULL 허용 컬럼
- 숫자형 컬럼
- 가변 길이 컬럼
4. 인덱스 설계
가장 중요한 두가지 선택 기준
- 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.
- ‘=’ 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.
데이터량도 인덱스를 설계할 때 중요한 판단 기준이다. 데이터량이 적다면 굳이 인덱스를 많이 만들 필요가 없다. Full Scan으로도 충분히 빠르기 때문이다. 반대로 인덱스를 많이 만들어도 저장 공간이나 트랜잭션 부하 측면에서 그다지 문제될 것이 없다.
후기
이 책을 처음 산 것은 작년 6월쯤이었던 것 같다. 회사 메인화면 호출시에 로딩이 오래걸린다는 이슈가 있었었는데, 나보고 그걸 고쳐보라는 대표님의 지시가 있었다. 그때 DB튜닝에 대해 공부해보고자 샀던 책이었는데 그때 잠깐 보고 이제야 시간을 내서 읽어보았다. 개념에 대한 정리도 사전식 정의보다도 그 흐름과 과정을 설명하려고 노력했다는 점이 마음에 들었다.
아쉬운점
다만 아쉬움점도 바로 그 점인데 당장의 퍼포먼스를 내기를 기대하고 이 책을 봤다면 나와 같은 아쉬움을 겪을 것이다. 그래서 튜닝을 어떻게 하고 그렇게 했을 때 정말로 성능 개선이 이루어지는가에 대한 예시가 더 많았으면 좋았을꺼라 생각이 든다. 또한 책이 오라클 기반으로 설명되어 있다는 점 또한 고려해야할 사항이다.
