Effective-SQL Better-Way-12 : 인덱스를 단순 필터링 이상의 목적으로 사용하자

저번 글에서 인덱스 스캔에 관련하여 공부를 했었습니다. 이번에는 인덱스의 사용목적에 대해 공부해보겠습니다.

인덱스 구조

인덱스를 생성하면 어떤 과정을 거칠까요? 아마 다음과 같이 이루어질 것입니다.

1. 데이터베이스 엔진은 생성된 인덱스를 별도의 저장공간을 만듭니다.
2. 인덱스화 된 데이터들을 별도의 저장공간에 저장시킨다.

위에서 봤을 때 인덱스 테이블도 같이 만들기 때문에 비효율적으로 보이지만, 테이블의 모든 로우를 매번 검색하지 않고 빠르게 데이터를 찾아올 수 있기 때문에 인덱스는 매우 유용하다고 생각합니다.

인덱스와 조인 관계

일단 조인은 무엇이며 왜 사용하는 것일까요?

조인은 여러 테이블에서 레코드를 조합하여 하나의 로우로 표현한 것이다.

운영중인 많은 서비스들에서 하나의 테이블에 모든 데이터를 담지 않습니다. 정규화를 통해 여러 테이블로 나눠 운영시키고 있죠. 조인은 여러 테이블의 레코드를 한번에 가지고 오고 싶을 때 사용합니다. 즉 조인을 이용하면 정규화된 모델의 데이터를 특정 처리에 적합한 역정규화된 형태로 변환 할 수 있습니다.

따라서 조인을 할 경우 인덱스 설정에 따라 응답하는 시간에 큰 영향을 줄 수 있다고 합니다. 여러 테이블에 레코드를 결합시키기 때문에 데이터를 더 많이 읽느라 탐색 대기 시간에 민감합니다.

그럼 어떻게 인덱스를 설정하면 조인의 성능을 높일 수 있을까요? 일단 데이버베이스 엔진이 수행하는 조인 알고리즘 부터 하나씩 알아보겠습니다.

중첩 루프 조인

중첩 루프 알고리즘은 조인의 가장 기본적인 알고리즘입니다. 중첩 루프 조인은 간단히 말하면 다음과 같습니다.

쿼리 두개가 중첩되어 실행한다.

이 말은 즉 2개의 쿼리로 나눠 첫번째 Outer 테이블에 데이터 결과를 먼저 가지고오고, 두번째는 Outer 쿼리 결과 집합을 통해 각 로우에 대응하는 데이터를 다른 테이블에서 가지고 옵니다.

따라서 중첩 루프 조인 알고리즘 수행시킬 경우 조인 조건에 참여하는 컬럼을 인덱스로 만들면 효과가 좋습니다. 또한 Outer 테이블(Driving 테이블)의 개수가 적을 수록 성능이 좋아집니다.

• Example

/* 인덱스 = Student id */
SELECT * 
    FROM Students 
    INNER LOOP JOIN Courses ON Students.id = Courses.student_id;

/* 2개의 쿼리로 나타냈을 경우 */
SELECT *            
    FROM Students,        
        (        
            SELECT Student_id
                FROM Cources
        )        
    WHERE            
        Students.id = Cources.id;

해시 조인

해시 조인 알고리즘은 다음과 같습니다.

레코드가 적은 한쪽 테이블 데이터를 해시 테이블로 만든 후, 다른 쪽 테이블의 각 로우를 매우 빠르게 탐색한다.

즉 해시 조인은 한쪽 테이블이 작은 집합이어야 큰 성과를 나타낼 수 있습니다. 일반적으로 대용량 테이블 조인 연산은 해시 조인 알고리즘이 좋지만 해시 메모리가 많이 필요하기 때문에 신경을 써주아야합니다.

해시 조인은 해시 테이블을 사용하기 때문에 조인되는 컬럼을 인덱스로 만들 필요가 없습니다. 그렇기에 WHERE이나 ON 에 들어가는 컬럼을 인덱스 해야 성능을 향상 시킬 수 있습니다.

• Example

/* 인덱스 = Student id */
SELECT * 
    FROM Students, Courses
    WHERE Students.id = Courses.student_id;

소트머지 조인

소트머지 조인 알고리즘은 다음과 같습니다.

