Multi Column Index

Multi Column Index란?

---

데이터베이스에서 성능 최적화는 매우 중요한 요소이다. 특히 대량의 데이터가 저장된 테이블에서 빠르게 데이터를 조회하려면 인덱스(Index)를 활용해야 한다. 그중에서도 Multi-Column Index(다중 열 인덱스)는 데이터베이스에서 두 개 이상의 열(column)을 결합하여 효율적으로 데이터를 검색할 수 있게 해주는 인덱스이며, 이를 통해 복합적인 조건의 검색 속도를 향상시킬 수 있다.

 

 

---

Multi Column Index의 특징

---

Multi Column Index는 두 개 이상의 컬럼을 결합하여 하나의 인덱스를 생성하는 방식이다. 단일 컬럼 인덱스와 달리, 특정 컬럼의 조합으로 이루어진 검색 조건에 대해 최적화된 성능을 제공한다.

 

1. 왼쪽 선행 열 원칙(Leftmost Prefix Rule)

---

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT, -- 주문 ID (기본 키)
    customer_id BIGINT NOT NULL,             -- 고객 ID
    order_date BIGINT NOT NULL,            -- 주문 날짜
    total_amount DECIMAL(10, 2),             -- 주문 총액
	sts VARCHAR(20),                      -- 주문 상태
    hash_email VARCHAR(500),
    INDEX idx_hash_email (hash_email),       -- 해시 인덱스
    INDEX idx_customer_date (customer_id, order_date) -- 복합 인덱스
);

: Multi-Column Index는 정의된 열 순서에 따라 동작한다. 예를 들어 (A, B, C) 순으로 인덱스를 생성했다면

• A만 검색 조건에 포함되면 인덱스를 사용할 수 있다.
• A와 B를 포함한 검색에서는 인덱스를 사용할 수 있다.
• 하지만 B, C만 포함된 검색은 인덱스를 사용할 수 없다.
  ※ 인덱스를 설계할 때 쿼리에서 가장 자주 사용하는 열을 왼쪽에 배치해야  한다.

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '20240101';

가장 자주 사용되는 customer_id를 왼쪽에 배치하고, customer_id와 order_date 컬럼 모두가 사용되는 WHERE 조건절을 통해 조회 성능을 높일 수 있다.

 

SELECT * FROM orders WHERE order_date = '20240101';

이 경우에는 인덱스를 사용하지 못한다.

 

2. 인덱스 크기 관리

---

프로그램 개발 과정에서 필요에 따라 복합 인덱스를 계속 추가로 생성할 수 있다.

예: INDEX idx_customer_date_status (customer_id, order_date, status)

 

그러나 하나의 테이블에 인덱스를 지나치게 많이 생성하면, 이를 관리하기 위한 추가 저장 공간이 필요하고, 데이터 업데이트 시 인덱스 갱신으로 인해 성능 저하가 발생할 수 있다.

• 열의 개수가 많아질수록 크기가 커지고 관리 비용이 증가한다.
  • 인덱스 크기 증가: 디스크 공간과 메모리를 더 많이 사용
  • 쓰기 성능 저하: 데이터 삽입/수정/삭제 시 인덱스를 함께 갱신해야 하므로 성능 저하
• 이러한 이유로 필요한 경우에만 생성해야 한다.
  • 복합 인덱스는 필요한 최소 열만 포함하도록 설계해야 한다.
  • 비슷한 쿼리에 여러 복합 인덱스를 생성하면 인덱스 간 충돌로 성능이 저하될 수 있다.

 

장단점

---

장점

1. 복합 조건에 대한 최적화
   • 여러 컬럼을 결합한 검색 조건을 처리할 때 단일 인덱스보다 훨씬 빠르게 데이터를 조회할 수 있다.
2. 디스크 I/O 감소
   • 데이터 페이지에 접근하는 횟수가 줄어들어 디스크 I/O 비용이 절감된다.
3. 공간 절약
   • 두 개의 단일 인덱스를 각각 생성하는 것보다 Multi Column Index 하나를 생성하는 것이 저장 공간을 절약할 수 있다.
4. 데이터 정렬 및 범위 검색 지원
   • Multi Column Index는 데이터 정렬을 포함한 작업에 유리하다. 선두 컬럼을 기준으로 정렬된 상태로 저장되므로, 범위 검색이나 정렬 작업에 최적화된 성능을 제공한다.

 

단점

