가장 중요한 인덱스 사용조건
예를 들어서 다음과 같은 인덱스가 있다고 하자
CREATE UNIQUE INDEX ex4_custom_pk_idx ON Ex4_Custom (customApplyDate , customName , customSex , customAge);
1
2
3
4
5
SELECT *
FROM Ex4_Custom
WHERE customName = '홍길동'
이와 같은 경우 인덱스 범위 스캔을 사용할 수 있을까 이건은 앞에서 잠깐 다루어 보았다 현재 인덱스는 customApplyDate , customName , customSex , customAge로 만들었기 때문에 B-Tree는 customApplyDate 정렬 후 그 정렬된 값을 바탕으로 customName 정렬하고 두 개의 컬럼이 정렬된 상태로 customSex 이렇게 끝에 customAge 정렬이 되게 된다
즉 customName 을 만족하는 데이터는 모든 리프 블록 전 구간에 흩어지게 된다 그렇기 때문에 지금처럼 선두 컬럼인 customApplyDate 없이 customName를 먼저 사용하게 되면 옵티마이저는 인덱스 스캔 시작점을 찾을 수도 없고 어디서 멈춰야 할지 모르기 때문에 그냥 Table Full Access를 사용하게 됩니다
java 소스예제
https://gitlab.com/kimdongy1000/sqltuningproject
이곳에서 Ex4_custom 를 찾자 아래 .sql 파일도 스키마도 마련해두었습니다
간단한 테이블 스키마
1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP TABLE Ex4_Custom
CREATE TABLE Ex4_Custom(
customApplyDate varchar(8),
customName varchar(11),
customSex varchar(2),
customAge number(2)
)
CREATE INDEX Ex4_Custom_idx ON Ex4_Custom (customApplyDate , customName , customSex , customAge);
java 소스
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
package com.example.sqltuningproject.controller;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
@RestController
@RequestMapping("/ex4_custom")
public class Ex4_Custom {
private static final String[] firstNames = {"김", "이", "박", "최", "정", "강", "조", "윤", "장", "임"};
private static final String[] middleNames = {"민", "서", "지", "현", "승", "예", "영", "우", "준", "석"};
private static final String[] lastNames = {"호", "빈", "희", "아", "수", "진", "연", "혁", "기", "찬"};
@Autowired
private SqlSessionTemplate sqlSessionTemplate;
@GetMapping("/create")
public String create()
{
List<Ex4_Custom_Model> customs = new ArrayList<>();
Set<String> dateSet = new HashSet<>();
Random random = new Random();
Random sexRandom = new Random();
Random ageRandom = new Random();
// 시작 날짜 및 종료 날짜 설정
LocalDate startDate = LocalDate.of(1900, 1, 1);
LocalDate endDate = LocalDate.of(2024, 12, 31);
// 총 일 수 계산 (1900-01-01 ~ 2024-12-31)
int totalDays = (int) (endDate.toEpochDay() - startDate.toEpochDay()) + 1;
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
// 중복되지 않는 랜덤 날짜 생성
while (dateSet.size() < 10000) {
int randomDayOffset = random.nextInt(totalDays); // 0 ~ totalDays-1 범위의 랜덤 값
LocalDate randomDate = startDate.plusDays(randomDayOffset);
//System.out.println(randomDate.format(formatter));
dateSet.add(randomDate.format(formatter)); // yyyyMMdd 형태로 저장
}
for (String date : dateSet) {
String name = generateRandomName(random);
String sex = "M";
int rndSexInd = sexRandom.nextInt(2);
int rndAgeInd = ageRandom.nextInt(80);
if(rndSexInd % 2 == 0) {
sex = "W";
}
sqlSessionTemplate.insert(this.getClass().getName() + ".create", new Ex4_Custom_Model(date , name , sex , rndAgeInd));
}
return "ok";
}
private static String generateRandomName(Random random) {
return firstNames[random.nextInt(firstNames.length)] +
middleNames[random.nextInt(middleNames.length)] +
lastNames[random.nextInt(lastNames.length)];
}
}
class Ex4_Custom_Model{
private final String customApplyDate;
private final String customName;
private final String customSex;
private final int customAge;
public Ex4_Custom_Model(String customApplyDate, String customName, String customSex, int customAge) {
this.customApplyDate = customApplyDate;
this.customName = customName;
this.customSex = customSex;
this.customAge = customAge;
}
@Override
public String toString() {
return String.format("INSERT INTO ex4_Custom (customApplyDate, customName, customSex, customAge) VALUES (%s, %s, %s, '%d');",
customApplyDate, customName, customSex, customAge);
}
}
해당 소스는 // 총 일 수 계산 (1900-01-01 ~ 2024-12-31) 날짜 데이터를 가지고 손님의 랜덤 한 이름과 랜덤 한 성별 랜덤 한 나이를 insert 하는 소스입니다 총 1만 개의 데이터를 생성했습니다
최신 통계정보 반영
1
2
3
4
EXEC DBMS_STATS.GATHER_TABLE_STATS('SQLTUNING', 'Ex4_Custom');
콘솔에서 다음의 쿼리를 입력해야 합니다 한 번에 많은 데이터가 들어오면 통계정보가 깨지게 되는데 이때 통계정보를 최신화하지 않으면 아무리 인덱스를 설정해두었다고 하더라도 옵티마이저는 과거의 테이블 통계를 바탕으로 실행계획을 만들어내기 때문에 대량의 데이터가 들어올 경우에는 반드시 통계정보를 최신화해 주어야 합니다 (통상적으로 10% ~ 20% 사이의 데이터가 한 번에 들어오면 이 테이블의 통계정보를 한번 최신화해 주어야 옵티마이저가 올바르게 인덱스를 사용할 수 있습니다)
선두컬럼 사용
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
EXPLAIN PLAN FOR
SELECT *
FROM Ex4_Custom
WHERE customApplyDate >= '20230301'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------+
Plan hash value: 4234916565 |
|
-----------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 151 | 3624 | 2 (0)| 00:00:01 ||
|* 1 | INDEX RANGE SCAN| EX4_CUSTOM_IDX | 151 | 3624 | 2 (0)| 00:00:01 ||
-----------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("CUSTOMAPPLYDATE">='20230301' AND "CUSTOMAPPLYDATE" IS NOT |
NULL) |
선두 컬럼인 customApplyDate인 경우에는 올바르게 인덱스를 사용하는 모습을 볼 수 있습니다
선두컬럼 없이 인덱스 사용
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
EXPLAIN PLAN FOR
SELECT *
FROM Ex4_Custom
WHERE customName = '이민빈'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
--------------------------------------------------------------------------------+
Plan hash value: 351498740 |
|
--------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 10 | 240 | 13 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX4_CUSTOM | 10 | 240 | 13 (0)| 00:00:01 ||
--------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("CUSTOMNAME"='이민빈') |
실제로 이민빈은 여러 customApplyDate 대해서 다른 브랜치에 분포되어 있으므로 옵티마이저는 인덱스 시작 지점과 끝 지점을 사용할 수 없게 됩니다 하지만 선두 컬럼과 같이 사용하게 되면 인덱스를 사용할 수 있습니다
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
EXPLAIN PLAN FOR
SELECT *
FROM Ex4_Custom
WHERE customApplyDate >= '20150101'
AND customName = '이민빈';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------+
Plan hash value: 4234916565 |
|
-----------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 24 | 7 (0)| 00:00:01 ||
|* 1 | INDEX RANGE SCAN| EX4_CUSTOM_IDX | 1 | 24 | 7 (0)| 00:00:01 ||
-----------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("CUSTOMAPPLYDATE">='20150101' AND "CUSTOMNAME"='이민빈' AND |
"CUSTOMAPPLYDATE" IS NOT NULL) |
filter("CUSTOMNAME"='이민빈') |
선두컬럼을 사용해도 인덱스를 안타는 경우
이러한 경우에는 인덱스를 타게 됩니다 다만 선두 컬럼을 사용해도 인덱스를 타지 않는 경우가 있는데
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
EXPLAIN PLAN FOR
SELECT *
FROM Ex4_Custom
WHERE customApplyDate >= '19910101'
AND customName = '이민빈';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
--------------------------------------------------------------------------------+
Plan hash value: 351498740 |
|
--------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3 | 72 | 13 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX4_CUSTOM | 3 | 72 | 13 (0)| 00:00:01 ||
--------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("CUSTOMNAME"='이민빈' AND "CUSTOMAPPLYDATE">='19910101') |
이때는 인덱스 스캔 범위가 너무 크기 때문에 옵티마이저는 인덱스를 스캔해서 찾기보다는 테이블을 전부 스캔해서 데이터를 찾는 게 빠르다고 판단하기 때문에 테이블 full access로 방향을 전환하게 됩니다 이때는 인덱스 힌트를 주더라도 cost에서 불리합니다
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
EXPLAIN PLAN FOR
SELECT /*+ INDEX(Ex4_Custom Ex4_Custom_idx) */
*
FROM Ex4_Custom
WHERE customApplyDate >= '19910101'
AND customName = '이민빈';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------+
Plan hash value: 4234916565 |
|
-----------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3 | 72 | 20 (0)| 00:00:01 ||
|* 1 | INDEX RANGE SCAN| EX4_CUSTOM_IDX | 3 | 72 | 20 (0)| 00:00:01 ||
-----------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("CUSTOMAPPLYDATE">='19910101' AND "CUSTOMNAME"='이민빈' AND |
"CUSTOMAPPLYDATE" IS NOT NULL) |
filter("CUSTOMNAME"='이민빈') |
동일한 결과를 가져다주지만 cost는 무려 7차이가 나게 됩니다
인덱스의 함정
우리는 바로 위의 경우에서 인덱스의 함정을 보았다 대부분의 개발자는 인덱스가 잘 타면 쿼리 튜닝이 잘 되었다고 오판하는 경우가 있는데 위와 같은 경우가 그렇지 않은 경우이다 분명히 인덱스는 탔지만 테이블을 full access 하는 경우보다 cost 가 높게 나왔기 때문이다 그렇기 때문에 인덱스를 탄다고 반드시 쿼리가 좋은 게 아니고 인덱스를 타지 않는다고 안 좋은 쿼리가 아니다