문제의 시작

Index가 있는데도 타지 않는 문제는 대부분 query plan을 보기 전까지 감으로 설명하기 어렵다. 특히 ORM을 쓰면 application code에서는 평범해 보이는 조건이 JDBC parameter type과 database collation/type conversion을 거치며 전혀 다른 실행 계획을 만들 수 있다. 이 글은 SQL Server에서 VARCHAR index가 무시되던 원인을 따라간 기록이다.

ORM에서 string 값을 쿼리에 사용할 때 쿼리를 NVARCHAR 타입으로 사용한다는 것이 관찰되었다. 하여 인덱스에 선언되어 있는 컬럼들 중 VARCHAR값이 있는데, 해당 인덱스를 사용하려 한다면 타입이 맞지 않아 인덱스를 타지 않는 문제가 발생하였다.

가령 가장 단순한 형태의 인덱스인 두 가지의 컬럼으로 이루어진 PK__CUST_INFO__7AA72534 (CARE_ORG_ID char(8), CUST_NO varchar(20)) 인덱스를 사용해보자.

통계에 오류가 발생하여 인덱스를 원활히 타지 않는다는 의심도 하여, option(recompile)을 통해 통계를 무시한 쿼리를 실행해본다.

구현하면서 확인한 흐름

select * from cust_info where CARE_ORG_ID = '15887361' and CUST_NO = '#1030197' option(recompile)
select * from cust_info where CARE_ORG_ID = N'15887361' and CUST_NO = N'#1030197' option(recompile)

결과를 어떻게 검증했는가

ace1b95f-ebf8-4a59-ae76-ee902a8491b5.png

DataGrip에서 실행된 Query Analysis이다. 위 쿼리에서는 의도했던 인덱스를 사용했지만, 아래 쿼리 (nvarchar 타입 파라미터 사용)에서는 뜬금없는 INDEX_CUST_INFO_CELL_PHONE index seek 후 pk index를 타는 것이 관찰된다.

CELL_PHONE 인덱스는 Non-Clustered 인덱스이기에 모든 컬럼을 들고 있지 않아 row를 가져온 후 pk 인덱스로 가져온 row와 join을 치는 모습이다.

34ee05af-9026-4d5f-8b2e-25b94bb10e63.png

CUST_INFO 복제 테이블에서 CELL_PHONE 인덱스를 날리고 쿼리를 실행(nvarchar params)한 결과이다.

operation이 단 하나인데 무엇을 Inner Join 하는건지 의문인데, 유추하기로는 nvarchar 타입으로 파라미터를 세팅했기에 테이블에 있는 전체 row의 CARE_ORG_ID 와 CUST_NO 를 nvarchar로 변환한 뒤 Join 하는 과정이 일어나는게 아닐까 싶다.

이제 CARE_ORG_ID 와 CUST_NO 컬럼 타입 모두를 nvarchar로 바꾼 뒤 동일 쿼리 두개를 재실행해본다.

4ae3b4d1-8bb3-42ac-81aa-9c44bcd2c30c.png

성능 분석 기준

ORM은 SQL을 숨겨주지만 database 동작까지 숨겨주지는 않는다. Parameter type, implicit conversion, collation, index column type이 맞지 않으면 좋은 index도 무력해질 수 있다. 성능 문제를 볼 때는 application 코드와 실제 database execution plan을 반드시 함께 읽어야 한다.

타입이 모두 nvarchar일 때는, 파라미터가 nvarchar이건 varchar이건 모두 인덱스를 사용한다는 것을 알 수 있다.

이전에 있었던 테이블 컬럼들의 타입 컨버전이 왜 없었냐 하면, 이는 https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16 에서 확인 가능하다.

요약하면 varchar type column에 nvarchar parameter로 조회할 때는 nvarchar의 precedence가 더 높기 때문에 table의 모든 row에서 type conversion이 발생한다. 반대로 nvarchar column에 varchar parameter로 조회할 때는 query parameter 쪽에서만 conversion이 일어나므로 동일한 query plan이 유지된다.

가장 단순한 형태의 인덱스를 태우려 했음에도 실패했다는 것을 보아 여타 join이 많게는 10테이블 이상 이루어지는 ORM에서 실행되는 모든 쿼리들이 의도한 인덱스를 전혀 사용하지 못하고 있다는 사실이 자명해졌다. 이에 해결책은 크게 세가지가 떠오른다.

  1. 인덱스를 타는 모든 컬럼의 타입을 nvarchar로 변환한다.

nvarchar는 유니코드를 지원하기에 character당 1byte를 사용하는 varchar/char와 달리 2byte를 소모하여, 데이터베이스의 전체적인 용량 뿐 아니라 인덱스의 용량도 비대해진다.

위와 연결되어 데이터값이 커지기에 I/O 작업의 시간소모가 커지며, 메모리 사용량 또한 증가한다.

컬럼의 타입 변환은 해당 컬럼에 인덱스에 사용될 경우 인덱스 drop 후 타입 변환, 인덱스 재생성의 절차가 필요하다.

nvarchar 컬럼을 사용해도 varchar 파라미터로 인덱스를 태울 수 있음을 위에서 확인했지만, 알 수 없는 side-effect(특히 native query를 사용하는 프로그램)가 존재할 수 있다.

  1. ORM에서 string 파라미터를 기본적으로 nvarchar로 유지하되, 인덱스를 타는 컬럼들에서 예외적으로 varchar/char 로 쿼리가 나가도록 한다.

가장 이상적인 해결책이나, Spring Boot 프로젝트에서 이를 적용하는데 이상하리만치 고행이다. 별도의 문서를 작성한다.

  1. 반대로 모든 string 파라미터를 varchar로 하고, nvarchar로 선언된 일부 컬럼들에 대한 핸들링