티스토리 뷰

개요

  • 최근 전에 경험하지 못한 슬로우 쿼리 이슈를 해결하면서 허탈한 마음과 함께 정리하였다.

확인된 이슈

  • 인덱스가 생성되어 있는 컬럼에 대해 2개 이상의 복수개의 파라메터를 IN 조건으로 조회하면 무조건 10초 이상의 슬로우 쿼리가 발생한다. 대상 테이블은 약 2천만개의 로우를 가진 파티셔닝 되지 않은 테이블이다.
# 파라메터가 1개일 경우
# 슬로우 쿼리가 발생하지 않음, 1ms 소요
SELECT *
 FROM foobar
WHERE id IN (1);

# 파라메터가 2개 이상일 경우
# 슬로우 쿼리가 발생, 최소 10초 이상 소요
SELECT *
 FROM foobar
WHERE id IN (1, 2);
  • 파라메터가 1개일 때만 인덱스를 사용하고, 파라메터가 2개 이상이면 테이블이 무조건 풀스캔된다. USE INDEX (PRIMIARY) 절로 인덱스 사용을 강제해도 슬로우 쿼리는 동일하게 발생한다.
  • 특정 테이블에 국한된 이슈가 아니고, 여러 테이블에서 전반적으로 동일한 현상이 발생한다.

해결책: INNER JOIN

  • 해결책은 WHERE IN 조건을 사용하지 않고, 아래와 같이 대상 파라메터 목록을 UNION ALL을 이용하여 서브 쿼리를 만들고, 서브 쿼리를 INNER JOIN 하는 것이다.
SELECT f.*
  FROM foobar f
 INNER JOIN (
    SELECT 1 AS id
    UNION ALL SELECT 2 AS id
 ) i ON f.id = i.id;
  • MySQL 8.0.19부터는 VALUES를 지원하여 아래처럼 위와 동일한 결과의 쿼리를 작성할 수 있다. [관련 링크]
SELECT f.*
  FROM foobar f
 INNER JOIN (
    VALUES ROW(1), ROW(2)
 ) i ON f.id = i.column_0;
  • INNER JOIN을 사용하는 방식은 SELECT 뿐만 아니라 DELETE에도 동일하게 적용된다. [관련 링크]
DELETE f
  FROM foobar f
 INNER JOIN (
    VALUES ROW(1), ROW(2)
 ) i ON f.id = i.column_0;

결론

  • WHERE IN 절을 JOIN으로 교체하면 드라마틱한 속도 개선을 누릴 수 있다.

JVM 환경에서의 제약

  • JPAUNION ALL을 지원하지 않아 동적으로 변화하는 Dynamic SQLNative Query로 작성해야 한다. 특정 컬럼 값만 조회하는 것은 큰 번거로움 없이 가능하지만, 엔티티 목록을 조회할 경우 RowMapper를 정의해야 하기 때문에 큰 번거로움이 따르게 된다.
  • 해당 이슈에 대한 INNER JOIN 절은 상당히 자주 사용되기 때문에 따로 기능을 만들어두면 편리하다. Kotlin으로는 아래와 같이 만들어 사용하면 된다.
@JvmStatic
@NotNull
fun generateNativeSqlWhereInClauseUsingUnionAllAndInnerJoin(tableDotColumnName: String, parameters: List<Long>): String {

    if (parameters.isEmpty()) return " "

    val where = StringBuilder(" INNER JOIN ( ")
    where.append(" SELECT ${parameters.first()} AS parameter ")
    if (parameters.size > 1) {
        (1..where.lastIndex).forEach {
            where.append(" UNION ALL SELECT ${parameters[it]} AS parameter")
        }
    }
    where.append(" ) p ON $tableDotColumnName = p.parameter ")

    return where.toString()
}

참고 글

댓글
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
링크
«   2024/03   »
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
글 보관함