개인정보 암호화 뒤 MySQL 장애: 검색 인덱스 누락

회사에서 개인정보를 암호화하는 작업이 진행된 뒤, 운영 데이터베이스가 멈추는 Critical 등급의 장애가 발생했다.

Critical은 서비스 핵심 기능이 중단돼 가장 먼저 복구해야 하는 심각도를 뜻한다.

암호화 알고리즘이 느린 것처럼 보일 수 있었지만, 실제 원인은 훨씬 익숙한 곳에 있었다.

기존 평문 컬럼을 대신해 조회에 쓰기 시작한 검색용 해시 컬럼에 인덱스가 없었다.

인덱스는 책의 맨 뒤에 있는 찾아보기처럼 특정 값이 있는 위치를 빠르게 찾도록 돕는 자료 구조다.

찾아보기가 없으면 원하는 단어 하나를 찾기 위해 책을 첫 장부터 넘겨야 하듯, 데이터베이스도 많은 행을 차례로 읽게 된다.

나는 장애 변경을 먼저 되돌린 뒤 새 컬럼과 기존 컬럼의 구조를 대조했고, 누락된 인덱스를 보완해 복구했다.

대응 시간은 업무 기록상 1시간 30분이었다.

장애가 발생한 뒤에는 변경을 롤백해 서비스부터 살리고, 새 컬럼과 기존 컬럼의 차이를 따라 원인을 좁혔다.

암호화 이후 검색 경로가 달라졌다

전화번호처럼 나중에 다시 보여 줘야 하는 값은 암호문으로 저장할 수 있다.

암호문은 올바른 키를 가진 제한된 경로에서 원래 값으로 복원할 수 있는 데이터다.

하지만 매번 암호문을 풀어 모든 값과 비교하는 방식은 검색에 적합하지 않다.

그래서 당시 구조에는 정확히 같은 값인지 비교할 수 있는 검색용 해시 컬럼이 따로 있었다.

애플리케이션은 사용자가 입력한 값을 같은 규칙으로 정리한 뒤 검색 토큰을 만들고, 그 토큰으로 행을 찾았다.

SELECT user_id
FROM user_account
WHERE phone_hash = :phone_hash;

여기서 :phone_hash는 실제 전화번호가 아니라 애플리케이션이 미리 계산해 전달하는 검색 토큰이다.

예시는 이해를 돕기 위해 테이블과 컬럼 이름을 일반화했으며, 실제 회사 식별자는 포함하지 않았다.

문제는 기존 평문 컬럼에 있던 인덱스가 새 phone_hash 컬럼으로 함께 옮겨지지 않았다는 점이었다.

쿼리의 모양은 단순했지만, 데이터베이스는 일치하는 한 행을 찾기 위해 테이블의 많은 행을 확인해야 했다.

이 구조가 잘 맞는 검색은 “이 전화번호와 정확히 같은 회원이 있는가?”처럼 정확 일치가 필요한 경우다.

전화번호 마지막 네 자리 검색이나 이름의 일부 검색에는 같은 토큰 하나를 그대로 사용할 수 없다.

부분 검색을 유지하려고 짧은 조각마다 토큰을 만들면 저장량과 노출되는 패턴이 늘어난다.

따라서 암호화 전환 전에 검색 요구를 정확 일치, 부분 일치, 정렬과 범위 조회로 나누고, 꼭 필요한 기능만 별도 설계해야 한다.

이 글의 장애는 정확 일치용 토큰이라는 선택이 잘못돼서가 아니라, 그 토큰을 찾는 운영 쿼리에 맞는 인덱스를 옮기지 않아 발생했다.

왜 테스트에서는 쉽게 지나갔을까

테스트를 통과했다는 사실만으로 운영에서도 충분히 빠르다고 볼 수는 없다.

행이 몇십 개뿐인 환경에서는 전부 읽어도 금방 끝나기 때문에 인덱스가 없어도 차이가 작다.

운영처럼 데이터가 많고 같은 검색이 동시에 들어오는 환경에서는 한 번의 넓은 스캔이 CPU와 디스크 읽기를 차지하고, 뒤따르는 쿼리의 대기까지 늘린다.

MySQL 공식 문서도 인덱스가 없으면 서버가 첫 행부터 읽으며 조건에 맞는 행을 찾고, 테이블이 클수록 그 비용이 커진다고 설명한다.

