특정 오류가 남은 출금 작업이 있는지 job_logs 의 message 를 LIKE '%insufficient fee%' 로 확인하려다 max_execution_time 에 걸렸다. 이 테이블은 수천만 행이고 자주 쓰는 자리였다.
Table of contents
Open Table of contents
증상 — 시간 제한에 걸린 조회
MySQL 5.7 매뉴얼의 B-Tree 인덱스 절이 정한 대로 LIKE 는 인자가 와일드카드로 시작하지 않는 상수일 때만 인덱스를 타므로, 앞에 % 가 붙은 이 검색은 인덱스를 못 탄다. 게다가 조건에 맞는 것만 세는 것이 아니라 기간 안 전체를 분류하는 형태로 짜여 있었다.
수천만 행 전부에 문자열 비교를 돌리니 끝나지 않는 것이 당연했다. 검색 조건 자체가 아니라 그것을 적용하는 대상 수가 문제였다.
판단 기준 — 질문을 다시 잡은 뒤
여기서 무엇을 세려는 것인지를 다시 물었다. 기간 안의 전부가 아니라 아직 안 끝났고 취소도 안 된 작업이 실제로 필요한 대상이었다.
두 가지 방법이 있었다. message 에 전문 인덱스를 새로 거는 것과, 이미 인덱스가 있는 completed_at 과 deleted_at 조건을 먼저 걸어 대상을 좁히는 것이다. 뒤쪽을 골랐다. 이유는 처음 쿼리에 그 상태 조건이 아예 빠져 있었고 실제 질문에는 그 조건이 들어 있어서다. 대신 완료된 작업의 오류는 이 조회로 못 찾는 것을 감수했다.
조치 — 좁히고 나서 훑으면 대상이 준다
completed_at IS NULL AND deleted_at IS NULL 을 먼저 걸어 인덱스로 좁히고 그 안에서만 LIKE 비교를 하게 바꾸니 즉시 결과가 나왔다. EXPLAIN 의 rows 가 수천만에서 소수로 줄었다.
문자열 검색이 느린 것이 아니라 그것을 적용할 대상이 많았던 것이라 조건의 순서가 아니라 선택도의 순서가 문제였다. 옵티마이저가 알아서 하기도 하지만 좁은 조건을 명시해 두는 편이 확실했다.
검증 — 0을 확증하는 방법
결과가 0이었는데 그것이 없다는 뜻인지 안 셌다는 뜻인지 확인이 필요했다. 조건을 하나씩 빼면서 다시 돌려 봤다.
LIKE 조건을 빼면 여러 건이 나왔고 날짜 조건을 빼도 여러 건이 나왔다. 쿼리 자체는 돌고 있고 그 조건이 걸러낸 결과가 0이라는 것이 그렇게 확정됐다.
재시도가 아닌 세대 교체
같은 조사에서 상위 출금을 수정하면 하위 작업이 어떻게 되는지도 확인했다. 기존 작업의 attempt 가 늘어나는 것이 아니라 기존이 취소되고 parent_id 가 같은 새 작업이 생기는 구조였다.
그러면 옛 작업이 남아 있다는 사실 자체가 미처리를 뜻하지 않으므로 deleted_at 을 봐야 한다. 취소된 작업의 cancel_reason 에 상위 수정으로 삭제됐다는 사유가 적혀 있어서 왜 없어졌는지를 다시 조사할 필요가 없었다.
정리
- 앞 와일드카드
LIKE검색은 인덱스를 못 탄다 - 검색 조건이 아니라 적용 대상 수가 문제일 수 있다
- 무엇을 세려는 것인지 다시 물으면 빠진 조건이 나온다
completed_at·deleted_at처럼 인덱스 있는 조건으로 먼저 좁히면 비교 대상이 준다- 조건의 순서가 아니라 선택도의 순서로 짠다
- 0이 나오면 조건을 빼며 쿼리가 돌고 있는지 확인한다
- 재시도가 아니라 세대 교체면 옛 작업의 존재가 미처리가 아니다
cancel_reason을 남기면 다음 사람이 다시 조사하지 않는다