슬로우 쿼리 해결기: 함수형 인덱스로 비트 연산 쿼리 최적화하기 (새 탭에서 열림)
LINE VOOM 서비스는 헤비 유저의 프로필 조회 시 비트 연산 조건으로 인해 발생하는 30초 이상의 슬로우 쿼리 문제를 해결하기 위해 MySQL 8.0.13의 함수형 인덱스(functional index)를 도입했습니다. 기존의 비트 연산 조건은 인덱스를 무력화하여 수십만 건의 데이터를 풀 스캔하게 만들었으나, 연산 결과를 인덱싱하고 이를 10진수 동등 비교 쿼리로 전환함으로써 스캔 범위를 3% 수준으로 대폭 축소했습니다. 이 과정에서 무중단 인덱스 생성과 점진적 롤아웃을 통해 운영 안정성을 확보하며 시스템 성능을 성공적으로 최적화했습니다. ### 비트 연산 조건에 의한 성능 저하 원인 * LINE VOOM의 포스트 메타데이터 테이블은 `category_flag`와 `access_flag`라는 `bit(64)` 타입 컬럼에 서비스 상태 정보를 압축 저장합니다. * 특정 상태를 필터링하기 위해 `category_flag & 0x0100`과 같은 비트 연산을 사용했는데, 이는 인덱스에 저장된 원본 값이 아닌 연산 결과를 조건으로 활용하므로 기존 인덱스를 타지 못합니다. * 결과적으로 특정 사용자의 모든 포스트를 일일이 순회하며 연산을 수행해야 했고, 포스트가 수십만 건에 달하는 헤비 유저의 경우 쿼리 타임아웃이 빈번하게 발생했습니다. ### 함수형 인덱스를 활용한 최적화 설계 * MySQL 8.0.13에서 도입된 함수형 인덱스는 표현식의 결과를 가상 컬럼 형태로 저장하고 인덱싱하는 기능을 제공합니다. * 팀은 `user_id`와 비트 연산 표현식을 결합한 복합 인덱스 `idx_user_premium_searchable (user_id, (category_flag & 0x0100), (access_flag & 0x0001))`를 설계했습니다. * 이 방식은 기존 테이블 스키마를 크게 변경하지 않으면서도 특정 비트 패턴에 대해 즉각적인 인덱스 조회를 가능하게 합니다. ### 인덱스 활성화를 위한 쿼리 튜닝 및 검증 * 개발 환경 테스트 결과, 단순히 비트 연산을 포함하는 것만으로는 인덱스가 작동하지 않는 것을 확인했습니다. * 인덱스를 타기 위해서는 쿼리의 표현식이 인덱스 정의와 완벽히 일치해야 하며, 특히 비트 연산 결과를 **10진수 값으로 동등 비교**(`= 256`)했을 때만 인덱스가 정상적으로 적용되었습니다. * 최적화 후 스캔 행 수가 805건에서 31건으로 줄어드는 등 극적인 성능 향상을 보였으며, 인덱스 용량 증가(약 24%)는 운영 환경에서 수용 가능한 수준으로 판단되었습니다. ### 운영 환경 적용 및 시행착오 해결 * **무중단 인덱스 생성**: DBA 팀과 협업하여 Online DDL 방식을 사용함으로써 서비스 중단 없이 수십 개의 테이블에 인덱스를 추가했습니다. * **복제 지연 대응**: 인덱스 생성 중 발생한 마스터-슬레이브 복제 지연으로 인해 최신 글이 보이지 않는 이슈가 발생했으나, 캐시 만료 시간을 단축하여 사용자 불편을 최소화했습니다. * **논리 오류 방지**: 비트 연산을 동등 비교로 바꿀 때 OR 조건과 AND 조건의 의미 차이로 인한 버그를 발견했습니다. 이를 방지하기 위해 동적 설정을 활용해 샤드별로 쿼리를 점진적으로 적용하며 안전하게 검증을 마쳤습니다. 비트 연산과 같이 컬럼 가공이 필요한 조건을 상시로 사용해야 한다면, MySQL의 함수형 인덱스는 스키마 변경 부담을 최소화하면서 성능을 극대화할 수 있는 강력한 도구입니다. 다만, 인덱스가 적용되는 정확한 표현식(10진수 비교 등)을 사전에 검증하고, 대규모 테이블 작업 시 발생할 수 있는 복제 지연과 논리적 조건 변화를 세심하게 모니터링하는 것이 중요합니다.