반대로 모든 컬럼에 인덱스를 추가하는 것도 답은 아니다.

인덱스는 저장 공간을 사용하고, INSERT, UPDATE, DELETE가 일어날 때 함께 갱신해야 하기 때문이다.

이번 문제의 핵심은 인덱스를 많이 만드는 것이 아니라, 운영의 WHERE 조건이 새 컬럼으로 바뀌었는데 그 조회 경로를 검증하지 않은 것이었다.

장애 후 1시간 30분: 롤백부터 복구까지

장애가 난 상태에서 바로 인덱스를 만들지 않고, 먼저 원인이 된 변경을 되돌려 데이터베이스를 복구했다.

롤백은 새 버전이나 설정을 이전의 동작 가능한 상태로 되돌리는 대응이다.

운영 데이터가 많은 테이블에 인덱스를 추가하면 작업 자체가 자원을 사용하고 잠금이나 지연을 만들 수 있으므로, 이미 과부하인 상태에서 성급하게 실행하는 것은 위험하다.

MySQL의 온라인 DDL 옵션을 사용해도 작업 시작과 종료에 짧은 메타데이터 잠금이 필요할 수 있다.
DDL(Data Definition Language)은 테이블과 인덱스 같은 구조를 바꾸는 명령이다.
운영에서는 대상 버전과 테이블 조건에서 허용되는 알고리즘·잠금 방식을 확인하고, 복제 지연과 여유 저장 공간까지 보고 실행 시점을 정해야 한다.

당시 기록으로 확인되는 대응 순서는 다음과 같다.

1. 장애를 일으킨 변경을 롤백해 기존 조회 경로로 복구했다.
2. 검색용 해시 컬럼과 운영 쿼리를 다시 확인했다.
3. 검색용 해시 컬럼에 필요한 인덱스를 추가했다.
4. 컬럼 교체 검토 항목에 타입, NULL, 인덱스, 제약 조건을 함께 넣었다.

인덱스를 보완한 SQL은 아래처럼 단순하다.

CREATE INDEX idx_user_account_phone_hash
ON user_account (phone_hash);

인덱스 이름과 테이블 이름은 공개용 예시이며 실제 이름이 아니다.

복구 뒤에는 존재 여부보다 사용 여부를 확인한다

CREATE INDEX가 성공했다고 해서 실제 조회가 그 인덱스를 사용한다고 자동으로 보장되지는 않는다.

컬럼 타입, 비교 방식, 복합 인덱스의 순서, 데이터 분포에 따라 옵티마이저의 선택이 달라질 수 있다.

옵티마이저는 여러 실행 방법의 비용을 비교해 쿼리를 어떻게 처리할지 고르는 데이터베이스 내부 기능이다.

당시 EXPLAIN 출력은 보존되지 않았으므로 아래에는 같은 문제를 예방하기 위해 적용할 검증 예시를 남겼다.

SHOW INDEX FROM user_account;

EXPLAIN
SELECT user_id
FROM user_account
WHERE phone_hash = :phone_hash;

SHOW INDEX는 테이블에 어떤 인덱스가 있고 어떤 컬럼 순서로 구성됐는지 보여 준다.

EXPLAIN은 실제 데이터를 바꾸지 않고 쿼리 실행 계획을 보여 주므로, key, possible_keys, 예상 조회 행 수를 배포 전후로 비교할 수 있다.

MySQL 공식 문서에 따르면 possible_keys가 비어 있으면 사용할 만한 인덱스가 없다는 뜻이며, key는 옵티마이저가 실제로 선택한 인덱스를 나타낸다.

EXPLAIN ANALYZE는 실행 계획뿐 아니라 쿼리를 실제로 실행해 측정하기 때문에 운영에서 사용할 때는 부하와 대상 쿼리를 먼저 검토해야 한다.

확인된 결과는 롤백과 인덱스 추가로 1시간 30분 안에 복구를 완료했다는 점이다.

컬럼 하나를 바꿀 때 함께 옮겨야 하는 것

이번 일 뒤에는 “새 컬럼이 생겼는가”보다 “기존 컬럼이 맡던 계약이 모두 이동했는가”를 보게 됐다.

데이터베이스 컬럼은 값만 담는 통이 아니라 여러 규칙이 붙은 인터페이스에 가깝다.

특히 검색용 해시는 정확 일치 조회에 쓰므로, 실제 WHERE 절과 같은 컬럼에 인덱스가 있어야 한다.

