[MySQL] 복합 인덱스의 성능을 위한 순서 최적화

 

들어가기 전에

이번에 프로젝트를 하면서 내가 친구로 추가한 친구들의 목록을 확인하기 위한 테이블을 설계해야 했다.

 

처음 설계할 때는 친구 테이블에 uid가 있었다. 멘토님이 바로 "uid는 왜 사용하는 건가요?"라고 질문을 주셨고, 생각해 보니 전혀 필요가 없었는데 무조건적으로 uid를 가지고 있어야 한다는 생각에 아무 고민없이 생성한 것 같다고 답변한 후에 멘토님의 조언과 더불어 구현하려는 기능을 생각했을 때 uid를 사용하지 않을 거라는 판단이 서서 uid 컬럼을 제거하고 남은 두 컬럼을 복합키로 설정하기로 했다.

 

회사에서도 잠시 테이블 설계를 맡았을 때 복합키를 사용하라는 조언을 받은 적이 있었는데 아쉽게도 경험을 하지 못했었고 개인적으로 했던 프로젝트들에서도 마찬가지라 정리하는 겸 글을 작성하고자 한다.

 

 

Composite Key(복합키)

복합키는 테이블의 각 레코드를 고유하게 식별할 수 있도록 하기 위해 사용하는 두 개 이상의 컬럼들의 조합이다. 복합키에 사용되는 컬럼은 같은 데이터 타입이 아니어도 상관없다. 

 

복합키 생성 예제 쿼리문을 보자. 참고로 복합키가 생성될 때 클러스터드 인덱스로 복합 인덱스가 생성된다.

-- 출처 : https://www.javatpoint.com/mysql-composite-key
CREATE TABLE Product (  
    Prod_ID int NOT NULL,   
    Name varchar(45),   
    Manufacturer varchar(45),  
    PRIMARY KEY(Name, Manufacturer)  
);

 

 

만약 아래의 INSERT문을 두 번 반복한다면 어떻게 될까?

-- 출처 : https://www.javatpoint.com/mysql-composite-key
INSERT INTO Product (prod_id, name, manufacturer)  
VALUES (101, 'Soap', 'Hamam'); -- 2번 반복한다면?

Error Code: 1062. Duplicate entry 'Soap-Hamam' for key 'product.PRIMARY'

Soap-Hamam 조합의 키가 중복된다는 에러 코드가 반환된다.

 

 

그렇다면 Name은 여전히 Soap이지만 Manufacturer이 다른 값을 가진 INSERT문은 어떨까?

-- 출처 : https://www.javatpoint.com/mysql-composite-key
INSERT INTO Product (prod_id, name, manufacturer)  
VALUES (101, 'Soap', 'LUX');

예상 가능하듯이 데이터가 성공적으로 생성된다. 두 개의 컬럼이 하나의 키로 작용하기 때문에 첫 번째 컬럼의 값이 같다면 두 번째 컬럼의 값은 당연히 달라야 한다.

 

 

복합 인덱스

앞서 언급한대로 복합키가 생성되면서 복합 인덱스가 자동으로 생성된다. 그리고 이 복합 인덱스에서 가장 중요한 것은 컬럼의 순서이다. 컬럼 순서에 따라 성능 개선에 영향을 주기 때문이다.

 

아래의 B-Tree 구조의 Root 페이지와 Leaf 페이지 구조를 생각하면서 다음 예제들을 살펴보자. Root 페이지를 통해 원하는 데이터가 어느 곳에 있는지 방향을 특정지을 수 있고 그 방향대로 가다보면 Leaf 페이지에서 데이터를 찾을 수 있다.

 

출처 : https://www.qwertee.io/blog/postgresql-b-tree-index-explained-part-1/

 

 

주문 테이블에서 ORD_YMD와 CUS_ID 컬럼의 순서로 복합키로 생성했을 때 데이터를 어떻게 찾게 될까?

출처 : SQL BOOSTER [그림 6-3.1-1] 에서 필요한 부분만 추출

