아이템 검색 인덱스 제공하기

아이템 검색은

• 이름
• 착용 가능 직업
• 최소 레벨
• 장비 종류

네 가지 조건으로 검색할 수 있도록 컨트롤러가 제공된다. 지금까지 ITEM 테이블에 적용되어 있는 인덱스는 PK 클러스터링 인덱스 뿐인데 이것만으로는 검색 속도를 높이는데 전혀 도움이 되지 않는다. 그래서 조금이나마 검색속도를 높이기 위해서 인덱스를 어떻게 태울지 고민해보았다.

지금 네가지 조건을 모두 기입했다고 가정할 때 쿼리는 다음과 같이 날아간다.

SELECT item_id, name
FROM Item
WHERE 	name like '%(입력한 검색어)%'
		and thief = true
        and required_level >= 50
        and category = 'CLAW'

일단 인덱스를 어떤 컬럼으로 구성해야하는지부터 알아보자. 먼저 검색어(name)의 경우에는 인덱스를 태울 수 없다. 왜냐하면 입력된 문자열을 포함하는 아이템을 찾는 것이기 때문에 포함(contain)조건으로 문자열을 매칭하게 된다. 매칭되는 문자열의 앞부분 일치가 아니면 정렬되어있는 인덱스의 특징을 이용해서 인덱스의 탐색범위를 줄일 수 없기 때문이다. 앞에서 인덱스를 태울 수 없다는 표현을 사용했는데 이것은 해당 컬럼으로 인덱스 탐색 범위를 줄일 수 없다는 것을 의미한다.

따라서 name 속성은 인덱스를 이루는 속성으로 포함시키지 않기로 했다. 처음에는 name 속성을 포함시키는 것도 고려하였다. 왜냐하면, 메이플 주문서 시뮬레이터에서 Item 데이터를 삽입(write)작업은 관리자만 할 수 있고, 조회(read)작업에 비해서 매우 적은 숫자로 발생한다. 그래서 이왕 아이템의 정보를 검색한다면 인덱스에서 검색조건 쿼리를 처리할 수 있는 커버링 인덱스로 구현해 세컨더리 스토리지 접근 횟수를 줄여 성능을 조금이라도 높일 수 있을 것이라 생각했다. 하지만 같은 논리라면 뒤에서 설명할 warrior, thief, …등의 모든 직업정보도 인덱스에 포함시켜 주어야한다. 인덱스의 크기가 너무 비대해지면 검색성능에도 영향이 갈것으로 보아 꼭 필요한 컬럼만 인덱스에 포함시키기로 하였다.

아이템의 최소 요구레벨(required_level) 컬럼으로는 인덱스 탐색범위를 줄일 수 있다. 인덱스에 컬럼이 포함되어있다면 레벨의 오름차순이든 내림차순이든 정렬이 되어있을 것이고 >= 로 범위연산을 하면 당연히 인덱스의 탐색범위도 줄일 수 있다.

Item 테이블에는 warrior, bowman, thief, … 등등 아이템을 사용할 수 있는지 유무가 boolean 타입으로 표현되는 컬럼들이 있다. thief값이 true라면 도적 직업이 아이템을 사용할 수 있다는 것을 의미하고, false라면 사용할수 없다는 것을 의미한다. 한번 최소 레벨 조건으로 걸러져 온 인덱스에서 값이 true인지 검사하는 것은 인덱스의 탐색 범위를 줄이는 것이 아니라 그냥 체크 조건으로 사용된다.

category 컬럼도 마찬가지이다. ENUM 타입의 문자열의 일치여부만 확인하기 때문에 required_level 속성에 의해 인덱스의 범위가 제한되고 나서부터는 단순 체크 조건이다.

결론적으로 Item 테이블에는 최소 요구레벨(required_level) 컬럼만을 키값으로 가지는 인덱스를 만들어 적용하기로 결정하였다.