MySQL 복합키 세팅 관련 정보

최근에 복합키를 생성하려고 했는데, 더 잘 세팅하는 법이 궁금하여 찾은 내용을 정리해둔다,

 

[잘 세팅하는 법]

1. 복합키 생성 시, 인덱스는 좌측에 있는 것을 최우선으로 움직임
> 중복도가 낮은(카디널리티가 높은) 컬럼을 왼쪽에 두는 게 좋음
> where 절 작성 시, 복합키의 왼쪽에 있는 걸 먼저 조건문에 써야함

2. 복합키의 우측에 있는 인덱스들을 단일 쿼리 조건에 사용할 경우, 풀스캔이 발생함
>> 단일 조건에 쓰는 컬럼이 있다면 키(인덱스)로 생성해야함

[테스트 내용]

회사 개발환경 테이블인 book.base 대상으로 아래 SQL을 실행하며 테스트해보았다.

CREATE INDEX idx_chkvoice_freecnt ON book.book_base (chkvoice, free_cnt);

>> 복합 인덱스 생성 (해당 인덱스는 단일 키로도 생성되어 있지 않은 상태였음)

explain select * from book.book_base where chkvoice = 'y';

 

>> type : ref로 노출 (복합키 왼쪽 측을 사용하기 때문에)

explain select * from book.book_base where free_cnt = 0;

 

>> type : ALL로 노출 (복합키가 있다하여도, 해당 컬럼은 우측에 위치하기 때문에 사용 못 함)

explain analyze select * from book.book_base where chkvoice = 'y' and free_cnt = 0;

 

>> 아래 쿼리와 속도 차이가 눈에 보이진 않았으나,

공식문서에서는 복합키 왼측에 있는 걸 where 절 첫 조건으로 쓰길 권고함

explain analyze select * from book.book_base where free_cnt = 0 and chkvoice = 'y';

 

>> 바로 위 쿼리와 대조하기 위해 썼었음

DROP INDEX idx_chkvoice_freecnt ON book.book_base;

** 테스트 후, 위에서 생성한 복합 인덱스 다시 삭제하였음

explain select * from book.book_base where chkvoice = 'y';

 

>> 다시 실행해보니 type이 ALL로 노출됨

[참고문서]
https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html