불친절 SQL 2장 정리 (인덱스 기본)
1. 인덱스 튜닝의 핵심 요소
- 스캔 과정 비효율 줄이기 -> 인덱스 구성 및 순서에 영향
- 랜덤 엑세스 최소화 튜닝 -> 인덱스 구성에 영향
2. 인덱스 탐색 과정
-> 수직적 탐색을 통해 리프 블록을 찾는다 (루트에서 브랜치를 통해 리프까지)
-> 이때 키에는 하위 브랜치의 범위가 담긴다.
-> 비교해야하는 값과 키를 탐색하며, 자신의 값보다 같거나 큰 값을 만나면 그 옆의 브랜치로 이동한다.
-> 수평적 탐색을 통해 값을 찾는다. (리프 사이 사이를 다니며 탐색)
-> 리프 블록의 값을 비교하며 필요한 값을 찾는다.
-> 인덱스의 값으로 충분하지 않으면, ROWID를 사용해서 테이블에 접근해서 필요한 데이터를 가져온다
3. 결합 인덱스의 탐색은 각각 이루어지는게 아니라, 모든 조건을 만족하는 인덱스를 찾는다
ex) 성별,이름 순 인덱스에 성별은 남, 이름은 홍길동을 탐색하면, 성별은 남을 찾은 후 그 후에 홍길동을 찾는게 아니라,
성별이 남이면서 이름이 홍길동인 데이터를 찾아서 탐색을 시작한다.
* 인덱스의 구조
1. 루트와 브랜치는 키와 하위 브랜치 위치를 저장
키는 자신보다 하위 브랜치가 가진 키의 범위 <- 찾아야하는 값과 키에 담긴 범위와 비교하며 찾아야 하는 값보다 같거나 큰 값을 만나면 그 이전 브랜치로 이동
* 이때 이전 브랜치로 이동하는 이유는 값의 범위만 나타내기 때문에 같거나 큰 범위 이전으로 이동해야 맨마지막 값이 시작 위치이기 때문이다.
(5를 찾아야할 때 브랜치 1( < 5 ) 브랜치2 ( 5>= )이라면, 브랜치 1로 이동해서 탐색하여 브랜치1과 브랜치2 사이에 정확히 5인 지점을 찾는 느낌! - 이것이 수직적 탐색 과정임)
2. 리프 - 개별 값과 ROWID가 저장
이때 ROWID를 통해 실제 테이블에 접근 할 수 있다.
4. 인덱스를 사용한다는 것
인덱스의 기본 사용법은 Index Range Scan을 하는 것이다.
Index Range Scan의 핵심은 스캔의 시작점(수직적 탐색)과 끝점(수평적탐색) 지점을 명확하게 아는 것이다.
이때, 인덱스를 사용해야하는 컬럼을 가공하거나 결합 인덱스에 중간 값만으로는 스캔의 시작과 끝을 알 수 없음으로
이를 주의해야한다.
4.1 인덱스를 Range Scan할 수 없는 이유
앞서 살펴보았듯 인덱스에서 일정 범위 스캔한다는 것은 그 시작과 끝을 아는 것이다.
다음과 같은 경우에 인덱스의 시작과 끝을 알 수 없다 (해당 SQL 조건문에 사용된 컬럼은 인덱스가 걸려있다고 가정)
(1) 인덱스 컬럼 조건절 가공
where TO_CHAR(t.date) = '2021%'
(2) LIKE를 %%로 사용
where 업체명 like '%대한%'
(3) OR 혹은 IN 연산자 사용
where (전화번호 = tel_no OR 고객명 = cust_nm)
where 고객명 IN ('홍길동','김철수')
*OR과 IN 연산자의 경우 UNION ALL로 SQL을 분리한다면 인덱스를 사용할 수 있다.
SELECT *
FROM 고객
WHERE 고객명 = cust_nm
union all
SELECT *
FROM 고객
WHERE 전화번호 = tel_no
AND (고객명 <> : cust_nm or 고객명 is null)
따라서, 인덱스를 적용한 컬럼을 조건절에서 IN 혹은 OR 연산자와 함께 사용하는 경우 옵티마이저가 위와 같은 UNION ALL 방식으로 SQL을 변형시켜주는데, 이를 각각 IN-List Iterator , OR-Expansion이라 부른다.
5. 인덱스 사용 조건
* 여기서 사용하는 선두 컬럼은 인덱스의 맨 앞 선두를 의미하는게 아니라, 특정 컬럼의 바로 앞 컬럼들을 의미한다.
맨 앞 컬럼은 선행 컬럼이라 부를 것이다.
ex 인덱스 [전화번호+이름+나이] -> 전화번호는 선행컬럼이면서 이름의 선두컬럼, 이름은 나이의 선두컬럼
즉, 인덱스 선두 컬럼이 가공하지 않은 상태로 조건절에 있어야 한다.
ex) 소속팀 + 사원명 + 나이 순서로 정렬된 인덱스에서 사원명 = 홍길동으로만 조회하면 시작과 끝 지점을 찾을 수 없음
하지만, 사원의 선두컬럼인 소속팀에 대한 조건이 있으면 시작 지점을 찾을 수 있음! 따라서 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야함
6. 인덱스를 이용한 소트 연산 생략
인덱스를 Range Scan 할 수 있는 이유는 데이터가 정렬되어 있기 때문이다.
이러한 특성을 활용하면 소트 연산 생략 효과를 부수적으로 얻을 수 있다.
SORT 연산 생략 조건은 다음과 같다.
- 사용한 인덱스 모든 컬럼에 대한 = 조건이 걸려있을 때
- 선두컬럼에 대한 = 조건과 ORDER BY에 나머지 컬럼이 순서대로 지정되어 있을 때
- 선두 컬럼에 대한 = 조건이 있고, 그 뒤 인덱스 컬럼에 대한 MIN과 MAX를 구할 때
- WHERE문에 = 조건이 걸린 컬럼이 인덱스로 구성되어 있고, ORDER BY에서 사용한 값을 차례대로 추가하면 SORT 연산 생략 가능하다.
* 이때, ORDER BY에서 가공된 컬럼을 사용하면 생략 불가, MIN과 MAX를 구할 때 가공한 컬럼을 사용하면 SORT 생략불가
7. 자동 형변환
- 문자 VS 숫자 -> 숫자
- 날짜 VS 문자 -> 날짜
- LIKE 연산에선 숫자를 문자로 바꿈
자동형 변환도 컬럼을 가공한 것과 같다. 따라서 인덱스가 작동하지 않을 수 있다.
> 자동 형변환 팁 (조건 값은 컬럼과 같은 자료형을 사용한다)
--문자열 날짜 자료형과 문자열 조건값 사용법
WHERE T1.ORD_YMD = '20170313'
--DATE 자료형과 문자열 조건값 사용법
WHERE T1.ORD_DT = '20170313';
--DATE 자료형 범위조건 처리
WHERE T1.ORD_DT >= TO_DATE('20170313','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170313','YYYYMMDD') +1WHERE 절에서 DATE 자료형의 컬럼에는 DATE 형태의 조건을, 문자열 자료형의 날짜 컬럼에는 문자열 조건값을 사용
* 조건 값이 무조건 문자형이라면, 테이블의 컬럼을 자동 변환하는 경우는 없다 (다만 DBMS 이관 및 올바른 사용법을 위해 무조건 문자 조건을 사용하는 방법은 지양하도록하자
8. 여러가지 인덱스 스캔 방식
(1). INDEX RANGE SCAN : 인덱스의 수직+수평 탐색을 통해 필요한 범위만 스캔
(2). INDEX FULL SCAN : 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
인덱스 선두 컬럼이 조건절에 없으면, 옵티마이저는 먼저 Table Full Scan을 고려 > 이게 너무 부담스러울 때 선택
+ 선두는 없지만 인덱스 스캔 단계에서 대부분의 레코드를 필터링하고, 아주 일부만 테이블을 엑세스 하는 상황일 때도 사용
(3). INDEX UNIQUE SCAN: 수직적 탐색만으로 데이터를 찾는 방식이다. Unique 인덱스를 = 조건으로 탐색하는 경우
+ Unique 인덱스라고 해도 범위 검색 조건으로 검색할 때는 Index Range Scan으로 처리된다.
+ Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan
(4). INDEX SKIP SCAN : 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스 하는 방식
작동 조건
- Distinct Value 개수가 적은 선두 컬럼이 조건절에 없고, 후행컬럼의 Distinct Value 개수가 많을 때
- 선두컬럼에 대한 조건절은 있고, 중간 컬럼에 대한 조건절이 없는 경우
- 선두 컬럼이 부등호, BERWEEN,LIKE 같은 범위검색 조건일 때도 사용 가능
* Distinct Value란 특정 컬럼에서 중복되지 않고 유일한 값
(5). INDEX FAST FULL SCAN: Mutiblock I/O 방식으로 스캔 관련 힌트는 index_ffs와 no_index_ffs (세그먼트 전체 스캔)
+ 쿼리에 사용한 칼럼이 모두 인덱스에 포함돼 있을 때만 사용할 수 있다
+ 속도는 빠르지만, 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다
+ 인덱스 파티션 x여도 병렬 쿼리가 가능하다는데 이건 무슨말..?
(6). INDEX RANGE SCAN DESCENDING : INDEX RANGE SCAN과 동일하지만, 내림차순으로 정렬된 결과를 얻음
+ ORDER BY를 선두컬럼에 DESC로 주거나, INDEX_DESC 힌트를 사용해서 유도