단순한 SELECT가 느린 이유 -- 하드 파싱과 소프트 파싱

단순한 SELECT가 왜 느리지?#

운영 중인 서비스에서 슬로우 쿼리 로그(2초 이상)를 모니터링하고 있었는데, 이상한 게 하나 잡혔습니다. 쿼리 자체는 단순한 SELECT인데 느린 경우가 있었습니다.

실행 계획을 까봤습니다. 인덱스를 잘 타고 있었고, 데이터 양도 많지 않았습니다. 쿼리 자체의 문제는 아니었습니다.

그런데 슬로우 쿼리 로그를 더 뒤져보니 패턴이 보였습니다. 동일한 구조의 쿼리가 파라미터만 바뀌면서 대량으로 들어오고 있었습니다.

SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
-- ... 수백 건

라이브러리 캐시를 확인해보니 유사한 SQL이 수천 개 쌓여 있었습니다. 원인은 바인딩 변수를 쓰지 않아서 매번 하드 파싱을 타고 있었던 것이었습니다.

DB가 SQL을 실행하는 두 가지 경로#

DB가 SQL을 받으면 먼저 라이브러리 캐시(Library Cache)에서 동일한 SQL이 있는지 찾습니다. 여기서 두 갈래로 나뉩니다.

소프트 파싱 -- 캐시 히트#

캐시에 같은 SQL이 있으면 기존 실행 계획을 그대로 재사용합니다. 옵티마이저를 거치지 않으니 빠릅니다.

하드 파싱 -- 캐시 미스#

캐시에 없으면 처음부터 다 해야 합니다:

1. 구문 분석: SQL 텍스트를 파싱해서 문법 오류 확인
2. 의미 분석: 참조하는 테이블, 컬럼이 실제로 존재하는지 확인
3. 실행 계획 수립: 옵티마이저가 테이블 통계, 인덱스 정보, 조인 순서 등을 전부 고려해서 최적의 실행 경로를 계산
4. 캐시 저장: 생성된 실행 계획을 라이브러리 캐시에 저장

이 중 3번이 무겁습니다. 옵티마이저는 가능한 실행 경로를 비교해서 비용이 가장 낮은 걸 고르는데, 테이블이 여러 개 조인되면 경우의 수가 기하급수적으로 늘어납니다. 단순 SELECT라도 이 과정을 매번 반복하면 CPU 사용률이 올라갑니다.

참고로 Oracle은 Shared Pool 내의 Library Cache에서 Parent Cursor(SQL 텍스트)와 Child Cursor(실행 계획, 바인딩 변수 정보)를 2단계 구조로 관리합니다. 소프트 파싱은 이 Parent Cursor를 찾아서 Child Cursor의 실행 계획을 재사용하는 것입니다. (Oracle SQL Tuning Guide - SQL Processing)

SQL 텍스트가 식별자다#

여기서 핵심적인 건, DB가 "같은 SQL인지"를 판단하는 기준입니다. SQL에는 이름이 없습니다. SQL 텍스트 자체가 식별자 역할을 합니다.

Oracle 공식 문서에 따르면, SQL 텍스트에 해싱 알고리즘을 적용해서 SQL ID를 생성하고, 해시가 일치하면 character-for-character 비교를 수행합니다.

SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;

사람이 보면 같은 쿼리에 파라미터만 다른 건데, DB 입장에서는 텍스트가 다르니까 완전히 다른 SQL입니다. 각각 별도로 하드 파싱을 탑니다.

대소문자나 공백이 달라도 다른 SQL로 인식됩니다:

SELECT * FROM users WHERE id = 1;
select * from users where id = 1;  -- 다른 SQL로 취급
SELECT *  FROM users WHERE id = 1; -- 공백 하나 차이, 다른 SQL

Oracle 문서 원문을 보면 이렇게 써져 있습니다:

For a textual match to occur, the text of the SQL statements or PL/SQL blocks must be character-for-character identical, including spaces, case, and comments.

이게 왜 문제가 되냐면, 사용자가 1000명이고 각각 다른 id로 조회하면 1000개의 서로 다른 SQL이 라이브러리 캐시에 쌓입니다. 캐시 공간은 유한하기 때문에 새로운 SQL이 들어올 때마다 오래된 실행 계획이 밀려납니다. 자주 쓰는 SQL의 실행 계획까지 밀려나면 그 SQL도 다시 하드 파싱을 해야 하고, 연쇄적으로 성능이 떨어집니다.

트래픽이 몰리는 시간대에 이런 일이 생기면 단순한 SELECT조차 제대로 처리가 안 되는 상황이 벌어집니다. 제가 만난 케이스가 정확히 이거였습니다.

바인딩 변수로 해결#

SELECT * FROM users WHERE id = :userId;

:userId 자리에 어떤 값이 들어오든 DB는 이걸 같은 SQL로 인식합니다. 최초 1회만 하드 파싱하고, 이후에는 전부 소프트 파싱으로 처리됩니다.

실제로 문제가 됐던 쿼리를 바인딩 변수로 전환한 이후, 해당 쿼리가 슬로우 쿼리 로그에서 사라졌습니다. 쿼리 자체는 전혀 안 바꿨고, 파라미터 전달 방식만 바꾼 건데 효과가 바로 나타났습니다.

Spring에서의 적용#

Spring Data JPA를 쓰면 대부분 자동으로 바인딩 변수가 적용됩니다.

자동으로 되는 경우#

