슬로우 쿼리 해결기: 함수형 인덱스로 비트 연산 쿼리 최적화하기

들어가며

안녕하세요. LINE VOOM 서비스의 포스트 서버를 개발하고 있는 서용준입니다. 이번 글에서는 저희 팀이 약 7개월에 걸쳐 슬로우 쿼리 문제를 해결한 과정과 그 과정에서 배운 교훈을 공유하고자 합니다.

저희 서비스에서는 헤비 유저의 소셜 프로필을 조회할 때 간헐적으로 슬로우 쿼리가 발생하고 있었습니다. 발생 빈도가 높지는 않았지만 한 번 발생하면 쿼리가 30초 이상 실행되다가 타임아웃이 발생했습니다. 결론부터 말씀드리면, MySQL 8.0.13의 함수형 인덱스(functional index)를 적용하고 쿼리 조건을 변경해 이 문제를 해소할 수 있었습니다. 이 글에서는 문제의 원인을 파악하고 해결책을 찾아가는 과정, 그리고 운영 환경에 적용하면서 겪은 시행착오를 상세히 다루겠습니다. 비슷한 문제를 겪고 계신 분들께 도움이 되길 바라며 시작하겠습니다.

참고: 이 글에서 사용하는 테이블명, 컬럼명, 비트 값 등은 이해를 돕기 위해 간소화한 예시입니다.

문제 상황

어떤 상황에서 슬로우 쿼리가 발생했는지 저희 서비스의 데이터 구조와 발생한 상황을 간략히 짚어보겠습니다.

LINE VOOM 포스트 서비스의 데이터 구조와 테이블의 역할

먼저 저희 서비스의 데이터 구조를 간략히 설명하겠습니다. 저희 서비스는 사용자의 포스트 메타 정보가 여러 샤드에 분산돼 있고, 각 샤드에는 여러 개의 파티션 테이블이 존재합니다. 여기서 주목해야 할 부분은 테이블 속 category_flag와 access_flag 컬럼입니다. 두 컬럼 모두 bit(64) 타입으로, 다양한 상태 정보를 비트 플래그 형식으로 압축 저장하고 있습니다. 예를 들어 category_flag & 0x0100 조건은 프리미엄 콘텐츠를 의미하고, access_flag & 0x0001 조건은 검색 노출 대상 포스트를 의미합니다.

헤비 유저 프로필 조회 시 타임아웃을 발생시켰던 쿼리 색출

문제는 포스트를 수십만 건 보유한 헤비 유저의 소셜 프로필을 조회할 때 쿼리가 30초 이상 실행되다가 타임아웃이 발생하는 것이었습니다. 이 때문에 최대 응답시간 이상치 알림도 빈번하게 울려서 오류 모니터링에 혼선을 빚어내기도 했습니다. 아래는 소셜 프로필을 조회할 때 호출되는 API의 최대 응답 시간 그래프인데, 응답 시간이 길어 임계치가 넘어가는 요청은 빨갛게 표시됩니다. 그래프가 붉게 물들어 있는 것을 볼 수 있습니다.

소셜 프로필을 조회할 때 호출되는 API의 최대 응답 시간 그래프

문제가 발생한 쿼리를 살펴보면 다음과 같습니다.

SELECT post_id, user_id, sort_key, category_flag, access_flag, create_dtm, update_dtm, writer_id
FROM post_metadata
WHERE (user_id = '{user_id}' AND category_flag & 0x0100 AND access_flag & 0x0001)
ORDER BY sort_key DESC LIMIT 70

실행 계획을 분석해 보니 스캔하는 행 수가 수십만 건에 달했습니다. 왜 이렇게 많은 행을 스캔해야 했을까요?

슬로우 쿼리가 발생한 원인: 인덱스를 무력화한 비트 연산 조건

원인은 비트 연산 조건에 있었습니다. category_flag & 0x0100과 같은 비트 연산은 컬럼 값 자체가 아닌 연산 결과를 조건으로 사용합니다. 인덱스는 컬럼의 원본 값을 기준으로 정렬돼 있기 때문에 비트 연산 결과로 필터링하려면 모든 행에 접근하여 연산을 수행해야 합니다. 결과적으로 user_id로 필터링한 후 해당 사용자의 모든 포스트를 순회해야 했습니다. 일반 사용자라면 큰 문제 없겠지만 수십만 건의 포스트를 보유한 헤비 유저의 경우 엄청난 양의 데이터를 스캔해야 하기 때문에 슬로우 쿼리가 발생한 것입니다.

