MySQL IN 절 결과 정렬 이슈

다음 SQL을 실행한다고 가정해보자:

SELECT * FROM table WHERE id IN (3,6,9,1,2,5,8,7);

이 경우 반환되는 결과는 일반적으로 id 기준 오름차순(1,2,3,4,5,6,7,8,9)으로 정렬된다. 만약 IN 절에 명시한 순서대로 결과를 받고 싶다면 어떻게 해야 할까? MySQL은 이를 위한 함수를 제공한다.

FIELD() 함수를 이용한 정렬

FIELD() 함수를 ORDER BY 절에 사용하면 원하는 순서로 결과를 정렬할 수 있다.

SELECT * FROM table WHERE id IN (3,6,9,1,2,5,8,7) ORDER BY FIELD(id,3,6,9,1,2,5,8,7);

또 다른 방법으로 FIND_IN_SET() 함수도 사용 가능하다:

SELECT * FROM table WHERE id IN (3,6,9,1,2,5,8,7) ORDER BY FIND_IN_SET(id,'3,6,9,1,2,5,8,7');

성능 고려사항

EXPLAIN 명령어로 확인해보면, FIELD()나 FIND_IN_SET()을 사용한 ORDER BY는 Using filesort를 유발하여 성능이 저하된다. 따라서 대용량 데이터에서는 애플리케이션 레벨에서 정렬하는 것이 더 효율적일 수 있다.

자바에서 스트림을 이용한 정렬 예시

List<Employee> empList = new ArrayList<>();
empList.add(new Employee(123, "Jack", "Johnson", LocalDate.of(1988, Month.APRIL, 12)));
empList.add(new Employee(345, "Cindy", "Bower", LocalDate.of(2011, Month.DECEMBER, 15)));
empList.add(new Employee(567, "Perry", "Node", LocalDate.of(2005, Month.JUNE, 7)));
empList.add(new Employee(467, "Pam", "Krauss", LocalDate.of(2005, Month.JUNE, 7)));
empList.add(new Employee(435, "Fred", "Shak", LocalDate.of(1988, Month.APRIL, 17)));
empList.add(new Employee(678, "Ann", "Lee", LocalDate.of(2007, Month.APRIL, 12)));

empList = empList.stream()
    .sorted(Comparator.comparing(Employee::getHireDate))
    .collect(Collectors.toList());

실제 사례: 앨범-컨텐츠 관계에서의 최적화

문제 상황

• 앨범 테이블(A): 3,000건
• 컨텐츠 테이블(C): 20,000건 (C.a_id가 A.id를 참조)
• 페이징된 앨범 목록에 각 앨범의 컨텐츠 개수(ContentSize)를 표시해야 함

접근 방식 1: LEFT JOIN + GROUP BY + ORDER BY FIELD

-- 비효율적인 쿼리
SELECT COUNT(c.id) 
FROM abm_album a 
LEFT JOIN abm_album_content c ON a.id = c.album_id AND c.is_deleted = 0
WHERE a.id IN (5138822, 5160757, 5000142, 5160750, 5159885)
GROUP BY a.id
ORDER BY FIELD(a.id, 5138822, 5160757, 5000142, 5160750, 5159885);

단점: LEFT JOIN으로 인한 성능 저하, Using filesort 발생.

접근 방식 2: 단일 테이블 GROUP BY + 애플리케이션 매핑 (권장)

-- 효율적인 쿼리
SELECT c.album_id, COUNT(c.id) 
FROM abm_album_content c
WHERE c.is_deleted = 0 AND c.album_id IN (5138822, 5160757, 5000142, 5160750, 5159885)
GROUP BY c.album_id;

자바 코드 처리

List<Album> datas = albumRepository.find(album, page.getStart(), page.getPageSize(), sort);

if (datas != null && !datas.isEmpty()) {
    try {
        List<Integer> ids = datas.stream()
            .map(Album::getId)
            .collect(Collectors.toList());

        List<Object[]> countResults = albumContentRepository
            .countMapByAlbumIdsAndIsDeleted(AlbumConstant.UNDELETED, ids);

        // HashMap으로 변환
        Map<Integer, BigInteger> countMap = new HashMap<>();
        countResults.forEach(row -> countMap.put((Integer) row[0], (BigInteger) row[1]));

        // 각 앨범에 컨텐츠 개수 설정
        datas.forEach(data -> {
            BigInteger cnt = countMap.get(data.getId());
            data.setContentsCount(cnt != null ? cnt.intValue() : 0);
        });

    } catch (Exception e) {
        log.error("앨범별 컨텐츠 개수 조회 오류", e);
    }
}
page.setDatas(datas);

핵심 포인트

1. IN 결과 정렬: IN 절에 명시된 순서로 결과를 얻으려면 ORDER BY FIELD() 또는 FIND_IN_SET()을 사용하되, 성능 저하 가능성에 유의하라.
2. 데이터 무결성: IN 절에 포함된 ID 중 실제 존재하지 않는 데이터가 있으면 반환되는 행 수가 다를 수 있다. 이 경우 애플리케이션에서 HashMap 등을 활용하여 누락된 ID를 기본값(0 등)으로 처리하는 것이 안전하다.
3. 단일 테이블 우선: 가능하다면 조인보다 단일 테이블에서 필요한 데이터를 조회하고, 애플리케이션 레벨에서 매핑 및 정렬을 처리하는 것이 성능상 유리하다.