복합 인덱스는 두 개 이상의 칼럼을 결합하여 생성된 인덱스이다. 이는 여러 칼럼을 함께 사용하는 쿼리의 검색 성능을 개선하기 위해 설계되었다.
하나의 칼럼으로 인덱스를 만들 때보다 더 적은 데이터 분포를 보여서 탐색할 데이터 수가 줄어든다.
AND 조건으로 검색되는 경우 성능에 중요한 역할을 한다.
두 개 이상의 조건이 OR로 조회되는 경우는 결합 인덱스를 만들면 안된다.
SELECT 카드번호, 결제금액
FROM 거래명세서
WHERE 카드번호 = '1234'
AND 거래 날짜 BETWEEN '20240121'
AND '20240122'
예를 들어 다음과 같이 카드사의 DB에서 특정 카드번호와 결제 금액을 가져오는 SELECT 문이 있다고 해보자.
이럴 때는 카드번호와 거래 날짜를 복합 인덱스로 구성을 하면 거래 명세서 테이블은 카드번호로 정렬이 한번 되고, 또 그 안에서 거래날짜별로 정렬이 될 것이다.
1. 단일 인덱스 2개 사용시:
2. 복합 인덱스 사용시:
결론적으로, 주어진 예제에서는 카드번호와 거래날짜를 함께 조건으로 사용하는 쿼리가 빈번하게 생성된다면, 복합 인덱스를 사용하는 것이 보다 효율적일 수 있습니다. 하지만 쿼리의 패턴, 데이터의 분포 및 양, 그리고 인덱스의 크기 등의 요소를 종합적으로 고려하여 인덱스 전략을 결정해야 한다.
중요한 점은, 디스크 I/O를 가장 적게 발생시키는 규칙을 가지고 순서를 구성하면 된다.
1. AND 조건 연산자: AND 연산자로 연결되어 자주 사용되며, 두 개 이상의 칼럼 결합 시 분포도가 향상되는 칼럼들.
2. 조인 연결고리: 다른 테이블과 조인 연결고리로 자주 사용되는 칼럼들
3. 정렬 우선순위: ORDER BY 절에서 자주 사용되는 칼럼들
4. 키 칼럼 조건: 하나 이상의 키 칼럼 조건으로 동일 테이블의 칼럼들이 자주 조회될 때
칼럼 순서 결정 중요성:
결합 인덱스의 칼럼 순서는 성능에 크게 영향을 미친다.
첫 번째 칼럼이 WHERE 절에 없을 경우 결합 인덱스는 활용되지 않는다.
인덱스는 첫번째 칼럼을 기준으로 주요 정렬을 하며, 그 다음 칼럼은 두 번째 정렬 기준으로 사용한다. 따라서 첫 번째 칼럼에 대한 조건 없이 두번째 칼럼만을 대상으로 한 검색에서는 복합 인덱스가 효율적으로 사용되지 못한다.
테이블 생성
먼저, 간단한 user 테이블과 orders 테이블을 만들어 보겠습니다.
CREATE TABLE users(
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
signup_date DATE
);
CREATE TABLE orders(
order_id INT PRIMARY KEY,
user_id INT,
product_name VARCHAR(100),
order_date DATE,
FOREIGN KEY (user_id) REFERENCES users(id)
);
사용자의 이메일과 가입 날짜를 기준으로 특정 기간 가입한 사용자를 조회하고 싶다면:
select * from users WHERE email='example@gmail.com' AND signup_date BETWEEN '2024-01-01' AND '2024-01-22';
여기서 email과 signup_date를 복합 인덱스로 설정하면 좋다.
주문 내역에서 사용자의 이름과 함께 제품 정보를 가져오고 싶다면:
SELECT u.name, o.product_name
FROM users u
JOIN orders o ON u.id = o.user_id;
이 경우, user_id가 orders 테이블에 대한 중요한 복합 인덱스 후보가 될 수 있다.
가장 최근의 주문을 사용자 이메일 순서대로 정렬하여 가져오고 싶다면:
SELECT u.email, o.product_name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY o.order_date DESC, u.email ASC;
이 경우 order_date와 email을 복합 인덱스로 설정하면 좋다.
이렇게 특정 조건과 정렬, 조인 등을 통해 조회할 때 자주 사용되는 칼럼들을 복합 인덱스로 설정하면 조회 성능을 향상시킬 수 있다.
CREATE INDEX composite_idx ON emp (ename,gender);
이렇게 결합인덱스를 구성했다고 가정하자.
SELECT * FROM emp WHERRE ename LIKE 'SMI%' AND gender = 'M';
여기서 ename이 결합 인덱스의 첫번째 칼럼이고, gender가 두 번째 칼럼이라고 가정하자. 범위 연산자를 첫번째 칼럼에 사용하면, 해당 범위 내의 모든 값을 탐색해야 한다. 그 결과, 인덱스의 첫 번째 칼럼으로만 필터링을 한 경우보다 더 많은 레코드가 반환된다. 물론 gender로 추가 필터링을 수행하겠지만, 그 전 단계에서 불필요하게 많은 레코드를 탐색해야 하기 때문에 효율성이 떨어진다.
SELECT * FROM emp WHERE dept = 'HR';
만약 ename과 gender 후에 dept 컬럼이 복합 인덱스의 세 번째 칼럼이라고 가정하면, 인덱스의 첫 번째와 두번째 칼럼을 무시하고, 바로 세 번째 칼럼을 찾아야 한다. B-Tree 구조의 인덱스에서는 중간 칼럼만을 직접 검색하는 것은 인덱스의 전체 구조를 효율적으로 활용하지 못하는 것이기에 성능이 떨이진다.
이러한 원리로, 복합 인덱스를 설계할 때 어떤 칼럼을 먼저 위치시킬지, 어떤 쿼리 패턴을 주로 사용하는지를 잘 고려해야 한다.