1. 선두 컬럼 의존성
   • Multi Column Index는 선두 컬럼에 의존하므로, 선두 컬럼이 조건에 포함되지 않으면 인덱스가 무용지물이 될 수 있다.
2. 인덱스 크기 증가
   • 결합된 컬럼의 데이터를 모두 포함하기 때문에, 단일 인덱스보다 크기가 클 수 있다.
   • 데이터가 많아질수록 관리 비용도 늘어난다.
3. 삽입/수정/삭제 성능 저하
   : 인덱스가 추가될수록 테이블에 데이터를 삽입, 수정, 삭제할 때 인덱스를 유지 관리해야 하므로 성능이 저하될 수 있다.
   • 특히 데이터가 자주 변동되는 테이블에서는 신중하게 설계해야 한다.
4. 과도한 사용 위험
   
   • 모든 컬럼 조합에 대해 Multi Column Index를 생성하면 테이블에 과도환 인덱스가 생성될 수 있다.
   • 이는 공간 낭비와 관리 복잡성 증가로 이어진다.

 

 

---

사용 사례

---

• 여러 열을 조합한 조건으로 자주 검색하는 경우
• 특정 순서로 데이터를 정렬하는 경우

 

예시: 전자상거래 주문 관리 시스템

전자상거래 플랫폼에서는 사용자의 주문 내역을 조회할 일이 매우 많다.

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2),
    order_status VARCHAR(20),
    INDEX idx_customer_date (customer_id, order_date)
);

 

사용 시나리오

: 고객별 주문 내역을 날짜 순으로 조회하는 기능

SELECT * 
FROM orders
WHERE customer_id = 12345
ORDER BY order_date DESC;

• customer_id와 order_date를 결합한 Multi-Column Index를 사용하여 성능을 최적화할 수 있다.
• customer_id를 기준으로 데이터를 빠르게 검색하고, 정렬된 상태로 변환하여 추가적인 정렬 작업을 줄일 수 있다.
• 특히 고객 수와 주문 수가 많을 때, 디스크 I/O를 최소화하여 성능이 크게 향상된다.

 

---

사용 예시

---

데이터 삽입

SET @@cte_max_recursion_depth = 15000;

INSERT INTO orders (customer_id, order_date)
WITH RECURSIVE my_cte AS (
	SELECT 1 AS n, CAST(1 AS DOUBLE) AS abc, CAST(2 AS DOUBLE) AS se
    UNION ALL
    SELECT 1+n, CAST(1+n AS DOUBLE), CAST(2+n AS DOUBLE) FROM my_cte WHERE n<10000
)
SELECT abc, se FROM my_cte;

orders 테이블에 테스트용으로 10,000개의 데이터를 삽입한다.

 

 

인덱스 사용

---

복합 인덱스(idx_customer_date)를 사용하는 경우

EXPLAIN SELECT hash_email, customer_id, order_date FROM orders WHERE customer_id = 1 AND order_date = 3;

 

SELECT 쿼리에 대한 EXPLAIN 결과는 다음과 같다.

• id: 1
  • 단일 SELECT로 구성된 간단한 쿼리임을 나타낸다.
• select_type: SIMPLE
  • 하위 쿼리나 조인 없는 단일 테이블 조회임을 의미한다.
• type: ref
  • ref는 사용된 인덱스(idx_customer_date)가 동등 비교(=) 조건으로 작동했음을 나타낸다.
  • 인덱스를 활용한 조건부 조회로, 효율적인 방식임을 나타낸다.
• possible_keys: idx_customer_date
  • 실행 가능한 인덱스 목록 중 idx_customer_dat가 사용될 수 있는 유일한 인덱스임을 나타낸다.
• key: idx_customer_date
  • 실제로 선택된 인덱스는 idx_customer_date이다.
• key_len: 16
  • 인덱스의 길이를 나타낸다.
  • customer_id와 order_date 각각의 데이터타입에 따라 계산된 값을 나타낸다.
  • BIGINT(8바이트) * 2 = 16바이트
• ref: "const,const"
  • 쿼리의 조건이 customer_id = 1 및 order_date = 3으로 명시되었기 때문에 두 조건 모두 상수 값으로 인덱스가 참조되었음을 나타낸다.
• rows: 1
  • 조건을 만족하는 레코드가 1개일 것을 의미한다.
• filtered: 100
  • 필터링 비율이 100%이므로 인덱스를 활용한 결과가 추가적인 필터링 없이 바로 반환되었음을 의미한다.
