ORM에서 VARCHAR 타입의 컬럼이 포함된 인덱스를 타지 못하는 문제 (SQL Server)

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)

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

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

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

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

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

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

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

요약하자면, varchar 타입 컬럼에 nvarchar 로 쿼리를 쳤을 때는 nvarchar 가 precedence 순위가 높기에 모든 테이블의 모든 row에서 타입 컨버젼이 발생해버리지만, nvarchar 컬럼에 varchar 로 쿼리를 쳤을 때는 query parameter에만 컨버젼을 발생시킨 후 쿼리를 치기에, 동일한 query plan이 발생한다는 것이다.

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

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

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

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

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

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

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

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

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