Mssql Mybatis DB툴과 웹에서 쿼리속도가 다를때 고려해 볼것

2021-10-26

Spring

스카우터에서 특정 쿼리를 수행할때 속도가 너무 느리다는 알람이 계속 출력되었다. 출력하는 양이 많아서 그런지 테스트를 해보았는데 조회되는 수량이 없다. 물론 스캔 수가 많을수는 있다지만 쿼리 결과가 0인 쿼리가 속도가 타임아웃이 걸릴 정도로 속도가 느리다니 뭔가 이상하다고 생각했다. 그래서 Mybatis의 쿼리를 DB툴로 가지고 와서 돌려보면 0.01초 만에 해결이 된다. 왜지?!

1. MSSQL의 특징

여러 가지를 검색해보고 알게된 사실은 이런 현상이 발생하는 경우 대부분 WHERE 절 조건이 VARCHAR로 되어 있다는 것이다. MSSQL의 경우 VARCHAR형 데이터를 비교할때 NVARCHAR형으로 변환하여 데이터를 비교 한다고 한다. VARCHAR와 NVARCHAR의 차이점은 오라클의 VARCHAR2 와 VARCHAR와 비슷한 관계라고 생각된다. 즉 데이터의 사이즈를 가변으로 할지 아닐지 결정하는 것으로 보인다. 회사에서는 DB를 MySQL과 MSSQL 두가지를 사용하고 있어서 인지 각 데이터 베이스만 지원하는 형식의 데이터 타입은 최대한 사용하지 않고 테이블을 구성하였다. 따라서 쿼리를 사용할때마다 강제적으로 타입 캐스팅이 일어나고 수행되는 쿼리의 타입이 다르기 때문에 인덱스를 아무리 걸어도 동작하지 않는다. 이부분이 핵심으로 보인다.

2. Mybatis의 특징

사실 특징이라고 하기 보단 사용법에 가깝다. mybatis에서 사용하면서 파라미터 값을 받기 위해서 #{value} 형태로 데이터를 출력하는 경우가 많았을 것이다. 그런데 ${value} 을 이용할수도 있다. 차이점은 Preparedstatement와 Statement의 차이다. 대부분 공부를 했을때 preparedstatement가 쿼리를 미리 생성하고 데이터를 넣기 때문에 성능에 유리하다! 라고 배웠을 것 같다.

나도 그렇게 생각하고 있었고 대부분 맞는 말 이지만 사실 기능을 조금 분리해서 사용하면 더 좋은 상황이 발생하기도 한다. Statement는 쿼리에 데이터를 삽입할때 별도의 변환을 수행하지 않고 전달 값을 그대로 넣게 된다. 말그대로 텍스트를 삽입하게 된다. preparedstatement 는 데이터를 삽입할때 데이터의 타입에 따라 알아서 변경을 해준다. 텍스트를 삽입할경우 ‘‘같은 키워드를 자동으로 삽입해주고 형변환 등 작업을 수행해 줄수 있어 비교적 안정적으로 관리가 가능하다. 단, 실행계획이 꼬이지 않는다는 가정하에….

3. 쿼리 수정

쿼리 수정은 정말 간단하다. #{value} 를 ${value} 형태로 변경하면 된다. 여기서 주의 할 점은 스트링 타입을 사용하기 위해서는 ‘‘(작은 따옴표)를 추가해서 데이터를 넣어야 한다는 것이다. LIKE연산을 수행할때도 Statement는 별도로 변환을 시켜주지 않는다. 쿼리 입력시

SELECT * FROM table WHERE id LIKE '%' + '${value}' + '%'

이런 방식으로 쿼리를 작성해 주어야 한다. 또한 Statement로 쿼리를 작성할 경우 변경없이 그대로 변수를 넣어주기 때문에 쿼리물을 삽입해 버린다 같은 문제가 발생할수도 있다. 그래서 이부분은 Service나 Controller에서 문제점을 확인하고 막아줄수 있는 코드를 작성해야 추후에 문제점을 막을수 있다.

4. 의문점 - 강제로 Typecasting을 해주면?

Mybatis 에는 파라미터의 타입을 명시해줄수 있는 기능이 있다. #{value, jdbcType=VARCHAR}

위에 지원하는 타입을 보면 분명히 NVARCHAR가 있다. 그러면 데이터를 삽입할때 #{value, jdbcType=NVARCHAR}로 입력하면 타입캐스팅이 자동으로 수행되서 따로 문제가 없지 않을까? 결론은 안된다. jdbcType이 수행되는 조건이 JDBC타입은 insert, update 또는 delete 하는 null 입력이 가능한 칼럼에서만 필요하다. 라고 되어 있어서 인지 쿼리에 변화가 발생하지 않았다. 다른 해결 방법이 있는지는 계속 찾아 봐야 할것 같다.

https://mybatis.org/mybatis-3/ko/sqlmap-xml.html

4.1 강제로 타입 캐스팅 하기

Statement로 쿼리를 넣기에는 보안상 문제가 생길 것 같아 조금 조사를 해보면서 새로운 방법을 알게되서 포스팅에 추가 한다. 첫번째는 컬럼의 형을 변경해주는 CONVERT(), CAST()를 사용하라는 것! 비교하는 쿼리의 데이터 형을 변경해서 비교하면 mybatis에서 인식할때 타입 캐스팅한 데이터로 인식한다는 내용이다. 간단하게 쿼리를 작성하면 이런 식으로 하라는 것이다.

SELECT * FROM table WHERE name  =  CAST( #{value} AS VARCHAR)
SELECT * FROM table WHERE  name = CONVERT(VARCHAR(10), #{value})

두번째 방법은 옵티마이저를 끄라는 것

select /*+ RULE */
      e.empno,
          e.ename,
          d.dname
from  dept d, emp e
where  e.deptno = d.deptno;

힌트에 대해서는 자세하게 알지 못하지만 /*+ RULE */은 옵티마이저 모드를 변경하는 것으로 개발자가 작성한 쿼리를 우선시 한다.(쿼리 를 옵티마이저에서 변경하지 않는다.) 는 것을 의미 한다고 한다. 실제로 적용을 해보니 Statement로 쿼리를 돌릴때 보다 속도는 좋지 않다. 하지만 적어도 타임아웃이 발생하는 수준 까지는 아니여서 위 방법으로 쿼리를 정리하였다.

4.2 변수를 설정해서 넣어주기

이렇게 했는데도 또 쿼리가 오래 걸렸다는 메세지가 날라와서 다른 방법을 생각하다 아예 변수를 만들어서 넣어주는 방법으로 개선해 보기로 하였다. 원래 이 방법은 like문에서 %을 삽입할때 작은 따옴표가 가독성이 좋지 않아서 사용하던 방법이다. (정석은 아니라는 말이다.)

DECLARE @Name VARCHAR(50);
SET @Name = 'GGMOUSE';

select /*+ RULE */
      e.empno,
      e.ename,          
from  emp e
where  e.ename = @Name;

이런 식으로 미리 타입이 결정되어 있는 변수에 mybatis 로 값을 넘겨주는 방법이다. 단 변수 선언 방식은 DBMS마다 조금 씩 차이가 있고 솔직히 깔끔한 방법은 아니라고 생각한다. 정말 방법이 없을때 쓰는 방식이다.