• Extra: null
  • 쿼리 실행 시 추가적인 작업이 없었음을 의미한다.

즉, type이 ref이고 rows가 1이므로, 데이터베이스는 불필요한 스캔 없이 정확히 조건을 만족하는 레코드를 검색하였다.

Extra에 추가적인 작업이 없고, filteredrk 100%이므로 이 쿼리는 매우 최적화된 상태에서 실행된다.

 

※ SQL 쿼리의 조건 순서와 인덱스 사용 방식
SQL 쿼리의 조건 순서가 실행 계획의  인덱스 사용 방식에는 영향을 주지 않는다.

복합 인덱스와 조건 순서
다음과 같은 복합 인덱스가 있다고 가정하자.
```
INDEX idx_customer_date (customer_id, order_date)
```
위와 같은 복합 인덱스에서 조건절에 두 컬럼(customer_id, order_date)의 순서를 반대로 작성하더라도 동일한 복합 인덱스(idx_customer_date)가 사용된다.

예시 쿼리: 조건 순서 변경
```
EXPLAIN SELECT hash_email, customer_id, order_date
FROM orders
WHERE customer_id = 1 AND order_date = 3;

EXPLAIN SELECT hash_email, customer_id, order_date
FROM orders
WHERE order_date = 3 AND customer_id = 1;
```
위 두 쿼리는 작성된 조건의 순서가 다르지만, 실행 계획(EXPLAIN 결과)은 동일하다. 두 경우 모두 복합 인덱스 idx_customer_date가 사용되며, 쿼리 성능에도 차이가 없다.

왜 동일한 실행 계획이 나오는가?
이것은 MySQL 쿼리 옵티마이저(Query Optimizer) 덕분이다.
MySQL의 쿼리 옵티마이저는 작성된 쿼리의 조건 순서와 관계없이 다음과 같은 작업을 수행한다.
1. 조건 최적화: 조건절(WHERE)을 분석하고, 가장 효율적으로 실행할 수 있는 순서를 결정한다.
2. 적합한 인덱스 선택: 쿼리에서 사용 가능한 인덱스를 확인한 후, 가장 효율적인 인덱스를 선택해 실행 계획을 구성한다.

 

 

기본 키(order_id)를 함께 조회하는 경우 (두 번째 컬럼에 대한 단일 조건)

EXPLAIN SELECT order_id, customer_id, order_date FROM orders WHERE order_date = 3;

 

SELECT 쿼리에 대한 EXPLAIN 결과는 다음과 같다.

• type: index
  • type이 index인 경우, Index Scan이 수행된다.
  • 이는 전체 인덱스를 순차적으로 스캔하여 데이터를 찾는 작업을 의미하며, 일반적으로 Full Table Scan(ALL) 보다 효율적이다.
  • 하지만 완벽히 최적화된 검색은 아니며, 필요한 데이터만 찾는 것이 아니라 인덱스를 끝가지 탐색해야 할 수도 있다.
• possible_keys: idx_customer_date
• key: idx_customer_date
  • 실제로 선택된 인덱스는 복합 인덱스인 idx_customer_date(customer_id, order_date)이다.
• key_len: 16
• rows: 9834
  • znjfldptj 9,834개의 행을 스캔해야 한다고 예측된다.
  • 이는 idx_customer_date가 복합 인덱스임에도 불구하고 order_date가 단독 조건으로 효율적으로 동작하지 않음을 의미한다.
• filtered: 10
  • 조건에 부합하는 결과가 전체 데이터의 약 10%에 해당한다.
  • 즉, 스캔된 데이터 중 약 10%만 실제로 유효하다.
• Extra: Using where; Using index
  • Using where: WHERE order_date = 3 조건이 적용되었음을 나타낸다.
  • Using index: 필요한 데이터를 인덱스에서 직접 가져왔으며, 테이블 데이터 페이지를 읽지 않았음을 의미한다. 즉, 커버링 인덱스가 사용되었다.

Multi-Column Index에서 첫 번째 컬럼 없이 두 번째 컬럼으로 조건을 사용하더라도, 커버링 인덱스로 동작할 경우 인덱스가 사용될 수 있다. 하지만, 이 방식은 최적화된 접근이 아니며, Full Index Scan처럼 동작할 가능성이 크다.(실제로 Full Index Scan과 결과가 동일)

order_date 조건을 자주 사용한다면 단일 인덱스를 추가하거나, 복합 인덱스를 설계 변경하여 성능을 최적화하는 것이 바람직하다.

 

