복합 인덱스의 기술: 컬럼 순서가 중요한 이유

복합 인덱스의 기술: 컬럼 순서가 중요한 이유

데이터베이스는 대부분의 애플리케이션의 중추이며, 성능은 사용자 경험에 지대한 영향을 미칩니다. 쿼리가 느려지면 전체 시스템이 고통받습니다. 데이터베이스 관리자와 개발자가 사용할 수 있는 많은 최적화 기법 중에서 인덱싱은 강력한 도구로 부상합니다. 그러나 인덱싱의 진정한 기술은 단순히 인덱스를 생성하는 것이 아니라, 특히 복합 인덱스의 경우 그 미묘한 차이를 이해하는 데 있습니다. 잘 설계된 복합 인덱스는 데이터 검색을 극적으로 가속화할 수 있는 반면, 잘못 설계된 인덱스는 쿼리 옵티마이저에 의해 완전히 무시되거나 성능을 저하시킬 수도 있습니다. 이 글에서는 복합 인덱스 내 컬럼 순서의 중요한 역할을 탐구하고, 이 사소해 보이는 세부 사항이 왜 데이터베이스 효율성에 엄청난 실질적인 의미를 갖는지 설명합니다.

핵심적으로 데이터베이스 인덱스는 책 뒤의 색인과 유사합니다. 특정 용어를 찾기 위해 모든 페이지를 순차적으로 스캔하는 대신, 색인에 나열된 관련 페이지로 빠르게 이동할 수 있습니다. 이 원칙은 데이터베이스에도 적용되어 모든 레코드를 검사하지 않고도 데이터 행을 찾을 수 있도록 합니다.

인덱스에 대해 이야기할 때 두 가지 핵심 용어가 떠오릅니다.

• 단일 컬럼 인덱스: 테이블의 단일 컬럼에 생성된 인덱스입니다. 해당 특정 컬럼의 값을 기준으로 행을 빠르게 찾는 데 도움이 됩니다. 예를 들어, users.email에 대한 인덱스는 이메일 주소로 빠른 조회를 가능하게 합니다.

• 복합 (다중 컬럼) 인덱스: 테이블의 두 개 이상의 컬럼에 생성된 인덱스입니다. 이 유형의 인덱스는 WHERE, ORDER BY 또는 GROUP BY 절에 여러 컬럼을 포함하는 쿼리에 특히 강력할 수 있습니다. 복합 인덱스의 핵심 측면은 정의되는 컬럼 순서입니다.

순서의 중요성을 설명하기 위해 실용적인 시나리오를 고려해 보겠습니다. customer_id, order_date, status와 같은 컬럼을 포함하는 수백만 개의 레코드가 있는 orders 테이블을 상상해 보세요.

다음과 같은 쿼리를 자주 실행한다고 가정해 보겠습니다.

1. SELECT * FROM orders WHERE customer_id = 123;
2. SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';
3. SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC;
4. SELECT * FROM orders WHERE order_date > '2023-01-01';
5. SELECT * FROM orders WHERE status = 'shipped';

이제 ((customer_id, order_date)) 복합 인덱스가 ((order_date, customer_id))보다 어떻게 성능을 발휘하는지 살펴보겠습니다.

가장 왼쪽 접두사 규칙 이해

복합 인덱스를 제어하는 기본 원칙은 "가장 왼쪽 접두사 규칙"입니다. ((col_a, col_b, col_c)) 인덱스는 다음을 효율적으로 검색하는 데 사용할 수 있습니다.

• col_a
• col_a, col_b
• col_a, col_b, col_c

그러나 col_b, col_c, col_b, col_b, 또는 col_a, col_c ( col_b 없이)에 대해서는 효율적으로 직접 검색할 수 없습니다. 인덱스의 가장 왼쪽 컬럼으로 시작하지 않기 때문입니다. 마치 (성, 이름)으로 정렬된 전화번호부와 같습니다. 특정 성을 가진 모든 사람이나 특정 성과 이름 모두를 가진 모든 사람을 쉽게 찾을 수 있습니다. 하지만 이름을 먼저 모르면 특정 이름의 모든 사람을 쉽게 찾을 수는 없습니다.

시나리오 1: 인덱스 ((customer_id, order_date))

이 인덱스를 생성해 보겠습니다.

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

