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