해결 방안 탐색 및 선택 - MySQL의 함수형 인덱스

이 문제를 해결하기 위해 저희 팀에서는 다양한 해결 방안을 도출하고 검토했습니다. 장비를 업그레이드하자니 비용도 문제였고 현재 서버 성능도 양호한 편이었습니다. 캐싱(caching)을 도입하자는 의견도 있었지만 데이터 일관성을 유지하기가 어려웠습니다. 파티셔닝을 더 세분화하는 방안은 근본적인 해결책이 되지 못했습니다.

그러던 중 컬럼값이 아닌 표현식의 결과를 인덱스로 저장할 수 있게 해주는 MySQL 8.0.13 버전의 함수형 인덱스라는 기능이 눈에 들어왔고, 이 기능을 1순위로 검토하기 시작했습니다. 함수형 인덱스는 기존 테이블 스키마를 변경할 필요 없이 인덱스만 추가하면 되기 때문입니다. 쿼리 조건만 수정하면 되므로 코드 변경 범위를 최소화할 수 있었고, 슬로우 쿼리를 유발하는 조건이 '프리미엄 콘텐츠 + 검색 노출 대상'이라는 특정 패턴에 집중돼 있었기 때문에 이 기능으로 충분히 커버할 수 있을 것이라는 판단도 있었습니다.

간단히 설명드리면 함수형 인덱스는 MySQL 8.0.13에서 도입된 기능으로, 내부적으로 숨겨진 가상 칼럼(hidden virtual generated column)을 생성해 표현식 결과를 저장하고 인덱스를 구성하는 기능입니다. 일반 인덱스와 비교하면 다음과 같습니다.

-- 일반 인덱스: 컬럼 값을 그대로 저장
CREATE INDEX idx_normal ON table(column);

-- 함수형 인덱스: 표현식 결과를 저장
CREATE INDEX idx_functional ON table((column & 0x0100));

여기서 중요한 점이 있습니다. 쿼리의 표현식이 인덱스 정의와 정확히 일치해야 인덱스를 활용할 수 있다는 것입니다. 예를 들어 MySQL에서 (category_flag & 0x0100)과 (category_flag & 0x0010)은 다른 표현식으로 인식합니다.

개발 환경에서 검증

이제 선택한 해결 방안을 개발 환경에 적용하고 검증해 본 과정을 살펴보겠습니다.

인덱스 설계

앞서 말씀드렸듯 슬로우 쿼리 분석 결과 주로 문제가 되는 조건은 category_flag & 0x0100(프리미엄 콘텐츠)와 access_flag & 0x0001(검색 노출 대상), 두 가지였습니다. 저희는 아래와 같이 고윳값인 user_id를 먼저 지정하고 비트 연산 표현식을 뒤에 배치하는 복합 인덱스를 설계했습니다.

ALTER TABLE post_metadata ADD INDEX idx_user_premium_searchable (
  user_id,
  (category_flag & 0x0100),
  (access_flag & 0x0001)
);

이렇게 하면 user_id로 먼저 필터링한 후 비트 연산 결과로 추가 필터링할 수 있습니다.

예상과 달랐던 첫 테스트

저희는 개발 환경에서 인덱스를 생성하고 기대에 부풀어 EXPLAIN을 실행해 봤습니다. 그런데 탐색할 데이터 수가 전혀 줄지 않았습니다. 여전히 테스트 계정의 모든 데이터(805 줄)를 스캔하고 있었습니다. 인덱스가 제대로 작동하지 않은 것입니다. 이에 저희는 인덱스를 사용하도록 아래와 같이 쿼리 조건을 이리저리 바꿔봤습니다. > 0 비교 연산자를 사용해 보기도 하고 비트값으로 동등 비교를 시도하기도 해봤지만 모두 실패했습니다.

-- 시도 1: 기존 truthy 체크 - 실패
WHERE user_id = '{user_id}' AND category_flag & 0x0100 AND access_flag & 0x0001

-- 시도 2: > 0 비교 - 실패
WHERE user_id = '{user_id}' AND (category_flag & 0x0100) > 0

-- 시도 3: 비트 값 동등 비교 - 실패
WHERE user_id = '{user_id}' AND (category_flag & 0x0100) = 0x0100

인덱스를 사용하지 않는 원인 발견: 10진수 동등 비교 필요

여러 시도 끝에 비트 연산 결과를 10진수 값으로 동등 비교해야 인덱스를 사용할 수 있다는 것을 확인했습니다. 이에 아래와 같이 쿼리를 변경하니 드디어 인덱스를 사용하기 시작했습니다.

