쿼리를 합쳤는데, 왜 안 빨라졌을까

Об этом предложении

DB 조회 4번을 2번으로 줄였다. 당연히 빨라졌을 줄 알았다. 실제로 재 보니 작은 현장은 빨라졌는데 큰 현장은 그대로였다. 범인은 조회 횟수가 아니라 인덱스였다. 어떤 서버냐면 건설 현장 출입구에 얼굴 인식 단말기가 있다. 근로자가 얼굴을 인식하면 단말기가 "누가, 언제 인증했는지"를 서버로 보내고, 서버는 그걸 DB에 저장한다. 하루에 4만 건 넘게 들어온다. 그런데 단말기는 근로자를 PIN 번호로만 안다. 그래서 서버는 기록이 올 때마다 "이 현장의 이 PIN이 누구지?"를 DB에서 찾아야 한다. 이번 이야기는 이 찾는 부분이다. 조회가 최대 4번씩 나가고 있었다 이 서버를 넘겨받고 코드를 열어 보니, 근로자 한 명을 찾는 데 조회가 여러 번 나가고 있었다. for (AttendanceInput in : records) { int registered = mapper.countWorkersByPin(in); // 1 이 현장에 등록된 PIN인가 if (registered == 0) { hold(in); continue; } WorkerMatch existing = mapper.findExistingLog(in); // 2 같은 기록이 이미 있나 WorkerMatch active = mapper.findActiveWorker(in); // 3 근무 중인 근로자가 있나 if (existing != null) save(existing); else if (active == null) save(mapper.findLatestWorker(in)); // 4 else save(mapper.findActiveWorkerDetail(in)); // 4 } 거슬리는 게 세 가지 있었다. 2에서 기록을 찾으면 3의 결과는 안 쓰는데, 3은 항상 실행된다. 비슷한 코드가 여기저기 복사돼 있어서, 이 조회를 부르는 곳이 12군데였다. 사진이나 일 집계가 이미 저장됐는지는 확인하지 않아서, 단말기가 같은 기록을 다시 보내면(재전송) 저장 단계까지 그대로 내려갔다. 그래서 하나로 합쳤다 2~4를 조회 하나로 합치고, 이미 저장된 게 뭔지도 같이 가져오게 했다. for (AttendanceInput in : records) { int registered = mapper.countWorkersByPin(in); // 1 그대로 if (registered == 0) { hold(in); continue; } WorkerMatch m = mapper.findWorkerWithStatus(in); // 통합 조회 (근로자 + 이미 저장된 것들) if (m.alreadyProcessed()) continue; // 이미 처리한 기록은 건너뜀 save(m); } 조회는 최대 4번에서 2번으로, 호출하는 곳은 12군데에서 1군데로 줄었다. 조회가 반으로 줄었으니 당연히 빨라졌겠지..... 진짜 빨라졌는지 재 봤다 이 작업을 정리하다가 문득 걸렸다. 조회 횟수를 센 적은 있어도 속도를 잰 적은 없었다. 그래서 옛 조회들과 통합 조회를 같은 입력으로 EXPLAIN (ANALYZE, BUFFERS) 에 넣어 돌려 봤다. 운영 DB라서 읽기 전용 트랜잭션 안에서만 실행했다. 실행 계획에서 본 건 두 가지다. Buffers : DB가 읽은 페이지 수. 많이 읽을수록 느리다. Filter : 인덱스로 바로 찾지 못하고, 일단 읽은 다음 걸러 낸 조건. 여기 있으면 쓸데없는 행까지 읽는다. 현장마다 근로자 수가 크게 달라서, 작은 현장(400명), 중간 현장(850명), 큰 현장(2,610명)을 하나씩 골라 비교했다. 결과: 작은 현장만 빨라졌다 작은 현장은 읽는 양이 4분의 1로 줄었다(새 기록 기준). 중간 현장도 줄었다. 그런데 큰 현장은 비슷하거나, 재전송 상황에서는 오히려 더 많이 읽었다. 조회를 합쳤는데 왜 큰 현장만 이럴까? 범인은 인덱스였다 옛 조회의 실행 계획을 열어 보니 이런 게 있었다. Bitmap Heap Scan on worker Recheck Cond: (site_id = '<현장>') ← 현장까지는 인덱스로 찾고 Filter: (pin = '<PIN>') ← PIN은 다 읽은 다음 걸러 냄 Rows Removed by Filter: 849 ← 850명 읽어서 849명 버림 근로자 한 명 찾으려고 현장 근로자 전원을 읽고 있었다. 팀 테이블은 더 심해서, 팀 26개를 찾으려고 6만 행을 통째로 읽었다. 이유는 단순했다. pin 이 들어간 인덱스가 어느 테이블에도 없었다. 통합 조회는 팀 테이블을 안 거쳐서 그 낭비는 사라졌다. 작은 현장이 빨라진 이유다. 대신 근태 로그를 뒤지는 부분이 새로 생겼는데, 여기서도 pin 은 Filter였다. 근태 로그는 현장이 클수록 많으니, 큰 현장에서는 새로 생긴 비용이 사라진 비용보다 커져 버렸다. 그래서 인덱스를 제안했다 원인이 pin 인덱스라는 건 분명했다. 근로자와 근태 로그 테이블에 이런 인덱스가 있으면 pin 이 Filter가 아니라 Index Cond로 처리될 것이다. CREATE INDEX ix_worker_site_pin ON worker (site_id, pin); CREATE INDEX ix_attendance_log_site_pin ON attendance_log (site_id, pin); 평소 인덱스는 직접 추가하는 편인데, 이번에는 그러지 않았다. 근태 로그는 1억 건이 넘고 단말기 기록이 하루 종일 쓰이는 테이블이다. 인덱스를 만드는 동안 쓰기가 막히거나 이후 저장이 느려지면 근태 수신 전체가 영향을 받는다. 그래서 혼자 넣지 않고 DB를 관리하는 CTO님께 말씀드렸다. 통합 조회는 그대로 두었다. 이미 처리한 기록을 걸러 내는 확인이 이 조회에 들어 있고, 12군데 흩어져 있던 코드를 다시 흩어 놓을 이유는 없었다. 정리 조회를 합친 건 성능보다 코드 정리에 더 의미가 있었다. 12군데 흩어진 호출을 한 곳으로 모았고, 이미 처리한 기록을 한 번에 걸러 낼 수 있게 됐다. 성능의 열쇠는 인덱스였다. 실행 계획은 "인덱스를 쓰나"보다 "몇 행 읽고 몇 행 버리나"를 봐야 한다. 인덱스를 쓰고 있었는데도 850행을 읽고 849행을 버리고 있었다. 한 곳만 쟀으면 결론이 틀렸을 거다. 작은 현장만 쟀으면 "4분의 1로 줄었다", 큰 현장만 쟀으면 "오히려 느려졌다"고 썼을 거다.