[Spring Batch] JdbcPagingItemReader의 페이징은 offset을 사용하지 않는다

정확히는 MySQL에서 PagingQueryProvider와 JdbcPagingItemReader을 함께 사용할때 페이징은 offset을 사용하지 않는다 입니다.

문제 상황으로는 JdbcPagingItemReader를 통해 조회를 할때, SELECT 절에 별칭을 주었는데 정상적으로 읽지 못하는 문제가 발생했습니다. 해당 이슈를 해결하면서 알게된 사실을 공유하려고 합니다.

(직접 코드를 기반으로 추정한 것이기에 다른 의견이 있으시다면 댓글을 남겨주세요)

 

JdbcPagingItemReader

@Bean
@StepScope
public JdbcPagingItemReader<Point> reader() {

    return new JdbcPagingItemReaderBuilder<Point>()
            .name("reader")
            .pageSize(chunkSize)
            .fetchSize(chunkSize)
            .dataSource(datasource)
            .rowMapper(pointRowMapper)
            .parameterValues(parameters)
            .queryProvider(pagingQueryProvider())
            .build();
}

@Bean
public PagingQueryProvider pagingQueryProvider() {

    SqlPagingQueryProviderFactoryBean queryProvider = new SqlPagingQueryProviderFactoryBean();
    queryProvider.setDataSource(datasource);
    queryProvider.setSelectClause("place_id, user_id as member_id, points");
    queryProvider.setFromClause("user_point");
    queryProvider.setWhereClause("place_id = :place_id");
    queryProvider.setSortKeys(sortKeyAsc("place_id", "member_id"));

    try {
        return queryProvider.getObject();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

위와 같은 PagingQueryProvider를 통해 페이징 쿼리를 만든다면 어떤 쿼리가 만들어져 나갈것으로 예상이 될까요?

SELECT place_id, user_id as member_id, point 
FROM user_point 
WHERE (user_point.place_id = ?) 
ORDER BY place_id ASC, member_id ASC 
OFFSET ?
LIMIT 100

이런식으로 offset기반으로 페이징이 나갈 것으로 예상을 할 것같은데요. 왜냐하면 JdbcPagingItemReader는 offset, limit기반으로 페이지네이션을 한다고 알고있기 때문입니다. 그래서 성능을 높이려면 cursorPagingItemReader나 ItemReader를 커스텀하게 구현하여 noOffset기반으로 사용하기도 합니다.

 

그런데 실제 위 reader가 실행되면 아래와 같은 쿼리가 나가게 됩니다. 🤨🤨

SELECT place_id, user_id as member_id, point 
FROM user_point 
WHERE (user_point.place_id = ?) 
AND ((place_id > ?) OR (place_id = ? AND member_id > ?)) 
ORDER BY place_id ASC, member_id ASC 
LIMIT 100

문제 상황

우선 위 상황을 알게 된 이유를 보자면, queryProvider에서 select절에 as 별칭을 주어 사용하고 있었습니다.

일반적으로 SQL에서는 쿼리 순서상 SELECT이후 ORDER BY절을 타므로 user_id에 member_id로 별칭을 주고 member_id를 정렬의 기준으로 사용했습니다.

그런데 point테이블에서 member_id를 찾을 수 없다는 에러가 나옵니다..!

별칭을 주었는데 ORDER BY에서 읽지를 못한다라...여기서부터 시작되었습니다.

 

DIG

public abstract class AbstractSqlPagingQueryProvider implements PagingQueryProvider {
...
    public Map<String, Order> getSortKeysWithoutAliases() {
        Map<String, Order> sortKeysWithoutAliases = new LinkedHashMap();
        Iterator var2 = this.sortKeys.entrySet().iterator();

        while(var2.hasNext()) {
            Map.Entry<String, Order> sortKeyEntry = (Map.Entry)var2.next();
            String key = (String)sortKeyEntry.getKey();
            int separator = key.indexOf(46);
            if (separator > 0) {
                int columnIndex = separator + 1;
                if (columnIndex < key.length()) {
                    sortKeysWithoutAliases.put(key.substring(columnIndex), (Order)sortKeyEntry.getValue());
                }
            } else {
                sortKeysWithoutAliases.put((String)sortKeyEntry.getKey(), (Order)sortKeyEntry.getValue());
            }
        }

        return sortKeysWithoutAliases;
    }
}

DB에 따라 이를 구현한 PagingQueryProvider들이 여러개 있습니다. SqlPagingQueryProviderFactoryBean 클래스를 확인하면 여러 PagingQueryProvider를 확인할 수 있고, 저는 Mysql을 사용하기 때문에 MySqlPagingQueryProvider 를 확인해봐야 합니다. (실제 동작할때는 datasource에 맞춰서 SqlPagingQueryProviderFactoryBean이 알맞은 queryProvider 실행해줍니다.)

MySqlPagingQueryProvider

public class MySqlPagingQueryProvider extends AbstractSqlPagingQueryProvider {
    public MySqlPagingQueryProvider() {
    }

    public String generateFirstPageQuery(int pageSize) {
        return SqlPagingQueryUtils.generateLimitSqlQuery(this, false, this.buildLimitClause(pageSize));
    }

    public String generateRemainingPagesQuery(int pageSize) {
        return StringUtils.hasText(this.getGroupClause()) ? SqlPagingQueryUtils.generateLimitGroupedSqlQuery(this, this.buildLimitClause(pageSize)) : SqlPagingQueryUtils.generateLimitSqlQuery(this, true, this.buildLimitClause(pageSize));
    }

    private String buildLimitClause(int pageSize) {
        return "LIMIT " + pageSize;
    }
}

## generateFirstPageQuery

첫 페이징 쿼리부터 확인해보겠습니다. ORDER BY절은 단순히 sortKeys를 사용해서 order by 구문을 만드는 곳입니다. 중요한 곳은 buildWhereClause 입니다.

public static String generateLimitSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, String limitClause) {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT ").append(provider.getSelectClause());
    sql.append(" FROM ").append(provider.getFromClause());
    buildWhereClause(provider, remainingPageQuery, sql);
    buildGroupByClause(provider, sql);
    sql.append(" ORDER BY ").append(buildSortClause(provider));
    sql.append(" ").append(limitClause);
    return sql.toString();
}

 

### buildWhereClause

내부의 buildSortConditions를 더 봐바야 할것 같습니다.

private static void buildWhereClause(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, StringBuilder sql) {
    if (remainingPageQuery) {
        sql.append(" WHERE ");
        if (provider.getWhereClause() != null) {
            sql.append("(");
            sql.append(provider.getWhereClause());
            sql.append(") AND ");
        }

        buildSortConditions(provider, sql);
    } else {
        sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
    }

}

### buildSortConditions

분명 buildWhereClause로 where절을 만드는 곳이라고 생각되었는데 SortConditions도 만드는 것을 볼 수 있습니다. 여기서 직접 페이징에 필요한 방식을 offset이 아닌 where절로 만드는 것을 확인할 수 있습니다.

public static void buildSortConditions(AbstractSqlPagingQueryProvider provider, StringBuilder sql) {
    List<Map.Entry<String, Order>> keys = new ArrayList(provider.getSortKeys().entrySet());
    List<String> clauses = new ArrayList();

    String prefix;
    for(int i = 0; i < keys.size(); ++i) {
        StringBuilder clause = new StringBuilder();
        prefix = "";

        for(int j = 0; j < i; ++j) {
            clause.append(prefix);
            prefix = " AND ";
            Map.Entry<String, Order> entry = (Map.Entry)keys.get(j);
            clause.append((String)entry.getKey());
            clause.append(" = ");
            clause.append(provider.getSortKeyPlaceHolder((String)entry.getKey()));
        }

        if (clause.length() > 0) {
            clause.append(" AND ");
        }

        clause.append((String)((Map.Entry)keys.get(i)).getKey());
        if (((Map.Entry)keys.get(i)).getValue() != null && ((Map.Entry)keys.get(i)).getValue() == Order.DESCENDING) {
            clause.append(" < ");
        } else {
            clause.append(" > ");
        }

        clause.append(provider.getSortKeyPlaceHolder((String)((Map.Entry)keys.get(i)).getKey()));
        clauses.add(clause.toString());
    }

    sql.append("(");
    String prefix = "";
    Iterator var10 = clauses.iterator();

    while(var10.hasNext()) {
        prefix = (String)var10.next();
        sql.append(prefix);
        prefix = " OR ";
        sql.append("(");
        sql.append(prefix);
        sql.append(")");
    }

    sql.append(")");
}

이처럼 Keyset Pagination 또는 noOffset과 같은 방식으로 구현되어있습니다.

아무래도 이러한 이유로 spring batch docs에서도 고유한 sortKey를 사용하라는 것 같습니다. sortKey를 기반으로 정렬을 할때는 unique가 중요하기 때문입니다.

unique sortKey

 

결론

1. JdbcPagingItemReader는 offset기반 페이지네이션이 아닌 noOffset기반 페이지네이션으로 처리하고 있었다.

2. 따라서 queryProvider에 alias로 별칭을 주면, 해당 별칭의 네이밍으로 where절에 조건에 들어가므로 컬럼을 찾을 수 없다는 예외가 발생할 수 있다.