Mysql 인덱스 컨디션 푸시다운

오늘은 회사에서 계속해서 올라오던 slow query에 대해 성능 최적화를 수행한 내용을 바탕으로 해당 포스팅을 작성한다.

 

soft delete를 사용함에 있어서 쿼리를 주의 깊게 작성해야 한다는 사실을 알고있었지만 이번 기회를 계기로 좀 더 깨달았던 것 같다.

문제의 구성은 이러했다.

 

원래의 문제가 됐던 쿼리

select count(*) from tables where 조건1 = ? and user_id = ? and deleted_at is null

 

유저별로 카운팅을 하지만 데이터에 맞는 카운팅 개수가 4천개를 넘어서는 순간부터 점점 응답이 느려져 4초 이상으로 가서 alert 채널에 공유가 되었다.

 

일단 더미데이터를 넣고 비슷하게 구성해보았다.

create table test_table
(
    id           bigint auto_increment comment '아이디',
    created_at   timestamp null comment '생성일자',
    updated_at   timestamp null comment '수정일자',
    deleted_at   timestamp null comment '삭제일',
    used  tinyint(1)       null comment '사용여부',
    user_id      bigint not null comment '유저아이디',
    constraint test_table_pk
        primary key (id)
);

create index idx_user_id_used
    on test_table (user_id, used);

explain select count(*) from test_table where user_id = 1 and used is null and deleted_at is null;
explain select count(*) from test_table where user_id = 1 and used is null;

 

아래 explain 쿼리 두개를 비교해볼건데

위에서 문제가 됐던 쿼리의 실행계획을 살펴보면 Extra 열에 Using index condition; Using where

이라고 표시되는데 여기서의 Using index condition이 이번 포스팅의 핵심 주제이다.

그럼 얘는 무엇인가?

 

Index Condition Pushdown

공식문서

MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.6 Index Condition Pushdown Optimization

 

공식문서에 설명이 잘 되어있지만 내가 이해한바로 정리를 좀 해보면

결국에 인덱스에 포함되지 않는 조건이 들어가있다고 해서 인덱스를 안타도록 쿼리를 수행하면 굉장히 비효율적으로 동작하여 조회 비용이 많이 비싸지기 때문에 먼저 인덱스를 탄 데이터들을 조회하고 여기서 불필요한 디스크 I/O를 줄인 이후에 나머지 Using where 라고 쓰여있듯, 이제부터 나머지 조건을 사용해서 데이터를 비교하여 결과를 반환하는 방식을 수행한다.

 

이렇게 많은 비용을 줄여볼 수 있는데, 문제는 4~5천건을 한번에 쿼리하는것에 있어서는 조회비용이 상당히 비싸다.

지금 내가 만든건 대략 3만건정도의 데이터를 넣고난 이후에 테스트를 했지만, 실제 운영에서 조회하는 테이블은 지금도 꾸준히 쌓이고 있지만, 개선 당시에 대략 5억 6천만건 정도가 있었다!!!!

 

그래서 사실 필요없는 soft_delete 컬럼인데, 전체 테이블에 공통으로 JPA 사용하면 많이들 사용하는 BaseEntity

여기엔 id와 audit 관련 컬럼들을 모아놓고 당연하게 사용했다.

그렇기때문에 전역적으로 `@Where` 이나 `@SQLRestriction` 을 사용하여 deleted_at is null 을 넣어준게 원인이었다.

 

그래서 개선하고 났을 때 조회되는 쿼리 계획은 아래와 같다.

이렇게 되니 인덱스 내에서만 데이터를 갖고 놀고 바로 반환하기 때문에 커버링 인덱스가 적용되어 데이터 조회에 굉장히 많은 차이가 발생했다.

4초 -> 0.5초 이내로 개선

유의미한 결과를 얻었다. 근데 대용량으로 count 되는 유저들은 따로 redis에 캐싱을 적용해서 좀 더 빠르고 db에 많이 접근하지 않게 수정하는것도 필요할 것 같다.

 

뭔가 비교를 해서 유의미한 데이터를 얻어야 하는 과정이라면 스캔하는 데이터 건수 자체를 잘라야하고, 대용량을 빠르게 조회해야된다고 하면 최대한 인덱싱 된 테이블 내에서만 조회를 해서 가공하는 작업을 하려고 노력하는게 가장 베스트가 아닐까 생각해본다!