조인 조건에 따라 두 테이블을 정렬 후 지퍼처럼 결합하는 형식이다.

소트머지 조인이랑 해시 조인은 비슷하지만 한 번에 모든 레코드를 읽을 수 있는 독립적인 조건을 구성하는 컬럼에 대한 인덱스가 필요합니다. 조인 순서는 별 의미가 없는 것이 소트머지 조인의 특징입니다. 다른 조인 알고리즘은 외부 조인을 할때 순서에 상관이 있지만 소트머지 조인은 그렇지 않습니다.

소트머지 조인은 데이터가 정렬만 잘 되어 있으면 좋은 성능을 발휘하지만, 그렇지 않으면 정렬하는 비용 때문에 잘 사용하지 않습니다. 또한 두 결과 집합 크기 차이가 큰 경우 비효율적입니다.

정리하자면?

위에 모든 조인 알고리즘은 옵티마이저가 알아서 수행해주지만 사용자가 강제로 조정이 가능합니다. (힌트 사용) 그러나 옵티마이저가 알아서 현재 데이터를 기반으로 가장 적합한 조인 방법을 선택하기 때문에 우리는 생성한 인덱스가 적절한지 확인하는 편이 좋습니다.

데이터 클러스터링

데이터 클러스트링을 통해 인덱스를 효과적으로 사용할 수 있습니다. 데이터 클러스트링이 뭘까요?

연속적으로 접근하는 데이터들을 모아 실제로 저장할 때 인접해서 저장하는 방법

즉 연속적으로 읽히는 데이터를 실제 디스크에 저장할 때 인접해서 저장하는 방법입니다. 이 방법은 I/O의 연산을 줄이기 때문에 스캔 할때도 탁월한 효과를 나타냅니다. 따라서 클러스터링 데이터를 인덱스로 사용하면 인접 데이터를 빠르게 가지고 올 수 있어 좋습니다.

그 외..

조인과 클러스터링 말고 다른 방법도 있습니다.

1. 가지고 올 데이터를 인덱스로 걸어놓으면 테이블에 접근하지 않기 때문에 효과적으로 성능을 높일 수 있습니다.

밑에 예를 보죠.

• Order Table

CREATE TABLE Orders (
  OrderNumber int IDENTITY (1,1) NOT NULL,
  OrderDate date NULL,
  ShipDate date NULL,
  CustomerID int NULL,
  EmployeeID int NULL,
  OrderTotal decimal NULL
)

CREATE INDEX CustOrder
    ON Orders (CustomerID, OrderTotal);

• 전체 주문 금액 조회 쿼리

SELECT CustomerID, Sum(OrderTotal) AS SumOrderTotal
    FROM Orders
    GROUP BY CustomerID;

인덱스를 CustomerID, OrderTotal 컬럼으로 생성했고 조회 쿼리에 인덱스 컬럼을 조회합니다. 그럼 인덱스 테이블에서 스캔을 하고 해당 결과 값을 바로 반환하기 때문에 테이블을 접근하지 않고 결과를 반환시킬 수있습니다.

여기서 중요한 점은 만약 인덱스가 안걸린 컬럼을 찾을 경우에는 더 적은 로우를 가지고 오더라도 테이블 스캔이 되기 때문에 성능이 느릴 수 있습니다.

2. 인덱스는 ORDER BY절에도 영향을 미칩니다.

정렬 작업은 많은 CPU 자원을 사용합니다. 또한 데이터베이스가 임시로 추출한 데이터들을 버퍼에 담아 둡니다. 따라서 정렬된 결과가 반환되기 전에 모든 데이터를 읽어야 합니다.

하지만 인덱스는 인덱스 컬럼 데이터를 미리 정렬 시켜놓습니다. 따라서 인덱스를 사용하면 ORDER BY절에 명시된 순서대로 데이터를 정렬할 필요가 없고 ORDER BY 연산을 할 필요도 없습니다.

데이터베이스는 양방향으로 인덱스를 읽을 수 있어 ORDER BY 절에 명시된 것과는 정반대 방향으로 인덱스 범위를 스캔하더라도 파이프라인 효과를 볼 수 있습니다. 하지만 인덱스 컬럼을 2개 이상으로 만들 때는 정렬 방향이 중요할 수 있습니다.