3.3장 인덱스 스캔 효율화
친절한 SQL 튜닝 3.3장
들어가며
ict, 클러스터, 파티션은 랜덤 액세스를 최소화하는데 매우 효과적이지만 적용하는데 성능 검증을 위한 장벽이 높은데,
인덱스 스캔 효율화
테이블 랜덤 액세스가 sql 성능에 큰 영향을 끼치지만, 튜닝은 의외로 간단하다. 운영 환경에서 가능한 일반적인 튜닝 기법은 인덱스 컬럼 추가다. 인덱스 스캔 효율화에 대해 알아보자.
인덱스 탐색은 어떻게 이루어지는지
루트 블록의 LMC를 통해 인덱스 스캔 시작점을 찾는 수직적 탐색을 수행한다. 이때 스캔량을 줄이는게 중요한데, 조건절에 인덱스 선행 조건이 있으면 스캔량을 확실히 줄일 수 있다.
인덱스 스캔 효율성 측정
SQL 트레이스을 통해 쉽게 알 수 있다.
rows: 스캔하고 얻은 레코드 수
cr(consistent reads): 몇 개의 블록을 읽었는지
용어 정리
선두 컬럼: 인덱스의 맨 앞의 컬럼
선행 컬럼: 어떤 컬럼보다 상대적으로 앞의 컬럼
인덱스 액세스 조건
인덱스 스캔 범위를 결정하는 조건절이다.
인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는데 영향을 미치고,
인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는데 영향을 미치는 조건절이다.
인덱스 필터 조건
테이블로 액세스 할지를 결정하는 조건절이다.
테이블 필터 조건
쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정한다.
비교연산자 종류와 컬럼 순서에 따른 군집성
선행 컬럼인
=일때 데이터는 군집되어 있다.중간 컬럼이 범위 검색인 경우 그 뒤 레코드들은 흩어지게 된다.
선행 컬럼이
=조건인 상태에서 첫 번째 나타나는 범위검색 조건이 인덱스 스캔 범위를 결정한다.
where c1 = 1
and c2 = 'A'
and c3 between '가' and '다'
and c4 = 'a'인덱스 액세스 조건: c1, c2, c3
인덱스 필터 조건: c4
인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 생기는 비효율
인덱스 컬럼의 조건절이 모두 등치 조건일 때 효율이 가장 좋고, 등치가 아니더라도 뒤쪽 컬럼일 때도 효율이 좋다. 반면, 인덱스 선행 컬림이 조건절에 없거나 범위 검색(부등호, between, like) 조건이면 비효율이 생긴다. 선행 컬럼의 범위 검색도 스캔량을 줄이지만, 대개 무시할만한 수준이다.
Between 을 In-List 로
선행 컬럼의 between 조건을 in 절로 바꾸면 큰 효과를 얻을 수도 있다. in 요소의 개수만큼 인덱스 수직적 탐색이 발생하기 때문에 between 사용할 때의 비효율이 사라진다. Index skip scan 방식과 비슷한 효과를 가진다.
select * from c1 between 1 and 3;
select * from c1 in (1, 2, 3);In-List 는 다음 조건에서 적절하게 선택할 수 있다.
In-List 항목이 적은 경우
레코드가 서로 멀리 떨어져 있는 경우
// 인덱스 (고객등급 + 고객번호)
where 고객등급 between 'C' and 'D'
and 고객번호 = 123 // C, D의 고객이 많아 멀리 떨어져있는 경우In-List 항목 개수가 늘어날 수 있다면,
이때는 NL 방식의 조인문이나 서브쿼리로 구현하자. 이 때는 in-List 값들을 코드 테이블로 관리하고 있을 때 가능한 방식이다.
In-List는 등치(=)인가?
IN 조건은 '=' 이 아니다. 데이터가 군집되어 있으면 IN-List 방식이 더 많은 I/O 를 반복적으로 발생시킬 수 있다. NUM_INDEX_KEYS 를 통해 액세스 조건 또는 필터 조건으로 유도하는방법도 존재한다.
Index Skip Scan
Between 을 In-List 조건으로 변환하지 않고 Index skip scan 을 사용했을 때 성능이 더 좋다. between 조건이어도 나머지 검색 조건을 만족하는 데이터가 서로 멀리 떨어져 있을 때 마찬가지로 효과적이다.
Between 와 Like 스캔 범위 비교
둘 다 범위 검색 조건으로 비효율적이지만, Between 이 Like 보다 무조건 효율적이다. 아래의 예시에서 Like 는 '201900' 의 가능성 과 '201913' 의 가능성을 배제할 수 없어 범위가 넓어진다.
where A like '2019%';
where A between '201901' and '201912;범위 검색 조건의 비효율
코딩을 쉽게 하려고 인덱스 컬럼에 범위검색 조건을 남용하면 인덱스 스캔 비효율이 생긴다. SQL 작성할 때 주의해야 하며, 데이터 분포에 따라 인덱스 컬럼에 대한 비교 연산자를 신중하게 선택해야 한다.
다양한 옵션 조건 처리 방식의 장단점
OR 조건 활용
OR 조건을 활용한 옵션 조건 처리 정리
인덱스 액세스 조건으로 사용 불가
인덱스 필터 조건으로도 사용 불가
테이블 필터 조건으로만 사용 가능
단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면, 18c부터 인덱스 필터 조건으로 사용 가능
가급적 사용하지 말자. 유일한 장점은 옵션 조건 컬럼이 NULL 허용 컬럼이더라도 결과집합을 보장한다는 것 뿐이다.
LIKE/BETWEEN 조건 활용
이 패턴을 사용하고자 할 때는 아래 네 가지 경우에 속하는지 반드시 점검해야 한다. (BETWEEN 조건은 1, 2번 조건만)
인덱스 선두 컬럼
선두 컬럼을 LIKE/BETWEEN 처리는 금물이다
만약 선두 컬럼을 입력하지 않으면 모든 데이터를 스캔하게 된다.
따라서 변별력이 안좋더라도 선두 컬럼은 가급적
=조건의 컬럼을 사용해야 한다. 그로 인한 비효율을 감수해야 한다.
NULL 허용 컬럼
실제 NULL 값이 입력되어 있으면 그 데이터는 결과 집합에서 누락된다.
숫자형 컬럼
LIKE 사용시 자동 형변환으로 인해 모든 데이터를 스캔할 수 있다.
가변 길이 컬럼
기대하지 않은 데이터가 나올 수 있다
조건절을 추가해라
UNION ALL 활용
옵션 조건 컬럼도 인덱스 액세스 조건으로 사용한다.
유일한 단점은 SQL 코딩량이 길어진다는 점이다.
NVL/DECODE 함수 활용
큰 장점은 옵션 조건 컬럼을 인덱스 액세스 조건으로 사용할 수 있다는 데 있다. 즉, UNION ALL보다 단순하면서도 UNION ALL과 같은 성능을 낸다.
단점은, LIKE 패턴처럼 NULL 허용 컬럼에 사용할 수 없다.
이 함수를 여러 개 사용하면 그중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어난다.
따라서 OR Expansion 기준으로 선택되지 않으면 인덱스 구성 컬럼이어도 모두 필터 조건으로 처리된다.
다른 성능 관련 SQL 패턴은 애플리케이션 특성에 맞게 어느 하나의 솔루션을 선택해서 개발팀에 권고안을 제시할 수 있지만, 옵션 조건 처리는 그럴 수 없기 때문에 어렵다.
지금까지 설명한 여러 방식의 장단점을 이해함으로써 상황에 따라 선택할 수밖에 없다.
함수 호출 부하 해소를 위한 인덱스 구성
PL/SQL 함수
PL/SQL 사용자 정의 함수는 매우 느리다.
느린 이유
가상머신 상에서 실행되는 인터프리터 언어
호출 시마다 컨텍스트 스위칭 발생
내장 SQL에 대한 Recursive Call 발생
따라서 PL/SQL 함수를 쓰지 않고 조인문으로 처리하는 것이 가장 좋다.
효과적인 인덱스 구정을 통한 함수 호출 최소화
인덱스를 효과적으로 구성하면 함수호출을 최소화할 수 있다.
함수를 사용한 조건을 포함하여, 조건절에 있는 모두 인덱스 액세스 조건으로 사용되도록 인덱스를 구성하면 함수 호출을 최소화할 수 있다.
ex) [생년 + 암호화된_전화번호] 인덱스
Last updated