지난 시간에는 스캔 범위에 대해 간략하게 정리를 했다 이번 시간부터는 인덱스를 직접 설정해 보면서 실행계획과 비교하는 예제를 많이 만들어볼 것이다 이때는 거의 대부분 데이터 가공이 필요함으로 java 프로그래밍 예제와 같이 제공될 것이다
인덱스 기본 사용법은 인덱스 Range Scan 하는 방법을 의미한다 인덱스 컬럼은 가공되지 않아야 인덱스를 정상적으로 사용할 수 있다 만약 인덱스 칼럼을 가공해버리면 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해아 하는데 이는 인덱스 사용할 때 기본 중에서도 기본에 해당한다 자 그럼 이에 대한 예제를 찾아보자
java 소스예제
https://gitlab.com/kimdongy1000/sqltuningproject#
이곳에서 Ex1_Student를 찾자 아래. sql 파일도 스키마도 마련해 두었습니다
간단한 예제 테이블 스키마
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP TABLE ex1_student
CREATE TABLE ex1_student(
grade_id NUMBER(1),
class_id NUMBER(1),
student_id Number(2),
student_name varchar(10)
)
CREATE UNIQUE INDEX ex1_student_pk_idx ON ex1_student (grade_id, class_id , student_id);
ALTER TABLE ex1_student ADD CONSTRAINT pk_ex1_student PRIMARY KEY (grade_id, class_id , student_id) USING INDEX ex1_student_pk_idx;
테이블 구조는 다음과 같습니다 그리고 이때 각각의 컬럼은 학년, 반, 번호, 이름 이렇게 되어 있고 인덱스는 학년 - 반 - 번호 이렇게 구성을 했습니다 안에 총 데이터는 600개를 맞추었습니다 그럼 이때 우리는 선두 컬럼을 가공하지 않을 때 플랜과 가공한 플랜을 각각 비교를 해볼 것입니다
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
package com.example.sqltuningproject.controller;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
@RestController
@RequestMapping("/ex1_student")
public class Ex1_Student {
private static final String[] firstNames = {"김", "이", "박", "최", "정", "강", "조", "윤", "장", "임"};
private static final String[] middleNames = {"민", "서", "지", "현", "승", "예", "영", "우", "준", "석"};
private static final String[] lastNames = {"호", "빈", "희", "아", "수", "진", "연", "혁", "기", "찬"};
@Autowired
private SqlSessionTemplate sqlSessionTemplate;
@GetMapping("/create")
@Transactional(rollbackFor = Exception.class)
public String create()
{
List<Student> students = new ArrayList<>();
Random random = new Random();
for (int grade = 1; grade <= 6; grade++) { // 1학년 ~ 6학년
for (int classNum = 1; classNum <= 5; classNum++) { // 1반 ~ 5반
for (int studentId = 1; studentId <= 20; studentId++) { // 학생번호 1 ~ 20
String name = generateRandomName(random);
students.add(new Student(grade, classNum, studentId, name));
}
}
}
for(int i = 0; i < students.size(); i++){
sqlSessionTemplate.insert(this.getClass().getName() + ".create", students.get(i));
}
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 Student {
private final int grade;
private final int classId;
private final int studentId;
private final String name;
public Student(int grade, int classId, int studentId, String name) {
this.grade = grade;
this.classId = classId;
this.studentId = studentId;
this.name = name;
}
@Override
public String toString() {
return String.format("INSERT INTO ex1_student (grade_id, class_id, student_id, student_name) VALUES (%d, %d, %d, '%s');",
grade, classId, studentId, name);
}
}
인덱스 선두컬럼
선두 컬럼이 무슨 뜻일까 이제 앞으로 인덱스 선두컬럼에 대해서 계속 이야기할 것이다 이는 말 그대로 앞에 있다는 뜻이다 인덱스 만드는 문구를 다시 보자
1
2
3
CREATE UNIQUE INDEX ex1_student_pk_idx ON ex1_student (grade_id, class_id , student_id);
유니크한 인덱스를 만들 때 grade_id , class_id , student_id 이렇게 순서를 만들었으므로 이때 인덱스 선두 컬럼은 차례대로 번호를 가지게 된다 즉 인덱스를 잘 사용하려면 옵티마이저가 인덱스를 잘 활용할 수 있도록 우리가 쿼리를 잘 만들어줘야 한다 인덱스 구성이 올바른데 우리가 쿼리를 엉망으로 만들어버리면 옵티마이저는 거의 대부분 인덱스를 사용하지도 못하고 Table Full Scan 을 사용할 것이다
선두컬럼 가공전 쿼리
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
EXPLAIN PLAN FOR
SELECT * FROM ex1_student WHERE grade_id = 3;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 1095574889
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4600 | 1 (0)| 00:00:01
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX1_STUDENT | 100 | 4600 | 1 (0)| 00:00:01
|* 2 | INDEX RANGE SCAN | EX1_STUDENT_PK_IDX | 100 | | 1 (0)| 00:00:01
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GRADE_ID"=3)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
먼저 선두 컬럼을 가공하지 않은 데이터로 grade_id 3인 데이터를 찾을 때의 모습이다 지금 보면 INDEX RANGE SCAN 일어났고 그때 object 이름은 EX1_STUDENT_PK_IDX로 진행을 했다는 것이다
선두컬럼 가공후 쿼리
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 ex1_student WHERE TO_CHAR(grade_id) = 3;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 2102840051 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 100 | 4600 | 3 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX1_STUDENT | 100 | 4600 | 3 (0)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter(TO_NUMBER(TO_CHAR("GRADE_ID"))=3) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
그리고 지금은 선두 컬럼 grade_id를 to_char로 감싸고 진행을 했다 이때는 인덱스를 타지 못하고 table-fullScan 을 선택한 모습이다 그리고 전체 Cost는 1과 3으로 압도적으로 선두 컬럼을 가공하지 않는 쪽이 낮게 나왔다
그 이유는 선두 컬럼을 가공하면 옵티마이저는 어디서부터 어디서 어디까지 범위를 지정할지 모르게 되는 상태이기 때문에 아예 테이블을 전체 스캔하는 것으로 플랜을 변경한다 그렇게 되면 당연히 부분만 스캔하는 것보다 SQL 성능이 떨어지는 것이 당연하다
그리고 여기서 또 하나 중요한 점은 1 - filter(TO_NUMBER(TO_CHAR("GRADE_ID"))=3) 이 부분이다 즉 난중에 다시 보겠지만 옵티마이저는 자동형 변환을 지원하는데 우리가 테이블 스키마로 number로 지정을 하고 검색 시 to-char로 해도 오류가 나지 않는 이유는 옵티마이저가 테이블 스키마를 보고 자동으로 형 변환의 해주게 된다 하지만 이러한 형 변환 자체가 쿼리의 속도를 떨어지게 만든다
그럼 간단한 예제로 인덱스 가공에 대해서 살펴보았고 조금만 더 공부를 해보자
선두컬럼가공 경우
1
2
3
where substr(birth_day , 5, 2) = '05'
이처럼 birth_day 가 인덱스 선두 컬럼이고 이때 월로 filter를 걸기 위해서 다음과 같이 선두 컬럼을 가공하면 인덱스 범위 스캔을 할 수 없습니다
1
2
3
where nvl(order_count , 0) < 1000
마찬가지로 선두 컬럼인 order_count를 nvl 처리하는 것도 선두 컬럼 가공에 해당하기 때문에 인덱스 범위 스캔을 할 수 없습니다
그럼 왜 선두컬럼을 가공하면 인덱스를 사용할 수 없을까?
당연하게도 인덱스 컬럼을 가공하면 인덱스를 스캔 시작 자체를 할 수 없기 때문이다 Index Range Scan에서 Range는 범위를 의미하는데 이때 인덱스 컬럼을 가공해버리면 시작 지점과 끝 지점 자체를 찾을 수 없기 때문에 그렇다 왜 그런지 그럼 간단한 그림을 보자 위의 예제로 우리는 학년 - 반 - 번호를 인덱스로 만들었으니 branch는 대략 이런 모양일 것이다
예를 들어서 인덱스 브랜치가 다음과 같은 경우다 즉 인덱스는 정렬 상태이므로 다음과 같이 배치가 되어 있는 상태가 된다 그렇지에 정확히 범위를 찾고 끝 지점도 찾을 수 있기 때문에 Range Scan 이 되게 된다
하지만 인덱스 선두 컬럼을 가공하기 시작하면 Branch 가 배열되지 않기 때문이다 즉 인덱스 리프 자체가 깨지게 된다 이런 깨진 상태에서는 이를 옵티마이저가 정렬된 key 값을 사용할 수 없기 때문에 테이블 Full Scan 이 유리하다고 판단하고 그렇게 진행을 하게 된다
핵심은 정렬된 인덱스 테이블이 핵심이라는 것이다 이것이 깨지게 되면 옵티마이저는 인덱스를 활용할 수 없고 그 결과 테이블 Full Scan 을 선택만 하게 됩니다
우리는 앞으로 여러 가지 예제를 통해서 인덱스를 타는 경우 그렇지 않은 경우 많은 예제를 볼 것입니다 그중에서 첫 번째 선두 컬럼의 변환에 대해서 보았습니다