Mattermost의 인덱스 머지 교집합이 슬로우 쿼리의 원인이 됐던 사례에 대한 해석본과 내 결론

똑똑한 최적화가 놀랍게도 역효과가난 사례와 그 최종 해결을 공유.

처음 작성한 슬로우 쿼리

SELECT id FROM posts WHERE channel_id = '9tne5g44z...'
AND deleted_at = 0
AND created_at < 15826836...
ORDER BY created_at DESC
LIMIT 1;

 

 

실행계획 분석 결과는 다음과 같다:

 

 

 

이 쿼리는 그들의 환경에서 실행하는 데 몇 분이나 걸렸는데, USE INDEX(idx_posts_channel_id_delete_at_create_at)을 사용하면 1초도 안걸려 실행됐다고한다. 위의 EXPLAIN 출력을 보면, 옵티마이저가 idx_posts_created_at 인덱스를 선택하고 있는 걸 알 수 있는데, 사실 idx_posts_channel_id_deleted_at_created_at를 선택하는게 명확히 더 나은 선택이다. 스키마를 살펴보면

 

mysql> show CREATE TABLE Posts;
CREATE TABLE `Posts` (
  `Id` varchar(26) NOT NULL,
  `CreateAt` bigint(20) DEFAULT NULL,
  `UpdateAt` bigint(20) DEFAULT NULL,
  `EditAt` bigint(20) DEFAULT NULL,
  `DeleteAt` bigint(20) DEFAULT NULL,
  `UserId` varchar(26) DEFAULT NULL,
  `ChannelId` varchar(26) DEFAULT NULL,
  `Message` text,
  PRIMARY KEY (`Id`),
  KEY `idx_posts_update_at` (`UpdateAt`),
  KEY `idx_posts_create_at` (`CreateAt`),
  KEY `idx_posts_delete_at` (`DeleteAt`),
  KEY `idx_posts_channel_id` (`ChannelId`),
  KEY `idx_posts_user_id` (`UserId`),
  KEY `idx_posts_channel_id_update_at` (`ChannelId`,`UpdateAt`),
  KEY `idx_posts_channel_id_delete_at_create_at` (`ChannelId`,`DeleteAt`,`CreateAt`),
  FULLTEXT KEY `idx_posts_message_txt` (`Message`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

 

요약된 Posts 테이블 축약본이다.

하지만 문제 이해를 위한 필수적 요소들을 담고 있다. 우리가 관심있는 컬럼은 createAt, deletedAt, updatedAt, channelId다. 각각의 컬럼들은 그들의 개별 인덱스들을 갖고 있다. 그리고 두개의 추가적인 복합 인덱스들을 갖고 있다.

이제, 다시 쿼리를 보자.

SELECT id FROM posts WHERE channel_id = 'x' 
AND deleted_at = y AND create_at < z
ORDER BY created_at DESC
LIMIT 1

 

 

우리는 세 칼럼(channel_id, deleted_at, created_at)이 테이블을 필터링 하고 있다는 걸 알 수 있다. 그리고 오름차순 정렬하는 created_at 그리고 첫 행을 가져오는 것. 이것이 복합 인덱스가 created_at 단일 칼럼을 선택하는 것 보다 빠르다는 것이 명확한 이유다. 이 쿼리는 간단하게 말하자면 세 컬럼으로 더 세세하게 데이터를 스캔하고 필터링한다. 근데, 왜 MySQL이 잘못된 인덱스를 처음 선택할까?

 

USE INDEX를 사용하는 것은 다른 선택지가 없는 최후의 수단으로 고려했다.

나는 그 문제를 로컬에서 재현할 수 없었기 때문에, 쿼리를 이리저리 변형해가며 시험해보는 것이 불가능했다. 하지만 구글링을 좀 해본 뒤, 무슨 일이 있는지에 대한 꽤 그럴듯한 가설을 세울 수 있었다.

범인은 바로 “ORDER BY” 절이었다. MySQL은 똑똑하게 굴려고 하면서, created_at 인덱스를 쓰면 더 많은 행을 스캔해야 할 수는 있지만 마지막에 정렬 과정을 생략할 수 있다고 판단한다.

 

이제 문제를 이해했으니, MySQL을 올바른 인덱스를 선택하도록 하면된다. 혹여 잘못될 일이 더 있을까?

인덱스 선택이 WHERE절이 아니라 ORDER BY 절에 의해 좌우되고 있으니, 그 결정 요인을 아예 ORDER BY 절 안에 넣어버리면 어떨까? ORDER BY created_at을 ORDER BY channel_id, deleted_at, created_at으로 바꿔도 쿼리 결과는 완전히 동일하다. 왜냐하면 channel_id와 deleted_at은 동등 조건이기 때문이다. 그런데 이제 MySQL은 이렇게 생각한다: “아하, 이제 이 세 칼럼으로 정렬해야 하는구나. 그러면 복합 인덱스를 쓰는 게 낫겠네.”

이는 우리가 원하는바다.

 

SELECT Id FROM Posts WHERE ChannelId = '9tne5g44z7f1zn4z1whebb7jna'
	AND DeleteAt = 0
	AND CreateAt < 1582683608013
	ORDER BY ChannelId, DeleteAt, CreateAt DESC
	LIMIT 1;

 

이는 곧 성공적인 예상 시간으로 응답되었다.

그리고 PR을 그날 보냈다.

 

 

 

두 번째 행동: 재발

몇 달이 흘렀다. 나는 이 일을 거의 잊고 있었다. 그런데 바로 그 고객이 다시 찾아와서, 그 수정 사항이 포함된 릴리스로 업그레이드했더니 모든 게 견딜 수 없을 만큼 느려졌다고 했다. 슬로우 쿼리 로그를 확인해보니 바로 내가 최적화했던 그 쿼리였다. 충격적이면서 동시에 부끄러웠다.

성능이 개선되지 않은 정도가 아니라, 오히려 훨씬 더 나빠진 것이다.

우리는 다시 실행계획을 분석했다:

 

 

 

 

이건 좀 이상했다. 인덱스를 사용하는 대신, 인덱스 머지를 하고 있었다

(Using intersect(idx_posts_channel_id, idx_posts_delete_at).

우리는 올드 쿼리(수정 전)를 실행했는데 이상할게 없었다.

 

예상대로, 이전 쿼리는 idx_posts_created_at만 선택하고 있었는데, 새 쿼리는 완전히 다른 짓을 하기로 결정한 것이었다.

index merge란 서로 다른 인덱스로 여러 개의 범위 스캔을 수행한 뒤, pk값만 가져와서 그 결과를 하나로 합친 후 디스크 IO로 데이터를 쿼리해오는 최적화 기법이다. 행을 합치는 방식은 intersection, union, sort-union이 있다.

MySQL은 기어코 index merge를 고집했다.

 

범인은 delete_at = 0 조건이었다. 특정 컬럼의 문제가 아니라, = 0이라는 조건 자체의 문제였다. delete_at = 0의 검색 공간은? 삭제되지 않은 모든 게시글. 즉 사실상 Posts테이블 전체다!

여기서 옵티마이저가 잘못 판단하고 있었다. 옵티마이저는 쿼리 비용을 교집합 이후의 검색 공간 기준으로 계산했지, 개별 컬럼별 스캔 비용으로 계산하지 않았다. 실제로 index merge를 쓰면 created_at 케이스의 61753639행 대신 195092행만 스캔하면 되는 것처럼 보인다. 아쉽게도 복합 인덱스 케이스의 수치는 없지만, 분명 195092보다는 컸을 것이다. 그래서 MySQL은 19만 5천 행만 스캔하면 된다고 생각했지만, 실제로는 테이블 전체 + 해당 channel_id의 모든 행, 합쳐서 약 1300만 행을 스캔하고 있었던 것이다.

상황은 꽤 암울해 보였다. 남은 선택지는 두어 가지뿐이었다. 옵티마이저 플랜에서 인덱스 머지 옵션을 끄거나. 올바른 인덱스를 선택하도록 강제하거나.

“USE INDEX”는 MySQL의 의사결정을 우리가 덮어써버린다는 문제가 있다 — 그 판단이 매번 옳으리란 보장이 없으니까. 하지만 옵티마이저 스위치 토글은 쿼리 레벨이 아니라 세션 레벨이었고, 모양새도 훌륭했다. 약간의 고민 끝에, USE INDEX를 선택했다.

 

쿼리 성능은 이후 지금까지 안정적으로 유지되고 있다.

이 글에서 단 한가지만 가져가야 한다면 이것이다: 알아보기 어려운 꼼수 최적화는 똑똑해보일 수 있지만, 그 게임은 상대방도 할 줄 안다.

혹시 MySQL이 특정 인덱스를 고르도록 구슬리려는 자신을 발견한다면, FORCE보다 USE INDEX를 쓰는게 더 안전할 수 있다. 그리고 선택도가 낮은 컬럼들을 늘 경계하라.

아니면, 사람들이 자꾸 얘기하는 Postgres라는 DB도 있다던데, 한번 써보는 건 어떨까?

 

 

---

교훈

옵티마이저가 선택도 낮은 인덱스(delete_at)의 진짜 비용을 제대로 반영해지 못해 생긴 문제로 파악된다.

교집합을 "만들어내는 비용"(두 인덱스 스캔의 합 ≈ 1,300만 행)이 진짜 비용인데, 옵티마이저는 교집합의 "결과 크기"를 비용처럼 취급해서 추정이 수십 배 어긋난 것.

비유하면, 두 개의 거대한 명단을 한 줄씩 대조해서 공통 인원을 뽑는 작업인데, "공통 인원이 195명밖에 안 되니까 금방 끝나겠네"라고 판단한 셈. 실제 일의 양은 두 명단의 길이에 비례하는데도 불구하고.

그럼 인덱스는 왜 실제 모든 스캔 비용을 계산하는게 아니라 인덱스 머지 교집합 후 남은 행 수를 기준으로 판단한걸까?

 

>> 나의 결론

 

비용 모델이 '테이블 행 접근 횟수'를 지배적 비용으로 보도록 설계돼 있고, index merge에서 그 횟수가 곧 교집합 결과 크기이기 때문에 그게 사실상 판단 기준이 돼버린 것이다.

index merge 과정은

1. 각 개별 인덱스 조건 절에 해당하는 pk 값을 뽑아온다.

2. 교집합 처리 후 필터링 된 pk로 실제 데이터에 접근한다.



따라서, 실제 테이블 행 접근 횟수는 실제 드는 비용(인덱스 범위 스캔비용이 생략)보다 줄어든 것이다.