3-3-3. 인덱스 스캔 효율화
과목3. SQL 고급 활용 및 튜닝
제3장 인덱스 튜닝
제3절 인덱스 스캔 효율화
1장 4절에서 데이터베이스 I/O 원리를 설명하면서 random 액세스와 Sequential 액세스의 차이점을 설명하였다. Sequential 액세스는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식을 말하고, random 액세스는 레코드간 논리적, 물리적 순서를 따르지 않고 한 건을 읽기 위해 한 블록씩 접근(=touch)하는 방식이라고 했다. 그리고 I/O 튜닝의 핵심 원리로서 아래 두 가지 항목을 꼽았다.
① random 액세스 발생량을 줄인다. ② Sequential 액세스에 의한 선택 비중을 높인다.
본 장에서는 지금까지 테이블 random 액세스를 최소화하는 방안에 대해 설명했고, 이는 ①번 항목에 해당한다. 지금부터는 ②번 Sequential 액세스에 의한 선택 비중을 높이는 방안, 그 중에서도 인덱스를 Sequential 방식으로 스캔하는 단계에서 발생하는 비효율 해소 원리를 다룬다.
1. 인덱스 선행 칼럼이 범위조건일 때의 비효율
인덱스 구성 칼럼이 조건절에서 모두 등치(=) 조건으로 비교되면 리프 블록을 스캔하면서 읽은 레코드는 모두 테이블 액세스로 이어진다. 읽고서 버리는 레코드가 하나도 없으므로 인덱스 스캔 단계에서의 효율은 최상이다. 인덱스 칼럼 중 일부가 '=' 조건이 아니거나 조건절에서 생략되더라도 그것이 뒤쪽 칼럼일 때는 비효율이 없다. 예를 들어, 인덱스가 [아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 순으로 구성됐을 때 조건절이 아래와 같은 경우를 말한다.
WHERE 아파트시세코드 = :a WHERE 아파트시세코드 = :a AND 평형 = :b WHERE 아파트시세코드 = :a AND 평형 = :b AND 평형타입 = :c WHERE 아파트시세코드 = :a AND 평형 = :b AND 평형타입 between :c AND :d
반면, 인덱스 선행 칼럼이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스를 스캔하는 단계에서 비효율이 발생한다. 예를 들어, 인덱스가 [아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 순으로 구성된 상황에서 아래 SQL을 수행하는 경우를 살펴보자.
[그림 Ⅲ-4-22]는 위 조건절을 만족하는 두 개 레코드(그림에서 음영 처리된 레코드)를 찾기 위해 인덱스를 범위 스캔하는 과정을 도식화한 것이다.
인터넷매물이 between 조건이지만 선행 칼럼들(아파트시세코드, 평형, 평형타입)이 모두 '=' 조건이기 때문에 전혀 비효율 없이 조건을 만족하는 2건을 빠르게 찾았다. 인덱스 선행 칼럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다. 이제 인덱스 구성을 [인터넷매물 + 아파트시세코드 + 평형 + 평형타입] 순으로 바꾸고 나서 같은 SQL을 수행해 보면, [그림 Ⅲ-4-23]처럼 인덱스 스mg/publishing/img/knowledge/SQL_354.jpg">
인덱스 선두 칼럼인 인터넷매물에 between 연산자를 사용하면 나머지 조건(아파트시세코드='A01011350900056' AND 평형 = '59' AND 평형타입 = 'A')을 만족하는 레코드들이 인터넷매물 값(0, 1, 2, 3)별로 뿔뿔이 흩어져 있게 된다. 따라서 조건을 만족하지 않는 레코드까지 스캔하고서 버려야 하는 비효율이 생긴다.
2. 범위조건을 In-List로 전환
범위검색 칼럼이 맨 뒤로 가도록 인덱스를 [아파트시세코드 + 평형 + 평형타입 + 인터넷매물] 순으로 변경하면 좋겠지만 운영 중인 시스템에서 인덱스 구성을 바꾸기는 쉽지 않다. 이럴 때 between 조건을 아래와 같이 IN-List로 바꿔주면 가끔 큰 효과를 얻는다.
[그림 Ⅲ-4-24]는 between 조건을 IN-List로 바꾸었을 때의 스캔 과정을 도식화한 것이다.
왼쪽에 화살표가 두 개인 이유는 인덱스의 수직적 탐색이 두 번 발생하기 때문이며, 이때의 실행계획은 아래(INLIST ITERATOR 오퍼레이션 주목)와 같다.
0
SELECT STATEMENT
1
37
1
INLIST ITERATOR
2
TABLE ACCESS BY INDEX ROWID
매물아파트매매
1
37
3
INDEX RANGE SCAN
매물아파트매매_PK
1
SQL Server에서의 실행계획은 다음과 같고, 특히 트레이스를 걸면 스캔 수가 2로 표시되는 것에 주목하기 바란다.
'매물아파트매매' 테이블. 스캔 수 2, 논리적 읽기 수 8, 물리적 읽기 수 0, 미리 읽기 수 0.
2
2
--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
2
--Index Seek(OBJECT:([..].[dbo].[매물아파트매매].[매물아파트매매_PK]),
SEEK:( [매물아파트매매].[인터넷매물] = '1'
AND | [매물아파트매매].[아파트시세코드] = 'A01011350900056'
AND | [매물아파트매매].[평형] = '59'
AND | [매물아파트매매].[평형타입] = 'A'
OR | [매물아파트매매].[인터넷매물] = '2'
AND | [매물아파트매매].[아파트시세코드] = 'A01011350900056'
AND | [매물아파트매매].[평형] = '59'
AND | [매물아파트매매].[평형타입] = 'A' )
2
--RID Lookup(OBJECT:([SQLPRO].[dbo].[매물아파트매매]),SEEK:([Bmk1000] = [Bmk1000]))
인덱스를 위와 같이 두 번 탐색한다는 것은 SQL을 아래와 같이 작성한 것과 마찬가지가 된다. 모든 칼럼이 '=' 조건인 것에 주목하기 바란다.
인덱스 선두 칼럼의 between 조건을 IN-List 조건으로 바꿀 때 주의할 점은, IN-List 개수가 많지 않아야 한다는 것이다. [그림 Ⅲ-4-23]처럼 필요 없는 범위를 스캔하는 비효율은 사라지겠지만 [그림 Ⅲ-4-24]처럼 인덱스 수직 탐색이 여러 번 발생하기 때문이다. IN-List 개수가 많을 때는, between 조건 때문에 리프 블록을 추가로 스캔하는 비효율보다 IN-List 조건 때문에 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있고, 인덱스 높이(height)가 높을 때 특히 그렇다. Oracle이라면 위와 같은 상황에서 Index Skip Scan이 유용할 수 있다. 1절에서 설명한 Index Skip Scan은 인덱스 선두 칼럼이 누락됐을 때뿐만 아니라 부등호, between, like 같은 범위검색 조건일 때도 사용될 수 있다.
3. 범위조건을 2개 이상 사용할 때의 비효율
인덱스 구성이 [회사 + 지역 + 상품명]일 때, 아래와 같이 범위검색 조건을 2개 이상 사용하면 첫 번째가 인덱스 스캔 범위를 거의 결정되고, 두 번째는 필터 조건 역할만 하기 때문에 성능상 불리해질 수 있다.
스캔량이 소량일 때는 그 차이가 미미하지만 대량일 때는 상당한 성능차이를 보일 수 있으므로 인덱스 칼럼에 대한 비교 연산자를 신중하게 선택해야 한다. 만약 지역 칼럼에 대한 검색조건이 입력되지 않을 수도 있어 위와 같이 LIKE 연산자를 사용한 거라면 SQL을 아래와 같이 2개 만들어 사용하는 것이 좋다.
또는 아래 처럼 UNION ALL을 이용하는 방법도 있다.
기존 인덱스 구성 하에서, UNION ALL 상단 쿼리는 기존과 동일한 비효율을 안은 채 수행되겠지만 하단 쿼리만큼은 최상으로 수행될 수 있다. 만약 UNION ALL 상단 쿼리까지 최적화하려면 [회사 + 상품명] 순으로 구성된 인덱스를 하나 더 추가해야 한다. 인덱스를 새로 추가하는 데 부담이 있으면 기존 인덱스 순서를 [회사 + 상품명 + 지역] 순으로 변경하는 것을 고려할 수 있는데, 그럴 경우 UNION ALL 하단 쿼리를 처리할 때 불리해진다. 따라서 이는 상품명 조건에 입력되는 값의 선택도에 따라 결정할 사항이다.
Last updated
Was this helpful?
