인덱스 설계
자 우리는 앞에서 여러 예시를 통해서 인덱스를 통해서 쿼리의 속도 등을 알아보았다 이제 테이블의 인덱스를 설계할 수 있을까? 사실 이 정도 지식만으로 인덱스를 설계하는 것은 무리가 있다 이러한 전문성을 갖추려면 세밀한 인덱스 원리와 이론을 바탕으로 많이 고민을 해보아야 한다 이번 시간에는 그러한 내용을 다루어 볼 것이다
인덱스 설계가 어려운 이유
SQL 각각에 최적화된 인덱스를 마음껏 생성할 수 있다면 SQL 튜닝과 인덱스 설계만큼 쉬운 일도 없을 것이다 하지만 그렇게 인덱스를 만들다 보면 옵티마이저가 어떤 인덱스가 좀 더 효율적인지 분석하는 것도 비용으로 처리가된다 그래서 이에 대한 관리 비용뿐만 아니라 시스템 부하를 증가시키는 원인이기도 하다 구체적으로는 DML 성능 저하, 데이터베이스 사이즈 증가, 데이터베이스 관리 및 운영 비용 상승이 있다
가장 중요한 두 가지 선택기준
우리는 앞에서 여러 인덱스 종류를 보았지만 가장 정상적이고 이상적인 방식은 Index Range Scan이라고 했다 이를 위해서는 인덱스 선두 컬럼을 반드시 조건절에 사용해야 한다 그리고 그 선두 컬럼은 반드시 동등 비교 (=) 조건으로 조회해야 한다는 게 우리는 앞에서 눈이 닳도록 보고 또 보았다
하지만 이것은 이상적인 개발 환경이고 실상 들어가다 보면 현실은 녹록지 않다
업무상 수행 빈도 - 만약 성능이 엉망인 쿼리가 있지만 그 쿼리가 1년에 한번 한 달에 1번 아니 하루에 딱 한 번만 실행되는 쿼리라면 이런 것들은 개선 대상이긴 하지만 당장은 큰 문제는 아닐 수 있다 하지만 업무상 빈도가 매우 많은데 인덱스 효율이 나오지 않는다면 이는 바로잡는 것이 중요하다
데이터양도 인덱스를 설계할 때 중요한 판단 기준이다 데이터량이 적다면 굳이 인덱스를 많이 만들 필요가 없다 Full Scan으로도 충분하기 때문이다 그럼 만대로 초대용량 테이블은 어떨까? 초대용량 테이블은 select 도 문제지만 insert 도 문제가 그만큼 데이터가 빈번하게 들어온다는 것이다 초당 DML 발생량은 트랜잭션 성능에 직접적인 영향을 준다 DML 을 아시다시피 테이블 데이터를 변경하는 INSERT , UPDATE , DELETE 가 있다
조건절 패턴이 10개일 때 인덱스를 10개 만드는 것도 비효율적이다 전문가라면 이 10개의 조건절 칼럼을 모두 커버할 수 있는 핵심적인 액세스 경로 한두 개를 선택한다
소트 연산 생략 우리는 앞에서 인덱스는 항상 정렬을 유지한다고 배웠다 그래서 우리가 임의로 넣은 ORDER BY 가 생략되는 것도 확인을 했다
아래 예제를 한번보도록 하자
소트연산 생략 할 수 있는 인덱스 설계
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
create table Ex15_PurchaseList(
purchase_item varchar(30),
purchase_cd varchar(5),
purchase_item_date varchar(8),
purchase_item_user varchar(10)
)
SELECT purchase_item,
purchase_cd,
purchase_item_date,
purchase_item_user
FROM Ex15_PurchaseList
WHERE purchase_item = 'APPLE_IPHONE16_PRO_MAX'
AND purchase_item_date BETWEEN '20240101' AND '20241231'
AND purchase_cd > 'B'
ORDER BY purchase_item_date , purchase_cd
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------------+
Plan hash value: 521797001 |
|
----------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3015 | 100K| 138 (2)| 00:00:01 ||
| 1 | SORT ORDER BY | | 3015 | 100K| 138 (2)| 00:00:01 ||
|* 2 | TABLE ACCESS FULL| EX15_PURCHASELIST | 3015 | 100K| 137 (1)| 00:00:01 ||
----------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - filter("PURCHASE_ITEM"='APPLE_IPHONE16_PRO_MAX' AND |
"PURCHASE_ITEM_DATE">='20240101' AND "PURCHASE_ITEM_DATE"<='20241231' AND |
"PURCHASE_CD">'B') |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
소스 코드는 https://gitlab.com/kimdongy1000/sqltuningproject 에 참조 바랍니다 이때 인덱스가 아직 없을떄 어떻게 설계를 해야 소트연산을 생략할 수 있을까?
지금은 SORT ORDER BY 오퍼레이션이 있는데 이떄 인덱스 설계는 다음처럼 하면된다
1
2
3
CREATE INDEX Ex15_PurchaseList_idx ON Ex15_PurchaseList(purchase_item , purchase_item_date , purchase_cd);
이렇게 인덱스를 설계하면 소트연산을 생략할 수 있다 마찬가지로 아래 2개는 인덱스를 생략할 수 있다
1
2
3
4
5
CREATE INDEX Ex15_PurchaseList_idx2 ON Ex15_PurchaseList(purchase_item_date , purchase_item , purchase_cd);
CREATE INDEX Ex15_PurchaseList_idx3 ON Ex15_PurchaseList(purchase_item_date , purchase_cd , purchase_item);
다만 이 인덱스는 소트연산을 생략할 수 없다
1
2
3
4
CREATE INDEX Ex15_PurchaseList_idx4 ON Ex15_PurchaseList(purchase_cd , purchase_item_date , purchase_item);
CREATE INDEX Ex15_PurchaseList_idx5 ON Ex15_PurchaseList(purchase_cd , purchase_item , purchase_item_date);
그 이유는 부등호로 비교하는 쿼리들은 인덱스 구성 시 선두 컬럼으로 와서는 안 된다 그 이유는 우리가 앞에서 인덱스 군집성에서 다루어보았다 선두 컬럼이 부등호로 비교하면 첫 번째 조건을 제외한 두 번째 컬럼부터 군집성에서 멀어짐으로 이들을 정렬할 수 없는 상태이기 때문에 소트 연산을 따로 하게 되는 것이다
그럼 인덱스를 구성하면 끝인가 사실 아니다 인덱스에 대한 실행계획을 따면서 최소한으로 스캔해서 데이터가 나올 수 있는지 고민해야 한다 그렇지 않고 스캔이 계속해서 이루어진다면 그만큼 스캔에는 포함되지만 버려지는 데이터들이 많기 때문에 단순히 Range Scan 이 된다는 조건으로만 만족하지 말고 실행계획 + 트레이스를 뜨면서 확인해 보는 것이 좋다
결합 인덱스 선택도
인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 주요한 판단 기준이다 선택도란 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말하며 선택도에 총 레코드 수를 곱해서 카디널리티를 구한다 그래서 선택도가 높은(카디널리티가 높은) 인덱스는 생성해 봐야 효용가치가 별로 없다 만약 높은 선택도에 의한 칼럼을 인덱스로 지정하더라도 랜덤 I/O 가 발생하는 비율과 인덱스 스캔 비율을 그렇게 낮아지지 않을 것이다
인덱스 설계할 때 우리가 할 일은 항상 사용하는 컬럼을 안쪽에 두고 그중 = 조건을 앞쪽에 위치시키는 거뿐이다 그중 선택도가 낮은 컬럼을 앞쪽에 두려는 노력은 의미 없거나 오히려 손해일 수 있다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WHERE customGrad = :1
AND customNumber = :2
AND tradeDts >= :3
WHERE customGrad = :1
AND customNumber = :2
AND tradeDts >= :3
AND tradCode = :4
WHERE customGrad = :1
AND customNumber = :2
AND tradeDts >= :3
AND tradCode = :4
AND goodCode = "5
예를 들어 이런 쿼리가 있다고 할 때 여기서 항상 사용하는 컬럼 중에서 customGrad , customNumber , tradeDts 중에서 customGrad , customNumber는 동등 조건이고 tradeDts는 부등호 조건, Between 조건일 때 다음과 같이 인덱스를 만들면 효용성 있게 개발할 수 있다
필수 인덱스
customGrad + customNumber + tradeDts
옵션 인덱스
tradCode + goodCode
이때 옵션 인덱스를 넣고 안 넣고의 차이는 불필요한 랜덤 I/O를 줄이기 때문에 필수조건만으로 랜덤 I/O 가 많이 생겨나면 고려할 수 있는 옵션 조건이다
중복 인덱스 정리
1
2
3
4
5
6
7
X01: A
X02: A + B
X03: A + B + C
이런 경우라면 이 세 개의 인덱스는 완전히 중복이다 X03 이 X01 X02를 포함하고 있기 때문에 2개의 인덱스는 삭제해도 된다 다음을 보자
1
2
3
4
5
6
7
8
X01: A + B
X02: A + C
X03: A + D
X04: A + E
이 네 개의 인덱스를 보자 얼핏 보면 중복이 아니지만 이때 A 가 선택성이 매우 낮은 칼럼이라면 사실상 중복이다 그래서 위와 같이 완전 중복이 아닌 불완전 중복이라고 한다
정리
인덱스의 선두 컬럼은 무조건 동등 조건(=)으로 비교할 수 있는 컬럼이 되어야 한다
인덱스의 최상의 상태는 단일 테이블 기준 Index Range Scan 이 최선의 조건이다
소트 연산도 쿼리 성능을 좌우하는데 그렇다면 최대한 인덱스에 Order By 컬럼이 들어가면 성능에 도움을 준다
복합 인덱스를 설계할 때 선택성이 낮은 컬럼이 선택되면 스캔 비용을 줄어든다 그리고 필수 인덱스와 옵션 인덱스가 있는데 이때 옵션 인덱스는 스캔 범위를 줄여주지는 못하지만 테이블 랜덤 I/O를 줄여주는 역할을 한다