대량의 조건이 WHERE IN 절로 들어온다면?

2000개 WHERE .. IN 절을 처리하는 과정에서 옵티마이저가 Index를 타지 않고 Table Full Scan을 하는 것을 확인했습니다. 이를 방지하기 위해 여러 고민을 하던 와중 FORCE 인덱스를 발견했는데 이에 대해 소개하고 어떤 상황에 쓰면 좋을지 고민해보는 시간을 가져 보겠습니다. 

 

또한 결론적으로 말씀드리면 결국 이 방법이 아닌 다른 방법을 택했는데 그거에 관해서도 소개 드리겠습니다. 또한 모든 설명은 MySQL 기준입니다.

FORCE INDEX?

FORCE 인덱스는 Query Hint 중 하나로 특정 인덱스를 강제로 사용하도록 합니다. MySQL에서는 적절한 Index를 사용하지 않으면 성능이 저하될 수 있기 때문에 신중하게 선택하여야 합니다. 

FORCE INDEX는 다음과 같이 사용할 수 있습니다.

SELECT * FROM MEMBER FORCE INDEX(IX_MEMBER_1) WHERE email like 'ilgolc%'

만약 잘못된 인덱스를 탄다고 하면 FORCE INDEX를 이용해서 올바른 인덱스를 타게 하여 성능을 향상시킬 수 있고 또는 위 내용 처럼 IN절에 데이터가 너무 많아서 Index를 안탄다면 FORCE INDEX로 개선이 가능합니다. 다만 모든 경우에 통하는 문제는 아니니 고민하여야 합니다. 

 

그렇다면 오히려 성능이 떨어지는 경우는 어떤 경우 일까요?

create table member
(
    member_id          bigint auto_increment
        primary key,
    created_at         datetime     null,
    last_modified_date datetime     null,
    birth              date         null,
    deleted            tinyint(1)   not null,
    email              varchar(255) not null,
    name               varchar(20)  not null,
    nickname           varchar(20)  not null,
    password           varchar(200) not null,
    phone_number       varchar(20)  not null,
    profile_image_url  varchar(100) null,
    role_type          varchar(255) not null
);

create index i_email
    on member (email);

create index i_nickname
    on member (nickname);

해당 테이블에서 다음과 같은 쿼리를 짜본다고 가정해봅시다.

SELECT * FROM member FORCE INDEX(i_email, i_nickname) WHERE id IN (123814, ..., 31232);

말도 안되는 쿼리라고 생각하지만 쿼리가 복잡해지거나 인덱스를 타야하는 상황에 잘못된 선택을 하게 된다면 분명 비슷한 상황이 나올 수 있습니다. 

 

위 쿼리를 실행계획으로 보면 type ALL로 잘못된 Index 힌트로 인하여 올바른 인덱스를 타지 않아 Full Scan을 하게 됩니다.  만약 실제 PRD 환경이었으면 정말 끔찍한 일입니다.

 

또한 적은 데이터가 들어오더라도 이미 인덱스가 강제되기 때문에 옵티마이저가 더 빠른 방법으로 쿼리를 실행할 수 있음에도 불구하고 인덱스 사용이 강제되기 때문에 적절한 해결방법이 아닐 수 있습니다.

 

그럼 어떤 방법을 선택했을까요? 

쿼리 분할 전략

쿼리를 쪼개서 분할하여 날리면 이 문제를 해결할 수 있습니다. Application에서 간단하게 List를 index기준으로 subList로 나누어 처리하여 성능을 개선하는 전략입니다. 

이 경우 RTT가 길어질 수록 성능이 느려질 수 있습니다. 쿼리 테스트 결과 RTT 덕분에 사용자 경험 측면에서 느껴질만한 속도는 아니었고 오히려 강제로 인덱스를 태워 성능이 더 느려졌기 때문에  일종의 Trade off가 필요했습니다.

 

예를 들어 2000개의 쿼리가 발생하면 100개씩 나누어 20번만  날려 인덱스를 타는 범위 만큼만 보내는 것입니다. 이러한 분할 쿼리를 날리는 방식은 운영환경을 고려하여 실행 계획을 확인해가며 점차 줄여나가며 확인하는 것을 추천드립니다.

 

실제로 성능 테스트를 해봤을 때 약 7초 이상이 걸리던 쿼리를 잘게 쪼개 날리는 방식으로 DB 커넥션을 좀 쓰더라도 2초까지 성능을 개선하였습니다. 

마무리

옵티마이저가 쿼리를 최적화하여 실행한다고만 알고 있었고 쓸일이 없었지만 이번 프로젝트를 하게 되면서 옵티마이저에 중요성을 알게 되었고 실행계획을 꼼꼼히 체크해야겠다고 한 번더 다짐하게 된 계기가 되었습니다. 

 

또한 위 방법 중 꼭 Force Index가 안좋은 것 만은 아닐 수 있습니다. 직접 테스트를 해보고 적절히 방법을 고려하는 Trade off가 필요할 것입니다.

 

더 좋은 방법이 있으면 댓글로 남겨주시면 참고하겠습니다. 

 

마침.