🖥️ 들어가며
📌 튜닝에서 가장 중요한 점
: 즉, 디스크 I/O 횟수를 절감해야하고, 이를 위해 튜너는 블록을 적게 읽는 방법을 찾아야 합니다.
✏️ 1. 인덱스의 구조
- 인덱스: 키 순서대로 정렬한다는 점이 가장 중요합니다.
- 인덱스는 Index Key + RowID만 저장하면 되기 때문에 한 블록에 테이블보다 더 많은 데이터를 저장할 수 있습니다.
- RowID: Oracle에서 데이터의 물리적 위치를 나타내는 포인터입니다. (Data Object + File + Block + Row)
- Node: key 범위 + 블록 주소를 갖고 있습니다.
- Root Node: 가장 상위 노드, 하위의 Branch Node 수 만큼의 Row
- Branch Node: Root와 Leaf의 연결 고리, 자기 하위의 Leaf Node 수 만큼의 Row
- Leaf Node: Key + RowID로 구성, Key 순서대로 정렬, 이전/이후 Leaf의 Chain
- Level별 인덱싱 건수
- 따라서 하나의 인덱스 블록에 400 row를 저장할 경우, 약 10조 개를 인덱싱할 수 있습니다.
(0 Level: 400 → 1 Level: 400**2 → 2 Level: 400**3 → 3 Level: 400**4 → 4 Level: 400**5, 즉 블록 4개만 읽으면 약 10조 개를 인덱싱할 수 있는 것입니다.)
- 따라서 하나의 인덱스 블록에 400 row를 저장할 경우, 약 10조 개를 인덱싱할 수 있습니다.
SELECT *
FROM 테이블
WHERE 이름 BETWEEN '강성욱' AND '최지성';- 인덱스 탐색 순서
- Root Block → Branch Block으로 이동: 강성욱 김갑동 - 이에 해당하는 Leaf Block으로 이동
- Leaf Block: 강성욱 ~ 김갑동이 한 블록, Leaf Chain → 최지성이 있는 Leaf Block으로 이동
(최지성 이후는 조회하지 않아도 됩니다. 왜냐하면 Leaf Block은 기본적으로 정렬되어 있기 때문입니다.) - 정리하자면, Root Block을 읽고 마지막 Branch만 읽으면 찾고자 하는 시작점을 알 수 있습니다. 왜냐하면 인덱스는 기본적으로 정렬이 되어 있기 때문입니다.
- 수직적 탐색과 수평적 탐색
- 수직적 탐색
- Root - Branch - Leaf: 어느 Leaf 노드에 내가 찾고자 하는 데이터가 있는가?
- 읽고자 하는 시작점을 검색합니다.
- Random Access ⭐⭐⭐
- 수평적 탐색 ⭐⭐⭐
- Leaf Block의 시작점부터 종료점까지를 찾습니다.
- Sequential Access
- SQL 튜닝의 핵심 원리: Random Access, 수평적 탐색
- 수직적 탐색
- 중요한 점은 블록을 읽은 횟수입니다.
- 인덱스가 없다면? Table Full Scan해야 할 것입니다.
(원하는 값을 다 찾아도 데이터를 다 읽어야 합니다. 왜냐하면 유니크 여부를 모르기 때문입니다.) - Index Scan의 손익분기점은 약 10%입니다. (일반화한 값)
- Index Scan시, 테이블 R.A는 선형적으로 늘어납니다. 그렇기 때문에 R.A를 줄이는 것이 SQL 튜닝에서 매우 중요합니다.
- 인덱스가 없다면? Table Full Scan해야 할 것입니다.
- Random Access vs. Sequential Access
| Random Access | Sequential Access |
| 주로 하나의 블록에서 하나의 레코드만 읽음 | 하나의 블록에서 순차적으로 읽음 |
| 효율이 낮음, 높은 비용 (하나의 블록에서 하나의 레코드만 읽으니까) |
Index Leaf Block 읽을 때, Full Scan할 때, 수평적 스캔 |
| RowID를 이용한 테이블 액세스 | 적은 비용 (블록 하나를 다 읽으니까) |
| DBA를 이용한 인덱스 수직적 탐색 | Full Scan일 경우, Multi Block I/O 가능 (하나의 익스텐트 단위로 가져옴) |
| 클러스터링팩터(C.F)가 낮을 때 높은 성능 | |
| Single Block I/O |
- 수직적 탐색: 수능장에서 내 번호를 찾는 것, 인덱스 구조에 따라서 Root → Branch → Leaf 노드로 내려가는 과정
- 수평적 스캔: S.A (Full Scan)
- 테이블 Random Access: R.A, RowId로 테이블을 찾는 것, 실제로 테이블의 특정 행에 직접 접근하는 것
- 비용 비교
- 테이블 Random Access > 수직적 탐색 > 수평적 스캔
✏️ 2. 인덱스의 기본 원리
- 인덱스 사용이 불가능하거나 범위 스캔(Range Scan)이 불가능한 경우는?
- 인덱스 컬럼의 가공(좌변 가공): 정렬 원칙이 위배됨
- Null 검색: Null은 인덱스가 생성되지 않음
- 묵시적 형변환
- 부정검색
- 인덱스 컬럼의 가공
- 컬럼을 가공하지 말자
WHERE substr(업체명, 1, 2) = '대한'; -- X
WHERE 업체명 like '대한%'; -- 상수 가공 O
WHERE 월급여 * 12 = 50000000; -- X
WHERE 월급여 = 50000000 / 12; -- 상수 가공 O
WHERE to_char(일시, 'yyyymmdd') = :dt; -- X
WHERE 일시 >= to_date(:dt, 'yyyymmdd')
AND 일시 < to_date(:dt, 'yyyymmdd') + 1; -- 상수 가공 O
WHERE 계약구분||년월||일련번호 = 'C1312001'; -- X
WHERE 계약구분 = 'C'
AND 년월 = '1312'
AND 일련번호 = '001'; -- 상수 가공 O
WHERE 계약구분||년월||일련번호 = :str; -- X
WHERE 계약구분 = substr(:str, 1, 1)
AND 년월 = substr(:str, 2, 4)
AND 일련번호 = substr(:str, 6, 3) -- 상수 가공 O
- Null 검색
- 기본적으로 Null은 인덱스를 안 만든다고 생각하면 쉽습니다.
- 복합 인덱스인 경우, 둘다 Null이 아닌 이상 인덱스가 생성됩니다.
(첫 번째 컬럼이 Null인 경우, SQL Server는 맨 위로 정렬되고 / Oracle은 맨 밑으로 정렬됩니다.) - Null 검색에 따른 Index Range Scan 가능여부는 다음과 같습니다.
- 1번 SQL: SELECT * FROM 고객 WHERE 고객번호 IS NULL;
- 2번 SQL: SELECT * FROM 고객 WHERE 고객번호 IS NULL AND 고객명 = '홍길동';
| 번호 | 인덱스 구성 | 1번 SQL | 2번 SQL | 설명 |
| 1 | 고객번호 | X | X | Null은 인덱스를 만들지 않음, 따라서 Full Scan |
| 2 | 고객번호 + 고객명 (단, 둘다 Nullable 컬럼) |
X | O | 1번: 고객번호, 고객명 모두 Null일 경우 인덱스에서 검색 불가 (Full Scan) 2번: Null + 홍길동 데이터는 인덱스에서 데이터 생성 (Nullable일지라도 상수를 정확하게 지정하면 인덱스로 검색 가능) |
| 3 | 고객번호 + 고객명 (단, 고객명 컬럼 Not Null) |
O | O | 1번: 고객번호, 고객명 둘다 모두 Null 데이터가 존재할 순 없으므로(고객명이 Not Null이므로) 인덱스 생성* 2번: Null + 홍길동 데이터는 인덱스에 데이터 생성 |
*따라서 모델링 시, Not Null 설정이 중요합니다.
- 묵시적 형변환
- 컬럼과 상수 Data Type이 상이할 경우, 묵시적 형변환이 일어납니다.
- 문자 = 숫자: 문자를 숫자로 (즉, 문자를 숫자로 변환 후 비교합니다.)
- 문자 = 날짜: 문자를 날짜로
- 날짜 = 숫자: Error (이처럼 변환이 불가할 경우 에러가 발생합니다.)
- 단, LIKE 연산자일 경우 반대로 숫자 → 문자로 변경됩니다.
- 컬럼과 상수 Data Type이 상이할 경우, 묵시적 형변환이 일어납니다.
-- 고객번호: VARCHAR2 -> Full Table Scan
WHERE 고객번호 = 100;
-- 문자 = 숫자이므로 숫자로 묵시적 형 변환, Table Full Scan하면서 고객번호 컬럼을 전부 변경합니다.
-- 고객번호: NUMBER
WHERE 고객번호 = '100';
-- 숫자 = 문자이므로 숫자로 묵시적 형 변환, '100'이라는 상수 1건만 바뀌고 Index Scan할 수 있습니다.
- 부정검색
- 부정검색: NOT 연산자 사용하는 경우입니다.
- WHERE salary != 3000 보다는
WHERE salary > 3000 OR salary < 3000으로 작성하는 것이 인덱스 사용에 더 유리할 수 있습니다.
📒 정리하면
- 인덱스는 키 순서대로 정렬되며, Index Key와 RowID만 저장하기에 테이블보다 더 많은 데이터를 한 블록에 저장할 수 있습니다. (Root Node, Branch Node, Leaf Node로 구성됨)
- 인덱스 탐색은 Root Block에서 시작 → Branch Block를 거쳐 → Leaf Block으로 이동하는 수직적 탐색과
Leaf Block의 시작점부터 종료점까지 찾는 수평적 탐색으로 이루어집니다. - Index Scan의 손익분기점은 약 10%(일반화한 값)이며,
Random Access는 테이블에서 하나의 레코드만 읽는 방식으로 비용이 높고, Sequential Access는 순차적으로 읽는 방식으로 비용이 낮습니다. - 인덱스 사용이 불가한 경우는 인덱스 컬럼 가공(좌변 가공), Null 검색, 묵시적 형 변환, 부정검색이 있으며,
이를 피하기 위해선 상수 가공을 활용하거나 적절한 데이터 타입을 사용해야 합니다. - Null은 기본적으로 인덱스를 생성하지 않으나 복합 인덱스에서 한 컬럼이라도 Not Null이면 인덱스가 생성될 수 있으며,
묵시적 형변환 시 문자는 숫자나 날짜로 변환되고 LIKE 연산자 사용 시에는 숫자가 문자로 변환됩니다.
반응형
