MyBatis를 쓰는데 상품 검색이 들어올 때마다 하드 파싱이 일어난다

기초SELECT이커머스550 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

이커머스의 상품 검색 API(Spring, MyBatis)는 카테고리와 검색어, 정렬 기준을 받아 상품을 찾는다. 세일 기간에는 초당 20번쯤 호출된다. DBA가 하드 파싱이 시간당 5만 번이라고 알렸지만, 개발팀의 답은 이랬다. "MyBatis를 쓰니까 바인드는 프레임워크가 알아서 합니다."

테이블과 인덱스

products: 280행 · 8블록 · PK(product_id)만 있다 · category_id, name
categories: 53행 · 4블록 · 상품은 카테고리마다 5개 남짓이다
정렬 기준은 이름순(name)과 최신순(product_id DESC) 둘이고, 화면은 고른 정렬 문구를 그대로 sort 파라미터로 넘긴다.
CURSOR_SHARING = EXACT

원본 SQL

sql

<!-- ProductMapper.xml -->
<select id="search" resultType="ProductDto">
  SELECT p.product_id, p.name, p.category_id
    FROM products p
   WHERE p.category_id = ${categoryId}
     AND p.name LIKE '%${keyword}%'
   ORDER BY p.${sort}
</select>

실행계획 학습용 예시

xplan

[세일 첫날 20시대 한 시간 · V$SYSSTAT 증가분]
  execute count (이 API)        71,940
  parse count (hard)            51,320

[그 직후 V$SQLAREA · 이 API의 부모 커서 (일부)]
  SQL_ID          EXECUTIONS  SQL_TEXT
  9x2kq0w1m3c7a            1  SELECT p.product_id, p.name, p.category_id FROM products p WHERE p.category_id = 12 AND p.name LIKE '%텀블러%' ORDER BY p.name
  0f7hd2s8vb41n            1  SELECT p.product_id, p.name, p.category_id FROM products p WHERE p.category_id = 12 AND p.name LIKE '%보온병%' ORDER BY p.name
  3jr6a1y0q9p2t            2  SELECT p.product_id, p.name, p.category_id FROM products p WHERE p.category_id = 31 AND p.name LIKE '%우산%' ORDER BY p.product_id DESC
  …

[같은 시각 · FORCE_MATCHING_SIGNATURE로 묶은 이 API 무리]
  PARENTS   EXECS     MB
    3,410   4,020   49.6    (나머지는 이미 공유 풀에서 밀려났다)

[한 번 실행의 계획]
------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |      2 |00:00:00.01 |       8 |
|   1 |  SORT ORDER BY     |          |      1 |      1 |      2 |00:00:00.01 |       8 |
|*  2 |   TABLE ACCESS FULL| PRODUCTS |      1 |      1 |      2 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("P"."CATEGORY_ID"=12 AND "P"."NAME" LIKE '%텀블러%'))

문제

MyBatis를 쓰는데도 검색할 때마다 하드 파싱이 일어나는 이유를 매퍼와 V$SQLAREA로 설명하시오.
매퍼를 고치시오. 정렬 기준처럼 바인드 변수로 넘길 수 없는 부분은 어떻게 처리할지도 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

<!-- ProductMapper.xml : 값은 #{} (바인드 변수) · 정렬은 정해 둔 두 문장 중 하나 -->
<select id="search" resultType="ProductDto">
  SELECT p.product_id, p.name, p.category_id
    FROM products p
   WHERE p.category_id = #{categoryId}
     AND p.name LIKE '%' || #{keyword} || '%'
  <choose>
    <when test="sort == 'latest'"> ORDER BY p.product_id DESC </when>
    <otherwise>                    ORDER BY p.name </otherwise>
  </choose>
</select>

개선 후 실행계획 학습용 예시

xplan

[고친 뒤 같은 한 시간 (학습용 예시)]
  parse count (hard)     2      (정렬 기준마다 한 번)
  V$SQLAREA              부모 2 · 실행 71,940

------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |      2 |00:00:00.01 |       8 |
|   1 |  SORT ORDER BY     |          |      1 |      1 |      2 |00:00:00.01 |       8 |
|*  2 |   TABLE ACCESS FULL| PRODUCTS |      1 |      1 |      2 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("P"."CATEGORY_ID"=:1 AND "P"."NAME" LIKE '%'||:2||'%'))

모범 답안 서술

MyBatis에서 #{}는 그 자리를 ?로 바꾸고 값을 PreparedStatement의 바인드 변수로 넘긴다. 반면 ${}는 값을 SQL 텍스트에 문자열로 그대로 끼워 넣는다. 이 매퍼는 카테고리와 검색어를 ${}로 넣었으므로, 검색어가 바뀔 때마다 처음 보는 문장이 되어 하드 파싱을 한다. V$SQLAREA에는 리터럴만 다른 부모가 줄지어 있고 대부분 실행 1번이다. 한 시간에 하드 파싱이 51,320번이었고, 남은 부모 3,410개(49.6MB)는 지나간 문장의 일부일 뿐이다. 값은 #{categoryId}, #{keyword}로 바인드한다. 검색어 앞뒤의 %는 '%' || #{keyword} || '%'처럼 SQL 쪽에서 붙인다. 정렬 컬럼은 식별자라 바인드 변수로 넘길 수 없으므로, 화면이 보낸 문구를 그대로 쓰지 않고 <choose>로 정해 둔 두 문장 중 하나를 고르게 한다(화이트리스트). 정렬 기준이 둘이면 문장도 둘이므로 하드 파싱 걱정이 없다. 고친 뒤에는 부모 2개가 71,940번 실행을 나눠 쓴다. ${}로 넣은 검색어는 SQL 주입 구멍이기도 하다. 검색어에 작은따옴표가 들어가면 문장이 깨지고, 조건을 덧붙인 문자열을 넣으면 다른 카테고리 상품까지 나온다. 정렬 문구를 그대로 붙이던 ORDER BY p.${sort}도 마찬가지다. 바인드 변수와 화이트리스트로 바꾸면 성능과 보안을 함께 고칠 수 있다.

해설

${}가 필요한 자리는 테이블 이름, 컬럼 이름, 정렬 방향처럼 바인드 변수로 넘길 수 없는 식별자뿐이다. 그때도 화면 값을 그대로 쓰지 않고, 코드에 정해 둔 값으로 바꿔 끼운다.
products는 8블록이라 실행 한 번은 가볍다. 하드 파싱 한 번(약 1ms)이 실행보다 비싸다. 작은 테이블을 자주 조회하는 화면일수록 파싱 비용이 차지하는 비율이 커진다.
검색어 앞에 %가 붙으므로 어느 값이 오든 인덱스 범위 스캔은 할 수 없다. 그래서 바인드 변수로 바꿔도 값마다 계획이 달라질 일이 없다.
CURSOR_SHARING = FORCE는 이런 문장을 서버에서 묶어 주지만, 일부러 쓴 리터럴까지 바꾸는 스위치다(PARSE-003). 매퍼 한 줄을 고칠 수 있으면 매퍼를 고친다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황