이건 첫 번째 레슨, 복합 인덱스 쓰기

한 학기동안 열심히 후꾸루마꾸루 프로젝트를 말았는데요.

 

끝나고 나니까 개선할게 좀 보이더라구요.

 

 

저희 AI 기반 감정 케어 다이어리 서비스인 "토닥"은 포인트 로그를 조회할 수 있습니다.

 

포인트는 출석해도 주고, 일기 써도 주고, 댓글 써도 주고

사용은 나무 성장 시킬 때 쓸 수 있습니다.

 

그만큼 분류 기준이 많은데, 로그가 많아지면 사용자가 분류기준으로 검색했을 때 시간이 좀 오래 걸릴 거 같더라구요.

 

 

MySQL로 EXPLAIN 분석해보니 역시나 조회하려 하면 Full Table Scan이 발생하네요.

비용이 상당합니다 아주.

 

근데 지금 로그에 여러 칼럼이 있습니다.

user_id, point_type, point_status, created_at 이 있는데, 어떤 순서로 복합 인덱스를 적용해야할까요?

 

이럴 땐? 어떤 조합을 사람들이 많이 사용하는지 보고 많이 사용하는 조합 순서대로 넣는게 좋을 것 같습니다.

 

이럴땐 MySQL 로그를 보고 선택을 해야 할텐데, MySQL에는 여러가지 로그가 있습니다.

 

모든 실행되는 쿼리가 기록되는 General Log

설정된 시간보다 더 느리게 실행되는 경우 기록되는 Slow query Log

MySQL 관련 에러가 기록되는 Error Log

그리고 복제를 위한 Binary Log 그리고 살짝 다른 결이긴 한데 트랜잭션을 위한 Redo Log와 Undo Log가 있습니다.

 

그런데 무슨 로그를 봐야 할지 고민할 필요가 없습니다. 왜냐구요?

 

저희 서비스는 개발자 말곤 사용자가 없거든요.

 

이왕 이렇게 된거 논리적으로 살짝 파고들어가 봅시다. 사용자가 누군지, 포인트를 소비한건지 얻은건지, 무슨 포인트인지, 언제 생성된 로그인지 순서대로 만들어봤습니다. 좀 일리있죠?

CREATE INDEX idx_userid_pointtype_pointstatus_createdat
ON point_log (user_id, point_type, point_status, created_at);

 

여기서 끝내면 좀 아쉽죠. 동적 쿼리로 쿼리 파라미터가 저 순서대로 만들어지게 해보겠습니다.

 

만약에 user_id가 뒤로 가면 인덱스를 못타게 되어 무용지물이 되버릴 겁니다. 그래서 순서를

private Page<PointLog> getPointLogs(String userId, String pointType, String pointStatus, LocalDate startDate, LocalDate endDate, Pageable pageable) {
    Specification<PointLog> spec = Specification.where(null);

    if (userId != null && !userId.isBlank()) {
        spec = spec.and(PointLogSpecifications.hasUserId(userId));
    }
    if (pointType != null && !pointType.isBlank()) {
        spec = spec.and(PointLogSpecifications.hasPointType(PointType.valueOf(pointType)));
    }
    if (pointStatus != null && !pointStatus.isBlank()) {
        spec = spec.and(PointLogSpecifications.hasPointStatus(PointStatus.valueOf(pointStatus)));
    }
    if (startDate != null && endDate != null) {
        LocalDateTime start = startDate.atStartOfDay();
        LocalDateTime end = endDate.plusDays(1).atStartOfDay().minusNanos(1);
        spec = spec.and(PointLogSpecifications.createdAtBetween(start, end));
    }

    return pointLogRepository.findAll(spec, pageable);
}

private class PointLogSpecifications {
    public static Specification<PointLog> hasUserId(String userId) {
        return (root, query, cb) -> cb.equal(root.get("member").get("userId"), userId);
    }

    public static Specification<PointLog> hasPointType(PointType pointType) {
        return (root, query, cb) -> cb.equal(root.get("pointType"), pointType);
    }

    public static Specification<PointLog> hasPointStatus(PointStatus pointStatus) {
        return (root, query, cb) -> cb.equal(root.get("pointStatus"), pointStatus);
    }

    public static Specification<PointLog> createdAtBetween(LocalDateTime start, LocalDateTime end) {
        return (root, query, cb) -> cb.between(root.get("createdAt"), start, end);
    }
}

 

이런 식으로 동적 쿼리로 만들어주면 우리가 원하는 순서대로 쿼리가 만들어질겁니다.

글고 없는 파라미터도 자동으로 빼주겠죠.

 

이제 다시 EXPLAIN 분석을 해봐야겠죠?

 

결과적으로 스캔 타입이 All에서 Range로 변경되고

 

Query Cost와 스캔 행 수가 99% 이상 줄어들었습니다.

 

예~~~~~~~~~

 

네

마치겠습니다