[SQL] spring JPA 에서 자주 등장하는 ':1' 은? (feat 파라미터 바인딩)

spring boot 프로젝트에서 JPA 를 이용해서 sql을 작성한 경우, 실행 로그를 볼 때 

select m from Table m where m.name = :name
select m from Table m where m.name = :0

 

이런 식으로 로그가 찍히는 것을  볼 때가 있다. 

처음에는 JPA 에서 동적으로 쿼리를 만들기 위해서 존재하는 기능인가보다~ 생각했었는데, 

 

요즘 SQL 을 깊게 공부하다 보니 이건 JPA 에서만 사용하는게 아니라, 

SQL 의 파라미터 바인딩 개념이라는 것을 알게 되었다. 

 

간단히 원리를 설명하면, SQL 은 우리가 select, from, where, order by 등 선언적으로 (~하라 는 식으로만 작성. ) 작성하지만, 실제로 동작하기 위해서는 절차적인 언어로( table을 어떻게 순회해서~ ~해라.. ) 변환해야 실제로 동작할 수 있다.

 

그렇게 절차적인 언어로 만든 것을 실행가능한 프로시저라고 부르고, 이 프로시저를 만드는 역할은 sql 옵티마이저가 한다. 

SQL 옵티마이저는 sql 을 실행하기 위한 다양한 방법 (table 을 full scan 할 건지, 인덱스를 활용할 건지, 어떤 join 방법을 사용할 건지)에 대해 마치 네비게이션 경로를 탐색하듯, 각 방법에 대한 비용을 계산하고, 이 중 최소 비용을 택해 실행하게 된다.

 

실제로 어떻게 실행할 건지 실행 계획 및 비용을 보려면, mysql 을 기준으로 실행하려는 쿼리 앞에 explain 을 붙여서 실행하면 된다. 

 

아무튼 sql 을 실행하기 위해서는 이렇듯 꽤나 무거운 작업이 동반되는데, 매번 이런일이 일어난다고 하면, 실시간 it 서비스를 하는 곳에서는 큰일이 날 것이다. 보통은 한 sql 에 대해 캐싱이 되는데, 매번 sql 에 대해 프로시저를 만드는 것이 아니라 사실 캐시에서 먼저 조회해서, 이미 만들어둔 프로시저가 있으면 그대로 사용하는 것이 보통이다. (캐싱되는 위치는 system global area (SGA) 의 라이브러리 캐시)

 

캐시가 된다면, 무엇을 key 로 해서 저장할까? 

놀랍게도, sql 쿼리 텍스트 그 자체를 key로 삼는다. 

 

여기서 발생하는 문제를 생각해보자. 

where name="ebang1", where name="ebang2" 조건이 들어간 서로 다른 쿼리가, 서로 다르게 캐싱된다. 

 

카카오톡 같은 서비스에서 수많은 멤버이름에 대해 매번 새롭게 Sql 이 캐싱되고 조회된다면, 이 얼마나 비효율적인 프로세스일까? 

이런 상황에서 바로 파라미터 바인딩을 사용한다. 

 

 

MySQL에서는 ?(플레이스홀더)를 사용하여 Prepared Statements를 실행할 수 있다. 

MySQL에서 직접 PREPARE를 사용하여 파라미터를 바인딩한 코드이다. 

PREPARE stmt FROM 'SELECT * FROM members WHERE name = ? AND age = ?'; 
SET @name = 'John';
SET @age = 30;
EXECUTE stmt
USING @name, @age;

 

이렇게 되면 장점은 매번 같은 조회를 조건만 다르게 하려는 경우, 미리 만들어둔 프로시저를 캐시에서 쉽게 꺼내서 실행할 수 있다는 점이다. 

 

JPA 에서는 기본적으로 이 파라미터 바인딩을 사용해서 쿼리를 요청하고 있었던 것이다! 

 

 

 

 

 

* sql 이 텍스트 자체로 내부적으로 캐싱되어 저장되고, 영구 저장되지 않는 이유는 다음과 같다. 

기본적으로 변경이 되면 완전히 새로운 내용이 되기 때문에, 이름이 같은 sql이 같은 기능을 하는 것을 유지하려면 변경 시 새로운 객체가 탄생되는 구조가 되어야 한다. 매번 새롭게 저장되는 것이 메모리 상 부하도 많아지게 되고, sql 을 사용해봐서 알겠지만 일회성 sql 도 많이 탄생하다보니, 영구 저장하는 것이 효율적이지 않아서, 내부적으로 캐싱만하고, 없으면 버려지는 식으로 작동하게 되었다. 

단, IBM, DB2 같은 DBMS 는 sql 을 영구저장하고 있다고는 하다. 

 

* SQL 을 실행하면 캐시에서 먼저 조회하고, 없으면 생성 후 프로시저를 캐싱한다. 

만약 한번 실행한 쿼리에 대해 다음에 실행하면 캐시에서 조회되어 바로 실행될텐데, 

이전에 말했던대로 sql 옵티마이저가 이미 최적화해둔 쿼리이기 때문에 의미가 크다. 

그런데 만약 sql 옵티마이저가 판단했을 때 최적의 비용이었던 그 쿼리가 db 상태가 변함에 따라 최적이 아니게 된다면? 

sql 옵티마이저가 당시에 판단을 위해 사용했던 통계 데이터는 변하기 마련이다. 

실제로 db 가 자주 변하는 상황에서는 이렇게 캐싱된 쿼리를 바로 사용하는 것이 마냥 좋은 성능을 나타내지는 않을 수도 있다. 

따라서 캐싱된 쿼리를 사용하는 파라미터 바인딩 역시 실시간으로 빠르게, 자주 조회되는 곳에서는 큰 성능 효과를 볼 수 있지만 

항상 최적의 정답이 아니라는 것도 염두에 두어야 한다. 

 

 

SQL 파싱 및 IO 에 대한 정리는 다음 글에 있다. 

https://ebang.tistory.com/180

[Sql 튜닝] SQL 처리과정과 IO