• 쿼리 1 (WHERE customer_id = 123): 이 쿼리는 idx_customer_date를 완전히 활용할 수 있습니다. 데이터베이스는 customer_id 부분이 가장 왼쪽 컬럼이기 때문에 customer_id = 123에 대한 레코드로 빠르게 범위를 좁힐 수 있습니다.
• 쿼리 2 (WHERE customer_id = 123 AND order_date > '2023-01-01'): 이 쿼리도 idx_customer_date를 완전히 활용할 수 있습니다. 데이터베이스는 먼저 customer_id로 필터링한 다음 order_date가 조건을 충족하는 해당 하위 집합 내에서 효율적으로 레코드를 찾을 것입니다. 이는 두 WHERE 절이 인덱스로 처리되기 때문에 이중 이익을 제공합니다.
• 쿼리 3 (WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC): 여기서 인덱스는 데이터를 필터링하는 데 도움이 될 뿐만 아니라 정렬에도 도움이 됩니다. customer_id 내의 데이터는 이미 order_date별로 정렬되어 있기 때문에, 추가적인 정렬 작업(MySQL 용어로 "filesort") 없이도 데이터베이스는 ORDER BY 절을 효율적으로 수행할 수 있습니다. 이것은 엄청난 성능 향상입니다.
• 쿼리 4 (WHERE order_date > '2023-01-01'): 이 쿼리는 idx_customer_date를 효과적으로 사용할 수 없습니다. order_date가 가장 왼쪽 컬럼이 아니기 때문에 데이터베이스는 전체 테이블 스캔을 하거나 order_date에 사용 가능한 다른 단일 컬럼 인덱스를 사용할 가능성이 높습니다.
• 쿼리 5 (WHERE status = 'shipped'): 이 쿼리는 status가 인덱스의 일부가 아니므로 확실히 idx_customer_date를 사용할 수 없습니다.

시나리오 2: 인덱스 ((order_date, customer_id))

이제 역순 인덱스를 고려해 봅시다.

CREATE INDEX idx_date_customer ON orders (order_date, customer_id);

• 쿼리 1 (WHERE customer_id = 123): customer_id가 가장 왼쪽 컬럼이 아니기 때문에 이 쿼리는 idx_date_customer를 효과적으로 사용할 수 없습니다. 데이터베이스는 전체 테이블 스캔을 수행하거나 사용 가능한 경우 customer_id에 대한 단일 컬럼 인덱스를 사용할 가능성이 높습니다.
• 쿼리 2 (WHERE customer_id = 123 AND order_date > '2023-01-01'): 이 쿼리는 idx_date_customer를 부분적으로 사용할 수 있습니다. order_date > '2023-01-01'로 효율적으로 필터링할 수 있지만, customer_id로 필터링하기 위해 선택된 행을 계속 스캔해야 합니다. 전체 테이블 스캔보다 낫지만 idx_customer_date만큼 효율적이지는 않습니다.
• 쿼리 3 (WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC): 쿼리 2와 유사하게 order_date 필터와 ORDER BY 절은 효율적이지만, customer_id는 여전히 후속 필터링 작업이 필요합니다.
• 쿼리 4 (WHERE order_date > '2023-01-01'): order_date가 가장 왼쪽 컬럼이기 때문에 이 쿼리는 idx_date_customer를 완전히 활용할 수 있습니다. 이것은 매우 빠를 것입니다.
• 쿼리 5 (WHERE status = 'shipped'): 여전히 이 인덱스를 사용할 수 없습니다.

컬럼 순서를 위한 주요 내용

1. 카디널리티: 일반적으로 같음(=) 조건에 자주 사용되는 컬럼, 특히 카디널리티(고유 값 수)가 가장 높은 컬럼을 먼저 배치합니다. 이렇게 하면 인덱스가 검색 공간을 가장 효과적으로 좁힐 수 있습니다. 하지만 이것은 지침일 뿐 엄격한 규칙은 아닙니다.
2. 사용 패턴: 가장 중요한 요소는 쿼리 패턴입니다.
   • col_a로만 쿼리하거나 col_a 와 col_b로 쿼리하는 경우가 많다면 ((col_a, col_b))가 적합합니다.
   • col_b로만 쿼리하는 경우가 많다면 ((col_b, col_a)) (또는 col_b에 대한 별도의 인덱스)가 더 좋습니다.
   • ORDER BY 또는 GROUP BY 절을 고려하십시오. col_a로 필터링한 후 ORDER BY col_b가 일반적이라면 ((col_a, col_b))는 필터와 정렬 모두를 충족하여 비싼 "filesort" 작업을 피할 수 있습니다.
