마이그레이션 쿼리와 인덱스의 중요성을 느끼며

이 글은 스스로 회고하기 위한 글이며, 제가 개발했던 배치의 간단한 쿼리에서 인덱스 사용 여부로 엄청난 성능 차이를 몸소 느끼게 되어 작성하게 되었습니다.

상황

기존 데이터를 조회해 계산된 결과를 새로운 컬럼에 부여하는 마이그레이션 잡을 개발하였습니다. Spring Batch를 통해 개발하였고, Reader에서 데이블 내 모든 데이터를 페이징으로 조회, Writer에서 조회된 데이터를 계산하여 계산된 값으로 해당 튜플에 업데이트하는 간단한 배치 잡이었습니다. 빠른 마이그레이션을 위해 Writer 단계에서 10개의 스레드를 통해 계산 및 UPDATE를 수행하도록 한 상태였습니다. 마이그레이션 데이터의 크기는 환경마다 달랐으며 A환경 5만건, B환경 25만건 정도 존재하였습니다.

방심했던 이유

테스트 환경에서 검증을 마친 상태였으며, 예상 수행시간 또한 테스트 환경의 결과를 통해 산출하였습니다. 수행시간은 테스트 환경에서의 데이터 양과 수행시간을 비례식으로 세워 계산할 수 있었습니다. 그 결과 B환경(25만건)의 경우 아무리 오래 걸려도 11분 내로 잡이 완료될것으로 계산되었고, 이 때만 해도 해당 마이그레이션은 문제 없이 끝날 줄 알았습니다. 그러나...

문제

리얼 환경에서 마이그레이션 수행은 영 속도가 나지 않았습니다. 로그 상으로 A환경의 경우 1초에 10건, B환경의 경우 1초에 고작 2건만 UPDATE 치고 있었기 때문인데요, 분명 스레드풀을 사용하였었고 쿼리도 단순한 UPDATE문이었는데 속도가 나지 않는것이 이상했었습니다.

원인을 찾는 중에 팀원중 한분이 UPDATE 쿼리의 where문 조건으로 인덱스가 존재하는지 물어보셨습니다. 인덱스엔 해당 조건으로된 인덱스가 없었고 쿼리 플랜 확인결과 역시나 인덱스를 제대로 타지 않고 있음을 확인할 수 있었습니다.

possible_keys 없으며, rows도 전체 데이터의 수이다. row는 MySQL이 찾아야하는 데이터행 수의 예상값이다.

패인은 이것이었습니다. 저는 마이그레이션 작업이기에 필요한 최소한의 데이터만 DB로 부터 조회하여 처리할수 있도록 설계를 했었습니다. 업데이트를 위해 필요한 컬럼만 Reader에서 조회하였으며, 해당 컬럼만을 조건을 주어 Writer에서 UPDATE 하도록 했습니다.

그러나, 해당 테이블은 결합 인덱스(C1, C2)를 사용하고 있었으며 조건으로 걸었던 컬럼은 결합 인덱스의 두번째 컬럼(C2)이었습니다. 당연히 C1, C2 컬럼의 순서로 클러스터링이 되어있을 것이기에 UPDATE 쿼리는 인덱스를 타지 못했겠죠. 25만건의 데이터가 있는 테이블에 쿼리 한발당 데이터 full scan이 일어나고 있는 것입니다... 실제 DB에 쿼리를 직접 수행해보니 한발당 5초의 시간이 걸리고 있었는데요, 10개 스레드가 돌았으니 1초에 2개씩 수행한것이 정확히 맞아 떨어졌습니다(이때 맞아 떨어진것이 한편으론 신기했습니다).

인덱스를 태우기위해 급히 필요한 컬럼도 함께 Reader에서 조회하도록, 그리고 UPDATE시 조건으로 인덱스를 구성하는 컬럼 순서로 where 문에 넣어주었습니다. 쿼리 플랜도 확인하여 possible_keys와 rows값이 바뀐것을 확인할 수 있었고요.

인덱스 이제 탄다!!

인덱스를 태울수 있는 쿼리로 마이그레이션을 재수행한 결과 A환경은 1초에 2083건, B환경은 1초에 833건의 데이터를 UPDATE 할 수 있었습니다.

환경	데이터 수	인덱스 미사용 초당 처리량	인덱스 사용 초당 처리량	인덱스 효율
A	54885	10/sec	2083/sec	208.3배
B	257795	2/sec	833/sec	416.5배

배운 것

처음엔 DB에서 가져오는 데이터 양을 최소화 하기 위해 최소한의 데이터만 요청하여 처리하도록 하였지만, 처리 시간을 전혀 고려하지 않은 방식이었습니다. 데이터가 많은 테이블에서의 쿼리인 만큼 인덱스의 효율은 배가 되며, 이를 위해서는 최소 조건이 아닌, 불필요하다 여긴 컬럼들도 함께 조회할 필요가 있었습니다. 쿼리에 where 절이 들어간다면, 항상 해당 컬럼이 인덱스를 탈 수 있는지 확인해보아야 합니다.