권순용의 DB 이야기 - 인덱스를 액세스하는 실행 계획을 이해하자
SQL의 최적화를 수행하기 위해서는 SQL의 실행 계획이 가장 중요하다. 이와 같은 실행 계획은 SQL이 어떻게 수행되는지를 알려주는 지도와 같다. SQL을 최적화하면서 실행 계획을 해석하지 못한다면 이는 어떠한 의미도 없을 것이다. SQL의 실행 계획이야말로 SQL 최적화를 수행하기 위한 길을 안내하는 전도사의 역할이라는 것을 이해하길 바란다.
이와 같은 실행 계획에서 가장 많이 생성되는 것이 인덱스 관련 실행 계획이다.
많은 경우에 실행 계획의 확인 없이 SQL 튜닝을 수행하는 경우를 많이 보아 왔다. 이와 같이 SQL 튜닝을 수행한다는 것은 무엇을 의미하는가? 이는 SQL 튜닝을 수행하지 않는 것이나 마찬가지이다. 해당 SQL이 어떻게 수행되는지를 확인하지 않고 어떻게 해당 SQL을 최적화할 수 있겠는가? SQL의 실행 계획을 확인하지 않았는데 테이블들이 인덱스를 이용했는지 안 했는지를 어떻게 파악할 수 있겠는가? 또한 인덱스를 이용했다면 이용했다는 한 가지만을 이해하면 되는 것인가? 인덱스를 이용하는 방법에는 많은 경우의 수가 존재한다. 그러므로 실행 계획을 확인하지 않고 예측으로 현상을 파악한다면 이는 SQL의 최적화에 실패하게 된다. 이번에는 SQL의 실행 계획 중 인덱스를 이용하는 실행 계획 분석 방법에 대해 같이 한번 확인해 보자.
인덱스 이용의 종류를 이해하자
SQL을 작성하면서 해당 SQL이 인덱스를 이용하는지 아닌지에 대해 많은 생각을 하게 될 것이다. 그렇다면 인덱스를 이용하고 이용하지 않는 것만으로 끝나는 것인가? 인덱스를 이용한다면 모두 동일하게 인덱스를 이용하는 것인가? 그것은 아닐 것이다. 인덱스를 이용한다고 하여도 어떻게 이용하는지는 상황에 따라 변하게 될 것이다. 이는 인덱스를 이용하는 방법이 여러 가지가 존재하기 때문이다. 그렇다면 인덱스를 이용하는 방법에는 무엇이 존재할까? 다음을 확인해 보자.
- Index Range Scan
- Index Unique Scan
- Index Full Scan
- Index Fast Full Scan
- Index Skip Scan
- Index Min/Max Scan
- Index Join
인덱스를 액세스하는 방법에는 위와 같은 방법들이 존재한다. 이와 같이 우리가 생각지 못한 여러 방법으로 인덱스를 액세스할 수 있다. 이와 같은 방법들을 정확히 이해하지 못한다면 우리는 SQL을 분석하여 성능을 최적화하는 과정에 실패하게 된다. 따라서 이와 같은 인덱스의 액세스 방법은 실행 계획에 매우 자주 생성되므로 정확히 이해하는 것이 중요하다.
Index Range Scan을 이해하자
인덱스의 Range Scan은 인덱스를 이용하는 실행 계획에서 가장 자주 등장하는 실행 계획이다.
위와 같이 SQL을 수행한 경우 사원 테이블에는 사원번호 컬럼에 일반 인덱스가 존재한다고 가정하자. 그렇다면 해당 SQL은 인덱스를 이용할 수 있을 것이다.
해당 SQL이 인덱스를 이용한다면 위와 같이 사원번호 인덱스를 이용하여 테이블을 액세스하는 실행 계획이 생성될 것이다. 여기서 인덱스를 이용하는 실행 계획에는 사원번호 인덱스를 Range Scan하는 실행 계획이 생성되었다. 이는 무엇을 의미하는 것인가? 사원번호 컬럼으로 생성된 인덱스가 Unique 인덱스가 아니므로 사원 테이블에서 사원번호 컬럼의 값을 만족하는 데이터가 여러 건 추출될 수 있다는 의미이다. 결국, Index Range Scan은 여러 건의 데이터가 추출될 수 있다는 의미이다. 따라서 위의 SQL은 한 건의 데이터가 추출될 수도 있지만 여러 건의 데이터가 결과로 추출될 수도 있다.
Index Unique Scan을 이해하자
앞서 Index Range Scan을 확인해 보았다면 이제는 Index Unique Scan을 확인해 보자. Index Unique Scan은 무엇인가?
위의 SQL에서 사원번호 컬럼에는 Unique Index가 존재한다고 가정하자. 그렇다면 실행 계획은 아래와 같이 생성될 수 있다.
위의 실행 계획은 무엇을 의미하는 것일까? 위의 실행 계획은 앞서 언급한 실행 계획과 달리 Index Unique Scan 실행 계획이 생성되었다. 이는 해당 사원번호 컬럼에 생성된 인덱스가 Unique 인덱스이며 이를 = 조건으로 조회할 경우 발생할 수 있다. 이는 인덱스를 이용하여 무조건 한 건의 데이터만이 결과로 추출되거나 또는 결과가 추출되지 않는 경우에 해당한다. 따라서 위의 SQL은 한 건의 결과 또는 어떠한 결과도 추출되지 않게 된다.
이처럼 Index Unique Scan은 인덱스를 액세스하여 하나의 인덱스 값만을 액세스하는 경우에 발생하는 실행 계획이다. 그렇기 때문에 인덱스를 Unique 인덱스로 생성했을 경우에만 생성될 수 있는 실행 계획이 된다. 결국 Index Range Scan과 Index Unique Scan은 아래와 같이 정의할 수 있다.
- Index Range Scan : 인덱스를 액세스하여 여러 건의 데이터가 추출될 수 있을 경우에 발생하는 실행 계획
- Index Unique Scan : 인덱스를 액세스하여 오직 하나의 값 또는 결과가 추출되지 않는 경우에 발생하는 실행 계획
이와 같이 인덱스를 이용한다고 하여도 어떻게 이용하는가에 따라 우리가 예측할 수 있는 결과는 달라진다. 그러므로 인덱스를 이용했는가 아닌가도 중요하지만 인덱스를 어떻게 이용했는가도 매우 중요한 요소가 된다. 인덱스를 어떻게 이용했는가에 따라 성능 변화가 발생하게 되며 SQL을 최적화한다는 것은 해당 SQL에 맞는 인덱스 액세스 방법을 제어하는 것도 포함된다.
지금까지 인덱스에 대해 Index Range Scan과 Index Unique Scan을 확인해 보았다. 인덱스를 액세스하는 방법은 여러 가지가 존재하며 이중 위에서 확인한 두 가지의 방법은 매우 기본이 되는 방법이다. 이와 같은 방법을 기본으로 인덱스를 액세스하는 다른 방법들도 정확히 이해해야만 우리는 SQL 최적화를 수행할 수 있다. 실행 계획의 이해는 SQL을 최적화하기 위해 반드시 이해해야 할 요소이다. 그렇기 때문에 계속적으로 실행 계획을 확인하는 습관을 가져야 할 것이다. 다음 호에서는 인덱스를 이용하는 다른 방법에 대해 많은 예제를 통해 함께 확인해 보도록 하겠다.
필자소개
권순용 kwontra@hanmail.net｜Data Consulting 업무를 수행하는 ㈜엑시엄의 대표이사이며 DBA로 시작하여 SQL 튜닝, 데이터베이스 아키텍처 및 모델링 업무를 주로 수행했다. 데이터베이스 교육에도 많은 관심을 가지고 있으며 저서로는 『Perfect! 오라클 실전 튜닝, 『초보자를 위한 오라클 10g』 및 『INSIDE SQL』이 있다. 또한, 데이터 액세스 최적화에 대한 특허를 출원했다.
출처 : 한국 마이크로 소프트웨어 [2009년 4월호]
댓글