3. 같음 vs. 범위: 같음 조건(=)에 사용되는 컬럼은 일반적으로 범위 조건(<, >, BETWEEN, LIKE 'prefix%')에 사용되는 컬럼보다 먼저 나와야 합니다. WHERE col_a = 'X' AND col_b > 'Y'가 있다면 ((col_a, col_b))가 매우 잘 작동할 것입니다. 인덱스는 col_a = 'X'로 이동한 다음 'Y'에서 col_b의 범위를 효율적으로 스캔할 수 있습니다. 순서가 ((col_b, col_a))였다면, col_b를 필터링하기 전에 인덱스는 훨씬 더 넓은 col_b 값 범위를 스캔해야 합니다.
4. 커버링 인덱스: 쿼리(SELECT, WHERE, ORDER BY, GROUP BY)에 필요한 모든 컬럼이 인덱스에 포함되면 복합 인덱스가 "커버링 인덱스"가 될 수 있습니다. 이는 데이터베이스가 실제 테이블 행에 액세스할 필요가 없음을 의미하며, 쿼리를 더욱 빠르게 만듭니다. 예를 들어, SELECT customer_id, order_date FROM orders WHERE customer_id = 123은 ((customer_id, order_date))로 커버될 수 있습니다.

-- ORDER BY 절에 대한 순서의 중요성을 보여주는 예시
-- 'orders' 테이블에 'customer_id'와 'order_date'가 있다고 가정

-- 인덱스 1: customer_id 먼저, 그 다음 order_date
CREATE INDEX idx_customer_date_order ON orders (customer_id, order_date);

-- 쿼리 1: customer_id로 필터링, order_date로 정렬
EXPLAIN SELECT customer_id, order_date, status
FROM orders
WHERE customer_id = 123
ORDER BY order_date DESC;

-- 이는 WHERE와 ORDER BY 모두에 대해 idx_customer_date_order를 효율적으로 사용할 가능성이 높습니다.
-- 인덱스는 customer_id로 정렬되고, 각 customer_id 내에서는 order_date로 정렬되어,
-- 원하는 순서로 효율적인 스캔을 가능하게 합니다.

-- 인덱스 2: order_date 먼저, 그 다음 customer_id
CREATE INDEX idx_date_customer_order ON orders (order_date, customer_id);

-- 쿼리 2: 위와 동일한 쿼리, 다른 인덱스 구조
EXPLAIN SELECT customer_id, order_date, status
FROM orders
WHERE customer_id = 123
ORDER BY order_date DESC;

-- idx_date_customer_order는 WHERE 절에 대해 customer_id의 선행 열이 아니므로
-- 일부 활용될 수 있지만, 효율적이지는 않을 것입니다.
-- 옵티마이저가 유익하다고 판단하면 ORDER BY에는 여전히 도움이 될 수 있습니다.
-- 그러나 customer_id에 대한 등가 조건이 없고 order_date에 대한 범위 조건만 있다면,
-- idx_date_customer_order가 빛을 발할 것입니다.

-- order_date가 범위이고 customer_id가 등가인 쿼리를 고려
EXPLAIN SELECT customer_id, order_date, status
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
AND customer_id = 456;

-- 이 쿼리의 경우, idx_date_customer_order가 가장 왼쪽 열인 order_date의 범위 조건을 먼저 사용할 수 있으므로 더 효율적입니다.

복합 인덱스 설계의 기술은 애플리케이션의 쿼리 워크로드에 대한 깊은 이해에 있습니다. 이것은 모든 컬럼 또는 모든 컬럼 조합에 대해 맹목적으로 인덱스를 생성하는 것이 아닙니다. 과도한 인덱싱은 쓰기 작업에 자체적인 성능 오버헤드를 가지고 있기 때문입니다. 대신, 가장 중요한 또는 빈번한 쿼리에 대한 인덱스의 유용성을 극대화하면서 전체 인덱스 공간을 최소화하는 순서로 컬럼을 전략적으로 배치하는 것입니다. 가장 왼쪽 접두사 규칙, 컬럼 카디널리티 및 쿼리 유형(같음 vs. 범위, WHERE vs. ORDER BY)을 신중하게 고려함으로써 상당한 성능 향상을 잠금 해제하고 데이터베이스가 과부하 상태에서도 효율적으로 작동하도록 보장할 수 있습니다. 복합 인덱스에서 컬럼 순서는 단순한 세부 사항이 아니라 효과의 초석입니다.