-- 최종 성공: 10진수로 동등 비교
WHERE user_id = '{user_id}'
  AND (category_flag & 0x0100) = 256
  AND (access_flag & 0x0001) = 1

실행 계획을 확인해보니 스캔 행 수가 805에서 31로 줄었습니다. 기존의 약 3%에 해당하는 행에만 접근하는 것입니다. 비록 저장 공간이 인덱스 용량 기준 약 24% 증가했는데요. 이와 관련해 DBA 팀에 확인한 결과 운영 환경에서도 용량 부족 문제가 발생하지는 않을 것으로 판단됐습니다.

운영 환경에 적용

저희는 개발 환경에서 확인을 마친 뒤 인덱스 생성 후 쿼리를 변경하는 순서로 운영 환경에 적용했습니다. 적용 방법과 적용 과정에서 발생한 이슈 및 해결 방법 등을 단계별로 하나씩 살펴보겠습니다.

인덱스 생성

먼저 인덱스 생성 단계입니다.

무중단 인덱스 생성

저희는 운영 환경에 인덱스를 추가하기 위해 DBA 팀과 협업해 온라인 스키마 변경(Online DDL) 방식을 사용했습니다. 이 방식은 새로운 임시 테이블을 생성하고, 기존 데이터를 복사한 뒤, 인덱스를 생성하고, rename 명령어로 테이블을 교체하는 방식입니다. 작업 중에 서비스가 중단되지 않고, 복제 지연이 발생하면 자동으로 작업이 일시 중지되며, 문제가 생기면 롤백도 가능한 방식입니다.

한 달간 인덱스 생성 롤아웃

작업 범위는 다수의 샤드에 걸쳐 있는 총 수십 개의 테이블이었습니다. 저희는 1번 샤드의 테이블들을 먼저 작업해 검증한 뒤 나머지 샤드에 순차적으로 적용하는 전략을 세웠습니다. 하루에 한두 개 테이블씩 신중하게 진행했으며, 혹시라도 긴급 상황 발생 시 DBA 팀의 대응이 어려울 수 있는 시점에는 DDL 작업을 진행하지 않았습니다.

예상치 못한 복제 지연 문제

인덱스 생성 작업을 진행하던 중 예상치 못한 문제가 발생하기도 했습니다. 공식 계정 관리 도구에서 포스트를 생성한 후 자동으로 이동되는 목록 페이지에서 방금 생성한 포스트가 보이지 않는다는 제보가 들어온 것입니다.

원인을 분석해 보니 인덱스 생성 작업 때문에 메인 DB에서 복제 DB로 복제하는 데 걸리는 지연 시간이 평소보다 증가했는데, 쓰기는 메인 DB에서, 읽기는 복제 DB에서 수행되다 보니 최신 데이터가 즉시 조회되지 않은 것이었습니다. 여기에 기존에 캐시 만료 시간이 비교적 길게 설정돼 있었기에 문제가 더욱 부각됐습니다.

저희는 문제를 해결하기 위해 본인 게시글 목록 조회 시 사용하는 캐시 만료 시간을 대폭 줄였고, 짧아진 캐시 기간 만큼의 복제 지연은 감수하기로 하고 인덱스 작업을 재개했습니다.

쿼리 변경

인덱스 생성을 완료한 뒤에는 쿼리 변경 작업에 돌입했습니다.

비트 비교에서 동등 비교로

쿼리 변경 작업은 기존의 비트 연산 truthy 체크를 10진수 동등 비교로 변경하는 작업이었습니다. 다음은 작업 예시 쿼리입니다.

-- Before
WHERE user_id = '{user_id}'
  AND category_flag & 0x0100
  AND access_flag & 0x0001

-- After
WHERE user_id = '{user_id}'
  AND (category_flag & 0x0100) = 256
  AND (access_flag & 0x0001) = 1

점진적 쿼리 적용 롤아웃 전략

쿼리 변경의 영향 범위가 컸기 때문에 동적 설정 관리 시스템을 활용한 점진적 롤아웃 전략을 사용했습니다. 이 글에서 예시로 든 쿼리 외에도 여러 쿼리를 변경해야 했는데요. 매일 하나의 쿼리 패턴만 1번 샤드에 적용한 뒤 모니터링하는 방식으로 1번 샤드에서 모든 쿼리를 검증한 후 나머지 샤드로 확장하는 방식이었습니다. 이 방식의 장점은 샤드별, 쿼리별로 개별 적용이 가능하고 문제 발생 시 설정 변경만으로 즉시 롤백할 수 있다는 것입니다.