암호문 컬럼에 인덱스를 만들고 검색용 해시 컬럼을 빠뜨리면 저장 구조는 그럴듯해 보여도 운영 쿼리는 빨라지지 않는다.

다음 배포라면 이렇게 진행한다

아래 순서는 당시 기록과 별개로, 같은 종류의 컬럼 마이그레이션에 적용할 재발 방지 절차다.

1. 스키마 차이를 코드 리뷰 항목으로 만든다

기존 컬럼과 새 컬럼의 타입, NULL, 기본값, 인덱스, 고유성, 외래 키를 한 표에서 비교한다.

마이그레이션 SQL만 보지 않고 애플리케이션이 실제로 사용하는 조회 조건도 함께 붙인다.

2. 운영과 비슷한 데이터 분포에서 실행 계획을 본다

행 수만 크게 만든 임의 데이터보다 자주 반복되는 값과 드문 값이 섞인 데이터가 더 유용하다.

검색 토큰의 분포가 다르면 옵티마이저가 선택하는 계획도 달라질 수 있기 때문이다.

3. 읽기 경로를 먼저 관찰 가능하게 만든다

새 컬럼을 사용하는 쿼리의 실행 시간, 호출 수, 오류율을 기존 경로와 분리해 본다.

관측 가능성은 시스템 안에서 무슨 일이 일어나는지 로그와 지표로 확인할 수 있는 상태를 뜻한다.

4. 일부 트래픽부터 전환한다

가능하다면 모든 요청을 한 번에 바꾸지 않고 작은 비율부터 새 조회 경로로 보낸다.

이 방식은 카나리 배포라고 부르며, 이상이 생겼을 때 영향을 받는 범위를 줄여 준다.

5. 롤백 조건을 숫자로 정한다

DB CPU, 활성 연결, 쿼리 지연, 잠금 대기 중 어떤 값이 어느 수준을 넘으면 되돌릴지 배포 전에 정한다.

장애가 시작된 뒤 “조금 더 볼지” 토론하는 시간을 줄이는 것이 목적이다.

배포 전 체크리스트

• 기존 컬럼과 새 컬럼의 전체 스키마 정의를 비교했는가?
• 실제 읽기와 쓰기 코드가 사용하는 컬럼을 목록으로 만들었는가?
• 각 WHERE와 JOIN 조건에 필요한 인덱스를 확인했는가?
• SHOW INDEX로 인덱스 정의를 확인했는가?
• EXPLAIN으로 새 조회 경로의 인덱스 사용 여부를 확인했는가?
• 운영과 비슷한 규모와 분포에서 지연을 측정했는가?
• 인덱스 생성 작업 자체가 운영에 줄 영향을 검토했는가?
• 배포 지표와 롤백 기준을 미리 정했는가?
• 평문 컬럼 제거 전에 누락된 읽기 경로가 없는지 확인했는가?

결론

이 장애는 어려운 암호화 알고리즘 때문에 생긴 문제가 아니었다.

기존 컬럼이 맡던 조회 성능의 계약을 새 검색 컬럼으로 완전히 옮기지 못해서 생겼다.

나는 먼저 변경을 롤백해 DB를 복구하고, 실제 조회 조건에 쓰이는 해시 컬럼의 인덱스를 보완했다.

그 결과는 “인덱스를 추가하면 빠르다”는 익숙한 문장보다 구체적인 교훈으로 남았다.

컬럼을 교체할 때는 값뿐 아니라 타입, 제약, 인덱스와 실제 쿼리 계획까지 함께 옮겨야 한다.

암호문과 검색용 해시를 왜 나눴는지는 개인정보를 암호화하면서 검색도 가능하게 만든 구조에서 이어서 설명한다.

느린 쿼리를 범위와 실행 단위로 나누어 확인하는 방법은 느린 쿼리를 병렬 범위로 나눌 때 먼저 확인한 것에서도 볼 수 있다.

참고한 공식 문서

• MySQL 8.4 Reference Manual — How MySQL Uses Indexes
• MySQL 8.4 Reference Manual — EXPLAIN Statement
• MySQL 8.4 Reference Manual — EXPLAIN Output Format
• MySQL 8.4 Reference Manual — SHOW INDEX Statement
• MySQL 8.4 Reference Manual — InnoDB Online DDL Operations