리프 블록과 데이터 블록의 앞뒤로 데이터가 더 있다고 가정했을 때, ORD_YMD의 조회 시작 구간을 '20230301'로 정하고 CUS_ID가 'CUS_0075'인 데이터를 찾는다고 생각해 보자. 'CUS_0075'를 찾는 과정 중에서 '20230301'에 해당하는 레코드들을 모두 살피다 보니 불필요한 레코드들을 계속해서 읽게 된다. 위의 그림에서 데이터 블록을 확인하면 정작 찾아야 했던 레코드는 2건인데 리프 블록에서 이미 6건의 레코드를 읽느라 시간을 낭비하게 되는 것이다.

 

 

그렇다면 반대의 경우로 CUS_ID와 ORD_YM 컬럼의 순서로 복합키를 생성한다면 어떨까?

출처 : SQL BOOSTER [그림 6-3.1-2] 에서 필요한 부분만 추출

확실히 찾고자 하는 데이터가 잘 모여 있는 것을 확인할 수 있다. 그래서 WHERE 절에서 같다(=) 조건을 사용하는 컬럼을 선두에 사용하게 되면 훨씬 성능을 향상시킬 수 있다. 다만 주의할 것은, 모든 경우에 이 공식이 통한다는 것이 아니기 때문에 실행계획을 꾸준히 잘 살펴보면서 어떻게 인덱스를 생성해야 하는지 고민해야 한다.

 

그리고 또 고려해야 하는 것은 첫 번째 순서로 선정된 컬럼은 무조건 조회 조건에 포함되어야 한다는 것이다. 만약 (A, B, C) 조합과 순서로 복합 인덱스가 생성되었다면 가장 피해야 할 조회 조건 조합은 무엇일까? 바로 (B, C)이다. 그러니까 최소한 첫 번째 순서로 지정된 A 컬럼은 항상 포함되어있어야 원하는 인덱스 스캔이 가능한 것이다.

 

 

그외에도 여러 가지 고려 사항이 있다.

• 선택률이 높은 컬럼을 선두 컬럼으로 선정한다.
• 인덱스 키가 INT 타입일 때 탐색 속도가 더 빠르기 때문에 INT 데이터 타입을 우선 선정한다.

복합 인덱스 관련해서 지금까지 설명한 내용을 정리해 보면 아래와 같다.
- 같다(=) 조건이 사용된 컬럼이 복합 인덱스의 앞부분에 위치해야 한다.
- 인덱스를 만들 때, 해당 테이블에 대한 SQL 전체를 검토하도록 한다.
(모든 SQL을 검토할 수 없다면, 최대한 많은 SQL을 검토한다.)
- 조건에 사용된 모든 컬럼을 무조건 복합 인덱스에 추가해서는 안 된다.
(성능에 도움 되는 조건 컬럼만 선별해서 복합 인덱스를 구현하도록 한다.

출처 : SQL BOOSTER

 

참고로 단일 인덱스와는 다르게 복합 인덱스에서는 ORDER BY절에 의한 순서가 최적화되지 않다보니까 여기에 대해서도 고민을 하고 사용해야 한다.

출처 : http://dev.cs.ovgu.de/db/sybase9/help/dbugen9/00000433.htm

 

좋은 예

-- 출처 : http://dev.cs.ovgu.de/db/sybase9/help/dbugen9/00000433.htm
-- 인덱스를 생성한다.
CREATE INDEX idx_example
ON table1 (col1 ASC, col2 DESC, col3 ASC);

-- 최적화 쿼리 1
SELECT col1, col2, col3 from table1
ORDER BY col1 ASC, col2 DESC, col3 ASC;

-- 최적화 쿼리 2
SELECT col1, col2, col3 from table1
ORDER BY col1 DESC, col2 ASC, col3 DESC;

 

나쁜 예

-- 출처 : http://dev.cs.ovgu.de/db/sybase9/help/dbugen9/00000433.htm
SELECT col1, col2, col3 from table1
ORDER BY col1 ASC, col2 ASC, col3 ASC;

ASC, ASC 순의 순서였다면 반드시 DESC를 사용해줘야 최적화할 수 있다.

 

 

 

 

참고

• SQL BOOSTER
• MySQL Composite Key
• 성능 향상을 위한 SQL서버 인덱스 생성 전략
• Composite Indexes
• 카디널리티와 복합키 순서 그리고 PK Index