점진적 쿼리 적용 중 발견한 치명적 버그

앞서 세운 전략에 따라 1번 샤드부터 점진적으로 적용하던 중 방문자 계정에 따라 특정 사용자의 소셜 프로필을 방문하면 아무 콘텐츠도 보이지 않는 심각한 버그가 발견됐습니다. 원인을 분석해 보니 비트 연산의 의미를 잘못 이해한 것이 문제였습니다. 기존 쿼리 category_flag & 0x0110은 0x0100(프리미엄) 또는 0x0010(일반) 비트 중 하나라도 있으면 매치되는 OR 조건이었는데요. 변경한 쿼리 (category_flag & 0x0110) = 272는 프리미엄과 일반 비트가 둘 다 있어야 매치되는 AND 조건이 돼 버렸고, 실제 데이터에는 프리미엄 비트만 저장돼 있었기에 변경된 쿼리 결과에 아무것도 나오지 않은 것입니다.

앞서 말씀드렸듯 슬로우 쿼리가 특정 패턴에서만 발생했기에 저희는 함수형 인덱스로 정의된 정확한 비교값과 일치하는 경우에만 등호 쿼리를 사용하고 그 외 복합 조건은 기존 비트 연산 쿼리를 유지하도록 수정했습니다. 점진적 롤아웃 덕분에 이와 같은 치명적 버그를 프로덕션 전체 적용 전에 발견할 수 있었습니다.

함수형 인덱스 적용 결과

함수형 인덱스가 적용된 타겟에서 슬로우 쿼리 타임아웃 오류가 더 이상 발생하지 않는 것을 확인했습니다. 최대 응답 시간 이상치 알림도 잦아들면서 모니터링 혼선이 해소돼 이제 실제로 문제가 있는 경우에 더 집중할 수 있게 되었습니다. 아래의 소셜 프로필 API 최대 응답 시간 그래프에서도 타임아웃 제한인 30초에 도달하는 요청은 없어졌고 임계치를 넘겨 붉게 표시되는 요청도 없어진 것을 볼 수 있습니다.

소셜 프로필 API 최대 응답 시간 그래프

마치며

이번 작업을 통해 몇 가지 중요한 교훈을 얻었습니다.

함수형 인덱스는 정의한 표현식과 쿼리의 표현식이 정확히 일치해야 하며, 비트 연산 결과를 조건으로 사용하려면 10진수 동등 비교가 필요합니다. 단순히 인덱스를 추가하는 것만으로는 부족했고 쿼리 조건까지 함께 맞춰야 비로소 인덱스를 활용할 수 있었습니다.
flag & 0x0110과 (flag & 0x0110) = 272는 완전히 다른 의미입니다. 전자는 OR 조건이고 후자는 AND 조건입니다. 이와 같이 비트 플래그를 다룰 때는 의미를 정확히 이해하고 변환해야 합니다.
점진적 롤아웃이 중요합니다. 저희는 1번 샤드에서 먼저 테스트한 덕분에 비트 조합 불일치 버그를 전체 적용 전에 발견할 수 있었습니다. 대규모 변경은 반드시 점진적으로 적용해야 합니다.
대규모 DDL 작업은 복제 지연을 유발할 수 있습니다. 80개 테이블에 인덱스를 추가하는 동안 복제 지연이 증가했고, 이로 인해 사용자 경험이 저하되는 이슈가 발생했습니다. DDL 작업 전에는 영향 범위를 충분히 고려해야 하고, 캐시 정책도 함께 검토해야 합니다.

만약 현재 함수형 인덱스 도입을 고려하고 계시다면, 쿼리 표현식과 인덱스 정의가 정확히 일치하는지, 비트 연산의 경우 10진수 동등 비교로 쿼리를 변환할 수 있는지, 새 인덱스를 저장할 공간이 충분한지, MySQL 8.0.13 이상인지를 먼저 확인해 보시기 바랍니다. 비트 연산 외에도 JSON 필드 추출, 문자열 함수, 날짜 추출, 계산 결과 등 다양한 상황에서 함수형 인덱스를 활용할 수 있습니다.

약 7개월간의 긴 여정이었지만, 문제를 해결하고 나니 앞으로의 업무에도 큰 자양분이 될 것 같습니다. 이 글이 비슷한 문제를 겪고 계신 분들께 도움이 되길 바라며 이만 마치겠습니다.