※ 복합 인덱스의 두 번째 컬럼만 사용했는데도 인덱스가 사용된 이유
복합 인덱스(Multi-Column Index)에서 첫 번째 컬럼 없이 두 번째 컬럼만 조건으로 사용하면, 일반적으로 인덱스가 제대로 활용되지 않는 것으로 알려져 있다. 그러나 특정 경우에는 복합 인덱스가 사용된다.
이는 커버링 인덱스(covering index) 때문이다.

커버링 인덱스란?
커버링 인덱스란 쿼리가 요청한 모든 컬럼이 인덱스 자체에 포함되어 있어, 테이블 데이터를 참조하지 않고 인덱스만으로 결과를 반환할 수 있는 경우를 말한다.

SELECT절에서 요청된 order_id, customer_id, order_date 컬럼은 모두 복합 인덱스 idx_customer_date (customer_id, order_date)에 포함되어 있다. 따라서, 커버링 인덱스로 동작하며 테이블 데이터를 읽지 않고 인덱스만으로 결과를 반환할 수 있다.

왜 복합 인덱스가 사용되었는가?
MySQL은 복합 인덱스에서 첫 번째 컬럼이 조건에 포함되지 않더라도 Index Scan 방식으로 전체 인덱스를 탐색할 수 있다.

조건 설명
- order_date는 복합 인덱스의 두 번째 컬럼이다.
- 첫 번째 컬럼(customer_id)을 무시하고도 인덱스를 순차적으로 스캔하여 검색을 진행하였다.

주의점
- 이런 경우는 Index Scan으로 처리되며, 특정 범위 조건이나 정렬이 없는 경우 Full Index Scan처럼 동작할 수 있다.
- 최적화된 검색 방식(ref)에 비해 성능이 떨어질 가능성이 있다.

최적화 방법
현재와 같은 상황에서 성능을 더욱 최적화하려면 다음과 같은 방법을 고려할 수 있다.

1. 단일 컬럼 인덱스 추가
order_date에 대한 단일 컬럼 인덱스를 추가하면, 조건 처리 성능을 크게 개선할 수 있다.
```
CREATE INDEX idx_order_date ON orders (order_date);
```

2. 복합 인덱스 설계 변경
쿼리 조건에서 order_date가 더 자주 사용된다면, 복합 인덱스를 order_date를 선두로 재설계하는 것이 좋다.
```
CREATE INDEX idx_date_customer ON orders (order_date, customer_id);
```

3. 쿼리 조건 변경
customer_id 조건을 추가하여 기존 복합 인덱스(idx_customer_date)를 완전하게 활용할 수 있도록 쿼리를 수정한다.

 

 

인덱스 사용이 안 되는 경우

---

복합 인덱스의 두 번째 컬럼만 조건 절에서 사용하는 경우

EXPLAIN SELECT hash_email, customer_id, order_date FROM orders WHERE order_date=3;

• type: ALL
  • Full Table Scan으로 인덱스를 사용하지 않고 테이블의 모든 행을 읽어야 함을 나타낸다.
  • 쿼리 성능 최적화가 필요하다.
• possible_keys: null
  • 사용 가능한 인덱스가 없음을 의미한다.
• rows: 9834
  • 쿼리를 실행하기 위해 약 9,834개의 행을 스캔해야 한다.
  • 이는 테이블의 모든 데이터를 읽는 작업이 필요함을 보여준다.
• filtered: 10
• Extra: Using where
  • WHERE 조건을 사용해 결과를 필터링했음을 나타낸다.
  • 인덱스를 활용하지 못했기 때문에 WHERE 조건이 테이블의 모든 행에 적용되었다.

해결 방안

1. 단일 컬럼 인덱스 생성
   • order_date 컬럼에 단일 컬럼 인덱스를 생성하면, WHERE order_date = 3 조건에서 테이블 스캔을 피할 수 있다.
2. 복합 인덱스 활용
   • 기존의 idx_customer_date 복합 인덱스를 활용하거나, 쿼리를 변경하여 인덱스를 활용하도록 유도한다.
   • 예를 들어, customer_id도 함께 조건에 포함시킬 경우 기존 복합 인덱스를 사용할 수 있다.
3. 쿼리 리팩토링
   • 현재 조건으로 결과가 많아 filtered 비율이 낮은 경우, 조건을 더욱 세분화하거나 필요한 데이터만 검색하는 방식으로 쿼리를 리팩토링할 수 있다.