[트러블슈팅] ISBN 검색속도 저하 문제

TEXT관련된 검색은 Full-Text 검색으로 속도가 많이 향상되었다.

그러나 숫자로만 이루어져있는 isbn에 대한 검색이 같은 쿼리로는 검색할 수 없다.

isbn 자료는 b트리 인덱스로 만들어뒀기 때문에 JPQL로 수정하여 검색할 수 있게 하였다.

@Query("SELECT b FROM Book b WHERE b.isbn = :isbn")
List<Book> findBooksByIsbn(@Param("isbn") String isbn);

속도도 나쁘지 않게 나오는 성능을 보여준다.

문제점 : isbn에서 한글자라도 틀리거나 덜 적으면 검색결과가 나오지 않는다.

비슷한 결과라도 나올 수 있게 하는 방법은 뭐가 있을까?

일단 Like절을 활용하여 쿼리를 수정해 적용했다. 

// ISBN 검색 Like사용 (B-tree 인덱스)
@Query("SELECT b FROM Book b WHERE b.isbn LIKE :isbn")
List<Book> findBooksByIsbn(@Param("isbn") String isbn);

Like절을 사용하고나니 검색시간이 100배 이상 걸렸다.

사용자 경험을 개선하기 위해서는 isbn이 제대로 입력되지 않아도 비슷한 결과를 찾아올 수 있어야 한다.

뭐가 문제일까?

 

B-tree 인덱스는 주어진 키 값이 정확하게 일치하는 레코드를 찾는 것을 목적으로 설계되었다. 

따라서 isbn을 정확하게 검색한다면 정보를 보다 빨리 찾아올 수 있지만 일부만 일치하는 경우는 아예 검색결과가 나오지 않게 되는 것이다. 사용자 입장에서는 빠르게 찾아오는 것을 선호할까? 아니면 비슷한 숫자를 입력했을 때에도 비슷한 자료라도 보여주는 것을 선호할까?

인덱스를 사용하는것보다 일반 검색이 더 낫지 않을까?

차이가 미비하지만 검색이 빠른경우도 있고 더 느린경우도 있었다.

인덱스를 지우고 일반 쿼리를 짜서 한번 실험해보았다. 

// ISBN 검색 Like미사용 (B-tree 인덱스)
@Query("SELECT b FROM Book b WHERE b.isbn = :isbn")
List<Book> findBooksByIsbn(@Param("isbn") String isbn);

인덱스 없이 검색하는 것은 정말 오랜 시간이 걸린다.

인덱스 없이 검색하는 것은 정확히 입력하더라도 500만건의 데이터안에서 정확한 자료를 찾아오는 것은 쉽지 않은 일이었다. 

인덱스를 사용하면서도 비슷한 자료를 찾아오기 위해서는 b트리인덱스의 장점을 포기해야한다.

 

그렇다면 다른방식의 인덱싱 방법은 없을까?

1. 해시테이블 : 해시 함수를 이용하여 값을 키(Key)와 연결하여 저장하는 자료구조다. ISBN을 해시 함수에 적용하여 해시 값(Hash Value)을 계산하고, 해당 해시 값에 대응하는 버킷(Bucket)에 ISBN을 저장하는 방식이다. 

 

ngram 인덱스는 full-text검색만 가능하다

 

실험해보고 싶은것. 토큰사이즈가 크면 속도는 어떻게 변할까? 

innodb_ft_min_token_size = 4

토큰사이즈 변경 후 인덱스 다시 만들기

시간 검색 확인 

1. isbn 정확하게 입력했을 경우

@Query("SELECT b FROM Book b WHERE b.isbn = :isbn")
List<Book> findBooksByIsbn(@Param("isbn") String isbn);

이전과 비슷하다

2. Like를 사용했을 경우

// ISBN 검색 Like사용 (B-tree 인덱스)
@Query("SELECT b FROM Book b WHERE b.isbn LIKE :isbn")
List<Book> findBooksByIsbn(@Param("isbn") String isbn);

토큰 사이즈를 4로 늘리자 유의미한 속도향상이 이뤄졌다.

그렇다면 일부만 입력했을 때도 검색이 가능할까?

검색이 되지 않는다. 어째서...

제목같은 경우는 일부만 일치해도 나왔는데 isbn은 왜 안나오는걸까.

 

문제는 ISBN컬럼이 Long타입으로 되어있어서 토큰사이즈에 맞춰 인덱싱할 수 없다는 것이었다.

토큰사이즈를 4로 변경하고 익덱싱 작업을 해서 그렇게 처리 됐을것이라고 생각했는데 그게 아니었다. 

int나 Long 타입은 두가지로 인덱싱 작업이 가능하다. 

 

Long타입은 b-tree 인덱싱, Hash 인덱스 가능하다.

• B-tree 인덱스는 범위 검색(Range scan)에 적합
• HASH 인덱스는 등호 검색(Exact match)에 적합  : 따라서, LONG 타입의 컬럼이 등호 검색을 많이 하는 컬럼이라면, HASH 인덱스를 사용하는 것이 더 효율적일 수 있다.

검색을 위해 Long을 String으로 바꾸는 것이 가능하다.

SELECT CAST(ISBN_THIRTEEN_NO AS CHAR) AS ISBN_THIRTEEN_NO_STR FROM books;

검색에 최적화를 위해서 데이터타입을 변경하는 것은 어떨까?

단점이 있을까?

메모리 부하에 대한 문제점?

서버 사용량의 증가?

그냥 정확하게 입력하게 해서 검색하는게 나을까?

 

선택의 연속이다

 

 

테스트 ISBN 검색 자료

9791185390116

9788968306273

9788954820400