MySQL/MariaDB, WHERE IN 조건 슬로우 쿼리 현상 해결하기

개요

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

확인된 이슈

• 인덱스가 생성되어 있는 컬럼에 대해 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 환경에서의 제약

• JPA는 UNION ALL을 지원하지 않아 동적으로 변화하는 Dynamic SQL을 Native 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()
}

참고 글

• Stackoverflow - Selecting Non-existent Data With MySQL
• Stackoverflow - What is the performance difference between IN (42) and id = 42 in MySQL?