메서드 이름 쿼리는 내부적으로 JPQL의 위치 기반 파라미터(?1, ?2)로 변환되고, Hibernate가 이를 JDBC PreparedStatement의 ? 파라미터로 변환합니다. (Spring Data JPA - Query Methods)

// 내부적으로 WHERE name = ?1 로 변환 -> PreparedStatement 바인딩
List<Product> findByName(String name);

@Query + @Param도 마찬가지입니다:

@Query("SELECT p.id FROM Product p WHERE p.subCategory.category.name = :categoryName")
List<Long> findIdsByCategoryName(@Param("categoryName") String categoryName);

:categoryName이 바인딩 변수입니다. 어떤 카테고리명이 들어오든 DB는 이걸 같은 SQL로 인식합니다.

주의해야 하는 경우#

네이티브 쿼리를 직접 작성할 때 문자열 결합으로 파라미터를 넣으면 하드 파싱이 매번 발생합니다. SQL 인젝션 위험도 있습니다.

// 하드 파싱 매번 발생 + SQL 인젝션 위험
@Query(value = "SELECT * FROM product WHERE name = '" + name + "'", nativeQuery = true)
 
// 바인딩 변수 사용
@Query(value = "SELECT * FROM product WHERE name = :name", nativeQuery = true)
List<Product> findByName(@Param("name") String name);

EntityManager.createNativeQuery로 동적 쿼리를 조합할 때도 마찬가지입니다. setParameter()를 쓰면 바인딩 변수가 적용되지만, 문자열 결합으로 값을 끼워넣으면 하드 파싱을 탑니다.

MyBatis에서의 # vs $#

MyBatis를 쓰는 환경이라면 #{}과 ${}의 차이를 알아야 합니다. (MyBatis 3 - Mapper XML Files)

<!-- #{} -- PreparedStatement 바인딩 변수, 소프트 파싱 가능 -->
<select id="findById" resultType="User">
    SELECT * FROM users WHERE id = #{userId}
</select>
 
<!-- ${} -- 문자열 치환, 매번 하드 파싱 -->
<select id="findById" resultType="User">
    SELECT * FROM users WHERE id = ${userId}
</select>

#{}은 PreparedStatement의 ? 파라미터로 변환되어 바인딩 변수가 적용됩니다. MyBatis 공식 문서에서도 "This tells MyBatis to create a PreparedStatement parameter"라고 설명하고 있습니다. ${}는 값이 SQL 텍스트에 그대로 삽입(literal substitution)되어 매번 다른 SQL로 인식됩니다.

${}를 써야 하는 경우가 있긴 합니다. 테이블명이나 컬럼명, ORDER BY 절처럼 SQL 구조 자체를 동적으로 바꿔야 할 때는 바인딩 변수를 쓸 수 없어서 ${}를 쓸 수밖에 없습니다. 하지만 WHERE 절의 값에는 항상 #{}을 써야 합니다.

바인딩 변수를 쓰면 무조건 좋은가#

대부분의 OLTP 쿼리에서는 그렇지만, 예외가 있습니다.

데이터 분포가 극단적으로 편향된 경우(bind variable peeking 문제)입니다. 예를 들어 status 컬럼에 99%가 'ACTIVE'이고 1%가 'INACTIVE'인 테이블이 있다고 하면:

SELECT * FROM orders WHERE status = :status;

Oracle에서 바인딩 변수를 쓰면, 최초 하드 파싱 시 옵티마이저가 바인드 변수의 첫 번째 값을 peek해서 실행 계획을 만듭니다. 'ACTIVE'가 먼저 들어오면 "99% 행을 가져오니까 풀스캔이 낫겠다"고 판단해서 풀스캔 실행 계획을 생성합니다. 이후 'INACTIVE'로 호출되면 1%만 가져오면 되는데도 풀스캔 계획이 재사용되면서 비효율이 발생합니다.

Oracle 11g부터는 Adaptive Cursor Sharing으로 이 문제를 자동 대응합니다. 커서를 bind-sensitive로 마킹하고, 실행할 때마다 예상 행 수와 실제 행 수를 비교합니다. 큰 차이가 감지되면 커서를 bind-aware로 전환하고, 다른 바인드 값 범위에 대해 별도의 Child Cursor(다른 실행 계획)를 생성합니다. (ORACLE-BASE - Adaptive Cursor Sharing)

MySQL의 경우 실행 계획 캐시(execution plan cache) 자체가 없어서 매 실행마다 옵티마이저가 새로 실행 계획을 생성합니다. 따라서 bind variable peeking 문제가 구조적으로 발생하지 않습니다. 대신 매번 optimization 오버헤드가 발생한다는 트레이드오프가 있습니다. 참고로 MySQL 8.0에서는 Query Cache도 제거되었는데, 이건 실행 계획이 아니라 결과 셋을 캐싱하는 기능이었습니다. (use-the-index-luke - Planning For Reuse)

정리#

단순한 SELECT가 느릴 때 실행 계획만 보지 말고, 같은 패턴의 쿼리가 파라미터만 바뀌면서 반복되고 있는지도 확인해볼 필요가 있습니다. 라이브러리 캐시에 유사한 SQL이 수천 개 쌓여 있다면 하드 파싱 반복이 원인일 수 있습니다.

Spring Data JPA를 쓰면 대부분 자동으로 바인딩 변수가 적용되지만, 네이티브 쿼리를 직접 작성하거나 MyBatis에서 ${}를 쓰는 경우에는 의식적으로 챙겨야 합니다. 성능뿐 아니라 SQL 인젝션 방지 측면에서도 바인딩 변수는 기본값이어야 합니다.