ORDER BY rand() 쿼리문 튜닝하기!

 

 

 

지난 프로젝트동안 감사하게도 무려 카카오에 재직중이신 Jack에게 코드리뷰를 받을 기회가 있었습니다.

받았던 코멘트 중, 기능 구현에 급급해 당시에는 넘어갔던 내용 중 다시 살펴보니 흥미로운 내용을 공유해보려고 합니다.

 

 

 

---

 

 

링크 : https://weicomes.tistory.com/322

 

음식상품 데이터를 랜덤으로 추출하는 API를 클라이언트쪽에 제공하고 있었습니다.

초기 데이터를 선택해서 뿌려주는 로직이라 위와 같이 ORDER BY RAND() LIMIT 3 이라는 쿼리문을 작성했고, 잘 작동했습니다.

그런데 위 쿼리는 큰 문제가 있습니다. ORDER BY 의 조건에 RAND()라는 함수가 걸려있어 스토리지 엔진이 비효율적으로 작동하기 때문입니다.

RAND() 함수는 쿼리 실행 순간에 레코드에 각각 임의의 값을 할당한 후에 그 할당된 값으로 정렬을 수행하게 됩니다. 비용이 아주 큰 연산이 됩니다.

더 자세하게는, ORDER BY 조건에 인덱스를 활용할 수 있는 조건이 아닌 모든 조건은 비효율적으로 작동하게 됩니다.

 

 

 

다음 예를 보면,

@Entity
@Data
public class Item_rand {
    @Id @GeneratedValue
    private Long id;
    private String name;
}

@Repository
public interface RandRepository extends CrudRepository<Item_rand, Long> {

    @Query(value = "select * from item order by rand()", nativeQuery = true)
    List<Item_rand> findAll();

}

엔티티 클래스가 있고 해당 엔티티를 랜덤으로 가져오는 메서드가 있습니다.

이 로직에서 만약 item 테이블의 레코드가 1억개가 넘는다면 어떻게 될까요?

1억개의 데이터에 1억번의 데이터 할당과 정렬을 수행하게 됩니다. 사실상 실무에서 쓸 수 없는 코드라는 짐작이 가실 겁니다.

 

 

이 문제를 개선해보겠습니다.

@Entity
@Data
public class Item_rand_improved {

    @Id @GeneratedValue
    private Long id;
    private String name;
    private int randId; // 여기에 인덱스 생성
}

테이블(엔티티)에 rand_id 라는 속성을 추가했습니다.

테이블에 데이터를 삽입할 때, rand_id 속성에 랜덤한 값이 들어가도록 설정해줍니다. (대신 속성이 int형이므로 100000000을 곱한 값을 삽입합니다)

-> 각 레코드가 랜덤한 값을 가집니다

그럼 이제 ORDER BY RAND() 에서 랜덤값을 레코드의 개수만큼 할당하는 문제는 해결되었습니다. 이미 랜덤값이 할당되어 있으니까요.

 

 

쿼리도 개선해보겠습니다

@Repository
public interface RandRepository extends CrudRepository<Item_rand, Long> {

    @Query(value = "select * from item_rand_improved where rand_id > FLOOR((RAND()*100000000)) order by rand_id limit 3", nativeQuery = true)
    List<Item_rand_improved> findAll_improved();

}

rand()에 또 한번 100000000을 곱한 값을 where 조건에 넣어 필터링한 후, order by 에 아까 삽입한 인덱스가 있는 rand_id 컬럼을 조건으로 넣어줍니다.

 

 

완성!​